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', 'ExpNFEDC'||NVL(CUR_FIL.CDEXPORTEMPR,CUR_FIL.CDFILIAL)||'.txt', 'w');

      FOR CUR_DADOS IN (
         SELECT SUBSTR(TO_CHAR(NVL(DTEMISSAO, DTENTRSAID), 'DD-MON-YY')||
                       LPAD(TO_CHAR(NVL(VRORIGPAG,0),'999999999990.00'),16)||
                       RPAD(SUBSTR(NVL(NMCOMPRZSFOR, ' '),1,5),5)||' '||
                       RPAD(SUBSTR(NVL(CDSERIEENTR, ' '),2,4),4)||
                       LPAD(substr(to_char(NRNOTAFISC),1,8),8, '0')||' '||
                       TO_CHAR(DTENTRSAID, 'DD-MON-YY')||
                       TO_CHAR(DTENTRSAID, 'DD-MON-YY')||
                       TO_CHAR(DTORIGVENPAG, 'DD-MON-YY')||' '||
                       RPAD(DECODE(SUBSTR(T.CDEXTIPOOPER,1,2),'FC','STANDARD',
            			   (DECODE(SUBSTR(T.CDEXTIPOOPER,1,2),'NC','CREDIT',
			               (DECODE(SUBSTR(T.CDEXTIPOOPER,1,2),'ND','DEBIT',' '))))),10)||
                       RPAD(SUBSTR(NVL(F.CDEXFORNECED, ' '),2,20),20)||                               --Descricion
                       RPAD(SUBSTR(NVL(T.CDEXTIPOOPER, ' '),1,10),10)||                               --Categoria de transaccion legal
                       RPAD(SUBSTR(NVL(CDSERIEENTR, ' '),1,1),1)||                                    --Carta de Transaccion
                       RPAD(DECODE(SUBSTR(T.CDEXTIPOOPER,1,2)||SUBSTR(CDSERIEENTR,1,1), 'FCA', '01',  --Tipo de Transaccion
                            DECODE(SUBSTR(T.CDEXTIPOOPER,1,2)||SUBSTR(CDSERIEENTR,1,1), 'FCB', '06',
                            DECODE(SUBSTR(T.CDEXTIPOOPER,1,2)||SUBSTR(CDSERIEENTR,1,1), 'FCC', '11',
                            DECODE(SUBSTR(T.CDEXTIPOOPER,1,2)||SUBSTR(CDSERIEENTR,1,1), 'FCE', '11',
                            DECODE(SUBSTR(T.CDEXTIPOOPER,1,2)||SUBSTR(CDSERIEENTR,1,1), 'FCM', '51',
                            DECODE(SUBSTR(T.CDEXTIPOOPER,1,2)||SUBSTR(CDSERIEENTR,1,1), 'FCX', '39',
                            DECODE(SUBSTR(T.CDEXTIPOOPER,1,2)||SUBSTR(CDSERIEENTR,1,1), 'NCA', '03',
                            DECODE(SUBSTR(T.CDEXTIPOOPER,1,2)||SUBSTR(CDSERIEENTR,1,1), 'NCB', '08',
                            DECODE(SUBSTR(T.CDEXTIPOOPER,1,2)||SUBSTR(CDSERIEENTR,1,1), 'NCC', '13',
                            DECODE(SUBSTR(T.CDEXTIPOOPER,1,2)||SUBSTR(CDSERIEENTR,1,1), 'NCE', '13',
                            DECODE(SUBSTR(T.CDEXTIPOOPER,1,2)||SUBSTR(CDSERIEENTR,1,1), 'NCM', '53',
                            DECODE(SUBSTR(T.CDEXTIPOOPER,1,2)||SUBSTR(CDSERIEENTR,1,1), 'NCX', '39',
                            DECODE(SUBSTR(T.CDEXTIPOOPER,1,2)||SUBSTR(CDSERIEENTR,1,1), 'NDA', '02',
                            DECODE(SUBSTR(T.CDEXTIPOOPER,1,2)||SUBSTR(CDSERIEENTR,1,1), 'NDB', '07',
                            DECODE(SUBSTR(T.CDEXTIPOOPER,1,2)||SUBSTR(CDSERIEENTR,1,1), 'NDC', '12',
                            DECODE(SUBSTR(T.CDEXTIPOOPER,1,2)||SUBSTR(CDSERIEENTR,1,1), 'NDE', '12', ' ')))))))))))))))),10)
                       , 1, 150) AS DSARQUIVO
         FROM (SELECT DISTINCT
                      NF.CDFILIAL,    NF.NRLANCTONF,   NF.DTEMISSAO,  NF.DTENTRSAID,
                      CP.VRORIGPAG,   PR.CDEXPORTEMPR, NF.IDTPIJURNF, NF.NRINSJURNF,
                      NF.CDSERIEENTR, CP.DTORIGVENPAG, NF.NRNOTAFISC,
                      ' ' AS IDMAISVENC, PR.CDCENTLUCREN, '' AS NRSEQCOUNT, PF.CDEXFILIAL,
                      NF.CDFORNECED, NF.CDCLIENTE, NF.IDENTRSAIDOP, NF.CDTIPOOPER
               FROM NOTAFISCAL NF, CONTAPAGAR CP, PARAMEMPRESA PR, PARAMFILIAL PF
               WHERE (NF.CDFILIRECE = CUR_FIL.CDFILIAL)                     AND
                     (PF.CDFILIAL   = CUR_FIL.CDFILIAL)                     AND
                     (NF.IDSTATUS   = 'A')                                  AND
                     (NF.IDENTRSAIDOP = 'E')                                AND
                     (NF.CDTIPOOPER <> '10')                                AND
                     (NF.DTEXPORTACAO IS NULL)                              AND
                     (NF.DTENTRSAID <= SYSDATE)                             AND
                     ((NF.IDORIGDESTNF IS NULL) OR (NF.IDORIGDESTNF = 'F')) AND
                     (CP.CDFILIAL   = NF.CDFILIAL)                          AND
                     (CP.NRLANCTONF = NF.NRLANCTONF)                        AND
                     (NF.CDEMPRESA  = PR.CDEMPRESA(+))                      AND
                     (1             = (SELECT COUNT(*) FROM CONTAPAGAR CP1
                                       WHERE (CP1.CDFILIAL   = CP.CDFILIAL) AND
                                             (CP1.NRLANCTONF = CP.NRLANCTONF)))
               UNION
               SELECT DISTINCT
                      NF.CDFILIAL,    NF.NRLANCTONF,   NF.DTEMISSAO,   NF.DTENTRSAID,
                      CP.VRORIGPAG,   PR.CDEXPORTEMPR, NF.IDTPIJURNF,  NF.NRINSJURNF,
                      NF.CDSERIEENTR, CP.DTORIGVENPAG, NF.NRNOTAFISC,
                      '*' AS IDMAISVENC, PR.CDCENTLUCREN, NRSEQCOUNT, PF.CDEXFILIAL,
                      NF.CDFORNECED, NF.CDCLIENTE, NF.IDENTRSAIDOP, NF.CDTIPOOPER
               FROM NOTAFISCAL NF, CONTAPAGAR CP, PARAMEMPRESA PR,USR_CONTAPAGAR UC,
                    PARAMFILIAL PF,
                    (SELECT CP1.CDFILIAL, CP1.NRLANCTONF, COUNT(*) AS NUMEVENC,
                            SUM(CP1.VRORIGPAG) AS VRNOTAFISC
                     FROM NOTAFISCAL NF1, CONTAPAGAR CP1
                     WHERE (NF1.CDFILIRECE   = CUR_FIL.CDFILIAL)                    AND
                           (NF1.IDSTATUS     = 'A')                                 AND
                           (NF1.DTENTRSAID <= SYSDATE)                              AND
                           (NF1.DTEXPORTACAO IS NULL )                              AND
                           ((NF1.IDORIGDESTNF IS NULL) OR (NF1.IDORIGDESTNF = 'F')) AND
                           (CP1.CDFILIAL     = NF1.CDFILIAL)                        AND
                           (CP1.NRLANCTONF   = NF1.NRLANCTONF)
                     GROUP BY CP1.CDFILIAL, CP1.NRLANCTONF) NV
               WHERE (NF.CDFILIRECE = CUR_FIL.CDFILIAL)                     AND
                     (PF.CDFILIAL   = CUR_FIL.CDFILIAL)                     AND
                     (NF.IDSTATUS   = 'A')                                  AND
                     (NF.IDENTRSAIDOP = 'E')                                AND
                     (NF.CDTIPOOPER <> '10')                                AND
                     (NF.DTEXPORTACAO IS NULL)                              AND
                     (NF.DTENTRSAID <= SYSDATE)                             AND
                     ((NF.IDORIGDESTNF IS NULL) OR (NF.IDORIGDESTNF = 'F')) AND
                     (CP.CDFILIAL   = NF.CDFILIAL)                          AND
                     (CP.NRLANCTONF = NF.NRLANCTONF)                        AND
                     (NF.CDEMPRESA  = PR.CDEMPRESA(+))                      AND
                     (CP.CDFILIAL        = UC.CDFILIAL(+))                  AND
                     (CP.NRLANCTONF      = UC.NRLANCTONF(+))                AND
                     (CP.NRSEQVENPAG     = UC.NRSEQVENPAG(+))               AND
                     (1             < (SELECT COUNT(*) AS FROM CONTAPAGAR CP1
                                       WHERE (CP1.CDFILIAL   = CP.CDFILIAL) AND
                                             (CP1.NRLANCTONF = CP.NRLANCTONF)))
               UNION
               SELECT DISTINCT
                      NF.CDFILIAL,    NF.NRLANCTONF,    NF.DTEMISSAO,     NF.DTENTRSAID,
                      (0 - NF.VRNOTAFISC) AS VRORIGREC, PR.CDEXPORTEMPR,  NF.IDTPIJURNF,
                      NF.NRINSJURNF,  NF.CDSERIE,       (NF.DTEMISSAO)  AS DTORIGVENREC,
                      NF.NRNOTAFISC, ' ' AS IDMAISVENC,
                      PR.CDCENTLUCREN, '' AS NRSEQCOUNT, PF.CDEXFILIAL,
                      NF.CDFORNECED, NF.CDCLIENTE, NF.IDENTRSAIDOP, NF.CDTIPOOPER
               FROM NOTAFISCAL NF, PARAMEMPRESA PR, PARAMFILIAL PF
               WHERE (NF.CDFILIEMIT = CUR_FIL.CDFILIAL)                     AND
                     (PF.CDFILIAL   = CUR_FIL.CDFILIAL)                     AND
                     (NF.IDSTATUS IN ('A','E'))                             AND
                     (NF.CDTIPOOPER = '06')                                 AND
                     (NF.DTEXPORTACAO IS NULL)                              AND
                     (NF.DTEMISSAO <= SYSDATE)                              AND
                     (NF.CDEMPRESA  = PR.CDEMPRESA(+))                      AND
                     (NF.IDENTRSAIDOP = 'S')                                AND
                     (NF.IDORIGDESTNF = 'F')) A,
            FORNECEDOR F, EXPFILCLIE C, TIPOOPERAC T
         WHERE
            (A.CDFORNECED   = F.CDFORNECED (+)) AND
            (A.CDFILIAL     = C.CDFILIAL   (+)) AND
            (A.CDCLIENTE    = C.CDCLIENTE  (+)) AND
            (T.IDENTRSAIDOP = A.IDENTRSAIDOP)   AND
            (T.CDTIPOOPER   = A.CDTIPOOPER)
         ORDER BY A.CDFILIAL, A.NRLANCTONF, A.NRSEQCOUNT) 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;
/
