CREATE OR REPLACE PROCEDURE CONSULTA_DESONERACAO(
   P_CDFILIAL IN VARCHAR2,
   P_NRLANCTONF 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(N.CDCLIENTE, NVL(N.CDFORNECED, DECODE(N.IDENTRSAIDOP, 'S', N.CDFILIRECE, N.CDFILIEMIT))) AS CDDESTINO,
             N.IDORIGDESTNF,
             I.CDPAIS,
             I.SGESTADO,
             N.IDENTRSAIDOP,
             N.CDTIPOOPER
        INTO V_CDDESTINO,
             V_IDORIGDESTNF,
             V_CDPAISEMIT,
             V_SGESTADOEMIT,
             V_IDENTRSAIDOP,
             V_CDTIPOOPER
        FROM NOTAFISCAL N JOIN INSCRESTAD I ON (N.CDEMPRESA = I.CDEMPRESA)
                                           AND (N.CDINSCESTA = I.CDINSCESTA)
       WHERE N.CDFILIAL = P_CDFILIAL
         AND N.NRLANCTONF = P_NRLANCTONF;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN BEGIN
         V_CDDESTINO := NULL;
         V_IDORIGDESTNF := NULL;
         V_CDPAISEMIT := NULL;
         V_SGESTADOEMIT := NULL;
         V_IDENTRSAIDOP := NULL;
         V_CDTIPOOPER :=  NULL;
      END;
   END;
   
   IF (V_IDENTRSAIDOP = 'S') AND (V_CDTIPOOPER = '03') THEN -- NOTA DE COMPLEMENTO
      BEGIN
         SELECT NVL(SUM(VRIMPOSTODESON), 0)
           INTO V_VRDESONERA
           FROM ITEMPROIMPOS
          WHERE CDFILIAL = P_CDFILIAL
            AND NRLANCTONF = P_NRLANCTONF
            AND IDIMPDESONERAIT = 'S';
      EXCEPTION
         WHEN NO_DATA_FOUND THEN BEGIN
            V_VRDESONERA := 0;
         END;
      END;
   ELSE
      FOR C_ITEMPROIMPOS IN (SELECT IP.CDFILIAL, 
                                    IP.NRLANCTONF, 
                                    IP.NRSEQITPROD, 
                                    IP.CDIMPOSTO,
                                    NVL(IP.IDCONSDESONVRNF, 'N') AS IDCONSDESONVRNF,
                                    IP.VRIMPOSTOIT,
                                    IP.CDSITUTRIBITNFE,
                                    (IT.VRTOTAITEM - IT.VRDESCITEM) AS VRTOTAITEM,
                                    ((IP.VRALIQITEM / 100) + (NVL(VRADICALIQFECP, 0)/100)) AS VRALIQITEM,
                                    (IP.VRPERREDBCIT / 100) AS VRPERREDBCIT
                               FROM ITEMPRODUTO IT LEFT JOIN ITEMPROIMPOS IP ON (IT.CDFILIAL = IP.CDFILIAL)
                                                                            AND (IT.NRLANCTONF = IP.NRLANCTONF)
                                                                            AND (IT.NRSEQITPROD = IP.NRSEQITPROD)
                              WHERE IT.CDFILIAL = P_CDFILIAL
                                AND IT.NRLANCTONF = P_NRLANCTONF
                                AND IP.IDDESONERAREGRA = 'S'
                                AND IP.NRITPROIMPOS = '1')
      LOOP
         V_VRIMPOSTODESON := C_ITEMPROIMPOS.VRIMPOSTOIT;
   
         IF (V_CDPAISEMIT = '0055') AND (V_SGESTADOEMIT = 'RJ') THEN
           
            IF C_ITEMPROIMPOS.CDSITUTRIBITNFE IN ('20', '70') THEN
               IF (NVL(C_ITEMPROIMPOS.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_ITEMPROIMPOS.VRALIQITEM / V_VRALIQNORMAL);
               ELSE
                  V_VRPERREDBCIT := C_ITEMPROIMPOS.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_ITEMPROIMPOS.VRTOTAITEM * (1 - (C_ITEMPROIMPOS.VRALIQITEM * (1 - V_VRPERREDBCIT))) / (1 - C_ITEMPROIMPOS.VRALIQITEM) - C_ITEMPROIMPOS.VRTOTAITEM, 2);
            END IF;
            
            IF C_ITEMPROIMPOS.CDSITUTRIBITNFE IN ('30', '40') THEN
               -- VALOR DO ICMS DESONERADO = (PRECO NA NOTA FISCAL / (1 - ALIQUOTA)) * ALIQUOTA
               V_VRIMPOSTODESON := ROUND((C_ITEMPROIMPOS.VRTOTAITEM / (1 - C_ITEMPROIMPOS.VRALIQITEM)) * C_ITEMPROIMPOS.VRALIQITEM, 2);
            END IF;
         END IF;
         
         IF C_ITEMPROIMPOS.IDCONSDESONVRNF = 'S' THEN
            V_VRDESONERA := V_VRDESONERA + V_VRIMPOSTODESON; --C_ITEMPROIMPOS.VRIMPOSTOIT;
         END IF;
   
         UPDATE ITEMPROIMPOS
            SET IDIMPDESONERAIT = CASE C_ITEMPROIMPOS.IDCONSDESONVRNF WHEN 'S' THEN 'S' ELSE 'N' END,
                VRIMPOSTODESON = V_VRIMPOSTODESON
          WHERE CDFILIAL = C_ITEMPROIMPOS.CDFILIAL
            AND NRLANCTONF = C_ITEMPROIMPOS.NRLANCTONF
            AND NRSEQITPROD = C_ITEMPROIMPOS.NRSEQITPROD
            AND CDIMPOSTO = C_ITEMPROIMPOS.CDIMPOSTO
            AND NRITPROIMPOS = '1';
   
         UPDATE TRABITPROIMPOS
            SET IDIMPDESOTRABIT = CASE C_ITEMPROIMPOS.IDCONSDESONVRNF WHEN 'S' THEN 'S' ELSE 'N' END ,
                VRIMPOSTODESON = V_VRIMPOSTODESON
          WHERE CDFILIMPOIT = C_ITEMPROIMPOS.CDFILIAL
            AND NRLANCIMPOIT = C_ITEMPROIMPOS.NRLANCTONF
            AND NRSEQIMPOIT = C_ITEMPROIMPOS.NRSEQITPROD
            AND CDIMPOSTOIT = C_ITEMPROIMPOS.CDIMPOSTO;
   
         UPDATE IMPOSTONF
            SET IDIMPDESONERANF = CASE C_ITEMPROIMPOS.IDCONSDESONVRNF WHEN 'S' THEN 'S' ELSE 'N' END
          WHERE CDFILIAL = C_ITEMPROIMPOS.CDFILIAL
            AND NRLANCTONF = C_ITEMPROIMPOS.NRLANCTONF
            AND CDIMPOSTO = C_ITEMPROIMPOS.CDIMPOSTO;
            
         UPDATE ITEMPRODUTO
            SET VRCONTITEM = CASE C_ITEMPROIMPOS.IDCONSDESONVRNF WHEN 'S' THEN VRCONTITEM - V_VRIMPOSTODESON ELSE VRCONTITEM END
          WHERE CDFILIAL = C_ITEMPROIMPOS.CDFILIAL
            AND NRLANCTONF = C_ITEMPROIMPOS.NRLANCTONF
            AND NRSEQITPROD = C_ITEMPROIMPOS.NRSEQITPROD;
      END LOOP;
   
      FOR C_IMPOSTO IN (SELECT IT.CDFILIAL, 
                               IT.NRLANCTONF, 
                               IT.NRSEQITPROD, 
                               IT.CDPRODUTO,
                               IP.CDIMPOSTO, 
                               IP.CDFISCALIT, 
                               IP.IDIMPDESONERAIT, 
                               IP.VRIMPOSTOIT
                          FROM ITEMPRODUTO IT, ITEMPROIMPOS IP, IMPOSTO IM, PRODUTO PR
                         WHERE IP.CDFILIAL = IT.CDFILIAL
                           AND IP.NRLANCTONF = IT.NRLANCTONF
                           AND IP.NRSEQITPROD = IT.NRSEQITPROD
                           AND IM.CDIMPOSTO = IP.CDIMPOSTO
                           AND PR.CDPRODUTO = IT.CDPRODUTO
                           AND IT.CDFILIAL = P_CDFILIAL
                           AND IT.NRLANCTONF = P_NRLANCTONF
                           AND IM.IDDESONERAIMPO = 'S'
                           AND IP.NRITPROIMPOS = '1'
                           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 PF.CDPRODUTO, PF.CDFORNECED AS DESTINO
                                         FROM PRODESONERAIMFOR PF, PRODUTO PRO
                                        WHERE PRO.CDPRODUTO = PF.CDPRODUTO
                                          AND PF.CDFORNECED = V_CDDESTINO
                                          AND V_IDORIGDESTNF = 'F'
                                          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.VRIMPOSTOIT;
   
         UPDATE ITEMPROIMPOS
            SET IDIMPDESONERAIT = 'S',
                VRIMPOSTODESON = C_IMPOSTO.VRIMPOSTOIT
          WHERE CDFILIAL = C_IMPOSTO.CDFILIAL
            AND NRLANCTONF = C_IMPOSTO.NRLANCTONF
            AND NRSEQITPROD = C_IMPOSTO.NRSEQITPROD
            AND CDIMPOSTO = C_IMPOSTO.CDIMPOSTO
            AND NRITPROIMPOS = '1';
   
         UPDATE TRABITPROIMPOS
            SET IDIMPDESOTRABIT = 'S',
                VRIMPOSTODESON = V_VRIMPOSTODESON
          WHERE CDFILIMPOIT = C_IMPOSTO.CDFILIAL
            AND NRLANCIMPOIT = C_IMPOSTO.NRLANCTONF
            AND NRSEQIMPOIT = C_IMPOSTO.NRSEQITPROD
            AND CDIMPOSTOIT = C_IMPOSTO.CDIMPOSTO;
   
         UPDATE IMPOSTONF
            SET IDIMPDESONERANF = 'S'
          WHERE CDFILIAL = C_IMPOSTO.CDFILIAL
            AND NRLANCTONF = C_IMPOSTO.NRLANCTONF
            AND CDIMPOSTO = C_IMPOSTO.CDIMPOSTO;
            
         UPDATE ITEMPRODUTO
            SET VRCONTITEM = VRCONTITEM - C_IMPOSTO.VRIMPOSTOIT
          WHERE CDFILIAL = C_IMPOSTO.CDFILIAL
            AND NRLANCTONF = C_IMPOSTO.NRLANCTONF
            AND NRSEQITPROD = C_IMPOSTO.NRSEQITPROD;
      END LOOP;
   END IF;

   P_VRDESONERA := V_VRDESONERA;
END;
/