AR_PAYMENT_SCHEDULES_ALL

-- ============================================================
-- Table  : AR_PAYMENT_SCHEDULES_ALL — The open-receivables ledger — one row per installment of every transaction and receipt, restated as amounts due, applied, and remaining; the table AR aging actually reads
-- Purpose: Column-selected read of AR_PAYMENT_SCHEDULES_ALL — auto-generated from field metadata
-- Grain  : One row per business unit (ORG_ID) + PAYMENT_SCHEDULE_ID
-- Caution: Open items carry a 31-DEC-4712 sentinel in GL_DATE_CLOSED/ACTUAL_DATE_CLOSED, not NULL — the "still open" filter is the 4712 date
-- 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.PAYMENT_SCHEDULE_ID AS "Surrogate key of the installment",
  t.ORG_ID AS "The business unit owning the row",
  t.CLASS AS "Row kind — INV, DM, CM, CB, PMT (receipts), or BR; page-enumerated but no named lookup type",
  t.STATUS AS "Open or closed",
  t.CUSTOMER_TRX_ID AS "The transaction, for non-receipt rows",
  t.CASH_RECEIPT_ID AS "The receipt, for PMT rows",
  t.CUSTOMER_ID AS "Customer account of the installment",
  t.CUSTOMER_SITE_USE_ID AS "Bill-to site use of the installment",
  t.TRX_NUMBER AS "Denormalized transaction number",
  t.TRX_DATE AS "Denormalized transaction date",
  t.DUE_DATE AS "Installment due date — the aging bucket driver",
  t.GL_DATE AS "Accounting date",
  t.GL_DATE_CLOSED AS "Accounting close date — 31-DEC-4712 while the installment is open, not NULL",
  t.AMOUNT_DUE_ORIGINAL AS "Original amount due",
  t.AMOUNT_DUE_REMAINING AS "Open balance still due — the aging amount",
  t.ACCTD_AMOUNT_DUE_REMAINING AS "Open balance restated in ledger currency",
  t.AMOUNT_APPLIED AS "Amount applied against the installment",
  t.INVOICE_CURRENCY_CODE AS "Transaction currency"
FROM <catalog>.<schema>.AR_PAYMENT_SCHEDULES_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.PAYMENT_SCHEDULE_ID = <PAYMENT_SCHEDULE_ID>
  -- AND t.DUE_DATE >= DATE '<DATE_FROM>'
  -- AND t.DUE_DATE <= DATE '<DATE_TO>'
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- the column incremental BICC extracts key on
ORDER BY t.PAYMENT_SCHEDULE_ID;