CREATE OR REPLACE FUNCTION SEF2_BLOCOE ( 
   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_IND_IPI      IN VARCHAR2,
   P_CDLIVROMAG   IN LIVROMAGNETICO.CDLIVROMAG%TYPE,
   P_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE) RETURN NUMBER
AS
   V_CONTREGE          NUMBER;
   V_MOVIMENT          NUMBER;
   V_NRSEQUMAPAPU      VARCHAR2(10);
   V_NRSEQMAPAIPI      VARCHAR2(10);
   TIPOOPER_COMPL_S    VARCHAR2(02);
   TIPOOPER_COMPL_E    VARCHAR2(02);
   V_SEQUPROD          NUMBER;
   V_SEQUOBS           NUMBER;
   V_COD_NAT           VARCHAR2(100);
   V_COD_PAR           VARCHAR2(100);
   V_E340_VL_01        NUMBER;
   V_E340_VL_02        NUMBER;
   V_E340_VL_03        NUMBER;
   V_E340_VL_04        NUMBER;
   V_E340_VL_05        NUMBER;
   V_E340_VL_06        NUMBER;
   V_E340_VL_07        NUMBER;
   V_E340_VL_08        NUMBER;
   V_E340_VL_09        NUMBER;
   V_E340_VL_10        NUMBER;
   V_E340_VL_11        NUMBER;
   V_E340_VL_12        NUMBER;
   V_E340_VL_13        NUMBER;
   V_E340_VL_14        NUMBER;
   V_E340_VL_15        NUMBER;
   V_E340_VL_16        NUMBER;
   V_E340_VL_17        NUMBER;
   V_E340_VL_18        NUMBER;
   V_E340_VL_19        NUMBER;
   V_E340_VL_20        NUMBER;
   V_E340_VL_21        NUMBER;
   V_E340_VL_22        NUMBER;
   V_E340_VL_99        NUMBER;
   V_REG_E340          VARCHAR2(4000);
   V_REG_E350          VARCHAR2(4000);
   V_REG_E360          VARCHAR2(4000);
   V_E540_VL_01        NUMBER;
   V_E540_VL_02        NUMBER;
   V_E540_VL_03        NUMBER;
   V_E540_VL_04        NUMBER;
   V_E540_VL_05        NUMBER;
   V_E540_VL_06        NUMBER;
   V_E540_VL_07        NUMBER;
   V_E540_VL_09        NUMBER;
   V_E540_VL_10        NUMBER;
   V_E540_VL_11        NUMBER;
   V_E540_VL_12        NUMBER;
   V_E540_VL_13        NUMBER;
   V_E540_VL_08        NUMBER;
   V_E540_VL_16        NUMBER;
   V_E540_VL_17        NUMBER;
   V_REG_E540          VARCHAR2(4000);
   V_NRINSJUR	       VARCHAR2(20);
   V_CDINSCESTA        VARCHAR2(16);
   V_CDINSCMUNI        VARCHAR2(16);
BEGIN
   V_CONTREGE := 0;

   SELECT MAX(CDCONTREGI)
     INTO V_SEQUOBS
     FROM TMP_REGDADOSSEF2
    WHERE CDREGISTRO = '0450';

   /*busca o tipo de operacao de complemento */
   SELECT MAX(TIPOOPER_S), MAX(TIPOOPER_E)
     INTO TIPOOPER_COMPL_S, TIPOOPER_COMPL_E
    FROM
   (
     SELECT DECODE(IDENTRSAIDOP,'S',CDTIPOOPER,NULL) AS TIPOOPER_S,
            DECODE(IDENTRSAIDOP,'E',CDTIPOOPER,NULL) AS TIPOOPER_E
      FROM TIPOOPERAC
     WHERE UPPER(NMTIPOOPER) LIKE '%NF COMPLEMENTO%'
      AND CDTIPOOPER <> '03');

   /*MAPA ICMS*/
   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;

   /*MAPA IPI*/
   BEGIN
      SELECT NRSEQUMAPAPU
        INTO V_NRSEQMAPAIPI
        FROM MAPAAPURAC M
       WHERE M.CDEMPRESA = P_CDEMPRESA
         AND M.CDINSCESTA = P_CDINSCESTA
         AND M.CDIMPOSTO = '10'
         AND M.DTINIPERIODO = P_DTINICIAL
         AND M.DTFIMPERIODO = P_DTFINAL;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_NRSEQMAPAIPI := NULL;
   END;

   /*REGISTRO E003*/
   V_CONTREGE := NVL(V_CONTREGE,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('501'||LPAD(RTRIM(V_CONTREGE), 7, '0'), '|E003|PE|E025|16|2|', P_NRSEQARQMAG);
   V_CONTREGE := NVL(V_CONTREGE,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('501'||LPAD(RTRIM(V_CONTREGE), 7, '0'), '|E003|PE|E055|09|1|', P_NRSEQARQMAG);
   V_CONTREGE := NVL(V_CONTREGE,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('501'||LPAD(RTRIM(V_CONTREGE), 7, '0'), '|E003|PE|E065|10|1|', P_NRSEQARQMAG);
   V_CONTREGE := NVL(V_CONTREGE,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('501'||LPAD(RTRIM(V_CONTREGE), 7, '0'), '|E003|PE|E085|06|1|', P_NRSEQARQMAG);
   V_CONTREGE := NVL(V_CONTREGE,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('501'||LPAD(RTRIM(V_CONTREGE), 7, '0'), '|E003|PE|E105|11|1|', P_NRSEQARQMAG);
   V_CONTREGE := NVL(V_CONTREGE,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('501'||LPAD(RTRIM(V_CONTREGE), 7, '0'), '|E003|PE|E310|10|1|', P_NRSEQARQMAG);
   V_CONTREGE := NVL(V_CONTREGE,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('501'||LPAD(RTRIM(V_CONTREGE), 7, '0'), '|E003|PE|E350|10|1|', P_NRSEQARQMAG);

   SELECT REPLACE(REPLACE(REPLACE(REPLACE(I.NRINSJURIEST,'.',''),'-',''),'/',''),' ','') AS NRINSJUR,
          REPLACE(REPLACE(REPLACE(REPLACE(I.CDINSCESTA,'.',''),'-',''),'/',''),' ','') AS CDINSCESTA
	 INTO V_NRINSJUR, V_CDINSCESTA
     FROM  INSCRESTAD I
    WHERE I.CDEMPRESA   = P_CDEMPRESA      
	  AND I.CDINSCESTA  = P_CDINSCESTA;

   /*REGISTRO E020*/
   FOR C_REGE020 IN (SELECT '|E020'||'|'||
                            IND_OPER||'|'||
                            IND_EMIT ||'|'||
                            COD_PART||'|'||
                            COD_MOD||'|'||
                            COD_SIT||'|'||
                            DECODE(SER,'','1','-','1','0','1',SER)||'|'||  --DECODE(NVL(SER,'-'),'-',DECODE(COD_MOD,'55','0','1'),SER)||'|'||
                            LTRIM(NUM_DOC,'0')||'|'||
                            CHV_NFE||'|'||
                            DECODE(IND_OPER,'0',TO_CHAR(DT_EMIS,'ddmmyyyy'),'')||'|'||
                            TO_CHAR(DT_DOC,'ddmmyyyy')||'|'||
                            COD_NAT||'|'||
                            COP||'|'||
                            NUM_LCTO||'|'||
                            ''||'|'||--IND_PGTO
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_CONT,2), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_OP_ISS,2), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_BC_ICMS,2), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_ICMS,2), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(DECODE(VL_ICMS_ST,0,'',ROUND(VL_ICMS_ST,2)), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(DECODE(COD_MOD,'65','',ROUND(VL_ST_E,2)), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(DECODE(COD_MOD,'65','',ROUND(VL_ST_S,2)), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(DECODE(IND_OPER,'0',DECODE(VL_AT,0,'',ROUND(VL_AT,2)),''), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_ISNT_ICMS,2), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_OUT_ICMS,2), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(DECODE(P_IND_IPI,'0',ROUND(VL_BC_IPI,2),''), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(DECODE(P_IND_IPI,'0',ROUND(VL_IPI,2),''), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(DECODE(P_IND_IPI,'0',ROUND(VL_ISNT_IPI,2),''), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(DECODE(P_IND_IPI,'0',ROUND(VL_OUT_IPI,2),''), '999999999990.00')), '.', ',')||'|'||
                            COD_INF_OBS||'|' AS DADOS, CDFILIAL, NRLANCTONF, COD_PART, NVL(IDORIGDESTNF,DECODE(IND_OPER,'0','F','C')) AS IDORIGDESTNF,
                            (CASE WHEN (IND_OPER='0') AND (IND_EMIT='1') THEN 'S' ELSE 'N' END) AS OBRG_IE, CDCAIXA, COD_MOD, COD_SIT
                       FROM (/*Nf's de Entrada*/
                            SELECT '0' AS IND_OPER,
                                   MAX((SELECT DECODE(NF.IDENTRSAIDOP, 'E', DECODE(NF.IDSTATUSNFE,'A','0',DECODE(NF.IDSTATUS,'E','0',
								                         CASE WHEN REPLACE(REPLACE(REPLACE(REPLACE(F.NRINSJURFILI,'.',''),'-',''),'/',''),' ','') = V_NRINSJUR
                                                       AND REPLACE(REPLACE(REPLACE(REPLACE(F.CDINSCESTA,'.',''),'-',''),'/',''),' ','')   = V_CDINSCESTA
                                                      THEN '0' ELSE '1' END)), '0')
                                          FROM FILIAL F
                                         WHERE F.CDFILIAL = NF.CDFILIEMIT
                                           AND NF.IDORIGDESTNF = 'L'
                                         UNION
                                        SELECT DECODE(NF.IDENTRSAIDOP, 'E', DECODE(NF.IDSTATUSNFE,'A','0',DECODE(NF.IDSTATUS,'E','0','1')),'0')
                                          FROM DUAL
                                         WHERE NF.IDORIGDESTNF <> 'L')) AS IND_EMIT,
                                   DECODE(NF.IDSTATUS,'C','',DECODE(NF.IDORIGDESTNF,'F',NF.CDFORNECED,DECODE(NF.IDORIGDESTNF,'C',NF.CDCLIENTE,NF.CDFILIEMIT))) AS COD_PART,
                                   NF.CDMODDOCFISC AS COD_MOD,
                                   CASE WHEN ((NF.CDSERIEENTR BETWEEN '890' AND '899') AND (NF.CDMODDOCFISC = '55')) THEN '10' ELSE DECODE(NF.IDSTATUS,'C','90',DECODE(CDTIPOOPER,'03','20',TIPOOPER_COMPL_E,'20','00')) END AS COD_SIT,
                                   NF.CDSERIEENTR AS SER, NF.NRNOTAFISC AS NUM_DOC, DECODE(NF.CDMODDOCFISC,'55',NF.NRACESSONFE,'') AS CHV_NFE,
                                   NF.DTEMISSAO AS DT_EMIS, NF.DTENTRSAID AS DT_DOC, '' AS COD_NAT, MIN(CF.CDLAYOUTLMAG) AS COP, NF.NRLANCTBDOC AS NUM_LCTO,
                                   DECODE(NF.IDSTATUS,'C','9',DECODE(PG.NRSEQVENPAG,1,'0','1')) AS IND_PGTO,
                                   SUM(IP.VRCONTABILNF) AS VL_CONT, 0 AS VL_OP_ISS,
                                   SUM(DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0)) AS VL_BC_ICMS,
                                   SUM(DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0)) AS VL_ICMS,
                                    VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'80','I','1') AS VL_ICMS_ST,
                                   0 AS VL_ST_E, 0 AS VL_ST_S, 0 AS VL_AT,
                                   SUM(DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0)) AS VL_ISNT_ICMS,
                                   SUM(DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0)) AS VL_OUT_ICMS,
                                    VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'10','B','1') AS VL_BC_IPI,
                                    VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'10','I','1') AS VL_IPI,
                                   VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'10','C','2') AS VL_ISNT_IPI,
                                   VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'10','C','3') AS VL_OUT_IPI,
                                   '' AS COD_INF_OBS, NF.CDFILIAL, NF.NRLANCTONF, NF.IDORIGDESTNF, '' AS CDCAIXA
                              FROM IMPOSTONF IP, NOTAFISCAL NF, CFOPLIVROMAG CF, IMPOSTO IM,
                                   (SELECT COUNT(C.NRSEQVENPAG) NRSEQVENPAG, C.CDFILIAL, C.NRLANCTONF
                                      FROM CONTAPAGAR C, NOTAFISCAL N
                                     WHERE N.CDFILIAL   = C.CDFILIAL
                                       AND N.NRLANCTONF = C.NRLANCTONF
                                     GROUP BY C.CDFILIAL, C.NRLANCTONF) PG
                             WHERE IP.NRSEQUMAPAPU = V_NRSEQUMAPAPU
                               AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                               AND IP.CDIMPOSTO    = IM.CDIMPOSTO
                               AND IP.CDFILIAL     = NF.CDFILIAL
                               AND IP.NRLANCTONF   = NF.NRLANCTONF
                               AND NF.IDENTRSAIDOP = 'E'
                               AND NF.CDMODDOCFISC IN ('01','04','55')
                               AND NF.CDFILIAL     = PG.CDFILIAL(+)
                               AND NF.NRLANCTONF   = PG.NRLANCTONF(+)
                               AND IP.CDFISCOPERNF = CF.CDCFOPLMAG(+)
                               AND CF.CDLIVROMAG(+) = P_CDLIVROMAG
                               AND ((IP.IDIMPOSTONF <> '3') AND (IP.IDIMPOSTONF IS NOT NULL))
                             GROUP BY --DECODE(NF.IDENTRSAIDOP, 'E', DECODE(NF.IDSTATUSNFE,'A','0',DECODE(NF.IDSTATUS,'E','0','1')), '0'),
                                   DECODE(NF.IDSTATUS,'C','',DECODE(NF.IDORIGDESTNF,'F',NF.CDFORNECED,DECODE(NF.IDORIGDESTNF,'C',NF.CDCLIENTE,NF.CDFILIEMIT))),
                                   NF.CDMODDOCFISC, NF.CDSERIEENTR,
                                   CASE WHEN ((NF.CDSERIEENTR BETWEEN '890' AND '899') AND (NF.CDMODDOCFISC = '55')) THEN '10' ELSE DECODE(NF.IDSTATUS,'C','90',DECODE(CDTIPOOPER,'03','20',TIPOOPER_COMPL_E,'20','00')) END,
                                   NF.NRNOTAFISC, DECODE(NF.CDMODDOCFISC,'55',NF.NRACESSONFE,''), NF.DTEMISSAO, NF.DTENTRSAID,  NF.NRLANCTBDOC,
                                   DECODE(NF.IDSTATUS,'C','9',DECODE(PG.NRSEQVENPAG,1,'0','1')), VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'80','I','1'),
                                   VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'10','B','1'), VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'10','I','1'),
                                   VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'10','C','2'), VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'10','C','3'),
                                   NF.CDFILIAL, NF.NRLANCTONF, NF.IDORIGDESTNF
                             UNION ALL /*Nf's de Saida*/
                            SELECT '1' AS IND_OPER, '0' AS IND_EMIT,
                                   DECODE(NF.IDSTATUS,'C','',DECODE(NF.IDORIGDESTNF,'F',NF.CDFORNECED,DECODE(NF.IDORIGDESTNF,'C',NF.CDCLIENTE,NF.CDFILIRECE))) AS COD_PART,
                                   NF.CDMODDOCFISC AS COD_MOD, DECODE(NF.IDSTATUS,'C','10',DECODE(NF.CDTIPOOPER,'03','02',TIPOOPER_COMPL_S,'02','00')) AS COD_SIT,
                                   NF.CDSERIE AS SER, NF.NRNOTAFISC AS NUM_DOC, DECODE(NF.CDMODDOCFISC,'55',NF.NRACESSONFE,'') AS CHV_NFE,
                                   NF.DTEMISSAO AS DT_EMIS, NF.DTEMISSAO AS DT_DOC, '' AS COD_NAT, MAX(CF.CDLAYOUTLMAG) AS COP, NF.NRLANCTBDOC AS NUM_LCTO,
                                   DECODE(NF.IDSTATUS,'C','9',DECODE(PG.NRSEQVENREC,1,'0','1')) AS IND_PGTO, SUM(IP.VRCONTABILNF) AS VL_CONT, 0 AS VL_OP_ISS,
                                   SUM(DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0)) AS VL_BC_ICMS,
                                   SUM(DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0)) AS VL_ICMS,
                                   VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'80','I','1') AS VL_ICMS_ST,
                                   0 AS VL_ST_E, 0 AS VL_ST_S, 0 AS VL_AT,
                                   SUM(DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0)) AS VL_ISNT_ICMS,
                                   SUM(DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0)) AS VL_OUT_ICMS,
                                   VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'10','B','1') AS VL_BC_IPI,
                                   VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'10','I','1') AS VL_IPI,
                                   VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'10','C','2') AS VL_ISNT_IPI,
                                   VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'10','C','3') AS VL_OUT_IPI,
                                   '' AS COD_INF_OBS, NF.CDFILIAL, NF.NRLANCTONF, NF.IDORIGDESTNF, '' AS CDCAIXA
                              FROM IMPOSTONF IP, NOTAFISCAL NF, CFOPLIVROMAG CF, IMPOSTO IM,
                                   (SELECT COUNT(C.NRSEQVENREC) NRSEQVENREC, N.CDFILIAL, N.NRLANCTONF
                                      FROM CONTARECEBER C, NOTAFISCAL N
                                     WHERE N.CDFILIAL   = C.CDFILIAL
                                       AND N.NRLANCTONF = C.NRLANCTONF
                                     GROUP BY N.CDFILIAL, N.NRLANCTONF) PG
                             WHERE IP.NRSEQUMAPAPU = V_NRSEQUMAPAPU
                               AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                               AND IP.CDIMPOSTO    = IM.CDIMPOSTO
                               AND IP.CDFILIAL     = NF.CDFILIAL
                               AND IP.NRLANCTONF   = NF.NRLANCTONF
                               AND NF.IDENTRSAIDOP = 'S'
                               AND NF.CDMODDOCFISC IN ('01','04','55')
                               AND NF.CDFILIAL     = PG.CDFILIAL(+)
                               AND NF.NRLANCTONF   = PG.NRLANCTONF(+)
                               AND IP.CDFISCOPERNF = CF.CDCFOPLMAG(+)
                               AND CF.CDLIVROMAG(+) = P_CDLIVROMAG
                               AND NF.IDSISTEMA     <> 'FIS'
                               AND ((IP.IDIMPOSTONF <> '3') AND (IP.IDIMPOSTONF IS NOT NULL))
                             GROUP BY DECODE(NF.IDSTATUS,'C','',DECODE(NF.IDORIGDESTNF,'F',NF.CDFORNECED,DECODE(NF.IDORIGDESTNF,'C',NF.CDCLIENTE,NF.CDFILIRECE))),
                                   NF.CDMODDOCFISC,DECODE(NF.IDSTATUS,'C','10',DECODE(NF.CDTIPOOPER,'03','02',TIPOOPER_COMPL_S,'02','00')),NF.CDSERIE,
                                   NF.NRNOTAFISC,DECODE(NF.CDMODDOCFISC,'55',NF.NRACESSONFE,''),NF.DTEMISSAO,NF.DTEMISSAO,NF.NRLANCTBDOC,
                                   DECODE(NF.IDSTATUS,'C','9',DECODE(PG.NRSEQVENREC,1,'0','1')),VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'80','I','1'),
                                   VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'10','B','1'),VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'10','I','1'),
                                   VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'10','C','2'),VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'10','C','3'),
                                   NF.CDFILIAL, NF.NRLANCTONF, NF.IDORIGDESTNF
                             UNION ALL  /*Nf's Denegadas*/
                            SELECT '1' AS IND_OPER, '0' AS IND_EMIT,
                                   DECODE(NF.IDORIGDESTNF,'F',NF.CDFORNECED,DECODE(NF.IDORIGDESTNF,'C',NF.CDCLIENTE,NF.CDFILIRECE)) AS COD_PART,
                                   NF.CDMODDOCFISC AS COD_MOD, '80' AS COD_SIT,
                                   NF.CDSERIE AS SER, NF.NRNOTAFISC AS NUM_DOC, DECODE(NF.CDMODDOCFISC,'55',NF.NRACESSONFE,'') AS CHV_NFE,
                                   NF.DTEMISSAO AS DT_EMIS, NF.DTEMISSAO AS DT_DOC, '' AS COD_NAT, MAX(CF.CDLAYOUTLMAG) AS COP, NF.NRLANCTBDOC AS NUM_LCTO,
                                   '9' AS IND_PGTO, 0 AS VL_CONT, 0 AS VL_OP_ISS, 0 AS VL_BC_ICMS, 0 AS VL_ICMS, 0 AS VL_ICMS_ST,
                                   0 AS VL_ST_E, 0 AS VL_ST_S, 0 AS VL_AT, 0 AS VL_ISNT_ICMS, 0 AS VL_OUT_ICMS, 0 AS VL_BC_IPI, 0 AS VL_IPI,
                                   0 AS VL_ISNT_IPI, 0 AS VL_OUT_IPI, '' AS COD_INF_OBS, NF.CDFILIAL, NF.NRLANCTONF, NF.IDORIGDESTNF, '' AS CDCAIXA
                              FROM NOTAFISCAL NF, IMPOSTONF IP, IMPOSTO IM, CFOPLIVROMAG CF
                             WHERE NF.CDEMPRESA     =  P_CDEMPRESA
                               AND NF.CDINSCESTA    = P_CDINSCESTA
                               AND NF.DTEMISSAO     BETWEEN P_DTINICIAL AND P_DTFINAL
                               AND NF.IDENTRSAIDOP  = 'S'
                               AND NF.CDMODDOCFISC  IN ('01','04','55')
                               AND NF.IDSTATUSNFE   = 'D'
                               AND NF.CDFILIAL      = IP.CDFILIAL
                               AND NF.NRLANCTONF    = IP.NRLANCTONF
                               AND IP.CDIMPOSTO     = IM.CDIMPOSTO
                               AND IM.CDIMPOSTONFE  = P_CDIMPOSTO
                               AND IP.CDFISCOPERNF  = CF.CDCFOPLMAG(+)
                               AND CF.CDLIVROMAG(+) = P_CDLIVROMAG
                             GROUP BY DECODE(NF.IDORIGDESTNF,'F',NF.CDFORNECED,DECODE(NF.IDORIGDESTNF,'C',NF.CDCLIENTE,NF.CDFILIRECE)),
                                   NF.CDMODDOCFISC, NF.CDSERIE, NF.NRNOTAFISC, DECODE(NF.CDMODDOCFISC,'55',NF.NRACESSONFE,''),
                                   NF.DTEMISSAO, NF.NRLANCTBDOC, NF.CDFILIAL, NF.NRLANCTONF, NF.IDORIGDESTNF
                             UNION ALL /*Nf's Canceladas*/
                            SELECT '1' AS IND_OPER, '0' AS IND_EMIT,
                                   DECODE(NF.IDORIGDESTNF,'F',NF.CDFORNECED,DECODE(NF.IDORIGDESTNF,'C',NF.CDCLIENTE,NF.CDFILIRECE)) AS COD_PART,
                                   NF.CDMODDOCFISC AS COD_MOD, '90' AS COD_SIT,
                                   NF.CDSERIE AS SER, NF.NRNOTAFISC AS NUM_DOC, DECODE(NF.CDMODDOCFISC,'55',NF.NRACESSONFE,'') AS CHV_NFE,
                                   NF.DTEMISSAO AS DT_EMIS, NF.DTEMISSAO AS DT_DOC, '' AS COD_NAT, 'SP10' AS COP, NF.NRLANCTBDOC AS NUM_LCTO,
                                   '9' AS IND_PGTO, 0 AS VL_CONT, 0 AS VL_OP_ISS, 0 AS VL_BC_ICMS, 0 AS VL_ICMS, 0 AS VL_ICMS_ST,
                                   0 AS VL_ST_E, 0 AS VL_ST_S, 0 AS VL_AT, 0 AS VL_ISNT_ICMS, 0 AS VL_OUT_ICMS, 0 AS VL_BC_IPI, 0 AS VL_IPI,
                                   0 AS VL_ISNT_IPI, 0 AS VL_OUT_IPI, '' AS COD_INF_OBS, NF.CDFILIAL, NF.NRLANCTONF, NF.IDORIGDESTNF, '' AS CDCAIXA
                              FROM NOTAFISCAL NF
                             WHERE NF.CDEMPRESA  =  P_CDEMPRESA
                               AND NF.CDINSCESTA = P_CDINSCESTA
                               AND NF.DTEMISSAO  BETWEEN P_DTINICIAL AND P_DTFINAL
                               AND NF.IDENTRSAIDOP = 'S'
                               AND NF.CDMODDOCFISC IN ('01','04','55')
                               AND NF.IDSTATUS = 'C'
                             UNION ALL /*Nf Inutilizada*/
                            SELECT '1' AS IND_OPER, '0' AS IND_EMIT,
                                   '' AS COD_PART,
                                   '55' AS COD_MOD, '81' AS COD_SIT,
                                   NF.CDSERIE AS SER, NF.NRINUTILNFELE AS NUM_DOC, '' AS CHV_NFE,
                                   TRUNC(NF.DTINUTNFE) AS DT_EMIS, TRUNC(NF.DTINUTNFE) AS DT_DOC,
                                   '' AS COD_NAT, 'SP10' AS COP, '' AS NUM_LCTO,
                                   '9' AS IND_PGTO, 0 AS VL_CONT, 0 AS VL_OP_ISS, 0 AS VL_BC_ICMS, 0 AS VL_ICMS, 0 AS VL_ICMS_ST,
                                   0 AS VL_ST_E, 0 AS VL_ST_S, 0 AS VL_AT, 0 AS VL_ISNT_ICMS, 0 AS VL_OUT_ICMS, 0 AS VL_BC_IPI, 0 AS VL_IPI,
                                   0 AS VL_ISNT_IPI, 0 AS VL_OUT_IPI, '' AS COD_INF_OBS, '' AS CDFILIAL, '' AS NRLANCTONF, '' AS IDORIGDESTNF, '' AS CDCAIXA
                              FROM NUMINUTILNFELE NF
                             WHERE NF.CDEMPRESA  =  P_CDEMPRESA
                               AND NF.CDINSCESTA = P_CDINSCESTA
                               AND TRUNC(NF.DTINUTNFE)  BETWEEN P_DTINICIAL AND P_DTFINAL
                               AND NF.NRPROTOCINUTNFE <> 'TEKNISA'
                             UNION ALL /*NFC-e*/
                            SELECT '1' AS IND_OPER, '0' AS IND_EMIT,
                                   '' AS COD_PART,
                                   NF.CDMODDOCFISC AS COD_MOD, '00' AS COD_SIT,
                                   NF.CDSERIE AS SER, NF.NRNOTAFISC AS NUM_DOC, NRACESSONFCE AS CHV_NFE,
                                   NF.DTEMISSAO AS DT_EMIS, NF.DTEMISSAO AS DT_DOC, '' AS COD_NAT, COP AS COP, '' AS NUM_LCTO,
                                   QTDEPARC AS IND_PGTO, NF.VRCONTABILNF AS VL_CONT, 0 AS VL_OP_ISS,
                                   NVL(NF.VRBASECALC,0) AS VL_BC_ICMS,
                                   NVL(NF.VRIMPOSTONF,0) AS VL_ICMS,
                                   0 AS VL_ICMS_ST,
                                   0 AS VL_ST_E, 0 AS VL_ST_S, 0 AS VL_AT,
                                    NVL(VL_ISNT_ICMS,0) AS VL_ISNT_ICMS,
                                    NVL(VL_OUT_ICMS,0) AS VL_OUT_ICMS,
                                   0 AS VL_BC_IPI,
                                   0 AS VL_IPI,
                                   0 AS VL_ISNT_IPI,
                                   0 AS VL_OUT_IPI,
                                   '' AS COD_INF_OBS, NF.CDFILIAL, NF.NRLANCTONF, NF.IDORIGDESTNF, NF.CDCAIXA
                              FROM (SELECT '1' AS CAMPO2, '0' AS CAMPO3, '65' AS CDMODDOCFISC, VE.IDSTATUSNFCE, VE.CDSERIENFCE AS CDSERIE, VE.NRNOTAFISCALCE AS NRNOTAFISC,
                                           SUM(TRUNC(IT.QTPRODVEND*(IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0)),2)-NVL(IT.VRDESITVEND,0)+NVL(IT.VRACRITVEND,0)) AS VRCONTABILNF,
                                           SUM(VRDESITVEND) AS VRDESITVEND, VE.NRACESSONFCE, TRUNC(VE.DTEMISSAONFCE) AS DTEMISSAO, IP.VRIMPOSTONF, IP.VRBASECALC,
                                           VE.CDFILIAL AS CDFILIAL, VE.NRSEQVENDA AS NRLANCTONF, VE.CDCAIXA, 'S' AS IDENTRSAIDOP,
                                           '' AS IDSTATUS, '' AS IDORIGDESTNF, '1' AS QTDEPARC,
                                           SUM(TRUNC(IT.QTPRODVEND*(IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0)),2)-NVL(IT.VRDESITVEND,0)+NVL(IT.VRACRITVEND,0)),
                                           MAX(COP) AS COP, IP.VL_ISNT_ICMS, IP.VL_OUT_ICMS
                                      FROM VENDA VE, ITEMVENDA IT, FILIAL FI,
                                           (SELECT I.CDFILIAL, I.CDCAIXA, I.NRSEQVENDA, MAX(CF.CDLAYOUTLMAG) AS COP,
                                                   SUM(DECODE(M.IDTPIMPOSFIS,'T',CASE WHEN I.VRPRBCREDUICMS > 0
                                                                                      THEN I.VRBASECALCREDUZ
                                                                                      ELSE TRUNC(T.QTPRODVEND*(T.VRUNITVEND+NVL(T.VRUNITVENDCL,0)),2)-NVL(T.VRDESITVEND,0)+NVL(T.VRACRITVEND,0) END,0)) AS VRBASECALC,
                                                   SUM(CASE WHEN I.VRPRBCREDUICMS > 0
                                                            THEN I.VRIMPOPRODREDUZ
                                                            ELSE TRUNC(I.VRIMPOPRODIT,2) END) AS VRIMPOSTONF,
                                                   SUM(CASE WHEN M.IDTPIMPOSFIS = 'I'
                                                            THEN TRUNC((T.QTPRODVEND * (T.VRUNITVEND+NVL(T.VRUNITVENDCL,0))) - NVL(T.VRDESITVEND,0) + NVL(T.VRACRITVEND,0),2)
                                                            ELSE 0 END) AS VL_ISNT_ICMS,
                                                   SUM(CASE WHEN M.IDTPIMPOSFIS IN ('N','F')
                                                            THEN TRUNC((T.QTPRODVEND * (T.VRUNITVEND+NVL(T.VRUNITVENDCL,0))) - NVL(T.VRDESITVEND,0) + NVL(T.VRACRITVEND,0),2)
                                                            ELSE 0 END) AS VL_OUT_ICMS
                                              FROM ITVENDAIMPOS I,  ITEMVENDA T,  VENDA V,  IMPOSTO M, FILIAL F, CFOPLIVROMAG CF
                                             WHERE F.CDEMPRESA       = P_CDEMPRESA
                                               AND F.CDINSCESTA      = P_CDINSCESTA
                                               AND F.CDEMPRESA       = V.CDEMPRESA
                                               AND F.CDFILIAL        = V.CDFILIAL
                                               AND TRUNC(V.DTEMISSAONFCE) BETWEEN P_DTINICIAL AND P_DTFINAL
                                               AND V.IDSITUVENDA     = 'O'
                                               AND V.IDTPAMBNFCE     = '1'
                                               AND V.IDSTATUSNFCE    = 'A'
                                               AND V.CDFILIAL        = T.CDFILIAL
                                               AND V.NRSEQVENDA      = T.NRSEQVENDA
                                               AND V.CDCAIXA         = T.CDCAIXA
                                               AND T.CDFILIAL        = I.CDFILIAL
                                               AND T.CDCAIXA         = I.CDCAIXA
                                               AND T.NRSEQUITVEND    = I.NRSEQUITVEND
                                               AND T.NRSEQVENDA      = I.NRSEQVENDA
                                               AND I.CDIMPOSTO       = M.CDIMPOSTO
                                               AND M.CDIMPOSTONFE    IN (P_CDIMPOSTO,'80')
                                               AND I.CDCFOPPROD   = CF.CDCFOPLMAG(+)
                                               AND CF.CDLIVROMAG(+)  = P_CDLIVROMAG
                                               AND M.IDTPIMPOSFIS    IN ('T','F')
                                               AND V.IDAPROVACAONFCE = 'A'
                                               AND V.IDTPEMISVEND    = 'N'
                                             GROUP BY I.CDFILIAL, I.CDCAIXA, I.NRSEQVENDA) IP
                                     WHERE VE.CDEMPRESA       = P_CDEMPRESA
                                       AND TRUNC(VE.DTEMISSAONFCE) BETWEEN P_DTINICIAL AND P_DTFINAL
                                       AND VE.IDSITUVENDA     = 'O'
                                       AND VE.IDTPAMBNFCE     = '1'
                                       AND VE.IDSTATUSNFCE    = 'A'
                                       AND VE.IDAPROVACAONFCE = 'A'
                                       AND VE.CDFILIAL        = IT.CDFILIAL
                                       AND VE.NRSEQVENDA      = IT.NRSEQVENDA
                                       AND VE.CDCAIXA         = IT.CDCAIXA
                                       AND IT.CDFILIAL        = IP.CDFILIAL(+)
                                       AND IT.CDCAIXA         = IP.CDCAIXA(+)
                                       AND IT.NRSEQVENDA      = IP.NRSEQVENDA(+)
                                       AND VE.IDTPEMISVEND    = 'N'
                                       AND VE.CDFILIAL        = FI.CDFILIAL
                                       AND FI.CDEMPRESA       = P_CDEMPRESA
                                       AND FI.CDINSCESTA      = P_CDINSCESTA
                                     GROUP BY VE.IDSTATUSNFCE, VE.CDSERIENFCE, VE.NRNOTAFISCALCE, VE.NRACESSONFCE,
                                           TRUNC(VE.DTEMISSAONFCE), VE.CDFILIAL, VE.NRSEQVENDA, VE.CDCAIXA,
                                           IP.VRIMPOSTONF, IP.VRBASECALC, COP, IP.VL_ISNT_ICMS, IP.VL_OUT_ICMS)NF))LOOP

      V_CONTREGE := NVL(V_CONTREGE,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('502'||LPAD(RTRIM(V_CONTREGE), 7, '0'), C_REGE020.DADOS, P_NRSEQARQMAG);

      /* CODIDO DO PARTICIPANTE - LINHA 0150*/
      SELECT MAX(CDCHAVEREG)
        INTO V_COD_PAR
        FROM TMP_REGDADOSSEF2
       WHERE CDCHAVEREG = C_REGE020.COD_PART
         AND CDREGISTRO = '0150';
      IF (V_COD_PAR IS NULL) AND (C_REGE020.COD_PART IS NOT NULL) THEN
         V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
         INSERT INTO TMP_REGDADOSSEF2
           (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1, CDCAMPO2)
         VALUES
           ('0150', V_SEQUPROD, C_REGE020.COD_PART, C_REGE020.IDORIGDESTNF, C_REGE020.OBRG_IE);
      END IF;
      V_COD_PAR := null;

      /*REGISTRO E025*/
      FOR C_REGE025 IN (SELECT '|E025'||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(VL_CONT_P,2), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(VL_OP_ISS_P,2), '999999999990.00')), '.', ',')||'|'||
                               CFOP||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(VL_BC_ICMS_P,2), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(DECODE(ALIQ_ICMS,0,'',ROUND(ALIQ_ICMS,2)), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(VL_ICMS_P,2), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(DECODE(C_REGE020.COD_MOD,'65','',ROUND(VL_BC_ST_P,2)), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(DECODE(C_REGE020.COD_MOD,'65','',ROUND(VL_ICMS_ST_P,2)), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(VL_ISNT_ICMS_P,2), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(VL_OUT_ICMS_P,2), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(DECODE(P_IND_IPI,'0',ROUND(VL_BC_IPI_P,2),''), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(DECODE(P_IND_IPI,'0',ROUND(VL_IPI_P,2),''), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(DECODE(P_IND_IPI,'0',ROUND(VL_ISNT_IPI_P,2),''), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(DECODE(P_IND_IPI,'0',ROUND(VL_OUT_IPI_P,2),''), '999999999990.00')), '.', ',')||'|'||
                               IND_PETR||'|'||
                               IND_IMUN||'|' AS DADOS, CFOP
                          FROM (SELECT SUM(DECODE(IM.CDIMPOSTONFE,'00',IP.VRCONTABILNF,0)) AS VL_CONT_P,
                                       0 AS VL_OP_ISS_P,
                                       IP.CDFISCOPERNF AS CFOP,
                                       SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0)) AS VL_BC_ICMS_P,
                                       MAX(DECODE(IM.CDIMPOSTONFE,'00',IP.VRALIQNF,0)) AS ALIQ_ICMS,
                                       SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_ICMS_P,
                                       SUM(DECODE(IM.CDIMPOSTONFE,'80',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0)) AS VL_BC_ST_P,
                                       SUM(DECODE(IM.CDIMPOSTONFE,'80',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_ICMS_ST_P,
                                       SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0),0)) AS VL_ISNT_ICMS_P,
                                       SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0),0)) AS VL_OUT_ICMS_P,
                                       SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0)) AS VL_BC_IPI_P,
                                       SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_IPI_P,
                                       SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0),0)) AS VL_ISNT_IPI_P,
                                       SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0),0)) AS VL_OUT_IPI_P,
                                       (CASE WHEN (SUBSTR(IP.CDFISCOPERNF,1,1) IN ('1','2','3')) AND (SUBSTR(IP.CDFISCOPERNF,2,3) IN ('651','652','653','658','659','660','661','662','663','664'))
                                             THEN '1'
                                             ELSE (CASE WHEN SUBSTR(IP.CDFISCOPERNF,1,1) IN ('1','2','3') THEN '0' ELSE '' END)
                                        END) AS IND_PETR,
                                       '' AS IND_IMUN
                                  FROM IMPOSTONF IP, IMPOSTO IM, NOTAFISCAL NF
                                 WHERE IP.CDFILIAL = C_REGE020.CDFILIAL
                                   AND IP.NRLANCTONF = C_REGE020.NRLANCTONF
                                   AND IP.CDFILIAL   = NF.CDFILIAL
                                   AND IP.NRLANCTONF = NF.NRLANCTONF
                                   AND NF.IDSISTEMA  <> 'FIS'
                                   AND C_REGE020.COD_MOD <> '65'
                                   AND IP.CDIMPOSTO = IM.CDIMPOSTO
                                   AND ((IP.IDIMPOSTONF <> '3') AND (IP.IDIMPOSTONF IS NOT NULL))
                                   AND C_REGE020.COD_SIT NOT IN ('81','90')
                                 GROUP BY IP.CDFISCOPERNF, (CASE WHEN SUBSTR(IP.CDFISCOPERNF,1,1) IN ('1','2','3') THEN '0' ELSE '' END)
                                 UNION ALL
                                SELECT SUM(TRUNC((IT.QTPRODVEND  * (IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0))) - NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0),2)) AS VL_CONT_P,
                                       0 AS VL_OP_ISS_P,
                                       IP.CDCFOPPROD 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))) - NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0),2) END
                                                     ELSE 0 END) AS VL_BC_ICMS_P,
                                       MAX(ROUND(IP.VRPEALPRODIT,2)) AS ALIQ_ICMS,
                                       SUM(CASE WHEN IM.IDTPIMPOSFIS = 'T' AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                                                     THEN CASE WHEN IP.VRPRBCREDUICMS > 0
                                                               THEN IP.VRIMPOPRODREDUZ
                                                               ELSE TRUNC(VRIMPOPRODIT,2) END
                                                     ELSE 0 END) AS VL_ICMS_P,
                                       0 AS VL_BC_ST_P,
                                       0 AS VL_ICMS_ST_P,
                                       SUM(CASE WHEN IM.IDTPIMPOSFIS = 'I' AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                                                     THEN TRUNC((IT.QTPRODVEND * (IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0))) - NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0),2)
                                                     ELSE 0 END) AS VL_ISNT_ICMS_P,
                                       SUM(CASE WHEN IM.IDTPIMPOSFIS IN ('N','F')
                                                     THEN TRUNC((IT.QTPRODVEND * (IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0))) - NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0),2)
                                                     ELSE 0 END) AS VL_OUT_ICMS_P,
                                       0 AS VL_BC_IPI_P,
                                       0 AS VL_IPI_P,
                                       0 AS VL_ISNT_IPI_P,
                                       0 AS VL_OUT_IPI_P,
                                       (CASE WHEN (SUBSTR(NVL(IP.CDCFOPPROD, '5102'),1,1) IN ('1','2','3')) AND (SUBSTR(IP.CDCFOPPROD,2,3) IN ('651','652','653','658','659','660','661','662','663','664'))
                                             THEN '1'
                                             ELSE (CASE WHEN SUBSTR(IP.CDCFOPPROD,1,1) IN ('1','2','3') THEN '0' ELSE '' END)
                                        END) AS IND_PETR,
                                       '' AS IND_IMUN
                                  FROM ITEMVENDA IT, ITVENDAIMPOS IP, IMPOSTO IM
                                 WHERE IT.CDFILIAL     = C_REGE020.CDFILIAL
                                   AND IT.CDCAIXA      = C_REGE020.CDCAIXA
                                   AND IT.NRSEQVENDA   = C_REGE020.NRLANCTONF
                                   AND IT.CDFILIAL     = IP.CDFILIAL
                                   AND IT.CDCAIXA      = IP.CDCAIXA
                                   AND IT.NRSEQUITVEND = IP.NRSEQUITVEND
                                   AND IT.NRSEQVENDA   = IP.NRSEQVENDA
                                   AND IP.CDIMPOSTO    = IM.CDIMPOSTO
                                   AND C_REGE020.COD_MOD = '65'
                                   AND C_REGE020.COD_SIT NOT IN ('81','90')
                                 GROUP BY (CASE WHEN (SUBSTR(NVL(IP.CDCFOPPROD, '5102'),1,1) IN ('1','2','3')) AND (SUBSTR(IP.CDCFOPPROD,2,3) IN ('651','652','653','658','659','660','661','662','663','664'))
                                             THEN '1'
                                             ELSE (CASE WHEN SUBSTR(IP.CDCFOPPROD,1,1) IN ('1','2','3') THEN '0' ELSE '' END)
                                            END),CDCFOPPROD
                                 UNION ALL
                                SELECT 0 AS VL_CONT_P,
                                       0 AS VL_OP_ISS_P,
                                       '5102' AS CFOP,
                                       0 AS VL_BC_ICMS_P,
                                       0 AS ALIQ_ICMS,
                                       0 AS VL_ICMS_P,
                                       0 AS VL_BC_ST_P,
                                       0 AS VL_ICMS_ST_P,
                                       0 AS VL_ISNT_ICMS_P,
                                       0 AS VL_OUT_ICMS_P,
                                       0 AS VL_BC_IPI_P,
                                       0 AS VL_IPI_P,
                                       0 AS VL_ISNT_IPI_P,
                                       0 AS VL_OUT_IPI_P,
                                       '' AS IND_PETR,
                                       '' AS IND_IMUN
                                  FROM DUAL
                                 WHERE C_REGE020.COD_SIT IN ('81','90')))LOOP

         V_CONTREGE := NVL(V_CONTREGE,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('502'||LPAD(RTRIM(V_CONTREGE), 7, '0'), C_REGE025.DADOS, P_NRSEQARQMAG);

         /* CODIGO DO CFOP - LINHA 0400*/
         SELECT MAX(CDCHAVEREG)
           INTO V_COD_NAT
           FROM TMP_REGDADOSSEF2
          WHERE CDCHAVEREG = C_REGE025.CFOP
            AND CDREGISTRO = '0400';
         IF (V_COD_NAT IS NULL) AND (C_REGE025.CFOP IS NOT NULL) THEN
            V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
            INSERT INTO TMP_REGDADOSSEF2
              (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
            VALUES
              ('0400', V_SEQUPROD, C_REGE025.CFOP, '');
         END IF;
         V_COD_NAT := null;
      END LOOP;
   END LOOP;

   /*REGISTRO E050*/
   FOR C_REGE050 IN (SELECT '|E050'||'|'||
                            COD_MOD||'|'||
                            QTD_CANC||'|'||
                            REPLACE(REPLACE(SER,'-',''),'1','')||'|'||
                            REPLACE(REPLACE(SER,'-',''),'D','')||'|'||--SUB
                            LTRIM(NUM_DOC_INI,'0')||'|'||
                            LTRIM(NUM_DOC_FIN,'0')||'|'||
                            TO_CHAR(DT_DOC,'ddmmyyyy')||'|'||
                            COP||'|'||
                            NUM_LCTO||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_CONT,2), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_BC_ICMS,2), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_ICMS,2), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_ISNT_ICMS,2), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_OUT_ICMS,2), '999999999990.00')), '.', ',')||'|'||
                            COD_INF_OBS||'|' AS DADOS, CDFILIAL, NRLANCTONF
                       FROM (SELECT NF.CDMODDOCFISC AS COD_MOD, 0 AS QTD_CANC,
                                   NF.CDSERIE AS SER, '' AS SUB, NF.NRNOTAFISC AS NUM_DOC_INI, NF.NRNOTAFISC AS NUM_DOC_FIN,
                                   NF.DTEMISSAO AS DT_DOC, MAX(CF.CDLAYOUTLMAG) AS COP, NF.NRLANCTBDOC AS NUM_LCTO,
                                   SUM(IP.VRCONTABILNF) AS VL_CONT,
                                   SUM(DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0)) AS VL_BC_ICMS,
                                   SUM(DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0)) AS VL_ICMS,
                                   SUM(DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0)) AS VL_ISNT_ICMS,
                                   SUM(DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0)) AS VL_OUT_ICMS,
                                   '' AS COD_INF_OBS, NF.CDFILIAL, NF.NRLANCTONF
                              FROM IMPOSTONF IP, NOTAFISCAL NF, CFOPLIVROMAG CF, IMPOSTO IM
                             WHERE IP.NRSEQUMAPAPU = V_NRSEQUMAPAPU
                               AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                               AND IP.CDIMPOSTO    = IM.CDIMPOSTO
                               AND IP.CDFILIAL     = NF.CDFILIAL
                               AND IP.NRLANCTONF   = NF.NRLANCTONF
                               AND NF.IDENTRSAIDOP = 'S'
                               AND NF.CDMODDOCFISC = '02'
                               AND (NVL(NF.IDSISTEMA, 'DFS') <> 'REC'
                               AND NVL(NF.IDSISTEMA, 'DFS')  <> 'PAG'
                               AND NVL(NF.IDSISTEMA, 'DFS')  <> 'CMP'
                               AND NVL(NF.IDSISTEMA, 'DFS')  <> 'SUP'
                               AND NVL(NF.IDSISTEMA, 'DFS')  <> 'FIS')
                               AND IP.CDFISCOPERNF = CF.CDCFOPLMAG(+)
                               AND CF.CDLIVROMAG(+) = P_CDLIVROMAG
                               AND ((IP.IDIMPOSTONF <> '3') AND (IP.IDIMPOSTONF IS NOT NULL))
                             GROUP BY NF.CDMODDOCFISC, NF.CDSERIE, NF.NRNOTAFISC, NF.DTEMISSAO, NF.NRLANCTBDOC, NF.CDFILIAL, NF.NRLANCTONF))LOOP

      V_CONTREGE := NVL(V_CONTREGE,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('504'||LPAD(RTRIM(V_CONTREGE), 7, '0'), C_REGE050.DADOS, P_NRSEQARQMAG);

      /*REGISTRO E055*/
      FOR C_REGE055 IN (SELECT '|E055'||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(VL_CONT_P,2), '999999999990.00')), '.', ',')||'|'||
                               CFOP||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(VL_BC_ICMS_P,2), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(DECODE(ALIQ_ICMS,0,'',ROUND(ALIQ_ICMS,2)), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(VL_ICMS_P,2), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(VL_ISNT_ICMS_P,2), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(VL_OUT_ICMS_P,2), '999999999990.00')), '.', ',')||'|'||
                               IND_IMUN||'|' AS DADOS, CFOP
                          FROM (SELECT SUM(DECODE(IM.CDIMPOSTONFE,'00',IP.VRCONTABILNF,0)) AS VL_CONT_P,
                                       IP.CDFISCOPERNF AS CFOP,
                                       SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0)) AS VL_BC_ICMS_P,
                                       DECODE(IM.CDIMPOSTONFE,'00',IP.VRALIQNF,0) AS ALIQ_ICMS,
                                       SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_ICMS_P,
                                       SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0),0)) AS VL_ISNT_ICMS_P,
                                       SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0),0)) AS VL_OUT_ICMS_P,
                                       '' AS IND_IMUN
                                  FROM IMPOSTONF IP, IMPOSTO IM
                                 WHERE IP.CDFILIAL = C_REGE050.CDFILIAL
                                   AND IP.NRLANCTONF = C_REGE050.NRLANCTONF
                                   AND IP.CDIMPOSTO = IM.CDIMPOSTO
                                   AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                                   AND ((IP.IDIMPOSTONF <> '3') AND (IP.IDIMPOSTONF IS NOT NULL))
                                 GROUP BY IP.CDFISCOPERNF, DECODE(IM.CDIMPOSTONFE,'00',IP.VRALIQNF,0)))LOOP

         V_CONTREGE := NVL(V_CONTREGE,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('504'||LPAD(RTRIM(V_CONTREGE), 7, '0'), C_REGE055.DADOS, P_NRSEQARQMAG);

         /* CODIGO DO CFOP - LINHA 0400*/
         SELECT MAX(CDCHAVEREG)
           INTO V_COD_NAT
           FROM TMP_REGDADOSSEF2
          WHERE CDCHAVEREG = C_REGE055.CFOP
            AND CDREGISTRO = '0400';
         IF (V_COD_NAT IS NULL) AND (C_REGE055.CFOP IS NOT NULL) THEN
            V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
            INSERT INTO TMP_REGDADOSSEF2
              (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
            VALUES
              ('0400', V_SEQUPROD, C_REGE055.CFOP, '');
         END IF;
         V_COD_NAT := null;
      END LOOP;
   END LOOP;

   /*REGISTRO E060*/
   FOR C_REGE060 IN (SELECT '|E060' || '|' ||
                            '2D' || '|' || /*COD_MOD*/
                            RZ.CDCAIXA || '|' || /*ECF_CX*/
                            SUBSTR(RZ.NRSERIEREDZ,1,20)|| '|' || /*ECF_FAB*/
                            LPAD(LTRIM(NVL(RZ.NRREINIREDZ,'0'),'0'), 3, '0')|| '|' || /*CRO*/
                            LPAD(LTRIM(NVL(RZ.NRREDREDZ,'0'),'0'), 6, '0')|| '|' || /*CRZ*/
                            TO_CHAR(RZ.DTREDUCAOZ,'ddmmyyyy')|| '|' || /*DT_DOC*/
                            LPAD(LTRIM(NVL(RZ.NRPRIMCFREDZ,'0'),'0'), 6, '0')|| '|' || /*NUM_DOC_INI*/
                            LPAD(LTRIM(NVL(RZ.NRULTCFREDZ,'0'),'0'), 6, '0')|| '|' || /*NUM_DOC_FIN*/
                            REPLACE(LTRIM(TO_CHAR(TRUNC(SUM(RZ.VRGTREDZ-RZ.VRVENBRUREDZ),2), '999999999990.00')), '.', ',')|| '|' || /*GT_INI*/
                            REPLACE(LTRIM(TO_CHAR(TRUNC(SUM(RZ.VRGTREDZ),2), '999999999990.00')), '.', ',')|| '|' || /*GT_FIN*/
                            REPLACE(LTRIM(TO_CHAR(TRUNC(SUM(RZ.VRVENBRUREDZ),2), '999999999990.00')), '.', ',')|| '|' || /*VL_BRT*/
                            REPLACE(LTRIM(TO_CHAR(TRUNC(SUM(RZ.VRTOTCANREDZ),2), '999999999990.00')), '.', ',')|| '|' || /*VL_CANC*/
                            REPLACE(LTRIM(TO_CHAR(TRUNC(SUM(RZ.VRTOTDESREDZ),2), '999999999990.00')), '.', ',')|| '|' || /*VL_DESC*/
                            REPLACE(LTRIM(TO_CHAR(TRUNC(SUM(RZ.VRTOTACRREDZ),2), '999999999990.00')), '.', ',')|| '|' || /*VL_ACMO*/
                            REPLACE(LTRIM(TO_CHAR(TRUNC(SUM(RZ.VRISSREDZ),2), '999999999990.00')), '.', ',')|| '|' || /*VL_OP_ISS*/
                            REPLACE(LTRIM(TO_CHAR(TRUNC(SUM(RZ.VRVENLIQREDZ),2), '999999999990.00')), '.', ',')|| '|' || /*VL_LIQ*/
                            REPLACE(LTRIM(TO_CHAR(TRUNC(SUM(NVL(I.VRBCALCIMPRZ,0)),2), '999999999990.00')), '.', ',')|| '|' || /*VL_BC_ICMS*/
                            REPLACE(LTRIM(TO_CHAR(TRUNC(SUM(NVL(I.VRIMPOSTORZ,0)),2), '999999999990.00')), '.', ',')|| '|' || /*VL_ICMS*/
                            REPLACE(LTRIM(TO_CHAR(TRUNC(SUM(RZ.VRISENCREDZ),2), '999999999990.00')), '.', ',')|| '|' || /*VL_ISN*/
                            REPLACE(LTRIM(TO_CHAR(TRUNC(SUM(RZ.VRNINCIDREDZ),2), '999999999990.00')), '.', ',')|| '|' || /*VL_NT*/
                            REPLACE(LTRIM(TO_CHAR(TRUNC(SUM(RZ.VRSTRIBREDZ),2), '999999999990.00')), '.', ',')|| '|' || /*VL_ST*/
                            '' || '|'  AS DADOS, SUM(RZ.VRSTRIBREDZ) AS VRSTRIBREDZ,
                            RZ.CDFILIAL, RZ.CDCAIXA, RZ.NRECFREDZ, RZ.DTREDUCAOZ
                       FROM REDUCAOZ RZ ,FILIAL FI, (SELECT SUM(IP.VRBCALCIMPRZ) AS VRBCALCIMPRZ, SUM(IP.VRIMPOSTORZ) AS VRIMPOSTORZ,
                                                            IP.CDFILIAL, IP.CDCAIXA, IP.NRECFREDZ, IP.DTREDUCAOZ
                                                       FROM IMPOSTOREDZ IP, IMPOSTO IM
                                                      WHERE IP.CDIMPOSTO    = IM.CDIMPOSTO
                                                        AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                                                      GROUP BY IP.CDFILIAL, IP.CDCAIXA, IP.NRECFREDZ, IP.DTREDUCAOZ) I
                      WHERE FI.CDEMPRESA    = P_CDEMPRESA
                        AND FI.CDINSCESTA   = P_CDINSCESTA
                        AND RZ.CDFILIAL     = FI.CDFILIAL
                        AND RZ.DTREDUCAOZ   BETWEEN P_DTINICIAL AND P_DTFINAL
                        AND RZ.NRSERIEREDZ  IS NOT NULL
                        AND I.CDFILIAL(+)   = RZ.CDFILIAL
                        AND I.CDCAIXA(+)    = RZ.CDCAIXA
                        AND I.NRECFREDZ(+)  = RZ.NRECFREDZ
                        AND I.DTREDUCAOZ(+) = RZ.DTREDUCAOZ
                        AND RZ.VRVENBRUREDZ <> RZ.VRTOTCANREDZ
                      GROUP BY RZ.CDCAIXA, SUBSTR(NRSERIEREDZ,1,20),
                            LPAD(LTRIM(NVL(RZ.NRREINIREDZ,'0'),'0'), 3, '0'), LPAD(LTRIM(NVL(RZ.NRREDREDZ,'0'),'0'), 6, '0'),
                            TO_CHAR(RZ.DTREDUCAOZ,'ddmmyyyy'),
                            LPAD(LTRIM(NVL(RZ.NRPRIMCFREDZ,'0'),'0'), 6, '0'), LPAD(LTRIM(NVL(RZ.NRULTCFREDZ,'0'),'0'), 6, '0'),
                            RZ.CDFILIAL, RZ.CDCAIXA, RZ.NRECFREDZ, RZ.DTREDUCAOZ
                      ORDER BY DTREDUCAOZ, LPAD(LTRIM(NVL(RZ.NRREDREDZ,'0'),'0'), 6, '0')) LOOP

      V_CONTREGE := NVL(V_CONTREGE,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('505'||LPAD(RTRIM(V_CONTREGE), 7, '0'), C_REGE060.DADOS, P_NRSEQARQMAG);

      /*REGISTRO E065*/
      FOR C_REGE065 IN (SELECT '|E065'||'|'||
                               '5102'||'|'||
                               REPLACE(LTRIM(TO_CHAR(TRUNC(SUM(VRBCALCIMPRZ),2), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(DECODE(VRALIQIMPRZ,0,'',TRUNC(VRALIQIMPRZ,2)), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(TRUNC(SUM(VRIMPOSTORZ),2), '999999999990.00')), '.', ',')||'|'||
                               ''||'|' AS DADOS, '5102' AS CFOP
                          FROM IMPOSTOREDZ IP, IMPOSTO IM
                         WHERE IP.CDFILIAL   = C_REGE060.CDFILIAL
                           AND IP.CDCAIXA    = C_REGE060.CDCAIXA
                           AND IP.NRECFREDZ  = C_REGE060.NRECFREDZ
                           AND IP.DTREDUCAOZ = C_REGE060.DTREDUCAOZ
                           AND IP.CDIMPOSTO  = IM.CDIMPOSTO
                           AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                         GROUP BY DECODE(VRALIQIMPRZ,0,'',TRUNC(VRALIQIMPRZ,2))
                        UNION ALL
                        SELECT '|E065'||'|'||
                               '5405'||'|'||
                               REPLACE(LTRIM(TO_CHAR(C_REGE060.VRSTRIBREDZ, '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(0, '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(0, '999999999990.00')), '.', ',')||'|'||
                               ''||'|' AS DADOS, '5405' AS CFOP
                          FROM DUAL
                         WHERE C_REGE060.VRSTRIBREDZ > 0
                        ORDER BY 1)LOOP

         V_CONTREGE := NVL(V_CONTREGE,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('505'||LPAD(RTRIM(V_CONTREGE), 7, '0'), C_REGE065.DADOS, P_NRSEQARQMAG);

         /* CODIGO DO CFOP - LINHA 0400*/
         SELECT MAX(CDCHAVEREG)
           INTO V_COD_NAT
           FROM TMP_REGDADOSSEF2
          WHERE CDCHAVEREG = C_REGE065.CFOP
            AND CDREGISTRO = '0400';
         IF (V_COD_NAT IS NULL) AND (C_REGE065.CFOP IS NOT NULL) THEN
            V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
            INSERT INTO TMP_REGDADOSSEF2
              (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
            VALUES
              ('0400', V_SEQUPROD, C_REGE065.CFOP, '');
         END IF;
         V_COD_NAT := null;
      END LOOP;

   END LOOP;

   /*REGISTRO E100*/
   FOR C_REGE100 IN (SELECT '|E100'||'|'||
                            IND_OPER||'|'||
                            CASE WHEN ((IND_OPER='0') AND (COP IN ('EA20','EA30','EA40'))) THEN '0' ELSE IND_EMIT END||'|'||
                            COD_PART||'|'||
                            COD_MUN_SERV||'|'||
                            COD_MOD||'|'||
                            COD_SIT||'|'||
                            DECODE(COD_SIT,'25',QTD_CANC,'')||'|'||
                            DECODE(SER,'','1','-','1','0','1',SER)||'|'||  --DECODE(NVL(SER,'-'),'-',DECODE(COD_MOD,'06','1','U'),SER)||'|'||
                            SUB||'|'||
                            COD_CONS||'|'||
                            LTRIM(NUM_DOC,'0')||'|'||
                            QTD_DOC||'|'||
                            TO_CHAR(DT_EMIS,'ddmmyyyy')||'|'||
                            TO_CHAR(DT_DOC,'ddmmyyyy')||'|'||
                            COP||'|'||
                            NUM_LCTO||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_CONT,2), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_OP_ISS,2), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_BC_ICMS,2), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_ICMS,2), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_ICMS_ST,2), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_ISNT_ICMS,2), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_OUT_ICMS,2), '999999999990.00')), '.', ',')||'|'||
                            COD_INF_OBS||'|' AS DADOS, CDFILIAL, NRLANCTONF, COD_PART, NVL(IDORIGDESTNF,DECODE(IND_OPER,'0','F','C')) AS IDORIGDESTNF
                       FROM (SELECT '0' AS IND_OPER, DECODE(NF.IDSTATUS,'E','0','1') AS IND_EMIT,
                                   DECODE(NF.IDSTATUS,'C','',DECODE(NF.IDORIGDESTNF,'F',NF.CDFORNECED,DECODE(NF.IDORIGDESTNF,'C',NF.CDCLIENTE,NF.CDFILIEMIT))) AS COD_PART,
                                   '' AS COD_MUN_SERV, NF.CDMODDOCFISC AS COD_MOD, DECODE(NF.IDSTATUS,'C','10', DECODE(CDTIPOOPER,'03','07',TIPOOPER_COMPL_E,'07','00')) AS COD_SIT,
                                   0 AS QTD_CANC, NF.CDSERIEENTR AS SER, '' AS SUB, '' AS COD_CONS, NF.NRNOTAFISC AS NUM_DOC, 1 AS QTD_DOC,
                                   NF.DTEMISSAO AS DT_EMIS, NF.DTENTRSAID AS DT_DOC, MAX(CF.CDLAYOUTLMAG) AS COP, NF.NRLANCTBDOC AS NUM_LCTO,
                                   SUM(IP.VRCONTABILNF) AS VL_CONT, 0 AS VL_OP_ISS,
                                   SUM(DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0)) AS VL_BC_ICMS,
                                   SUM(DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0)) AS VL_ICMS,
                                   VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'80','I') AS VL_ICMS_ST,
                                   SUM(DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0)) AS VL_ISNT_ICMS,
                                   SUM(DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0)) AS VL_OUT_ICMS,
                                   '' AS COD_INF_OBS, NF.CDFILIAL, NF.NRLANCTONF, NF.IDORIGDESTNF, MAX(IP.CDFISCOPERNF) AS OPER
                              FROM IMPOSTONF IP, NOTAFISCAL NF, CFOPLIVROMAG CF, IMPOSTO IM
                             WHERE IP.NRSEQUMAPAPU = V_NRSEQUMAPAPU
                               AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                               AND IP.CDIMPOSTO    = IM.CDIMPOSTO
                               AND IP.CDFILIAL     = NF.CDFILIAL
                               AND IP.NRLANCTONF   = NF.NRLANCTONF
                               AND NF.IDENTRSAIDOP = 'E'
                               AND NF.CDMODDOCFISC IN ('06','21','22','28','29')
                               AND IP.CDFISCOPERNF = CF.CDCFOPLMAG(+)
                               AND CF.CDLIVROMAG(+) = P_CDLIVROMAG
                               AND ((IP.IDIMPOSTONF <> '3') AND (IP.IDIMPOSTONF IS NOT NULL))
                             GROUP BY DECODE(NF.IDSTATUS,'E','0','1'),
                                   DECODE(NF.IDSTATUS,'C','',DECODE(NF.IDORIGDESTNF,'F',NF.CDFORNECED,DECODE(NF.IDORIGDESTNF,'C',NF.CDCLIENTE,NF.CDFILIEMIT))),
                                   NF.CDMODDOCFISC, DECODE(NF.IDSTATUS,'C','10', DECODE(CDTIPOOPER,'03','07',TIPOOPER_COMPL_E,'07','00')),
                                   NF.CDSERIEENTR, NF.NRNOTAFISC, NF.DTEMISSAO, NF.DTENTRSAID,  NF.NRLANCTBDOC,
                                   VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'80','I'), NF.CDFILIAL, NF.NRLANCTONF, NF.IDORIGDESTNF
                            UNION ALL
                            SELECT '1' AS IND_OPER, '0' AS IND_EMIT,
                                   DECODE(NF.IDSTATUS,'C','',DECODE(NF.IDORIGDESTNF,'F',NF.CDFORNECED,DECODE(NF.IDORIGDESTNF,'C',NF.CDCLIENTE,NF.CDFILIRECE))) AS COD_PART,
                                   DECODE(NF.IDSTATUS,'C','',MU.CDMUNIEXPO) AS COD_MUN_SERV,
                                   NF.CDMODDOCFISC AS COD_MOD, DECODE(NF.IDSTATUS,'C','10',DECODE(NF.CDTIPOOPER,'03','02',TIPOOPER_COMPL_S,'02','00')) AS COD_SIT,
                                   0 AS QTD_CANC, NF.CDSERIE AS SER, '' AS SUB, '' AS COD_CONS, NF.NRNOTAFISC AS NUM_DOC, 1 AS QTD_DOC,
                                   NF.DTEMISSAO AS DT_EMIS, NF.DTEMISSAO AS DT_DOC, MAX(CF.CDLAYOUTLMAG) AS COP, NF.NRLANCTBDOC AS NUM_LCTO,
                                   SUM(IP.VRCONTABILNF) AS VL_CONT, 0 AS VL_OP_ISS,
                                   SUM(DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0)) AS VL_BC_ICMS,
                                   SUM(DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0)) AS VL_ICMS,
                                   VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'80','I') AS VL_ICMS_ST,
                                   SUM(DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0)) AS VL_ISNT_ICMS,
                                   SUM(DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0)) AS VL_OUT_ICMS,
                                   '' AS COD_INF_OBS, NF.CDFILIAL, NF.NRLANCTONF, NF.IDORIGDESTNF, MAX(IP.CDFISCOPERNF) AS OPER
                              FROM IMPOSTONF IP, NOTAFISCAL NF, ENDEFILI EF, MUNICIPIO MU, CFOPLIVROMAG CF, IMPOSTO IM
                             WHERE IP.NRSEQUMAPAPU = V_NRSEQUMAPAPU
                               AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                               AND IP.CDIMPOSTO    = IM.CDIMPOSTO
                               AND IP.CDFILIAL     = NF.CDFILIAL
                               AND IP.NRLANCTONF   = NF.NRLANCTONF
                               AND NF.IDENTRSAIDOP = 'S'
                               AND NF.CDMODDOCFISC IN ('06','21','22','28','29')
                               AND NF.CDFISOPENTF  = CF.CDCFOPLMAG(+)
                               AND CF.CDLIVROMAG(+) = P_CDLIVROMAG
                               AND NF.CDFILIEMIT   = EF.CDFILIAL(+)
                               AND EF.IDTPENDEFILI(+) = 'P'
                               AND EF.CDPAIS       = MU.CDPAIS(+)
                               AND EF.SGESTADO     = MU.SGESTADO(+)
                               AND EF.CDMUNICIPIO  = MU.CDMUNICIPIO(+)
                               AND ((IP.IDIMPOSTONF <> '3') AND (IP.IDIMPOSTONF IS NOT NULL))
                             GROUP BY DECODE(NF.IDSTATUS,'C','',DECODE(NF.IDORIGDESTNF,'F',NF.CDFORNECED,DECODE(NF.IDORIGDESTNF,'C',NF.CDCLIENTE,NF.CDFILIRECE))),
                                   DECODE(NF.IDSTATUS,'C','',MU.CDMUNIEXPO),
                                   NF.CDMODDOCFISC, DECODE(NF.IDSTATUS,'C','10',DECODE(NF.CDTIPOOPER,'03','02',TIPOOPER_COMPL_S,'02','00')),
                                   NF.CDSERIE, NF.NRNOTAFISC, NF.DTEMISSAO, NF.DTEMISSAO, NF.NRLANCTBDOC,
                                   VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'80','I'), NF.CDFILIAL, NF.NRLANCTONF, NF.IDORIGDESTNF))LOOP

      V_CONTREGE := NVL(V_CONTREGE,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('506'||LPAD(RTRIM(V_CONTREGE), 7, '0'), C_REGE100.DADOS, P_NRSEQARQMAG);

      /* CODIDO DO PARTICIPANTE - LINHA 0150*/
      SELECT MAX(CDCHAVEREG)
        INTO V_COD_PAR
        FROM TMP_REGDADOSSEF2
       WHERE CDCHAVEREG = C_REGE100.COD_PART
         AND CDREGISTRO = '0150';
      IF (V_COD_PAR IS NULL) AND (C_REGE100.COD_PART IS NOT NULL) THEN
         V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
         INSERT INTO TMP_REGDADOSSEF2
           (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
         VALUES
           ('0150', V_SEQUPROD, C_REGE100.COD_PART, C_REGE100.IDORIGDESTNF);
      END IF;
      V_COD_PAR := null;

      /*REGISTRO E105*/
      FOR C_REGE105 IN (SELECT '|E105'||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(VL_CONT_P,2), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(VL_OP_ISS_P,2), '999999999990.00')), '.', ',')||'|'||
                               CFOP||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(VL_BC_ICMS_P,2), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(DECODE(ALIQ_ICMS,0,'',ROUND(ALIQ_ICMS,2)), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(VL_ICMS_P,2), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(VL_ICMS_ST_P,2), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(VL_ISNT_ICMS_P,2), '999999999990.00')), '.', ',')||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(VL_OUT_ICMS_P,2), '999999999990.00')), '.', ',')||'|'||
                               IND_PETR||'|' AS DADOS, CFOP
                          FROM (SELECT SUM(DECODE(IM.CDIMPOSTONFE,'00',IP.VRCONTABILNF,0)) AS VL_CONT_P,
                                       0 AS VL_OP_ISS_P,
                                       IP.CDFISCOPERNF AS CFOP,
                                       SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0)) AS VL_BC_ICMS_P,
                                       DECODE(IM.CDIMPOSTONFE,'00',IP.VRALIQNF,0) AS ALIQ_ICMS,
                                       SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_ICMS_P,
                                       SUM(DECODE(IM.CDIMPOSTONFE,'80',IP.VRIMPOSTONF,0)) AS VL_ICMS_ST_P,
                                       SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0),0)) AS VL_ISNT_ICMS_P,
                                       SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0),0)) AS VL_OUT_ICMS_P,
                                       (CASE WHEN SUBSTR(IP.CDFISCOPERNF,1,1) IN ('1','2','3') THEN '0' ELSE '' END) AS IND_PETR
                                  FROM IMPOSTONF IP, IMPOSTO IM
                                 WHERE IP.CDFILIAL = C_REGE100.CDFILIAL
                                   AND IP.NRLANCTONF = C_REGE100.NRLANCTONF
                                   AND IP.CDIMPOSTO = IM.CDIMPOSTO
                                   AND ((IP.IDIMPOSTONF <> '3') AND (IP.IDIMPOSTONF IS NOT NULL))
                                 GROUP BY IP.CDFISCOPERNF, DECODE(IM.CDIMPOSTONFE,'00',IP.VRALIQNF,0),
                                          (CASE WHEN SUBSTR(IP.CDFISCOPERNF,1,1) IN ('1','2','3') THEN '0' ELSE '' END)))LOOP

         V_CONTREGE := NVL(V_CONTREGE,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('506'||LPAD(RTRIM(V_CONTREGE), 7, '0'), C_REGE105.DADOS, P_NRSEQARQMAG);

         /* CODIGO DO CFOP - LINHA 0400*/
         SELECT MAX(CDCHAVEREG)
           INTO V_COD_NAT
           FROM TMP_REGDADOSSEF2
          WHERE CDCHAVEREG = C_REGE105.CFOP
            AND CDREGISTRO = '0400';
         IF (V_COD_NAT IS NULL) AND (C_REGE105.CFOP IS NOT NULL) THEN
            V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
            INSERT INTO TMP_REGDADOSSEF2
              (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
            VALUES
              ('0400', V_SEQUPROD, C_REGE105.CFOP, '');
         END IF;
         V_COD_NAT := null;
      END LOOP;
   END LOOP;

   /*REGISTRO E120*/
   FOR C_REGE120 IN (SELECT '|E120'||'|'||
                            IND_OPER||'|'||
                            CASE WHEN ((IND_OPER='0') AND (COP IN ('EA20','EA30','EA40'))) THEN '0' ELSE IND_EMIT END||'|'||
                            COD_PART||'|'||
                            COD_MUN_SERV||'|'||
                            COD_MOD||'|'||
                            COD_SIT||'|'||
                            DECODE(SER,'','1','-','1','0','1',SER)||'|'|| --REPLACE(SER,'-','U')||'|'||
                            SUB||'|'||
                            LTRIM(NUM_DOC,'0')||'|'||
                            CHV_NFE||'|'||
                            TO_CHAR(DT_EMIS,'ddmmyyyy')||'|'||
                            TO_CHAR(DT_DOC,'ddmmyyyy')||'|'||
                            COP||'|'||
                            NUM_LCTO||'|'||
                            ''||'|'||--IND_PGTO
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_CONT,2), '999999999990.00')), '.', ',')||'|'||
                            CFOP||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_BC_ICMS,2), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(DECODE(ALIQ_ICMS,0,'',ROUND(ALIQ_ICMS,2)), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_ICMS,2), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(DECODE(VL_ICMS_ST,0,'',ROUND(VL_ICMS_ST,2)), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_ISNT_ICMS,2), '999999999990.00')), '.', ',')||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(VL_OUT_ICMS,2), '999999999990.00')), '.', ',')||'|'||
                            COD_INF_OBS||'|' AS DADOS, CDFILIAL, NRLANCTONF, COD_PART, NVL(IDORIGDESTNF,DECODE(IND_OPER,'0','F','C')) AS IDORIGDESTNF
                       FROM (SELECT '0' AS IND_OPER, DECODE(NF.IDSTATUS,'E','0','1') AS IND_EMIT,
                                   DECODE(NF.IDSTATUS,'C','',DECODE(NF.IDORIGDESTNF,'F',NF.CDFORNECED,DECODE(NF.IDORIGDESTNF,'C',NF.CDCLIENTE,NF.CDFILIEMIT))) AS COD_PART,
                                   '' AS COD_MUN_SERV,
                                   NF.CDMODDOCFISC AS COD_MOD, DECODE(NF.IDSTATUS,'C','10',DECODE(CDTIPOOPER,'03','07',TIPOOPER_COMPL_E,'07','00')) AS COD_SIT,
                                   NF.CDSERIEENTR AS SER, '' AS SUB, NF.NRNOTAFISC AS NUM_DOC, DECODE(NF.CDMODDOCFISC,'55',NF.NRACESSONFE,'') AS CHV_NFE,
                                   NF.DTEMISSAO AS DT_EMIS, NF.DTENTRSAID AS DT_DOC, MAX(CF.CDLAYOUTLMAG) AS COP, NF.NRLANCTBDOC AS NUM_LCTO,
                                   DECODE(NF.IDSTATUS,'C','9',DECODE(PG.NRSEQVENPAG,1,'0','1')) AS IND_PGTO, SUM(IP.VRCONTABILNF) AS VL_CONT,
                                   IP.CDFISCOPERNF AS CFOP,
                                   SUM(DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0)) AS VL_BC_ICMS, IP.VRALIQNF AS ALIQ_ICMS,
                                   SUM(DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0)) AS VL_ICMS,
                                   VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'80','I') AS VL_ICMS_ST,
                                   SUM(DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0)) AS VL_ISNT_ICMS,
                                   SUM(DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0)) AS VL_OUT_ICMS,
                                   '' AS COD_INF_OBS, NF.CDFILIAL, NF.NRLANCTONF, NF.IDORIGDESTNF
                              FROM IMPOSTONF IP, NOTAFISCAL NF, CFOPLIVROMAG CF, IMPOSTO IM,
                                   (SELECT COUNT(C.NRSEQVENPAG) NRSEQVENPAG, C.CDFILIAL, C.NRLANCTONF
                                      FROM CONTAPAGAR C, NOTAFISCAL N
                                     WHERE N.CDFILIAL   = C.CDFILIAL
                                       AND N.NRLANCTONF = C.NRLANCTONF
                                     GROUP BY C.CDFILIAL, C.NRLANCTONF) PG
                             WHERE IP.NRSEQUMAPAPU = V_NRSEQUMAPAPU
                               AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                               AND IP.CDIMPOSTO    = IM.CDIMPOSTO
                               AND IP.CDFILIAL     = NF.CDFILIAL
                               AND IP.NRLANCTONF   = NF.NRLANCTONF
                               AND NF.IDENTRSAIDOP = 'E'
                               AND NF.CDMODDOCFISC IN ('07','08','09','10','11','26','27','57','67','18')
                               AND NF.CDFILIAL     = PG.CDFILIAL(+)
                               AND NF.NRLANCTONF   = PG.NRLANCTONF(+)
                               AND IP.CDFISCOPERNF = CF.CDCFOPLMAG(+)
                               AND CF.CDLIVROMAG(+) = P_CDLIVROMAG
                               AND ((IP.IDIMPOSTONF <> '3') AND (IP.IDIMPOSTONF IS NOT NULL))
                             GROUP BY DECODE(NF.IDSTATUS,'E','0','1'),
                                   DECODE(NF.IDSTATUS,'C','',DECODE(NF.IDORIGDESTNF,'F',NF.CDFORNECED,DECODE(NF.IDORIGDESTNF,'C',NF.CDCLIENTE,NF.CDFILIEMIT))),
                                   NF.CDMODDOCFISC, DECODE(NF.IDSTATUS,'C','10',DECODE(CDTIPOOPER,'03','07',TIPOOPER_COMPL_E,'07','00')),
                                   NF.CDSERIEENTR, NF.NRNOTAFISC, DECODE(NF.CDMODDOCFISC,'55',NF.NRACESSONFE,''),
                                   NF.DTEMISSAO, NF.DTENTRSAID,  NF.NRLANCTBDOC, DECODE(NF.IDSTATUS,'C','9',DECODE(PG.NRSEQVENPAG,1,'0','1')),
                                   IP.CDFISCOPERNF, IP.VRALIQNF, VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'80','I'), NF.CDFILIAL, NF.NRLANCTONF, NF.IDORIGDESTNF
                            UNION ALL
                            SELECT '1' AS IND_OPER, '0' AS IND_EMIT,
                                   DECODE(NF.IDSTATUS,'C','',DECODE(NF.IDORIGDESTNF,'F',NF.CDFORNECED,DECODE(NF.IDORIGDESTNF,'C',NF.CDCLIENTE,NF.CDFILIRECE))) AS COD_PART,
                                   DECODE(NF.IDSTATUS,'C','',MU.CDMUNIEXPO) AS COD_MUN_SERV,
                                   NF.CDMODDOCFISC AS COD_MOD, DECODE(NF.IDSTATUS,'C','10',DECODE(NF.CDTIPOOPER,'03','02',TIPOOPER_COMPL_S,'02','00')) AS COD_SIT,
                                   NF.CDSERIE AS SER, '' AS SUB, NF.NRNOTAFISC AS NUM_DOC, DECODE(NF.CDMODDOCFISC,'55',NF.NRACESSONFE,'') AS CHV_NFE,
                                   NF.DTEMISSAO AS DT_EMIS, NF.DTEMISSAO AS DT_DOC, MAX(CF.CDLAYOUTLMAG) AS COP, NF.NRLANCTBDOC AS NUM_LCTO,
                                   DECODE(NF.IDSTATUS,'C','9',DECODE(PG.NRSEQVENREC,1,'0','1')) AS IND_PGTO, SUM(IP.VRCONTABILNF) AS VL_CONT,
                                   IP.CDFISCOPERNF AS CFOP,
                                   SUM(DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0)) AS VL_BC_ICMS, IP.VRALIQNF AS ALIQ_ICMS,
                                   SUM(DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0)) AS VL_ICMS,
                                   VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'80','I') AS VL_ICMS_ST,
                                   SUM(DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0)) AS VL_ISNT_ICMS,
                                   SUM(DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0)) AS VL_OUT_ICMS,
                                   '' AS COD_INF_OBS, NF.CDFILIAL, NF.NRLANCTONF, NF.IDORIGDESTNF
                              FROM IMPOSTONF IP, NOTAFISCAL NF, ENDEFILI EF, MUNICIPIO MU, CFOPLIVROMAG CF, IMPOSTO IM,
                                   (SELECT COUNT(C.NRSEQVENREC) NRSEQVENREC, N.CDFILIAL, N.NRLANCTONF
                                      FROM CONTARECEBER C, NOTAFISCAL N
                                     WHERE N.CDFILIAL   = C.CDFILIAL
                                       AND N.NRLANCTONF = C.NRLANCTONF
                                     GROUP BY N.CDFILIAL, N.NRLANCTONF) PG
                             WHERE IP.NRSEQUMAPAPU = V_NRSEQUMAPAPU
                               AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                               AND IP.CDIMPOSTO    = IM.CDIMPOSTO
                               AND IP.CDFILIAL     = NF.CDFILIAL
                               AND IP.NRLANCTONF   = NF.NRLANCTONF
                               AND NF.IDENTRSAIDOP = 'S'
                               AND NF.CDMODDOCFISC IN ('07','08','09','10','11','26','27','57','18')
                               AND NF.CDFILIAL     = PG.CDFILIAL(+)
                               AND NF.NRLANCTONF   = PG.NRLANCTONF(+)
                               AND NF.CDFILIEMIT   = EF.CDFILIAL(+)
                               AND EF.IDTPENDEFILI(+) = 'P'
                               AND EF.CDPAIS       = MU.CDPAIS(+)
                               AND EF.SGESTADO     = MU.SGESTADO(+)
                               AND EF.CDMUNICIPIO  = MU.CDMUNICIPIO(+)
                               AND IP.CDFISCOPERNF = CF.CDCFOPLMAG(+)
                               AND CF.CDLIVROMAG(+) = P_CDLIVROMAG
                               AND ((IP.IDIMPOSTONF <> '3') AND (IP.IDIMPOSTONF IS NOT NULL))
                             GROUP BY DECODE(NF.IDSTATUS,'C','',DECODE(NF.IDORIGDESTNF,'F',NF.CDFORNECED,DECODE(NF.IDORIGDESTNF,'C',NF.CDCLIENTE,NF.CDFILIRECE))),
                                   DECODE(NF.IDSTATUS,'C','',MU.CDMUNIEXPO),
                                   NF.CDMODDOCFISC, DECODE(NF.IDSTATUS,'C','10',DECODE(NF.CDTIPOOPER,'03','02',TIPOOPER_COMPL_S,'02','00')),
                                   NF.CDSERIE, NF.NRNOTAFISC, DECODE(NF.CDMODDOCFISC,'55',NF.NRACESSONFE,''),
                                   NF.DTEMISSAO, NF.DTEMISSAO,  NF.NRLANCTBDOC, DECODE(NF.IDSTATUS,'C','9',DECODE(PG.NRSEQVENREC,1,'0','1')),
                                   IP.CDFISCOPERNF, IP.VRALIQNF, VALOR_IMPOSTO_NFE(IP.CDFILIAL,IP.NRLANCTONF,'80','I'), NF.CDFILIAL, NF.NRLANCTONF, NF.IDORIGDESTNF))LOOP

      V_CONTREGE := NVL(V_CONTREGE,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('508'||LPAD(RTRIM(V_CONTREGE), 7, '0'), C_REGE120.DADOS, P_NRSEQARQMAG);

      /* CODIDO DO PARTICIPANTE - LINHA 0150*/
      SELECT MAX(CDCHAVEREG)
        INTO V_COD_PAR
        FROM TMP_REGDADOSSEF2
       WHERE CDCHAVEREG = C_REGE120.COD_PART
         AND CDREGISTRO = '0150';
      IF (V_COD_PAR IS NULL) AND (C_REGE120.COD_PART IS NOT NULL) THEN
         V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
         INSERT INTO TMP_REGDADOSSEF2
           (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
         VALUES
           ('0150', V_SEQUPROD, C_REGE120.COD_PART, C_REGE120.IDORIGDESTNF);
      END IF;
      V_COD_PAR := null;

   END LOOP;

   FOR C_REGE300 IN (SELECT DISTINCT ('|E300'                   || '|' ||
                            TO_CHAR(DTINIPERIODO,'ddmmyyyy')    || '|' ||
                            TO_CHAR(DTFIMPERIODO,'ddmmyyyy')    || '|'  ) AS DADOS
                       FROM MAPAAPURAC
                      WHERE NRSEQUMAPAPU = V_NRSEQUMAPAPU) LOOP
      V_CONTREGE := NVL(V_CONTREGE,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('509'||LPAD(RTRIM(V_CONTREGE), 7, '0'), C_REGE300.DADOS, P_NRSEQARQMAG);

      /*REGISTRO E310*/
      FOR C_REGE310 IN (SELECT '|E310' ||'|'||
                                REPLACE(LTRIM(TO_CHAR(ROUND(VL_CONT,2), '999999999990.00')), '.', ',')||'|'||
                                REPLACE(LTRIM(TO_CHAR(ROUND(VL_OP_ISS,2), '999999999990.00')), '.', ',')||'|'||
                                CFOP||'|'||
                                REPLACE(LTRIM(TO_CHAR(ROUND(VL_BC_ICMS,2), '999999999990.00')), '.', ',')||'|'||
                                REPLACE(LTRIM(TO_CHAR(ROUND(VL_ICMS,2), '999999999990.00')), '.', ',')||'|'||
                                REPLACE(LTRIM(TO_CHAR(ROUND(VL_ICMS_ST,2), '999999999990.00')), '.', ',')||'|'||
                                REPLACE(LTRIM(TO_CHAR(ROUND(VL_ISNT_ICMS,2), '999999999990.00')), '.', ',')||'|'||
                                REPLACE(LTRIM(TO_CHAR(ROUND(VL_OUT_ICMS,2), '999999999990.00')), '.', ',')||'|'||
                                IND_IMUN ||'|' AS DADOS
                           FROM (SELECT SUM(DECODE(IM.CDIMPOSTONFE,'00',IP.VRCONTABILNF,0)) AS VL_CONT,
                                        0 AS VL_OP_ISS,
                                        IP.CDFISCOPERNF AS CFOP,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0)) AS VL_BC_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'80',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_ICMS_ST,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0),0)) AS VL_ISNT_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0),0)) AS VL_OUT_ICMS,
                                        '' IND_IMUN
                                   FROM IMPOSTONF IP, IMPOSTO IM,
                                        (SELECT DISTINCT CDFILIAL, NRLANCTONF
                                           FROM IMPOSTONF
                                          WHERE NRSEQUMAPAPU = V_NRSEQUMAPAPU) NF
                                  WHERE IP.CDFILIAL = NF.CDFILIAL
                                    AND IP.NRLANCTONF = NF.NRLANCTONF
                                    AND IP.CDIMPOSTO = IM.CDIMPOSTO
                                    AND IM.CDIMPOSTONFE IN ('00','80')
                                    AND ((IP.IDIMPOSTONF <> '3') AND (IP.IDIMPOSTONF IS NOT NULL))
                                  GROUP BY IP.CDFISCOPERNF))LOOP

          V_CONTREGE := NVL(V_CONTREGE,0) + 1;
          INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
            ('509'||LPAD(RTRIM(V_CONTREGE), 7, '0'), C_REGE310.DADOS, P_NRSEQARQMAG);

      END LOOP;

      /*REGISTRO E330*/
      FOR C_REGE330 IN (SELECT '|E330' ||'|'||
                                IND_TOT ||'|'||
                                REPLACE(LTRIM(TO_CHAR(ROUND(NVL(VL_CONT,0),2), '999999999990.00')), '.', ',')||'|'||
                                REPLACE(LTRIM(TO_CHAR(ROUND(NVL(VL_OP_ISS,0),2), '999999999990.00')), '.', ',')||'|'||
                                REPLACE(LTRIM(TO_CHAR(ROUND(NVL(VL_BC_ICMS,0),2), '999999999990.00')), '.', ',')||'|'||
                                REPLACE(LTRIM(TO_CHAR(ROUND(NVL(VL_ICMS,0),2), '999999999990.00')), '.', ',')||'|'||
                                REPLACE(LTRIM(TO_CHAR(ROUND(NVL(VL_ICMS_ST,0),2), '999999999990.00')), '.', ',')||'|'||
                                REPLACE(LTRIM(TO_CHAR(ROUND(NVL(VL_ST_ENT,0),2), '999999999990.00')), '.', ',')||'|'||
                                REPLACE(LTRIM(TO_CHAR(ROUND(NVL(VL_ST_FNT,0),2), '999999999990.00')), '.', ',')||'|'||
                                REPLACE(LTRIM(TO_CHAR(ROUND(NVL(VL_ST_UF,0),2), '999999999990.00')), '.', ',')||'|'||
                                REPLACE(LTRIM(TO_CHAR(ROUND(NVL(VL_ST_OE,0),2), '999999999990.00')), '.', ',')||'|'||
                                REPLACE(LTRIM(TO_CHAR(ROUND(NVL(VL_AT,0),2), '999999999990.00')), '.', ',')||'|'||
                                REPLACE(LTRIM(TO_CHAR(ROUND(NVL(VL_ISNT_ICMS,0),2), '999999999990.00')), '.', ',')||'|'||
                                REPLACE(LTRIM(TO_CHAR(ROUND(NVL(VL_OUT_ICMS,0),2), '999999999990.00')), '.', ',')||'|' AS DADOS
                           FROM (SELECT '1' AS IND_TOT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',IP.VRCONTABILNF,0)) AS VL_CONT,
                                        0 AS VL_OP_ISS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0)) AS VL_BC_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'80',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_ICMS_ST,
                                        0 AS VL_ST_ENT,
                                        0 AS VL_ST_FNT,
                                        0 AS VL_ST_UF,
                                        0 AS VL_ST_OE,
                                        0 AS VL_AT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0),0)) AS VL_ISNT_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0),0)) AS VL_OUT_ICMS
                                   FROM IMPOSTONF IP, IMPOSTO IM,
                                        (SELECT DISTINCT CDFILIAL, NRLANCTONF
                                           FROM IMPOSTONF
                                          WHERE NRSEQUMAPAPU = V_NRSEQUMAPAPU) NF
                                  WHERE IP.CDFILIAL = NF.CDFILIAL
                                    AND IP.NRLANCTONF = NF.NRLANCTONF
                                    AND SUBSTR(IP.CDFISCOPERNF,1,1) = '1'
                                    AND IP.CDIMPOSTO = IM.CDIMPOSTO
                                    AND ((IP.IDIMPOSTONF <> '3') AND (IP.IDIMPOSTONF IS NOT NULL))
                                 UNION ALL
                                 SELECT '2' AS IND_TOT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',IP.VRCONTABILNF,0)) AS VL_CONT,
                                        0 AS VL_OP_ISS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0)) AS VL_BC_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'80',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_ICMS_ST,
                                        0 AS VL_ST_ENT,
                                        0 AS VL_ST_FNT,
                                        0 AS VL_ST_UF,
                                        0 AS VL_ST_OE,
                                        0 AS VL_AT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0),0)) AS VL_ISNT_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0),0)) AS VL_OUT_ICMS
                                   FROM IMPOSTONF IP, IMPOSTO IM,
                                        (SELECT DISTINCT CDFILIAL, NRLANCTONF
                                           FROM IMPOSTONF
                                          WHERE NRSEQUMAPAPU = V_NRSEQUMAPAPU) NF
                                  WHERE IP.CDFILIAL = NF.CDFILIAL
                                    AND IP.NRLANCTONF = NF.NRLANCTONF
                                    AND SUBSTR(IP.CDFISCOPERNF,1,1) = '2'
                                    AND IP.CDIMPOSTO = IM.CDIMPOSTO
                                    AND ((IP.IDIMPOSTONF <> '3') AND (IP.IDIMPOSTONF IS NOT NULL))
                                 UNION ALL
                                 SELECT '3' AS IND_TOT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',IP.VRCONTABILNF,0)) AS VL_CONT,
                                        0 AS VL_OP_ISS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0)) AS VL_BC_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'80',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_ICMS_ST,
                                        0 AS VL_ST_ENT,
                                        0 AS VL_ST_FNT,
                                        0 AS VL_ST_UF,
                                        0 AS VL_ST_OE,
                                        0 AS VL_AT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0),0)) AS VL_ISNT_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0),0)) AS VL_OUT_ICMS
                                   FROM IMPOSTONF IP, IMPOSTO IM,
                                        (SELECT DISTINCT CDFILIAL, NRLANCTONF
                                           FROM IMPOSTONF
                                          WHERE NRSEQUMAPAPU = V_NRSEQUMAPAPU) NF
                                  WHERE IP.CDFILIAL = NF.CDFILIAL
                                    AND IP.NRLANCTONF = NF.NRLANCTONF
                                    AND SUBSTR(IP.CDFISCOPERNF,1,1) = '3'
                                    AND IP.CDIMPOSTO = IM.CDIMPOSTO
                                    AND ((IP.IDIMPOSTONF <> '3') AND (IP.IDIMPOSTONF IS NOT NULL))
                                 UNION ALL
                                 SELECT '4' AS IND_TOT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',IP.VRCONTABILNF,0)) AS VL_CONT,
                                        0 AS VL_OP_ISS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0)) AS VL_BC_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'80',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_ICMS_ST,
                                        0 AS VL_ST_ENT,
                                        0 AS VL_ST_FNT,
                                        0 AS VL_ST_UF,
                                        0 AS VL_ST_OE,
                                        0 AS VL_AT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0),0)) AS VL_ISNT_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0),0)) AS VL_OUT_ICMS
                                   FROM IMPOSTONF IP, IMPOSTO IM,
                                        (SELECT DISTINCT CDFILIAL, NRLANCTONF
                                           FROM IMPOSTONF
                                          WHERE NRSEQUMAPAPU = V_NRSEQUMAPAPU) NF
                                  WHERE IP.CDFILIAL = NF.CDFILIAL
                                    AND IP.NRLANCTONF = NF.NRLANCTONF
                                    AND SUBSTR(IP.CDFISCOPERNF,1,1) IN ('1','2','3')
                                    AND IP.CDIMPOSTO = IM.CDIMPOSTO
                                    AND ((IP.IDIMPOSTONF <> '3') AND (IP.IDIMPOSTONF IS NOT NULL))
                                 UNION ALL
                                 SELECT '5' AS IND_TOT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',IP.VRCONTABILNF,0)) AS VL_CONT,
                                        0 AS VL_OP_ISS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0)) AS VL_BC_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'80',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_ICMS_ST,
                                        0 AS VL_ST_ENT,
                                        0 AS VL_ST_FNT,
                                        0 AS VL_ST_UF,
                                        0 AS VL_ST_OE,
                                        0 AS VL_AT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0),0)) AS VL_ISNT_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0),0)) AS VL_OUT_ICMS
                                   FROM IMPOSTONF IP, IMPOSTO IM,
                                        (SELECT DISTINCT CDFILIAL, NRLANCTONF
                                           FROM IMPOSTONF
                                          WHERE NRSEQUMAPAPU = V_NRSEQUMAPAPU) NF
                                  WHERE IP.CDFILIAL = NF.CDFILIAL
                                    AND IP.NRLANCTONF = NF.NRLANCTONF
                                    AND SUBSTR(IP.CDFISCOPERNF,1,1) = '5'
                                    AND IP.CDIMPOSTO = IM.CDIMPOSTO
                                    AND ((IP.IDIMPOSTONF <> '3') AND (IP.IDIMPOSTONF IS NOT NULL))
                                 UNION ALL
                                 SELECT '6' AS IND_TOT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',IP.VRCONTABILNF,0)) AS VL_CONT,
                                        0 AS VL_OP_ISS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0)) AS VL_BC_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'80',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_ICMS_ST,
                                        0 AS VL_ST_ENT,
                                        0 AS VL_ST_FNT,
                                        0 AS VL_ST_UF,
                                        0 AS VL_ST_OE,
                                        0 AS VL_AT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0),0)) AS VL_ISNT_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0),0)) AS VL_OUT_ICMS
                                   FROM IMPOSTONF IP, IMPOSTO IM,
                                        (SELECT DISTINCT CDFILIAL, NRLANCTONF
                                           FROM IMPOSTONF
                                          WHERE NRSEQUMAPAPU = V_NRSEQUMAPAPU) NF
                                  WHERE IP.CDFILIAL = NF.CDFILIAL
                                    AND IP.NRLANCTONF = NF.NRLANCTONF
                                    AND SUBSTR(IP.CDFISCOPERNF,1,1) = '6'
                                    AND IP.CDIMPOSTO = IM.CDIMPOSTO
                                    AND ((IP.IDIMPOSTONF <> '3') AND (IP.IDIMPOSTONF IS NOT NULL))
                                 UNION ALL
                                 SELECT '7' AS IND_TOT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',IP.VRCONTABILNF,0)) AS VL_CONT,
                                        0 AS VL_OP_ISS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0)) AS VL_BC_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'80',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_ICMS_ST,
                                        0 AS VL_ST_ENT,
                                        0 AS VL_ST_FNT,
                                        0 AS VL_ST_UF,
                                        0 AS VL_ST_OE,
                                        0 AS VL_AT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0),0)) AS VL_ISNT_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0),0)) AS VL_OUT_ICMS
                                   FROM IMPOSTONF IP, IMPOSTO IM,
                                        (SELECT DISTINCT CDFILIAL, NRLANCTONF
                                           FROM IMPOSTONF
                                          WHERE NRSEQUMAPAPU = V_NRSEQUMAPAPU) NF
                                  WHERE IP.CDFILIAL = NF.CDFILIAL
                                    AND IP.NRLANCTONF = NF.NRLANCTONF
                                    AND SUBSTR(IP.CDFISCOPERNF,1,1) = '7'
                                    AND IP.CDIMPOSTO = IM.CDIMPOSTO
                                    AND ((IP.IDIMPOSTONF <> '3') AND (IP.IDIMPOSTONF IS NOT NULL))
                                 UNION ALL
                                 SELECT '8' AS IND_TOT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',IP.VRCONTABILNF,0)) AS VL_CONT,
                                        0 AS VL_OP_ISS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0)) AS VL_BC_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'80',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_ICMS_ST,
                                        0 AS VL_ST_ENT,
                                        0 AS VL_ST_FNT,
                                        0 AS VL_ST_UF,
                                        0 AS VL_ST_OE,
                                        0 AS VL_AT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0),0)) AS VL_ISNT_ICMS,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'00',DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0),0)) AS VL_OUT_ICMS
                                   FROM IMPOSTONF IP, IMPOSTO IM,
                                        (SELECT DISTINCT CDFILIAL, NRLANCTONF
                                           FROM IMPOSTONF
                                          WHERE NRSEQUMAPAPU = V_NRSEQUMAPAPU) NF
                                  WHERE IP.CDFILIAL = NF.CDFILIAL
                                    AND IP.NRLANCTONF = NF.NRLANCTONF
                                    AND SUBSTR(IP.CDFISCOPERNF,1,1) IN ('5','6','7')
                                    AND IP.CDIMPOSTO = IM.CDIMPOSTO
                                    AND ((IP.IDIMPOSTONF <> '3') AND (IP.IDIMPOSTONF IS NOT NULL)))
                          ORDER BY IND_TOT)LOOP

          V_CONTREGE := NVL(V_CONTREGE,0) + 1;
          INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
            ('509'||LPAD(RTRIM(V_CONTREGE), 7, '0'), C_REGE330.DADOS, P_NRSEQARQMAG);

      END LOOP;

      /*REGISTRO E340*/
      BEGIN
        SELECT MR.VRNORMCRED AS VL_01,
               SUM(NVL(DECODE(NF.IDENTRSAIDOP,'E',DECODE(IP.CDIMPOSTO,'80',IP.VRIMPOSTONF,0),0),0)) AS VL_02,
               0 AS VL_03,
               0 AS VL_04,
               MR.VROUTRCRED AS VL_05,
               MR.VRESTODEBI AS VL_06,
               MR.VRSLDCRANTE AS VL_07,
               MR.VRNORMDEBI AS VL_09,
               MR.VROUTRDEBI AS VL_10,
               MR.VRESTOCRED AS VL_11,
               MR.VRDEDUCAO AS VL_15,
               0 AS VL_17,
               0 AS VL_18,
               SUM(NVL(DECODE(IM.CDIMPOSTONFE,'80',DECODE(SUBSTR(IP.CDFISCOPERNF,1,1),'5',IP.VRIMPOSTONF,0),0),0)) AS VL_19,
               SUM(NVL(DECODE(IM.CDIMPOSTONFE,'80',DECODE(SUBSTR(IP.CDFISCOPERNF,1,1),'3',IP.VRIMPOSTONF,0),0),0)) AS VL_20,
               0 AS VL_21,
               SUM(NVL(DECODE(IM.CDIMPOSTONFE,'80',DECODE(SUBSTR(IP.CDFISCOPERNF,1,1),'6',IP.VRIMPOSTONF,0),0),0)) AS VL_99
          INTO V_E340_VL_01, V_E340_VL_02, V_E340_VL_03, V_E340_VL_04, V_E340_VL_05, V_E340_VL_06,
               V_E340_VL_07, V_E340_VL_09, V_E340_VL_10, V_E340_VL_11, V_E340_VL_15, V_E340_VL_17,
               V_E340_VL_18, V_E340_VL_19, V_E340_VL_20, V_E340_VL_21, V_E340_VL_99
          FROM MAPAAPURAC MP, MAPARECUP MR, IMPOSTONF IP, NOTAFISCAL NF, IMPOSTO IM,
               (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF, I.NRSEQUMAPAPU
                  FROM IMPOSTONF I
                 WHERE I.NRSEQUMAPAPU = V_NRSEQUMAPAPU) IT
         WHERE MP.NRSEQUMAPAPU = IT.NRSEQUMAPAPU
           AND MR.NRSEQUMAPAPU = MP.NRSEQUMAPAPU
           AND IT.CDFILIAL     = IP.CDFILIAL
           AND IT.NRLANCTONF   = IP.NRLANCTONF
           AND IP.CDFILIAL     = NF.CDFILIAL
           AND IP.NRLANCTONF   = NF.NRLANCTONF
           AND IP.CDIMPOSTO    = IM.CDIMPOSTO
         GROUP BY MR.VRNORMCRED, MR.VROUTRCRED, MR.VRESTODEBI, MR.VRSLDCRANTE,
               MR.VRNORMDEBI, MR.VROUTRDEBI, MR.VRESTOCRED, MR.VRDEDUCAO;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
              V_E340_VL_01:=0; V_E340_VL_02:=0; V_E340_VL_03:=0; V_E340_VL_04:=0; V_E340_VL_05:=0; V_E340_VL_06:=0;
              V_E340_VL_07:=0; V_E340_VL_09:=0; V_E340_VL_10:=0; V_E340_VL_11:=0; V_E340_VL_15:=0; V_E340_VL_17:=0;
              V_E340_VL_18:=0; V_E340_VL_19:=0; V_E340_VL_20:=0; V_E340_VL_21:=0; V_E340_VL_99:=0;
      END;

      V_E340_VL_08 := V_E340_VL_01+V_E340_VL_02+V_E340_VL_03+V_E340_VL_04+V_E340_VL_05+V_E340_VL_06+V_E340_VL_07;
      V_E340_VL_12 := V_E340_VL_09+V_E340_VL_10+V_E340_VL_11;
      V_E340_VL_13 := CASE WHEN (V_E340_VL_08-V_E340_VL_12)<0 THEN 0 ELSE (V_E340_VL_08-V_E340_VL_12) END;
      V_E340_VL_14 := CASE WHEN (V_E340_VL_12-V_E340_VL_08)<0 THEN 0 ELSE (V_E340_VL_12-V_E340_VL_08) END;
      V_E340_VL_16 := CASE WHEN (V_E340_VL_14-V_E340_VL_15)<0 THEN 0 ELSE (V_E340_VL_14-V_E340_VL_15) END;
      V_E340_VL_22 := V_E340_VL_16+V_E340_VL_17+V_E340_VL_18+V_E340_VL_19+V_E340_VL_20+V_E340_VL_21;

      V_REG_E340 := '|E340'                                                                           ||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_01,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_02,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_03,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_04,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_05,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_06,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_07,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_08,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_09,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_10,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_11,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_12,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_13,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_14,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_15,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_16,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_17,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_18,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_19,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_20,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_21,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_22,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E340_VL_99,2),0), '999999999990.00')), '.', ',')||'|';

      V_CONTREGE := NVL(V_CONTREGE,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('509'||LPAD(RTRIM(V_CONTREGE), 7, '0'), V_REG_E340, P_NRSEQARQMAG);

      /*REGISTRO E350*/
      FOR C_REGE350 IN (SELECT '|E350' ||'|'||
                                UF_AJ ||'|'||
                                COD_AJ ||'|'||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VL_AJ,2),0), '999999999990.00')), '.', ',') ||'|'||
                                NUM_DA ||'|'||
                                NUM_PROC ||'|'||
                                IND_PROC ||'|'||
                                DESCR_PROC  AS DADOS, COD_AJ, DSITEMMAPR
                           FROM (SELECT 'PE' AS UF_AJ,
                                        MR.CDAJUSTEITSEF AS COD_AJ,
                                        SUM(MR.VRITEMMAPR) AS VL_AJ,
                                        '' AS NUM_DA,
                                        '' AS NUM_PROC,
                                        '' AS IND_PROC,
                                        '' AS DESCR_PROC,
                                        MR.DSITEMMAPR
                                   FROM ITMAPARECUP MR
                                  WHERE MR.NRSEQUMAPAPU = V_NRSEQUMAPAPU
                                    AND MR.IDTIPOITMAPR <> 'DA'
                                  GROUP BY MR.CDAJUSTEITSEF, MR.DSITEMMAPR)
                          WHERE VL_AJ > 0) LOOP

          IF (C_REGE350.COD_AJ IN ('199','299','399','425','499','599')) THEN
             /* OBSERVACOES - LINHA 0450*/
             V_SEQUOBS := NVL(V_SEQUOBS,0) + 1;
             INSERT INTO TMP_REGDADOSSEF2
               (CDREGISTRO, CDCONTREGI, CDCHAVEREG)
             VALUES
               ('0450', V_SEQUOBS, V_SEQUOBS||' - '||C_REGE350.DSITEMMAPR);

             V_REG_E350 := C_REGE350.DADOS ||'|'|| LPAD(V_SEQUOBS,9,'0') ||'|'|| '' ||'|';
          ELSE
             V_REG_E350 := C_REGE350.DADOS ||'|'|| '' ||'|'|| '' ||'|';
          END IF;

          V_CONTREGE := NVL(V_CONTREGE,0) + 1;
          INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
            ('509'||LPAD(RTRIM(V_CONTREGE), 7, '0'), V_REG_E350, P_NRSEQARQMAG);
      END LOOP;

      /*REGISTRO E360*/
      FOR C_REGE360 IN (SELECT '|E360' || '|' ||
                                UF_OR || '|' ||
                                COD_OR || '|' ||
                                TO_CHAR(PER_REF,'MMYYYY') || '|' ||
                                SUBSTR(LPAD(COD_REC,10,'0'),7,4) || '|' ||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VL_ICMS_REC,2),0),'999999999990.00')),'.',',') || '|' ||
                                TO_CHAR(DT_VCTO,'DDMMYYYY') || '|' ||
                                NUM_PROC || '|' ||
                                IND_PROC || '|' ||
                                DESCR_PROC AS DADOS, COD_OR, OBSERV
                           FROM (SELECT 'PE' AS UF_OR,
                                        '800' AS COD_OR,
                                        M.DTINIPERIODO AS PER_REF,
                                        M.CDRECEGUIA AS COD_REC,
                                        M.VRRECEGUIA AS VL_ICMS_REC,
                                        M.DTVENCGUIA AS DT_VCTO,
                                        '' AS NUM_PROC,
                                        '' AS IND_PROC,
                                        '' AS DESCR_PROC,
                                        DSHISTGUIA AS OBSERV
                                   FROM MAPAAPURAC M
                                  WHERE M.NRSEQUMAPAPU = V_NRSEQUMAPAPU
                                    AND M.VRRECEGUIA > 0
                                    AND M.CDRECEGUIA IS NOT NULL
                                  UNION ALL
                                 SELECT 'PE' AS UF_OR,
                                        G.CDOBRIGAGUIA AS COD_OR,
                                        M.DTINIPERIODO AS PER_REF,
                                        G.CDRECEITAGUIA AS COD_REC,
                                        G.VRRECEITAGUIA AS VL_ICMS_REC,
                                        G.DTVENCGUIACOMPL AS DT_VCTO,
                                        G.NRPROCESSOGUIA AS NUM_PROC,
                                        DECODE(G.NRPROCESSOGUIA,'','',G.IDORIGEMGUIA) AS IND_PROC,
                                        DECODE(G.NRPROCESSOGUIA,'','',G.DSHISTGUIACOMPL) AS DESCR_PROC,
                                        NVL(G.DSHISTGUIACOMPL,'GUIA COMPLEMENTAR') AS OBSERV
                                   FROM GUIACOMPLMAPA G, MAPAAPURAC M
                                  WHERE G.NRSEQUMAPAPU = M.NRSEQUMAPAPU
                                    AND M.NRSEQUMAPAPU = V_NRSEQUMAPAPU)) LOOP

          IF (C_REGE360.COD_OR = '879') THEN
             /* OBSERVACOES - LINHA 0450*/
             V_SEQUOBS := NVL(V_SEQUOBS,0) + 1;
             INSERT INTO TMP_REGDADOSSEF2
               (CDREGISTRO, CDCONTREGI, CDCHAVEREG)
             VALUES
               ('0450', V_SEQUOBS, V_SEQUOBS||' - '||C_REGE360.OBSERV);

             V_REG_E360 := C_REGE360.DADOS ||'|'|| LPAD(V_SEQUOBS,9,'0') ||'|';
          ELSE
             V_REG_E360 := C_REGE360.DADOS ||'|'|| '' ||'|';
          END IF;

          V_CONTREGE := NVL(V_CONTREGE,0) + 1;
          INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
            ('509'||LPAD(RTRIM(V_CONTREGE), 7, '0'), V_REG_E360, P_NRSEQARQMAG);
      END LOOP;
   END LOOP;

   /*REGISTRO E500*/
   FOR C_REGE500 IN (SELECT ('|E500'                          || '|' ||
                              TO_CHAR(DTINIPERIODO,'ddmmyyyy') || '|' ||
                              TO_CHAR(DTFIMPERIODO,'ddmmyyyy') || '|'  ) AS ARQ_E500
                         FROM MAPAAPURAC M
                        WHERE M.NRSEQUMAPAPU = V_NRSEQMAPAIPI) LOOP

       V_CONTREGE := NVL(V_CONTREGE,0) + 1;
       INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
       VALUES
         ('515'||LPAD(RTRIM(V_CONTREGE), 7, '0'), C_REGE500.ARQ_E500, P_NRSEQARQMAG);

      /*REGISTRO E520*/
      FOR C_REGE520 IN (SELECT ('|E520' || '|' ||
                                 REPLACE(LTRIM(TO_CHAR(NVL(ROUND(SUM(VL_CONT),2),0), '999999999990.00')), '.', ',') || '|' ||
                                 CFOP || '|' ||
                                 REPLACE(LTRIM(TO_CHAR(NVL(ROUND(SUM(VL_BC_IPI),2),0), '999999999990.00')), '.', ',') || '|' ||
                                 REPLACE(LTRIM(TO_CHAR(NVL(ROUND(SUM(VL_IPI),2),0), '999999999990.00')), '.', ',') || '|' ||
                                 REPLACE(LTRIM(TO_CHAR(NVL(ROUND(SUM(VL_ISNT_IPI),2),0),'999999999990.00')),'.',',') || '|' ||
                                 REPLACE(LTRIM(TO_CHAR(NVL(ROUND(SUM(VL_OUT_IPI),2),0),'999999999990.00')),'.',',') || '|'  ) AS DADOS
                            FROM (SELECT I.VRCONTABILNF AS VL_CONT,
                                         I.CDFISCOPERNF AS CFOP,
                                         DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0) AS VL_BC_IPI,
                                         DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0) AS VL_IPI,
                                         DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0) AS VL_ISNT_IPI,
                                         DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0) AS VL_OUT_IPI
                                    FROM IMPOSTONF I
                                   WHERE ((I.NRSEQUMAPAPU  = V_NRSEQMAPAIPI) OR (V_NRSEQMAPAIPI = I.NRSEQMAPOIMP))
                                     AND NVL(I.IDIMPOSTONF,'1') <> '3')N
                           GROUP BY N.CFOP)LOOP
          V_CONTREGE := NVL(V_CONTREGE,0) + 1;
          INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
            ('515'||LPAD(RTRIM(V_CONTREGE), 7, '0'), C_REGE520.DADOS, P_NRSEQARQMAG);
      END LOOP;

      /*REGISTRO E525*/
      FOR C_REGE525 IN (SELECT '|E525' ||'|'||
                                IND_TOT ||'|'||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VL_CONT,2),0), '999999999990.00')), '.', ',')||'|'||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VL_BC_IPI,2),0), '999999999990.00')), '.', ',')||'|'||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VL_IPI,2),0), '999999999990.00')), '.', ',')||'|'||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VL_ISNT_IPI,2),0), '999999999990.00')), '.', ',')||'|'||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VL_OUT_IPI,2),0), '999999999990.00')), '.', ',')||'|' AS DADOS
                           FROM (SELECT '1' AS IND_TOT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',IP.VRCONTABILNF,0)) AS VL_CONT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0)) AS VL_BC_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0),0)) AS VL_ISNT_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0),0)) AS VL_OUT_IPI
                                   FROM IMPOSTONF IP, IMPOSTO IM
                                  WHERE NRSEQUMAPAPU = V_NRSEQMAPAIPI
                                    AND SUBSTR(IP.CDFISCOPERNF,1,1) = '1'
                                    AND IM.CDIMPOSTONFE = IP.CDIMPOSTO
                                 UNION ALL
                                 SELECT '2' AS IND_TOT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',IP.VRCONTABILNF,0)) AS VL_CONT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0)) AS VL_BC_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0),0)) AS VL_ISNT_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0),0)) AS VL_OUT_IPI
                                   FROM IMPOSTONF IP, IMPOSTO IM
                                  WHERE NRSEQUMAPAPU = V_NRSEQMAPAIPI
                                    AND SUBSTR(IP.CDFISCOPERNF,1,1) = '2'
                                    AND IM.CDIMPOSTONFE = IP.CDIMPOSTO
                                 UNION ALL
                                 SELECT '3' AS IND_TOT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',IP.VRCONTABILNF,0)) AS VL_CONT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0)) AS VL_BC_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0),0)) AS VL_ISNT_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0),0)) AS VL_OUT_IPI
                                   FROM IMPOSTONF IP, IMPOSTO IM
                                  WHERE NRSEQUMAPAPU = V_NRSEQMAPAIPI
                                    AND SUBSTR(IP.CDFISCOPERNF,1,1) = '3'
                                    AND IM.CDIMPOSTONFE = IP.CDIMPOSTO
                                 UNION ALL
                                 SELECT '4' AS IND_TOT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',IP.VRCONTABILNF,0)) AS VL_CONT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0)) AS VL_BC_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0),0)) AS VL_ISNT_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0),0)) AS VL_OUT_IPI
                                   FROM IMPOSTONF IP, IMPOSTO IM
                                  WHERE NRSEQUMAPAPU = V_NRSEQMAPAIPI
                                    AND SUBSTR(IP.CDFISCOPERNF,1,1) IN ('1','2','3')
                                    AND IM.CDIMPOSTONFE = IP.CDIMPOSTO
                                 UNION ALL
                                 SELECT '5' AS IND_TOT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',IP.VRCONTABILNF,0)) AS VL_CONT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0)) AS VL_BC_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0),0)) AS VL_ISNT_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0),0)) AS VL_OUT_IPI
                                   FROM IMPOSTONF IP, IMPOSTO IM
                                  WHERE NRSEQUMAPAPU = V_NRSEQMAPAIPI
                                    AND SUBSTR(IP.CDFISCOPERNF,1,1) = '5'
                                    AND IM.CDIMPOSTONFE = IP.CDIMPOSTO
                                 UNION ALL
                                 SELECT '6' AS IND_TOT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',IP.VRCONTABILNF,0)) AS VL_CONT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0)) AS VL_BC_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0),0)) AS VL_ISNT_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0),0)) AS VL_OUT_IPI
                                   FROM IMPOSTONF IP, IMPOSTO IM
                                  WHERE NRSEQUMAPAPU = V_NRSEQMAPAIPI
                                    AND SUBSTR(IP.CDFISCOPERNF,1,1) = '6'
                                    AND IM.CDIMPOSTONFE = IP.CDIMPOSTO
                                 UNION ALL
                                 SELECT '7' AS IND_TOT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',IP.VRCONTABILNF,0)) AS VL_CONT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0)) AS VL_BC_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0),0)) AS VL_ISNT_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0),0)) AS VL_OUT_IPI
                                   FROM IMPOSTONF IP, IMPOSTO IM
                                  WHERE NRSEQUMAPAPU = V_NRSEQMAPAIPI
                                    AND SUBSTR(IP.CDFISCOPERNF,1,1) = '7'
                                    AND IM.CDIMPOSTONFE = IP.CDIMPOSTO
                                 UNION ALL
                                 SELECT '8' AS IND_TOT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',IP.VRCONTABILNF,0)) AS VL_CONT,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0)) AS VL_BC_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),0)) AS VL_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0),0)) AS VL_ISNT_IPI,
                                        SUM(DECODE(IM.CDIMPOSTONFE,'10',DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0),0)) AS VL_OUT_IPI
                                   FROM IMPOSTONF IP, IMPOSTO IM
                                  WHERE NRSEQUMAPAPU = V_NRSEQMAPAIPI
                                    AND SUBSTR(IP.CDFISCOPERNF,1,1) IN ('5','6','7')
                                    AND IM.CDIMPOSTONFE = IP.CDIMPOSTO)
                          ORDER BY IND_TOT)LOOP

          V_CONTREGE := NVL(V_CONTREGE,0) + 1;
          INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
            ('515'||LPAD(RTRIM(V_CONTREGE), 7, '0'), C_REGE525.DADOS, P_NRSEQARQMAG);
      END LOOP;

      /*REGISTRO E540*/
      BEGIN
        SELECT SUM(NVL(CASE WHEN SUBSTR(IP.CDFISCOPERNF,1,1) IN ('1','2') THEN IP.VRIMPOSTONF ELSE 0 END,0)) AS VL_01,
               SUM(NVL(CASE WHEN SUBSTR(IP.CDFISCOPERNF,1,1) IN ('3') THEN IP.VRIMPOSTONF ELSE 0 END,0)) AS VL_02,
               SUM(NVL(CASE WHEN SUBSTR(IP.CDFISCOPERNF,1,1) IN ('7') THEN IP.VRIMPOSTONF ELSE 0 END,0)) AS VL_03,
               MR.VRESTODEBI AS VL_04,
               MR.VROUTRCRED AS VL_05,
               MR.VRSLDCRANTE AS VL_07,
               SUM(NVL(CASE WHEN SUBSTR(IP.CDFISCOPERNF,1,1) IN ('5','6') THEN IP.VRIMPOSTONF ELSE 0 END,0)) AS VL_09,
               MR.VRESTOCRED AS VL_10,
               0 AS VL_11,
               MR.VROUTRDEBI AS VL_12,
               MR.VRTOTADEBI AS VL_13,
               MR.VRTOTACRED AS VL_08
          INTO V_E540_VL_01, V_E540_VL_02, V_E540_VL_03, V_E540_VL_04, V_E540_VL_05, V_E540_VL_07,
               V_E540_VL_09, V_E540_VL_10, V_E540_VL_11, V_E540_VL_12, V_E540_VL_13, V_E540_VL_08
          FROM MAPARECUP MR, IMPOSTONF IP, NOTAFISCAL NF
         WHERE MR.NRSEQUMAPAPU = V_NRSEQMAPAIPI
           AND IP.NRSEQUMAPAPU = MR.NRSEQUMAPAPU
           AND NF.CDFILIAL     = IP.CDFILIAL
           AND NF.NRLANCTONF   = IP.NRLANCTONF
         GROUP BY MR.VRESTODEBI, MR.VROUTRCRED, MR.VRSLDCRANTE, MR.VRESTOCRED,
               MR.VROUTRDEBI, MR.VRTOTADEBI, MR.VRTOTACRED;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_E540_VL_01:=0; V_E540_VL_02:=0; V_E540_VL_03:=0; V_E540_VL_04:=0; V_E540_VL_05:=0; V_E540_VL_07:=0;
            V_E540_VL_09:=0; V_E540_VL_10:=0; V_E540_VL_11:=0; V_E540_VL_12:=0; V_E540_VL_13:=0; V_E540_VL_08:=0;
      END;

      V_E540_VL_06 := V_E540_VL_01+V_E540_VL_02+V_E540_VL_03+V_E540_VL_04+V_E540_VL_05;
      V_E540_VL_13 := V_E540_VL_13-(V_E540_VL_09+V_E540_VL_10+V_E540_VL_11+V_E540_VL_12);
      V_E540_VL_08 := V_E540_VL_08-(V_E540_VL_06+V_E540_VL_07);
      V_E540_VL_16 := CASE WHEN (V_E540_VL_13-V_E540_VL_08)<0 THEN 0 ELSE (V_E540_VL_13-V_E540_VL_08) END;
      V_E540_VL_17 := CASE WHEN (V_E540_VL_08-V_E540_VL_13)<0 THEN 0 ELSE (V_E540_VL_08-V_E540_VL_13) END;

      V_REG_E540 := '|E540'                                                                           ||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E540_VL_01,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E540_VL_02,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E540_VL_03,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E540_VL_04,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E540_VL_05,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E540_VL_06,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E540_VL_07,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E540_VL_09,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E540_VL_10,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E540_VL_11,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E540_VL_12,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E540_VL_13,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E540_VL_08,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E540_VL_16,2),0), '999999999990.00')), '.', ',')||'|'||
                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_E540_VL_17,2),0), '999999999990.00')), '.', ',')||'|';

      V_CONTREGE := NVL(V_CONTREGE,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('515'||LPAD(RTRIM(V_CONTREGE), 7, '0'), V_REG_E540, P_NRSEQARQMAG);

      /*REGISTRO E550*/
      FOR C_REGE550 IN (SELECT '|E550' ||'|'||
                                COD_AJ ||'|'||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VL_AJ,2),0), '999999999990.00')), '.', ',') ||'|'||
                                IND_DOC ||'|'||
                                NUM_DOC ||'|'||
                                DESCR_AJ ||'|'||
                                COD_INF_OBS ||'|' AS DADOS
                           FROM (SELECT MR.CDAJUSTEITMAPA AS COD_AJ,
                                        MR.VRITEMMAPR AS VL_AJ,
                                        '' AS IND_DOC,
                                        NF.NRNOTAFISC AS NUM_DOC,
                                        MR.DSITEMMAPR AS DESCR_AJ,
                                        '' AS COD_INF_OBS
                                   FROM ITMAPARECUP MR, NOTAFISCAL NF
                                  WHERE MR.NRSEQUMAPAPU = V_NRSEQMAPAIPI
                                    AND NF.CDFILIAL(+) = MR.CDFILIAL
                                    AND NF.NRLANCTONF(+) = MR.NRLANCTONF)) LOOP

          V_CONTREGE := NVL(V_CONTREGE,0) + 1;
          INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
            ('515'||LPAD(RTRIM(V_CONTREGE), 7, '0'), C_REGE550.DADOS, P_NRSEQARQMAG);
      END LOOP;

      /*REGISTRO E560*/
      FOR C_REGE560 IN (SELECT '|E560' || '|' ||
                                COD_OR_IPI || '|' ||
                                TO_CHAR(PER_REF,'MMYYYY') || '|' ||
                                COD_REC_IPI || '|' ||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VL_IPI_REC,2),0),'999999999990.00')),'.',',') || '|' ||
                                TO_CHAR(DT_VCTO,'DDMMYYYY') || '|' ||
                                IND_DOC || '|' ||
                                NUM_DOC || '|' ||
                                DESCR_AJ || '|' ||
                                COD_INF_OBS || '|' AS DADOS
                           FROM (SELECT '' AS COD_OR_IPI,
                                        M.DTINIPERIODO AS PER_REF,
                                        M.CDRECEGUIA AS COD_REC_IPI,
                                        M.VRRECEGUIA AS VL_IPI_REC,
                                        M.DTVENCGUIA AS DT_VCTO,
                                        '' AS IND_DOC,
                                        '' AS NUM_DOC,
                                        '' AS DESCR_AJ,
                                        '' AS COD_INF_OBS
                                   FROM MAPAAPURAC M
                                  WHERE M.NRSEQUMAPAPU = V_NRSEQMAPAIPI
                                    AND M.VRRECEGUIA > 0
                                    AND M.CDRECEGUIA IS NOT NULL
                                  UNION ALL
                                 SELECT G.CDOBRIGAGUIA AS COD_OR_IPI,
                                        M.DTINIPERIODO AS PER_REF,
                                        G.CDRECEITAGUIA AS COD_REC_IPI,
                                        G.VRRECEITAGUIA AS VL_IPI_REC,
                                        G.DTVENCGUIACOMPL AS DT_VCTO,
                                        DECODE(G.NRPROCESSOGUIA,'','',G.IDORIGEMGUIA) AS IND_DOC,
                                        G.NRPROCESSOGUIA AS NUM_DOC,
                                        DECODE(G.NRPROCESSOGUIA,'','',G.DSHISTGUIACOMPL) AS DESCR_AJ,
                                        '' AS COD_INF_OBS
                                   FROM GUIACOMPLMAPA G, MAPAAPURAC M
                                  WHERE G.NRSEQUMAPAPU = M.NRSEQUMAPAPU
                                    AND M.NRSEQUMAPAPU = V_NRSEQMAPAIPI)) LOOP

          V_CONTREGE := NVL(V_CONTREGE,0) + 1;
          INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
            ('515'||LPAD(RTRIM(V_CONTREGE), 7, '0'), C_REGE560.DADOS, P_NRSEQARQMAG);
      END LOOP;
   END LOOP;

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

   /* ABERTURA DO BLOCO E */
   V_CONTREGE := NVL(V_CONTREGE,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('500'||LPAD(RTRIM(V_CONTREGE), 7, '0'), '|E001'|| '|' ||V_MOVIMENT|| '|', P_NRSEQARQMAG);

   /* ENCERRAMENTO DO BLOCO E */
   V_CONTREGE := NVL(V_CONTREGE,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('599'||LPAD(RTRIM(V_CONTREGE), 7, '0'), '|E990'|| '|' ||V_CONTREGE|| '|', P_NRSEQARQMAG);
   RETURN(V_CONTREGE);
END;
/