CREATE OR REPLACE FUNCTION SPED_PIS_COFINS_BLOCOC_ECF(
   P_CONTEST     IN NUMBER,
   P_NRSEQARQMAG IN ARQMAGPISCOFINS.NRSEQARQMAG%TYPE,
   P_DTINICIAL   IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL     IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_CDEMPRESA   IN EMPRESA.CDEMPRESA%TYPE,
   P_PIS         IN IMPOSTO.CDIMPOSTO%TYPE,
   P_COFINS      IN IMPOSTO.CDIMPOSTO%TYPE,
   P_LIVROMAG    IN LIVROMAGNETICO.CDLIVROMAG%TYPE,
   P_CONTREGA    IN DOUBLE PRECISION,
   P_COUNTA      OUT NUMBER,
   P_CONTESTAB   OUT NUMBER) RETURN NUMBER
AS  
   V_OLDESTAB  VARCHAR2(20); --Controle codigo estabelecimento registro atual
   V_CONTEST   NUMBER;  --Controle contador de estabelecimentos
   V_NRINSJUR  VARCHAR2(20);
   V_REG_C010  VARCHAR(100);
   V_IDMOVREF  VARCHAR2(1);
   V_REG       VARCHAR2(1000);
   V_PRODUTO   VARCHAR2(100);
   V_NOVOEST   VARCHAR2(1);
   V_UNID      VARCHAR2(10);
   V_CONTREGA  NUMBER;
   V_COUNTBCL0 NUMBER;
   V_TMPBLCM   NUMBER;
   V_SEQUPROD  NUMBER;
   V_IDC010    VARCHAR2(20);
   V_COD_CC    VARCHAR2(20);
   V_SEQCCTBL  NUMBER;
BEGIN
   SELECT MAX(CDCONTREGI)
     INTO V_SEQUPROD
     FROM TMP_REGDADOSSPED_PIS;

   SELECT MAX(CDCONTREGI)
     INTO V_TMPBLCM
     FROM TMP_REGBLOCOM_PIS;

   DELETE FROM TMP_REGDADOSSPED_ECF_NF;
   DELETE FROM TMP_REGDADOSSPED_ECF_RE;

   INSERT INTO TMP_REGDADOSSPED_ECF_NF
      (CDFILIAL, NRLANCTONF, DTEMISSAO, CDEMPRESA, CDINSCESTA, IDSISTEMA)
   (SELECT NF.CDFILIAL, NF.NRLANCTONF, NF.DTEMISSAO, NF.CDEMPRESA, NF.CDINSCESTA, NF.IDSISTEMA
      FROM NOTAFISCAL NF
     WHERE NF.DTEMISSAO BETWEEN P_DTINICIAL AND P_DTFINAL
       AND NF.CDEMPRESA = P_CDEMPRESA
       AND NF.IDSISTEMA = 'FIS');

   INSERT INTO TMP_REGDADOSSPED_ECF_RE
      (CDFILIALVENDA, CDCAIXA, NRSEQVENDA, CDFILIALNOTA, NRLANCTONF)
   (SELECT RE.CDFILIALVENDA, RE.CDCAIXA, RE.NRSEQVENDA, RE.CDFILIALNOTA, RE.NRLANCTONF
      FROM RELACNFCEAPROV RE, TMP_REGDADOSSPED_ECF_NF NF
     WHERE RE.CDFILIALNOTA = NF.CDFILIAL
       AND RE.NRLANCTONF   = NF.NRLANCTONF);

   V_CONTEST  := P_CONTEST;
   V_CONTREGA := P_CONTREGA;
   FOR C_ESTAB IN (SELECT DISTINCT CNPJ, INSCRICAO
                     FROM (SELECT REPLACE(REPLACE(REPLACE(REPLACE(I.NRINSJURIEST,'.',''),'/',''),'-',''),'\','') AS CNPJ,
                                  REPLACE(REPLACE(REPLACE(REPLACE(I.CDINSCESTA,'.',''),'/',''),'-',''),'\','') AS INSCRICAO
                             FROM INSCRESTAD I, FILIAL F, CAIXA CX
                            WHERE I.CDEMPRESA  = P_CDEMPRESA
                              AND F.CDINSCESTA = I.CDINSCESTA
                              AND I.CDEMPRESA  = F.CDEMPRESA
                              AND F.CDFILIAL   = CX.CDFILIAL)JUR)LOOP

      V_NOVOEST:= 'S';
      FOR C_REGC400 IN (SELECT '|C400'                      ||'|'||
                               '2D'                         ||'|'||
                               TRIM(NVL(DSMODMARCAECF,'M')) ||'|'||  --DSMODMARCAECF
                               NRSERIEREDZ1                 ||'|'||
                               CDCAIXA                      ||'|'   AS C400,
                               CDCAIXA, NRSERIEREDZ, CDFILIAL, CDINSCESTA
                          FROM (SELECT SUBSTR(NRSERIEREDZ,1,20) AS NRSERIEREDZ1,
                                       SUBSTR(REPLACE(TRIM(DSMODMARCAECF),'|','-'),1,20) AS DSMODMARCAECF, --DSMODMARCAECF
                                       CDCAIXA, RZ.NRSERIEREDZ, RZ.CDFILIAL, FI.CDINSCESTA
                                  FROM REDUCAOZ RZ ,FILIAL FI, INSCRESTAD IE
                                 WHERE RZ.DTREDUCAOZ BETWEEN P_DTINICIAL AND P_DTFINAL
                                   AND RZ.CDFILIAL     = FI.CDFILIAL
                                   AND FI.CDEMPRESA    = P_CDEMPRESA
                                   AND REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.',''),'/',''),'-',''),'\','') = C_ESTAB.CNPJ
                                   AND FI.CDINSCESTA   = IE.CDINSCESTA
                                   AND REPLACE(REPLACE(REPLACE(REPLACE(FI.CDINSCESTA,'.',''),'/',''),'-',''),'\','') = C_ESTAB.INSCRICAO
                                   AND RZ.IDSTATUS     = 'A'
                                   AND NRSERIEREDZ IS NOT NULL
                                 GROUP BY SUBSTR(NRSERIEREDZ,1,20),DSMODMARCAECF,
                                          CDCAIXA, RZ.NRSERIEREDZ,RZ.CDFILIAL, FI.CDINSCESTA))LOOP


         IF V_NOVOEST = 'S' THEN
            /*VERIFICA SE O ESTABELECIMENTO JA FOI INSERIDO ANTERIORMENTE*/
            V_OLDESTAB:=NULL;
            V_NRINSJUR:=NULL;
            SELECT MAX(CDESTAB) AS ESTAB, MAX(CDCHAVEREG) AS CNPJ
              INTO V_OLDESTAB, V_NRINSJUR
              FROM TMP_REGDADOSSPED_PIS
             WHERE CDREGISTRO = '0140'
               AND CDCHAVEREG = C_ESTAB.CNPJ;

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

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

            /*VERIFICA SE O ESTABELECIMENTO JA FOI INSERIDO*/
            V_IDC010:= NULL;
            SELECT MAX(CDCHAVEREG)
             INTO V_IDC010
             FROM TMP_REGDADOSSPED_PIS
            WHERE CDREGISTRO = 'C010'
              AND CDCHAVEREG = C_ESTAB.CNPJ;

            /*IDENTIFICACAO DO ESTABELECIMENTO*/
            IF V_IDC010 IS NULL THEN
               V_REG_C010 := '|C010|'||C_ESTAB.CNPJ||'|2|';
               V_CONTREGA := NVL(V_CONTREGA,0)+1;
               INSERT INTO ARQMAGPISCOFINS
                  (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
               VALUES
                  (P_NRSEQARQMAG,'303'||LTRIM(TO_CHAR(V_OLDESTAB,'000')),V_REG_C010);

               V_SEQUPROD := NVL(V_SEQUPROD,0)+1;
               INSERT INTO TMP_REGDADOSSPED_PIS
                  (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
               VALUES
                  ('C010',V_SEQUPROD,C_ESTAB.CNPJ,NULL,LTRIM(TO_CHAR(V_OLDESTAB,'000')));
            END IF;
         END IF;
         /*INSERE REGISTRO C400*/
         V_CONTREGA := NVL(V_CONTREGA,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'303'||LTRIM(TO_CHAR(V_OLDESTAB,'000'))||'03'||LPAD(RTRIM(V_CONTREGA),22,'0'),C_REGC400.C400);

         V_NOVOEST:= 'N';

         FOR C_REGC405 IN (SELECT ('|C405'                                                                           || '|' ||
                                   TO_CHAR(RZ.DTREDUCAOZ,'ddmmyyyy')                                                 || '|' ||
                                   LPAD(LTRIM(NVL(RZ.NRREINIREDZ,'0'),'0'), 3, '0')                                  || '|' ||
                                   LPAD(LTRIM(NVL(RZ.NRREDREDZ,'0'),'0'), 6, '0')                                    || '|' ||
                                   LPAD(LTRIM(NVL(RZ.NRULTCFREDZ,'0'),'0'), 6, '0')                                  || '|' ||
                                   REPLACE(LTRIM(TO_CHAR(TRUNC(RZ.VRGTREDZ,2), '999999999990.00')), '.', ',')        || '|' ||
                                   REPLACE(LTRIM(TO_CHAR(TRUNC(RZ.VRVENBRUREDZ,2), '999999999990.00')), '.', ',')    || '|' ) AS C405,
                                   DTREDUCAOZ, RZ.NRECFREDZ, FI.CDFILIAL, NRULTCFREDZ, RZ.NRREDREDZ
                             FROM REDUCAOZ RZ, FILIAL FI, INSCRESTAD IE
                            WHERE RZ.CDFILIAL    = FI.CDFILIAL
                              AND RZ.DTREDUCAOZ BETWEEN P_DTINICIAL AND P_DTFINAL
                              AND FI.CDEMPRESA   = P_CDEMPRESA
                              AND IE.NRINSJURIEST = C_ESTAB.CNPJ
                              AND FI.CDINSCESTA   = IE.CDINSCESTA
                              AND RZ.IDSTATUS     = 'A'
                              AND IE.CDEMPRESA    = P_CDEMPRESA
                              AND REPLACE(REPLACE(REPLACE(REPLACE(FI.CDINSCESTA,'.',''),'/',''),'-',''),'\','') = C_ESTAB.INSCRICAO
                              AND RZ.CDCAIXA     = C_REGC400.CDCAIXA
                              AND NRSERIEREDZ    = C_REGC400.NRSERIEREDZ
                              AND VRVENBRUREDZ   > 0) LOOP
            V_CONTREGA := NVL(V_CONTREGA,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'303'||LTRIM(TO_CHAR(V_OLDESTAB,'000'))||'03'||LPAD(RTRIM(V_CONTREGA),22,'0'),C_REGC405.C405);

            /*REGISTRO C481 - TOTALIZACAO PIS*/
            FOR C_REGC481 IN (SELECT REGISTRO      ||'|'||
                                     CDSITUTRIBNFE ||'|'||
                                     LTRIM(REPLACE(TO_CHAR(ROUND(SUM(NVL(VRPRODUTO,0)),2),'9999999999990.00'),'.',','))||'|'||
                                     LTRIM(REPLACE(TO_CHAR(ROUND(SUM(NVL(VRBASECALCIT,0)),2),'9999999999990.00'),'.',','))||'|'||
                                     LTRIM(REPLACE(TO_CHAR(ROUND(NVL(VRALIQIT,0),2),'990.00'),'.',','))||'|'||
                                     ALI_PIS_QT                                               ||'|'||
                                     QT_BC_PIS                                                ||'|'||
                                     LTRIM(REPLACE(TO_CHAR(ROUND(SUM(NVL(VRIMPOSTO,0)),2),'9999999999990.00'),'.',','))||'|'||
                                     CDARVPROD||'|'||
                                     CONTCTBL_SPED('S',CDPRODUTO,CDTIPOOPER,C_REGC405.CDFILIAL) ||'|' AS REGISTRO /*CODIGO CONTA ANALITICA*/,
                                     CDSITUTRIBNFE, CDPRODUTO, VRALIQIT,CDARVPROD,SGUNIDADE,
                                     CONTCTBL_SPED('S',CDPRODUTO,CDTIPOOPER,C_REGC405.CDFILIAL) AS COD_CTA, CDCLASFISC
                                FROM (SELECT '|C481' AS REGISTRO, CDSITUTRIBNFE, IT.VRPRODUTO AS VRPRODUTO, DECODE(CDFISCALIT,1,IT.VRPRODUTO,0) AS VRBASECALCIT,
                                             DECODE(CDFISCALIT,'1',VRALIQIT,0) AS VRALIQIT, '' AS ALI_PIS_QT, '' AS QT_BC_PIS,
                                             (VRPRODUTO*(DECODE(CDFISCALIT,'1',VRALIQIT,0))/100) AS VRIMPOSTO,
                                             P.CDARVPROD AS CDARVPROD, P.CDPRODUTO, P.SGUNIDADE, PV.CDTPOPERCNTREC AS CDTIPOOPER, IT.CDCLASFISC
                                        FROM ITEMVENOUTIMPOS IT, PRODUTO P, IMPOSTO I, PARAVEND PV
                                       WHERE IT.CDFILIAL    = C_REGC405.CDFILIAL
                                         AND IT.CDCAIXA     = C_REGC400.CDCAIXA
                                         AND IT.DTREDUCAOZ  = C_REGC405.DTREDUCAOZ
                                         AND IT.CDIMPOSTO   = I.CDIMPOSTO
                                         AND I.CDIMPOSTONFE = P_PIS
                                         AND P.CDPRODUTO    = IT.CDPRODUTO
                                         AND (IT.IDORIGEMVENDA NOT IN ('SAT', 'NFCE'))
                                         AND PV.CDFILIAL    = IT.CDFILIAL)
                               GROUP BY CDSITUTRIBNFE, CDPRODUTO, VRALIQIT, REGISTRO, ALI_PIS_QT, QT_BC_PIS, CDARVPROD,SGUNIDADE,
                                        CONTCTBL_SPED('S',CDPRODUTO,CDTIPOOPER,C_REGC405.CDFILIAL),CDCLASFISC)LOOP

               V_CONTREGA := NVL(V_CONTREGA,0)+1;
               INSERT INTO ARQMAGPISCOFINS
                  (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
               VALUES
                  (P_NRSEQARQMAG,'303'||LTRIM(TO_CHAR(V_OLDESTAB,'000'))||'03'||LPAD(RTRIM(V_CONTREGA),22,'0'),C_REGC481.REGISTRO);

               V_PRODUTO:=NULL;
              /* CODIGO DO PRODUTO */
               SELECT MAX(CDCHAVEREG)
                 INTO V_PRODUTO
                 FROM TMP_REGDADOSSPED_PIS
                WHERE CDCHAVEREG = C_REGC481.CDARVPROD
                  AND CDREGISTRO = '0200'
                  AND CDESTAB = LTRIM(TO_CHAR(V_OLDESTAB,'000'));

               IF (V_PRODUTO IS NULL) AND (C_REGC481.CDARVPROD IS NOT NULL) THEN
                  V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
                  INSERT INTO TMP_REGDADOSSPED_PIS
                     (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB,CDCAMPO4)
                  VALUES
                     ('0200', V_SEQUPROD, C_REGC481.CDARVPROD,NULL,LTRIM(TO_CHAR(V_OLDESTAB,'000')),C_REGC481.CDCLASFISC);
               END IF;

               V_UNID:= null;
               /* UNIDADE DE MEDIDA */
               SELECT MAX(CDCHAVEREG)
                 INTO V_UNID
                 FROM TMP_REGDADOSSPED_PIS
                WHERE CDCHAVEREG = C_REGC481.SGUNIDADE
                  AND CDREGISTRO = '0190'
                  AND CDESTAB = LTRIM(TO_CHAR(V_OLDESTAB,'000'));

               IF (V_UNID IS NULL) AND (C_REGC481.SGUNIDADE IS NOT NULL) THEN
                  V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
                  INSERT INTO TMP_REGDADOSSPED_PIS
                     (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
                  VALUES
                     ('0190', V_SEQUPROD, C_REGC481.SGUNIDADE,NULL,LTRIM(TO_CHAR(V_OLDESTAB,'000')));
               END IF;

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

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

            /*GRAVA OS DADOS DE CST, TIPO DE BASE DE CALCULO E TIPO DE CREDITO PARA O BLOCO M*/
            FOR C_GRAVM2 IN(SELECT SUM(VRBASECALCIT) AS VRBASECALCIT, SUM(VRIMPOSTO) AS VRIMPOSTO, CDSITUTRIBNFE,
                                   VRALIQIT, CDTPCRPICOSTNFE, CDNATUBCPCSTNFE, SUM(VRPRODUTO) AS VRPRODUTO,
                                   CDNATUREC, COD_CTA
                              FROM (SELECT DECODE(IT.CDFISCALIT,1,IT.VRPRODUTO,0) AS VRBASECALCIT, IT.VRPRODUTO,
                                           (IT.VRPRODUTO*(DECODE(IT.CDFISCALIT,'1',IT.VRALIQIT,0))/100) AS VRIMPOSTO,
                                           IT.CDSITUTRIBNFE, DECODE(IT.CDFISCALIT,'1',VRALIQIT,0) AS VRALIQIT, IT.CDTPCRPICOSTNFE,
                                           IT.CDNATUBCPCSTNFE, NVL(IT.CDNATUREC,R.CDNATUREC) AS CDNATUREC,
                                           CONTCTBL_SPED('S',IT.CDPRODUTO,P.CDTPOPERCNTREC,P.CDFILIAL) AS COD_CTA
                                      FROM ITEMVENOUTIMPOS IT, IMPOSTO I, RELACPRODITGEN R, PARAVEND P
                                     WHERE IT.CDFILIAL    = C_REGC405.CDFILIAL
                                       AND IT.CDCAIXA     = C_REGC400.CDCAIXA
                                       AND IT.DTREDUCAOZ  = C_REGC405.DTREDUCAOZ
                                       AND IT.CDFILIAL    = P.CDFILIAL
                                       AND IT.CDIMPOSTO   = I.CDIMPOSTO
                                       AND I.CDIMPOSTONFE = P_PIS
                                       AND IT.CDPRODUTO   = R.CDPRODUTO(+)
                                       AND P_LIVROMAG     = R.CDLIVROMAG(+)
                                       AND NVL(IT.IDORIGEMVENDA,'XXX') NOT IN ('SAT', 'NFCE'))
                             GROUP BY CDSITUTRIBNFE, VRALIQIT, CDTPCRPICOSTNFE, CDNATUBCPCSTNFE, CDNATUREC, COD_CTA)LOOP

               /*INSERE O VALOR REFERENTE AO PIS PARA SAIDAS(DEBITO)*/
               /*PARA O CST 05 NAO E NECESSARIO O DETALHAMENTO NAS VENDAS ECF, NFCE E SAT, A PARTIR DA VERSAO 2.05 DO VALIDADOR, DE AGOSTO DE 2013*/
               IF (C_GRAVM2.CDSITUTRIBNFE <> '05') AND (C_REGC405.DTREDUCAOZ >= '01/08/2013') 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,CDCONTCTBL)
                  VALUES
                     ('C481'||C_REGC405.DTREDUCAOZ||C_REGC405.CDFILIAL,'M200',V_TMPBLCM,C_GRAVM2.CDTPCRPICOSTNFE,C_GRAVM2.VRBASECALCIT,C_GRAVM2.VRALIQIT,'P',C_GRAVM2.VRIMPOSTO,C_GRAVM2.CDSITUTRIBNFE,C_REGC405.CDFILIAL,C_GRAVM2.CDNATUBCPCSTNFE,'0',C_GRAVM2.VRPRODUTO,C_GRAVM2.CDNATUREC,C_GRAVM2.COD_CTA);
               END IF;
            END LOOP;

            /*REGISTRO C485 - TOTALIZACAO COFINS*/
            FOR C_REGC485 IN (SELECT REGISTRO      ||'|'||
                                     CDSITUTRIBNFE ||'|'||
                                     LTRIM(REPLACE(TO_CHAR(ROUND(SUM(NVL(VRPRODUTO,0)),2),'9999999999990.00'),'.',','))||'|'||
                                     LTRIM(REPLACE(TO_CHAR(ROUND(SUM(NVL(VRBASECALCIT,0)),2),'9999999999990.00'),'.',','))||'|'||
                                     LTRIM(REPLACE(TO_CHAR(ROUND(NVL(VRALIQIT,0),2),'990.00'),'.',','))||'|'||
                                     ALI_COFINS_QT                                               ||'|'||
                                     QT_BC_COFINS                                                ||'|'||
                                     LTRIM(REPLACE(TO_CHAR(ROUND(SUM(NVL(VRIMPOSTO,0)),2),'9999999999990.00'),'.',','))||'|'||
                                     CDARVPROD||'|'||
                                     CONTCTBL_SPED('S',CDPRODUTO,CDTIPOOPER,C_REGC405.CDFILIAL) ||'|' AS REGISTRO /*CODIGO CONTA ANALITICA*/,
                                     CDSITUTRIBNFE, CDPRODUTO, VRALIQIT, CDARVPROD, SGUNIDADE,
                                     CONTCTBL_SPED('S',CDPRODUTO,CDTIPOOPER,C_REGC405.CDFILIAL) AS COD_CTA
                                FROM (SELECT '|C485' AS REGISTRO, CDSITUTRIBNFE, IT.VRPRODUTO AS VRPRODUTO, DECODE(CDFISCALIT,1,IT.VRPRODUTO,0) AS VRBASECALCIT,
                                             DECODE(CDFISCALIT,'1',VRALIQIT,0) AS VRALIQIT, '' AS ALI_COFINS_QT, '' AS QT_BC_COFINS, (VRPRODUTO*(DECODE(CDFISCALIT,'1',VRALIQIT,0))/100) AS VRIMPOSTO,
                                             P.CDARVPROD AS CDARVPROD, P.CDPRODUTO, P.SGUNIDADE,PV.CDTPOPERCNTREC AS CDTIPOOPER
                                        FROM ITEMVENOUTIMPOS IT, PRODUTO P, IMPOSTO I, PARAVEND PV
                                       WHERE IT.CDFILIAL    = C_REGC405.CDFILIAL
                                         AND IT.CDCAIXA     = C_REGC400.CDCAIXA
                                         AND IT.DTREDUCAOZ  = C_REGC405.DTREDUCAOZ
                                         AND IT.CDIMPOSTO   = I.CDIMPOSTO
                                         AND I.CDIMPOSTONFE = P_COFINS
                                         AND P.CDPRODUTO    = IT.CDPRODUTO
                                         AND (IT.IDORIGEMVENDA NOT IN ('SAT', 'NFCE'))
                                         AND PV.CDFILIAL    = IT.CDFILIAL)
                                       GROUP BY REGISTRO, CDSITUTRIBNFE, CDPRODUTO, CDARVPROD, VRALIQIT, ALI_COFINS_QT, QT_BC_COFINS,
                                             SGUNIDADE, CONTCTBL_SPED('S',CDPRODUTO,CDTIPOOPER,C_REGC405.CDFILIAL))LOOP

               V_CONTREGA := NVL(V_CONTREGA,0)+1;
               INSERT INTO ARQMAGPISCOFINS
                  (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
               VALUES
                  (P_NRSEQARQMAG,'303'||LTRIM(TO_CHAR(V_OLDESTAB,'000'))||'03'||LPAD(RTRIM(V_CONTREGA),22,'0'),C_REGC485.REGISTRO);

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

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

            /*GRAVA OS DADOS DE CST, TIPO DE BASE DE CALCULO E TIPO DE CREDITO PARA O BLOCO M*/
            FOR C_GRAVM6 IN(SELECT SUM(VRBASECALCIT) AS VRBASECALCIT, SUM(VRIMPOSTO) AS VRIMPOSTO, CDSITUTRIBNFE,
                                   VRALIQIT, CDTPCRPICOSTNFE, CDNATUBCPCSTNFE, SUM(VRPRODUTO) AS VRPRODUTO,
                                   CDNATUREC, COD_CTA
                              FROM (SELECT DECODE(IT.CDFISCALIT,1,IT.VRPRODUTO,0) AS VRBASECALCIT, IT.VRPRODUTO,
                                           (IT.VRPRODUTO*(DECODE(IT.CDFISCALIT,'1',IT.VRALIQIT,0))/100) AS VRIMPOSTO,
                                           IT.CDSITUTRIBNFE, DECODE(IT.CDFISCALIT,'1',IT.VRALIQIT,0) AS VRALIQIT, IT.CDTPCRPICOSTNFE,
                                           IT.CDNATUBCPCSTNFE, NVL(IT.CDNATUREC,R.CDNATUREC) AS CDNATUREC,
                                           CONTCTBL_SPED('S',IT.CDPRODUTO,P.CDTPOPERCNTREC,P.CDFILIAL) AS COD_CTA
                                      FROM ITEMVENOUTIMPOS IT, IMPOSTO I, RELACPRODITGEN R, PARAVEND P
                                     WHERE IT.CDFILIAL    = C_REGC405.CDFILIAL
                                       AND IT.CDCAIXA     = C_REGC400.CDCAIXA
                                       AND IT.DTREDUCAOZ  = C_REGC405.DTREDUCAOZ
                                       AND IT.CDFILIAL    = P.CDFILIAL
                                       AND IT.CDIMPOSTO   = I.CDIMPOSTO
                                       AND I.CDIMPOSTONFE = P_COFINS
                                       AND IT.CDPRODUTO   = R.CDPRODUTO(+)
                                       AND P_LIVROMAG     = R.CDLIVROMAG(+)
                                       AND NVL(IT.IDORIGEMVENDA,'XXX') NOT IN ('SAT', 'NFCE'))
                                     GROUP BY CDSITUTRIBNFE, VRALIQIT,CDTPCRPICOSTNFE, CDNATUBCPCSTNFE, CDNATUREC, COD_CTA)LOOP

               /*INSERE O VALOR REFERENTE AO COFINS PARA SAIDAS(DEBITO)*/
               /*PARA O CST 05 NAO E NECESSARIO O DETALHAMENTO NAS VENDAS ECF, NFCE E SAT, A PARTIR DA VERSAO 2.05 DO VALIDADOR, DE AGOSTO DE 2013*/
               IF (C_GRAVM6.CDSITUTRIBNFE <> '05') AND (C_REGC405.DTREDUCAOZ >= '01/08/2013') 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,CDCONTCTBL)
                  VALUES
                     ('C485'||C_REGC405.DTREDUCAOZ||C_REGC405.CDFILIAL,'M600',V_TMPBLCM,C_GRAVM6.CDTPCRPICOSTNFE,C_GRAVM6.VRBASECALCIT,C_GRAVM6.VRALIQIT,'P',C_GRAVM6.VRIMPOSTO,C_GRAVM6.CDSITUTRIBNFE,C_REGC405.CDFILIAL,C_GRAVM6.CDNATUBCPCSTNFE,'0',C_GRAVM6.VRPRODUTO,C_GRAVM6.CDNATUREC,C_GRAVM6.COD_CTA);
               END IF;
            END LOOP;
         END LOOP;
      END LOOP;

      FOR C_REGC860 IN (SELECT (REG     || '|' ||
                                COD_MOD || '|' ||
                                NR_SAT  || '|' ||
                                DT_DOC  || '|' ||
                                DOC_INI || '|' ||
                                DOC_FIM || '|' ) C860, NR_SAT, DTEMISSAO, CDFILIAL
                          FROM (SELECT '|C860' AS REG, '59' AS COD_MOD, VE.CDSERIESAT AS NR_SAT, TO_CHAR(NF.DTEMISSAO,'DDMMYYYY') AS DT_DOC,
                                       MIN(LPAD(NVL(VE.NRNOTAFISCALCE,0),6,'0')) DOC_INI, MAX(LPAD(NVL(VE.NRNOTAFISCALCE,0),6,'0')) DOC_FIM,
                                       NF.DTEMISSAO, MAX(VE.CDFILIAL) AS CDFILIAL
                                  FROM VENDA VE, INSCRESTAD IE, TMP_REGDADOSSPED_ECF_RE /*RELACNFCEAPROV*/ RE, TMP_REGDADOSSPED_ECF_NF /*NOTAFISCAL*/ NF
                                 WHERE VE.CDFILIAL        = RE.CDFILIALVENDA
                                   AND VE.CDCAIXA         = RE.CDCAIXA
                                   AND VE.NRSEQVENDA      = RE.NRSEQVENDA
                                   AND RE.CDFILIALNOTA    = NF.CDFILIAL
                                   AND RE.NRLANCTONF      = NF.NRLANCTONF
                                   AND NF.DTEMISSAO       BETWEEN P_DTINICIAL AND P_DTFINAL
                                   AND VE.IDTPEMISVEND    = 'S'
                                   AND NF.CDEMPRESA       = IE.CDEMPRESA
                                   AND NF.CDINSCESTA      = IE.CDINSCESTA
                                   AND REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.',''),'/',''),'-',''),'\','') = C_ESTAB.CNPJ
                                   AND REPLACE(REPLACE(REPLACE(REPLACE(IE.CDINSCESTA,'.',''),'/',''),'-',''),'\','')   = C_ESTAB.INSCRICAO
                                   AND VE.IDSITUVENDA     IN ('O')
                                   AND VE.IDAPROVACAONFCE = 'A'
                                   AND VE.IDSTATUSNFCE    = 'A'
                                   AND VE.IDTPAMBNFCE     = '1'
                                 GROUP BY VE.CDSERIESAT, NF.DTEMISSAO)) LOOP

         IF V_NOVOEST = 'S' THEN
            /*VERIFICA SE O ESTABELECIMENTO JA FOI INSERIDO ANTERIORMENTE*/
            V_OLDESTAB:=NULL;
            V_NRINSJUR:=NULL;
            SELECT MAX(CDESTAB) AS ESTAB, MAX(CDCHAVEREG) AS CNPJ
              INTO V_OLDESTAB, V_NRINSJUR
              FROM TMP_REGDADOSSPED_PIS
             WHERE CDREGISTRO = '0140'
               AND CDCHAVEREG = C_ESTAB.CNPJ;

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

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

            /*VERIFICA SE O ESTABELECIMENTO JA FOI INSERIDO*/
            V_IDC010:= NULL;
            SELECT MAX(CDCHAVEREG)
              INTO V_IDC010
              FROM TMP_REGDADOSSPED_PIS
             WHERE CDREGISTRO = 'C010'
               AND CDCHAVEREG = C_ESTAB.CNPJ;

            /*IDENTIFICACAO DO ESTABELECIMENTO*/
            IF V_IDC010 IS NULL THEN
               V_REG_C010:= '|C010|'||C_ESTAB.CNPJ||'|2|';
               V_CONTREGA := NVL(V_CONTREGA,0)+1;
               INSERT INTO ARQMAGPISCOFINS
                  (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
               VALUES
                  (P_NRSEQARQMAG,'303'||LTRIM(TO_CHAR(V_OLDESTAB,'000')),V_REG_C010);

               V_SEQUPROD := NVL(V_SEQUPROD,0)+1;
               INSERT INTO TMP_REGDADOSSPED_PIS
                  (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
               VALUES
                  ('C010',V_SEQUPROD,C_ESTAB.CNPJ,NULL,LTRIM(TO_CHAR(V_OLDESTAB,'000')));
            END IF;
         END IF;
         V_NOVOEST := 'N';

         /*INSERE REGISTRO C860*/
         V_CONTREGA := NVL(V_CONTREGA,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'303'||LTRIM(TO_CHAR(V_OLDESTAB,'000'))||'05'||LPAD(RTRIM(V_CONTREGA),22,'0'),C_REGC860.C860);

         FOR C_REGC870 IN (SELECT (REG                                                             ||'|'||
                                   COD_ITEM                                                        ||'|'||
                                   CFOP                                                            ||'|'||
                                   LTRIM(REPLACE(TO_CHAR(VL_ITEM,  '999999999990.00'),'.',','))    ||'|'||
                                   VL_DESC                                                         ||'|'||
                                   CST_PIS                                                         ||'|'||
                                   LTRIM(REPLACE(TO_CHAR(VL_BC_PIS,'999999999990.00'),'.',','))    ||'|'||
                                   LTRIM(REPLACE(TO_CHAR(ALIQ_PIS, '990.00'),'.',','))             ||'|'||
                                   LTRIM(REPLACE(TO_CHAR(VL_PIS,   '999999999990.00'),'.',','))    ||'|'||
                                   CST_COFINS                                                      ||'|'||
                                   LTRIM(REPLACE(TO_CHAR(VL_BC_COFINS,'999999999990.00'),'.',',')) ||'|'||
                                   LTRIM(REPLACE(TO_CHAR(ALIQ_COFINS, '990.00'),'.',','))          ||'|'||
                                   LTRIM(REPLACE(TO_CHAR(VL_COFINS,   '999999999990.00'),'.',',')) ||'|'||
                                   COD_CTA                                                         ||'|') C870,
                                   COD_ITEM, VL_ITEM, CST_PIS, VL_BC_PIS, ALIQ_PIS, VL_PIS, VL_BC_COFINS,
                                   ALIQ_COFINS, VL_COFINS, SGUNIDADE, CDNATUREC, COD_CTA, CDCLASFISC
                             FROM (SELECT '|C870' AS REG, PR.CDARVPROD AS COD_ITEM, IT.CDFISCOPERSN AS CFOP,
                                          SUM(DECODE(IM.CDIMPOSTONFE,'50',IT.VRPRODUTO,0)) AS VL_ITEM,
                                          '0,00' AS VL_DESC,
                                          IT.CDSITUTRIBNFE AS CST_PIS,
                                          SUM(DECODE(IM.CDIMPOSTONFE,'50',DECODE(IT.CDFISCALIT,'1',IT.VRBASECALCIT,0),0)) AS VL_BC_PIS,
                                          MAX(DECODE(IM.CDIMPOSTONFE,'50',DECODE(IT.CDFISCALIT,'1',NVL(IT.VRALIQIT,0),0),0)) AS ALIQ_PIS,
                                          SUM(DECODE(IM.CDIMPOSTONFE,'50',DECODE(IT.CDFISCALIT,'1',CASE WHEN IT.IDORIGEMVENDA IN ('SAT','ECF')
                                              THEN IT.VRIMPOSTOIT ELSE IT.VRPRODUTO*IT.VRALIQIT/100 END,0),0)) AS VL_PIS,
                                          IT.CDSITUTRIBNFE AS CST_COFINS,
                                          SUM(DECODE(IM.CDIMPOSTONFE,'60',DECODE(IT.CDFISCALIT,'1',IT.VRBASECALCIT,0),0)) AS VL_BC_COFINS,
                                          MAX(DECODE(IM.CDIMPOSTONFE,'60',DECODE(IT.CDFISCALIT,'1',NVL(IT.VRALIQIT,0),0),0)) AS ALIQ_COFINS,
                                          SUM(DECODE(IM.CDIMPOSTONFE,'60',DECODE(IT.CDFISCALIT,'1',CASE WHEN IT.IDORIGEMVENDA IN ('SAT','ECF')
                                              THEN IT.VRIMPOSTOIT ELSE IT.VRPRODUTO*IT.VRALIQIT/100 END,0),0)) AS VL_COFINS,
                                          CONTCTBL_SPED('S',PR.CDPRODUTO,PV.CDTPOPERCNTREC,IT.CDFILIAL) AS COD_CTA,
                                          PR.SGUNIDADE, NVL(IT.CDNATUREC,RE.CDNATUREC) AS CDNATUREC, MAX(IT.CDCLASFISC) AS CDCLASFISC
                                     FROM ITEMVENOUTIMPOS IT, FILIAL FI, INSCRESTAD IE, PRODUTO PR, IMPOSTO IM,
                                          RELACPRODITGEN RE, PARAVEND PV, TMP_REGDADOSSPED_ECF_NF /*NOTAFISCAL*/ NF
                                    WHERE IT.CDFILIAL      = FI.CDFILIAL
                                      AND FI.CDEMPRESA     = P_CDEMPRESA
                                      AND FI.CDINSCESTA    = IE.CDINSCESTA
                                      AND FI.CDEMPRESA     = IE.CDEMPRESA
                                      AND REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.',''),'/',''),'-',''),'\','') = C_ESTAB.CNPJ
                                      AND REPLACE(REPLACE(REPLACE(REPLACE(IE.CDINSCESTA,'.',''),'/',''),'-',''),'\','') = C_ESTAB.INSCRICAO
                                      AND IT.CDSERIESAT    = C_REGC860.NR_SAT
                                      AND IT.CDFILIALNFFIS = NF.CDFILIAL
                                      AND IT.NRLANCTONFFIS = NF.NRLANCTONF
                                      AND NF.DTEMISSAO     = C_REGC860.DTEMISSAO
                                      AND NF.CDEMPRESA     = FI.CDEMPRESA
                                      AND NF.CDINSCESTA    = FI.CDINSCESTA
                                      AND NF.IDSISTEMA     = 'FIS'
                                      AND IT.CDPRODUTO     = PR.CDPRODUTO
                                      AND IT.CDIMPOSTO     = IM.CDIMPOSTO
                                      AND IM.CDIMPOSTONFE  IN ('50','60')
                                      AND RE.CDLIVROMAG(+) = P_LIVROMAG
                                      AND RE.CDPRODUTO(+)  = PR.CDPRODUTO
                                      AND IT.IDORIGEMVENDA IN ('SAT', 'ECF')
                                      AND PV.CDFILIAL      = IT.CDFILIAL
                                    GROUP BY PR.CDARVPROD, IT.CDFISCOPERSN, IT.CDSITUTRIBNFE, PR.SGUNIDADE, NVL(IT.CDNATUREC,RE.CDNATUREC),
                                          CONTCTBL_SPED('S',PR.CDPRODUTO,PV.CDTPOPERCNTREC,IT.CDFILIAL))) LOOP

            /*INSERE REGISTRO C870*/
            V_CONTREGA := NVL(V_CONTREGA,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'303'||LTRIM(TO_CHAR(V_OLDESTAB,'000'))||'05'||LPAD(RTRIM(V_CONTREGA),22,'0'),C_REGC870.C870);

            IF (C_REGC870.CST_PIS <> '05') AND (C_REGC860.DTEMISSAO >= '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,CDCONTCTBL)
               VALUES
                  ('C870'||C_REGC860.CDFILIAL,'M200',V_TMPBLCM,'',C_REGC870.VL_BC_PIS,C_REGC870.ALIQ_PIS,'P',C_REGC870.VL_PIS,C_REGC870.CST_PIS,C_REGC860.CDFILIAL,'','0',C_REGC870.VL_ITEM,C_REGC870.CDNATUREC,C_REGC870.COD_CTA);

               /*INSERE O VALOR REFERENTE AO COFINS 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,CDCONTCTBL)
               VALUES
                  ('C870'||C_REGC860.CDFILIAL,'M600',V_TMPBLCM,'',C_REGC870.VL_BC_COFINS,C_REGC870.ALIQ_COFINS,'P',C_REGC870.VL_COFINS,C_REGC870.CST_PIS,C_REGC860.CDFILIAL,'','0',C_REGC870.VL_ITEM,C_REGC870.CDNATUREC,C_REGC870.COD_CTA);
            END IF;

            V_PRODUTO:=NULL;

            /* CODIGO DO PRODUTO */
            SELECT MAX(CDCHAVEREG)
              INTO V_PRODUTO
              FROM TMP_REGDADOSSPED_PIS
             WHERE CDCHAVEREG = C_REGC870.COD_ITEM
               AND CDREGISTRO = '0200'
               AND CDESTAB = LTRIM(TO_CHAR(V_OLDESTAB,'000'));

            IF (V_PRODUTO IS NULL) AND (C_REGC870.COD_ITEM IS NOT NULL) THEN
               V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
               INSERT INTO TMP_REGDADOSSPED_PIS
                  (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB, CDCAMPO4)
               VALUES
                  ('0200', V_SEQUPROD, C_REGC870.COD_ITEM, NULL, LTRIM(TO_CHAR(V_OLDESTAB,'000')), C_REGC870.CDCLASFISC);
            END IF;

            V_UNID:= null;
            /* UNIDADE DE MEDIDA */
            SELECT MAX(CDCHAVEREG)
              INTO V_UNID
              FROM TMP_REGDADOSSPED_PIS
             WHERE CDCHAVEREG = C_REGC870.SGUNIDADE
               AND CDREGISTRO = '0190'
               AND CDESTAB = LTRIM(TO_CHAR(V_OLDESTAB,'000'));

            IF (V_UNID IS NULL) AND (C_REGC870.SGUNIDADE IS NOT NULL) THEN
               V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
               INSERT INTO TMP_REGDADOSSPED_PIS
                  (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
               VALUES
                  ('0190', V_SEQUPROD, C_REGC870.SGUNIDADE,NULL,LTRIM(TO_CHAR(V_OLDESTAB,'000')));
            END IF;

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

            IF (V_COD_CC IS NULL) AND (C_REGC870.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_REGC870.COD_CTA, NULL);
            END IF;
         END LOOP;
      END LOOP;
   END LOOP;
   P_COUNTA    := V_COUNTBCL0;
   P_CONTESTAB := V_CONTEST;
   RETURN(V_CONTREGA);
END;
/