CREATE OR REPLACE PROCEDURE NOTAFISCAL_NFE_SANTOANDRE(
   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_CDINSCMUNI  IN NOTAFISCAL.CDINSCMUNI%TYPE)

AS
   V_REGISTRO_1    VARCHAR2(4000);
   V_REGISTRO_2    VARCHAR2(4000);
   V_REGISTRO_3    VARCHAR2(4000);
   V_EMPRESA       VARCHAR2(4000);
   V_CDISS         VARCHAR2(02);  
   V_CDISS_RET     VARCHAR2(02);  
   V_NMMUNICIP     VARCHAR2(11); 
   V_NRNOTAFISC    VARCHAR2(10); 
   V_REGDESC       VARCHAR2(4000);
   V_CONTADOR      NUMBER;
   V_CONT_REG_2    NUMBER;
   V_SEQRTS        NUMBER;
   V_DESCONTO      NUMBER;
BEGIN
   /* sequencial para gerao da nota fiscal SantoAndre */
   V_CONTADOR      := 0000000000;
   V_CONT_REG_2    := 0;
   V_SEQRTS        := 0;

   V_CDISS         := '31'; /*Codigo do Grupo do ISS*/
   V_CDISS_RET     := '35'; /*Codigo do Grupo do ISS RETIDO*/

   BEGIN
      SELECT TRIM(NMMUNICIPIO)
      INTO V_NMMUNICIP
      FROM MUNICIPIO
      WHERE NMMUNICIPIO LIKE 'SANTO ANDR%';
   END;


   /* 1 - CABECALHO */
   V_REGISTRO_1 :=  '1' ||
                    LPAD(REPLACE(REPLACE(REPLACE(REPLACE(P_CDINSCMUNI,'-',''),'.',''),'/',''), ' ', ''), 8, 0) ||
                    TO_CHAR(P_DTINICIAL, 'mmyyyy');

   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     (V_CONTADOR, V_REGISTRO_1, P_NRSEQARQMAG);
   V_CONTADOR := NVL(V_CONTADOR,0)+1;
   

   /* 2 - DADOS GERAIS E DO TOMADOR */

   FOR C_REGNOTAS IN (SELECT CDLISTA
                        FROM LISTA
                       WHERE NRLISTA = P_NRLISTA) LOOP

        /* FORNECEDOR ISS*/
     FOR C_NOTAS IN (SELECT DISTINCT N.CDFILIAL, N.NRLANCTONF, N.VRNOTAFISC,
                           ('2' ||
                           LPAD(NVL(NRNOTAFISC,'0'), 10, '0') ||
                           TO_CHAR(N.DTEMISSAO, 'ddmmyyyy')  ||
                           '1'  ||
                           UPPER(RPAD(NVL(SUBSTR(NMRAZSOCFORN, 1,100), ' '), 100,' ')) ||
                           RPAD('0', 10,' ') ||
                           UPPER(RPAD(NVL(SUBSTR(DSENDEFORN, 1,100), '0'), 100,' '))  ||
                           RPAD('0', 10,' ') ||
                           UPPER(RPAD(NVL(SUBSTR(NMBAIRFORN, 1,60), '0'), 60,' '))  ||
                           UPPER(RPAD(NVL(SUBSTR(DSCOMPENDFOR, 1,50), '0'), 50,' ')) ||
                           UPPER(RPAD(NVL(SUBSTR(M.NMMUNICIPIO,1,50), '0'), 50,' ')) ||
                           RPAD(NVL(E.SGESTADO, ' '), 2,' ')  ||
                           LPAD(NVL(SUBSTR(REPLACE(REPLACE(NRCEPFORN,'.',''),'-',''),1,8), ' '), 8,' ')||
                           UPPER(RPAD(NVL(DSEMAILFORN, ' '), 100,' ')) ||
                           LPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(NRINSESTFORN,'/',''),'.',''),'-',''), ' ', ''), ' '), 20,' ')||
                           DECODE(F.IDTPIJURFORN, 'F', '1', DECODE(F.IDTPIJURFORN, 'J', '2', '3')) ||
                           LPAD(DECODE(F.IDTPIJURFORN, 'F',F.NRINSJURFORN, DECODE(F.IDTPIJURFORN, 'J', F.NRINSJURFORN, F.NRINSJURFORN)), 14, ' ') ||
                           DECODE(F.IDTPIJURFORN, 'J',DECODE(M.NMMUNICIPIO, V_NMMUNICIP, RPAD(SUBSTR(REPLACE(REPLACE(REPLACE(REPLACE(NRINSMUNFORN,'.',''),'/',''),'-',''), ' ', ''), 1, 8), 8, ' '), LPAD(' ', 8,' ')), '        ') ||
                           '2'  ||
                           LPAD(' ',3000, ' ')                   ) AS DSGERAIS
                      FROM NOTAFISCAL N, FORNECEDOR F, ENDEFORN E,
                           MUNICIPIO M, EMPRESA EP, IMPOSTO IT,
                           MUNICIPIO MU, ITEMPRODUTO IP, IMPOSTONF IF,
                           INSCRMUNICIP IM, INSCRESTAD IE
                     WHERE IDTPENDEFORN   = 'P'
                       AND N.IDORIGDESTNF = 'F'
                       AND N.CDFILIAL||N.NRLANCTONF = C_REGNOTAS.CDLISTA
                       AND N.CDFORNECED    = F.CDFORNECED
                       AND N.CDFILIAL      = IP.CDFILIAL
                       AND N.NRLANCTONF    = IP.NRLANCTONF
                       AND F.CDFORNECED    = E.CDFORNECED
                       AND E.CDPAIS        = M.CDPAIS
                       AND E.SGESTADO      = M.SGESTADO
                       AND E.CDMUNICIPIO   = M.CDMUNICIPIO
                       AND EP.CDEMPRESA    = N.CDEMPRESA
                       AND EP.CDPAIS       = MU.CDPAIS
                       AND EP.SGESTADO     = MU.SGESTADO
                       AND EP.CDMUNICIPIO  = MU.CDMUNICIPIO
                       AND N.CDEMPRESA     = IM.CDEMPRESA
                       AND N.CDINSCMUNI    = IM.CDINSCMUNI
                       AND N.CDEMPRESA     = IE.CDEMPRESA
                       AND N.CDINSCESTA    = IE.CDINSCESTA
                       AND IF.CDIMPOSTO    = IT.CDIMPOSTO
                       AND IF.NRLANCTONF   = N.NRLANCTONF
                       AND IF.CDFILIAL     = N.CDFILIAL
                       AND IT.CDIMPOSTONFE =  V_CDISS

                     /* FORNECEDOR ISS RETIDO*/
                     UNION ALL
                     SELECT DISTINCT N.CDFILIAL, N.NRLANCTONF, N.VRNOTAFISC,
                           ('2' ||
                           LPAD(NVL(NRNOTAFISC,'0'), 10, '0') ||
                           TO_CHAR(N.DTEMISSAO, 'ddmmyyyy')  ||
                           '1'  ||
                           UPPER(RPAD(NVL(SUBSTR(NMRAZSOCFORN, 1,100), ' '), 100,' ')) ||
                           RPAD('0', 10,' ') ||
                           UPPER(RPAD(NVL(SUBSTR(DSENDEFORN, 1,100), '0'), 100,' '))  ||
                           RPAD('0', 10,' ') ||
                           UPPER(RPAD(NVL(SUBSTR(NMBAIRFORN, 1,60), '0'), 60,' '))  ||
                           UPPER(RPAD(NVL(SUBSTR(DSCOMPENDFOR, 1,50), '0'), 50,' ')) ||
                           UPPER(RPAD(NVL(SUBSTR(M.NMMUNICIPIO,1,50), '0'), 50,' ')) ||
                           RPAD(NVL(E.SGESTADO, ' '), 2,' ')  ||
                           LPAD(NVL(SUBSTR(REPLACE(REPLACE(NRCEPFORN,'.',''),'-',''),1,8), ' '), 8,' ')||
                           UPPER(RPAD(NVL(DSEMAILFORN, ' '), 100,' ')) ||
                           LPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(NRINSESTFORN,'/',''),'.',''),'-',''), ' ', ''), ' '), 20,' ')||
                           DECODE(F.IDTPIJURFORN, 'F', '1', DECODE(F.IDTPIJURFORN, 'J', '2', '3')) ||
                           LPAD(DECODE(F.IDTPIJURFORN, 'F',F.NRINSJURFORN, DECODE(F.IDTPIJURFORN, 'J', F.NRINSJURFORN, F.NRINSJURFORN)), 14, ' ') ||
                           DECODE(F.IDTPIJURFORN, 'J',DECODE(M.NMMUNICIPIO, V_NMMUNICIP, RPAD(SUBSTR(REPLACE(REPLACE(REPLACE(REPLACE(NRINSMUNFORN,'.',''),'/',''),'-',''), ' ', ''), 1, 8), 8, ' '), LPAD(' ', 8,' ')), '        ') ||
                           '1'  ||
                           LPAD(' ',3000, ' ')                   ) AS DSGERAIS
                      FROM NOTAFISCAL N, FORNECEDOR F, ENDEFORN E,
                           MUNICIPIO M, EMPRESA EP, IMPOSTO IT,
                           MUNICIPIO MU, ITEMPRODUTO IP, IMPOSTONF IF,
                           INSCRMUNICIP IM, INSCRESTAD IE
                     WHERE IDTPENDEFORN   = 'P'
                       AND N.IDORIGDESTNF = 'F'
                       AND N.CDFILIAL||N.NRLANCTONF = C_REGNOTAS.CDLISTA
                       AND N.CDFORNECED    = F.CDFORNECED
                       AND N.CDFILIAL      = IP.CDFILIAL
                       AND N.NRLANCTONF    = IP.NRLANCTONF
                       AND F.CDFORNECED    = E.CDFORNECED
                       AND E.CDPAIS        = M.CDPAIS
                       AND E.SGESTADO      = M.SGESTADO
                       AND E.CDMUNICIPIO   = M.CDMUNICIPIO
                       AND EP.CDEMPRESA    = N.CDEMPRESA
                       AND EP.CDPAIS       = MU.CDPAIS
                       AND EP.SGESTADO     = MU.SGESTADO
                       AND EP.CDMUNICIPIO  = MU.CDMUNICIPIO
                       AND N.CDEMPRESA     = IM.CDEMPRESA
                       AND N.CDINSCMUNI    = IM.CDINSCMUNI
                       AND N.CDEMPRESA     = IE.CDEMPRESA
                       AND N.CDINSCESTA    = IE.CDINSCESTA
                       AND IF.CDIMPOSTO    = IT.CDIMPOSTO
                       AND IF.NRLANCTONF   = N.NRLANCTONF
                       AND IF.CDFILIAL     = N.CDFILIAL
                       AND IT.CDIMPOSTONFE = V_CDISS_RET

                     /* CLIENTE ISS*/
                     UNION ALL
                     SELECT DISTINCT N.CDFILIAL, N.NRLANCTONF, N.VRNOTAFISC,
                            ('2' ||
                            LPAD(NVL(NRNOTAFISC,'0'), 10, '0') ||
                            TO_CHAR(N.DTEMISSAO, 'ddmmyyyy') ||
                            '1' ||
                            UPPER(RPAD(NVL(SUBSTR(NMRAZSOCCLIE, 1,100), ' '), 100,' ')) ||
                            RPAD('0', 10,' ')  ||
                            UPPER(RPAD(NVL(SUBSTR(DSENDECLIE, 1,100), '0'), 100,' ')) ||
                            RPAD('0', 10,' ') ||
                            UPPER(RPAD(NVL(SUBSTR(NMBAIRCLIE, 1,30), '0'), 30,' ')) ||
                            UPPER(RPAD(NVL(SUBSTR(DSCOMPENDCLI, 1,50), '0'), 50,' ')) ||
                            UPPER(RPAD(NVL(SUBSTR(M.NMMUNICIPIO,1,50), '0'), 50,' ')) ||
                            RPAD(NVL(E.SGESTADO, ' '), 2,' ')  ||
                            LPAD(NVL(SUBSTR(REPLACE(REPLACE(NRCEPCLIE,'.',''),'-',''),1,8), '0'), 8,'0')||
                            UPPER(RPAD(NVL(DSEMAILCLIE, ' '), 100,' ')) ||
                            LPAD(NVL(REPLACE(REPLACE(REPLACE(NRINSESTCLIE,'/',''),'.',''),'-',''), '0'), 20,'0')||
                            DECODE(C.IDTPIJURCLIE, 'F', '1', DECODE(C.IDTPIJURCLIE, 'J', '2', '3')) ||
                            LPAD(DECODE(C.IDTPIJURCLIE, 'F', C.NRINSJURCLIE, DECODE(C.IDTPIJURCLIE, 'J', C.NRINSJURCLIE, C.NRINSJURCLIE)), 14, ' ') ||
                            DECODE(C.IDTPIJURCLIE, 'J', DECODE(M.NMMUNICIPIO, 'SANTO ANDRE', RPAD(SUBSTR(REPLACE(REPLACE(REPLACE(REPLACE(C.NRINSMUNICLIE,'.',''),'/',''),'-',''), ' ', ''), 1, 8), 8, ' '), LPAD(' ', 8,' ')), '        ') ||
                            '2' ||
                            LPAD(' ',3000, ' ')                   ) AS DSGERAIS
                       FROM NOTAFISCAL N, CLIENTE C, ENDECLIE E,
                            MUNICIPIO M, EMPRESA EP, IMPOSTO IT,
                            MUNICIPIO MU, ITEMPRODUTO IP, IMPOSTONF IF,
                            INSCRMUNICIP IM, INSCRESTAD IE
                      WHERE IDTPENDECLIE   = 'P'
                        AND N.IDORIGDESTNF = 'C'
                        AND N.CDFILIAL||N.NRLANCTONF = C_REGNOTAS.CDLISTA
                        AND N.CDCLIENTE     = C.CDCLIENTE
                        AND N.CDFILIAL      = IP.CDFILIAL
                        AND N.NRLANCTONF    = IP.NRLANCTONF
                        AND C.CDCLIENTE     = E.CDCLIENTE
                        AND E.CDPAIS        = M.CDPAIS
                        AND E.SGESTADO      = M.SGESTADO
                        AND E.CDMUNICIPIO   = M.CDMUNICIPIO
                        AND EP.CDEMPRESA    = N.CDEMPRESA
                        AND EP.CDPAIS       = MU.CDPAIS
                        AND EP.SGESTADO     = MU.SGESTADO
                        AND EP.CDMUNICIPIO  = MU.CDMUNICIPIO
                        AND N.CDEMPRESA     = IM.CDEMPRESA
                        AND N.CDINSCMUNI    = IM.CDINSCMUNI
                        AND N.CDEMPRESA     = IE.CDEMPRESA
                        AND N.CDINSCESTA    = IE.CDINSCESTA
                        AND IF.CDIMPOSTO    = IT.CDIMPOSTO
                        AND IF.NRLANCTONF   = N.NRLANCTONF
                        AND IF.CDFILIAL     = N.CDFILIAL
                        AND IT.CDIMPOSTONFE = V_CDISS

                     /* CLIENTE ISS RETIDO*/
                     UNION ALL
                     SELECT DISTINCT N.CDFILIAL, N.NRLANCTONF, N.VRNOTAFISC,
                            ('2' ||
                            LPAD(NVL(NRNOTAFISC,'0'), 10, '0') ||
                            TO_CHAR(N.DTEMISSAO, 'ddmmyyyy') ||
                            '1' ||
                            UPPER(RPAD(NVL(SUBSTR(NMRAZSOCCLIE, 1,100), ' '), 100,' ')) ||
                            RPAD('0', 10,' ')  ||
                            UPPER(RPAD(NVL(SUBSTR(DSENDECLIE, 1,100), '0'), 100,' ')) ||
                            RPAD('0', 10,' ') ||
                            UPPER(RPAD(NVL(SUBSTR(NMBAIRCLIE, 1,30), '0'), 30,' ')) ||
                            UPPER(RPAD(NVL(SUBSTR(DSCOMPENDCLI, 1,50), '0'), 50,' ')) ||
                            UPPER(RPAD(NVL(SUBSTR(M.NMMUNICIPIO,1,50), '0'), 50,' ')) ||
                            RPAD(NVL(E.SGESTADO, ' '), 2,' ')  ||
                            LPAD(NVL(SUBSTR(REPLACE(REPLACE(NRCEPCLIE,'.',''),'-',''),1,8), '0'), 8,'0')||
                            UPPER(RPAD(NVL(DSEMAILCLIE, ' '), 100,' ')) ||
                            LPAD(NVL(REPLACE(REPLACE(REPLACE(NRINSESTCLIE,'/',''),'.',''),'-',''), '0'), 20,'0')||
                            DECODE(C.IDTPIJURCLIE, 'F', '1', DECODE(C.IDTPIJURCLIE, 'J', '2', '3')) ||
                            LPAD(DECODE(C.IDTPIJURCLIE, 'F', C.NRINSJURCLIE, DECODE(C.IDTPIJURCLIE, 'J', C.NRINSJURCLIE, C.NRINSJURCLIE)), 14, ' ') ||
                            DECODE(C.IDTPIJURCLIE, 'J', DECODE(M.NMMUNICIPIO, 'SANTO ANDRE', RPAD(SUBSTR(REPLACE(REPLACE(REPLACE(REPLACE(C.NRINSMUNICLIE,'.',''),'/',''),'-',''), ' ', ''), 1, 8), 8, ' '), LPAD(' ', 8,' ')), '        ') ||
                            '1' ||
                            LPAD(' ',3000, ' ')                   ) AS DSGERAIS
                       FROM NOTAFISCAL N, CLIENTE C, ENDECLIE E,
                            MUNICIPIO M, EMPRESA EP, IMPOSTO IT,
                            MUNICIPIO MU, ITEMPRODUTO IP, IMPOSTONF IF,
                            INSCRMUNICIP IM, INSCRESTAD IE
                      WHERE IDTPENDECLIE   = 'P'
                        AND N.IDORIGDESTNF = 'C'
                        AND N.CDFILIAL||N.NRLANCTONF = C_REGNOTAS.CDLISTA
                        AND N.CDCLIENTE     = C.CDCLIENTE
                        AND N.CDFILIAL      = IP.CDFILIAL
                        AND N.NRLANCTONF    = IP.NRLANCTONF
                        AND C.CDCLIENTE     = E.CDCLIENTE
                        AND E.CDPAIS        = M.CDPAIS
                        AND E.SGESTADO      = M.SGESTADO
                        AND E.CDMUNICIPIO   = M.CDMUNICIPIO
                        AND EP.CDEMPRESA    = N.CDEMPRESA
                        AND EP.CDPAIS       = MU.CDPAIS
                        AND EP.SGESTADO     = MU.SGESTADO
                        AND EP.CDMUNICIPIO  = MU.CDMUNICIPIO
                        AND N.CDEMPRESA     = IM.CDEMPRESA
                        AND N.CDINSCMUNI    = IM.CDINSCMUNI
                        AND N.CDEMPRESA     = IE.CDEMPRESA
                        AND N.CDINSCESTA    = IE.CDINSCESTA
                        AND IF.CDIMPOSTO    = IT.CDIMPOSTO
                        AND IF.NRLANCTONF   = N.NRLANCTONF
                        AND IF.CDFILIAL     = N.CDFILIAL
                        AND IT.CDIMPOSTONFE = V_CDISS_RET

                     /* FILIAL ISS*/
                     UNION ALL
                     SELECT DISTINCT N.CDFILIAL, N.NRLANCTONF, N.VRNOTAFISC,
                            ('2' ||
                            LPAD(NVL(NRNOTAFISC,'0'), 10, '0')  ||
                            TO_CHAR(N.DTEMISSAO, 'ddmmyyyy')   ||
                            '1' ||
                            UPPER(RPAD(NVL(SUBSTR(NMRAZSOCFILI, 1,100), ' '), 100,' ')) ||
                            RPAD('0', 10,' ')  ||
                            UPPER(RPAD(NVL(SUBSTR(DSENDEFILI, 1,100), '0'), 100,' ')) ||
                            RPAD('0', 10,' ')  ||
                            UPPER(RPAD(NVL(SUBSTR(NMBAIRFILI, 1,30), '0'), 30,' ')) ||
                            UPPER(RPAD(NVL(SUBSTR(DSCOMPENDFIL, 1,50), '0'), 50,' ')) ||
                            UPPER(RPAD(NVL(SUBSTR(M.NMMUNICIPIO,1,50), '0'), 50,' ')) ||
                            RPAD(NVL(E.SGESTADO, ' '), 2,' ')  ||
                            LPAD(NVL(SUBSTR(REPLACE(REPLACE(NRCEPFILI,'.',''),'-',''),1,8), '0'), 8,'0')||
                            UPPER(RPAD(NVL(DSEMAILFILI, ' '), 100,' '))  ||
                            LPAD(NVL(REPLACE(REPLACE(REPLACE(F.CDINSCESTA,'/',''),'.',''),'-',''), '0'), 20,'0')||
                            DECODE(F.IDTPIJURFILI, 'F', '1', DECODE(F.IDTPIJURFILI, 'J', '2', '3')) ||
                            LPAD(DECODE(F.IDTPIJURFILI, 'F', F.NRINSJURFILI, DECODE(F.IDTPIJURFILI, 'J', F.NRINSJURFILI, F.NRINSJURFILI)), 14, ' ') ||
                            DECODE(F.IDTPIJURFILI, 'J', DECODE(M.NMMUNICIPIO, V_NMMUNICIP, RPAD(SUBSTR(REPLACE(REPLACE(REPLACE(REPLACE(F.CDINSCMUNI,'.',''),'/',''),'-',''), ' ', ''), 1, 8), 8, ' '), LPAD(' ', 8,' ')), '        ')  ||
                            '2' ||
                            LPAD(' ',3000, ' ')                  ) AS DSGERAIS
                       FROM NOTAFISCAL N, FILIAL F, ENDEFILI E,
                            MUNICIPIO M, EMPRESA EP, IMPOSTO IT,
                            MUNICIPIO MU, ITEMPRODUTO IP, IMPOSTONF IF,
                            INSCRMUNICIP IM, INSCRESTAD IE
                      WHERE N.IDENTRSAIDOP = 'E'
                        AND IDTPENDEFILI   = 'P'
                        AND N.IDORIGDESTNF = 'L'
                        AND N.CDFILIAL||N.NRLANCTONF = C_REGNOTAS.CDLISTA
                        AND N.CDFILIRECE    = F.CDFILIAL
                        AND N.CDFILIAL      = IP.CDFILIAL
                        AND N.NRLANCTONF    = IP.NRLANCTONF
                        AND F.CDFILIAL      = E.CDFILIAL
                        AND E.CDPAIS        = M.CDPAIS
                        AND E.SGESTADO      = M.SGESTADO
                        AND E.CDMUNICIPIO   = M.CDMUNICIPIO
                        AND EP.CDEMPRESA    = N.CDEMPRESA
                        AND EP.CDPAIS       = MU.CDPAIS
                        AND EP.SGESTADO     = MU.SGESTADO
                        AND EP.CDMUNICIPIO  = MU.CDMUNICIPIO
                        AND N.CDEMPRESA     = IM.CDEMPRESA
                        AND N.CDINSCMUNI    = IM.CDINSCMUNI
                        AND N.CDEMPRESA     = IE.CDEMPRESA
                        AND N.CDINSCESTA    = IE.CDINSCESTA
                        AND IF.CDIMPOSTO    = IT.CDIMPOSTO
                        AND IF.NRLANCTONF   = N.NRLANCTONF
                        AND IF.CDFILIAL     = N.CDFILIAL
                        AND IT.CDIMPOSTONFE = V_CDISS

                     /* FILIAL ISS RETIDO*/
                     UNION ALL
                     SELECT DISTINCT N.CDFILIAL, N.NRLANCTONF, N.VRNOTAFISC,
                            ('2' ||
                            LPAD(NVL(NRNOTAFISC,'0'), 10, '0')  ||
                            TO_CHAR(N.DTEMISSAO, 'ddmmyyyy')   ||
                            '1' ||
                            UPPER(RPAD(NVL(SUBSTR(NMRAZSOCFILI, 1,100), ' '), 100,' ')) ||
                            RPAD('0', 10,' ')  ||
                            UPPER(RPAD(NVL(SUBSTR(DSENDEFILI, 1,100), '0'), 100,' ')) ||
                            RPAD('0', 10,' ')  ||
                            UPPER(RPAD(NVL(SUBSTR(NMBAIRFILI, 1,30), '0'), 30,' ')) ||
                            UPPER(RPAD(NVL(SUBSTR(DSCOMPENDFIL, 1,50), '0'), 50,' ')) ||
                            UPPER(RPAD(NVL(SUBSTR(M.NMMUNICIPIO,1,50), '0'), 50,' ')) ||
                            RPAD(NVL(E.SGESTADO, ' '), 2,' ')  ||
                            LPAD(NVL(SUBSTR(REPLACE(REPLACE(NRCEPFILI,'.',''),'-',''),1,8), '0'), 8,'0')||
                            UPPER(RPAD(NVL(DSEMAILFILI, ' '), 100,' '))  ||
                            LPAD(NVL(REPLACE(REPLACE(REPLACE(F.CDINSCESTA,'/',''),'.',''),'-',''), '0'), 20,'0')||
                            DECODE(F.IDTPIJURFILI, 'F', '1', DECODE(F.IDTPIJURFILI, 'J', '2', '3')) ||
                            LPAD(DECODE(F.IDTPIJURFILI, 'F', F.NRINSJURFILI, DECODE(F.IDTPIJURFILI, 'J', F.NRINSJURFILI, F.NRINSJURFILI)), 14, ' ') ||
                            DECODE(F.IDTPIJURFILI, 'J', DECODE(M.NMMUNICIPIO, V_NMMUNICIP, RPAD(SUBSTR(REPLACE(REPLACE(REPLACE(REPLACE(F.CDINSCMUNI,'.',''),'/',''),'-',''), ' ', ''), 1, 8), 8, ' '), LPAD(' ', 8,' ')), '        ')  ||
                            '1' ||
                            LPAD(' ',3000, ' ')                  ) AS DSGERAIS
                       FROM NOTAFISCAL N, FILIAL F, ENDEFILI E,
                            MUNICIPIO M, EMPRESA EP, IMPOSTO IT,
                            MUNICIPIO MU, ITEMPRODUTO IP, IMPOSTONF IF,
                            INSCRMUNICIP IM, INSCRESTAD IE
                      WHERE N.IDENTRSAIDOP = 'E'
                        AND IDTPENDEFILI   = 'P'
                        AND N.IDORIGDESTNF = 'L'
                        AND N.CDFILIAL||N.NRLANCTONF = C_REGNOTAS.CDLISTA
                        AND N.CDFILIRECE    = F.CDFILIAL
                        AND N.CDFILIAL      = IP.CDFILIAL
                        AND N.NRLANCTONF    = IP.NRLANCTONF
                        AND F.CDFILIAL      = E.CDFILIAL
                        AND E.CDPAIS        = M.CDPAIS
                        AND E.SGESTADO      = M.SGESTADO
                        AND E.CDMUNICIPIO   = M.CDMUNICIPIO
                        AND EP.CDEMPRESA    = N.CDEMPRESA
                        AND EP.CDPAIS       = MU.CDPAIS
                        AND EP.SGESTADO     = MU.SGESTADO
                        AND EP.CDMUNICIPIO  = MU.CDMUNICIPIO
                        AND N.CDEMPRESA     = IM.CDEMPRESA
                        AND N.CDINSCMUNI    = IM.CDINSCMUNI
                        AND N.CDEMPRESA     = IE.CDEMPRESA
                        AND N.CDINSCESTA    = IE.CDINSCESTA
                        AND IF.CDIMPOSTO    = IT.CDIMPOSTO
                        AND IF.NRLANCTONF   = N.NRLANCTONF
                        AND IF.CDFILIAL     = N.CDFILIAL
                        AND IT.CDIMPOSTONFE = V_CDISS_RET     ) LOOP

        INSERT INTO ARQMAGNETICO
          (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
        VALUES
          (V_CONTADOR, C_NOTAS.DSGERAIS, P_NRSEQARQMAG);

        V_CONTADOR   := NVL(V_CONTADOR,0)+1;
        V_CONT_REG_2 := NVL(V_CONT_REG_2,0)+1;

        /* identificador de exportacao da nf */
        UPDATE NOTAFISCAL
           SET IDEXPNFESANAND = 'S'
         WHERE CDFILIAL       = C_NOTAS.CDFILIAL
           AND NRLANCTONF     = C_NOTAS.NRLANCTONF;


        /* 3 - DISCRIMINACAO DOS SERVICOS */
        V_SEQRTS := 1;

        FOR C_REGISTRO3 IN (SELECT ('3' || LPAD(NVL(NRNOTAFISC,'0'), 10, '0')) AS DSINICIAL,
                                   (LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(QTITEMPROD,2), '999999999990.00')), '.', ''), 15, 0) ||
                                    UPPER(RPAD(NVL(SUBSTR(DSPRODEMIS,1,250), ' '), 250,' '))                              ||
                                    LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(VRUNITITEM,2), '999999999990.00')), '.', ''), 15, 0) ||
                                    RPAD(NVL(REPLACE(LTRIM(TO_CHAR(ROUND(VRALIQITEM,2), '00.00')), '.', ''),'0'),4,'0')||
                                    ' ' ) AS DSFINAL
                              FROM (SELECT IT.NRLANCTONF, QTITEMPROD, DSPRODEMIS, VRUNITITEM, IP.CDFISCALIT, IP.VRALIQITEM,
                                           DECODE(IP.CDFISCALIT, '1', IP.VRALIQITEM, 0), IT.VRDESCITEM, IT.CDFILIAL, N.NRNOTAFISC
                                      FROM ITEMPRODUTO IT, ITEMPROIMPOS IP, NOTAFISCAL N, IMPOSTO IM
                                     WHERE IT.CDFILIAL      = N.CDFILIAL         AND
                                           IT.NRLANCTONF    = N.NRLANCTONF       AND
                                           IT.CDFILIAL      = IP.CDFILIAL        AND
                                           IT.NRLANCTONF    = IP.NRLANCTONF      AND
                                           IT.NRSEQITPROD   = IP.NRSEQITPROD     AND
                                           IT.NRLANCTONF    = C_NOTAS.NRLANCTONF AND
                                           IT.CDFILIAL      = C_NOTAS.CDFILIAL   AND
                                           IP.NRITPROIMPOS  = '1'                AND
                                           IM.CDIMPOSTONFE  = V_CDISS            AND
                                           IM.CDIMPOSTO     = IP.CDIMPOSTO         )
                            
                            UNION ALL       
                            SELECT ('3' || LPAD(NVL(NRNOTAFISC,'0'), 10, '0')) AS DSINICIAL,
                                   (LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(QTITEMPROD,2), '999999999990.00')), '.', ''), 15, 0) ||
                                    UPPER(RPAD(NVL(SUBSTR(DSPRODEMIS,1,250), ' '), 250,' '))                              ||
                                    LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(VRUNITITEM,2), '999999999990.00')), '.', ''), 15, 0) ||
                                    RPAD(NVL(REPLACE(LTRIM(TO_CHAR(ROUND(VRALIQITEM,2), '00.00')), '.', ''),'0'),4,'0')||
                                    ' ' ) AS DSFINAL
                              FROM (SELECT IT.NRLANCTONF, QTITEMPROD, DSPRODEMIS, VRUNITITEM, IP.CDFISCALIT, IP.VRALIQITEM,
                                           DECODE(IP.CDFISCALIT, '1', IP.VRALIQITEM, 0), IT.VRDESCITEM, IT.CDFILIAL, N.NRNOTAFISC
                                      FROM ITEMPRODUTO IT, ITEMPROIMPOS IP, NOTAFISCAL N, IMPOSTO IM
                                     WHERE IT.CDFILIAL      = N.CDFILIAL         AND
                                           IT.NRLANCTONF    = N.NRLANCTONF       AND
                                           IT.CDFILIAL      = IP.CDFILIAL        AND
                                           IT.NRLANCTONF    = IP.NRLANCTONF      AND
                                           IT.NRSEQITPROD   = IP.NRSEQITPROD     AND
                                           IT.NRLANCTONF    = C_NOTAS.NRLANCTONF AND
                                           IT.CDFILIAL      = C_NOTAS.CDFILIAL   AND
                                           IP.NRITPROIMPOS  = '1'                AND
                                           IM.CDIMPOSTONFE  = V_CDISS_RET        AND
                                           IM.CDIMPOSTO     = IP.CDIMPOSTO       )) LOOP

            INSERT INTO ARQMAGNETICO
               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
               (V_CONTADOR, C_REGISTRO3.DSINICIAL ||LPAD(TRIM(V_SEQRTS), 5,'0') || C_REGISTRO3.DSFINAL, P_NRSEQARQMAG);
            V_CONTADOR := NVL(V_CONTADOR,0)+1;

            V_SEQRTS := V_SEQRTS + 1;   /* identificador de sequencial de RTS */

        END LOOP;   /* LOOP C_REGISTRO3 */
   
     
        V_DESCONTO := 0;
        BEGIN
           SELECT  SUM(VRDESCITEM)
             INTO  V_DESCONTO
             FROM  ITEMPRODUTO IT
            WHERE  IT.NRLANCTONF  = C_NOTAS.NRLANCTONF AND
                   IT.CDFILIAL    = C_NOTAS.CDFILIAL;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_DESCONTO := 0;
        END;       
           
        IF V_DESCONTO > 0 THEN

           BEGIN
             SELECT  NRNOTAFISC
               INTO  V_NRNOTAFISC
               FROM  NOTAFISCAL
              WHERE  NRLANCTONF  = C_NOTAS.NRLANCTONF AND
                     CDFILIAL    = C_NOTAS.CDFILIAL;
           EXCEPTION
              WHEN NO_DATA_FOUND THEN
                 V_NRNOTAFISC := NULL;
           END; 

           V_REGDESC := '3' || LPAD(NVL(V_NRNOTAFISC,'0'), 10, '0') ||
                        LPAD(TRIM(V_SEQRTS), 5,'0') ||
                        '000000000000100' ||
                        RPAD('DESCONTO' , 250,' ') ||
                        LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(V_DESCONTO,2), '999999999990.00')), '.', ''), 15, 0) ||
                        '0000' ||
                        '1';

           INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
           VALUES
              (V_CONTADOR, V_REGDESC , P_NRSEQARQMAG);
           V_CONTADOR := NVL(V_CONTADOR,0)+1;

        END IF;   

     END LOOP;  /* LOOP C_NOTAS  */

   END LOOP;  /* LOOP C_REGNOTAS  */

   /* 4 - FIM DO ARQUIVO */
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     (V_CONTADOR, '4'||LPAD(NVL(V_CONT_REG_2,0), 15, '0'), P_NRSEQARQMAG);
END;
/