CREATE OR REPLACE PROCEDURE REINF_R2020(
   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_INDCPRB      IN VARCHAR2,
   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 INSS*/
   BEGIN
      SELECT M.NRSEQUMAPAPU, IM.IDFORMAPURNFE 
        INTO V_NRSEQUMAPAPU, V_IDFORMAPURNFE  
        FROM MAPAAPURAC M, IMPOSTONFE IM
       WHERE M.CDIMPOSTO = '42'
         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_R2020 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(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
                          CASE WHEN PR.CDCNO IS NULL THEN '1' ELSE '4' END AS TPINSCESTABPREST,                      --- 1 PARA CNPJ 4 PARA CNO
                          CASE WHEN PR.CDCNO IS NOT NULL THEN PR.CDCNO ELSE E.NRINSJUREMPR END AS NRINSCESTABPREST,  --- PREENCHER COM CNO OU CNPJ CONFORME O CAMPO ACIMA
                          CASE WHEN PR.CDCNO IS NULL THEN '1' ELSE '4' END AS TPINSCTOMADOR,                         --- 1 PARA CNPJ 4 PARA CNO
                          CASE WHEN PR.CDCNO IS NOT NULL THEN PR.CDCNO ELSE CL.NRINSJURCLIE END AS NRINSCTOMADOR,    --- PREENCHER COM CNO OU CNPJ CONFORME O CAMPO ACIMA
                          PR.IDSERVOBRCCIVIL AS INDOBRA,                                                             --- INDICACAO DE PRESTACAO DE SERVICOS DE OBRA
                          --DECODE(PR.IDSERVOBRCCIVIL,'1',PR.CDCNO,CL.NRINSJURCLIE) AS CNPJPRESTADOR,                --- CNPJ DO PRESTADOR DE SERVICO/CNO
                          SUM(nvl(PR.VRCONTABOBRA,0)) AS VLRTOTALBRUTO,                                              --- VALOR TOTAL DAS NOTAS FISCAIS DE SERVICOS
                          SUM(DECODE(IM.IDTPIMPFILHO,'00',NVL(PR.VRBASECALCIT,0),0)) AS VLRTOTALBASERET,             --- VALOR TOTAL DA BASE RETIDA DAS NOTAS FISCAIS DE SERVICO
                          SUM(DECODE(IM.IDTPIMPFILHO,'00',NVL(PR.VRIMPOSTOIT,0),0)) AS VLRTOTALRETPRINC,             --- VALOR TOTAL DA RETENCAO DAS NOTAS FISCAIS DE SERVICO
                          VALORADICINSS AS VLRTOTALRETADIC,                                                          --- VALOR ADICIONAL DAS NOTAS FISCAIS DE SERVICO
                          0.00 AS VLRTOTALNRETPRINC,                                                                 --- VALOR TOTAL NAO RETIDO PELO CONTRATANTE
                          0.00 AS VLRTOTALNRETADIC,                                                                  --- VALOR ADICIONAL NAO RETIDO
                          P.IDINDCPRB AS INDCPRB,
                          NF.CDCLIENTE
                          FROM NOTAFISCAL NF, IMPOSTONF IP, PARAMEMPREINF P, ENVIOEVENTOREINF EV,
                          IMPOSTO IM, IMPOSTONFE IE, CLIENTE CL, EMPRESA E,
                          (SELECT SUM(DECODE(I.IDTPIMPFILHO,'00',VRIMPOSTOIT,0)) AS VRIMPOSTOIT,
                                  SUM(DECODE(I.IDTPIMPFILHO,'00',VRBASECALCIT,0)) AS VRBASECALCIT,
                                  SUM(DECODE(I.IDTPIMPFILHO,'00',VRCONTITIMP,0)) AS VRCONTABOBRA,
                                  IT.CDFILIAL, IT.NRLANCTONF, PR.IDSERVOBRCCIVIL, PR.CDCNO,
                                  SUM(CASE WHEN I.IDTPIMPFILHO IN ('07','08','09')
                                                 THEN IT.VRIMPOSTOIT
                                                 ELSE 0.00 END) AS VALORADICINSS
                             FROM ITEMPRODUTO P,
                                  IMPOSTO I,
                                  ITEMPROIMPOS IT,
                                  PARAMMUNISERV PR,
                                  NOTAFISCAL NF
                            WHERE P.CDFILIAL = IT.CDFILIAL
                              AND P.NRLANCTONF = IT.NRLANCTONF
                              AND P.NRSEQITPROD = IT.NRSEQITPROD
                              AND PR.CDPRODSERV = P.CDPRODUTO
                              AND I.CDIMPOSTO = IT.CDIMPOSTO
                              AND I.CDIMPOSTONFE = '42'
                              AND PR.CDEMPRESA   = P_CDEMPRESA
                              AND NF.CDFILIAL   = IT.CDFILIAL
                              AND NF.NRLANCTONF = IT.NRLANCTONF
                              AND NF.NRLANCTONF = P.NRLANCTONF
                              AND NF.CDFILIAL   = P.CDFILIAL
                              AND PR.CDINSCMUNI = NF.CDINSCMUNI
                              AND PR.IDDECLARACAO = 'RE'
                              AND PR.IDENTRSAIDOP IN ('S','T')
                            GROUP BY IT.CDFILIAL, IT.NRLANCTONF, PR.IDSERVOBRCCIVIL, PR.CDCNO) PR
                    WHERE IP.CDFILIAL     = NF.CDFILIAL
                      AND IP.NRLANCTONF   = NF.NRLANCTONF
                      AND NF.CDEMPRESA    = E.CDEMPRESA
                      AND NF.IDORIGDESTNF = 'C'
                      AND NF.CDEMPRESA    = P_CDEMPRESA
                      and NF.CDCLIENTE    = CL.CDCLIENTE
                      AND ((IP.NRSEQUMAPAPU = V_NRSEQUMAPAPU) OR (IP.NRSEQMAPOIMP = V_NRSEQUMAPAPU))
                      AND IE.CDIMPOSTONFE = IM.CDIMPOSTONFE
                      AND IP.CDIMPOSTO    = IM.CDIMPOSTO
                      AND IP.CDFISCALNF = '1'
                      AND IP.VRIMPOSTONF > 0
                      AND IE.CDIMPOSTONFE = '42'
                      AND P.CDEMPRESA(+)  = E.CDEMPRESA
                      AND PR.CDFILIAL(+)     = NF.CDFILIAL
                      AND PR.NRLANCTONF(+)   = NF.NRLANCTONF
                      AND EV.NRENVIO      = P_NRENVIO
                      AND EV.CDESTABREINF = NF.CDCLIENTE
                    GROUP BY P_INDRETIF,
                          P_NRRECIBO,
                          TO_CHAR(TO_DATE(P_DTINICIAL),'YYYY-MM'),
                          P_IDAMBIENTE,
                          '1',
                          '1.5.1',
                          DECODE(E.IDTPIJUREMPR,'J','1','F','2',' '),
                          DECODE(E.IDTPIJUREMPR,'J',SUBSTR(E.NRINSJUREMPR,1,8), 'F', E.NRINSJUREMPR),
                          CASE WHEN PR.CDCNO IS NULL THEN '1' ELSE '4' END,
                          CASE WHEN PR.CDCNO IS NOT NULL THEN PR.CDCNO ELSE E.NRINSJUREMPR END,
                          CASE WHEN PR.CDCNO IS NULL THEN '1' ELSE '4' END,
                          CASE WHEN PR.CDCNO IS NOT NULL THEN PR.CDCNO ELSE CL.NRINSJURCLIE END,
                          PR.IDSERVOBRCCIVIL ,
                          --DECODE(PR.IDSERVOBRCCIVIL,'1',PR.CDCNO,CL.NRINSJURCLIE),
                          P.IDINDCPRB,
                          NF.CDCLIENTE, VALORADICINSS) 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, VRCAMPO1, VRCAMPO2,
               VRCAMPO3, VRCAMPO4, VRCAMPO5, VRCAMPO6, CDCAMPO14, CDCAMPO15, NRSEQREINF)
        VALUES
              (P_CDEMPRESA, P_NREVENTO, P_NRENVIO, C_R2020.INDRETIF, C_R2020.NRRECIBO, C_R2020.PERAPUR,
               C_R2020.TMPAMB, C_R2020.PROCEMI, C_R2020.VERPROC, C_R2020.TPINSC, C_R2020.NRINSC, C_R2020.TPINSCESTABPREST,
               C_R2020.NRINSCESTABPREST, C_R2020.TPINSCTOMADOR, C_R2020.NRINSCTOMADOR, C_R2020.INDOBRA, C_R2020.VLRTOTALBRUTO, C_R2020.VLRTOTALBASERET,
               C_R2020.VLRTOTALRETPRINC, C_R2020.VLRTOTALRETADIC, C_R2020.VLRTOTALNRETPRINC, C_R2020.VLRTOTALNRETADIC, C_R2020.INDCPRB, C_R2020.CDCLIENTE, V_NRSEQREINF);
    /*<nfs>*/
      FOR C_NFS IN (SELECT NVL(NF.CDSERIE,'0') AS SERIE,
                           NF.NRNOTAFISC  AS NUMDOCTO,
                           DECODE(V_IDFORMAPURNFE,'D', NF.DTENTRSAID, NF.DTEMISSAO)   AS DTEMISSAONF,
                           MAX(NF.VRNOTAFISC)  AS VLRBRUTO,
                           '' AS OBS,
                           NF.CDFILIAL, NF.NRLANCTONF,
                           NF.CDINSCMUNI
                      FROM NOTAFISCAL NF, IMPOSTONF IP
                     WHERE NF.CDCLIENTE = C_R2020.CDCLIENTE
                       AND NF.CDFILIAL   = IP.CDFILIAL
                       and NF.NRLANCTONF = IP.NRLANCTONF
                       AND ((IP.NRSEQUMAPAPU = V_NRSEQUMAPAPU) OR (IP.NRSEQMAPOIMP = V_NRSEQUMAPAPU))
                       AND IP.VRIMPOSTONF > 0
                       AND IP.CDFISCALNF = '1'
                       GROUP BY NVL(NF.CDSERIE,'0'), NF.NRNOTAFISC,  DECODE(V_IDFORMAPURNFE,'D', NF.DTENTRSAID, NF.DTEMISSAO),NF.CDFILIAL, NF.NRLANCTONF,
                                NF.CDINSCMUNI )LOOP
         NOVO_CODIGO_NT('NRSEQREINF', V_NRSEQREINF);
         INSERT INTO ARQEFD_REINF
                     (CDEMPRESA, NREVENTO, NRENVIO, CDCAMPO1, CDCAMPO2,
                      CDCAMPO3, CDCAMPO4, CDCAMPO5, CDCAMPO6, DTCAMPO1,
                      VRCAMPO1, CDCAMPO7, CDCAMPO8, CDCAMPO9, CDCAMPO10, NRSEQREINF)
              VALUES (P_CDEMPRESA, P_NREVENTO, P_NRENVIO, C_R2020.INDRETIF, C_R2020.NRRECIBO,
                      C_R2020.PERAPUR, C_R2020.TMPAMB,  C_NFS.SERIE, C_NFS.NUMDOCTO, C_NFS.DTEMISSAONF,
                      C_NFS.VLRBRUTO, C_NFS.OBS, C_R2020.CDCLIENTE ,C_NFS.CDFILIAL ,C_NFS.NRLANCTONF, V_NRSEQREINF);

         /*<infoTpServ>*/
         FOR C_INFOTPSERV IN (SELECT CDCLASERVMOBEMP AS TPSERVICO,   --TIPO SERVICO
                                     SUM(DECODE(I.IDTPIMPFILHO,'00',DECODE(IT.CDFISCALIT,'1',IT.VRBASECALCIT,0),0)) AS VLRBASERET,
                                     SUM(DECODE(I.IDTPIMPFILHO,'00',DECODE(IT.CDFISCALIT,'1',IT.VRIMPOSTOIT,0),0))  AS VLRRETENCAO,
                                     0.00   AS VLRRETSUB,
                                     0.00   AS VLRNRETPRINC,
                                     SUM(DECODE(I.IDTPIMPFILHO,'07',DECODE(IT.CDFISCALIT,'1',IT.VRBASECALCIT,0),0)) AS VLRSERVICOS15,
                                     SUM(DECODE(I.IDTPIMPFILHO,'08',DECODE(IT.CDFISCALIT,'1',IT.VRBASECALCIT,0),0)) AS VLRSERVICOS20,
                                     SUM(DECODE(I.IDTPIMPFILHO,'09',DECODE(IT.CDFISCALIT,'1',IT.VRBASECALCIT,0),0)) AS VLRSERVICOS25,
                                     SUM(CASE WHEN I.IDTPIMPFILHO <> '00'
                                                 THEN IT.VRIMPOSTOIT
                                                 ELSE 0.00 END) AS VLRADICIONAL,
                                     0.00   AS VLRNRETADIC
                                FROM ITEMPRODUTO IP, ITEMPROIMPOS IT, PARAMMUNISERV PR, IMPOSTO I
                               WHERE IP.CDFILIAL    = IT.CDFILIAL
                                 AND IP.NRLANCTONF  = IT.NRLANCTONF
                                 AND IP.NRSEQITPROD = IT.NRSEQITPROD
                                 AND IT.CDFILIAL    = C_NFS.CDFILIAL
                                 AND IT.NRLANCTONF  = C_NFS.NRLANCTONF
                                 AND PR.CDINSCMUNI  = C_NFS.CDINSCMUNI
                                 AND PR.IDDECLARACAO = 'RE'
                                 AND PR.IDENTRSAIDOP IN ('S','T')
                                 AND I.CDIMPOSTO     = IT.CDIMPOSTO
                                 AND I.CDIMPOSTONFE  = '42'
                                 AND PR.CDEMPRESA   = P_CDEMPRESA
                                 AND PR.CDPRODSERV = IP.CDPRODUTO
                                GROUP BY CDCLASERVMOBEMP)LOOP
               NOVO_CODIGO_NT('NRSEQREINF', V_NRSEQREINF);
               INSERT INTO ARQEFD_REINF
                      (CDEMPRESA, NREVENTO, NRENVIO, CDCAMPO1,
                       CDCAMPO2, CDCAMPO3, CDCAMPO4, CDCAMPO5,
                       VRCAMPO1, VRCAMPO2, VRCAMPO3, VRCAMPO4,
                       VRCAMPO5, VRCAMPO6, VRCAMPO7, VRCAMPO8,
                       VRCAMPO9, CDCAMPO6, CDCAMPO7, NRSEQREINF)
               VALUES (P_CDEMPRESA, P_NREVENTO, P_NRENVIO, C_R2020.INDRETIF,
                       C_R2020.NRRECIBO, C_R2020.PERAPUR, C_R2020.TMPAMB, C_INFOTPSERV.TPSERVICO,
                       C_INFOTPSERV.VLRBASERET, C_INFOTPSERV.VLRRETENCAO, C_INFOTPSERV.VLRRETSUB, C_INFOTPSERV.VLRNRETPRINC,
                       C_INFOTPSERV.VLRSERVICOS15, C_INFOTPSERV.VLRSERVICOS20, C_INFOTPSERV.VLRSERVICOS25, C_INFOTPSERV.VLRADICIONAL,
                       C_INFOTPSERV.VLRADICIONAL, C_NFS.CDFILIAL ,C_NFS.NRLANCTONF, V_NRSEQREINF);


         END LOOP;
      END LOOP;
   END LOOP;
END;
/