create or replace FUNCTION BUSCA_VRMINIMO_PED(
    P_CDFILIAL      IN FILIAL.CDFILIAL%TYPE,
    P_CDMUNICIPIO   IN MUNICIPIO.CDMUNICIPIO%TYPE,
    P_CDPAIS        IN MUNICIPIO.CDPAIS%TYPE,
    P_SGESTADO      IN MUNICIPIO.SGESTADO%TYPE,
    P_VRTOTPEDLIQ   IN ITEMPEDIDO.VRMARGITPED%TYPE,
    P_CDCLIENTE     IN CLIENTE.CDCLIENTE%TYPE,
    P_NRINSJURTRAN  IN TRANSPORT.NRINSJURTRAN%TYPE,
    P_IDTPIJURTRAN  IN TRANSPORT.IDTPIJURTRAN%TYPE)
    
RETURN VARCHAR2 IS

    V_VRMINPEDMUN		 MUNICIPIO.VRMINPEDMUN%TYPE;
    V_VRMINPEDREG		 REGIAOPED.VRMINPEDREG%TYPE;
    V_VRMINPEDTRANSP	 TRANSPORT.VRMINPEDTRANSP%TYPE;
    V_VRMINPEDCLI		 CLIENTE.VRMINPEDCLI%TYPE;
    V_VRMINPEDIDO		 PARAMPEDIDO.VRMINPEDIDO%TYPE;
    V_VRMINPED           NUMBER;
    V_RESULT             VARCHAR2(1);

BEGIN
   
   BEGIN
        SELECT VRMINPEDMUN 
            INTO V_VRMINPEDMUN
        FROM MUNICIPIO  
          WHERE CDMUNICIPIO = P_CDMUNICIPIO AND 
                SGESTADO = P_SGESTADO AND
                CDPAIS = P_CDPAIS;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
               V_VRMINPEDMUN := NULL;
    END;
   IF   (V_VRMINPEDMUN IS NULL)  THEN 
        BEGIN
            SELECT M.VRMINPEDMUN
                INTO V_VRMINPEDMUN
            FROM MUNICIPIO  M , ENDECLIE E
              WHERE M.CDMUNICIPIO = E.CDMUNICIPIO
                  AND E.CDCLIENTE = P_CDCLIENTE
                  /* ADICIONEI JOIN AQUI */
                  AND M.SGESTADO = E.SGESTADO
                  AND M.CDPAIS = E.CDPAIS
                  /* FIM */
                  AND (E.IDTPENDECLIE = 'T');
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                 V_VRMINPEDMUN := NULL;
        END;
   END IF;
   IF (V_VRMINPEDMUN IS NULL)  THEN
       BEGIN
            SELECT M.VRMINPEDMUN
                INTO V_VRMINPEDMUN
            FROM MUNICIPIO  M , ENDECLIE E
              WHERE M.CDMUNICIPIO = E.CDMUNICIPIO
                  /* ADICIONEI JOIN AQUI */
                  AND M.SGESTADO = E.SGESTADO
                  AND M.CDPAIS = E.CDPAIS
                  /* FIM */
                  AND E.CDCLIENTE = P_CDCLIENTE
                  AND (E.IDTPENDECLIE = 'P');
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                 V_VRMINPEDMUN := NULL;
        END;
   END IF;
    
   BEGIN
        SELECT R.VRMINPEDREG 
            INTO V_VRMINPEDREG 
        FROM  REGIAOPED R JOIN CLIENTE C                    
                ON R.CDREGIAOPED = C.CDREGIAOPED  
        WHERE CDCLIENTE = P_CDCLIENTE;
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
           V_VRMINPEDREG := NULL;
    END;
    
    BEGIN
        SELECT VRMINPEDTRANSP 
            INTO V_VRMINPEDTRANSP 
        FROM  TRANSPORT  
        WHERE NRINSJURTRAN = P_NRINSJURTRAN AND
              IDTPIJURTRAN = P_IDTPIJURTRAN;
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
           V_VRMINPEDTRANSP := NULL;
    END;
    
    BEGIN
        SELECT VRMINPEDCLI 
            INTO  V_VRMINPEDCLI
        FROM  CLIENTE  
        WHERE CDCLIENTE = P_CDCLIENTE;
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
           V_VRMINPEDCLI := NULL;
    END;
    
    BEGIN
        SELECT VRMINPEDIDO 
            INTO  V_VRMINPEDIDO
        FROM  PARAMPEDIDO  
        WHERE CDFILIAL = P_CDFILIAL;
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
           V_VRMINPEDIDO := NULL;
    END;

    IF(V_VRMINPEDMUN IS NOT NULL) AND (V_VRMINPEDMUN > 0) THEN
        V_VRMINPED := V_VRMINPEDMUN;
    ELSIF  (V_VRMINPEDREG IS NOT NULL) AND (V_VRMINPEDREG > 0)  THEN
        V_VRMINPED := V_VRMINPEDREG;
    ELSIF  (V_VRMINPEDTRANSP IS NOT NULL) AND (V_VRMINPEDTRANSP > 0)  THEN
        V_VRMINPED := V_VRMINPEDTRANSP;
    ELSIF  (V_VRMINPEDCLI IS NOT NULL) AND (V_VRMINPEDCLI > 0)  THEN
        V_VRMINPED := V_VRMINPEDCLI;
    ELSIF (V_VRMINPEDIDO IS NOT NULL) AND (V_VRMINPEDIDO > 0)  THEN
        V_VRMINPED := V_VRMINPEDIDO;
    ELSE
        V_VRMINPED := 0;
    END IF;

    IF (V_VRMINPED > P_VRTOTPEDLIQ) THEN
        V_RESULT := 'N';
    ELSE
        V_RESULT := 'S';
    END IF;

    RETURN (V_RESULT);
END;