The Infor M3 Quirks Guide

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.

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.

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.

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) use server time. Cloud Edition server time is UTC; on-premises server time follows the deployment’s configured system time. Establish that source timezone before converting business-day boundaries.

M3 statuses are two-char numeric strings on ladders that generally run '00'–'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.

On purchase orders the pair is not header-only. Infor documents the highest/lowest pair on the order lines as well, so MPLINE carries PUSL alongside PUST. The worked example in Infor’s Purchase Order documentation is exactly the trap: a partly received line sits at lowest 35, highest 50, so WHERE IBPUST >= '50' counts it as fully received while part of its quantity has not yet arrived. Test IBPUSL for “fully received,” exactly as you would test a header’s lowest status.

The same rule operates inside a single line’s status. A customer order line status is not one rung — it is two independent numerals. The first is the lowest degree of processing reached across the line’s quantity; the second is the furthest. The documented digits are 2 left to allocate, 3 allocated, 4 printed on a picking list, 6 delivered, 7 invoiced, and 9 deleted, with 05 (quotation), 10 (preliminary) and 99 (completed without delivery) sitting outside that scale. So 46 is not a rung between 44 and 66 — it is a line partly picked and partly delivered.

This is the trap the ladder costs you if you miss it. Writing WHERE OBORST >= '66' to find delivered lines is a string comparison against a two-digit encoding, so it silently drops every partially-delivered line at 46, 47 or 67. Test the numeral you actually mean instead — see the snippet above. And note 99: a line flagged completed without delivery is closed but never shipped, so decoding only 66/77 reads it as still open.

Delivery progress status is direction-scoped. MHDISH carries one progress-status field, OQPGRS, but two ladders, selected by the delivery’s direction (OQINOU). Outbound deliveries walk the release-and-picking rungs — 00 not released for auto allocation, 01 released, 02 failed allocation control, 03 stopped by credit check or customer stop, 05 ready to release for picking, 50 picking list created, 90 fully reported — while inbound deliveries walk 70 shipped from consignor, 75 partly received, 90 fully received. The rung sets are disjoint except at 90, where the meanings are different events: outbound 90 is shipped, inbound 90 is received. So WHERE OQPGRS = '90' over an unfiltered MHDISH silently unions shipments with receipts — always pair OQPGRS with an OQINOU filter. Because one ladder key cannot carry both rung sets, this site renders OQPGRS raw with a pointer here rather than decoding it.

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 five ladders — the customer-order line ladder and the customer-order header pair, which are separate documents with different value sets (a released order’s header sits at 20; its lines never do), the item lifecycle ladder on MITMAS.MMSTAT, the purchase-order line ladder on MPLINE.IBPUSL/IBPUST, and the delivery packing status on MHDISH.OQPIST (00 packing not used through 30 packing completed, fully enumerated). The purchase-order line ladder is deliberately partial: the entry, receipt, inspection and put-away rungs are verified and decoded, while the confirmation-range and invoice-matching/closing rungs surface as raw codes through the ELSE passthrough. The purchase-order header pair (IAPUSL/IAPUST on MPHEAD), the manufacturing-order (WHST) ladder, and the direction-scoped delivery progress status (OQPGRS, above) still render as raw codes, as do the warehouse, customer and supplier statuses on MITBAL/OCUSMA/CIDMAS — those three share the STAT alias with the item ladder but are entirely different ladders, so the item decode is deliberately not applied to them. 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.

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 CTTX40 text. 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.

When an implementer needs a field M3 doesn’t ship, it usually doesn’t become a new column on the host table — it lands in one of three generic extension tables: CUGEX1 (mixed alpha, numeric and date fields), CUGEX2 (numeric) and CUGEX3 (alphanumeric), maintained through the CUSEXTMI API and configured in CMS080/CMS081. Each row names its host table in a FILE column and identifies the host record through up to eight generic key columns, PK01–PK08. Which host key each PK column holds is a configuration choice, made per site.

That is the analytics warning. You cannot write the join without knowing your own site’s configuration — the same PK01 may be a customer number on one deployment and an item number on the next, and the physical key layout is only partly documented publicly. So these ship here as a quirk rather than as catalog tables: check the extension setup before you join, treat the snippet above as the shape and not the answer, and confirm which columns actually exist in your landed schema.

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.

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.”

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.

Data Catalog additional properties identify the JSON paths used for record identity and variation, plus the paths and values that mean deleted or archived. Those configuration keys are not universal payload column names. Resolve them for each object, then alias the payload properties to stable bronze names before applying the example. Whether Compass hands you the current, non-deleted and non-archived state now depends on the object’s query processing mode: in analytical mode the deduplication is done for you, but in transactional mode — the mode an object gets when its Mode Configuration switch is off — no record-level deduplication is applied and every variation comes back as its own row. And raw landed objects always 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.