RouteVersion

-- ============================================================
-- Table  : RouteVersion Route version — the link that assigns a route to a finished item for a validity period, quantity range, and site, mirroring how BOMVersion assigns a BOM
-- Purpose: Column-selected read of RouteVersion — auto-generated from field metadata
-- Grain  : One row per company (dataareaid) + RouteId + ItemId
-- 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.routeid AS "Route this version assigns",
  r.itemid AS "Finished item the route is assigned to",
  r.active AS "Whether this version is the active one planning and order creation pick",  -- enum: decode r.active via GlobalOptionsetMetadata join — see quirks guide #enums
  r.approved AS "Whether the version is approved",  -- enum: decode r.approved via GlobalOptionsetMetadata join — see quirks guide #enums
  r.approver AS "RecId of the worker who approved the version",
  CASE WHEN CAST(r.fromdate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(r.fromdate AS DATE) END AS "Version validity start; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  CASE WHEN CAST(r.todate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(r.todate AS DATE) END AS "Version validity end; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  r.fromqty AS "Minimum order quantity the version applies from",
  r.inventdimid AS "Dimension scope of the assignment (typically the site); join to InventDim",
  r.name AS "Version name"
FROM <catalog>.<schema>.routeversion r
-- Dimension expansion: uncomment to resolve site / warehouse / location / batch
-- LEFT JOIN <catalog>.<schema>.inventdim id ON id.inventdimid = r.inventdimid AND id.dataareaid = r.dataareaid
WHERE
  r.dataareaid = '<company>'
  -- AND r.itemid = '<ItemId>'
ORDER BY r.routeid;