ProdTable

-- ============================================================
-- Table  : ProdTable Production order header — one row per order carrying the item being built, order status and type, scheduled and actual dates, and the BOM and route it was created from
-- Purpose: Column-selected read of ProdTable — auto-generated from field metadata
-- Grain  : One row per company (dataareaid) + ProdId
-- 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
  pr.prodid AS "The production order number — the natural key BOM lines and postings join back on",
  pr.itemid AS "Item the order produces",
  pr.name AS "Order description, defaulted from the item name",
  pr.prodstatus AS "Order status across its life cycle (created through ended)",  -- enum: decode pr.prodstatus via GlobalOptionsetMetadata join — see quirks guide #enums
  pr.prodtype AS "Whether this is a standard order or a vendor (subcontracted) order",  -- enum: decode pr.prodtype via GlobalOptionsetMetadata join — see quirks guide #enums
  pr.inventdimid AS "Inventory-dimension combination the order produces into; join to InventDim to resolve site / warehouse",
  pr.bomid AS "BOM header the order's material lines were copied from",
  pr.routeid AS "Route the order's operations were copied from",
  pr.qtysched AS "Quantity the order is scheduled to produce",
  pr.qtycalc AS "Estimated quantity the order was cost-estimated at",
  pr.qtystup AS "Started quantity — how much of the order has been released to production",
  pr.remaininventphysical AS "Quantity still to be reported as finished",
  pr.schedstart AS "Scheduled start date — the primary analysis date for production load",
  pr.schedend AS "Scheduled end date",
  pr.dlvdate AS "Delivery date the order is due against",
  CASE WHEN CAST(pr.stupdate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(pr.stupdate AS DATE) END AS "Actual start date; unstarted orders carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  CASE WHEN CAST(pr.finisheddate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(pr.finisheddate AS DATE) END AS "Date the order was reported as finished; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  CASE WHEN CAST(pr.realdate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(pr.realdate AS DATE) END AS "Date the order was ended (costed); unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  pr.prodpoolid AS "Production pool the order is grouped under for scheduling and reporting",
  pr.prodgroupid AS "Production group, used for ledger posting profiles",
  pr.collectrefprodid AS "Reference production order — links a sub-production back to its parent order",
  pr.inventreftype AS "What kind of demand the order was created against (sales order, production, none…)",  -- enum: decode pr.inventreftype via GlobalOptionsetMetadata join — see quirks guide #enums
  pr.inventrefid AS "Order number of the referenced demand document, per InventRefType",
  pr.inventtransid AS "Inventory transaction reference of the order's receipt — the lot the produced quantity lands on",
  pr.defaultdimension AS "RecId of the order's default financial-dimension value set in DimensionAttributeValueSet"
FROM <catalog>.<schema>.prodtable pr
-- Dimension expansion: uncomment to resolve site / warehouse / location / batch
-- LEFT JOIN <catalog>.<schema>.inventdim id ON id.inventdimid = pr.inventdimid AND id.dataareaid = pr.dataareaid
-- Resolve DimensionAttributeValueSet (via DefaultDimension): uncomment to join on its RecId surrogate key
-- LEFT JOIN <catalog>.<schema>.dimensionattributevalueset dim ON dim.recid = pr.defaultdimension
WHERE
  pr.dataareaid = '<company>'
  -- AND pr.prodid = '<ProdId>'
  -- AND pr.schedstart >= '<DATE_FROM>'  -- yyyy-MM-dd (UTC)
  -- AND pr.schedstart <= '<DATE_TO>'  -- yyyy-MM-dd (UTC)
ORDER BY pr.prodid;