BEGIN
   DECLARE V_FILEHANDLE  UTL_FILE.FILE_TYPE;
BEGIN
   FOR CUR_FIL IN (
   SELECT F.CDFILIAL, LPAD(TO_CHAR(TO_NUMBER(F.CDFILIAL)),2,'0') AS CDEXPORTEMPR
   FROM FILIAL F, PARAMEMPRESA P
   WHERE F.CDEMPRESA = P.CDEMPRESA
   ORDER BY F.CDFILIAL) LOOP

      V_FILEHANDLE := UTL_FILE.FOPEN('EXPORA_DIR', 'ExpNFSCN'||NVL(CUR_FIL.CDEXPORTEMPR,CUR_FIL.CDFILIAL)||'.txt', 'w');

      FOR CUR_DADOS IN (

      SELECT 'Sky Chefs Argentina'||' '||
             LPAD(substr(to_char(NRNOTAFISC),1,10),10, '0')||' '||
             RPAD(NVL(SUBSTR(CDCTRLNUMENF,1,5), ' '),5)||'-'||
             LPAD(substr(to_char(NRFORMULARNF),1,8),8, '0')||' '||
             RPAD(NVL(NVL(EF.CDCLIFILIEXP,CDCLIEEXPORT), ' '), 7)||
             RPAD(NVL(NVL(EF.CDEXPFILCLIE,CDFILIEXPORT), ' '), 2)||
             LPAD(NVL(TO_CHAR(DTEMISSAO, 'DD-MON-YY'),' '),9, ' ')||
             LPAD(NVL(TO_CHAR(DTATUAVENREC, 'DD-MON-YY'), ' '),9, ' ')||
             LPAD(TO_CHAR(DECODE(IDNACCIA, '1', 0, NVL(VRINDICE,0)),'9990.0000'),10)||
             ' '||
             LPAD(TO_CHAR(TRUNC(DTATUAVENREC - DTEMISSAO)),3,'0')||
             '  '||
             SUBSTR(NVL(DP.NRDUPLICATA, '      '), 5, 6)||
             RPAD(SUBSTR(NVL(NF.CDEXTIPOOPER, ' '),1,3),3)||                             --Categoria de transaccion legal
             RPAD(SUBSTR(NVL(NF.CDSERIE, ' '),1,1),1)||' '||                             --Carta de Transaccion
             'EZE'||
             LPAD(TO_CHAR(NVL(VRORIGREC,0),'00000000000000.00'),18)||
             'PES'||
             '1' AS DSARQUIVO
      FROM (SELECT N.CDFILIAL,   N.NRLANCTONF, N.DTEMISSAO,  N.CDEMPRESA,
                   N.NRNOTAFISC, P.CDTPINDICE, P.IDPERVALOR, CDINDICE,
                   N.CDCLIENTE, N.CDFILIEMIT, T.CDEXTIPOOPER, N.CDSERIE, F.NRFORMULARNF, F.CDCTRLNUMENF
            FROM NOTAFISCAL N, PARAMINTEGRA P, TIPOOPERAC T,
                (SELECT CDFILIAL, NRLANCTONF, MAX(NRFORMULARNF) AS NRFORMULARNF, CDCTRLNUMENF
                 FROM FORMULARIONF
                 GROUP BY CDFILIAL, NRLANCTONF, CDCTRLNUMENF
                ) F
            WHERE (N.CDFILIEMIT    = CUR_FIL.CDFILIAL)              AND
                  (N.IDSTATUS      IN ('A','E'))                    AND
                  (N.DTEXPORTACAO IS NULL)                          AND
                  (N.DTEMISSAO    <= SYSDATE)                       AND
                  (N.IDENTRSAIDOP  = 'S')                           AND
                  (N.CDTIPOOPER   IN ('01','17','18'))              AND
                  (T.IDENTRSAIDOP = N.IDENTRSAIDOP)                 AND
                  (T.CDTIPOOPER   = N.CDTIPOOPER)                   AND
                  (N.CDFILIAL     = F.CDFILIAL)                     AND
                  (N.NRLANCTONF   = F.NRLANCTONF)) NF,
            CONTARECEBER CR, MOVINDICE MI, EXPORTCLIE EC, EXPFILCLIE EF, COMPANHIA CP, PARAMEMPRESA PR, DUPLICATA DP
      WHERE (CR.CDFILIAL        = NF.CDFILIAL)    AND
            (CR.NRLANCTONF      = NF.NRLANCTONF)  AND
            (CR.NRLANCTODUP IS NOT NULL)          AND
            (MI.CDTPINDICE(+)   = NF.CDTPINDICE)  AND
            (MI.IDPERVALOR(+)   = NF.IDPERVALOR)  AND
            (MI.CDINDICE(+)     = NF.CDINDICE)    AND
            (MI.DTREFIND(+)     = NF.DTEMISSAO)   AND
            (EC.CDCLIENTE(+)    = NF.CDCLIENTE)   AND
            (NF.CDCLIENTE       = EF.CDCLIENTE(+))AND
            (NF.CDFILIEMIT      = EF.CDFILIAL(+)) AND
            (CP.CDCLIENTE(+)    = NF.CDCLIENTE)   AND
            (NF.CDEMPRESA       = PR.CDEMPRESA(+) AND
            (CR.CDFILIAL        = DP.CDFILIAL)    AND
            (CR.NRLANCTODUP     = DP.NRLANCTODUP))
      ORDER BY NF.CDFILIAL, NF.NRLANCTONF) LOOP

         UTL_FILE.PUT(V_FILEHANDLE, CUR_DADOS.DSARQUIVO||CHR(13)||CHR(10));
         UTL_FILE.FFLUSH(V_FILEHANDLE);
      END LOOP;

      UTL_FILE.FCLOSE(V_FILEHANDLE);
   END LOOP;
END;
END;
/
