MMS200MI

-- ============================================================
-- Program: MMS200MI — Item API — reads and maintains items and their warehouse connections programmatically
-- Purpose: Read the tables behind API program MMS200MI — auto-generated from program-table-map
-- Grain  : MITMAS × MITBAL — 1:N joins yield one row per line
-- Tables : MITMAS, MITBAL
-- 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
  im.MMCONO AS "Company",
  im.MMITNO AS "Item number — the natural key every balance, order line, and movement joins on",
  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)",
  ib.MBWHLO AS "Warehouse",
  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"
FROM <catalog>.<schema>.MITMAS im
LEFT JOIN <catalog>.<schema>.MITBAL ib
  ON ib.MBITNO = im.MMITNO
 AND ib.MBCONO = im.MMCONO
WHERE
  im.MMCONO = <company>
ORDER BY im.MMITNO;