MWS420

-- ============================================================
-- Program: MWS420 — Picking list report — confirms picking on the MHDISH/MHDISL deliveries and updates the on-hand behind them
-- Purpose: Read the tables behind program MWS420 — auto-generated from program-table-map
-- Grain  : MHDISH × MHDISL — 1:N joins yield one row per line
-- Tables : MHDISH, MHDISL
-- 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.OQCONO AS "Company",
  h.OQINOU AS "Direction — inbound or outbound delivery",
  h.OQDLIX AS "Delivery index — the delivery's identity, carried by every line and document that rides on it",
  h.OQPGRS AS "Progress status — release, picking and reporting progress; the ladder is direction-scoped by INOU, so it is not decoded here — see the quirks guide",  -- status: decode h.OQPGRS against your configuration — see quirks guide #statuses
  CASE h.OQPIST WHEN '00' THEN 'Packing not used' WHEN '10' THEN 'Packing not started' WHEN '20' THEN 'Packing started but not completed' WHEN '30' THEN 'Packing completed' ELSE h.OQPIST END AS "Packing status — whether packing is unused, not started, started, or completed for the delivery",  -- status: PIST_DISH (all 4 curated values)
  m.URRIDN AS "Order number of the line being delivered",
  m.URRIDL AS "Order line number being delivered",
  m.URTRQT AS "Quantity moving on this delivery line"
FROM <catalog>.<schema>.MHDISH h
LEFT JOIN <catalog>.<schema>.MHDISL m
  ON m.URDLIX = h.OQDLIX
 AND m.URINOU = h.OQINOU
 AND m.URCONO = h.OQCONO
WHERE
  h.OQCONO = <company>
ORDER BY h.OQINOU;