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 Infor Data Lake-landed M3 data. Dates are numeric YYYYMMDD (0 = none, mapped to NULL); status ladders decoded inline where verified; 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.MBSTQT AS "On-hand balance in the warehouse, in the basic unit",
  ib.MBALQT AS "Allocated quantity — stock reserved against demand but not yet issued",
  im.MMITDS AS "Item name (short description)",
  im.MMFUDS AS "Item description (full)",
  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;