Using SQL*Loader to Populate the Payables Open InterfaceTables

You can create a SQL*Loader program that produces a flat file containing the invoice details you want to bring into Payables. For detailed information on the tables, refer to Open Interface Import Table Descriptions.

To use SQL*Loader to populate the Payables Open Interface tables:

1. For each invoice, the program should identify the supplier by providing a value for at least one of the following columns: VENDOR_ID, VENDOR_NUM, VENDOR_SITE_ID, or PO_NUMBER. In addition, for each record, you must populate the following required columns in AP_INVOICES_INTERFACE:
INVOICE_ID
INVOICE_NUM
INVOICE_AMOUNT
SOURCE. Make certain that you specify a Source type QuickCode exactly as you entered it in the Payables QuickCodes window.
For each invoice line, include the following values to populate the columns in the AP_INVOICE_LINES_INTERFACE:
INVOICE_ID
LINE_TYPE_LOOKUP_CODE
AMOUNT
In addition, if you are importing foreign currency invoices, include information to populate the following columns in AP_INVOICES_INTERFACE:
EXCHANGE_RATE_TYPE
EXCHANGE_RATE (if you use User-defined rate type)
If this exchange rate information is the same for all invoices and invoice distributions you import, you can include this information in your SQL*Loader control file, instead of entering it separately for each invoice.
Finally, ensure that your invoice flat file has the appropriate information to populate AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE as indicated in the preceding table descriptions. If a value is not required for a column, you may leave the column empty.

2. Create a SQL*Loader control file.
Read the SQL*Loader documentation to learn how to write a SQL*Loader control file. The file you write will vary greatly depending on the nature and format of the invoice flat file you use. Your control file must populate AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE as indicated in the previous table descriptions. See also: SQL*Loader (ORACLE8 Server Utilities Guide).
Code your control file to populate the SOURCE column in AP_INVOICES_INTERFACE with the Source code exactly as you defined it in the QuickCodes window.
If all your invoices are in the same currency, you can hard code the value for DEFAULT_CURRENCY_CODE in AP_INVOICES_INTERFACE and CURRENCY_CODE in AP_INVOICE_LINES_INTERFACE in your control file.

3. Use the SQL*Loader program you created to load your invoice information from your flat file into the interface tables. Indicate your control file as the SQL*Loader control file, your invoice flat file as the SQL*Loader input file, and your Payables SQL*Plus username and password as the database sign on. For more information see: Oracle8 Server Utilities Guide.

4. Use the Open Interface Invoices window to review and edit the data in the Payables Open Interface tables.