BOM

-- ============================================================
-- Table  : BOM Bill of materials line — one component per row under a BOM header, with quantity per unit, validity dates, scrap factors, and the operation it is consumed at
-- Purpose: Column-selected read of BOM — auto-generated from field metadata
-- Grain  : One row per company (dataareaid) + BOMId + LineNum
-- 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 "The BOM header this component line belongs to",
  b.linenum AS "Line number within the BOM",
  b.itemid AS "Component item consumed by the line",
  b.bomtype AS "Line type — item, phantom, pegged supply, or vendor",  -- enum: decode b.bomtype via GlobalOptionsetMetadata join — see quirks guide #enums
  b.bomqty AS "Component quantity per the BOM quantity series",
  b.bomqtyserie AS "Quantity series (per-series denominator) BOMQty applies to",
  b.unitid AS "Unit the component quantity is stated in",
  b.inventdimid AS "Inventory-dimension combination the component defaults from; join to InventDim to resolve site / warehouse",
  CASE WHEN CAST(b.fromdate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(b.fromdate AS DATE) END AS "Line 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 "Line validity end; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  b.oprnum AS "Route operation the component is consumed at",
  b.position AS "Position reference on the parent item",
  b.configgroupid AS "Configuration group that decides which product configurations use the line",
  b.scrapconst AS "Constant scrap quantity added to the requirement",
  b.scrapvar AS "Variable scrap percentage added to the requirement",
  b.roundup AS "How the calculated consumption is rounded (no rounding, quantity, measurement…)",  -- enum: decode b.roundup via GlobalOptionsetMetadata join — see quirks guide #enums
  b.formula AS "Measurement formula used to compute consumption from height / width / depth / density",  -- enum: decode b.formula via GlobalOptionsetMetadata join — see quirks guide #enums
  b.prodflushingprincip AS "Flushing principle defaulted to production order lines",  -- enum: decode b.prodflushingprincip via GlobalOptionsetMetadata join — see quirks guide #enums
  b.itembomid AS "Specific sub-BOM to use when the component is itself a BOM item",
  b.itemrouteid AS "Specific sub-route to use when the component is produced",
  b.vendid AS "Vendor for subcontracted (vendor-type) lines"
FROM <catalog>.<schema>.bom 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>'
ORDER BY b.bomid;