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)
The external table works on a fixed filename (location). I suggest you copy a dated or numbered file at runtime.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_filesaccess parameters( RECORDS DELIMITED BY NEWLINEFIELDS 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'));
-- making copy of file as xx_data_file.csv to load
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)
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.INSERT INTO xxap_invoices(run_id, batch_number, batch_type....)SELECT xx_ap_invoices_s.nextval, batch_number...FROM XXAP_INVOICES_FILE;
More examples to follow. Let me know if you've any suggestions or questions?fnd_request.submit_request