CREATE OR REPLACE PROCEDURE CONSULTA_DESONERACAO_PED(
   P_CDFILIAL     IN VARCHAR2,
   P_NRPEDIDO   IN VARCHAR2,
   P_CDPAISDEST   IN VARCHAR2,
   P_SGESTADODEST IN VARCHAR2,
   P_VRDESONERA OUT FLOAT)
IS
   V_VRDESONERA     FLOAT;
   V_CDDESTINO      VARCHAR2(50);
   V_IDORIGDESTNF   VARCHAR2(1);
   V_VRIMPOSTODESON FLOAT;
   V_CDPAISEMIT     VARCHAR2(04);
   V_SGESTADOEMIT   VARCHAR2(02);
   V_VRPERREDBCIT   FLOAT;
   V_VRALIQNORMAL   FLOAT;
   V_IDENTRSAIDOP   VARCHAR2(01);
   V_CDTIPOOPER     VARCHAR2(02);
BEGIN
   V_VRDESONERA := 0;

   BEGIN
      SELECT NVL(P.CDFILIALPED, P.CDCLIENTE) AS CDDESTINO,
             I.CDPAIS,
             I.SGESTADO,
             P.CDTIPOOPER
        INTO V_CDDESTINO,
             V_CDPAISEMIT,
             V_SGESTADOEMIT,
             V_CDTIPOOPER
        FROM PEDIDO P JOIN FILIAL F ON (P.CDFILIAL = F.CDFILIAL)
                      LEFT JOIN INSCRESTAD I ON (F.CDEMPRESA = I.CDEMPRESA)
                                            AND (F.CDINSCESTA = I.CDINSCESTA)
       WHERE P.CDFILIAL = P_CDFILIAL
         AND P.NRPEDIDO = P_NRPEDIDO;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN BEGIN
         V_CDDESTINO := NULL;
         V_CDPAISEMIT := NULL;
         V_SGESTADOEMIT := NULL;
         V_CDTIPOOPER :=  NULL;
      END;
   END;

   FOR C_IMPITEMPEDIDO IN (SELECT IP.CDFILIAL,
                                  IP.NRPEDIDO,
                                  IP.NRITEMPED,
                                  IP.CDIMPOSTO,
                                  NVL(IP.IDCONSDESONVRNF, 'N') AS IDCONSDESONVRNF,
                                  IP.VRIMPOSTOITPED,
                                  IP.CDSITUTRIBITPED,
                                  ((IT.QTITEMPED * ROUND(IT.VRPREUNITPED,2)) - (ROUND(NVL(IT.VRDESITPED,0),2))) AS VRTOTAITEM,
                                  ((IP.VRALIQITPED / 100) + (NVL(VRALIQITFECP, 0)/100)) AS VRALIQITEM,
                                  (IP.VRPERREDBCITPED / 100) AS VRPERREDBCIT
                            FROM ITEMPEDIDO IT LEFT JOIN IMPITEMPEDIDO IP ON (IT.CDFILIAL = IP.CDFILIAL)
                                                                         AND (IT.NRPEDIDO = IP.NRPEDIDO)
                                                                         AND (IT.NRITEMPED = IP.NRITEMPED)
                           WHERE IT.CDFILIAL = P_CDFILIAL
                             AND IT.NRPEDIDO = P_NRPEDIDO
                             AND IP.IDDESONERAREGRA = 'S')
   LOOP
      V_VRIMPOSTODESON := C_IMPITEMPEDIDO.VRIMPOSTOITPED;

      IF (V_CDPAISEMIT = '0055') AND (V_SGESTADOEMIT = 'RJ') THEN

         IF C_IMPITEMPEDIDO.CDSITUTRIBITPED IN ('20', '70') THEN
            IF (NVL(C_IMPITEMPEDIDO.VRPERREDBCIT, 0) = 0) THEN
               BEGIN
                  SELECT (NVL(CONSULTA_QUADRO_ALIQUOTAS(V_SGESTADOEMIT, P_SGESTADODEST), 0) / 100)
                    INTO V_VRALIQNORMAL
                    FROM DUAL;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN BEGIN
                     V_VRALIQNORMAL := 0;
                  END;
               END;

               V_VRPERREDBCIT := (C_IMPITEMPEDIDO.VRALIQITEM / V_VRALIQNORMAL);
            ELSE
               V_VRPERREDBCIT := C_IMPITEMPEDIDO.VRPERREDBCIT;
            END IF;

            -- VALOR DO ICMS DESONERADO = PRECO NA NOTA FISCAL * (1 - (ALIQUOTA * (1 - PERCENTUAL DE REDUCAO DA BC))) / (1 - ALIQUOTA) - PRECO NA NOTA FISCAL
            V_VRIMPOSTODESON := ROUND(C_IMPITEMPEDIDO.VRTOTAITEM * (1 - (C_IMPITEMPEDIDO.VRALIQITEM * (1 - V_VRPERREDBCIT))) / (1 - C_IMPITEMPEDIDO.VRALIQITEM) - C_IMPITEMPEDIDO.VRTOTAITEM, 2);
         END IF;

         IF C_IMPITEMPEDIDO.CDSITUTRIBITPED IN ('30', '40') THEN
            -- VALOR DO ICMS DESONERADO = (PRECO NA NOTA FISCAL / (1 - ALIQUOTA)) * ALIQUOTA
               V_VRIMPOSTODESON := ROUND((C_IMPITEMPEDIDO.VRTOTAITEM / (1 - C_IMPITEMPEDIDO.VRALIQITEM)) * C_IMPITEMPEDIDO.VRALIQITEM, 2);
         END IF;
      END IF;

      IF C_IMPITEMPEDIDO.IDCONSDESONVRNF = 'S' THEN
         V_VRDESONERA := V_VRDESONERA + V_VRIMPOSTODESON;
      END IF;

      UPDATE IMPITEMPEDIDO
         SET IDIMPDESONERAIT = CASE C_IMPITEMPEDIDO.IDCONSDESONVRNF WHEN 'S' THEN 'S' ELSE 'N' END,
             VRIMPOSTODESON = V_VRIMPOSTODESON
       WHERE CDFILIAL  = C_IMPITEMPEDIDO.CDFILIAL
         AND NRPEDIDO  = C_IMPITEMPEDIDO.NRPEDIDO
         AND NRITEMPED = C_IMPITEMPEDIDO.NRITEMPED
         AND CDIMPOSTO = C_IMPITEMPEDIDO.CDIMPOSTO;

   END LOOP;

   FOR C_IMPOSTO IN (SELECT IT.CDFILIAL,
                            IT.NRPEDIDO,
                            IT.NRITEMPED,
                            IT.CDPRODUTO,
                            IP.CDIMPOSTO,
                            IP.CDFISCALITPED,
                            IP.IDIMPDESONERAIT,
                            IP.VRIMPOSTOITPED
                       FROM ITEMPEDIDO IT, IMPITEMPEDIDO IP, IMPOSTO IM, PRODUTO PR
                      WHERE IP.CDFILIAL  = IT.CDFILIAL
                        AND IP.NRPEDIDO  = IT.NRPEDIDO
                        AND IP.NRITEMPED = IT.NRITEMPED
                        AND IM.CDIMPOSTO = IP.CDIMPOSTO
                        AND PR.CDPRODUTO = IT.CDPRODUTO
                        AND IT.CDFILIAL  = P_CDFILIAL
                        AND IT.NRPEDIDO  = P_NRPEDIDO
                        AND IM.IDDESONERAIMPO = 'S'
                        AND IP.IDDESONERAREGRA = 'N'
                        AND EXISTS (SELECT PC.CDPRODUTO, PC.CDCLIENTE AS DESTINO
                                      FROM PRODESONERAIMCLI PC, PRODUTO PRO
                                     WHERE PRO.CDPRODUTO = PC.CDPRODUTO
                                       AND PC.CDCLIENTE = V_CDDESTINO
                                       AND V_IDORIGDESTNF = 'C'
                                       AND INSTR(PR.CDARVPROD, NVL(RTRIM(PRO.CDARVPROD), PR.CDARVPROD)) = 1
                                     UNION ALL
                                    SELECT PL.CDPRODUTO, PL.CDFILIAL AS DESTINO
                                      FROM PRODESONERAIMFIL PL, PRODUTO PRO
                                     WHERE PRO.CDPRODUTO = PL.CDPRODUTO
                                       AND PL.CDFILIAL = V_CDDESTINO
                                       AND V_IDORIGDESTNF = 'L'
                                       AND INSTR(PR.CDARVPROD, NVL(RTRIM(PRO.CDARVPROD), PR.CDARVPROD)) = 1)) LOOP

      V_VRDESONERA := V_VRDESONERA + C_IMPOSTO.VRIMPOSTOITPED;

      UPDATE IMPITEMPEDIDO
         SET IDIMPDESONERAIT = 'S',
             VRIMPOSTODESON = C_IMPOSTO.VRIMPOSTOITPED
       WHERE CDFILIAL = C_IMPOSTO.CDFILIAL
         AND NRPEDIDO = C_IMPOSTO.NRPEDIDO
         AND NRITEMPED = C_IMPOSTO.NRITEMPED
         AND CDIMPOSTO = C_IMPOSTO.CDIMPOSTO;
   END LOOP;

   P_VRDESONERA := V_VRDESONERA;
END;
/