InventTrans

-- ============================================================
-- Table  : InventTrans The inventory transaction ledger — every physical and financial stock movement, with issue/receipt status, quantities, dates, and links back to its source document
-- Purpose: Column-selected read of InventTrans — 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
  t.inventtransorigin AS "RecId of the InventTransOrigin row this movement was created from — the link back to its source document",
  t.itemid AS "Item that moved",
  t.inventdimid AS "Inventory-dimension combination the movement is against; join to InventDim to resolve site / warehouse / location",
  CASE t.statusissue WHEN 0 THEN 'None' WHEN 1 THEN 'Sold' WHEN 2 THEN 'Deducted' WHEN 3 THEN 'Picked' WHEN 4 THEN 'ReservPhysical' WHEN 5 THEN 'ReservOrdered' WHEN 6 THEN 'OnOrder' WHEN 7 THEN 'QuotationIssue' ELSE CAST(t.statusissue AS STRING) END AS "Issue side of the movement — how far an outbound transaction has progressed",  -- enum: StatusIssue
  CASE t.statusreceipt WHEN 0 THEN 'None' WHEN 1 THEN 'Purchased' WHEN 2 THEN 'Received' WHEN 3 THEN 'Registered' WHEN 4 THEN 'Arrived' WHEN 5 THEN 'Ordered' WHEN 6 THEN 'QuotationReceipt' ELSE CAST(t.statusreceipt AS STRING) END AS "Receipt side of the movement — how far an inbound transaction has progressed",  -- enum: StatusReceipt
  t.qty AS "Quantity moved, signed by direction",
  t.datephysical AS "Date the movement became physical (received or shipped); the primary analysis date",
  CASE WHEN CAST(t.dateexpected AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(t.dateexpected AS DATE) END AS "Expected physical date; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  t.voucher AS "Ledger voucher the movement posted under",
  t.datestatus AS "Date of the movement's most recent status change (replaces the retired illustrative DateStatusChanged row — this is the real column, and it is a date, not a datetime)",
  CASE WHEN CAST(t.datefinancial AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(t.datefinancial AS DATE) END AS "Date the movement was financially posted (invoiced); unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  t.dateinvent AS "Inventory date used for on-hand and closing calculations",
  CASE WHEN CAST(t.dateclosed AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(t.dateclosed AS DATE) END AS "Date inventory closing settled the transaction; open rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  t.timeexpected AS "Expected time of day paired with DateExpected, stored as seconds since midnight",
  t.shippingdaterequested AS "Shipping date requested by the order line",
  CASE WHEN CAST(t.shippingdateconfirmed AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(t.shippingdateconfirmed AS DATE) END AS "Confirmed shipping date; unconfirmed rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  t.currencycode AS "Currency of the transaction amounts",
  t.costamountposted AS "Financially posted cost amount in company currency",
  t.costamountphysical AS "Physically posted cost amount (packing slip / product receipt value)",
  t.costamountadjustment AS "Adjustment applied by inventory closing or recalculation",
  t.costamountsettled AS "Cost amount settled against offsetting transactions by inventory closing",
  t.qtysettled AS "Quantity settled by inventory closing",
  t.valueopen AS "Whether the transaction still has open value for inventory closing to settle",  -- enum: decode t.valueopen via GlobalOptionsetMetadata join — see quirks guide #enums
  t.invoiceid AS "Invoice the movement was financially posted under",
  t.invoicereturned AS "Whether the invoiced movement has been returned",  -- enum: decode t.invoicereturned via GlobalOptionsetMetadata join — see quirks guide #enums
  t.packingslipid AS "Packing slip / product receipt the movement was physically posted under",
  t.packingslipreturned AS "Whether the physically posted movement has been returned",  -- enum: decode t.packingslipreturned via GlobalOptionsetMetadata join — see quirks guide #enums
  t.pickingrouteid AS "Picking route that picked the movement (note the capital ID in the column name)",
  t.projid AS "Project the movement is charged to, for project-related transactions",
  t.revenueamountphysical AS "Physically posted revenue amount, where the flow carries revenue",
  t.transchildtype AS "Sub-split discriminator that, with TransChildRefId, divides one origin into child branches (returns, credit notes…)",  -- enum: decode t.transchildtype via GlobalOptionsetMetadata join — see quirks guide #enums
  t.transchildrefid AS "Reference id of the child branch typed by TransChildType",
  t.markingrefinventtransorigin AS "RecId of the InventTransOrigin this movement is marked (cost-linked) against",
  t.loadid AS "Warehouse load the movement ships on, when warehouse management is used"
FROM <catalog>.<schema>.inventtrans t
-- Dimension expansion: uncomment to resolve site / warehouse / location / batch
-- LEFT JOIN <catalog>.<schema>.inventdim id ON id.inventdimid = t.inventdimid AND id.dataareaid = t.dataareaid
-- Resolve InventTransOrigin (via InventTransOrigin): uncomment to join on its RecId surrogate key
-- LEFT JOIN <catalog>.<schema>.inventtransorigin inv ON inv.recid = t.inventtransorigin
WHERE
  t.dataareaid = '<company>'
  -- AND t.datephysical >= '<DATE_FROM>'  -- yyyy-MM-dd (UTC)
  -- AND t.datephysical <= '<DATE_TO>'  -- yyyy-MM-dd (UTC)
;