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

      FOR CUR_DADOS IN (
      SELECT TIPOTRAN||
             RPAD(NMRAZSOCFORN, 40)||
             RPAD(DECODE(IDTPIJURFORN, 'J', '0' || SUBSTR(NRINSJURFORN, 1, 8), SUBSTR(NRINSJURFORN, 1, 9)),9)||
             TO_CHAR(DTCADAFORN, 'DD-MON-YY')||'Y' AS DSARQUIVO
      FROM (SELECT DISTINCT 'I' AS TIPOTRAN, IDTPIJURFORN, NRINSJURFORN, NMRAZSOCFORN, DTCADAFORN
            FROM FORNECEDOR FO, NOTAFISCAL NF
            WHERE (NF.CDFILIRECE = CUR_FIL.CDFILIAL)                     AND
                  (NF.IDENTRSAIDOP = 'E')				                      AND
                  (NF.IDSTATUS = 'A')                                    AND
                  (NF.DTENTRSAID <= SYSDATE)                             AND
                  (NF.DTEXPORTACAO IS NULL)                              AND
                  ((NF.IDORIGDESTNF IS NULL) OR (NF.IDORIGDESTNF = 'F')) AND
                  (NF.CDFORNECED = FO.CDFORNECED)
            UNION
            SELECT DISTINCT 'A' AS TIPOTRAN, IDTPIJURFORN, NRINSJURFORN, NMRAZSOCFORN, DTCADAFORN
            FROM FORNECEDOR FO, NOTAFISCAL NF
            WHERE (NF.CDFILIRECE = CUR_FIL.CDFILIAL)                     AND
                  (NF.IDENTRSAIDOP = 'E')				                      AND
                  (NF.IDSTATUS = 'A')                                    AND
                  (NF.DTENTRSAID <= SYSDATE)                             AND
                  (NF.DTEXPORTACAO IS NULL)                              AND
                  ((NF.IDORIGDESTNF IS NULL) OR (NF.IDORIGDESTNF = 'F')) AND
                  (NF.CDFORNECED = FO.CDFORNECED))
      ORDER BY IDTPIJURFORN, NRINSJURFORN, TIPOTRAN DESC) 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;
/


