CREATE OR REPLACE PROCEDURE CALCULA_ICMS60(
   P_CDFILIAL VARCHAR2,
   P_NRLANCTONF VARCHAR2,
   P_NRSEQITPROD VARCHAR2,
   P_VRALIQITEM FLOAT)
IS
   V_VRBASECALCIT FLOAT;
   V_VRIMPOSTOIT FLOAT;
   V_CDPRODESTO VARCHAR2(10);
   V_QTITEMPROD FLOAT;
   V_VRUNITITEM FLOAT;
   V_VRDESCITEM FLOAT;
   V_VRFATOCONV FLOAT;
   V_IDCONSFINAL VARCHAR2(01);
   V_CDFILIALICMS60 VARCHAR2(04);
   V_NRLANCTONFICMS60 VARCHAR2(10);
   V_NRSEQITPRODICMS60 VARCHAR2(04); 
   V_QTITEMPRODENTRADA FLOAT;
   V_VRUNITITEMENTRADA FLOAT;
   V_VRBASECALCITENTRADA FLOAT;
   V_VRIMPOSTOITENTRADA FLOAT;
   V_VRBCENTRADAUNIT FLOAT;
   V_VRIMPENTRADAUNIT FLOAT;
   V_VRICMSSUBSTITUTO FLOAT;
   V_CDIMPOSTO VARCHAR2(02);
   V_CDFILIEMIT VARCHAR2(04);
BEGIN
   V_VRBASECALCIT := 0;
   V_VRIMPOSTOIT := 0;
   
   BEGIN
      SELECT P.CDPRODESTO,
             IP.QTITEMPROD,
             IP.VRUNITITEM,
             IP.VRDESCITEM,
             P.VRFATOCONV,
             NVL(CASE NF.IDORIGDESTNF WHEN 'C' THEN C.IDCONSFINAL WHEN 'F' THEN F.IDCONSFINAL ELSE 'N' END, 'N') AS IDCONSFINAL,
             NF.CDFILIEMIT
        INTO V_CDPRODESTO,
             V_QTITEMPROD,
             V_VRUNITITEM,
             V_VRDESCITEM,
             V_VRFATOCONV,
             V_IDCONSFINAL,
             V_CDFILIEMIT
        FROM NOTAFISCAL NF JOIN ITEMPRODUTO IP ON(NF.CDFILIAL = IP.CDFILIAL)
                                              AND (NF.NRLANCTONF = IP.NRLANCTONF)
                      LEFT JOIN CLIENTE C ON (C.CDCLIENTE = NF.CDCLIENTE)
                      LEFT JOIN FORNECEDOR F ON (F.CDFORNECED = NF.CDFORNECED)
                           JOIN PRODUTO P ON (P.CDPRODUTO = IP.CDPRODUTO)
       WHERE IP.CDFILIAL = P_CDFILIAL
         AND IP.NRLANCTONF = P_NRLANCTONF
         AND IP.NRSEQITPROD = P_NRSEQITPROD;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN BEGIN
         V_CDPRODESTO := NULL;
         V_QTITEMPROD := 0;
         V_VRUNITITEM := 0;
         V_VRDESCITEM := 0;
         V_VRFATOCONV := 1;
         V_IDCONSFINAL := 'N';
         V_CDFILIEMIT := NULL;
      END;
   END;
   
   V_VRBASECALCIT := (V_QTITEMPROD * V_VRUNITITEM) - V_VRDESCITEM;
   V_VRIMPOSTOIT := V_VRBASECALCIT * P_VRALIQITEM;

   IF V_IDCONSFINAL <> 'S' THEN
      BEGIN
         SELECT A.CDFILIALICMS60,
                A.NRLANCTONFICMS60,
                A.NRSEQITPRODICMS60,
                A.QTITEMPRODENTRADA,
                A.VRUNITITEMENTRADA,
                A.VRBASECALCITENTRADA,
                A.VRIMPOSTOITENTRADA,
                (A.VRBASECALCITENTRADA / A.QTITEMPRODENTRADA) AS VRBCENTRADAUNIT,
                (A.VRIMPOSTOITENTRADA / A.QTITEMPRODENTRADA) AS VRIMPENTRADAUNIT,
                A.CDIMPOSTO
           INTO V_CDFILIALICMS60,
                V_NRLANCTONFICMS60,
                V_NRSEQITPRODICMS60,
                V_QTITEMPRODENTRADA,
                V_VRUNITITEMENTRADA,
                V_VRBASECALCITENTRADA,
                V_VRIMPOSTOITENTRADA,
                V_VRBCENTRADAUNIT,
                V_VRIMPENTRADAUNIT,
                V_CDIMPOSTO
           FROM (SELECT NF.CDFILIAL AS CDFILIALICMS60,
                        NF.NRLANCTONF AS NRLANCTONFICMS60,
                        II.NRSEQITPROD AS NRSEQITPRODICMS60,
                        IP.QTITEMPROD AS QTITEMPRODENTRADA,
                        IP.VRUNITITEM AS VRUNITITEMENTRADA,
                        II.VRBASECALCIT AS VRBASECALCITENTRADA,
                        II.VRIMPOSTOIT AS VRIMPOSTOITENTRADA,
                        II.CDIMPOSTO,
                        ROW_NUMBER() OVER (PARTITION BY CASE NF.IDENTRSAIDOP WHEN 'S' THEN NF.CDFILIEMIT ELSE NF.CDFILIRECE END ORDER BY NF.DTENTRSAID DESC) AS NRCHAVE
                   FROM NOTAFISCAL NF JOIN ITEMPRODUTO IP ON (NF.CDFILIAL = IP.CDFILIAL)
                                                         AND (NF.NRLANCTONF = IP.NRLANCTONF)
                                      JOIN (SELECT IA.CDFILIAL, IA.NRLANCTONF, IA.NRSEQITPROD, IA.CDIMPOSTO,
                                                   IA.VRBASECALCIT, IA.VRIMPOSTOIT
                                              FROM ITEMPROIMPOS IA
                                             WHERE EXISTS (SELECT DISTINCT IB.CDFILIAL, IB.NRLANCTONF, IB.NRSEQITPROD
                                                             FROM ITEMPROIMPOS IB JOIN IMPOSTO I ON (IB.CDIMPOSTO = I.CDIMPOSTO)
                                                            WHERE IA.CDFILIAL = IB.CDFILIAL
                                                              AND IA.NRLANCTONF = IB.NRLANCTONF
                                                              AND IA.NRSEQITPROD = IB.NRSEQITPROD
                                                              AND I.CDIMPOSTONFE = '00'
                                                              AND IB.CDSITUTRIBITNFE NOT IN ('40', '41', '90')
                                                              AND IB.NRITPROIMPOS = '1')) II ON (IP.CDFILIAL = II.CDFILIAL)
                                                                                           AND (IP.NRLANCTONF = II.NRLANCTONF)
                                                                                           AND (IP.NRSEQITPROD = II.NRSEQITPROD)
                                      JOIN PRODUTO PC ON (IP.CDPRODUTO = PC.CDPRODUTO)
                                      JOIN IMPOSTO I ON (II.CDIMPOSTO = I.CDIMPOSTO)
                  WHERE NF.IDENTRSAIDOP = 'E'
                    AND I.CDIMPOSTONFE = '80'
                    AND PC.CDPRODESTO = V_CDPRODESTO
                    AND CASE NF.IDENTRSAIDOP WHEN 'S' THEN NF.CDFILIEMIT ELSE NF.CDFILIRECE END = V_CDFILIEMIT) A
             WHERE A.NRCHAVE = '1';
      EXCEPTION
         WHEN NO_DATA_FOUND THEN BEGIN
            V_CDFILIALICMS60 := NULL;
            V_NRLANCTONFICMS60 := NULL;
            V_NRSEQITPRODICMS60 := NULL;
            V_QTITEMPRODENTRADA := NULL;
            V_VRUNITITEMENTRADA := NULL;
            V_VRBASECALCITENTRADA := NULL;
            V_VRIMPOSTOITENTRADA := NULL;
            V_VRBCENTRADAUNIT := NULL;
            V_VRIMPENTRADAUNIT := NULL;
            V_CDIMPOSTO := NULL;
         END;
      END;
      
      BEGIN
         SELECT IP.VRIMPOSTOIT
           INTO V_VRICMSSUBSTITUTO
           FROM ITEMPROIMPOS IP JOIN IMPOSTO I ON (I.CDIMPOSTO = IP.CDIMPOSTO)
          WHERE IP.CDFILIAL = V_CDFILIALICMS60
            AND IP.NRLANCTONF = V_NRLANCTONFICMS60
            AND IP.NRSEQITPROD = V_NRSEQITPRODICMS60
            AND I.CDIMPOSTONFE = '00'
            AND IP.NRITPROIMPOS = '1';
      EXCEPTION
         WHEN NO_DATA_FOUND THEN BEGIN
            V_VRICMSSUBSTITUTO := NULL;
         END;
      END;
      
      IF V_VRFATOCONV = 1 THEN
         V_VRBASECALCIT := (V_QTITEMPROD * V_VRUNITITEM) - V_VRDESCITEM;
         V_VRIMPOSTOIT := V_VRIMPENTRADAUNIT * V_QTITEMPROD;
      ELSE
         V_VRBASECALCIT := (V_QTITEMPROD * (V_VRBCENTRADAUNIT / V_VRFATOCONV)) - V_VRDESCITEM;
         V_VRIMPOSTOIT := (V_QTITEMPROD * (V_VRIMPENTRADAUNIT / V_VRFATOCONV));
      END IF;

      IF (V_VRBASECALCIT < 0) THEN
         V_VRBASECALCIT := 0;
      END IF;
      
      IF (V_VRIMPOSTOIT < 0) THEN
         V_VRIMPOSTOIT := 0;
      END IF;
      
      UPDATE ITEMPROIMPOS
         SET CDFILIALICMS60 = V_CDFILIALICMS60,
             NRLANCTONFICMS60 = V_NRLANCTONFICMS60,
             VRBCITENTR = ROUND((V_VRBCENTRADAUNIT * V_QTITEMPROD), 2),
             VRIMPITENTR = ROUND(V_VRIMPENTRADAUNIT * V_QTITEMPROD, 2),
             VRALIQITEM = 0,
             VRIMPOSTOIT = 0,
             VRPERREDBCIT = 0,
             VRICMSSUBSTITUTO = V_VRICMSSUBSTITUTO,
             VRALIQCONSFINAL = VRALIQITEM
       WHERE CDFILIAL = P_CDFILIAL
         AND NRLANCTONF = P_NRLANCTONF
         AND NRSEQITPROD = P_NRSEQITPROD
         AND CDIMPOSTO = V_CDIMPOSTO;
   END IF;
END;