CREATE OR REPLACE PROCEDURE CONSULTA_FISCAL_PROD(
    P_EMPRESA       IN INCIDENCIA.CDEMPRESA%TYPE,
    P_ENTRSAID      IN INCIDENCIA.IDENTRSAIDIN%TYPE,
    P_TIPOOPER      IN INCIDENCIA.CDTIPOOPER%TYPE,
    P_INSCESTA      IN INCIDENCIA.CDINSCESTA%TYPE,
    P_CDARVPROD     IN PRODUTO.CDARVPROD%TYPE,
    P_INIVALID      IN ALIQUOTA.DTINIVALID%TYPE,
    P_DTENTRFIS     IN ALIQUOTA.DTINIVALID%TYPE,
    P_IMPOSTO       IN ALIQUOTA.CDIMPOSTO%TYPE,
    P_PAISORIG      IN PAIS.CDPAIS%TYPE,
    P_ESTAORIG      IN ESTADO.SGESTADO%TYPE,
    P_PAISDEST      IN PAIS.CDPAIS%TYPE,
    P_ESTADEST      IN ESTADO.SGESTADO%TYPE,
    P_CDMUNICIPIO   IN MUNICIPIO.CDMUNICIPIO%TYPE,
    P_FILIAL        IN VARCHAR2,
    P_LANCTONF      IN NOTAFISCAL.NRLANCTONF%TYPE,
    P_CDORIGDESTNF  IN VARCHAR2 DEFAULT NULL,
    P_CDFILIORIG    IN VARCHAR2 DEFAULT NULL,
    P_CDFILIDEST    IN VARCHAR2 DEFAULT NULL
)
IS
   V_IDORIGDESTNF VARCHAR2(01);
   V_CDGRUPOFISC  VARCHAR2(03);
   V_IDINTEREST   VARCHAR2(01);
   V_IDINTERNA    VARCHAR2(01);
   V_CDFORNECED   VARCHAR2(21);
   V_CDCLIENTE    VARCHAR2(21);
   V_INCITEMP     VARCHAR2(14);
   V_CDIMPOTEMP   VARCHAR2(02);
   V_CDFISCTEMP   VARCHAR2(01);
   V_EXISTE       INTEGER;
   V_CDGRUPOFISCAUX VARCHAR2(03);
   V_SITUTRIBPR   VARCHAR2(03);
   V_CDSITTRIBTMP    CODSITUTRIBNFE.CDSITUTRIBNFE%TYPE;
   V_IDMODBCNFE      CODSITUTRIBNFE.IDMODBCNFE%TYPE;
   V_IDMOTIDESSTNFE  CODSITUTRIBNFE.IDMOTIDESSTNFE%TYPE;
   V_IDMODBCSTNFE    CODSITUTRIBNFE.IDMODBCSTNFE%TYPE;
   V_CDTPCRPICOSTNFE CODSITUTRIBNFE.CDTPCRPICOSTNFE%TYPE;
   V_CDNATUBCPCSTNFE CODSITUTRIBNFE.CDNATUBCPCSTNFE%TYPE;
   V_FISOPEITEM     VARCHAR2(11);
   V_SUFXCFOPPR     VARCHAR2(03);
   V_PARDEDTMP    NUMBER;
   V_PREDBCTMP    NUMBER;
   V_ALIQUTMP     NUMBER;
   V_TRATIMPST      VARCHAR2(01);
   V_PBCSUBTRIB   NUMBER;
   V_IDPMCBASECAL   VARCHAR2(01);
   V_CDREGRAITALIQ    VARCHAR2(10);
BEGIN
   DELETE FROM PARAMFISCALPROD
    WHERE CDFILIOPELOG = P_CDFILIORIG
      AND CDFILDEST = P_CDFILIDEST
      AND DTERFISCALOL = P_DTENTRFIS
      AND DTEMBRFISCOL = P_INIVALID
      AND CDARVPROD = P_CDARVPROD
      AND CDIMPOSTO = P_IMPOSTO
      AND IDENTRSAIDOP = P_ENTRSAID
      AND CDTIPOOPER = P_TIPOOPER;

   V_INCITEMP := P_FILIAL||P_LANCTONF;/* INCIDENCIA */
   SELECT IDORIGDESTOP
     INTO V_IDORIGDESTNF
     FROM TIPOOPERAC
    WHERE IDENTRSAIDOP = P_ENTRSAID
      AND CDTIPOOPER   = P_TIPOOPER;

   CONSULTA_GRUPOFISCAL(V_IDORIGDESTNF, P_CDORIGDESTNF, P_CDFILIORIG, P_ENTRSAID, P_TIPOOPER, V_CDGRUPOFISC);

   V_IDINTEREST := 'N';
   V_IDINTERNA  := 'N';
   IF ((P_PAISDEST = P_PAISORIG) AND (P_ESTADEST <> P_ESTAORIG)) THEN
      V_IDINTEREST := 'S';
   ELSIF (P_PAISDEST <> P_PAISORIG) THEN
      V_IDINTERNA := 'S';
   END IF;

   V_CDCLIENTE  := NULL;
   V_CDFORNECED := NULL;
   IF V_IDORIGDESTNF = 'C' THEN
      V_CDCLIENTE := P_CDORIGDESTNF;
   ELSIF V_IDORIGDESTNF = 'F' THEN
      V_CDFORNECED := P_CDORIGDESTNF;
   END IF;

   CONSULTA_INCIDENCIA(P_ENTRSAID, P_EMPRESA, P_CDARVPROD, P_INSCESTA, P_TIPOOPER, P_FILIAL, P_LANCTONF, V_CDGRUPOFISC, null, V_IDINTEREST, 'PED', P_CDFILIDEST, V_CDFORNECED, V_CDCLIENTE, P_INIVALID, P_CDFILIORIG);

   SELECT MAX(G.CDGRUPOFISC)
     INTO V_CDGRUPOFISCAUX
     FROM GRUPOFISCIMPFIL G
    WHERE G.CDFILIAL = P_CDFILIORIG
      AND NOT G.CDGRUPOFISC IS NULL
      AND G.CDIMPOSTO = P_IMPOSTO;

   IF V_CDGRUPOFISCAUX IS NOT NULL THEN
      V_CDGRUPOFISC := V_CDGRUPOFISCAUX;
   END IF;

   SELECT MAX(CDIMPOTEMP), MAX(CDFISCTEMP)
     INTO V_CDIMPOTEMP, V_CDFISCTEMP
     FROM INCITEMP IT
    WHERE (IT.NRINCITEMP = V_INCITEMP)
      AND (IT.CDIMPOTEMP = P_IMPOSTO)
   ORDER BY CDIMPOTEMP;

   V_EXISTE := 0;
   SELECT COUNT(1)
     INTO V_EXISTE
     FROM PARAMFISCALPROD
    WHERE CDFILIOPELOG = P_CDFILIORIG
      AND CDFILDEST = P_CDFILIDEST
      AND DTERFISCALOL = P_DTENTRFIS
      AND DTEMBRFISCOL = P_INIVALID
      AND CDARVPROD = P_CDARVPROD
      AND CDIMPOSTO = P_IMPOSTO
      AND IDENTRSAIDOP = P_ENTRSAID
      AND CDTIPOOPER = P_TIPOOPER;

   IF V_EXISTE = 0 THEN
      IF V_CDIMPOTEMP IS NULL THEN
         INSERT INTO PARAMFISCALPROD
            (CDFILIOPELOG, CDFILDEST, DTERFISCALOL, DTEMBRFISCOL, CDARVPROD, CDIMPOSTO,
             IDENTRSAIDOP, CDTIPOOPER, CDFISCAL, CDGRUPOFISC, CDPAISOL, SGESTADOOL,
             CDPAISDEST, SGESTADODEST)
         VALUES
            (P_CDFILIORIG, P_CDFILIDEST, P_DTENTRFIS, P_INIVALID, P_CDARVPROD, P_IMPOSTO,
             P_ENTRSAID, P_TIPOOPER,'', V_CDGRUPOFISC, P_PAISORIG, P_ESTAORIG,
             P_PAISDEST, P_ESTADEST);
      ELSE
         INSERT INTO PARAMFISCALPROD
            (CDFILIOPELOG, CDFILDEST, DTERFISCALOL, DTEMBRFISCOL, CDARVPROD, CDIMPOSTO,
             IDENTRSAIDOP, CDTIPOOPER, CDFISCAL, CDGRUPOFISC, CDPAISOL, SGESTADOOL,
             CDPAISDEST, SGESTADODEST)
         VALUES
            (P_CDFILIORIG, P_CDFILIDEST, P_DTENTRFIS, P_INIVALID, P_CDARVPROD, P_IMPOSTO,
             P_ENTRSAID, P_TIPOOPER,V_CDFISCTEMP, V_CDGRUPOFISC, P_PAISORIG, P_ESTAORIG,
             P_PAISDEST, P_ESTADEST);
      END IF;

      CONSULTA_CST(P_ENTRSAID, P_EMPRESA, P_CDARVPROD, P_INSCESTA, P_TIPOOPER, P_FILIAL, P_LANCTONF, V_CDGRUPOFISC, V_IDINTEREST, V_IDINTERNA, 'PED');
      SELECT MAX(CDSITTRIBTMP)
        INTO V_SITUTRIBPR
        FROM CSTTEMP
       WHERE NRCSTTEMP = V_INCITEMP;

      IF V_SITUTRIBPR IS NULL THEN
          UPDATE PARAMFISCALPROD
             SET CDSITUTRIB = ''
          WHERE CDFILIOPELOG = P_CDFILIORIG
            AND CDFILDEST = P_CDFILIDEST
            AND DTERFISCALOL = P_DTENTRFIS
            AND DTEMBRFISCOL = P_INIVALID
            AND CDARVPROD = P_CDARVPROD
            AND CDIMPOSTO = P_IMPOSTO
            AND IDENTRSAIDOP = P_ENTRSAID
            AND CDTIPOOPER = P_TIPOOPER
            AND CDGRUPOFISC = V_CDGRUPOFISC;
      ELSE
         UPDATE PARAMFISCALPROD
             SET CDSITUTRIB = V_SITUTRIBPR
          WHERE CDFILIOPELOG = P_CDFILIORIG
            AND CDFILDEST = P_CDFILIDEST
            AND DTERFISCALOL = P_DTENTRFIS
            AND DTEMBRFISCOL = P_INIVALID
            AND CDARVPROD = P_CDARVPROD
            AND CDIMPOSTO = P_IMPOSTO
            AND IDENTRSAIDOP = P_ENTRSAID
            AND CDTIPOOPER = P_TIPOOPER;
      END IF;

      CONSULTA_CST_NFE(P_ENTRSAID, P_EMPRESA, P_CDARVPROD, P_INSCESTA, P_TIPOOPER, V_CDGRUPOFISC, P_IMPOSTO, V_IDINTEREST, V_IDINTERNA, P_FILIAL, P_LANCTONF, '',  V_CDSITTRIBTMP, V_IDMODBCNFE, V_IDMODBCSTNFE, V_CDTPCRPICOSTNFE, V_CDNATUBCPCSTNFE, V_IDMOTIDESSTNFE);

      IF V_CDSITTRIBTMP IS NULL THEN
          UPDATE PARAMFISCALPROD
             SET CDSITUTRIBNFE = ''
          WHERE CDFILIOPELOG = P_CDFILIORIG
            AND CDFILDEST = P_CDFILIDEST
            AND DTERFISCALOL = P_DTENTRFIS
            AND DTEMBRFISCOL = P_INIVALID
            AND CDARVPROD = P_CDARVPROD
            AND CDIMPOSTO = P_IMPOSTO
            AND IDENTRSAIDOP = P_ENTRSAID
            AND CDTIPOOPER = P_TIPOOPER
            AND CDGRUPOFISC = V_CDGRUPOFISC;
      ELSE
         UPDATE PARAMFISCALPROD
             SET CDSITUTRIBNFE = V_CDSITTRIBTMP
          WHERE CDFILIOPELOG = P_CDFILIORIG
            AND CDFILDEST = P_CDFILIDEST
            AND DTERFISCALOL = P_DTENTRFIS
            AND DTEMBRFISCOL = P_INIVALID
            AND CDARVPROD = P_CDARVPROD
            AND CDIMPOSTO = P_IMPOSTO
            AND IDENTRSAIDOP = P_ENTRSAID
            AND CDTIPOOPER = P_TIPOOPER;
      END IF;

      CONSULTA_CFOP(P_ENTRSAID, P_EMPRESA, P_CDARVPROD, P_INSCESTA, P_TIPOOPER, P_PAISORIG, P_ESTAORIG,
                    P_PAISDEST, P_ESTADEST, P_FILIAL, P_LANCTONF, V_CDGRUPOFISC, P_INIVALID);

      SELECT MAX(CDFISOPETMP), MAX(CDSUFCFOPTMP)
        INTO   V_FISOPEITEM    , V_SUFXCFOPPR
        FROM CFOPTEMP
       WHERE NRCFOPTEMP = V_INCITEMP;

       IF V_FISOPEITEM IS NULL THEN
          UPDATE PARAMFISCALPROD
             SET CDCFOP = ''
          WHERE CDFILIOPELOG = P_CDFILIORIG
            AND CDFILDEST = P_CDFILIDEST
            AND DTERFISCALOL = P_DTENTRFIS
            AND DTEMBRFISCOL = P_INIVALID
            AND CDARVPROD = P_CDARVPROD
            AND CDIMPOSTO = P_IMPOSTO
            AND IDENTRSAIDOP = P_ENTRSAID
            AND CDTIPOOPER = P_TIPOOPER
            AND CDGRUPOFISC = V_CDGRUPOFISC;
      ELSE
         UPDATE PARAMFISCALPROD
             SET CDCFOP = V_FISOPEITEM
          WHERE CDFILIOPELOG = P_CDFILIORIG
            AND CDFILDEST = P_CDFILIDEST
            AND DTERFISCALOL = P_DTENTRFIS
            AND DTEMBRFISCOL = P_INIVALID
            AND CDARVPROD = P_CDARVPROD
            AND CDIMPOSTO = P_IMPOSTO
            AND IDENTRSAIDOP = P_ENTRSAID
            AND CDTIPOOPER = P_TIPOOPER;
      END IF;

      CONSULTA_ALIQUOTA(P_IMPOSTO, P_ENTRSAID, P_EMPRESA, P_CDARVPROD, P_INSCESTA, P_PAISORIG, P_ESTAORIG,
                        P_PAISDEST, P_ESTADEST, P_CDMUNICIPIO, 0, P_INIVALID, P_FILIAL, P_LANCTONF, V_CDGRUPOFISC,P_TIPOOPER,
                        'PED', 'N', 0, P_CDFILIDEST, V_CDFORNECED, V_CDCLIENTE);

      SELECT    NVL(MAX(VRPARDEDTMP),0), NVL(MAX(VRPREDBCTMP),0),      NVL(MAX(VRALIQUTMP),0),
                NVL(MAX(IDTRIMPSTTMP), '0'), NVL(MAX(VRPBCSTTMP),0),  NVL(MAX(IDPMCBSCALTMP), 'S'), MAX(CDREGRATMP)
        INTO   V_PARDEDTMP,             V_PREDBCTMP,                  V_ALIQUTMP,
               V_TRATIMPST,             V_PBCSUBTRIB,                 V_IDPMCBASECAL,               V_CDREGRAITALIQ
        FROM ALIQTEMP
       WHERE (NRALIQTEMP = V_INCITEMP);

      IF (V_ALIQUTMP IS NULL)  THEN
          UPDATE PARAMFISCALPROD
             SET VRALIQUOTA = ''
          WHERE CDFILIOPELOG = P_CDFILIORIG
            AND CDFILDEST = P_CDFILIDEST
            AND DTERFISCALOL = P_DTENTRFIS
            AND DTEMBRFISCOL = P_INIVALID
            AND CDARVPROD = P_CDARVPROD
            AND CDIMPOSTO = P_IMPOSTO
            AND IDENTRSAIDOP = P_ENTRSAID
            AND CDTIPOOPER = P_TIPOOPER
            AND CDGRUPOFISC = V_CDGRUPOFISC;
      ELSE
         UPDATE PARAMFISCALPROD
             SET VRALIQUOTA = V_ALIQUTMP
          WHERE CDFILIOPELOG = P_CDFILIORIG
            AND CDFILDEST = P_CDFILIDEST
            AND DTERFISCALOL = P_DTENTRFIS
            AND DTEMBRFISCOL = P_INIVALID
            AND CDARVPROD = P_CDARVPROD
            AND CDIMPOSTO = P_IMPOSTO
            AND IDENTRSAIDOP = P_ENTRSAID
            AND CDTIPOOPER = P_TIPOOPER;
      END IF;
      IF (V_PREDBCTMP IS NULL)  THEN
          UPDATE PARAMFISCALPROD
             SET VRREDUCAOBC = ''
          WHERE CDFILIOPELOG = P_CDFILIORIG
            AND CDFILDEST = P_CDFILIDEST
            AND DTERFISCALOL = P_DTENTRFIS
            AND DTEMBRFISCOL = P_INIVALID
            AND CDARVPROD = P_CDARVPROD
            AND CDIMPOSTO = P_IMPOSTO
            AND IDENTRSAIDOP = P_ENTRSAID
            AND CDTIPOOPER = P_TIPOOPER
            AND CDGRUPOFISC = V_CDGRUPOFISC;
      ELSE
         UPDATE PARAMFISCALPROD
             SET VRREDUCAOBC = V_PREDBCTMP
          WHERE CDFILIOPELOG = P_CDFILIORIG
            AND CDFILDEST = P_CDFILIDEST
            AND DTERFISCALOL = P_DTENTRFIS
            AND DTEMBRFISCOL = P_INIVALID
            AND CDARVPROD = P_CDARVPROD
            AND CDIMPOSTO = P_IMPOSTO
            AND IDENTRSAIDOP = P_ENTRSAID
            AND CDTIPOOPER = P_TIPOOPER;
      END IF;
   END IF;

   DELETE INCITEMP
    WHERE NRINCITEMP = V_INCITEMP;

   DELETE CSTTEMP
    WHERE NRCSTTEMP = V_INCITEMP;

   DELETE CFOPTEMP
    WHERE NRCFOPTEMP = V_INCITEMP;

   DELETE ALIQTEMP
    WHERE NRALIQTEMP = V_INCITEMP;
END;
/