Key Tables for Oracle Accounts Payable

Key Tables for Oracle Accounts Payable

Invoice Work Flow:

Ø  ERS invoice (Pay on receipt) or PO matching invoice

Ø  Invoice validation

Ø  Invoice Approval

Ø  Create Accounting

Ø  Payment

Ø  Create Accounting 

Ø  Transfer to GL (in 11i , Payables transfer to GL program)

Ø  Journal Import

Ø  GL Balances

Key Table Name	Key Table Description
PO_VENDORS	PO_VENDORS is a view which contains selected columns of AP_SUPPLIERS
PO_VENDOR_SITES_ALL	PO_VENDORS is a view which contains selected columns of AP_SUPPLIERS
PO_VENDOR_CONTACTS
AP_SUPPLIERS	AP_SUPPLIERS stores information about your supplier level attributes. Each row includes the purchasing, receiving, invoice, tax, classification, and general information. Oracle Purchasing uses this information to determine active suppliers.
AP_INVOICE _INTERFACE	This is the open interface table for importing AP Invoices from external sources and stores header information about invoices. Invoice data comes from sources including: EDI invoices from suppliers that are loaded through Oracle e-Commerce Gateway, supplier invoices that are transferred through the Oracle XML Gateway, invoices that are loaded using Oracle SQL*Loader, lease invoices from Oracle Property Manager, Disbursements from Oracle loans, lease payments from Oracle Assets, credit card transaction data that are loaded using the Credit Card Invoice Interface Summary, Expense Report invoices from Oracle Internet Expenses, Payment Requests from Receivables, and invoices that are entered through the Invoice Gateway. There is one row for each invoice you import. Oracle Payables application uses this information to create invoice header information when Payables Open Interface program is submitted.
AP_INVOICES_LINES_INTERFACE	This is the lines interface table for the AP Invoice Open Interface. Use it in conjunction with AP_INVOICE_INTERFACE table.
AP_INVOICES_ALL	This table corresponds to the Invoices header block of Invoice workbench. AP_INVOICES_ALL holds information of all AP invoices whether it is a manually entered, imported, created from other products like Oracle Loans, Oracle Projects, I-Supplier Portal, Refunds from Oracle Receivables etc. This table holds all type of invoices, which includes Standard, Prepayments, Credit Memo, Debit Memo, Mixed invoice, Withholding invoice, Interest Invoice, Retainage invoices, Payment Requests etc., An invoice can also have one or more scheduled payments. There will be one row for each invoice you enter.
AP_INVOICES_DISTRIBUTIONS_ALL	AP_INVOICE_DISTRIBUTIONS_ALL holds the distribution line information that you enter for invoices. There is a row for each invoice distribution. A distribution line must be associated with an invoice. An invoice can have multiple distribution lines.
AP_HOLDS_ALL	AP_HOLDS_ALL contains information about holds that you or your Oracle Payables application place on an invoice. For none “matching holds, there is one row for each hold placed on an invoice. For matching holds, there is one row for each hold placed on an invoice “shipment match. An invoice may have one or more corresponding rows in this table. Your Oracle Payables application does not pay invoices that have one or more unreleased holds recorded in this table.
AP_PAYMENT_SCHEDULES_ALL	AP_PAYMENT_SCHEDULES_ALL contains information about scheduled payments for an invoice. You need one row for each time you intend to make a payment on an invoice. Your Oracle Payables application uses this information to determine when to make payments on an invoice and how much to pay in an automatic payment batch.
AP_INVOICE_PAYMENTS_ALL
AP_CHECKS_ALL	AP_CHECKS_ALL stores information about payments issued to suppliers or refunds received from suppliers.  You need one row for each payment you issue to a supplier or refund received from a supplier. Your Oracle Payables application uses this information to record payments you make to suppliers or refunds you receive from suppliers. Your Oracle Payables application stores the supplier name and bank account name for auditing purposes, in case either one is changed after you create the payment.
AP_INTERFACE_REJECTIONS	AP_INTERFACE_REJECTIONS stores information about invoice data from the AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE tables which could not be processed by Payables Open Interface Import.
AP_AE_LINES_ALL	An accounting entry line is an entity containing a proper accounting entry with debits or credits both in transaction currency as well as functional currency along with an account and other reference information pointing to the transaction data that originated the accounting entry line. An accounting entry line is grouped with other accounting entry lines for a specific accounting entry header. Any such group of accounting entry lines should result in balanced entries in the functional currency.
AP_AE_HEADERS_ALL	An accounting entry header is an entity grouping all accounting entry lines created for a given accounting event and a particular set of books. An accounting entry header can either be transferred over to GL or not at all. That is, either all its accounting entry lines are transferred or none at all. The transferred to GL status is marked in the GL_TRANSFER_FLAG. Possible values for GL_TRANSFER_FLAG are Y, N, or E. Y indicates that the accounting entry header has been transferred to GL. N indicates that the accounting entry header has not been transferred to GL due to 2 possible reasons: either the transfer process has not run or it has run but the accounting entry had an accounting error on it. E indicates that an error was encountered during the transfer to GL process.

Script to get cancelled invoices in AP

Background:
Following script gives the list of cancelled AP Invoices in the data range.
Enter From_Date, To_Date in DD-MON-YYYY format.

SELECT *
  FROM ap_invoices_all
 WHERE cancelled_date IS NOT NULL
   AND invoice_date BETWEEN TO_DATE ('&From_Date', 'DD-MON-YYYY') AND TO_DATE ('&TO_DATE', 'DD-MON-YYYY');