CREATE OR REPLACE FUNCTION SPED_PIS_COFINS_BLOCO1(
   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_PIS          IN VARCHAR2,
   P_COFINS       IN VARCHAR2,
   P_GERABCLP     IN VARCHAR2,
   P_CRTESCRITUR  IN VARCHAR2,
   P_VERSAO       IN VARCHAR2,
   P_CONTREGA     IN DOUBLE PRECISION,
   P_CONTEST      IN NUMBER,
   P_CONTESTAB    OUT NUMBER,
   P_COUNTBLC0    OUT NUMBER) RETURN NUMBER
AS
   V_CONTREGA      NUMBER;
   V_SEQCCTBL      NUMBER;
   V_NRSEQUMAPPIS  VARCHAR2(10);
   V_OLDESTAB      VARCHAR2(20);
   V_REG_0140      VARCHAR2(1000);
   V_CONTEST       NUMBER;
   V_CONTBLC0      NUMBER;
   V_SEQUPROD      NUMBER;
   V_COD_CC        VARCHAR2(20);
   V_CONTREG       NUMBER;
   V_REGCOMP       VARCHAR2(1000);
   V_VRSALDOCREDOR      FLOAT;
   V_VRSALDOCREDUTANT   FLOAT;
   V_VRSALDOCREDORUT    FLOAT;
   V_VRSALDOCREDFIN     FLOAT;
   V_COUNTRATEIO        NUMBER;
   V_VRSALCREDORACUM    FLOAT;
   V_VRSALCREDUTANTACUM FLOAT;
   V_VRSALCREDORUTACUM  FLOAT;
   V_VRSALCREDFINACUM   FLOAT;
   V_RATEIOCRED         NUMBER;
   V_REG_RATEIOCRED     VARCHAR2(1000);
BEGIN
   V_CONTREGA := P_CONTREGA;

   SELECT MAX(CDCONTREGI)
     INTO V_SEQUPROD
     FROM TMP_REGDADOSSPED_PIS;

   /*REGISTRO 1010*/
   FOR C_REG1010 IN (SELECT DISTINCT '|1010'                ||'|'||
                            P.NRPROCESSO                    ||'|'||
                            P.CDSECAO                       ||'|'||
                            P.CDVARA                        ||'|'||
                            P.IDNATUACAO                    ||'|'||
                            P.DSOBSERVACAO                  ||'|'||
                            TO_CHAR(P.DTDECISAO,'DDMMYYYY') ||'|' AS REG_1010
                       FROM PROCESSOFIS P, IMPOPROCESSOFIS I
                      WHERE P.CDEMPRESA    = P_CDEMPRESA
                        AND P.CDEMPRESA    = I.CDEMPRESA
                        AND P.CDPROCESSO   = I.CDPROCESSO
                        AND I.CDIMPOSTO    IN ('50','60')
                        AND P.TPPROCESSO   = '2'
                        AND P.DTINIVALID   <= P_DTFINAL
                        AND (P.DTFIMVALID  >= P_DTFINAL OR P.DTFIMVALID IS NULL)) LOOP

      V_CONTREGA:= NVL(V_CONTREGA,0)+1;
      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'8010'||V_CONTREGA,C_REG1010.REG_1010);
   END LOOP;

   /*REGISTRO 1020*/
   FOR C_REG1020 IN (SELECT DISTINCT '|1020'                ||'|'||
                            P.NRPROCESSO                    ||'|'||
                            P.IDNATUACAO                    ||'|'||
                            TO_CHAR(P.DTDECISAO,'DDMMYYYY') ||'|' AS REG_1020
                       FROM PROCESSOFIS P, IMPOPROCESSOFIS I
                      WHERE P.CDEMPRESA    = P_CDEMPRESA
                        AND P.CDEMPRESA    = I.CDEMPRESA
                        AND P.CDPROCESSO   = I.CDPROCESSO
                        AND I.CDIMPOSTO    IN ('50','60')
                        AND P.TPPROCESSO   = '1'
                        AND P.DTINIVALID   <= P_DTFINAL
                        AND (P.DTFIMVALID  >= P_DTFINAL OR P.DTFIMVALID IS NULL)) LOOP

      V_CONTREGA:= NVL(V_CONTREGA,0)+1;
      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'8010'||V_CONTREGA,C_REG1020.REG_1020);
   END LOOP;

   IF P_VERSAO >= '005' THEN
      /* REGISTRO 1050 */
      FOR C_REG1050 IN (SELECT '|1050'         ||'|'||
                               TO_CHAR(NVL(A.DTAJUSTE,P_DTFINAL),'DDMMYYYY') ||'|'||
                               A.CDAJUSTEBC    ||'|'||
                               I.NRINSJURIEST  ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(NVL(A.VRBASECALCAJ,0)),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(DECODE(A.CDSITUTRIBITNFE,'01',NVL(A.VRBASECALCAJ,0),0)),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(DECODE(A.CDSITUTRIBITNFE,'02',NVL(A.VRBASECALCAJ,0),0)),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(DECODE(A.CDSITUTRIBITNFE,'03',NVL(A.VRBASECALCAJ,0),0)),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(DECODE(A.CDSITUTRIBITNFE,'04',NVL(A.VRBASECALCAJ,0),0)),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(DECODE(A.CDSITUTRIBITNFE,'05',NVL(A.VRBASECALCAJ,0),0)),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(DECODE(A.CDSITUTRIBITNFE,'06',NVL(A.VRBASECALCAJ,0),0)),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(DECODE(A.CDSITUTRIBITNFE,'07',NVL(A.VRBASECALCAJ,0),0)),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(DECODE(A.CDSITUTRIBITNFE,'08',NVL(A.VRBASECALCAJ,0),0)),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(DECODE(A.CDSITUTRIBITNFE,'09',NVL(A.VRBASECALCAJ,0),0)),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(DECODE(A.CDSITUTRIBITNFE,'49',NVL(A.VRBASECALCAJ,0),0)),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(DECODE(A.CDSITUTRIBITNFE,'99',NVL(A.VRBASECALCAJ,0),0)),'999999999990.00')),'.',',') ||'|'||
                               A.IDAPROPRIACAO ||'|'||
                               A.NRRECIBO      ||'|'||
                               ''              ||'|' AS REG_1050
                          FROM AJUSTEAPURCONTR A, FILIAL F, INSCRESTAD I, 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  = A.NRSEQUMAPAPU
                           AND A.CDFILIAL      = F.CDFILIAL
                           AND F.CDEMPRESA     = I.CDEMPRESA
                           AND F.CDINSCESTA    = I.CDINSCESTA
                           AND A.IDINDICAJUSTE = 'DB'
                         GROUP BY A.DTAJUSTE, A.CDAJUSTEBC, I.NRINSJURIEST, A.IDAPROPRIACAO, A.NRRECIBO) LOOP

         V_CONTREGA:= NVL(V_CONTREGA,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'8010'||V_CONTREGA,C_REG1050.REG_1050);

      END LOOP;
   END IF;

   IF (P_GERABCLP IS NULL) OR (P_GERABCLP IN ('0','1')) THEN 
      V_RATEIOCRED := 0;
      /*VERIFICA SE TEM RATEIO POR TIPO DE CREDITO PARA O MAPA*/
      BEGIN
         SELECT COUNT(*)
           INTO V_RATEIOCRED
           FROM CREDANTSPED C, RATEIOCREDMAPA R
          WHERE C.DTUTILIZACRED = P_DTINICIAL
            AND C.CDEMPRESA     = P_CDEMPRESA
            AND C.IDTPCREDANT   = 'CA'
            AND C.CDREGISTRO    = '1100'
            AND C.NRSEQUMAPAPU  = R.NRSEQUMAPAPU;
       EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_RATEIOCRED := 0;
       END;
      /*CASO NAO TENHA RATEIO POR TIPO DE CREDIDO, O ARQUIVO E MONTADO COM O TIPO DE CREDITO FIXO*/
      IF V_RATEIOCRED = 0 THEN
         /*REGISTRO 1100 - CREDITO DE PERIODO ANTERIOR PIS*/
         FOR C_1100 IN (SELECT '|1100'                                                               ||'|'||
                               TO_CHAR(M.DTINIPERIODO,'MMYYYY')                                      ||'|'||
                               '01'                                                                  ||'|'||
                               ''                                                                    ||'|'||
                               '199'                                                                 ||'|'||
                               REPLACE(LTRIM(TO_CHAR(C.VRSALDOCREDOR,'999999999990.00')),'.',',')    ||'|'||
                               '0'                                                                   ||'|'||
                               REPLACE(LTRIM(TO_CHAR(C.VRSALDOCREDOR,'999999999990.00')),'.',',')    ||'|'||
                               REPLACE(LTRIM(TO_CHAR(C.VRSALDOCREDUTANT,'999999999990.00')),'.',',') ||'|'||
                               '0'                                                                   ||'|'||
                               '0'                                                                   ||'|'||
                               REPLACE(LTRIM(TO_CHAR(NVL(C.VRSALDOCREDORUT,0) + NVL(C.VRSALDOCREDFIN,0),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(C.VRSALDOCREDORUT,'999999999990.00')),'.',',')  ||'|'||
                               '0'                                                                   ||'|'||
                               '0'                                                                   ||'|'||
                               '0'                                                                   ||'|'||
                               '0'                                                                   ||'|'||
                               REPLACE(LTRIM(TO_CHAR(C.VRSALDOCREDFIN,'999999999990.00')),'.',',')   ||'|' AS REG_1100
                          FROM CREDANTSPED C, MAPAAPURAC M
                         WHERE C.DTUTILIZACRED = P_DTINICIAL
                           AND C.CDEMPRESA     = P_CDEMPRESA
                           AND C.IDTPCREDANT   = 'CA'
                           AND C.CDREGISTRO    = '1100'
                           AND C.NRSEQUMAPAPU  = M.NRSEQUMAPAPU
                         ORDER BY M.NRSEQUMAPAPU) LOOP

            V_CONTREGA := NVL(V_CONTREGA,0) +1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'8013'||LPAD(V_CONTREGA,10,'0'),C_1100.REG_1100);
         END LOOP;
      ELSE
         /*E REALIZADO O CALCULO PROPORCIONAL NOS SALDOS DOS CREDITOS ANTERIORES SSEPARANDO POR TIPO DE CREDITO*/
         /*NA TABELA RATEIOCREDMAPA CONTEM O PERCENTUAL CORRESPONDENTE AO TIPO DE CREDITO, SENDO ESTA PREENCHIDA NA PROCEDURE DE APURACAO DO IMPOSTO */
         V_VRSALDOCREDOR     := 0;
         V_VRSALDOCREDUTANT  := 0;
         V_VRSALDOCREDORUT   := 0;
         V_VRSALDOCREDFIN    := 0;

         V_COUNTRATEIO       := 0;

         V_VRSALCREDORACUM    := 0;
         V_VRSALCREDUTANTACUM := 0;
         V_VRSALCREDORUTACUM  := 0;
         V_VRSALCREDFINACUM   := 0;

         FOR C_RATEIOCRED IN (SELECT C.NRSEQUMAPAPU, M.DTINIPERIODO, C.VRSALDOCREDOR, C.VRSALDOCREDUTANT, C.VRSALDOCREDORUT, C.VRSALDOCREDFIN,
                               R.VRPERCREDITO, R.CDTPCREDPICOITE
                              FROM CREDANTSPED C, MAPAAPURAC M, RATEIOCREDMAPA R
                             WHERE C.DTUTILIZACRED = P_DTINICIAL
                               AND C.CDEMPRESA     = P_CDEMPRESA
                               AND C.IDTPCREDANT   = 'CA'
                               AND C.CDREGISTRO    = '1100'
                               AND C.NRSEQUMAPAPU  = M.NRSEQUMAPAPU
                               AND R.NRSEQUMAPAPU  = M.NRSEQUMAPAPU
                               ORDER BY C.NRSEQUMAPAPU, R.CDTPCREDPICOITE) LOOP
                               
               V_VRSALDOCREDOR    := ROUND((C_RATEIOCRED.VRSALDOCREDOR * C_RATEIOCRED.VRPERCREDITO) / 100,2);
               V_VRSALDOCREDUTANT := ROUND((C_RATEIOCRED.VRSALDOCREDUTANT * C_RATEIOCRED.VRPERCREDITO) / 100,2);
               V_VRSALDOCREDORUT  := ROUND((C_RATEIOCRED.VRSALDOCREDORUT * C_RATEIOCRED.VRPERCREDITO) / 100,2);
               V_VRSALDOCREDFIN   := ROUND((C_RATEIOCRED.VRSALDOCREDFIN * C_RATEIOCRED.VRPERCREDITO) / 100,2);

               V_VRSALCREDORACUM    := ROUND(V_VRSALCREDORACUM + V_VRSALDOCREDOR,2);
               V_VRSALCREDUTANTACUM := ROUND(V_VRSALCREDUTANTACUM + V_VRSALDOCREDUTANT,2);
               V_VRSALCREDORUTACUM  := ROUND(V_VRSALCREDORUTACUM + V_VRSALDOCREDORUT,2);
               V_VRSALCREDFINACUM   := ROUND(V_VRSALCREDFINACUM + V_VRSALDOCREDFIN,2);

               V_COUNTRATEIO := V_COUNTRATEIO + 1;

               /*E VERIFICADO PARA O ULTIMO REGISTRO SE EXISTE DIFERENCA PARA TOTAL DO SALDO, CASO HAJA DEVIDO A ARREDONDAMENTO, ESTA E CONSIDERADA NO ULTIMO REGISTRO*/
               IF (V_COUNTRATEIO = V_RATEIOCRED) AND (ROUND(C_RATEIOCRED.VRSALDOCREDOR - V_VRSALCREDORACUM,2)) > 0 THEN
                   V_VRSALDOCREDOR :=  ROUND(V_VRSALDOCREDOR + ROUND(C_RATEIOCRED.VRSALDOCREDOR - V_VRSALCREDORACUM,2),2);
               END IF;

               IF (V_COUNTRATEIO = V_RATEIOCRED) AND (ROUND(C_RATEIOCRED.VRSALDOCREDUTANT - V_VRSALCREDUTANTACUM,2)) > 0 THEN
                   V_VRSALDOCREDUTANT :=  ROUND(V_VRSALDOCREDUTANT + ROUND(C_RATEIOCRED.VRSALDOCREDUTANT - V_VRSALCREDUTANTACUM,2),2);
               END IF;

               IF (V_COUNTRATEIO = V_RATEIOCRED) AND (ROUND(C_RATEIOCRED.VRSALDOCREDORUT - V_VRSALCREDORUTACUM,2)) > 0 THEN
                   V_VRSALDOCREDORUT :=  ROUND(V_VRSALDOCREDORUT + ROUND(C_RATEIOCRED.VRSALDOCREDORUT - V_VRSALCREDORUTACUM,2),2);
               END IF;

               IF (V_COUNTRATEIO = V_RATEIOCRED) AND (ROUND(C_RATEIOCRED.VRSALDOCREDFIN - V_VRSALCREDFINACUM,2)) > 0 THEN
                   V_VRSALDOCREDFIN :=  ROUND(V_VRSALDOCREDFIN + ROUND(C_RATEIOCRED.VRSALDOCREDFIN - V_VRSALCREDFINACUM,2),2);
               END IF;

               V_REG_RATEIOCRED :=  '|1100'                                                                ||'|'||
                                     TO_CHAR(C_RATEIOCRED.DTINIPERIODO,'MMYYYY')                           ||'|'||
                                     '01'                                                                  ||'|'||
                                     ''                                                                    ||'|'||
                                     C_RATEIOCRED.CDTPCREDPICOITE                                          ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(V_VRSALDOCREDOR,'999999999990.00')),'.',',')    ||'|'||
                                     '0'                                                                   ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(V_VRSALDOCREDOR,'999999999990.00')),'.',',')    ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(V_VRSALDOCREDUTANT,'999999999990.00')),'.',',') ||'|'||
                                     '0'                                                                   ||'|'||
                                     '0'                                                                   ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(NVL(V_VRSALDOCREDORUT,0) + NVL(V_VRSALDOCREDFIN,0),'999999999990.00')),'.',',') ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(V_VRSALDOCREDORUT,'999999999990.00')),'.',',')  ||'|'||
                                     '0'                                                                   ||'|'||
                                     '0'                                                                   ||'|'||
                                     '0'                                                                   ||'|'||
                                     '0'                                                                   ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(V_VRSALDOCREDFIN,'999999999990.00')),'.',',') ||'|';

            V_CONTREGA := NVL(V_CONTREGA,0) +1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'8013'||LPAD(V_CONTREGA,10,'0'),V_REG_RATEIOCRED);
         END LOOP;
      END IF;

      V_RATEIOCRED := 0;
      /*VERIFICA SE TEM RATEIO POR TIPO DE CREDITO PARA O MAPA*/
      BEGIN
         SELECT COUNT(*)
           INTO V_RATEIOCRED
           FROM CREDANTSPED C, RATEIOCREDMAPA R
          WHERE C.DTUTILIZACRED = P_DTINICIAL
            AND C.CDEMPRESA     = P_CDEMPRESA
            AND C.IDTPCREDANT   = 'CA'
            AND C.CDREGISTRO    = '1500'
            AND C.NRSEQUMAPAPU  = R.NRSEQUMAPAPU;
      EXCEPTION
           WHEN NO_DATA_FOUND THEN
             V_RATEIOCRED := 0;
      END;
      /*CASO NAO TENHA RATEIO POR TIPO DE CREDIDO, O ARQUIVO E MONTADO COM O TIPO DE CREDITO FIXO*/
      IF V_RATEIOCRED = 0 THEN
         /*REGISTRO 1500 - CREDITO DE PERIODO ANTERIOR COFINS*/
         FOR C_1500 IN (SELECT '|1500'                                                               ||'|'||
                               TO_CHAR(M.DTINIPERIODO,'MMYYYY')                                      ||'|'||
                               '01'                                                                  ||'|'||
                               ''                                                                    ||'|'||
                               '199'                                                                 ||'|'||
                               REPLACE(LTRIM(TO_CHAR(C.VRSALDOCREDOR,'999999999990.00')),'.',',')    ||'|'||
                               '0'                                                                   ||'|'||
                               REPLACE(LTRIM(TO_CHAR(C.VRSALDOCREDOR,'999999999990.00')),'.',',')    ||'|'||
                               REPLACE(LTRIM(TO_CHAR(C.VRSALDOCREDUTANT,'999999999990.00')),'.',',') ||'|'||
                               '0'                                                                   ||'|'||
                               '0'                                                                   ||'|'||
                               REPLACE(LTRIM(TO_CHAR(NVL(C.VRSALDOCREDORUT,0) + NVL(C.VRSALDOCREDFIN,0),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(C.VRSALDOCREDORUT,'999999999990.00')),'.',',')  ||'|'||
                               '0'                                                                   ||'|'||
                               '0'                                                                   ||'|'||
                               '0'                                                                   ||'|'||
                               '0'                                                                   ||'|'||
                               REPLACE(LTRIM(TO_CHAR(C.VRSALDOCREDFIN,'999999999990.00')),'.',',')   ||'|' AS REG_1500
                          FROM CREDANTSPED C, MAPAAPURAC M
                         WHERE C.DTUTILIZACRED = P_DTINICIAL
                           AND C.CDEMPRESA     = P_CDEMPRESA
                           AND C.IDTPCREDANT   = 'CA'
                           AND C.CDREGISTRO    = '1500'
                           AND C.NRSEQUMAPAPU  = M.NRSEQUMAPAPU
                         ORDER BY M.NRSEQUMAPAPU) LOOP 

            V_CONTREGA := NVL(V_CONTREGA,0) +1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'8013'||LPAD(V_CONTREGA,10,'0'),C_1500.REG_1500);
         END LOOP;
      ELSE
         /*E REALIZADO O CALCULO PROPORCIONAL NOS SALDOS DOS CREDITOS ANTERIORES SSEPARANDO POR TIPO DE CREDITO*/
         /*NA TABELA RATEIOCREDMAPA CONTEM O PERCENTUAL CORRESPONDENTE AO TIPO DE CREDITO, SENDO ESTA PREENCHIDA NA PROCEDURE DE APURACAO DO IMPOSTO */
         V_VRSALDOCREDOR     := 0;
         V_VRSALDOCREDUTANT  := 0;
         V_VRSALDOCREDORUT   := 0;
         V_VRSALDOCREDFIN    := 0;

         V_COUNTRATEIO       := 0;

         V_VRSALCREDORACUM    := 0;
         V_VRSALCREDUTANTACUM := 0;
         V_VRSALCREDORUTACUM  := 0;
         V_VRSALCREDFINACUM   := 0;

         FOR C_RATEIOCRED IN (SELECT C.NRSEQUMAPAPU, M.DTINIPERIODO, C.VRSALDOCREDOR, C.VRSALDOCREDUTANT, C.VRSALDOCREDORUT, C.VRSALDOCREDFIN,
                               R.VRPERCREDITO, R.CDTPCREDPICOITE
                              FROM CREDANTSPED C, MAPAAPURAC M, RATEIOCREDMAPA R
                             WHERE C.DTUTILIZACRED = P_DTINICIAL
                               AND C.CDEMPRESA     = P_CDEMPRESA
                               AND C.IDTPCREDANT   = 'CA'
                               AND C.CDREGISTRO    = '1500'
                               AND C.NRSEQUMAPAPU  = M.NRSEQUMAPAPU
                               AND R.NRSEQUMAPAPU  = M.NRSEQUMAPAPU
                               ORDER BY C.NRSEQUMAPAPU, R.CDTPCREDPICOITE) LOOP

               V_VRSALDOCREDOR    := ROUND((C_RATEIOCRED.VRSALDOCREDOR * C_RATEIOCRED.VRPERCREDITO) / 100,2);
               V_VRSALDOCREDUTANT := ROUND((C_RATEIOCRED.VRSALDOCREDUTANT * C_RATEIOCRED.VRPERCREDITO) / 100,2);
               V_VRSALDOCREDORUT  := ROUND((C_RATEIOCRED.VRSALDOCREDORUT * C_RATEIOCRED.VRPERCREDITO) / 100,2);
               V_VRSALDOCREDFIN   := ROUND((C_RATEIOCRED.VRSALDOCREDFIN * C_RATEIOCRED.VRPERCREDITO) / 100,2);

               V_VRSALCREDORACUM    := ROUND(V_VRSALCREDORACUM + V_VRSALDOCREDOR,2);
               V_VRSALCREDUTANTACUM := ROUND(V_VRSALCREDUTANTACUM + V_VRSALDOCREDUTANT,2);
               V_VRSALCREDORUTACUM  := ROUND(V_VRSALCREDORUTACUM + V_VRSALDOCREDORUT,2);
               V_VRSALCREDFINACUM   := ROUND(V_VRSALCREDFINACUM + V_VRSALDOCREDFIN,2);

               V_COUNTRATEIO := V_COUNTRATEIO + 1;

               /*E VERIFICADO PARA O ULTIMO REGISTRO SE EXISTE DIFERENCA PARA TOTAL DO SALDO, CASO HAJA DEVIDO A ARREDONDAMENTO, ESTA E CONSIDERADA NO ULTIMO REGISTRO*/
               IF (V_COUNTRATEIO = V_RATEIOCRED) AND (ROUND(C_RATEIOCRED.VRSALDOCREDOR - V_VRSALCREDORACUM,2)) > 0 THEN
                   V_VRSALDOCREDOR :=  ROUND(V_VRSALDOCREDOR + ROUND(C_RATEIOCRED.VRSALDOCREDOR - V_VRSALCREDORACUM,2),2);
               END IF;

               IF (V_COUNTRATEIO = V_RATEIOCRED) AND (ROUND(C_RATEIOCRED.VRSALDOCREDUTANT - V_VRSALCREDUTANTACUM,2)) > 0 THEN
                   V_VRSALDOCREDUTANT :=  ROUND(V_VRSALDOCREDUTANT + ROUND(C_RATEIOCRED.VRSALDOCREDUTANT - V_VRSALCREDUTANTACUM,2),2);
               END IF;

               IF (V_COUNTRATEIO = V_RATEIOCRED) AND (ROUND(C_RATEIOCRED.VRSALDOCREDORUT - V_VRSALCREDORUTACUM,2)) > 0 THEN
                   V_VRSALDOCREDORUT :=  ROUND(V_VRSALDOCREDORUT + ROUND(C_RATEIOCRED.VRSALDOCREDORUT - V_VRSALCREDORUTACUM,2),2);
               END IF;

               IF (V_COUNTRATEIO = V_RATEIOCRED) AND (ROUND(C_RATEIOCRED.VRSALDOCREDFIN - V_VRSALCREDFINACUM,2)) > 0 THEN
                   V_VRSALDOCREDFIN :=  ROUND(V_VRSALDOCREDFIN + ROUND(C_RATEIOCRED.VRSALDOCREDFIN - V_VRSALCREDFINACUM,2),2);
               END IF;

               V_REG_RATEIOCRED :=  '|1500'                                                                ||'|'||
                                     TO_CHAR(C_RATEIOCRED.DTINIPERIODO,'MMYYYY')                           ||'|'||
                                     '01'                                                                  ||'|'||
                                     ''                                                                    ||'|'||
                                     C_RATEIOCRED.CDTPCREDPICOITE                                          ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(V_VRSALDOCREDOR,'999999999990.00')),'.',',')    ||'|'||
                                     '0'                                                                   ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(V_VRSALDOCREDOR,'999999999990.00')),'.',',')    ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(V_VRSALDOCREDUTANT,'999999999990.00')),'.',',') ||'|'||
                                     '0'                                                                   ||'|'||
                                     '0'                                                                   ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(NVL(V_VRSALDOCREDORUT,0) + NVL(V_VRSALDOCREDFIN,0),'999999999990.00')),'.',',') ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(V_VRSALDOCREDORUT,'999999999990.00')),'.',',')  ||'|'||
                                     '0'                                                                   ||'|'||
                                     '0'                                                                   ||'|'||
                                     '0'                                                                   ||'|'||
                                     '0'                                                                   ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(V_VRSALDOCREDFIN,'999999999990.00')),'.',',') ||'|';

            V_CONTREGA := NVL(V_CONTREGA,0) +1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'8013'||LPAD(V_CONTREGA,10,'0'),V_REG_RATEIOCRED);
         END LOOP;
      END IF;
   END IF;

   /*GERA O CONTROLE DE COMPENSACAO, COM OS VALORES DE CREDITO DE COMPENSACAO DO MES ATUAL E DE MESES ANTERIORES,
     UTILIZADOS NO PROPRIO MES OU AINDA DISPONIVEIS*/
   FOR C_COMP IN (SELECT C.NRSEQUMAPAPU, C.CDREGISTRO AS REG, '99' AS IND_NAT_RET,
                         TO_CHAR(M.DTINIPERIODO,'MMYYYY') AS PR_REC_RET,
                         REPLACE(LTRIM(TO_CHAR(SUM(ROUND(C.VRSALDOCREDOR,  2)),'999999999990.00')),'.',',') AS VL_RET_APU,
                         REPLACE(LTRIM(TO_CHAR(SUM(ROUND(NVL(C.VRSALDOCREDORUT,0) + NVL(C.VRSALDOCREDUTANT,0),2)),'999999999990.00')),'.',',') AS VL_RET_DED,
                         REPLACE(LTRIM(TO_CHAR(SUM(ROUND(C.VRSALDOCREDFIN, 2)),'999999999990.00')),'.',',') AS SLD_RET
                    FROM CREDANTSPED C, MAPAAPURAC M
                   WHERE DTUTILIZACRED  = P_DTINICIAL
                     AND IDTPCREDANT    = 'CO'
                     AND C.NRSEQUMAPAPU = M.NRSEQUMAPAPU
                     AND C.CDEMPRESA    = P_CDEMPRESA
                   GROUP BY C.NRSEQUMAPAPU, C.CDREGISTRO, TO_CHAR(M.DTINIPERIODO,'MMYYYY')) LOOP

      V_REGCOMP := NULL;

      /* REGISTROS 1300 E 1700 */
      V_REGCOMP := '|'|| C_COMP.REG   ||'|'||
                   C_COMP.IND_NAT_RET ||'|'||
                   C_COMP.PR_REC_RET  ||'|'||
                   C_COMP.VL_RET_APU  ||'|'||
                   C_COMP.VL_RET_DED  ||'|'||
                   '0,00'             ||'|'||
                   '0,00'             ||'|'||
                   C_COMP.SLD_RET     ||'|';

      IF C_COMP.REG = '1300' THEN
         V_CONTREGA := NVL(V_CONTREGA,0) +1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG, NRORDREGARQMAG, DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'8012'||C_COMP.NRSEQUMAPAPU,V_REGCOMP);
      ELSIF C_COMP.REG = '1700' THEN
         V_CONTREGA := NVL(V_CONTREGA,0) +1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'8014'||C_COMP.NRSEQUMAPAPU,V_REGCOMP);
      END IF;
   END LOOP;

   IF (((P_GERABCLP IS NULL) OR (P_GERABCLP IN ('0','1'))) AND (P_CRTESCRITUR IN ('1','2'))) THEN
      BEGIN
         SELECT NRSEQUMAPAPU AS NRSEQUMAPPIS
           INTO V_NRSEQUMAPPIS
           FROM MAPAAPURAC MP
          WHERE MP.CDEMPRESA = P_CDEMPRESA
            AND MP.CDIMPOSTO = P_PIS
            AND MP.DTINIPERIODO BETWEEN P_DTINICIAL AND P_DTFINAL;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_NRSEQUMAPPIS := NULL;
      END;

      /* REGISTRO 1900 */
      FOR C_1900 IN (SELECT '|1900'         ||'|'||
                            CNPJ            ||'|'||
                            COD_MOD         ||'|'||
                            SER             ||'|'||
                            SUB_SER         ||'|'||
                            COD_SIT         ||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_TOT_REC),2), '999999999990.00')), '.', ',') ||'|'||
                            SUM(QUANT_DOC)  ||'|'||
                            CST_PIS         ||'|'||
                            CST_COFINS      ||'|'||
                            CFOP            ||'|'||
                            INF_COMPL       ||'|'||
                            COD_CTA         ||'|' AS REGISTRO, COD_CTA,
                            TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CNPJ,'.',''),'/',''),'-',''),'\',''),' ',''),CHR(10),''),CHR(9),'')) AS CNPJ
                       FROM (SELECT CNPJ, COD_MOD, SER, SUB_SER, COD_SIT, SUM(VL_TOT_REC) AS VL_TOT_REC, QUANT_DOC, MAX(CST_PIS) AS CST_PIS,
                                    MAX(CST_COFINS) AS CST_COFINS, CFOP, INF_COMPL, COD_CTA
                               FROM (SELECT E.NRINSJUREMPR AS CNPJ,
                                            DECODE(SUBSTR(CDESTAB,LENGTH(CDESTAB)-2,3),'ISS','98',N.CDMODDOCFISC) AS COD_MOD,
                                            N.CDSERIE AS SER, '' AS SUB_SER,
                                            CASE WHEN N.IDSTATUS = 'C' THEN '02' WHEN N.IDSTATUS IN ('A','E') THEN '00' ELSE '99' END AS COD_SIT,
                                            SUM(DECODE(IM.CDIMPOSTONFE,P_PIS,I.VRCONTITIMP,0)) AS VL_TOT_REC,
                                            COUNT(DISTINCT N.NRLANCTONF) AS QUANT_DOC,
                                            DECODE(IM.CDIMPOSTONFE,P_PIS,I.CDSITUTRIBITNFE,'') AS CST_PIS,
                                            DECODE(IM.CDIMPOSTONFE,P_COFINS,I.CDSITUTRIBITNFE,'') AS CST_COFINS,
                                            I.CDFISCOPERIT AS CFOP,
                                            '' AS INF_COMPL,
                                            CONTCTBL_SPED(N.IDENTRSAIDOP,P.CDPRODUTO,N.CDTIPOOPER,DECODE(N.IDENTRSAIDOP,'E',N.CDFILIRECE,N.CDFILIEMIT)) AS COD_CTA
                                       FROM TMP_REGDADOSSPED_PIS T, NOTAFISCAL N, EMPRESA E, ITEMPROIMPOS I, ITEMPRODUTO P, IMPOSTO IM
                                      WHERE T.CDREGISTRO   = 'NOTA'
                                        AND T.CDCAMPO1     = N.CDFILIAL
                                        AND T.CDCAMPO2     = N.NRLANCTONF
                                        AND N.IDENTRSAIDOP = 'S'
                                        AND E.CDEMPRESA    = P_CDEMPRESA
                                        AND I.CDFILIAL     = N.CDFILIAL
                                        AND I.NRLANCTONF   = N.NRLANCTONF
                                        AND I.CDIMPOSTO    = IM.CDIMPOSTO
                                        AND I.CDFILIAL     = P.CDFILIAL
                                        AND I.NRLANCTONF   = P.NRLANCTONF
                                        AND I.NRSEQITPROD  = P.NRSEQITPROD
                                        AND IM.CDIMPOSTONFE IN (P_PIS,P_COFINS)
                                        AND NOT EXISTS (SELECT CDFILIAL, NRLANCTONF
                                                          FROM IMPBAIXAREC
                                                         WHERE CDFILIAL     = T.CDCAMPO1
                                                           AND NRLANCTONF   = T.CDCAMPO2
                                                           AND NRSEQUMAPAPU = V_NRSEQUMAPPIS)
                                      GROUP BY E.NRINSJUREMPR, DECODE(SUBSTR(CDESTAB,LENGTH(CDESTAB)-2,3),'ISS','98',N.CDMODDOCFISC), N.CDSERIE,
                                               CASE WHEN N.IDSTATUS = 'C' THEN '02' WHEN N.IDSTATUS IN ('A','E') THEN '00' ELSE '99' END,
                                               DECODE(IM.CDIMPOSTONFE,P_PIS,I.CDSITUTRIBITNFE,''), DECODE(IM.CDIMPOSTONFE,P_COFINS,I.CDSITUTRIBITNFE,''),
                                               I.CDFISCOPERIT, CONTCTBL_SPED(N.IDENTRSAIDOP,P.CDPRODUTO,N.CDTIPOOPER,DECODE(N.IDENTRSAIDOP,'E',N.CDFILIRECE,N.CDFILIEMIT)))
                              GROUP BY CNPJ, COD_MOD, SER, SUB_SER, COD_SIT, QUANT_DOC, CFOP, INF_COMPL, COD_CTA
                              UNION ALL
                             SELECT E.NRINSJUREMPR AS CNPJ,
                                    DECODE(SUBSTR(CDESTAB,LENGTH(CDESTAB)-2,3),'ISS','98',N.CDMODDOCFISC) AS COD_MOD,
                                    N.CDSERIE AS SER, '' AS SUB_SER,
                                    CASE WHEN N.IDSTATUS = 'C' THEN '02' WHEN N.IDSTATUS IN ('A','E') THEN '00' ELSE '99' END AS COD_SIT,
                                    SUM(VALOR_IMPOSTO_ITEM_NFE_CAIXA(N.CDFILIAL,N.NRLANCTONF,P_PIS,'C',I.DTBAIXAREC,'',I.CDFISCOPERNF,I.CDSITUTRIBITNFE,I.NRSEQUENCIAL)) AS VL_TOT_REC,
                                    COUNT(DISTINCT N.NRLANCTONF) AS QUANT_DOC,
                                    I.CDSITUTRIBITNFE AS CST_PIS,
                                    I.CDSITUTRIBITNFE AS CST_COFINS,
                                    I.CDFISCOPERNF AS CFOP,
                                    '' AS INF_COMPL,
                                    CONTCTBL_SPED(N.IDENTRSAIDOP,'',N.CDTIPOOPER,N.CDFILIEMIT) AS COD_CTA
                               FROM TMP_REGDADOSSPED_PIS T, NOTAFISCAL N, EMPRESA E, IMPBAIXAREC I, IMPOSTO IM
                              WHERE T.CDREGISTRO    = 'NOTA'
                                AND T.CDCAMPO1      = N.CDFILIAL
                                AND T.CDCAMPO2      = N.NRLANCTONF
                                AND N.IDENTRSAIDOP  = 'S'
                                AND E.CDEMPRESA     = P_CDEMPRESA
                                AND I.CDFILIAL      = N.CDFILIAL
                                AND I.NRLANCTONF    = N.NRLANCTONF
                                AND I.CDIMPOSTO     = IM.CDIMPOSTO
                                AND IM.CDIMPOSTONFE = P_PIS
                                AND I.NRSEQUMAPAPU  = V_NRSEQUMAPPIS
                              GROUP BY E.NRINSJUREMPR, DECODE(SUBSTR(CDESTAB,LENGTH(CDESTAB)-2,3),'ISS','98',N.CDMODDOCFISC),
                                    N.CDSERIE, CASE WHEN N.IDSTATUS = 'C' THEN '02' WHEN N.IDSTATUS IN ('A','E') THEN '00' ELSE '99' END,
                                    I.CDSITUTRIBITNFE, I.CDSITUTRIBITNFE, I.CDFISCOPERNF, CONTCTBL_SPED(N.IDENTRSAIDOP,'',N.CDTIPOOPER,N.CDFILIEMIT)
                              UNION ALL
                             SELECT E.NRINSJUREMPR AS CNPJ,
                                    '99' AS COD_MOD,
                                    '' AS SER,
                                    '' AS SUB_SER,
                                    '99' AS COD_SIT,
                                    SUM(ABS(LC.VRLANCAMENT)) AS VL_TOT_REC,
                                    0 AS QUANT_DOC,
                                    GP.CDSITUTRIBPICO AS CST_PIS,
                                    GP.CDSITUTRIBPICO AS CST_COFINS,
                                    '' AS CFOP,
                                    '' AS INF_COMPL,
                                    CGP.CDCONTCTBL AS COD_CTA
                               FROM OUTRECCONT GP, OUTRECCONTCTBL CGP, LANCCONT LC, EMPRESA E
                              WHERE GP.CDEMPRESA    = P_CDEMPRESA
                                AND GP.CDEMPRESA    = CGP.CDEMPRESA
                                AND GP.CDOUTRECCONT =  CGP.CDOUTRECCONT
                                AND LC.CDCONTCTBL LIKE CGP.CDCONTCTBL||'%'
                                AND LC.IDLANCINTE   NOT IN ('F','S')
                                AND LC.DTLANCCONT BETWEEN P_DTINICIAL AND P_DTFINAL
                                AND LC.CDEMPRESA    = P_CDEMPRESA
                                AND LC.CDEMPRESA    = E.CDEMPRESA
                                AND LC.VRLANCAMENT  > 0
                              GROUP BY GP.CDSITUTRIBPICO,  CGP.CDCONTCTBL, E.NRINSJUREMPR
                              UNION ALL
                             SELECT NRINSJURIEST AS CNPJ,
                                    '99' AS COD_MOD,
                                    '' AS SER,
                                    '' AS SUB_SER,
                                    '99' AS COD_SIT,
                                    SUM(VRRECEITA) AS VL_TOT_REC,
                                    0 AS QUANT_DOC,
                                    CSTPIS,
                                    CSTCOFINS,
                                    CFOP,
                                    INFO_COMPL,
                                    COD_CTA
                               FROM(SELECT IT.VRPRODUTO  AS VRRECEITA,
                                           CDSITUTRIBNFE AS CSTPIS,
                                           CDSITUTRIBNFE AS CSTCOFINS,
                                           '' AS CFOP,
                                           CONTCTBL_SPED('S',IT.CDPRODUTO,P.CDTPOPERCNTREC,RZ.CDFILIAL) AS COD_CTA,
                                           '' AS INFO_COMPL,
                                           IE.NRINSJURIEST
                                      FROM ITEMVENOUTIMPOS IT, REDUCAOZ RZ, FILIAL F, IMPOSTO I, INSCRESTAD IE, PARAVEND P
                                     WHERE IT.CDFILIAL    = RZ.CDFILIAL
                                       AND IT.CDCAIXA     = RZ.CDCAIXA
                                       AND IT.DTREDUCAOZ  = RZ.DTREDUCAOZ
                                       AND RZ.CDFILIAL    = P.CDFILIAL
                                       AND IT.CDIMPOSTO   = I.CDIMPOSTO
                                       AND F.CDFILIAL     = RZ.CDFILIAL
                                       AND F.CDFILIAL     = IT.CDFILIAL
                                       AND F.CDEMPRESA    = P_CDEMPRESA
                                       AND F.CDEMPRESA    = IE.CDEMPRESA
                                       AND F.CDINSCESTA   = IE.CDINSCESTA
                                       AND I.CDIMPOSTONFE = P_PIS
                                       AND IT.DTREDUCAOZ BETWEEN P_DTINICIAL AND P_DTFINAL)
                              GROUP BY NRINSJURIEST, CSTPIS, CSTCOFINS, CFOP, INFO_COMPL, COD_CTA
                              UNION ALL
                              SELECT E.NRINSJUREMPR AS CNPJ,
                                    '59' AS COD_MOD,
                                    V.CDSERIENFCE AS SER, '' AS SUB_SER,
                                    CASE WHEN V.IDSTATUSNFCE = 'C' THEN '02' WHEN V.IDSTATUSNFCE IN ('A','E') THEN '00' ELSE '99' END AS COD_SIT,
                                    SUM(TRUNC((IT.QTPRODVEND*(NVL(IT.VRUNITVEND,0)+NVL(IT.VRUNITVENDCL,0)))-NVL(IT.VRDESITVEND,0)+NVL(IT.VRACRITVEND,0),2)) AS VL_TOT_REC,
                                    COUNT(DISTINCT V.NRSEQVENDA) AS QUANT_DOC,
                                    I.CDCSTPRODPC AS CST_PIS,
                                    I.CDCSTPRODPC AS CST_COFINS,
                                    I.CDCFOPPROD AS CFOP,
                                    '' AS INF_COMPL,
                                    CONTCTBL_SPED('S',IT.CDPRODUTO,P.CDTPOPERCNTREC,IT.CDFILIAL) AS COD_CTA
                               FROM TMP_REGDADOSSPED_PIS T, VENDA V, EMPRESA E, ITVENDAIMPOS I, ITEMVENDA IT, PARAVEND P
                              WHERE T.CDREGISTRO   = 'SAT'
                                AND T.CDCAMPO1     = V.CDFILIAL
                                AND T.CDCAMPO2     = V.NRSEQVENDA
                                AND T.CDCAMPO3     = V.CDCAIXA
                                AND V.CDFILIAL     = P.CDFILIAL
                                AND E.CDEMPRESA    = P_CDEMPRESA
                                AND V.CDFILIAL     = I.CDFILIAL
                                AND V.NRSEQVENDA   = I.NRSEQVENDA
                                AND V.CDCAIXA      = I.CDCAIXA
                                AND I.CDFILIAL     = IT.CDFILIAL
                                AND I.NRSEQVENDA   = IT.NRSEQVENDA
                                AND I.CDCAIXA      = IT.CDCAIXA
                                AND I.NRSEQUITVEND = IT.NRSEQUITVEND
                              GROUP BY E.NRINSJUREMPR, V.CDSERIENFCE, I.CDCSTPRODPC, I.CDCSTPRODPC, I.CDCFOPPROD,
                                    CASE WHEN V.IDSTATUSNFCE = 'C' THEN '02' WHEN V.IDSTATUSNFCE IN ('A','E') THEN '00' ELSE '99' END,
                                    CONTCTBL_SPED('S',IT.CDPRODUTO,P.CDTPOPERCNTREC,IT.CDFILIAL)
                              UNION ALL
                             SELECT E.NRINSJUREMPR AS CNPJ,
                                    '65' AS COD_MOD,
                                    V.CDSERIENFCE AS SER, '' AS SUB_SER,
                                    CASE WHEN V.IDSTATUSNFCE = 'C' THEN '02' WHEN V.IDSTATUSNFCE IN ('A','E') THEN '00' ELSE '99' END AS COD_SIT,
                                    SUM(TRUNC((IT.QTPRODVEND*(NVL(IT.VRUNITVEND,0)+NVL(IT.VRUNITVENDCL,0)))-NVL(IT.VRDESITVEND,0)+NVL(IT.VRACRITVEND,0),2)) AS VL_TOT_REC,
                                    COUNT(DISTINCT V.NRSEQVENDA) AS QUANT_DOC,
                                    I.CDCSTPRODPC AS CST_PIS,
                                    I.CDCSTPRODPC AS CST_COFINS,
                                    I.CDCFOPPROD AS CFOP,
                                    '' AS INF_COMPL,
                                    CONTCTBL_SPED('S',IT.CDPRODUTO,P.CDTPOPERCNTREC,IT.CDFILIAL) AS COD_CTA
                               FROM TMP_REGDADOSSPED_PIS T, VENDA V, EMPRESA E, ITVENDAIMPOS I, ITEMVENDA IT, PARAVEND P
                              WHERE T.CDREGISTRO   = 'NFCE'
                                AND T.CDCAMPO1     = V.CDFILIAL
                                AND T.CDCAMPO2     = V.NRSEQVENDA
                                AND T.CDCAMPO3     = V.CDCAIXA
                                AND V.CDFILIAL     = P.CDFILIAL
                                AND E.CDEMPRESA    = P_CDEMPRESA
                                AND V.CDFILIAL     = I.CDFILIAL
                                AND V.NRSEQVENDA   = I.NRSEQVENDA
                                AND V.CDCAIXA      = I.CDCAIXA
                                AND I.CDFILIAL     = IT.CDFILIAL
                                AND I.NRSEQVENDA   = IT.NRSEQVENDA
                                AND I.CDCAIXA      = IT.CDCAIXA
                                AND I.NRSEQUITVEND = IT.NRSEQUITVEND
                              GROUP BY E.NRINSJUREMPR, V.CDSERIENFCE, I.CDCSTPRODPC, I.CDCSTPRODPC, I.CDCFOPPROD,
                                    CASE WHEN V.IDSTATUSNFCE = 'C' THEN '02' WHEN V.IDSTATUSNFCE IN ('A','E') THEN '00' ELSE '99' END,
                                    CONTCTBL_SPED('S',IT.CDPRODUTO,P.CDTPOPERCNTREC,IT.CDFILIAL))
                      GROUP BY CNPJ, COD_MOD, SER, SUB_SER, COD_SIT, CST_PIS, CST_COFINS, CFOP, INF_COMPL, COD_CTA, TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CNPJ,'.',''),'/',''),'-',''),'\',''),' ',''),CHR(10),''),CHR(9),''))) LOOP

         V_CONTREGA:= NVL(V_CONTREGA,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'802'||V_CONTREGA,C_1900.REGISTRO);

         V_OLDESTAB := NULL;
         /*VERIFICA SE O ESTABELECIMENTO JA FOI INSERIDO*/
         SELECT MAX(CDESTAB)
           INTO V_OLDESTAB
           FROM TMP_REGDADOSSPED_PIS
          WHERE CDREGISTRO = '0140'
            AND CDCHAVEREG = C_1900.CNPJ;

         V_REG_0140 := '';
         IF V_OLDESTAB IS NULL THEN
            V_CONTEST := NVL(V_CONTEST,0)+1;
            BEGIN
               SELECT REG
                 INTO V_REG_0140
                 FROM (SELECT '|0140'         ||'|'||
                              LTRIM(TO_CHAR(V_CONTEST,'000')) ||'|'||
                              E.NMRAZSOCEMPR  ||'|'||
                              LPAD(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(E.NRINSJUREMPR,'.',''),'/',''),'-',''),'\',''),' ',''),CHR(10),''),CHR(9),'')),14,'0') ||'|'||
                              E.SGESTADO      ||'|'||
                              ''              ||'|'||
                              M.CDMUNICIBGE   ||'|'||
                              ''              ||'|'||
                              ''              ||'|' AS REG,
                              LPAD(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(E.NRINSJUREMPR,'.',''),'/',''),'-',''),'\',''),' ',''),CHR(10),''),CHR(9),'')),14,'0') AS INSJUREST
                         FROM MUNICIPIO M, EMPRESA E
                        WHERE E.CDEMPRESA   = P_CDEMPRESA
                          AND E.CDPAIS      = M.CDPAIS
                          AND E.SGESTADO    = M.SGESTADO
                          AND E.CDMUNICIPIO = M.CDMUNICIPIO);
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_REG_0140:= NULL;
            END;

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

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

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

         IF (V_COD_CC IS NULL) AND (C_1900.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, C_1900.COD_CTA, NULL);
         END IF;
      END LOOP;
   END IF;

   P_COUNTBLC0 := V_CONTBLC0;
   P_CONTESTAB := V_CONTEST;
   RETURN(V_CONTREGA);
END;
/