The Infor M3 Quirks Guide
Infor M3 models data in ways that make a first extract wrong in exactly the ways that look right — every column renamed by a table prefix, every table split by company, dates stored as numbers, statuses as two-char strings, and hundreds of code tables folded into one. This guide is the shortcut past every one of those, with copy-ready Databricks SQL. Last verified July 2026.
Column prefixes & aliases
Every physical column in M3 is the table's 2-char prefix plus a 4-char field alias. The alias is the concept, and it travels: MMITNO on MITMAS, MBITNO on MITBAL, and OBITNO on OOLINE are all ITNO — the item number. Once you internalize the prefix rule, an unfamiliar table stops being a wall of arbitrary six-char names.
It also gives you the join rule: match aliases across tables. A column whose last four characters equal another table's alias is almost always the same concept, and joining on that pair (plus CONO — see the next section) is how M3 tables connect. This reference shows both the physical name and the alias on every field so you never have to strip prefixes in your head.
-- Every physical column = the table's 2-char prefix + a 4-char field alias.
-- The alias is the concept: MMITNO on MITMAS, MBITNO on MITBAL, and OBITNO on
-- OOLINE are all ITNO — the item number. Join by matching aliases across tables.
SELECT mm.MMITNO, mm.MMITDS, mb.MBWHLO, mb.MBSTQT
FROM MITBAL mb
JOIN MITMAS mm
ON mm.MMITNO = mb.MBITNO -- same alias (ITNO), different prefixes
AND mm.MMCONO = mb.MBCONO
WHERE mb.MBCONO = 100
-- This reference shows both the physical name and the alias on every field.CONO (company partitioning)
CONO— the numeric company key — sits on essentially every M3 table, under each table's own prefix (MMCONO, OBCONO, CTCONO). Natural keys are only unique within a company, so every query starts by pinning the company: WHERE {px}CONO = your company.
Where it bites is the join. Join two tables on the business key alone and any key that repeats across companies matches every company's copy — the join fans out silently, row counts inflate, and nothing errors. Join on CONO too, always. The generated SQL and the copyable join clauses in this reference include the CONO equality for exactly this reason.
-- CONO is the numeric company key on essentially every table. Two rules:
-- 1. Always filter to your company: WHERE {px}CONO = <your company>.
-- 2. When you join two tables, join on CONO TOO — or a business key that
-- repeats across companies fans the join out into a cross-company mess.
SELECT ol.OBORNO, ol.OBITNO, ol.OBORQT
FROM OOLINE ol
JOIN OOHEAD oh
ON oh.OAORNO = ol.OBORNO
AND oh.OACONO = ol.OBCONO -- the join newcomers forget
WHERE ol.OBCONO = 100DIVI / FACI / WHLO hierarchy
M3's structure runs company (CONO) → division (DIVI) → facility (FACI) → warehouse (WHLO). Master data and configuration can attach at different levels of that ladder, and transactional records usually carry several of the keys at once.
For inventory questions the practical consequence is grain: balances live at different levels. MITBAL is the item/warehouse record — planning policy plus warehouse-level on-hand — while MITLOC is item/location/lot, the finest grain M3 tracks stock at. Pick the grain that matches the question; a location-level answer summed from MITBAL doesn't exist, and a warehouse-level answer from MITLOC must be aggregated deliberately.
-- The structure hierarchy: company (CONO) → division (DIVI) → facility (FACI)
-- → warehouse (WHLO). Balances live at different grains — pick the right one:
-- MITBAL = item / warehouse (planning policy + warehouse on-hand)
-- MITLOC = item / location / lot (physical stock at the finest grain)
SELECT mb.MBWHLO, SUM(mb.MBSTQT) AS on_hand
FROM MITBAL mb
WHERE mb.MBCONO = 100
GROUP BY mb.MBWHLO
-- For location- or lot-level questions, aggregate MITLOC instead — MITBAL's
-- warehouse figures are the roll-up of those rows.Numeric YYYYMMDD dates
M3 dates are numeric YYYYMMDD values — 0 means “no date.” A raw MIN over a delivery-date column returns 0, an AVG is meaningless, and a numeric BETWEEN quietly scoops up every unset row. NULL-wrap the 0 sentinel and convert with TO_DATE — the snippet shows the exact expression the generated boilerplate uses, so the docs and the generators never drift.
One timezone caution: the entry and change dates (RGDT, LMDT — see audit columns) are stored in the server's timezone, not UTC. Don't run UTC conversions over them, and don't expect midnight boundaries to line up with your business timezone.
-- M3 stores dates as numeric YYYYMMDD, and 0 means "no date". A raw MIN or AVG
-- over such a column lies. NULL-wrap the 0 sentinel and convert (this is the
-- exact expression the generated SQL uses):
SELECT
mt.MTITNO,
CASE WHEN mt.MTTRDT = 0 THEN NULL ELSE TO_DATE(CAST(CAST(mt.MTTRDT AS BIGINT) AS STRING), 'yyyyMMdd') END AS transaction_date
FROM MITTRA mt
WHERE mt.MTCONO = 100
-- Entry/change dates (RGDT / LMDT) are stamped in the server's timezone, not
-- UTC — don't shift them as if they were UTC values.Two-char status ladders
M3 statuses are two-char numeric strings on ladders that run from '10' to '99'. Order headers carry both ends of their lines' ladder: OOHEAD holds ORSL (lowest line status) and ORST (highest), and MPHEAD mirrors the pattern with PUSL/PUST. An order is “fully X” only when the lowest status has reached X — reading just the highest overstates progress.
The ladder policy here is deliberate: rung meanings vary by order type and configuration, so this site decodes only ladders whose values are primary-source verified. Today that is the customer-order ladder (ORST) — decoded in the snippet above and on the OOHEAD and OOLINE pages — while the purchase-order (PUSL/PUST), item/customer/supplier (STAT), and manufacturing-order (WHST) ladders still render as raw codes. Until a ladder is verified, generated SQL emits a pointer to this section instead of a guessed CASE, because a partially-wrong decode is worse than none. Decode unverified ladders against your own configuration, and always keep an ELSE passthrough — configurations extend and skip rungs.
-- M3 statuses are two-char numeric strings, ladders running '10'…'99'.
-- Order headers carry BOTH ends of their lines' ladder:
-- OOHEAD: OAORSL (lowest line status) / OAORST (highest line status)
-- MPHEAD: IAPUSL (lowest) / IAPUST (highest)
SELECT
oh.OAORNO,
CASE oh.OAORST WHEN '10' THEN 'Preliminary' WHEN '20' THEN 'Final (entered)' WHEN '22' THEN 'Not allocated' WHEN '23' THEN 'Partially allocated' WHEN '33' THEN 'Allocated' WHEN '44' THEN 'Picking list printed' WHEN '66' THEN 'Delivered' WHEN '77' THEN 'Invoiced' ELSE oh.OAORST END AS highest_line_status,
CASE oh.OAORSL WHEN '10' THEN 'Preliminary' WHEN '20' THEN 'Final (entered)' WHEN '22' THEN 'Not allocated' WHEN '23' THEN 'Partially allocated' WHEN '33' THEN 'Allocated' WHEN '44' THEN 'Picking list printed' WHEN '66' THEN 'Delivered' WHEN '77' THEN 'Invoiced' ELSE oh.OAORSL END AS lowest_line_status
FROM OOHEAD oh
WHERE oh.OACONO = 100CSYTAB (generic code tables)
Hundreds of logical code tables share the one physical CSYTAB, discriminated by the STCO constant. A coded field elsewhere decodes by matching its value against STKY under the right STCO, then selecting the CTTX40text. If you've worked in JD Edwards: this is M3's UDC.
Two traps in the join. Omit the STCO restriction and you match unrelated code tables that happen to share a key value. And texts repeat per language code — filter CTLNCD to one language or every decoded name multiplies by the number of languages installed.
-- Hundreds of logical code tables share the one physical CSYTAB, discriminated
-- by the STCO constant. Decode a coded field by matching its value against STKY
-- under the right STCO — and filter to one language, or names multiply:
SELECT mm.MMITNO, mm.MMITGR, ct.CTTX40 AS item_group_name
FROM MITMAS mm
LEFT JOIN CSYTAB ct
ON ct.CTCONO = mm.MMCONO
AND ct.CTSTCO = '<code table>' -- the STCO constant naming the logical code table
AND ct.CTSTKY = mm.MMITGR -- the coded field's value
AND ct.CTLNCD = 'GB' -- one language code
WHERE mm.MMCONO = 100Audit columns
Every table carries the same audit set under its own prefix: {px}RGDT (entry date), {px}RGTM (entry time), {px}LMDT (change date), {px}CHNO (change number), and {px}CHID(changed by). They're bookkeeping, not business data — which is why the generated SQL on this site omits them from SELECT lists.
The one analytics job they do well is change detection: filter on LMDT and tie-break with CHNO to find rows touched since your last load. For landed Data Lake objects there is a better watermark — see the extraction guide — but in-table LMDT + CHNO is the fallback that works everywhere.
-- Every table carries the same audit set under its own prefix:
-- {px}RGDT entry date · {px}RGTM entry time · {px}LMDT change date
-- {px}CHNO change number · {px}CHID changed by
SELECT mm.MMITNO, mm.MMLMDT, mm.MMCHNO
FROM MITMAS mm
WHERE mm.MMCONO = 100
AND mm.MMLMDT >= 20260101 -- change detection: LMDT, tie-broken by CHNO
-- The generated SQL on this site omits the audit columns from SELECT lists.Units of measure & quantities
Balances and stock movements are stored in the item's basic unit — UNMS on MITMAS. Ordered quantities, though, can be entered in other units: purchase lines use the purchase unit. Compare an ordered quantity against an on-hand balance without normalizing and the numbers are in different units — close enough to look right, wrong enough to matter.
Normalize to the basic unit before any cross-table quantity math. The conversion factors live in MITAUN— one row per item, unit type, and alternate unit, with the factor (and its multiply-or-divide form) back to the basic unit. Treat any quantity not on a balance or movement table as “unit unknown until checked against MITAUN.”
-- Balances and movements are stored in the item's basic unit (UNMS on MITMAS).
-- Ordered quantities can be entered in other units — purchase lines use the
-- purchase unit — so compare quantities only after normalizing to the basic unit.
SELECT mm.MMITNO, mm.MMUNMS AS basic_unit, ib.IBORQA AS ordered_qty
FROM MPLINE ib
JOIN MITMAS mm
ON mm.MMITNO = ib.IBITNO
AND mm.MMCONO = ib.IBCONO
WHERE ib.IBCONO = 100
-- IBORQA is in the purchase unit; MITBAL/MITTRA quantities are in MMUNMS.Item keys: ITNO, BANO, REPN
ITNO is the universal item key — every balance, order line, and movement carries it. Below the item, lot-controlled stock adds BANO (the lot number) at the MITLOC grain, blank when the item isn't lot-controlled, and receipts are further split by REPN (the receiving number). Group by the keys your question actually needs; summing across lots when you meant one lot — or forgetting that non-lot items carry a blank BANO — skews on-hand.
One area this reference keeps deliberately conservative: fashion deployments with style/SKU matrix items structure ITNO itself. How that structure works varies by configuration, so it's left unstated here until verified.
-- ITNO is the universal item key. Lot-controlled stock adds BANO at the MITLOC
-- grain (blank when the item isn't lot-controlled); receipts are further split
-- by the receiving number REPN.
SELECT ml.MLITNO, ml.MLBANO, SUM(ml.MLSTQT) AS on_hand
FROM MITLOC ml
WHERE ml.MLCONO = 100
GROUP BY ml.MLITNO, ml.MLBANOData Lake variations
If you land raw Data Lake objects, every record carries variation metadata — the Data Fabric properties identifierpaths, variationpath, deleteindicator, and archiveindicator. Default Compass queries return the current non-deleted state for you, but raw landed objects include every variation of each record: the full change history, deletes and archives included.
So dedup before analytics: keep the highest variation per primary key and honor the delete indicator, or every updated record counts multiple times. The extraction guide turns this into a full incremental MERGE pattern.
-- Raw landed Data Lake objects carry variation metadata on every record
-- (Data Fabric properties): identifierpaths, variationpath, deleteindicator,
-- archiveindicator. Default Compass queries return the current non-deleted
-- state — but raw landed objects include EVERY variation. Dedup before analytics:
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY MBCONO, MBWHLO, MBITNO -- the primary key
ORDER BY variationpath DESC -- keep the highest variation
) AS rn
FROM MITBAL
) WHERE rn = 1 AND deleteindicator = false