M3 Reference
MMS200MI
APIItem API — reads and maintains items and their warehouse connections programmatically
Tables vs APIs
This MI API 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 →
Notes
Get/List and search-query (SQRY) transactions confirmed to exist; the full transaction list is verified in Phase 2.
Boilerplate SQL
Databricks SQLStarting point for reading the tables behind MMS200MI from landed data — the backing tables joined on their keys and CONO. Set your Unity Catalog location, company, and filter values below.
Query parameters
-- ============================================================
-- 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 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
im.MMCONO AS "Company",
im.MMITNO AS "Item number — the natural key every balance, order line, and movement joins on",
im.MMITDS AS "Item name (short description)",
im.MMFUDS AS "Item description (full)",
ib.MBWHLO AS "Warehouse",
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>.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;3 parameters not filled: <catalog>, <schema>, <company>
Backing Tables
How these tables connect. Nodes are clickable.