CustTrans

-- ============================================================
-- Table  : CustTrans The customer transaction ledger — one open-item row per posted AR amount (invoice, payment, credit note), carrying signed amounts, due date, and settlement state; the table AR aging and DSO are built from, companion to the CustInvoiceJour invoice detail
-- Purpose: Column-selected read of CustTrans — auto-generated from field metadata
-- Grain  : One row per company (dataareaid) + recid (surrogate key)
-- Notes  : Auto-generated skeleton for Synapse Link / Fabric Link-landed F&O data (lowercase column names). Enums decoded inline where verified; datetimes stored in UTC; 1900-01-01 dates are sentinels mapped to NULL. System/audit columns omitted — see the quirks guide.
-- ============================================================
SELECT
  c.accountnum AS "Customer account the transaction posts against — the join to CustTable",
  c.transdate AS "Posting date of the transaction — the primary analysis date for AR activity",
  c.voucher AS "Ledger voucher the transaction posted under — the bridge to general-ledger transactions",
  c.invoice AS "Invoice number for invoice-born rows (empty on payments) — the link back to CustInvoiceJour, whose key is composite; the invoice number alone is not guaranteed unique",
  c.transtype AS "The kind of posting that created the row (invoice, payment, and so on); values are not decoded here, use the metadata join",  -- enum: decode c.transtype via GlobalOptionsetMetadata join — see quirks guide #enums
  c.amountcur AS "Transaction amount in the transaction currency, signed by debit/credit side — invoices and their offsetting payments and credit notes carry opposite signs, so balances are straight sums",
  c.amountmst AS "Transaction amount restated in the accounting (company) currency, with the same sign convention",
  c.settleamountcur AS "Amount settled so far against the transaction, in the transaction currency — use Closed (not arithmetic on this column) to separate open items from settled ones",
  c.settleamountmst AS "Amount settled so far, restated in the accounting currency",
  c.currencycode AS "Transaction currency the amounts post in",
  c.exchrate AS "Exchange rate used to restate the transaction into the accounting currency",
  CASE WHEN CAST(c.duedate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(c.duedate AS DATE) END AS "Payment due date — the anchor date aging buckets and DSO count from; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  CASE WHEN CAST(c.closed AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(c.closed AS DATE) END AS "Date the transaction became fully settled; open items carry the 1900-01-01 sentinel — filtering on the sentinel is the open-item (aging) filter",  -- 1900-01-01 sentinel → NULL
  CASE WHEN CAST(c.lastsettledate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(c.lastsettledate AS DATE) END AS "Date of the most recent settlement against the transaction; never-settled rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  c.lastsettlevoucher AS "Voucher of the most recent settlement against the transaction",
  CASE WHEN CAST(c.documentdate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(c.documentdate AS DATE) END AS "The document (external) date on the transaction; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  c.documentnum AS "External document number on the transaction",
  c.orderaccount AS "Customer account that placed the underlying order, when it differs from the account invoiced",
  c.paymmode AS "Method of payment on the transaction",
  c.paymtermid AS "Terms of payment the due date was derived from",
  c.paymreference AS "Payment reference used to match incoming payments",
  c.postingprofile AS "Customer posting profile the transaction posted with — decides the summary (control) account in the ledger",
  c.prepayment AS "Whether the transaction is a prepayment",  -- enum: decode c.prepayment via GlobalOptionsetMetadata join — see quirks guide #enums
  c.txt AS "Transaction text entered at posting",
  c.defaultdimension AS "RecId of the transaction's financial-dimension value set in DimensionAttributeValueSet"
FROM <catalog>.<schema>.custtrans c
-- Resolve DimensionAttributeValueSet (via DefaultDimension): uncomment to join on its RecId surrogate key
-- LEFT JOIN <catalog>.<schema>.dimensionattributevalueset dim ON dim.recid = c.defaultdimension
WHERE
  c.dataareaid = '<company>'
  -- AND c.transdate >= '<DATE_FROM>'  -- yyyy-MM-dd (UTC)
  -- AND c.transdate <= '<DATE_TO>'  -- yyyy-MM-dd (UTC)
;