CREATE OR REPLACE PROCEDURE IMPOSTO_ITEMPEDCOL
(
   P_EMPRESA        IN ALIQUOTA.CDEMPRESA%TYPE,
   P_INSCESTA       IN ALIQUOTA.CDINSCESTA%TYPE,
   P_FILCENT        IN ITEMPEDCOL.CDFILIAL%TYPE,
   P_LANCCOLP       IN ITEMPEDCOL.NRPEDCOLETA%TYPE,
   P_CDFORNECED     IN ITEMPEDCOL.CDFORNECED%TYPE,
   P_CDREGIAO       IN ITEMPEDCOL.CDREGIAO%TYPE,
   P_CDPRODUTO      IN ITEMPEDCOL.CDPRODUTO%TYPE,
   P_CDMARCA        IN ITEMPEDCOL.CDMARCA%TYPE,
   P_INIVALID       IN ALIQUOTA.DTINIVALID%TYPE,
   P_VRITEMPEDCOL   IN ITEMPEDCOL.VRITEMPEDCOL%TYPE,
   P_VRPERDESPEDCOL IN ITEMPEDCOL.VRPERDESPEDCOL%TYPE,
   P_TRANSACAO      IN VARCHAR2 DEFAULT 'S'
)
IS
   V_PAISORIG     VARCHAR2(04);
   V_ESTAORIG     VARCHAR2(02);
   V_MUNIORIG     VARCHAR2(04);
   V_PAISDEST     VARCHAR2(04);
   V_ESTADEST     VARCHAR2(02);
   V_ALIQTEMP     VARCHAR2(18);
   V_INCITEMP     VARCHAR2(18);
   V_TIPOOPER     VARCHAR2(02);
   V_CARACFIS     VARCHAR2(01);
   V_LIQUIITEM    NUMBER;
   V_PARDEDTMP    NUMBER;
   V_PREDBCTMP    NUMBER;
   V_ALIQUTMP     NUMBER;
   V_IMPOSTOIT    NUMBER;
   V_TAXABASECALC NUMBER;
   V_TAXAALIQUOTA NUMBER;
   V_BASECALCIT   NUMBER;
   V_CONTABCALC   NUMBER;
   V_PERDBCALC    NUMBER;
   V_ADIMPAF      NUMBER;
   V_TOTAIMPBCALC NUMBER;
   V_QTACMPITMCOL NUMBER;
   V_INSCESTA     ALIQUOTA.CDINSCESTA%TYPE;
   V_CDGRUPOFISC VARCHAR2(3);
   V_CDARVPROD    PRODUTO.CDARVPROD%TYPE;
   V_IDINTEREST   VARCHAR2(01);

   V_CDIMPOBCALTPOP IMPOSTO.CDIMPOSTO%TYPE;
   V_IDCONSDESTITEMCOL           PARASUPRI.IDCONSDESTITEMCOL%TYPE;
BEGIN

  CONSULTA_GRUPOFISCAL('F', P_CDFORNECED, '', 'E', '01', V_CDGRUPOFISC);


  V_ALIQTEMP := 'COLP'||P_FILCENT||P_LANCCOLP;
  V_INCITEMP := 'COLP'||P_FILCENT||P_LANCCOLP;
  

  SELECT MAX(CDINSCESTA) INTO V_INSCESTA
  FROM FILIAL
  WHERE
   CDFILIAL  = P_FILCENT AND
   CDEMPRESA = P_EMPRESA;

  SELECT MAX(CDARVPROD) INTO V_CDARVPROD
  FROM PRODUTO
  WHERE CDPRODUTO = P_CDPRODUTO;

  SELECT  MAX(CDPAIS), MAX(SGESTADO) INTO V_PAISDEST, V_ESTADEST
  FROM REGIAO
  WHERE (CDREGIAO = P_CDREGIAO);

  SELECT MAX(CDPAIS), MAX(SGESTADO), MAX(CDMUNICIPIO) INTO V_PAISORIG, V_ESTAORIG, V_MUNIORIG
  FROM ENDEFORN
  WHERE (CDFORNECED   = P_CDFORNECED) AND
        (IDTPENDEFORN = 'P');

  SELECT MAX(CDTIPOOPER) INTO V_TIPOOPER
  FROM PARCMPSUP
  WHERE CDFILIAL = P_FILCENT;

  IF (V_TIPOOPER IS NULL) THEN
     V_TIPOOPER := '01';
  END IF;

  SELECT MAX(IDCARACFIS) INTO V_CARACFIS
  FROM PARAMFILIAL
  WHERE CDFILIAL = P_FILCENT;

  SELECT NVL(MAX(PARASUPRI.IDCONSDESTITEMCOL), 'N')
    INTO V_IDCONSDESTITEMCOL
    FROM PARASUPRI
   WHERE PARASUPRI.CDFILIAL = P_FILCENT;
  FOR C_ITEMCOLP IN (SELECT P_VRITEMPEDCOL AS VRPRECUNIITM, P_VRPERDESPEDCOL AS VRPERDESITM
                     FROM DUAL
                     WHERE V_IDCONSDESTITEMCOL = 'N'
                     )LOOP

     IF (C_ITEMCOLP.VRPERDESITM IS NULL) THEN
        V_LIQUIITEM := C_ITEMCOLP.VRPRECUNIITM;
     ELSE
        V_LIQUIITEM := (C_ITEMCOLP.VRPRECUNIITM) * ((100 - C_ITEMCOLP.VRPERDESITM)/100);
     END IF;

     V_IDINTEREST := 'N';
     IF (V_PAISDEST = V_PAISORIG) AND (V_ESTADEST <> V_ESTAORIG) THEN
        V_IDINTEREST := 'S';
     END IF;

     CONSULTA_INCIDENCIA('E', P_EMPRESA, V_CDARVPROD, V_INSCESTA, V_TIPOOPER, 'COLP'||P_FILCENT, P_LANCCOLP, V_CDGRUPOFISC, NULL, V_IDINTEREST);


      FOR C_INCIDENCIA IN (SELECT DISTINCT
                                  CDIMPOTEMP AS CDIMPOTEMP,
                                  CDFISCTEMP,
                                  IP.IDTRATIMPO,
                                  IP.IDIMPORECUP,
                                  NVL(IMPPEDCOLETA.IDIMPDIGPEDCOL, 'N') AS IDIMPDIGCOL,
                                  IMPPEDCOLETA.VRALIQIMPPEDCOL AS VRALIQIMPCOL
                           FROM INCITEMP IT, IMPOSTO IP, IMPPEDCOLETA
                           WHERE (IT.NRINCITEMP = V_INCITEMP)    AND
                                 (IP.CDIMPOSTO  = IT.CDIMPOTEMP) AND
                                 (IP.IDPRODNOTA = 'P')        AND
                                 (IMPPEDCOLETA.CDIMPOSTO(+)   = IP.CDIMPOSTO) AND
                                 (IMPPEDCOLETA.CDFILIAL(+)    = P_FILCENT)    AND
                                 (IMPPEDCOLETA.NRPEDCOLETA(+) = P_LANCCOLP)   AND
                                 (IMPPEDCOLETA.CDFORNECED(+)  = P_CDFORNECED) AND
                                 (IMPPEDCOLETA.CDREGIAO(+)    = P_CDREGIAO)   AND
                                 (IMPPEDCOLETA.CDPRODUTO(+)   = P_CDPRODUTO)  AND
                                 (IMPPEDCOLETA.CDMARCA(+)     = P_CDMARCA)) LOOP

         CONSULTA_ALIQUOTA(C_INCIDENCIA.CDIMPOTEMP, 'E', P_EMPRESA, V_CDARVPROD, V_INSCESTA, V_PAISORIG,
                           V_ESTAORIG, V_PAISDEST, V_ESTADEST, V_MUNIORIG, V_LIQUIITEM, P_INIVALID,
                           'COLP'||P_FILCENT, P_LANCCOLP, V_CDGRUPOFISC, V_TIPOOPER);

         V_PARDEDTMP  := 0;
         V_PREDBCTMP  := 0;
         V_ALIQUTMP   := 0;
         V_IMPOSTOIT  := 0;

         SELECT MAX(VRPARDEDTMP), MAX(VRPREDBCTMP), MAX(VRALIQUTMP)
         INTO V_PARDEDTMP,V_PREDBCTMP,V_ALIQUTMP
         FROM ALIQTEMP
         WHERE (NRALIQTEMP = V_ALIQTEMP);

         IF (V_ALIQUTMP IS NULL) THEN
            V_ALIQUTMP := 0;
         ELSE
            IF C_INCIDENCIA.IDIMPDIGCOL = 'S' THEN
               V_ALIQUTMP := C_INCIDENCIA.VRALIQIMPCOL;
            END IF;
         END IF;

         IF (V_PREDBCTMP IS NULL) THEN
            V_PREDBCTMP := 0;
         END IF;

         IF (V_PARDEDTMP IS NULL) THEN
            V_PARDEDTMP := 0;
         END IF;

         V_TAXABASECALC := (100 - V_PREDBCTMP)/100;
         V_TAXAALIQUOTA := (V_ALIQUTMP/100);
         V_BASECALCIT   := V_LIQUIITEM * V_TAXABASECALC;
         V_IMPOSTOIT    := ((V_LIQUIITEM * V_TAXABASECALC) * V_TAXAALIQUOTA) - V_PARDEDTMP;

         V_CONTABCALC := V_LIQUIITEM - (V_LIQUIITEM * V_TAXABASECALC);
         V_PERDBCALC  := 100 - V_PREDBCTMP;

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

         V_ADIMPAF := 0;

         IF (C_INCIDENCIA.IDTRATIMPO = 'A') THEN
            V_ADIMPAF := V_IMPOSTOIT;
         ELSIF (C_INCIDENCIA.IDTRATIMPO = 'D') THEN
            V_ADIMPAF := - V_IMPOSTOIT;
         END IF;

         DELETE IMPPEDCOLETA
         WHERE (CDFILIAL    = P_FILCENT)    AND
               (NRPEDCOLETA = P_LANCCOLP)   AND
               (CDFORNECED  = P_CDFORNECED) AND
               (CDREGIAO    = P_CDREGIAO)   AND
               (CDPRODUTO   = P_CDPRODUTO)  AND
               (CDMARCA     = P_CDMARCA)    AND
               (CDIMPOSTO   = C_INCIDENCIA.CDIMPOTEMP);

         INSERT INTO IMPPEDCOLETA(CDFILIAL, NRPEDCOLETA, CDFORNECED, CDREGIAO,
                                  CDPRODUTO, CDMARCA, CDIMPOSTO, VRALIQIMPPEDCOL,
                                  VRPERRBCALPEDCO, VRIMPPEDCOL, VRBASECALPEDCOL,
                                  VRPARCDEDPEDCOL, CDFISCITMPEDCOL, VRADIMPPEDCOL,
                                  IDIMPDIGPEDCOL)
         VALUES(
            P_FILCENT, P_LANCCOLP, P_CDFORNECED, P_CDREGIAO, P_CDPRODUTO, P_CDMARCA,
            C_INCIDENCIA.CDIMPOTEMP, V_ALIQUTMP, V_PREDBCTMP, V_IMPOSTOIT, V_BASECALCIT,
            V_PARDEDTMP, C_INCIDENCIA.CDFISCTEMP, V_ADIMPAF, C_INCIDENCIA.IDIMPDIGCOL);
         DELETE ALIQTEMP
         WHERE NRALIQTEMP = V_ALIQTEMP;
      END LOOP;

      DELETE INCITEMP
      WHERE NRINCITEMP = V_INCITEMP;

      IF P_TRANSACAO = 'S' THEN
         COMMIT;
      END IF;
   END LOOP;

   FOR C_PRODUTO IN (SELECT  P_CDFORNECED AS CDFORNECED, P_CDREGIAO AS CDREGIAO, P_CDPRODUTO AS CDPRODUTO,
                             P_CDMARCA AS CDMARCA, II.CDIMPOSTO,
                             II.VRALIQIMPPEDCOL VRALIQIMPCOL, II.VRPERRBCALPEDCO VRPERRBCALCO,
                             II.VRIMPPEDCOL VRIMPCOLETA, II.VRPARCDEDPEDCOL VRPARCDEDCOL,
                             II.CDFISCITMPEDCOL CDFISCITMCOL, IM.IDTRATIMPO, IM.IDIMPORECUP
                     FROM IMPPEDCOLETA II, IMPOSTO IM
                     WHERE (II.CDFILIAL       = P_FILCENT)      AND
                           (II.NRPEDCOLETA    = P_LANCCOLP)     AND
                           (II.CDFORNECED     = P_CDFORNECED)   AND
                           (II.CDREGIAO       = P_CDREGIAO)     AND
                           (II.CDPRODUTO      = P_CDPRODUTO)    AND
                           (II.CDMARCA        = P_CDMARCA)      AND
                           (IM.CDIMPOSTO      = II.CDIMPOSTO)   AND
                           V_IDCONSDESTITEMCOL = 'N'            AND
                           (II.IDIMPDIGPEDCOL = 'N')) LOOP

      V_CDIMPOBCALTPOP := NULL;
      SELECT MAX(BC.CDIMPOBCALC), SUM(VRIMPPEDCOL) AS VRIMPOBCALC INTO V_CDIMPOBCALTPOP, V_TOTAIMPBCALC
      FROM  IMPPEDCOLETA II, BASECALCTPOPER BC
      WHERE (BC.IDENTRSAIDOP = 'E')
         AND (BC.CDTIPOOPER  = V_TIPOOPER)
         AND (BC.CDIMPOSTO   = C_PRODUTO.CDIMPOSTO)
         AND (II.CDFILIAL    = P_FILCENT)
         AND (II.NRPEDCOLETA = P_LANCCOLP)
         AND (II.CDFORNECED  = C_PRODUTO.CDFORNECED)
         AND (II.CDREGIAO    = C_PRODUTO.CDREGIAO)
         AND (II.CDPRODUTO   = C_PRODUTO.CDPRODUTO)
         AND (II.CDMARCA     = C_PRODUTO.CDMARCA)
         AND (II.CDIMPOSTO   = BC.CDIMPOBCALC)
         AND (II.IDIMPDIGPEDCOL = 'N')
         AND ((C_PRODUTO.CDFISCITMCOL = BC.CDFISCIMPOTPOP) OR (BC.CDFISCIMPOTPOP IS NULL))
         AND ((BC.CDEMPRESABCTP = P_EMPRESA) OR (BC.CDEMPRESABCTP IS NULL));

      IF V_CDIMPOBCALTPOP IS NULL THEN
         SELECT SUM(VRIMPPEDCOL) AS VRIMPOBCALC INTO V_TOTAIMPBCALC
         FROM  IMPPEDCOLETA II, BASECALC BC
         WHERE (BC.CDIMPOSTO      = C_PRODUTO.CDIMPOSTO)   AND
               (II.CDFILIAL       = P_FILCENT)           AND
               (II.NRPEDCOLETA    = P_LANCCOLP)            AND
               (II.CDFORNECED     = C_PRODUTO.CDFORNECED)  AND
               (II.CDREGIAO       = C_PRODUTO.CDREGIAO)    AND
               (II.CDPRODUTO      = C_PRODUTO.CDPRODUTO)   AND
               (II.CDMARCA        = C_PRODUTO.CDMARCA)     AND
               (II.CDIMPOSTO      = BC.CDIMPOBCALC)        AND
               (II.IDIMPDIGPEDCOL = 'N')                   AND
               ((C_PRODUTO.CDFISCITMCOL = BC.CDFISCIMPO) OR (BC.CDFISCIMPO IS NULL)) AND
               (BC.IDTIPOBCALC   = 'I') AND
               ((BC.CDEMPRESABC = P_EMPRESA) OR (BC.CDEMPRESABC IS NULL));
      END IF;

      IF NOT (V_TOTAIMPBCALC IS NULL) THEN
         V_TAXABASECALC := (100 - C_PRODUTO.VRPERRBCALCO)/100;
         V_TAXAALIQUOTA := (C_PRODUTO.VRALIQIMPCOL/100);

         UPDATE IMPPEDCOLETA
         SET VRBASECALPEDCOL = VRBASECALPEDCOL + (V_TOTAIMPBCALC * V_TAXABASECALC),
             VRIMPPEDCOL     = VRIMPPEDCOL  + ((V_TOTAIMPBCALC * V_TAXABASECALC) * V_TAXAALIQUOTA)
         WHERE (CDFILIAL     = P_FILCENT)             AND
               (NRPEDCOLETA  = P_LANCCOLP)            AND
               (CDFORNECED   = C_PRODUTO.CDFORNECED)  AND
               (CDREGIAO     = C_PRODUTO.CDREGIAO)    AND
               (CDPRODUTO    = C_PRODUTO.CDPRODUTO)   AND
               (CDMARCA      = C_PRODUTO.CDMARCA)     AND
               (CDIMPOSTO    = C_PRODUTO.CDIMPOSTO);
      END IF;
      IF P_TRANSACAO = 'S' THEN
         COMMIT;
      END IF;

   END LOOP;
   IF V_IDCONSDESTITEMCOL  = 'S' THEN
      PREV_IMPOSTO (P_FILCENT, P_LANCCOLP,'E', P_CDREGIAO, P_CDFORNECED, P_LANCCOLP, P_CDPRODUTO, P_CDMARCA, 'CL', P_VRITEMPEDCOL);
   END IF;
   IF P_TRANSACAO = 'S' THEN
      COMMIT;
   END IF;
END;
/
