AP_INVOICES_ALL

-- ============================================================
-- Table  : AP_INVOICES_ALL — Supplier invoice headers — standard invoices, credit/debit memos, prepayments, and expense reports share the table by type, with amounts, payment status, and the liability account
-- Purpose: Column-selected read of AP_INVOICES_ALL — auto-generated from field metadata
-- Grain  : One row per operating unit (ORG_ID) + INVOICE_ID
-- 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 "Surrogate key of the invoice — the join key; the invoice number has no database uniqueness",
  t.ORG_ID AS "Operating unit",
  t.INVOICE_NUM AS "The supplier's invoice number — uniqueness per supplier/OU is application-enforced only",
  t.VENDOR_ID AS "The supplier",
  t.VENDOR_SITE_ID AS "The supplier site",
  t.INVOICE_DATE AS "The invoice's business date — the primary analysis date",
  t.INVOICE_AMOUNT AS "Invoice total",
  t.AMOUNT_PAID AS "Amount paid to date",
  t.INVOICE_CURRENCY_CODE AS "Invoice currency",
  t.INVOICE_TYPE_LOOKUP_CODE AS "Standard, credit/debit memo, prepayment, expense report — a lookup code, undecoded here",
  t.PAYMENT_STATUS_FLAG AS "Y paid, N unpaid, P partially paid (per the data dictionary's comment)",
  t.SOURCE AS "The system the invoice arrived through",
  t.TERMS_ID AS "Payment terms",
  t.GL_DATE AS "The accounting date placing the invoice in a period",
  t.ACCTS_PAY_CODE_COMBINATION_ID AS "The liability account (CCID)",
  t.CANCELLED_DATE AS "When the invoice was cancelled — NULL if never",
  t.WFAPPROVAL_STATUS AS "Invoice-approval workflow state",
  t.PARTY_ID AS "The supplier's trading-community party (denormalized)"
FROM <catalog>.<schema>.AP_INVOICES_ALL t
WHERE
  t.ORG_ID = <operating_unit_id>  -- operating unit (MOAC does not filter extracts)
  -- AND t.INVOICE_ID = <INVOICE_ID>
  -- AND t.INVOICE_DATE >= DATE '<DATE_FROM>'
  -- AND t.INVOICE_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;