Tuesday, August 10, 2010

AP Invoices conversion process

The process for AP invoice program is as follows:

Excel Sheet -> Data file -> SQL Loader (Control file) -> Staging Tables -> Run Validation program -> Interface Tables -> Run the Payables Open Invoice Import Program -> Base Tables

Staging tables:
Create a custom staging table XX_AP_INVOICE_INTERFACE, XX_AP_INVOICE_LINES_INTERFACE which should be same as seeded interface table AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE.

Interface Tables:
1. AP_INVOICES_INTERFACE
2. AP_INVOICE_LINES_INTERFACE

Base Tables:
1. AP_INVOICES_ALL
2. AP_INVOICE_DISTRIBUTIONS_ALL
3. AP_PAYMENT_SCHEDULES_ALL

Process Flow:
-----------------
1. Excel sheet template is prepared which contains all the columns in AP_INVOICES_INTERFACE and AP_INVOICE_LINES table.
2. Convert the Excel Sheet which contains data to be interfaced given by the user into CSV file format.
3. Format the CSV file so that it contains only the relevant data separated with commas.
4. Rename the CSV into DAT file.
5. Prepare the Control File.
6. Run SQL Loader Script.
7. Check the Log file for any errors. In case of any errors rectify it and re-run the SQL Loader Script.
8. Run the PL/SQL Program to validate staging table data and load to Interface tables.
9. Run the Payables Open Invoice Import Program.
10. Check for the status in the Interface Table.
11. Query for the data in the front-end.

1 comment:

kanna said...

Hi Sairam,

Thanks for all the valuable posts on your blog, it really helps many technical developers.

I have a question on this AP Invoices conversion process, cant we use Data Loader to load instead of APIs for AP Invoices?

Thanks -
Kanna