MMS080

-- ============================================================
-- Program: MMS080 — Material plan — the per-item, per-warehouse view over MITPLO where planners work supply and demand
-- Purpose: Read the tables behind program MMS080 — auto-generated from program-table-map
-- Grain  : MITPLO × MITBAL — 1:N joins yield one row per line
-- Tables : MITPLO, MITBAL
-- 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
  h.MOCONO AS "Company",
  h.MOWHLO AS "Warehouse",
  h.MOITNO AS "Item number",
  CASE WHEN h.MOPLDT = 0 THEN NULL ELSE TO_DATE(CAST(CAST(h.MOPLDT AS BIGINT) AS STRING), 'yyyyMMdd') END AS "Planning date the transaction is scheduled on",  -- YYYYMMDD, 0 → NULL
  h.MOTRQT AS "Planned quantity, signed by direction (demand negative, supply positive)",
  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"
FROM <catalog>.<schema>.MITPLO h
LEFT JOIN <catalog>.<schema>.MITBAL ib
  ON ib.MBWHLO = h.MOWHLO
 AND ib.MBITNO = h.MOITNO
 AND ib.MBCONO = h.MOCONO
WHERE
  h.MOCONO = <company>
ORDER BY h.MOWHLO;