Payables Open Interface Import

To load large numbers of invoices into Oracle Payables from external systems Data from Source Systems must be populated (eg: from oracle form: Invoices => Entry => Open Interface Invoices, which writes directly) to Interface tables
Submit the Payables Open Interface Import concurrent program which inserts data from interface tables to AP Base Tables (Invoice & Payments)
 
Source Systems:

    1. Invoice Gateway: invoice records that you entered in the Quick Invoices window
    2. Oracle e-commerce gate way:EDI (Electronic data Interchange) invoices
    3. Legacy Systems: Using SQL*Loader OR BPEL Interfaces can load invoices
    4. Credit Card invoices: (throu' Flat file) & XML invoices
    5. Invoices that your suppliers entered and submitted online via Oracle iSupplier Portal
    6. Oracle Property Manager: Lease Invoices    
    7. Oracle Assets: Lease Payments

EDI(Electronic data Interchange): 
     Data files are exchanged in a standard format electronically between Trading Partners
     Once Data Files are defined, EDI Translators interface application data to Interface Tables

Interface Tables:
   AP_INVOICES_INTERFACE
   AP_INVOICE_LINES_INTERFACE
   AP Base Tables:
   AP_INVOICES_ALL
   AP_INVOICE_DISTRIBUTIONS_ALL
   & Payments Tables
 
Step1: MINIMUM REQUIREMENTS
Required fields in the interface tables that must be populated in order for the import to complete successfully. Oracle Payable provides a form that writes directly to the interface tables manually (Invoices => Entry => Open Interface Invoices)
The minimum amount of data that is required for populating AP_INVOICES_INTERFACE:
     1. invoice_id
     2. invoice_num
     3. invoice_amount
     4. source
     5. supplier information
1. INVOICE_ID: The invoice_id will be generated by the application if the invoice is entered manually through the form. This invoice_id is the primary key for the  AP_INVOICES_INTERFACE table and will not be imported into the AP_INVOICES_ALL table.
2. INVOICE_NUM: The invoice_num must be unique for the particular supplier site associated with the invoice
3. INVOICE_AMOUNT: The invoice_amount is simply the total dollar amount of the invoice.
4. SOURCE : When submitting the Open Interface Import concurrent process one required
parameter will be “Source.” The import program will import all of the records with the same source field as entered in the source parameter. Sources can be defined in the Quickcodes form (Setup => Quick codes => Payables)
If the Source field is not populated or is populated with data that does not match the parameter when submitting the concurrent request, the import will not process any data and the Payables Open Interface Audit Report will read “No data exists for this report.”
5. SUPPLIER INFORMATION
There are options for entering supplier information. Some form of the following must be entered:
     a. vendor_id (supplier id)
     b. vendor_num (supplier num)
     c. vendor_site_id (supplier_site_id)
     d. po_number (if matching to a po)
     e. vendor_site_code (supplier_site_code)
The fields above are not actually required fields (ie. not null), but vendor and vendor site information
is necessary. Otherwise, when the import process is run the invoice will be rejected.
AP_INVOICES_LINES_INTERFACE
The next step is to populate the AP_INVOICE_LINES_INTERFACE table with the invoice distributions.
The minimum amount of data that is required is:
     1. invoice_id
     2. line_type_lookup_code (line type)
     3. Amount
     4. distribution account
1. INVOICE_ID: The invoice id for each distribution must correspond to one invoice_id in the
AP_INVOICES_INTERFACE table as there is a one to many relationship between
the AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE tables.
2. LINE_TYPE_LOOKUP_CODE: The line_type_lookup_code is the distribution line type,for example
“ITEM.”
3. AMOUNT: The amount is simply the dollar amount of the distribution line. The sum of the
distribution amounts for one invoice must equal the invoice amount in AP_INVOICES_INTERFACE. If
not, the invoice will be put on hold when approval is run in Payables.
4. DISTRIBUTION ACCOUNT: For the distribution account one of the following must be used:
     a. dist_code_combination_id
     b. dist_code_concatenated
     c. distribution set
     d. po distributions from po match
 
Step 2:
Submit the Payables Open Interface Import concurrent program. The parameter chosen for “Source” will determine which records in the interface tables are imported. The source chosen in the parameters must match the source in the tables for the records to be imported.
After the concurrent request completes, imported invoices are inserted into AP_INVOICES_ALL and AP_INVOICE_DISTRIBUTIONS_ALL. The successfully imported invoices in the interface table will then have a status of “PROCESSED” and the rejected invoices “REJECTED.”
There is a report that is run when the import process is submitted. There are two sections to the
report:
1. The Payables Open Interface Audit Report: lists the invoices that were successfully imported.
2. The Payables Open Interface Rejections Report: list the rejected invoices and the reason
why they are rejected. We can correct the rejected invoices and re-submit the import.
Resubmitting the import will not import invoices in the interface table that have already
been imported.
 
Step 3:
Once all invoices are imported, submit the Payables Open Interface Purge. This program will delete
records from the interface tables for the source and optionally group entered. The group field in the
interface table allows you to import and purge subsets of records with the same source.
 
Common Issues:
The most common issue in support is “no data exists for this report” in both the Audit and Rejections reports. There are a few things to check:
1. Does the source field in the AP_INVOICES_INTERFACE table match exactly the source entered in the parameters screen when submitting the concurrent process?
2. Is the concurrent process being submitted the “Payables Open Interface Import?” Many times the “Payables Invoice Import” is mistakenly run instead.
3. Is the voucher_num field populated in the AP_INVOICES_INTERFACE table? Support has seen some instances where invoices are rejected because the voucher_num field in the interface table is a duplicate of one in the AP_INVOICES_ALL table. Most of the time the resulting rejected invoice will show on the Rejections report, but sometimes it does not. This can cause invoices in the interface table to have a status of “REJECTED”, but the report still says, “no data exists.” This can be fixed by running the latest version of apiiseed.sql. This script seeds the rejection codes for the report.
4. Are Invoice Batch Controls turned on in Payables Options? If so a batch name must be entered in the parameters screen when submitting the Payables Open Interface Import concurrent request.
 
Additional Issues
Most other problems can be diagnosed by viewing the rejections report. This is the second page after clicking “Report” in the concurrent requests screen and will list the reason why invoices are rejected.