AP_INVOICE_LINES_ALL

-- ============================================================
-- Table  : AP_INVOICE_LINES_ALL — Payables invoice lines — the goods/services, freight, tax, and prepayment rows under each invoice, carrying the amount, quantity, and the PO/receipt match pointers
-- Purpose: Column-selected read of AP_INVOICE_LINES_ALL — auto-generated from field metadata
-- Grain  : One row per business unit (ORG_ID) + INVOICE_ID + LINE_NUMBER
-- Caution: Mixed line kinds share the table (Item/Freight/Miscellaneous/Tax/Prepayment/Withholding) — filter LINE_TYPE_LOOKUP_CODE before summing spend
-- Notes  : Auto-generated skeleton for Oracle Fusion Cloud data landed in your lakehouse by a BICC extract — there is no SQL path to the SaaS database. Column names follow Oracle's table documentation — if your landed data still carries PVO attribute headers, map names first; see quirks #pvo-drift. Dates are real DATE/TIMESTAMP columns — no conversion needed. WHO audit columns omitted (see the quirks guide); the optional LAST_UPDATE_DATE watermark filter supports incremental extracts.
-- ============================================================
SELECT
  t.INVOICE_ID AS "The invoice — half of the composite key; there is no line surrogate id",
  t.LINE_NUMBER AS "Line number — the other half of the composite key",
  t.ORG_ID AS "The business unit owning the line",
  t.LINE_TYPE_LOOKUP_CODE AS "Row kind — Item, Freight, Miscellaneous, Tax, Prepayment, or Withholding Tax",  -- decode t.LINE_TYPE_LOOKUP_CODE via FND_LOOKUP_VALUES (LOOKUP_TYPE = 'INVOICE LINE TYPE', LANGUAGE-filtered)
  t.AMOUNT AS "Line amount in invoice currency",
  t.QUANTITY_INVOICED AS "Quantity invoiced",
  t.UNIT_PRICE AS "Price per unit",
  t.INVENTORY_ITEM_ID AS "Item on the line — the documented FK pairs it with the BU column, so the shipped edge joins the item id alone",
  t.PO_HEADER_ID AS "Matched purchase order",
  t.PO_LINE_LOCATION_ID AS "Matched PO schedule",
  t.PO_DISTRIBUTION_ID AS "Matched PO distribution",
  t.RCV_TRANSACTION_ID AS "Matched receiving transaction",
  t.ACCOUNTING_DATE AS "Accounting date for the line",
  t.MATCH_TYPE AS "How the line was matched (PO, receipt, not matched)",  -- decode t.MATCH_TYPE via FND_LOOKUP_VALUES (LOOKUP_TYPE = 'INVOICE LINE MATCH TYPE', LANGUAGE-filtered)
  t.DISCARDED_FLAG AS "Line discarded — distributions reversed and amount zeroed",
  t.CANCELLED_FLAG AS "Line cancelled",
  t.TAX_CLASSIFICATION_CODE AS "Tax classification of the line"
FROM <catalog>.<schema>.AP_INVOICE_LINES_ALL t
WHERE
  t.ORG_ID = <business_unit_id>  -- business unit — Fusion _ALL means BU, not the EBS operating unit; see quirks guide #all-means-bu
  -- AND t.INVOICE_ID = <INVOICE_ID>
  -- AND t.LINE_NUMBER = <LINE_NUMBER>
  -- AND t.ACCOUNTING_DATE >= DATE '<DATE_FROM>'
  -- AND t.ACCOUNTING_DATE <= DATE '<DATE_TO>'
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- the column incremental BICC extracts key on
ORDER BY t.INVOICE_ID;