CREATE OR REPLACE PROCEDURE CONSIDERA_DIFAL_ICMS(
   P_CDFILIAL        IN VARCHAR2,
   P_NRLANCTONF      IN VARCHAR2,
   P_NRSEQITPROD     IN VARCHAR2,
   P_CDGRUPOFISC     IN VARCHAR2,
   P_CDPAISDEST      IN VARCHAR2,
   P_SGESTADODEST    IN VARCHAR2,
   P_CDMUNICIPIODEST IN VARCHAR2,
   P_VRMAJORACAOBC   IN FLOAT,
   P_VRREDUCAOBC     IN FLOAT,
   P_VRBASECALCIT    OUT FLOAT,
   P_VRIMPOSTOIT     OUT FLOAT,
   P_VRADICFECP      OUT FLOAT)
AS
   V_CDEMPRESA VARCHAR2(02);
   V_CDINSCESTA VARCHAR2(16);
   V_DTENTRSAID DATE;
   V_IDENTRSAIDOP VARCHAR2(01);
   V_CDTIPOOPER VARCHAR2(02);
   V_CDARVPROD VARCHAR2(20);
   V_QTITEMPROD FLOAT;
   V_VRUNITITEM FLOAT;
   V_VRTOTAITEM FLOAT;
   V_VRDESCITEM FLOAT;
   V_IDORIGEMPROD VARCHAR2(01);
   V_CDIMPOSTO VARCHAR2(02);
   V_VRALIQINTERESTADUAL FLOAT;
   V_VRALIQFECPINTERESTADUAL FLOAT;
   V_CDIMPOSTRIB VARCHAR2(02);
   V_VRLIQUIITEM FLOAT;
   V_ALIQTEMP VARCHAR2(14);
   V_VRALIQINTERNA FLOAT;
   V_VRALIQFECPINTERNA FLOAT;
   V_VRBASECALCOPERACAO FLOAT;
   V_VRSOMAIMPOSTOBC FLOAT;
   V_VRSOMAENCARGOBC FLOAT;
   V_VRIMPOSTOINTERESTADUAL FLOAT;
   V_VRFECPINTERESTADUAL FLOAT;
   V_VRCUSTOOPERACAO FLOAT;
   V_VRBASECALCDIFEALIQ FLOAT;
   V_VRIMPODIFEALIQ FLOAT;
   V_VRFECPDIFEALIQ FLOAT;
BEGIN
   BEGIN
      SELECT CDEMPRESA,
             CDINSCESTA,
             DTENTRSAID,
             IDENTRSAIDOP,
             CDTIPOOPER
        INTO V_CDEMPRESA,
             V_CDINSCESTA,
             V_DTENTRSAID,
             V_IDENTRSAIDOP,
             V_CDTIPOOPER
        FROM NOTAFISCAL
       WHERE CDFILIAL = P_CDFILIAL
         AND NRLANCTONF = P_NRLANCTONF;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN BEGIN
         V_CDEMPRESA := NULL;
         V_CDINSCESTA := NULL;
         V_DTENTRSAID := NULL;
         V_IDENTRSAIDOP := NULL;
         V_CDTIPOOPER := NULL;
      END;
   END;
   
   BEGIN
      SELECT P.CDARVPROD, 
             I.QTITEMPROD,
             I.VRUNITITEM,
             I.VRTOTAITEM,
             I.VRDESCITEM,
             I.IDORIGEMPROD
        INTO V_CDARVPROD,
             V_QTITEMPROD,
             V_VRUNITITEM,
             V_VRTOTAITEM,
             V_VRDESCITEM,
             V_IDORIGEMPROD
        FROM ITEMPRODUTO I JOIN PRODUTO P ON (I.CDPRODUTO = P.CDPRODUTO)
       WHERE I.CDFILIAL = P_CDFILIAL
         AND I.NRLANCTONF = P_NRLANCTONF
         AND I.NRSEQITPROD = P_NRSEQITPROD;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN BEGIN
         V_CDARVPROD := NULL;
         V_QTITEMPROD := 0;
         V_VRUNITITEM := 0;
         V_VRTOTAITEM := 0;
         V_VRDESCITEM := 0;
         V_IDORIGEMPROD := NULL;
      END;
   END;

   BEGIN
      SELECT II.CDIMPOSTO, 
             NVL(II.VRALIQITEM, 0),
             NVL(II.VRADICALIQFECP, 0),
             I.CDIMPOSTRIB
        INTO V_CDIMPOSTO, 
             V_VRALIQINTERESTADUAL,
             V_VRALIQFECPINTERESTADUAL,
             V_CDIMPOSTRIB
        FROM ITEMPROIMPOS II JOIN IMPOSTO I ON (II.CDIMPOSTO = I.CDIMPOSTO)
       WHERE (II.CDFILIAL = P_CDFILIAL)
         AND (II.NRLANCTONF = P_NRLANCTONF)
         AND (II.NRSEQITPROD = P_NRSEQITPROD)
         AND (II.NRITPROIMPOS = '1')
         AND (I.CDIMPOSTONFE = '00');
   EXCEPTION
      WHEN NO_DATA_FOUND THEN BEGIN
         V_CDIMPOSTO := NULL;
         V_VRALIQINTERESTADUAL := 0;
         V_VRALIQFECPINTERESTADUAL := 0;
         V_CDIMPOSTRIB := NULL;
      END;
   END;
   
   V_VRSOMAIMPOSTOBC := 0;

   V_VRSOMAENCARGOBC := 0;

   V_VRLIQUIITEM := ROUND(ROUND((V_QTITEMPROD * V_VRUNITITEM),2) - V_VRDESCITEM, 2);

   V_ALIQTEMP := P_CDFILIAL||P_NRLANCTONF;
   
   DELETE ALIQTEMP
    WHERE NRALIQTEMP = V_ALIQTEMP;

   CONSULTA_ALIQUOTA(V_CDIMPOSTO, 
                     'S',
                     V_CDEMPRESA,
                     V_CDARVPROD,
                     V_CDINSCESTA,
                     P_CDPAISDEST,
                     P_SGESTADODEST,
                     P_CDPAISDEST,
                     P_SGESTADODEST,
                     P_CDMUNICIPIODEST,
                     V_VRLIQUIITEM,
                     V_DTENTRSAID,
                     P_CDFILIAL,
                     P_NRLANCTONF,
                     P_CDGRUPOFISC,
                     V_CDTIPOOPER,
                     NULL,
                     NULL,
                     NULL,
                     NULL,
                     NULL,
                     NULL,
                     V_IDORIGEMPROD);

   BEGIN
      SELECT VRALIQUTMP,
             NVL(VRADICALIQFECP, 0)
        INTO V_VRALIQINTERNA,
             V_VRALIQFECPINTERNA
        FROM ALIQTEMP
       WHERE NRALIQTEMP = V_ALIQTEMP;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN BEGIN
         V_VRALIQINTERNA := 0;
         V_VRALIQFECPINTERNA := 0;
      END;
   END;

   IF ((V_VRALIQINTERNA IS NULL) OR (V_VRALIQINTERNA < V_VRALIQINTERESTADUAL)) THEN
      BEGIN
         SELECT NVL(CONSULTA_QUADRO_ALIQUOTAS(P_SGESTADODEST, P_SGESTADODEST), 0)
           INTO V_VRALIQINTERNA
           FROM DUAL;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_VRALIQINTERNA := 0;
      END;
   END IF;

   -- SOMATORIORIO DOS OUTROS IMPOSTOS POR TIPO DE OPERACAO
   BEGIN
      SELECT NVL(SUM(CASE NVL(B.IDTRATIMPOBCTPOP, M.IDTRATIMPO) WHEN 'D' THEN I.VRIMPOSTOIT * (- 1) ELSE I.VRIMPOSTOIT END), 0)
        INTO V_VRSOMAIMPOSTOBC
        FROM ITEMPROIMPOS I JOIN BASECALCTPOPER B ON (I.CDIMPOSTO = B.CDIMPOBCALC)
                            JOIN IMPOSTO M ON (M.CDIMPOSTO = B.CDIMPOBCALC)
                            JOIN ITEMPRODUTO IT ON (I.CDFILIAL = IT.CDFILIAL)
                                               AND (I.NRLANCTONF = IT.NRLANCTONF)
                                               AND (I.NRSEQITPROD = IT.NRSEQITPROD)
       WHERE B.CDIMPOSTO = V_CDIMPOSTRIB
         AND B.IDENTRSAIDOP = V_IDENTRSAIDOP
         AND B.CDTIPOOPER = V_CDTIPOOPER
         AND I.CDFILIAL = P_CDFILIAL
         AND I.NRLANCTONF = P_NRLANCTONF
         AND I.NRSEQITPROD = P_NRSEQITPROD
         AND ((SUBSTR(IT.CDSITUTRIBPR, 2, 2) = B.CDCSTIMPOTPOP) OR (B.CDCSTIMPOTPOP IS NULL))
         AND ((SUBSTR(IT.CDFISOPEITEM, 2, 4) = B.CDCFOPIMPOTPOP) OR (B.CDCFOPIMPOTPOP IS NULL))
         AND ((I.CDFISCALIT = B.CDFISCIMPOTPOP) OR (B.CDFISCIMPOTPOP IS NULL));
   EXCEPTION
      WHEN NO_DATA_FOUND THEN BEGIN
         V_VRSOMAIMPOSTOBC := 0;
      END;
   END;

   IF V_VRSOMAIMPOSTOBC = 0 THEN
      -- SOMATORIORIO DOS OUTROS IMPOSTOS PADROES
      BEGIN
         SELECT NVL(SUM(CASE NVL(B.IDTRATIMPOBC, M.IDTRATIMPO) WHEN 'D' THEN I.VRIMPOSTOIT * (- 1) ELSE I.VRIMPOSTOIT END), 0)
           INTO V_VRSOMAIMPOSTOBC
           FROM ITEMPROIMPOS I JOIN BASECALC B ON (I.CDIMPOSTO = B.CDIMPOBCALC)
                               JOIN IMPOSTO M ON (M.CDIMPOSTO = B.CDIMPOBCALC)
                               JOIN ITEMPRODUTO IT ON (I.CDFILIAL = IT.CDFILIAL)
                                                  AND (I.NRLANCTONF = IT.NRLANCTONF)
                                                  AND (I.NRSEQITPROD = IT.NRSEQITPROD)
          WHERE B.CDIMPOSTO = V_CDIMPOSTRIB
            AND I.CDFILIAL = P_CDFILIAL
            AND I.NRLANCTONF = P_NRLANCTONF
            AND I.NRSEQITPROD = P_NRSEQITPROD
            AND B.IDTIPOBCALC = 'I'
            AND ((SUBSTR(IT.CDSITUTRIBPR, 2, 2) = B.CDCSTIMPO) OR (B.CDCSTIMPO IS NULL))
            AND ((SUBSTR(IT.CDFISOPEITEM, 2, 4) = B.CDCFOPIMPO) OR (B.CDCFOPIMPO IS NULL))
            AND ((I.CDFISCALIT = B.CDFISCIMPO) OR (B.CDFISCIMPO IS NULL));
      EXCEPTION
         WHEN NO_DATA_FOUND THEN BEGIN
            V_VRSOMAIMPOSTOBC := 0;
         END;
      END;
   END IF;

   -- SOMATORIORIO DOS ENCARGOS
   BEGIN
      SELECT NVL(SUM(CASE E.IDENCARGO WHEN 'D' THEN 
                        (((I.VRITEMENCA / A.VRSOMATOTALITEM) * V_VRLIQUIITEM) * (- 1)) 
                     ELSE 
                        ((I.VRITEMENCA / A.VRSOMATOTALITEM) * V_VRLIQUIITEM) 
                     END), 0)
        INTO V_VRSOMAENCARGOBC
        FROM ITEMENCARGO I JOIN BASECALC B ON (I.CDENCARGO = B.CDENCARGO)
                           JOIN ENCARGO E ON (E.CDENCARGO = B.CDENCARGO),
             (SELECT SUM(ROUND(ROUND((IP.QTITEMPROD * IP.VRUNITITEM), 2) - IP.VRDESCITEM, 2)) AS VRSOMATOTALITEM
                FROM ITEMPRODUTO IP
               WHERE IP.CDFILIAL = P_CDFILIAL
                 AND IP.NRLANCTONF = P_NRLANCTONF) A
       WHERE B.CDIMPOSTO = V_CDIMPOSTRIB
         AND I.CDFILIAL = P_CDFILIAL
         AND I.NRLANCTONF = P_NRLANCTONF
         AND B.IDTIPOBCALC = 'E'
         AND E.IDENCARGO IN ('A', 'D');
   EXCEPTION
      WHEN NO_DATA_FOUND THEN BEGIN
         V_VRSOMAENCARGOBC := 0;
      END;
   END;
   
   V_VRBASECALCOPERACAO := V_VRLIQUIITEM + V_VRSOMAIMPOSTOBC + V_VRSOMAENCARGOBC;
   
   V_VRBASECALCOPERACAO := V_VRBASECALCOPERACAO * (1 + (P_VRMAJORACAOBC / 100));
   
   V_VRBASECALCOPERACAO := V_VRBASECALCOPERACAO - (V_VRBASECALCOPERACAO * (P_VRREDUCAOBC / 100));
   
   V_VRIMPOSTOINTERESTADUAL := V_VRBASECALCOPERACAO * (V_VRALIQINTERESTADUAL / 100);
   
   V_VRFECPINTERESTADUAL := V_VRBASECALCOPERACAO * (V_VRALIQFECPINTERESTADUAL / 100);
   
   V_VRCUSTOOPERACAO := V_VRBASECALCOPERACAO - V_VRIMPOSTOINTERESTADUAL - V_VRFECPINTERESTADUAL;

   V_VRBASECALCDIFEALIQ := V_VRCUSTOOPERACAO / (1 - ((V_VRALIQINTERNA + V_VRALIQFECPINTERNA) / 100));

   V_VRIMPODIFEALIQ := (V_VRBASECALCDIFEALIQ * (V_VRALIQINTERNA / 100)) - V_VRIMPOSTOINTERESTADUAL;

   V_VRFECPDIFEALIQ := (V_VRBASECALCDIFEALIQ * (V_VRALIQFECPINTERNA / 100)) - V_VRFECPINTERESTADUAL;
   
   IF (V_VRBASECALCDIFEALIQ < 0) THEN
      V_VRBASECALCDIFEALIQ := 0;
   END IF;

   IF (V_VRIMPODIFEALIQ < 0) THEN
      V_VRIMPODIFEALIQ := 0;
   END IF;

   IF (V_VRFECPDIFEALIQ < 0) THEN
      V_VRFECPDIFEALIQ := 0;
   END IF;

   P_VRBASECALCIT := ROUND(V_VRBASECALCDIFEALIQ, 2);
   
   P_VRIMPOSTOIT := ROUND(V_VRIMPODIFEALIQ, 2);
   
   P_VRADICFECP := ROUND(V_VRFECPDIFEALIQ, 2);

   DELETE ALIQTEMP
    WHERE NRALIQTEMP = V_ALIQTEMP;
END;
/