Search Results ap_invoice_lines_u1

Overview

AP.AP_INVOICE_LINES_ALL is the core Payables transaction line table in Oracle E-Business Suite Releases 12.1.1 and 12.2.2. It stores one row for every invoice line entered manually, generated automatically, or loaded through the Payables Open Interface. Each invoice header in AP_INVOICES_ALL may have one or more lines, and each line may fan out into one or more invoice distributions in AP_INVOICE_DISTRIBUTIONS_ALL. A line represents goods (direct or indirect materials), services, or associated tax, freight, and miscellaneous charges billed by a supplier, and carries the attributes needed to describe the charge consistently with the physical or electronic invoice. Documented line types include Item, Freight, Miscellaneous, Tax, Prepayment, and Withholding Tax. Prepayment lines arise from prepayment application, while Item lines may reflect a purchase order or receipt match, a price or quantity correction against a matched line, an invoice correction against a non-matched line, or a non-matched line itself.

From a Data Vault modeling perspective, the mined foreign-key structure classifies this table as satellite-leaning. That is a heuristic suggestion rather than a delivered design: the object behaves primarily as a descriptive satellite attached to an invoice hub or link, though it also carries genuine relationship semantics through its purchase order, receipt, and project references.

Key Information Stored

The table is defined in the AP schema under FND Design Data SQLAP.AP_INVOICE_LINES_ALL and contains 202 documented columns in the 12.2.2 physical schema. The declared primary key is AIL_PK, composed of INVOICE_ID and LINE_NUMBER. The unique index AP_INVOICE_LINES_U1 also covers (INVOICE_ID, LINE_NUMBER), making this composite the business-key candidate that callers such as the search term ap_invoice_lines_u1 refer to.

• INVOICE_ID, LINE_NUMBER — surrogate composite primary key and business key; the join path back to AP_INVOICES_ALL.
• LINE_TYPE_LOOKUP_CODE — Item, Freight, Miscellaneous, Tax, Prepayment, or Withholding Tax.
• LINE_SOURCE — how the line was created, whether manual, matched, or generated.
• AMOUNT, BASE_AMOUNT, ORIGINAL_AMOUNT — entered, functional-currency, and original line values.
• QUANTITY_INVOICED, UNIT_PRICE, UNIT_MEAS_LOOKUP_CODE — quantity, price, and unit of measure for the charge.
• PO_HEADER_ID, PO_LINE_ID, PO_LINE_LOCATION_ID, PO_DISTRIBUTION_ID — purchase order match references.
• RCV_SHIPMENT_LINE_ID, RCV_TRANSACTION_ID — receipt and receiving match references (RCV_SHIPMENT_LINE_ID is a documented foreign key to RCV_SHIPMENT_LINES).
• PROJECT_ID, TASK_ID, EXPENDITURE_TYPE, EXPENDITURE_ITEM_DATE, PA_QUANTITY — Project Accounting and expenditure detail.
• PREPAY_INVOICE_ID, PREPAY_LINE_NUMBER, INVOICE_INCLUDES_PREPAY_FLAG — prepayment application linkage.
• DISTRIBUTION_SET_ID, ACCOUNT_SEGMENT, OVERLAY_DIST_CODE_CONCAT, DEFAULT_DIST_CCID — distribution set and accounting flexfield defaults.
• TAX_RATE, TAX_CODE_ID, TAX_REGIME_CODE, INCLUDED_TAX_AMOUNT, SUMMARY_TAX_LINE_ID — E-Business Tax and ZX summary tax attributes.
• MATCH_TYPE, FINAL_MATCH_FLAG, WFAPPROVAL_STATUS, DISCARDED_FLAG, CANCELLED_FLAG — matching, approval, and lifecycle state.
• ATTRIBUTE1–ATTRIBUTE15, GLOBAL_ATTRIBUTE1–GLOBAL_ATTRIBUTE20 — extensibility and localization slots.

Common Use Cases and Queries

The table is central to invoice-line inquiry, match validation, tax reconciliation, and open-interface troubleshooting. A typical retrieval joins lines to their header and distributions:

• Lines for one invoice: SELECT line_number, line_type_lookup_code, amount, quantity_invoiced FROM ap.ap_invoice_lines_all WHERE invoice_id = :p_invoice_id ORDER BY line_number;
• Lines matched to a purchase order: filter on PO_LINE_LOCATION_ID and MATCH_TYPE to reconcile three-way match exceptions.
• Receipt-matched reporting: join RCV_SHIPMENT_LINE_ID to RCV_SHIPMENT_LINES to trace the receipt behind a freight or item line.
• Distribution auditing: join AP_INVOICE_DISTRIBUTIONS_ALL on INVOICE_ID and LINE_NUMBER to verify what was charged.
• Tax analysis: aggregate TAX, TOTAL_REC_TAX_AMOUNT, and SUMMARY_TAX_LINE_ID for E-Business Tax reporting.
• Line-source analysis: group by LINE_SOURCE and LINE_TYPE_LOOKUP_CODE to examine auto-generated versus manually entered volumes.
• Interface diagnostics: inspect rows created by the Payables Open Interface when a load fails validation.

Related Objects

• AP.AP_INVOICES_ALL — parent header, joined on INVOICE_ID (INVOICE_ID and AIL_INVOICE_ID foreign keys).
• AP.AP_INVOICE_DISTRIBUTIONS_ALL — child distributions; joined on INVOICE_ID and LINE_NUMBER, and referenced by AIL_INVOICE_ID2, AIL_INVOICE_ID3, and AIL_INVOICE_ID4.
• PO.PO_LINE_LOCATIONS_ALL — matched purchase order shipment, via PO_LINE_LOCATION_ID.
• PO.PO_RELEASES_ALL — matched release, via PO_RELEASE_ID.
• RCV.RCV_SHIPMENT_LINES — matched receipt line, via RCV_SHIPMENT_LINE_ID.
• ZX.ZX_LINES_SUMMARY — E-Business Tax summary line, via SUMMARY_TAX_LINE_ID.
• IGF.IGF_AW_AWARD_ALL — grants award association, via AWARD_ID.
• IGI.IGI_DOS_TRX_SOURCES — subledger source transaction, via SOURCE_TRX_ID.
• JAI.JAI_AP_INVOICE_LINES — localization table referencing INVOICE_ID.