CREATE OR REPLACE PROCEDURE REINF_R2010(
   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 ISSQN*/
   BEGIN
      SELECT M.NRSEQUMAPAPU, IM.IDFORMAPURNFE  
        INTO V_NRSEQUMAPAPU, V_IDFORMAPURNFE   
        FROM MAPAAPURAC M, IMPOSTONFE IM  
       WHERE M.CDIMPOSTO = '41' 
         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;
         V_IDFORMAPURNFE := NULL;
   END;

   FOR C_R2010 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
                          MAX(CASE WHEN PR.CDCNO IS NULL THEN '1' ELSE '4' END) AS TPINSCESTAB,                      ---1 PARA CNPJ 4 PARA CNO
                          MAX(CASE WHEN PR.CDCNO IS NOT NULL THEN PR.CDCNO ELSE FI.NRINSJURFILI END) AS NRINSCESTAB, --- PREENCHER COM CNO OU CNPJ CONFORME O CAMPO ACIMA
                          MAX(PR.IDSERVOBRCCIVIL) AS INDOBRA,                                                        --- INDICACAO DE PRESTACAO DE SERVICOS DE OBRA
                          FO.NRINSJURFORN AS CNPJPRESTADOR,                                                          --- CNPJ DO PRESTADOR DE SERVICO DE OBRA
                          SUM(PR.VRCONTABOBRA) AS VLRTOTALBRUTO,                                                     --- VALOR TOTAL DAS NOTAS FISCAIS DE SERVICOS
                          SUM(DECODE(IM.IDTPIMPFILHO,'00',PR.VRBASECALCIT,0)) AS VLRTOTALBASERET,                    --- VALOR TOTAL DA BASE RETIDA DAS NOTAS FISCAIS DE SERVICO
                          SUM(DECODE(IM.IDTPIMPFILHO,'00',PR.VRIMPOSTOIT,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
                          FO.IDINDCPRB AS INDCPRB,
                          NF.CDFORNECED
                     FROM NOTAFISCAL NF, (SELECT DISTINCT CDFILIAL, NRLANCTONF, CDIMPOSTO
                                            FROM IMPOSTONF
                                           WHERE ((NRSEQUMAPAPU = V_NRSEQUMAPAPU) OR (NRSEQMAPOIMP = V_NRSEQUMAPAPU))
                                             AND CDFISCALNF = '1' AND VRIMPOSTONF > 0)IP,
                          PARAMEMPREINF P, ENVIOEVENTOREINF EV,
                          IMPOSTO IM, IMPOSTONFE IE, FORNECEDOR FO, EMPRESA E, FILIAL FI,
                          (SELECT SUM(CASE WHEN VRIMPOSTOIT - ROUND(VRBASECALCIT*VRALIQ/100,2) BETWEEN 0 AND 0.02
                                           THEN ROUND(VRBASECALCIT*VRALIQ/100,2) ELSE VRIMPOSTOIT END) AS VRIMPOSTOIT,
                                  SUM(VRBASECALCIT) AS VRBASECALCIT, SUM(VRCONTABOBRA) AS VRCONTABOBRA,
                                  P.CDFILIAL, P.NRLANCTONF, P.IDSERVOBRCCIVIL, P.CDCNO,
                                  SUM(VALORADICINSS) AS VALORADICINSS
                             FROM (SELECT SUM(DECODE(I.IDTPIMPFILHO,'00',VRIMPOSTOIT,0)) AS VRIMPOSTOIT,
                                          SUM(DECODE(I.IDTPIMPFILHO,'00',VRBASECALCIT,0)) AS VRBASECALCIT,
                                          MAX(DECODE(I.IDTPIMPFILHO,'00',VRALIQITEM,0)) AS VRALIQ,
                                          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,
                                          PR.CDCLASERVMOBEMP
                                     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.CDEMPRESA    = P_CDEMPRESA
                                      AND PR.CDPRODSERV   = P.CDPRODUTO
                                      AND I.CDIMPOSTO     = IT.CDIMPOSTO
                                      AND I.CDIMPOSTONFE  = '41'
                                      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 ('E','T')
                                    GROUP BY IT.CDFILIAL, IT.NRLANCTONF, PR.IDSERVOBRCCIVIL, PR.CDCNO, PR.CDCLASERVMOBEMP) P
                             GROUP BY P.CDFILIAL, P.NRLANCTONF, P.IDSERVOBRCCIVIL, P.CDCNO) PR
                    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 IE.CDIMPOSTONFE = IM.CDIMPOSTONFE
                      AND IP.CDIMPOSTO    = IM.CDIMPOSTO
                      AND IE.CDIMPOSTONFE = '41'
                      AND P.CDEMPRESA(+)  = E.CDEMPRESA
                      AND PR.CDFILIAL     = NF.CDFILIAL
                      AND PR.NRLANCTONF   = NF.NRLANCTONF
                      AND EV.NRENVIO      = P_NRENVIO
                      AND EV.CDESTABREINF = NF.CDFORNECED
                      AND FI.CDFILIAL     = NF.CDFILIRECE
                    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),
                          FO.IDINDCPRB, FO.NRINSJURFORN,
                          NF.CDFORNECED, 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,
               VRCAMPO1, VRCAMPO2, VRCAMPO3, VRCAMPO4, VRCAMPO5,
               VRCAMPO6, CDCAMPO14, CDCAMPO15, NRSEQREINF)
       VALUES (P_CDEMPRESA, P_NREVENTO, P_NRENVIO, C_R2010.INDRETIF, C_R2010.NRRECIBO,
               C_R2010.PERAPUR, C_R2010.TMPAMB, C_R2010.PROCEMI, C_R2010.VERPROC, C_R2010.TPINSC,
               C_R2010.NRINSC, C_R2010.TPINSCESTAB, C_R2010.NRINSCESTAB, C_R2010.INDOBRA, C_R2010.CNPJPRESTADOR,
               C_R2010.VLRTOTALBRUTO, C_R2010.VLRTOTALBASERET, C_R2010.VLRTOTALRETPRINC, C_R2010.VLRTOTALRETADIC, C_R2010.VLRTOTALNRETPRINC,
               C_R2010.VLRTOTALNRETADIC, C_R2010.INDCPRB, C_R2010.CDFORNECED, V_NRSEQREINF);

      /*<nfs>*/
      FOR C_NFS IN (SELECT NF.CDSERIEENTR AS SERIE,
                           NF.NRNOTAFISC  AS NUMDOCTO,
                           DECODE(V_IDFORMAPURNFE,'E',NF.DTEMISSAO, NF.DTENTRSAID) AS DTEMISSAONF,
                           MAX(NF.VRNOTAFISC)  AS VLRBRUTO,
                           '' AS OBS,
                           NF.CDFILIAL, NF.NRLANCTONF,
                           NF.CDINSCMUNI
                      FROM NOTAFISCAL NF, (SELECT DISTINCT CDFILIAL, NRLANCTONF, CDIMPOSTO
                                             FROM IMPOSTONF IP
                                            WHERE ((IP.NRSEQUMAPAPU = V_NRSEQUMAPAPU) OR (IP.NRSEQMAPOIMP = V_NRSEQUMAPAPU))
                                              AND IP.VRIMPOSTONF > 0
                                              AND IP.CDFISCALNF = '1' ) IP
                     WHERE NF.CDFORNECED = C_R2010.CDFORNECED
                       AND NF.CDFILIAL   = IP.CDFILIAL
                       AND NF.NRLANCTONF = IP.NRLANCTONF
                       GROUP BY NF.CDSERIEENTR, NF.NRNOTAFISC, DECODE(V_IDFORMAPURNFE,'E',NF.DTEMISSAO, NF.DTENTRSAID),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_R2010.INDRETIF, C_R2010.NRRECIBO,
                      C_R2010.PERAPUR, C_R2010.TMPAMB,  C_NFS.SERIE, C_NFS.NUMDOCTO, C_NFS.DTEMISSAONF,
                      C_NFS.VLRBRUTO, C_NFS.OBS, C_R2010.CDFORNECED,C_NFS.CDFILIAL , C_NFS.NRLANCTONF , V_NRSEQREINF);

         /*<infoTpServ>*/
         FOR C_INFOTPSERV IN (SELECT TPSERVICO, VLRBASERET,
                                     CASE WHEN VLRRETENCAO - ROUND(VLRBASERET*VRALIQ/100,2) BETWEEN 0 AND 0.02
                                          THEN ROUND(VLRBASERET*VRALIQ/100,2) ELSE VLRRETENCAO END AS VLRRETENCAO,
                                     VLRRETSUB, VLRNRETPRINC, VLRSERVICOS15, VLRSERVICOS20, VLRSERVICOS25,
                                     VLRADICIONAL,VLRNRETADIC
                                FROM (SELECT CDCLASERVMOBEMP AS TPSERVICO,
                                             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,
                                             MAX(DECODE(I.IDTPIMPFILHO,'00',DECODE(IT.CDFISCALIT,'1',IT.VRALIQITEM,0),0)) AS VRALIQ,
                                             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 ('E' ,'T')
                                         AND I.CDIMPOSTO     = IT.CDIMPOSTO
                                         AND I.CDIMPOSTONFE  = '41'
                                         AND PR.CDEMPRESA   = P_CDEMPRESA
                                         AND PR.CDPRODSERV = IP.CDPRODUTO
                                         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_R2010.INDRETIF,
                       C_R2010.NRRECIBO, C_R2010.PERAPUR, C_R2010.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;
/