MMS002

-- ============================================================
-- Program: MMS002 — Item/warehouse maintenance — connects items to warehouses and manages the MITBAL planning parameters
-- Purpose: Read the tables behind program MMS002 — auto-generated from program-table-map
-- Grain  : MITBAL × MITMAS, MITWHL — 1:N joins yield one row per line
-- Tables : MITBAL, MITMAS, MITWHL
-- 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
  ib.MBCONO AS "Company",
  ib.MBWHLO AS "Warehouse",
  ib.MBITNO AS "Item number",
  ib.MBSTAT AS "Item/warehouse status — whether the item is active in this warehouse",  -- status: decode ib.MBSTAT against your configuration — see quirks guide #statuses
  ib.MBSTQT AS "On-hand balance in the warehouse, in the basic unit",
  CASE im.MMSTAT WHEN '00' THEN 'Created, basic data incomplete' WHEN '05' THEN 'Basic data specified' WHEN '10' THEN 'Preliminarily released — purchasable, not usable' WHEN '15' THEN 'To be replaced by another item' WHEN '20' THEN 'Released' WHEN '30' THEN 'Alternate items exist' WHEN '50' THEN 'Use then discontinue' WHEN '80' THEN 'Discontinued — stock transactions still permitted' WHEN '90' THEN 'Discontinued — set manually' WHEN '99' THEN 'Discontinued through item-number change' ELSE im.MMSTAT END AS "Item status — a two-char lifecycle ladder from created through released to discontinued",  -- status: ITEM_STAT (all 10 curated values)
  im.MMITDS AS "Item name (short description)",
  w.MWWHNM AS "Warehouse name"
FROM <catalog>.<schema>.MITBAL ib
LEFT JOIN <catalog>.<schema>.MITMAS im
  ON im.MMITNO = ib.MBITNO
 AND im.MMCONO = ib.MBCONO
LEFT JOIN <catalog>.<schema>.MITWHL w
  ON w.MWWHLO = ib.MBWHLO
 AND w.MWCONO = ib.MBCONO
WHERE
  ib.MBCONO = <company>
ORDER BY ib.MBWHLO;