ReqTrans

-- ============================================================
-- Table  : ReqTrans Net requirements — every demand and supply the planning run sees per plan version, with the planned orders and action suggestions it generated; the table behind supply/demand pegging analysis
-- Purpose: Column-selected read of ReqTrans — 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
  r.itemid AS "Item the requirement is for",
  r.covinventdimid AS "Coverage inventory-dimension set the requirement is planned at (only the dimensions marked for coverage planning) — join to InventDim through this column, not InventDimId",
  r.reqdate AS "Requirement date — when the demand is needed or the supply is expected; the primary analysis date",
  r.qty AS "Requirement quantity, signed — receipts positive, issues negative",
  r.covqty AS "Quantity still open for coverage after netting",
  r.direction AS "Whether the row is a receipt (supply) or an issue (demand)",  -- enum: decode r.direction via GlobalOptionsetMetadata join — see quirks guide #enums
  r.reftype AS "What kind of supply or demand the row represents — sales line, purchase line, planned order, forecast, BOM demand…",  -- enum: decode r.reftype via GlobalOptionsetMetadata join — see quirks guide #enums
  r.refid AS "Document number of the referenced supply or demand (order number, planned order number…)",
  r.openstatus AS "Whether the requirement is still open against coverage",  -- enum: decode r.openstatus via GlobalOptionsetMetadata join — see quirks guide #enums
  r.level AS "BOM explosion level the requirement sits at in the plan. Reserved word — lands as level_ in Synapse Link / Fabric Link exports",
  r.planversion AS "RecId of the plan version this row belongs to — always filter to one plan version before aggregating, or every regeneration double-counts",
  r.inventtransorigin AS "RecId of the InventTransOrigin row for requirements backed by real inventory transactions — the pegging link back to the source document",
  CASE WHEN CAST(r.actiondate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(r.actiondate AS DATE) END AS "Date the action suggestion proposes moving the order to; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  r.actiontype AS "Suggested action for the referenced order — advance, postpone, decrease, cancel…",  -- enum: decode r.actiontype via GlobalOptionsetMetadata join — see quirks guide #enums
  CASE WHEN CAST(r.futuresdate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(r.futuresdate AS DATE) END AS "Calculated futures date — when the requirement can realistically be met given lead times; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  r.isdelayed AS "Whether the requirement is flagged as delayed (futures date after requirement date)",  -- enum: decode r.isdelayed via GlobalOptionsetMetadata join — see quirks guide #enums
  r.itembomid AS "BOM version the planned order explodes with",
  r.itemrouteid AS "Route version the planned order schedules with",
  r.custaccountid AS "Customer behind the demand, where the reference carries one"
FROM <catalog>.<schema>.reqtrans r
-- Resolve InventTransOrigin (via InventTransOrigin): uncomment to join on its RecId surrogate key
-- LEFT JOIN <catalog>.<schema>.inventtransorigin inv ON inv.recid = r.inventtransorigin
WHERE
  r.dataareaid = '<company>'
  -- AND r.reqdate >= '<DATE_FROM>'  -- yyyy-MM-dd (UTC)
  -- AND r.reqdate <= '<DATE_TO>'  -- yyyy-MM-dd (UTC)
;