CREATE GLOBAL TEMPORARY TABLE TMP_REGDADOSSPED
(
   CDREGISTRO      VARCHAR2(04)   NOT NULL,
   CDCONTREGI      NUMBER   NOT NULL,
   CDCHAVEREG      VARCHAR2(50)   NOT NULL,
   CDCAMPO1        VARCHAR2(50)   NULL,
   CDCAMPO2        VARCHAR2(50)   NULL,
   CDCAMPO3        VARCHAR2(50)   NULL,
   CDCAMPO4        VARCHAR2(50)   NULL,
   CDCAMPO5        VARCHAR2(50)   NULL,
 CONSTRAINT PK_TMP_REGPRODSPED PRIMARY KEY(CDREGISTRO, CDCONTREGI)) ON COMMIT PRESERVE ROWS
/


CREATE OR REPLACE FUNCTION SPED_INICIO_FOS(
   P_CDIMPOSTO    IN IMPOSTO.CDIMPOSTO%TYPE,
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCESTA   IN EMPRESA.CDINSCESTA%TYPE,
   P_DTINICIAL    IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_LIVROMAG     IN LIVROMAGNETICO.CDLIVROMAG%TYPE,
   P_TIPO         IN NOTAFISCAL.IDSTATUS%TYPE,
   P_FINALIDADE   IN NOTAFISCAL.CDTIPOOPER%TYPE,
   P_INDESCRITU   IN NOTAFISCAL.IDSTATUS%TYPE,
   P_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
   P_PERFIL       IN NOTAFISCAL.IDSTATUS%TYPE,
   P_ATIVIDADE    IN NOTAFISCAL.IDSTATUS%TYPE,
   P_VERSAO       IN VARCHAR2) RETURN NUMBER
AS
   V_CONTREGA        NUMBER;
   V_CODIGO          VARCHAR2(21);
   V_ORIGEM          VARCHAR2(01);
   V_NRSEQUMAPAPU    VARCHAR2(10);
BEGIN
   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;

   FOR C_REG0000 IN (SELECT  ('|0000'                                                       || '|' ||
                              P_VERSAO                                                      || '|' ||
                              '0'                                                           || '|' ||
                              TO_CHAR(P_DTINICIAL,'ddmmyyyy')                               || '|' ||
                              TO_CHAR(P_DTFINAL,'ddmmyyyy')                                 || '|' ||
                              E.NMEMPRESA                                                   || '|' ||
                              LPAD(LTRIM(I.NRINSJURIEST,'0'), 14, '0')                      || '|' ||
                              ''                                                            || '|' ||
                              I.SGESTADO                                                    || '|' ||
                              REPLACE(REPLACE(REPLACE(I.CDINSCESTA,'.',''),'-',''),'/','')  || '|' ||
                              LPAD(NVL(M.CDMUNICIBGE,'0'),7,'0')                            || '|' ||
                              E.CDINSCMUNI                                                  || '|' ||
                              ''                                                            || '|' ||
                              P_PERFIL                                                      || '|' ||
                              P_ATIVIDADE                                                   || '|'  ) AS ARQ_0000
                     FROM EMPRESA E, MUNICIPIO M, INSCRESTAD I
                     WHERE I.CDEMPRESA   = P_CDEMPRESA   AND
                           I.CDINSCESTA  = P_CDINSCESTA  AND
                           I.CDPAIS      = M.CDPAIS      AND
                           I.SGESTADO    = M.SGESTADO    AND
                           I.CDMUNICIPIO = M.CDMUNICIPIO AND
                           E.CDEMPRESA   = I.CDEMPRESA ) LOOP
      V_CONTREGA := NVL(V_CONTREGA,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('00'||LPAD(RTRIM(V_CONTREGA), 8, '0'), C_REG0000.ARQ_0000, P_NRSEQARQMAG);
   END LOOP;
   /* ABERTURA DO ARQUIVO 0000 */
   V_CONTREGA := NVL(V_CONTREGA,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('01'||LPAD(RTRIM(V_CONTREGA), 8, '0'), '|0001'||'|'||'0'|| '|', P_NRSEQARQMAG);
   FOR C_REG0005 IN (SELECT ('|0005'                       || '|' ||
                             EP.NMRAZSOCEMPR               || '|' ||
                             REPLACE(REPLACE(IE.NRCEPIEST,'.',''),'-','')  || '|' ||
                             IE.DSENDEIEST                 || '|' ||
                             ''                            || '|' ||
                             IE.DSCOMPENDIES               || '|' ||
                             IE.NMBAIRIEST                 || '|' ||
                             SUBSTR(RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(IE.NRTELEIEST, '(',''),')','') ,'-',''))),1,10)  || '|' ||
                             ''                            || '|' ||
                             ''                            || '|'  ) ARQ_0005
                     FROM EMPRESA EP, INSCRESTAD IE
                     WHERE IE.CDEMPRESA  = P_CDEMPRESA    AND
                           IE.CDINSCESTA = P_CDINSCESTA   AND
                           IE.CDEMPRESA  = EP.CDEMPRESA ) LOOP
      V_CONTREGA := NVL(V_CONTREGA,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('02'||LPAD(RTRIM(V_CONTREGA), 8, '0'),  C_REG0005.ARQ_0005, P_NRSEQARQMAG);
   END LOOP;
   FOR C_REG0100 IN (SELECT ('|0100'                                   || '|' ||
                             NMCONTADOR                                || '|' ||
                             NRCPFRESPONS                              || '|' ||
                             REPLACE(REPLACE(NRCRCCONT2,'/',''),'-','')|| '|' ||
                             ''                                        || '|' ||
                             REPLACE(REPLACE(NRCEP,'/',''),'-','')     || '|' ||
                             LTRIM(RTRIM(DSLOGRADOURO))                || '|' ||
                             ''                                        || '|' ||
                             LTRIM(RTRIM(DSCOMPLLOGRA))                || '|' ||
                             LTRIM(RTRIM(DSBAIRRO))                    || '|' ||
                             SUBSTR(RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(NRTELCONTATO, '(',''),')','') ,'-',''))),1,10)  || '|' ||
                             SUBSTR(RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(NRFAXCONTATO, '(',''),')','') ,'-',''))),1,10)  || '|' ||
                             ''                                        || '|' ||
                             M.CDMUNICIBGE                             || '|'  ) ARQ_0100
                     FROM CONTADOR C, MUNICIPIO M, EMPRESA E
                     WHERE C.CDPAIS = M.CDPAIS
                       AND C.SGESTADO = M.SGESTADO
                       AND C.CDCONTADOR2 = E.CDCONTADOR2
                       AND E.CDEMPRESA = P_CDEMPRESA
                       AND C.CDMUNICIPIO = M.CDMUNICIPIO) LOOP
      V_CONTREGA := NVL(V_CONTREGA,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('04'||LPAD(RTRIM(V_CONTREGA), 8, '0'), C_REG0100.ARQ_0100, P_NRSEQARQMAG);
   END LOOP;
   FOR C_REG0150 IN (SELECT DISTINCT ('|0150'                               || '|' ||
                                      CODIGO                                || '|' ||
                                      LTRIM(RTRIM(NMRAZAO))                 || '|' ||
                                      LPAD(CDPAIS,5,'0')                    || '|' ||
                                      CNPJ1                                 || '|' ||
                                      CPF                                   || '|' ||
                                      INSCESTA                              || '|' ||
                                      MUNICIPIO                             || '|' ||
                                      SUFRAMA                               || '|' ||
                                      LTRIM(RTRIM(ENDERECO))                || '|' ||
                                      NUMERO                                || '|' ||
                                      LTRIM(RTRIM(SUBSTR(COMPLEMENT,1,60))) || '|' ||
                                      LTRIM(RTRIM(BAIRRO))                  || '|'  ) ARQ_0150,
                                      CNPJ
                     FROM(SELECT DISTINCT FI.CDFILIAL                        AS CODIGO,
                                          FI.NMFILIAL                        AS NMRAZAO,
                                          PA.CDPAISBACEN                     AS CDPAIS,
                                          REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.',''),'/',''),'-','') AS CNPJ1,
                                          ''                                 AS CPF,
                                          ''                                 AS CEI,
                                          ''                                 AS NIT,
                                          EF.SGESTADO                        AS ESTADO,
                                          DECODE(FI.CDINSCESTA, 'ISENTO', '', REPLACE(REPLACE(REPLACE(FI.CDINSCESTA,'.',''),'/',''),'-','')) AS INSCESTA,
                                          ''                                 AS INSCESTST,
                                          LPAD(NVL(M.CDMUNICIBGE,'0'),7,'0') AS MUNICIPIO,
                                          ''                                 AS INSCMUNI,
                                          ''                                 AS SUFRAMA,
                                          FI.CDFILIAL                        AS CNPJ,
                                          EF.DSENDEFILI                      AS ENDERECO,
                                          ''                                 AS NUMERO,
                                          EF.DSCOMPENDFIL                    AS COMPLEMENT,
                                          EF.NMBAIRFILI                      AS BAIRRO
                          FROM FILIAL FI, ENDEFILI EF, MUNICIPIO M, PAIS PA,
                               (SELECT DISTINCT DECODE (N.IDENTRSAIDOP, 'E', N.CDFILIEMIT, N.CDFILIRECE) AS CDFILIAL,
                                                N.IDORIGDESTNF
                                FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I
                                WHERE M.CDEMPRESA       = P_CDEMPRESA
                                  AND M.CDINSCESTA      = P_CDINSCESTA
                                  AND M.DTINIPERIODO    = P_DTINICIAL
                                  AND M.DTFIMPERIODO    = P_DTFINAL
                                  AND M.CDIMPOSTO       = P_CDIMPOSTO
                                  AND N.IDORIGDESTNF    =  'L'
                                  AND I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU
                                  AND I.CDIMPOSTO       = P_CDIMPOSTO
                                  AND N.CDMODDOCFISC IN ('01','04','06','21','22','28','55',  '07', '08', '09', '10', '11', '26', '27')
                                  AND N.CDFILIAL        = I.CDFILIAL
                                  AND N.NRLANCTONF      = I.NRLANCTONF
                                  AND ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E') OR (N.IDSTATUS = 'E')))NF
                          WHERE FI.CDFILIAL       = NF.CDFILIAL
                            AND EF.CDFILIAL(+)    = FI.CDFILIAL
                            AND NVL(EF.IDTPENDEFILI,'P') = 'P'
                            AND EF.CDPAIS      = M.CDPAIS
                            AND EF.SGESTADO    = M.SGESTADO
                            AND EF.CDMUNICIPIO = M.CDMUNICIPIO
                            AND EF.CDPAIS      = PA.CDPAIS
                          UNION
                          /* issqn */
                          SELECT DISTINCT CL.CDCLIENTE                                  AS CODIGO,
                                          CL.NMRAZSOCCLIE                               AS NMRAZAO,
                                          PA.CDPAISBACEN                                AS CDPAIS,
                                          REPLACE(REPLACE(REPLACE(DECODE(CL.IDTPIJURCLIE, 'J', CL.NRINSJURCLIE),'.',''),'/',''),'-','') AS CNPJ1,
                                          REPLACE(REPLACE(REPLACE(DECODE(CL.IDTPIJURCLIE, 'F', CL.NRINSJURCLIE),'.',''),'/',''),'-','') AS CPF,
                                          ''                                            AS CEI,
                                          ''                                            AS NIT,
                                          EC.SGESTADO                                   AS ESTADO,
                                          DECODE(CL.NRINSESTCLIE, 'ISENTO', '', REPLACE(REPLACE(REPLACE(CL.NRINSESTCLIE,'.',''),'/',''),'-','')) AS INSCESTA,
                                          ''                                            AS INSCESTST,
                                          LPAD(NVL(M.CDMUNICIBGE,'0'),7,'0')            AS MUNICIPIO,
                                          ''                                            AS INSCMUNI,
                                          ''                                            AS SUFRAMA,
                                          CL.CDCLIENTE                                  AS CNPJ,
                                          EC.DSENDECLIE                                 AS ENDERECO,
                                          ''                                            AS NUMERO,
                                          EC.DSCOMPENDCLI                               AS COMPLEMENT,
                                          EC.NMBAIRCLIE                                 AS BAIRRO
                          FROM CLIENTE CL, ENDECLIE EC, MUNICIPIO M, PAIS PA,
                               (SELECT DISTINCT N.CDCLIENTE, N.IDORIGDESTNF
                                FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I
                                WHERE M.CDEMPRESA       = P_CDEMPRESA
                                  AND M.CDINSCESTA      = P_CDINSCESTA
                                  AND M.DTINIPERIODO    = P_DTINICIAL
                                  AND M.DTFIMPERIODO    = P_DTFINAL
                                  AND M.CDIMPOSTO       = P_CDIMPOSTO
                                  AND I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU
                                  AND I.CDIMPOSTO       = P_CDIMPOSTO
                                  AND N.CDFILIAL        = I.CDFILIAL
                                  AND N.IDORIGDESTNF    =  'C'
                                  AND N.CDMODDOCFISC IN ('01','04','06','21','22','28','55',  '07', '08', '09', '10', '11', '26', '27')
                                  AND N.NRLANCTONF      = I.NRLANCTONF
                                  AND ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E') OR (N.IDSTATUS = 'E')))NF
                          WHERE CL.CDCLIENTE       = NF.CDCLIENTE
                            AND EC.CDCLIENTE(+)    = CL.CDCLIENTE
                            AND EC.IDTPENDECLIE(+) = NVL(CL.IDENDEMISCLI,'P')
                            AND EC.CDPAIS          = M.CDPAIS
                            AND EC.SGESTADO        = M.SGESTADO
                            AND EC.CDMUNICIPIO     = M.CDMUNICIPIO
                            AND EC.CDPAIS          = PA.CDPAIS
                          UNION
                          SELECT DISTINCT FO.CDFORNECED                                    AS CODIGO,
                                          FO.NMRAZSOCFORN                                  AS NMRAZAO,
                                          PA.CDPAISBACEN                                   AS CDPAIS,
                                          REPLACE(REPLACE(REPLACE(DECODE(FO.IDTPIJURFORN, 'J', FO.NRINSJURFORN),'.',''),'/',''),'-','') AS CNPJ1,
                                          REPLACE(REPLACE(REPLACE(DECODE(FO.IDTPIJURFORN, 'F', FO.NRINSJURFORN),'.',''),'/',''),'-','') AS CPF,
                                          ''                                               AS CEI,
                                          ''                                               AS NIT,
                                          EF.SGESTADO                                      AS ESTADO,
                                          DECODE(FO.NRINSESTFORN, 'ISENTO', '', REPLACE(REPLACE(REPLACE(FO.NRINSESTFORN,'.',''),'/',''),'-','')) AS INSCESTA,
                                          ''                                               AS INSCESTST,
                                          LPAD(NVL(M.CDMUNICIBGE,'0'),7,'0')               AS MUNICIPIO ,
                                          REPLACE(REPLACE(FO.NRINSMUNFORN,'-',''),'/','')  AS INSCMUNI,
                                          ''                                               AS SUFRAMA,
                                          FO.CDFORNECED                                    AS CNPJ,
                                          EF.DSENDEFORN                                    AS ENDERECO,
                                          ''                                               AS NUMERO,
                                          EF.DSCOMPENDFOR                                  AS COMPLEMENT,
                                          EF.NMBAIRFORN                                    AS BAIRRO
                          FROM FORNECEDOR FO, ENDEFORN EF, MUNICIPIO M, PAIS PA,
                               (SELECT DISTINCT N.CDFORNECED, N.IDORIGDESTNF
                                FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I
                                WHERE M.CDEMPRESA       = P_CDEMPRESA
                                  AND M.CDINSCESTA      = P_CDINSCESTA
                                  AND M.DTINIPERIODO    = P_DTINICIAL
                                  AND M.DTFIMPERIODO    = P_DTFINAL
                                  AND M.CDIMPOSTO       = P_CDIMPOSTO
                                  AND I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU
                                  AND I.CDIMPOSTO       = P_CDIMPOSTO
                                  AND N.CDMODDOCFISC IN ('01','04','06','21','22','28','55',  '07', '08', '09', '10', '11', '26', '27')
                                  AND N.CDFILIAL        = I.CDFILIAL
                                  AND N.NRLANCTONF      = I.NRLANCTONF
                                  AND N.IDORIGDESTNF    =  'F'
                                  AND ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E') OR (N.IDSTATUS = 'E')))NF
                          WHERE FO.CDFORNECED      = NF.CDFORNECED
                            AND EF.CDFORNECED(+)   = FO.CDFORNECED
                            AND EF.IDTPENDEFORN(+) = NVL(FO.IDENDEMISFOR,'P')
                            AND EF.CDPAIS          = M.CDPAIS
                            AND EF.SGESTADO        = M.SGESTADO
                            AND EF.CDMUNICIPIO     = M.CDMUNICIPIO
                            AND EF.CDPAIS          = PA.CDPAIS)
                     ORDER BY CNPJ) LOOP
      V_CONTREGA := NVL(V_CONTREGA,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('06'||LPAD(RTRIM(V_CONTREGA), 8, '0'), C_REG0150.ARQ_0150, P_NRSEQARQMAG);
   END LOOP;
   /* unidade de medida
   FOR C_REG0190 IN (SELECT DISTINCT ('|0190'      || '|' ||
                                      PR.SGUNIDADE || '|' ||
                                      PR.DSUNIDADE || '|' ) AS ARQ_0190,
                                      PR.SGUNIDADE
                     FROM (SELECT PR.SGUNIDADE,
                                  UN.DSUNIDADE
                           FROM NOTAFISCAL N, ITEMPRODUTO I, PRODUTO PR,
                                IMPOSTONF IM, MAPAAPURAC M, UNIDMEDIDA UN
                           WHERE M.CDEMPRESA       = P_CDEMPRESA
                             AND M.CDINSCESTA      = P_CDINSCESTA
                             AND M.DTINIPERIODO    = P_DTINICIAL
                             AND M.DTFIMPERIODO    = P_DTFINAL
                             AND M.CDIMPOSTO       = P_CDIMPOSTO
                             AND IM.CDIMPOSTO      = P_CDIMPOSTO
                             AND N.CDMODDOCFISC    IN ('01','04','06','21','22','28','55',  '07', '08', '09', '10', '11', '26', '27')
                             AND N.CDFILIAL        = I.CDFILIAL
                             AND N.NRLANCTONF      = I.NRLANCTONF
                             AND N.CDFILIAL        = IM.CDFILIAL
                             AND N.NRLANCTONF      = IM.NRLANCTONF
                             AND I.CDPRODUTO       = PR.CDPRODUTO
                             AND M.NRSEQUMAPAPU    = IM.NRSEQUMAPAPU
                             AND PR.SGUNIDADE      = UN.SGUNIDADE
                             AND EXISTS
                                 (SELECT IP.CDIMPOSTO
                                  FROM ITEMPROIMPOS IP
                                  WHERE IP.CDFILIAL    = I.CDFILIAL
                                    AND IP.NRLANCTONF  = I.NRLANCTONF
                                    AND IP.NRSEQITPROD = I.NRSEQITPROD
                                    AND IP.CDIMPOSTO   = IM.CDIMPOSTO)
                           UNION
                           SELECT PR.SGUNIDADE,
                                  UN.DSUNIDADE
                           FROM INVENTFISC I, PRODUTO PR, UNIDMEDIDA UN
                           WHERE I.CDEMPRESA    = P_CDEMPRESA
                             AND I.CDINSCESTA   = P_CDINSCESTA
                             AND I.DTINVEFISCAL = P_DTFINAL
                             AND I.QTESTINVFISC <> 0
                             AND I.CDPRODUTO    = PR.CDPRODUTO
                             AND PR.SGUNIDADE   = UN.SGUNIDADE) PR
                     ORDER BY PR.SGUNIDADE ASC) LOOP
      V_CONTREGA := NVL(V_CONTREGA,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('08'||LPAD(RTRIM(V_CONTREGA), 8, '0'), C_REG0190.ARQ_0190, P_NRSEQARQMAG);
   END LOOP;

   FOR C_REG0200 IN (SELECT DISTINCT ('|0200'         || '|' ||
                                      PR.CDARVPROD    || '|' ||
                                      PR.NMPRODUTO    || '|' ||
                                      ''              || '|' ||
                                      ''              || '|' || /* COD_ANT_ITEM
                                      PR.SGUNIDADE    || '|' ||
                                      PR.CDTPITEMPROD || '|' || /* TIPO ITEM
                                      ''              || '|' ||
                                      ''              || '|' ||
                                      PR.CDGENPROD    || '|' ||
                                      ''              || '|' ||
                                      ''              || '|'   ) AS ARQ_0200,
                                      PR.CDARVPROD, PR.SGUNIDADE
                     FROM (/* produtos que exibem entrada e saida
                           SELECT PR.CDARVPROD AS CDARVPROD,
                                  PR.NMPRODUTO AS NMPRODUTO,
                                  R.CDGENPROD AS CDGENPROD,
                                  PR.SGUNIDADE, PR.CDPRODNCM,
                                  R.CDTPITEMPROD
                             FROM NOTAFISCAL N, ITEMPRODUTO I, PRODUTO PR,
                                  IMPOSTONF IM, RELACPRODITGEN R
                            WHERE IM.NRSEQUMAPAPU = V_NRSEQUMAPAPU
                              AND IM.CDIMPOSTO    = P_CDIMPOSTO
                              AND R.CDLIVROMAG    = P_LIVROMAG
                              AND N.CDMODDOCFISC  IN ('01','02','04','55')
                              AND N.CDFILIAL      = I.CDFILIAL
                              AND N.NRLANCTONF    = I.NRLANCTONF
                              AND N.CDFILIAL      = IM.CDFILIAL
                              AND N.NRLANCTONF    = IM.NRLANCTONF
                              AND I.CDPRODUTO     = PR.CDPRODUTO
                              AND R.CDPRODUTO     = PR.CDPRODUTO
                              AND EXISTS
                                  (SELECT IP.CDIMPOSTO
                                     FROM ITEMPROIMPOS IP
                                    WHERE IP.CDFILIAL    = I.CDFILIAL
                                      AND IP.NRLANCTONF  = I.NRLANCTONF
                                      AND IP.NRSEQITPROD = I.NRSEQITPROD
                                      AND IP.CDIMPOSTO   = IM.CDIMPOSTO)
                           UNION
                           /* produtos do inventario fiscal
                           SELECT PR.CDARVPROD AS CDARVPROD,
                                  PR.NMPRODUTO AS NMPRODUTO,
                                  R.CDGENPROD AS CDGENPROD,
                                  PR.SGUNIDADE, PR.CDPRODNCM,
                                  R.CDTPITEMPROD
                             FROM INVENTFISC I, PRODUTO PR, RELACPRODITGEN R
                            WHERE I.CDEMPRESA    = P_CDEMPRESA
                              AND I.CDINSCESTA   = P_CDINSCESTA
                              AND I.DTINVEFISCAL = P_DTFINAL
                              AND R.CDLIVROMAG   = P_LIVROMAG
                              AND I.CDPRODUTO    = PR.CDPRODUTO
                              AND I.QTESTINVFISC <> 0
                              AND R.CDPRODUTO    = PR.CDPRODUTO
                           UNION
                           /* produtos que apenas sao exibidoa para a saida
                           SELECT PR.CDARVPROD AS CDARVPROD,
                                  PR.NMPRODUTO AS NMPRODUTO,
                                  R.CDGENPROD AS CDGENPROD,
                                  PR.SGUNIDADE, PR.CDPRODNCM,
                                  R.CDTPITEMPROD
                             FROM NOTAFISCAL N, ITEMPRODUTO I, PRODUTO PR,
                                  IMPOSTONF IM, RELACPRODITGEN R
                            WHERE IM.NRSEQUMAPAPU = V_NRSEQUMAPAPU
                              AND IM.CDIMPOSTO    = P_CDIMPOSTO
                              AND R.CDLIVROMAG    = P_LIVROMAG
                              AND N.CDMODDOCFISC IN ('06','28',   '07',    '21','22')
                              AND N.IDENTRSAIDOP  = 'S'
                              AND N.CDFILIAL      = I.CDFILIAL
                              AND N.NRLANCTONF    = I.NRLANCTONF
                              AND N.CDFILIAL      = IM.CDFILIAL
                              AND N.NRLANCTONF    = IM.NRLANCTONF
                              AND I.CDPRODUTO     = PR.CDPRODUTO
                              AND R.CDPRODUTO     = PR.CDPRODUTO
                              AND EXISTS
                                  (SELECT IP.CDIMPOSTO
                                     FROM ITEMPROIMPOS IP
                                    WHERE IP.CDFILIAL    = I.CDFILIAL
                                      AND IP.NRLANCTONF  = I.NRLANCTONF
                                      AND IP.NRSEQITPROD = I.NRSEQITPROD
                                      AND IP.CDIMPOSTO   = IM.CDIMPOSTO) )PR
                     ORDER BY PR.CDARVPROD ASC) LOOP

         V_CONTREGA := NVL(V_CONTREGA,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('08'||LPAD(RTRIM(V_CONTREGA), 8, '0'), C_REG0200.ARQ_0200, P_NRSEQARQMAG);

      /* fator de conversao de unidades
      FOR C_REG0220 IN (SELECT ('|0220'       || '|' ||
                                SGUNIDADE     || '|' ||
                                REPLACE(LTRIM(TO_CHAR(VRFATOCONV, '999999999990.00')), '.', ',') || '|'   ) AS ARQ_0220
                        FROM (SELECT PR.SGUNIDADE  AS SGUNIDADE,
                                     SUM(PR.VRFATOCONV) AS VRFATOCONV
                              FROM  PRODUTO PR, UNIDMEDIDA UN
                              WHERE PR.SGUNIDADE = UN.SGUNIDADE
                                AND UN.SGUNIDADE = C_REG0200.SGUNIDADE
                                AND PR.CDARVPROD = C_REG0200.CDARVPROD
                              GROUP BY PR.SGUNIDADE) ) LOOP
         V_CONTREGA := NVL(V_CONTREGA,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('08'||LPAD(RTRIM(V_CONTREGA), 8, '0'), C_REG0220.ARQ_0220, P_NRSEQARQMAG);
      END LOOP;
   END LOOP;
*/
   FOR C_REG0400_AUX IN (SELECT DISTINCT SUBSTR(CDFISCOPERNF,2,4) AS CDFISCOAUX, CDFISCOPERNF
                         FROM IMPOSTONF I, MAPAAPURAC M, NOTAFISCAL N
                         WHERE M.CDEMPRESA    = P_CDEMPRESA
                           AND M.CDIMPOSTO    = P_CDIMPOSTO
                           AND M.CDINSCESTA   = P_CDINSCESTA
                           AND M.DTINIPERIODO = P_DTINICIAL
                           AND M.DTFIMPERIODO = P_DTFINAL
                           AND I.NRSEQUMAPAPU = M.NRSEQUMAPAPU
                           AND I.CDFILIAL     = N.CDFILIAL
                           AND I.NRLANCTONF   = N.NRLANCTONF
                           AND N.CDMODDOCFISC IN ('01','04','55')) LOOP
                           --AND N.CDMODDOCFISC IN ('01','04','21','22','55')
      FOR C_REG0400 IN (SELECT ('|0400'                      || '|' ||
                                C_REG0400_AUX.CDFISCOPERNF   || '|' ||
                                MAX(DSCDFISCOPER)            || '|'  ) ARQ_0400, CDFISCOPER
                        FROM CODFISCOPER
                        WHERE CDFISCOPER = C_REG0400_AUX.CDFISCOAUX
                        GROUP BY CDFISCOPER) LOOP
         V_CONTREGA := NVL(V_CONTREGA,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('09'||LPAD(RTRIM(V_CONTREGA), 8, '0'), C_REG0400.ARQ_0400, P_NRSEQARQMAG);
      END LOOP;
   END LOOP;

   /* ENCERRAMENTO DO BLOCO 0
   V_CONTREGA := NVL(V_CONTREGA,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('10'||LPAD(RTRIM(V_CONTREGA), 8, '0'), '|0990'|| '|' ||V_CONTREGA|| '|' , P_NRSEQARQMAG);

   */
   RETURN(V_CONTREGA);

END;
/

-- VERSAO --
BEGIN ATUVEROBJ('SPED_INICIO_FOS','5.11.000'); END;
/