create or replace FUNCTION ARQDIGITAL_BLOCOC(
   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_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE) RETURN NUMBER
AS
   V_CONTREGC     NUMBER;
   V_MOVIMENT     NUMBER;
   V_VRCANCELA    NUMBER;
   V_SEQUENCIAL   VARCHAR2(100);
   V_IDFRETE      VARCHAR2(1);
   V_IDSEGUR      VARCHAR2(1);
   V_IDOUTROS     VARCHAR2(1);
   V_CDIMPOSTRIB  VARCHAR2(02);
   V_DADOS_INF    VARCHAR2(10);
   V_LINHA        VARCHAR2(4000);
   V_LINHA2       VARCHAR2(4000);
   V_LINHA4       VARCHAR2(4000);
   V_SEQUC600     VARCHAR2(100);
   V_NRECFREDZ    VARCHAR2(100);
   V_COD_PAR      VARCHAR2(100);
   V_SEQUPROD     NUMBER;
   V_PRODUTO      VARCHAR2(100);
   V_FCP          VARCHAR2(10);
   V_TEMFCP       VARCHAR2(01);
BEGIN
   BEGIN
      SELECT EN.IDENCARGO
      INTO V_IDFRETE
      FROM RELACENCARGO E, ENCARGO EN
      WHERE E.CDENCARGO = EN.CDENCARGO AND
            E.IDENCARGODOS LIKE '%FRET%';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
      V_IDFRETE    := NULL;
   END;

   BEGIN
      SELECT EN.IDENCARGO
      INTO V_IDSEGUR
      FROM RELACENCARGO E, ENCARGO EN
      WHERE E.CDENCARGO = EN.CDENCARGO AND
            E.IDENCARGODOS LIKE '%SEGU%';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
      V_IDSEGUR    := 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;

   V_TEMFCP := 'S';
   FOR C_REGC020 IN (SELECT ('|C020'                                                              || '|' ||
                            NF.CAMPO2                                                            || '|' ||
                            NF.CAMPO3                                                            || '|' ||
                            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.IDENTRSAIDOP,'S', (CASE WHEN NF.DTEMISSAO < P_DTINICIAL THEN '01' ELSE '00' END), DECODE(NF.IDENTRSAIDOP,'E', (CASE WHEN NF.DTENTRSAID < P_DTINICIAL THEN '01' ELSE '00' END),'00'))) || '|' ||
                            DECODE(NF.IDENTRSAIDOP, 'E', NF.CDSERIEENTR, NF.CDSERIE)             || '|' ||
                            NF.NRNOTAFISC                                                        || '|' ||
                            TO_CHAR(NF.DTEMISSAO,'DDMMYYYY')                                     || '|' ||
                            TO_CHAR(NF.DTENTRSAID,'DDMMYYYY')                                    || '|' ||
                            NF.CDFISCOPERNF                                                      || '|' ||
                            REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRCONTABILNF,2), '999999999990.00')), '.', ',') || '|' ||
                            DECODE(PGTO.DTORIGVENREC, NF.DTEMISSAO, '0', '1')                    || '|' ||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_ENCARGO(NF.CDFILIAL, NF.NRLANCTONF, 'D'),2), '999999999990.00')), '.', ',') || '|' ||
                            REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRPRODUTOS,2), '999999999990.00')), '.', ',') || '|' ||
                            DECODE(NVL(NF.IDPGTOFRETE,'0'), '0', '0', '1')                       || '|' ||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_ENCARGO(NF.CDFILIAL, NF.NRLANCTONF, V_IDFRETE),2), '999999999990.00')), '.', ',') || '|' ||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_ENCARGO(NF.CDFILIAL, NF.NRLANCTONF, V_IDSEGUR),2), '999999999990.00')), '.', ',') || '|' ||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_ENCARGO(NF.CDFILIAL, NF.NRLANCTONF, V_IDOUTROS),2), '999999999990.00')), '.', ',') || '|' ||
                            REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRBASECALCNF,2), '999999999990.00')), '.', ',') || '|' ||
                            REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRIMPOSTONF,2), '999999999990.00')), '.', ',') || '|' ||
                            REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRBASECALCNF,2), '999999999990.00')), '.', ',') || '|' ||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(NF.CDFILIAL, NF.NRLANCTONF, '80', 'I'),2), '999999999990.00')), '.', ',') || '|' ||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(NF.CDFILIAL, NF.NRLANCTONF, '10', 'I'),2), '999999999990.00')), '.', ',') || '|') AS ARQ_C020,

                            NF.CDFILIAL, NF.NRLANCTONF, NF.NRINSJURTRAN, NF.IDTPIJURTRAN, NF.VRPBRUTOVOLU, NF.VRPLIQUVOLU,
                            NF.NRPLACAVEIC, NF.SGUFVEIC, NF.DSQTDEVOLU, NF.DSESPECVOLU, NF.DSMARCAVOLU, NF.DSNUMEVOLU, NF.CDFISCOPERNF,
                            DECODE(NF.IDORIGDESTNF, 'F', NF.CDFORNECED, DECODE(NF.IDORIGDESTNF, 'C', NF.CDCLIENTE , DECODE(NF.IDENTRSAIDOP, 'E', NF.CDFILIEMIT, NF.CDFILIRECE))) AS CPF_CNPJ, NF.IDORIGDESTNF,
                            NF.IDENTRSAIDOP
                       FROM (SELECT '1' AS CAMPO2, '0' AS CAMPO3,
                                    I.CDFILIAL, I.NRLANCTONF, N.IDENTRSAIDOP, N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR,
                                    N.NRINSJURNF, N.IDSTATUS, N.DTENTRSAID, N.VRNOTAFISC, N.VRPRODUTOS, N.IDPGTOFRETE,
                                    N.NRPLACAVEIC, N.SGUFVEIC, 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(I.VRCONTABILNF) AS VRCONTABILNF, N.NRINSJURTRAN, N.IDTPIJURTRAN, N.VRPBRUTOVOLU, N.VRPLIQUVOLU,
                                    SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                                    SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0))  AS VRIMPOSTONF,
                                    SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0))   AS VROUTRCRED,
                                    SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,'1',DECODE(I.VRPERREDBCNF,0, 0,I.VRCONTABILNF-I.VRBASECALCNF), 0)) AS VRISENTRIB
                               FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, IMPOSTO P
                              WHERE M.CDEMPRESA       = P_CDEMPRESA
                                AND M.CDINSCESTA      = P_CDINSCESTA
                                AND M.DTINIPERIODO    = P_DTINICIAL
                                AND M.DTFIMPERIODO    = P_DTFINAL
                                AND M.CDIMPOSTO       = P_CDIMPOSTO
                                AND I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU
                                AND I.CDIMPOSTO       = P.CDIMPOSTO
                                AND P.CDIMPOSTONFE    = P_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 N.IDENTRSAIDOP    = 'S'
                                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 ('01', '04', '55')
                              GROUP BY I.CDFILIAL,  I.NRLANCTONF, N.IDENTRSAIDOP, N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR,
                                    N.NRINSJURNF,  N.IDSTATUS, 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.SGUFVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU, N.DSNUMEVOLU
                              UNION ALL
                             SELECT '0' AS CAMPO2, '1' AS CAMPO3,
                                    I.CDFILIAL, I.NRLANCTONF, N.IDENTRSAIDOP, N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR,
                                    N.NRINSJURNF, N.IDSTATUS, N.DTENTRSAID, N.VRNOTAFISC, N.VRPRODUTOS, N.IDPGTOFRETE,
                                    N.NRPLACAVEIC, N.SGUFVEIC, 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(I.VRCONTABILNF) AS VRCONTABILNF, N.NRINSJURTRAN, N.IDTPIJURTRAN, N.VRPBRUTOVOLU, N.VRPLIQUVOLU,
                                    SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                                    SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0))  AS VRIMPOSTONF,
                                    SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0))   AS VROUTRCRED,
                                    SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,'1',DECODE(I.VRPERREDBCNF,0, 0,I.VRCONTABILNF-I.VRBASECALCNF), 0)) AS VRISENTRIB
                               FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, IMPOSTO P
                              WHERE M.CDEMPRESA       = P_CDEMPRESA
                                AND M.CDINSCESTA      = P_CDINSCESTA
                                AND M.DTINIPERIODO    = P_DTINICIAL
                                AND M.DTFIMPERIODO    = P_DTFINAL
                                AND M.CDIMPOSTO       = P_CDIMPOSTO
                                AND I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU
                                AND I.CDIMPOSTO       = P.CDIMPOSTO
                                AND P.CDIMPOSTONFE    = P_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 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 ('01', '04', '55')
                              GROUP BY I.CDFILIAL, I.NRLANCTONF, N.IDENTRSAIDOP, N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR,
                                    N.NRINSJURNF, N.IDSTATUS, 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.SGUFVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU, N.DSNUMEVOLU) 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

      IF C_REGC020.IDENTRSAIDOP = 'E' THEN
         SELECT DECODE(MAX(IDIMPOSTONF),'3','AAAAAFCP','')
           INTO V_FCP      /*FUNDO DE COMBATE A PROBREZA*/
           FROM IMPOSTONF
          WHERE CDFILIAL    = C_REGC020.CDFILIAL
            AND NRLANCTONF  = C_REGC020.NRLANCTONF
            AND CDIMPOSTO   = P_CDIMPOSTO
            AND IDIMPOSTONF = '3'; /*DIFERENCIAL DE ALIQUOTA*/
            --AND CDFISCOPERNF IN ('2351', '2352','2406', '2407', '2551', '2556', '2406','2407');  /*CFOPS DE ENTRADA PARA ATIVO PERMANENTE E USO CONSUMO FORA ESTADO*/

         IF (V_FCP = 'AAAAAFCP') AND (V_TEMFCP = 'S') THEN     /*PARA GERAR APENAS UMA LINHA NO REGISTRO 0450*/
            V_TEMFCP := 'N';
            INSERT INTO ARQMAGNETICO
               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
               ('0900000000', '|0450|AAAAAFCP|OPERACOES SUJEITAS AO ADICIONAL DE 2% DO FUNDO DE COMBATE A PROBREZA|', P_NRSEQARQMAG);
         END IF;
      ELSE
         V_FCP := '';
      END IF;

      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('19'||LPAD(RTRIM(V_CONTREGC), 8, '0'), C_REGC020.ARQ_C020||V_FCP||'|', P_NRSEQARQMAG);
      V_CONTREGC :=  NVL(V_CONTREGC,0) + 1;

      /* CODIGO DO PARTICIPANTE */
      SELECT MAX(CDCHAVEREG)
        INTO V_COD_PAR
        FROM TMP_LVELETRONICO
       WHERE CDCHAVEREG = C_REGC020.CPF_CNPJ
         AND CDREGISTRO = '0150';

      IF (V_COD_PAR IS NULL) AND (C_REGC020.CPF_CNPJ IS NOT NULL) THEN
         V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
         INSERT INTO TMP_LVELETRONICO
            (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
         VALUES
            ('0150', V_SEQUPROD, C_REGC020.CPF_CNPJ, C_REGC020.IDORIGDESTNF);
      END IF;

      FOR C_REGC250 IN (SELECT ('|C250'                    || '|' ||
                               T.CDFORNECED               || '|' ||
                               C_REGC020.NRPLACAVEIC      || '|' ||
                               C_REGC020.SGUFVEIC         || '|' ||
                               C_REGC020.DSQTDEVOLU       || '|' ||
                               C_REGC020.DSESPECVOLU      || '|' ||
                               C_REGC020.DSMARCAVOLU      || '|' ||
                               C_REGC020.DSNUMEVOLU       || '|' ||
                               REPLACE(LTRIM(TO_CHAR(ROUND(C_REGC020.VRPBRUTOVOLU,3), '999999999990.000')), '.', ',')     || '|' ||
                               REPLACE(LTRIM(TO_CHAR(ROUND(C_REGC020.VRPLIQUVOLU,3), '999999999990.000')), '.', ',')      || '|'  ) AS ARQ_C250,
                               T.CDFORNECED
                          FROM TRANSPORT T
                         WHERE T.IDTPIJURTRAN = C_REGC020.IDTPIJURTRAN
                           AND T.NRINSJURTRAN = C_REGC020.NRINSJURTRAN) LOOP

         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            ('19'||LPAD(RTRIM(V_CONTREGC), 8, '0'), C_REGC250.ARQ_C250, P_NRSEQARQMAG);
         V_CONTREGC :=  NVL(V_CONTREGC,0) + 1;

         /* CODIGO DO PARTICIPANTE */
         SELECT MAX(CDCHAVEREG)
           INTO V_COD_PAR
           FROM TMP_LVELETRONICO
          WHERE CDCHAVEREG = C_REGC250.CDFORNECED
            AND CDREGISTRO = '0150';

         IF (V_COD_PAR IS NULL) AND (C_REGC250.CDFORNECED IS NOT NULL) THEN
            V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
            INSERT INTO TMP_LVELETRONICO
              (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
            VALUES
              ('0150', V_SEQUPROD, C_REGC250.CDFORNECED, 'F');
         END IF;
      END LOOP;

      V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER('1'))),3,'0');
      SELECT SUM(NRLANCTONF)
        INTO V_DADOS_INF
        FROM ITEMPROIMPOS
       WHERE CDFILIAL   = C_REGC020.CDFILIAL
         AND NRLANCTONF = C_REGC020.NRLANCTONF;

      IF (V_DADOS_INF IS NOT NULL) THEN
         FOR C_REGC300 IN (SELECT (PR.CDARVPROD                                     || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(IT.VRUNITITEM,3), '999999999990.000')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(IT.QTITEMPROD,3), '999999999990.000')), '.', ',')    || '|' ||
                                  PR.SGUNIDADE                                     || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(IT.VRTOTAITEM,2), '999999999990.00')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(IT.VRDESCITEM,2), '999999999990.00')), '.', ',')    || '|' ||
                                  '0'                                              || '|' ||
                                  SUBSTR(NVL(IT.CDPRODNCMTRANS,PR.CDCLASFISC),3,9)     || '|' ||
                                  ''                                               || '|' ||
                                  ''                                               || '|' ||
                                  LPAD(NVL(IP.CDSITUTRIBIT,0), 3, '0')             || '|' ||
                                  IP.CDFISCOPERIT                                  || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(IP.CDFISCALIT, '1', IP.VRBASECALCIT, 0),2), '999999999990.00')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(IP.CDFISCALIT, '1', IP.VRALIQITEM, 0),2), '999999999990.00')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(IP.CDFISCALIT, '1', IP.VRIMPOSTOIT, 0),2), '999999999990.00')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL, IP.NRLANCTONF, IP.NRSEQITPROD, '80','B'),2), '999999999990.00')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL, IP.NRLANCTONF, IP.NRSEQITPROD, '80','A'),2), '999999999990.00')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL, IP.NRLANCTONF, IP.NRSEQITPROD, '80','I'),2), '999999999990.00')), '.', ',')    || '|' ||
                                  SUBSTR(CDSITUTRIBIT,2,3)                         || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL, IP.NRLANCTONF, IP.NRSEQITPROD, '10','B'),2), '999999999990.00')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL, IP.NRLANCTONF, IP.NRSEQITPROD, '10','A'),2), '999999999990.00')), '.', ',')    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL, IP.NRLANCTONF, IP.NRSEQITPROD, '10','I'),2), '999999999990.00')), '.', ',')    || '|' ) AS ARQ_C300,
                                  PR.CDPRODUTO, PR.CDARVPROD
                             FROM ITEMPRODUTO IT, PRODUTO PR, ITEMPROIMPOS IP, IMPOSTO P
                            WHERE IT.CDFILIAL      = C_REGC020.CDFILIAL
                              AND IT.NRLANCTONF    = C_REGC020.NRLANCTONF
                              AND IT.CDFILIAL      = IP.CDFILIAL
                              AND IT.NRLANCTONF    = IP.NRLANCTONF
                              AND IT.NRSEQITPROD   = IP.NRSEQITPROD
                              AND IT.CDPRODUTO     = PR.CDPRODUTO
                              AND IP.CDIMPOSTO     = P.CDIMPOSTO
                              AND P.CDIMPOSTONFE   = P_CDIMPOSTO
                              AND IP.NRITPROIMPOS  = '1') LOOP

            INSERT INTO ARQMAGNETICO
               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
               ('19'||LPAD(RTRIM(V_CONTREGC), 8, '0'), '|C300'|| '|' ||V_SEQUENCIAL|| '|' || C_REGC300.ARQ_C300, P_NRSEQARQMAG);
            V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_SEQUENCIAL))+1)), 3,'0');
            V_CONTREGC :=  NVL(V_CONTREGC,0) + 1;

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

            IF V_PRODUTO IS NULL THEN
               V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
               INSERT INTO TMP_LVELETRONICO
                 (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
               VALUES
                 ('0200', V_SEQUPROD, C_REGC300.CDPRODUTO, C_REGC300.CDARVPROD);
            END IF;
            V_PRODUTO := '';
         END LOOP;
      END IF;
   END LOOP;
   FOR C_REGC550 IN(SELECT DISTINCT ('|C550'                                                     || '|' ||
                                     CPF                                                         || '|' || -- CPF
                                     CNPJ                                                        || '|' || -- CNPJ
                                     MODELO                                                      || '|' || -- MODELO DE DOCUMENTO
                                     COD_SIT                                                     || '|' || -- SITUACAO DO DOCUMENTO
                                     SERIE                                                       || '|' || -- SERIE
                                     ''                                                          || '|' || -- SUBSERIE
                                     NUMERO                                                      || '|' || -- NUMERO DA NOTA FISCAL
                                     TO_CHAR(EMISSAO,'DDMMYYYY')                                 || '|' || -- DTEMISSAO
                                     REPLACE(LTRIM(TO_CHAR(ROUND(VRNOTAFISC,2), '999999999990.00')), '.', ',')    || '|' || -- VALOR DA NOTA FISCAL
                                     REPLACE(LTRIM(TO_CHAR(ROUND(VRDESCONTO,2), '999999999990.00')), '.', ',')    || '|' || -- VALOR DO DESCONTO
                                     REPLACE(LTRIM(TO_CHAR(ROUND(VRPRODUTOS,2), '999999999990.00')), '.', ',')    || '|' || -- VALOR DOS PRODUTOS
                                     REPLACE(LTRIM(TO_CHAR(ROUND(VRBASE,2), '999999999990.00')), '.', ',')        || '|' || -- VALOR DA BASE DE CALCULO
                                     REPLACE(LTRIM(TO_CHAR(ROUND(VRIMPOSTO,2), '999999999990.00')), '.', ',')     || '|' || -- VALOR DO IMPOSTO
                                     ''                                                            || '|'  ) AS ARQ_C550,
                                     CDFILIAL, CDCAIXA, NRLANCTONF, MODELO
                      FROM (/*SELECT N.CDMODDOCFISC AS MODELO,
                                     DECODE(N.IDSTATUS, 'C', '02', '00') AS COD_SIT,
                                     N.CDSERIE AS SERIE,
                                     N.NRNOTAFISC AS NUMERO,
                                     N.DTEMISSAO AS EMISSAO,
                                     N.VRNOTAFISC,
                                     VALOR_ENCARGO(N.CDFILIAL, N.NRLANCTONF, DESCONTO.IDENCARGO, DESCONTO.CDENCARGO) AS VRDESCONTO,
                                     N.VRPRODUTOS,
                                     N.VRBASECALCNF AS VRBASE,
                                     N.VRIMPOSTONF  AS VRIMPOSTO,
                                     N.CDFILIAL, '' AS CDCAIXA, N.NRLANCTONF,
                                     CPF,
                                     CNPJ
                                FROM ITEMPRODUTO IP, ITEMVENDA IV, MOVICLIE MC,
                                     (SELECT MAX(E.CDENCARGO) AS CDENCARGO,
                                             MAX(EN.IDENCARGO) AS IDENCARGO
                                        FROM RELACENCARGO E, ENCARGO EN
                                       WHERE E.CDENCARGO  = EN.CDENCARGO AND
                                             EN.IDENCARGO = 'D'          AND
                                             E.IDENCARGODOS LIKE '%DESC%') DESCONTO,
                                     (SELECT DECODE(IDTPIJURCLIE, 'F', NRINSJURCLIE, '') AS CPF,
                                             DECODE(IDTPIJURCLIE, 'J', NRINSJURCLIE, '') AS CNPJ,
                                             I.CDFILIAL, I.NRLANCTONF, N.CDSERIE, N.NRNOTAFISC, N.DTEMISSAO, N.VRNOTAFISC,
                                             N.VRPRODUTOS, N.IDSTATUS, N.CDMODDOCFISC,
                                             SUM(I.VRCONTABILNF) AS VRCONTABILNF,
                                             SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                                             SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0))  AS VRIMPOSTONF,
                                             SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0))   AS VROUTRCRED,
                                             SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,'1',DECODE(I.VRPERREDBCNF,0, 0,I.VRCONTABILNF-I.VRBASECALCNF), 0)) AS VRISENTRIB
                                       FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, CLIENTE C, IMPOSTO P
                                      WHERE M.CDEMPRESA       = P_CDEMPRESA
                                        AND M.CDINSCESTA      = P_CDINSCESTA
                                        AND M.DTINIPERIODO    = P_DTINICIAL
                                        AND M.DTFIMPERIODO    = P_DTFINAL
                                        AND M.CDIMPOSTO       = P_CDIMPOSTO
                                        AND I.CDIMPOSTO       = P.CDIMPOSTO
                                        AND P.CDIMPOSTONFE    = P_CDIMPOSTO
                                        AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                        AND I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU
                                        AND N.CDFILIAL        = I.CDFILIAL
                                        AND N.NRLANCTONF      = I.NRLANCTONF
                                        AND N.CDCLIENTE       = C.CDCLIENTE
                                        AND ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E'))
                                        AND N.IDENTRSAIDOP    = 'S'
                                        AND N.CDMODDOCFISC = '02'
                                      GROUP BY DECODE(IDTPIJURCLIE, 'F', NRINSJURCLIE, ''),
                                               DECODE(IDTPIJURCLIE, 'J', NRINSJURCLIE, ''),
                                               I.CDFILIAL, I.NRLANCTONF, N.CDSERIE, N.NRNOTAFISC, N.DTEMISSAO, N.VRNOTAFISC,
                                               N.VRPRODUTOS,N.IDSTATUS, N.CDMODDOCFISC) N
                            WHERE N.CDFILIAL        = IP.CDFILIAL                   AND
                                  N.NRLANCTONF      = IP.NRLANCTONF                 AND
                                  IP.CDFILIAL       = IV.CDFILIAL(+)                AND
                                  IP.CDCAIXA        = IV.CDCAIXA  (+)               AND
                                  IP.NRSEQVENDA     = IV.NRSEQVENDA (+)             AND
                                  IP.NRSEQUITVEND   = IV.NRSEQUITVEND(+)            AND
                                  IV.CDFILIAL       = MC.CDFILIAL (+)               AND
                                  IV.CDCAIXA        = MC.CDCAIXA   (+)              AND
                                  IV.NRSEQVENDA     = MC.NRSEQVENDA (+)
                            UNION ALL*/
                           SELECT '65' AS MODELO,
                                  DECODE(V.IDSTATUSNFCE, 'C', '02', '00') AS COD_SIT,
                                  V.CDSERIENFCE    AS SERIE,
                                  V.NRNOTAFISCALCE AS NUMERO,
                                  V.DTEMISSAONFCE  AS EMISSAO,
                                  SUM(TRUNC(I.QTPRODVEND*(I.VRUNITVEND+NVL(I.VRUNITVENDCL,0)),2)-NVL(I.VRDESITVEND,0)+NVL(I.VRACRITVEND,0)) AS VRNOTAFISC,
                                  SUM(NVL(I.VRDESITVEND,0)) AS VRDESCONTO,
                                  SUM(TRUNC(I.QTPRODVEND*(I.VRUNITVEND+NVL(I.VRUNITVENDCL,0)),2)-NVL(I.VRDESITVEND,0)+NVL(I.VRACRITVEND,0)) AS VRPRODUTOS,
                                  NVL(IP.VRBASE,0) AS VRBASE,
                                  NVL(IP.VRIMPOPRODIT,0) AS VRIMPOSTO,
                                  V.CDFILIAL, V.CDCAIXA, V.NRSEQVENDA AS NRLANCTONF,
                                  DECODE(LENGTH(NVL(REPLACE(REPLACE(REPLACE(REPLACE(V.NRINSCRCONS,'.'),'-'),'/'),' '),'')), 11, REPLACE(REPLACE(REPLACE(REPLACE(V.NRINSCRCONS,'.'),'-'),'/'),' '), '') AS CPF,
                                  DECODE(LENGTH(NVL(REPLACE(REPLACE(REPLACE(REPLACE(V.NRINSCRCONS,'.'),'-'),'/'),' '),'')), 14, REPLACE(REPLACE(REPLACE(REPLACE(V.NRINSCRCONS,'.'),'-'),'/'),' '), '')  AS CNPJ
                             FROM VENDA V, FILIAL FI, ITEMVENDA I, (SELECT SUM(CASE WHEN I.VRPRBCREDUICMS > 0
																					THEN I.VRIMPOPRODREDUZ
																					ELSE TRUNC(I.VRIMPOPRODIT,2) END) AS VRIMPOPRODIT,
																		   SUM(CASE WHEN I.VRPRBCREDUICMS > 0
																					THEN I.VRBASECALCREDUZ
																					ELSE TRUNC(IT.QTPRODVEND*(IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0)),2)-NVL(IT.VRDESITVEND,0)+NVL(IT.VRACRITVEND,0) END) AS VRBASE,
                                                                           I.CDFILIAL, I.NRSEQVENDA, I.CDCAIXA
                                                                      FROM VENDA V, ITEMVENDA IT, ITVENDAIMPOS I, IMPOSTO IM, FILIAL FI
                                                                     WHERE I.CDIMPOSTO = IM.CDIMPOSTO
                                                                       AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                                                                       AND V.CDFILIAL  = I.CDFILIAL
                                                                       AND V.CDCAIXA   = V.CDCAIXA
                                                                       AND V.NRSEQVENDA = V.NRSEQVENDA
                                                                       AND V.IDSTATUSNFCE IN ('A')
                                                                       AND V.IDTPEMISVEND  = 'N'
                                                                       AND V.IDAPROVACAONFCE = 'A'
                                                                       AND TRUNC(V.DTEMISSAONFCE) BETWEEN P_DTINICIAL AND P_DTFINAL
                                                                       AND V.IDSITUVENDA   = 'O'
                                                                       AND V.IDTPAMBNFCE   = '1'
                                                                       AND IM.IDTPIMPOSFIS = 'T'
                                                                       AND V.CDFILIAL     = IT.CDFILIAL
                                                                       AND V.CDCAIXA      = IT.CDCAIXA
                                                                       AND V.NRSEQVENDA   = IT.NRSEQVENDA
                                                                       AND V.CDFILIAL     = I.CDFILIAL
                                                                       AND V.CDCAIXA      = I.CDCAIXA
                                                                       AND V.NRSEQVENDA   = I.NRSEQVENDA
                                                                       AND IT.CDFILIAL    = I.CDFILIAL
                                                                       AND IT.CDCAIXA     = I.CDCAIXA
                                                                       AND IT.NRSEQVENDA  = I.NRSEQVENDA
                                                                       AND V.CDFILIAL     = FI.CDFILIAL
                                                                       AND IT.CDFILIAL    = FI.CDFILIAL
                                                                       AND I.CDFILIAL     = FI.CDFILIAL
                                                                       AND I.NRSEQUITVEND = IT.NRSEQUITVEND
                                                                       AND FI.CDEMPRESA   = V.CDEMPRESA
                                                                       AND FI.CDINSCESTA  = P_CDINSCESTA
                                                                       AND FI.CDEMPRESA   = P_CDEMPRESA
                                                                     GROUP BY I.CDFILIAL, I.NRSEQVENDA, I.CDCAIXA) IP
                            WHERE V.IDSTATUSNFCE IN ('A')
                              AND V.IDTPEMISVEND  = 'N'
                              AND V.IDAPROVACAONFCE = 'A'
                              AND TRUNC(V.DTEMISSAONFCE) BETWEEN P_DTINICIAL AND P_DTFINAL
                              AND V.IDSITUVENDA  = 'O'
                              AND V.IDTPAMBNFCE  = '1'
                              AND FI.CDINSCESTA  = P_CDINSCESTA
                              AND FI.CDEMPRESA   = V.CDEMPRESA
                              AND FI.CDEMPRESA   = P_CDEMPRESA
                              AND FI.CDFILIAL    = V.CDFILIAL
                              AND I.CDFILIAL     = V.CDFILIAL
                              AND I.NRSEQVENDA   = V.NRSEQVENDA
                              AND I.CDCAIXA      = V.CDCAIXA
                              AND I.CDFILIAL     = IP.CDFILIAL(+)
                              AND I.CDCAIXA      = IP.CDCAIXA(+)
                              AND I.NRSEQVENDA   = IP.NRSEQVENDA(+)
                            GROUP BY V.CDFILIAL, V.CDCAIXA, V.NRSEQVENDA, DECODE(V.IDSTATUSNFCE, 'C', '02', '00'), V.CDSERIENFCE, V.NRNOTAFISCALCE, V.DTEMISSAONFCE,
                                     NVL(IP.VRIMPOPRODIT,0), NVL(IP.VRBASE,0),
                                     DECODE(LENGTH(NVL(REPLACE(REPLACE(REPLACE(REPLACE(V.NRINSCRCONS,'.'),'-'),'/'),' '),'')), 11, REPLACE(REPLACE(REPLACE(REPLACE(V.NRINSCRCONS,'.'),'-'),'/'),' '), ''),
                                     DECODE(LENGTH(NVL(REPLACE(REPLACE(REPLACE(REPLACE(V.NRINSCRCONS,'.'),'-'),'/'),' '),'')), 14, REPLACE(REPLACE(REPLACE(REPLACE(V.NRINSCRCONS,'.'),'-'),'/'),' '), ''))) LOOP
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('20'||LPAD(RTRIM(V_CONTREGC), 8, '0'), C_REGC550.ARQ_C550, P_NRSEQARQMAG);
         V_CONTREGC := NVL(V_CONTREGC,0) + 1;
         V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER('1'))),3,'0');

      FOR C_REGC555 IN (SELECT (CDARVPROD                                                                     || '|' ||
                                REPLACE(LTRIM(TO_CHAR(ROUND(QTITEMPROD,3), '999999999990.000')), '.', ',')    || '|' ||
                                REPLACE(LTRIM(TO_CHAR(ROUND(VRUNITITEM,3), '999999999990.000')), '.', ',')    || '|' ||
                                SGUNIDADE                                                                     || '|' ||
                                REPLACE(LTRIM(TO_CHAR(ROUND(VRTOTAITEM,2), '999999999990.00')), '.', ',')     || '|' ||
                                REPLACE(LTRIM(TO_CHAR(ROUND(ABS(VRDESCITEM),2), '999999999990.00')), '.', ',')|| '|' ||
                                CST                                                                           || '|' ||
                                CFOP                                                                          || '|' ||
                                REPLACE(LTRIM(TO_CHAR(ROUND(VRBASE,2), '999999999990.00')), '.', ',')         || '|' ||
                                REPLACE(LTRIM(TO_CHAR(VRALIQITEM, '90.00')), '.', ',')                        || '|' ||
                                REPLACE(LTRIM(TO_CHAR(ROUND(VRIMPOSTOIT,2), '999999999990.00')), '.', ',')    || '|'  ) AS ARQ_C555,
                                CDPRODUTO, CDARVPROD
                          FROM (SELECT PR.CDARVPROD,
                                       PR.CDPRODUTO,
                                       PR.SGUNIDADE,
                                       IP.QTITEMPROD,
                                       IP.VRUNITITEM,
                                       IP.VRTOTAITEM,
                                       IP.VRDESCITEM,
                                       IT.CDSITUTRIBIT AS CST,
                                       IT.CDFISCOPERIT AS CFOP,
                                       SUM(DECODE(IT.CDFISCALIT, '1', IT.VRBASECALCIT, 0)) AS VRBASE,
                                       DECODE(IT.CDFISCALIT, '1', IT.VRALIQITEM, 0) AS VRALIQITEM,
                                       SUM(DECODE(IT.CDFISCALIT, '1', IT.VRIMPOSTOIT, 0)) AS VRIMPOSTOIT
                                  FROM ITEMPRODUTO IP, PRODUTO PR, ITEMPROIMPOS IT
                                 WHERE C_REGC550.CDFILIAL   = IP.CDFILIAL
                                   AND C_REGC550.NRLANCTONF = IP.NRLANCTONF
                                   AND C_REGC550.MODELO     <> '65'
                                   AND IP.CDFILIAL          = IT.CDFILIAL
                                   AND IP.NRLANCTONF        = IT.NRLANCTONF
                                   AND IP.NRSEQITPROD       = IT.NRSEQITPROD
                                   AND PR.CDPRODUTO         = IP.CDPRODUTO
                                 GROUP BY PR.CDARVPROD, PR.CDPRODUTO, IP.QTITEMPROD, IP.VRUNITITEM,
                                          PR.SGUNIDADE, IP.VRTOTAITEM, IP.VRDESCITEM, IT.CDSITUTRIBIT,
                                          IT.CDFISCOPERIT, DECODE(IT.CDFISCALIT, '1', IT.VRALIQITEM, 0)
                                 UNION ALL
                                SELECT PR.CDARVPROD,
                                       PR.CDPRODUTO,
                                       PR.SGUNIDADE,
                                       IT.QTPRODVEND AS QTITEMPROD,
                                       (IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0)+NVL(IT.VRACRITVEND,0)) AS VRUNITITEM,
                                       SUM(TRUNC((IT.QTPRODVEND  * (IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0))) - NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0),2)) AS VRTOTAITEM,
                                       NVL(VRDESITVEND,0) AS VRDESCITEM,
                                       NVL(LPAD(CDCSTPRODI,3,'0'), (CASE WHEN IM.IDTPIMPOSFIS = 'T' THEN '000'
                                                                         WHEN IM.IDTPIMPOSFIS = 'I' THEN '040'
                                                                         WHEN IM.IDTPIMPOSFIS = 'F' THEN '060'
                                                                         WHEN IM.IDTPIMPOSFIS = 'N' THEN '041' END)) AS CST,
                                       NVL(CDCFOPPROD, '5102') AS CFOP,
                                       SUM(CASE WHEN IM.IDTPIMPOSFIS = 'T' AND IM.CDIMPOSTONFE = P_CDIMPOSTO 
                                                THEN CASE WHEN IP.VRPRBCREDUICMS > 0
                                                          THEN IP.VRBASECALCREDUZ
                                                          ELSE TRUNC(IT.QTPRODVEND*(IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0)),2)-NVL(IT.VRDESITVEND,0)+NVL(IT.VRACRITVEND,0) END
                                                ELSE 0 END) AS VRBASE,
                                       ROUND(IP.VRPEALPRODIT,2) AS VRALIQITEM,
                                       SUM(CASE WHEN IM.IDTPIMPOSFIS = 'T' AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                                                THEN CASE WHEN IP.VRPRBCREDUICMS > 0
												                      THEN IP.VRIMPOPRODREDUZ
												                      ELSE TRUNC(IP.VRIMPOPRODIT,2) END
                                                ELSE 0 END) AS VRIMPOSTOIT
                                  FROM PRODUTO PR, ITEMVENDA IT,  ITVENDAIMPOS IP, IMPOSTO IM
                                 WHERE IT.CDFILIAL      = IP.CDFILIAL
                                   AND IT.CDCAIXA       = IP.CDCAIXA
                                   AND IT.NRSEQUITVEND  = IP.NRSEQUITVEND
                                   AND IT.NRSEQVENDA    = IP.NRSEQVENDA
                                   AND IT.CDFILIAL      = C_REGC550.CDFILIAL
                                   AND IT.CDCAIXA       = C_REGC550.CDCAIXA
                                   AND IT.NRSEQVENDA    = C_REGC550.NRLANCTONF
                                   AND C_REGC550.MODELO = '65'
                                   AND IP.CDIMPOSTO     = IM.CDIMPOSTO
                                   AND PR.CDPRODUTO     = IT.CDPRODUTO
                                 GROUP BY NVL(CDCFOPPROD, '5102'), IM.IDTPIMPOSFIS, IP.VRPEALPRODIT, IM.CDIMPOSTONFE,
                                          NVL(VRDESITVEND,0), PR.CDARVPROD, PR.CDPRODUTO, PR.SGUNIDADE, IT.QTPRODVEND,
                                          NVL(LPAD(CDCSTPRODI,3,'0'), (CASE WHEN IM.IDTPIMPOSFIS = 'T' THEN '000'
                                                                            WHEN IM.IDTPIMPOSFIS = 'I' THEN '040'
                                                                            WHEN IM.IDTPIMPOSFIS = 'F' THEN '060'
                                                                            WHEN IM.IDTPIMPOSFIS = 'N' THEN '041' END)),
                                          (IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0)+NVL(IT.VRACRITVEND,0)))) LOOP
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('20'||LPAD(RTRIM(V_CONTREGC), 8, '0'), '|C555'|| '|' ||V_SEQUENCIAL|| '|' || C_REGC555.ARQ_C555, P_NRSEQARQMAG);
         V_CONTREGC :=  NVL(V_CONTREGC,0) + 1;
         V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_SEQUENCIAL))+1)), 3,'0');

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

         IF V_PRODUTO IS NULL THEN
            V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
            INSERT INTO TMP_LVELETRONICO
              (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
            VALUES
              ('0200', V_SEQUPROD, C_REGC555.CDPRODUTO, C_REGC555.CDARVPROD);
         END IF;
         V_PRODUTO := '';
      END LOOP;
   END LOOP;

   /* REGISTRO C600 */
   FOR C_FILIAL IN (SELECT CDFILIAL
                      FROM FILIAL
                     WHERE CDINSCESTA = P_CDINSCESTA
                       AND CDEMPRESA  = P_CDEMPRESA) LOOP
      FOR C_VENDA IN (SELECT '|C600'|| '|' ||
                             DECODE(VALIDA_CPF_CNPJ(REPLACE(REPLACE(REPLACE(NRINSCRCONS,'-',''),'.',''),'/','')), 'CPF',  REPLACE(REPLACE(REPLACE(NRINSCRCONS,'-',''),'.',''),'/',''), '') || '|' ||
                             DECODE(VALIDA_CPF_CNPJ(REPLACE(REPLACE(REPLACE(NRINSCRCONS,'-',''),'.',''),'/','')), 'CNPJ', REPLACE(REPLACE(REPLACE(NRINSCRCONS,'-',''),'.',''),'/',''), '') || '|' ||
                             '2D'|| '|' ||
                             '00'|| '|'  AS LINHA1,
                             V.CDCAIXA      || '|' AS CAIXA,
                             V.NRCUPOMFIS   || '|' AS CUPOM,
                             TO_CHAR(V.DTENTRVENDA,'DDMMYYYY')   || '|' ||
                             REPLACE(LTRIM(TO_CHAR(ROUND(SUM((IT.QTPRODVEND * (IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0)))-NVL(IT.VRDESITVEND,0)+ NVL(IT.VRACRITVEND,0)),2), '999999999990.00')), '.', ',') || '|' AS LINHA3,
                             '0,00'           || '|' ||
                             REPLACE(LTRIM(TO_CHAR(ROUND(SUM(0),2), '999999999990.00')), '.', ',') || '|' ||
                             REPLACE(LTRIM(TO_CHAR(ROUND(SUM(0),2), '999999999990.00')), '.', ',') || '|' ||
                             '0,00'           || '|' ||
                             REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(IT.VRDESITVEND,0)),2), '999999999990.00')), '.', ',') || '|' ||
                             REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(IT.VRDESITVEND,0)),2), '999999999990.00')), '.', ',') || '|' ||
                             '0,00'           || '|' ||
                             REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(IT.VRACRITVEND,0)),2), '999999999990.00')), '.', ',') || '|' ||
                             REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(IT.VRACRITVEND,0)),2), '999999999990.00')), '.', ',') || '|' ||
                             REPLACE(LTRIM(TO_CHAR(ROUND(SUM(0),2), '999999999990.00')), '.', ',') || '|' ||
                             REPLACE(LTRIM(TO_CHAR(ROUND(SUM(DECODE(I.IDTPIMPOSFIS, 'T', (TRUNC((IT.QTPRODVEND * (IT.VRUNITVEND+IT.VRUNITVENDCL)),2)-NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0)),0)),2), '999999999990.00')), '.', ',') || '|' ||
                             REPLACE(LTRIM(TO_CHAR(ROUND(SUM(DECODE(I.IDTPIMPOSFIS, 'T', ((TRUNC((IT.QTPRODVEND * (IT.VRUNITVEND+IT.VRUNITVENDCL)),2)-NVL(IT.VRDESITVEND,0)+NVL(IT.VRACRITVEND,0)) * (IV.VRPEALPRODIT/100)),0)),2), '999999999990.00')), '.', ',') || '|' ||
                             REPLACE(LTRIM(TO_CHAR(ROUND(SUM(DECODE(I.IDTPIMPOSFIS, 'I', (TRUNC((IT.QTPRODVEND * (IT.VRUNITVEND+IT.VRUNITVENDCL)),2)-NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0)),0)),2), '999999999990.00')), '.', ',') || '|' ||
                             '0,00'           || '|' ||
                             REPLACE(LTRIM(TO_CHAR(ROUND(SUM(DECODE(I.IDTPIMPOSFIS,'F',(TRUNC((IT.QTPRODVEND * (IT.VRUNITVEND+IT.VRUNITVENDCL)),2)-NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0)),0)),2), '999999999990.00')), '.', ',') || '|' AS LINHA4,
                             V.CDFILIAL, V.CDCAIXA, V.DTENTRVENDA, V.NRSEQVENDA
                        FROM VENDA V, ITEMVENDA IT, ITVENDAIMPOS IV, IMPOSTO I, FILIAL FI
                       WHERE V.DTENTRVENDA BETWEEN P_DTINICIAL AND P_DTFINAL
                         AND V.IDSITUVENDA = 'O'
                         AND V.IDVENDEBCONS <> 'S'
                         AND V.IDTPEMISVEND  NOT IN ('N', 'S')
                         AND FI.CDEMPRESA  = P_CDEMPRESA
                         AND FI.CDINSCESTA = P_CDINSCESTA
                         AND V.CDFILIAL = C_FILIAL.CDFILIAL
                         AND V.CDFILIAL = IT.CDFILIAL
                         AND V.CDCAIXA = IT.CDCAIXA
                         AND V.NRSEQVENDA = IT.NRSEQVENDA
                         AND IT.CDFILIAL  = IV.CDFILIAL
                         AND IT.NRSEQVENDA = IV.NRSEQVENDA
                         AND IT.CDCAIXA    = IV.CDCAIXA
                         AND IT.NRSEQUITVEND = IV.NRSEQUITVEND
                         AND IV.CDIMPOSTO   = I.CDIMPOSTO
                       GROUP BY NRINSCRCONS, DECODE(SUBSTR(V.CDCLIENTE,1,1), 'J', CDCLIENTE, ''),
                                V.CDCAIXA, V.NRCUPOMFIS, V.DTENTRVENDA, V.CDFILIAL, V.NRSEQVENDA,
                                TO_CHAR(V.DTENTRVENDA,'DDMMYYYY') ) LOOP

         SELECT NVL(SUM(QTPRODVENDC*VRUNITVENDC),0)
           INTO V_VRCANCELA
           FROM ITVENDACAN I
          WHERE I.CDFILIAL = C_VENDA.CDFILIAL
            AND I.NRSEQVENDA = C_VENDA.NRSEQVENDA
            AND I.CDCAIXA = C_VENDA.CDCAIXA;

         BEGIN
            SELECT MAX(R.NRECFREDZ)
              INTO V_NRECFREDZ
              FROM REDUCAOZ R
             WHERE R.CDFILIAL = C_VENDA.CDFILIAL
               AND R.CDCAIXA = C_VENDA.CDCAIXA
               AND R.DTREDUCAOZ = C_VENDA.DTENTRVENDA;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
            V_NRECFREDZ := NULL;
         END;

         IF V_NRECFREDZ IS NOT NULL THEN
            SELECT MAX(R.NRECFREDZ  || '|' ||
                   R.NRREINIREDZ    || '|' ||
                   R.NRREINIREDZ    || '|') AS LINHA2
              INTO V_LINHA2
              FROM REDUCAOZ R, IMPOSTOREDZ I
             WHERE R.CDFILIAL = C_VENDA.CDFILIAL
               AND R.CDCAIXA = C_VENDA.CDCAIXA
               AND R.DTREDUCAOZ = C_VENDA.DTENTRVENDA
               AND R.CDFILIAL = I.CDFILIAL(+)
               AND R.CDCAIXA = I.CDCAIXA(+)
               AND R.NRECFREDZ = I.NRECFREDZ(+)
               AND R.DTREDUCAOZ = I.DTREDUCAOZ(+)
             GROUP BY R.NRECFREDZ, R.NRREINIREDZ, R.NRREINIREDZ;

            V_LINHA := C_VENDA.LINHA1 ||
                       C_VENDA.CAIXA  ||
                       V_LINHA2       ||
                       C_VENDA.CUPOM  ||
                       C_VENDA.LINHA3 ||
                       C_VENDA.LINHA4;
         END IF;

         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('20'||LPAD(RTRIM(V_CONTREGC), 8, '0'), V_LINHA, P_NRSEQARQMAG);

         V_CONTREGC :=  NVL(V_CONTREGC,0) + 1;
         V_SEQUC600 := LPAD(RTRIM(TO_CHAR(TO_NUMBER('1'))),3,'0');
         V_LINHA2 := NULL;
         V_LINHA4 := NULL;

         FOR C_ITVENDA IN (SELECT (P.CDARVPROD      || '|' ||
                                   REPLACE(LTRIM(TO_CHAR(ROUND(SUM(I.VRUNITVEND+NVL(I.VRUNITVENDCL,0)),3), '999999999990.000')), '.', ',') || '|' ||
                                   REPLACE(LTRIM(TO_CHAR(ROUND(SUM(I.QTPRODVEND),3), '999999999990.000')), '.', ',') || '|' ||
                                   '0'  || '|' ||
                                   P.SGUNIDADE|| '|' ||
                                   REPLACE(LTRIM(TO_CHAR(ROUND(SUM(I.VRUNITVEND+NVL(I.VRUNITVENDCL,0)),2), '999999999990.00')), '.', ',') || '|' ||
                                   REPLACE(LTRIM(TO_CHAR(ROUND(SUM(I.VRDESITVEND),2), '999999999990.00')), '.', ',') || '|' ||
                                   '0'     || '|' ||
                                   REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VRACRITVEND),2), '999999999990.00')), '.', ',') || '|' ||
                                   '0'     || '|' ||
                                   '000'   || '|' ||
                                   '5102'  || '|' ||
                                   '0'     || '|' ||
                                   REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VRPEALPRODIT),2), '999999999990.00')), '.', ',') || '|' ||
                                   REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VRIMPOPRODIT),2), '999999999990.00')), '.', ',') || '|' ||
                                   '0' || '|' ||
                                   '0' || '|' ||
                                   '0' || '|') DADOS,
                                   P.CDPRODUTO, P.CDARVPROD
                             FROM ITEMVENDA I, PRODUTO P, ITVENDAIMPOS T
                            WHERE I.NRSEQVENDA = C_VENDA.NRSEQVENDA
                              AND I.CDFILIAL = C_VENDA.CDFILIAL
                              AND I.CDCAIXA = C_VENDA.CDCAIXA
                              AND I.CDPRODUTO = P.CDPRODUTO
                              AND I.CDFILIAL = T.CDFILIAL
                              AND I.CDCAIXA = T.CDCAIXA
                              AND I.NRSEQVENDA = T.NRSEQVENDA
                              AND I.NRSEQUITVEND = T.NRSEQUITVEND
                            GROUP BY P.CDARVPROD, P.SGUNIDADE, I.NRSEQITPROD, P.CDPRODUTO
                            ORDER BY I.NRSEQITPROD) LOOP

            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('20'||LPAD(RTRIM(V_CONTREGC), 8, '0'), '|C605|'||V_SEQUC600||'|'||C_ITVENDA.DADOS, P_NRSEQARQMAG);
            V_CONTREGC := NVL(V_CONTREGC,0) + 1;
            V_SEQUC600 := LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_SEQUC600))+1)), 3,'0');

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

   IF V_CONTREGC <> 0 THEN
      V_MOVIMENT := 0;
   ELSE
      V_MOVIMENT := 1;
   END IF;

   /* ABERTURA DO REGISTRO C */
   V_CONTREGC := NVL(V_CONTREGC,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('18'||LPAD('0', 8, '0'), '|C001'|| '|' ||V_MOVIMENT|| '|', P_NRSEQARQMAG);

   /* ENCERRAMENTO DO BLOCO C */
   V_CONTREGC := NVL(V_CONTREGC,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('23'||LPAD(RTRIM(V_CONTREGC), 8, '0'), '|C990'|| '|' ||V_CONTREGC|| '|', P_NRSEQARQMAG);
   RETURN(V_CONTREGC);
END;
/