VendTrans

-- ============================================================
-- Table  : VendTrans The vendor transaction ledger — one open-item row per posted AP amount (invoice, payment, credit note), mirroring CustTrans on the payable side; the table AP aging and DPO are built from, companion to the VendInvoiceJour invoice detail
-- Purpose: Column-selected read of VendTrans — 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
  v.accountnum AS "Vendor account the transaction posts against — the join to VendTable",
  v.transdate AS "Posting date of the transaction — the primary analysis date for AP activity",
  v.voucher AS "Ledger voucher the transaction posted under — the bridge to general-ledger transactions",
  v.invoice AS "The vendor's invoice number for invoice-born rows (empty on payments) — the link back to VendInvoiceJour; join together with AccountNum, the vendor's invoice number is not unique on its own",
  v.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 v.transtype via GlobalOptionsetMetadata join — see quirks guide #enums
  v.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",
  v.amountmst AS "Transaction amount restated in the accounting (company) currency, with the same sign convention",
  v.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",
  v.settleamountmst AS "Amount settled so far, restated in the accounting currency",
  v.currencycode AS "Transaction currency the amounts post in",
  v.exchrate AS "Exchange rate used to restate the transaction into the accounting currency",
  CASE WHEN CAST(v.duedate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(v.duedate AS DATE) END AS "Payment due date — the anchor date aging buckets and DPO count from; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  CASE WHEN CAST(v.closed AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(v.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(v.lastsettledate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(v.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
  v.lastsettlevoucher AS "Voucher of the most recent settlement against the transaction",
  CASE WHEN CAST(v.documentdate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(v.documentdate AS DATE) END AS "The vendor's document date on the transaction; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  v.documentnum AS "External document number on the transaction",
  v.paymmode AS "Method of payment on the transaction",
  v.paymtermid AS "Terms of payment the due date was derived from",
  v.paymreference AS "Payment reference sent with or matched against the payment",
  v.postingprofile AS "Vendor posting profile the transaction posted with — decides the summary (control) account in the ledger",
  v.approved AS "Whether the transaction is approved for payment — unapproved invoices are excluded from payment proposals",  -- enum: decode v.approved via GlobalOptionsetMetadata join — see quirks guide #enums
  CASE WHEN CAST(v.approveddate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(v.approveddate AS DATE) END AS "Date the transaction was approved for payment; unapproved rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  v.prepayment AS "Whether the transaction is a prepayment",  -- enum: decode v.prepayment via GlobalOptionsetMetadata join — see quirks guide #enums
  v.txt AS "Transaction text entered at posting",
  v.defaultdimension AS "RecId of the transaction's financial-dimension value set in DimensionAttributeValueSet"
FROM <catalog>.<schema>.vendtrans v
-- Resolve DimensionAttributeValueSet (via DefaultDimension): uncomment to join on its RecId surrogate key
-- LEFT JOIN <catalog>.<schema>.dimensionattributevalueset dim ON dim.recid = v.defaultdimension
WHERE
  v.dataareaid = '<company>'
  -- AND v.transdate >= '<DATE_FROM>'  -- yyyy-MM-dd (UTC)
  -- AND v.transdate <= '<DATE_TO>'  -- yyyy-MM-dd (UTC)
;