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

      FOR CUR_DADOS IN (
      SELECT TIPOTRAN||
             RPAD(DECODE(IDTPIJURFORN, 'J', '0' || SUBSTR(NRINSJURFORN, 1, 8), SUBSTR(NRINSJURFORN, 1, 9)),9)||
             RPAD(DECODE(IDTPIJURFORN, 'J', SUBSTR(NRINSJURFORN, 9, 4), '0001'), 4)||
             RPAD(DECODE(IDTPIJURFORN, 'O', '  ', SUBSTR(NRINSJURFORN, LENGTH(NRINSJURFORN) - 1, 2)),2)||
             RPAD(SUBSTR(NVL(DSENDEFORN, ' '), 1, 35),35)||
             RPAD(SUBSTR(NVL(NMBAIRFORN, ' '), 1, 35),70)||
             RPAD(SUBSTR(NVL(NMMUNICIPIO, ' '), 1, 25),25)||
             RPAD(SUBSTR(NVL(NMESTADO, ' '), 1, 25),25)||
             RPAD(SUBSTR(NVL(NRCEPFORN, ' '), 1, 20),20)||
             RPAD('ARG', 25)||
             RPAD(NVL(NRTELEFORN, ' '), 16)||
             RPAD(NVL(NRFAXFORN, ' '), 16)||
             RPAD(NVL(NRTELEXFORN, ' '), 12)||
             DECODE(IDTPFRETFORN, 'C', 'CIF', NULL, '   ', 'FOB')||
             DECODE(IDTPFRETFORN, 'C', 'CIF', 'F', 'FOB', 'T', 'FOT', 'R', 'FOR', '   ')||
             DECODE(IDTPIJURFORN, 'F', '1 ', 'J', '2 ', '99')||
             RPAD(NVL(NRINSESTFORN, ' '), 15)||
             RPAD(NVL(NRINSMUNFORN, ' '), 15)||
             'EQI' AS DSARQUIVO
      FROM (SELECT DISTINCT 'I' AS TIPOTRAN,         FO.IDTPIJURFORN, FO.NRINSJURFORN,
                   ET.NMESTADO,     MU.NMMUNICIPIO,  EF.DSENDEFORN,   EF.NMBAIRFORN,
                   EF.NRCEPFORN,    EF.NRTELEFORN,   EF.NRTELEXFORN,  EF.NRFAXFORN,
                   FO.IDTPFRETFORN, FO.NRINSESTFORN, FO.NRINSMUNFORN
            FROM FORNECEDOR FO, NOTAFISCAL NF, ENDEFORN EF, ESTADO ET, MUNICIPIO MU
            WHERE (NF.CDFILIRECE  = CUR_FIL.CDFILIAL)                    AND
                  (NF.IDSTATUS = 'A')                                    AND
                  (NF.IDENTRSAIDOP = 'E')                                AND
                  (NF.DTENTRSAID <= SYSDATE)                             AND
                  (NF.DTEXPORTACAO IS NULL)                              AND
                  ((NF.IDORIGDESTNF IS NULL) OR (NF.IDORIGDESTNF = 'F')) AND
                  (NF.CDFORNECED      = FO.CDFORNECED)                   AND
                  (EF.CDFORNECED(+)   = FO.CDFORNECED)                   AND
                  (EF.IDTPENDEFORN(+) = 'P')                             AND
                  (ET.CDPAIS(+)       = EF.CDPAIS)                       AND
                  (ET.SGESTADO(+)     = EF.SGESTADO)                     AND
                  (MU.CDPAIS(+)       = EF.CDPAIS)                       AND
                  (MU.SGESTADO(+)     = EF.SGESTADO)                     AND
                  (MU.CDMUNICIPIO(+)  = EF.CDMUNICIPIO)
            UNION
            SELECT DISTINCT 'A' AS TIPOTRAN,         FO.IDTPIJURFORN, FO.NRINSJURFORN,
                   ET.NMESTADO,     MU.NMMUNICIPIO,  EF.DSENDEFORN,   EF.NMBAIRFORN,
                   EF.NRCEPFORN,    EF.NRTELEFORN,   EF.NRTELEXFORN,  EF.NRFAXFORN,
                   FO.IDTPFRETFORN, FO.NRINSESTFORN, FO.NRINSMUNFORN
            FROM FORNECEDOR FO, NOTAFISCAL NF, ENDEFORN EF, ESTADO ET, MUNICIPIO MU
            WHERE (NF.CDFILIRECE = CUR_FIL.CDFILIAL)                     AND
                  (NF.IDSTATUS = 'A')                                    AND
                  (NF.IDENTRSAIDOP = 'E')                                AND
                  (NF.DTENTRSAID <= SYSDATE)                             AND
                  (NF.DTEXPORTACAO IS NULL)                              AND
                  ((NF.IDORIGDESTNF IS NULL) OR (NF.IDORIGDESTNF = 'F')) AND
                  (NF.CDFORNECED      = FO.CDFORNECED)                   AND
                  (EF.CDFORNECED(+)   = FO.CDFORNECED)                   AND
                  (EF.IDTPENDEFORN(+) = 'P')                             AND
                  (ET.CDPAIS(+)       = EF.CDPAIS)                       AND
                  (ET.SGESTADO(+)     = EF.SGESTADO)                     AND
                  (MU.CDPAIS(+)       = EF.CDPAIS)                       AND
                  (MU.SGESTADO(+)     = EF.SGESTADO)                     AND
                  (MU.CDMUNICIPIO(+)  = EF.CDMUNICIPIO))
      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;
/


