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?