PDS002

-- ============================================================
-- Program: PDS002 — Product structure materials and operations — connects components to a structure, writing the MPDMAT lines under their MPDHED header
-- Purpose: Read the tables behind program PDS002 — auto-generated from program-table-map
-- Grain  : MPDMAT × MPDHED — 1:N joins yield one row per line
-- Tables : MPDMAT, MPDHED
-- 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
  h.PMCONO AS "Company",
  h.PMFACI AS "Facility",
  h.PMPRNO AS "Product the structure builds",
  h.PMSTRT AS "Structure type",
  h.PMMSEQ AS "Material sequence number within the structure",
  h.PMMTNO AS "Component item — joins to the item master on ITNO"
FROM <catalog>.<schema>.MPDMAT h
LEFT JOIN <catalog>.<schema>.MPDHED m
  ON m.PHPRNO = h.PMPRNO
 AND m.PHFACI = h.PMFACI
 AND m.PHSTRT = h.PMSTRT
 AND m.PHCONO = h.PMCONO
WHERE
  h.PMCONO = <company>
ORDER BY h.PMFACI;