CREATE OR REPLACE PROCEDURE ENTSAI_CREDANTST_RJ(
   PDATADE IN DATE,
   PDATAATE IN DATE,
   PCDRATPADSTRJFIL IN RATEIOPADRAOFIS.CDRATEIOPADRAO%TYPE,
   PCDRATPADSTRJCFOPE IN RATEIOPADRAOFIS.CDRATEIOPADRAO%TYPE,
   PCDRATPADSTRJTPEN IN RATEIOPADRAOFIS.CDRATEIOPADRAO%TYPE,
   PCDRATPADSTRJNCMS IN RATEIOPADRAOFIS.CDRATEIOPADRAO%TYPE,
   PCDRATPADSTRJCFOPS IN RATEIOPADRAOFIS.CDRATEIOPADRAO%TYPE) AS
BEGIN

   EXECUTE IMMEDIATE 'TRUNCATE TABLE TMP_CREDSTRJ_SAIDA';

   INSERT INTO TMP_CREDSTRJ_SAIDA
      (CONSIDERA,
      INSCR_ESTADUAL,
      FILIAL_EMITENTE,
      FILIAL_DESTINO,
      NOME_FILIAL,
      ESTADO,
      NUM_NOTA,
      CNPJ,
      EMISSAO,
      DATA_SAIDA,
      TIPO_OPERACAO,
      OPERACAO,
      DESCR_OPERACAO,
      IMPOSTO,
      INCIDENCIA,
      CFOP,
      CST,
      FAMILIA,
      SUBFAMILIA,
      PRODUTO,
      DESCRICAO,
      EMBALAGEM,
      QTDE,
      VALOR_PRODUTO,
      BASE_CALCULO,
      VALOR_IMPOSTO,
      ALIQUOTA,
      BASE_CALCULO_ST,
      VALOR_IMPOSTO_ST,
      ALIQUOTA_ST,
      VALOR_NF,
      CHAVE_NFE,
      NCM)
      SELECT DISTINCT
             'N' AS CONSIDERA,
             '*' || NF.CDINSCESTA AS INSCR_ESTADUAL,
             NF.CDFILIEMIT AS FILIAL_EMITENTE,
             NF.CDFILIRECE AS FILIAL_DESTINO, F.NMFILIAL    AS NOME_FILIAL, ENDF.SGESTADO AS ESTADO,
             NF.NRNOTAFISC AS NUM_NOTA, '*' || NF.NRINSJURNF AS CNPJ, NF.DTEMISSAO AS EMISSAO, NF.DTENTRSAID AS DATA_SAIDA,
             NF.IDENTRSAIDOP AS TIPO_OPERACAO, NF.CDTIPOOPER AS OPERACAO, T.NMTIPOOPER AS DESCR_OPERACAO,
             IT.CDIMPOSTO AS IMPOSTO, IT.CDFISCALIT AS INCIDENCIA, IT.CDFISCOPERIT AS CFOP, IT.CDSITUTRIBIT AS CST,
             P1.CDARVPROD || ' - ' || P1.NMPRODUTO AS FAMILIA, P2.CDARVPROD || ' - ' || P2.NMPRODUTO AS SUBFAMILIA, P.CDARVPROD AS PRODUTO, P.NMPRODUTO AS DESCRICAO,
             P.VRQTDEMB || ' ' ||  P.SGUNIDEMB AS EMBALAGEM, IP.QTITEMPROD AS QTDE,
             IT.VRCONTITIMP AS VALOR_PRODUTO,
             CASE IT.CDFISCALIT WHEN '1' THEN IT.VRBASECALCIT ELSE 0 END AS BASE_CALCULO,
             CASE IT.CDFISCALIT WHEN '1' THEN IT.VRIMPOSTOIT ELSE  0 END AS VALOR_IMPOSTO,
             CASE IT.CDFISCALIT WHEN '1' THEN IT.VRALIQITEM ELSE 0 END AS ALIQUOTA,
             CASE IT1.CDFISCALIT WHEN '1' THEN IT1.VRBASECALCIT ELSE 0 END AS BASE_CALCULO_ST,
             CASE IT1.CDFISCALIT WHEN '1' THEN IT1.VRIMPOSTOIT ELSE  0 END AS VALOR_IMPOSTO_ST,
             CASE IT1.CDFISCALIT WHEN '1' THEN IT1.VRALIQITEM ELSE 0 END AS ALIQUOTA_ST,
             NF.VRNOTAFISC AS VALOR_NF, '*' || NF.NRACESSONFE AS CHAVE_NFE,
             LTRIM(TO_CHAR(P.CDCLASFISC,'00000000')) AS NCM
        FROM NOTAFISCAL NF, ITEMPRODUTO IP,
            (SELECT CDFILIAL, NRLANCTONF, NRSEQITPROD, CDIMPOSTO, '1' AS CDFISCALIT, CDSITUTRIBIT, CDFISCOPERIT,
                    SUM(VRCONTITIMP) AS VRCONTITIMP,
                    SUM(CASE CDFISCALIT WHEN '1' THEN VRBASECALCIT ELSE 0 END) AS VRBASECALCIT,
                    SUM(CASE CDFISCALIT WHEN '1' THEN VRIMPOSTOIT ELSE  0 END) AS VRIMPOSTOIT,
                    SUM(CASE CDFISCALIT WHEN '1' THEN VRALIQITEM ELSE 0 END) AS VRALIQITEM
               FROM ITEMPROIMPOS
              GROUP BY CDFILIAL, NRLANCTONF, NRSEQITPROD, CDIMPOSTO, CDSITUTRIBIT, CDFISCOPERIT) IT,
             (SELECT * FROM ITEMPROIMPOS WHERE CDIMPOSTO = '88') IT1,
             FILIAL F, ENDEFILI ENDF,
             PRODUTO P, PRODUTO P1, PRODUTO P2,
             TIPOOPERAC T,
             (SELECT CDPARTICIPANTE AS CDFILIAL
                 FROM RATFISPARTICIP
                WHERE IDTPPART = 'L'
                  AND CDRATEIOPADRAO = PCDRATPADSTRJFIL) PART
       WHERE NF.CDFILIAL          = IP.CDFILIAL
         AND NF.NRLANCTONF        = IP.NRLANCTONF
         AND IP.CDFILIAL          = IT.CDFILIAL
         AND IP.NRLANCTONF        = IT.NRLANCTONF
         AND IP.NRSEQITPROD       = IT.NRSEQITPROD
         AND IP.CDFILIAL          = IT1.CDFILIAL     (+)
         AND IP.NRLANCTONF        = IT1.NRLANCTONF   (+)
         AND IP.NRSEQITPROD       = IT1.NRSEQITPROD  (+)
         AND NF.CDFILIRECE        = F.CDFILIAL
         AND F.CDFILIAL           = ENDF.CDFILIAL
         AND IP.CDPRODUTO         = P.CDPRODUTO
         AND SUBSTR(IP.CDPRODUTO, 1, 3) = P1.CDPRODUTO
         AND SUBSTR(IP.CDPRODUTO, 1, 5) = P2.CDPRODUTO
         AND ENDF.IDTPENDEFILI     = 'P'
         AND ENDF.SGESTADO        NOT IN ('RJ')
         AND NF.CDTIPOOPER        = T.CDTIPOOPER
         AND NF.IDENTRSAIDOP      = T.IDENTRSAIDOP
         AND NF.CDFILIEMIT        = PART.CDFILIAL
         AND NF.IDENTRSAIDOP       = 'S'
         AND IT.CDIMPOSTO         IN ('00')
         AND NF.DTEMISSAO BETWEEN PDATADE AND PDATAATE;
   COMMIT;

   FOR X IN (SELECT S.ROWID AS LINHA
               FROM TMP_CREDSTRJ_SAIDA S,

               (SELECT SUBSTR(CDCLASFISC,1,6) AS CDCLASFISC
                 FROM RATFISNCM
                WHERE IDENTRSAIDOP = 'S'
                  AND CDRATEIOPADRAO = PCDRATPADSTRJNCMS) I

              WHERE S.ESTADO = 'RJ'
                AND SUBSTR(S.NCM, 1, 6) = I.CDCLASFISC) LOOP

      UPDATE TMP_CREDSTRJ_SAIDA
         SET CONSIDERA = 'S'
       WHERE ROWID = X.LINHA;

   END LOOP;

   COMMIT;

   FOR X IN (SELECT S.ROWID AS LINHA
               FROM TMP_CREDSTRJ_SAIDA S,

                  (SELECT SUBSTR(CDCLASFISC,1,8) AS CDCLASFISC
                 FROM RATFISNCM
                WHERE IDENTRSAIDOP = 'S'
                  AND CDRATEIOPADRAO = PCDRATPADSTRJNCMS) I
                  
              WHERE S.ESTADO = 'RJ'
                AND SUBSTR(S.NCM, 1, 8) = I.CDCLASFISC) LOOP

       UPDATE TMP_CREDSTRJ_SAIDA
          SET CONSIDERA = 'S'
        WHERE ROWID = X.LINHA;

   END LOOP;
   
   COMMIT;

   UPDATE TMP_CREDSTRJ_SAIDA
      SET CONSIDERA = 'S'
    WHERE ESTADO <> 'RJ';

   UPDATE TMP_CREDSTRJ_SAIDA
      SET CONSIDERA = 'N'
    WHERE CFOP IN (SELECT CDCFOPPADRAO
                     FROM RATFISCFOPPADRAO
                    WHERE IDENTRSAIDOP = 'S'
                      AND CDRATEIOPADRAO = PCDRATPADSTRJCFOPS);

   COMMIT;

   DELETE TMP_CREDSTRJ_ENTRADA;

   INSERT INTO TMP_CREDSTRJ_ENTRADA
      (CONSIDERA,
      INSCRICAO,
      CNPJ,
      RAZAO_SOCIAL,
      ESTADO,
      NUMERO_NF,
      EMISSAO,
      SERIE,
      MODELO,
      ENTRADA,
      FILIAL_RECE,
      NOME_FILIAL,
      TIPO,
      OPERACAO,
      DESCR_OPERACAO,
      NCM,
      PRODUTO,
      DESCR_PRODUTO,
      QTDE,
      VALOR_CONTABIL,
      BASE,
      IMPOSTO,
      ALIQUOTA,
      CFOP,
      CST,
      CHAVE,
      SALDO_QTDE)
      SELECT DISTINCT
         'N' AS CONSIDERA,
         '*'||NF.CDINSCESTA AS INSCRICAO,
         '*'||NF.NRINSJURNF AS CNPJ,
         F.NMRAZSOCFORN AS RAZAO_SOCIAL,
         EF.SGESTADO AS ESTADO,
         TO_CHAR(NF.NRNOTAFISC, '000000000') AS NUMERO_NF,
         NF.DTEMISSAO AS EMISSAO,
         NF.CDSERIE AS SERIE,
         NF.CDMODDOCFISC AS MODELO,
         NF.DTENTRSAID AS ENTRADA,
         NF.CDFILIRECE AS FILIAL_RECE,
         FL.NMFILIAL AS NOME_FILIAL,
         NF.IDENTRSAIDOP  AS TIPO,
         NF.CDTIPOOPER AS OPERACAO,
         T.NMTIPOOPER AS DESCR_OPERACAO,
         LTRIM(TO_CHAR(P.CDCLASFISC, '00000000')) AS NCM,
         IP.CDPRODUTO AS PRODUTO,
         IP.DSPRODEMIS AS DESCR_PRODUTO,
         IP.QTITEMPROD AS QTDE,
         IT.VRCONTITIMP AS VALOR_CONTABIL,
         CASE WHEN IT.CDFISCALIT  = '1' OR IT.CDFISCOPERIT IN (SELECT CDCFOPPADRAO
                                                                 FROM RATFISCFOPPADRAO
                                                                WHERE IDENTRSAIDOP = 'E'
                                                                  AND CDRATEIOPADRAO = PCDRATPADSTRJCFOPE) THEN IT.VRBASECALCIT ELSE 0 END AS BASE,
         CASE IT.CDFISCALIT WHEN '1' THEN IT.VRIMPOSTOIT ELSE 0 END AS IMPOSTO,
         CASE IT.CDFISCALIT WHEN '1' THEN IT.VRALIQITEM ELSE 0 END AS ALIQUOTA,
         IT.CDFISCOPERIT AS CFOP,
         IT.CDSITUTRIBIT AS CST,
         '*' || NF.NRACESSONFE AS CHAVE,
         IP.QTITEMPROD AS SALDO_QTDE
      FROM
         NOTAFISCAL     NF,
         ITEMPRODUTO    IP,
         ITEMPROIMPOS   IT,
         FORNECEDOR F,
         ENDEFORN EF,
         PRODUTO P,
         FILIAL FL,
         TIPOOPERAC T,
         (SELECT CDPARTICIPANTE AS CDFILIAL
            FROM RATFISPARTICIP
           WHERE IDTPPART = 'L'
             AND CDRATEIOPADRAO = PCDRATPADSTRJFIL) PART
      WHERE NF.CDFILIAL          = IP.CDFILIAL
        AND NF.NRLANCTONF        = IP.NRLANCTONF
        AND IP.CDFILIAL          = IT.CDFILIAL
        AND IP.NRLANCTONF        = IT.NRLANCTONF
        AND IP.NRSEQITPROD       = IT.NRSEQITPROD
        AND IP.CDPRODUTO         = P.CDPRODUTO
        AND NF.NRINSJURNF        = F.NRINSJURFORN
        AND NF.CDFILIRECE        = FL.CDFILIAL
        AND NF.CDTIPOOPER        = T.CDTIPOOPER
        AND NF.IDENTRSAIDOP      = T.IDENTRSAIDOP
        AND NF.CDFORNECED        = EF.CDFORNECED
        AND EF.IDTPENDEFORN      = 'P'
        AND NF.DTENTRSAID BETWEEN LAST_DAY(ADD_MONTHS(PDATADE, -3)) + 1 AND PDATAATE
        AND NF.IDSTATUS IN ('A','E')
        AND NF.CDFILIRECE = PART.CDFILIAL
        AND IT.NRITPROIMPOS <> '3'
        AND IT.CDIMPOSTO IN ('00')
        AND NF.IDENTRSAIDOP = 'E'
        AND NF.CDTIPOOPER IN (SELECT CDTIPOOPER
                                FROM RATFISTPOPERAC
                               WHERE IDENTRSAIDOP = 'E'
                                 AND CDRATEIOPADRAO = PCDRATPADSTRJTPEN)
        AND EXISTS (SELECT 1
                      FROM TMP_CREDSTRJ_SAIDA RF11
                     WHERE RF11.PRODUTO = IP.CDPRODUTO);
   COMMIT;

   RESUL_CREDANTST_RJ;

END;
/