ProdBOM

-- ============================================================
-- Table  : ProdBOM Production order BOM line — the materials a specific production order plans to consume, copied from the item's active BOM at order creation and then adjusted per order
-- Purpose: Column-selected read of ProdBOM — auto-generated from field metadata
-- Grain  : One row per company (dataareaid) + ProdId + 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
  p.prodid AS "The production order this material line belongs to",
  p.linenum AS "Line number within the order's BOM",
  p.itemid AS "Component item planned for consumption",
  p.inventdimid AS "Inventory-dimension combination the component is consumed from; join to InventDim to resolve site / warehouse",
  p.bomid AS "BOM header the line was copied from",
  p.prodlinetype AS "Line type — item, phantom, pegged supply, or vendor (subcontracted)",  -- enum: decode p.prodlinetype via GlobalOptionsetMetadata join — see quirks guide #enums
  p.bomqty AS "Component quantity per the BOM quantity series",
  p.bomqtyserie AS "Quantity series (per-series denominator) BOMQty applies to",
  p.bomconsump AS "Whether consumption is constant or varies with order quantity",  -- enum: decode p.bomconsump via GlobalOptionsetMetadata join — see quirks guide #enums
  p.qtybomcalc AS "Estimated consumption in the BOM unit",
  p.qtyinventcalc AS "Estimated consumption in the inventory unit",
  p.remainbomphysical AS "Remaining quantity to consume in the BOM unit",
  p.remaininventphysical AS "Remaining quantity to consume in the inventory unit",
  p.unitid AS "Unit the BOM quantities are stated in",
  p.oprnum AS "Route operation the component is consumed at",
  p.position AS "Position reference on the parent item (where the component sits)",
  p.prodflushingprincip AS "Flushing principle — when consumption posts automatically (start, finish, manual…)",  -- enum: decode p.prodflushingprincip via GlobalOptionsetMetadata join — see quirks guide #enums
  p.scrapconst AS "Constant scrap quantity added to the requirement",
  p.scrapvar AS "Variable scrap percentage added to the requirement",
  p.vendid AS "Vendor for subcontracted (vendor-type) lines",
  p.rawmaterialdate AS "Date the material is required — the primary analysis date for component demand",
  p.inventtransid AS "Inventory transaction reference of the line's issue — the lot the consumption posts against"
FROM <catalog>.<schema>.prodbom p
-- Dimension expansion: uncomment to resolve site / warehouse / location / batch
-- LEFT JOIN <catalog>.<schema>.inventdim id ON id.inventdimid = p.inventdimid AND id.dataareaid = p.dataareaid
WHERE
  p.dataareaid = '<company>'
  -- AND p.prodid = '<ProdId>'
  -- AND p.rawmaterialdate >= '<DATE_FROM>'  -- yyyy-MM-dd (UTC)
  -- AND p.rawmaterialdate <= '<DATE_TO>'  -- yyyy-MM-dd (UTC)
ORDER BY p.prodid;