CREATE OR REPLACE PROCEDURE REINF_R4010(
   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_R4010 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 CPFBENEF,
                          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_R4010.INDRETIF, C_R4010.NRRECIBO,
           C_R4010.PERAPUR, C_R4010.TPAMB, C_R4010.PROCEMI, C_R4010.VERPROC, C_R4010.TPINSC,
           C_R4010.NRINSC, C_R4010.TPINSCESTAB, C_R4010.NRINSCESTAB, C_R4010.CPFBENEF, C_R4010.NMBENEF,
           C_R4010.CDFORNECED, V_NRSEQREINF);

       V_NATUREND := NULL;

       FOR C_NATUREND IN (SELECT NVL(NATREND,'00000') AS NATREND, DTFG, SUM(VLRBRUTO) AS VLRRENDBRUTO, SUM(VLRBASEIR) AS VLRRENDTRIB, SUM(VLRIR) AS VLRIR
                            FROM (SELECT NATREND, DTFG, VLRBRUTO, SUM(VLRBASEIR) AS VLRBASEIR, SUM(VLRIR) AS VLRIR, CDFILIAL, NRLANCTONF
                                    FROM (SELECT (SELECT CDNATUREND FROM PARAMMUNISERV PS
                                                   WHERE PS.CDEMPRESA    = NF.CDEMPRESA
                                                     AND PS.CDINSCMUNI   = NF.CDINSCMUNI
                                                     AND PS.IDDECLARACAO = 'RE'
                                                     AND PS.IDENTRSAIDOP IN ('E','T')
                                                     AND PS.CDPRODSERV   = IT.CDPRODUTO) AS NATREND,
                                                 RETORNA_DATA_FATOGERADOR(NF.CDFILIAL, NF.NRLANCTONF, NF.IDENTRSAIDOP, P_DTINICIAL, P_DTFINAL, IM.CDIMPOSTO) AS DTFG,
                                                 NF.VRNOTAFISC AS VLRBRUTO,
                                                 (DECODE(IM.CDIMPOSTONFE,'21',DECODE(II.CDFISCALIT,'1',II.VRBASECALCIT,0),0)) AS VLRBASEIR,
                                                 (DECODE(IM.CDIMPOSTONFE,'21',DECODE(II.CDFISCALIT,'1',II.VRIMPOSTOIT,0),0)) AS VLRIR,
                                                 NF.CDFILIAL, NF.NRLANCTONF
                                            FROM ENVIOEVENTOREINF EV, NOTAFISCAL NF, ITEMPROIMPOS II, ITEMPRODUTO IT,
                                                 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 ('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_R4010.CDFORNECED
                                             AND EF.CDFORNECED      = NF.CDFORNECED
                                             AND EF.IDTPENDEFORN    = 'P'
                                             AND NF.CDFILIRECE      = FI.CDFILIAL
                                             AND FI.NRINSJURFILI    = C_R4010.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'
                                             )
                               GROUP BY NATREND, DTFG, VLRBRUTO, CDFILIAL, NRLANCTONF)
                           GROUP BY NATREND, DTFG
                           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_R4010.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, VRCAMPO2, VRCAMPO3, NRSEQREINF)
          VALUES
             (P_CDEMPRESA, P_NREVENTO, P_NRENVIO, C_NATUREND.DTFG, C_NATUREND.VLRRENDBRUTO,
              C_NATUREND.NATREND, C_NATUREND.VLRRENDTRIB, C_NATUREND.VLRIR, V_NRSEQREINF);

       END LOOP;
   END LOOP;
END;
/