PPS200

-- ============================================================
-- Program: PPS200 — Purchase order maintenance — creates and maintains purchase orders, writing the MPHEAD/MPLINE pair
-- Purpose: Read the tables behind program PPS200 — auto-generated from program-table-map
-- Grain  : MPHEAD × MPLINE, CIDMAS — 1:N joins yield one row per line
-- Tables : MPHEAD, MPLINE, CIDMAS
-- 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
  ph.IACONO AS "Company",
  ph.IAPUNO AS "Purchase order number — the key order lines join on",
  ph.IAPUSL AS "Lowest line status on the order",  -- status: decode ph.IAPUSL against your configuration — see quirks guide #statuses
  ph.IAPUST AS "Highest line status on the order",  -- status: decode ph.IAPUST against your configuration — see quirks guide #statuses
  pl.IBPNLI AS "Purchase order line number",
  pl.IBPNLS AS "Line subnumber — subdivides a line across deliveries",
  CASE pl.IBPUSL WHEN '12' THEN 'Awaiting authorization' WHEN '15' THEN 'Entered — ready for printout' WHEN '20' THEN 'Document printed' WHEN '50' THEN 'Goods received' WHEN '60' THEN 'Quality inspection partially performed' WHEN '64' THEN 'Rejected at inspection — handling not yet decided' WHEN '65' THEN 'Quality inspection completed' WHEN '69' THEN 'Rejected at inspection — handled through the claim routine' WHEN '70' THEN 'Put-away partially completed' WHEN '75' THEN 'Put-away complete' ELSE pl.IBPUSL END AS "Lowest line status — the least-processed point any part of the line's quantity still sits at; the line is fully received only when this end reaches the receipt rungs",  -- status: PUST_LINE (all 10 curated values)
  CASE pl.IBPUST WHEN '12' THEN 'Awaiting authorization' WHEN '15' THEN 'Entered — ready for printout' WHEN '20' THEN 'Document printed' WHEN '50' THEN 'Goods received' WHEN '60' THEN 'Quality inspection partially performed' WHEN '64' THEN 'Rejected at inspection — handling not yet decided' WHEN '65' THEN 'Quality inspection completed' WHEN '69' THEN 'Rejected at inspection — handled through the claim routine' WHEN '70' THEN 'Put-away partially completed' WHEN '75' THEN 'Put-away complete' ELSE pl.IBPUST END AS "Highest line status — the furthest point any part of the line's quantity has reached; on a partially received line this reads ahead of the quantity still outstanding",  -- status: PUST_LINE (all 10 curated values)
  s.IDSTAT AS "Supplier status — whether the supplier is active for new business",  -- status: decode s.IDSTAT against your configuration — see quirks guide #statuses
  s.IDSUNM AS "Supplier name"
FROM <catalog>.<schema>.MPHEAD ph
LEFT JOIN <catalog>.<schema>.MPLINE pl
  ON pl.IBPUNO = ph.IAPUNO
 AND pl.IBCONO = ph.IACONO
LEFT JOIN <catalog>.<schema>.CIDMAS s
  ON s.IDSUNO = ph.IASUNO
 AND s.IDCONO = ph.IACONO
WHERE
  ph.IACONO = <company>
ORDER BY ph.IAPUNO;