CREATE OR REPLACE FUNCTION SPED_PIS_COFINS_BLOCOM(
   P_CONTEST       IN NUMBER,
   P_CDEMPRESA     IN EMPRESA.CDEMPRESA%TYPE,
   P_NRSEQARQMAG   IN ARQMAGPISCOFINS.NRSEQARQMAG%TYPE,
   P_DTINICIAL     IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL       IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_LIVROMAG      IN LIVROMAGNETICO.CDLIVROMAG%TYPE,
   P_CDINCTRIB     IN VARCHAR2,
   P_GERABCLP      IN VARCHAR2,
   P_PIS           IN IMPOSTO.CDIMPOSTO%TYPE,
   P_COFINS        IN IMPOSTO.CDIMPOSTO%TYPE,
   P_GERADEVOLVEND IN VARCHAR2,
   P_VERSAO        IN VARCHAR2,
   P_COUNTBLC0     OUT NUMBER) RETURN NUMBER
AS
   V_CONTREGA            NUMBER;
   V_SEQAJUST            NUMBER;  
   V_CONT                NUMBER;
   V_REGM100             VARCHAR2(1000);
   V_REGM110             VARCHAR2(1000);
   V_REGM210             VARCHAR2(1000);
   V_REGM500             VARCHAR2(1000);
   V_REGM510             VARCHAR2(1000);
   V_REGM610             VARCHAR2(1000);
   V_REGM205_C           VARCHAR2(1000);
   V_REGM205_NC          VARCHAR2(1000);
   V_REGM605_C           VARCHAR2(1000);
   V_REGM605_NC          VARCHAR2(1000); 
   V_NRMAPAPIS           VARCHAR2(10);
   V_NRMAPACOFINS        VARCHAR2(10);
   V_CDRECEPISCUMU       VARCHAR2(6);
   V_CDRECEPISNCUMU      VARCHAR2(6);
   V_CDRECECOFINSCUMU    VARCHAR2(6);
   V_CDRECECOFINSNCUMU   VARCHAR2(6);
   V_IDUTICRED           VARCHAR2(01);
   V_VRCREDPIS           NUMBER;
   V_VRCREDCOFINS        NUMBER;
   V_VRCREDITO           FLOAT;
   V_VRTOTACRED          FLOAT;
   V_CREDUTLIZADO        FLOAT;
   V_VRSALDOFUTURO       FLOAT;
   V_CREDANTERIOR        FLOAT;
   V_IMPRETIDONCUM       FLOAT;
   V_IMPRETIDOCUM        FLOAT;
   V_VRDEDUCAONCUM       FLOAT;
   V_VRDEDUCAOCUMU       FLOAT;
   V_GERAAJUSTEAUTO      VARCHAR2(01);
   V_VRAJUSTEAUTO        FLOAT;
   V_DESCAJUSTEAUTO      VARCHAR2(250);
   V_CDAJUSTEAUTO        VARCHAR2(02);
   V_VRAJUSTEACRES       FLOAT;
   V_VRBCAJUSTEACRES     FLOAT;
   V_VRAJUSTEREDUC       FLOAT;
   V_VRBCAJUSTEREDUC     FLOAT;
   V_VRAJUSTENCUM        FLOAT;
   V_VRAJUSTECUMU        FLOAT;
   V_VRACRESAJUSAUTO     FLOAT;
   V_VRREDUZAJUSAUTO     FLOAT;
   V_TIPOCREDITO         VARCHAR2(04);
   V_CDCONTRIB           VARCHAR2(02);
   V_CONTEST             NUMBER;
   V_COUNTBCL0           NUMBER;
   V_NRINSJUR            VARCHAR2(20);
   V_REG                 VARCHAR2(1000);
   V_SEQUPROD            NUMBER;
   V_COD_CC              VARCHAR2(20);
   V_SEQCCTBL            NUMBER;
   V_VRCREDDIFERIDO      FLOAT;
   V_VRCONTRIBDIF        FLOAT;
   V_VRCONTRIBDIFANT     FLOAT;
   V_VRDIFERIMENTONCUM   FLOAT;
   V_VRDIFERIMENTOCUMU   FLOAT;
   V_VRAJUSTEACRESBC     FLOAT;
   V_VRAJUSTEREDUCBC     FLOAT;
BEGIN
   /*INICIALIZA AS VARIAVEIS COM 0 PARA QUE AS MESMAS NAO FIQUEM NULAS*/
   V_VRCREDPIS    := 0;
   V_VRCREDCOFINS := 0;

   V_CONTEST := P_CONTEST;

   SELECT MAX(CDCONTREGI)
     INTO V_SEQUPROD
     FROM TMP_REGDADOSSPED_PIS;

   /*OBSERVACAO: O CAMPO IDCUMUPISCOFIL DEFINE SE A FILIAL E CUMULATIVA OU NAO CUMULATIVA. N PARA CUMULATIVA E S PARA NAO-CUMULATIVA*/
   /*BUSCA OS AJUSTES DE ACRESCIMO E REDUCAO - AJUSTES E DEVOLUCOES*/
   FOR C_AJUS IN (SELECT DECODE(P.CDIMPOSTONFE,P_PIS,I.CDTPCREDPICOITE,'') AS CDTPCREDPIS,
                         DECODE(P.CDIMPOSTONFE,P_COFINS,I.CDTPCREDPICOITE,'') AS CDTPCREDCOFINS,
                         DECODE(P.CDIMPOSTONFE,P_PIS,I.VRALIQITEM,'') AS VRALIQPIS,
                         DECODE(P.CDIMPOSTONFE,P_COFINS,I.VRALIQITEM,'') AS VRALIQCOFINS,
                         DECODE(P.CDIMPOSTONFE,P_PIS,I.CDSITUTRIBITNFE,'') AS CSTPIS,
                         DECODE(P.CDIMPOSTONFE,P_COFINS,I.CDSITUTRIBITNFE,'') AS CSTCOFINS,
                         DECODE(P.CDIMPOSTONFE,P_PIS,I.CDNATUBCALPCIT,'') AS CDNATUBCPIS,
                         DECODE(P.CDIMPOSTONFE,P_COFINS,I.CDNATUBCALPCIT,'') AS CDNATUBCCOFINS,
                         N.IDENTRSAIDOP AS IDAJUSTE,
                         '05' AS CDAJUSTE,
                         SUM(DECODE(P.CDIMPOSTONFE,P_PIS,DECODE(I.CDFISCALIT,'1',I.VRBASECALCIT,0),0)) AS VRBASEPIS,
                         SUM(DECODE(P.CDIMPOSTONFE,P_COFINS,DECODE(I.CDFISCALIT,'1',I.VRBASECALCIT,0),0)) AS VRBASECOFINS,
                         SUM(DECODE(P.CDIMPOSTONFE,P_PIS,DECODE(I.CDFISCALIT,'1',I.VRIMPOSTOIT,0),0)) AS VRAJUSTEPIS,
                         SUM(DECODE(P.CDIMPOSTONFE,P_COFINS,DECODE(I.CDFISCALIT,'1',I.VRIMPOSTOIT,0),0)) AS VRAJUSTECOFINS,
                         I.CDFILIAL||I.NRLANCTONF AS CHVNOTA,
                         DECODE(N.IDENTRSAIDOP,'E',N.CDFILIRECE,N.CDFILIEMIT) AS FILIAL,
                         SUM(I.VRCONTITIMP) AS VRCONTITIMP
                    FROM ITEMPROIMPOS I, IMPOSTO P, NOTAFISCAL N, CFOPPADRAO C, ITEMPRODUTO D, RELACPRODITGEN G, FILIAL F,
                         (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                            FROM MAPAAPURAC M, IMPOSTONF I
                           WHERE M.CDEMPRESA    = P_CDEMPRESA
                             AND M.CDIMPOSTO    IN (P_PIS,P_COFINS)
                             AND M.DTINIPERIODO = P_DTINICIAL
                             AND M.DTFIMPERIODO = P_DTFINAL
                             AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU) NF
                   WHERE NF.CDFILIAL    = I.CDFILIAL
                     AND NF.NRLANCTONF  = I.NRLANCTONF
                     AND I.CDIMPOSTO    = P.CDIMPOSTO
                     AND P.CDIMPOSTONFE IN (P_PIS,P_COFINS)
                     AND I.CDFILIAL     = N.CDFILIAL
                     AND I.NRLANCTONF   = N.NRLANCTONF
                     AND C.CDCFOPPADRAO = I.CDFISCOPERIT
                     AND C.IDTIPOCFOP   = 'D'
                     AND I.CDFILIAL     = D.CDFILIAL
                     AND I.NRLANCTONF   = D.NRLANCTONF
                     AND I.NRSEQITPROD  = D.NRSEQITPROD
                     AND D.CDPRODUTO    = G.CDPRODUTO(+)
                     AND G.CDLIVROMAG(+)= P_LIVROMAG
                     AND I.CDFISCALIT   = '1'
                     AND F.CDFILIAL     = DECODE(N.IDENTRSAIDOP,'E',N.CDFILIRECE,N.CDFILIEMIT)
                     AND ((F.IDCUMUPISCOFIL = 'N' AND N.IDENTRSAIDOP = 'E') OR /*APENAS DEVOL. VENDA PARA CUMULATIVO*/
                          (F.IDCUMUPISCOFIL = 'S' AND N.IDENTRSAIDOP IN ('E','S')))
                   GROUP BY N.IDENTRSAIDOP, I.CDFILIAL||I.NRLANCTONF, DECODE(N.IDENTRSAIDOP,'E',N.CDFILIRECE,N.CDFILIEMIT),
                            DECODE(P.CDIMPOSTONFE,P_PIS,I.CDTPCREDPICOITE,''),
                            DECODE(P.CDIMPOSTONFE,P_COFINS,I.CDTPCREDPICOITE,''),
                            DECODE(P.CDIMPOSTONFE,P_PIS,I.VRALIQITEM,''),
                            DECODE(P.CDIMPOSTONFE,P_COFINS,I.VRALIQITEM,''),
                            DECODE(P.CDIMPOSTONFE,P_PIS,I.CDSITUTRIBITNFE,''),
                            DECODE(P.CDIMPOSTONFE,P_COFINS,I.CDSITUTRIBITNFE,''),
                            DECODE(P.CDIMPOSTONFE,P_PIS,I.CDNATUBCALPCIT,''),
                            DECODE(P.CDIMPOSTONFE,P_COFINS,I.CDNATUBCALPCIT,'')
                   UNION ALL
                  SELECT DECODE(P.CDIMPOSTONFE,P_PIS,I.CDTPCREDPICOITE,'') AS CDTPCREDPIS,
                         DECODE(P.CDIMPOSTONFE,P_COFINS,I.CDTPCREDPICOITE,'') AS CDTPCREDCOFINS,
                         DECODE(P.CDIMPOSTONFE,P_PIS,I.VRALIQNF,'') AS VRALIQPIS,
                         DECODE(P.CDIMPOSTONFE,P_COFINS,I.VRALIQNF,'') AS VRALIQCOFINS,
                         DECODE(P.CDIMPOSTONFE,P_PIS,I.CDSITUTRIBITNFE,'') AS CSTPIS,
                         DECODE(P.CDIMPOSTONFE,P_COFINS,I.CDSITUTRIBITNFE,'') AS CSTCOFINS,
                         '' AS CDNATUBCPIS,
                         '' AS CDNATUBCCOFINS,
                         N.IDENTRSAIDOP AS IDAJUSTE,
                         '05' AS CDAJUSTE,
                         SUM(DECODE(P.CDIMPOSTONFE,P_PIS,DECODE(I.CDFISCALNF,'1',I.VRPROPRECBC,0),0)) AS VRBASEPIS,
                         SUM(DECODE(P.CDIMPOSTONFE,P_COFINS,DECODE(I.CDFISCALNF,'1',I.VRPROPRECBC,0),0)) AS VRBASECOFINS,
                         SUM(ROUND(DECODE(P.CDIMPOSTONFE,P_PIS,(I.VRPROPRECBC * I.VRALIQNF/100),0),2)) AS VRAJUSTEPIS,
                         SUM(ROUND(DECODE(P.CDIMPOSTONFE,P_COFINS,(I.VRPROPRECBC * I.VRALIQNF/100),0),2)) AS VRAJUSTECOFINS,
                         I.CDFILIAL||I.NRLANCTONF AS CHVNOTA,
                         DECODE(N.IDENTRSAIDOP,'E',N.CDFILIRECE,N.CDFILIEMIT) AS FILIAL,
                         SUM(I.VRPROPREC) AS VRCONTITIMP
                    FROM IMPBAIXAREC I, IMPOSTO P, NOTAFISCAL N, CFOPPADRAO C, FILIAL F, MAPAAPURAC M
                   WHERE M.CDEMPRESA    = P_CDEMPRESA
                     AND M.CDIMPOSTO    IN (P_PIS,P_COFINS)
                     AND M.DTINIPERIODO = P_DTINICIAL
                     AND M.DTFIMPERIODO = P_DTFINAL
                     AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                     AND I.CDIMPOSTO    = P.CDIMPOSTO
                     AND P.CDIMPOSTONFE IN (P_PIS,P_COFINS)
                     AND I.CDFILIAL     = N.CDFILIAL
                     AND I.NRLANCTONF   = N.NRLANCTONF
                     AND C.CDCFOPPADRAO = I.CDFISCOPERnf
                     AND C.IDTIPOCFOP   = 'D'
                     AND I.CDFISCALNF   = '1'
                     AND F.CDFILIAL     = DECODE(N.IDENTRSAIDOP,'E',N.CDFILIRECE,N.CDFILIEMIT)
                     AND ((F.IDCUMUPISCOFIL = 'N' AND N.IDENTRSAIDOP = 'E') OR /*APENAS DEVOL. VENDA PARA CUMULATIVO*/
                          (F.IDCUMUPISCOFIL = 'S' AND N.IDENTRSAIDOP IN ('E','S')))
                   GROUP BY DECODE(P.CDIMPOSTONFE,P_PIS,I.CDTPCREDPICOITE,''),
                         DECODE(P.CDIMPOSTONFE,P_COFINS,I.CDTPCREDPICOITE,''),
                         DECODE(P.CDIMPOSTONFE,P_PIS,I.VRALIQNF,''),
                         DECODE(P.CDIMPOSTONFE,P_COFINS,I.VRALIQNF,''),
                         DECODE(P.CDIMPOSTONFE,P_PIS,I.CDSITUTRIBITNFE,''),
                         DECODE(P.CDIMPOSTONFE,P_COFINS,I.CDSITUTRIBITNFE,''),
                         N.IDENTRSAIDOP,
                         I.CDFILIAL||I.NRLANCTONF,
                         DECODE(N.IDENTRSAIDOP,'E',N.CDFILIRECE,N.CDFILIEMIT)) LOOP
      IF C_AJUS.VRAJUSTEPIS > 0 THEN
         IF C_AJUS.IDAJUSTE = 'E' THEN
            IF NVL(P_GERADEVOLVEND,'A') = 'A' THEN
               V_SEQAJUST := NVL(V_SEQAJUST,0) + 1;
               INSERT INTO TMP_REGBLOCOM_PIS
                  (CHAVENOTA,CDREGISTRO, CDCONTREGI, CDTPCRED, VRALIQCRED, IDTPALIQ, CSTPISCOFINS, CDNATUBCALPCIT, IDAJUSTE, CDAJUSTE, VRBCCRED, VRAJUSTE, VRTOTNF, CDFILIAL)
               VALUES
                  (C_AJUS.CHVNOTA||'M220', 'M220', V_SEQAJUST, C_AJUS.CDTPCREDPIS, C_AJUS.VRALIQPIS, 'P', C_AJUS.CSTPIS, C_AJUS.CDNATUBCPIS, C_AJUS.IDAJUSTE, C_AJUS.CDAJUSTE, C_AJUS.VRBASEPIS, C_AJUS.VRAJUSTEPIS, C_AJUS.VRCONTITIMP, C_AJUS.FILIAL);
            END IF;
         ELSE
            IF P_CDINCTRIB = '2' THEN /*CUMULATIVO GERA APENAS M200*/
               V_SEQAJUST := NVL(V_SEQAJUST,0) + 1;
               INSERT INTO TMP_REGBLOCOM_PIS
                  (CHAVENOTA,CDREGISTRO, CDCONTREGI, CDTPCRED, VRALIQCRED, IDTPALIQ, CSTPISCOFINS, CDNATUBCALPCIT, IDAJUSTE, CDAJUSTE, VRBCCRED, VRAJUSTE, VRTOTNF, CDFILIAL)
               VALUES
                  (C_AJUS.CHVNOTA||'M220', 'M220', V_SEQAJUST, C_AJUS.CDTPCREDPIS, C_AJUS.VRALIQPIS, 'P', C_AJUS.CSTPIS, C_AJUS.CDNATUBCPIS, C_AJUS.IDAJUSTE, C_AJUS.CDAJUSTE, C_AJUS.VRBASEPIS, C_AJUS.VRAJUSTEPIS, C_AJUS.VRCONTITIMP, C_AJUS.FILIAL);
            ELSE /*NAO-CUMULATIVO GERA COMO ACRESCIMO NO M100 PARA BATER COM A APURACAO*/
               V_SEQAJUST := NVL(V_SEQAJUST,0) + 1;
               INSERT INTO TMP_REGBLOCOM_PIS
                  (CHAVENOTA,CDREGISTRO, CDCONTREGI, CDTPCRED, VRALIQCRED, IDTPALIQ, CSTPISCOFINS, CDNATUBCALPCIT, IDAJUSTE, CDAJUSTE, VRBCCRED, VRAJUSTE, VRTOTNF, CDFILIAL)
               VALUES
                  (C_AJUS.CHVNOTA||'M110', 'M110', V_SEQAJUST, C_AJUS.CDTPCREDPIS, C_AJUS.VRALIQPIS, 'P', C_AJUS.CSTPIS, C_AJUS.CDNATUBCPIS, C_AJUS.IDAJUSTE, C_AJUS.CDAJUSTE, C_AJUS.VRBASEPIS, C_AJUS.VRAJUSTEPIS, C_AJUS.VRCONTITIMP, C_AJUS.FILIAL);
            END IF;
         END IF;
      END IF;
      IF C_AJUS.VRAJUSTECOFINS > 0 THEN
         IF C_AJUS.IDAJUSTE = 'E' THEN
            IF NVL(P_GERADEVOLVEND,'A') = 'A' THEN
               V_SEQAJUST := NVL(V_SEQAJUST,0) + 1;
               INSERT INTO TMP_REGBLOCOM_PIS
                  (CHAVENOTA,CDREGISTRO, CDCONTREGI, CDTPCRED, VRALIQCRED, IDTPALIQ, CSTPISCOFINS, CDNATUBCALPCIT, IDAJUSTE, CDAJUSTE, VRBCCRED, VRAJUSTE, VRTOTNF, CDFILIAL)
               VALUES
                  (C_AJUS.CHVNOTA||'M620', 'M620', V_SEQAJUST, C_AJUS.CDTPCREDCOFINS, C_AJUS.VRALIQCOFINS, 'P', C_AJUS.CSTCOFINS, C_AJUS.CDNATUBCCOFINS, C_AJUS.IDAJUSTE, C_AJUS.CDAJUSTE, C_AJUS.VRBASECOFINS, C_AJUS.VRAJUSTECOFINS, C_AJUS.VRCONTITIMP, C_AJUS.FILIAL);
            END IF;
         ELSE
            IF P_CDINCTRIB = '2' THEN /*CUMULATIVO GERA APENAS M600*/
               V_SEQAJUST := NVL(V_SEQAJUST,0) + 1;
               INSERT INTO TMP_REGBLOCOM_PIS
                  (CHAVENOTA,CDREGISTRO, CDCONTREGI, CDTPCRED, VRALIQCRED, IDTPALIQ, CSTPISCOFINS, CDNATUBCALPCIT, IDAJUSTE, CDAJUSTE, VRBCCRED, VRAJUSTE, VRTOTNF, CDFILIAL)
               VALUES
                  (C_AJUS.CHVNOTA||'M620', 'M620', V_SEQAJUST, C_AJUS.CDTPCREDCOFINS, C_AJUS.VRALIQCOFINS, 'P', C_AJUS.CSTCOFINS, C_AJUS.CDNATUBCCOFINS, C_AJUS.IDAJUSTE, C_AJUS.CDAJUSTE, C_AJUS.VRBASECOFINS, C_AJUS.VRAJUSTECOFINS, C_AJUS.VRCONTITIMP, C_AJUS.FILIAL);
            ELSE /*NAO-CUMULATIVO GERA COMO ACRESCIMO NO M500 PARA BATER COM A APURACAO*/
               V_SEQAJUST := NVL(V_SEQAJUST,0) + 1;
               INSERT INTO TMP_REGBLOCOM_PIS
                  (CHAVENOTA,CDREGISTRO, CDCONTREGI, CDTPCRED, VRALIQCRED, IDTPALIQ, CSTPISCOFINS, CDNATUBCALPCIT, IDAJUSTE, CDAJUSTE, VRBCCRED, VRAJUSTE, VRTOTNF, CDFILIAL)
               VALUES
                  (C_AJUS.CHVNOTA||'M510', 'M510', V_SEQAJUST, C_AJUS.CDTPCREDCOFINS, C_AJUS.VRALIQCOFINS, 'P', C_AJUS.CSTCOFINS, C_AJUS.CDNATUBCCOFINS, C_AJUS.IDAJUSTE, C_AJUS.CDAJUSTE, C_AJUS.VRBASECOFINS, C_AJUS.VRAJUSTECOFINS, C_AJUS.VRCONTITIMP, C_AJUS.FILIAL);
            END IF;
         END IF;
      END IF;
   END LOOP;

   /*BUSCA A PARAMETRIZACAO PARA GERAR AJUSTE AUTOMATICO COM A DIFERENCA DE ARREDONDAMENTO NO CALCULO DO CREDITO PELO VALIDADOR*/
   BEGIN
      SELECT IDGERAJUSAUTSPED, DSGERAJUSAUTSPED, CDAJUSTE
        INTO V_GERAAJUSTEAUTO, V_DESCAJUSTEAUTO, V_CDAJUSTEAUTO
        FROM PARAMFISCAL;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_GERAAJUSTEAUTO := 'N';
         V_DESCAJUSTEAUTO := '';
         V_CDAJUSTEAUTO   := '';
   END;

   /*BUSCA O MAPA DO PIS DO PERIODO*/
   BEGIN
      SELECT NRSEQUMAPAPU
        INTO V_NRMAPAPIS
        FROM MAPAAPURAC
       WHERE DTINIPERIODO = P_DTINICIAL
         AND DTFIMPERIODO = P_DTFINAL
         AND CDEMPRESA    = P_CDEMPRESA
         AND CDIMPOSTO    = P_PIS;
   EXCEPTION
        WHEN NO_DATA_FOUND
        THEN V_NRMAPAPIS := '';
   END;

   /*BUSCA OS VALORES DA TABELA QUE GRAVA SALDO PARA O PROXIMO PERIODO, PARA SABER SE O CREDITO FOI TOTALMENTE UTILIZADO,
     OU, CASO CONTRARIO, QUANTO RESTOU PARA O PERIODO SEGUINTE, PARA PREENCHER O REGISTRO M100*/
   V_VRSALDOFUTURO := 0;
   BEGIN
      SELECT DISTINCT NVL(VRSALDOCREDOR,0)
        INTO V_VRSALDOFUTURO
        FROM CREDANTSPED C
       WHERE C.NRSEQUMAPAPU = V_NRMAPAPIS
         AND C.CDEMPRESA    = P_CDEMPRESA
         AND C.IDTPCREDANT  = 'CA';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_VRSALDOFUTURO := 0;
   END;
   
   /*INICIALIZA AS VARIAVEIS COM VALOR ZERO PARA QUE AS MESMAS NAO FIQUEM NULAS*/
   V_TIPOCREDITO     := NULL;
   V_VRACRESAJUSAUTO := 0;
   V_VRREDUZAJUSAUTO := 0;
   V_CREDUTLIZADO    := 0;

   /*REGISTRO M100*/
   FOR C_M100 IN (SELECT '|M100'                                                                                  ||'|'||
                         CDTPCRED                                                                                 ||'|'|| /*TIPO DE CREDITO*/
                         '0'                                                                                      ||'|'|| /*INDICADOR CREDITO = OP. PROPRIAS*/
                         REPLACE(LTRIM(TO_CHAR(SUM(DECODE(IDTPALIQ,'P',VRBCCRED,0)),'999999999990.00')),'.',',')  ||'|'|| /*BC PIS*/
                         REPLACE(LTRIM(TO_CHAR(DECODE(IDTPALIQ,'P',VRALIQCRED,0),'990.0000')),'.',',')            ||'|'|| /*ALIQ PIS*/
                         REPLACE(LTRIM(TO_CHAR(SUM(DECODE(IDTPALIQ,'Q',VRBCCRED,0)),'999999999990.000')),'.',',') ||'|'|| /*QTDE BC PIS*/
                         REPLACE(LTRIM(TO_CHAR(DECODE(IDTPALIQ,'Q',VRALIQCRED,''),'990.0000')),'.',',')           ||'|' AS REGM100,/*ALIQ PIS QTDE*/
                         CDTPCRED, SUM(VRCREDPIS) AS VRCREDPIS, SUM(VRCRED) AS VRCRED,
                         DECODE(IDTPALIQ,'P',VRALIQCRED, 0) AS VRALIQ_P,
                         DECODE(IDTPALIQ,'Q',VRALIQCRED,'') AS VRALIQ_Q
                    FROM (SELECT VRBCCRED*VRALIQCRED/100 AS VRCREDPIS, CDTPCRED, VRBCCRED, VRALIQCRED, IDTPALIQ, VRCRED
                            FROM (SELECT CDTPCRED, SUM(VRBCCRED) AS VRBCCRED, VRALIQCRED, SUM(VRCRED) AS VRCRED, IDTPALIQ
                                    FROM TMP_REGBLOCOM_PIS
                                   WHERE CDREGISTRO = 'M100'
                                   GROUP BY CDTPCRED, VRALIQCRED, IDTPALIQ))
                   GROUP BY CDTPCRED, IDTPALIQ, VRALIQCRED) LOOP

      /*BUSCA OS VALORES DOS AJUSTES DE CREDITO PARA GERAR O REGISTRO M100*/
      V_VRAJUSTEACRES  := 0;
      V_VRAJUSTEREDUC  := 0;
      V_VRCREDDIFERIDO := 0;

      /*NO CASO DE EXISTIR MAIS DE UMA ALIQUOTA PARA O MESMO TIPO DE CREDITO, OS AJUSTES SERAO GERADOS APENAS PARA A PRIMEIRA ALIQUOTA*/
      BEGIN
         SELECT NVL(SUM(VL_AJUS_ACRES),0) AS VL_AJUS_ACRES, NVL(SUM(VL_AJUS_REDUC),0) AS VL_AJUS_REDUC
           INTO V_VRAJUSTEACRES, V_VRAJUSTEREDUC
           FROM (SELECT SUM(DECODE(A.IDTPAJUSTE,'1',A.VRAJUSTE,0)) AS VL_AJUS_ACRES,
                        SUM(DECODE(A.IDTPAJUSTE,'0',A.VRAJUSTE,0)) AS VL_AJUS_REDUC
                   FROM AJUSTEAPURCONTR A
                  WHERE A.NRSEQUMAPAPU    = V_NRMAPAPIS
                    AND A.IDINDICAJUSTE   = 'C'
                    AND A.CDTPCREDPICOITE = C_M100.CDTPCRED
                    AND ((V_TIPOCREDITO IS NULL) OR (V_TIPOCREDITO <> C_M100.CDTPCRED))
                  UNION ALL
                 SELECT SUM(DECODE(T.IDAJUSTE,'E',T.VRAJUSTE,0)) AS VL_AJUS_ACRES,
                        SUM(DECODE(T.IDAJUSTE,'S',T.VRAJUSTE,0)) AS VL_AJUS_REDUC
                   FROM TMP_REGBLOCOM_PIS T
                  WHERE T.CDREGISTRO = 'M110'
                    AND T.CDTPCRED   = C_M100.CDTPCRED
                    AND DECODE(T.IDTPALIQ,'P',T.VRALIQCRED,0)    = C_M100.VRALIQ_P
                    AND ((DECODE(T.IDTPALIQ,'Q',T.VRALIQCRED,'') = C_M100.VRALIQ_Q) OR (DECODE(T.IDTPALIQ,'Q',T.VRALIQCRED,'') IS NULL AND C_M100.VRALIQ_Q IS NULL)));
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_VRAJUSTEACRES := 0;
            V_VRAJUSTEREDUC := 0;
      END;

      /*BUSCA O VALOR DE CREDITO DIFERIDO NO PERIODO*/
      BEGIN
         SELECT SUM(VRCREDDIFERIDO)
           INTO V_VRCREDDIFERIDO
           FROM CONTROLCREDDIF
          WHERE CDTPCREDITO  = C_M100.CDTPCRED
            AND NRSEQMAPADIF = V_NRMAPAPIS;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_VRCREDDIFERIDO := 0;
      END;

      IF ABS(C_M100.VRCRED - C_M100.VRCREDPIS) > 0.5 THEN
         V_VRCREDITO := C_M100.VRCREDPIS;
         IF V_GERAAJUSTEAUTO = 'S' THEN
            V_VRAJUSTEAUTO := C_M100.VRCRED-C_M100.VRCREDPIS;

            IF V_VRAJUSTEAUTO > 0 THEN
               V_VRAJUSTEACRES   := NVL(V_VRAJUSTEACRES,  0) + NVL(V_VRAJUSTEAUTO,0);
               V_VRACRESAJUSAUTO := NVL(V_VRACRESAJUSAUTO,0) + NVL(V_VRAJUSTEAUTO,0);
            ELSE
               V_VRAJUSTEREDUC   := NVL(V_VRAJUSTEREDUC,  0) + ABS(NVL(V_VRAJUSTEAUTO,0));
               V_VRREDUZAJUSAUTO := NVL(V_VRREDUZAJUSAUTO,0) + ABS(NVL(V_VRAJUSTEAUTO,0));
            END IF;
         END IF;
      ELSE
         V_VRCREDITO := C_M100.VRCRED;
      END IF;

      V_VRTOTACRED := NVL(V_VRCREDITO,0) + NVL(V_VRAJUSTEACRES,0) - NVL(V_VRAJUSTEREDUC,0) - NVL(V_VRCREDDIFERIDO,0);

      /*VERIFICA SE O CREDITO FOI TOTALMENTE UTILIZADO OU FOI UTILIZADO PARCIALMENTE*/
      IF V_VRSALDOFUTURO > 0 THEN
         IF V_VRSALDOFUTURO >= V_VRTOTACRED THEN
            V_CREDUTLIZADO  := 0;
            V_VRSALDOFUTURO := V_VRSALDOFUTURO - NVL(V_VRTOTACRED,0);
         ELSE
            V_CREDUTLIZADO  := NVL(V_VRTOTACRED,0) - V_VRSALDOFUTURO;
            V_VRSALDOFUTURO := 0;
         END IF;
      ELSE
         V_CREDUTLIZADO := NVL(V_VRTOTACRED,0);
      END IF;

      IF V_CREDUTLIZADO = V_VRTOTACRED THEN
         V_IDUTICRED := 0;
      ELSE
         V_IDUTICRED := 1;
      END IF;

      /*SOMA O VALOR DO CREDITO APLICADO O PERCENTUAL PARA GERAR O MESMO VALOR DE CREDITO NO REGISTRO M200*/
      V_VRCREDPIS := NVL(V_VRCREDPIS,0) + NVL(V_CREDUTLIZADO,0);

      /*MONTA  O RESTANTE DO REGISTRO M100 COM OS VALORES APOS O CALCULO*/
      V_REGM100 := REPLACE(LTRIM(TO_CHAR(V_VRCREDITO,'999999999990.00')),'.',',')                 ||'|'|| /*08-CREDITO PIS*/
                   REPLACE(LTRIM(TO_CHAR(V_VRAJUSTEACRES,'999999999990.00')),'.',',')             ||'|'|| /*09-AJUSTE ACRESCIMO*/
                   REPLACE(LTRIM(TO_CHAR(V_VRAJUSTEREDUC,'999999999990.00')),'.',',')             ||'|'|| /*10-AJUSTE REDUCAO*/
                   REPLACE(LTRIM(TO_CHAR(NVL(V_VRCREDDIFERIDO,0),'999999999990.00')),'.',',')      ||'|'|| /*11-CREDITO DIFERIDO*/
                   REPLACE(LTRIM(TO_CHAR(V_VRTOTACRED,'999999999990.00')),'.',',')                ||'|'|| /*CREDITO (08+09-10-11)*/
                   NVL(V_IDUTICRED,'0')                                                           ||'|'|| /*INDICADOR UTILIZACAO DO CREDITO*/
                   REPLACE(LTRIM(TO_CHAR(V_CREDUTLIZADO,'999999999990.00')),'.',',')              ||'|'|| /*VALOR DO CREDITO UTILIZADO*/
                   REPLACE(LTRIM(TO_CHAR(V_VRTOTACRED-V_CREDUTLIZADO,'999999999990.00')),'.',',') ||'|';  /*VALOR DO CREDITO PARA PERIODO FUTURO*/
      V_CONTREGA := NVL(V_CONTREGA,0)+1;

      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'610'||LPAD(RTRIM(V_CONTREGA),22,'0'),C_M100.REGM100||V_REGM100);

      /*REGISTRO M105(APENAS PARA NAO CUMULATIVO)*/
      IF P_CDINCTRIB <> '2' THEN
         FOR REG_M105 IN (SELECT '|M105'                                                                           ||'|'||
                                 CDNATUBCALPCIT                                                                    ||'|'||
                                 CSTPISCOFINS                                                                      ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(SUM(VRBCCRED),'999999999990.00')),'.',',')                  ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(SUM(VRBCCRECUM),'999999999990.00')),'.',',')                ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(SUM(VRBCCRED)-SUM(VRBCCRECUM),'999999999990.00')),'.',',')  ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(SUM(VRBCCRED)-SUM(VRBCCRECUM),'999999999990.00')),'.',',')  ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(SUM(VRBCCREDQT),'999999999990.000')),'.',',')               ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(SUM(VRBCCREDQT),'999999999990.000')),'.',',')               ||'|'||
                                 DECODE(CDNATUBCALPCIT,'13','OUTRAS OPERACOES COM DIREITO A CREDITO','')           ||'|' AS REGM105,
                                 SUM(VRBCCRED) AS VRBCCRED, SUM(VRBCCRECUM) AS VRBCCRECUM
                            FROM (SELECT CDNATUBCALPCIT, CSTPISCOFINS, ROUND(SUM(VRBCCRED),2) AS VRBCCRED, IDTPALIQ,
                                         DECODE(P_CDINCTRIB,'3',DECODE(NVL(NVL(F.IDCUMUPISCOFIL,T.CDFILIAL),'S'),'N',ROUND(SUM(VRBCCRED),2),0),0) AS VRBCCRECUM,
                                         DECODE(IDTPALIQ,'Q',SUM(VRBCCRED),'') AS VRBCCREDQT
                                    FROM TMP_REGBLOCOM_PIS T, FILIAL F
                                   WHERE T.CDFILIAL          = F.CDFILIAL(+)
                                     AND NVL(T.CDTPCRED,'T') = NVL(C_M100.CDTPCRED,'T') /*CASO OS CAMPOS ESTEJAM NULOS COMPARO T COM T PARA QUE O REGISTRO SEJA GERADO MESMO ASSIM*/
                                     AND DECODE(T.IDTPALIQ,'P',T.VRALIQCRED,0)    = C_M100.VRALIQ_P
                                     AND ((DECODE(T.IDTPALIQ,'Q',T.VRALIQCRED,'') = C_M100.VRALIQ_Q) OR (DECODE(T.IDTPALIQ,'Q',T.VRALIQCRED,'') IS NULL AND C_M100.VRALIQ_Q IS NULL))
                                     AND T.CDREGISTRO        = 'M100'
                                   GROUP BY CDNATUBCALPCIT, CSTPISCOFINS, NVL(F.IDCUMUPISCOFIL,T.CDFILIAL), IDTPALIQ)
                           GROUP BY CDNATUBCALPCIT,CSTPISCOFINS,DECODE(CDNATUBCALPCIT,'13','OUTRAS OPERACOES COM DIREITO A CREDITO',''))LOOP

            V_CONTREGA:= NVL(V_CONTREGA,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'610'||LPAD(RTRIM(V_CONTREGA),22,'0'),REG_M105.REGM105);
         END LOOP;
      END IF;

      /*REGISTRO M110*/
      IF (ABS(C_M100.VRCRED - C_M100.VRCREDPIS) > 0.5) AND (V_GERAAJUSTEAUTO = 'S') THEN
         V_REGM110 := '|M110'                                                                ||'|'||
                      CASE WHEN V_VRAJUSTEAUTO > 0 THEN 1 ELSE 0 END                         ||'|'||
                      REPLACE(LTRIM(TO_CHAR(ABS(V_VRAJUSTEAUTO),'999999999990.00')),'.',',') ||'|'||
                      V_CDAJUSTEAUTO                                                         ||'|'||
                      ''                                                                     ||'|'||
                      V_DESCAJUSTEAUTO                                                       ||'|'||
                      ''                                                                     ||'|';
         V_CONTREGA:= NVL(V_CONTREGA,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'610'||LPAD(RTRIM(V_CONTREGA),22,'0'),V_REGM110);
      END IF;

      FOR C_M110 IN (SELECT '|M110'                                                          ||'|'||
                            IDAJUSTE                                                         ||'|'||
                            REPLACE(LTRIM(TO_CHAR(SUM(VRAJUSTE),'999999999990.00')),'.',',') ||'|'||
                            CDAJUSTE                                                         ||'|'||
                            NUMDOC                                                           ||'|'||
                            DSAJUSTE                                                         ||'|'||
                            TO_CHAR(DTAJUSTE,'DDMMYYYY')                                     ||'|' AS REGM110
                       FROM (SELECT DECODE(R.IDAJUSTE,'S','0','1') AS IDAJUSTE, R.VRAJUSTE, R.CDAJUSTE,
                                    N.NRNOTAFISC AS NUMDOC, T.NMTIPOOPER||' - '||N.NRINSJURNF AS DSAJUSTE,
                                    DECODE(N.IDENTRSAIDOP,'E',N.DTENTRSAID,N.DTEMISSAO) AS DTAJUSTE
                               FROM TMP_REGBLOCOM_PIS R, NOTAFISCAL N, TIPOOPERAC T
                              WHERE R.CDREGISTRO   = 'M110'
                                AND NVL(R.CDTPCRED,'T') = NVL(C_M100.CDTPCRED,'T') /*CASO OS CAMPOS ESTEJAM NULOS COMPARO T COM T PARA QUE O REGISTRO SEJA GERADO MESMO ASSIM*/
                                AND DECODE(R.IDTPALIQ,'P',R.VRALIQCRED,0)    = C_M100.VRALIQ_P
                                AND ((DECODE(R.IDTPALIQ,'Q',R.VRALIQCRED,'') = C_M100.VRALIQ_Q) OR (DECODE(R.IDTPALIQ,'Q',R.VRALIQCRED,'') IS NULL AND C_M100.VRALIQ_Q IS NULL))
                                AND N.CDFILIAL     = SUBSTR(R.CHAVENOTA,1,4)
                                AND N.NRLANCTONF   = SUBSTR(R.CHAVENOTA,5,10)
                                AND N.CDTIPOOPER   = T.CDTIPOOPER
                                AND N.IDENTRSAIDOP = T.IDENTRSAIDOP
                              UNION ALL
                             SELECT A.IDTPAJUSTE AS IDAJUSTE, A.VRAJUSTE, A.CDAJUSTE,
                                    A.NRPROCESSO AS NUMDOC, A.DSAJUSTE, A.DTAJUSTE
                               FROM AJUSTEAPURCONTR A
                              WHERE A.NRSEQUMAPAPU    = V_NRMAPAPIS
                                AND A.IDINDICAJUSTE   = 'C'
                                AND A.CDTPCREDPICOITE = C_M100.CDTPCRED
                                AND ((V_TIPOCREDITO IS NULL) OR (V_TIPOCREDITO <> C_M100.CDTPCRED)))
                      GROUP BY IDAJUSTE, CDAJUSTE, NUMDOC, DSAJUSTE, DTAJUSTE) LOOP

         V_CONTREGA:= NVL(V_CONTREGA,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'610'||LPAD(RTRIM(V_CONTREGA),22,'0'),C_M110.REGM110);
      END LOOP;

      V_TIPOCREDITO := C_M100.CDTPCRED;
   END LOOP;

   /*Gera os Registros M100 e M110 quando existirem ajustes para o tipo de credito, mas nao houver movimentacao para o mesmo*/
   FOR C_M100_AJ IN (SELECT '|M100'              ||'|'||
                            AJ.CDTPCREDPICOITE   ||'|'|| /*02-TIPO DE CREDITO*/
                            '0'                  ||'|'|| /*03-INDICADOR CREDITO = OP. PROPRIAS*/
                            '0,00'               ||'|'|| /*04-BC PIS*/
                            '0,0000'             ||'|'|| /*05-ALIQ PIS*/
                            ''                   ||'|'|| /*06-QTDE BC PIS*/
                            ''                   ||'|'|| /*07-ALIQ PIS QTDE*/
                            '0,00'               ||'|'|| /*08-CREDITO PIS*/
                            REPLACE(LTRIM(TO_CHAR(NVL(AJ.VRAJUSTEACRES,0),'999999999990.00')),'.',',') ||'|'|| /*09-AJUSTE ACRESCIMO*/
                            REPLACE(LTRIM(TO_CHAR(NVL(AJ.VRAJUSTEREDUZ,0),'999999999990.00')),'.',',') ||'|'|| /*10-AJUSTE REDUCAO*/
                            '0,00'               ||'|'|| /*11-CREDITO DIFERIDO*/
                            REPLACE(LTRIM(TO_CHAR(AJ.VRAJUSTEACRES - AJ.VRAJUSTEREDUZ,'999999999990.00')),'.',',') ||'|' AS REGM100, /*CREDITO (08+09-10-11)*/
                            AJ.VRAJUSTEACRES, AJ.VRAJUSTEREDUZ, AJ.CDTPCREDPICOITE
                       FROM (SELECT SUM(DECODE(A.IDTPAJUSTE,'1',A.VRAJUSTE,0)) AS VRAJUSTEACRES,
                                    SUM(DECODE(A.IDTPAJUSTE,'0',A.VRAJUSTE,0)) AS VRAJUSTEREDUZ,
                                    A.CDTPCREDPICOITE
                               FROM AJUSTEAPURCONTR A
                              WHERE A.NRSEQUMAPAPU    = V_NRMAPAPIS
                                AND A.IDINDICAJUSTE   = 'C'
                                AND A.CDTPCREDPICOITE NOT IN (SELECT CDTPCRED
                                                                FROM TMP_REGBLOCOM_PIS
                                                               WHERE CDREGISTRO = 'M100')
                              GROUP BY A.CDTPCREDPICOITE)AJ) LOOP

      V_VRTOTACRED := NVL(C_M100_AJ.VRAJUSTEACRES,0) - NVL(C_M100_AJ.VRAJUSTEREDUZ,0);

      /*VERIFICA SE O CREDITO FOI TOTALMENTE UTILIZADO OU FOI UTILIZADO PARCIALMENTE*/
      IF V_VRSALDOFUTURO > 0 THEN
         IF V_VRSALDOFUTURO >= V_VRTOTACRED THEN
            V_CREDUTLIZADO  := 0;
            V_VRSALDOFUTURO := V_VRSALDOFUTURO - NVL(V_VRTOTACRED,0);
         ELSE
            V_CREDUTLIZADO  := NVL(V_VRTOTACRED,0) - V_VRSALDOFUTURO;
            V_VRSALDOFUTURO := 0;
         END IF;
      ELSE
         V_CREDUTLIZADO := NVL(V_VRTOTACRED,0);
      END IF;

      IF V_CREDUTLIZADO = V_VRTOTACRED THEN
         V_IDUTICRED := 0;
      ELSE
         V_IDUTICRED := 1;
      END IF;

      /*SOMA O VALOR DO CREDITO APLICADO O PERCENTUAL PARA GERAR O MESMO VALOR DE CREDITO NO REGISTRO M200*/
      V_VRCREDPIS := NVL(V_VRCREDPIS,0) + NVL(V_CREDUTLIZADO,0);

      /*MONTA  O RESTANTE DO REGISTRO M100 COM OS VALORES APOS O CALCULO*/
      V_REGM100 := NVL(V_IDUTICRED,'0')                                                           ||'|'|| /*INDICADOR UTILIZACAO DO CREDITO*/
                   REPLACE(LTRIM(TO_CHAR(V_CREDUTLIZADO,'999999999990.00')),'.',',')              ||'|'|| /*VALOR DO CREDITO UTILIZADO*/
                   REPLACE(LTRIM(TO_CHAR(V_VRTOTACRED-V_CREDUTLIZADO,'999999999990.00')),'.',',') ||'|';  /*VALOR DO CREDITO PARA PERIODO FUTURO*/
      V_CONTREGA := NVL(V_CONTREGA,0)+1;

      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'610'||LPAD(RTRIM(V_CONTREGA),22,'0'),C_M100_AJ.REGM100||V_REGM100);

      FOR C_M110_AJ IN (SELECT '|M110'                                                          ||'|'||
                               IDAJUSTE                                                         ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(VRAJUSTE),'999999999990.00')),'.',',') ||'|'||
                               CDAJUSTE                                                         ||'|'||
                               NUMDOC                                                           ||'|'||
                               DSAJUSTE                                                         ||'|'||
                               TO_CHAR(DTAJUSTE,'DDMMYYYY')                                     ||'|' AS REGM110
                          FROM (SELECT A.IDTPAJUSTE AS IDAJUSTE, A.VRAJUSTE, A.CDAJUSTE,
                                       A.NRPROCESSO AS NUMDOC, A.DSAJUSTE, A.DTAJUSTE
                                  FROM AJUSTEAPURCONTR A
                                 WHERE A.NRSEQUMAPAPU    = V_NRMAPAPIS
                                   AND A.IDINDICAJUSTE   = 'C'
                                   AND A.CDTPCREDPICOITE = C_M100_AJ.CDTPCREDPICOITE)
                         GROUP BY IDAJUSTE, CDAJUSTE, NUMDOC, DSAJUSTE, DTAJUSTE) LOOP

         V_CONTREGA:= NVL(V_CONTREGA,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'610'||LPAD(RTRIM(V_CONTREGA),22,'0'),C_M110_AJ.REGM110);
      END LOOP;
   END LOOP;

   V_CDCONTRIB         := NULL;
   V_VRAJUSTENCUM      := 0;
   V_VRAJUSTECUMU      := 0;
   V_VRDIFERIMENTONCUM := 0;
   V_VRDIFERIMENTOCUMU := 0;

   /*REGISTRO M210*/
   FOR REGM210 IN (SELECT '|M210'   AS REG,
                          CDCONTRI  AS COD_CONT,
                          NVL(SUM(VRRECEITA),0) AS VL_REC_BRT,
                          NVL(DECODE(IDTPALIQ,'P',SUM(VRBCCONTRI),''),0) AS VL_BC_CONT,
                          NVL(DECODE(IDTPALIQ,'P',VRALIQCRED,''),'') AS ALIQ_PIS,
                          NVL(DECODE(IDTPALIQ,'Q',SUM(VRBCCONTRI),''),'') AS QUANT_BC_PIS, /*BC POR QUANTIDADE*/
                          NVL(DECODE(IDTPALIQ,'Q',VRALIQCRED,''),'') AS ALIQ_PIS_QUANT, /*ALIQUOTA POR QUANTIDADE*/
                          NVL(SUM(VRCONTRI),0) AS VL_CONT_APUR,
                          CDCONTRI, IDTPALIQ, VRALIQCRED, IDCUMUPISCOFIL, SUM(VRCONTRI) AS VRCONTRIBUICAO
                     FROM (/*ALIQUOTA BASICA NAO-CUMULATIVA*/
                           SELECT '01' AS CDCONTRI, SUM(VRTOTNF) AS VRRECEITA ,SUM(VRBCCRED) AS VRBCCONTRI, VRALIQCRED, SUM(VRCRED) AS VRCONTRI,IDTPALIQ,
                                  NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) AS IDCUMUPISCOFIL, CSTPISCOFINS
                             FROM TMP_REGBLOCOM_PIS T, FILIAL F
                            WHERE T.CDREGISTRO   = 'M200'
                              AND T.CDFILIAL     = F.CDFILIAL(+)
                              AND NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) = 'S'
                              AND T.CSTPISCOFINS = '01'
                            GROUP BY VRALIQCRED, IDTPALIQ, NVL(F.IDCUMUPISCOFIL,T.CDFILIAL), CSTPISCOFINS
                            UNION ALL /*ALIQUOTAS DIFERENCIADAS NAO-CUMULATIVA*/
                           SELECT '02' AS CDCONTRI, SUM(VRTOTNF) AS VRRECEITA ,SUM(VRBCCRED) AS VRBCCONTRI, VRALIQCRED, SUM(VRCRED) AS VRCONTRI,IDTPALIQ,
                                  NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) AS IDCUMUPISCOFIL, CSTPISCOFINS
                             FROM TMP_REGBLOCOM_PIS T, FILIAL F
                            WHERE T.CDREGISTRO   = 'M200'
                              AND T.CDFILIAL     = F.CDFILIAL(+)
                              AND NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) = 'S'
                              AND T.CSTPISCOFINS =  '02'
                            GROUP BY VRALIQCRED, IDTPALIQ, NVL(F.IDCUMUPISCOFIL,T.CDFILIAL), CSTPISCOFINS
                            UNION ALL /*ALIQUOTA BASICA CUMULATIVA*/
                           SELECT '51' AS CDCONTRI, SUM(VRTOTNF) AS VRRECEITA ,SUM(VRBCCRED) AS VRBCCONTRI, VRALIQCRED, SUM(VRCRED) AS VRCONTRI,IDTPALIQ,
                                  NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) AS IDCUMUPISCOFIL, CSTPISCOFINS
                             FROM TMP_REGBLOCOM_PIS T, FILIAL F
                            WHERE T.CDREGISTRO   = 'M200'
                              AND T.CDFILIAL     = F.CDFILIAL(+)
                              AND NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) = 'N'
                              AND T.CSTPISCOFINS = '01'
                            GROUP BY VRALIQCRED, IDTPALIQ, NVL(F.IDCUMUPISCOFIL,T.CDFILIAL), CSTPISCOFINS
                            UNION ALL /*ALIQUOTA DIFERENCIADA CUMULATIVA*/
                           SELECT '52' AS CDCONTRI, SUM(VRTOTNF) AS VRRECEITA ,SUM(VRBCCRED) AS VRBCCONTRI, VRALIQCRED, SUM(VRCRED) AS VRCONTRI,IDTPALIQ,
                                  NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) AS IDCUMUPISCOFIL, CSTPISCOFINS
                             FROM TMP_REGBLOCOM_PIS T, FILIAL F
                            WHERE T.CDREGISTRO   = 'M200'
                              AND T.CDFILIAL     = F.CDFILIAL(+)
                              AND NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) = 'N'
                              AND T.CSTPISCOFINS = '02'
                            GROUP BY VRALIQCRED, IDTPALIQ, NVL(F.IDCUMUPISCOFIL,T.CDFILIAL), CSTPISCOFINS
                            UNION ALL /*CONTRIBUICAO NAO-CUMULATIVA APURADA A ALIQUOTA POR UNIDADE DE MEDIDA DE PRODUTO*/
                           SELECT '03' AS CDCONTRI, SUM(VRTOTNF) AS VRRECEITA ,SUM(VRBCCRED) AS VRBCCONTRI, VRALIQCRED, SUM(VRCRED) AS VRCONTRI,IDTPALIQ,
                                   NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) AS IDCUMUPISCOFIL, CSTPISCOFINS
                             FROM TMP_REGBLOCOM_PIS T, FILIAL F
                            WHERE T.CDREGISTRO   = 'M200'
                              AND T.CDFILIAL     = F.CDFILIAL(+)
                              AND NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) = 'S'
                              AND T.CSTPISCOFINS = '03'
                            GROUP BY VRALIQCRED, IDTPALIQ, NVL(F.IDCUMUPISCOFIL,T.CDFILIAL), CSTPISCOFINS
                            UNION ALL /*CONTRIBUICAO CUMULATIVA APURADA POR SUBSTITUICAO TRIBUTARIA*/
                           SELECT '31' AS CDCONTRI, SUM(VRTOTNF) AS VRRECEITA ,SUM(VRBCCRED) AS VRBCCONTRI, VRALIQCRED, SUM(VRCRED) AS VRCONTRI,IDTPALIQ,
                                  NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) AS IDCUMUPISCOFIL, CSTPISCOFINS
                             FROM TMP_REGBLOCOM_PIS T, FILIAL F
                            WHERE T.CDREGISTRO   = 'M200'
                              AND T.CDFILIAL     = F.CDFILIAL(+)
                              AND NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) = 'N'
                              AND T.CSTPISCOFINS = '05'
                            GROUP BY VRALIQCRED, IDTPALIQ, NVL(F.IDCUMUPISCOFIL,T.CDFILIAL), CSTPISCOFINS
                            UNION ALL /*CONTRIBUICAO NAO-CUMULATIVA APURADA POR SUBSTITUICAO TRIBUTARIA*/
                           SELECT '32' AS CDCONTRI, SUM(VRTOTNF) AS VRRECEITA ,SUM(VRBCCRED) AS VRBCCONTRI, VRALIQCRED, SUM(VRCRED) AS VRCONTRI,IDTPALIQ,
                                  NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) AS IDCUMUPISCOFIL, CSTPISCOFINS
                             FROM TMP_REGBLOCOM_PIS T, FILIAL F
                            WHERE T.CDREGISTRO   = 'M200'
                              AND T.CDFILIAL     = F.CDFILIAL(+)
                              AND NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) = 'S'
                              AND T.CSTPISCOFINS = '05'
                            GROUP BY VRALIQCRED, IDTPALIQ, NVL(F.IDCUMUPISCOFIL,T.CDFILIAL), CSTPISCOFINS
                            UNION ALL /*CONTRIBUICAO CUMULATIVA APURADA A ALIQUOTA POR UNIDADE DE MEDIDA DE PRODUTO*/
                           SELECT '53' AS CDCONTRI, SUM(VRTOTNF) AS VRRECEITA ,SUM(VRBCCRED) AS VRBCCONTRI, VRALIQCRED, SUM(VRCRED) AS VRCONTRI,IDTPALIQ,
                                  NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) AS IDCUMUPISCOFIL, CSTPISCOFINS
                             FROM TMP_REGBLOCOM_PIS T, FILIAL F
                            WHERE T.CDREGISTRO   = 'M200'
                              AND T.CDFILIAL     = F.CDFILIAL(+)
                              AND NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) = 'N'
                              AND T.CSTPISCOFINS = '03'
                            GROUP BY VRALIQCRED, IDTPALIQ, NVL(F.IDCUMUPISCOFIL,T.CDFILIAL), CSTPISCOFINS)
                    GROUP BY IDTPALIQ, VRALIQCRED, CDCONTRI, IDCUMUPISCOFIL) LOOP

      V_VRBCAJUSTEACRES := 0;
      V_VRAJUSTEACRESBC := 0;
      V_VRAJUSTEACRES   := 0;
      V_VRBCAJUSTEREDUC := 0;
      V_VRAJUSTEREDUCBC := 0;
      V_VRAJUSTEREDUC   := 0;

      BEGIN
         SELECT SUM(VRBCACRESCIMO) AS VRBCACRESCIMO, SUM(VRACRESCIMOBC) AS VRACRESCIMOBC, SUM(VRACRESCIMO) AS VRACRESCIMO, 
                SUM(VRBCREDUCAO) AS VRBCREDUCAO, SUM(VRREDUCAOBC) AS VRREDUCAOBC, SUM(VRREDUCAO) AS VRREDUCAO 
           INTO V_VRBCAJUSTEACRES, V_VRAJUSTEACRESBC, V_VRAJUSTEACRES, V_VRBCAJUSTEREDUC, V_VRAJUSTEREDUCBC, V_VRAJUSTEREDUC 
           FROM (SELECT CASE WHEN P_VERSAO >= '005' THEN SUM(DECODE(IDAJUSTE,'S',VRBCCRED,0)) ELSE 0 END AS VRBCACRESCIMO,
                        CASE WHEN P_VERSAO >= '005' THEN SUM(DECODE(IDAJUSTE,'S',VRAJUSTE,0)) ELSE 0 END AS VRACRESCIMOBC,
                        CASE WHEN P_VERSAO >= '005' THEN 0 ELSE SUM(DECODE(IDAJUSTE,'S',VRAJUSTE,0)) END AS VRACRESCIMO,
                        CASE WHEN P_VERSAO >= '005' THEN SUM(DECODE(IDAJUSTE,'E',VRBCCRED,0)) ELSE 0 END AS VRBCREDUCAO,
                        CASE WHEN P_VERSAO >= '005' THEN SUM(DECODE(IDAJUSTE,'E',VRAJUSTE,0)) ELSE 0 END AS VRREDUCAOBC,
                        CASE WHEN P_VERSAO >= '005' THEN 0 ELSE SUM(DECODE(IDAJUSTE,'E',VRAJUSTE,0)) END AS VRREDUCAO
                   FROM TMP_REGBLOCOM_PIS T, FILIAL F
                  WHERE T.CDREGISTRO   = 'M220'
                    AND T.CDFILIAL     = F.CDFILIAL(+)
                    AND T.CSTPISCOFINS IN ('49','98','99')
                    AND ((DECODE(VRALIQCRED,1.65,'01','02') = REGM210.CDCONTRI) OR
                         (DECODE(VRALIQCRED,0.65,'51','52') = REGM210.CDCONTRI))
                    AND NVL(F.IDCUMUPISCOFIL,T.CDFILIAL)  = REGM210.IDCUMUPISCOFIL
                    AND T.IDTPALIQ     = REGM210.IDTPALIQ
                    AND T.VRALIQCRED   = REGM210.VRALIQCRED
                  UNION ALL
                 SELECT SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DB',VRBASECALCAJ,0),0)) AS VRBCACRESCIMO,
                        SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DB',VRAJUSTE,0),0)) AS VRACRESCIMOBC,
                        SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DI',VRAJUSTE,0),0)) AS VRACRESCIMO,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DB',VRBASECALCAJ,0),0)) AS VRBCREDUCAO,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DB',VRAJUSTE,0),0)) AS VRREDUCAOBC,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DI',VRAJUSTE,0),0)) AS VRREDUCAO
                   FROM AJUSTEAPURCONTR A
                  WHERE A.NRSEQUMAPAPU    = V_NRMAPAPIS
                    AND SUBSTR(A.IDINDICAJUSTE,1,1) = 'D'
                    AND A.IDCUMUPISCOFIL  = REGM210.IDCUMUPISCOFIL
                    AND A.CDSITUTRIBITNFE IN ('01','49','98','99')
                    AND REGM210.CDCONTRI  = DECODE(A.IDCUMUPISCOFIL,'N','51','01')
                    AND ((V_CDCONTRIB IS NULL) OR (V_CDCONTRIB <> REGM210.CDCONTRI))
                  UNION ALL
                 SELECT SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DB',VRBASECALCAJ,0),0)) AS VRBCACRESCIMO,
                        SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DB',VRAJUSTE,0),0)) AS VRACRESCIMOBC,
                        SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DI',VRAJUSTE,0),0)) AS VRACRESCIMO,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DB',VRBASECALCAJ,0),0)) AS VRBCREDUCAO,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DB',VRAJUSTE,0),0)) AS VRREDUCAOBC,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DI',VRAJUSTE,0),0)) AS VRREDUCAO
                   FROM AJUSTEAPURCONTR A
                  WHERE A.NRSEQUMAPAPU    = V_NRMAPAPIS
                    AND SUBSTR(A.IDINDICAJUSTE,1,1) = 'D'
                    AND A.IDCUMUPISCOFIL  = REGM210.IDCUMUPISCOFIL
                    AND A.CDSITUTRIBITNFE = '02'
                    AND REGM210.CDCONTRI  = DECODE(A.IDCUMUPISCOFIL,'N','52','02')
                    AND ((V_CDCONTRIB IS NULL) OR (V_CDCONTRIB <> REGM210.CDCONTRI))
                  UNION ALL
                 SELECT SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DB',VRBASECALCAJ,0),0)) AS VRBCACRESCIMO,
                        SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DB',VRAJUSTE,0),0)) AS VRACRESCIMOBC,
                        SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DI',VRAJUSTE,0),0)) AS VRACRESCIMO,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DB',VRBASECALCAJ,0),0)) AS VRBCREDUCAO,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DB',VRAJUSTE,0),0)) AS VRREDUCAOBC,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DI',VRAJUSTE,0),0)) AS VRREDUCAO
                   FROM AJUSTEAPURCONTR A
                  WHERE A.NRSEQUMAPAPU    = V_NRMAPAPIS
                    AND SUBSTR(A.IDINDICAJUSTE,1,1) = 'D'
                    AND A.IDCUMUPISCOFIL  = REGM210.IDCUMUPISCOFIL
                    AND A.CDSITUTRIBITNFE = '03'
                    AND REGM210.CDCONTRI  = DECODE(A.IDCUMUPISCOFIL,'N','53','03')
                    AND ((V_CDCONTRIB IS NULL) OR (V_CDCONTRIB <> REGM210.CDCONTRI))
                  UNION ALL
                 SELECT SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DB',VRBASECALCAJ,0),0)) AS VRBCACRESCIMO,
                        SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DB',VRAJUSTE,0),0)) AS VRACRESCIMOBC,
                        SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DI',VRAJUSTE,0),0)) AS VRACRESCIMO,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DB',VRBASECALCAJ,0),0)) AS VRBCREDUCAO,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DB',VRAJUSTE,0),0)) AS VRREDUCAOBC,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DI',VRAJUSTE,0),0)) AS VRREDUCAO
                   FROM AJUSTEAPURCONTR A
                  WHERE A.NRSEQUMAPAPU    = V_NRMAPAPIS
                    AND SUBSTR(A.IDINDICAJUSTE,1,1) = 'D'
                    AND A.IDCUMUPISCOFIL  = REGM210.IDCUMUPISCOFIL
                    AND A.CDSITUTRIBITNFE = '05'
                    AND REGM210.CDCONTRI  = DECODE(A.IDCUMUPISCOFIL,'N','31','32')
                    AND ((V_CDCONTRIB IS NULL) OR (V_CDCONTRIB <> REGM210.CDCONTRI)));
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_VRBCAJUSTEACRES := 0;
            V_VRAJUSTEACRESBC := 0;
            V_VRAJUSTEACRES   := 0;
            V_VRBCAJUSTEREDUC := 0;
            V_VRAJUSTEREDUCBC := 0;
            V_VRAJUSTEREDUC   := 0;
      END;

      IF REGM210.IDCUMUPISCOFIL = 'N' THEN
         V_VRAJUSTECUMU := NVL(V_VRAJUSTECUMU,0) + NVL(V_VRAJUSTEACRES,0) - NVL(V_VRAJUSTEREDUC,0) + NVL(V_VRAJUSTEACRESBC,0) - NVL(V_VRAJUSTEREDUCBC,0);
      ELSE
         V_VRAJUSTENCUM := NVL(V_VRAJUSTENCUM,0) + NVL(V_VRAJUSTEACRES,0) - NVL(V_VRAJUSTEREDUC,0) + NVL(V_VRAJUSTEACRESBC,0) - NVL(V_VRAJUSTEREDUCBC,0);
      END IF;

      V_VRCONTRIBDIF    := 0;
      V_VRCONTRIBDIFANT := 0;

      FOR C_DIF IN (SELECT SUM(C.VRCONTRIBDIF) AS VRCONTRIBDIF, SUM(C.VRCONTRIBDIFANT) AS VRCONTRIBDIFANT, F.IDCUMUPISCOFIL
                      FROM CONTRIBDIFERIDA C, NOTAFISCAL N, FILIAL F
                     WHERE C.NRSEQMAPADIF  = V_NRMAPAPIS
                       AND C.CDFILIAL      = N.CDFILIAL
                       AND C.NRLANCTONF    = N.NRLANCTONF
                       AND N.CDFILIEMIT    = F.CDFILIAL
                       AND C.CDCONTRIBAPUR = REGM210.CDCONTRI
                     GROUP BY F.IDCUMUPISCOFIL) LOOP

         V_VRCONTRIBDIF    := NVL(V_VRCONTRIBDIF,   0) + C_DIF.VRCONTRIBDIF;
         V_VRCONTRIBDIFANT := NVL(V_VRCONTRIBDIFANT,0) + C_DIF.VRCONTRIBDIFANT;

         IF C_DIF.IDCUMUPISCOFIL = 'N' THEN
            V_VRDIFERIMENTOCUMU := NVL(V_VRDIFERIMENTOCUMU,0) - NVL(C_DIF.VRCONTRIBDIF,0) + NVL(C_DIF.VRCONTRIBDIFANT,0);
         ELSE
            V_VRDIFERIMENTONCUM := NVL(V_VRDIFERIMENTONCUM,0) - NVL(C_DIF.VRCONTRIBDIF,0) + NVL(C_DIF.VRCONTRIBDIFANT,0);
         END IF;
      END LOOP;

      V_REGM210 := REGM210.REG      ||'|'||
                   REGM210.COD_CONT ||'|'||
                   REPLACE(LTRIM(TO_CHAR(NVL(REGM210.VL_REC_BRT,0),'999999999990.00')),'.',',')       ||'|'||
                   REPLACE(LTRIM(TO_CHAR(NVL(REGM210.VL_BC_CONT,0),'999999999990.00')),'.',',')       ||
                   CASE WHEN P_VERSAO >= '005' THEN
                      '|'||REPLACE(LTRIM(TO_CHAR(NVL(V_VRBCAJUSTEACRES,0),'999999999990.00')),'.',',')||'|'||
                      REPLACE(LTRIM(TO_CHAR(NVL(V_VRBCAJUSTEREDUC,0),'999999999990.00')),'.',',')     ||'|'||
                      REPLACE(LTRIM(TO_CHAR(NVL(REGM210.VL_BC_CONT,0) + NVL(V_VRBCAJUSTEACRES,0) - NVL(V_VRBCAJUSTEREDUC,0),'999999999990.00')),'.',',')
                   ELSE '' END      ||'|'||
                   REPLACE(LTRIM(TO_CHAR(NVL(REGM210.ALIQ_PIS,''),'990.0000')),'.',',')               ||'|'||
                   REPLACE(LTRIM(TO_CHAR(NVL(REGM210.QUANT_BC_PIS,''),'999999999990.000')),'.',',')   ||'|'||/*BC POR QUANTIDADE*/
                   REPLACE(LTRIM(TO_CHAR(NVL(REGM210.ALIQ_PIS_QUANT,''),'990.0000')),'.',',')         ||'|'||/*ALIQUOTA POR QUANTIDADE*/
                   REPLACE(LTRIM(TO_CHAR(NVL(REGM210.VL_CONT_APUR,0) + NVL(V_VRAJUSTEACRESBC,0) - NVL(V_VRAJUSTEREDUCBC,0),'999999999990.00')),'.',',')     ||'|'||
                   REPLACE(LTRIM(TO_CHAR(NVL(V_VRAJUSTEACRES,0),'999999999990.00')),'.',',')          ||'|'||/*AJUSTE A ACRESCIMO*/
                   REPLACE(LTRIM(TO_CHAR(NVL(V_VRAJUSTEREDUC,0),'999999999990.00')),'.',',')          ||'|'||/*AJUSTE A REDUCAO*/
                   REPLACE(LTRIM(TO_CHAR(NVL(V_VRCONTRIBDIF,0),'999999999990.00')),'.',',')           ||'|'||/*VALOR CONTRI. A DIFERIR*/
                   REPLACE(LTRIM(TO_CHAR(NVL(V_VRCONTRIBDIFANT,0),'999999999990.00')),'.',',')        ||'|'||/*VALOR CONTRI. DIFERIDA PER. ANT*/
                   REPLACE(LTRIM(TO_CHAR(NVL(REGM210.VRCONTRIBUICAO,0)+NVL(V_VRAJUSTEACRESBC,0)-NVL(V_VRAJUSTEREDUCBC,0)+NVL(V_VRAJUSTEACRES,0)-NVL(V_VRAJUSTEREDUC,0)-NVL(V_VRCONTRIBDIF,0)+NVL(V_VRCONTRIBDIFANT,0),'999999999990.00')),'.',',')||'|';

      V_CONTREGA := NVL(V_CONTREGA,0)+1;
      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'622'||LPAD(RTRIM(V_CONTREGA),22,'0'),V_REGM210);

      IF P_VERSAO >= '005' THEN
         /*REGISTRO M215*/
         FOR REG_M215 IN (SELECT '|M215'                                                            ||'|'||
                                 IND_AJ_BC                                                          ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(NVL(VL_AJ_BC,0),'999999999990.00')),'.',',') ||'|'||
                                 COD_AJ_BC                                                          ||'|'||
                                 NUM_DOC                                                            ||'|'||
                                 DESCR_AJ_BC                                                        ||'|'||
                                 TO_CHAR(DT_REF,'DDMMYYYY')                                         ||'|'||
                                 COD_CTA                                                            ||'|'||
                                 CNPJ                                                               ||'|'||
                                 INFO_COMPL                                                         ||'|' AS REGM215,
                                 CNPJ, COD_CTA
                            FROM (SELECT DECODE(T.IDAJUSTE,'S','1','0') AS IND_AJ_BC,
                                         NVL(T.VRBCCRED,0) AS VL_AJ_BC,
                                         '02' AS COD_AJ_BC,
                                         N.NRNOTAFISC AS NUM_DOC,
                                         'NF de Devolucao: '|| N.NRNOTAFISC || ' - ' || N.NRINSJURNF AS DESCR_AJ_BC,
                                         DECODE(N.IDENTRSAIDOP,'E',N.DTENTRSAID,N.DTEMISSAO) AS DT_REF,
                                         MAX(CONTCTBL_SPED(N.IDENTRSAIDOP, IT.CDPRODUTO, N.CDTIPOOPER, DECODE(N.IDENTRSAIDOP,'E',N.CDFILIRECE,N.CDFILIEMIT))) AS COD_CTA, 
                                         REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'/'),'.'),'-') AS CNPJ,
                                         '' AS INFO_COMPL,
                                         T.CHAVENOTA 
                                    FROM TMP_REGBLOCOM_PIS T, FILIAL F, NOTAFISCAL N, ITEMPRODUTO IT, INSCRESTAD IE
                                   WHERE T.CDREGISTRO   = 'M220'
                                     AND T.CDFILIAL     = F.CDFILIAL(+)
                                     AND T.CSTPISCOFINS IN ('49','98','99')
                                     AND ((DECODE(VRALIQCRED,1.65,'01','02') = REGM210.CDCONTRI) OR
                                          (DECODE(VRALIQCRED,0.65,'51','52') = REGM210.CDCONTRI))
                                     AND NVL(F.IDCUMUPISCOFIL,T.CDFILIAL)  = REGM210.IDCUMUPISCOFIL
                                     AND T.IDTPALIQ     = REGM210.IDTPALIQ 
                                     AND T.VRALIQCRED   = REGM210.VRALIQCRED
                                     AND N.CDFILIAL     = SUBSTR(T.CHAVENOTA,1,4)
                                     AND N.NRLANCTONF   = SUBSTR(T.CHAVENOTA,5,10) 
                                     AND N.CDFILIAL     = IT.CDFILIAL
                                     AND N.NRLANCTONF   = IT.NRLANCTONF
                                     AND N.CDINSCESTA   = IE.CDINSCESTA
                                     AND N.CDEMPRESA    = IE.CDEMPRESA
                                     AND P_VERSAO      >= '005'
                                   GROUP BY T.IDAJUSTE, T.VRBCCRED, N.NRNOTAFISC, N.NRINSJURNF, N.IDENTRSAIDOP,
                                         N.DTENTRSAID, N.DTEMISSAO, IE.NRINSJURIEST, T.CHAVENOTA
                                   UNION ALL
                                   SELECT A.IDTPAJUSTE AS IND_AJ_BC,
                                         NVL(A.VRBASECALCAJ,0) AS VL_AJ_BC,
                                         A.CDAJUSTEBC AS COD_AJ_BC,
                                         A.NRPROCESSO AS NUM_DOC,
                                         A.DSAJUSTE AS DESCR_AJ_BC,
                                         NVL(A.DTAJUSTE,P_DTFINAL) AS DT_REF,
                                         A.CDCONTCTBL AS COD_CTA,
                                         REPLACE(REPLACE(REPLACE(I.NRINSJURIEST,'/'),'.'),'-') AS CNPJ,
                                         '' AS INFO_COMPL,
                                         A.NRSEQAJUSTE AS CHAVENOTA
                                    FROM AJUSTEAPURCONTR A, FILIAL F, INSCRESTAD I
                                   WHERE A.NRSEQUMAPAPU    = V_NRMAPAPIS
                                     AND A.IDINDICAJUSTE   = 'DB'
                                     AND A.IDCUMUPISCOFIL  = REGM210.IDCUMUPISCOFIL
                                     AND A.CDSITUTRIBITNFE IN ('01','49','98','99')
                                     AND REGM210.CDCONTRI  = DECODE(A.IDCUMUPISCOFIL,'N','51','01')
                                     AND ((V_CDCONTRIB IS NULL) OR (V_CDCONTRIB <> REGM210.CDCONTRI))
                                     AND A.CDFILIAL        = F.CDFILIAL
                                     AND F.CDEMPRESA       = I.CDEMPRESA
                                     AND F.CDINSCESTA      = I.CDINSCESTA
                                   UNION ALL
                                  SELECT A.IDTPAJUSTE AS IND_AJ_BC,
                                         NVL(A.VRBASECALCAJ,0) AS VL_AJ_BC,
                                         A.CDAJUSTEBC AS COD_AJ_BC,
                                         A.NRPROCESSO AS NUM_DOC,
                                         A.DSAJUSTE AS DESCR_AJ_BC,
                                         NVL(A.DTAJUSTE,P_DTFINAL) AS DT_REF,
                                         A.CDCONTCTBL AS COD_CTA,
                                         REPLACE(REPLACE(REPLACE(I.NRINSJURIEST,'/'),'.'),'-') AS CNPJ,
                                         '' AS INFO_COMPL,
                                         A.NRSEQAJUSTE AS CHAVENOTA
                                    FROM AJUSTEAPURCONTR A, FILIAL F, INSCRESTAD I
                                   WHERE A.NRSEQUMAPAPU    = V_NRMAPAPIS
                                     AND A.IDINDICAJUSTE   = 'DB'
                                     AND A.IDCUMUPISCOFIL  = REGM210.IDCUMUPISCOFIL
                                     AND A.CDSITUTRIBITNFE = '02'
                                     AND REGM210.CDCONTRI  = DECODE(A.IDCUMUPISCOFIL,'N','52','02')
                                     AND ((V_CDCONTRIB IS NULL) OR (V_CDCONTRIB <> REGM210.CDCONTRI))
                                     AND A.CDFILIAL        = F.CDFILIAL
                                     AND F.CDEMPRESA       = I.CDEMPRESA
                                     AND F.CDINSCESTA      = I.CDINSCESTA
                                   UNION ALL
                                  SELECT A.IDTPAJUSTE AS IND_AJ_BC,
                                         NVL(A.VRBASECALCAJ,0) AS VL_AJ_BC,
                                         A.CDAJUSTEBC AS COD_AJ_BC,
                                         A.NRPROCESSO AS NUM_DOC,
                                         A.DSAJUSTE AS DESCR_AJ_BC,
                                         NVL(A.DTAJUSTE,P_DTFINAL) AS DT_REF,
                                         A.CDCONTCTBL AS COD_CTA,
                                         REPLACE(REPLACE(REPLACE(I.NRINSJURIEST,'/'),'.'),'-') AS CNPJ,
                                         '' AS INFO_COMPL,
                                         A.NRSEQAJUSTE AS CHAVENOTA
                                    FROM AJUSTEAPURCONTR A, FILIAL F, INSCRESTAD I
                                   WHERE A.NRSEQUMAPAPU    = V_NRMAPAPIS
                                     AND A.IDINDICAJUSTE   = 'DB'
                                     AND A.IDCUMUPISCOFIL  = REGM210.IDCUMUPISCOFIL
                                     AND A.CDSITUTRIBITNFE = '03'
                                     AND REGM210.CDCONTRI  = DECODE(A.IDCUMUPISCOFIL,'N','53','03')
                                     AND ((V_CDCONTRIB IS NULL) OR (V_CDCONTRIB <> REGM210.CDCONTRI))
                                     AND A.CDFILIAL        = F.CDFILIAL
                                     AND F.CDEMPRESA       = I.CDEMPRESA
                                     AND F.CDINSCESTA      = I.CDINSCESTA
                                   UNION ALL
                                  SELECT A.IDTPAJUSTE AS IND_AJ_BC,
                                         NVL(A.VRBASECALCAJ,0) AS VL_AJ_BC,
                                         A.CDAJUSTEBC AS COD_AJ_BC,
                                         A.NRPROCESSO AS NUM_DOC,
                                         A.DSAJUSTE AS DESCR_AJ_BC,
                                         NVL(A.DTAJUSTE,P_DTFINAL) AS DT_REF,
                                         A.CDCONTCTBL AS COD_CTA,
                                         REPLACE(REPLACE(REPLACE(I.NRINSJURIEST,'/'),'.'),'-') AS CNPJ,
                                         '' AS INFO_COMPL,
                                         A.NRSEQAJUSTE AS CHAVENOTA
                                    FROM AJUSTEAPURCONTR A, FILIAL F, INSCRESTAD I
                                   WHERE A.NRSEQUMAPAPU    = V_NRMAPAPIS
                                     AND A.IDINDICAJUSTE   = 'DB'
                                     AND A.IDCUMUPISCOFIL  = REGM210.IDCUMUPISCOFIL
                                     AND A.CDSITUTRIBITNFE = '05'
                                     AND REGM210.CDCONTRI  = DECODE(A.IDCUMUPISCOFIL,'N','31','32')
                                     AND ((V_CDCONTRIB IS NULL) OR (V_CDCONTRIB <> REGM210.CDCONTRI))
                                     AND A.CDFILIAL        = F.CDFILIAL
                                     AND F.CDEMPRESA       = I.CDEMPRESA
                                     AND F.CDINSCESTA      = I.CDINSCESTA)) LOOP

            V_CONTREGA := NVL(V_CONTREGA,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'622'||LPAD(RTRIM(V_CONTREGA),22,'0'),REG_M215.REGM215);

            /*VERIFICA SE O ESTABELECIMENTO JA FOI INSERIDO ANTERIORMENTE*/
            V_NRINSJUR := NULL;

            SELECT MAX(CDCHAVEREG) AS CNPJ
              INTO V_NRINSJUR
              FROM TMP_REGDADOSSPED_PIS
             WHERE CDREGISTRO = '0140'
               AND CDCHAVEREG = REG_M215.CNPJ;

            IF V_NRINSJUR IS NULL THEN
               V_CONTEST  := NVL(V_CONTEST,0)+1;
               V_REG      := NULL;
               BEGIN
                  SELECT '|0140'             ||'|'||
                         LTRIM(TO_CHAR(V_CONTEST,'000')) ||'|'||
                         MAX(E.NMRAZSOCEMPR) ||'|'||
                         REPLACE(REPLACE(REPLACE(REPLACE(MAX(IE.NRINSJURIEST),'.',''),'/',''),'-',''),'\','') ||'|'||
                         MAX(IE.SGESTADO)    ||'|'||
                         ''                  ||'|'||
                         MAX(M.CDMUNICIBGE)  ||'|'||
                         ''                  ||'|'||
                         ''                  ||'|'
                    INTO V_REG
                    FROM INSCRESTAD IE, MUNICIPIO M, EMPRESA E
                   WHERE IE.NRINSJURIEST = REG_M215.CNPJ
                     AND IE.CDPAIS       = M.CDPAIS
                     AND IE.SGESTADO     = M.SGESTADO
                     AND IE.CDMUNICIPIO  = M.CDMUNICIPIO
                     AND IE.CDEMPRESA    = P_CDEMPRESA
                     AND E.CDEMPRESA     = IE.CDEMPRESA
                   GROUP BY '0140';
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_REG:= NULL;
               END;

               V_COUNTBCL0:= NVL(V_COUNTBCL0,0)+1;
               INSERT INTO ARQMAGPISCOFINS
                  (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
               VALUES
                  (P_NRSEQARQMAG,'104'||LTRIM(TO_CHAR(V_CONTEST,'000')),NVL(V_REG,'VAZIO'));

               V_SEQUPROD := NVL(V_SEQUPROD,0)+1;
               INSERT INTO TMP_REGDADOSSPED_PIS
                 (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
                VALUES
                 ('0140', V_SEQUPROD, REG_M215.CNPJ ,NULL, LTRIM(TO_CHAR(V_CONTEST,'000')));
            END IF;

            /* CODIGO DA CONTA CONTABIL */
            BEGIN
               SELECT MAX(CDCHAVEREG)
                 INTO V_COD_CC
                 FROM TMP_REGDADOSSPED_PIS
                WHERE CDCHAVEREG = REG_M215.COD_CTA
                  AND CDREGISTRO = '0500';
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_COD_CC := NULL;
            END;

            IF (V_COD_CC IS NULL) AND (REG_M215.COD_CTA IS NOT NULL) THEN
               V_SEQCCTBL := NVL(V_SEQCCTBL,0) + 1;
               INSERT INTO TMP_REGDADOSSPED_PIS
                  (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
               VALUES
                  ('0500', V_SEQCCTBL, REG_M215.COD_CTA, NULL);
            END IF;
         END LOOP;
      END IF;

      /*REGISTRO M220 - REGISTRO DE AJUSTES*/
      FOR REG_M220 IN (SELECT '|M220'                                                                 ||'|'||
                              IDAJUSTE                                                                ||'|'||
                              REPLACE(LTRIM(TO_CHAR(SUM(NVL(VRAJUSTE,0)),'999999999990.00')),'.',',') ||'|'||
                              CDAJUSTE                                                                ||'|'||
                              NUMDOC                                                                  ||'|'||
                              DSAJUSTE                                                                ||'|'||
                              TO_CHAR(DTAJUSTE,'DDMMYYYY')                                            ||'|' AS REGM220
                              /*DEVOLUCOES VENDA(ENTRADAS) REDUZEM - DEVOLUCOES COMPRA(SAIDAS) ACRESCEM*/
                         FROM (SELECT DECODE(R.IDAJUSTE,'S','1','0') AS IDAJUSTE, R.VRAJUSTE, R.CDAJUSTE,
                                      N.NRNOTAFISC AS NUMDOC, T.NMTIPOOPER||' - '||N.NRINSJURNF AS DSAJUSTE,
                                      DECODE(N.IDENTRSAIDOP,'E',N.DTENTRSAID,N.DTEMISSAO) AS DTAJUSTE
                                 FROM TMP_REGBLOCOM_PIS R, FILIAL F, NOTAFISCAL N, TIPOOPERAC T
                                WHERE R.CDREGISTRO     = 'M220'
                                  AND R.CSTPISCOFINS   IN ('49','98','99')
                                  AND R.CDFILIAL       = F.CDFILIAL
                                  AND F.IDCUMUPISCOFIL = REGM210.IDCUMUPISCOFIL
                                  AND REGM210.CDCONTRI IN ('01','02','51','52')
                                  AND N.CDFILIAL       = SUBSTR(R.CHAVENOTA,1,4)
                                  AND N.NRLANCTONF     = SUBSTR(R.CHAVENOTA,5,10)
                                  AND N.CDTIPOOPER     = T.CDTIPOOPER
                                  AND N.IDENTRSAIDOP   = T.IDENTRSAIDOP
                                  AND R.IDTPALIQ       = REGM210.IDTPALIQ
                                  AND R.VRALIQCRED     = REGM210.VRALIQCRED
                                  AND P_VERSAO         < '005'
                                  AND ((DECODE(VRALIQCRED,1.65,'01','02') = REGM210.CDCONTRI) OR
                                       (DECODE(VRALIQCRED,0.65,'51','52') = REGM210.CDCONTRI))
                                UNION ALL
                               SELECT A.IDTPAJUSTE AS IDAJUSTE, A.VRAJUSTE, A.CDAJUSTE,
                                      A.NRPROCESSO AS NUMDOC, A.DSAJUSTE, A.DTAJUSTE
                                 FROM AJUSTEAPURCONTR A
                                WHERE A.NRSEQUMAPAPU    = V_NRMAPAPIS
                                  AND A.IDINDICAJUSTE   = 'DI'
                                  AND A.IDCUMUPISCOFIL = REGM210.IDCUMUPISCOFIL
                                  AND DECODE(A.CDSITUTRIBITNFE,'05',DECODE(A.IDCUMUPISCOFIL,'N','31','32'),
                                                               '03',DECODE(A.IDCUMUPISCOFIL,'N','53','03'),
                                                               '02',DECODE(A.IDCUMUPISCOFIL,'N','52','02'),
                                                               DECODE(A.IDCUMUPISCOFIL,'N','51','01')) = REGM210.CDCONTRI
                                  AND A.CDSITUTRIBITNFE IN ('01','02','03','05','49','98','99')
                                  AND ((V_CDCONTRIB IS NULL) OR (V_CDCONTRIB <> REGM210.CDCONTRI)))
                        GROUP BY IDAJUSTE, CDAJUSTE, NUMDOC, DSAJUSTE, DTAJUSTE) LOOP

          V_CONTREGA:= NVL(V_CONTREGA,0)+1;
          INSERT INTO ARQMAGPISCOFINS
             (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
          VALUES
             (P_NRSEQARQMAG,'622'||LPAD(RTRIM(V_CONTREGA),22,'0'),REG_M220.REGM220);
      END LOOP;

      /*REGISTRO M230: INFORMACOES ADICIONAIS DE DIFERIMENTO*/
      FOR REG_M230 IN (SELECT '|M230'                                                                     ||'|'||
                              REPLACE(REPLACE(REPLACE(CNPJ,'.'),'/'),'-')                                 ||'|'||
                              REPLACE(LTRIM(TO_CHAR(SUM(NVL(VL_VEND,0)),'999999999990.00')),'.',',')      ||'|'||
                              REPLACE(LTRIM(TO_CHAR(SUM(NVL(VL_NAO_RECEB,0)),'999999999990.00')),'.',',') ||'|'||
                              REPLACE(LTRIM(TO_CHAR(SUM(NVL(VL_CONT_DIF,0)),'999999999990.00')),'.',',')  ||'|'||
                              REPLACE(LTRIM(TO_CHAR(SUM(NVL(VL_CRED_DIF,0)),'999999999990.00')),'.',',')  ||'|'||
                              CDTPCREDITO  ||'|' AS REGM230
                         FROM (SELECT CL.NRINSJURCLIE AS CNPJ,
                                      CD.VRCONTABIL AS VL_VEND, CD.VRCONTNAOREC AS VL_NAO_RECEB,
                                      SUM(NVL(CC.VRCONTRIBDIF,CD.VRCONTRIBDIF)) AS VL_CONT_DIF,
                                      SUM(NVL(CC.VRCREDDIFERIDO,CD.VRCREDDIFERIDO)) AS VL_CRED_DIF,
                                      CC.CDTPCREDITO, CD.NRSEQCONTRDIF
                                 FROM CONTRIBDIFERIDA CD, CLIENTE CL, NOTAFISCAL NF, FILIAL FL,
                                      (SELECT SUM(VRCREDDIFERIDO) AS VRCREDDIFERIDO, SUM(VRCONTRIBDIF) AS  VRCONTRIBDIF,
                                              CDTPCREDITO, NRSEQCONTRDIF
                                         FROM CONTROLCREDDIF
                                        WHERE NRSEQMAPADIF = V_NRMAPAPIS
                                        GROUP BY CDTPCREDITO, NRSEQCONTRDIF) CC
                                WHERE CD.NRSEQMAPADIF  = V_NRMAPAPIS
                                  AND CD.CDFILIAL      = NF.CDFILIAL
                                  AND CD.NRLANCTONF    = NF.NRLANCTONF
                                  AND NF.CDFILIEMIT    = FL.CDFILIAL
                                  AND CD.CDCONTRIBAPUR = REGM210.CDCONTRI
                                  AND CD.CDCLIENTE     = CL.CDCLIENTE
                                  AND CD.NRSEQCONTRDIF = CC.NRSEQCONTRDIF(+)
                                GROUP BY CL.NRINSJURCLIE, CD.VRCONTABIL, CD.VRCONTNAOREC, CC.VRCREDDIFERIDO, CC.CDTPCREDITO, CD.NRSEQCONTRDIF)
                         GROUP BY CNPJ, CDTPCREDITO)LOOP

          V_CONTREGA:= NVL(V_CONTREGA,0)+1;
          INSERT INTO ARQMAGPISCOFINS
             (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
          VALUES
             (P_NRSEQARQMAG,'622'||LPAD(RTRIM(V_CONTREGA),22,'0'),REG_M230.REGM230);
      END LOOP;

      V_CDCONTRIB := REGM210.CDCONTRI;
   END LOOP;

   /*GERA OS REGISTROS M210 E M220 PARA ITENS DE AJUSTES QUE POSSUEM CST PARA O QUAL NAO EXISTEM NOTAS NO PERIODO*/
   FOR REG_M210A IN (SELECT DECODE(A.CDSITUTRIBITNFE,'05',DECODE(A.IDCUMUPISCOFIL,'N','31','32'),
                                                     '03',DECODE(A.IDCUMUPISCOFIL,'N','53','03'),
                                                     '02',DECODE(A.IDCUMUPISCOFIL,'N','52','02'),
                                                          DECODE(A.IDCUMUPISCOFIL,'N','51','01')) AS CDCONTRI,
                            SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DB',VRBASECALCAJ,0),0)) AS VRBASECALCAJREDUC,
                            SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DB',VRAJUSTE,0),0)) AS VRAJUSTEREDUCBC,
                            SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DI',VRAJUSTE,0),0)) AS VRAJUSTEREDUC,
                            SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DB',VRBASECALCAJ,0),0)) AS VRBASECALCAJACRES,
                            SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DB',VRAJUSTE,0),0)) AS VRAJUSTEACRESBC,
                            SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DI',VRAJUSTE,0),0)) AS VRAJUSTEACRES,
                            A.IDCUMUPISCOFIL
                       FROM AJUSTEAPURCONTR A
                      WHERE A.NRSEQUMAPAPU   = V_NRMAPAPIS
                        AND SUBSTR(A.IDINDICAJUSTE,1,1) = 'D'
                        AND A.CDSITUTRIBITNFE IN ('01','02','03','05','49','98','99')
                        AND DECODE(A.CDSITUTRIBITNFE,'05',DECODE(A.IDCUMUPISCOFIL,'N','31','32'),
                                                     '03',DECODE(A.IDCUMUPISCOFIL,'N','53','03'),
                                                     '02',DECODE(A.IDCUMUPISCOFIL,'N','52','02'),
                                                          DECODE(A.IDCUMUPISCOFIL,'N','51','01')) NOT IN (SELECT DECODE(T.CSTPISCOFINS,'05',DECODE(F.IDCUMUPISCOFIL,'N','31','32'),
                                                                                                                                       '03',DECODE(F.IDCUMUPISCOFIL,'N','53','03'),
                                                                                                                                       '02',DECODE(F.IDCUMUPISCOFIL,'N','52','02'),
                                                                                                                                            DECODE(F.IDCUMUPISCOFIL,'N','51','01')) AS COD_CONTR
                                                                                                            FROM TMP_REGBLOCOM_PIS T, FILIAL F
                                                                                                           WHERE T.CDREGISTRO IN ('M200','M220')
                                                                                                             AND T.CDFILIAL   = F.CDFILIAL(+))
                      GROUP BY DECODE(A.CDSITUTRIBITNFE,'05',DECODE(A.IDCUMUPISCOFIL,'N','31','32'),
                                                        '03',DECODE(A.IDCUMUPISCOFIL,'N','53','03'),
                                                        '02',DECODE(A.IDCUMUPISCOFIL,'N','52','02'),
                                                             DECODE(A.IDCUMUPISCOFIL,'N','51','01')), A.IDCUMUPISCOFIL) LOOP

      IF NVL(REG_M210A.VRAJUSTEACRES,0) - NVL(REG_M210A.VRAJUSTEREDUC,0) + NVL(REG_M210A.VRAJUSTEACRESBC,0) - NVL(REG_M210A.VRAJUSTEREDUCBC,0) > 0 THEN
         IF REG_M210A.IDCUMUPISCOFIL = 'N' THEN
            V_VRAJUSTECUMU := NVL(V_VRAJUSTECUMU,0) + NVL(REG_M210A.VRAJUSTEACRES,0) - NVL(REG_M210A.VRAJUSTEREDUC,0) + NVL(REG_M210A.VRAJUSTEACRESBC,0) - NVL(REG_M210A.VRAJUSTEREDUCBC,0);
         ELSE
            V_VRAJUSTENCUM := NVL(V_VRAJUSTENCUM,0) + NVL(REG_M210A.VRAJUSTEACRES,0) - NVL(REG_M210A.VRAJUSTEREDUC,0) + NVL(REG_M210A.VRAJUSTEACRESBC,0) - NVL(REG_M210A.VRAJUSTEREDUCBC,0);
         END IF;

         V_REGM210 := '|M210'            ||'|'||
                      REG_M210A.CDCONTRI ||'|'||
                      '0,00'             ||'|'||
                      '0,00'             ||
                      CASE WHEN P_VERSAO >= '005' THEN
                         '|'||REPLACE(LTRIM(TO_CHAR(NVL(REG_M210A.VRBASECALCAJACRES,0),'999999999990.00')),'.',',')||'|'||
                         REPLACE(LTRIM(TO_CHAR(NVL(REG_M210A.VRBASECALCAJREDUC,0),'999999999990.00')),'.',',')     ||'|'||
                         REPLACE(LTRIM(TO_CHAR(NVL(REG_M210A.VRBASECALCAJACRES,0) - NVL(REG_M210A.VRBASECALCAJREDUC,0),'999999999990.00')),'.',',')
                      ELSE '' END        ||'|'||
                      ''                 ||'|'||
                      ''                 ||'|'||
                      ''                 ||'|'||
                      REPLACE(LTRIM(TO_CHAR(NVL(REG_M210A.VRAJUSTEACRESBC,0) - NVL(REG_M210A.VRAJUSTEREDUCBC,0),'999999999990.00')),'.',',')  ||'|'||
                      REPLACE(LTRIM(TO_CHAR(NVL(REG_M210A.VRAJUSTEACRES,0),'999999999990.00')),'.',',') ||'|'||
                      REPLACE(LTRIM(TO_CHAR(NVL(REG_M210A.VRAJUSTEREDUC,0),'999999999990.00')),'.',',') ||'|'||
                      ''                 ||'|'||
                      ''                 ||'|'||
                      REPLACE(LTRIM(TO_CHAR(NVL(REG_M210A.VRAJUSTEACRES,0)-NVL(REG_M210A.VRAJUSTEREDUC,0)+NVL(REG_M210A.VRAJUSTEACRESBC,0)-NVL(REG_M210A.VRAJUSTEREDUCBC,0),'999999999990.00')),'.',',')||'|';

         V_CONTREGA := NVL(V_CONTREGA,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'622'||LPAD(RTRIM(V_CONTREGA),22,'0'),V_REGM210);

         IF P_VERSAO >= '005' THEN
            FOR REG_M215A IN (SELECT '|M215'                                                                  ||'|'||
                                     A.IDTPAJUSTE                                                             ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(NVL(A.VRBASECALCAJ,0),'999999999990.00')),'.',',') ||'|'||
                                     A.CDAJUSTEBC                                                             ||'|'||
                                     A.NRPROCESSO                                                             ||'|'||
                                     A.DSAJUSTE                                                               ||'|'||
                                     TO_CHAR(NVL(A.DTAJUSTE,P_DTFINAL),'DDMMYYYY')                            ||'|'||
                                     A.CDCONTCTBL                                                             ||'|'||
                                     REPLACE(REPLACE(REPLACE(I.NRINSJURIEST,'/'),'.'),'-')                    ||'|'||
                                     ''                                                                       ||'|' AS REGM215,
                                     REPLACE(REPLACE(REPLACE(I.NRINSJURIEST,'/'),'.'),'-') AS CNPJ, A.CDCONTCTBL AS COD_CTA
                                FROM AJUSTEAPURCONTR A, FILIAL F, INSCRESTAD I
                               WHERE A.NRSEQUMAPAPU    = V_NRMAPAPIS
                                 AND A.IDINDICAJUSTE   = 'DB'
                                 AND A.CDSITUTRIBITNFE IN ('01','02','03','05','49','98','99')
                                 AND A.IDCUMUPISCOFIL  = REG_M210A.IDCUMUPISCOFIL
                                 AND DECODE(A.CDSITUTRIBITNFE,'05',DECODE(A.IDCUMUPISCOFIL,'N','31','32'),
                                                              '03',DECODE(A.IDCUMUPISCOFIL,'N','53','03'),
                                                              '02',DECODE(A.IDCUMUPISCOFIL,'N','52','02'),
                                                                   DECODE(A.IDCUMUPISCOFIL,'N','51','01')) = REG_M210A.CDCONTRI
                                 AND A.CDFILIAL        = F.CDFILIAL
                                 AND F.CDEMPRESA       = I.CDEMPRESA
                                 AND F.CDINSCESTA      = I.CDINSCESTA) LOOP

                V_CONTREGA := NVL(V_CONTREGA,0)+1;
               INSERT INTO ARQMAGPISCOFINS
                  (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
               VALUES
                  (P_NRSEQARQMAG,'622'||LPAD(RTRIM(V_CONTREGA),22,'0'),REG_M215A.REGM215);

               /*VERIFICA SE O ESTABELECIMENTO JA FOI INSERIDO ANTERIORMENTE*/
               V_NRINSJUR := NULL;

               SELECT MAX(CDCHAVEREG) AS CNPJ
                 INTO V_NRINSJUR
                 FROM TMP_REGDADOSSPED_PIS
                WHERE CDREGISTRO = '0140'
                  AND CDCHAVEREG = REG_M215A.CNPJ;

               IF V_NRINSJUR IS NULL THEN
                  V_CONTEST  := NVL(V_CONTEST,0)+1;
                  V_REG      := NULL;
                  BEGIN
                     SELECT '|0140'             ||'|'||
                            LTRIM(TO_CHAR(V_CONTEST,'000')) ||'|'||
                            MAX(E.NMRAZSOCEMPR) ||'|'||
                            REPLACE(REPLACE(REPLACE(REPLACE(MAX(IE.NRINSJURIEST),'.',''),'/',''),'-',''),'\','') ||'|'||
                            MAX(IE.SGESTADO)    ||'|'||
                            ''                  ||'|'||
                            MAX(M.CDMUNICIBGE)  ||'|'||
                            ''                  ||'|'||
                            ''                  ||'|'
                       INTO V_REG
                       FROM INSCRESTAD IE, MUNICIPIO M, EMPRESA E
                      WHERE IE.NRINSJURIEST = REG_M215A.CNPJ
                        AND IE.CDPAIS       = M.CDPAIS
                        AND IE.SGESTADO     = M.SGESTADO
                        AND IE.CDMUNICIPIO  = M.CDMUNICIPIO
                        AND IE.CDEMPRESA    = P_CDEMPRESA
                        AND E.CDEMPRESA     = IE.CDEMPRESA
                      GROUP BY '0140';
                  EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                        V_REG:= NULL;
                  END;

                  V_COUNTBCL0:= NVL(V_COUNTBCL0,0)+1;
                  INSERT INTO ARQMAGPISCOFINS
                     (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
                  VALUES
                     (P_NRSEQARQMAG,'104'||LTRIM(TO_CHAR(V_CONTEST,'000')),NVL(V_REG,'VAZIO'));

                  V_SEQUPROD := NVL(V_SEQUPROD,0)+1;
                  INSERT INTO TMP_REGDADOSSPED_PIS
                    (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
                   VALUES
                    ('0140', V_SEQUPROD, REG_M215A.CNPJ ,NULL, LTRIM(TO_CHAR(V_CONTEST,'000')));
               END IF;

               /* CODIGO DA CONTA CONTABIL */
               BEGIN
                  SELECT MAX(CDCHAVEREG)
                    INTO V_COD_CC
                    FROM TMP_REGDADOSSPED_PIS
                   WHERE CDCHAVEREG = REG_M215A.COD_CTA
                     AND CDREGISTRO = '0500';
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_COD_CC := NULL;
               END;

               IF (V_COD_CC IS NULL) AND (REG_M215A.COD_CTA IS NOT NULL) THEN
                  V_SEQCCTBL := NVL(V_SEQCCTBL,0) + 1;
                  INSERT INTO TMP_REGDADOSSPED_PIS
                     (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
                  VALUES
                     ('0500', V_SEQCCTBL, REG_M215A.COD_CTA, NULL);
               END IF;
            END LOOP;
         END IF;

         FOR REG_M220A IN (SELECT '|M220'                                                                 ||'|'||
                                  IDTPAJUSTE                                                              ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(SUM(NVL(VRAJUSTE,0)),'999999999990.00')),'.',',') ||'|'||
                                  CDAJUSTE                                                                ||'|'||
                                  NRPROCESSO                                                              ||'|'||
                                  DSAJUSTE                                                                ||'|'||
                                  TO_CHAR(DTAJUSTE,'DDMMYYYY')                                            ||'|' AS REGM220
                             FROM AJUSTEAPURCONTR A
                            WHERE A.NRSEQUMAPAPU    = V_NRMAPAPIS
                              AND A.IDINDICAJUSTE   = 'DI'
                              AND A.CDSITUTRIBITNFE IN ('01','02','03','05','49','98','99')
                              AND A.IDCUMUPISCOFIL  = REG_M210A.IDCUMUPISCOFIL
                              AND DECODE(A.CDSITUTRIBITNFE,'05',DECODE(A.IDCUMUPISCOFIL,'N','31','32'),
                                                           '03',DECODE(A.IDCUMUPISCOFIL,'N','53','03'),
                                                           '02',DECODE(A.IDCUMUPISCOFIL,'N','52','02'),
                                                                DECODE(A.IDCUMUPISCOFIL,'N','51','01')) = REG_M210A.CDCONTRI
                            GROUP BY IDTPAJUSTE, CDAJUSTE, NRPROCESSO, DSAJUSTE, DTAJUSTE) LOOP

            V_CONTREGA:= NVL(V_CONTREGA,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'622'||LPAD(RTRIM(V_CONTREGA),22,'0'),REG_M220A.REGM220);
         END LOOP;
      END IF;
   END LOOP;

   /*BUSCA O CREDITO ANTERIOR UTILIZADO NO PERIODO*/
   V_CREDANTERIOR := 0;
   BEGIN
      SELECT NVL(SUM(VRSALDOCREDORUT),0)
        INTO V_CREDANTERIOR
        FROM CREDANTSPED C
       WHERE C.NRSEQUMAPAPU  <> V_NRMAPAPIS
         AND C.CDEMPRESA     = P_CDEMPRESA
         AND C.DTUTILIZACRED = P_DTINICIAL
         AND C.IDTPCREDANT   = 'CA'
         AND C.CDREGISTRO    = '1100';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_CREDANTERIOR := 0;
   END;

   /*BUSCA O VALOR DO IMPOSTO RETIDO UTILIZADO NO PERIODO*/
   V_IMPRETIDONCUM := 0;
   V_IMPRETIDOCUM  := 0;
   BEGIN
      SELECT NVL(SUM(DECODE(IDCUMUPISCOFIL,'S',VRSALDOCREDORUT,0)),0),
             NVL(SUM(DECODE(IDCUMUPISCOFIL,'N',VRSALDOCREDORUT,0)),0)
        INTO V_IMPRETIDONCUM, V_IMPRETIDOCUM
        FROM CREDANTSPED C
       WHERE C.DTUTILIZACRED = P_DTINICIAL
         AND C.CDEMPRESA     = P_CDEMPRESA
         AND C.IDTPCREDANT   = 'CO'
         AND C.CDREGISTRO    = '1300';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_IMPRETIDONCUM := 0;
         V_IMPRETIDOCUM  := 0;
   END;

   /*BUSCA O VALOR DAS DEDUCOES CUMULATIVO E NAO CUMULATIVO NO PERIODO*/
   V_VRDEDUCAONCUM := 0;
   V_VRDEDUCAOCUMU := 0;
   BEGIN
      SELECT NVL(SUM(DECODE(I.IDCUMUPISCOFIL,'S',I.VRITEMDETAPU,0)),0) AS VRDEDUCAONCUM,
             NVL(SUM(DECODE(I.IDCUMUPISCOFIL,'N',I.VRITEMDETAPU,0)),0) AS VRDEDUCAOCUMU
        INTO V_VRDEDUCAONCUM, V_VRDEDUCAOCUMU
        FROM ITDETAPUCONTR I
       WHERE I.NRSEQUMAPAPU = V_NRMAPAPIS
         AND I.IDTIPODETAPU = 'DD'
       GROUP BY I.IDCUMUPISCOFIL;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_VRDEDUCAONCUM := 0;
         V_VRDEDUCAOCUMU := 0;
   END;

   /*REGISTRO M200(UM POR ARQUIVO)*/
   FOR REG_M200 IN (SELECT '|M200'                                                            ||'|'||
                           REPLACE(LTRIM(TO_CHAR(VRDEBITONCUM,   '999999999990.00')),'.',',') ||'|'|| /*CONTRIB. NAO CUMULATIVA*/
                           REPLACE(LTRIM(TO_CHAR(VRCREDNCUM,     '999999999990.00')),'.',',') ||'|'|| /*CREDITO UTILIZADO DO PROPRIO PERIODO - NAO CUMULATIVO*/
                           REPLACE(LTRIM(TO_CHAR(VRCREDANTNCUM,  '999999999990.00')),'.',',') ||'|'|| /*CREDITO UTILIZADO DE PERIODOS ANTERIORES - NAO CUMULATIVO*/
                           REPLACE(LTRIM(TO_CHAR(VRIMPDEVIDONCUM,'999999999990.00')),'.',',') ||'|'|| /*VR. DEVIDO NAO CUMULATIVO*/
                           REPLACE(LTRIM(TO_CHAR(VRIMPRETIDONCUM,'999999999990.00')),'.',',') ||'|'|| /*RETENCAO NA FONTE NAO CUMULATIVO*/
                           REPLACE(LTRIM(TO_CHAR(V_VRDEDUCAONCUM,'999999999990.00')),'.',',') ||'|'|| /*DEDUCOES NAO CUMULATIVO*/
                           REPLACE(LTRIM(TO_CHAR(CASE WHEN VRIMPDEVIDONCUM - VRIMPRETIDONCUM - V_VRDEDUCAONCUM > 0 THEN VRIMPDEVIDONCUM - VRIMPRETIDONCUM - V_VRDEDUCAONCUM ELSE 0 END,'999999999990.00')),'.',',') ||'|'|| /*VR. NAO CUMULATIVO A PAGAR*/
                           REPLACE(LTRIM(TO_CHAR(VRDEBITOCUMU,   '999999999990.00')),'.',',') ||'|'|| /*CONTRIB. CUMULATIVA*/
                           REPLACE(LTRIM(TO_CHAR(VRIMPRETIDOCUMU,'999999999990.00')),'.',',') ||'|'|| /*RETENCAO NA FONTE CUMULATIVO*/
                           REPLACE(LTRIM(TO_CHAR(V_VRDEDUCAOCUMU,'999999999990.00')),'.',',') ||'|'|| /*DEDUCOES CUMULATIVO*/
                           REPLACE(LTRIM(TO_CHAR(CASE WHEN VRDEBITOCUMU - VRIMPRETIDOCUMU - V_VRDEDUCAOCUMU > 0 THEN VRDEBITOCUMU - VRIMPRETIDOCUMU - V_VRDEDUCAOCUMU ELSE 0 END,'999999999990.00')),'.',',')       ||'|'|| /*VR. CUMULATIVO A PAGAR*/
                           REPLACE(LTRIM(TO_CHAR(CASE WHEN (CASE WHEN VRIMPDEVIDONCUM-VRIMPRETIDONCUM-V_VRDEDUCAONCUM > 0 THEN VRIMPDEVIDONCUM-VRIMPRETIDONCUM-V_VRDEDUCAONCUM ELSE 0 END + CASE WHEN VRDEBITOCUMU-VRIMPRETIDOCUMU-V_VRDEDUCAOCUMU > 0 THEN VRDEBITOCUMU-VRIMPRETIDOCUMU-V_VRDEDUCAOCUMU ELSE 0 END) > 0
                                                      THEN (CASE WHEN VRIMPDEVIDONCUM-VRIMPRETIDONCUM-V_VRDEDUCAONCUM > 0 THEN VRIMPDEVIDONCUM-VRIMPRETIDONCUM-V_VRDEDUCAONCUM ELSE 0 END + CASE WHEN VRDEBITOCUMU-VRIMPRETIDOCUMU-V_VRDEDUCAOCUMU > 0 THEN VRDEBITOCUMU-VRIMPRETIDOCUMU-V_VRDEDUCAOCUMU ELSE 0 END) ELSE 0 END,'999999999990.00')),'.',',') ||'|' AS REGM200,
                           /*BUSCA OS VALORES DE DEBITO NAO CUMULATIVO E CUMULATIVO PARA GERAR O REGISTRO M205*/
                           CASE WHEN VRIMPDEVIDONCUM - VRIMPRETIDONCUM - V_VRDEDUCAONCUM > 0 THEN VRIMPDEVIDONCUM - VRIMPRETIDONCUM - V_VRDEDUCAONCUM ELSE 0 END AS VRIMPRECOLNCUM,
                           CASE WHEN VRDEBITOCUMU - VRIMPRETIDOCUMU - V_VRDEDUCAOCUMU > 0 THEN VRDEBITOCUMU - VRIMPRETIDOCUMU - V_VRDEDUCAOCUMU ELSE 0 END AS VRIMPRECOLCUM
                      FROM (SELECT CASE WHEN NVL(VRDEBITONCUM,0) + V_VRAJUSTENCUM + V_VRDIFERIMENTONCUM > 0
                                        THEN NVL(VRDEBITONCUM,0) + V_VRAJUSTENCUM + V_VRDIFERIMENTONCUM
                                        ELSE 0 END AS VRDEBITONCUM,
                                   DECODE(P_CDINCTRIB,'2',0,NVL(V_VRCREDPIS, 0)) AS VRCREDNCUM,
                                   DECODE(P_CDINCTRIB,'2',0,NVL(V_CREDANTERIOR,0)) AS VRCREDANTNCUM,
                                   CASE WHEN NVL(VRDEBITONCUM,0) + V_VRAJUSTENCUM + V_VRDIFERIMENTONCUM - DECODE(P_CDINCTRIB,'2',0,NVL(V_VRCREDPIS, 0)) -  DECODE(P_CDINCTRIB,'2',0,NVL(V_CREDANTERIOR,0)) > 0
                                        THEN NVL(VRDEBITONCUM,0) + V_VRAJUSTENCUM + V_VRDIFERIMENTONCUM - DECODE(P_CDINCTRIB,'2',0,NVL(V_VRCREDPIS, 0)) -  DECODE(P_CDINCTRIB,'2',0,NVL(V_CREDANTERIOR,0)) ELSE 0 END AS VRIMPDEVIDONCUM,
                                   CASE WHEN NVL(VRDEBITOCUM,0)  + V_VRAJUSTECUMU + V_VRDIFERIMENTOCUMU > 0
                                        THEN NVL(VRDEBITOCUM,0)  + V_VRAJUSTECUMU + V_VRDIFERIMENTOCUMU
                                        ELSE 0 END AS VRDEBITOCUMU,
                                   V_IMPRETIDONCUM AS VRIMPRETIDONCUM, V_IMPRETIDOCUM AS VRIMPRETIDOCUMU
                              FROM (SELECT SUM(DECODE(NVL(F.IDCUMUPISCOFIL,T.CDFILIAL),'S',NVL(VRCRED,0),0)) AS VRDEBITONCUM,
                                           SUM(DECODE(NVL(F.IDCUMUPISCOFIL,T.CDFILIAL),'N',NVL(VRCRED,0),0)) AS VRDEBITOCUM
                                      FROM TMP_REGBLOCOM_PIS T, FILIAL F
                                     WHERE T.CDREGISTRO = 'M200'
                                       AND T.CDFILIAL   = F.CDFILIAL(+)))  /*USA ALTER JOIN NA FILIAL DEVIDO AS RECEITAS FINANCEIRAS, QUE NAO TEM FILIAL, E O PROPRIO CAMPO JA TRAZ O REGIME*/
                     UNION
                    SELECT '|M200|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|' AS REGM200,
                           0 AS VRIMPRECOLCUM,
                           0 AS VRIMPRECOLNCUM
                      FROM DUAL
                     WHERE P_GERABCLP = '2')LOOP

      V_CONTREGA:= NVL(V_CONTREGA,0)+1;
      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'620'||LPAD(RTRIM(V_CONTREGA),22,'0'),REG_M200.REGM200);

      /*REGISTRO M205 - OBRIGATORIO A PARTIR DE ABRIL DE 2014*/
	   /*GERA O REGISTRO CASO TENHA VALOR DE IMPOSTO A RECOLHER PARA CUMULATIVO OU NAO CUMULATIVO DE ACORDO COM OS CODIGOS DA RECEITA INFORMADOS NO MAPA*/
      BEGIN
         SELECT NVL(NVL(CDRECECUMU, CDRECEITACONTR||CDVARIACAO),CDRECEGUIA||NVL(CDVARIACAO,'02')), /*CASO OS ARQUIVOS GERADOS SEJAM DE MAPAS ANTIGOS PEGA O CODIGO DA RECEITA DO CAMPO CDRECEITACONTR CONCATENADO COM A VARIACAO*/
                NVL(NVL(CDRECENCUMU,CDRECEITACONTR||CDVARIACAO),CDRECEGUIA||NVL(CDVARIACAO,'01'))
           INTO V_CDRECEPISCUMU, V_CDRECEPISNCUMU
           FROM MAPAAPURAC
          WHERE CDEMPRESA    = P_CDEMPRESA
            AND CDIMPOSTO    = P_PIS
            AND DTINIPERIODO = P_DTINICIAL
            AND DTFIMPERIODO = P_DTFINAL;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDRECEPISCUMU  := NULL;
            V_CDRECEPISNCUMU := NULL;
      END;

      IF ((NVL(REG_M200.VRIMPRECOLCUM,0) > 0) OR (NVL(REG_M200.VRIMPRECOLNCUM,0) > 0)) AND ((P_DTINICIAL > '31/03/2014')) THEN  /*VERIFICA SE TEVE IMPOSTO A RECOLHER SE O MES E MAIOR QUE MARCO DE 2014, PARA GERAR O RESGISTRO M205*/
         IF NVL(REG_M200.VRIMPRECOLCUM,0) > 0 THEN /*VERIFICA SE TEVE DEBITO CUMULATIVO*/
            V_REGM205_C := '|M205'         ||'|'||
                           '12'            ||'|'||
                           V_CDRECEPISCUMU ||'|'||
                           REPLACE(LTRIM(TO_CHAR(REG_M200.VRIMPRECOLCUM,'999999999990.00')),'.',',') ||'|';

            V_CONTREGA:= NVL(V_CONTREGA,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'621'||LPAD(RTRIM(V_CONTREGA),22,'0'),V_REGM205_C);

         END IF;
         IF NVL(REG_M200.VRIMPRECOLNCUM,0) > 0 THEN /*VERIFICA SE TEVE DEBITO NAO CUMULATIVO*/
            V_REGM205_NC := '|M205'          ||'|'||
                            '08'             ||'|'||
                            V_CDRECEPISNCUMU ||'|'||
                            REPLACE(LTRIM(TO_CHAR(REG_M200.VRIMPRECOLNCUM,'999999999990.00')),'.',',') ||'|';

            V_CONTREGA:= NVL(V_CONTREGA,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'621'||LPAD(RTRIM(V_CONTREGA),22,'0'),V_REGM205_NC);

         END IF;
      END IF;
   END LOOP;

   /*REGISTRO M300: CONTRIBUICAO DE PIS/PASEP DIFERIDA EM PERIODOS ANTERIORES  VALORES A PAGAR NO PERIODO.*/
   FOR REG_M300 IN (SELECT '|M300'            ||'|'||
                           CD.CDCONTRIBAPUR   ||'|'||
                           REPLACE(LTRIM(TO_CHAR(SUM(NVL(CC.VRCONTRIBDIFAT, CD.VRCONTRIBDIFAT)),  '999999999990.00')),'.',',') ||'|'||
                           CC.CDNATUCREDDIF   ||'|'||
                           REPLACE(LTRIM(TO_CHAR(SUM(NVL(CC.VRCREDDIFAT, CD.VRCREDDIFAT)),        '999999999990.00')),'.',',') ||'|'||
                           REPLACE(LTRIM(TO_CHAR(SUM(NVL(CC.VRCONTRIBDIFANT, CD.VRCONTRIBDIFANT)),'999999999990.00')),'.',',') ||'|'||
                           TO_CHAR(MP.DTINIPERIODO,'MMYYYY')    ||'|'||
                           TO_CHAR(CD.DTRECEBIMENTO,'DDMMYYYY') ||'|' AS REGM300
                      FROM CONTRIBDIFERIDA CD, MAPAAPURAC MP,
                           (SELECT C.VRCONTRIBDIFAT, C.CDNATUCREDDIF, C.VRCREDDIFAT, C.VRCONTRIBDIFANT, C.NRSEQCONTRDIF
                              FROM CONTROLCREDDIF C
                             WHERE C.NRSEQMAPADIF = V_NRMAPAPIS
                               AND C.VRCONTRIBDIFANT > 0) CC
                     WHERE CD.NRSEQMAPADIF  = V_NRMAPAPIS
                       AND CD.NRSEQUMAPAPU  = MP.NRSEQUMAPAPU
                       AND CD.VRCONTRIBDIFANT > 0
                       AND CD.NRSEQCONTRDIF = CC.NRSEQCONTRDIF(+)
                     GROUP BY CD.CDCONTRIBAPUR, CC.CDNATUCREDDIF, MP.DTINIPERIODO, CD.DTRECEBIMENTO) LOOP

       V_CONTREGA:= NVL(V_CONTREGA,0)+1;
       INSERT INTO ARQMAGPISCOFINS
          (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
       VALUES
          (P_NRSEQARQMAG,'630'||LPAD(RTRIM(V_CONTREGA),22,'0'),REG_M300.REGM300);
   END LOOP;

   /*REGISTRO M400*/
   FOR REGM400 IN (SELECT '|M400'       ||'|'||
                          CSTPISCOFINS  ||'|'||
                          REPLACE(LTRIM(TO_CHAR(SUM(VRTOTNF),'999999999990.00')),'.',',')||'|'||
                          CDCONTCTBL    ||'|'||
                          ''            ||'|' AS REGISTRO, CSTPISCOFINS, CDCONTCTBL
                     FROM TMP_REGBLOCOM_PIS
                    WHERE CDREGISTRO   = 'M200'
                      AND CSTPISCOFINS IN ('04','05','06','07','08','09')
                    GROUP BY 'M400', CSTPISCOFINS, CDCONTCTBL)LOOP

      V_CONTREGA:= NVL(V_CONTREGA,0)+1;
      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'640'||LPAD(RTRIM(V_CONTREGA),22,'0'),REGM400.REGISTRO);

      /*REGISTRO M410*/
      FOR REGM410 IN (SELECT '|M410'      ||'|'||
                             CDNATUREC    ||'|'||
                             REPLACE(LTRIM(TO_CHAR(SUM(VRTOTNF),'999999999990.00')),'.',',')||'|'||
                             CDCONTCTBL   ||'|'||
                             ''           ||'|' AS REGISTRO
                        FROM TMP_REGBLOCOM_PIS
                       WHERE CDREGISTRO   = 'M200'
                         AND CSTPISCOFINS = REGM400.CSTPISCOFINS
                         AND NVL(CDCONTCTBL,'T') = NVL(REGM400.CDCONTCTBL,'T')
                       GROUP BY 'M410', CDNATUREC, CDCONTCTBL)LOOP

         V_CONTREGA:= NVL(V_CONTREGA,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'640'||LPAD(RTRIM(V_CONTREGA),22,'0'),REGM410.REGISTRO);
      END LOOP;
   END LOOP;

   /*BUSCA O MAPA DO COFINS DO PERIODO*/
   BEGIN
      SELECT NRSEQUMAPAPU
        INTO V_NRMAPACOFINS
        FROM MAPAAPURAC
       WHERE DTINIPERIODO = P_DTINICIAL
         AND DTFIMPERIODO = P_DTFINAL
         AND CDEMPRESA    = P_CDEMPRESA
         AND CDIMPOSTO    = P_COFINS;
   EXCEPTION
        WHEN NO_DATA_FOUND
        THEN V_NRMAPACOFINS := '';
   END;

   /*BUSCA OS VALORES DA TABELA QUE GRAVA SALDO PARA O PROXIMO PERIODO, PARA SABER SE O CREDITO FOI TOTALMENTE UTILIZADO,
     OU, CASO CONTRARIO, QUANTO RESTOU PARA O PERIODO SEGUINTE, PARA PREENCHER O REGISTRO M500*/
   V_VRSALDOFUTURO := 0;
   BEGIN
      SELECT DISTINCT NVL(VRSALDOCREDOR,0)
        INTO V_VRSALDOFUTURO
        FROM CREDANTSPED C
       WHERE C.NRSEQUMAPAPU = V_NRMAPACOFINS
         AND C.CDEMPRESA    = P_CDEMPRESA
         AND C.IDTPCREDANT  = 'CA';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_VRSALDOFUTURO := 0;
   END;

   /*INICIALIZA AS VARIAVEIS COM VALOR ZERO PARA QUE AS MESMAS NAO FIQUEM NULAS*/
   V_TIPOCREDITO     := NULL;
   V_VRACRESAJUSAUTO := 0;
   V_VRREDUZAJUSAUTO := 0;
   V_CREDUTLIZADO    := 0;

   /*REGISTRO M500*/
   FOR C_M500 IN (SELECT '|M500'                                                                                   ||'|'||
                         CDTPCRED                                                                                  ||'|'|| /*TIPO DE CREDITO*/
                         '0'                                                                                       ||'|'|| /*INDICADOR CREDITO = OP. PROPRIAS*/
                         REPLACE(LTRIM(TO_CHAR(SUM(DECODE(IDTPALIQ,'P',VRBCCRED,0)),'999999999990.00')),'.',',')   ||'|'|| /*BC COFINS*/
                         REPLACE(LTRIM(TO_CHAR(DECODE(IDTPALIQ,'P',VRALIQCRED,0),'990.0000')),'.',',')             ||'|'|| /*ALIQ COFINS*/
                         REPLACE(LTRIM(TO_CHAR(SUM(DECODE(IDTPALIQ,'Q',VRBCCRED,0)),'999999999990.000')),'.',',')  ||'|'|| /*QTDE BC COFINS*/
                         REPLACE(LTRIM(TO_CHAR(DECODE(IDTPALIQ,'Q',VRALIQCRED,''),'990.0000')),'.',',')            ||'|' AS REGM500, /*ALIQ COFINS QTDE*/
                         CDTPCRED, SUM(VRCREDCOFINS) AS VRCREDCOFINS, SUM(VRCRED) AS VRCRED,
                         DECODE(IDTPALIQ,'P',VRALIQCRED, 0) AS VRALIQ_P,
                         DECODE(IDTPALIQ,'Q',VRALIQCRED,'') AS VRALIQ_Q
                    FROM (SELECT VRBCCRED*VRALIQCRED/100 AS VRCREDCOFINS, CDTPCRED, VRBCCRED, VRALIQCRED, IDTPALIQ, VRCRED
                            FROM (SELECT CDTPCRED, SUM(VRBCCRED) AS VRBCCRED, VRALIQCRED, SUM(VRCRED) AS VRCRED, IDTPALIQ
                                    FROM TMP_REGBLOCOM_PIS
                                   WHERE CDREGISTRO = 'M500'
                                   GROUP BY CDTPCRED, VRALIQCRED, IDTPALIQ))
                   GROUP BY CDTPCRED, IDTPALIQ, VRALIQCRED) LOOP

      /*BUSCA OS VALORES DOS AJUSTES DE CREDITO PARA GERAR O REGISTRO M500*/
      V_VRAJUSTEACRES  := 0;
      V_VRAJUSTEREDUC  := 0;
      V_VRCREDDIFERIDO := 0;

      /*NO CASO DE EXISTIR MAIS DE UMA ALIQUOTA PARA O MESMO TIPO DE CREDITO, OS AJUSTES SERAO GERADOS APENAS PARA A PRIMEIRA ALIQUOTA*/
      BEGIN
         SELECT NVL(SUM(VL_AJUS_ACRES),0) AS VL_AJUS_ACRES, NVL(SUM(VL_AJUS_REDUC),0) AS VL_AJUS_REDUC
           INTO V_VRAJUSTEACRES, V_VRAJUSTEREDUC
           FROM (SELECT SUM(DECODE(A.IDTPAJUSTE,'1',A.VRAJUSTE,0)) AS VL_AJUS_ACRES,
                        SUM(DECODE(A.IDTPAJUSTE,'0',A.VRAJUSTE,0)) AS VL_AJUS_REDUC
                   FROM AJUSTEAPURCONTR A
                  WHERE A.NRSEQUMAPAPU    = V_NRMAPACOFINS
                    AND A.IDINDICAJUSTE   = 'C'
                    AND A.CDTPCREDPICOITE = C_M500.CDTPCRED
                    AND ((V_TIPOCREDITO IS NULL) OR (V_TIPOCREDITO <> C_M500.CDTPCRED))
                  UNION ALL
                 SELECT SUM(DECODE(T.IDAJUSTE,'E',T.VRAJUSTE,0)) AS VL_AJUS_ACRES,
                        SUM(DECODE(T.IDAJUSTE,'S',T.VRAJUSTE,0)) AS VL_AJUS_REDUC
                   FROM TMP_REGBLOCOM_PIS T
                  WHERE T.CDREGISTRO = 'M510'
                    AND T.CDTPCRED   = C_M500.CDTPCRED
                    AND DECODE(T.IDTPALIQ,'P',T.VRALIQCRED,0)    = C_M500.VRALIQ_P
                    AND ((DECODE(T.IDTPALIQ,'Q',T.VRALIQCRED,'') = C_M500.VRALIQ_Q) OR (DECODE(T.IDTPALIQ,'Q',T.VRALIQCRED,'') IS NULL AND C_M500.VRALIQ_Q IS NULL)));
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_VRAJUSTEACRES := 0;
            V_VRAJUSTEREDUC := 0;
      END;

      /*BUSCA O VALOR DE CREDITO DIFERIDO NO PERIODO*/
      BEGIN
         SELECT SUM(VRCREDDIFERIDO)
           INTO V_VRCREDDIFERIDO
           FROM CONTROLCREDDIF
          WHERE CDTPCREDITO  = C_M500.CDTPCRED
            AND NRSEQMAPADIF = V_NRMAPACOFINS;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_VRCREDDIFERIDO := 0;
      END;

      IF ABS(C_M500.VRCRED - C_M500.VRCREDCOFINS) > 0.5 THEN
         V_VRCREDITO := C_M500.VRCREDCOFINS;
         IF V_GERAAJUSTEAUTO = 'S' THEN
            V_VRAJUSTEAUTO := C_M500.VRCRED-C_M500.VRCREDCOFINS;

            IF V_VRAJUSTEAUTO > 0 THEN
               V_VRAJUSTEACRES   := NVL(V_VRAJUSTEACRES,  0) + NVL(V_VRAJUSTEAUTO,0);
               V_VRACRESAJUSAUTO := NVL(V_VRACRESAJUSAUTO,0) + NVL(V_VRAJUSTEAUTO,0);
            ELSE
               V_VRAJUSTEREDUC   := NVL(V_VRAJUSTEREDUC,  0) + ABS(NVL(V_VRAJUSTEAUTO,0));
               V_VRREDUZAJUSAUTO := NVL(V_VRREDUZAJUSAUTO,0) + ABS(NVL(V_VRAJUSTEAUTO,0));
            END IF;
         END IF;
      ELSE
         V_VRCREDITO := C_M500.VRCRED;
      END IF;

      V_VRTOTACRED := NVL(V_VRCREDITO,0) + NVL(V_VRAJUSTEACRES,0) - NVL(V_VRAJUSTEREDUC,0) - NVL(V_VRCREDDIFERIDO,0);

      /*VERIFICA SE O CREDITO FOI TOTALMENTE UTILIZADO OU FOI UTILIZADO PARCIALMENTE*/
      IF V_VRSALDOFUTURO > 0 THEN
         IF V_VRSALDOFUTURO >= V_VRTOTACRED THEN
            V_CREDUTLIZADO  := 0;
            V_VRSALDOFUTURO := V_VRSALDOFUTURO - NVL(V_VRTOTACRED,0);
         ELSE
            V_CREDUTLIZADO  := NVL(V_VRTOTACRED,0) - V_VRSALDOFUTURO;
            V_VRSALDOFUTURO := 0;
         END IF;
      ELSE
         V_CREDUTLIZADO := NVL(V_VRTOTACRED,0);
      END IF;

      IF V_CREDUTLIZADO = V_VRTOTACRED THEN
         V_IDUTICRED := 0;
      ELSE
         V_IDUTICRED := 1;
      END IF;

      /*SOMA O VALOR DO CREDITO APLICADO O PERCENTUAL PARA GERAR O MESMO VALOR DE CREDITO NO REGISTRO M600*/
      V_VRCREDCOFINS := NVL(V_VRCREDCOFINS,0) + NVL(V_CREDUTLIZADO,0);

      /*MONTA O RESTANTE DO REGISTRO M500 COM OS VALORES APOS O CALCULO*/
      V_REGM500 := REPLACE(LTRIM(TO_CHAR(V_VRCREDITO,'999999999990.00')),'.',',')                 ||'|'|| /*08-CREDITO COFINS*/
                   REPLACE(LTRIM(TO_CHAR(V_VRAJUSTEACRES,'999999999990.00')),'.',',')             ||'|'|| /*09-AJUSTE ACRESCIMO*/
                   REPLACE(LTRIM(TO_CHAR(V_VRAJUSTEREDUC,'999999999990.00')),'.',',')             ||'|'|| /*10-AJUSTE REDUCAO*/
                   REPLACE(LTRIM(TO_CHAR(NVL(V_VRCREDDIFERIDO,0),'999999999990.00')),'.',',')     ||'|'|| /*11-CREDITO DIFERIDO*/
                   REPLACE(LTRIM(TO_CHAR(V_VRTOTACRED,'999999999990.00')),'.',',')                ||'|'|| /*CREDITO (08+09-10-11)*/
                   NVL(V_IDUTICRED,'0')                                                           ||'|'|| /*INDICADOR UTILIZACAO DO CREDITO*/
                   REPLACE(LTRIM(TO_CHAR(V_CREDUTLIZADO,'999999999990.00')),'.',',')              ||'|'|| /*VALOR DO CREDITO UTILIZADO*/
                   REPLACE(LTRIM(TO_CHAR(V_VRTOTACRED-V_CREDUTLIZADO,'999999999990.00')),'.',',') ||'|';  /*VALOR DO CREDITO PARA PERIODO FUTURO*/
      V_CONTREGA:= NVL(V_CONTREGA,0)+1;

      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'650'||LPAD(RTRIM(V_CONTREGA),22,'0'),C_M500.REGM500||V_REGM500);

      /*REGISTRO M505(APENAS PARA NAO CUMULATIVO)*/
      IF P_CDINCTRIB <> '2' THEN
         FOR REG_M505 IN (SELECT '|M505'                                                                          ||'|'||
                                 CDNATUBCALPCIT                                                                   ||'|'||
                                 CSTPISCOFINS                                                                     ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(SUM(VRBCCRED),'999999999990.00')),'.',',')                 ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(SUM(VRBCCRECUM),'999999999990.00')),'.',',')               ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(SUM(VRBCCRED)-SUM(VRBCCRECUM),'999999999990.00')),'.',',') ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(SUM(VRBCCRED)-SUM(VRBCCRECUM),'999999999990.00')),'.',',') ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(SUM(VRBCCREDQT),'999999999990.000')),'.',',')              ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(SUM(VRBCCREDQT),'999999999990.000')),'.',',')              ||'|'||
                                 DECODE(CDNATUBCALPCIT,'13','OUTRAS OPERACOES COM DIREITO A CREDITO','')          ||'|' AS REGM505,
                                 SUM(VRBCCRED) AS VRBCCRED, SUM(VRBCCRECUM) AS VRBCCRECUM
                            FROM (SELECT CDNATUBCALPCIT, CSTPISCOFINS, ROUND(SUM(VRBCCRED),2) AS VRBCCRED, IDTPALIQ,
                                         DECODE(P_CDINCTRIB,'3',DECODE(NVL(NVL(F.IDCUMUPISCOFIL,T.CDFILIAL),'S'),'N',ROUND(SUM(VRBCCRED),2),0),0) AS VRBCCRECUM,
                                         DECODE(IDTPALIQ,'Q',SUM(VRBCCRED),'') AS VRBCCREDQT
                                    FROM TMP_REGBLOCOM_PIS T, FILIAL F
                                   WHERE T.CDFILIAL          = F.CDFILIAL(+)
                                     AND NVL(T.CDTPCRED,'T') = NVL(C_M500.CDTPCRED,'T') /*CASO OS CAMPOS ESTEJAM NULOS COMPARO T COM T PARA QUE O REGISTRO SEJA GERADO MESMO ASSIM(NAO DA PARA FAZER NULL=NULL)*/
                                     AND DECODE(T.IDTPALIQ,'P',T.VRALIQCRED,0)    = C_M500.VRALIQ_P
                                     AND ((DECODE(T.IDTPALIQ,'Q',T.VRALIQCRED,'') = C_M500.VRALIQ_Q) OR (DECODE(T.IDTPALIQ,'Q',T.VRALIQCRED,'') IS NULL AND C_M500.VRALIQ_Q IS NULL))
                                     AND T.CDREGISTRO        = 'M500'
                                   GROUP BY CDNATUBCALPCIT, CSTPISCOFINS, NVL(F.IDCUMUPISCOFIL,T.CDFILIAL), IDTPALIQ)
                           GROUP BY CDNATUBCALPCIT,CSTPISCOFINS,DECODE(CDNATUBCALPCIT,'13','OUTRAS OPERACOES COM DIREITO A CREDITO',''))LOOP

            V_CONTREGA:= NVL(V_CONTREGA,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'650'||LPAD(RTRIM(V_CONTREGA),22,'0'),REG_M505.REGM505);
         END LOOP;
      END IF;

      /*REGISTRO M510*/
      IF (ABS(C_M500.VRCRED - C_M500.VRCREDCOFINS) > 0.5) AND (V_GERAAJUSTEAUTO = 'S') THEN
         V_REGM510 := '|M510'                                                                ||'|'||
                      CASE WHEN V_VRAJUSTEAUTO > 0 THEN 1 ELSE 0 END                         ||'|'||
                      REPLACE(LTRIM(TO_CHAR(ABS(V_VRAJUSTEAUTO),'999999999990.00')),'.',',') ||'|'||
                      V_CDAJUSTEAUTO                                                         ||'|'||
                      ''                                                                     ||'|'||
                      V_DESCAJUSTEAUTO                                                       ||'|'||
                      ''                                                                     ||'|';
         V_CONTREGA:= NVL(V_CONTREGA,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'650'||LPAD(RTRIM(V_CONTREGA),22,'0'),V_REGM510);
      END IF;

      FOR REG_M510 IN (SELECT '|M510'                                                          ||'|'||
                              IDAJUSTE                                                         ||'|'||
                              REPLACE(LTRIM(TO_CHAR(SUM(VRAJUSTE),'999999999990.00')),'.',',') ||'|'||
                              CDAJUSTE                                                         ||'|'||
                              NUMDOC                                                           ||'|'||
                              DSAJUSTE                                                         ||'|'||
                              TO_CHAR(DTAJUSTE,'DDMMYYYY')                                     ||'|' AS REGM510
                         FROM (SELECT DECODE(R.IDAJUSTE,'S','0','1') AS IDAJUSTE, R.VRAJUSTE, R.CDAJUSTE,
                                      N.NRNOTAFISC AS NUMDOC, T.NMTIPOOPER||' - '||N.NRINSJURNF AS DSAJUSTE,
                                      DECODE(N.IDENTRSAIDOP,'E',N.DTENTRSAID,N.DTEMISSAO) AS DTAJUSTE
                                 FROM TMP_REGBLOCOM_PIS R, NOTAFISCAL N, TIPOOPERAC T
                                WHERE R.CDREGISTRO = 'M510'
                                  AND NVL(R.CDTPCRED,'T') = NVL(C_M500.CDTPCRED,'T') /*CASO OS CAMPOS ESTEJAM NULOS COMPARO T COM T PARA QUE O REGISTRO SEJA GERADO MESMO ASSIM*/
                                  AND DECODE(R.IDTPALIQ,'P',R.VRALIQCRED,0)    = C_M500.VRALIQ_P
                                  AND ((DECODE(R.IDTPALIQ,'Q',R.VRALIQCRED,'') = C_M500.VRALIQ_Q) OR (DECODE(R.IDTPALIQ,'Q',R.VRALIQCRED,'') IS NULL AND C_M500.VRALIQ_Q IS NULL))
                                  AND N.CDFILIAL     = SUBSTR(R.CHAVENOTA,1,4)
                                  AND N.NRLANCTONF   = SUBSTR(R.CHAVENOTA,5,10)
                                  AND N.CDTIPOOPER   = T.CDTIPOOPER
                                  AND N.IDENTRSAIDOP = T.IDENTRSAIDOP
                                UNION ALL
                               SELECT A.IDTPAJUSTE AS IDAJUSTE, A.VRAJUSTE, A.CDAJUSTE,
                                      A.NRPROCESSO AS NUMDOC, A.DSAJUSTE, A.DTAJUSTE
                                 FROM AJUSTEAPURCONTR A
                                WHERE A.NRSEQUMAPAPU    = V_NRMAPACOFINS
                                  AND A.IDINDICAJUSTE   = 'C'
                                  AND A.CDTPCREDPICOITE = C_M500.CDTPCRED
                                  AND ((V_TIPOCREDITO IS NULL) OR (V_TIPOCREDITO <> C_M500.CDTPCRED)))
                        GROUP BY IDAJUSTE, CDAJUSTE, NUMDOC, DSAJUSTE, DTAJUSTE) LOOP

          V_CONTREGA:= NVL(V_CONTREGA,0)+1;
          INSERT INTO ARQMAGPISCOFINS
             (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
          VALUES
             (P_NRSEQARQMAG,'650'||LPAD(RTRIM(V_CONTREGA),22,'0'),REG_M510.REGM510);
      END LOOP;

      V_TIPOCREDITO := C_M500.CDTPCRED;
   END LOOP;

   /*Gera os Registros M100 e M110 quando existirem ajustes para o tipo de credito, mas nao houver movimentacao para o mesmo*/
   FOR C_M500_AJ IN (SELECT '|M500'              ||'|'||
                            AJ.CDTPCREDPICOITE   ||'|'|| /*02-TIPO DE CREDITO*/
                            '0'                  ||'|'|| /*03-INDICADOR CREDITO = OP. PROPRIAS*/
                            '0,00'               ||'|'|| /*04-BC PIS*/
                            '0,0000'             ||'|'|| /*05-ALIQ PIS*/
                            ''                   ||'|'|| /*06-QTDE BC PIS*/
                            ''                   ||'|'|| /*07-ALIQ PIS QTDE*/
                            '0,00'               ||'|'|| /*08-CREDITO PIS*/
                            REPLACE(LTRIM(TO_CHAR(NVL(AJ.VRAJUSTEACRES,0),'999999999990.00')),'.',',') ||'|'|| /*09-AJUSTE ACRESCIMO*/
                            REPLACE(LTRIM(TO_CHAR(NVL(AJ.VRAJUSTEREDUZ,0),'999999999990.00')),'.',',') ||'|'|| /*10-AJUSTE REDUCAO*/
                            '0,00'               ||'|'|| /*11-CREDITO DIFERIDO*/
                            REPLACE(LTRIM(TO_CHAR(AJ.VRAJUSTEACRES - AJ.VRAJUSTEREDUZ,'999999999990.00')),'.',',') ||'|' AS REGM500, /*CREDITO (08+09-10-11)*/
                            AJ.VRAJUSTEACRES, AJ.VRAJUSTEREDUZ, AJ.CDTPCREDPICOITE
                       FROM (SELECT SUM(DECODE(A.IDTPAJUSTE,'1',A.VRAJUSTE,0)) AS VRAJUSTEACRES,
                                    SUM(DECODE(A.IDTPAJUSTE,'0',A.VRAJUSTE,0)) AS VRAJUSTEREDUZ,
                                    A.CDTPCREDPICOITE
                               FROM AJUSTEAPURCONTR A
                              WHERE A.NRSEQUMAPAPU    = V_NRMAPACOFINS
                                AND A.IDINDICAJUSTE   = 'C'
                                AND A.CDTPCREDPICOITE NOT IN (SELECT CDTPCRED
                                                                FROM TMP_REGBLOCOM_PIS
                                                               WHERE CDREGISTRO = 'M500')
                              GROUP BY A.CDTPCREDPICOITE)AJ) LOOP

      V_VRTOTACRED := NVL(C_M500_AJ.VRAJUSTEACRES,0) - NVL(C_M500_AJ.VRAJUSTEREDUZ,0);

      /*VERIFICA SE O CREDITO FOI TOTALMENTE UTILIZADO OU FOI UTILIZADO PARCIALMENTE*/
      IF V_VRSALDOFUTURO > 0 THEN
         IF V_VRSALDOFUTURO >= V_VRTOTACRED THEN
            V_CREDUTLIZADO  := 0;
            V_VRSALDOFUTURO := V_VRSALDOFUTURO - NVL(V_VRTOTACRED,0);
         ELSE
            V_CREDUTLIZADO  := NVL(V_VRTOTACRED,0) - V_VRSALDOFUTURO;
            V_VRSALDOFUTURO := 0;
         END IF;
      ELSE
         V_CREDUTLIZADO := NVL(V_VRTOTACRED,0);
      END IF;

      IF V_CREDUTLIZADO = V_VRTOTACRED THEN
         V_IDUTICRED := 0;
      ELSE
         V_IDUTICRED := 1;
      END IF;

      /*SOMA O VALOR DO CREDITO APLICADO O PERCENTUAL PARA GERAR O MESMO VALOR DE CREDITO NO REGISTRO M600*/
      V_VRCREDCOFINS := NVL(V_VRCREDCOFINS,0) + NVL(V_CREDUTLIZADO,0);

      /*MONTA  O RESTANTE DO REGISTRO M100 COM OS VALORES APOS O CALCULO*/
      V_REGM500 := NVL(V_IDUTICRED,'0')                                                           ||'|'|| /*INDICADOR UTILIZACAO DO CREDITO*/
                   REPLACE(LTRIM(TO_CHAR(V_CREDUTLIZADO,'999999999990.00')),'.',',')              ||'|'|| /*VALOR DO CREDITO UTILIZADO*/
                   REPLACE(LTRIM(TO_CHAR(V_VRTOTACRED-V_CREDUTLIZADO,'999999999990.00')),'.',',') ||'|';  /*VALOR DO CREDITO PARA PERIODO FUTURO*/
      V_CONTREGA := NVL(V_CONTREGA,0)+1;

      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'650'||LPAD(RTRIM(V_CONTREGA),22,'0'),C_M500_AJ.REGM500||V_REGM500);

      FOR C_M510_AJ IN (SELECT '|M510'                                                          ||'|'||
                               IDAJUSTE                                                         ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(VRAJUSTE),'999999999990.00')),'.',',') ||'|'||
                               CDAJUSTE                                                         ||'|'||
                               NUMDOC                                                           ||'|'||
                               DSAJUSTE                                                         ||'|'||
                               TO_CHAR(DTAJUSTE,'DDMMYYYY')                                     ||'|' AS REGM510
                          FROM (SELECT A.IDTPAJUSTE AS IDAJUSTE, A.VRAJUSTE, A.CDAJUSTE,
                                       A.NRPROCESSO AS NUMDOC, A.DSAJUSTE, A.DTAJUSTE
                                  FROM AJUSTEAPURCONTR A
                                 WHERE A.NRSEQUMAPAPU    = V_NRMAPACOFINS
                                   AND A.IDINDICAJUSTE   = 'C'
                                   AND A.CDTPCREDPICOITE = C_M500_AJ.CDTPCREDPICOITE)
                         GROUP BY IDAJUSTE, CDAJUSTE, NUMDOC, DSAJUSTE, DTAJUSTE) LOOP

         V_CONTREGA:= NVL(V_CONTREGA,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'650'||LPAD(RTRIM(V_CONTREGA),22,'0'),C_M510_AJ.REGM510);
      END LOOP;
   END LOOP;

   V_CDCONTRIB         := NULL;
   V_VRAJUSTENCUM      := 0;
   V_VRAJUSTECUMU      := 0;
   V_VRDIFERIMENTONCUM := 0;
   V_VRDIFERIMENTOCUMU := 0;

   /*REGISTRO M610*/
   FOR REGM610 IN (SELECT '|M610'   AS REG,
                          CDCONTRI  AS COD_CONT,
                          NVL(SUM(VRRECEITA),0) AS VL_REC_BRT,
                          NVL(DECODE(IDTPALIQ,'P',SUM(VRBCCONTRI),''),0) AS VL_BC_CONT,
                          NVL(DECODE(IDTPALIQ,'P',VRALIQCRED,''),'') AS ALIQ_COFINS,
                          NVL(DECODE(IDTPALIQ,'Q',SUM(VRBCCONTRI),''),'') AS QUANT_BC_COFINS, /*BC POR QUANTIDADE*/
                          NVL(DECODE(IDTPALIQ,'Q',VRALIQCRED,''),'') AS ALIQ_COFINS_QUANT, /*ALIQUOTA POR QUANTIDADE*/
                          NVL(SUM(VRCONTRI),0) AS VL_CONT_APUR,
                          CDCONTRI, IDTPALIQ, VRALIQCRED, IDCUMUPISCOFIL, SUM(VRCONTRI) AS VRCONTRIBUICAO
                   FROM (/*ALIQUOTA BASICA NAO-CUMULATIVA*/
                         SELECT '01' AS CDCONTRI, SUM(VRTOTNF) AS VRRECEITA ,SUM(VRBCCRED) AS VRBCCONTRI, VRALIQCRED, SUM(VRCRED) AS VRCONTRI,IDTPALIQ,
                                NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) AS IDCUMUPISCOFIL, CSTPISCOFINS
                           FROM TMP_REGBLOCOM_PIS T, FILIAL F
                          WHERE T.CDREGISTRO   = 'M600'
                            AND T.CDFILIAL     = F.CDFILIAL(+)
                            AND NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) = 'S'
                            AND T.CSTPISCOFINS = '01'
                          GROUP BY VRALIQCRED, IDTPALIQ, NVL(F.IDCUMUPISCOFIL,T.CDFILIAL), CSTPISCOFINS
                         UNION ALL /*ALIQUOTAS DIFERENCIADAS NAO-CUMULATIVA*/
                         SELECT '02' AS CDCONTRI, SUM(VRTOTNF) AS VRRECEITA ,SUM(VRBCCRED) AS VRBCCONTRI, VRALIQCRED, SUM(VRCRED) AS VRCONTRI,IDTPALIQ,
                                NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) AS IDCUMUPISCOFIL, CSTPISCOFINS
                           FROM TMP_REGBLOCOM_PIS T, FILIAL F
                          WHERE T.CDREGISTRO   = 'M600'
                            AND T.CDFILIAL     = F.CDFILIAL(+)
                            AND NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) = 'S'
                            AND T.CSTPISCOFINS =  '02'
                          GROUP BY VRALIQCRED, IDTPALIQ, NVL(F.IDCUMUPISCOFIL,T.CDFILIAL), CSTPISCOFINS
                         UNION ALL /*ALIQUOTA BASICA CUMULATIVA*/
                         SELECT '51' AS CDCONTRI, SUM(VRTOTNF) AS VRRECEITA ,SUM(VRBCCRED) AS VRBCCONTRI, VRALIQCRED, SUM(VRCRED) AS VRCONTRI,IDTPALIQ,
                                NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) AS IDCUMUPISCOFIL, CSTPISCOFINS
                           FROM TMP_REGBLOCOM_PIS T, FILIAL F
                          WHERE T.CDREGISTRO   = 'M600'
                            AND T.CDFILIAL     = F.CDFILIAL(+)
                            AND NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) = 'N'
                            AND T.CSTPISCOFINS = '01'
                          GROUP BY VRALIQCRED, IDTPALIQ, NVL(F.IDCUMUPISCOFIL,T.CDFILIAL), CSTPISCOFINS
                         UNION ALL /*ALIQUOTA DIFERENCIADA CUMULATIVA*/
                         SELECT '52' AS CDCONTRI, SUM(VRTOTNF) AS VRRECEITA ,SUM(VRBCCRED) AS VRBCCONTRI, VRALIQCRED, SUM(VRCRED) AS VRCONTRI,IDTPALIQ,
                                NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) AS IDCUMUPISCOFIL, CSTPISCOFINS
                           FROM TMP_REGBLOCOM_PIS T, FILIAL F
                          WHERE T.CDREGISTRO   = 'M600'
                            AND T.CDFILIAL     = F.CDFILIAL(+)
                            AND NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) = 'N'
                            AND T.CSTPISCOFINS = '02'
                          GROUP BY VRALIQCRED, IDTPALIQ, NVL(F.IDCUMUPISCOFIL,T.CDFILIAL), CSTPISCOFINS
                         UNION ALL /*CONTRIBUICAO NAO-CUMULATIVA APURADA A ALIQUOTA POR UNIDADE DE MEDIDA DE PRODUTO*/
                         SELECT '03' AS CDCONTRI, SUM(VRTOTNF) AS VRRECEITA ,SUM(VRBCCRED) AS VRBCCONTRI, VRALIQCRED, SUM(VRCRED) AS VRCONTRI,IDTPALIQ,
                                NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) AS IDCUMUPISCOFIL, CSTPISCOFINS
                           FROM TMP_REGBLOCOM_PIS T, FILIAL F
                          WHERE T.CDREGISTRO   = 'M600'
                            AND T.CDFILIAL     = F.CDFILIAL(+)
                            AND NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) = 'S'
                            AND T.CSTPISCOFINS = '03'
                          GROUP BY VRALIQCRED, IDTPALIQ, NVL(F.IDCUMUPISCOFIL,T.CDFILIAL), CSTPISCOFINS
                          UNION ALL /*CONTRIBUICAO CUMULATIVA APURADA POR SUBSTITUICAO TRIBUTARIA*/
                         SELECT '31' AS CDCONTRI, SUM(VRTOTNF) AS VRRECEITA ,SUM(VRBCCRED) AS VRBCCONTRI, VRALIQCRED, SUM(VRCRED) AS VRCONTRI,IDTPALIQ,
                                NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) AS IDCUMUPISCOFIL, CSTPISCOFINS
                           FROM TMP_REGBLOCOM_PIS T, FILIAL F
                          WHERE T.CDREGISTRO   = 'M600'
                            AND T.CDFILIAL     = F.CDFILIAL(+)
                            AND NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) = 'N'
                            AND T.CSTPISCOFINS = '05'
                          GROUP BY VRALIQCRED, IDTPALIQ, NVL(F.IDCUMUPISCOFIL,T.CDFILIAL), CSTPISCOFINS
                          UNION ALL /*CONTRIBUICAO NAO-CUMULATIVA APURADA POR SUBSTITUICAO TRIBUTARIA*/
                         SELECT '32' AS CDCONTRI, SUM(VRTOTNF) AS VRRECEITA ,SUM(VRBCCRED) AS VRBCCONTRI, VRALIQCRED, SUM(VRCRED) AS VRCONTRI,IDTPALIQ,
                                NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) AS IDCUMUPISCOFIL, CSTPISCOFINS
                           FROM TMP_REGBLOCOM_PIS T, FILIAL F
                          WHERE T.CDREGISTRO   = 'M600'
                            AND T.CDFILIAL     = F.CDFILIAL(+)
                            AND NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) = 'S'
                            AND T.CSTPISCOFINS = '05'
                          GROUP BY VRALIQCRED, IDTPALIQ, NVL(F.IDCUMUPISCOFIL,T.CDFILIAL), CSTPISCOFINS
                          UNION ALL /*CONTRIBUICAO CUMULATIVA APURADA A ALIQUOTA POR UNIDADE DE MEDIDA DE PRODUTO*/
                         SELECT '53' AS CDCONTRI, SUM(VRTOTNF) AS VRRECEITA ,SUM(VRBCCRED) AS VRBCCONTRI, VRALIQCRED, SUM(VRCRED) AS VRCONTRI,IDTPALIQ,
                                NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) AS IDCUMUPISCOFIL, CSTPISCOFINS
                           FROM TMP_REGBLOCOM_PIS T, FILIAL F
                          WHERE T.CDREGISTRO   = 'M600'
                            AND T.CDFILIAL     = F.CDFILIAL(+)
                            AND NVL(F.IDCUMUPISCOFIL,T.CDFILIAL) = 'N'
                            AND T.CSTPISCOFINS = '03'
                          GROUP BY VRALIQCRED, IDTPALIQ, NVL(F.IDCUMUPISCOFIL,T.CDFILIAL), CSTPISCOFINS)
                   GROUP BY IDTPALIQ, VRALIQCRED, CDCONTRI, IDCUMUPISCOFIL) LOOP

      V_VRBCAJUSTEACRES := 0;
      V_VRAJUSTEACRESBC := 0;
      V_VRAJUSTEACRES   := 0;
      V_VRBCAJUSTEREDUC := 0;
      V_VRAJUSTEREDUCBC := 0;
      V_VRAJUSTEREDUC   := 0;

      BEGIN
         SELECT SUM(VRBCACRESCIMO) AS VRBCACRESCIMO, SUM(VRACRESCIMOBC) AS VRACRESCIMOBC, SUM(VRACRESCIMO) AS VRACRESCIMO, 
                SUM(VRBCREDUCAO) AS VRBCREDUCAO, SUM(VRREDUCAOBC) AS VRREDUCAOBC, SUM(VRREDUCAO) AS VRREDUCAO 
           INTO V_VRBCAJUSTEACRES, V_VRAJUSTEACRESBC, V_VRAJUSTEACRES, V_VRBCAJUSTEREDUC, V_VRAJUSTEREDUCBC, V_VRAJUSTEREDUC
           FROM (SELECT CASE WHEN P_VERSAO >= '005' THEN SUM(DECODE(IDAJUSTE,'S',VRBCCRED,0)) ELSE 0 END AS VRBCACRESCIMO,
                        CASE WHEN P_VERSAO >= '005' THEN SUM(DECODE(IDAJUSTE,'S',VRAJUSTE,0)) ELSE 0 END AS VRACRESCIMOBC,
                        CASE WHEN P_VERSAO >= '005' THEN 0 ELSE SUM(DECODE(IDAJUSTE,'S',VRAJUSTE,0)) END AS VRACRESCIMO,
                        CASE WHEN P_VERSAO >= '005' THEN SUM(DECODE(IDAJUSTE,'E',VRBCCRED,0)) ELSE 0 END AS VRBCREDUCAO,
                        CASE WHEN P_VERSAO >= '005' THEN SUM(DECODE(IDAJUSTE,'E',VRAJUSTE,0)) ELSE 0 END AS VRREDUCAOBC,
                        CASE WHEN P_VERSAO >= '005' THEN 0 ELSE SUM(DECODE(IDAJUSTE,'E',VRAJUSTE,0)) END AS VRREDUCAO
                   FROM TMP_REGBLOCOM_PIS T, FILIAL F
                  WHERE T.CDREGISTRO   = 'M620'
                    AND T.CDFILIAL     = F.CDFILIAL(+)
                    AND T.CSTPISCOFINS IN ('49','98','99')
                    AND ((DECODE(VRALIQCRED,7.6,'01','02') = REGM610.CDCONTRI) OR
                         (DECODE(VRALIQCRED,3,'51','52')   = REGM610.CDCONTRI))
                    AND NVL(F.IDCUMUPISCOFIL,T.CDFILIAL)   = REGM610.IDCUMUPISCOFIL
                    AND T.IDTPALIQ     = REGM610.IDTPALIQ
                    AND T.VRALIQCRED   = REGM610.VRALIQCRED
                  UNION ALL
                 SELECT SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DB',VRBASECALCAJ,0),0)) AS VRBCACRESCIMO,
                        SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DB',VRAJUSTE,0),0)) AS VRACRESCIMOBC,
                        SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DI',VRAJUSTE,0),0)) AS VRACRESCIMO,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DB',VRBASECALCAJ,0),0)) AS VRBCREDUCAO,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DB',VRAJUSTE,0),0)) AS VRREDUCAOBC,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DI',VRAJUSTE,0),0)) AS VRREDUCAO
                   FROM AJUSTEAPURCONTR A
                  WHERE A.NRSEQUMAPAPU    = V_NRMAPACOFINS
                    AND SUBSTR(A.IDINDICAJUSTE,1,1) = 'D'
                    AND A.IDCUMUPISCOFIL  = REGM610.IDCUMUPISCOFIL
                    AND A.CDSITUTRIBITNFE IN ('01','49','98','99')
                    AND REGM610.CDCONTRI  = DECODE(A.IDCUMUPISCOFIL,'N','51','01')
                    AND ((V_CDCONTRIB IS NULL) OR (V_CDCONTRIB <> REGM610.CDCONTRI))
                  UNION ALL
                 SELECT SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DB',VRBASECALCAJ,0),0)) AS VRBCACRESCIMO,
                        SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DB',VRAJUSTE,0),0)) AS VRACRESCIMOBC,
                        SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DI',VRAJUSTE,0),0)) AS VRACRESCIMO,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DB',VRBASECALCAJ,0),0)) AS VRBCREDUCAO,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DB',VRAJUSTE,0),0)) AS VRREDUCAOBC,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DI',VRAJUSTE,0),0)) AS VRREDUCAO
                   FROM AJUSTEAPURCONTR A
                  WHERE A.NRSEQUMAPAPU    = V_NRMAPACOFINS
                    AND SUBSTR(A.IDINDICAJUSTE,1,1) = 'D'
                    AND A.IDCUMUPISCOFIL  = REGM610.IDCUMUPISCOFIL
                    AND A.CDSITUTRIBITNFE = '02'
                    AND REGM610.CDCONTRI  = DECODE(A.IDCUMUPISCOFIL,'N','52','02')
                    AND ((V_CDCONTRIB IS NULL) OR (V_CDCONTRIB <> REGM610.CDCONTRI))
                  UNION ALL
                 SELECT SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DB',VRBASECALCAJ,0),0)) AS VRBCACRESCIMO,
                        SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DB',VRAJUSTE,0),0)) AS VRACRESCIMOBC,
                        SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DI',VRAJUSTE,0),0)) AS VRACRESCIMO,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DB',VRBASECALCAJ,0),0)) AS VRBCREDUCAO,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DB',VRAJUSTE,0),0)) AS VRREDUCAOBC,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DI',VRAJUSTE,0),0)) AS VRREDUCAO
                   FROM AJUSTEAPURCONTR A
                  WHERE A.NRSEQUMAPAPU    = V_NRMAPACOFINS
                    AND SUBSTR(A.IDINDICAJUSTE,1,1) = 'D'
                    AND A.IDCUMUPISCOFIL  = REGM610.IDCUMUPISCOFIL
                    AND A.CDSITUTRIBITNFE = '03'
                    AND REGM610.CDCONTRI  = DECODE(A.IDCUMUPISCOFIL,'N','53','03')
                    AND ((V_CDCONTRIB IS NULL) OR (V_CDCONTRIB <> REGM610.CDCONTRI))
                  UNION ALL
                 SELECT SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DB',VRBASECALCAJ,0),0)) AS VRBCACRESCIMO,
                        SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DB',VRAJUSTE,0),0)) AS VRACRESCIMOBC,
                        SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DI',VRAJUSTE,0),0)) AS VRACRESCIMO,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DB',VRBASECALCAJ,0),0)) AS VRBCREDUCAO,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DB',VRAJUSTE,0),0)) AS VRREDUCAOBC,
                        SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DI',VRAJUSTE,0),0)) AS VRREDUCAO
                   FROM AJUSTEAPURCONTR A
                  WHERE A.NRSEQUMAPAPU    = V_NRMAPACOFINS
                    AND SUBSTR(A.IDINDICAJUSTE,1,1) = 'D'
                    AND A.IDCUMUPISCOFIL  = REGM610.IDCUMUPISCOFIL
                    AND A.CDSITUTRIBITNFE = '05'
                    AND REGM610.CDCONTRI  = DECODE(A.IDCUMUPISCOFIL,'N','31','32')
                    AND ((V_CDCONTRIB IS NULL) OR (V_CDCONTRIB <> REGM610.CDCONTRI)));
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_VRBCAJUSTEACRES := 0;
            V_VRAJUSTEACRESBC := 0;
            V_VRAJUSTEACRES   := 0;
            V_VRBCAJUSTEREDUC := 0;
            V_VRAJUSTEREDUCBC := 0;
            V_VRAJUSTEREDUC   := 0;
      END;

      IF REGM610.IDCUMUPISCOFIL = 'N' THEN
         V_VRAJUSTECUMU := NVL(V_VRAJUSTECUMU,0) + NVL(V_VRAJUSTEACRES,0) - NVL(V_VRAJUSTEREDUC,0) + NVL(V_VRAJUSTEACRESBC,0) - NVL(V_VRAJUSTEREDUCBC,0);
      ELSE
         V_VRAJUSTENCUM := NVL(V_VRAJUSTENCUM,0) + NVL(V_VRAJUSTEACRES,0) - NVL(V_VRAJUSTEREDUC,0) + NVL(V_VRAJUSTEACRESBC,0) - NVL(V_VRAJUSTEREDUCBC,0);
      END IF;

      V_VRCONTRIBDIF    := 0;
      V_VRCONTRIBDIFANT := 0;

      FOR C_DIF IN (SELECT SUM(C.VRCONTRIBDIF) AS VRCONTRIBDIF, SUM(C.VRCONTRIBDIFANT) AS VRCONTRIBDIFANT, F.IDCUMUPISCOFIL
                      FROM CONTRIBDIFERIDA C, NOTAFISCAL N, FILIAL F
                     WHERE C.NRSEQMAPADIF  = V_NRMAPACOFINS
                       AND C.CDFILIAL      = N.CDFILIAL
                       AND C.NRLANCTONF    = N.NRLANCTONF
                       AND N.CDFILIEMIT    = F.CDFILIAL
                       AND C.CDCONTRIBAPUR = REGM610.CDCONTRI
                     GROUP BY F.IDCUMUPISCOFIL) LOOP

         V_VRCONTRIBDIF    := NVL(V_VRCONTRIBDIF,   0) + C_DIF.VRCONTRIBDIF;
         V_VRCONTRIBDIFANT := NVL(V_VRCONTRIBDIFANT,0) + C_DIF.VRCONTRIBDIFANT;

         IF C_DIF.IDCUMUPISCOFIL = 'N' THEN
            V_VRDIFERIMENTOCUMU := NVL(V_VRDIFERIMENTOCUMU,0) - NVL(C_DIF.VRCONTRIBDIF,0) + NVL(C_DIF.VRCONTRIBDIFANT,0);
         ELSE
            V_VRDIFERIMENTONCUM := NVL(V_VRDIFERIMENTONCUM,0) - NVL(C_DIF.VRCONTRIBDIF,0) + NVL(C_DIF.VRCONTRIBDIFANT,0);
         END IF;
      END LOOP;

      V_REGM610 := REGM610.REG      ||'|'||
                   REGM610.COD_CONT ||'|'||
                   REPLACE(LTRIM(TO_CHAR(NVL(REGM610.VL_REC_BRT,0),'999999999990.00')),'.',',')        ||'|'||
                   REPLACE(LTRIM(TO_CHAR(NVL(REGM610.VL_BC_CONT,0),'999999999990.00')),'.',',')        ||
                   CASE WHEN P_VERSAO >= '005' THEN
                      '|'||REPLACE(LTRIM(TO_CHAR(NVL(V_VRBCAJUSTEACRES,0),'999999999990.00')),'.',',') ||'|'||
                      REPLACE(LTRIM(TO_CHAR(NVL(V_VRBCAJUSTEREDUC,0),'999999999990.00')),'.',',')      ||'|'||
                      REPLACE(LTRIM(TO_CHAR(NVL(REGM610.VL_BC_CONT,0) + NVL(V_VRBCAJUSTEACRES,0) - NVL(V_VRBCAJUSTEREDUC,0),'999999999990.00')),'.',',')
                   ELSE '' END                                                                         ||'|'||
                   REPLACE(LTRIM(TO_CHAR(NVL(REGM610.ALIQ_COFINS,''),'990.0000')),'.',',')             ||'|'||
                   REPLACE(LTRIM(TO_CHAR(NVL(REGM610.QUANT_BC_COFINS,''),'999999999990.000')),'.',',') ||'|'||/*BC POR QUANTIDADE*/
                   REPLACE(LTRIM(TO_CHAR(NVL(REGM610.ALIQ_COFINS_QUANT,''),'990.0000')),'.',',')       ||'|'||/*ALIQUOTA POR QUANTIDADE*/
                   REPLACE(LTRIM(TO_CHAR(NVL(REGM610.VL_CONT_APUR,0) + NVL(V_VRAJUSTEACRESBC,0) - NVL(V_VRAJUSTEREDUCBC,0),'999999999990.00')),'.',',')      ||'|'||
                   REPLACE(LTRIM(TO_CHAR(NVL(V_VRAJUSTEACRES,0),'999999999990.00')),'.',',')           ||'|'||/*AJUSTE A ACRESCIMO*/
                   REPLACE(LTRIM(TO_CHAR(NVL(V_VRAJUSTEREDUC,0),'999999999990.00')),'.',',')           ||'|'||/*AJUSTE A REDUCAO*/
                   REPLACE(LTRIM(TO_CHAR(NVL(V_VRCONTRIBDIF,0),'999999999990.00')),'.',',')            ||'|'||/*VALOR CONTRI. A DIFERIR*/
                   REPLACE(LTRIM(TO_CHAR(NVL(V_VRCONTRIBDIFANT,0),'999999999990.00')),'.',',')         ||'|'||/*VALOR CONTRI. DIFERIDA PER. ANT*/
                   REPLACE(LTRIM(TO_CHAR(NVL(REGM610.VRCONTRIBUICAO,0)+NVL(V_VRAJUSTEACRESBC,0)-NVL(V_VRAJUSTEREDUCBC,0)+NVL(V_VRAJUSTEACRES,0)-NVL(V_VRAJUSTEREDUC,0)-NVL(V_VRCONTRIBDIF,0)+NVL(V_VRCONTRIBDIFANT,0),'999999999990.00')),'.',',') ||'|';

      V_CONTREGA:= NVL(V_CONTREGA,0)+1;
      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'662'||LPAD(RTRIM(V_CONTREGA),22,'0'),V_REGM610);

      IF P_VERSAO >= '005' THEN
         /*REGISTRO M615 - */
         FOR REG_M615 IN (SELECT '|M615'                                                            ||'|'||
                                 IND_AJ_BC                                                          ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(NVL(VL_AJ_BC,0),'999999999990.00')),'.',',') ||'|'||
                                 COD_AJ_BC                                                          ||'|'||
                                 NUM_DOC                                                            ||'|'||
                                 DESCR_AJ_BC                                                        ||'|'||
                                 TO_CHAR(DT_REF,'DDMMYYYY')                                         ||'|'||
                                 COD_CTA                                                            ||'|'||
                                 CNPJ                                                               ||'|'||
                                 INFO_COMPL                                                         ||'|' AS REGM615,
                                 CNPJ, COD_CTA
                            FROM (SELECT DECODE(T.IDAJUSTE,'S','1','0') AS IND_AJ_BC,
                                         NVL(T.VRBCCRED,0) AS VL_AJ_BC,
                                         '02' AS COD_AJ_BC,
                                         N.NRNOTAFISC AS NUM_DOC,
                                         'NF de Devolucao: '|| N.NRNOTAFISC || ' - ' || N.NRINSJURNF AS DESCR_AJ_BC,
                                         DECODE(N.IDENTRSAIDOP,'E',N.DTENTRSAID,N.DTEMISSAO) AS DT_REF,
                                         MAX(CONTCTBL_SPED(N.IDENTRSAIDOP, IT.CDPRODUTO, N.CDTIPOOPER, DECODE(N.IDENTRSAIDOP,'E',N.CDFILIRECE,N.CDFILIEMIT))) AS COD_CTA,
                                         REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'/'),'.'),'-') AS CNPJ,
                                         '' AS INFO_COMPL,
                                         T.CHAVENOTA
                                    FROM TMP_REGBLOCOM_PIS T, FILIAL F, NOTAFISCAL N, ITEMPRODUTO IT, INSCRESTAD IE
                                   WHERE T.CDREGISTRO   = 'M620'
                                     AND T.CDFILIAL     = F.CDFILIAL(+)
                                     AND T.CSTPISCOFINS IN ('49','98','99')
                                     AND ((DECODE(VRALIQCRED,7.6,'01','02') = REGM610.CDCONTRI) OR
                                          (DECODE(VRALIQCRED,3,'51','52') = REGM610.CDCONTRI))
                                     AND NVL(F.IDCUMUPISCOFIL,T.CDFILIAL)  = REGM610.IDCUMUPISCOFIL
                                     AND T.IDTPALIQ     = REGM610.IDTPALIQ
                                     AND T.VRALIQCRED   = REGM610.VRALIQCRED
                                     AND N.CDFILIAL     = SUBSTR(T.CHAVENOTA,1,4)
                                     AND N.NRLANCTONF   = SUBSTR(T.CHAVENOTA,5,10)
                                     AND N.CDFILIAL     = IT.CDFILIAL
                                     AND N.NRLANCTONF   = IT.NRLANCTONF
                                     AND N.CDINSCESTA   = IE.CDINSCESTA
                                     AND N.CDEMPRESA    = IE.CDEMPRESA
                                     AND P_VERSAO       >= '005'
                                   GROUP BY T.IDAJUSTE, T.VRBCCRED, N.NRNOTAFISC, N.NRINSJURNF, N.IDENTRSAIDOP,
                                         N.DTENTRSAID, N.DTEMISSAO, IE.NRINSJURIEST, T.CHAVENOTA
                                   UNION ALL
                                   SELECT A.IDTPAJUSTE AS IND_AJ_BC,
                                         NVL(A.VRBASECALCAJ,0) AS VL_AJ_BC,
                                         A.CDAJUSTEBC AS COD_AJ_BC,
                                         A.NRPROCESSO AS NUM_DOC,
                                         A.DSAJUSTE AS DESCR_AJ_BC,
                                         NVL(A.DTAJUSTE,P_DTFINAL) AS DT_REF,
                                         A.CDCONTCTBL AS COD_CTA,
                                         REPLACE(REPLACE(REPLACE(I.NRINSJURIEST,'/'),'.'),'-') AS CNPJ,
                                         '' AS INFO_COMPL,
                                         A.NRSEQAJUSTE AS CHAVENOTA
                                    FROM AJUSTEAPURCONTR A, FILIAL F, INSCRESTAD I
                                   WHERE A.NRSEQUMAPAPU    = V_NRMAPACOFINS
                                     AND A.IDINDICAJUSTE   = 'DB'
                                     AND A.IDCUMUPISCOFIL  = REGM610.IDCUMUPISCOFIL
                                     AND A.CDSITUTRIBITNFE IN ('01','49','98','99')
                                     AND REGM610.CDCONTRI  = DECODE(A.IDCUMUPISCOFIL,'N','51','01')
                                     AND ((V_CDCONTRIB IS NULL) OR (V_CDCONTRIB <> REGM610.CDCONTRI))
                                     AND A.CDFILIAL        = F.CDFILIAL
                                     AND F.CDEMPRESA       = I.CDEMPRESA
                                     AND F.CDINSCESTA      = I.CDINSCESTA
                                   UNION ALL
                                  SELECT A.IDTPAJUSTE AS IND_AJ_BC,
                                         NVL(A.VRBASECALCAJ,0) AS VL_AJ_BC,
                                         A.CDAJUSTEBC AS COD_AJ_BC,
                                         A.NRPROCESSO AS NUM_DOC,
                                         A.DSAJUSTE AS DESCR_AJ_BC,
                                         NVL(A.DTAJUSTE,P_DTFINAL) AS DT_REF,
                                         A.CDCONTCTBL AS COD_CTA,
                                         REPLACE(REPLACE(REPLACE(I.NRINSJURIEST,'/'),'.'),'-') AS CNPJ,
                                         '' AS INFO_COMPL,
                                         A.NRSEQAJUSTE AS CHAVENOTA
                                    FROM AJUSTEAPURCONTR A, FILIAL F, INSCRESTAD I
                                   WHERE A.NRSEQUMAPAPU    = V_NRMAPACOFINS
                                     AND A.IDINDICAJUSTE   = 'DB'
                                     AND A.IDCUMUPISCOFIL  = REGM610.IDCUMUPISCOFIL
                                     AND A.CDSITUTRIBITNFE = '02'
                                     AND REGM610.CDCONTRI  = DECODE(A.IDCUMUPISCOFIL,'N','52','02')
                                     AND ((V_CDCONTRIB IS NULL) OR (V_CDCONTRIB <> REGM610.CDCONTRI))
                                     AND A.CDFILIAL        = F.CDFILIAL
                                     AND F.CDEMPRESA       = I.CDEMPRESA
                                     AND F.CDINSCESTA      = I.CDINSCESTA
                                   UNION ALL
                                  SELECT A.IDTPAJUSTE AS IND_AJ_BC,
                                         NVL(A.VRBASECALCAJ,0) AS VL_AJ_BC,
                                         A.CDAJUSTEBC AS COD_AJ_BC,
                                         A.NRPROCESSO AS NUM_DOC,
                                         A.DSAJUSTE AS DESCR_AJ_BC,
                                         NVL(A.DTAJUSTE,P_DTFINAL) AS DT_REF,
                                         A.CDCONTCTBL AS COD_CTA,
                                         REPLACE(REPLACE(REPLACE(I.NRINSJURIEST,'/'),'.'),'-') AS CNPJ,
                                         '' AS INFO_COMPL,
                                         A.NRSEQAJUSTE AS CHAVENOTA
                                    FROM AJUSTEAPURCONTR A, FILIAL F, INSCRESTAD I
                                   WHERE A.NRSEQUMAPAPU    = V_NRMAPACOFINS
                                     AND A.IDINDICAJUSTE   = 'DB'
                                     AND A.IDCUMUPISCOFIL  = REGM610.IDCUMUPISCOFIL
                                     AND A.CDSITUTRIBITNFE = '03'
                                     AND REGM610.CDCONTRI  = DECODE(A.IDCUMUPISCOFIL,'N','53','03')
                                     AND ((V_CDCONTRIB IS NULL) OR (V_CDCONTRIB <> REGM610.CDCONTRI))
                                     AND A.CDFILIAL        = F.CDFILIAL
                                     AND F.CDEMPRESA       = I.CDEMPRESA
                                     AND F.CDINSCESTA      = I.CDINSCESTA
                                   UNION ALL
                                  SELECT A.IDTPAJUSTE AS IND_AJ_BC,
                                         NVL(A.VRBASECALCAJ,0) AS VL_AJ_BC,
                                         A.CDAJUSTEBC AS COD_AJ_BC,
                                         A.NRPROCESSO AS NUM_DOC,
                                         A.DSAJUSTE AS DESCR_AJ_BC,
                                         NVL(A.DTAJUSTE,P_DTFINAL) AS DT_REF,
                                         A.CDCONTCTBL AS COD_CTA,
                                         REPLACE(REPLACE(REPLACE(I.NRINSJURIEST,'/'),'.'),'-') AS CNPJ,
                                         '' AS INFO_COMPL,
                                         A.NRSEQAJUSTE AS CHAVENOTA
                                    FROM AJUSTEAPURCONTR A, FILIAL F, INSCRESTAD I
                                   WHERE A.NRSEQUMAPAPU    = V_NRMAPACOFINS
                                     AND A.IDINDICAJUSTE   = 'DB'
                                     AND A.IDCUMUPISCOFIL  = REGM610.IDCUMUPISCOFIL
                                     AND A.CDSITUTRIBITNFE = '05'
                                     AND REGM610.CDCONTRI  = DECODE(A.IDCUMUPISCOFIL,'N','31','32')
                                     AND ((V_CDCONTRIB IS NULL) OR (V_CDCONTRIB <> REGM610.CDCONTRI))
                                     AND A.CDFILIAL        = F.CDFILIAL
                                     AND F.CDEMPRESA       = I.CDEMPRESA
                                     AND F.CDINSCESTA      = I.CDINSCESTA)) LOOP
            V_CONTREGA := NVL(V_CONTREGA,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'662'||LPAD(RTRIM(V_CONTREGA),22,'0'),REG_M615.REGM615);

            /*VERIFICA SE O ESTABELECIMENTO JA FOI INSERIDO ANTERIORMENTE*/
            V_NRINSJUR := NULL;

            SELECT MAX(CDCHAVEREG) AS CNPJ
              INTO V_NRINSJUR
              FROM TMP_REGDADOSSPED_PIS
             WHERE CDREGISTRO = '0140'
               AND CDCHAVEREG = REG_M615.CNPJ;

            IF V_NRINSJUR IS NULL THEN
               V_CONTEST  := NVL(V_CONTEST,0)+1;
               V_REG      := NULL;
               BEGIN
                  SELECT '|0140'             ||'|'||
                         LTRIM(TO_CHAR(V_CONTEST,'000')) ||'|'||
                         MAX(E.NMRAZSOCEMPR) ||'|'||
                         REPLACE(REPLACE(REPLACE(REPLACE(MAX(IE.NRINSJURIEST),'.',''),'/',''),'-',''),'\','') ||'|'||
                         MAX(IE.SGESTADO)    ||'|'||
                         ''                  ||'|'||
                         MAX(M.CDMUNICIBGE)  ||'|'||
                         ''                  ||'|'||
                         ''                  ||'|'
                    INTO V_REG
                    FROM INSCRESTAD IE, MUNICIPIO M, EMPRESA E
                   WHERE IE.NRINSJURIEST = REG_M615.CNPJ
                     AND IE.CDPAIS       = M.CDPAIS
                     AND IE.SGESTADO     = M.SGESTADO
                     AND IE.CDMUNICIPIO  = M.CDMUNICIPIO
                     AND IE.CDEMPRESA    = P_CDEMPRESA
                     AND E.CDEMPRESA     = IE.CDEMPRESA
                   GROUP BY '0140';
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_REG:= NULL;
               END;

               V_COUNTBCL0:= NVL(V_COUNTBCL0,0)+1;
               INSERT INTO ARQMAGPISCOFINS
                  (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
               VALUES
                  (P_NRSEQARQMAG,'104'||LTRIM(TO_CHAR(V_CONTEST,'000')),NVL(V_REG,'VAZIO'));

               V_SEQUPROD := NVL(V_SEQUPROD,0)+1;
               INSERT INTO TMP_REGDADOSSPED_PIS
                 (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
                VALUES
                 ('0140', V_SEQUPROD, REG_M615.CNPJ ,NULL, LTRIM(TO_CHAR(V_CONTEST,'000')));
            END IF;

            /* CODIGO DA CONTA CONTABIL */
            BEGIN
               SELECT MAX(CDCHAVEREG)
                 INTO V_COD_CC
                 FROM TMP_REGDADOSSPED_PIS
                WHERE CDCHAVEREG = REG_M615.COD_CTA
                  AND CDREGISTRO = '0500';
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_COD_CC := NULL;
            END;

            IF (V_COD_CC IS NULL) AND (REG_M615.COD_CTA IS NOT NULL) THEN
               V_SEQCCTBL := NVL(V_SEQCCTBL,0) + 1;
               INSERT INTO TMP_REGDADOSSPED_PIS
                  (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
               VALUES
                  ('0500', V_SEQCCTBL, REG_M615.COD_CTA, NULL);
            END IF;
         END LOOP;
      END IF;

      /*REGISTRO M620 - REGISTRO DE AJUSTES*/
      FOR REG_M620 IN (SELECT '|M620'                                                                 ||'|'||
                              IDAJUSTE                                                                ||'|'||
                              REPLACE(LTRIM(TO_CHAR(SUM(NVL(VRAJUSTE,0)),'999999999990.00')),'.',',') ||'|'||
                              CDAJUSTE                                                                ||'|'||
                              NUMDOC                                                                  ||'|'||
                              DSAJUSTE                                                                ||'|'||
                              TO_CHAR(DTAJUSTE,'DDMMYYYY')                                            ||'|' AS REGM620
                              /*DEVOLUCOES VENDA(ENTRADAS) REDUZEM - DEVOLUCOES COMPRA(SAIDAS) ACRESCEM*/
                         FROM (SELECT DECODE(R.IDAJUSTE,'S','1','0') AS IDAJUSTE, R.VRAJUSTE, R.CDAJUSTE,
                                     N.NRNOTAFISC AS NUMDOC, T.NMTIPOOPER||' - '||N.NRINSJURNF AS DSAJUSTE,
                                     DECODE(N.IDENTRSAIDOP,'E',N.DTENTRSAID,N.DTEMISSAO) AS DTAJUSTE
                                FROM TMP_REGBLOCOM_PIS R, FILIAL F, NOTAFISCAL N, TIPOOPERAC T
                               WHERE R.CDREGISTRO     = 'M620'
                                 AND R.CSTPISCOFINS   IN ('49','98','99')
                                 AND R.CDFILIAL       = F.CDFILIAL
                                 AND F.IDCUMUPISCOFIL = REGM610.IDCUMUPISCOFIL
                                 AND REGM610.CDCONTRI IN ('01','02','51','52')
                                 AND N.CDFILIAL       = SUBSTR(R.CHAVENOTA,1,4)
                                 AND N.NRLANCTONF     = SUBSTR(R.CHAVENOTA,5,10)
                                 AND N.CDTIPOOPER     = T.CDTIPOOPER
                                 AND N.IDENTRSAIDOP   = T.IDENTRSAIDOP
                                 AND R.IDTPALIQ       = REGM610.IDTPALIQ
                                 AND R.VRALIQCRED     = REGM610.VRALIQCRED
                                 AND P_VERSAO         < '005'
                                 AND ((DECODE(VRALIQCRED,7.6,'01','02') = REGM610.CDCONTRI) OR
                                      (DECODE(VRALIQCRED,3,'51','52')   = REGM610.CDCONTRI))
                               UNION ALL
                              SELECT A.IDTPAJUSTE AS IDAJUSTE, A.VRAJUSTE, A.CDAJUSTE,
                                     A.NRPROCESSO AS NUMDOC, A.DSAJUSTE, A.DTAJUSTE
                                FROM AJUSTEAPURCONTR A
                               WHERE A.NRSEQUMAPAPU   = V_NRMAPACOFINS
                                 AND A.IDINDICAJUSTE  = 'DI'
                                 AND A.IDCUMUPISCOFIL = REGM610.IDCUMUPISCOFIL
                                 AND DECODE(A.CDSITUTRIBITNFE,'05',DECODE(A.IDCUMUPISCOFIL,'N','31','32'),
                                                              '03',DECODE(A.IDCUMUPISCOFIL,'N','53','03'),
                                                              '02',DECODE(A.IDCUMUPISCOFIL,'N','52','02'),
                                                              DECODE(A.IDCUMUPISCOFIL,'N','51','01')) = REGM610.CDCONTRI
                                 AND A.CDSITUTRIBITNFE IN ('01','02','03','05','49','98','99')
                                 AND ((V_CDCONTRIB IS NULL) OR (V_CDCONTRIB <> REGM610.CDCONTRI)))
                        GROUP BY IDAJUSTE, CDAJUSTE, NUMDOC, DSAJUSTE, DTAJUSTE ) LOOP

          V_CONTREGA:= NVL(V_CONTREGA,0)+1;
          INSERT INTO ARQMAGPISCOFINS
             (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
          VALUES
             (P_NRSEQARQMAG,'662'||LPAD(RTRIM(V_CONTREGA),22,'0'),REG_M620.REGM620);
      END LOOP;

      /*REGISTRO M630: INFORMACOES ADICIONAIS DE DIFERIMENTO*/
      FOR REG_M630 IN (SELECT '|M630'                                                                     ||'|'||
                              REPLACE(REPLACE(REPLACE(CNPJ,'.'),'/'),'-')                                 ||'|'||
                              REPLACE(LTRIM(TO_CHAR(SUM(NVL(VL_VEND,0)),'999999999990.00')),'.',',')      ||'|'||
                              REPLACE(LTRIM(TO_CHAR(SUM(NVL(VL_NAO_RECEB,0)),'999999999990.00')),'.',',') ||'|'||
                              REPLACE(LTRIM(TO_CHAR(SUM(NVL(VL_CONT_DIF,0)),'999999999990.00')),'.',',')  ||'|'||
                              REPLACE(LTRIM(TO_CHAR(SUM(NVL(VL_CRED_DIF,0)),'999999999990.00')),'.',',')  ||'|'||
                              CDTPCREDITO  ||'|' AS REGM630
                         FROM (SELECT CL.NRINSJURCLIE AS CNPJ,
                                      CD.VRCONTABIL AS VL_VEND, CD.VRCONTNAOREC AS VL_NAO_RECEB,
                                      SUM(NVL(CC.VRCONTRIBDIF,CD.VRCONTRIBDIF)) AS VL_CONT_DIF,
                                      SUM(NVL(CC.VRCREDDIFERIDO,CD.VRCREDDIFERIDO)) AS VL_CRED_DIF,
                                      CC.CDTPCREDITO, CD.NRSEQCONTRDIF
                                 FROM CONTRIBDIFERIDA CD, CLIENTE CL, NOTAFISCAL NF, FILIAL FL,
                                      (SELECT SUM(VRCREDDIFERIDO) AS VRCREDDIFERIDO, SUM(VRCONTRIBDIF) AS  VRCONTRIBDIF,
                                              CDTPCREDITO, NRSEQCONTRDIF
                                         FROM CONTROLCREDDIF
                                        WHERE NRSEQMAPADIF = V_NRMAPACOFINS
                                        GROUP BY CDTPCREDITO, NRSEQCONTRDIF) CC
                                WHERE CD.NRSEQMAPADIF  = V_NRMAPACOFINS
                                  AND CD.CDFILIAL      = NF.CDFILIAL
                                  AND CD.NRLANCTONF    = NF.NRLANCTONF
                                  AND NF.CDFILIEMIT    = FL.CDFILIAL
                                  AND CD.CDCONTRIBAPUR = REGM610.CDCONTRI
                                  AND CD.CDCLIENTE     = CL.CDCLIENTE
                                  AND CD.NRSEQCONTRDIF = CC.NRSEQCONTRDIF(+)
                                GROUP BY CL.NRINSJURCLIE, CD.VRCONTABIL, CD.VRCONTNAOREC, CC.VRCREDDIFERIDO, CC.CDTPCREDITO, CD.NRSEQCONTRDIF)
                         GROUP BY CNPJ, CDTPCREDITO)LOOP

          V_CONTREGA:= NVL(V_CONTREGA,0)+1;
          INSERT INTO ARQMAGPISCOFINS
             (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
          VALUES
             (P_NRSEQARQMAG,'662'||LPAD(RTRIM(V_CONTREGA),22,'0'),REG_M630.REGM630);
      END LOOP;

      V_CDCONTRIB := REGM610.CDCONTRI;
   END LOOP;

   /*GERA OS REGISTROS M610 E M620 PARA ITENS DE AJUSTES QUE POSSUEM CST PARA O QUAL NAO EXISTEM NOTAS NO PERIODO*/
   FOR REG_M610A IN (SELECT DECODE(A.CDSITUTRIBITNFE,'05',DECODE(A.IDCUMUPISCOFIL,'N','31','32'),
                                                     '03',DECODE(A.IDCUMUPISCOFIL,'N','53','03'),
                                                     '02',DECODE(A.IDCUMUPISCOFIL,'N','52','02'),
                                                          DECODE(A.IDCUMUPISCOFIL,'N','51','01')) AS CDCONTRI,
                            SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DB',VRBASECALCAJ,0),0)) AS VRBASECALCAJREDUC,
                            SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DB',VRAJUSTE,0),0)) AS VRAJUSTEREDUCBC,
                            SUM(DECODE(IDTPAJUSTE,'0',DECODE(IDINDICAJUSTE,'DI',VRAJUSTE,0),0)) AS VRAJUSTEREDUC,
                            SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DB',VRBASECALCAJ,0),0)) AS VRBASECALCAJACRES,
                            SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DB',VRAJUSTE,0),0)) AS VRAJUSTEACRESBC,
                            SUM(DECODE(IDTPAJUSTE,'1',DECODE(IDINDICAJUSTE,'DI',VRAJUSTE,0),0)) AS VRAJUSTEACRES,
                            A.IDCUMUPISCOFIL
                       FROM AJUSTEAPURCONTR A
                      WHERE A.NRSEQUMAPAPU   = V_NRMAPACOFINS
                        AND SUBSTR(A.IDINDICAJUSTE,1,1) = 'D'
                        AND A.CDSITUTRIBITNFE IN ('01','02','03','05','49','98','99')
                        AND DECODE(A.CDSITUTRIBITNFE,'05',DECODE(A.IDCUMUPISCOFIL,'N','31','32'),
                                                     '03',DECODE(A.IDCUMUPISCOFIL,'N','53','03'),
                                                     '02',DECODE(A.IDCUMUPISCOFIL,'N','52','02'),
                                                          DECODE(A.IDCUMUPISCOFIL,'N','51','01')) NOT IN (SELECT DECODE(T.CSTPISCOFINS,'05',DECODE(F.IDCUMUPISCOFIL,'N','31','32'),
                                                                                                                                       '03',DECODE(F.IDCUMUPISCOFIL,'N','53','03'),
                                                                                                                                       '02',DECODE(F.IDCUMUPISCOFIL,'N','52','02'),
                                                                                                                                            DECODE(F.IDCUMUPISCOFIL,'N','51','01'))  AS COD_CONTR
                                                                                                            FROM TMP_REGBLOCOM_PIS T, FILIAL F
                                                                                                           WHERE T.CDREGISTRO IN ('M600','M620')
                                                                                                             AND T.CDFILIAL   = F.CDFILIAL(+))
                      GROUP BY DECODE(A.CDSITUTRIBITNFE,'05',DECODE(A.IDCUMUPISCOFIL,'N','31','32'),
                                                        '03',DECODE(A.IDCUMUPISCOFIL,'N','53','03'),
                                                        '02',DECODE(A.IDCUMUPISCOFIL,'N','52','02'),
                                                             DECODE(A.IDCUMUPISCOFIL,'N','51','01')), A.IDCUMUPISCOFIL) LOOP

      IF NVL(REG_M610A.VRAJUSTEACRES,0) - NVL(REG_M610A.VRAJUSTEREDUC,0) + NVL(REG_M610A.VRAJUSTEACRESBC,0) - NVL(REG_M610A.VRAJUSTEREDUCBC,0) > 0 THEN

         IF REG_M610A.IDCUMUPISCOFIL = 'N' THEN
            V_VRAJUSTECUMU := NVL(V_VRAJUSTECUMU,0) + NVL(REG_M610A.VRAJUSTEACRES,0) - NVL(REG_M610A.VRAJUSTEREDUC,0) + NVL(REG_M610A.VRAJUSTEACRESBC,0) - NVL(REG_M610A.VRAJUSTEREDUCBC,0);
         ELSE
            V_VRAJUSTENCUM := NVL(V_VRAJUSTENCUM,0) + NVL(REG_M610A.VRAJUSTEACRES,0) - NVL(REG_M610A.VRAJUSTEREDUC,0) + NVL(REG_M610A.VRAJUSTEACRESBC,0) - NVL(REG_M610A.VRAJUSTEREDUCBC,0);
         END IF;

         V_REGM610 := '|M610'            ||'|'||
                      REG_M610A.CDCONTRI ||'|'||
                      '0,00'             ||'|'||
                      '0,00'             ||
                      CASE WHEN P_VERSAO >= '005' THEN
                         '|'||REPLACE(LTRIM(TO_CHAR(NVL(REG_M610A.VRBASECALCAJACRES,0),'999999999990.00')),'.',',')||'|'||
                         REPLACE(LTRIM(TO_CHAR(NVL(REG_M610A.VRBASECALCAJREDUC,0),'999999999990.00')),'.',',')     ||'|'||
                         REPLACE(LTRIM(TO_CHAR(NVL(REG_M610A.VRBASECALCAJACRES,0) - NVL(REG_M610A.VRBASECALCAJREDUC,0),'999999999990.00')),'.',',')
                      ELSE '' END        ||'|'||
                      ''                 ||'|'||
                      ''                 ||'|'||
                      ''                 ||'|'||
                      REPLACE(LTRIM(TO_CHAR(NVL(REG_M610A.VRAJUSTEACRESBC,0)-NVL(REG_M610A.VRAJUSTEREDUCBC,0),'999999999990.00')),'.',',') ||'|'||
                      REPLACE(LTRIM(TO_CHAR(NVL(REG_M610A.VRAJUSTEACRES,0),'999999999990.00')),'.',',') ||'|'||
                      REPLACE(LTRIM(TO_CHAR(NVL(REG_M610A.VRAJUSTEREDUC,0),'999999999990.00')),'.',',') ||'|'||
                      ''                 ||'|'||
                      ''                 ||'|'||
                      REPLACE(LTRIM(TO_CHAR(NVL(REG_M610A.VRAJUSTEACRES,0)-NVL(REG_M610A.VRAJUSTEREDUC,0)+NVL(REG_M610A.VRAJUSTEACRESBC,0)-NVL(REG_M610A.VRAJUSTEREDUCBC,0),'999999999990.00')),'.',',')||'|';

         V_CONTREGA := NVL(V_CONTREGA,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'662'||LPAD(RTRIM(V_CONTREGA),22,'0'),V_REGM610);

         IF P_VERSAO >= '005' THEN
            FOR REG_M615A IN (SELECT '|M615'                                                                  ||'|'||
                                     A.IDTPAJUSTE                                                             ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(NVL(A.VRBASECALCAJ,0),'999999999990.00')),'.',',') ||'|'||
                                     A.CDAJUSTEBC                                                             ||'|'||
                                     A.NRPROCESSO                                                             ||'|'||
                                     A.DSAJUSTE                                                               ||'|'||
                                     TO_CHAR(NVL(A.DTAJUSTE,P_DTFINAL),'DDMMYYYY')                            ||'|'||
                                     A.CDCONTCTBL                                                             ||'|'||
                                     REPLACE(REPLACE(REPLACE(I.NRINSJURIEST,'/'),'.'),'-')                    ||'|'||
                                     ''                                                                       ||'|' AS REGM615,
                                     REPLACE(REPLACE(REPLACE(I.NRINSJURIEST,'/'),'.'),'-') AS CNPJ, A.CDCONTCTBL AS COD_CTA
                                FROM AJUSTEAPURCONTR A, FILIAL F, INSCRESTAD I
                               WHERE A.NRSEQUMAPAPU    = V_NRMAPACOFINS
                                 AND A.IDINDICAJUSTE   = 'DB'
                                 AND A.CDSITUTRIBITNFE IN ('01','02','03','05','49','98','99')
                                 AND A.IDCUMUPISCOFIL  = REG_M610A.IDCUMUPISCOFIL
                                 AND DECODE(A.CDSITUTRIBITNFE,'05',DECODE(A.IDCUMUPISCOFIL,'N','31','32'),
                                                              '03',DECODE(A.IDCUMUPISCOFIL,'N','53','03'),
                                                              '02',DECODE(A.IDCUMUPISCOFIL,'N','52','02'),
                                                                   DECODE(A.IDCUMUPISCOFIL,'N','51','01')) = REG_M610A.CDCONTRI
                                 AND A.CDFILIAL        = F.CDFILIAL
                                 AND F.CDEMPRESA       = I.CDEMPRESA
                                 AND F.CDINSCESTA      = I.CDINSCESTA) LOOP

                V_CONTREGA := NVL(V_CONTREGA,0)+1;
               INSERT INTO ARQMAGPISCOFINS
                  (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
               VALUES
                  (P_NRSEQARQMAG,'662'||LPAD(RTRIM(V_CONTREGA),22,'0'),REG_M615A.REGM615);

               /*VERIFICA SE O ESTABELECIMENTO JA FOI INSERIDO ANTERIORMENTE*/
               V_NRINSJUR := NULL;

               SELECT MAX(CDCHAVEREG) AS CNPJ
                 INTO V_NRINSJUR
                 FROM TMP_REGDADOSSPED_PIS
                WHERE CDREGISTRO = '0140'
                  AND CDCHAVEREG = REG_M615A.CNPJ;

               IF V_NRINSJUR IS NULL THEN
                  V_CONTEST  := NVL(V_CONTEST,0)+1;
                  V_REG      := NULL;
                  BEGIN
                     SELECT '|0140'             ||'|'||
                            LTRIM(TO_CHAR(V_CONTEST,'000')) ||'|'||
                            MAX(E.NMRAZSOCEMPR) ||'|'||
                            REPLACE(REPLACE(REPLACE(REPLACE(MAX(IE.NRINSJURIEST),'.',''),'/',''),'-',''),'\','') ||'|'||
                            MAX(IE.SGESTADO)    ||'|'||
                            ''                  ||'|'||
                            MAX(M.CDMUNICIBGE)  ||'|'||
                            ''                  ||'|'||
                            ''                  ||'|'
                       INTO V_REG
                       FROM INSCRESTAD IE, MUNICIPIO M, EMPRESA E
                      WHERE IE.NRINSJURIEST = REG_M615A.CNPJ
                        AND IE.CDPAIS       = M.CDPAIS
                        AND IE.SGESTADO     = M.SGESTADO
                        AND IE.CDMUNICIPIO  = M.CDMUNICIPIO
                        AND IE.CDEMPRESA    = P_CDEMPRESA
                        AND E.CDEMPRESA     = IE.CDEMPRESA
                      GROUP BY '0140';
                  EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                        V_REG:= NULL;
                  END;

                  V_COUNTBCL0:= NVL(V_COUNTBCL0,0)+1;
                  INSERT INTO ARQMAGPISCOFINS
                     (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
                  VALUES
                     (P_NRSEQARQMAG,'104'||LTRIM(TO_CHAR(V_CONTEST,'000')),NVL(V_REG,'VAZIO'));

                  V_SEQUPROD := NVL(V_SEQUPROD,0)+1;
                  INSERT INTO TMP_REGDADOSSPED_PIS
                    (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
                   VALUES
                    ('0140', V_SEQUPROD, REG_M615A.CNPJ ,NULL, LTRIM(TO_CHAR(V_CONTEST,'000')));
               END IF;

               /* CODIGO DA CONTA CONTABIL */
               BEGIN
                  SELECT MAX(CDCHAVEREG)
                    INTO V_COD_CC
                    FROM TMP_REGDADOSSPED_PIS
                   WHERE CDCHAVEREG = REG_M615A.COD_CTA
                     AND CDREGISTRO = '0500';
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_COD_CC := NULL;
               END;

               IF (V_COD_CC IS NULL) AND (REG_M615A.COD_CTA IS NOT NULL) THEN
                  V_SEQCCTBL := NVL(V_SEQCCTBL,0) + 1;
                  INSERT INTO TMP_REGDADOSSPED_PIS
                     (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
                  VALUES
                     ('0500', V_SEQCCTBL, REG_M615A.COD_CTA, NULL);
               END IF;
            END LOOP;
         END IF;

         FOR REG_M620A IN (SELECT '|M620'                                                                 ||'|'||
                                  IDTPAJUSTE                                                              ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(SUM(NVL(VRAJUSTE,0)),'999999999990.00')),'.',',') ||'|'||
                                  CDAJUSTE                                                                ||'|'||
                                  NRPROCESSO                                                              ||'|'||
                                  DSAJUSTE                                                                ||'|'||
                                  TO_CHAR(DTAJUSTE,'DDMMYYYY')                                            ||'|' AS REGM620
                             FROM AJUSTEAPURCONTR A
                            WHERE A.NRSEQUMAPAPU    = V_NRMAPACOFINS
                              AND A.IDINDICAJUSTE   = 'DI'
                              AND A.CDSITUTRIBITNFE IN ('01','02','03','05','49','98','99')
                              AND A.IDCUMUPISCOFIL  = REG_M610A.IDCUMUPISCOFIL
                              AND DECODE(A.CDSITUTRIBITNFE,'05',DECODE(A.IDCUMUPISCOFIL,'N','31','32'),
                                                           '03',DECODE(A.IDCUMUPISCOFIL,'N','53','03'),
                                                           '02',DECODE(A.IDCUMUPISCOFIL,'N','52','02'),
                                                                DECODE(A.IDCUMUPISCOFIL,'N','51','01')) = REG_M610A.CDCONTRI
                            GROUP BY IDTPAJUSTE, CDAJUSTE, NRPROCESSO, DSAJUSTE, DTAJUSTE) LOOP

            V_CONTREGA:= NVL(V_CONTREGA,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'662'||LPAD(RTRIM(V_CONTREGA),22,'0'),REG_M620A.REGM620);
         END LOOP;
      END IF;
   END LOOP;

   /*BUSCA O CREDITO ANTERIOR UTILIZADO NO PERIODO*/
   V_CREDANTERIOR := 0;
   BEGIN
      SELECT NVL(SUM(VRSALDOCREDORUT),0)
        INTO V_CREDANTERIOR
        FROM CREDANTSPED C
       WHERE C.NRSEQUMAPAPU  <> V_NRMAPACOFINS
         AND C.CDEMPRESA     = P_CDEMPRESA
         AND C.DTUTILIZACRED = P_DTINICIAL
         AND C.IDTPCREDANT   = 'CA'
         AND C.CDREGISTRO    = '1500';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_CREDANTERIOR := 0;
   END;

   /*BUSCA O VALOR DO IMPOSTO RETIDO UTILIZADO NO PERIODO*/
   V_IMPRETIDONCUM := 0;
   V_IMPRETIDOCUM  := 0;
   BEGIN
      SELECT NVL(SUM(DECODE(IDCUMUPISCOFIL,'S',VRSALDOCREDORUT,0)),0),
             NVL(SUM(DECODE(IDCUMUPISCOFIL,'N',VRSALDOCREDORUT,0)),0)
        INTO V_IMPRETIDONCUM, V_IMPRETIDOCUM
        FROM CREDANTSPED C
       WHERE C.DTUTILIZACRED = P_DTINICIAL
         AND C.CDEMPRESA     = P_CDEMPRESA
         AND C.IDTPCREDANT   = 'CO'
         AND C.CDREGISTRO    = '1700';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_IMPRETIDONCUM := 0;
         V_IMPRETIDOCUM  := 0;
   END;

   /*BUSCA O VALOR DAS DEDUCOES CUMULATIVO E NAO CUMULATIVO NO PERIODO*/
   V_VRDEDUCAONCUM := 0;
   V_VRDEDUCAOCUMU := 0;
   BEGIN
      SELECT NVL(SUM(DECODE(I.IDCUMUPISCOFIL,'S',I.VRITEMDETAPU,0)),0) AS VRDEDUCAONCUM,
             NVL(SUM(DECODE(I.IDCUMUPISCOFIL,'N',I.VRITEMDETAPU,0)),0) AS VRDEDUCAOCUMU
        INTO V_VRDEDUCAONCUM, V_VRDEDUCAOCUMU
        FROM ITDETAPUCONTR I
       WHERE I.NRSEQUMAPAPU = V_NRMAPACOFINS
         AND I.IDTIPODETAPU = 'DD'
       GROUP BY I.IDCUMUPISCOFIL;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_VRDEDUCAONCUM := 0;
         V_VRDEDUCAOCUMU := 0;
   END;

   /*REGISTRO M600(UM POR ARQUIVO)*/
   FOR REG_M600 IN (SELECT '|M600'                                                                                          ||'|'||
                           REPLACE(LTRIM(TO_CHAR(VRDEBITONCUM,   '999999999990.00')),'.',',') ||'|'|| /*CONTRIB. NAO CUMULATIVA*/
                           REPLACE(LTRIM(TO_CHAR(VRCREDNCUM,     '999999999990.00')),'.',',') ||'|'|| /*CREDITO UTILIZADO DO PROPRIO PERIODO - NAO CUMULATIVO*/
                           REPLACE(LTRIM(TO_CHAR(VRCREDANTNCUM,  '999999999990.00')),'.',',') ||'|'|| /*CREDITO UTILIZADO DE PERIODOS ANTERIORES - NAO CUMULATIVO*/
                           REPLACE(LTRIM(TO_CHAR(VRIMPDEVIDONCUM,'999999999990.00')),'.',',') ||'|'|| /*VR. DEVIDO NAO CUMULATIVO*/
                           REPLACE(LTRIM(TO_CHAR(VRIMPRETIDONCUM,'999999999990.00')),'.',',') ||'|'|| /*RETENCAO NA FONTE NAO CUMULATIVO*/
                           REPLACE(LTRIM(TO_CHAR(V_VRDEDUCAONCUM,'999999999990.00')),'.',',') ||'|'|| /*DEDUCOES NAO CUMULATIVO*/
                           REPLACE(LTRIM(TO_CHAR(CASE WHEN VRIMPDEVIDONCUM - VRIMPRETIDONCUM - V_VRDEDUCAONCUM > 0 THEN VRIMPDEVIDONCUM - VRIMPRETIDONCUM - V_VRDEDUCAONCUM ELSE 0 END,'999999999990.00')),'.',',') ||'|'|| /*VR. NAO CUMULATIVO A PAGAR*/
                           REPLACE(LTRIM(TO_CHAR(VRDEBITOCUMU,   '999999999990.00')),'.',',') ||'|'|| /*CONTRIB. CUMULATIVA*/
                           REPLACE(LTRIM(TO_CHAR(VRIMPRETIDOCUMU,'999999999990.00')),'.',',') ||'|'|| /*RETENCAO NA FONTE CUMULATIVO*/
                           REPLACE(LTRIM(TO_CHAR(V_VRDEDUCAOCUMU,'999999999990.00')),'.',',') ||'|'|| /*DEDUCOES CUMULATIVO*/
                           REPLACE(LTRIM(TO_CHAR(CASE WHEN VRDEBITOCUMU - VRIMPRETIDOCUMU - V_VRDEDUCAOCUMU > 0 THEN VRDEBITOCUMU - VRIMPRETIDOCUMU - V_VRDEDUCAOCUMU ELSE 0 END,'999999999990.00')),'.',',')       ||'|'|| /*VR. CUMULATIVO A PAGAR*/
                           REPLACE(LTRIM(TO_CHAR(CASE WHEN (CASE WHEN VRIMPDEVIDONCUM-VRIMPRETIDONCUM-V_VRDEDUCAONCUM > 0 THEN VRIMPDEVIDONCUM-VRIMPRETIDONCUM-V_VRDEDUCAONCUM ELSE 0 END + CASE WHEN VRDEBITOCUMU-VRIMPRETIDOCUMU-V_VRDEDUCAOCUMU > 0 THEN VRDEBITOCUMU-VRIMPRETIDOCUMU-V_VRDEDUCAOCUMU ELSE 0 END) > 0
                                                      THEN (CASE WHEN VRIMPDEVIDONCUM-VRIMPRETIDONCUM-V_VRDEDUCAONCUM > 0 THEN VRIMPDEVIDONCUM-VRIMPRETIDONCUM-V_VRDEDUCAONCUM ELSE 0 END + CASE WHEN VRDEBITOCUMU-VRIMPRETIDOCUMU-V_VRDEDUCAOCUMU > 0 THEN VRDEBITOCUMU-VRIMPRETIDOCUMU-V_VRDEDUCAOCUMU ELSE 0 END) ELSE 0 END,'999999999990.00')),'.',',') ||'|' AS REGM600,
                           /*BUSCA OS VALORES DE DEBITO NAO CUMULATIVO E CUMULATIVO PARA GERAR O REGISTRO M605*/
                           CASE WHEN VRIMPDEVIDONCUM - VRIMPRETIDONCUM - V_VRDEDUCAONCUM > 0 THEN VRIMPDEVIDONCUM - VRIMPRETIDONCUM - V_VRDEDUCAONCUM ELSE 0 END AS VRIMPRECOLNCUM,
                           CASE WHEN VRDEBITOCUMU - VRIMPRETIDOCUMU - V_VRDEDUCAOCUMU > 0 THEN VRDEBITOCUMU - VRIMPRETIDOCUMU - V_VRDEDUCAOCUMU ELSE 0 END AS VRIMPRECOLCUM
                      FROM (SELECT CASE WHEN NVL(VRDEBITONCUM,0) + V_VRAJUSTENCUM + V_VRDIFERIMENTONCUM > 0
                                        THEN NVL(VRDEBITONCUM,0) + V_VRAJUSTENCUM + V_VRDIFERIMENTONCUM
                                        ELSE 0 END AS VRDEBITONCUM,
                                   DECODE(P_CDINCTRIB,'2',0,NVL(V_VRCREDCOFINS, 0)) AS VRCREDNCUM, DECODE(P_CDINCTRIB,'2',0,NVL(V_CREDANTERIOR,0)) AS VRCREDANTNCUM,
                                   CASE WHEN NVL(VRDEBITONCUM,0) + V_VRAJUSTENCUM + V_VRDIFERIMENTONCUM - DECODE(P_CDINCTRIB,'2',0,NVL(V_VRCREDCOFINS, 0)) - DECODE(P_CDINCTRIB,'2',0,NVL(V_CREDANTERIOR,0)) > 0
                                        THEN NVL(VRDEBITONCUM,0) + V_VRAJUSTENCUM + V_VRDIFERIMENTONCUM - DECODE(P_CDINCTRIB,'2',0,NVL(V_VRCREDCOFINS, 0)) - DECODE(P_CDINCTRIB,'2',0,NVL(V_CREDANTERIOR,0))
                                        ELSE 0 END AS VRIMPDEVIDONCUM,
                                   CASE WHEN NVL(VRDEBITOCUM,0) + V_VRAJUSTECUMU + V_VRDIFERIMENTOCUMU> 0
                                        THEN NVL(VRDEBITOCUM,0) + V_VRAJUSTECUMU + V_VRDIFERIMENTOCUMU
                                        ELSE 0 END AS VRDEBITOCUMU,
                                   V_IMPRETIDONCUM AS VRIMPRETIDONCUM, V_IMPRETIDOCUM AS VRIMPRETIDOCUMU
                              FROM (SELECT SUM(DECODE(NVL(F.IDCUMUPISCOFIL,T.CDFILIAL),'S',NVL(VRCRED,0),0)) AS VRDEBITONCUM,
                                           SUM(DECODE(NVL(F.IDCUMUPISCOFIL,T.CDFILIAL),'N',NVL(VRCRED,0),0)) AS VRDEBITOCUM
                               FROM TMP_REGBLOCOM_PIS T, FILIAL F
                              WHERE T.CDREGISTRO = 'M600'
                                AND T.CDFILIAL   = F.CDFILIAL(+))) /*USA ALTER JOIN NA FILIAL DEVIDO AS RECEITAS FINANCEIRAS, QUE NAO TEM FILIAL, E O PROPRIO CAMPO JA TRAZ O REGIME*/
                     UNION
                    SELECT '|M600|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|' AS REGM600,
                           0 AS VRIMPRECOLCUM,
                           0 AS VRIMPRECOLNCUM
                      FROM DUAL
                     WHERE P_GERABCLP = '2')LOOP

      V_CONTREGA:= NVL(V_CONTREGA,0)+1;
      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'660'||LPAD(RTRIM(V_CONTREGA),22,'0'),REG_M600.REGM600);

      /*REGISTRO M605 - OBRIGATORIO A PARTIR DE ABRIL DE 2014*/
      /*GERA O REGISTRO CASO TENHA VALOR DE IMPOSTO A RECOLHER PARA CUMULATIVO OU NAO CUMULATIVO DE ACORDO COM OS CODIGOS DA RECEITA INFORMADOS NO MAPA*/
      BEGIN
         SELECT NVL(NVL(CDRECECUMU, CDRECEITACONTR||CDVARIACAO),CDRECEGUIA||NVL(CDVARIACAO,'01')), /*CASO OS ARQUIVOS GERADOS SEJAM DE MAPAS ANTIGOS PEGA O CODIGO DA RECEITA DO CAMPO CDRECEITACONTR CONCACTENADO COM A VARIACAO*/
                NVL(NVL(CDRECENCUMU,CDRECEITACONTR||CDVARIACAO),CDRECEGUIA||NVL(CDVARIACAO,'01'))
           INTO V_CDRECECOFINSCUMU, V_CDRECECOFINSNCUMU
           FROM MAPAAPURAC
          WHERE CDEMPRESA = P_CDEMPRESA
            AND CDIMPOSTO = P_COFINS
            AND DTINIPERIODO = P_DTINICIAL
            AND DTFIMPERIODO = P_DTFINAL;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDRECECOFINSCUMU  := NULL;
            V_CDRECECOFINSNCUMU := NULL;
      END;

      IF ((NVL(REG_M600.VRIMPRECOLCUM,0) > 0) OR (NVL(REG_M600.VRIMPRECOLNCUM,0) > 0)) AND ((P_DTINICIAL > '31/03/2014')) THEN     /*VERIFICA SE TEVE IMPOSTO A RECOLHER SE O MES E MAIOR QUE MARCO DE 2014 PARA GERAR O RESGISTRO M605*/
         IF NVL(REG_M600.VRIMPRECOLCUM,0) > 0 THEN /*VERIFICA SE TEVE DEBITO CUMULATIVO*/
            V_REGM605_C := '|M605'            ||'|'||
                           '12'               ||'|'||
                           V_CDRECECOFINSCUMU ||'|'||
                           REPLACE(LTRIM(TO_CHAR(REG_M600.VRIMPRECOLCUM,'999999999990.00')),'.',',')  ||'|';

            V_CONTREGA:= NVL(V_CONTREGA,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'661'||LPAD(RTRIM(V_CONTREGA),22,'0'),V_REGM605_C);
         END IF;
         IF NVL(REG_M600.VRIMPRECOLNCUM,0) > 0 THEN /*VERIFICA SE TEVE DEBITO NAO CUMULATIVO*/
            V_REGM605_NC := '|M605'             ||'|'||
                            '08'                ||'|'||
                            V_CDRECECOFINSNCUMU ||'|'||
                            REPLACE(LTRIM(TO_CHAR(REG_M600.VRIMPRECOLNCUM,'999999999990.00')),'.',',')  ||'|';

            V_CONTREGA:= NVL(V_CONTREGA,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'661'||LPAD(RTRIM(V_CONTREGA),22,'0'),V_REGM605_NC);
         END IF;
      END IF;
   END LOOP;

   /*REGISTRO M300: CONTRIBUICAO DE PIS/PASEP DIFERIDA EM PERIODOS ANTERIORES  VALORES A PAGAR NO PERIODO.*/
   FOR REG_M700 IN (SELECT '|M700'            ||'|'||
                           CD.CDCONTRIBAPUR   ||'|'||
                           REPLACE(LTRIM(TO_CHAR(SUM(NVL(CC.VRCONTRIBDIFAT, CD.VRCONTRIBDIFAT)),  '999999999990.00')),'.',',') ||'|'||
                           CC.CDNATUCREDDIF   ||'|'||
                           REPLACE(LTRIM(TO_CHAR(SUM(NVL(CC.VRCREDDIFAT, CD.VRCREDDIFAT)),        '999999999990.00')),'.',',') ||'|'||
                           REPLACE(LTRIM(TO_CHAR(SUM(NVL(CC.VRCONTRIBDIFANT, CD.VRCONTRIBDIFANT)),'999999999990.00')),'.',',') ||'|'||
                           TO_CHAR(MP.DTINIPERIODO,'MMYYYY')    ||'|'||
                           TO_CHAR(CD.DTRECEBIMENTO,'DDMMYYYY') ||'|' AS REGM700
                      FROM CONTRIBDIFERIDA CD, MAPAAPURAC MP,
                           (SELECT C.VRCONTRIBDIFAT, C.CDNATUCREDDIF, C.VRCREDDIFAT, C.VRCONTRIBDIFANT, C.NRSEQCONTRDIF
                              FROM CONTROLCREDDIF C
                             WHERE C.NRSEQMAPADIF = V_NRMAPACOFINS
                               AND C.VRCONTRIBDIFANT > 0) CC
                     WHERE CD.NRSEQMAPADIF  = V_NRMAPACOFINS
                       AND CD.NRSEQUMAPAPU  = MP.NRSEQUMAPAPU
                       AND CD.VRCONTRIBDIFANT > 0
                       AND CD.NRSEQCONTRDIF = CC.NRSEQCONTRDIF(+)
                     GROUP BY CD.CDCONTRIBAPUR, CC.CDNATUCREDDIF, MP.DTINIPERIODO, CD.DTRECEBIMENTO) LOOP

       V_CONTREGA:= NVL(V_CONTREGA,0)+1;
       INSERT INTO ARQMAGPISCOFINS
          (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
       VALUES
          (P_NRSEQARQMAG,'670'||LPAD(RTRIM(V_CONTREGA),22,'0'),REG_M700.REGM700);
   END LOOP;

   /*REGISTRO M800*/
   FOR REGM800 IN (SELECT '|M800'       ||'|'||
                          CSTPISCOFINS  ||'|'||
                          REPLACE(LTRIM(TO_CHAR(SUM(VRTOTNF),'999999999990.00')),'.',',')||'|'||
                          CDCONTCTBL    ||'|'||
                          ''            ||'|' AS REGISTRO, CSTPISCOFINS, CDCONTCTBL
                     FROM TMP_REGBLOCOM_PIS
                    WHERE CDREGISTRO   = 'M600'
                      AND CSTPISCOFINS IN ('04','05','06','07','08','09')
                    GROUP BY 'M800', CSTPISCOFINS,CDCONTCTBL)LOOP

      V_CONTREGA:= NVL(V_CONTREGA,0)+1;
      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'680'||LPAD(RTRIM(V_CONTREGA),22,'0'),REGM800.REGISTRO);

      /*REGISTRO M810*/
      FOR REGM810 IN (SELECT '|M810'       ||'|'||
                             CDNATUREC     ||'|'||
                             REPLACE(LTRIM(TO_CHAR(SUM(VRTOTNF),'999999999990.00')),'.',',')||'|'||
                             CDCONTCTBL    ||'|'||
                             ''            ||'|' AS REGISTRO
                        FROM TMP_REGBLOCOM_PIS
                       WHERE CDREGISTRO   = 'M600'
                         AND CSTPISCOFINS = REGM800.CSTPISCOFINS
                         AND NVL(CDCONTCTBL,'T')   = NVL(REGM800.CDCONTCTBL,'T')
                       GROUP BY 'M810', CDNATUREC, CDCONTCTBL)LOOP

         V_CONTREGA:= NVL(V_CONTREGA,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'680'||LPAD(RTRIM(V_CONTREGA),22,'0'),REGM810.REGISTRO);
      END LOOP;
   END LOOP;
   P_COUNTBLC0 := V_COUNTBCL0;
   RETURN(V_CONTREGA);
END;
/