No Unix No SQL Loader Interfaces and Data Conversions

The classic Interface or Dataload routines take the form of a flat datafile being loaded into the database, then some PL/SQL processing to parse and validate the data before moving it (usually) into Open Interface tables.

The data file would usually need FTP'ing to a directory on the host, loading into the database with SQL*Loader, file copied and archived using Unix (.prog) and a concurrent program submitted to do the SQL processing.  These steps would probably be defined in a sequential request set.

With 11g its now alot easier and all of this can be done in one PL/SQL package using UTL_FILE, database directories and External tables.  No .prog. No Unix. No SQL Loader.


Basic steps are as follows.


Create the external table (use _FILE suffix for external tables)

create table xxap_invoice_file

(

BATCH_NUMBER                     VARCHAR(20),

BATCH_TYPE                      VARCHAR(20),

BATCH_DATE                      DATE,

VENDOR_NAME                        VARCHAR(240),

...

INVOICE_NUMBER                  VARCHAR(240)

)

organization external

( default directory xx_incoming_files

access parameters

( RECORDS DELIMITED BY NEWLINE

  FIELDS TERMINATED by ','

  OPTIONALLY ENCLOSED BY '"'

  MISSING FIELD VALUES ARE NULL

  ( BATCH_NUMBER,

    BATCH_TYPE,

    BATCH_DATE date 'DD-MM-YYYY',

    VENDOR_NAME,

     ...

    INVOICE_NUMBER

  )

)

location ('xx_data_file.csv')

);

The external table works on a fixed filename (location).  I suggest you copy a dated or numbered file at runtime.

     --  making copy of file as xx_data_file.csv to load

     UTL_FILE.FCOPY ( 

      src_location  => 'XX_INCOMING_FILES', 

      src_filename  => v_filename,  -- e.g. 'xx_data_file_01-JAN-2011.csv' 

      dest_location => 'XX_INCOMING_FILES', 

      dest_filename => 'xx_data_file.csv');

Now populate a custom interface table from the external table (adding a RUN_ID from a custom sequence)

INSERT INTO xxap_invoices

(run_id, batch_number, batch_type....)

SELECT xx_ap_invoices_s.nextval, batch_number...

FROM XXAP_INVOICES_FILE;

Perform custom validation and processing on the new data in XXAP_INVOICES and populate Open Interface tables.  Finally submit the open interface job using fnd_submit.

fnd_request.submit_request

More examples to follow.  Let me know if you've any suggestions or questions?