PMS100

-- ============================================================
-- Program: PMS100 — Manufacturing order maintenance — where MWOHED orders are opened, rescheduled, and managed through their status ladder
-- Purpose: Read the tables behind program PMS100 — auto-generated from program-table-map
-- Grain  : MWOHED × MWOMAT, MWOOPE — 1:N joins yield one row per line
-- Tables : MWOHED, MWOMAT, MWOOPE
-- Notes  : Auto-generated skeleton for a prefixed physical M3 schema or a landing schema normalized to the prefixed names in this catalog. Raw Data Lake property names vary with the published object: map them through Data Catalog before running this SQL. Dates are numeric YYYYMMDD (0 = none, mapped to NULL); all curated status values are decoded inline; company-partitioned tables are joined on CONO to prevent cross-company fan-out. Audit columns (RGDT/RGTM/LMDT/CHNO/CHID) omitted — see the quirks guide.
-- ============================================================
SELECT
  wo.VHCONO AS "Company",
  wo.VHFACI AS "Facility the order is manufactured in",
  wo.VHMFNO AS "Manufacturing order number",
  wo.VHWHST AS "Order status — the two-char ladder the order walks from creation through release, reporting, and completion",  -- status: decode wo.VHWHST against your configuration — see quirks guide #statuses
  wo.VHPRNO AS "Product being manufactured — joins to the item master on ITNO",
  m.VMMSEQ AS "Material sequence number within the order",
  m.VMMTNO AS "Component item consumed — joins to the item master on ITNO",
  m.VMREQT AS "Required quantity of the component for the order",
  mw.VOOPNO AS "Operation number within the order"
FROM <catalog>.<schema>.MWOHED wo
LEFT JOIN <catalog>.<schema>.MWOMAT m
  ON m.VMMFNO = wo.VHMFNO
 AND m.VMFACI = wo.VHFACI
 AND m.VMCONO = wo.VHCONO
LEFT JOIN <catalog>.<schema>.MWOOPE mw
  ON mw.VOMFNO = wo.VHMFNO
 AND mw.VOFACI = wo.VHFACI
 AND mw.VOCONO = wo.VHCONO
WHERE
  wo.VHCONO = <company>
ORDER BY wo.VHFACI;