M3 Reference
MMS001
InteractiveItem master maintenance — where items are created and their MITMAS attributes managed
Tables vs APIs
This program works over the tables below. For analytics at scale, land the raw tables — the SQL further down reads them directly, joined on their keys and CONO. When to land tables vs call MI APIs
Boilerplate SQL
Databricks SQLStarting point for reading the tables behind MMS001 from landed data — the backing tables joined on their keys and CONO. Set your Unity Catalog location, company, and filter values below.
Query parameters
3 parameters not filled: <catalog>, <schema>, <company>
-- ============================================================
-- Program: MMS001 — Item master maintenance — where items are created and their MITMAS attributes managed
-- Purpose: Read the tables behind program MMS001 — auto-generated from program-table-map
-- Grain : One row per MITMAS record
-- Tables : MITMAS
-- 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)"
FROM <catalog>.<schema>.MITMAS im
WHERE
im.MMCONO = <company>
ORDER BY im.MMITNO;