CREATE OR REPLACE PROCEDURE REINF_R4020(
   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;
   V_NATUREND      VARCHAR2(10);
BEGIN
   FOR C_R4020 IN (SELECT DISTINCT P_INDRETIF AS INDRETIF,
                          P_NRRECIBO AS NRRECIBO,
                          TO_CHAR(TO_DATE(P_DTINICIAL),'YYYY-MM') AS PERAPUR,
                          P_IDAMBIENTE AS TPAMB,
                          '1' AS PROCEMI ,
                          '2.1.2'  AS VERPROC,
                          '1' AS TPINSC,
                          DECODE(EP.IDTPIJUREMPR,'J',SUBSTR(EP.NRINSJUREMPR,1,8), 'F', EP.NRINSJUREMPR) AS NRINSC,
                          '1' AS TPINSCESTAB,
                          FI.NRINSJURFILI AS NRINSCESTAB,
                          DECODE(EF.CDPAIS,'0055',FO.NRINSJURFORN,'') AS CNPJBENEF,
                          DECODE(EF.CDPAIS,'0055','',FO.NMRAZSOCFORN) AS NMBENEF,
                          FO.CDFORNECED, FI.NRINSJURFILI
                     FROM ENVIOEVENTOREINF EV, MAPAAPURAC MP, IMPOSTONF IP, NOTAFISCAL NF,
                          EMPRESA EP, FORNECEDOR FO, ENDEFORN EF, FILIAL FI
                    WHERE EV.NRENVIO       = P_NRENVIO
                      AND EV.CDEMPRESA     = MP.CDEMPRESA
                      AND MP.CDIMPOSTO     IN ('55','65','75','24','21')
                      AND MP.DTINIPERIODO >= P_DTINICIAL
                      AND MP.DTFIMPERIODO <= P_DTFINAL
                      AND MP.NRSEQUMAPAPU  = IP.NRSEQUMAPAPU
                      AND IP.CDFILIAL      = NF.CDFILIAL
                      AND IP.NRLANCTONF    = NF.NRLANCTONF
                      AND EV.CDESTABREINF  = NF.CDFORNECED
                      AND EV.CDEMPRESA     = EP.CDEMPRESA
                      AND EV.CDESTABREINF  = FO.CDFORNECED
                      AND FO.CDFORNECED    = EF.CDFORNECED
                      AND EF.IDTPENDEFORN  = 'P'
                      AND NF.CDFILIRECE    = FI.CDFILIAL
                      AND EV.NRINSJURFILI  = 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, CDCAMPO11, CDCAMPO12,
           CDCAMPO13, NRSEQREINF)
       VALUES
          (P_CDEMPRESA, P_NREVENTO, P_NRENVIO, C_R4020.INDRETIF, C_R4020.NRRECIBO,
           C_R4020.PERAPUR, C_R4020.TPAMB, C_R4020.PROCEMI, C_R4020.VERPROC, C_R4020.TPINSC,
           C_R4020.NRINSC, C_R4020.TPINSCESTAB, C_R4020.NRINSCESTAB, C_R4020.CNPJBENEF, C_R4020.NMBENEF,
           C_R4020.CDFORNECED, V_NRSEQREINF);

       V_NATUREND := NULL;

       FOR C_NATUREND IN (SELECT NVL(NATREND,'00000') AS NATREND, DTFG, SUM(VLRBRUTO) AS VLRBRUTO, PAISRESIDEXT, SUM(VLRBASEIR)  AS VLRBASEIR,
                                 SUM(VLRIR) AS VLRIR, SUM(VLRBASEAGREG) AS VLRBASEAGREG, SUM(VLRAGREG) AS VLRAGREG,
                                 SUM(VLRBASECSLL) AS VLRBASECSLL, SUM(VLRCSLL) AS VLRCSLL, SUM(VLRBASECOFINS) AS VLRBASECOFINS,
                                 SUM(VLRCOFINS) AS VLRCOFINS, SUM(VLRBASEPP) AS VLRBASEPP, SUM(VLRPP) AS VLRPP,
                                 DSCLOGRAD, NRLOGRAD, COMPLEM, BAIRRO, CIDADE, ESTADO, CODPOSTAL, TELEF
                            FROM (SELECT PS.CDNATUREND AS NATREND,
                                         RETORNA_DATA_FATOGERADOR(NF.CDFILIAL, NF.NRLANCTONF, NF.IDENTRSAIDOP, P_DTINICIAL, P_DTFINAL, IM.CDIMPOSTO) AS DTFG,
                                         NF.VRNOTAFISC AS VLRBRUTO,
                                         DECODE(EF.CDPAIS,'0055','',P.CDPAISEXPFIS) AS PAISRESIDEXT,
                                         SUM(DECODE(IM.CDIMPOSTONFE,'21',II.VRBASECALCIT,0))  AS VLRBASEIR,
                                         SUM(DECODE(IM.CDIMPOSTONFE,'21',II.VRIMPOSTOIT,0)) AS VLRIR,
                                         SUM(DECODE(IM.CDIMPOSTONFE,'24',DECODE(IM.IDTPIMPFILHO,'00',II.VRBASECALCIT,0),0)) AS VLRBASEAGREG,
                                         SUM(DECODE(IM.CDIMPOSTONFE,'24',DECODE(IM.IDTPIMPFILHO,'00',II.VRIMPOSTOIT,0),0)) AS VLRAGREG,
                                         SUM(NVL(DECODE(IM.CDIMPOSTONFE,'55',II.VRBASECALCIT,0),0) + NVL(DECODE(IM.CDIMPOSTONFE,'24',DECODE(IM.IDTPIMPFILHO,'03',II.VRBASECALCIT,0),0),0)) AS VLRBASECSLL,
                                         SUM(NVL(DECODE(IM.CDIMPOSTONFE,'55',II.VRIMPOSTOIT, 0),0) + NVL(DECODE(IM.CDIMPOSTONFE,'24',DECODE(IM.IDTPIMPFILHO,'03',II.VRIMPOSTOIT, 0),0),0)) AS VLRCSLL,
                                         SUM(NVL(DECODE(IM.CDIMPOSTONFE,'55',II.VRBASECALCIT,0),0) + NVL(DECODE(IM.CDIMPOSTONFE,'24',DECODE(IM.IDTPIMPFILHO,'02',II.VRBASECALCIT,0),0),0)) AS VLRBASECOFINS,
                                         SUM(NVL(DECODE(IM.CDIMPOSTONFE,'55',II.VRIMPOSTOIT, 0),0) + NVL(DECODE(IM.CDIMPOSTONFE,'24',DECODE(IM.IDTPIMPFILHO,'02',II.VRIMPOSTOIT, 0),0),0)) AS VLRCOFINS,
                                         SUM(NVL(DECODE(IM.CDIMPOSTONFE,'55',II.VRBASECALCIT,0),0) + NVL(DECODE(IM.CDIMPOSTONFE,'24',DECODE(IM.IDTPIMPFILHO,'01',II.VRBASECALCIT,0),0),0)) AS VLRBASEPP,
                                         SUM(NVL(DECODE(IM.CDIMPOSTONFE,'55',II.VRIMPOSTOIT, 0),0) + NVL(DECODE(IM.CDIMPOSTONFE,'24',DECODE(IM.IDTPIMPFILHO,'01',II.VRIMPOSTOIT, 0),0),0)) AS VLRPP,
                                         DECODE(EF.CDPAIS,'0055','',NVL(EF.DSENDEFORNSEP,EF.DSENDEFORN)) AS DSCLOGRAD,
                                         DECODE(EF.CDPAIS,'0055','',EF.NRENDEFORNSEP) AS NRLOGRAD,
                                         DECODE(EF.CDPAIS,'0055','',EF.DSCOMPENDFOR) AS COMPLEM,
                                         DECODE(EF.CDPAIS,'0055','',EF.NMBAIRFORN) AS BAIRRO,
                                         DECODE(EF.CDPAIS,'0055','',MU.NMMUNICIPIO) AS CIDADE,
                                         DECODE(EF.CDPAIS,'0055','',ES.NMESTADO) AS ESTADO,
                                         DECODE(EF.CDPAIS,'0055','',REPLACE(REPLACE(REPLACE(EF.NRCEPFORN,'.'),'-'),' ')) AS CODPOSTAL,
                                         DECODE(EF.CDPAIS,'0055','',REPLACE(REPLACE(REPLACE(REPLACE(EF.NRTELEFORN,')'),'('),'-'),' ')) AS TELEF,
                                         NF.CDFILIAL, NF.NRLANCTONF
                                    FROM ENVIOEVENTOREINF EV, NOTAFISCAL NF, ITEMPROIMPOS II, ITEMPRODUTO IT, PARAMMUNISERV PS,
                                         FILIAL FI, ENDEFORN EF, PAIS P, ESTADO ES, MUNICIPIO MU, IMPOSTO IM,
                                         (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF, I.CDIMPOSTO
                                            FROM MAPAAPURAC M, IMPOSTONF I
                                           WHERE M.CDEMPRESA     = P_CDEMPRESA
                                             AND M.CDIMPOSTO     IN ('55','65','75','24','21')
                                             AND M.DTINIPERIODO >= P_DTINICIAL
                                             AND M.DTFIMPERIODO <= P_DTFINAL
                                             AND M.NRSEQUMAPAPU  = I.NRSEQUMAPAPU) IP
                                   WHERE EV.NRENVIO         = P_NRENVIO
                                     AND EV.CDEMPRESA       = P_CDEMPRESA
                                     AND IP.CDFILIAL        = NF.CDFILIAL
                                     AND IP.NRLANCTONF      = NF.NRLANCTONF
                                     AND EV.CDESTABREINF    = NF.CDFORNECED
                                     AND NF.CDFORNECED      = C_R4020.CDFORNECED
                                     AND EF.CDFORNECED      = NF.CDFORNECED
                                     AND EF.IDTPENDEFORN    = 'P'
                                     AND NF.CDFILIRECE      = FI.CDFILIAL
                                     AND FI.NRINSJURFILI    = C_R4020.NRINSJURFILI
                                     AND EF.CDPAIS          = P.CDPAIS
                                     AND EF.CDPAIS          = ES.CDPAIS
                                     AND EF.SGESTADO        = ES.SGESTADO
                                     AND EF.CDPAIS          = MU.CDPAIS
                                     AND EF.SGESTADO        = MU.SGESTADO
                                     AND EF.CDMUNICIPIO     = MU.CDMUNICIPIO
                                     AND IP.CDFILIAL        = II.CDFILIAL
                                     AND IP.NRLANCTONF      = II.NRLANCTONF
                                     AND IP.CDIMPOSTO       = II.CDIMPOSTO
                                     AND II.CDIMPOSTO       = IM.CDIMPOSTO
                                     AND II.CDFILIAL        = IT.CDFILIAL
                                     AND II.NRLANCTONF      = IT.NRLANCTONF
                                     AND II.NRSEQITPROD     = IT.NRSEQITPROD
                                     AND II.CDFISCALIT      = '1'
                                     AND PS.CDEMPRESA(+)    = NF.CDEMPRESA
                                     AND PS.CDINSCMUNI(+)   = NF.CDINSCMUNI
                                     AND PS.IDDECLARACAO(+) = 'RE'
                                     AND PS.IDENTRSAIDOP(+) IN ('E','T')
                                     AND PS.CDPRODSERV(+)   = IT.CDPRODUTO
                                   GROUP BY PS.CDNATUREND,
                                         RETORNA_DATA_FATOGERADOR(NF.CDFILIAL, NF.NRLANCTONF, NF.IDENTRSAIDOP, P_DTINICIAL, P_DTFINAL, IM.CDIMPOSTO),
                                         NF.VRNOTAFISC,
                                         DECODE(EF.CDPAIS,'0055','',P.CDPAISEXPFIS),
                                         DECODE(EF.CDPAIS,'0055','',NVL(EF.DSENDEFORNSEP,EF.DSENDEFORN)),
                                         DECODE(EF.CDPAIS,'0055','',EF.NRENDEFORNSEP),
                                         DECODE(EF.CDPAIS,'0055','',EF.DSCOMPENDFOR),
                                         DECODE(EF.CDPAIS,'0055','',EF.NMBAIRFORN),
                                         DECODE(EF.CDPAIS,'0055','',MU.NMMUNICIPIO),
                                         DECODE(EF.CDPAIS,'0055','',ES.NMESTADO),
                                         DECODE(EF.CDPAIS,'0055','',REPLACE(REPLACE(REPLACE(EF.NRCEPFORN,'.'),'-'),' ')),
                                         DECODE(EF.CDPAIS,'0055','',REPLACE(REPLACE(REPLACE(REPLACE(EF.NRTELEFORN,')'),'('),'-'),' ')) ,
                                         NF.CDFILIAL, NF.NRLANCTONF)
                           GROUP BY NATREND, DTFG, PAISRESIDEXT, DSCLOGRAD, NRLOGRAD, COMPLEM, BAIRRO, CIDADE,  ESTADO,  CODPOSTAL,  TELEF
                           ORDER BY NATREND) LOOP

          IF (V_NATUREND IS NULL) OR (V_NATUREND <> C_NATUREND.NATREND AND C_NATUREND.NATREND  IS NOT NULL) THEN
             NOVO_CODIGO_NT('NRSEQREINF', V_NRSEQREINF);
             INSERT INTO ARQEFD_REINF
                (CDEMPRESA, NREVENTO, NRENVIO, CDCAMPO1, CDCAMPO2, NRSEQREINF)
             VALUES
                (P_CDEMPRESA, P_NREVENTO, P_NRENVIO, C_NATUREND.NATREND, C_R4020.CDFORNECED, V_NRSEQREINF);
             V_NATUREND := C_NATUREND.NATREND;
          END IF;

          NOVO_CODIGO_NT('NRSEQREINF', V_NRSEQREINF);
          INSERT INTO ARQEFD_REINF
             (CDEMPRESA, NREVENTO, NRENVIO, DTCAMPO1, VRCAMPO1,
              CDCAMPO2, CDCAMPO1, VRCAMPO2, VRCAMPO3, VRCAMPO4,
              VRCAMPO5, VRCAMPO6, VRCAMPO7, VRCAMPO8, VRCAMPO9,
              VRCAMPO10, VRCAMPO11, CDCAMPO3, CDCAMPO4, CDCAMPO5,
              CDCAMPO6, CDCAMPO7, CDCAMPO8, CDCAMPO9, CDCAMPO10,
              NRSEQREINF)
          VALUES
             (P_CDEMPRESA, P_NREVENTO, P_NRENVIO, C_NATUREND.DTFG, C_NATUREND.VLRBRUTO,
              C_NATUREND.NATREND, C_NATUREND.PAISRESIDEXT, C_NATUREND.VLRBASEIR, C_NATUREND.VLRIR, C_NATUREND.VLRBASEAGREG,
              C_NATUREND.VLRAGREG, C_NATUREND.VLRBASECSLL, C_NATUREND.VLRCSLL, C_NATUREND.VLRBASECOFINS, C_NATUREND.VLRCOFINS,
              C_NATUREND.VLRBASEPP, C_NATUREND.VLRPP, C_NATUREND.DSCLOGRAD, C_NATUREND.NRLOGRAD, C_NATUREND.COMPLEM,
              C_NATUREND.BAIRRO, C_NATUREND.CIDADE, C_NATUREND.ESTADO, C_NATUREND.CODPOSTAL, C_NATUREND.TELEF,
              V_NRSEQREINF);

       END LOOP;
   END LOOP;
END;
/