Important Account Payables Tables -Part 1
INVOICE_ID – NUMBER : Unique internal Identifier for this record. Generated using a database sequence.
VENDOR_ID – NUMBER : The unique internal identifier of the supplier to which the invoice belongs. Foreign key to the AP_SUPPLIERS table.
INVOICE_NUM : Invoice number for an invoice. This number entered will be unique per supplier and operating unit.
SET_OF_BOOKS_ID : Ledger unique identifier
INVOICE_CURRENCY_CODE : Currency code of the invoice. Foreign key to the FND_CURRENCIES table.
PAYMENT_CURRENCY_CODE : Currency code of payment. If the Invoice Currency is not a fixed-rate currency, then the Payment Currency will be the same as the INVOICE_CURRENCY_CODE. For fixed-rate currency such as euro or another EMU currency it can be different to INVOICE_CURRENCY_CODE
PAYMENT_CROSS_RATE NUMBER : Yes Exchange rate between invoice and payment. The value is always 1 unless they are associated fixed-rate currencies such as euro or another EMU currency.
DOC_SEQUENCE_ID NUMBER : Unique Sequence identifier assigned to an invoice, based on the document category of the invoice. Foreign key to the DOC_SEQUENCE_ID of the AP_DOC_SEQUENCE_AUDIT table.
DOC_SEQUENCE_VALUE NUMBER : Sequence name assigned to an invoice, based on the document category of the invoice. Foreign key to the DOC_SEQUENCE_VALUE of the AP_DOC_SEQUENCE_AUDIT table
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, iSupplier 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.
An invoice can have one or more invoice lines and each line can have one or more invoice distributions.
An invoice of type EXPENSE REPORT must relate to a row in AP_EXPENSE_REPORT_HEADERS_ALL unless the record has been purged from AP_EXPENSE_REPORT_HEADERS_ALL.
Oracle Payables application uses the INTEREST type invoice for interest that it calculates on invoices that are overdue. Oracle Payables application links the interest invoice to the original invoice by inserting the INVOICE_ID in the AP_INVOICE_RELATIONSHIPS table.
The accounting code combinations stored in this table is meta data for creating accounting entries in Subledger Accounting. Payables provides the code combinations stored in this table to Subledger Accounting as a source for accounting attributes for deriving the accounting code combinations. Subledger Accounting generates the final accounting entries that are posted to General Ledger. The accounting code combination used in the final accounting entries can be same as that stored in AP_INVOICES_ALL table or can be different depending on the rules setup in Subledger Accounting. The XLA_AE_HEADERS and XLA_AE_LINES tables in Subledger Accounting stores the accounting entries posted to General Ledger.
AP_HOLDS_ALL
- INVOICE_ID : Invoice identifier
- LINE_LOCATION_ID : Purchase order line location identifier
- HOLD_LOOKUP_CODE : Name of hold code
- HELD_BY : User that placed hold on invoice
- HOLD_DATE : Date user placed hold on invoice
- HOLD_REASON : Reason for hold being placed on invoice
- RELEASE_LOOKUP_CODE : Name of release code
- RELEASE_REASON : Reason for release being placed on invoice
- LINE_NUMBER : Invoice line number of the applied hold
- HOLD_ID : Hold identifier
WF_STATUS : Holds resolution workflow status
AP_HOLDS_ALL contains information about holds that you or your Oracle Payables application place on an invoice. For non-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.
This table holds information referenced by the Invoice Holds window.
In the strictest sense, AP_HOLDS_ALL has no primary key. It is possible for your Oracle Payables application to place a certain type of hold on an invoice, then release it, then place another hold of the same type (if data changes before each submission of Payables Invoice Validation), which would result in a duplicate primary key. But for practical purposes, the primary key is a concatenation of INVOICE_ID, LINE_LOCATION_ID, and HOLD_LOOKUP_CODE.
AP_INVOICES_INTERFACE
INVOICE_ID : Unique identifier for this invoice within this batch. Same value should be populated in invoice’s lines in the AP_INVOICE_LINES_INTERFACE table to identify the data as belonging to the same invoice.
INVOICE_NUM : Enter the invoice number that needs to be assigned to the invoice created in Payables from this record. The number must be unique for the supplier. If no value is populated in this column, then during import, Payables uses the system date at the time of import as a default.
PO_NUMBER : Purchase order number from PO_HEADERS.SEGMENT1. This column needs to be populated if invoice to be matched with an purchase order.
VENDOR_ID NUMBER : Unique identifier for a supplier. Validated against (PO_VENDORS.VENDOR_ID). Supplier of the invoice to be derived by value in one of the following columns in this table: VENDOR_ID, VENDOR_NUM, VENDOR_NAME, VENDOR_SITE_ID or PO_NUMBER.
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.
Data in the AP_INVOICES_INTERFACE table used in conjunction with AP_INVOICE_LINES_INTERFACE table to create Payables Invoice, Invoice lines, Distributions and Schedule payments.
Data in this table can be viewed and edited using ‘Open Interface Invoices’ window.
The Payables Open Interface program validates each record in this interface table selected for import, and if the record contains valid data then the program creates a Payables Invoice.
INVOICE_ID : Invoice identifier.Validated against AP_INVOICES_INTERFACE.INVOICE_ID
INVOICE_LINE_ID : Invoice line identifier
LINE_NUMBER : Invoice line number
LINE_TYPE_LOOKUP_CODE : Type of invoice line (Item, Freight, Tax, Miscellaneous)
LINE_GROUP_NUMBER NUMBER : Value to identify each item line to which you want to prorate
This is the lines interface table for the AP Invoice Open Interface. Use it in conjunction with AP_INVOICE_INTERFACE table.
AP_INVOICE_LINES_INTERFACE stores information used to create one or more invoice distributions. Note that one row in this table may create, during the import process, more than one invoice distribution. For example, if you enter a Tax line in this table and prorate it across multiple Item lines, during Open Interface Import the system may create multiple Tax invoice distributions based on the single Tax line in this table.
Leave a Reply
Want to join the discussion?Feel free to contribute!