MWS410

-- ============================================================
-- Program: MWS410 — Delivery toolbox — the delivery workbench where MHDISH deliveries are monitored and driven through picking and dispatch
-- Purpose: Read the tables behind program MWS410 — auto-generated from program-table-map
-- Grain  : MHDISH × MHDISL — 1:N joins yield one row per line; MITALO join on company (CONO) only — no row-level join key is cataloged, so its grain is unresolved (see the TODO below)
-- Tables : MHDISH, MHDISL, MITALO
-- 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",
  mi.MQWHLO AS "Warehouse",
  mi.MQITNO AS "Item number",
  mi.MQWHSL AS "Stock location the allocation points at",
  mi.MQBANO AS "Lot (batch) number allocated",
  mi.MQALQT AS "Allocated quantity"
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
LEFT JOIN <catalog>.<schema>.MITALO mi
  ON mi.MQCONO = h.OQCONO
  -- TODO: CONO alone does not identify matching rows — this pair's business
  -- join key is not yet cataloged; add it before using this join.
WHERE
  h.OQCONO = <company>
ORDER BY h.OQINOU;