CREATE OR REPLACE PROCEDURE REINF_R2040(
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_DTINICIAL    IN NOTAFISCAL.DTEMISSAO%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTEMISSAO%TYPE,
   P_NRENVIO      IN NUMBER,
   P_NREVENTO     IN NUMBER,
   P_NRRECIBO     IN VARCHAR2,
   P_INDRETIF     IN VARCHAR2,
   P_IDAMBIENTE   IN VARCHAR2)
AS
   V_NRSEQREINF    NUMBER;
BEGIN
   FOR C_R2040 IN (SELECT P_INDRETIF AS INDRETIF,                                                                   --INDICADOR DA ESCRITURACAO
                          P_NRRECIBO AS NRRECIBO,                                                                   --NRRECIBO CASO FOR RETIFICACAO
                          TO_CHAR(TO_DATE(P_DTINICIAL),'YYYY-MM')  AS PERAPUR,
                          P_IDAMBIENTE AS TMPAMB,
                          '1' AS PROCEMI ,
                          '1.5.1'  AS VERPROC,
                          DECODE(EP.IDTPIJUREMPR,'J','1','F','2','') AS TPINSC,
                          DECODE(EP.IDTPIJUREMPR,'J',SUBSTR(EP.NRINSJUREMPR,1,8), 'F', EP.NRINSJUREMPR)  AS NRINSC, --SE FOR PESSOA JURIDICA PASSA APENAS A RAIZ DO CNPJ
                          DECODE(FI.IDTPIJURFILI,'J','1','F','3','') AS TPINSCESTAB,                                --TIPO DA INSCRICAO DO ESTABELECIMENTO DO CONTRIBUINTE
                          REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.'),'/'),'-') AS NRINSCESTAB,                    --INSCRICAO DO ESTABELECIMENTO  DO CONTRIBUINTE
                          MAX(NF.CDFILIRECE) AS CDFILIRECE
                     FROM NOTAFISCAL NF, CONTAPAGAR CP, BOLETPAGAR BP, BAIXAPAGA BX, FORNECEDOR FO,
                          ENVIOEVENTOREINF EV, EMPRESA EP, FILIAL FI
                    WHERE NF.CDFILIAL     = CP.CDFILIAL
                      AND NF.NRLANCTONF   = CP.NRLANCTONF
                      AND CP.CDFILIBPAG   = BP.CDFILIAL
                      AND CP.NRLANBOLPAG  = BP.NRLANBOLPAG
                      AND BP.CDFILIBAIPAG = BX.CDFILIAL
                      AND BP.NRBAIXAPAG   = BX.NRBAIXAPAG
                      AND BX.DTBAIXAPAG   BETWEEN P_DTINICIAL AND P_DTFINAL
                      AND NF.IDSISTEMA    = 'PAG'
                      AND NF.IDENTRSAIDOP = 'E'
                      AND FO.IDASSOCDESP  = 'S'
                      AND NF.CDFORNECED   = FO.CDFORNECED
                      AND NF.CDEMPRESA    = P_CDEMPRESA
                      AND EV.NRENVIO      = P_NRENVIO
                      AND EV.CDESTABREINF = NF.CDFILIRECE
                      AND NF.CDEMPRESA    = EP.CDEMPRESA
                      AND NF.CDFILIRECE   = FI.CDFILIAL
                    GROUP BY P_INDRETIF, P_NRRECIBO, TO_CHAR(TO_DATE(P_DTINICIAL),'YYYY-MM'), P_IDAMBIENTE, '1', '1.5.1',
                             DECODE(EP.IDTPIJUREMPR,'J','1','F','2',''),
                             DECODE(EP.IDTPIJUREMPR,'J',SUBSTR(EP.NRINSJUREMPR,1,8), 'F', EP.NRINSJUREMPR),
                             DECODE(FI.IDTPIJURFILI,'J','1','F','3',''),
                             REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.'),'/'),'-')) LOOP

      NOVO_CODIGO_NT('NRSEQREINF', V_NRSEQREINF);
      INSERT INTO ARQEFD_REINF
         (CDEMPRESA, NREVENTO, NRENVIO, CDCAMPO1, CDCAMPO2,
          CDCAMPO3, CDCAMPO4, CDCAMPO5, CDCAMPO6, CDCAMPO7,
          CDCAMPO8, CDCAMPO9, CDCAMPO10, CDCAMPO12, NRSEQREINF)
      VALUES
         (P_CDEMPRESA, P_NREVENTO, P_NRENVIO, C_R2040.INDRETIF, C_R2040.NRRECIBO,
          C_R2040.PERAPUR, C_R2040.TMPAMB, C_R2040.PROCEMI, C_R2040.VERPROC, C_R2040.TPINSC,
          C_R2040.NRINSC, C_R2040.TPINSCESTAB, C_R2040.NRINSCESTAB, C_R2040.CDFILIRECE, V_NRSEQREINF);

      /*<recursosRep>*/
      FOR C_RECURSOSREP IN (SELECT REPLACE(REPLACE(REPLACE(FO.NRINSJURFORN,'.'),'/'),'-') AS CNPJASSOCDESP,                  --INSCRICAO DA ASSOCIACAO DESPORTIVA PARA QUAL FOI FEITO O REPASSE
                                   SUM(NVL(CP.VRORIGPAG,0)) AS VLRTOTALREP,                                                  --VALOR BRUTO DOS RECURSOS REPASSADOS
                                   SUM(ROUND(NVL(CP.VRORIGPAG,0)*NVL(FO.VRALIQRETDESP,0)/100,2)) AS VLRTOTALRET,             --VALOR TOTAL DA RETENCAO EFETUADA SOBRE OS RECURSOS RECEBIDOS
                                   SUM(ROUND(NVL(CP.VRORIGPAG,0)*NVL(FO.VRALIQNRETDESP,0)/100,2)) AS VLRTOTALNRET,           --VALOR DA RETENCAO QUE DEIXOU DE SER FEITA
                                   NF.CDFORNECED
                              FROM NOTAFISCAL NF, CONTAPAGAR CP, BOLETPAGAR BP, BAIXAPAGA BX, FORNECEDOR FO,
                                   ENVIOEVENTOREINF EV, EMPRESA EP, FILIAL FI
                             WHERE NF.CDFILIAL     = CP.CDFILIAL
                               AND NF.NRLANCTONF   = CP.NRLANCTONF
                               AND CP.CDFILIBPAG   = BP.CDFILIAL
                               AND CP.NRLANBOLPAG  = BP.NRLANBOLPAG
                               AND BP.CDFILIBAIPAG = BX.CDFILIAL
                               AND BP.NRBAIXAPAG   = BX.NRBAIXAPAG
                               AND BX.DTBAIXAPAG   BETWEEN P_DTINICIAL AND P_DTFINAL
                               AND NF.IDSISTEMA    = 'PAG'
                               AND NF.IDENTRSAIDOP = 'E'
                               AND FO.IDASSOCDESP  = 'S'
                               AND NF.CDFORNECED   = FO.CDFORNECED
                               AND NF.CDEMPRESA    = P_CDEMPRESA
                               AND EV.NRENVIO      = P_NRENVIO
                               AND NF.CDFILIRECE   = C_R2040.CDFILIRECE
                               AND EV.CDESTABREINF = NF.CDFILIRECE
                               AND NF.CDEMPRESA    = EP.CDEMPRESA
                               AND NF.CDFILIRECE   = FI.CDFILIAL
                             GROUP BY FO.NRINSJURFORN, NF.CDFORNECED) LOOP

         NOVO_CODIGO_NT('NRSEQREINF', V_NRSEQREINF);
         INSERT INTO ARQEFD_REINF
             (CDEMPRESA, NREVENTO, NRENVIO, CDCAMPO1, CDCAMPO2,
              CDCAMPO3, CDCAMPO4, CDCAMPO5, VRCAMPO1,
              VRCAMPO2, VRCAMPO3, CDCAMPO6, CDCAMPO13, NRSEQREINF)
         VALUES
             (P_CDEMPRESA, P_NREVENTO, P_NRENVIO, C_R2040.INDRETIF, C_R2040.NRRECIBO,
              C_R2040.PERAPUR, C_R2040.TMPAMB, C_RECURSOSREP.CNPJASSOCDESP, C_RECURSOSREP.VLRTOTALREP,
              C_RECURSOSREP.VLRTOTALRET, C_RECURSOSREP.VLRTOTALNRET, C_RECURSOSREP.CDFORNECED, C_R2040.CDFILIRECE, V_NRSEQREINF);

         /*<infoRecurso>*/
         FOR C_INFORECURSO IN (SELECT NF.IDTPREPASSEDESP AS TPREPASSE,                                     --TIPO DE REPASSE
                                      NF.DSRECURSODESP AS DESCRECURSO,                                     --DESCRICAO RESUMIDA DO RECURSO REPASSADO
                                      SUM(NVL(CP.VRORIGPAG,0)) AS VLRBRUTO,                                --VALOR BRUTO DOS RECURSOS DO TIPO DE REPASSE
                                      SUM(ROUND(NVL(CP.VRORIGPAG,0)*NVL(FO.VRALIQRETDESP,0)/100,2)) AS VLRRETAPUR,   --VALOR TOTAL DA RETENCAO EFETUADA SOBRE OS RECURSOS RECEBIDOS
                                      PR.TPPROCESSO AS TPPROC,                                             --TIPO DE PROCESSO: 1 - ADMINISTRATIVO; 2 - JUDICIAL
                                      PR.NRPROCESSO AS NRPROC,                                             --NUMERO DO PROCESSO
                                      SUM(ROUND(NVL(CP.VRORIGPAG,0)*NVL(FO.VRALIQNRETDESP,0)/100,2)) AS VLRNRET,     --VALOR DA RETENCAO QUE DEIXOU DE SER FEITA
                                      NF.CDFORNECED, NF.CDFILIAL, NF.NRLANCTONF
                                 FROM NOTAFISCAL NF, CONTAPAGAR CP, BOLETPAGAR BP, BAIXAPAGA BX, FORNECEDOR FO, PROCESSOFIS PR
                                WHERE NF.CDFILIAL      = CP.CDFILIAL
                                  AND NF.NRLANCTONF    = CP.NRLANCTONF
                                  AND CP.CDFILIBPAG    = BP.CDFILIAL
                                  AND CP.NRLANBOLPAG   = BP.NRLANBOLPAG
                                  AND BP.CDFILIBAIPAG  = BX.CDFILIAL
                                  AND BP.NRBAIXAPAG    = BX.NRBAIXAPAG
                                  AND BX.DTBAIXAPAG    BETWEEN P_DTINICIAL AND P_DTFINAL
                                  AND NF.IDSISTEMA     = 'PAG'
                                  AND NF.IDENTRSAIDOP  = 'E'
                                  AND NF.CDEMPRESA     = P_CDEMPRESA
                                  AND NF.CDFILIRECE    = C_R2040.CDFILIRECE
                                  AND NF.CDFORNECED    = C_RECURSOSREP.CDFORNECED
                                  AND NF.CDFORNECED    = FO.CDFORNECED
                                  AND FO.CDPROCESSO    = PR.CDPROCESSO(+)
                                  GROUP BY NF.IDTPREPASSEDESP, NF.DSRECURSODESP, NF.CDFORNECED, PR.TPPROCESSO, PR.NRPROCESSO, NF.CDFILIAL, NF.NRLANCTONF
                                ) LOOP

            NOVO_CODIGO_NT('NRSEQREINF', V_NRSEQREINF);
            INSERT INTO ARQEFD_REINF
               (CDEMPRESA, NREVENTO, NRENVIO, CDCAMPO1, CDCAMPO2,
                CDCAMPO3, CDCAMPO4, CDCAMPO5, CDCAMPO6,
                VRCAMPO1, VRCAMPO2,  CDCAMPO7, CDCAMPO8,
                VRCAMPO3, CDCAMPO9, CDCAMPO14, NRSEQREINF)
            VALUES
               (P_CDEMPRESA, P_NREVENTO, P_NRENVIO, C_R2040.INDRETIF, C_R2040.NRRECIBO,
                C_R2040.PERAPUR, C_R2040.TMPAMB, C_INFORECURSO.TPREPASSE, C_INFORECURSO.DESCRECURSO,
                C_INFORECURSO.VLRBRUTO, C_INFORECURSO.VLRRETAPUR, C_INFORECURSO.TPPROC, C_INFORECURSO.NRPROC,
                C_INFORECURSO.VLRNRET, C_INFORECURSO.CDFORNECED, C_R2040.CDFILIRECE, V_NRSEQREINF);
         END LOOP;
      END LOOP;
   END LOOP;
END;
/