AP_INVOICE_LINES_ALL

-- ============================================================
-- Table  : AP_INVOICE_LINES_ALL — Supplier invoice lines — the R12-new layer between header and distributions, carrying line type, amount, and the purchase-order and receipt matching references
-- Purpose: Column-selected read of AP_INVOICE_LINES_ALL — auto-generated from field metadata
-- Grain  : One row per operating unit (ORG_ID) + INVOICE_ID + LINE_NUMBER
-- Caution: ITEM, TAX, FREIGHT, and MISC lines share the table — filter LINE_TYPE_LOOKUP_CODE before summing spend, and exclude discarded/cancelled lines
-- Notes  : Auto-generated skeleton for Oracle EBS R12 data landed in your lakehouse. 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 — key together with the line number; no surrogate line id exists",
  t.LINE_NUMBER AS "Line number within the invoice — part of the key",
  t.ORG_ID AS "Operating unit",
  t.LINE_TYPE_LOOKUP_CODE AS "Item, tax, freight, or miscellaneous line — a lookup code, undecoded; filter before summing spend",
  t.AMOUNT AS "Line amount",
  t.BASE_AMOUNT AS "Line amount in the ledger currency",
  t.QUANTITY_INVOICED AS "Quantity invoiced",
  t.UNIT_PRICE AS "Unit price",
  t.INVENTORY_ITEM_ID AS "The item, for item-matched lines",
  t.ITEM_DESCRIPTION AS "Item description on the line",
  t.PO_HEADER_ID AS "The matched purchase order",
  t.PO_LINE_ID AS "The matched PO line",
  t.PO_LINE_LOCATION_ID AS "The matched PO shipment schedule",
  t.PO_DISTRIBUTION_ID AS "The matched PO distribution",
  t.RCV_TRANSACTION_ID AS "The matched receiving transaction — the three-way-match thread",
  t.ACCOUNTING_DATE AS "The accounting date — the primary analysis date",
  t.DISCARDED_FLAG AS "Y when the line was discarded — exclude from spend",
  t.CANCELLED_FLAG AS "Y when the line was cancelled"
FROM <catalog>.<schema>.AP_INVOICE_LINES_ALL t
WHERE
  t.ORG_ID = <operating_unit_id>  -- operating unit (MOAC does not filter extracts)
  -- 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>'  -- WHO watermark, bulk-stamped by batch jobs; see quirks guide #who-columns
ORDER BY t.INVOICE_ID;