Search Results ail_pk

Overview

AP_INVOICE_LINES_ALL is the core Payables table in Oracle E-Business Suite (validated in 12.1.1 and 12.2.2) that stores every invoice line record in the AP module. It resides in the AP schema and holds lines entered manually, generated automatically by matching, prepayment, or recurring invoice processes, or imported through the Payables Open Interface. Each row represents a single line on an invoice header defined in AP_INVOICES_ALL, capturing the amount, quantity, tax, matching, and accounting attributes that drive distribution creation, approval, and payment. Because header-level records are stored separately, the line table functions as a child satellite relative to the invoice header. The Data Vault classification heuristic (mined from the FK structure) suggests a satellite-leaning classification, supported by its dependency on AP_INVOICES_ALL and its own detailed descriptive columns.

Key Information Stored

The primary key is defined by the AIL_PK constraint on the composite columns INVOICE_ID and LINE_NUMBER, which is also the business-key candidate enforced by the unique index AP_INVOICE_LINES_U1. Major columns include:

• INVOICE_ID, LINE_NUMBER — composite key linking the line to its invoice header and defining its sequence.
• LINE_TYPE_LOOKUP_CODE — classifies the line (for example, Item, Freight, Tax, Miscellaneous).
• AMOUNT, BASE_AMOUNT, ORIGINAL_AMOUNT — entered and functional-currency line amounts used in accounting and matching.
• QUANTITY_INVOICED, UNIT_PRICE, UNIT_MEAS_LOOKUP_CODE — quantity and pricing detail for item and service lines.
• LINE_SOURCE — indicates whether the line was entered manually or generated by another process.
• MATCH_TYPE, PO_HEADER_ID, PO_LINE_ID, PO_LINE_LOCATION_ID, PO_DISTRIBUTION_ID — purchasing match references to PO entities.
• RCV_SHIPMENT_LINE_ID, RCV_TRANSACTION_ID — receipt-matching references.
• DISTRIBUTION_SET_ID, DEFAULT_DIST_CCID — defaults used to generate invoice distributions.
• ACCOUNTING_DATE, PERIOD_NAME, SET_OF_BOOKS_ID — accounting context for the line.
• TAX_*, TOTAL_REC_TAX_AMOUNT, TOTAL_NREC_TAX_AMOUNT, INCLUDED_TAX_AMOUNT — tax calculation and reporting attributes.
• WFAPPROVAL_STATUS, DISCARDED_FLAG, CANCELLED_FLAG, FINAL_MATCH_FLAG — workflow and lifecycle state indicators.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY — standard audit columns.

Common Use Cases and Queries

Typical reporting and reconciliation queries join lines to their invoice headers and to distributions. A common pattern retrieves all lines for an invoice:

• SELECT ail.line_number, ail.line_type_lookup_code, ail.amount, ail.quantity_invoiced FROM ap_invoice_lines_all ail WHERE ail.invoice_id = :p_invoice_id ORDER BY ail.line_number;
• Purchase order match analysis joins PO_LINE_LOCATION_ID to PO_LINE_LOCATIONS_ALL to reconcile invoiced versus ordered quantities.
• Receipt matching joins RCV_SHIPMENT_LINE_ID to RCV_SHIPMENT_LINES to verify receipt-based accruals.
• Tax reporting aggregates TOTAL_REC_TAX_AMOUNT and TOTAL_NREC_TAX_AMOUNT by TAX_REGIME_CODE or PERIOD_NAME.
• Approval and workflow dashboards filter on WFAPPROVAL_STATUS and DISCARDED_FLAG.

These patterns support accrual reconciliation, three-way match exceptions, aging, and audit reporting, and are frequently the foundation of custom Payables extracts.

Related Objects

AP_INVOICE_LINES_ALL is tightly coupled to several core Payables and non-Payables entities:

• AP_INVOICES_ALL — header table joined on INVOICE_ID (and AIL_INVOICE_ID references).
• AP_INVOICE_DISTRIBUTIONS_ALL — child distributions joined on INVOICE_ID and INVOICE_LINE_NUMBER, where distributions reference this table.
• PO_LINE_LOCATIONS_ALL — purchasing match via PO_LINE_LOCATION_ID.
• PO_RELEASES_ALL — release reference via PO_RELEASE_ID.
• RCV_SHIPMENT_LINES — receipt matching via RCV_SHIPMENT_LINE_ID.
• ZX_LINES_SUMMARY — tax summary via SUMMARY_TAX_LINE_ID.
• IGF_AW_AWARD_ALL — grants award reference via AWARD_ID.
• JAI_AP_INVOICE_LINES — localization table referencing INVOICE_ID and INVOICE_LINE_NUMBER.

These relationships make AP_INVOICE_LINES_ALL the central hub for invoice line-level processing across purchasing, receiving, tax, and accounting subledgers.