Using SQL*Loader to Populate the Credit Card Transaction InterfaceTables
You can create a SQL*Loader program that uses a flat file containing the credit card transaction details you want to transfer into the AP_EXPENSE_FEED_LINES table. For detailed information on this table, refer to the Oracle Payables Applications Technical Reference Manual.
To use SQL*Loader to populate the AP_EXPENSE_FEED_LINES table:
1. For each record, you must populate the following required columns in AP_EXPENSE_FEED_LINES:
- CARD_PROGRAM_ID. The unique identifier of the card program for which you are loading transactions.
- CARD_NUMBER. The number of the credit card that was used for the transaction. This should be contained in the transaction file you receive from your card issuer.
- FEED_LINE_ID. Use the sequence AP_EXPENSE_FEED_LINES_S.NEXTVAL to populate this column for each transaction.
- AMOUNT. The amount of the transaction. This should be contained in the transaction file you receive from your card issuer.
Attention: You must load the amount as either a positive or negative number. For example, if you are loading a credit, load the transaction amount as a negative number. Also, the precision of all amounts loaded should be to the same precision you define for the currency of the credit card program.
- POSTED_DATE. The date the merchant posted the transaction to the credit card issuer. This should be contained in the transaction file you receive from your card issuer.
2. Set the CREATE_DISTRIBUTION_FLAG to Y. You must perform this step to have the Credit Card Transaction Validation and Exception Report create default accounting distributions for the transactions you load.
3. Optionally populate the Standard Who Columns for the AP_EXPENSE_FEED_LINES table if you would like to retain this information for the transactions you load.
4. POSTED_CURRENCY_CODE. The posted currency code of the transaction should be the same as the currency of the credit card program.
5. ORG_ID: If you are running Payables in a multi-org environment, you may want to populate this column when you load transactions.
6. 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 credit card transaction flat file you use. Your control file must populate AP_EXPENSE_FEED_LINES as indicated in the Oracle Payables Applications Technical Reference Manual. See also: SQL*Loader (ORACLE8 Server Utilities Guide).
7. Use the SQL*Loader program you created to load your credit card transaction information from your flat file into the interface table. Indicate your control file as the SQL*Loader control file, your credit card transaction 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.