CREATE OR REPLACE FUNCTION SPED_PIS_COFINS_BLOCOF(
   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_CDINCTRIB    IN VARCHAR2,
   P_CRTESCRITUR  IN VARCHAR2,
   P_PIS          IN VARCHAR2,
   P_PIS_RET_E    IN VARCHAR2,
   P_PIS_RET_S    IN VARCHAR2,
   P_COFINS       IN VARCHAR2,
   P_COFINS_RET_E IN VARCHAR2,
   P_COFINS_RET_S IN VARCHAR2,
   P_ULTESTAB     IN NUMBER,
   P_CDLIVROMAG   IN VARCHAR,
   P_SEQUPROD     OUT NUMBER) RETURN NUMBER
AS
   V_CONTADOR     NUMBER;
   V_SEQUPROD     NUMBER;
   V_SEQCFOP      NUMBER;
   V_SEQCCTBL     NUMBER;
   V_CONTBLC0     NUMBER;
   V_TMPBLCM      NUMBER;
   V_CONTESTAB    NUMBER;
   V_COUNTEST     NUMBER;
   V_COD_PAR      VARCHAR2(30);
   V_CNPJFILI     VARCHAR2(30);
   V_CDFILIAL     VARCHAR2(4);
   V_CDESTAB      VARCHAR2(3);
   V_IDF010       VARCHAR2(100);
   V_REG          VARCHAR2(500);
   V_REG_F010     VARCHAR2(100);
   V_COD_NAT      VARCHAR2(4);
   V_COD_CC       VARCHAR2(20);
   V_PISRET       NUMBER;
   V_COFINSRET    NUMBER;
   V_VL_REC       NUMBER;
   V_NRSEQUMAPPIS VARCHAR2(10);
   V_NRSEQUMAPCON VARCHAR2(10);
BEGIN
   V_CONTESTAB:= P_ULTESTAB;
   /*SELECT PARA BUSCAR SEQUENCIAL DO BLOCO M*/
   SELECT MAX(CDCONTREGI)
     INTO V_TMPBLCM
     FROM TMP_REGBLOCOM_PIS;

   ----------------------------------------------/*BLOCO F100 - OPERACOES SEM DOCUMENTOS FISCAIS */------------------------------------------------------
   /*NOTAS QUE NAO ENTRARAM NOS REGISTRO A, C, D MAS TEM INCIDENCIA DE PIS E COFINS*/
   IF ((P_CRTESCRITUR IS NULL) OR (P_CRTESCRITUR = '9')) THEN
      FOR C_F100A IN(SELECT '|F100'                                                              ||'|'||
                            PIS.IND_OPER                                                         ||'|'||
                            DECODE(PIS.IND_OPER,'0',PIS.CD_PARTC,'')                             ||'|'||
                            PIS.COD_ITEM                                                         ||'|'||
                            TO_CHAR(PIS.DT_OPER,'DDMMYYYY')                                      ||'|'||
                            REPLACE(LTRIM(TO_CHAR(SUM(PIS.VR_OPER),'9999999999990.00')),'.',',') ||'|'||
                            PIS.CST_PIS                                                          ||'|'||
                            REPLACE(LTRIM(TO_CHAR(SUM(VR_BC_PIS),'9999999999990.0000')),'.',',') ||'|'||
                            REPLACE(LTRIM(TO_CHAR(VR_ALIQ_PIS,'990.0000')),'.',',')              ||'|'||
                            REPLACE(LTRIM(TO_CHAR(SUM(VR_IMP_PIS),'9999999999990.00')),'.',',')  ||'|'||
                            CST_COFINS                                                           ||'|'||
                            REPLACE(LTRIM(TO_CHAR(SUM(VR_BC_COFINS),'9999999999990.0000')),'.',',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(VR_ALIQ_COFINS,'990.0000')),'.',',')           ||'|'||
                            REPLACE(LTRIM(TO_CHAR(SUM(VR_IMP_COFINS),'9999999999990.00')),'.',',')||'|'||
                            PIS.CDNATUBCALPCIT                                                   ||'|'||
                            '0'                                                                  ||'|'|| --INDICADOR DA ORIGEM DO CREDITO
                            CONTCTBL_SPED(PIS.IDENTRSAIDOP,PIS.CDPRODUTO,PIS.CDTIPOOPER,DECODE(PIS.IDENTRSAIDOP,'E',PIS.CDFILIRECE,PIS.CDFILIEMIT)) ||'|'||
                            ''                                                                   ||'|'||
                            'NOTA:'||PIS.NRNOTAFISC||'/OPERACAO:'||REPLACE(PIS.NMTIPOOPER, '|','-') ||'|' as REGISTRO,
                            PIS.CDFILIEMIT, PIS.CDFILIRECE, PIS.IDENTRSAIDOP, SUM(PIS.VR_BC_PIS) AS VR_BC_PIS, PIS.VR_ALIQ_PIS,
                            SUM(PIS.VR_IMP_PIS) AS VR_IMP_PIS, TPCRED_PIS, PIS.CST_PIS, SUM(PIS.VR_OPER) AS VR_OPER,
                            TPCRED_COFINS, CST_COFINS,  SUM(VR_BC_COFINS) AS VR_BC_COFINS, VR_ALIQ_COFINS, PIS.NRNOTAFISC,
                            SUM(VR_IMP_COFINS) AS VR_IMP_COFINS, PIS.CDNATUBCALPCIT, PIS.CDNATURECPIS, COFINS.CDNATURECCOFINS,
                            PIS.CD_PARTC, PIS.CDINSCESTA, PIS.IND_OPER, TRIM(REPLACE(REPLACE(PIS.NRINSJURIEST,CHR(10)),CHR(9))) AS NRINSJURIEST,
                            CONTCTBL_SPED(PIS.IDENTRSAIDOP,PIS.CDPRODUTO,PIS.CDTIPOOPER,DECODE(PIS.IDENTRSAIDOP,'E',PIS.CDFILIRECE,PIS.CDFILIEMIT)) AS COD_CTA
                       FROM (SELECT CASE WHEN I.CDSITUTRIBITNFE BETWEEN '50' AND '66' THEN '0'
                                         WHEN I.CDSITUTRIBITNFE IN ('01','02','03','05') THEN '1'
                                         WHEN I.CDSITUTRIBITNFE IN ('04','06','07','08','09','49','99') THEN '2' END AS IND_OPER,
                                    N.IDORIGDESTNF||DECODE(N.IDORIGDESTNF,'L',DECODE(N.IDENTRSAIDOP,'E',N.CDFILIEMIT,N.CDFILIRECE),NVL(N.CDCLIENTE,N.CDFORNECED)) AS CD_PARTC,
                                    '' AS COD_ITEM,
                                    DECODE(N.IDENTRSAIDOP,'E',N.DTENTRSAID,N.DTEMISSAO) AS  DT_OPER,
                                    ROUND(I.VRCONTITIMP,2)   AS VR_OPER, I.CDTPCREDPICOITE AS TPCRED_PIS,
                                    I.CDSITUTRIBITNFE AS CST_PIS,
                                    ROUND(DECODE(I.CDFISCALIT,'1',I.VRBASECALCIT,0),4) AS VR_BC_PIS,
                                    ROUND(DECODE(I.CDFISCALIT,'1',I.VRALIQITEM,0),4) AS VR_ALIQ_PIS,
                                    ROUND(DECODE(I.CDFISCALIT,'1',I.VRIMPOSTOIT,0),2) AS VR_IMP_PIS,
                                    I.NRLANCTONF, I.CDFILIAL, I.NRSEQITPROD, I.CDNATUBCALPCIT, N.CDFILIEMIT, N.CDFILIRECE,
                                    N.IDENTRSAIDOP, NVL(I.CDNATUREC,R.CDNATUREC) AS CDNATURECPIS, N.NRNOTAFISC, O.NMTIPOOPER, N.CDINSCESTA,
                                    REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.'),'/'),'-'),'\') AS NRINSJURIEST,
                                    N.CDTIPOOPER, IP.CDPRODUTO
                               FROM TMP_REGDADOSSPED_PIS TMP, ITEMPROIMPOS I, NOTAFISCAL N, ITEMPRODUTO IP,
                                    RELACPRODITGEN R, TIPOOPERAC O, INSCRESTAD IE
                              WHERE I.CDFILIAL      = N.CDFILIAL
                                AND I.NRLANCTONF    = N.NRLANCTONF
                                AND I.CDIMPOSTO     = P_PIS
                                AND I.NRITPROIMPOS  = '1'
                                AND I.CDSITUTRIBITNFE IN ('50','51','52','53','54','55','56','60','61','62','63','64','65','66',
                                                          '01','02','03','05','04','06','07','08','09','49','99')
                                AND I.NRSEQITPROD   = IP.NRSEQITPROD
                                AND I.CDFILIAL      = IP.CDFILIAL
                                AND I.NRLANCTONF    = IP.NRLANCTONF
                                AND R.CDPRODUTO(+)  = IP.CDPRODUTO
                                AND R.CDLIVROMAG(+) = P_CDLIVROMAG
                                AND O.CDTIPOOPER    = N.CDTIPOOPER
                                AND O.IDENTRSAIDOP  = N.IDENTRSAIDOP
                                AND N.CDEMPRESA     = IE.CDEMPRESA
                                AND N.CDINSCESTA    = IE.CDINSCESTA
                                AND TMP.CDCHAVEREG  = N.CDFILIAL||N.NRLANCTONF
                                AND TMP.CDCAMPO1    = N.CDFILIAL
                                AND TMP.CDCAMPO2    = N.NRLANCTONF
                                AND TMP.CDREGISTRO  = 'F100')PIS,
                            (SELECT CASE WHEN I.CDSITUTRIBITNFE BETWEEN '50' AND '66' THEN '0'
                                         WHEN I.CDSITUTRIBITNFE IN ('01','02','03','05') THEN '1'
                                         WHEN I.CDSITUTRIBITNFE IN ('04','06','07','08','09','49','99') THEN '2' END AS IND_OPER,
                                    N.IDORIGDESTNF||DECODE(N.IDORIGDESTNF,'L',DECODE(N.IDENTRSAIDOP,'E',N.CDFILIEMIT,N.CDFILIRECE),NVL(N.CDCLIENTE,N.CDFORNECED)) AS CD_PARTC,
                                    '' AS COD_ITEM,
                                    DECODE(N.IDENTRSAIDOP,'E',N.DTENTRSAID,N.DTEMISSAO) AS  DT_OPER,
                                    ROUND(I.VRCONTITIMP,2)   AS VR_OPER, I.CDTPCREDPICOITE AS TPCRED_COFINS,
                                    I.CDSITUTRIBITNFE AS CST_COFINS,
                                    ROUND(DECODE(I.CDFISCALIT,'1',I.VRBASECALCIT,0),4) AS VR_BC_COFINS,
                                    ROUND(DECODE(I.CDFISCALIT,'1',I.VRALIQITEM,0),4) AS VR_ALIQ_COFINS,
                                    ROUND(DECODE(I.CDFISCALIT,'1',I.VRIMPOSTOIT,0),2) AS VR_IMP_COFINS,
                                    I.NRLANCTONF, I.CDFILIAL, I.NRSEQITPROD, I.CDNATUBCALPCIT, N.CDFILIEMIT, N.CDFILIRECE,
                                    N.IDENTRSAIDOP, NVL(I.CDNATUREC,R.CDNATUREC) AS CDNATURECCOFINS, N.NRNOTAFISC, O.NMTIPOOPER, N.CDINSCESTA,
                                    REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.'),'/'),'-'),'\') AS NRINSJURIEST,
                                    N.CDTIPOOPER, IP.CDPRODUTO
                               FROM TMP_REGDADOSSPED_PIS TMP, ITEMPROIMPOS I, NOTAFISCAL N, ITEMPRODUTO IP,
                                    RELACPRODITGEN R, TIPOOPERAC O, INSCRESTAD IE
                              WHERE I.CDFILIAL      = N.CDFILIAL
                                AND I.NRLANCTONF    = N.NRLANCTONF
                                AND I.CDIMPOSTO     = P_COFINS
                                AND I.NRITPROIMPOS  = '1'
                                AND I.CDSITUTRIBITNFE IN ('50','51','52','53','54','55','56','60','61','62','63','64','65','66',
                                                          '01','02','03','05','04','06','07','08','09','49','99')
                                AND I.NRSEQITPROD   = IP.NRSEQITPROD
                                AND I.CDFILIAL      = IP.CDFILIAL
                                AND I.NRLANCTONF    = IP.NRLANCTONF
                                AND R.CDPRODUTO(+)  = IP.CDPRODUTO
                                AND R.CDLIVROMAG(+) = P_CDLIVROMAG
                                AND O.CDTIPOOPER    = N.CDTIPOOPER
                                AND O.IDENTRSAIDOP  = N.IDENTRSAIDOP
                                AND N.CDEMPRESA     = IE.CDEMPRESA
                                AND N.CDINSCESTA    = IE.CDINSCESTA
                                AND TMP.CDCHAVEREG  = N.CDFILIAL||N.NRLANCTONF
                                AND TMP.CDCAMPO1    = N.CDFILIAL
                                AND TMP.CDCAMPO2    = N.NRLANCTONF
                                AND TMP.CDREGISTRO  = 'F100')COFINS
                      WHERE PIS.NRLANCTONF  = COFINS.NRLANCTONF
                        AND PIS.CDFILIAL    = COFINS.CDFILIAL
                        AND PIS.NRSEQITPROD = COFINS.NRSEQITPROD
                      GROUP BY PIS.IND_OPER, PIS.CD_PARTC, PIS.COD_ITEM, TO_CHAR(PIS.DT_OPER,'DDMMYYYY'), TPCRED_PIS, PIS.CST_PIS,
                               VR_ALIQ_PIS, PIS.CDNATUBCALPCIT, VR_ALIQ_COFINS,  TPCRED_COFINS, CST_COFINS, PIS.CDFILIEMIT, PIS.CDFILIRECE,
                               PIS.IDENTRSAIDOP,PIS.CDNATURECPIS, COFINS.CDNATURECCOFINS, PIS.NRNOTAFISC, PIS.NMTIPOOPER, PIS.CDINSCESTA, PIS.NRINSJURIEST,
                               CONTCTBL_SPED(PIS.IDENTRSAIDOP,PIS.CDPRODUTO,PIS.CDTIPOOPER,DECODE(PIS.IDENTRSAIDOP,'E',PIS.CDFILIRECE,PIS.CDFILIEMIT))) LOOP

         /*BUSCA O CNPJ DO ESTABELECIMENTO*/
         V_CNPJFILI := C_F100A.NRINSJURIEST;

         /*VERIFICA SE O REGISTRO DE IDENTIFICACAO DO ESTABELECIMENTO JA FOI INSERIDO */
         V_CDESTAB:= NULL;
         BEGIN
            SELECT MAX(CDESTAB)
              INTO V_CDESTAB
              FROM TMP_REGDADOSSPED_PIS
             WHERE CDREGISTRO = 'F010'
               AND CDCHAVEREG = V_CNPJFILI;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_CDESTAB := NULL;
         END;

         /*IDENTIFICACAO DO ESTABELECIMENTO*/
         IF V_CDESTAB IS NULL THEN
            V_REG_F010 := '|F010|'||V_CNPJFILI||'|';

            /*VERIFICA SE O ESTABELECIMENTO JA FOI INSERIDO*/
            V_CDESTAB := NULL;
            BEGIN
               SELECT MAX(CDESTAB)
                 INTO V_CDESTAB
                 FROM TMP_REGDADOSSPED_PIS
                WHERE CDREGISTRO = '0140'
                  AND CDCHAVEREG = V_CNPJFILI;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_CDESTAB := NULL;
            END;

            /*INSERE O ESTABELECIMENTO*/
            IF V_CDESTAB IS NULL THEN
               V_CONTESTAB:= NVL(V_CONTESTAB,0)+1;
               V_COUNTEST := V_CONTESTAB;
               BEGIN
                  SELECT '|0140'         ||'|'||
                         LTRIM(TO_CHAR(V_COUNTEST,'000')) ||'|'||
                         E.NMRAZSOCEMPR ||'|'||
                         V_CNPJFILI     ||'|'||
                         I.SGESTADO     ||'|'||
                         ''             ||'|'||
                         M.CDMUNICIBGE  ||'|'||
                         ''             ||'|'||
                         ''             ||'|' AS REG
                    INTO V_REG
                    FROM MUNICIPIO M, EMPRESA E, INSCRESTAD I
                   WHERE I.CDEMPRESA    = P_CDEMPRESA
                     AND I.CDINSCESTA   = C_F100A.CDINSCESTA
                     AND I.CDPAIS       = M.CDPAIS
                     AND I.SGESTADO     = M.SGESTADO
                     AND I.CDMUNICIPIO  = M.CDMUNICIPIO
                     AND E.CDEMPRESA    = I.CDEMPRESA;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_REG:= NULL;
               END;

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

               V_SEQUPROD:= NVL(V_SEQUPROD,0)+1;
               INSERT INTO TMP_REGDADOSSPED_PIS
                  (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
                VALUES
                  ('0140', V_SEQUPROD, V_CNPJFILI ,NULL,LTRIM(TO_CHAR(V_COUNTEST,'000')));
            ELSE
               V_COUNTEST := TO_NUMBER(V_CDESTAB);
            END IF;

            V_CONTADOR := NVL(V_CONTADOR,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'501'||LTRIM(TO_CHAR(V_COUNTEST,'000')),V_REG_F010);

            V_SEQUPROD:= NVL(V_SEQUPROD,0)+1;
            INSERT INTO TMP_REGDADOSSPED_PIS
              (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
            VALUES
              ('F010', V_SEQUPROD, V_CNPJFILI, NULL, LTRIM(TO_CHAR(V_COUNTEST,'000')));
         ELSE
            V_COUNTEST := TO_NUMBER(V_CDESTAB);
         END IF;

         V_CONTADOR:= NVL(V_CONTADOR,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'501'||LTRIM(TO_CHAR(V_COUNTEST,'000'))||'03'||TO_CHAR(V_CONTADOR),C_F100A.REGISTRO);

         IF C_F100A.IDENTRSAIDOP = 'S' THEN
            V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
            INSERT INTO TMP_REGBLOCOM_PIS
               (CHAVENOTA,CDREGISTRO,CDCONTREGI,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CSTPISCOFINS,CDFILIAL,CDNATUBCALPCIT,VRIMPRETIDO,VRTOTNF,CDNATUREC,CDESTAB,NRINSJUREST,CDCONTCTBL)
            VALUES
               ('F100A'||C_F100A.NRNOTAFISC,'M200',V_TMPBLCM,C_F100A.TPCRED_PIS,C_F100A.VR_BC_PIS,C_F100A.VR_ALIQ_PIS,'P',C_F100A.VR_IMP_PIS,C_F100A.CST_PIS,C_F100A.CDFILIEMIT,C_F100A.CDNATUBCALPCIT,0,C_F100A.VR_OPER,C_F100A.CDNATURECPIS,LTRIM(TO_CHAR(V_COUNTEST,'000')),V_CNPJFILI,C_F100A.COD_CTA);

            V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
            INSERT INTO TMP_REGBLOCOM_PIS
               (CHAVENOTA,CDREGISTRO,CDCONTREGI,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CSTPISCOFINS,CDFILIAL,CDNATUBCALPCIT,VRIMPRETIDO,VRTOTNF,CDNATUREC,CDESTAB,NRINSJUREST,CDCONTCTBL)
            VALUES
               ('F100A'||C_F100A.NRNOTAFISC,'M600',V_TMPBLCM,C_F100A.TPCRED_COFINS,C_F100A.VR_BC_COFINS,C_F100A.VR_ALIQ_COFINS,'P',C_F100A.VR_IMP_COFINS,C_F100A.CST_COFINS,C_F100A.CDFILIEMIT,C_F100A.CDNATUBCALPCIT,0,C_F100A.VR_OPER,C_F100A.CDNATURECCOFINS,LTRIM(TO_CHAR(V_COUNTEST,'000')),V_CNPJFILI,C_F100A.COD_CTA);
         ELSE
            IF (C_F100A.CST_PIS IN ('50','51','52','53','54','55','56','60','61','62','63','64','65','66')) AND (C_F100A.VR_BC_PIS>0) THEN
               V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
               INSERT INTO TMP_REGBLOCOM_PIS
                  (CHAVENOTA,CDREGISTRO,CDCONTREGI,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CSTPISCOFINS,CDFILIAL,CDNATUBCALPCIT,VRIMPRETIDO,VRTOTNF,CDNATUREC,CDESTAB,NRINSJUREST,CDCONTCTBL)
               VALUES
                  ('F100A'||C_F100A.NRNOTAFISC,'M100',V_TMPBLCM,C_F100A.TPCRED_PIS,C_F100A.VR_BC_PIS,C_F100A.VR_ALIQ_PIS,'P',C_F100A.VR_IMP_PIS,C_F100A.CST_PIS,C_F100A.CDFILIRECE,C_F100A.CDNATUBCALPCIT,0,C_F100A.VR_OPER,C_F100A.CDNATURECPIS,LTRIM(TO_CHAR(V_COUNTEST,'000')),V_CNPJFILI,C_F100A.COD_CTA);
            END IF;

            IF (C_F100A.CST_COFINS IN ('50','51','52','53','54','55','56','60','61','62','63','64','65','66')) AND (C_F100A.VR_BC_COFINS>0) THEN
               V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
               INSERT INTO TMP_REGBLOCOM_PIS
                  (CHAVENOTA,CDREGISTRO,CDCONTREGI,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CSTPISCOFINS,CDFILIAL,CDNATUBCALPCIT,VRIMPRETIDO,VRTOTNF,CDNATUREC,CDESTAB,NRINSJUREST,CDCONTCTBL)
               VALUES
                  ('F100A'||C_F100A.NRNOTAFISC,'M500',V_TMPBLCM,C_F100A.TPCRED_COFINS,C_F100A.VR_BC_COFINS,C_F100A.VR_ALIQ_COFINS,'P',C_F100A.VR_IMP_COFINS,C_F100A.CST_COFINS,C_F100A.CDFILIRECE,C_F100A.CDNATUBCALPCIT,0,C_F100A.VR_OPER,C_F100A.CDNATURECCOFINS,LTRIM(TO_CHAR(V_COUNTEST,'000')),V_CNPJFILI,C_F100A.COD_CTA);
            END IF;
         END IF;

         V_COD_PAR:= NULL;
         IF C_F100A.IND_OPER = '0' THEN
            /* CODIGO DO PARTICIPANTE */
            BEGIN
               SELECT MAX(CDCHAVEREG)
                 INTO V_COD_PAR
                 FROM TMP_REGDADOSSPED_PIS
                WHERE CDCHAVEREG = C_F100A.CD_PARTC
                  AND CDREGISTRO = '0150'
                  AND CDESTAB    = LTRIM(TO_CHAR(V_COUNTEST,'000'));
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_COD_PAR := NULL;
            END;

            IF (V_COD_PAR IS NULL) AND (C_F100A.CD_PARTC IS NOT NULL) THEN
               V_SEQUPROD:= NVL(V_SEQUPROD,0)+1;
               INSERT INTO TMP_REGDADOSSPED_PIS
                 (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
               VALUES
                 ('0150', V_SEQUPROD, C_F100A.CD_PARTC,NULL,LTRIM(TO_CHAR(V_COUNTEST,'000')));
            END IF;
         END IF;

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

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

      /*DEBITOS SEM NOTA FISCAL APURADOS PELAS CONTAS CONTABEIS - RECEITAS FINANCEIRAS*/
      FOR C_F100 IN(SELECT '|F100'  ||'|'||
                           IDOPER   ||'|'||
                           ''       ||'|'|| --COD PARTICIPANTE
                           ''       ||'|'|| --COD ITEM
                           TO_CHAR(P_DTFINAL,'ddmmyyyy')                                      ||'|'||
                           REPLACE(LTRIM(TO_CHAR(VROPER,'9999999999990.00')),'.',',')         ||'|'||
                           CST                                                                ||'|'||--CST PIS
                           REPLACE(LTRIM(TO_CHAR(BC_PIS,'9999999999990.0000')),'.',',')       ||'|'||
                           REPLACE(LTRIM(TO_CHAR(ALIQ_PIS,'990.0000')),'.',',')               ||'|'||
                           REPLACE(LTRIM(TO_CHAR(VR_IMP_PIS,'9999999999990.00')),'.',',')     ||'|'||
                           CST                                                                ||'|'||--CST COFINS
                           REPLACE(LTRIM(TO_CHAR(BC_COFINS,'9999999999990.0000')),'.',',')    ||'|'||
                           REPLACE(LTRIM(TO_CHAR(ALIQ_COFINS,'990.0000')),'.',',')            ||'|'||
                           REPLACE(LTRIM(TO_CHAR(VR_IMP_COFINS,'9999999999990.00')),'.',',')  ||'|'||
                           ''          ||'|'||--NATUREZA BC CREDITO
                           ''          ||'|'||--INDICADOR DE ORIGEM DE CREDITO
                           CDCONTCTBL  ||'|'||--COD CONTA
                           ''          ||'|'||--COD CENTRO DE CUSTO
                           DSHISTLANC  ||'|' AS REGISTRO,--DESCRICAO OPERACAO
                           TRIM(REPLACE(REPLACE(NRINSJURIEST, CHR(10), ''), CHR(9), '')) AS NRINSJURIEST,
                           CST, BC_PIS, BC_COFINS, ALIQ_COFINS, ALIQ_PIS, VR_IMP_PIS,
                           VR_IMP_COFINS, VROPER, CDCONTCTBL, CDNATUREC, FILIAL
                      FROM (SELECT GP.IDOPERPICO AS IDOPER,
                                   SUM(LC.VRLANCAMENT) AS VROPER,
                                   GP.CDSITUTRIBPICO AS CST,
                                   ROUND(SUM(CASE WHEN CDSITUTRIBPICO IN ('01','02','03','05') THEN LC.VRLANCAMENT ELSE 0 END),4) AS BC_PIS,
                                   ROUND(GP.VRALIQPIS,4) AS ALIQ_PIS,
                                   ROUND(SUM(((CASE WHEN GP.CDSITUTRIBPICO IN ('01','02','03','05') THEN LC.VRLANCAMENT ELSE 0 END) * VRALIQPIS)/100),2) AS VR_IMP_PIS,
                                   ROUND(SUM(CASE WHEN GP.CDSITUTRIBPICO IN ('01','02','03','05') THEN LC.VRLANCAMENT ELSE 0 END),4) AS BC_COFINS,
                                   ROUND(GP.VRALIQCOFINS,4) AS ALIQ_COFINS,
                                   ROUND(SUM(((CASE WHEN GP.CDSITUTRIBPICO IN ('01','02','03','05') THEN LC.VRLANCAMENT ELSE 0 END) * VRALIQCOFINS)/100),2) AS VR_IMP_COFINS,
                                   CGP.CDCONTCTBL,
                                   '' AS DSHISTLANC,
                                   REPLACE(REPLACE(REPLACE(REPLACE(E.NRINSJUREMPR,'.',''),'/',''),'-',''),'\','')  AS NRINSJURIEST,
                                   GP.CDNATUREC, GP.IDCUMUPISCOFIL AS FILIAL
                              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 GP.DTINIVALID <= LC.DTLANCCONT
                               AND (GP.DTFIMVALID >= LC.DTLANCCONT OR GP.DTFIMVALID IS NULL)
                               AND LC.CDEMPRESA = P_CDEMPRESA
                               AND LC.CDEMPRESA = E.CDEMPRESA
                               AND ((LC.VRLANCAMENT > 0 AND CGP.IDCONSVRDEB = 'N') OR (CGP.IDCONSVRDEB = 'S'))
                             GROUP BY GP.IDOPERPICO, GP.CDSITUTRIBPICO, ROUND(GP.VRALIQPIS,4), ROUND(GP.VRALIQCOFINS,4), GP.IDCUMUPISCOFIL,
                                      CGP.CDCONTCTBL, REPLACE(REPLACE(REPLACE(REPLACE(E.NRINSJUREMPR,'.',''),'/',''),'-',''),'\',''), GP.CDNATUREC)
                     WHERE VROPER > 0)LOOP

         /*BUSCA O CNPJ DO ESTABELECIMENTO*/
         V_CNPJFILI:= C_F100.NRINSJURIEST;

         /*VERIFICA SE O REGISTRO DE IDENTIFICACAO DO ESTABELECIMENTO JA FOI INSERIDO */
         V_CDESTAB:= NULL;
         BEGIN
            SELECT MAX(CDESTAB)
              INTO V_CDESTAB
              FROM TMP_REGDADOSSPED_PIS
             WHERE CDREGISTRO = 'F010'
               AND CDCHAVEREG = V_CNPJFILI;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_CDESTAB := NULL;
         END;

         /*IDENTIFICACAO DO ESTABELECIMENTO*/
         IF V_CDESTAB IS NULL THEN
            V_REG_F010 := '|F010|'||V_CNPJFILI||'|';

            /*VERIFICA SE O ESTABELECIMENTO JA FOI INSERIDO*/
            V_CDESTAB := NULL;
            BEGIN
               SELECT MAX(CDESTAB)
                 INTO V_CDESTAB
                 FROM TMP_REGDADOSSPED_PIS
                WHERE CDREGISTRO = '0140'
                  AND CDCHAVEREG = V_CNPJFILI;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_CDESTAB := NULL;
            END;

            /*INSERE O ESTABELECIMENTO*/
            IF V_CDESTAB IS NULL THEN
               V_CONTESTAB:= NVL(V_CONTESTAB,0)+1;
               V_COUNTEST := V_CONTESTAB;
               BEGIN
                  SELECT '|0140'            ||'|'||
                         LTRIM(TO_CHAR(V_COUNTEST,'000')) ||'|'||
                         E.NMRAZSOCEMPR     ||'|'||
                         V_CNPJFILI         ||'|'||
                         E.SGESTADO         ||'|'||
                         ''                 ||'|'||
                         M.CDMUNICIBGE      ||'|'||
                         ''                 ||'|'||
                         ''                 ||'|' AS REG
                    INTO V_REG
                    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:= NULL;
               END;

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

               V_SEQUPROD:= NVL(V_SEQUPROD,0)+1;
               INSERT INTO TMP_REGDADOSSPED_PIS
                  (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
               VALUES
                  ('0140', V_SEQUPROD, V_CNPJFILI ,NULL,LTRIM(TO_CHAR(V_COUNTEST,'000')));
            ELSE
               V_COUNTEST:= TO_NUMBER(V_CDESTAB);
            END IF;

            V_CONTADOR := NVL(V_CONTADOR,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'501'||LTRIM(TO_CHAR(V_COUNTEST,'000')),V_REG_F010);

            V_SEQUPROD:= NVL(V_SEQUPROD,0)+1;
            INSERT INTO TMP_REGDADOSSPED_PIS
              (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
            VALUES
              ('F010', V_SEQUPROD, V_CNPJFILI, NULL,LTRIM(TO_CHAR(V_COUNTEST,'000')));
         ELSE
            V_COUNTEST := TO_NUMBER(V_CDESTAB);
         END IF;

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

         IF (V_COD_CC IS NULL) AND (C_F100.CDCONTCTBL 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_F100.CDCONTCTBL, NULL);
         END IF;

         V_CONTADOR:= NVL(V_CONTADOR,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'501'||LTRIM(TO_CHAR(V_COUNTEST,'000'))||'03'||TO_CHAR(V_CONTADOR),C_F100.REGISTRO);

         V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
         INSERT INTO TMP_REGBLOCOM_PIS
            (CHAVENOTA,CDREGISTRO,CDCONTREGI,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CSTPISCOFINS,CDFILIAL,CDNATUBCALPCIT,VRIMPRETIDO,VRTOTNF,CDNATUREC,CDESTAB,NRINSJUREST,CDCONTCTBL)
         VALUES
            ('F100'||C_F100.FILIAL||C_F100.CDCONTCTBL,'M200',V_TMPBLCM,'',C_F100.BC_PIS,C_F100.ALIQ_PIS,'P',C_F100.VR_IMP_PIS,C_F100.CST,C_F100.FILIAL,'',0,C_F100.VROPER,C_F100.CDNATUREC,LTRIM(TO_CHAR(V_COUNTEST,'000')),V_CNPJFILI,C_F100.CDCONTCTBL);

         V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
         INSERT INTO TMP_REGBLOCOM_PIS
            (CHAVENOTA,CDREGISTRO,CDCONTREGI,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CSTPISCOFINS,CDFILIAL,CDNATUBCALPCIT,VRIMPRETIDO,VRTOTNF,CDNATUREC,CDESTAB,NRINSJUREST,CDCONTCTBL)
         VALUES
            ('F100'||C_F100.FILIAL||C_F100.CDCONTCTBL,'M600',V_TMPBLCM,'',C_F100.BC_COFINS,C_F100.ALIQ_COFINS,'P',C_F100.VR_IMP_COFINS,C_F100.CST,C_F100.FILIAL,'',0,C_F100.VROPER,C_F100.CDNATUREC,LTRIM(TO_CHAR(V_COUNTEST,'000')),V_CNPJFILI,C_F100.CDCONTCTBL);
      END LOOP;
   END IF;

  --------------------------------/*Registro F120 - CREDITO IMOBILIZADO */------------------------------------------------------------------------------
   FOR C_F120 IN (SELECT '|F120'        ||'|'||
                         CDNATUBCALC  ||'|'||
                         CDTIPOBEM    ||'|'||
                         IDORIGEMBEM  ||'|'||
                         IDUTILIZACAO ||'|'||
                         REPLACE(LTRIM(TO_CHAR(SUM(VRPARCELA),'9999999999990.00')),'.',',')||'|'||
                         '0,00'       ||'|'||
                         CDSITUTRIB   ||'|'||
                         REPLACE(LTRIM(TO_CHAR(SUM(VRPARCELA),'9999999999990.00')),'.',',')||'|'||
                         REPLACE(LTRIM(TO_CHAR(VRALIQPIS,     '99990.0000')),'.',',')      ||'|'||
                         REPLACE(LTRIM(TO_CHAR(SUM(VRPIS),    '9999999999990.00')),'.',',')||'|'||
                         CDSITUTRIB   ||'|'||
                         REPLACE(LTRIM(TO_CHAR(SUM(VRPARCELA),'9999999999990.00')),'.',',')||'|'||
                         REPLACE(LTRIM(TO_CHAR(VRALIQCOFINS,  '99990.0000')),'.',',')      ||'|'||
                         REPLACE(LTRIM(TO_CHAR(SUM(VRCOFINS), '9999999999990.00')),'.',',')||'|'||
                         CDCONTCTBL   ||'|'||
                         ''           ||'|'||
                         ''           ||'|'  AS REGISTRO, CDSITUTRIB, CDNATUBCALC, CDTPCRED, CDCONTCTBL, SUM(VRPARCELA) AS VRBASE,
                         SUM(VRPIS) AS VRPIS, SUM(VRCOFINS) AS VRCOFINS, VRALIQPIS, VRALIQCOFINS, CNPJ
                    FROM (SELECT C.CDNATUBCALC, C.CDTIPOBEM, C.IDORIGEMBEM, C.IDUTILIZACAO, C.CDSITUTRIB, C.CDTPCRED,
                                 C.VRPARCELA, C.VRALIQPIS, ROUND(C.VRPARCELA * (C.VRALIQPIS/100),2) AS VRPIS,
                                 C.VRALIQCOFINS, ROUND(C.VRPARCELA * (C.VRALIQCOFINS/100),2) AS VRCOFINS, C.CDCONTCTBL,
                                 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(E.NRINSJUREMPR,'.'),'/'),'-'),'\'),' ') AS CNPJ
                            FROM CREDBENFEITAPUR C, EMPRESA E
                           WHERE C.CDEMPRESA = P_CDEMPRESA
                             AND P_DTINICIAL BETWEEN C.DTINICREDITO AND C.DTFIMCREDITO
                             AND C.CDEMPRESA = E.CDEMPRESA)
                   GROUP BY CDNATUBCALC, CDTIPOBEM, IDORIGEMBEM, IDUTILIZACAO, CDSITUTRIB,
                            CDTPCRED, VRALIQPIS, VRALIQCOFINS, CDCONTCTBL, CNPJ)LOOP

      /*BUSCA O CNPJ DO ESTABELECIMENTO*/
      V_CNPJFILI:= C_F120.CNPJ;

      /*VERIFICA SE O REGISTRO DE IDENTIFICACAO DO ESTABELECIMENTO JA FOI INSERIDO */
      V_CDESTAB:= NULL;
      BEGIN
         SELECT MAX(CDESTAB)
           INTO V_CDESTAB
           FROM TMP_REGDADOSSPED_PIS
          WHERE CDREGISTRO = 'F010'
            AND CDCHAVEREG = V_CNPJFILI;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDESTAB := NULL;
      END;

      /*IDENTIFICACAO DO ESTABELECIMENTO*/
      IF V_CDESTAB IS NULL THEN
         V_REG_F010 := '|F010|'||V_CNPJFILI||'|';

         /*VERIFICA SE O ESTABELECIMENTO JA FOI INSERIDO*/
         V_CDESTAB := NULL;
         BEGIN
            SELECT MAX(CDESTAB)
              INTO V_CDESTAB
              FROM TMP_REGDADOSSPED_PIS
             WHERE CDREGISTRO = '0140'
               AND CDCHAVEREG = V_CNPJFILI;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_CDESTAB := NULL;
         END;

         /*INSERE O ESTABELECIMENTO*/
         IF V_CDESTAB IS NULL THEN
            V_CONTESTAB:= NVL(V_CONTESTAB,0)+1;
            V_COUNTEST := V_CONTESTAB;
            BEGIN
               SELECT '|0140'            ||'|'||
                      LTRIM(TO_CHAR(V_COUNTEST,'000')) ||'|'||
                      E.NMRAZSOCEMPR     ||'|'||
                      V_CNPJFILI         ||'|'||
                      E.SGESTADO         ||'|'||
                      ''                 ||'|'||
                      M.CDMUNICIBGE      ||'|'||
                      ''                 ||'|'||
                      ''                 ||'|' AS REG
                 INTO V_REG
                 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:= NULL;
            END;

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

            V_SEQUPROD:= NVL(V_SEQUPROD,0)+1;
            INSERT INTO TMP_REGDADOSSPED_PIS
               (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
            VALUES
               ('0140', V_SEQUPROD, V_CNPJFILI ,NULL,LTRIM(TO_CHAR(V_COUNTEST,'000')));
         ELSE
            V_COUNTEST:= TO_NUMBER(V_CDESTAB);
         END IF;

         V_CONTADOR := NVL(V_CONTADOR,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'501'||LTRIM(TO_CHAR(V_COUNTEST,'000')),V_REG_F010);

         V_SEQUPROD:= NVL(V_SEQUPROD,0)+1;
         INSERT INTO TMP_REGDADOSSPED_PIS
           (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
         VALUES
           ('F010', V_SEQUPROD, V_CNPJFILI, NULL,LTRIM(TO_CHAR(V_COUNTEST,'000')));
      ELSE
         V_COUNTEST := TO_NUMBER(V_CDESTAB);
      END IF;

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

      IF (V_COD_CC IS NULL) AND (C_F120.CDCONTCTBL 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_F120.CDCONTCTBL, NULL);
      END IF;

      V_CONTADOR:= NVL(V_CONTADOR,0)+1;
      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'501'||LTRIM(TO_CHAR(NVL(V_COUNTEST,0),'000'))||'05'||TO_CHAR(V_CONTADOR),C_F120.REGISTRO);

      V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
      INSERT INTO TMP_REGBLOCOM_PIS
         (CHAVENOTA,CDREGISTRO,CDCONTREGI,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CSTPISCOFINS,CDFILIAL,CDNATUBCALPCIT)
      VALUES
         ('F120'||C_F120.CDCONTCTBL,'M100',V_TMPBLCM,C_F120.CDTPCRED,C_F120.VRBASE,C_F120.VRALIQPIS,'P',C_F120.VRPIS,C_F120.CDSITUTRIB,'S',C_F120.CDNATUBCALC);

      V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
      INSERT INTO TMP_REGBLOCOM_PIS
         (CHAVENOTA,CDREGISTRO,CDCONTREGI,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CSTPISCOFINS,CDFILIAL,CDNATUBCALPCIT)
      VALUES
         ('F120'||C_F120.CDCONTCTBL,'M500',V_TMPBLCM,C_F120.CDTPCRED,C_F120.VRBASE,C_F120.VRALIQCOFINS,'P',C_F120.VRCOFINS,C_F120.CDSITUTRIB,'S',C_F120.CDNATUBCALC);
   END LOOP;

   /*F130 - CREDITOS DE AQUISICOES PARA O ATIVO IMOBILIZADO*/
   /*A TABELA TEMPORARIA E PREENCHIDA NO BLOCO C POIS, CASO EXISTAM CFOP'S QUE NAO SAO DO ATIVO, ELES SERAO GERADOS NO BLOCO C*/
   FOR C_F130 IN (SELECT '|F130'        ||'|'||
                         CDNATUBCALPCIT ||'|'||
                         IDBEMIMOB      ||'|'||/*VERIFICAR IDENT_BEM_IMOB*/
                         IND_ORIG       ||'|'||
                         '9'            ||'|'||/*VERIFICAR IND_UTIL_BEM_IMOB*/
                         DT_AQUIS       ||'|'||
                         REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_OPER),2), '999999999990.00')), '.', ',') ||'|'||
                         '0,00'         ||'|'||/*PARCELA A EXCLUIR*/
                         REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_OPER),2), '999999999990.00')), '.', ',') ||'|'||
                         '1'            ||'|'||/*NUMERO DE PARCELAS*/
                         CSTPIS         ||'|'||
                         REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_BCPIS),2), '999999999990.00')), '.', ',') ||'|'||
                         REPLACE(LTRIM(TO_CHAR(ROUND(VL_ALIQPIS,4), '999999990.0000')), '.', ',') ||'|'||
                         REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_PIS),2), '999999999990.00')), '.', ',') ||'|'||
                         CSTCOFINS      ||'|'||
                         REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_BCCOFINS),2), '999999999990.00')), '.', ',') ||'|'||
                         REPLACE(LTRIM(TO_CHAR(ROUND(VL_ALIQCOFINS,4), '999999990.0000')), '.', ',') ||'|'||
                         REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_COFINS),2), '999999999990.00')), '.', ',') ||'|'||
                         MAX(COD_CTA)   ||'|'||/*CODIGO CONTA CONTABIL*/
                         ''             ||'|'||/*CODIGO CENTRO DE CUSTO*/
                         ''             ||'|'  /*DESCRICAO COMPLEMENTAR DO BEM*/ AS REGISTRO,
                         MAX(CDFILIAL) AS CDFILIAL, CDTPCREDPICOITE, CDNATUBCALPCIT, CSTPIS, CSTCOFINS,
                         SUM(VL_BCPIS) AS VL_BCPIS, ROUND(VL_ALIQPIS,4) AS VL_ALIQPIS, SUM(VL_PIS) AS VL_PIS,
                         SUM(VL_BCCOFINS) AS VL_BCCOFINS, ROUND(VL_ALIQCOFINS,4) AS VL_ALIQCOFINS,
                         SUM(VL_COFINS) AS VL_COFINS, MAX(CDINSCESTA) AS CDINSCESTA, MAX(COD_CTA) AS COD_CTA,
                         TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJURIEST,'.'),'/'),'-'),'\'),CHR(10)),CHR(9))) AS NRINSCRICAO
                    FROM (SELECT FI.CDFILIAL, IT.NRSEQITPROD, IT.CDTPCREDPICOITE AS CDTPCREDPICOITE, IT.CDNATUBCALPCIT AS CDNATUBCALPCIT,
                                 DECODE(SUBSTR(IT.CDFISCOPERIT,1,1),'1','0','2','0','3','1') AS IND_ORIG, TMP.CDCAMPO3 AS DT_AQUIS,
                                 VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL,IT.NRLANCTONF,IT.NRSEQITPROD,P_PIS,'B') AS VL_OPER,
                                 DECODE(IM.CDIMPOSTONFE,P_PIS,IT.CDSITUTRIBITNFE) AS CSTPIS,
                                 VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL,IT.NRLANCTONF,IT.NRSEQITPROD,P_PIS,'B') AS VL_BCPIS,
                                 VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL,IT.NRLANCTONF,IT.NRSEQITPROD,P_PIS,'A') AS VL_ALIQPIS,
                                 VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL,IT.NRLANCTONF,IT.NRSEQITPROD,P_PIS,'I') AS VL_PIS,
                                 DECODE(IM.CDIMPOSTONFE,P_PIS,IT.CDSITUTRIBITNFE) AS CSTCOFINS,
                                 VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL,IT.NRLANCTONF,IT.NRSEQITPROD,P_COFINS,'B') AS VL_BCCOFINS,
                                 VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL,IT.NRLANCTONF,IT.NRSEQITPROD,P_COFINS,'A') AS VL_ALIQCOFINS,
                                 VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL,IT.NRLANCTONF,IT.NRSEQITPROD,P_COFINS,'I') AS VL_COFINS,
                                 CASE WHEN SUBSTR(NVL(IP.CDPRODNCMTRANS,PR.CDCLASFISC), 1,5) IN ('10101','10102') THEN '01'
                                      WHEN (SUBSTR(NVL(IP.CDPRODNCMTRANS,PR.CDCLASFISC),1,5) IN ('10126','10128','10131') OR SUBSTR(NVL(IP.CDPRODNCMTRANS,PR.CDCLASFISC),1,9) IN ('101110000','101290000','101380000')) THEN '03'
                                      WHEN SUBSTR(NVL(IP.CDPRODNCMTRANS,PR.CDCLASFISC), 1,2) IN ('84','85','90') THEN '04'
                                      WHEN SUBSTR(NVL(IP.CDPRODNCMTRANS,PR.CDCLASFISC), 1,2) IN ('64','65','66','67','68','69','70','72','73','74','75','76','77','78','79','80','81','82','83','91','92','93','94','95','96','97') THEN '05'
                                      WHEN SUBSTR(NVL(IP.CDPRODNCMTRANS,PR.CDCLASFISC), 1,2) IN ('86','87','88','89') THEN '06'
                                      ELSE '99' END AS IDBEMIMOB, N.CDINSCESTA, IE.NRINSJURIEST,
                                 CONTCTBL_SPED(N.IDENTRSAIDOP,IP.CDPRODUTO,N.CDTIPOOPER,DECODE(N.IDENTRSAIDOP,'E',N.CDFILIRECE,N.CDFILIEMIT)) AS COD_CTA
                            FROM TMP_REGDADOSSPED_PIS TMP, NOTAFISCAL N, INSCRESTAD IE, ITEMPROIMPOS IT, ITEMPRODUTO IP, PRODUTO PR, IMPOSTO IM, FILIAL FI
                           WHERE TMP.CDREGISTRO  = 'F130'
                             AND TMP.CDCAMPO1    = IT.CDFILIAL
                             AND TMP.CDCAMPO2    = IT.NRLANCTONF
                             AND IT.CDIMPOSTO    = IM.CDIMPOSTO
                             AND IM.CDIMPOSTONFE = P_PIS
                             AND SUBSTR(IT.CDFISCOPERIT,2,3) IN ('406','551','552','553','554','555','604')
                             AND IT.CDSITUTRIBITNFE BETWEEN '50' AND '66'
                             AND IT.CDFILIAL     = IP.CDFILIAL
                             AND IT.NRLANCTONF   = IP.NRLANCTONF
                             AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                             AND IP.CDPRODUTO    = PR.CDPRODUTO
                             AND TMP.CDCAMPO4    = FI.CDFILIAL(+)
                             AND IT.CDFILIAL    = N.CDFILIAL
                             AND IT.NRLANCTONF  = N.NRLANCTONF
                             AND N.CDEMPRESA    = IE.CDEMPRESA
                             AND N.CDINSCESTA   = IE.CDINSCESTA)
                   GROUP BY CDTPCREDPICOITE, CDNATUBCALPCIT, IDBEMIMOB, IND_ORIG, DT_AQUIS,
                            CSTPIS, ROUND(VL_ALIQPIS,4), CSTCOFINS, ROUND(VL_ALIQCOFINS,4),
                            TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJURIEST,'.'),'/'),'-'),'\'),CHR(10)),CHR(9)))) LOOP

      /*BUSCA O CNPJ DO ESTABELECIMENTO*/
      V_CNPJFILI := C_F130.NRINSCRICAO;

      /*VERIFICA SE O REGISTRO DE IDENTIFICACAO DO ESTABELECIMENTO JA FOI INSERIDO */
      V_CDESTAB := NULL;
      BEGIN
         SELECT MAX(CDESTAB)
           INTO V_CDESTAB
           FROM TMP_REGDADOSSPED_PIS
          WHERE CDREGISTRO = 'F010'
            AND CDCHAVEREG = V_CNPJFILI;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDESTAB := NULL;
      END;

      /*IDENTIFICACAO DO ESTABELECIMENTO*/
      IF V_CDESTAB IS NULL THEN
         V_REG_F010 := '|F010|'||V_CNPJFILI||'|';

         /*VERIFICA SE O ESTABELECIMENTO JA FOI INSERIDO*/
         V_CDESTAB := NULL;
         BEGIN
            SELECT MAX(CDESTAB)
              INTO V_CDESTAB
              FROM TMP_REGDADOSSPED_PIS
             WHERE CDREGISTRO = '0140'
               AND CDCHAVEREG = V_CNPJFILI;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_CDESTAB := NULL;
         END;

         /*INSERE O ESTABELECIMENTO*/
         IF V_CDESTAB IS NULL THEN
            V_CONTESTAB:= NVL(V_CONTESTAB,0)+1;
            V_COUNTEST := V_CONTESTAB;
            BEGIN
               SELECT '|0140'         ||'|'||
                      LTRIM(TO_CHAR(V_COUNTEST,'000')) ||'|'||
                      E.NMRAZSOCEMPR ||'|'||
                      V_CNPJFILI     ||'|'||
                      I.SGESTADO     ||'|'||
                      ''             ||'|'||
                      M.CDMUNICIBGE  ||'|'||
                      ''             ||'|'||
                      ''             ||'|' AS REG
                 INTO V_REG
                 FROM MUNICIPIO M, EMPRESA E, INSCRESTAD I
                WHERE I.CDEMPRESA    = P_CDEMPRESA
                  AND I.CDINSCESTA   = C_F130.CDINSCESTA
                  AND I.CDPAIS       = M.CDPAIS
                  AND I.SGESTADO     = M.SGESTADO
                  AND I.CDMUNICIPIO  = M.CDMUNICIPIO
                  AND E.CDEMPRESA    = I.CDEMPRESA;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_REG:= NULL;
            END;

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

            V_SEQUPROD:= NVL(V_SEQUPROD,0)+1;
            INSERT INTO TMP_REGDADOSSPED_PIS
               (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
             VALUES
               ('0140', V_SEQUPROD, V_CNPJFILI ,NULL,LTRIM(TO_CHAR(V_COUNTEST,'000')));
         ELSE
            V_COUNTEST:= TO_NUMBER(V_CDESTAB);
         END IF;

         V_CONTADOR := NVL(V_CONTADOR,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'501'||LTRIM(TO_CHAR(V_COUNTEST,'000')),V_REG_F010);

         V_SEQUPROD:= NVL(V_SEQUPROD,0)+1;
         INSERT INTO TMP_REGDADOSSPED_PIS
           (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
         VALUES
           ('F010', V_SEQUPROD, V_CNPJFILI, NULL, LTRIM(TO_CHAR(V_COUNTEST,'000')));
      ELSE
         V_COUNTEST := TO_NUMBER(V_CDESTAB);
      END IF;

      V_CONTADOR:= NVL(V_CONTADOR,0)+1;
      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'501'||LTRIM(TO_CHAR(V_COUNTEST,'000'))||'15'||TO_CHAR(V_CONTADOR),C_F130.REGISTRO);

      /*INSERE O VALOR REFERENTE AO PIS ENTRADA*/
      IF (C_F130.CSTPIS IN ('50','51','52','53','54','55','56','60','61','62','63','64','65','66')) AND (C_F130.VL_BCPIS > 0) THEN
         V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
         INSERT INTO TMP_REGBLOCOM_PIS
            (CHAVENOTA,CDREGISTRO,CDCONTREGI,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CSTPISCOFINS,CDFILIAL,CDNATUBCALPCIT)
         VALUES
            ('F130'||C_F130.CDFILIAL,'M100',V_TMPBLCM,C_F130.CDTPCREDPICOITE,C_F130.VL_BCPIS,C_F130.VL_ALIQPIS,'P',C_F130.VL_PIS,C_F130.CSTPIS,C_F130.CDFILIAL,C_F130.CDNATUBCALPCIT);
      END IF;

      /*INSERE O VALOR REFERENTE AO COFINS ENTRADA*/
      IF (C_F130.CSTCOFINS IN ('50','51','52','53','54','55','56','60','61','62','63','64','65','66')) AND (C_F130.VL_BCCOFINS > 0) THEN
         V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
         INSERT INTO TMP_REGBLOCOM_PIS
            (CHAVENOTA,CDREGISTRO,CDCONTREGI,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CSTPISCOFINS,CDFILIAL,CDNATUBCALPCIT)
         VALUES
            ('F130'||C_F130.CDFILIAL,'M500',V_TMPBLCM,C_F130.CDTPCREDPICOITE,C_F130.VL_BCCOFINS,C_F130.VL_ALIQCOFINS,'P',C_F130.VL_COFINS,C_F130.CSTCOFINS,C_F130.CDFILIAL,C_F130.CDNATUBCALPCIT);
      END IF;

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

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

   /*F500*/
   /*GERA REGISTROS PARA O REGIME DE CAIXA CONSOLIDADO, SE O REGIME FOR CUMULATIVO E O CRITERIO DE ESCRITURACAO 1*/
   IF ((P_CDINCTRIB = '2') AND (P_CRTESCRITUR = '1')) THEN

      BEGIN
         SELECT MAX(DECODE(MP.CDIMPOSTO,P_PIS,NRSEQUMAPAPU,''))    AS NRSEQUMAPPIS,
                MAX(DECODE(MP.CDIMPOSTO,P_COFINS,NRSEQUMAPAPU,'')) AS NRSEQUMAPCOFINS
           INTO V_NRSEQUMAPPIS, V_NRSEQUMAPCON
           FROM MAPAAPURAC MP
          WHERE MP.CDEMPRESA = P_CDEMPRESA
            AND MP.CDIMPOSTO IN (P_PIS,P_COFINS)
            AND MP.DTINIPERIODO BETWEEN P_DTINICIAL AND P_DTFINAL;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_NRSEQUMAPPIS := NULL;
            V_NRSEQUMAPCON := NULL;
      END;

      /*(F500)- VALOR DA RECEITA DE PIS E COFINS*/
      FOR C_F500 IN (SELECT REGISTRO                                                                            ||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_REC_CAIXA),2), '999999999990.00')), '.', ',')    ||'|'||/*VALOR DA RECETA REGIME DE CAIXA*/
                            CST_PIS                                                                             ||'|'||/*CST PIS*/
                            REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_DESC_PIS),2), '999999999990.00')), '.', ',')     ||'|'||/*DESCONTO PIS*/
                            REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_BC_PIS),2), '99999999990.00')), '.', ',')        ||'|'||/*BASE DE CALCULO PIS*/
                            REPLACE(LTRIM(TO_CHAR(ROUND(MAX(ALIQ_PIS),4), '99999990.0000')), '.', ',')          ||'|'||/*ALIQUOTA PIS*/
                            REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_PIS),2), '99999990.00')), '.', ',')              ||'|'||/*VALOR DO PIS*/
                            CST_COFINS                                                                          ||'|'||/*CST COFINS*/
                            REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_DESC_COFINS),2), '999999999990.00')), '.', ',')  ||'|'||/*DESCONTO COFINS*/
                            REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_BC_COFINS),2), '999999999990.00')), '.', ',')    ||'|'||/*BASE DE CALCULO COFINS */
                            REPLACE(LTRIM(TO_CHAR(ROUND(MAX(ALIQ_COFINS),4), '99999990.0000')), '.', ',')       ||'|'||/*ALIQUOTA COFINS*/
                            REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_COFINS),2), '99999990.00')), '.', ',')           ||'|'||/*VALOR DO COFINS*/
                            COD_MOD                                                                             ||'|'||/*MODELO DE DOCUMENTO*/
                            CFOP                                                                                ||'|'||/*CFOP*/
                            COD_CTA                                                                             ||'|'||/*CODIGO DA CONTA CONTABIL ANALITICA*/
                            INFO_COMPL                                                                          ||'|'/*INFORMACAO COMPLEMENTAR*/ AS REGISTRO,
                            TRIM(REPLACE(REPLACE(NRINSJURIEST,CHR(10)),CHR(9))) AS NRINSJURIEST,
                            MAX(CDINSCESTA) AS CDINSCESTA, MAX(CDFILIAL) AS CDFILIAL,
                            CST_PIS, CST_COFINS, COD_CTA, MAX(CDNATUREC) AS CDNATUREC, SUM(VL_REC_CAIXA) AS VROPER,
                            SUM(VL_BC_PIS) AS BC_PIS, MAX(ALIQ_PIS) AS ALIQ_PIS, SUM(VL_PIS) AS VR_IMP_PIS,
                            SUM(VL_BC_COFINS) AS BC_COFINS, MAX(ALIQ_COFINS) AS ALIQ_COFINS, SUM(VL_COFINS) AS VR_IMP_COFINS, CFOP,
                            (CASE WHEN SUBSTR(CFOP,1,1) IN ('1','2','3') THEN 'E' ELSE 'S' END) AS IDENTRSAIDOP
                       FROM (SELECT '|F500' AS REGISTRO,
                                    NVL(VALOR_IMPOSTO_ITEM_NFE(P.CDFILIAL,P.NRLANCTONF,P.NRSEQITPROD,P_PIS,'C',''),0) AS VL_REC_CAIXA,
                                    DECODE(I.CDIMPOSTONFE,P_PIS,T.CDSITUTRIBITNFE,'') AS CST_PIS,
                                    0 AS VL_DESC_PIS,
                                    NVL(VALOR_IMPOSTO_ITEM_NFE(P.CDFILIAL,P.NRLANCTONF,P.NRSEQITPROD,P_PIS,'B','1'),0) AS VL_BC_PIS,
                                    NVL(VALOR_IMPOSTO_ITEM_NFE(P.CDFILIAL,P.NRLANCTONF,P.NRSEQITPROD,P_PIS,'A','1'),0) AS ALIQ_PIS,
                                    NVL(VALOR_IMPOSTO_ITEM_NFE(P.CDFILIAL,P.NRLANCTONF,P.NRSEQITPROD,P_PIS,'I','1'),0) AS VL_PIS,
                                    DECODE(I.CDIMPOSTONFE,P_PIS,T.CDSITUTRIBITNFE,'') AS CST_COFINS,
                                    0 AS VL_DESC_COFINS,
                                    NVL(VALOR_IMPOSTO_ITEM_NFE(P.CDFILIAL,P.NRLANCTONF,P.NRSEQITPROD,P_COFINS,'B','1'),0) AS VL_BC_COFINS,
                                    NVL(VALOR_IMPOSTO_ITEM_NFE(P.CDFILIAL,P.NRLANCTONF,P.NRSEQITPROD,P_COFINS,'A','1'),0) AS ALIQ_COFINS,
                                    NVL(VALOR_IMPOSTO_ITEM_NFE(P.CDFILIAL,P.NRLANCTONF,P.NRSEQITPROD,P_COFINS,'I','1'),0) AS VL_COFINS,
                                    DECODE(SUBSTR(CDESTAB,LENGTH(TMP.CDESTAB)-2,3),'ISS','98',N.CDMODDOCFISC) AS COD_MOD, T.CDFISCOPERIT AS CFOP,
                                    CONTCTBL_SPED(N.IDENTRSAIDOP,P.CDPRODUTO,N.CDTIPOOPER,DECODE(N.IDENTRSAIDOP,'E',N.CDFILIRECE,N.CDFILIEMIT)) AS  COD_CTA,
                                    '' AS INFO_COMPL, NVL(T.CDNATUREC,R.CDNATUREC) AS CDNATUREC, F.CDFILIAL, N.CDINSCESTA,
                                    REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.'),'/'),'-'),'\')  AS NRINSJURIEST
                               FROM TMP_REGDADOSSPED_PIS TMP, NOTAFISCAL N, FILIAL F, ITEMPROIMPOS T, IMPOSTO I,
                                    ITEMPRODUTO P, RELACPRODITGEN R, INSCRESTAD IE
                              WHERE TMP.CDREGISTRO = 'NOTA'
                                AND TMP.CDCAMPO1   = N.CDFILIAL
                                AND TMP.CDCAMPO2   = N.NRLANCTONF
                                AND NVL(N.IDSISTEMA,'DFS') <> 'FIS'
                                AND N.CDEMPRESA    = IE.CDEMPRESA
                                AND N.CDINSCESTA   = IE.CDINSCESTA
                                AND DECODE(IDENTRSAIDOP,'E',N.CDFILIRECE, N.CDFILIEMIT) = F.CDFILIAL
                                AND IDENTRSAIDOP   = 'S'
                                AND N.CDFILIAL     = T.CDFILIAL
                                AND N.NRLANCTONF   = T.NRLANCTONF
                                AND T.CDIMPOSTO    = I.CDIMPOSTO
                                AND I.CDIMPOSTONFE IN (P_PIS)
                                AND T.CDFILIAL     = P.CDFILIAL
                                AND T.NRLANCTONF   = P.NRLANCTONF
                                AND T.NRSEQITPROD  = P.NRSEQITPROD
                                AND P.CDPRODUTO    = R.CDPRODUTO(+)
                                AND R.CDLIVROMAG(+)= P_CDLIVROMAG
                                AND NOT EXISTS (SELECT CDFILIAL, NRLANCTONF
                                                  FROM IMPBAIXAREC
                                                 WHERE CDFILIAL     = TMP.CDCAMPO1
                                                   AND NRLANCTONF   = TMP.CDCAMPO2
                                                   AND NRSEQUMAPAPU = V_NRSEQUMAPPIS)
                              UNION ALL
                             SELECT '|F500' AS REGISTRO,
                                    NVL(VALOR_IMPOSTO_ITEM_NFE_CAIXA(N.CDFILIAL,N.NRLANCTONF,P_PIS,'C',T.DTBAIXAREC,'',T.CDFISCOPERNF,T.CDSITUTRIBITNFE,T.NRSEQUENCIAL),0) AS VL_REC_CAIXA,
                                    T.CDSITUTRIBITNFE AS CST_PIS,
                                    0 AS VL_DESC_PIS,
                                    NVL(VALOR_IMPOSTO_ITEM_NFE_CAIXA(N.CDFILIAL,N.NRLANCTONF,P_PIS,'B',T.DTBAIXAREC,'1',T.CDFISCOPERNF,T.CDSITUTRIBITNFE,T.NRSEQUENCIAL),0) AS VL_BC_PIS,
                                    NVL(VALOR_IMPOSTO_ITEM_NFE_CAIXA(N.CDFILIAL,N.NRLANCTONF,P_PIS,'A',T.DTBAIXAREC,'1',T.CDFISCOPERNF,T.CDSITUTRIBITNFE,T.NRSEQUENCIAL),0) AS ALIQ_PIS,
                                    NVL(VALOR_IMPOSTO_ITEM_NFE_CAIXA(N.CDFILIAL,N.NRLANCTONF,P_PIS,'I',T.DTBAIXAREC,'1',T.CDFISCOPERNF,T.CDSITUTRIBITNFE,T.NRSEQUENCIAL),0) AS VL_PIS,
                                    T.CDSITUTRIBITNFE AS CST_COFINS,
                                    0 AS VL_DESC_COFINS,
                                    NVL(VALOR_IMPOSTO_ITEM_NFE_CAIXA(N.CDFILIAL,N.NRLANCTONF,P_COFINS,'B',T.DTBAIXAREC,'1',T.CDFISCOPERNF,T.CDSITUTRIBITNFE,T.NRSEQUENCIAL),0) AS VL_BC_COFINS,
                                    NVL(VALOR_IMPOSTO_ITEM_NFE_CAIXA(N.CDFILIAL,N.NRLANCTONF,P_COFINS,'A',T.DTBAIXAREC,'1',T.CDFISCOPERNF,T.CDSITUTRIBITNFE,T.NRSEQUENCIAL),0) AS ALIQ_COFINS,
                                    NVL(VALOR_IMPOSTO_ITEM_NFE_CAIXA(N.CDFILIAL,N.NRLANCTONF,P_COFINS,'I',T.DTBAIXAREC,'1',T.CDFISCOPERNF,T.CDSITUTRIBITNFE,T.NRSEQUENCIAL),0) AS VL_COFINS,
                                    DECODE(SUBSTR(CDESTAB,LENGTH(TMP.CDESTAB)-2,3),'ISS','98',N.CDMODDOCFISC) AS COD_MOD, T.CDFISCOPERNF AS CFOP,
                                    CONTCTBL_SPED(N.IDENTRSAIDOP,R.CDPRODUTO,N.CDTIPOOPER,DECODE(N.IDENTRSAIDOP,'E',N.CDFILIRECE,N.CDFILIEMIT)) AS  COD_CTA,
                                    '' AS INFO_COMPL, NVL(R.CDNATUREC,T.CDNATUREC) AS CDNATUREC, F.CDFILIAL, IE.CDINSCESTA,
                                    REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.'),'/'),'-'),'\')  AS NRINSJURIEST
                               FROM TMP_REGDADOSSPED_PIS TMP, NOTAFISCAL N, FILIAL F, IMPBAIXAREC T, IMPOSTO I, INSCRESTAD IE,
                                    (SELECT IT.NRLANCTONF, IT.CDFILIAL, MAX(NVL(IT.CDNATUREC,R.CDNATUREC)) AS CDNATUREC, IT.CDSITUTRIBITNFE, MAX(IP.CDPRODUTO) AS CDPRODUTO
                                       FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, RELACPRODITGEN R, IMPOSTO IM, TMP_REGDADOSSPED_PIS TMP
                                      WHERE IT.NRSEQITPROD  = IP.NRSEQITPROD
                                        AND IT.CDFILIAL     = IP.CDFILIAL
                                        AND IT.NRLANCTONF   = IP.NRLANCTONF
                                        AND IP.CDPRODUTO    = R.CDPRODUTO(+)
                                        AND R.CDLIVROMAG(+) = P_CDLIVROMAG
                                        AND IT.CDIMPOSTO    = IM.CDIMPOSTO
                                        AND IM.CDIMPOSTONFE = P_PIS
                                        AND TMP.CDREGISTRO  = 'NOTA'
                                        AND TMP.CDCAMPO1    = IT.CDFILIAL
                                        AND TMP.CDCAMPO2    = IT.NRLANCTONF
                                      GROUP BY IT.NRLANCTONF, IT.CDFILIAL, IT.CDSITUTRIBITNFE) R
                              WHERE TMP.CDREGISTRO    = 'NOTA'
                                AND TMP.CDCAMPO1      = N.CDFILIAL
                                AND TMP.CDCAMPO2      = N.NRLANCTONF
                                --AND NVL(N.IDSISTEMA,'DFS') <> 'TMP'
                                AND DECODE(IDENTRSAIDOP,'E',N.CDFILIRECE, N.CDFILIEMIT) = F.CDFILIAL
                                AND IDENTRSAIDOP      = 'S'
                                AND N.CDFILIAL        = T.CDFILIAL
                                AND N.NRLANCTONF      = T.NRLANCTONF
                                AND N.CDEMPRESA       = IE.CDEMPRESA
                                AND N.CDINSCESTA      = IE.CDINSCESTA
                                AND T.CDIMPOSTO       = I.CDIMPOSTO
                                AND I.CDIMPOSTONFE    IN (P_PIS,P_COFINS)
                                AND T.CDFILIAL        = R.CDFILIAL(+)
                                AND T.NRLANCTONF      = R.NRLANCTONF(+)
                                AND T.CDSITUTRIBITNFE = R.CDSITUTRIBITNFE(+)
                                AND T.NRSEQUMAPAPU    IN (V_NRSEQUMAPPIS,V_NRSEQUMAPCON)
                              UNION ALL
                            SELECT '|F500' AS REGISTRO,
                                    VROPER AS VL_REC_CAIXA,
                                    CST    AS CST_PIS,
                                    0 AS VL_DESC_PIS,
                                    BC_PIS AS VL_BC_PIS,
                                    ALIQ_PIS,
                                    VR_IMP_PIS AS VL_PIS,
                                    CST    AS CST_COFINS,
                                    0 AS VL_DESC_COFINS,
                                    BC_COFINS  AS VL_BC_COFINS,
                                    ALIQ_COFINS,
                                    VR_IMP_COFINS AS VL_COFINS,
                                    '' AS COD_MOD,
                                    '' AS CFOP,
                                    CDCONTCTBL AS COD_CTA,
                                    '' AS INFO_COMPL,
                                    CDNATUREC,
                                    FILIAL  AS CDFILIAL,
                                    ''  AS CDINSCESTA,
                                    NRINSJURIEST
                              FROM(SELECT GP.IDOPERPICO AS IDOPER,
                                          SUM(LC.VRLANCAMENT) AS VROPER,
                                          GP.CDSITUTRIBPICO AS CST,
                                          ROUND(SUM(CASE WHEN CDSITUTRIBPICO IN ('01','02','03','05') THEN LC.VRLANCAMENT ELSE 0 END),4) AS BC_PIS,
                                          ROUND(GP.VRALIQPIS,4) AS ALIQ_PIS,
                                          ROUND(SUM(((CASE WHEN GP.CDSITUTRIBPICO IN ('01','02','03','05') THEN LC.VRLANCAMENT ELSE 0 END) * VRALIQPIS)/100),2) AS VR_IMP_PIS,
                                          ROUND(SUM(CASE WHEN GP.CDSITUTRIBPICO IN ('01','02','03','05') THEN LC.VRLANCAMENT ELSE 0 END),4) AS BC_COFINS,
                                          ROUND(GP.VRALIQCOFINS,4) AS ALIQ_COFINS,
                                          ROUND(SUM(((CASE WHEN GP.CDSITUTRIBPICO IN ('01','02','03','05') THEN LC.VRLANCAMENT ELSE 0 END) * VRALIQCOFINS)/100),2) AS VR_IMP_COFINS,
                                          CGP.CDCONTCTBL,
                                          '' AS DSHISTLANC,
                                          REPLACE(REPLACE(REPLACE(REPLACE(E.NRINSJUREMPR,'.',''),'/',''),'-',''),'\','')  AS NRINSJURIEST,
                                          GP.CDNATUREC, GP.IDCUMUPISCOFIL AS FILIAL
                                     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 GP.DTINIVALID <= LC.DTLANCCONT
                                      AND (GP.DTFIMVALID >= LC.DTLANCCONT OR GP.DTFIMVALID IS NULL)
                                      AND LC.CDEMPRESA = P_CDEMPRESA
                                      AND LC.CDEMPRESA = E.CDEMPRESA
                                      AND ((LC.VRLANCAMENT > 0 AND CGP.IDCONSVRDEB = 'N') OR (CGP.IDCONSVRDEB = 'S'))
                                    GROUP BY GP.IDOPERPICO, GP.CDSITUTRIBPICO, ROUND(GP.VRALIQPIS,4), ROUND(GP.VRALIQCOFINS,4), GP.IDCUMUPISCOFIL,
                                          CGP.CDCONTCTBL, REPLACE(REPLACE(REPLACE(REPLACE(E.NRINSJUREMPR,'.',''),'/',''),'-',''),'\',''), GP.CDNATUREC)
                                  WHERE VROPER > 0

                              UNION ALL
                             SELECT '|F500' AS REGISTRO, NVL(VROPERACAO,0) AS VL_REC_CAIXA,
                                    CDCSTPRODPC AS CST_PIS, NVL(VRDESCONTO,0) AS VL_DESC_PIS,
                                    NVL(VRBASECALC,0) AS VL_BC_PIS, NVL(VRPERPIS,0) AS ALIQ_PIS,
                                    NVL(VRPIS,0) AS VL_PIS, CDCSTPRODPC AS CST_COFINS,
                                    NVL(VRDESCONTO,0) AS VL_DESC_COFINS, NVL(VRBASECALC,0) AS VL_BC_COFINS,
                                    NVL(VRPERCOFINS,0) AS ALIQ_COFINS, NVL(VRCOFINS,0) AS VL_COFINS,
                                    '65' AS COD_MOD, CDCFOPPROD AS CFOP, COD_CTA, '' AS INFO_COMPL,
                                    CDNATUREC AS CDNATUREC, CDFILIAL, CDINSCESTA, NRINSJURIEST
                               FROM (SELECT SUM(TRUNC((T.QTPRODVEND*(T.VRUNITVEND+NVL(T.VRUNITVENDCL,0)))+NVL(T.VRDESITVEND,0)+NVL(T.VRACRITVEND,0),2)) AS VROPERACAO,
                                          SUM(NVL(T.VRDESITVEND,0)) AS VRDESCONTO, I.CDCFOPPROD, I.CDCSTPRODPC, I.VRPERCOFINS, I.VRPERPIS,
                                          SUM(TRUNC((T.QTPRODVEND*(T.VRUNITVEND+NVL(T.VRUNITVENDCL,0)))-NVL(T.VRDESITVEND,0)+NVL(T.VRACRITVEND,0),2)) AS VRBASECALC,
                                          SUM(I.VRIMPPIS) AS VRPIS, SUM(I.VRIMPCOFINS) AS VRCOFINS,
                                          F.CDFILIAL, IE.CDINSCESTA, REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.'),'/'),'-'),'\')  AS NRINSJURIEST,
                                          MAX(NVL(I.CDNATUREC,R.CDNATUREC)) AS CDNATUREC, CONTCTBL_SPED('S',T.CDPRODUTO,P.CDTPOPERCNTREC,T.CDFILIAL) AS  COD_CTA
                                     FROM TMP_REGDADOSSPED_PIS TMP, ITEMVENDA T, ITVENDAIMPOS I, FILIAL F, INSCRESTAD IE, RELACPRODITGEN R, PARAVEND P
                                    WHERE TMP.CDREGISTRO = 'NFCE'
                                      AND TMP.CDCAMPO1   = T.CDFILIAL
                                      AND TMP.CDCAMPO2   = T.NRSEQVENDA
                                      AND TMP.CDCAMPO3   = T.CDCAIXA
                                      AND T.CDFILIAL     = P.CDFILIAL
                                      AND T.CDFILIAL     = I.CDFILIAL
                                      AND T.CDCAIXA      = I.CDCAIXA         
                                      AND T.NRSEQUITVEND = I.NRSEQUITVEND
                                      AND T.NRSEQVENDA   = I.NRSEQVENDA
                                      AND (I.VRPERPIS IS NOT NULL OR I.VRPERCOFINS IS NOT NULL)
                                      AND T.CDFILIAL     = F.CDFILIAL
                                      AND F.CDEMPRESA    = IE.CDEMPRESA
                                      AND F.CDINSCESTA   = IE.CDINSCESTA
                                      AND P_CDLIVROMAG   = R.CDLIVROMAG(+)
                                      AND T.CDPRODUTO    = R.CDPRODUTO(+)
                                      AND NOT EXISTS (SELECT I.CDFILIAL, I.NRLANCTONF
                                                       FROM IMPBAIXAREC I, MOVCAIXA M
                                                      WHERE I.CDFILIAL     = M.CDFILIALNF
                                                       AND I.NRLANCTONF   = M.NRLANCTONF
                                                       AND M.CDFILIAL     = TMP.CDCAMPO1
                                                       AND M.NRSEQVENDA   = TMP.CDCAMPO2
                                                       AND M.CDCAIXA      = TMP.CDCAMPO3
                                                       AND I.NRSEQUMAPAPU = V_NRSEQUMAPPIS)
                                    GROUP BY I.CDCFOPPROD, I.CDCSTPRODPC, I.VRPERCOFINS, I.VRPERPIS, F.CDFILIAL, IE.CDINSCESTA,
                                          IE.NRINSJURIEST, CONTCTBL_SPED('S',T.CDPRODUTO,P.CDTPOPERCNTREC,T.CDFILIAL)))
                      GROUP BY REGISTRO, CST_PIS, CST_COFINS, COD_MOD, CFOP, COD_CTA, INFO_COMPL,
                               TRIM(REPLACE(REPLACE(NRINSJURIEST,CHR(10)),CHR(9))),
                               (CASE WHEN SUBSTR(CFOP,1,1) IN ('1','2','3') THEN 'E' ELSE 'S' END)
                      ORDER BY NRINSJURIEST)LOOP

         /*BUSCA O CNPJ DO ESTABELECIMENTO*/
         V_CNPJFILI := C_F500.NRINSJURIEST;

         /*VERIFICA SE O REGISTRO DE IDENTIFICACAO DO ESTABELECIMENTO JA FOI INSERIDO */
         V_CDESTAB := NULL;
         BEGIN
           SELECT MAX(CDESTAB)
             INTO V_CDESTAB
             FROM TMP_REGDADOSSPED_PIS
            WHERE CDREGISTRO = 'F010'
              AND CDCHAVEREG = V_CNPJFILI;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_CDESTAB := NULL;
         END;

         /*IDENTIFICACAO DO ESTABELECIMENTO*/
         IF V_CDESTAB IS NULL THEN
            V_REG_F010 := '|F010|'||V_CNPJFILI||'|';

            /*VERIFICA SE O ESTABELECIMENTO JA FOI INSERIDO*/
            V_CDESTAB := NULL;
            BEGIN
               SELECT MAX(CDESTAB)
                 INTO V_CDESTAB
                 FROM TMP_REGDADOSSPED_PIS
                WHERE CDREGISTRO = '0140'
                  AND CDCHAVEREG = V_CNPJFILI;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_CDESTAB := NULL;
            END;

            /*INSERE O ESTABELECIMENTO*/
            IF V_CDESTAB IS NULL THEN
               V_CONTESTAB:= NVL(V_CONTESTAB,0)+1;
               V_COUNTEST := V_CONTESTAB;
               BEGIN
                  SELECT '|0140'         ||'|'||
                         LTRIM(TO_CHAR(V_COUNTEST,'000')) ||'|'||
                         E.NMRAZSOCEMPR  ||'|'||
                         V_CNPJFILI      ||'|'||
                         I.SGESTADO      ||'|'||
                         ''              ||'|'||
                         M.CDMUNICIBGE   ||'|'||
                         ''              ||'|'||
                         ''              ||'|' AS REG
                    INTO V_REG
                    FROM MUNICIPIO M, EMPRESA E, INSCRESTAD I
                   WHERE I.CDEMPRESA   = P_CDEMPRESA
                     AND I.CDINSCESTA  = C_F500.CDINSCESTA
                     AND I.CDPAIS      = M.CDPAIS
                     AND I.SGESTADO    = M.SGESTADO
                     AND I.CDMUNICIPIO = M.CDMUNICIPIO
                     AND E.CDEMPRESA   = I.CDEMPRESA;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_REG:= NULL;
               END;

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

               V_SEQUPROD:= NVL(V_SEQUPROD,0)+1;
               INSERT INTO TMP_REGDADOSSPED_PIS
                  (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
               VALUES
                  ('0140', V_SEQUPROD, V_CNPJFILI, NULL, LTRIM(TO_CHAR(V_COUNTEST,'000')));
            ELSE
               V_COUNTEST:= TO_NUMBER(V_CDESTAB);
            END IF;

            V_CONTADOR := NVL(V_CONTADOR,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'501'||LTRIM(TO_CHAR(V_COUNTEST,'000')),V_REG_F010);

            V_SEQUPROD:= NVL(V_SEQUPROD,0)+1;
            INSERT INTO TMP_REGDADOSSPED_PIS
               (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
            VALUES
               ('F010', V_SEQUPROD, V_CNPJFILI, NULL, LTRIM(TO_CHAR(V_COUNTEST,'000')));
         ELSE
            V_COUNTEST := TO_NUMBER(V_CDESTAB);
         END IF;

         V_CONTADOR:= NVL(V_CONTADOR,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'501'||LTRIM(TO_CHAR(V_COUNTEST,'000'))||'18'||TO_CHAR(V_CONTADOR),C_F500.REGISTRO);

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

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

         /*PARA O CST 05 NAO E NECESSARIO O DETALHAMENTO*/
         IF (C_F500.CST_PIS <> '05') OR ((C_F500.CST_PIS = '05') AND (P_DTINICIAL < '01/08/2013')) THEN
            /*INSERE O VALOR REFERENTE AO PIS PARA SAIDAS(DEBITO)*/
            V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
            INSERT INTO TMP_REGBLOCOM_PIS
               (CHAVENOTA,CDREGISTRO,CDCONTREGI,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CSTPISCOFINS,CDFILIAL,CDNATUBCALPCIT,VRIMPRETIDO,VRTOTNF,CDNATUREC,CDESTAB,NRINSJUREST,CDCONTCTBL)
            VALUES
               ('F500'||C_F500.CDFILIAL,'M200',V_TMPBLCM,'',C_F500.BC_PIS,C_F500.ALIQ_PIS,'P',C_F500.VR_IMP_PIS,C_F500.CST_PIS,C_F500.CDFILIAL,'',0,C_F500.VROPER,C_F500.CDNATUREC,LTRIM(TO_CHAR(V_COUNTEST,'000')),V_CNPJFILI,C_F500.COD_CTA);
         END IF;
         /*PARA O CST 05 NAO E NECESSARIO O DETALHAMENTO*/
         IF (C_F500.CST_COFINS <> '05') OR ((C_F500.CST_COFINS = '05') AND (P_DTINICIAL < '01/08/2013')) THEN
            /*INSERE O VALOR REFERENTE AO COFINS SAIDAS(DEBITO)*/
            V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
            INSERT INTO TMP_REGBLOCOM_PIS
               (CHAVENOTA,CDREGISTRO,CDCONTREGI,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CSTPISCOFINS,CDFILIAL,CDNATUBCALPCIT,VRIMPRETIDO,VRTOTNF,CDNATUREC,CDESTAB,NRINSJUREST,CDCONTCTBL)
            VALUES
               ('F500'||C_F500.CDFILIAL,'M600',V_TMPBLCM,'',C_F500.BC_COFINS,C_F500.ALIQ_COFINS,'P',C_F500.VR_IMP_COFINS,C_F500.CST_COFINS,C_F500.CDFILIAL,'',0,C_F500.VROPER,C_F500.CDNATUREC,LTRIM(TO_CHAR(V_COUNTEST,'000')),V_CNPJFILI,C_F500.COD_CTA);
         END IF;
      END LOOP;

      /*GERA REGISTRO F525 DETALHAMENTO DAS RECEITAS POR DOCUMENTO*/
      BEGIN
         SELECT SUM(ROUND(NVL(VL_REC,0),2))
           INTO V_VL_REC
           FROM (SELECT NVL(VALOR_IMPOSTO_ITEM_NFE(T.CDFILIAL,T.NRLANCTONF,T.NRSEQITPROD,P_PIS,'C',''),0) AS VL_REC
                   FROM TMP_REGDADOSSPED_PIS TMP, NOTAFISCAL N, ITEMPROIMPOS T, IMPOSTO I
                  WHERE TMP.CDREGISTRO = 'NOTA'
                    AND TMP.CDCAMPO1   = N.CDFILIAL
                    AND TMP.CDCAMPO2   = N.NRLANCTONF
                    AND N.IDENTRSAIDOP = 'S'
                    AND N.CDFILIAL     = T.CDFILIAL
                    AND N.NRLANCTONF   = T.NRLANCTONF
                    AND T.CDIMPOSTO    = I.CDIMPOSTO
                    AND I.CDIMPOSTONFE IN (P_PIS)
                    AND NOT EXISTS (SELECT CDFILIAL, NRLANCTONF
                                      FROM IMPBAIXAREC
                                     WHERE CDFILIAL     = TMP.CDCAMPO1
                                       AND NRLANCTONF   = TMP.CDCAMPO2
                                       AND NRSEQUMAPAPU = V_NRSEQUMAPPIS)
                  UNION ALL
                 SELECT NVL(VALOR_IMPOSTO_ITEM_NFE_CAIXA(N.CDFILIAL,N.NRLANCTONF,P_PIS,'C',T.DTBAIXAREC,'',T.CDFISCOPERNF,T.CDSITUTRIBITNFE,T.NRSEQUENCIAL),0) AS VL_REC
                   FROM TMP_REGDADOSSPED_PIS TMP, NOTAFISCAL N, IMPBAIXAREC T, IMPOSTO I
                  WHERE TMP.CDREGISTRO = 'NOTA'
                    AND TMP.CDCAMPO1   = N.CDFILIAL
                    AND TMP.CDCAMPO2   = N.NRLANCTONF
                    AND N.IDENTRSAIDOP = 'S'
                    AND N.CDFILIAL     = T.CDFILIAL
                    AND N.NRLANCTONF   = T.NRLANCTONF
                    AND T.CDIMPOSTO    = I.CDIMPOSTO
                    AND I.CDIMPOSTONFE IN (P_PIS)
                    AND T.NRSEQUMAPAPU = V_NRSEQUMAPPIS
                  UNION ALL
                 SELECT SUM(VL_REC)
                   FROM (SELECT SUM(LC.VRLANCAMENT) AS VL_REC
                          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 GP.DTINIVALID <= LC.DTLANCCONT
                           AND (GP.DTFIMVALID >= LC.DTLANCCONT OR GP.DTFIMVALID IS NULL)
                           AND LC.CDEMPRESA = P_CDEMPRESA
                           AND LC.CDEMPRESA = E.CDEMPRESA
                           AND ((LC.VRLANCAMENT > 0 AND CGP.IDCONSVRDEB = 'N') OR (CGP.IDCONSVRDEB = 'S')))
                  WHERE VL_REC > 0
                  UNION ALL
                 SELECT SUM(TRUNC((T.QTPRODVEND*(T.VRUNITVEND+NVL(T.VRUNITVENDCL,0)))-NVL(T.VRDESITVEND,0)+NVL(T.VRACRITVEND,0),2)) AS VL_REC
                   FROM TMP_REGDADOSSPED_PIS TMP, ITEMVENDA T
                  WHERE TMP.CDREGISTRO = 'NFCE'
                    AND TMP.CDCAMPO1   = T.CDFILIAL
                    AND TMP.CDCAMPO2   = T.NRSEQVENDA
                    AND TMP.CDCAMPO3   = T.CDCAIXA
                    AND NOT EXISTS (SELECT I.CDFILIAL, I.NRLANCTONF
                                      FROM IMPBAIXAREC I, MOVCAIXA M
                                     WHERE I.CDFILIAL     = M.CDFILIALNF
                                       AND I.NRLANCTONF   = M.NRLANCTONF
                                       AND M.CDFILIAL     = TMP.CDCAMPO1
                                       AND M.NRSEQVENDA   = TMP.CDCAMPO2
                                       AND M.CDCAIXA      = TMP.CDCAMPO3
                                       AND I.NRSEQUMAPAPU = V_NRSEQUMAPPIS));
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_VL_REC := 0;
      END;

      FOR C_F525 IN (SELECT REGISTRO                                                                            ||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(V_VL_REC,2), '999999999990.00')), '.', ',')             ||'|'|| /*VALOR DA RECEITA*/
                            IND_REC                                                                             ||'|'||/*INDICADOR DE COMPOSICAO DA RECEITA*/
                            CPF_CNPJ                                                                            ||'|'||/*CPF_CNPJ DO PARTICIPANTE*/
                            NUM_DOC                                                                             ||'|'||/*NUMERO DO DOCUMENTO*/
                            COD_ITEM                                                                            ||'|'||/*CODIGO DO ITEM*/
                            REPLACE(LTRIM(TO_CHAR(SUM(ROUND(VL_REC_DET,2)), '99999990.00')), '.', ',')          ||'|'||/*VALOR DA RECEITA DETALHADA*/
                            CST_PIS                                                                             ||'|'||/*CST DO PIS*/
                            CST_COFINS                                                                          ||'|'||/*CST DO COFINS*/
                            INFO_COMPL                                                                          ||'|'|| /*INFORMACAO COMPLEMENTAR*/
                            COD_CTA                                                                             ||'|'/*CODIGO DA CONTA ANALITICA*/AS REGISTRO,
                            CDINSCESTA, TRIM(REPLACE(REPLACE(NRINSJURIEST,CHR(10),''),CHR(9),'')) AS NRINSJURIEST, COD_CTA
                       FROM (SELECT '|F525' AS REGISTRO,
                                    '04' AS IND_REC,
                                    '' AS CPF_CNPJ,
                                    N.NRNOTAFISC AS NUM_DOC,
                                    '' AS COD_ITEM,
                                    NVL(VALOR_IMPOSTO_ITEM_NFE(T.CDFILIAL,T.NRLANCTONF,T.NRSEQITPROD,P_PIS,'C',''),0) AS VL_REC_DET,
                                    T.CDSITUTRIBITNFE AS CST_PIS,
                                    T.CDSITUTRIBITNFE AS CST_COFINS,
                                    CONTCTBL_SPED(N.IDENTRSAIDOP,P.CDPRODUTO,N.CDTIPOOPER,DECODE(N.IDENTRSAIDOP,'E',N.CDFILIRECE,N.CDFILIEMIT)) AS COD_CTA,
                                    '' AS INFO_COMPL,
                                    IE.CDINSCESTA,
                                    REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.',''),'/',''),'-',''),'\','')  AS NRINSJURIEST,
                                    N.CDFILIAL, N.NRLANCTONF
                               FROM TMP_REGDADOSSPED_PIS TMP, NOTAFISCAL N, FILIAL F, ITEMPROIMPOS T, ITEMPRODUTO P, IMPOSTO I, INSCRESTAD IE
                              WHERE TMP.CDREGISTRO = 'NOTA'
                                AND TMP.CDCAMPO1   = N.CDFILIAL
                                AND TMP.CDCAMPO2   = N.NRLANCTONF
                                AND IE.CDEMPRESA   = N.CDEMPRESA
                                AND IE.CDINSCESTA  = N.CDINSCESTA
                                AND DECODE(IDENTRSAIDOP,'E',N.CDFILIRECE, N.CDFILIEMIT) = F.CDFILIAL
                                AND IDENTRSAIDOP   = 'S'
                                AND N.CDFILIAL     = T.CDFILIAL
                                AND N.NRLANCTONF   = T.NRLANCTONF
                                AND T.CDIMPOSTO    = I.CDIMPOSTO
                                AND I.CDIMPOSTONFE = P_PIS
                                AND T.CDFILIAL     = P.CDFILIAL
                                AND T.NRLANCTONF   = P.NRLANCTONF
                                AND T.NRSEQITPROD  = P.NRSEQITPROD
                                AND NOT EXISTS (SELECT CDFILIAL, NRLANCTONF
                                                  FROM IMPBAIXAREC
                                                 WHERE CDFILIAL     = TMP.CDCAMPO1
                                                   AND NRLANCTONF   = TMP.CDCAMPO2
                                                   AND NRSEQUMAPAPU = V_NRSEQUMAPPIS)
                              UNION ALL
                             SELECT '|F525' AS REGISTRO,
                                    '04' AS IND_REC,
                                    '' AS CPF_CNPJ,
                                    N.NRNOTAFISC AS NUM_DOC,
                                    '' AS COD_ITEM,
                                    NVL(VALOR_IMPOSTO_ITEM_NFE_CAIXA(N.CDFILIAL,N.NRLANCTONF,P_PIS,'C',T.DTBAIXAREC,'',T.CDFISCOPERNF,T.CDSITUTRIBITNFE,T.NRSEQUENCIAL),0) AS VL_REC_DET,
                                    T.CDSITUTRIBITNFE AS CST_PIS,
                                    T.CDSITUTRIBITNFE AS CST_COFINS,
                                    CONTCTBL_SPED(N.IDENTRSAIDOP,'',N.CDTIPOOPER,DECODE(N.IDENTRSAIDOP,'E',N.CDFILIRECE,N.CDFILIEMIT)) AS  COD_CTA,
                                    '' AS INFO_COMPL,
                                    IE.CDINSCESTA,
                                    REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.',''),'/',''),'-',''),'\','')  AS NRINSJURIEST,
                                    N.CDFILIAL, N.NRLANCTONF
                               FROM TMP_REGDADOSSPED_PIS TMP, NOTAFISCAL N, FILIAL F, IMPBAIXAREC T, IMPOSTO I, INSCRESTAD IE
                              WHERE TMP.CDREGISTRO = 'NOTA'
                                AND TMP.CDCAMPO1   = N.CDFILIAL
                                AND TMP.CDCAMPO2   = N.NRLANCTONF
                                AND IE.CDEMPRESA   = N.CDEMPRESA
                                AND IE.CDINSCESTA  = N.CDINSCESTA
                                AND DECODE(IDENTRSAIDOP,'E',N.CDFILIRECE, N.CDFILIEMIT) = F.CDFILIAL
                                AND IDENTRSAIDOP   = 'S'
                                AND N.CDFILIAL     = T.CDFILIAL
                                AND N.NRLANCTONF   = T.NRLANCTONF
                                AND T.CDIMPOSTO    = I.CDIMPOSTO
                                AND I.CDIMPOSTONFE = P_PIS
                                AND T.NRSEQUMAPAPU = V_NRSEQUMAPPIS
                              UNION ALL
                             SELECT '|F525' AS REGISTRO,
                                     '99' AS IND_REC,
                                    '' AS CPF_CNPJ,
                                    '' AS NUM_DOC,
                                    '' AS COD_ITEM,
                                    VROPER AS VL_REC_DET,
                                    CST AS CST_PIS,
                                    CST AS CST_COFINS,
                                    CDCONTCTBL AS  COD_CTA,
                                    'Outras Receitas' AS INFO_COMPL,
                                    '' AS CDINSCESTA,
                                    NRINSJURIEST,
                                    FILIAL  AS CDFILIAL,
                                    '' AS NRLANCTONF
                              FROM(SELECT GP.IDOPERPICO AS IDOPER,
                                          SUM(LC.VRLANCAMENT) AS VROPER,
                                          GP.CDSITUTRIBPICO AS CST,
                                          ROUND(SUM(CASE WHEN CDSITUTRIBPICO IN ('01','02','03','05') THEN LC.VRLANCAMENT ELSE 0 END),4) AS BC_PIS,
                                          ROUND(GP.VRALIQPIS,4) AS ALIQ_PIS,
                                          ROUND(SUM(((CASE WHEN GP.CDSITUTRIBPICO IN ('01','02','03','05') THEN LC.VRLANCAMENT ELSE 0 END) * VRALIQPIS)/100),2) AS VR_IMP_PIS,
                                          ROUND(SUM(CASE WHEN GP.CDSITUTRIBPICO IN ('01','02','03','05') THEN LC.VRLANCAMENT ELSE 0 END),4) AS BC_COFINS,
                                          ROUND(GP.VRALIQCOFINS,4) AS ALIQ_COFINS,
                                          ROUND(SUM(((CASE WHEN GP.CDSITUTRIBPICO IN ('01','02','03','05') THEN LC.VRLANCAMENT ELSE 0 END) * VRALIQCOFINS)/100),2) AS VR_IMP_COFINS,
                                          CGP.CDCONTCTBL,
                                          '' AS DSHISTLANC,
                                          REPLACE(REPLACE(REPLACE(REPLACE(E.NRINSJUREMPR,'.',''),'/',''),'-',''),'\','')  AS NRINSJURIEST,
                                          GP.CDNATUREC, GP.IDCUMUPISCOFIL AS FILIAL
                                     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 GP.DTINIVALID <= LC.DTLANCCONT
                                      AND (GP.DTFIMVALID >= LC.DTLANCCONT OR GP.DTFIMVALID IS NULL)
                                      AND LC.CDEMPRESA = P_CDEMPRESA
                                      AND LC.CDEMPRESA = E.CDEMPRESA
                                      AND ((LC.VRLANCAMENT > 0 AND CGP.IDCONSVRDEB = 'N') OR (CGP.IDCONSVRDEB = 'S'))
                                    GROUP BY GP.IDOPERPICO, GP.CDSITUTRIBPICO, ROUND(GP.VRALIQPIS,4), ROUND(GP.VRALIQCOFINS,4), GP.IDCUMUPISCOFIL,
                                             CGP.CDCONTCTBL, REPLACE(REPLACE(REPLACE(REPLACE(E.NRINSJUREMPR,'.',''),'/',''),'-',''),'\',''), GP.CDNATUREC)
                                  WHERE VROPER > 0

                              UNION ALL
                             SELECT '|F525' AS REGISTRO,
                                    '04' AS IND_REC,
                                    '' AS CPF_CNPJ,
                                    V.NRNOTAFISCALCE AS NUM_DOC,
                                    '' AS COD_ITEM,
                                    SUM(TRUNC((T.QTPRODVEND*(T.VRUNITVEND+NVL(T.VRUNITVENDCL,0)))-NVL(T.VRDESITVEND,0)+NVL(T.VRACRITVEND,0),2)) AS VL_REC_DET,
                                    I.CDCSTPRODPC AS CST_PIS,
                                    I.CDCSTPRODPC AS CST_COFINS,
                                    CONTCTBL_SPED('S',T.CDPRODUTO,P.CDTPOPERCNTREC,T.CDFILIAL) AS  COD_CTA,
                                    '' AS INFO_COMPL,
                                    IE.CDINSCESTA,
                                    REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.',''),'/',''),'-',''),'\','')  AS NRINSJURIEST,
                                    V.CDFILIAL AS CDFILIAL, V.NRSEQVENDA AS NRLANCTONF
                               FROM TMP_REGDADOSSPED_PIS TMP, VENDA V, ITEMVENDA T, ITVENDAIMPOS I, FILIAL F, INSCRESTAD IE, PARAVEND P
                              WHERE TMP.CDREGISTRO = 'NFCE'
                                AND TMP.CDCAMPO1   = V.CDFILIAL
                                AND TMP.CDCAMPO2   = V.NRSEQVENDA
                                AND TMP.CDCAMPO3   = V.CDCAIXA
                                AND V.CDFILIAL     = P.CDFILIAL
                                AND V.CDFILIAL     = T.CDFILIAL
                                AND V.NRSEQVENDA   = T.NRSEQVENDA
                                AND V.CDCAIXA      = T.CDCAIXA
                                AND T.CDFILIAL     = I.CDFILIAL
                                AND T.NRSEQVENDA   = I.NRSEQVENDA
                                AND T.CDCAIXA      = I.CDCAIXA
                                AND T.NRSEQUITVEND = I.NRSEQUITVEND
                                AND V.CDFILIAL     = F.CDFILIAL
                                AND F.CDEMPRESA    = IE.CDEMPRESA
                                AND F.CDINSCESTA   = IE.CDINSCESTA
                                AND NOT EXISTS (SELECT I.CDFILIAL, I.NRLANCTONF
                                                  FROM IMPBAIXAREC I, MOVCAIXA M
                                                 WHERE I.CDFILIAL     = M.CDFILIALNF
                                                   AND I.NRLANCTONF   = M.NRLANCTONF
                                                   AND M.CDFILIAL     = TMP.CDCAMPO1 
												               AND M.NRSEQVENDA   = TMP.CDCAMPO2
												               AND M.CDCAIXA      = TMP.CDCAMPO3
												               AND I.NRSEQUMAPAPU = V_NRSEQUMAPPIS)
                              GROUP BY V.NRNOTAFISCALCE, I.CDCSTPRODPC, V.CDFILIAL, V.NRSEQVENDA, IE.CDINSCESTA,
                                    REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.',''),'/',''),'-',''),'\',''),
                                    CONTCTBL_SPED('S',T.CDPRODUTO,P.CDTPOPERCNTREC,T.CDFILIAL))
                      GROUP BY REGISTRO, CST_PIS, CST_COFINS, COD_CTA, INFO_COMPL, COD_ITEM, IND_REC, CPF_CNPJ, NUM_DOC,
                               CDINSCESTA, TRIM(REPLACE(REPLACE(NRINSJURIEST,CHR(10),''),CHR(9),'')))LOOP

         /*BUSCA O CNPJ DO ESTABELECIMENTO*/
         V_CNPJFILI := C_F525.NRINSJURIEST;

         /*VERIFICA SE O REGISTRO DE IDENTIFICACAO DO ESTABELECIMENTO JA FOI INSERIDO */
         V_CDESTAB := NULL;
         BEGIN
            SELECT MAX(CDESTAB)
              INTO V_CDESTAB
              FROM TMP_REGDADOSSPED_PIS
             WHERE CDREGISTRO = 'F010'
               AND CDCHAVEREG = V_CNPJFILI;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_CDESTAB := NULL;
         END;

         /*IDENTIFICACAO DO ESTABELECIMENTO*/
         IF V_CDESTAB IS NULL THEN
            V_REG_F010 := '|F010|'||V_CNPJFILI||'|';

            /*VERIFICA SE O ESTABELECIMENTO JA FOI INSERIDO*/
            V_CDESTAB := NULL;
            BEGIN
               SELECT MAX(CDESTAB)
                 INTO V_CDESTAB
                 FROM TMP_REGDADOSSPED_PIS
                WHERE CDREGISTRO = '0140'
                  AND CDCHAVEREG = V_CNPJFILI;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_CDESTAB := NULL;
            END;

            /*INSERE O ESTABELECIMENTO*/
            IF V_CDESTAB IS NULL THEN
               V_CONTESTAB:= NVL(V_CONTESTAB,0)+1;
               V_COUNTEST := V_CONTESTAB;
               BEGIN
                  SELECT '|0140'         ||'|'||
                         LTRIM(TO_CHAR(V_COUNTEST,'000')) ||'|'||
                         E.NMRAZSOCEMPR  ||'|'||
                         V_CNPJFILI      ||'|'||
                         I.SGESTADO      ||'|'||
                         ''              ||'|'||
                         M.CDMUNICIBGE   ||'|'||
                         ''              ||'|'||
                         ''              ||'|' AS REG
                    INTO V_REG
                    FROM MUNICIPIO M, EMPRESA E, INSCRESTAD I
                   WHERE I.CDEMPRESA   = P_CDEMPRESA
                     AND I.CDINSCESTA  = C_F525.CDINSCESTA
                     AND I.CDPAIS      = M.CDPAIS
                     AND I.SGESTADO    = M.SGESTADO
                     AND I.CDMUNICIPIO = M.CDMUNICIPIO
                     AND E.CDEMPRESA   = I.CDEMPRESA;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_REG:= NULL;
               END;

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

               V_SEQUPROD:= NVL(V_SEQUPROD,0)+1;
               INSERT INTO TMP_REGDADOSSPED_PIS
                  (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
               VALUES
                  ('0140', V_SEQUPROD, V_CNPJFILI, NULL, LTRIM(TO_CHAR(V_COUNTEST,'000')));
            ELSE
               V_COUNTEST:= TO_NUMBER(V_CDESTAB);
            END IF;

            V_CONTADOR := NVL(V_CONTADOR,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'501'||LTRIM(TO_CHAR(V_COUNTEST,'000')),V_REG_F010);

            V_SEQUPROD:= NVL(V_SEQUPROD,0)+1;
            INSERT INTO TMP_REGDADOSSPED_PIS
               (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
            VALUES
               ('F010', V_SEQUPROD, V_CNPJFILI, NULL, LTRIM(TO_CHAR(V_COUNTEST,'000')));
         ELSE
            V_COUNTEST:= TO_NUMBER(V_CDESTAB);
         END IF;

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

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

         /*INSERE REGISTRO F525*/
         V_CONTADOR:= NVL(V_CONTADOR,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'501'||LTRIM(TO_CHAR(V_COUNTEST,'000'))||'19'||TO_CHAR(V_CONTADOR),C_F525.REGISTRO);
      END LOOP;

   /* F550 */
   /*SIM: (se no registro 0110 o Campo "COD_INC_TRIB" = "2" e o Campo "IND_REG_CUM" = "2")*/
   ELSIF ((P_CDINCTRIB = '2') AND (P_CRTESCRITUR = '2')) THEN
      FOR C_F550 IN(SELECT REGISTRO                                                                            ||'|'||
                           REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VRRECEITA),2), '999999999990.00')), '.', ',')       ||'|'||
                           CSTPIS                                                                              ||'|'||
                           REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VRDESCPIS),2), '999999999990.00')), '.', ',')       ||'|'||
                           REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VRBASECALCPIS),2), '99999999990.00')), '.', ',')    ||'|'||
                           REPLACE(LTRIM(TO_CHAR(ROUND(MAX(VRALIQPIS),4), '99999990.0000')), '.', ',')         ||'|'||
                           REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VRIMPPIS),2), '99999990.00')), '.', ',')            ||'|'||
                           CSTCOFINS                                                                           ||'|'||
                           REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VRDESCCOFINS),2), '999999999990.00')), '.', ',')    ||'|'||
                           REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VRBASECALCCOFINS),2), '999999999990.00')), '.', ',')||'|'||
                           REPLACE(LTRIM(TO_CHAR(ROUND(MAX(VRALIQCOFINS),4), '99999990.0000')), '.', ',')      ||'|'||
                           REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VRIMPCOFINS),2), '99999990.00')), '.', ',')         ||'|'||
                           CDMODELO                                                                            ||'|'||
                           CFOP                                                                                ||'|'||
                           COD_CTA                                                                             ||'|'||
                           INFO_COMPL                                                                          ||'|' AS REGISTRO,
                           TRIM(REPLACE(REPLACE(MAX(NRINSJURIEST), CHR(10), ''), CHR(9), '')) AS NRINSJURIEST,
                           MAX(CDINSCESTA) AS CDINSCESTA, MAX(CDFILIAL) AS CDFILIAL, CSTPIS, CSTCOFINS, COD_CTA, MAX(CDNATUREC) AS CDNATUREC, SUM(VRRECEITA) AS VROPER,
                           SUM(VRBASECALCPIS) AS BC_PIS, MAX(VRALIQPIS) AS ALIQ_PIS, SUM(VRIMPPIS) AS VR_IMP_PIS,
                           SUM(VRBASECALCPIS) AS BC_COFINS, MAX(VRALIQCOFINS) AS ALIQ_COFINS, SUM(VRIMPCOFINS) AS VR_IMP_COFINS, CFOP,
                           MAX(TPCREDPIS) AS TPCREDPIS, MAX(TPCREDCOFINS) AS TPCREDCOFINS,
                           (CASE WHEN SUBSTR(CFOP,1,1) IN ('1','2','3') THEN 'E' ELSE 'S' END) AS IDENTRSAIDOP
                      FROM (SELECT '|F550' AS REGISTRO, /*OUTRAS RECEITAS*/
                                   SUM(ABS(LC.VRLANCAMENT)) AS VRRECEITA,
                                   GP.CDSITUTRIBPICO AS CSTPIS,
                                   '' AS TPCREDPIS,
                                   '' AS CDNATBCPIS,
                                   0 AS VRDESCPIS,
                                   ROUND(SUM(CASE WHEN CDSITUTRIBPICO IN ('01','02','03','05') THEN LC.VRLANCAMENT ELSE 0 END),4) AS VRBASECALCPIS,
                                   ROUND(GP.VRALIQPIS,4) AS VRALIQPIS,
                                   ROUND(SUM(((CASE WHEN GP.CDSITUTRIBPICO IN ('01','02','03','05') THEN LC.VRLANCAMENT ELSE 0 END) * VRALIQPIS)/100),2) AS VRIMPPIS,
                                   GP.CDSITUTRIBPICO AS CSTCOFINS,
                                   '' AS TPCREDCOFINS,
                                   '' AS CDNATBCCOFINS,
                                   0 AS VRDESCCOFINS,
                                   ROUND(SUM(CASE WHEN GP.CDSITUTRIBPICO IN ('01','02','03','05') THEN ABS(LC.VRLANCAMENT) ELSE 0 END),4) AS VRBASECALCCOFINS,
                                   ROUND(ABS(GP.VRALIQCOFINS),4) AS VRALIQCOFINS,
                                   ROUND(SUM(((CASE WHEN GP.CDSITUTRIBPICO IN ('01','02','03','05') THEN ABS(LC.VRLANCAMENT) ELSE 0 END) * VRALIQCOFINS)/100),2) AS VRIMPCOFINS,
                                   '' AS CDMODELO, '' AS CFOP, CGP.CDCONTCTBL AS COD_CTA, '' AS INFO_COMPL, GP.CDNATUREC,
                                   GP.IDCUMUPISCOFIL AS CDFILIAL, REPLACE(REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'/',''),'-',''),'\','')  AS NRINSJURIEST, '' AS CDINSCESTA
                              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 GP.DTINIVALID   <= LC.DTLANCCONT
                               AND (GP.DTFIMVALID  >= LC.DTLANCCONT OR GP.DTFIMVALID IS NULL)
                               AND LC.CDEMPRESA    = P_CDEMPRESA
                               AND LC.CDEMPRESA    = E.CDEMPRESA
                               AND ((LC.VRLANCAMENT > 0 AND CGP.IDCONSVRDEB = 'N') OR (CGP.IDCONSVRDEB = 'S'))
                             GROUP BY GP.CDSITUTRIBPICO, ROUND(GP.VRALIQPIS,4), ROUND(ABS(GP.VRALIQCOFINS),4), GP.IDCUMUPISCOFIL,
                                      CGP.CDCONTCTBL, GP.CDNATUREC, REPLACE(REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'/',''),'-',''),'\','')
                             UNION ALL/*NOTAS FISCAIS*/
                            SELECT '|F550' AS REGISTRO,
                                   NVL(VALOR_IMPOSTO_ITEM_NFE(P.CDFILIAL,P.NRLANCTONF,P.NRSEQITPROD,P_PIS,'C',''),0) AS VRRECEITA,
                                   T.CDSITUTRIBITNFE AS CSTPIS,
                                   T.CDTPCREDPICOITE AS TPCREDPIS,
                                   T.CDNATUBCALPCIT  AS CDNATBCPIS,
                                   0 AS VRDESCPIS,
                                   NVL(VALOR_IMPOSTO_ITEM_NFE(P.CDFILIAL,P.NRLANCTONF,P.NRSEQITPROD,P_PIS,'B','1'),0) AS VRBASECALCPIS,
                                   NVL(VALOR_IMPOSTO_ITEM_NFE(P.CDFILIAL,P.NRLANCTONF,P.NRSEQITPROD,P_PIS,'A','1'),0) AS VRALIQPIS,
                                   NVL(VALOR_IMPOSTO_ITEM_NFE(P.CDFILIAL,P.NRLANCTONF,P.NRSEQITPROD,P_PIS,'I','1'),0) AS VRIMPPIS,
                                   T.CDSITUTRIBITNFE AS CSTCOFINS,
                                   T.CDTPCREDPICOITE AS TPCREDCOFINS,
                                   T.CDNATUBCALPCIT  AS CDNATBCCOFINS,
                                   0 AS VRDESCCOFINS,
                                   NVL(VALOR_IMPOSTO_ITEM_NFE(P.CDFILIAL,P.NRLANCTONF,P.NRSEQITPROD,P_COFINS,'B','1'),0) AS VRBASECALCCOFINS,
                                   NVL(VALOR_IMPOSTO_ITEM_NFE(P.CDFILIAL,P.NRLANCTONF,P.NRSEQITPROD,P_COFINS,'A','1'),0) AS VRALIQCOFINS,
                                   NVL(VALOR_IMPOSTO_ITEM_NFE(P.CDFILIAL,P.NRLANCTONF,P.NRSEQITPROD,P_COFINS,'I','1'),0) AS VRIMPCOFINS,
                                   DECODE(SUBSTR(CDESTAB,LENGTH(TMP.CDESTAB)-2,3),'ISS','98',N.CDMODDOCFISC) AS CDMODELO, T.CDFISCOPERIT AS CFOP,
                                   CONTCTBL_SPED(N.IDENTRSAIDOP,P.CDPRODUTO,N.CDTIPOOPER,DECODE(N.IDENTRSAIDOP,'E',N.CDFILIRECE,N.CDFILIEMIT)) AS COD_CTA,
                                   '' AS INFO_COMPL, NVL(T.CDNATUREC,R.CDNATUREC) AS CDNATUREC, F.CDFILIAL,
                                   REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.',''),'/',''),'-',''),'\','')  AS NRINSJURIEST, IE.CDINSCESTA
                              FROM TMP_REGDADOSSPED_PIS TMP, NOTAFISCAL N, FILIAL F, ITEMPROIMPOS T, IMPOSTO I,
                                   ITEMPRODUTO P, RELACPRODITGEN R, INSCRESTAD IE
                             WHERE TMP.CDREGISTRO = 'NOTA'
                               AND TMP.CDCAMPO1   = N.CDFILIAL
                               AND TMP.CDCAMPO2   = N.NRLANCTONF
                               AND N.CDFILIEMIT   = F.CDFILIAL
                               AND N.CDEMPRESA    = IE.CDEMPRESA
                               AND N.CDINSCESTA   = IE.CDINSCESTA
                               AND N.CDFILIAL     = T.CDFILIAL
                               AND N.NRLANCTONF   = T.NRLANCTONF
                               AND T.CDIMPOSTO    = I.CDIMPOSTO
                               AND N.IDENTRSAIDOP = 'S'
                               AND I.CDIMPOSTONFE = P_PIS
                               AND T.CDFILIAL     = P.CDFILIAL
                               AND T.NRLANCTONF   = P.NRLANCTONF
                               AND T.NRSEQITPROD  = P.NRSEQITPROD
                               AND P.CDPRODUTO    = R.CDPRODUTO(+)
                               AND R.CDLIVROMAG(+)= P_CDLIVROMAG
                             UNION ALL /*REDUCAOZ*/
                            SELECT REGISTRO, SUM(VRRECEITA) AS VRRECEITA,
                                   CSTPIS, TPCREDPIS,
                                   CDNATBCPIS, SUM(VRDESCPIS) AS VRDESCPIS,
                                   SUM(VRBASECALCPIS) AS VRBASECALCPIS, MAX(VRALIQPIS) AS VRALIQPIS,
                                   SUM(VRIMPPIS) AS VRIMPPIS, CSTCOFINS,
                                   TPCREDCOFINS, CDNATBCCOFINS,
                                   SUM(VRDESCCOFINS) AS VRDESCCOFINS, SUM(VRBASECALCCOFINS) AS VRBASECALCCOFINS,
                                   MAX(VRALIQCOFINS) AS VRALIQCOFINS, SUM(VRIMPCOFINS) AS  VRIMPCOFINS,
                                   CDMODELO, CFOP, COD_CTA, INFO_COMPL,
                                   CDNATUREC, CDFILIAL, NRINSJURIEST, CDINSCESTA
                              FROM(SELECT '|F550' AS REGISTRO,
                                          DECODE(I.CDIMPOSTONFE, P_PIS, IT.VRPRODUTO, 0) AS VRRECEITA,
                                          IT.CDSITUTRIBNFE AS CSTPIS,
                                          '' AS TPCREDPIS,
                                          '' AS CDNATBCPIS,
                                          0 AS VRDESCPIS,
                                          DECODE(IT.CDFISCALIT,'1',DECODE(I.CDIMPOSTONFE, P_PIS, IT.VRPRODUTO, 0),0) AS VRBASECALCPIS,
                                          DECODE(I.CDIMPOSTONFE, P_PIS, DECODE(IT.CDFISCALIT,'1',IT.VRALIQIT,0),0) AS VRALIQPIS,
                                          DECODE(I.CDIMPOSTONFE, P_PIS,(IT.VRPRODUTO*(DECODE(IT.CDFISCALIT,'1',IT.VRALIQIT,0))/100),0) AS VRIMPPIS,
                                          IT.CDSITUTRIBNFE AS CSTCOFINS,
                                          '' AS TPCREDCOFINS,
                                          '' AS CDNATBCCOFINS,
                                          0 AS VRDESCCOFINS,
                                          DECODE(IT.CDFISCALIT,'1',DECODE(I.CDIMPOSTONFE, P_COFINS, IT.VRPRODUTO, 0),0) AS VRBASECALCCOFINS,
                                          DECODE(I.CDIMPOSTONFE, P_COFINS, DECODE(IT.CDFISCALIT,'1',IT.VRALIQIT,0),0) AS VRALIQCOFINS,
                                          DECODE(I.CDIMPOSTONFE, P_COFINS,(IT.VRPRODUTO*(DECODE(IT.CDFISCALIT,'1',IT.VRALIQIT,0))/100),0) AS VRIMPCOFINS,
                                          '' AS CDMODELO, '' AS CFOP,
                                          CONTCTBL_SPED('S',IT.CDPRODUTO,PV.CDTPOPERCNTREC,F.CDFILIAL) AS COD_CTA, '' AS INFO_COMPL,
                                          NVL(IT.CDNATUREC,RE.CDNATUREC) AS CDNATUREC, F.CDFILIAL, IE.NRINSJURIEST, IE.CDINSCESTA
                                     FROM ITEMVENOUTIMPOS IT, REDUCAOZ RZ, FILIAL F, PRODUTO P, IMPOSTO I, RELACPRODITGEN RE, INSCRESTAD IE, PARAVEND PV
                                    WHERE IT.CDFILIAL   = RZ.CDFILIAL
                                      AND IT.CDCAIXA    = RZ.CDCAIXA
                                      AND IT.DTREDUCAOZ = RZ.DTREDUCAOZ
                                      AND RZ.CDFILIAL   = PV.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 IN (P_PIS, P_COFINS)
                                      AND IT.DTREDUCAOZ BETWEEN P_DTINICIAL AND P_DTFINAL
                                      AND P.CDPRODUTO   = IT.CDPRODUTO
                                      AND RE.CDLIVROMAG(+) = P_CDLIVROMAG
                                      AND RE.CDPRODUTO(+)  = P.CDPRODUTO)
                             GROUP BY REGISTRO, CSTPIS, TPCREDPIS, CDNATBCPIS, CSTCOFINS, TPCREDCOFINS, CDNATBCCOFINS, CDMODELO, CFOP, COD_CTA, INFO_COMPL, CDNATUREC, CDFILIAL, NRINSJURIEST, CDINSCESTA
                             UNION ALL/*NFCE*/
                            SELECT '|F550' AS REGISTRO, NVL(VROPERACAO,0) AS VRRECEITA,
                                   CDCSTPRODPC AS CSTPIS, '' AS TPCREDPIS,
                                   '' AS CDNATBCPIS, NVL(VRDESCONTO,0) AS VRDESCPIS,
                                   NVL(VRBASECALC,0) AS VRBASECALCPIS, NVL(VRPERPIS,0) AS VRALIQPIS,
                                   NVL(VRPIS,0) AS VRIMPPIS, CDCSTPRODPC AS CSTCOFINS,
                                   '' AS TPCREDCOFINS, '' AS CDNATBCCOFINS,
                                   NVL(VRDESCONTO,0) AS VRDESCPIS, NVL(VRBASECALC,0) AS VRBASECALCCOFINS,
                                   NVL(VRPERCOFINS,0) AS VRALIQCOFINS, NVL(VRCOFINS,0) AS VRIMPCOFINS,
                                   '65' AS CDMODELO, CDCFOPPROD AS CFOP, COD_CTA, '' AS INFO_COMPL,
                                   CDNATUREC, CDFILIAL, NRINSJURIEST, CDINSCESTA
                              FROM (SELECT SUM(TRUNC((T.QTPRODVEND*(T.VRUNITVEND+NVL(T.VRUNITVENDCL,0)))+NVL(T.VRDESITVEND,0)+NVL(T.VRACRITVEND,0),2)) AS VROPERACAO,
                                           SUM(NVL(T.VRDESITVEND,0)) AS VRDESCONTO, I.CDCFOPPROD, I.CDCSTPRODPC, I.VRPERCOFINS, I.VRPERPIS,
                                           SUM(TRUNC((T.QTPRODVEND*(T.VRUNITVEND+NVL(T.VRUNITVENDCL,0)))-NVL(T.VRDESITVEND,0)+NVL(T.VRACRITVEND,0),2)) AS VRBASECALC,
                                           SUM(I.VRIMPPIS) AS VRPIS, SUM(I.VRIMPCOFINS) AS VRCOFINS,
                                           F.CDFILIAL, REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.',''),'/',''),'-',''),'\','') AS NRINSJURIEST, IE.CDINSCESTA,
                                           NVL(I.CDNATUREC,R.CDNATUREC) AS CDNATUREC, CONTCTBL_SPED('S',T.CDPRODUTO,PV.CDTPOPERCNTREC,F.CDFILIAL) AS COD_CTA
                                      FROM TMP_REGDADOSSPED_PIS TMP, ITEMVENDA T, ITVENDAIMPOS I, FILIAL F, INSCRESTAD IE, RELACPRODITGEN R, PARAVEND PV
                                     WHERE TMP.CDREGISTRO  = 'NFCE'
                                       AND TMP.CDCAMPO1    = T.CDFILIAL
                                       AND TMP.CDCAMPO2    = T.NRSEQVENDA
                                       AND TMP.CDCAMPO3    = T.CDCAIXA
                                       AND T.CDFILIAL      = PV.CDFILIAL
                                       AND T.CDFILIAL      = I.CDFILIAL
                                       AND T.CDCAIXA       = I.CDCAIXA
                                       AND T.NRSEQUITVEND  = I.NRSEQUITVEND
                                       AND T.NRSEQVENDA    = I.NRSEQVENDA
                                       AND (I.VRPERPIS IS NOT NULL OR I.VRPERCOFINS IS NOT NULL)
                                       AND T.CDFILIAL      = F.CDFILIAL
                                       AND F.CDEMPRESA     = IE.CDEMPRESA
                                       AND F.CDINSCESTA    = IE.CDINSCESTA
                                       AND R.CDLIVROMAG(+) = P_CDLIVROMAG
                                       AND R.CDPRODUTO(+)  = T.CDPRODUTO
                                     GROUP BY I.CDCFOPPROD, I.CDCSTPRODPC, I.VRPERCOFINS, I.VRPERPIS, F.CDFILIAL, IE.NRINSJURIEST,
                                           IE.CDINSCESTA, NVL(I.CDNATUREC,R.CDNATUREC), CONTCTBL_SPED('S',T.CDPRODUTO,PV.CDTPOPERCNTREC,F.CDFILIAL))
                             UNION ALL /*SAT*/
                            SELECT '|F550' AS REGISTRO, NVL(VROPERACAO,0) AS VRRECEITA,
                                   CDCSTPRODPC AS CSTPIS, '' AS TPCREDPIS,
                                   '' AS CDNATBCPIS, NVL(VRDESCONTO,0) AS VRDESCPIS,
                                   NVL(VRBASECALC,0) AS VRBASECALCPIS, NVL(VRPERPIS,0) AS VRALIQPIS,
                                   NVL(VRPIS,0) AS VRIMPPIS, CDCSTPRODPC AS CSTCOFINS,
                                   '' AS TPCREDCOFINS, '' AS CDNATBCCOFINS,
                                   NVL(VRDESCONTO,0) AS VRDESCPIS, NVL(VRBASECALC,0) AS VRBASECALCCOFINS,
                                   NVL(VRPERCOFINS,0) AS VRALIQCOFINS, NVL(VRCOFINS,0) AS VRIMPCOFINS,
                                   '59' AS CDMODELO, CDCFOPPROD AS CFOP, COD_CTA, '' AS INFO_COMPL,
                                   CDNATUREC, CDFILIAL, NRINSJURIEST, CDINSCESTA
                              FROM (SELECT SUM(ROUND((T.QTPRODVEND*(T.VRUNITVEND+NVL(T.VRUNITVENDCL,0)))+NVL(T.VRDESITVEND,0)+NVL(T.VRACRITVEND,0),2)) AS VROPERACAO,
                                           SUM(NVL(T.VRDESITVEND,0)) AS VRDESCONTO, I.CDCFOPPROD, I.CDCSTPRODPC, I.VRPERCOFINS, I.VRPERPIS,
                                           SUM(ROUND((T.QTPRODVEND*(T.VRUNITVEND+NVL(T.VRUNITVENDCL,0)))-NVL(T.VRDESITVEND,0)+NVL(T.VRACRITVEND,0),2)) AS VRBASECALC,
                                           SUM(I.VRIMPPIS) AS VRPIS, SUM(I.VRIMPCOFINS) AS VRCOFINS,
                                           F.CDFILIAL, REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.',''),'/',''),'-',''),'\','') AS NRINSJURIEST,
                                           IE.CDINSCESTA, NVL(I.CDNATUREC,R.CDNATUREC) AS CDNATUREC,
                                           CONTCTBL_SPED('S',T.CDPRODUTO,PV.CDTPOPERCNTREC,F.CDFILIAL) AS COD_CTA
                                      FROM TMP_REGDADOSSPED_PIS TMP, ITEMVENDA T, ITVENDAIMPOS I, FILIAL F, INSCRESTAD IE, RELACPRODITGEN R, PARAVEND PV
                                     WHERE TMP.CDREGISTRO  = 'SAT'
                                       AND TMP.CDCAMPO1    = T.CDFILIAL
                                       AND TMP.CDCAMPO2    = T.NRSEQVENDA
                                       AND TMP.CDCAMPO3    = T.CDCAIXA
                                       AND T.CDFILIAL      = PV.CDFILIAL
                                       AND T.CDFILIAL      = I.CDFILIAL
                                       AND T.CDCAIXA       = I.CDCAIXA
                                       AND T.NRSEQUITVEND  = I.NRSEQUITVEND
                                       AND T.NRSEQVENDA    = I.NRSEQVENDA
                                       AND (I.VRPERPIS IS NOT NULL OR I.VRPERCOFINS IS NOT NULL)
                                       AND T.CDFILIAL      = F.CDFILIAL
                                       AND F.CDEMPRESA     = IE.CDEMPRESA
                                       AND F.CDINSCESTA    = IE.CDINSCESTA
                                       AND R.CDLIVROMAG(+) = P_CDLIVROMAG
                                       AND R.CDPRODUTO(+)  = T.CDPRODUTO
                                     GROUP BY I.CDCFOPPROD, I.CDCSTPRODPC, I.VRPERCOFINS, I.VRPERPIS, F.CDFILIAL, IE.NRINSJURIEST,
                                           IE.CDINSCESTA, NVL(I.CDNATUREC,R.CDNATUREC), CONTCTBL_SPED('S',T.CDPRODUTO,PV.CDTPOPERCNTREC,F.CDFILIAL)))
                     GROUP BY REGISTRO, CSTPIS, CSTCOFINS, CDMODELO, CFOP, COD_CTA, INFO_COMPL,
                              (CASE WHEN SUBSTR(CFOP,1,1) IN ('1','2','3') THEN 'E' ELSE 'S' END), NRINSJURIEST) LOOP

         /*BUSCA O CNPJ DO ESTABELECIMENTO*/
         V_CNPJFILI:= C_F550.NRINSJURIEST;

         /*VERIFICA SE O REGISTRO DE IDENTIFICACAO DO ESTABELECIMENTO JA FOI INSERIDO */
         V_CDESTAB:= NULL;
         BEGIN
            SELECT MAX(CDESTAB)
              INTO V_CDESTAB
              FROM TMP_REGDADOSSPED_PIS
             WHERE CDREGISTRO = 'F010'
               AND CDCHAVEREG = V_CNPJFILI;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_CDESTAB := NULL;
         END;

         /*IDENTIFICACAO DO ESTABELECIMENTO*/
         IF V_CDESTAB IS NULL THEN
            V_REG_F010 := '|F010|'||V_CNPJFILI||'|';

            /*VERIFICA SE O ESTABELECIMENTO JA FOI INSERIDO*/
            V_CDESTAB := NULL;
            BEGIN
               SELECT MAX(CDESTAB)
                 INTO V_CDESTAB
                 FROM TMP_REGDADOSSPED_PIS
                WHERE CDREGISTRO = '0140'
                  AND CDCHAVEREG = V_CNPJFILI;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_CDESTAB := NULL;
            END;

            /*INSERE O ESTABELECIMENTO*/
            IF V_CDESTAB IS NULL THEN
               V_CONTESTAB:= NVL(V_CONTESTAB,0)+1;
               V_COUNTEST := V_CONTESTAB;
               BEGIN
                  SELECT '|0140'         ||'|'||
                         LTRIM(TO_CHAR(V_COUNTEST,'000')) ||'|'||
                         E.NMRAZSOCEMPR                   ||'|'||
                         V_CNPJFILI                       ||'|'||
                         NVL(I.SGESTADO,E.SGESTADO)       ||'|'||
                         ''                               ||'|'||
                         NVL(I.CDMUNICIBGE,M.CDMUNICIBGE) ||'|'||
                         ''                               ||'|'||
                         ''                               ||'|' AS REG
                    INTO V_REG
                    FROM MUNICIPIO M, EMPRESA E, (SELECT IE.SGESTADO, MU.CDMUNICIBGE, IE.CDEMPRESA
                                                    FROM INSCRESTAD IE, MUNICIPIO MU
                                                   WHERE IE.CDEMPRESA   = P_CDEMPRESA
                                                     AND IE.CDINSCESTA  = C_F550.CDINSCESTA
                                                     AND IE.CDPAIS      = MU.CDPAIS
                                                     AND IE.SGESTADO    = MU.SGESTADO
                                                     AND IE.CDMUNICIPIO = MU.CDMUNICIPIO) I
                   WHERE E.CDEMPRESA   = P_CDEMPRESA
                     AND E.CDPAIS      = M.CDPAIS
                     AND E.SGESTADO    = M.SGESTADO
                     AND E.CDMUNICIPIO = M.CDMUNICIPIO
                     AND I.CDEMPRESA(+)= E.CDEMPRESA;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_REG:= NULL;
               END;

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

               V_SEQUPROD:= NVL(V_SEQUPROD,0)+1;
               INSERT INTO TMP_REGDADOSSPED_PIS
                  (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
               VALUES
                  ('0140', V_SEQUPROD, V_CNPJFILI, NULL, LTRIM(TO_CHAR(V_COUNTEST,'000')));
            ELSE
               V_COUNTEST:= TO_NUMBER(V_CDESTAB);
            END IF;

            V_CONTADOR := NVL(V_CONTADOR,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'501'||LTRIM(TO_CHAR(V_COUNTEST,'000')),V_REG_F010);

            V_SEQUPROD:= NVL(V_SEQUPROD,0)+1;
            INSERT INTO TMP_REGDADOSSPED_PIS
               (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
            VALUES
               ('F010', V_SEQUPROD, V_CNPJFILI, NULL, LTRIM(TO_CHAR(V_COUNTEST,'000')));
         ELSE
            V_COUNTEST := TO_NUMBER(V_CDESTAB);
         END IF;

         V_CONTADOR:= NVL(V_CONTADOR,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'501'||LTRIM(TO_CHAR(V_COUNTEST,'000'))||'18'||TO_CHAR(V_CONTADOR),C_F550.REGISTRO);

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

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

         /*PARA O CST 05 NAO E NECESSARIO O DETALHAMENTO*/
         IF (C_F550.CSTPIS <> '05') OR ((C_F550.CSTPIS = '05') AND (P_DTINICIAL < '01/08/2013')) THEN
            /*INSERE O VALOR REFERENTE AO PIS PARA SAIDAS(DEBITO)*/
            V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
            INSERT INTO TMP_REGBLOCOM_PIS
               (CHAVENOTA,CDREGISTRO,CDCONTREGI,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CSTPISCOFINS,CDFILIAL,CDNATUBCALPCIT,VRIMPRETIDO,VRTOTNF,CDNATUREC,CDESTAB,NRINSJUREST,CDCONTCTBL)
            VALUES
               ('F550'||C_F550.CDFILIAL,'M200',V_TMPBLCM,'',C_F550.BC_PIS,C_F550.ALIQ_PIS,'P',C_F550.VR_IMP_PIS,C_F550.CSTPIS,C_F550.CDFILIAL,'',0,C_F550.VROPER,C_F550.CDNATUREC,LTRIM(TO_CHAR(V_COUNTEST,'000')),V_CNPJFILI,C_F550.COD_CTA);
         END IF;
         /*PARA O CST 05 NAO E NECESSARIO O DETALHAMENTO*/
         IF (C_F550.CSTCOFINS <> '05')  OR ((C_F550.CSTCOFINS = '05') AND (P_DTINICIAL < '01/08/2013')) THEN
            /*INSERE O VALOR REFERENTE AO COFINS SAIDAS(DEBITO)*/
            V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
            INSERT INTO TMP_REGBLOCOM_PIS
               (CHAVENOTA,CDREGISTRO,CDCONTREGI,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CSTPISCOFINS,CDFILIAL,CDNATUBCALPCIT,VRIMPRETIDO,VRTOTNF,CDNATUREC,CDESTAB,NRINSJUREST,CDCONTCTBL)
            VALUES
               ('F550'||C_F550.CDFILIAL,'M600',V_TMPBLCM,'',C_F550.BC_COFINS,C_F550.ALIQ_COFINS,'P',C_F550.VR_IMP_COFINS,C_F550.CSTCOFINS,C_F550.CDFILIAL,'',0,C_F550.VROPER,C_F550.CDNATUREC,LTRIM(TO_CHAR(V_COUNTEST,'000')),V_CNPJFILI,C_F550.COD_CTA);
         END IF;
      END LOOP;
   END IF;

   V_COUNTEST := V_CONTESTAB;

   /*F600*/
   FOR C_F600 IN(SELECT REGISTRO      ||'|'||
                        IND_NAT_RET   ||'|'||
                        DT_RET        ||'|'||
                        REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_BC_RET),4),'999999999990.0000')),'.',',')  ||'|'||
                        REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_RET),2),'999999999990.00')),'.',',')  ||'|'||
                        COD_RECE      ||'|'||
                        IND_NAT_REC   ||'|'||
                        CNPJ          ||'|'||
                        REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_RET_PIS),2),'999999999990.00')),'.',',')  ||'|'||
                        REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_RET_COFINS),2),'999999999990.00')),'.',',')  ||'|'||
                        IND_DEC       ||'|' AS REGISTRO,
                        ROUND(SUM(VL_BC_RET),2) AS VL_BC_RET, ROUND(SUM(VL_RET_PIS),2) AS VL_RET_PIS,
                        ROUND(SUM(VL_RET_COFINS),2) AS VL_RET_COFINS, MAX(CDFILIAL) AS CDFILIAL, MAX(CDINSCESTA) AS CDINSCESTA,
                        TRIM(REPLACE(REPLACE(NRINSJURIEST,CHR(10),''),CHR(9),'')) AS NRINSJURIEST
                   FROM (SELECT '|F600' AS REGISTRO,
                                NVL(C.IDIDENTCLIE,'99') AS IND_NAT_RET,
                                C.CDRECECLIE AS COD_RECE,
                                MIN(TO_CHAR(DECODE(P.IDFORMAPUR,'P',(SELECT MIN(DTBAIXAREC)
                                                                       FROM BAIXARECEBIDA BX, BOLETRECEBER BO, CONTARECEBER CO
                                                                      WHERE CO.CDFILIAL     = N.CDFILIAL
                                                                        AND CO.NRLANCTONF   = N.NRLANCTONF
                                                                        AND CO.CDFILIBOLREC = BO.CDFILIAL
                                                                        AND CO.NRLANBOLREC  = BO.NRLANBOLREC
                                                                        AND BO.CDFILIBAIREC = BX.CDFILIAL
                                                                        AND BO.NRBAIXAREC   = BX.NRBAIXAREC),N.DTEMISSAO),'DDMMYYYY')) AS DT_RET,
                                DECODE(F.IDCUMUPISCOFIL,'S','0','1') AS IND_NAT_REC,
                                SUM(CASE WHEN P.CDIMPOSTONFE IN (P_PIS_RET_E,P_PIS_RET_S) THEN I.VRCONTITIMP ELSE 0 END) AS VL_CONT_RET,
                                SUM(CASE WHEN P.CDIMPOSTONFE IN (P_PIS_RET_E,P_PIS_RET_S) THEN I.VRBASECALCIT ELSE 0 END) AS VL_BC_RET,
                                SUM(CASE WHEN P.CDIMPOSTONFE IN (P_PIS_RET_E,P_PIS_RET_S) THEN I.VRIMPOSTOIT ELSE 0 END + CASE WHEN P.CDIMPOSTONFE IN (P_COFINS_RET_E,P_COFINS_RET_S) THEN I.VRIMPOSTOIT ELSE 0 END) AS VL_RET,
                                LPAD(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(C.NRINSJURCLIE,'.',''),'/',''),'-',''),'\',''),' ',''),14,'0') AS CNPJ,
                                SUM(CASE WHEN P.CDIMPOSTONFE IN (P_PIS_RET_E,P_PIS_RET_S) THEN I.VRIMPOSTOIT ELSE 0 END) AS VL_RET_PIS,
                                SUM(CASE WHEN P.CDIMPOSTONFE IN (P_COFINS_RET_E,P_COFINS_RET_S) THEN I.VRIMPOSTOIT ELSE 0 END) AS VL_RET_COFINS,
                                '0' AS IND_DEC, MAX(F.CDFILIAL) AS CDFILIAL, IE.CDINSCESTA,
                                LPAD(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.',''),'/',''),'-',''),'\',''),' ',''),14,'0') AS NRINSJURIEST
                           FROM NOTAFISCAL N, ITEMPROIMPOS I, INSCRESTAD IE, IMPOSTO P, CLIENTE C, FILIAL F,
                                (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                                   FROM IMPOSTONF IP, MAPAAPURAC MP, IMPOSTO IM
                                  WHERE MP.CDEMPRESA  = P_CDEMPRESA
                                    AND MP.CDIMPOSTO IN (P_PIS,P_COFINS)
                                    AND MP.DTINIPERIODO BETWEEN P_DTINICIAL AND P_DTFINAL
                                    AND MP.NRSEQUMAPAPU = IP.NRSEQMAPOIMP) NF
                          WHERE NF.CDFILIAL   = N.CDFILIAL
                            AND NF.NRLANCTONF = N.NRLANCTONF
                            AND N.CDFILIAL    = I.CDFILIAL
                            AND N.NRLANCTONF  = I.NRLANCTONF
                            AND I.VRIMPOSTOIT > 0
                            AND I.CDFISCALIT  = '1'
                            AND I.CDIMPOSTO   = P.CDIMPOSTO
                            AND P.CDIMPOSTONFE IN (P_PIS_RET_E,P_PIS_RET_S,P_COFINS_RET_E,P_COFINS_RET_S)
                            AND C.CDCLIENTE   = N.CDCLIENTE
                            AND F.CDFILIAL    = N.CDFILIEMIT
                            AND N.CDEMPRESA   = IE.CDEMPRESA
                            AND N.CDINSCESTA  = IE.CDINSCESTA
                          GROUP BY LPAD(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(C.NRINSJURCLIE,'.',''),'/',''),'-',''),'\',''),' ',''),14,'0'),
                                   NVL(C.IDIDENTCLIE,'99'), C.CDRECECLIE, DECODE(F.IDCUMUPISCOFIL,'S','0','1'),
                                   IE.CDINSCESTA, LPAD(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.',''),'/',''),'-',''),'\',''),' ',''),14,'0')
                          UNION ALL
                         SELECT '|F600' AS REGISTRO,
                                NVL(C.IDIDENTCLIE,'99') AS IND_NAT_RET,
                                C.CDRECECLIE AS COD_RECE,
                                TO_CHAR(I.DTBAIXAREC,'DDMMYYYY') AS DT_RET,
                                DECODE(F.IDCUMUPISCOFIL,'S','0','1') AS IND_NAT_REC,
								        SUM(CASE WHEN P.CDIMPOSTONFE IN (P_PIS_RET_E,P_PIS_RET_S) THEN I.VRPROPREC ELSE 0 END) AS VL_CONT_RET,
                                SUM(ROUND(CASE WHEN P.CDIMPOSTONFE IN (P_PIS_RET_E,P_PIS_RET_S) THEN I.VRPROPRECBC ELSE 0 END,2)) AS VL_BC_RET,
                                SUM(ROUND(CASE WHEN P.CDIMPOSTONFE IN (P_PIS_RET_E,P_PIS_RET_S) THEN (I.VRPROPRECBC*I.VRALIQNF/100) ELSE 0 END,2) + ROUND(CASE WHEN P.CDIMPOSTONFE IN (P_COFINS_RET_E,P_COFINS_RET_S) THEN (I.VRPROPRECBC*I.VRALIQNF/100) ELSE 0 END,2)) AS VL_RET,
                                LPAD(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(C.NRINSJURCLIE,'.',''),'/',''),'-',''),'\',''),' ',''),14,'0') AS CNPJ,
                                SUM(ROUND(CASE WHEN P.CDIMPOSTONFE IN (P_PIS_RET_E,P_PIS_RET_S) THEN (I.VRPROPRECBC*I.VRALIQNF/100) ELSE 0 END,2)) AS VL_RET_PIS,
                                SUM(ROUND(CASE WHEN P.CDIMPOSTONFE IN (P_COFINS_RET_E,P_COFINS_RET_S) THEN (I.VRPROPRECBC*I.VRALIQNF/100) ELSE 0 END,2)) AS VL_RET_COFINS,
                                '0' AS IND_DEC, MAX(F.CDFILIAL) AS CDFILIAL, IE.CDINSCESTA,
                                LPAD(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.',''),'/',''),'-',''),'\',''),' ',''),14,'0') AS NRINSJURIEST
                           FROM NOTAFISCAL N, IMPBAIXAREC I, INSCRESTAD IE, IMPOSTO P, CLIENTE C, FILIAL F, MAPAAPURAC MP
                          WHERE MP.CDEMPRESA    = P_CDEMPRESA
                            AND MP.CDIMPOSTO    IN (P_PIS,P_COFINS)
                            AND MP.DTINIPERIODO BETWEEN P_DTINICIAL AND P_DTFINAL
                            AND MP.NRSEQUMAPAPU = I.NRSEQMAPOIMP
                            AND N.CDFILIAL      = I.CDFILIAL
                            AND N.NRLANCTONF    = I.NRLANCTONF
                            AND ROUND(I.VRPROPRECBC*I.VRALIQNF/100,2) > 0
                            AND I.CDIMPOSTO     = P.CDIMPOSTO
                            AND P.CDIMPOSTONFE  IN (P_PIS_RET_E,P_PIS_RET_S,P_COFINS_RET_E,P_COFINS_RET_S)
                            AND C.CDCLIENTE     = N.CDCLIENTE
                            AND F.CDFILIAL      = N.CDFILIEMIT
                            AND N.CDEMPRESA     = IE.CDEMPRESA
                            AND N.CDINSCESTA    = IE.CDINSCESTA
                          GROUP BY TO_CHAR(I.DTBAIXAREC,'DDMMYYYY'), LPAD(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(C.NRINSJURCLIE,'.',''),'/',''),'-',''),'\',''),' ',''),14,'0'),
                                   NVL(C.IDIDENTCLIE,'99'), C.CDRECECLIE, DECODE(F.IDCUMUPISCOFIL,'S','0','1'),
                                   IE.CDINSCESTA, LPAD(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.',''),'/',''),'-',''),'\',''),' ',''),14,'0'))
                         GROUP BY  REGISTRO, IND_NAT_RET, COD_RECE, IND_NAT_REC, DT_RET, CNPJ, IND_DEC, TRIM(REPLACE(REPLACE(NRINSJURIEST,CHR(10),''),CHR(9),'')))LOOP

      /*BUSCA O CNPJ DO ESTABELECIMENTO*/
      V_CNPJFILI:= C_F600.NRINSJURIEST;

      /*VERIFICA SE O REGISTRO DE IDENTIFICACAO DO ESTABELECIMENTO JA FOI INSERIDO */
      V_CDESTAB := NULL;
      BEGIN
         SELECT MAX(CDESTAB)
           INTO V_CDESTAB
           FROM TMP_REGDADOSSPED_PIS
          WHERE CDREGISTRO = 'F010'
            AND CDCHAVEREG = V_CNPJFILI;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDESTAB := NULL;
      END;

      /*IDENTIFICACAO DO ESTABELECIMENTO*/
      IF V_CDESTAB IS NULL THEN
         V_REG_F010 := '|F010|'||V_CNPJFILI||'|';

         /*VERIFICA SE O ESTABELECIMENTO JA FOI INSERIDO*/
         V_CDESTAB := NULL;
         BEGIN
            SELECT MAX(CDESTAB)
              INTO V_CDESTAB
              FROM TMP_REGDADOSSPED_PIS
             WHERE CDREGISTRO = '0140'
               AND CDCHAVEREG = V_CNPJFILI;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_CDESTAB := NULL;
         END;

         /*INSERE O ESTABELECIMENTO*/
         IF V_CDESTAB IS NULL THEN
            V_CONTESTAB:= NVL(V_CONTESTAB,0)+1;
            V_COUNTEST := V_CONTESTAB;
            BEGIN
               SELECT '|0140'         ||'|'||
                      LTRIM(TO_CHAR(V_COUNTEST,'000')) ||'|'||
                      E.NMRAZSOCEMPR  ||'|'||
                      V_CNPJFILI      ||'|'||
                      I.SGESTADO      ||'|'||
                      ''              ||'|'||
                      M.CDMUNICIBGE   ||'|'||
                      ''              ||'|'||
                      ''              ||'|' AS REG
                 INTO V_REG
                 FROM MUNICIPIO M, EMPRESA E, INSCRESTAD I
                WHERE I.CDEMPRESA   = P_CDEMPRESA
                  AND I.CDINSCESTA  = C_F600.CDINSCESTA
                  AND I.CDPAIS      = M.CDPAIS
                  AND I.SGESTADO    = M.SGESTADO
                  AND I.CDMUNICIPIO = M.CDMUNICIPIO
                  AND E.CDEMPRESA   = I.CDEMPRESA;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_REG:= NULL;
            END;

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

            V_SEQUPROD:= NVL(V_SEQUPROD,0)+1;
            INSERT INTO TMP_REGDADOSSPED_PIS
               (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
            VALUES
               ('0140', V_SEQUPROD, V_CNPJFILI, NULL, LTRIM(TO_CHAR(V_COUNTEST,'000')));
         ELSE
            V_COUNTEST:= TO_NUMBER(V_CDESTAB);
         END IF;

         V_CONTADOR := NVL(V_CONTADOR,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'501'||LTRIM(TO_CHAR(V_COUNTEST,'000')),V_REG_F010);

         V_SEQUPROD:= NVL(V_SEQUPROD,0)+1;
         INSERT INTO TMP_REGDADOSSPED_PIS
            (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
         VALUES
            ('F010',V_SEQUPROD,V_CNPJFILI,NULL,LTRIM(TO_CHAR(V_COUNTEST,'000')));
      ELSE
         V_COUNTEST := TO_NUMBER(V_CDESTAB);
      END IF;

      V_CONTADOR:= NVL(V_CONTADOR,0)+1;
      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'501'||LTRIM(TO_CHAR(V_COUNTEST,'000'))||'60'||TO_CHAR(V_CONTADOR),C_F600.REGISTRO);

      V_PISRET    := NVL(V_PISRET,0) + C_F600.VL_RET_PIS;
      V_COFINSRET := NVL(V_COFINSRET,0) + C_F600.VL_RET_COFINS;

      V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
      INSERT INTO TMP_REGBLOCOM_PIS
         (CHAVENOTA,CDREGISTRO,CDCONTREGI,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CSTPISCOFINS,CDFILIAL,CDNATUBCALPCIT,VRIMPRETIDO,VRTOTNF,CDNATUREC,CDESTAB,NRINSJUREST,CDCONTCTBL)
      VALUES
         ('F600'||C_F600.CDFILIAL,'M200',V_TMPBLCM,'',C_F600.VL_BC_RET,0.65,'P',0,'',C_F600.CDFILIAL,'',C_F600.VL_RET_PIS,C_F600.VL_RET_PIS,'',LTRIM(TO_CHAR(V_COUNTEST,'000')),'','');

      V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
      INSERT INTO TMP_REGBLOCOM_PIS
         (CHAVENOTA,CDREGISTRO,CDCONTREGI,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CSTPISCOFINS,CDFILIAL,CDNATUBCALPCIT,VRIMPRETIDO,VRTOTNF,CDNATUREC,CDESTAB,NRINSJUREST,CDCONTCTBL)
      VALUES
         ('F600'||C_F600.CDFILIAL,'M600',V_TMPBLCM,'',C_F600.VL_BC_RET,3,'P',0,'',C_F600.CDFILIAL,'',C_F600.VL_RET_COFINS,C_F600.VL_RET_COFINS,'',LTRIM(TO_CHAR(V_COUNTEST,'000')),'','');
   END LOOP;

   /*F700 - Deducoes Diversas*/
   FOR C_F700 IN(SELECT '|F700'                              ||'|'||
                        I.IDORIGDEDUC                        ||'|'||
                        DECODE(I.IDCUMUPISCOFIL,'S','0','1') ||'|'||
                        REPLACE(LTRIM(TO_CHAR(SUM(DECODE(M.CDIMPOSTO,P_PIS,I.VRITEMDETAPU,0)),    '999999999990.00')),'.',',') ||'|'||
                        REPLACE(LTRIM(TO_CHAR(SUM(DECODE(M.CDIMPOSTO,P_COFINS,I.VRITEMDETAPU,0)), '999999999990.00')),'.',',') ||'|'||
                        REPLACE(LTRIM(TO_CHAR(SUM(DECODE(M.CDIMPOSTO,P_PIS,I.VRBASEITEMDETAPU,0)),'999999999990.00')),'.',',') ||'|'||
                        TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.'),'/'),'-'),'\'),CHR(10)),CHR(9)))||'|'||
                        MAX(TRIM(RETIRA_CARACTER_ESPECIAL(I.DSITEMDETAPU,'S'))) ||'|' AS REGISTRO, MAX(IE.CDINSCESTA) AS CDINSCESTA,
                        TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.'),'/'),'-'),'\'),CHR(10)),CHR(9))) AS NRINSJUR
                   FROM MAPAAPURAC M, ITDETAPUCONTR I, NOTAFISCAL N, EMPRESA E, INSCRESTAD IE
                  WHERE E.CDEMPRESA    = P_CDEMPRESA
                    AND E.CDEMPRESA    = M.CDEMPRESA
                    AND N.CDEMPRESA    = IE.CDEMPRESA
                    AND N.CDINSCESTA   = IE.CDINSCESTA
                    AND M.CDIMPOSTO    IN (P_PIS,P_COFINS)
                    AND M.DTINIPERIODO BETWEEN P_DTINICIAL AND P_DTFINAL
                    AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                    AND I.IDTIPODETAPU = 'DD'
                    AND I.CDFILIAL     = N.CDFILIAL(+)
                    AND I.NRLANCTONF   = N.NRLANCTONF(+)
                  GROUP BY I.IDORIGDEDUC, I.IDCUMUPISCOFIL, IE.NRINSJURIEST) LOOP

      /*BUSCA O CNPJ DO ESTABELECIMENTO*/
      V_CNPJFILI:= C_F700.NRINSJUR;

      /*VERIFICA SE O REGISTRO DE IDENTIFICACAO DO ESTABELECIMENTO JA FOI INSERIDO */
      V_CDESTAB := NULL;
      BEGIN
         SELECT MAX(CDESTAB)
           INTO V_CDESTAB
           FROM TMP_REGDADOSSPED_PIS
          WHERE CDREGISTRO = 'F010'
            AND CDCHAVEREG = V_CNPJFILI;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDESTAB := NULL;
      END;

      /*IDENTIFICACAO DO ESTABELECIMENTO*/
      IF V_CDESTAB IS NULL THEN
         V_REG_F010 := '|F010|'||V_CNPJFILI||'|';

         /*VERIFICA SE O ESTABELECIMENTO JA FOI INSERIDO*/
         V_CDESTAB := NULL;
         BEGIN
            SELECT MAX(CDESTAB)
              INTO V_CDESTAB
              FROM TMP_REGDADOSSPED_PIS
             WHERE CDREGISTRO = '0140'
               AND CDCHAVEREG = V_CNPJFILI;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_CDESTAB := NULL;
         END;

         /*INSERE O ESTABELECIMENTO*/
         IF V_CDESTAB IS NULL THEN
            V_CONTESTAB := NVL(V_CONTESTAB,0)+1;
            V_COUNTEST  := V_CONTESTAB;
            BEGIN
               SELECT '|0140'        ||'|'||
                      LTRIM(TO_CHAR(V_COUNTEST,'000')) ||'|'||
                      E.NMRAZSOCEMPR ||'|'||
                      V_CNPJFILI     ||'|'||
                      I.SGESTADO     ||'|'||
                      ''             ||'|'||
                      M.CDMUNICIBGE  ||'|'||
                      ''             ||'|'||
                      ''             ||'|' AS REG
                 INTO V_REG
                 FROM MUNICIPIO M, EMPRESA E, INSCRESTAD I
                WHERE I.CDEMPRESA    = P_CDEMPRESA
                  AND I.CDINSCESTA   = C_F700.CDINSCESTA
                  AND I.CDPAIS       = M.CDPAIS
                  AND I.SGESTADO     = M.SGESTADO
                  AND I.CDMUNICIPIO  = M.CDMUNICIPIO
                  AND E.CDEMPRESA    = I.CDEMPRESA;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_REG:= NULL;
            END;

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

            V_SEQUPROD:= NVL(V_SEQUPROD,0)+1;
            INSERT INTO TMP_REGDADOSSPED_PIS
               (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
            VALUES
               ('0140', V_SEQUPROD, V_CNPJFILI ,NULL, LTRIM(TO_CHAR(V_COUNTEST,'000')));
         ELSE
            V_COUNTEST:= TO_NUMBER(V_CDESTAB);
         END IF;

         V_CONTADOR := NVL(V_CONTADOR,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'501'||LTRIM(TO_CHAR(V_COUNTEST,'000')),V_REG_F010);

         V_SEQUPROD:= NVL(V_SEQUPROD,0)+1;
         INSERT INTO TMP_REGDADOSSPED_PIS
           (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
         VALUES
           ('F010', V_SEQUPROD, V_CNPJFILI, NULL, LTRIM(TO_CHAR(V_COUNTEST,'000')));
      ELSE
         V_COUNTEST := TO_NUMBER(V_CDESTAB);
      END IF;

      V_CONTADOR:= NVL(V_CONTADOR,0)+1;
      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'501'||LTRIM(TO_CHAR(V_COUNTEST,'000'))||'70'||TO_CHAR(V_CONTADOR),C_F700.REGISTRO);

   END LOOP;

   P_SEQUPROD:= NVL(V_CONTBLC0,0);
   RETURN(V_CONTADOR);
END;
/