BOMVersion

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