CREATE OR REPLACE FUNCTION SPED_BLOCOD(
   P_CDIMPOSTO    IN IMPOSTO.CDIMPOSTO%TYPE,
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCESTA   IN EMPRESA.CDINSCESTA%TYPE,
   P_DTINICIAL    IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_LIVROMAG     IN LIVROMAGNETICO.CDLIVROMAG%TYPE,
   P_CDLIVROMAGADIC IN LIVROMAGNETICO.CDLIVROMAG%TYPE,
   P_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
   P_PERFIL       IN NOTAFISCAL.IDSTATUS%TYPE,
   P_VERSAO       IN VARCHAR2,
   P_TOTALIZA     IN NUMBER) RETURN VARCHAR
AS
   V_CONTREGD        NUMBER;
   V_VL_BC_ICMS_ST   NUMBER;
   V_VL_ICMS_ST      NUMBER;
   V_VL_IP           NUMBER;
   V_VL_RED_BC       NUMBER;
   V_VRDESCONTO      NUMBER;
   V_SEQUENCIAL      VARCHAR2(100);
   V_IDOUTROS        VARCHAR2(1);
   V_CDIMPOSTRIB     VARCHAR2(02);
   V_IPI             VARCHAR2(02);
   V_PIS             VARCHAR2(02);
   V_COFINS          VARCHAR2(02);
   V_ICMS            VARCHAR2(02);
   V_MOVIMENT        VARCHAR2(01);
   V_NRSEQUMAPAPU    VARCHAR2(10);
   V_LINHA           VARCHAR2(4000);
   V_COD_PAR         VARCHAR2(100);
   V_SEQUPROD        NUMBER;
   V_PRODUTO         VARCHAR2(100);
   V_UNID            VARCHAR2(100);
   V_COD_NAT         VARCHAR2(100);
   V_CDSITUCRT       VARCHAR2(01);
   V_CONTCTBL        VARCHAR2(100);
   V_D195            VARCHAR2(06);
   V_CODD195         VARCHAR2(06);
   V_TXTD195         VARCHAR2(500);
   V_REG_D197        VARCHAR2(1000);
   V_COD_OBS         VARCHAR2(1000);
   V_PARTICIPANTE    VARCHAR2(50);
   V_IDSOMAFECP      VARCHAR2(01);
   V_IDSOMAFECPST    VARCHAR2(01);
BEGIN
   V_SEQUPROD := NVL(P_TOTALIZA,0);

   BEGIN 
      SELECT IDSOMAFECP 
        INTO V_IDSOMAFECP
        FROM AJUSTEFECP
       WHERE CDEMPRESA    = P_CDEMPRESA
         AND CDINSCESTA   = P_CDINSCESTA
         AND CDIMPOSTO    = P_CDIMPOSTO;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_IDSOMAFECP := 'S';
   END;    
   V_IDSOMAFECP := NVL(V_IDSOMAFECP,'S');
   
   BEGIN 
      SELECT IDSOMAFECP 
        INTO V_IDSOMAFECPST
        FROM AJUSTEFECP
       WHERE CDEMPRESA    = P_CDEMPRESA
         AND CDINSCESTA   = P_CDINSCESTA
         AND CDIMPOSTO    = P_CDIMPOSTO;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_IDSOMAFECPST := 'S';
   END;    
   V_IDSOMAFECPST := NVL(V_IDSOMAFECPST,'S');

   BEGIN
      SELECT CDIMPOSTOLIVRO
      INTO V_IPI
      FROM TPIMPOSLIVRO
      WHERE SGIMPOSTOLIVRO LIKE '%IPI'
        AND CDLIVROMAG = P_LIVROMAG;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
      V_IPI := NULL;
   END;
   BEGIN
      SELECT CDIMPOSTOLIVRO
      INTO V_PIS
      FROM TPIMPOSLIVRO
      WHERE SGIMPOSTOLIVRO LIKE '%PIS'
        AND CDLIVROMAG = P_LIVROMAG;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_PIS := NULL;
   END;
   BEGIN
      SELECT CDIMPOSTOLIVRO
      INTO V_COFINS
      FROM TPIMPOSLIVRO
      WHERE SGIMPOSTOLIVRO LIKE '%COFINS'
        AND CDLIVROMAG = P_LIVROMAG;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
      V_COFINS := NULL;
   END;
   BEGIN
      SELECT EN.IDENCARGO
      INTO V_IDOUTROS
      FROM RELACENCARGO E, ENCARGO EN
      WHERE E.CDENCARGO = EN.CDENCARGO AND
            E.IDENCARGODOS LIKE '%OUTR%';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
      V_IDOUTROS    := NULL;
   END;
   BEGIN
      SELECT CDIMPOSTRIB
      INTO V_CDIMPOSTRIB
      FROM IMPOSTO
      WHERE CDIMPOSTO = P_CDIMPOSTO;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
      V_CDIMPOSTRIB    := NULL;
   END;
   /*VERIFICA SE A EMPRESA E DO SIMPLES NACIONAL, '3' NAO E DO SIMPLES, APENAS 1 E 2*/
   SELECT NVL(MAX(CDSITUCRT), '3') CDSITUCRT
     INTO V_CDSITUCRT
     FROM PARAMEMPRESA
    WHERE CDEMPRESA = P_CDEMPRESA;
   IF V_CDSITUCRT = '3' THEN
       BEGIN
          SELECT NRSEQUMAPAPU
            INTO V_NRSEQUMAPAPU
            FROM MAPAAPURAC M
           WHERE M.CDEMPRESA = P_CDEMPRESA
             AND M.CDINSCESTA = P_CDINSCESTA
             AND M.CDIMPOSTO = P_CDIMPOSTO
             AND M.DTINIPERIODO = P_DTINICIAL
             AND M.DTFIMPERIODO = P_DTFINAL;
       EXCEPTION
          WHEN NO_DATA_FOUND THEN
             V_NRSEQUMAPAPU := NULL;
       END;
   END IF;
   IF V_CDSITUCRT = '1' THEN
       BEGIN
          SELECT NRSEQUMAPAPU
            INTO V_NRSEQUMAPAPU
            FROM MAPAAPURACSN M
           WHERE M.CDEMPRESA = P_CDEMPRESA
             AND M.DTINIPERIODO = P_DTINICIAL
             AND M.DTFIMPERIODO = P_DTFINAL;
       EXCEPTION
          WHEN NO_DATA_FOUND THEN
             V_NRSEQUMAPAPU := NULL;
       END;
   END IF;
   V_LINHA := NULL;
   IF V_CDSITUCRT IN ('2','3') THEN
      FOR C_REGD100 IN (SELECT NF.REG                 || '|' ||
                               NF.IND_OPER            || '|' ||
                               NF.IND_EMIT            || '|' AS PARTE_1,
                               --REPLACE(REPLACE(REPLACE(REPLACE(NF.COD_PAR,'.'),'-'),'/'),' ') || '|' ||
                               NF.COD_MOD             || '|' ||
                               DECODE(NF.IDSTATUS,'C','02',DECODE(NF.IDREGESPECIAL,'S','08',CASE WHEN DECODE(NF.IDENTRSAIDOP,'E',NF.DTENTRSAID,NF.DTEMISSAO) < P_DTINICIAL THEN '01' ELSE '00' END)) || '|' ||
							          NF.SER                 || '|' ||
                               NF.SUB                 || '|' ||
                               NF.NUM_DOC             || '|' ||
                               NF.CHV_CTE             || '|' ||
                               NF.DT_DOC              || '|' ||
                               NF.DT_A_P              || '|' ||
                               NF.TP_CT_e             || '|' ||
                               NF.CHV_CTE_REF         || '|' ||
                               REPLACE(LTRIM(TO_CHAR(ROUND(NF.VL_DOC,2), '999999999990.00')), '.', ',')    || '|' AS PARTE_3,
                               NF.IND_FRT             || '|' ||
                               REPLACE(LTRIM(TO_CHAR(ROUND(NF.VL_SERV,2), '999999999990.00')), '.', ',')    || '|' ||
                               REPLACE(LTRIM(TO_CHAR(ROUND(NF.VL_BC_ICMS,2), '999999999990.00')), '.', ',')    || '|' ||
                               REPLACE(LTRIM(TO_CHAR(ROUND(NF.VL_ICMS,2), '999999999990.00')), '.', ',')    || '|' ||
                               REPLACE(LTRIM(TO_CHAR(ROUND(NF.VL_NT,2), '999999999990.00')), '.', ',')    || '|' ||
                               NF.COD_INF             || '|' ||
                               NF.COD_CTA             || '|' ||
                               CASE WHEN TO_NUMBER(P_VERSAO) >= 12 THEN COD_MUN_ORIG ||'|' ELSE '' END ||
                               CASE WHEN TO_NUMBER(P_VERSAO) >= 12 THEN COD_MUN_DEST ||'|' ELSE '' END  AS PARTE_5,
                               NF.CDFILIAL, NF.NRLANCTONF, NF.DTEMISSAO, NF.DTENTRSAID, NF.IDSTATUS,NF.IDREGESPECIAL, NF.IDENTRSAIDOP, NF.CDTIPOOPER,
                               NF.VL_SERV, NF.COD_MOD, NF.COD_PAR, NF.IDORIGDESTNF, NF.COD_CTA
                          FROM (SELECT '|D100' AS REG,
                                       DECODE(N.IDENTRSAIDOP, 'E', '0','1') AS IND_OPER,
                                       DECODE(N.IDENTRSAIDOP, 'E', DECODE(N.IDSTATUS,'E','0','1'), '0') AS IND_EMIT,
                                       N.IDORIGDESTNF||DECODE(N.IDORIGDESTNF, 'F', N.CDFORNECED, DECODE(N.IDORIGDESTNF, 'C', N.CDCLIENTE , DECODE(N.IDENTRSAIDOP, 'E', N.CDFILIEMIT, N.CDFILIRECE))) AS COD_PAR,
                                       N.CDMODDOCFISC AS COD_MOD,
                                       CASE WHEN LENGTH(N.NRACESSONFE) = '44' THEN SUBSTR(NRACESSONFE,23,3) ELSE REPLACE(REPLACE(DECODE(N.IDENTRSAIDOP, 'E', N.CDSERIEENTR, N.CDSERIE),'-',''),'.','') END AS SER,
                                       '' AS SUB,
                                       LTRIM(N.NRNOTAFISC, '0') AS NUM_DOC,
                                       CASE WHEN N.CDMODDOCFISC IN ('57','67') THEN N.NRACESSONFE ELSE '' END AS CHV_CTE,
                                       TO_CHAR(N.DTEMISSAO,'ddmmyyyy')  AS DT_DOC,
                                       TO_CHAR(N.DTENTRSAID,'ddmmyyyy') AS DT_A_P,
                                       N.CDTIPOCTE AS TP_CT_e,
                                       '' AS CHV_CTE_REF,
                                       SUM(ROUND(I.VRCONTABILNF,2)) AS VL_DOC,
                                       CASE WHEN N.IDPGTOFRETE IN ('0','1','3','4') THEN '1'
                                            WHEN N.IDPGTOFRETE = '2' THEN '2'
                                            ELSE '9' END AS IND_FRT,
                                       SUM(ROUND(I.VRCONTABILNF,2)) AS VL_SERV,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0),2)) AS VL_BC_ICMS,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF+CASE WHEN V_IDSOMAFECP = 'S' OR N.IDENTRSAIDOP = 'E' THEN NVL(I.VRADICFECP,0) ELSE 0 END,0),2))  AS VL_ICMS,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,'0'),2)) AS VL_NT,
                                       '' AS COD_INF,
                                       MAX((SELECT MAX(CONTCTBL_SPED(N.IDENTRSAIDOP,I.CDPRODUTO,N.CDTIPOOPER,N.CDFILIEMIT))
                                             FROM ITEMPRODUTO I
                                            WHERE I.CDFILIAL   = N.CDFILIAL
                                              AND I.NRLANCTONF = N.NRLANCTONF)) AS COD_CTA,
                                       N.CDFILIAL, N.NRLANCTONF, N.DTEMISSAO, N.DTENTRSAID, N.IDSTATUS,T.IDREGESPECIAL,
                                       N.IDENTRSAIDOP, N.CDTIPOOPER, N.IDORIGDESTNF,
                                       CASE WHEN SUBSTR(I.CDFISCOPERNF,1,1) IN ('3','7') THEN '9999999' ELSE N.CDIBGEORIG END AS COD_MUN_ORIG,
                                       CASE WHEN SUBSTR(I.CDFISCOPERNF,1,1) IN ('3','7') THEN '9999999' ELSE N.CDIBGEDEST END AS COD_MUN_DEST
                                  FROM IMPOSTONF I, NOTAFISCAL N, IMPOSTO IM, SERIENF SS, SERIEENTR SE , TIPOOPERAC T
                                 WHERE I.CDFILIAL        = N.CDFILIAL
                                   AND I.NRLANCTONF      = N.NRLANCTONF
                                   AND N.CDMODDOCFISC    IN ('07', '08', '09', '10', '11', '26', '27', '57', '67')
                                   AND SE.CDSERIEENTR(+) = N.CDSERIEENTR
                                   AND SS.CDEMPRESA(+)   = N.CDEMPRESA
                                   AND SS.CDINSCESTA(+)  = N.CDINSCESTA
                                   AND SS.CDINSCMUNI(+)  = N.CDINSCMUNI
                                   AND SS.CDSERIE(+)     = N.CDSERIE
                                   AND I.NRSEQUMAPAPU    = V_NRSEQUMAPAPU
                                   AND IM.CDIMPOSTONFE   = P_CDIMPOSTO
                                   AND I.CDIMPOSTO       = IM.CDIMPOSTO
                                   AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                   AND N.IDSTATUS        IN ('E','A')
                                   AND T.IDENTRSAIDOP    = N.IDENTRSAIDOP
                                   AND T.CDTIPOOPER      = N.CDTIPOOPER
                                 GROUP BY DECODE(N.IDENTRSAIDOP, 'E', '0','1'),
                                       DECODE(N.IDENTRSAIDOP, 'E', DECODE(N.IDSTATUS,'E','0','1'), '0'),
                                       N.IDORIGDESTNF||DECODE(N.IDORIGDESTNF, 'F', N.CDFORNECED, DECODE(N.IDORIGDESTNF, 'C', N.CDCLIENTE , DECODE(N.IDENTRSAIDOP, 'E', N.CDFILIEMIT, N.CDFILIRECE))),
                                       N.CDMODDOCFISC, N.IDPGTOFRETE,
                                       CASE WHEN LENGTH(N.NRACESSONFE) = '44' THEN SUBSTR(NRACESSONFE,23,3) ELSE REPLACE(REPLACE(DECODE(N.IDENTRSAIDOP, 'E', N.CDSERIEENTR, N.CDSERIE),'-',''),'.','') END ,
                                       LTRIM(N.NRNOTAFISC, '0'),CASE WHEN N.CDMODDOCFISC IN ('57','67') THEN N.NRACESSONFE ELSE '' END, TO_CHAR(N.DTEMISSAO,'ddmmyyyy'), TO_CHAR(N.DTENTRSAID,'ddmmyyyy'),
                                       N.CDTIPOCTE, N.CDFILIAL, N.NRLANCTONF, N.DTEMISSAO, N.DTENTRSAID, N.IDSTATUS,T.IDREGESPECIAL, N.IDENTRSAIDOP, N.CDTIPOOPER, N.IDORIGDESTNF,
                                       CASE WHEN SUBSTR(I.CDFISCOPERNF,1,1) IN ('3','7') THEN '9999999' ELSE N.CDIBGEORIG END,
                                       CASE WHEN SUBSTR(I.CDFISCOPERNF,1,1) IN ('3','7') THEN '9999999' ELSE N.CDIBGEDEST END
                                 UNION ALL
                                 /***********NOTA CANCELADA************/
                                 SELECT '|D100' AS REG,
                                       DECODE(N.IDENTRSAIDOP, 'E', '0','1') AS IND_OPER,
                                       DECODE(N.IDENTRSAIDOP, 'E', DECODE(N.IDSTATUS,'E','0','1'), '0') AS IND_EMIT,
                                       N.IDORIGDESTNF||DECODE(N.IDORIGDESTNF, 'F', N.CDFORNECED, DECODE(N.IDORIGDESTNF, 'C', N.CDCLIENTE , DECODE(N.IDENTRSAIDOP, 'E', N.CDFILIEMIT, N.CDFILIRECE))) AS COD_PAR,
                                       N.CDMODDOCFISC AS COD_MOD,
                                       CASE WHEN LENGTH(N.NRACESSONFE) = '44' THEN SUBSTR(NRACESSONFE,23,3) ELSE REPLACE(REPLACE(DECODE(N.IDENTRSAIDOP, 'E', N.CDSERIEENTR, N.CDSERIE),'-',''),'.','') END AS SER,
                                       '' AS SUB,
                                       LTRIM(N.NRNOTAFISC, '0') AS NUM_DOC,
                                       '' AS CHV_CTE,
                                       TO_CHAR(N.DTEMISSAO,'ddmmyyyy')  AS DT_DOC,
                                       TO_CHAR(N.DTENTRSAID,'ddmmyyyy') AS DT_A_P,
                                       '' AS TP_CT_e,
                                       '' AS CHV_CTE_REF,
                                       SUM(N.VRNOTAFISC) AS VL_DOC,
                                       CASE WHEN N.IDPGTOFRETE IN ('0','1','3','4') THEN '1'
                                            WHEN N.IDPGTOFRETE = '2' THEN '2'
                                            ELSE '9' END AS IND_FRT,
                                       0 AS VL_SERV,
                                       0 AS VL_BC_ICMS,
                                       0 AS VL_ICMS,
                                       0 AS VL_NT,
                                       '' AS COD_INF,
                                       '' AS COD_CTA,
                                       N.CDFILIAL, N.NRLANCTONF, N.DTEMISSAO,
                                       N.DTENTRSAID, N.IDSTATUS,''AS IDREGESPECIAL,
                                       N.IDENTRSAIDOP, N.CDTIPOOPER, N.IDORIGDESTNF,
                                       '' AS COD_MUN_ORIG, '' AS COD_MUN_DEST
                                  FROM NOTAFISCAL N, SERIENF SS
                                 WHERE N.CDMODDOCFISC    IN ('07', '08', '09', '10', '11', '26', '27', '57', '67')
                                   AND N.DTEMISSAO BETWEEN P_DTINICIAL AND P_DTFINAL
                                   AND N.IDENTRSAIDOP    = 'S'
                                   AND SS.CDEMPRESA(+)   = N.CDEMPRESA
                                   AND SS.CDINSCESTA(+)  = N.CDINSCESTA
                                   AND SS.CDINSCMUNI(+)  = N.CDINSCMUNI
                                   AND SS.CDSERIE(+)     = N.CDSERIE
                                   AND N.IDSTATUS        = 'C'
                                   AND N.CDINSCESTA      = P_CDINSCESTA
                                   AND N.CDEMPRESA       = P_CDEMPRESA  
                                 GROUP BY DECODE(N.IDENTRSAIDOP, 'E', '0','1'),
                                       DECODE(N.IDENTRSAIDOP, 'E', DECODE(N.IDSTATUS,'E','0','1'), '0'),
                                       N.IDORIGDESTNF||DECODE(N.IDORIGDESTNF, 'F', N.CDFORNECED, DECODE(N.IDORIGDESTNF, 'C', N.CDCLIENTE , DECODE(N.IDENTRSAIDOP, 'E', N.CDFILIEMIT, N.CDFILIRECE))),
                                       N.CDMODDOCFISC, N.IDPGTOFRETE,
                                       CASE WHEN LENGTH(N.NRACESSONFE) = '44' THEN SUBSTR(NRACESSONFE,23,3) ELSE REPLACE(REPLACE(DECODE(N.IDENTRSAIDOP, 'E', N.CDSERIEENTR, N.CDSERIE),'-',''),'.','') END,
                                       LTRIM(N.NRNOTAFISC, '0'), TO_CHAR(N.DTEMISSAO,'ddmmyyyy'), TO_CHAR(N.DTENTRSAID,'ddmmyyyy'),
                                       N.CDFILIAL, N.NRLANCTONF, N.DTEMISSAO, N.DTENTRSAID, N.IDSTATUS,N.IDENTRSAIDOP,N.CDTIPOOPER,N.IDORIGDESTNF)NF) LOOP

         V_PARTICIPANTE := '';
         /* CODIGO DO PARTICIPANTE */
         SELECT MAX(CDCHAVEREG)
           INTO V_COD_PAR
           FROM TMP_REGDADOSSPED
          WHERE CDCHAVEREG = C_REGD100.COD_PAR
            AND CDREGISTRO = '0150';

         IF (V_COD_PAR IS NULL) AND (C_REGD100.COD_PAR IS NOT NULL) THEN
            V_PARTICIPANTE := RETORNA_PARTICIPANTE_SPED(C_REGD100.COD_PAR);
            IF V_PARTICIPANTE IS NULL THEN
               V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
               INSERT INTO TMP_REGDADOSSPED
                 (CDREGISTRO, CDCONTREGI, CDCHAVEREG)
               VALUES
                 ('0150', V_SEQUPROD, C_REGD100.COD_PAR);
            END IF;
         END IF;
         V_COD_PAR := NULL;

         V_PARTICIPANTE := REPLACE(REPLACE(REPLACE(REPLACE(NVL(V_PARTICIPANTE,C_REGD100.COD_PAR),'.'),'-'),'/'),' ')||'|';

         /* PLANO DE CONTAS REFERENCIAL */
         SELECT MAX(CDCHAVEREG)
           INTO V_CONTCTBL
           FROM TMP_REGDADOSSPED
          WHERE CDCHAVEREG = C_REGD100.COD_CTA
            AND CDREGISTRO = '0500';

         IF (V_CONTCTBL IS NULL) AND (C_REGD100.COD_CTA IS NOT NULL) THEN
            V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
            INSERT INTO TMP_REGDADOSSPED
               (CDREGISTRO, CDCONTREGI, CDCHAVEREG)
            VALUES
               ('0500', V_SEQUPROD, C_REGD100.COD_CTA);
         END IF;
         V_CONTCTBL := NULL;

         /* resgata o valor total de descontos por nota fiscal */
         SELECT SUM(I.VRITEMENCA)
           INTO V_VRDESCONTO
           FROM ITEMENCARGO I, ENCARGO E
          WHERE E.IDENCARGO = 'D'
            AND I.CDENCARGO = E.CDENCARGO
            AND I.CDFILIAL = C_REGD100.CDFILIAL
            AND I.NRLANCTONF = C_REGD100.NRLANCTONF;

         V_LINHA := C_REGD100.PARTE_1||
                    V_PARTICIPANTE ||
                    C_REGD100.PARTE_3||
                    REPLACE(LTRIM(TO_CHAR(ROUND(V_VRDESCONTO,2), '999999999990.00')), '.', ',') || '|' ||
                    C_REGD100.PARTE_5;
         V_CONTREGD :=  NVL(V_CONTREGD,0) + 1;
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            ('42'||LPAD(RTRIM(V_CONTREGD), 8, '0'), V_LINHA, P_NRSEQARQMAG);

         /*REGISTRO D101 - INFORMACAO COMPLEMENTAR DOS DOCUMENTOS FISCAIS QUANDO DAS
         OPERACOES INTERESTADUAIS DESTINADAS A CONSUMIDOR FINAL NAO CONTRIBUINTE EC 87/15*/
         FOR C_REGD101 IN (SELECT '|D101'                                                           || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(VRFCP,       '999999999990.00')), '.', ',') || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(VRDIFALDEST, '999999999990.00')), '.', ',') || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(VRDIFALORIG, '999999999990.00')), '.', ',') || '|'  AS ARQD101
                             FROM (SELECT SUM(ROUND(DECODE(C_REGD100.IDENTRSAIDOP,'S',I.VRIMPOFCP,DECODE(C.IDTIPOCFOP,'D',I.VRIMPOFCP,0)),2)) AS VRFCP,
                                          SUM(ROUND(DECODE(C_REGD100.IDENTRSAIDOP,'S',I.VRIMPOPARTDEST,DECODE(C.IDTIPOCFOP,'D',I.VRIMPOPARTDEST,0)),2)) AS VRDIFALDEST,
                                          SUM(ROUND(DECODE(C_REGD100.IDENTRSAIDOP,'S',I.VRIMPOPARTREME,DECODE(C.IDTIPOCFOP,'D',I.VRIMPOPARTREME,0)),2)) AS VRDIFALORIG
                                     FROM ITEMPROIMPOS I, IMPOSTO IM, CFOPPADRAO C
                                    WHERE I.CDFILIAL      = C_REGD100.CDFILIAL
                                      AND I.NRLANCTONF    = C_REGD100.NRLANCTONF
                                      AND I.CDIMPOSTO     = IM.CDIMPOSTO
                                      AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                                      AND I.CDFISCOPERIT  = C.CDCFOPPADRAO
                                      AND C_REGD100.COD_MOD IN ('57','67')
                                      AND C_REGD100.IDENTRSAIDOP = 'S')
                            WHERE VRFCP > 0 OR VRDIFALDEST > 0 OR VRDIFALORIG > 0) LOOP

            V_CONTREGD :=  NVL(V_CONTREGD,0) + 1;
            INSERT INTO ARQMAGNETICO
               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
               ('42'||LPAD(RTRIM(V_CONTREGD), 8, '0'), C_REGD101.ARQD101 , P_NRSEQARQMAG);
         END LOOP;

         V_LINHA := NULL;
         IF (C_REGD100.IDENTRSAIDOP = 'S') AND (C_REGD100.COD_MOD = '07') THEN
            V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER('1'))),3,'0');
            FOR C_REGD110 IN (SELECT CDARVPROD    || '|' ||
                                     REPLACE(LTRIM(TO_CHAR(ROUND(C_REGD100.VL_SERV,2), '999999999990.00')), '.', ',')    || '|' ||
                                     '0,00'       || '|' AS PART_2, P.CDPRODUTO, P.SGUNIDADE, I.CDPRODNCMTRANS AS CDCLASFISC
                                FROM ITEMPRODUTO I, PRODUTO P
                               WHERE I.CDFILIAL = C_REGD100.CDFILIAL
                                 AND I.NRLANCTONF = C_REGD100.NRLANCTONF
                                 AND I.CDPRODUTO = P.CDPRODUTO ) LOOP

               V_CONTREGD :=  NVL(V_CONTREGD,0) + 1;
               INSERT INTO ARQMAGNETICO
                  (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
               VALUES
                  ('42'||LPAD(RTRIM(V_CONTREGD), 8, '0'), '|D110'|| '|' ||V_SEQUENCIAL|| '|' ||C_REGD110.PART_2, P_NRSEQARQMAG);
               V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_SEQUENCIAL))+1)), 3,'0');

               /* UNIDADE DE MEDIDA */
               SELECT MAX(CDCHAVEREG)
                 INTO V_UNID
                 FROM TMP_REGDADOSSPED
                WHERE CDCHAVEREG = C_REGD110.SGUNIDADE
                  AND CDREGISTRO = '0190';
               IF (V_UNID IS NULL) AND (C_REGD110.SGUNIDADE IS NOT NULL) THEN
                  V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
                  INSERT INTO TMP_REGDADOSSPED
                     (CDREGISTRO, CDCONTREGI, CDCHAVEREG)
                  VALUES
                     ('0190', V_SEQUPROD, C_REGD110.SGUNIDADE);
               END IF;
               V_UNID := NULL;

               /* CODIGO DO PRODUTO */
               SELECT MAX(CDCHAVEREG)
                 INTO V_PRODUTO
                 FROM TMP_REGDADOSSPED
                WHERE CDCHAVEREG = C_REGD110.CDPRODUTO
                  AND CDREGISTRO = '0200';
               IF (V_PRODUTO IS NULL) AND (C_REGD110.CDPRODUTO IS NOT NULL) THEN
                  V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
                  INSERT INTO TMP_REGDADOSSPED
                    (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO3)
                  VALUES
                    ('0200', V_SEQUPROD, C_REGD110.CDPRODUTO, C_REGD110.CDCLASFISC);
               END IF;
               V_PRODUTO := NULL;
            END LOOP;
         END IF;

         /* valor reducao de base de calculo por nf */
         SELECT NVL(SUM(ROUND(I.VRCONTABILNF,2)),0)
           INTO V_VL_RED_BC
           FROM IMPOSTONF I, IMPOSTO IM
          WHERE CDFILIAL   = C_REGD100.CDFILIAL
            AND NRLANCTONF = C_REGD100.NRLANCTONF
            AND IM.CDIMPOSTONFE  = P_CDIMPOSTO
            AND IM.CDIMPOSTO  = I.CDIMPOSTO
            AND IDIMPOSTONF = '2';

         FOR C_REGD190 IN (SELECT ('|D190'          || '|' ||
                                  T.CDSITUTRIBNF   || '|' ||
                                  T.CDFISCOPERNF   || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VRALIQNF+NVL(VRALIQFECP,0),0),2),'999999999990.00')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VL_OPR,0),2), '999999999990.00')), '.', ',')      || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VL_BC_ICMS,0),2), '999999999990.00')), '.', ',')  || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VL_ICMS,0),2), '999999999990.00')), '.', ',')     || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(V_VL_RED_BC,0),2), '999999999990.00')), '.', ',')   || '|' ||
                                  T.COD_OBS        || '|' ) ARQ_D190, T.VL_ICMS, T.CDFISCOPERNF, T.COD_OBS, T.DSCFOPLMAG
                             FROM (SELECT I.VRALIQNF AS VRALIQNF,
                                          CASE WHEN V_IDSOMAFECP = 'S' OR C_REGD100.IDENTRSAIDOP = 'E' THEN I.VRADICALIQFECP ELSE 0 END AS VRALIQFECP,
                                          REPLACE(I.CDFISCOPERNF,'.','') AS CDFISCOPERNF,
                                          LPAD(I.CDSITUTRIBNF, 3, '0') AS CDSITUTRIBNF,
                                          NVL(SUM(ROUND(I.VRCONTABILNF,2)),0) AS VL_OPR,
                                          NVL(SUM(ROUND(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0),2)),0) AS VL_BC_ICMS,
                                          NVL(SUM(ROUND(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF+CASE WHEN V_IDSOMAFECP = 'S' OR C_REGD100.IDENTRSAIDOP = 'E' THEN NVL(I.VRADICFECP,0) ELSE 0 END,0),2)),0) AS VL_ICMS,
                                          C.CDTIPOOPER||C.CDCFOPLMAG AS COD_OBS, C.DSCFOPLMAG
                                     FROM IMPOSTONF I, IMPOSTO IM, CFOPLIVROMAGTPOP C
                                    WHERE I.CDFILIAL        = C_REGD100.CDFILIAL
                                      AND I.NRLANCTONF      = C_REGD100.NRLANCTONF
                                      AND IM.CDIMPOSTONFE   = P_CDIMPOSTO
                                      AND IM.CDIMPOSTO      = I.CDIMPOSTO
                                      AND I.IDIMPOSTONF     = 1
                                      AND I.NRSEQUMAPAPU    = V_NRSEQUMAPAPU
                                      AND C.IDENTRSAIDOP(+) = C_REGD100.IDENTRSAIDOP
                                      AND C.CDTIPOOPER(+)   = C_REGD100.CDTIPOOPER
                                      AND C.CDCFOPLMAG(+)   = I.CDFISCOPERNF
                                      AND C.CDLIVROMAG(+)   = P_CDLIVROMAGADIC
                                    GROUP BY I.VRALIQNF, I.VRADICALIQFECP, REPLACE(I.CDFISCOPERNF,'.',''), LPAD(I.CDSITUTRIBNF, 3, '0'), C.CDTIPOOPER||C.CDCFOPLMAG, C.DSCFOPLMAG) T) LOOP
            V_VL_BC_ICMS_ST := 0;
            V_VL_ICMS_ST := 0;
            V_VL_RED_BC := 0;
            V_VL_IP := 0;

              V_COD_OBS := NULL;
            SELECT MAX(CDCHAVEREG)
              INTO V_COD_OBS
              FROM TMP_REGDADOSSPED
             WHERE CDCHAVEREG = C_REGD190.COD_OBS
               AND CDREGISTRO = '0460';

            IF (V_COD_OBS IS NULL) AND (C_REGD190.COD_OBS IS NOT NULL) THEN
               V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
               INSERT INTO TMP_REGDADOSSPED
                  (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
               VALUES
                  ('0460', V_SEQUPROD, C_REGD190.COD_OBS, C_REGD190.DSCFOPLMAG);
            END IF;
            V_CONTREGD := NVL(V_CONTREGD,0) + 1;
            INSERT INTO ARQMAGNETICO
               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
               ('42'||LPAD(RTRIM(V_CONTREGD), 8, '0'), C_REGD190.ARQ_D190, P_NRSEQARQMAG);
         END LOOP;

         V_D195 := 'N';
         /* GERACAO DOS REGISTROS REFERENTES A AJUSTES NO DOCUMENTO*/
         FOR C_REGD197 IN (SELECT COD_AJ, DESCR_COMPL_AJ, COD_ITEM, IDTIPOITMAPR,
                                  REPLACE(LTRIM(TO_CHAR(ROUND(VL_BC_ICMS,2),'999999999990.00')), '.', ',') AS VL_BC_ICMS,
                                  REPLACE(LTRIM(TO_CHAR(ROUND(ALIQ_ICMS+NVL(ALIQ_FECP,0),2), '999999999990.00')), '.', ',') AS ALIQ_ICMS,
                                  REPLACE(LTRIM(TO_CHAR(ROUND(VL_ICMS,2),   '999999999990.00')), '.', ',') AS VL_ICMS,
                                  REPLACE(LTRIM(TO_CHAR(ROUND(VL_OUTROS,2), '999999999990.00')), '.', ',') AS VL_OUTROS,
                                  CDCFOPPADRAO, CDCLASFISC, CDPRODUTO
                             FROM (SELECT IM.CDAJUSTEITMAPA AS COD_AJ, IM.DSITEMMAPR AS DESCR_COMPL_AJ, '' AS COD_ITEM,
                                          0 AS VL_BC_ICMS, 0 AS ALIQ_ICMS, 0 AS ALIQ_FECP,
                                          IM.VRITEMMAPR AS VL_ICMS,
                                          IM.VROUTROSITMAPR AS VL_OUTROS, IM.IDTIPOITMAPR,
                                          IM.CDCFOPPADRAO AS CDCFOPPADRAO, '' AS CDCLASFISC, '' AS CDPRODUTO
                                     FROM ITMAPARECUP IM
                                    WHERE IM.CDFILIAL     = C_REGD100.CDFILIAL
                                      AND IM.NRLANCTONF   = C_REGD100.NRLANCTONF
                                      AND IM.IDCOMPOEC197 = 'D'
                                    UNION ALL
                                   SELECT IM.CDAJUSTEITMAPA AS COD_AJ, IM.DSITEMMAPR AS DESCR_COMPL_AJ,
                                          NVL(PA.CDARVPROD,PR.CDARVPROD) AS COD_ITEM,
                                          SUM(ROUND(DECODE(DT.IDSUBSTSOLIDST,'S',IT.VRBASECALCIT,DECODE(IT.CDFISCALIT,'1',IT.VRBASECALCIT,0)),2)) AS VL_BC_ICMS,
                                          DECODE(DT.IDSUBSTSOLIDST,'S',IT.VRALIQITEM,DECODE(IT.CDFISCALIT,'1',IT.VRALIQITEM,0)) AS ALIQ_ICMS,
                                          CASE WHEN V_IDSOMAFECP = 'S' OR C_REGD100.IDENTRSAIDOP = 'E'
                                               THEN DECODE(DT.IDSUBSTSOLIDST,'S',IT.VRADICALIQFECP,DECODE(IT.CDFISCALIT,'1',IT.VRADICALIQFECP,0))
                                               ELSE 0 END AS ALIQ_FECP,
                                          SUM(ROUND(DECODE(DT.IDSUBSTSOLIDST,'S',DT.VRIMPOSTOIT,
                                                                                 IT.VRIMPOSTOIT + CASE WHEN V_IDSOMAFECP = 'S' OR C_REGD100.IDENTRSAIDOP = 'E'
                                                                                                       THEN NVL(IT.VRADICFECP,0) ELSE 0 END),2)) AS VL_ICMS,
                                          SUM(ROUND(IM.VROUTROSITMAPR,2)) AS VL_OUTROS, IM.IDTIPOITMAPR, '' AS CDCFOPPADRAO, IP.CDPRODNCMTRANS AS CDCLASFISC,
                                          NVL(PA.CDPRODUTO,PR.CDPRODUTO) AS CDPRODUTO
                                     FROM ITMAPARECUP IM, IMPOSTO IM, ITEMPROIMPOS IT, DETITMAPARECUP DT, PRODUTO PA, PRODUTO PR, ITEMPRODUTO IP
                                    WHERE IM.CDFILIAL     = C_REGD100.CDFILIAL
                                      AND IM.NRLANCTONF   = C_REGD100.NRLANCTONF
                                      AND IM.IDCOMPOEC197 = 'I'
                                      AND IM.CDFILIAL     = IT.CDFILIAL
                                      AND IM.NRLANCTONF   = IT.NRLANCTONF
                                      AND IT.CDIMPOSTO    = IM.CDIMPOSTO
                                      AND IM.CDIMPOSTONFE IN ('00','80')
                                      AND IT.CDIMPOSTO    = DT.CDIMPOSTO
                                      AND IM.NRSEQUMAPAPU = DT.NRSEQUMAPAPU
                                      AND IM.IDTIPOITMAPR = DT.IDTIPOITMAPR
                                      AND IM.NRSEQITMAPR  = DT.NRSEQITMAPR
                                      AND IT.CDFILIAL     = DT.CDFILIAL
                                      AND IT.NRLANCTONF   = DT.NRLANCTONF
                                      AND IT.NRSEQITPROD  = DT.NRSEQITPROD
                                      AND IT.NRITPROIMPOS = DT.NRITPROIMPOS
                                      AND IT.CDFILIAL     = IP.CDFILIAL
                                      AND IT.NRLANCTONF   = IP.NRLANCTONF
                                      AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                                      AND DT.CDFILIAL     = IP.CDFILIAL
                                      AND DT.NRLANCTONF   = IP.NRLANCTONF
                                      AND DT.NRSEQITPROD  = IP.NRSEQITPROD
                                      AND PR.CDPRODUTO    = IP.CDPRODUTO
                                      AND PR.CDPRODESTO   = PA.CDPRODUTO(+)
                                      AND DT.IDTIPOITMAPR <> 'VI'
                                      AND ((IT.NRITPROIMPOS = '1' AND IT.CDFISCALIT = '1') OR (IT.NRITPROIMPOS = '3') OR (DT.IDSUBSTSOLIDST = 'S'))
                                    GROUP BY IM.CDAJUSTEITMAPA, IM.DSITEMMAPR, IT.CDFISCALIT, IT.VRALIQITEM, IT.VRADICALIQFECP, DT.IDSUBSTSOLIDST,
                                          NVL(PA.CDARVPROD,PR.CDARVPROD), IM.IDTIPOITMAPR, IP.CDPRODNCMTRANS, NVL(PA.CDPRODUTO,PR.CDPRODUTO)
                                    UNION ALL
                                   SELECT IM.CDAJUSTEITMAPA AS COD_AJ, IM.DSITEMMAPR AS DESCR_COMPL_AJ,
                                          NVL(PA.CDARVPROD,PR.CDARVPROD) AS COD_ITEM,
                                          SUM(DT.VROUTBASE) AS VL_BC_ICMS,
                                          NVL(DT.VROUTALIQ,0) AS ALIQ_ICMS,
                                          0 AS ALIQ_FECP, 0 AS VL_ICMS,
                                          SUM(ROUND(DT.VROUTROSIMPIT,2)) AS VL_OUTROS, IM.IDTIPOITMAPR,
                                          IM.CDCFOPPADRAO, IP.CDPRODNCMTRANS AS CDCLASFISC,
                                          NVL(PA.CDPRODUTO,PR.CDPRODUTO) AS CDPRODUTO
                                     FROM ITMAPARECUP IM, DETITMAPARECUP DT, PRODUTO PA, PRODUTO PR, ITEMPRODUTO IP
                                    WHERE IM.CDFILIAL     = C_REGD100.CDFILIAL
                                      AND IM.NRLANCTONF   = C_REGD100.NRLANCTONF
                                      AND IM.IDCOMPOEC197 = 'I'
                                      AND IM.IDTIPOITMAPR = 'VI'
                                      AND IM.NRSEQUMAPAPU = DT.NRSEQUMAPAPU
                                      AND IM.IDTIPOITMAPR = DT.IDTIPOITMAPR
                                      AND IM.NRSEQITMAPR  = DT.NRSEQITMAPR
                                      AND DT.CDFILIAL     = IP.CDFILIAL
                                      AND DT.NRLANCTONF   = IP.NRLANCTONF
                                      AND DT.NRSEQITPROD  = IP.NRSEQITPROD
                                      AND PR.CDPRODUTO    = IP.CDPRODUTO
                                      AND PR.CDPRODESTO   = PA.CDPRODUTO(+)
                                    GROUP BY IM.CDAJUSTEITMAPA, IM.DSITEMMAPR,
                                          NVL(PA.CDARVPROD,PR.CDARVPROD), DT.VROUTALIQ,
                                          IM.IDTIPOITMAPR, IM.CDCFOPPADRAO, IP.CDPRODNCMTRANS,
                                          NVL(PA.CDPRODUTO,PR.CDPRODUTO))
                            ORDER BY SUBSTR(COD_AJ,8,3), IDTIPOITMAPR, CDCFOPPADRAO, CDCLASFISC, CDPRODUTO) LOOP

             BEGIN
                SELECT LPAD(NVL(IT.CDITEMVARIAVEL,'0')||DECODE(C_REGD197.CDCFOPPADRAO,'','',SUBSTR(C_REGD197.DESCR_COMPL_AJ,2,3)),6,'0') AS COD_OBS,
                       TRIM(IT.DSAJUSTE) AS TXT
                  INTO V_CODD195, V_TXTD195
                  FROM ITEMVARAJEFD IT
                 WHERE IT.SGESTADO       = SUBSTR(C_REGD197.COD_AJ,1,2)
                   AND IT.CDITEMVARIAVEL = SUBSTR(C_REGD197.COD_AJ,8,3);
             EXCEPTION
                WHEN NO_DATA_FOUND THEN
                   V_CODD195 := '';
                   V_TXTD195 := '';
             END;

            /* CODIGO DO PRODUTO */
            SELECT MAX(CDCHAVEREG)
              INTO V_PRODUTO
              FROM TMP_REGDADOSSPED
             WHERE CDCHAVEREG = C_REGD197.CDPRODUTO
               AND CDREGISTRO = '0200';

            IF (V_PRODUTO IS NULL) AND (C_REGD197.CDPRODUTO IS NOT NULL) THEN
               V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
               INSERT INTO TMP_REGDADOSSPED
                 (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO3)
               VALUES
                 ('0200', V_SEQUPROD, C_REGD197.CDPRODUTO, C_REGD197.CDCLASFISC);
            END IF;
            V_PRODUTO := NULL;

             V_COD_OBS := NULL;
             SELECT MAX(CDCHAVEREG)
               INTO V_COD_OBS
               FROM TMP_REGDADOSSPED
              WHERE CDCHAVEREG = V_CODD195
                AND CDREGISTRO = '0460';

             IF (V_COD_OBS IS NULL) THEN
                V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
                INSERT INTO TMP_REGDADOSSPED
                   (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
                VALUES
                   ('0460', V_SEQUPROD, NVL(V_CODD195,SUBSTR(C_REGD197.COD_AJ,8,3)), V_TXTD195);
             END IF;

             IF (V_D195 IS NULL) OR (V_D195 <> SUBSTR(C_REGD197.COD_AJ,8,3)||CASE WHEN C_REGD197.CDCFOPPADRAO IS NOT NULL THEN SUBSTR(C_REGD197.DESCR_COMPL_AJ,2,3) ELSE '' END) THEN
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   ('42'||LPAD(RTRIM(V_CONTREGD), 8, '0'), '|D195'  || '|' || V_CODD195 || '|' || ''  || '|', P_NRSEQARQMAG);
                V_CONTREGD := NVL(V_CONTREGD,0) + 1;
             END IF;

             V_REG_D197 := '|D197'                   || '|' ||
                           C_REGD197.COD_AJ          || '|' ||
                           C_REGD197.DESCR_COMPL_AJ  || '|' ||
                           C_REGD197.COD_ITEM        || '|' ||
                           C_REGD197.VL_BC_ICMS      || '|' ||
                           C_REGD197.ALIQ_ICMS       || '|' ||
                           C_REGD197.VL_ICMS         || '|' ||
                           C_REGD197.VL_OUTROS       || '|' ;
             INSERT INTO ARQMAGNETICO
               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
               ('42'||LPAD(RTRIM(V_CONTREGD), 8, '0'), V_REG_D197, P_NRSEQARQMAG);
             V_CONTREGD := NVL(V_CONTREGD,0) + 1;

             V_D195 := SUBSTR(C_REGD197.COD_AJ,8,3)||CASE WHEN C_REGD197.CDCFOPPADRAO IS NOT NULL THEN SUBSTR(C_REGD197.DESCR_COMPL_AJ,2,3) ELSE '' END;
         END LOOP;
      END LOOP;
   END IF;
   IF V_CDSITUCRT = '1' THEN  /*SIMPLES NACIONAL*/
      FOR C_REGD100 IN (SELECT NF.REG                 || '|' ||
                               NF.IND_OPER            || '|' ||
                               NF.IND_EMIT            || '|'  AS PARTE_1,
                               --REPLACE(REPLACE(REPLACE(REPLACE(NF.COD_PAR,'.'),'-'),'/'),' ') || '|' ||
                               NF.COD_MOD             || '|' ||
                               DECODE(NF.IDSTATUS,'C','02',DECODE(NF.IDREGESPECIAL,'S','08',CASE WHEN DECODE(NF.IDENTRSAIDOP,'E',NF.DTENTRSAID,NF.DTEMISSAO) < P_DTINICIAL THEN '01' ELSE '00' END)) || '|' ||
							          NF.SER                 || '|' ||
                               NF.SUB                 || '|' ||
                               NF.NUM_DOC             || '|' ||
                               NF.CHV_CTE             || '|' ||
                               NF.DT_DOC              || '|' ||
                               NF.DT_A_P              || '|' ||
                               NF.TP_CT_e             || '|' ||
                               NF.CHV_CTE_REF         || '|' ||
                               REPLACE(LTRIM(TO_CHAR(ROUND(NF.VL_DOC,2), '999999999990.00')), '.', ',')    || '|' AS PARTE_3,
                               NF.IND_FRT             || '|' ||
                               REPLACE(LTRIM(TO_CHAR(ROUND(NF.VL_SERV,2), '999999999990.00')), '.', ',')    || '|' ||
                               REPLACE(LTRIM(TO_CHAR(ROUND(NF.VL_BC_ICMS,2), '999999999990.00')), '.', ',')    || '|' ||
                               REPLACE(LTRIM(TO_CHAR(ROUND(NF.VL_ICMS,2), '999999999990.00')), '.', ',')    || '|' ||
                               REPLACE(LTRIM(TO_CHAR(ROUND(NF.VL_NT,2), '999999999990.00')), '.', ',')    || '|' ||
                               NF.COD_INF             || '|' ||
                               NF.COD_CTA             || '|'  AS PARTE_5,
                               NF.CDFILIAL, NF.NRLANCTONF, NF.DTEMISSAO, NF.DTENTRSAID, NF.IDSTATUS,NF.IDREGESPECIAL, NF.IDENTRSAIDOP, NF.CDTIPOOPER,
                               NF.VL_SERV, NF.COD_MOD, NF.COD_PAR, NF.IDORIGDESTNF, NF.COD_CTA
                          FROM (SELECT '|D100' AS REG,
                                       DECODE(N.IDENTRSAIDOP, 'E', '0','1') AS IND_OPER,
                                       DECODE(N.IDENTRSAIDOP, 'E', DECODE(N.IDSTATUS,'E','0','1'), '0') AS IND_EMIT,
                                       N.IDORIGDESTNF||DECODE(N.IDORIGDESTNF, 'F', N.CDFORNECED, DECODE(N.IDORIGDESTNF, 'C', N.CDCLIENTE , DECODE(N.IDENTRSAIDOP, 'E', N.CDFILIEMIT, N.CDFILIRECE))) AS COD_PAR,
                                       N.CDMODDOCFISC AS COD_MOD,
                                       REPLACE(REPLACE(DECODE(N.IDENTRSAIDOP, 'E', NVL(SE.CDSERIEENTRNFE,N.CDSERIEENTR), NVL(SS.CDSERIENFE,N.CDSERIE)),'-',''),'.','') AS SER,
                                       '' AS SUB,
                                       LTRIM(N.NRNOTAFISC, '0') AS NUM_DOC,
                                       CASE WHEN N.CDMODDOCFISC IN ('57','67') THEN N.NRACESSONFE ELSE '' END AS CHV_CTE,
                                       TO_CHAR(N.DTEMISSAO,'ddmmyyyy')  AS DT_DOC,
                                       TO_CHAR(N.DTENTRSAID,'ddmmyyyy') AS DT_A_P,
                                       N.CDTIPOCTE AS TP_CT_e,
                                       '' AS CHV_CTE_REF,
                                       SUM(ROUND(I.VRCONTABILNF,2)) AS VL_DOC,
                                       CASE WHEN N.IDPGTOFRETE IN ('0','1','3','4') THEN '1'
                                            WHEN N.IDPGTOFRETE = '2' THEN '2'
                                            ELSE '9' END AS IND_FRT,
                                       SUM(ROUND(I.VRCONTABILNF,2)) AS VL_SERV,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0),2)) AS VL_BC_ICMS,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF+CASE WHEN V_IDSOMAFECP = 'S' OR N.IDENTRSAIDOP = 'E' THEN NVL(I.VRADICFECP,0) ELSE 0 END,0),2))  AS VL_ICMS,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,'0'),2)) AS VL_NT,
                                       '' AS COD_INF,
                                       MAX((SELECT MAX(CONTCTBL_SPED(N.IDENTRSAIDOP,I.CDPRODUTO,N.CDTIPOOPER,N.CDFILIEMIT))
                                              FROM ITEMPRODUTO I
                                             WHERE I.CDFILIAL   = N.CDFILIAL
                                               AND I.NRLANCTONF = N.NRLANCTONF)) AS COD_CTA,
                                       N.CDFILIAL, N.NRLANCTONF, N.DTEMISSAO, N.DTENTRSAID, N.IDSTATUS,T.IDREGESPECIAL,
                                       N.IDENTRSAIDOP, N.CDTIPOOPER, N.IDORIGDESTNF
                                  FROM IMPOSTONF I, NOTAFISCAL N, IMPOSTO IM, SERIENF SS, SERIEENTR SE, TIPOOPERAC T
                                 WHERE I.CDFILIAL      = N.CDFILIAL
                                   AND I.NRLANCTONF    = N.NRLANCTONF
                                   AND N.CDMODDOCFISC IN ('07', '08', '09', '10', '11', '26', '27', '57', '67')
                                   AND SE.CDSERIEENTR(+)  = N.CDSERIEENTR
                                   AND SS.CDEMPRESA(+)   = N.CDEMPRESA
                                   AND SS.CDINSCESTA(+)  = N.CDINSCESTA
                                   AND SS.CDINSCMUNI(+)  = N.CDINSCMUNI
                                   AND SS.CDSERIE(+)     = N.CDSERIE
                                   AND N.NRSEQUMAPAPUSN  = V_NRSEQUMAPAPU
                                   AND N.CDINSCESTA      = P_CDINSCESTA
                                   AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                                   AND I.CDIMPOSTO     = IM.CDIMPOSTO
                                   AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                   AND N.IDSTATUS IN ('E','A')
								           AND T.IDENTRSAIDOP  = N.IDENTRSAIDOP
								           AND T.CDTIPOOPER    = N.CDTIPOOPER
                                 GROUP BY DECODE(N.IDENTRSAIDOP, 'E', '0','1'),
                                       DECODE(N.IDENTRSAIDOP, 'E', DECODE(N.IDSTATUS,'E','0','1'), '0'),
                                       N.IDORIGDESTNF||DECODE(N.IDORIGDESTNF, 'F', N.CDFORNECED, DECODE(N.IDORIGDESTNF, 'C', N.CDCLIENTE , DECODE(N.IDENTRSAIDOP, 'E', N.CDFILIEMIT, N.CDFILIRECE))),
                                       N.CDMODDOCFISC,N.IDPGTOFRETE,
                                       REPLACE(REPLACE(DECODE(N.IDENTRSAIDOP, 'E', NVL(SE.CDSERIEENTRNFE,N.CDSERIEENTR), NVL(SS.CDSERIENFE,N.CDSERIE)),'-',''),'.',''),
                                       LTRIM(N.NRNOTAFISC, '0'),CASE WHEN N.CDMODDOCFISC IN ('57','67') THEN N.NRACESSONFE ELSE '' END, TO_CHAR(N.DTEMISSAO,'ddmmyyyy'), TO_CHAR(N.DTENTRSAID,'ddmmyyyy'),
                                       N.CDTIPOCTE, N.CDFILIAL, N.NRLANCTONF, N.DTEMISSAO, N.DTENTRSAID, N.IDSTATUS,T.IDREGESPECIAL, N.IDENTRSAIDOP,N.CDTIPOOPER,N.IDORIGDESTNF
                                 UNION ALL
                                 /***********NOTA CANCELADA************/
                                 SELECT '|D100' AS REG,
                                       DECODE(N.IDENTRSAIDOP, 'E', '0','1') AS IND_OPER,
                                       DECODE(N.IDENTRSAIDOP, 'E', DECODE(N.IDSTATUS,'E','0','1'), '0') AS IND_EMIT,
                                       N.IDORIGDESTNF||DECODE(N.IDORIGDESTNF, 'F', N.CDFORNECED, DECODE(N.IDORIGDESTNF, 'C', N.CDCLIENTE , DECODE(N.IDENTRSAIDOP, 'E', N.CDFILIEMIT, N.CDFILIRECE))) AS COD_PAR,
                                       N.CDMODDOCFISC AS COD_MOD,
                                       REPLACE(REPLACE(DECODE(N.IDENTRSAIDOP, 'E', N.CDSERIEENTR, NVL(SS.CDSERIENFE,N.CDSERIE)),'-',''),'.','') AS SER,
                                       '' AS SUB,
                                       LTRIM(N.NRNOTAFISC, '0') AS NUM_DOC,
                                       '' AS CHV_CTE,
                                       TO_CHAR(N.DTEMISSAO,'ddmmyyyy')  AS DT_DOC,
                                       TO_CHAR(N.DTENTRSAID,'ddmmyyyy') AS DT_A_P,
                                       '' AS TP_CT_e,
                                       '' AS CHV_CTE_REF,
                                       SUM(N.VRNOTAFISC) AS VL_DOC,
                                       CASE WHEN N.IDPGTOFRETE IN ('0','1','3','4') THEN '1'
                                            WHEN N.IDPGTOFRETE = '2' THEN '2'
                                            ELSE '9' END AS IND_FRT,
                                       0 AS VL_SERV,
                                       0 AS VL_BC_ICMS,
                                       0 AS VL_ICMS,
                                       0 AS VL_NT,
                                       '' AS COD_INF,
                                       '' AS COD_CTA,
                                       N.CDFILIAL, N.NRLANCTONF, N.DTEMISSAO, N.DTENTRSAID, N.IDSTATUS,'' AS IDREGESPECIAL, N.IDENTRSAIDOP, N.CDTIPOOPER, N.IDORIGDESTNF
                                  FROM NOTAFISCAL N, SERIENF SS
                                 WHERE N.CDMODDOCFISC IN ('07', '08', '09', '10', '11', '26', '27', '57', '67')
                                   AND N.DTEMISSAO BETWEEN P_DTINICIAL AND P_DTFINAL
                                   AND N.IDENTRSAIDOP = 'S'
                                   AND SS.CDEMPRESA(+)   = N.CDEMPRESA
                                   AND SS.CDINSCESTA(+)  = N.CDINSCESTA
                                   AND SS.CDINSCMUNI(+)  = N.CDINSCMUNI
                                   AND SS.CDSERIE(+)     = N.CDSERIE
                                   AND N.IDSTATUS = 'C'
                                   AND N.CDINSCESTA = P_CDINSCESTA
                                   AND N.CDEMPRESA = P_CDEMPRESA
                                 GROUP BY DECODE(N.IDENTRSAIDOP, 'E', '0','1'),
                                       DECODE(N.IDENTRSAIDOP, 'E', DECODE(N.IDSTATUS,'E','0','1'), '0'),
                                       N.IDORIGDESTNF||DECODE(N.IDORIGDESTNF, 'F', N.CDFORNECED, DECODE(N.IDORIGDESTNF, 'C', N.CDCLIENTE , DECODE(N.IDENTRSAIDOP, 'E', N.CDFILIEMIT, N.CDFILIRECE))),
                                       N.CDMODDOCFISC, N.IDPGTOFRETE,
                                       REPLACE(REPLACE(DECODE(N.IDENTRSAIDOP, 'E', N.CDSERIEENTR, NVL(SS.CDSERIENFE,N.CDSERIE)),'-',''),'.',''),
                                       LTRIM(N.NRNOTAFISC, '0'), TO_CHAR(N.DTEMISSAO,'ddmmyyyy'), TO_CHAR(N.DTENTRSAID,'ddmmyyyy'),
                                       N.CDFILIAL, N.NRLANCTONF, N.DTEMISSAO, N.DTENTRSAID, N.IDSTATUS, N.IDENTRSAIDOP,N.CDTIPOOPER,N.IDORIGDESTNF) NF ) LOOP

         V_PARTICIPANTE := '';
         /* CODIGO DO PARTICIPANTE */
         SELECT MAX(CDCHAVEREG)
           INTO V_COD_PAR
           FROM TMP_REGDADOSSPED
          WHERE CDCHAVEREG = C_REGD100.COD_PAR
            AND CDREGISTRO = '0150';

         IF (V_COD_PAR IS NULL) AND (C_REGD100.COD_PAR IS NOT NULL) THEN
            V_PARTICIPANTE := RETORNA_PARTICIPANTE_SPED(C_REGD100.COD_PAR);
            IF V_PARTICIPANTE IS NULL THEN
               V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
               INSERT INTO TMP_REGDADOSSPED
                 (CDREGISTRO, CDCONTREGI, CDCHAVEREG)
               VALUES
                 ('0150', V_SEQUPROD, C_REGD100.COD_PAR);
            END IF;
         END IF;
         V_COD_PAR := NULL;

         V_PARTICIPANTE := REPLACE(REPLACE(REPLACE(REPLACE(NVL(V_PARTICIPANTE,C_REGD100.COD_PAR),'.'),'-'),'/'),' ')||'|';

         /* PLANO DE CONTAS REFERENCIAL */
         SELECT MAX(CDCHAVEREG)
           INTO V_CONTCTBL
           FROM TMP_REGDADOSSPED
          WHERE CDCHAVEREG = C_REGD100.COD_CTA
            AND CDREGISTRO = '0500';

         IF (V_CONTCTBL IS NULL) AND (C_REGD100.COD_CTA IS NOT NULL) THEN
            V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
            INSERT INTO TMP_REGDADOSSPED
               (CDREGISTRO, CDCONTREGI, CDCHAVEREG)
            VALUES
               ('0500', V_SEQUPROD, C_REGD100.COD_CTA);
         END IF;
         V_CONTCTBL := NULL;

         /* resgata o valor total de descontos por nota fiscal */
         SELECT SUM(I.VRITEMENCA)
           INTO V_VRDESCONTO
           FROM ITEMENCARGO I, ENCARGO E
          WHERE E.IDENCARGO = 'D'
            AND I.CDENCARGO = E.CDENCARGO
            AND I.CDFILIAL = C_REGD100.CDFILIAL
            AND I.NRLANCTONF = C_REGD100.NRLANCTONF;
         V_LINHA := C_REGD100.PARTE_1||
                    V_PARTICIPANTE||
                    C_REGD100.PARTE_3||
                    REPLACE(LTRIM(TO_CHAR(ROUND(V_VRDESCONTO,2), '999999999990.00')), '.', ',')    || '|' ||
                    C_REGD100.PARTE_5;
         V_CONTREGD :=  NVL(V_CONTREGD,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('42'||LPAD(RTRIM(V_CONTREGD), 8, '0'), V_LINHA, P_NRSEQARQMAG);
         V_LINHA := NULL;

         IF (C_REGD100.IDENTRSAIDOP = 'S') AND (C_REGD100.COD_MOD = '07') THEN
            V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER('1'))),3,'0');
            FOR C_REGD110 IN (SELECT CDARVPROD    || '|' ||
                                     REPLACE(LTRIM(TO_CHAR(ROUND(C_REGD100.VL_SERV,2), '999999999990.00')), '.', ',')    || '|' ||
                                     '0,00'       || '|' AS PART_2, P.CDPRODUTO, P.SGUNIDADE, I.CDPRODNCMTRANS AS CDCLASFISC
                                FROM ITEMPRODUTO I, PRODUTO P
                               WHERE I.CDFILIAL = C_REGD100.CDFILIAL
                                 AND I.NRLANCTONF = C_REGD100.NRLANCTONF
                                 AND I.CDPRODUTO = P.CDPRODUTO ) LOOP

               V_CONTREGD :=  NVL(V_CONTREGD,0) + 1;
               INSERT INTO ARQMAGNETICO
                  (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
               VALUES
                  ('42'||LPAD(RTRIM(V_CONTREGD), 8, '0'), '|D110'|| '|' ||V_SEQUENCIAL|| '|' ||C_REGD110.PART_2, P_NRSEQARQMAG);
               V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_SEQUENCIAL))+1)), 3,'0');

               /* UNIDADE DE MEDIDA */
               SELECT MAX(CDCHAVEREG)
                  INTO V_UNID
                  FROM TMP_REGDADOSSPED
                 WHERE CDCHAVEREG = C_REGD110.SGUNIDADE
                   AND CDREGISTRO = '0190';
               IF (V_UNID IS NULL) AND (C_REGD110.SGUNIDADE IS NOT NULL) THEN
                  V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
                  INSERT INTO TMP_REGDADOSSPED
                    (CDREGISTRO, CDCONTREGI, CDCHAVEREG)
                  VALUES
                    ('0190', V_SEQUPROD, C_REGD110.SGUNIDADE);
               END IF;
               V_UNID := NULL;

               /* CODIGO DO PRODUTO */
               SELECT MAX(CDCHAVEREG)
                 INTO V_PRODUTO
                 FROM TMP_REGDADOSSPED
                WHERE CDCHAVEREG = C_REGD110.CDPRODUTO
                  AND CDREGISTRO = '0200';
               IF (V_PRODUTO IS NULL) AND (C_REGD110.CDPRODUTO IS NOT NULL) THEN
                  V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
                  INSERT INTO TMP_REGDADOSSPED
                    (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO3)
                  VALUES
                    ('0200', V_SEQUPROD, C_REGD110.CDPRODUTO, C_REGD110.CDCLASFISC);
               END IF;
               V_PRODUTO := NULL;
            END LOOP;
         END IF;

         /* valor reducao de base de calculo por nf */
         SELECT NVL(SUM(ROUND(I.VRCONTABILNF,2)),0)
           INTO V_VL_RED_BC
           FROM IMPOSTONF I, IMPOSTO IM
          WHERE CDFILIAL   = C_REGD100.CDFILIAL
            AND NRLANCTONF = C_REGD100.NRLANCTONF
            AND IM.CDIMPOSTONFE  = P_CDIMPOSTO
            AND IM.CDIMPOSTO  = I.CDIMPOSTO
            AND IDIMPOSTONF = '2';

         FOR C_REGD190 IN (SELECT ('|D190'          || '|' ||
                                  T.CDSITUTRIBNF   || '|' ||
                                  T.CDFISCOPERNF   || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VRALIQNF,0),2), '999999999990.00')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VL_OPR,0),2), '999999999990.00')), '.', ',')      || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VL_BC_ICMS,0),2), '999999999990.00')), '.', ',')  || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VL_ICMS,0),2), '999999999990.00')), '.', ',')     || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(V_VL_RED_BC,0),2), '999999999990.00')), '.', ',')   || '|' ||
                                  T.COD_OBS        || '|' ) ARQ_D190, T.VL_ICMS, T.CDFISCOPERNF, T.COD_OBS, T.DSCFOPLMAG
                             FROM (SELECT I.VRALIQNF+CASE WHEN V_IDSOMAFECP = 'S' OR C_REGD100.IDENTRSAIDOP = 'E' THEN NVL(I.VRADICALIQFECP,0) ELSE 0 END AS VRALIQNF,
                                          REPLACE(I.CDFISCOPERNF,'.','') AS CDFISCOPERNF,
                                          LPAD(I.CDSITUTRIBNF, 3, '0') AS CDSITUTRIBNF,
                                          NVL(SUM(ROUND(I.VRCONTABILNF,2)),0) AS VL_OPR,
                                          NVL(SUM(ROUND(DECODE(I.CDFISCALNF, '1', I.VRBASECALCNF, 0),2)),0) AS VL_BC_ICMS,
                                          NVL(SUM(ROUND(DECODE(I.CDFISCALNF, '1', I.VRIMPOSTONF+CASE WHEN V_IDSOMAFECP = 'S' OR C_REGD100.IDENTRSAIDOP = 'E' THEN NVL(I.VRADICFECP,0) ELSE 0 END,0),2)),0) AS VL_ICMS,
                                          C.CDTIPOOPER||C.CDCFOPLMAG AS COD_OBS, C.DSCFOPLMAG
                                     FROM IMPOSTONF I, IMPOSTO IM, CFOPLIVROMAGTPOP C
                                    WHERE CDFILIAL          = C_REGD100.CDFILIAL
                                      AND NRLANCTONF        = C_REGD100.NRLANCTONF
                                      AND IM.CDIMPOSTONFE   = P_CDIMPOSTO
                                      AND IM.CDIMPOSTO      = I.CDIMPOSTO
                                      AND IDIMPOSTONF       = 1
                                      AND NRSEQUMAPAPU      = V_NRSEQUMAPAPU
                                      AND C.IDENTRSAIDOP(+) = C_REGD100.IDENTRSAIDOP
                                      AND C.CDTIPOOPER(+)   = C_REGD100.CDTIPOOPER
                                      AND C.CDCFOPLMAG(+)   = I.CDFISCOPERNF
                                      AND C.CDLIVROMAG(+)   = P_CDLIVROMAGADIC
                                    GROUP BY VRALIQNF, I.VRADICALIQFECP, REPLACE(CDFISCOPERNF,'.',''), LPAD(CDSITUTRIBNF, 3, '0'), C.CDTIPOOPER||C.CDCFOPLMAG, C.DSCFOPLMAG) T) LOOP
            V_VL_BC_ICMS_ST := 0;
            V_VL_ICMS_ST := 0;
            V_VL_RED_BC := 0;
            V_VL_IP := 0;

             V_COD_OBS := NULL;
            SELECT MAX(CDCHAVEREG)
              INTO V_COD_OBS
              FROM TMP_REGDADOSSPED
             WHERE CDCHAVEREG = C_REGD190.COD_OBS
               AND CDREGISTRO = '0460';

            IF (V_COD_OBS IS NULL) AND (C_REGD190.COD_OBS IS NOT NULL) THEN
               V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
               INSERT INTO TMP_REGDADOSSPED
                  (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
               VALUES
                  ('0460', V_SEQUPROD, C_REGD190.COD_OBS, C_REGD190.DSCFOPLMAG);
            END IF;
            V_CONTREGD := NVL(V_CONTREGD,0) + 1;
            INSERT INTO ARQMAGNETICO
               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
               ('42'||LPAD(RTRIM(V_CONTREGD), 8, '0'), C_REGD190.ARQ_D190, P_NRSEQARQMAG);
         END LOOP;
      END LOOP;
   END IF;
   IF V_CDSITUCRT IN ('2','3') THEN
      FOR C_REGD500 IN (SELECT '|D500'                                                           || '|' ||
                               NF.CAMPO2                                                         || '|' ||
                               NF.CAMPO3                                                         || '|' AS DADOS_1,
                               --REPLACE(REPLACE(REPLACE(REPLACE(NF.IDORIGDESTNF||DECODE(NF.IDORIGDESTNF, 'F', NF.CDFORNECED, DECODE(NF.IDORIGDESTNF, 'C', NF.CDCLIENTE , DECODE(NF.IDENTRSAIDOP, 'E', NF.CDFILIEMIT, NF.CDFILIRECE))),'.'),'-'),'/'),' ')  || '|' ||
                               NF.CDMODDOCFISC                                                   || '|' ||
                               DECODE(NF.IDSTATUS,'C','02',DECODE(NF.IDREGESPECIAL,'S','08',CASE WHEN DECODE(NF.IDENTRSAIDOP,'E',NF.DTENTRSAID,NF.DTEMISSAO) < P_DTINICIAL THEN '01' ELSE '00' END)) || '|' ||
                               (REPLACE(REPLACE(NF.CDSERIE,'-',''),'.','')                       || '|' ||
                               ''                                                                || '|' ||
                               LTRIM(NF.NRNOTAFISC, '0')                                         || '|' ||
                               TO_CHAR(NF.DTEMISSAO,'ddmmyyyy')                                  || '|' ||
                               DECODE(NF.IDSTATUS, 'C', '', TO_CHAR(NF.DTENTRSAID,'ddmmyyyy'))   || '|' ||
                               DECODE(NF.IDSTATUS, 'C', '', REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRCONTABILNF,2), '999999999990.00')), '.', ',')) || '|' ||
                               DECODE(NF.IDSTATUS, 'C', '', REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_ENCARGO(NF.CDFILIAL, NF.NRLANCTONF, 'D'),2), '999999999990.00')), '.', ','))              || '|' ||
                               DECODE(NF.IDSTATUS, 'C', '', REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRPRODUTOS,2), '999999999990.00')), '.', ','))   || '|' || --campo 14
                               DECODE(NF.IDSTATUS, 'C', '', REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRNAOTRIBUT,2), '999999999990.00')), '.', ','))  || '|' ||
                               DECODE(NF.IDSTATUS, 'C', '', '0,00')                                                                         || '|' ||
                               DECODE(NF.IDSTATUS, 'C', '', REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_ENCARGO(NF.CDFILIAL, NF.NRLANCTONF, V_IDOUTROS),2), '999999999990.00')), '.', ','))       || '|' || --campo 17
                               DECODE(NF.IDSTATUS, 'C', '', REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRBASECALCNF,2), '999999999990.00')), '.', ',')) || '|' || -- campo 18
                               DECODE(NF.IDSTATUS, 'C', '', REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRIMPOSTONF,2), '999999999990.00')), '.', ','))  || '|' || -- campo 19
                               ''                                                                             || '|' ||
                               DECODE(NF.IDSTATUS, 'C', '', REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NF(NF.CDFILIAL, NF.NRLANCTONF, V_PIS, 'I'),2), '999999999990.00')), '.', ','))    || '|' || -- valor do PIS
                               DECODE(NF.IDSTATUS, 'C', '', REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NF(NF.CDFILIAL, NF.NRLANCTONF, V_COFINS, 'I'),2), '999999999990.00')), '.', ',')) || '|' || -- valor do COFINS
                               NF.COD_CTA          || '|' ||
                               DECODE(NF.IDSTATUS, 'C', '|', '1|')  ) AS DADOS_3,
                               NF.CDFILIAL AS CDFILIAL, NF.NRLANCTONF AS NRLANCTONF, NF.NRINSJURTRAN, NF.IDTPIJURTRAN, NF.VRPBRUTOVOLU, NF.VRPLIQUVOLU,
                               NF.NRPLACAVEIC, NF.DSQTDEVOLU, NF.DSESPECVOLU, NF.DSMARCAVOLU, NF.DSNUMEVOLU, NF.CDFISCOPERNF, NF.NRNOTAFISC AS NRNOTAFISC,
                               NF.DTEMISSAO, NF.DTENTRSAID, NF.IDENTRSAIDOP, NF.VRIMPOSTONF,
                               NF.IDORIGDESTNF||DECODE(NF.IDORIGDESTNF, 'F', NF.CDFORNECED, DECODE(NF.IDORIGDESTNF, 'C', NF.CDCLIENTE , DECODE(NF.IDENTRSAIDOP, 'E', NF.CDFILIEMIT, NF.CDFILIRECE))) AS CODPART,
                               NF.IDORIGDESTNF, NF.COD_CTA, DECODE(NF.IDENTRSAIDOP,'E',NF.CDFILIRECE,NF.CDFILIEMIT) AS FILIAL, NF.CDTIPOOPER ,NF.IDREGESPECIAL
                          FROM (SELECT DECODE(N.IDENTRSAIDOP, 'E', '0','1') AS CAMPO2,
                                       '0' AS CAMPO3, NRACESSONFE, N.CDTIPOOPER,
                                       I.CDFILIAL, I.NRLANCTONF, N.IDENTRSAIDOP, N.NRNOTAFISC, SUBSTR(NVL(SS.CDSERIENFE,N.CDSERIE),1,3) AS CDSERIE,
                                       N.NRINSJURNF, N.IDSTATUS,T.IDREGESPECIAL, N.DTENTRSAID, N.VRNOTAFISC, N.VRPRODUTOS,
                                       N.IDPGTOFRETE, N.NRPLACAVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU, N.DSNUMEVOLU,
                                       N.CDFILIEMIT, N.CDFILIRECE, N.DTEMISSAO, MAX(I.CDFISCOPERNF) AS CDFISCOPERNF, N.CDMODDOCFISC,N.CDCLIENTE, N.CDFORNECED, N.IDORIGDESTNF,
                                       SUM(ROUND(I.VRCONTABILNF,2)) AS VRCONTABILNF, N.NRINSJURTRAN, N.IDTPIJURTRAN, N.VRPBRUTOVOLU, N.VRPLIQUVOLU,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0),2)) AS VRBASECALCNF,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF+DECODE(V_IDSOMAFECP,'S',NVL(I.VRADICFECP,0),0),0),2)) AS VRIMPOSTONF,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0),2))   AS VROUTRCRED,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,'1',DECODE(I.VRPERREDBCNF,0, 0,I.VRCONTABILNF-I.VRBASECALCNF),0),2)) AS VRISENTRIB,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'1',0,I.VRCONTABILNF),2)) AS VRNAOTRIBUT,
                                       MAX((SELECT MAX(CONTCTBL_SPED(N.IDENTRSAIDOP,I.CDPRODUTO,N.CDTIPOOPER,N.CDFILIEMIT))
                                              FROM ITEMPRODUTO I
                                             WHERE I.CDFILIAL   = N.CDFILIAL
                                               AND I.NRLANCTONF = N.NRLANCTONF)) AS COD_CTA
                                  FROM NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM, SERIENF SS, TIPOOPERAC T
                                 WHERE I.NRSEQUMAPAPU    = V_NRSEQUMAPAPU
                                   AND IM.CDIMPOSTONFE   = P_CDIMPOSTO
                                   AND I.CDIMPOSTO       = IM.CDIMPOSTO
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'REC'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'PAG'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'CMP'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'SUP'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'FIS'
                                   AND SS.CDEMPRESA(+)   = N.CDEMPRESA
                                   AND SS.CDINSCESTA(+)  = N.CDINSCESTA
                                   AND SS.CDINSCMUNI(+)  = N.CDINSCMUNI
                                   AND SS.CDSERIE(+)     = N.CDSERIE
                                   AND N.IDENTRSAIDOP    = 'S'
                                   AND P_PERFIL          = 'A'
                                   AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                   AND N.CDFILIAL        = I.CDFILIAL
                                   AND N.NRLANCTONF      = I.NRLANCTONF
                                   AND ((N.IDSTATUS = 'A') OR (N.IDSTATUS ='E'))
                                   AND N.CDMODDOCFISC IN ('21', '22')
                                   AND T.IDENTRSAIDOP    = N.IDENTRSAIDOP
                                   AND T.CDTIPOOPER      = N.CDTIPOOPER
                                 GROUP BY DECODE(N.IDENTRSAIDOP, 'E', '0','1'), I.CDFILIAL, I.NRLANCTONF, N.IDENTRSAIDOP , N.NRNOTAFISC,
                                       SUBSTR(NVL(SS.CDSERIENFE,N.CDSERIE),1,3),
                                       N.NRINSJURNF, N.IDSTATUS,T.IDREGESPECIAL, N.DTENTRSAID, N.VRNOTAFISC, N.VRPRODUTOS, N.IDPGTOFRETE,
                                       N.DTEMISSAO, N.CDMODDOCFISC, N.CDCLIENTE, N.CDFORNECED, N.IDORIGDESTNF, N.CDTIPOOPER,
                                       N.CDFILIEMIT, N.CDFILIRECE, N.NRINSJURTRAN, N.IDTPIJURTRAN, N.VRPBRUTOVOLU, N.VRPLIQUVOLU,
                                       N.NRPLACAVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU, N.DSNUMEVOLU, NRACESSONFE
                                 UNION ALL
                                /*************NOTA CANCELADA***************/
                                SELECT '1' AS CAMPO2,
                                       '0' AS CAMPO3, '' AS NRACESSONFE, N.CDTIPOOPER,
                                       N.CDFILIAL, N.NRLANCTONF, N.IDENTRSAIDOP, N.NRNOTAFISC, NVL(SS.CDSERIENFE,N.CDSERIE) AS CDSERIE,
                                       '' AS NRINSJURNF, N.IDSTATUS,'' AS IDREGESPECIAL, N.DTENTRSAID, 0 AS VRNOTAFISC, 0 AS VRPRODUTOS,
                                       N.IDPGTOFRETE, N.NRPLACAVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU, N.DSNUMEVOLU,
                                       '' AS CDFILIEMIT, '' AS CDFILIRECE, N.DTEMISSAO, '' AS CDFISCOPERNF, N.CDMODDOCFISC, N.CDCLIENTE,
                                       N.CDFORNECED, N.IDORIGDESTNF, 0 AS VRCONTABILNF, N.NRINSJURTRAN, N.IDTPIJURTRAN,
                                       0 AS VRPBRUTOVOLU, 0 AS VRPLIQUVOLU, 0 AS VRBASECALCNF, 0 AS VRIMPOSTONF,
                                       0 AS VROUTRCRED, 0 AS VRISENTRIB, 0 AS VRNAOTRIBUT, '' AS COD_CTA
                                  FROM NOTAFISCAL N, SERIENF SS
                                 WHERE NVL(N.IDSISTEMA, 'DFS') <> 'REC'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'PAG'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'CMP'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'SUP'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'FIS'
                                   AND SS.CDEMPRESA(+)   = N.CDEMPRESA
                                   AND SS.CDINSCESTA(+)  = N.CDINSCESTA
                                   AND SS.CDINSCMUNI(+)  = N.CDINSCMUNI
                                   AND SS.CDSERIE(+)     = N.CDSERIE
                                   AND N.IDENTRSAIDOP    = 'S'
                                   AND P_PERFIL          = 'A'
                                   AND N.IDSTATUS        = 'C'
                                   AND N.CDMODDOCFISC    IN ('21', '22')
                                   AND N.CDINSCESTA      = P_CDINSCESTA
                                   AND N.CDEMPRESA       = P_CDEMPRESA
                                   AND N.DTEMISSAO       BETWEEN P_DTINICIAL AND P_DTFINAL
                                 GROUP BY N.CDFILIAL, N.NRLANCTONF, N.IDENTRSAIDOP, N.NRNOTAFISC,  NVL(SS.CDSERIENFE,N.CDSERIE), N.IDSTATUS,
                                       N.DTENTRSAID, N.IDPGTOFRETE, N.NRPLACAVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU,
                                       N.DSNUMEVOLU, N.DTEMISSAO, N.CDMODDOCFISC, N.CDCLIENTE, N.CDFORNECED, N.IDORIGDESTNF,
                                       N.NRINSJURTRAN, N.IDTPIJURTRAN, N.CDTIPOOPER
                                 UNION ALL
                                SELECT DECODE(N.IDENTRSAIDOP, 'E', '0','1') AS CAMPO2,
                                       '1' AS CAMPO3, NRACESSONFE, N.CDTIPOOPER,
                                       I.CDFILIAL, I.NRLANCTONF, N.IDENTRSAIDOP, N.NRNOTAFISC, SUBSTR(NVL(SE.CDSERIEENTRNFE,N.CDSERIEENTR),1,3) AS CDSERIE,
                                       N.NRINSJURNF, N.IDSTATUS, '' AS IDREGESPECIAL,N.DTENTRSAID, N.VRNOTAFISC, N.VRPRODUTOS,
                                       N.IDPGTOFRETE, N.NRPLACAVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU, N.DSNUMEVOLU,
                                       N.CDFILIEMIT, N.CDFILIRECE, N.DTEMISSAO, MAX(I.CDFISCOPERNF) AS CDFISCOPERNF, N.CDMODDOCFISC, N.CDCLIENTE, N.CDFORNECED, N.IDORIGDESTNF,
                                       SUM(ROUND(I.VRCONTABILNF,2)) AS VRCONTABILNF, N.NRINSJURTRAN, N.IDTPIJURTRAN, N.VRPBRUTOVOLU, N.VRPLIQUVOLU,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0),2)) AS VRBASECALCNF,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF+NVL(I.VRADICFECP,0),0),2))  AS VRIMPOSTONF,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0),2))   AS VROUTRCRED,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,'1',DECODE(I.VRPERREDBCNF,0, 0,I.VRCONTABILNF-I.VRBASECALCNF), 0),2)) AS VRISENTRIB,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'1',0,I.VRCONTABILNF),2)) AS VRNAOTRIBUT,
                                       MAX((SELECT MAX(CONTCTBL_SPED(N.IDENTRSAIDOP,I.CDPRODUTO,N.CDTIPOOPER,N.CDFILIRECE))
                                              FROM ITEMPRODUTO I
                                             WHERE I.CDFILIAL   = N.CDFILIAL
                                               AND I.NRLANCTONF = N.NRLANCTONF)) AS COD_CTA
                                  FROM NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM, SERIEENTR SE
                                 WHERE I.NRSEQUMAPAPU    = V_NRSEQUMAPAPU
                                   AND IM.CDIMPOSTONFE   = P_CDIMPOSTO
                                   AND IM.CDIMPOSTO      = I.CDIMPOSTO
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'REC'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'PAG'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'CMP'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'SUP'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'FIS'
                                   AND SE.CDSERIEENTR(+) = N.CDSERIEENTR
                                   AND N.IDENTRSAIDOP    = 'E'
                                   AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                   AND N.CDFILIAL        = I.CDFILIAL
                                   AND N.NRLANCTONF      = I.NRLANCTONF
                                   AND ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E'))
                                   AND N.CDMODDOCFISC    IN ('21', '22') 
                                   GROUP BY DECODE(N.IDENTRSAIDOP, 'E', '0','1'), I.CDFILIAL, I.NRLANCTONF, N.IDENTRSAIDOP, N.CDTIPOOPER,
                                       N.NRNOTAFISC, SUBSTR(NVL(SE.CDSERIEENTRNFE,N.CDSERIEENTR),1,3), N.NRINSJURNF, N.IDSTATUS,N.DTENTRSAID, NRACESSONFE,
                                       N.VRNOTAFISC, N.VRPRODUTOS, N.IDPGTOFRETE, N.DTEMISSAO, N.CDMODDOCFISC, N.CDCLIENTE,
                                       N.CDFORNECED, N.IDORIGDESTNF, N.CDFILIEMIT, N.CDFILIRECE, N.NRINSJURTRAN, N.IDTPIJURTRAN, N.VRPBRUTOVOLU, N.VRPLIQUVOLU,
                                       N.NRPLACAVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU, N.DSNUMEVOLU, NRACESSONFE) NF,
                               (SELECT MAX(C.DTORIGVENREC) DTORIGVENREC,  N.CDFILIAL, N.NRLANCTONF
                                  FROM NOTAFISCAL N, CONTARECEBER C
                                 WHERE N.CDFILIAL   = C.CDFILIAL
                                   AND N.NRLANCTONF = C.NRLANCTONF
                                 GROUP BY N.CDFILIAL, N.NRLANCTONF) PGTO
                        WHERE NF.CDFILIAL   = PGTO.CDFILIAL(+) AND
                              NF.NRLANCTONF = PGTO.NRLANCTONF(+))LOOP

         V_PARTICIPANTE := NULL;
         /* CODIGO DO PARTICIPANTE */
         SELECT MAX(CDCHAVEREG)
           INTO V_COD_PAR
           FROM TMP_REGDADOSSPED
          WHERE CDCHAVEREG = C_REGD500.CODPART
            AND CDREGISTRO = '0150';

         IF (V_COD_PAR IS NULL) AND (C_REGD500.CODPART IS NOT NULL) THEN
            V_PARTICIPANTE := RETORNA_PARTICIPANTE_SPED(C_REGD500.CODPART);
            IF V_PARTICIPANTE IS NULL THEN
               V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
               INSERT INTO TMP_REGDADOSSPED
                 (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
               VALUES
                 ('0150', V_SEQUPROD, C_REGD500.CODPART, '');
            END IF;
         END IF;
         V_COD_PAR := NULL;

         V_PARTICIPANTE := REPLACE(REPLACE(REPLACE(REPLACE(NVL(V_PARTICIPANTE,C_REGD500.CODPART),'.'),'-'),'/'),' ')||'|';

         V_CONTREGD :=  NVL(V_CONTREGD,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('43'||LPAD(RTRIM(V_CONTREGD), 8, '0'), C_REGD500.DADOS_1||V_PARTICIPANTE||C_REGD500.DADOS_3, P_NRSEQARQMAG);
         V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER('1'))),3,'0');

         /* PLANO DE CONTAS REFERENCIAL */
         SELECT MAX(CDCHAVEREG)
           INTO V_CONTCTBL
           FROM TMP_REGDADOSSPED
          WHERE CDCHAVEREG = C_REGD500.COD_CTA
            AND CDREGISTRO = '0500';

         IF (V_CONTCTBL IS NULL) AND (C_REGD500.COD_CTA IS NOT NULL) THEN
            V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
            INSERT INTO TMP_REGDADOSSPED
               (CDREGISTRO, CDCONTREGI, CDCHAVEREG)
            VALUES
               ('0500', V_SEQUPROD, C_REGD500.COD_CTA);
         END IF;
         V_CONTCTBL := NULL;

         IF C_REGD500.IDENTRSAIDOP = 'S' AND P_PERFIL = 'A' THEN
            FOR C_REGD510 IN (SELECT COD_ITEM          || '|' ||
                                     ''             || '|' ||
                                     QTD            || '|' ||
                                     UNID           || '|' ||
                                     VL_ITEM        || '|' ||
                                     VL_DESC        || '|' ||
                                     CST_ICMS       || '|' ||
                                     CFOP           || '|' ||
                                     VL_BC_ICMS     || '|' ||
                                     ALIQ_ICMS      || '|' ||
                                     VL_ICMS        || '|' ||   /* valor imposto item */
                                     VL_BC_ICMS_UF  || '|' ||
                                     '0,00'      	 || '|' ||
                                     ''             || '|' ||
                                     COD_PART       || '|' ||
                                     VL_PIS         || '|' ||
                                     VL_COFINS      || '|' ||
                                     COD_CTA        || '|' AS ARQ_D510,
                                     CDPRODUTO, UNID AS SGUNIDADE, COD_CTA, CDCLASFISC
                                FROM (SELECT PR.CDARVPROD AS COD_ITEM,
                                             REPLACE(LTRIM(TO_CHAR(ROUND(IT.QTITEMPROD,3), '999999999990.000')), '.', ',') AS QTD,
                                             PR.SGUNIDADE AS UNID,
                                             REPLACE(LTRIM(TO_CHAR(ROUND(IT.VRUNITITEM,2), '999999999990.00')), '.', ',') AS VL_ITEM,
                                             REPLACE(LTRIM(TO_CHAR(ROUND(IT.VRDESCITEM,2), '999999999990.00')), '.', ',') AS VL_DESC,
                                             LPAD(NVL(IP.CDSITUTRIBIT,0), 3, '0') AS CST_ICMS,
                                             REPLACE(IP.CDFISCOPERIT,'.','') AS CFOP,
                                             REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(IP.CDFISCALIT, '1', IP.VRBASECALCIT, 0),2), '999999999990.00')), '.', ',') AS VL_BC_ICMS,
                                             REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(IP.CDFISCALIT, '1', IP.VRALIQITEM, 0),2), '999999999990.00')), '.', ',') AS ALIQ_ICMS,
                                             REPLACE(LTRIM(TO_CHAR(ROUND(IP.VRIMPOSTOIT+DECODE(V_IDSOMAFECP,'S',NVL(IP.VRADICFECP,0),0),2), '999999999990.00')), '.', ',') AS VL_ICMS,
                                             REPLACE(LTRIM(TO_CHAR(ROUND(NVL(VALOR_IMPOSTO_ITEM(IP.CDFILIAL, IP.NRLANCTONF, IP.NRSEQITPROD, V_CDIMPOSTRIB,'A'),'0'),2), '999999999990.00')), '.', ',') AS VL_BC_ICMS_UF,
                                             C_REGD500.CODPART AS COD_PART,
                                             REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM(IP.CDFILIAL, IP.NRLANCTONF, IP.NRSEQITPROD, '50', 'I'),2), '999999999990.00')), '.', ',') AS VL_PIS,
                                             REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM(IP.CDFILIAL, IP.NRLANCTONF, IP.NRSEQITPROD, '60', 'I'),2), '999999999990.00')), '.', ',') AS VL_COFINS,
                                             CONTCTBL_SPED(C_REGD500.IDENTRSAIDOP, IT.CDPRODUTO, C_REGD500.CDTIPOOPER, C_REGD500.FILIAL) AS COD_CTA,
                                             PR.CDPRODUTO, IT.CDPRODNCMTRANS AS CDCLASFISC
                                        FROM ITEMPRODUTO IT, PRODUTO PR, ITEMPROIMPOS IP, IMPOSTO IM
                                       WHERE IT.CDFILIAL      = C_REGD500.CDFILIAL
                                         AND IT.NRLANCTONF    = C_REGD500.NRLANCTONF
                                         AND IM.CDIMPOSTO     = IP.CDIMPOSTO
                                         AND IM.CDIMPOSTONFE  = P_CDIMPOSTO
                                         AND IT.CDFILIAL      = IP.CDFILIAL
                                         AND IT.NRLANCTONF    = IP.NRLANCTONF
                                         AND IT.NRSEQITPROD   = IP.NRSEQITPROD
                                         AND IT.CDPRODUTO     = PR.CDPRODUTO
                                         AND IP.NRITPROIMPOS  = '1'
                                         AND C_REGD500.IDENTRSAIDOP = 'S'))LOOP

               V_CONTREGD :=  NVL(V_CONTREGD,0) + 1;
               INSERT INTO ARQMAGNETICO
                  (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
               VALUES
                  ('43'||LPAD(RTRIM(V_CONTREGD), 8, '0'), '|D510'|| '|' ||V_SEQUENCIAL|| '|' || C_REGD510.ARQ_D510, P_NRSEQARQMAG);
               V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_SEQUENCIAL))+1)), 3,'0');

               /* UNIDADE DE MEDIDA */
               SELECT MAX(CDCHAVEREG)
                 INTO V_UNID
                 FROM TMP_REGDADOSSPED
                WHERE CDCHAVEREG = C_REGD510.SGUNIDADE
                  AND CDREGISTRO = '0190';
               IF (V_UNID IS NULL) AND (C_REGD510.SGUNIDADE IS NOT NULL) THEN
                  V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
                  INSERT INTO TMP_REGDADOSSPED
                    (CDREGISTRO, CDCONTREGI, CDCHAVEREG)
                  VALUES
                    ('0190', V_SEQUPROD, C_REGD510.SGUNIDADE);
               END IF;
               V_UNID := NULL;

               /* CODIGO DO PRODUTO */
               SELECT MAX(CDCHAVEREG)
                 INTO V_PRODUTO
                 FROM TMP_REGDADOSSPED
                WHERE CDCHAVEREG = C_REGD510.CDPRODUTO
                  AND CDREGISTRO = '0200';
               IF (V_PRODUTO IS NULL) AND (C_REGD510.CDPRODUTO IS NOT NULL) THEN
                  V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
                  INSERT INTO TMP_REGDADOSSPED
                    (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO3)
                  VALUES
                    ('0200', V_SEQUPROD, C_REGD510.CDPRODUTO, C_REGD510.CDCLASFISC);
               END IF;
               V_PRODUTO := NULL;

               /* PLANO DE CONTAS REFERENCIAL */
               SELECT MAX(CDCHAVEREG)
                 INTO V_CONTCTBL
                 FROM TMP_REGDADOSSPED
                WHERE CDCHAVEREG = C_REGD510.COD_CTA
                  AND CDREGISTRO = '0500';

               IF (V_CONTCTBL IS NULL) AND (C_REGD510.COD_CTA IS NOT NULL) THEN
                  V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
                  INSERT INTO TMP_REGDADOSSPED
                     (CDREGISTRO, CDCONTREGI, CDCHAVEREG)
                  VALUES
                     ('0500', V_SEQUPROD, C_REGD510.COD_CTA);
               END IF;
               V_CONTCTBL := NULL;
            END LOOP;
         END IF;

         /* valor de ipi por nf */
         SELECT NVL(SUM(ROUND(I.VRIMPOSTONF,2)),0)
           INTO V_VL_IP
           FROM IMPOSTONF I, IMPOSTO IM
          WHERE I.CDFILIAL      = C_REGD500.CDFILIAL
            AND I.NRLANCTONF    = C_REGD500.NRLANCTONF
            AND IM.CDIMPOSTONFE = '10'
            AND IM.CDIMPOSTO    = I.CDIMPOSTO;

         /* valor de sub. tributaria por nf */
         SELECT NVL(SUM(ROUND(I.VRBASECALCNF,2)),0),
                NVL(SUM(ROUND(I.VRIMPOSTONF+ CASE WHEN V_IDSOMAFECPST = 'S' OR C_REGD500.IDENTRSAIDOP = 'E' THEN NVL(I.VRADICFECP,0) ELSE 0 END,2)),0)
           INTO V_VL_BC_ICMS_ST, V_VL_ICMS_ST
           FROM IMPOSTONF I , IMPOSTO IM
          WHERE I.CDFILIAL      = C_REGD500.CDFILIAL
            AND I.NRLANCTONF    = C_REGD500.NRLANCTONF
            AND IM.CDIMPOSTONFE = '80'
            AND IM.CDIMPOSTO    = I.CDIMPOSTO;

         /* valor reducao de base de calculo por nf */
         SELECT NVL(SUM(ROUND(I.VRCONTABILNF,2)),0)
           INTO V_VL_RED_BC
           FROM IMPOSTONF I, IMPOSTO IM
          WHERE I.CDFILIAL      = C_REGD500.CDFILIAL
            AND I.NRLANCTONF    = C_REGD500.NRLANCTONF
            AND IM.CDIMPOSTONFE = P_CDIMPOSTO
            AND IM.CDIMPOSTO    = I.CDIMPOSTO
            AND I.IDIMPOSTONF   = '2';

         FOR C_REGD590 IN (SELECT ('|D590'|| '|' ||
                                  T.CDSITUTRIBNF|| '|' ||
                                  T.CDFISCOPERNF|| '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VRALIQNF,0),2), '999999999990.00')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VL_OPR,0),2), '999999999990.00')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VL_BC_ICMS,0),2), '999999999990.00')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VL_ICMS,0),2), '999999999990.00')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(V_VL_BC_ICMS_ST,0),2), '999999999990.00')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(V_VL_ICMS_ST,0),2), '999999999990.00')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(V_VL_RED_BC,0),2), '999999999990.00')), '.', ',')    || '|' ||
                                  T.COD_OBS   || '|' ) ARQ_D590, T.VL_ICMS, T.CDFISCOPERNF, T.COD_OBS, T.DSCFOPLMAG
                             FROM (SELECT I.VRALIQNF+CASE WHEN V_IDSOMAFECP = 'S' OR C_REGD500.IDENTRSAIDOP = 'E' THEN NVL(I.VRADICALIQFECP,0) ELSE 0 END AS VRALIQNF,
                                          REPLACE(I.CDFISCOPERNF,'.','') AS CDFISCOPERNF,
                                          LPAD(I.CDSITUTRIBNF, 3, '0') AS CDSITUTRIBNF,
                                          NVL(SUM(ROUND(I.VRCONTABILNF,2)),0) AS VL_OPR,
                                          NVL(SUM(ROUND(DECODE(I.CDFISCALNF, '1', I.VRBASECALCNF, 0),2)),0) AS VL_BC_ICMS,
                                          NVL(SUM(ROUND(DECODE(I.CDFISCALNF, '1', I.VRIMPOSTONF+CASE WHEN V_IDSOMAFECP = 'S' OR C_REGD500.IDENTRSAIDOP = 'E' THEN NVL(I.VRADICFECP,0) ELSE 0 END, 0),2)),0) AS VL_ICMS,
                                          C.CDTIPOOPER||C.CDCFOPLMAG AS COD_OBS, C.DSCFOPLMAG
                                     FROM IMPOSTONF I, IMPOSTO IM, CFOPLIVROMAGTPOP C
                                    WHERE I.CDFILIAL        = C_REGD500.CDFILIAL
                                      AND I.NRLANCTONF      = C_REGD500.NRLANCTONF
                                      AND IM.CDIMPOSTONFE   = P_CDIMPOSTO
                                      AND IM.CDIMPOSTO      = I.CDIMPOSTO
                                      AND I.IDIMPOSTONF     = 1
                                      AND I.NRSEQUMAPAPU    = V_NRSEQUMAPAPU
                                      AND C.IDENTRSAIDOP(+) = C_REGD500.IDENTRSAIDOP
                                      AND C.CDTIPOOPER(+)   = C_REGD500.CDTIPOOPER
                                      AND C.CDCFOPLMAG(+)   = I.CDFISCOPERNF
                                      AND C.CDLIVROMAG(+)   = P_CDLIVROMAGADIC
                                    GROUP BY I.VRALIQNF, I.VRADICALIQFECP, REPLACE(I.CDFISCOPERNF,'.',''), LPAD(I.CDSITUTRIBNF, 3, '0'), C.CDTIPOOPER||C.CDCFOPLMAG, C.DSCFOPLMAG) T) LOOP
            V_VL_BC_ICMS_ST := 0;
            V_VL_ICMS_ST := 0;
            V_VL_RED_BC := 0;
            V_VL_IP := 0;

             V_COD_OBS := NULL;
            SELECT MAX(CDCHAVEREG)
              INTO V_COD_OBS
              FROM TMP_REGDADOSSPED
             WHERE CDCHAVEREG = C_REGD590.COD_OBS
               AND CDREGISTRO = '0460';

            IF (V_COD_OBS IS NULL) AND (C_REGD590.COD_OBS IS NOT NULL) THEN
               V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
               INSERT INTO TMP_REGDADOSSPED
                  (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
               VALUES
                  ('0460', V_SEQUPROD, C_REGD590.COD_OBS, C_REGD590.DSCFOPLMAG);
            END IF;

            V_CONTREGD := NVL(V_CONTREGD,0) + 1;

            INSERT INTO ARQMAGNETICO
               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
               ('43'||LPAD(RTRIM(V_CONTREGD), 8, '0'), C_REGD590.ARQ_D590, P_NRSEQARQMAG);
         END LOOP;
      END LOOP;
   END IF;
   
   IF V_CDSITUCRT = '1' THEN  /*SIMPLES NACIONAL*/
      FOR C_REGD500 IN (SELECT '|D500'                                                          || '|' ||
                               NF.CAMPO2                                                        || '|' ||
                               NF.CAMPO3                                                        || '|' AS DADOS_1,
                               --REPLACE(REPLACE(REPLACE(REPLACE(NF.IDORIGDESTNF||DECODE(NF.IDORIGDESTNF, 'F', NF.CDFORNECED, DECODE(NF.IDORIGDESTNF, 'C', NF.CDCLIENTE , DECODE(NF.IDENTRSAIDOP, 'E', NF.CDFILIEMIT, NF.CDFILIRECE))),'.'),'-'),'/'),' ')  || '|' ||
                               NF.CDMODDOCFISC                                                  || '|' ||
                               DECODE(NF.IDSTATUS,'C','02',DECODE(NF.IDREGESPECIAL,'S','08',CASE WHEN DECODE(NF.IDENTRSAIDOP,'E',NF.DTENTRSAID,NF.DTEMISSAO) < P_DTINICIAL THEN '01' ELSE '00' END)) || '|' ||
                               REPLACE(REPLACE(NF.CDSERIE,'-',''),'.','')                       || '|' ||
                               ''                                                               || '|' ||
                               LTRIM(NF.NRNOTAFISC, '0')                                        || '|' ||
                               TO_CHAR(NF.DTEMISSAO,'ddmmyyyy')                                 || '|' ||
                               DECODE(NF.IDSTATUS, 'C', '', TO_CHAR(NF.DTENTRSAID,'ddmmyyyy'))  || '|' ||
                               DECODE(NF.IDSTATUS, 'C', '', REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRCONTABILNF,2), '999999999990.00')), '.', ',')) || '|' ||
                               DECODE(NF.IDSTATUS, 'C', '', REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_ENCARGO(NF.CDFILIAL, NF.NRLANCTONF, 'D'),2), '999999999990.00')), '.', ','))              || '|' ||
                               DECODE(NF.IDSTATUS, 'C', '', REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRPRODUTOS,2), '999999999990.00')), '.', ','))   || '|' || --campo 14
                               DECODE(NF.IDSTATUS, 'C', '', REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRNAOTRIBUT,2), '999999999990.00')), '.', ','))  || '|' ||
                               DECODE(NF.IDSTATUS, 'C', '', '0,00')                                                                         || '|' ||
                               DECODE(NF.IDSTATUS, 'C', '', REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_ENCARGO(NF.CDFILIAL, NF.NRLANCTONF, V_IDOUTROS),2), '999999999990.00')), '.', ','))       || '|' || --campo 17
                               DECODE(NF.IDSTATUS, 'C', '', REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRBASECALCNF,2), '999999999990.00')), '.', ',')) || '|' || -- campo 18
                               DECODE(NF.IDSTATUS, 'C', '', REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRIMPOSTONF,2), '999999999990.00')), '.', ','))  || '|' || -- campo 19
                               ''                                                                             || '|' ||
                               DECODE(NF.IDSTATUS, 'C', '', REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NF(NF.CDFILIAL, NF.NRLANCTONF, V_PIS, 'I'),2), '999999999990.00')), '.', ','))    || '|' || -- valor do PIS
                               DECODE(NF.IDSTATUS, 'C', '', REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NF(NF.CDFILIAL, NF.NRLANCTONF, V_COFINS, 'I'),2), '999999999990.00')), '.', ',')) || '|' || -- valor do COFINS
                               NF.COD_CTA            || '|' ||
                               DECODE(NF.IDSTATUS, 'C', '|', '1|') AS DADOS_3,
                               NF.CDFILIAL AS CDFILIAL, NF.NRLANCTONF AS NRLANCTONF, NF.NRINSJURTRAN, NF.IDTPIJURTRAN, NF.VRPBRUTOVOLU, NF.VRPLIQUVOLU,
                               NF.NRPLACAVEIC, NF.DSQTDEVOLU, NF.DSESPECVOLU, NF.DSMARCAVOLU, NF.DSNUMEVOLU, NF.CDFISCOPERNF, NF.NRNOTAFISC AS NRNOTAFISC,
                               NF.DTEMISSAO, NF.DTENTRSAID, NF.IDENTRSAIDOP, NF.VRIMPOSTONF,
                               NF.IDORIGDESTNF||DECODE(NF.IDORIGDESTNF, 'F', NF.CDFORNECED, DECODE(NF.IDORIGDESTNF, 'C', NF.CDCLIENTE , DECODE(NF.IDENTRSAIDOP, 'E', NF.CDFILIEMIT, NF.CDFILIRECE))) AS CODPART,
                               NF.IDORIGDESTNF, NF.COD_CTA, DECODE(NF.IDENTRSAIDOP,'E',NF.CDFILIRECE,NF.CDFILIEMIT) AS FILIAL, NF.CDTIPOOPER,NF.IDREGESPECIAL
                          FROM (SELECT DECODE(N.IDENTRSAIDOP, 'E', '0','1') AS CAMPO2,
                                       '0' AS CAMPO3, NRACESSONFE, N.CDTIPOOPER,
                                       I.CDFILIAL, I.NRLANCTONF, N.IDENTRSAIDOP, N.NRNOTAFISC, SUBSTR(NVL(SS.CDSERIENFE, N.CDSERIE),2,4) AS CDSERIE,
                                       N.NRINSJURNF, N.IDSTATUS,T.IDREGESPECIAL, N.DTENTRSAID, N.VRNOTAFISC, N.VRPRODUTOS,
                                       N.IDPGTOFRETE, N.NRPLACAVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU, N.DSNUMEVOLU,
                                       N.CDFILIEMIT, N.CDFILIRECE, N.DTEMISSAO, MAX(I.CDFISCOPERNF) AS CDFISCOPERNF, N.CDMODDOCFISC,N.CDCLIENTE, N.CDFORNECED, N.IDORIGDESTNF,
                                       SUM(ROUND(I.VRCONTABILNF,2)) AS VRCONTABILNF, N.NRINSJURTRAN, N.IDTPIJURTRAN, N.VRPBRUTOVOLU, N.VRPLIQUVOLU,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0),2)) AS VRBASECALCNF,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF+DECODE(V_IDSOMAFECP,'S',NVL(I.VRADICFECP,0),0),0),2))  AS VRIMPOSTONF,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0),2)) AS VROUTRCRED,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,'1',DECODE(I.VRPERREDBCNF,0, 0,I.VRCONTABILNF-I.VRBASECALCNF), 0),2)) AS VRISENTRIB,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'1',0,I.VRCONTABILNF),2)) AS VRNAOTRIBUT,
                                       MAX((SELECT MAX(CONTCTBL_SPED(N.IDENTRSAIDOP,I.CDPRODUTO,N.CDTIPOOPER,N.CDFILIEMIT))
                                              FROM ITEMPRODUTO I
                                             WHERE I.CDFILIAL   = N.CDFILIAL
                                               AND I.NRLANCTONF = N.NRLANCTONF)) AS COD_CTA
                                  FROM NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM, SERIENF SS, TIPOOPERAC T
                                 WHERE N.NRSEQUMAPAPUSN    = V_NRSEQUMAPAPU
                                   AND N.CDINSCESTA        = P_CDINSCESTA
                                   AND IM.CDIMPOSTONFE   = P_CDIMPOSTO
                                   AND I.CDIMPOSTO       = IM.CDIMPOSTO
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'REC'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'PAG'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'CMP'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'SUP'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'FIS'
                                   AND SS.CDEMPRESA(+)   = N.CDEMPRESA
                                   AND SS.CDINSCESTA(+)  = N.CDINSCESTA
                                   AND SS.CDINSCMUNI(+)  = N.CDINSCMUNI
                                   AND SS.CDSERIE(+)     = N.CDSERIE
                                   AND N.IDENTRSAIDOP    = 'S'
                                   AND P_PERFIL = 'A'
                                   AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                   AND N.CDFILIAL        = I.CDFILIAL
                                   AND N.NRLANCTONF      = I.NRLANCTONF
                                   AND ((N.IDSTATUS = 'A') OR (N.IDSTATUS ='E'))
                                   AND N.CDMODDOCFISC IN ('21', '22')
                                   AND T.IDENTRSAIDOP    = N.IDENTRSAIDOP
                                   AND T.CDTIPOOPER      = N.CDTIPOOPER
                                 GROUP BY DECODE(N.IDENTRSAIDOP, 'E', '0','1'), I.CDFILIAL, I.NRLANCTONF, N.IDENTRSAIDOP, N.NRNOTAFISC,
                                       NVL(SS.CDSERIENFE,N.CDSERIE), N.CDTIPOOPER,
                                       N.NRINSJURNF, N.IDSTATUS,T.IDREGESPECIAL, N.DTENTRSAID, N.VRNOTAFISC, N.VRPRODUTOS, N.IDPGTOFRETE,
                                       N.DTEMISSAO, N.CDMODDOCFISC,N.CDCLIENTE, N.CDFORNECED, N.IDORIGDESTNF,
                                       N.CDFILIEMIT, N.CDFILIRECE, N.NRINSJURTRAN, N.IDTPIJURTRAN, N.VRPBRUTOVOLU, N.VRPLIQUVOLU,
                                       N.NRPLACAVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU, N.DSNUMEVOLU, NRACESSONFE
                                 UNION ALL
                                /*************NOTA CANCELADA***************/
                                SELECT '1' AS CAMPO2,
                                       '0' AS CAMPO3, '' AS NRACESSONFE, N.CDTIPOOPER,
                                       N.CDFILIAL, N.NRLANCTONF, N.IDENTRSAIDOP, N.NRNOTAFISC, NVL(SS.CDSERIENFE,N.CDSERIE) AS CDSERIE,
                                       '' AS NRINSJURNF, N.IDSTATUS, '' AS IDREGESPECIAL, N.DTENTRSAID, 0 AS VRNOTAFISC, 0 AS VRPRODUTOS,
                                       N.IDPGTOFRETE, N.NRPLACAVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU, N.DSNUMEVOLU,
                                       '' AS CDFILIEMIT, '' AS CDFILIRECE, N.DTEMISSAO, '' AS CDFISCOPERNF, N.CDMODDOCFISC, N.CDCLIENTE,
                                       N.CDFORNECED, N.IDORIGDESTNF, 0 AS VRCONTABILNF, N.NRINSJURTRAN, N.IDTPIJURTRAN,
                                       0 AS VRPBRUTOVOLU, 0 AS VRPLIQUVOLU, 0 AS VRBASECALCNF, 0 AS VRIMPOSTONF,
                                       0 AS VROUTRCRED, 0 AS VRISENTRIB, 0 AS VRNAOTRIBUT, '' AS COD_CTA
                                  FROM NOTAFISCAL N, SERIENF SS
                                 WHERE NVL(N.IDSISTEMA, 'DFS') <> 'REC'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'PAG'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'CMP'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'SUP'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'FIS'
                                   AND SS.CDEMPRESA(+)   = N.CDEMPRESA
                                   AND SS.CDINSCESTA(+)  = N.CDINSCESTA
                                   AND SS.CDINSCMUNI(+)  = N.CDINSCMUNI
                                   AND SS.CDSERIE(+)     = N.CDSERIE
                                   AND N.IDENTRSAIDOP    = 'S'
                                   AND P_PERFIL          = 'A'
                                   AND N.IDSTATUS        = 'C'
                                   AND N.CDMODDOCFISC    IN ('21', '22')
                                   AND N.CDINSCESTA      = P_CDINSCESTA
                                   AND N.CDEMPRESA       = P_CDEMPRESA
                                   AND N.DTEMISSAO       BETWEEN P_DTINICIAL AND P_DTFINAL
                                 GROUP BY N.CDFILIAL, N.NRLANCTONF, N.IDENTRSAIDOP, N.NRNOTAFISC, NVL(SS.CDSERIENFE,N.CDSERIE), N.IDSTATUS,
                                       N.DTENTRSAID, N.IDPGTOFRETE, N.NRPLACAVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU,
                                       N.DSNUMEVOLU, N.DTEMISSAO, N.CDMODDOCFISC, N.CDCLIENTE, N.CDFORNECED, N.IDORIGDESTNF,
                                       N.NRINSJURTRAN, N.IDTPIJURTRAN, N.CDTIPOOPER
                                 UNION ALL
                                SELECT DECODE(N.IDENTRSAIDOP, 'E', '0','1') AS CAMPO2,
                                       '1' AS CAMPO3, NRACESSONFE, N.CDTIPOOPER,
                                       I.CDFILIAL, I.NRLANCTONF, N.IDENTRSAIDOP, N.NRNOTAFISC, SUBSTR(NVL(SE.CDSERIEENTRNFE, N.CDSERIEENTR),2,4) AS CDSERIE,
                                       N.NRINSJURNF, N.IDSTATUS,'' AS IDREGESPECIAL, N.DTENTRSAID, N.VRNOTAFISC, N.VRPRODUTOS,
                                       N.IDPGTOFRETE, N.NRPLACAVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU, N.DSNUMEVOLU,
                                       N.CDFILIEMIT, N.CDFILIRECE, N.DTEMISSAO, MAX(I.CDFISCOPERNF) AS CDFISCOPERNF, N.CDMODDOCFISC, N.CDCLIENTE, N.CDFORNECED, N.IDORIGDESTNF,
                                       SUM(ROUND(I.VRCONTABILNF,2)) AS VRCONTABILNF, N.NRINSJURTRAN, N.IDTPIJURTRAN, N.VRPBRUTOVOLU, N.VRPLIQUVOLU,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0),2)) AS VRBASECALCNF,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF+NVL(I.VRADICFECP,0),0),2))  AS VRIMPOSTONF,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0),2)) AS VROUTRCRED,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,'1',DECODE(I.VRPERREDBCNF,0, 0,I.VRCONTABILNF-I.VRBASECALCNF), 0),2)) AS VRISENTRIB,
                                       SUM(ROUND(DECODE(I.CDFISCALNF,'1',0,I.VRCONTABILNF),2)) AS VRNAOTRIBUT,
                                       MAX((SELECT MAX(CONTCTBL_SPED(N.IDENTRSAIDOP,I.CDPRODUTO,N.CDTIPOOPER,N.CDFILIRECE))
                                              FROM ITEMPRODUTO I
                                             WHERE I.CDFILIAL   = N.CDFILIAL
                                               AND I.NRLANCTONF = N.NRLANCTONF)) AS COD_CTA
                                  FROM NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM, SERIEENTR SE
                                 WHERE N.NRSEQUMAPAPUSN  = V_NRSEQUMAPAPU
                                   AND N.CDINSCESTA      = P_CDINSCESTA
                                   AND IM.CDIMPOSTONFE   = P_CDIMPOSTO
                                   AND IM.CDIMPOSTO      = I.CDIMPOSTO
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'REC'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'PAG'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'CMP'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'SUP'
                                   AND NVL(N.IDSISTEMA, 'DFS') <> 'FIS'
                                   AND SE.CDSERIEENTR(+) = N.CDSERIEENTR
                                   AND N.IDENTRSAIDOP    = 'E'
                                   AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                   AND N.CDFILIAL        = I.CDFILIAL
                                   AND N.NRLANCTONF      = I.NRLANCTONF
                                   AND ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E'))
                                   AND N.CDMODDOCFISC    IN ('21', '22')
                                 GROUP BY DECODE(N.IDENTRSAIDOP, 'E', '0','1'), I.CDFILIAL, I.NRLANCTONF, N.IDENTRSAIDOP, N.CDTIPOOPER,
                                       N.NRNOTAFISC, SUBSTR(NVL(SE.CDSERIEENTRNFE, N.CDSERIEENTR),2,4), N.NRINSJURNF, N.IDSTATUS, N.DTENTRSAID, NRACESSONFE,
                                       N.VRNOTAFISC, N.VRPRODUTOS, N.IDPGTOFRETE, N.DTEMISSAO, N.CDMODDOCFISC, N.CDCLIENTE,
                                       N.CDFORNECED, N.IDORIGDESTNF, N.CDFILIEMIT, N.CDFILIRECE, N.NRINSJURTRAN, N.IDTPIJURTRAN, N.VRPBRUTOVOLU, N.VRPLIQUVOLU,
                                       N.NRPLACAVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU, N.DSNUMEVOLU, NRACESSONFE) NF,
                               (SELECT MAX(C.DTORIGVENREC) DTORIGVENREC,  N.CDFILIAL, N.NRLANCTONF
                                  FROM NOTAFISCAL N, CONTARECEBER C
                                 WHERE N.CDFILIAL   = C.CDFILIAL
                                   AND N.NRLANCTONF = C.NRLANCTONF
                                 GROUP BY N.CDFILIAL, N.NRLANCTONF) PGTO
                         WHERE NF.CDFILIAL   = PGTO.CDFILIAL(+)
                           AND NF.NRLANCTONF = PGTO.NRLANCTONF(+))LOOP

         V_PARTICIPANTE := NULL;
         /* CODIGO DO PARTICIPANTE */
         SELECT MAX(CDCHAVEREG)
           INTO V_COD_PAR
           FROM TMP_REGDADOSSPED
          WHERE CDCHAVEREG = C_REGD500.CODPART
            AND CDREGISTRO = '0150';

         IF (V_COD_PAR IS NULL) AND (C_REGD500.CODPART IS NOT NULL) THEN
            V_PARTICIPANTE := RETORNA_PARTICIPANTE_SPED(C_REGD500.CODPART);
            IF V_PARTICIPANTE IS NULL THEN
               V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
               INSERT INTO TMP_REGDADOSSPED
                 (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
               VALUES
                 ('0150', V_SEQUPROD, C_REGD500.CODPART, '');
            END IF;
         END IF;
         V_COD_PAR := NULL;

         V_PARTICIPANTE := REPLACE(REPLACE(REPLACE(REPLACE(NVL(V_PARTICIPANTE,C_REGD500.CODPART),'.'),'-'),'/'),' ')||'|';

         V_CONTREGD :=  NVL(V_CONTREGD,0) + 1;
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            ('43'||LPAD(RTRIM(V_CONTREGD), 8, '0'), C_REGD500.DADOS_1||V_PARTICIPANTE||C_REGD500.DADOS_3, P_NRSEQARQMAG);
         V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER('1'))),3,'0');

         /* PLANO DE CONTAS REFERENCIAL */
         SELECT MAX(CDCHAVEREG)
           INTO V_CONTCTBL
           FROM TMP_REGDADOSSPED
          WHERE CDCHAVEREG = C_REGD500.COD_CTA
            AND CDREGISTRO = '0500';

         IF (V_CONTCTBL IS NULL) AND (C_REGD500.COD_CTA IS NOT NULL) THEN
            V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
            INSERT INTO TMP_REGDADOSSPED
               (CDREGISTRO, CDCONTREGI, CDCHAVEREG)
            VALUES
               ('0500', V_SEQUPROD, C_REGD500.COD_CTA);
         END IF;
         V_CONTCTBL := NULL;

         IF C_REGD500.IDENTRSAIDOP = 'S' AND P_PERFIL = 'A' THEN
            FOR C_REGD510 IN (SELECT COD_ITEM       || '|' ||
                                     ''             || '|' ||
                                     QTD            || '|' ||
                                     UNID           || '|' ||
                                     VL_ITEM        || '|' ||
                                     VL_DESC        || '|' ||
                                     CST_ICMS       || '|' ||
                                     CFOP           || '|' ||
                                     VL_BC_ICMS     || '|' ||
                                     ALIQ_ICMS      || '|' ||
                                     VL_ICMS        || '|' ||   /* valor imposto item */
                                     VL_BC_ICMS_UF  || '|' ||
                                     '0,00'      	 || '|' ||
                                     ''             || '|' ||
                                     COD_PART       || '|' ||
                                     VL_PIS         || '|' ||
                                     VL_COFINS      || '|' ||
                                     COD_CTA        || '|' AS ARQ_D510,
                                     CDPRODUTO, UNID AS SGUNIDADE, COD_CTA, CDCLASFISC
                                FROM (SELECT PR.CDARVPROD AS COD_ITEM,
                                             REPLACE(LTRIM(TO_CHAR(ROUND(IT.QTITEMPROD,3), '999999999990.000')), '.', ',') AS QTD,
                                             PR.SGUNIDADE AS UNID,
                                             REPLACE(LTRIM(TO_CHAR(ROUND(IT.VRUNITITEM,2), '999999999990.00')), '.', ',') AS VL_ITEM,
                                             REPLACE(LTRIM(TO_CHAR(ROUND(IT.VRDESCITEM,2), '999999999990.00')), '.', ',') AS VL_DESC,
                                             LPAD(NVL(IP.CDSITUTRIBIT,0), 3, '0') AS CST_ICMS,
                                             REPLACE(IP.CDFISCOPERIT,'.','') AS CFOP,
                                             REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(IP.CDFISCALIT, '1', IP.VRBASECALCIT, 0),2), '999999999990.00')), '.', ',') AS VL_BC_ICMS,
                                             REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(IP.CDFISCALIT, '1', IP.VRALIQITEM +DECODE(V_IDSOMAFECP,'S',NVL(IP.VRADICALIQFECP,0),0),0),2), '999999999990.00')), '.', ',') AS ALIQ_ICMS,
                                             REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(IP.CDFISCALIT, '1', IP.VRIMPOSTOIT+DECODE(V_IDSOMAFECP,'S',NVL(IP.VRADICFECP,0),0),0),2), '999999999990.00')), '.', ',') AS VL_ICMS,
                                             REPLACE(LTRIM(TO_CHAR(ROUND(NVL(VALOR_IMPOSTO_ITEM(IP.CDFILIAL, IP.NRLANCTONF, IP.NRSEQITPROD, V_CDIMPOSTRIB,'A'),'0'),2), '999999999990.00')), '.', ',') AS VL_BC_ICMS_UF,
                                             C_REGD500.CODPART AS COD_PART,
                                             REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM(IP.CDFILIAL, IP.NRLANCTONF, IP.NRSEQITPROD, '50', 'I'),2), '999999999990.00')), '.', ',') AS VL_PIS,
                                             REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM(IP.CDFILIAL, IP.NRLANCTONF, IP.NRSEQITPROD, '60', 'I'),2), '999999999990.00')), '.', ',') AS VL_COFINS,
                                             CONTCTBL_SPED(C_REGD500.IDENTRSAIDOP, IT.CDPRODUTO, C_REGD500.CDTIPOOPER, C_REGD500.FILIAL) AS COD_CTA,
                                             PR.CDPRODUTO, IT.CDPRODNCMTRANS AS CDCLASFISC
                                        FROM ITEMPRODUTO IT, PRODUTO PR, ITEMPROIMPOS IP, IMPOSTO IM
                                       WHERE IT.CDFILIAL      = C_REGD500.CDFILIAL
                                         AND IT.NRLANCTONF    = C_REGD500.NRLANCTONF
                                         AND IM.CDIMPOSTO     = IP.CDIMPOSTO
                                         AND IM.CDIMPOSTONFE  = P_CDIMPOSTO
                                         AND IT.CDFILIAL      = IP.CDFILIAL
                                         AND IT.NRLANCTONF    = IP.NRLANCTONF
                                         AND IT.NRSEQITPROD   = IP.NRSEQITPROD
                                         AND IT.CDPRODUTO     = PR.CDPRODUTO
                                         AND IP.NRITPROIMPOS  = '1'
                                         AND C_REGD500.IDENTRSAIDOP = 'S'))LOOP

               V_CONTREGD :=  NVL(V_CONTREGD,0) + 1;
               INSERT INTO ARQMAGNETICO
                 (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
               VALUES
                 ('43'||LPAD(RTRIM(V_CONTREGD), 8, '0'), '|D510'|| '|' ||V_SEQUENCIAL|| '|' || C_REGD510.ARQ_D510, P_NRSEQARQMAG);
               V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_SEQUENCIAL))+1)), 3,'0');

               /* UNIDADE DE MEDIDA */
               SELECT MAX(CDCHAVEREG)
                  INTO V_UNID
                  FROM TMP_REGDADOSSPED
                 WHERE CDCHAVEREG = C_REGD510.SGUNIDADE
                   AND CDREGISTRO = '0190';
               IF (V_UNID IS NULL) AND (C_REGD510.SGUNIDADE IS NOT NULL) THEN
                  V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
                  INSERT INTO TMP_REGDADOSSPED
                    (CDREGISTRO, CDCONTREGI, CDCHAVEREG)
                  VALUES
                    ('0190', V_SEQUPROD, C_REGD510.SGUNIDADE);
               END IF;
               V_UNID := NULL;

               /* CODIGO DO PRODUTO */
               SELECT MAX(CDCHAVEREG)
                 INTO V_PRODUTO
                 FROM TMP_REGDADOSSPED
                WHERE CDCHAVEREG = C_REGD510.CDPRODUTO
                  AND CDREGISTRO = '0200';
               IF (V_PRODUTO IS NULL) AND (C_REGD510.CDPRODUTO IS NOT NULL) THEN
                  V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
                  INSERT INTO TMP_REGDADOSSPED
                    (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO3)
                  VALUES
                    ('0200', V_SEQUPROD, C_REGD510.CDPRODUTO, C_REGD510.CDCLASFISC);
               END IF;
               V_PRODUTO := NULL;

               /* PLANO DE CONTAS REFERENCIAL */
               SELECT MAX(CDCHAVEREG)
                 INTO V_CONTCTBL
                 FROM TMP_REGDADOSSPED
                WHERE CDCHAVEREG = C_REGD510.COD_CTA
                  AND CDREGISTRO = '0500';

               IF (V_CONTCTBL IS NULL) AND (C_REGD510.COD_CTA IS NOT NULL) THEN
                  V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
                  INSERT INTO TMP_REGDADOSSPED
                     (CDREGISTRO, CDCONTREGI, CDCHAVEREG)
                  VALUES
                     ('0500', V_SEQUPROD, C_REGD510.COD_CTA);
               END IF;
               V_CONTCTBL := NULL;
            END LOOP;
         END IF;

         /* valor de ipi por nf */
         SELECT NVL(SUM(ROUND(I.VRIMPOSTONF,2)),0)
           INTO V_VL_IP
           FROM IMPOSTONF I, IMPOSTO IM
          WHERE I.CDFILIAL       = C_REGD500.CDFILIAL
            AND I.NRLANCTONF     = C_REGD500.NRLANCTONF
            AND IM.CDIMPOSTONFE  = '10'
            AND IM.CDIMPOSTO     = I.CDIMPOSTO;
         /* valor de sub. tributaria por nf */
         SELECT NVL(SUM(ROUND(I.VRBASECALCNF,2)),0),
                NVL(SUM(ROUND(I.VRIMPOSTONF+CASE WHEN V_IDSOMAFECPST = 'S' OR C_REGD500.IDENTRSAIDOP = 'E' THEN NVL(I.VRADICFECP,0) ELSE 0 END,2)),0)
           INTO V_VL_BC_ICMS_ST, V_VL_ICMS_ST
           FROM IMPOSTONF I , IMPOSTO IM
          WHERE I.CDFILIAL       = C_REGD500.CDFILIAL
            AND I.NRLANCTONF     = C_REGD500.NRLANCTONF
            AND IM.CDIMPOSTONFE  = '80'
            AND IM.CDIMPOSTO     = I.CDIMPOSTO;
         /* valor reducao de base de calculo por nf */
         SELECT NVL(SUM(ROUND(I.VRCONTABILNF,2)),0)
           INTO V_VL_RED_BC
           FROM IMPOSTONF I, IMPOSTO IM
          WHERE I.CDFILIAL       = C_REGD500.CDFILIAL
            AND I.NRLANCTONF     = C_REGD500.NRLANCTONF
            AND IM.CDIMPOSTONFE  = P_CDIMPOSTO
            AND IM.CDIMPOSTO     = I.CDIMPOSTO
            AND I.IDIMPOSTONF    = '2';

         FOR C_REGD590 IN (SELECT ('|D590'|| '|' ||
                                  T.CDSITUTRIBNF|| '|' ||
                                  T.CDFISCOPERNF|| '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VRALIQNF,0),2), '999999999990.00')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VL_OPR,0),2), '999999999990.00')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VL_BC_ICMS,0),2), '999999999990.00')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VL_ICMS,0),2), '999999999990.00')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(V_VL_BC_ICMS_ST,0),2), '999999999990.00')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(V_VL_ICMS_ST,0),2), '999999999990.00')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(V_VL_RED_BC,0),2), '999999999990.00')), '.', ',')    || '|' ||
                                  T.COD_OBS   || '|' ) ARQ_D590, T.VL_ICMS, T.CDFISCOPERNF, T.COD_OBS, T.DSCFOPLMAG
                             FROM (SELECT I.VRALIQNF + CASE WHEN V_IDSOMAFECP = 'S' OR C_REGD500.IDENTRSAIDOP = 'E' THEN NVL(I.VRADICALIQFECP,0) ELSE 0 END AS VRALIQNF,
                                          REPLACE(I.CDFISCOPERNF,'.','') AS CDFISCOPERNF,
                                          LPAD(I.CDSITUTRIBNF, 3, '0') AS CDSITUTRIBNF,
                                          NVL(SUM(ROUND(I.VRCONTABILNF,2)),0) AS VL_OPR,
                                          NVL(SUM(ROUND(DECODE(I.CDFISCALNF, '1', I.VRBASECALCNF, 0),2)),0) AS VL_BC_ICMS,
                                          NVL(SUM(ROUND(DECODE(I.CDFISCALNF, '1', I.VRIMPOSTONF+CASE WHEN V_IDSOMAFECP = 'S' OR C_REGD500.IDENTRSAIDOP = 'E' THEN NVL(I.VRADICFECP,0) ELSE 0 END, 0),2)),0) AS VL_ICMS,
                                          C.CDTIPOOPER||C.CDCFOPLMAG AS COD_OBS, C.DSCFOPLMAG
                                     FROM IMPOSTONF I, IMPOSTO IM, CFOPLIVROMAGTPOP C
                                    WHERE I.CDFILIAL        = C_REGD500.CDFILIAL
                                      AND I.NRLANCTONF      = C_REGD500.NRLANCTONF
                                      AND IM.CDIMPOSTONFE   = P_CDIMPOSTO
                                      AND IM.CDIMPOSTO      = I.CDIMPOSTO
                                      AND I.IDIMPOSTONF     = 1
                                      AND I.NRSEQUMAPAPU    = V_NRSEQUMAPAPU
                                      AND C.IDENTRSAIDOP(+) = C_REGD500.IDENTRSAIDOP
                                      AND C.CDTIPOOPER(+)   = C_REGD500.CDTIPOOPER
                                      AND C.CDCFOPLMAG(+)   = I.CDFISCOPERNF
                                      AND C.CDLIVROMAG(+)   = P_CDLIVROMAGADIC
                                    GROUP BY I.VRALIQNF, I.VRADICALIQFECP, REPLACE(I.CDFISCOPERNF,'.',''), LPAD(I.CDSITUTRIBNF, 3, '0'), C.CDTIPOOPER||C.CDCFOPLMAG, C.DSCFOPLMAG) T) LOOP
            V_VL_BC_ICMS_ST := 0;
            V_VL_ICMS_ST := 0;
            V_VL_RED_BC := 0;
            V_VL_IP := 0;

             V_COD_OBS := NULL;
            SELECT MAX(CDCHAVEREG)
              INTO V_COD_OBS
              FROM TMP_REGDADOSSPED
             WHERE CDCHAVEREG = C_REGD590.COD_OBS
               AND CDREGISTRO = '0460';

            IF (V_COD_OBS IS NULL) AND (C_REGD590.COD_OBS IS NOT NULL) THEN
               V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
               INSERT INTO TMP_REGDADOSSPED
                  (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
               VALUES
                  ('0460', V_SEQUPROD, C_REGD590.COD_OBS, C_REGD590.DSCFOPLMAG);
            END IF;
            V_CONTREGD := NVL(V_CONTREGD,0) + 1;
            INSERT INTO ARQMAGNETICO
               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
               ('43'||LPAD(RTRIM(V_CONTREGD), 8, '0'), C_REGD590.ARQ_D590, P_NRSEQARQMAG);
         END LOOP;
      END LOOP;
   END IF;

   IF V_CONTREGD <> 0 THEN
      V_MOVIMENT := 0;
   ELSE
      V_MOVIMENT := 1;
   END IF;
   /* ABERTURA DO BLOCO D */
   V_CONTREGD := NVL(V_CONTREGD,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('41'||LPAD(RTRIM(V_CONTREGD), 8, '0'), '|D001'|| '|' ||V_MOVIMENT|| '|' , P_NRSEQARQMAG);
   /* ENCERRAMENTO DO BLOCO D */
   V_CONTREGD := NVL(V_CONTREGD,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('45'||LPAD(RTRIM(V_CONTREGD), 8, '0'), '|D990'|| '|' ||V_CONTREGD|| '|', P_NRSEQARQMAG);
   RETURN(LPAD(TO_CHAR(NVL(V_CONTREGD,0)),10, '0')||LPAD(TO_CHAR(NVL(V_SEQUPROD,0)),10, '0'));
END;
/