CREATE OR REPLACE PROCEDURE NF_PAULISTA(
   P_NRLISTA     IN NOTAFISCAL.NRLANCTONF%TYPE,
   P_CDEMPRESA   IN NOTAFISCAL.CDEMPRESA%TYPE,
   P_DTINICIAL   IN NOTAFISCAL.DTEMISSAO%TYPE,
   P_DTFINAL     IN NOTAFISCAL.DTEMISSAO%TYPE,
   P_NRSEQARQMAG IN NOTAFISCAL.NRLANCTONF%TYPE,
   P_LIVROMAG    IN NOTAFISCAL.NRLANCTONF%TYPE,
   P_CDINSCESTA  IN NOTAFISCAL.CDINSCESTA%TYPE,
   P_VERSAO      IN VARCHAR2)
AS
   V_EMPRESA       VARCHAR2(4000);
   V_DADOS_20      VARCHAR2(4000);
   V_DADOS_40      VARCHAR2(4000);
   V_DADOS_50      VARCHAR2(4000); 
   V_DADOS_90      VARCHAR2(4000); 
   V_CDFISCOPERNF  VARCHAR2(04); 
   V_CONTADOR      NUMBER; 
   V_CONT_REG_20   NUMBER; 
   V_CONT_REG_30   NUMBER; 
   V_CONT_REG_40   NUMBER; 
   V_CONT_REG_50   NUMBER; 
   V_CONT_REG_60   NUMBER; 
   V_VRCONT        NUMBER; 
   V_VRFRETE       NUMBER; 
   V_VRSEGURO      NUMBER; 
   V_VROUTROS      NUMBER; 
   V_VRDESCONTO    NUMBER; 
   V_VRDESCPROD    NUMBER; 
BEGIN 
    
   V_CONTADOR    := 0000000000; 
   V_CONT_REG_20 := 0; 
   V_CONT_REG_30 := 0;
   V_CONT_REG_40 := 0;
   V_CONT_REG_50 := 0;
   V_CONT_REG_60 := 0;

   SELECT '10'        || '|' ||
          P_VERSAO    || '|' ||
          REPLACE(REPLACE(REPLACE(RPAD(NRINSJURIEST, 14, '0'),'/',''),'-',''),'.','') || '|' ||
          P_DTINICIAL || '|' ||
          P_DTFINAL 
     INTO V_EMPRESA 
     FROM INSCRESTAD 
    WHERE CDEMPRESA = P_CDEMPRESA 
      AND CDINSCESTA = P_CDINSCESTA; 
   INSERT INTO ARQMAGNETICO 
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG) 
   VALUES 
     (V_CONTADOR, V_EMPRESA, P_NRSEQARQMAG); 
   V_CONTADOR := NVL(V_CONTADOR,0)+1; 
    
   FOR C_NOTAS IN (SELECT ARQUIVO, DADOS_1, CDLISTA, CDFILIAL, NRLANCTONF, NRNOTAFISC 
                   FROM (SELECT '20'                                                                           || '|' || 
                                DECODE(N.IDSTATUS, 'C', 'C',DECODE(N.IDIMPNFPA, 'S', 'R', 'I'))                || '|' || 
                                DECODE(IDSTATUS, 'C', 'CANCEL', '')                                            || '|' || 
                                SUBSTR(NMTIPOOPER,1,60)                                                        || '|' || 
                                DECODE(N.IDENTRSAIDOP, 'S',DECODE(N.CDSERIE, 'U', '0', DECODE(SUBSTR(N.CDSERIE,1,1), '-', '0', SUBSTR(N.CDSERIE,1,1))), DECODE(N.CDSERIEENTR, 'U', '0', DECODE(SUBSTR(N.CDSERIEENTR,1,1), '-', '0', SUBSTR(N.CDSERIEENTR,1,1)))) || '|' || 
                                NRNOTAFISC                                                                     || '|' || 
                                TO_CHAR(N.DTEMISSAO, 'dd/mm/yyyy hh:mm:ss')                                    || '|' || 
                                TO_CHAR(N.DTENTRSAID, 'dd/mm/yyyy hh:mm:ss')                                   || '|' || 
                                DECODE(N.IDENTRSAIDOP, 'E', '0', '1')                                          || '|'  AS DADOS_1, 
                                ''                                                                             || '|' || 
                                ''                                                                             || '|' || 
                                REPLACE(REPLACE(REPLACE(NRINSJURCLIE,'-',''),'/',''),'.','')                   || '|' || 
                                SUBSTR(C.NMRAZSOCCLIE,1,60)                                                    || '|' || 
                                SUBSTR(DSENDECLIE,1,60)                                                        || '|' || 
                                '0'                                                                            || '|' || 
                                DSCOMPENDCLI                                                                   || '|' || 
                                NMBAIRCLIE                                                                     || '|' || 
                                SUBSTR(NVL(M.NMMUNICIPIO,'EXTERIOR'),1,60)                                     || '|' || 
                                NVL(E.SGESTADO,'EX')                                                           || '|' || 
                                REPLACE(REPLACE(REPLACE(NRCEPCLIE,'-',''),'.',''),' ','')                      || '|' || 
                                ''                                                                             || '|' || 
                                SUBSTR(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(E.NRTELECLIE,'(',''),')',''),'-',''),' ',''),'.',''),1,10)   || '|' || 
                                REPLACE(REPLACE(REPLACE(C.NRINSESTCLIE,'-',''),'/',''),'.','')                  AS ARQUIVO, 
                                L.CDLISTA, N.CDFILIAL, N.NRLANCTONF, N.NRNOTAFISC 
                         FROM NOTAFISCAL N, TIPOOPERAC T, IMPOSTONF I, 
                              CLIENTE C, ENDECLIE E, MUNICIPIO M, 
                              LISTA L, IMPOSTO IM 
                         WHERE N.CDFILIAL||N.NRLANCTONF = L.CDLISTA 
                           AND N.CDINSCESTA = P_CDINSCESTA 
                           AND N.IDENTRSAIDOP = T.IDENTRSAIDOP 
                           AND N.CDTIPOOPER = T.CDTIPOOPER 
                           AND N.CDFILIAL = I.CDFILIAL 
                           AND N.NRLANCTONF = I.NRLANCTONF 
                           AND IM.CDIMPOSTONFE = '00' 
                           AND IM.CDIMPOSTO = I.CDIMPOSTO 
                           AND N.CDCLIENTE = C.CDCLIENTE 
                           AND C.CDCLIENTE = E.CDCLIENTE 
                           AND IDTPENDECLIE = 'P' 
                           AND N.IDORIGDESTNF = 'C' 
                           AND E.CDPAIS = M.CDPAIS 
                           AND E.SGESTADO = M.SGESTADO 
                           AND E.CDMUNICIPIO = M.CDMUNICIPIO 
                           AND L.NRLISTA = P_NRLISTA 
                         GROUP BY DECODE(N.IDSTATUS, 'C', 'C',DECODE(N.IDIMPNFPA, 'S', 'R', 'I')), 
                                  DECODE(IDSTATUS, 'C', 'CANCEL', ''), 
                                  DECODE(N.IDENTRSAIDOP, 'S',DECODE(N.CDSERIE, 'U', '0', DECODE(SUBSTR(N.CDSERIE,1,1), '-', '0', SUBSTR(N.CDSERIE,1,1))), DECODE(N.CDSERIEENTR, 'U', '0', DECODE(SUBSTR(N.CDSERIEENTR,1,1), '-', '0', SUBSTR(N.CDSERIEENTR,1,1)))), 
                                  SUBSTR(NMTIPOOPER,1,60), NRNOTAFISC, 
                                  TO_CHAR(N.DTEMISSAO, 'dd/mm/yyyy hh:mm:ss'), 
                                  TO_CHAR(N.DTENTRSAID, 'dd/mm/yyyy hh:mm:ss'), 
                                  DECODE(N.IDENTRSAIDOP, 'E', '0', '1'), 
                                  REPLACE(REPLACE(REPLACE(NRINSJURCLIE,'-',''),'/',''),'.',''), 
                                  SUBSTR(C.NMRAZSOCCLIE,1,60), SUBSTR(DSENDECLIE,1,60), 
                                  DSCOMPENDCLI, NMBAIRCLIE, SUBSTR(NVL(M.NMMUNICIPIO,'EXTERIOR'),1,60), 
                                  NVL(E.SGESTADO,'EX'), REPLACE(REPLACE(REPLACE(NRCEPCLIE,'-',''),'.',''),' ',''), 
                                  SUBSTR(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(E.NRTELECLIE,'(',''),')',''),'-',''),' ',''),'.',''),1,10), 
                                  REPLACE(REPLACE(REPLACE(C.NRINSESTCLIE,'-',''),'/',''),'.',''), 
                                  L.CDLISTA, N.CDFILIAL, N.NRLANCTONF 
                         UNION ALL 
                         SELECT '20'                                                                           || '|' || 
                                DECODE(N.IDSTATUS, 'C', 'C',DECODE(N.IDIMPNFPA, 'S', 'R', 'I'))                || '|' || 
                                DECODE(IDSTATUS, 'C', 'CANCEL', '')                                            || '|' || 
                                SUBSTR(NMTIPOOPER,1,60)                                                        || '|' || 
                                DECODE(N.IDENTRSAIDOP, 'S',DECODE(N.CDSERIE, 'U', '0', DECODE(SUBSTR(N.CDSERIE,1,1), '-', '0', SUBSTR(N.CDSERIE,1,1))), DECODE(N.CDSERIEENTR, 'U', '0', DECODE(SUBSTR(N.CDSERIEENTR,1,1), '-', '0', SUBSTR(N.CDSERIEENTR,1,1))))   || '|' || 
                                NRNOTAFISC                                                                     || '|' || 
                                TO_CHAR(N.DTEMISSAO, 'dd/mm/yyyy hh:mm:ss')                                    || '|' || 
                                TO_CHAR(N.DTENTRSAID, 'dd/mm/yyyy hh:mm:ss')                                   || '|' || 
                                DECODE(N.IDENTRSAIDOP, 'E', '0', '1')                                          || '|'  AS DADOS_1, 
                                ''                                                                             || '|' || 
                                ''                                                                             || '|' || 
                                REPLACE(REPLACE(REPLACE(NRINSJURFORN,'-',''),'/',''),'.','')                   || '|' || 
                                SUBSTR(F.NMRAZSOCFORN,1,60)                                                    || '|' || 
                                SUBSTR(DSENDEFORN,1,60)                                                        || '|' || 
                                '0'                                                                            || '|' || 
                                DSCOMPENDFOR                                                                   || '|' || 
                                NMBAIRFORN                                                                     || '|' || 
                                SUBSTR(NVL(M.NMMUNICIPIO,'EXTERIOR'),1,60)                                     || '|' || 
                                NVL(E.SGESTADO,'EX')                                                           || '|' || 
                                REPLACE(REPLACE(REPLACE(NRCEPFORN,'-',''),'.',''),' ','')                      || '|' || 
                                ''                                                                             || '|' || 
                                SUBSTR(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(E.NRTELEFORN,'(',''),')',''),'-',''),' ',''),'.',''),1,10)   || '|' || 
                                REPLACE(REPLACE(REPLACE(F.NRINSESTFORN,'-',''),'/',''),'.','')                  AS ARQUIVO, 
                                L.CDLISTA, N.CDFILIAL, N.NRLANCTONF, N.NRNOTAFISC 
                         FROM NOTAFISCAL N, TIPOOPERAC T, IMPOSTONF I, 
                              FORNECEDOR F, ENDEFORN E, MUNICIPIO M, LISTA L, IMPOSTO IM 
                         WHERE N.CDFILIAL||N.NRLANCTONF = L.CDLISTA 
                           AND N.CDINSCESTA = P_CDINSCESTA 
                           AND N.IDENTRSAIDOP = T.IDENTRSAIDOP 
                           AND N.CDTIPOOPER = T.CDTIPOOPER 
                           AND N.CDFILIAL = I.CDFILIAL 
                           AND N.NRLANCTONF = I.NRLANCTONF 
                           AND IM.CDIMPOSTONFE = '00' 
                           AND IM.CDIMPOSTO = I.CDIMPOSTO 
                           AND N.CDFORNECED = F.CDFORNECED 
                           AND F.CDFORNECED = E.CDFORNECED 
                           AND IDTPENDEFORN = 'P' 
                           AND N.IDORIGDESTNF = 'F' 
                           AND E.CDPAIS = M.CDPAIS 
                           AND E.SGESTADO = M.SGESTADO 
                           AND E.CDMUNICIPIO = M.CDMUNICIPIO 
                           AND L.NRLISTA = P_NRLISTA 
                         GROUP BY DECODE(N.IDSTATUS, 'C', 'C',DECODE(N.IDIMPNFPA, 'S', 'R', 'I')), 
                                  DECODE(IDSTATUS, 'C', 'CANCEL', ''), 
                                  SUBSTR(NMTIPOOPER,1,60), 
                                  DECODE(N.IDENTRSAIDOP, 'S',DECODE(N.CDSERIE, 'U', '0', DECODE(SUBSTR(N.CDSERIE,1,1), '-', '0', SUBSTR(N.CDSERIE,1,1))), DECODE(N.CDSERIEENTR, 'U', '0', DECODE(SUBSTR(N.CDSERIEENTR,1,1), '-', '0', SUBSTR(N.CDSERIEENTR,1,1)))), 
                                  NRNOTAFISC, TO_CHAR(N.DTEMISSAO, 'dd/mm/yyyy hh:mm:ss'), 
                                  TO_CHAR(N.DTENTRSAID, 'dd/mm/yyyy hh:mm:ss'), 
                                  DECODE(N.IDENTRSAIDOP, 'E', '0', '1'), 
                                  REPLACE(REPLACE(REPLACE(NRINSESTFORN,'-',''),'/',''),'.',''), 
                                  REPLACE(REPLACE(REPLACE(NRINSJURFORN,'-',''),'/',''),'.',''), 
                                  SUBSTR(F.NMRAZSOCFORN,1,60), SUBSTR(DSENDEFORN,1,60), 
                                  DSCOMPENDFOR, NMBAIRFORN, SUBSTR(NVL(M.NMMUNICIPIO,'EXTERIOR'),1,60), 
                                  NVL(E.SGESTADO,'EX'), REPLACE(REPLACE(REPLACE(NRCEPFORN,'-',''),'.',''),' ',''), 
                                  SUBSTR(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(E.NRTELEFORN,'(',''),')',''),'-',''),' ',''),'.',''),1,10) , 
                                  REPLACE(REPLACE(REPLACE(F.NRINSESTFORN,'-',''),'/',''),'.',''), L.CDLISTA, N.CDFILIAL, N.NRLANCTONF 
                         UNION ALL 
                         SELECT '20'                                                                        || '|' || 
                                DECODE(N.IDSTATUS, 'C', 'C',DECODE(N.IDIMPNFPA, 'S', 'R', 'I'))                || '|' || 
                                DECODE(IDSTATUS, 'C', 'CANCEL', '')                                            || '|' || 
                                SUBSTR(NMTIPOOPER,1,60)                                                        || '|' || 
                                DECODE(N.IDENTRSAIDOP, 'S',DECODE(N.CDSERIE, 'U', '0', DECODE(SUBSTR(N.CDSERIE,1,1), '-', '0', SUBSTR(N.CDSERIE,1,1))), DECODE(N.CDSERIEENTR, 'U', '0', DECODE(SUBSTR(N.CDSERIEENTR,1,1), '-', '0', SUBSTR(N.CDSERIEENTR,1,1))))   || '|' || 
                                NRNOTAFISC                                                                     || '|' || 
                                TO_CHAR(N.DTEMISSAO, 'dd/mm/yyyy hh:mm:ss')                                    || '|' || 
                                TO_CHAR(N.DTENTRSAID, 'dd/mm/yyyy hh:mm:ss')                                   || '|' || 
                                DECODE(N.IDENTRSAIDOP, 'E', '0', '1')                                          || '|'  AS DADOS_1, 
                                ''                                                                             || '|' || 
                                ''                                                                             || '|' || 
                                REPLACE(REPLACE(REPLACE(NRINSJURFILI,'-',''),'/',''),'.','')                   || '|' || 
                                SUBSTR(F.NMRAZSOCFILI,1,60)                                                    || '|' || 
                                SUBSTR(DSENDEFILI,1,60)                                                        || '|' || 
                                '0'                                                                            || '|' || 
                                DSCOMPENDFIL                                                                   || '|' || 
                                NMBAIRFILI                                                                     || '|' || 
                                SUBSTR(NVL(M.NMMUNICIPIO,'EXTERIOR'),1,60)                                     || '|' || 
                                NVL(E.SGESTADO,'EX')                                                           || '|' || 
                                REPLACE(REPLACE(REPLACE(NRCEPFILI,'-',''),'.',''),' ','')                                                      || '|' || 
                                ''                                                                             || '|' || 
                                SUBSTR(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(E.NRTELEFILI,'(',''),')',''),'-',''),' ',''),'.',''),1,10)   || '|' || 
                                REPLACE(REPLACE(REPLACE(F.CDINSCESTA,'-',''),'/',''),'.','')                  AS ARQUIVO, 
                                L.CDLISTA, N.CDFILIAL, N.NRLANCTONF, N.NRNOTAFISC 
                         FROM NOTAFISCAL N, TIPOOPERAC T, IMPOSTONF I, 
                              FILIAL F, ENDEFILI E, MUNICIPIO M, LISTA L,IMPOSTO IM 
                         WHERE N.CDFILIAL||N.NRLANCTONF = L.CDLISTA 
                           AND N.CDINSCESTA = P_CDINSCESTA 
                           AND N.IDENTRSAIDOP = T.IDENTRSAIDOP 
                           AND N.CDTIPOOPER = T.CDTIPOOPER 
                           AND N.CDFILIAL = I.CDFILIAL 
                           AND N.NRLANCTONF = I.NRLANCTONF 
                           AND IM.CDIMPOSTONFE = '00' 
                           AND IM.CDIMPOSTO = I.CDIMPOSTO 
                           AND N.CDFILIRECE = F.CDFILIAL 
                           AND F.CDFILIAL = E.CDFILIAL 
                           AND IDTPENDEFILI = 'P' 
                           AND N.IDORIGDESTNF = 'L' 
                           AND E.CDPAIS = M.CDPAIS 
                           AND E.SGESTADO = M.SGESTADO 
                           AND E.CDMUNICIPIO = M.CDMUNICIPIO 
                           AND L.NRLISTA = P_NRLISTA 
                         GROUP BY DECODE(N.IDSTATUS, 'C', 'C',DECODE(N.IDIMPNFPA, 'S', 'R', 'I')), 
                                  DECODE(IDSTATUS, 'C', 'CANCEL', ''), SUBSTR(NMTIPOOPER,1,60), 
                                  DECODE(N.IDENTRSAIDOP, 'S',DECODE(N.CDSERIE, 'U', '0', DECODE(SUBSTR(N.CDSERIE,1,1), '-', '0', SUBSTR(N.CDSERIE,1,1))), DECODE(N.CDSERIEENTR, 'U', '0', DECODE(SUBSTR(N.CDSERIEENTR,1,1), '-', '0', SUBSTR(N.CDSERIEENTR,1,1)))), 
                                  NRNOTAFISC, TO_CHAR(N.DTEMISSAO, 'dd/mm/yyyy hh:mm:ss'), 
                                  TO_CHAR(N.DTENTRSAID, 'dd/mm/yyyy hh:mm:ss'), 
                                  DECODE(N.IDENTRSAIDOP, 'E', '0', '1'), 
                                  REPLACE(REPLACE(REPLACE(F.CDINSCESTA,'-',''),'/',''),'.',''), 
                                  REPLACE(REPLACE(REPLACE(NRINSJURFILI,'-',''),'/',''),'.',''), 
                                  SUBSTR(F.NMRAZSOCFILI,1,60), SUBSTR(DSENDEFILI,1,60), 
                                  DSCOMPENDFIL, NMBAIRFILI, SUBSTR(NVL(M.NMMUNICIPIO,'EXTERIOR'),1,60), 
                                  NVL(E.SGESTADO,'EX'), REPLACE(REPLACE(REPLACE(NRCEPFILI,'-',''),'.',''),' ',''), 
                                  SUBSTR(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(E.NRTELEFILI,'(',''),')',''),'-',''),' ',''),'.',''),1,10) , 
                                  REPLACE(REPLACE(REPLACE(F.CDINSCESTA,'-',''),'/',''),'.',''), L.CDLISTA, N.CDFILIAL, N.NRLANCTONF) 
                   ORDER BY NRNOTAFISC) LOOP 
         SELECT MAX(VRCONTABILNF) AS VRCONTABILNF, 
                MAX(CDFISCOPERNF) AS CDFISCOPERNF 
           INTO V_VRCONT, V_CDFISCOPERNF 
           FROM IMPOSTONF I, IMPOSTO IM 
          WHERE CDFILIAL = C_NOTAS.CDFILIAL 
            AND NRLANCTONF = C_NOTAS.NRLANCTONF 
            AND IM.CDIMPOSTONFE = '00' 
            AND I.CDIMPOSTO = IM.CDIMPOSTO; 
         V_CONT_REG_20 := NVL(V_CONT_REG_20,0)+1; 
         INSERT INTO ARQMAGNETICO 
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG) 
         VALUES 
           (V_CONTADOR, C_NOTAS.DADOS_1||V_CDFISCOPERNF||'|'||C_NOTAS.ARQUIVO, P_NRSEQARQMAG); 
         V_CONTADOR := NVL(V_CONTADOR,0)+1; 
          
         FOR C_ITENS_NOTAS IN (SELECT ('30'                           || '|' || 
                                   P.CDARVPROD                        || '|' || 
                                   SUBSTR(P.NMPRODUTO||' - '||CDFISCOPERIT,1,60)           || '|' || 
                                   P.CDPRODNCM                        || '|' || 
                                   P.SGUNIDADE                        || '|' || 
                                   NVL(REPLACE(LTRIM(TO_CHAR(ROUND(QTITEMPROD,4), '0000000.0000')), '.', ','),'0,00')           || '|' || 
                                   NVL(REPLACE(LTRIM(TO_CHAR(ROUND(VRUNITITEM,4), '00000000000.0000')), '.', ','),'0,00')           || '|' || 
                                   NVL(REPLACE(LTRIM(TO_CHAR(ROUND(VRTOTAITEM,2), '000000000000.00')), '.', ','),'0,00')           || '|' || 
                                   CDSITUTRIBPR           || '|' || 
                                   NVL(REPLACE(LTRIM(TO_CHAR(ROUND(VRALIQITEM,2), '00.00')), '.', ','),'0,00')           || '|' || 
                                   NVL(REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(IT.CDFILIAL,IT.NRLANCTONF, '10', 'A'),2), '00.00')), '.', ','),'0,00')            || '|' || 
                                   NVL(REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(IT.CDFILIAL,IT.NRLANCTONF, '10', 'I'),2), '000000000000.00')), '.', ','),'0,00')  ) AS ARQUIVO 
                            FROM ITEMPRODUTO I, PRODUTO P, ITEMPROIMPOS IT, IMPOSTO IM 
                            WHERE I.CDFILIAL   = C_NOTAS.CDFILIAL 
                              AND I.NRLANCTONF = C_NOTAS.NRLANCTONF 
                              AND P.CDPRODUTO   = I.CDPRODUTO 
                              AND I.CDFILIAL    = IT.CDFILIAL 
                              AND I.NRLANCTONF  = IT.NRLANCTONF 
                              AND I.NRSEQITPROD = IT.NRSEQITPROD 
                              AND IM.CDIMPOSTONFE     = '00' 
                              AND IM.CDIMPOSTO   = IT.CDIMPOSTO 
                              AND IT.NRITPROIMPOS = '1' ) LOOP 
          
         V_CONT_REG_30 := NVL(V_CONT_REG_30,0)+1; 
         INSERT INTO ARQMAGNETICO 
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG) 
         VALUES 
           (V_CONTADOR, C_ITENS_NOTAS.ARQUIVO, P_NRSEQARQMAG); 
         V_CONTADOR := NVL(V_CONTADOR,0)+1; 
         END LOOP; 
          
         V_VRFRETE    := 0; 
         V_VRSEGURO   := 0; 
         V_VROUTROS   := 0; 
         V_VRDESCONTO := 0; 
         V_VRDESCPROD := 0; 
         FOR C_FRETE IN (SELECT VRITEMENCA 
                           FROM ITEMENCARGO E, ENCARGO EN 
                          WHERE E.CDENCARGO = EN.CDENCARGO 
                            AND UPPER(EN.NMENCARGO) LIKE '%FRET%' 
                            AND EN.IDENCARGO = 'A' 
                            AND E.CDFILIAL = C_NOTAS.CDFILIAL 
                            AND E.NRLANCTONF = C_NOTAS.NRLANCTONF 
                            AND VRITEMENCA > 0) LOOP 
            V_VRFRETE := NVL(V_VRFRETE,0) + C_FRETE.VRITEMENCA; 
         END LOOP; 
         FOR C_SEGURRO IN (SELECT VRITEMENCA 
                             FROM ITEMENCARGO E, ENCARGO EN 
                            WHERE E.CDENCARGO = EN.CDENCARGO 
                              AND UPPER(EN.NMENCARGO) LIKE '%SEGU%' 
                              AND EN.IDENCARGO = 'A' 
                              AND E.CDFILIAL = C_NOTAS.CDFILIAL 
                              AND E.NRLANCTONF = C_NOTAS.NRLANCTONF 
                              AND VRITEMENCA > 0) LOOP 
            V_VRSEGURO := NVL(V_VRSEGURO,0) + C_SEGURRO.VRITEMENCA; 
         END LOOP; 
         FOR C_OUTROS IN (SELECT VRITEMENCA 
                            FROM ITEMENCARGO E, ENCARGO EN 
                           WHERE E.CDENCARGO = EN.CDENCARGO 
                             AND UPPER(EN.NMENCARGO) LIKE '%OUTR%' 
                             AND UPPER(EN.NMENCARGO) NOT LIKE '%SEGU%' 
                             AND UPPER(EN.NMENCARGO) NOT LIKE '%DESC%' 
                             AND UPPER(EN.NMENCARGO) NOT LIKE '%FRET%' 
                             AND EN.IDENCARGO = 'A' 
                             AND E.CDFILIAL = C_NOTAS.CDFILIAL 
                             AND E.NRLANCTONF = C_NOTAS.NRLANCTONF 
                             AND VRITEMENCA > 0) LOOP 
            V_VROUTROS := NVL(V_VROUTROS,0) + C_OUTROS.VRITEMENCA; 
          END LOOP; 
         FOR C_DESCONTO IN (SELECT VRITEMENCA 
                              FROM ITEMENCARGO E, ENCARGO EN 
                             WHERE E.CDENCARGO = EN.CDENCARGO 
                               AND EN.IDENCARGO = 'D' 
                               AND E.CDFILIAL = C_NOTAS.CDFILIAL 
                               AND E.NRLANCTONF = C_NOTAS.NRLANCTONF 
                               AND VRITEMENCA > 0) LOOP 
            V_VRDESCONTO := NVL(V_VRDESCONTO,0) + C_DESCONTO.VRITEMENCA; 
         END LOOP; 
         SELECT SUM(VRDESCITEM) 
           INTO V_VRDESCPROD 
           FROM ITEMPRODUTO 
          WHERE CDFILIAL = C_NOTAS.CDFILIAL 
            AND NRLANCTONF = C_NOTAS.NRLANCTONF; 
         BEGIN 
            SELECT '40'|| '|' || 
                   VALOR1|| '|' || 
                   VALOR2|| '|' || 
                   NVL(REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(C_NOTAS.CDFILIAL, C_NOTAS.NRLANCTONF, '80', 'B'),2), '000000000000.00')), '.', ','),'0,00')|| '|' || 
                   NVL(REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(C_NOTAS.CDFILIAL, C_NOTAS.NRLANCTONF, '80', 'I'),2), '000000000000.00')), '.', ','),'0,00')|| '|' || 
                   NVL(REPLACE(LTRIM(TO_CHAR(ROUND((NVL(VALOR5,0)-(NVL(V_VRFRETE,0)+NVL(V_VRSEGURO,0)+NVL(V_VROUTROS,0)))+NVL(V_VRDESCONTO,0)+NVL(V_VRDESCPROD,0),2), '000000000000.00')), '.', ','),'0,00') || '|' || 
                   NVL(REPLACE(LTRIM(TO_CHAR(ROUND(V_VRFRETE,2), '000000000000.00')), '.', ','),'0,00') || '|' || 
                   NVL(REPLACE(LTRIM(TO_CHAR(ROUND(V_VRSEGURO,2), '000000000000.00')), '.', ','),'0,00') || '|' || 
                   NVL(REPLACE(LTRIM(TO_CHAR(ROUND(NVL(V_VRDESCONTO,0)+NVL(V_VRDESCPROD,0),2), '000000000000.00')), '.', ','),'0,00') || '|' ||  
                   NVL(REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(C_NOTAS.CDFILIAL, C_NOTAS.NRLANCTONF, '10', 'I'),2), '000000000000.00')), '.', ','),'0,00')|| '|' || 
                   NVL(REPLACE(LTRIM(TO_CHAR(ROUND(V_VROUTROS,2), '000000000000.00')), '.', ','),'0,00') || '|' || 
                   VALOR11|| '|' || 
                   '0,00'|| '|' || 
                   NVL(REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(C_NOTAS.CDFILIAL, C_NOTAS.NRLANCTONF, '31', 'A'),2), '00.00')), '.', ','),'0,00')|| '|' || 
                   NVL(REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(C_NOTAS.CDFILIAL, C_NOTAS.NRLANCTONF, '31', 'I'),2), '000000000000.00')), '.', ','),'0,00')|| '|' 
            INTO V_DADOS_40 
            FROM (SELECT NVL(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VRBASECALCIT),2), '000000000000.00')), '.', ','),'0,00') AS VALOR1, 
                         NVL(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VRIMPOSTOIT),2), '000000000000.00')), '.', ','),'0,00')  AS VALOR2, 
                         NVL(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VRCONTITIMP),2), '000000000000.00')), '.', ','),'0,00')  AS VALOR5, 
                         NVL(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VRCONTITIMP),2), '000000000000.00')), '.', ','),'0,00')  AS VALOR11 
                    FROM ITEMPROIMPOS I, IMPOSTO IM 
                   WHERE I.CDFILIAL = C_NOTAS.CDFILIAL 
                     AND I.NRLANCTONF = C_NOTAS.NRLANCTONF 
                     AND IM.CDIMPOSTONFE = '00' 
                     AND I.CDIMPOSTO = IM.CDIMPOSTO); 
         EXCEPTION 
            WHEN NO_DATA_FOUND THEN 
               V_DADOS_40 := NULL; 
         END; 
          
         V_CONT_REG_40 := NVL(V_CONT_REG_40,0)+1; 
         INSERT INTO ARQMAGNETICO 
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG) 
         VALUES 
           (V_CONTADOR, V_DADOS_40, P_NRSEQARQMAG); 
         V_CONTADOR := NVL(V_CONTADOR,0)+1; 
          
         BEGIN 
            SELECT('50'        || '|' || 
                   DECODE(N.IDPGTOFRETE, '1', '0', '1')        || '|' || 
                   REPLACE(REPLACE(REPLACE(T.NRINSJURTRAN,'.',''),'/',''),'-','')        || '|' || 
                   SUBSTR(T.NMRAZSOCTRAN,1,60)        || '|' || 
                   T.NRINSESTTRAN        || '|' || 
                   T.DSENDETRAN        || '|' || 
                   SUBSTR(M.NMMUNICIPIO,1,60)        || '|' || 
                   M.SGESTADO        || '|' || 
                   N.NRPLACAVEIC        || '|' || 
                   ''        || '|' || 
                   SUBSTR(DSQTDEVOLU,1,15)        || '|' || 
                   SUBSTR(DSESPECVOLU,1,60)        || '|' || 
                   SUBSTR(DSMARCAVOLU,1,60)        || '|' || 
                   SUBSTR(DSNUMEVOLU,1,60)        || '|' || 
                   NVL(REPLACE(LTRIM(TO_CHAR(ROUND(VRPLIQUVOLU,2), '00000000000.000')), '.', ','),'0,00')        || '|' || 
                   NVL(REPLACE(LTRIM(TO_CHAR(ROUND(VRPBRUTOVOLU,2), '00000000000.000')), '.', ','),'0,00')        )ARQ_50 
            INTO V_DADOS_50 
            FROM NOTAFISCAL N, TRANSPORT T, MUNICIPIO M 
            WHERE N.CDFILIAL = C_NOTAS.CDFILIAL 
              AND N.NRLANCTONF = C_NOTAS.NRLANCTONF 
              AND N.IDTPIJURTRAN = T.IDTPIJURTRAN 
              AND N.NRINSJURTRAN = T.NRINSJURTRAN 
              AND T.CDPAIS = M.CDPAIS 
              AND T.SGESTADO = M.SGESTADO 
              AND T.CDMUNICIPIO = M.CDMUNICIPIO; 
         EXCEPTION 
            WHEN NO_DATA_FOUND THEN 
               V_DADOS_50 := NULL; 
         END; 
         IF V_DADOS_50 IS NULL THEN 
            V_DADOS_50 := '50'  || '|' || '0' 
                                || '|' 
                                || '|' 
                                || '|' 
                                || '|' 
                                || '|' 
                                || '|' 
                                || '|' 
                                || '|' 
                                || '|' 
                                || '|' 
                                || '|' 
                                || '|' 
                                || '|' 
                                || '|' 
                                || '|'; 
             
            V_CONT_REG_50 := NVL(V_CONT_REG_50,0)+1; 
            INSERT INTO ARQMAGNETICO 
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG) 
            VALUES 
              (V_CONTADOR, V_DADOS_50, P_NRSEQARQMAG); 
            V_CONTADOR := NVL(V_CONTADOR,0)+1; 
         ELSE 
             
            V_CONT_REG_50 := NVL(V_CONT_REG_50,0)+1; 
            INSERT INTO ARQMAGNETICO 
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG) 
            VALUES 
              (V_CONTADOR, V_DADOS_50, P_NRSEQARQMAG); 
            V_CONTADOR := NVL(V_CONTADOR,0)+1; 
         END IF; 
          
         V_CONT_REG_60 := NVL(V_CONT_REG_60,0)+1; 
         INSERT INTO ARQMAGNETICO 
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG) 
         VALUES 
           (V_CONTADOR, '60'|| '|' || ''|| '|' ||''|| '|' || '|' , P_NRSEQARQMAG); 
         V_CONTADOR := NVL(V_CONTADOR,0)+1; 
      UPDATE NOTAFISCAL 
      SET IDIMPNFPA = 'S' 
      WHERE CDFILIAL = C_NOTAS.CDFILIAL 
        AND NRLANCTONF = C_NOTAS.NRLANCTONF; 
   END LOOP; 
   V_DADOS_90 := '90'               || '|' || 
                 LPAD(V_CONT_REG_20, 5, '0')      || '|' || 
                 LPAD(V_CONT_REG_30, 5, '0')      || '|' || 
                 LPAD(V_CONT_REG_40, 5, '0')      || '|' || 
                 LPAD(V_CONT_REG_50, 5, '0')      || '|' || 
                 LPAD(V_CONT_REG_60, 5, '0'); 
    
   INSERT INTO ARQMAGNETICO 
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG) 
   VALUES 
     (V_CONTADOR, V_DADOS_90 , P_NRSEQARQMAG); 
   V_CONTADOR := NVL(V_CONTADOR,0)+1; 
END; 
/

-- VERSAO --
BEGIN ATUVEROBJ('NF_PAULISTA','5.05.025'); END;
/

