RA_CUSTOMER_TRX_ALL

-- ============================================================
-- Table  : RA_CUSTOMER_TRX_ALL — Receivables transaction headers — one row per invoice, credit memo, debit memo, or bill receivable, with the transaction number, type, dates, customer account and site-use pointers, and currency; business-unit striped
-- Purpose: Column-selected read of RA_CUSTOMER_TRX_ALL — auto-generated from field metadata
-- Grain  : One row per business unit (ORG_ID) + CUSTOMER_TRX_ID
-- 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.CUSTOMER_TRX_ID AS "Surrogate key of the transaction",
  t.ORG_ID AS "The business unit owning the transaction — Fusion's BU stripe, not the EBS operating unit",
  t.TRX_NUMBER AS "Transaction number — repeats across BUs and sources; join on the id",
  t.TRX_DATE AS "Transaction date",
  t.TRX_CLASS AS "Class of the document (invoice, credit memo, debit memo, bill receivable)",
  t.STATUS_TRX AS "Transaction status",
  t.COMPLETE_FLAG AS "Whether the transaction is complete — filter it for finalized documents",
  t.CUST_TRX_TYPE_SEQ_ID AS "Transaction type — a Fusion _SEQ_ID column, not the EBS CUST_TRX_TYPE_ID; the type table is not cataloged",
  t.BILL_TO_CUSTOMER_ID AS "Bill-to customer account",
  t.SHIP_TO_CUSTOMER_ID AS "Ship-to customer account",
  t.BILL_TO_SITE_USE_ID AS "Bill-to site use — the TCA bottom layer the invoice addresses through",
  t.SHIP_TO_SITE_USE_ID AS "Ship-to site use",
  t.TERM_ID AS "Payment terms",
  t.INVOICE_CURRENCY_CODE AS "Transaction currency",
  t.EXCHANGE_RATE AS "Conversion rate to the ledger currency",
  t.PREVIOUS_CUSTOMER_TRX_ID AS "For a credit memo, the transaction it credits (self-referencing)",
  t.PURCHASE_ORDER AS "The customer's PO number as text",
  t.LEGAL_ENTITY_ID AS "Legal entity of the transaction"
FROM <catalog>.<schema>.RA_CUSTOMER_TRX_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.CUSTOMER_TRX_ID = <CUSTOMER_TRX_ID>
  -- AND t.TRX_DATE >= DATE '<DATE_FROM>'
  -- AND t.TRX_DATE <= DATE '<DATE_TO>'
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- the column incremental BICC extracts key on
ORDER BY t.CUSTOMER_TRX_ID;