create or replace PROCEDURE REINF_R2055(
   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_VERSAO       IN VARCHAR2,
   P_NRRECIBO     IN VARCHAR2,
   P_INDRETIF     IN VARCHAR2,
   P_IDAMBIENTE   IN VARCHAR2)
AS
   V_NRSEQREINF    NUMBER;
   V_NRSEQUMAPAPU  MAPAAPURAC.NRSEQUMAPAPU%TYPE;
   V_IDFORMAPURNFE IMPOSTONFE.IDFORMAPURNFE%TYPE;
BEGIN

   /*RETORNA O NUMERO DO MAPA DO FUNRURAL*/
   BEGIN
      SELECT M.NRSEQUMAPAPU, IM.IDFORMAPURNFE
        INTO V_NRSEQUMAPAPU, V_IDFORMAPURNFE
        FROM MAPAAPURAC M, IMPOSTONFE IM
       WHERE M.CDIMPOSTO     = '26'
         AND M.CDEMPRESA     = P_CDEMPRESA
         AND M.DTINIPERIODO  = P_DTINICIAL
         AND M.DTFIMPERIODO  = P_DTFINAL
         AND IM.CDIMPOSTONFE = M.CDIMPOSTO;
   EXCEPTION
   WHEN NO_DATA_FOUND THEN
      V_NRSEQUMAPAPU := NULL;
   END;

   FOR C_R2055 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,
                          NF.TPINSC,
                          NF.NRINSC,                                                                                     --SE FOR PESSOA JURIDICA PASSA APENAS A RAIZ DO CNPJ
                          NF.TPINSCADQ,                                                                                  --TIPO DA INSCRICAO DO ESTABELECIMENTO ADIQUIRENTE DA PRODUCAO AGROPECUARIA
                          NF.NRINSCADQ,                                                                                  --INSCRICAO DO ESTABELECIMENTO ADIQUIRENTE DA PRODUCAO AGROPECUARIA
                          NF.TPINSCPROD,                                                                                 --TIPO DA INSCRICAO DO PRODUTOR
                          NF.NRINSCPROD,                                                                                 --INSCRICAO DO PRODUTOR RURAL
                          NF.INDOPCCP,                                                                                   --O CAMPO SO DEVE SER INFORMADO SE O PRODUTOR FOR OPTANTE PELA TRIBUTACAO NA FOLHA
                          NF.INDAQUIS,
                          SUM(NF.VLRBRUTO) AS VLRBRUTO,                                                                  --VALOR BRUTO DO SERVICO
                          SUM(NF.VLRCPDESCPR) AS VLRCPDESCPR,                                                            --VALOR DO FUNRURAL REFERENTE A CONTRIBUICAO PREVIDENCIARIA
                          SUM(NF.VLRRATDESCPR) AS VLRRATDESCPR,                                                          --VALOR DO FUNRURAL REFERENTE AO RAT
                          SUM(NF.VLRSENARDESC) AS VLRSENARDESC,                                                          --VALOR DO FUNRURAL REFERENTE AO SENAR
                          NF.CDFORNECED
                      FROM (
                             SELECT
                                   DECODE(E.IDTPIJUREMPR,'J','1','F','2','') AS TPINSC,
                                   DECODE(E.IDTPIJUREMPR,'J',SUBSTR(E.NRINSJUREMPR,1,8), 'F', E.NRINSJUREMPR)  AS NRINSC,          --SE FOR PESSOA JURIDICA PASSA APENAS A RAIZ DO CNPJ
                                   DECODE(FI.IDTPIJURFILI,'J','1','F','3','') AS TPINSCADQ,                                        --TIPO DA INSCRICAO DO ESTABELECIMENTO ADIQUIRENTE DA PRODUCAO AGROPECUARIA
                                   REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.'),'/'),'-') AS NRINSCADQ,                            --INSCRICAO DO ESTABELECIMENTO ADIQUIRENTE DA PRODUCAO AGROPECUARIA
                                   DECODE(FO.IDTPIJURFORN,'J',CASE WHEN NVL(FO.IDNATURJURPF,'N') = 'S' AND (FO.NRCPFNATURJUR IS NOT NULL)
                                                                   THEN '2' ELSE '1' END,'F','2','') AS TPINSCPROD,                --TIPO DA INSCRICAO DO PRODUTOR
                                   REPLACE(REPLACE(REPLACE(CASE WHEN NVL(FO.IDNATURJURPF,'N') = 'S' AND (FO.NRCPFNATURJUR IS NOT NULL)
                                      THEN FO.NRCPFNATURJUR
                                      ELSE FO.NRINSJURFORN END,'.'),'/'),'-') AS NRINSCPROD,                                       --INSCRICAO DO PRODUTOR RURAL
                                   DECODE(FO.IDTRIBCPFOLHA,'S','S',NULL) AS INDOPCCP,                                              --O CAMPO SO DEVE SER INFORMADO SE O PRODUTOR FOR OPTANTE PELA TRIBUTACAO NA FOLHA
                                   CASE WHEN (FO.IDTPIJURFORN = 'F' OR (NVL(FO.IDNATURJURPF,'N') = 'S' AND (FO.NRCPFNATURJUR IS NOT NULL))) AND IP.CDFISCOPERNF IN ('1501','2501') THEN '7'                 --AQUISICAO DE PESSOA FISICA PARA FINS DE EXPORTACAO
                                        WHEN (FO.IDTPIJURFORN = 'F' OR (NVL(FO.IDNATURJURPF,'N') = 'S' AND (FO.NRCPFNATURJUR IS NOT NULL))) AND FO.IDPRODPARTPAA = 'N' AND FO.IDPRODISENT = 'N' THEN '1'    --AQUISICAO DE PESSOA FISICA
                                        WHEN (FO.IDTPIJURFORN = 'F' OR (NVL(FO.IDNATURJURPF,'N') = 'S' AND (FO.NRCPFNATURJUR IS NOT NULL))) AND FO.IDPRODPARTPAA = 'S' AND FO.IDPRODISENT = 'N' THEN '2'    --AQUISICAO DE PESSOA FISICA POR ENTIDADE EXECUTORA DO PAA
                                        WHEN FO.IDTPIJURFORN = 'J' AND NVL(FO.IDNATURJURPF,'N') = 'N' AND FO.IDPRODPARTPAA = 'S' AND FO.IDPRODISENT = 'N' THEN '3'                                          --AQUISICAO DE PESSOA JURIDICA POR ENTIDADE EXECUTORA DO PAA
                                        WHEN (FO.IDTPIJURFORN = 'F' OR (NVL(FO.IDNATURJURPF,'N') = 'S' AND (FO.NRCPFNATURJUR IS NOT NULL))) AND FO.IDPRODPARTPAA = 'N' AND FO.IDPRODISENT = 'S' THEN '4'    --AQUISICAO DE PESSOA FISICA PRODUCAO ISENTA
                                        WHEN (FO.IDTPIJURFORN = 'F' OR (NVL(FO.IDNATURJURPF,'N') = 'S' AND (FO.NRCPFNATURJUR IS NOT NULL))) AND FO.IDPRODPARTPAA = 'S' AND FO.IDPRODISENT = 'S' THEN '5'    --AQUISICAO DE PESSOA FISICA POR ENTIDADE EXECUTORA DO PAA - PRODUCAO ISENTA
                                        WHEN FO.IDTPIJURFORN = 'J' AND NVL(FO.IDNATURJURPF,'N') = 'N' AND FO.IDPRODPARTPAA = 'S' AND FO.IDPRODISENT = 'S' THEN '6'                                          --AQUISICAO DE PESSOA JURIDICA POR ENTIDADE EXECUTORA DO PAA - PRODUCAO ISENTA
                                        ELSE '0'
                                   END AS INDAQUIS,
                                   NF.VRNOTAFISC AS VLRBRUTO,                                                                      --VALOR BRUTO DO SERVICO
                                   SUM(DECODE(IM.IDTPIMPFILHO,'04',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VLRCPDESCPR,  --VALOR DO FUNRURAL REFERENTE A CONTRIBUICAO PREVIDENCIARIA
                                   SUM(DECODE(IM.IDTPIMPFILHO,'05',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VLRRATDESCPR, --VALOR DO FUNRURAL REFERENTE AO RAT
                                   SUM(DECODE(IM.IDTPIMPFILHO,'06',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VLRSENARDESC, --VALOR DO FUNRURAL REFERENTE AO SENAR
                                   NF.CDFORNECED, NF.CDFILIAL, NF.NRLANCTONF
                              FROM NOTAFISCAL NF, IMPOSTONF IP, PARAMEMPREINF P, ENVIOEVENTOREINF EV,
                                   IMPOSTO IM, IMPOSTONFE IE, FORNECEDOR FO, EMPRESA E, FILIAL FI
                             WHERE IP.CDFILIAL     = NF.CDFILIAL
                               AND IP.NRLANCTONF   = NF.NRLANCTONF
                               AND NF.CDEMPRESA    = E.CDEMPRESA
                               AND NF.IDORIGDESTNF = 'F'
                               AND NF.CDEMPRESA    = P_CDEMPRESA
                               and NF.CDFORNECED   = FO.CDFORNECED
                               AND ((IP.NRSEQUMAPAPU = V_NRSEQUMAPAPU) OR (IP.NRSEQMAPOIMP = V_NRSEQUMAPAPU))
                               AND IE.CDIMPOSTONFE = IM.CDIMPOSTONFE
                               AND IP.CDIMPOSTO    = IM.CDIMPOSTO
                               AND IE.CDIMPOSTONFE = '26'
                               AND P.CDEMPRESA(+)  = E.CDEMPRESA
                               AND EV.NRENVIO      = P_NRENVIO
                               AND EV.CDESTABREINF = NF.CDFORNECED
                               AND FI.CDFILIAL     = NF.CDFILIRECE
                               AND FI.NRINSJURFILI = NVL(EV.NRINSJURFILI, FI.NRINSJURFILI)
                             GROUP BY DECODE(E.IDTPIJUREMPR,'J','1','F','2',''),
                                   DECODE(E.IDTPIJUREMPR,'J',SUBSTR(E.NRINSJUREMPR,1,8), 'F', E.NRINSJUREMPR),
                                   DECODE(FI.IDTPIJURFILI,'J','1','F','3',''),
                                   REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.'),'/'),'-'),
                                   DECODE(FO.IDTPIJURFORN,'J',CASE WHEN NVL(FO.IDNATURJURPF,'N') = 'S' AND (FO.NRCPFNATURJUR IS NOT NULL)
                                                                   THEN '2' ELSE '1' END,'F','2',''),
                                   REPLACE(REPLACE(REPLACE(CASE WHEN NVL(FO.IDNATURJURPF,'N') = 'S' AND (FO.NRCPFNATURJUR IS NOT NULL)
                                      THEN FO.NRCPFNATURJUR
                                      ELSE FO.NRINSJURFORN END,'.'),'/'),'-'),
                                   DECODE(FO.IDTRIBCPFOLHA,'S','S',NULL),
                                   CASE WHEN (FO.IDTPIJURFORN = 'F' OR (NVL(FO.IDNATURJURPF,'N') = 'S' AND (FO.NRCPFNATURJUR IS NOT NULL))) AND IP.CDFISCOPERNF IN ('1501','2501') THEN '7'                 --AQUISICAO DE PESSOA FISICA PARA FINS DE EXPORTACAO
                                        WHEN (FO.IDTPIJURFORN = 'F' OR (NVL(FO.IDNATURJURPF,'N') = 'S' AND (FO.NRCPFNATURJUR IS NOT NULL))) AND FO.IDPRODPARTPAA = 'N' AND FO.IDPRODISENT = 'N' THEN '1'    --AQUISICAO DE PESSOA FISICA
                                        WHEN (FO.IDTPIJURFORN = 'F' OR (NVL(FO.IDNATURJURPF,'N') = 'S' AND (FO.NRCPFNATURJUR IS NOT NULL))) AND FO.IDPRODPARTPAA = 'S' AND FO.IDPRODISENT = 'N' THEN '2'    --AQUISICAO DE PESSOA FISICA POR ENTIDADE EXECUTORA DO PAA
                                        WHEN FO.IDTPIJURFORN = 'J' AND NVL(FO.IDNATURJURPF,'N') = 'N' AND FO.IDPRODPARTPAA = 'S' AND FO.IDPRODISENT = 'N' THEN '3'                                          --AQUISICAO DE PESSOA JURIDICA POR ENTIDADE EXECUTORA DO PAA
                                        WHEN (FO.IDTPIJURFORN = 'F' OR (NVL(FO.IDNATURJURPF,'N') = 'S' AND (FO.NRCPFNATURJUR IS NOT NULL))) AND FO.IDPRODPARTPAA = 'N' AND FO.IDPRODISENT = 'S' THEN '4'    --AQUISICAO DE PESSOA FISICA PRODUCAO ISENTA
                                        WHEN (FO.IDTPIJURFORN = 'F' OR (NVL(FO.IDNATURJURPF,'N') = 'S' AND (FO.NRCPFNATURJUR IS NOT NULL))) AND FO.IDPRODPARTPAA = 'S' AND FO.IDPRODISENT = 'S' THEN '5'    --AQUISICAO DE PESSOA FISICA POR ENTIDADE EXECUTORA DO PAA - PRODUCAO ISENTA
                                        WHEN FO.IDTPIJURFORN = 'J' AND NVL(FO.IDNATURJURPF,'N') = 'N' AND FO.IDPRODPARTPAA = 'S' AND FO.IDPRODISENT = 'S' THEN '6'    --AQUISICAO DE PESSOA JURIDICA POR ENTIDADE EXECUTORA DO PAA - PRODUCAO ISENTA
                                        ELSE '0'
                                   END, NF.CDFORNECED, NF.CDFILIAL, NF.NRLANCTONF, NF.VRNOTAFISC ) NF
                          GROUP BY NF.TPINSC, NF.NRINSC, NF.TPINSCADQ, NF.NRINSCADQ, NF.TPINSCPROD, NF.NRINSCPROD, NF.INDOPCCP,
                                   NF.INDAQUIS, NF.CDFORNECED) 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, CDCAMPO14, VRCAMPO1, VRCAMPO2, VRCAMPO3,
               VRCAMPO4, CDCAMPO15, NRSEQREINF)
       VALUES (P_CDEMPRESA, P_NREVENTO, P_NRENVIO, C_R2055.INDRETIF, C_R2055.NRRECIBO,
               C_R2055.PERAPUR, C_R2055.TMPAMB, C_R2055.PROCEMI, C_R2055.VERPROC, C_R2055.TPINSC,
               C_R2055.NRINSC, C_R2055.TPINSCADQ, C_R2055.NRINSCADQ, C_R2055.TPINSCPROD, C_R2055.NRINSCPROD,
               C_R2055.INDOPCCP, C_R2055.INDAQUIS, C_R2055.VLRBRUTO, C_R2055.VLRCPDESCPR, C_R2055.VLRRATDESCPR,
               C_R2055.VLRSENARDESC, C_R2055.CDFORNECED, V_NRSEQREINF);
   END LOOP;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('REINF_R2040','6.49.024'); END;
/
