M3 Reference

MITLOC

Prefix: MLbalance

The location-level balance — one row per item, warehouse, location, lot, and receipt, holding the physical on-hand at the finest grain M3 tracks stock; warehouse-level figures in MITBAL are the roll-up of these rows

Module: Inventory & WarehouseCompany-partitioned (CONO)Parent: MITBAL

What the badges mean

Prefix: MM: Prefix: the table’s 2-character physical-column prefix (e.g. MM on MITMAS) — every field alias on the table carries it. Join by matching aliases across tables, not full column names (see the quirks guide).
master: Data class: what the table holds — master data, balances, transaction documents, code/reference tables, or history.
Shared across companies: The table carries no CONO — rows aren’t partitioned per company (see the quirks guide).
Division-level: The table carries DIVI — rows are scoped below company to a division, one layer more granular than CONO alone (see the quirks guide).

Used in the library

KPI definitions

K-05Inventory Turns

K-06Days of Supply (DOS / DIO)

K-10Excess & Obsolete (E&O)

Dashboard patterns

D-01Inventory Health

Cross-ERP guides

GuideERP Deletes & Change Tracking

GuideSource History & Ingestion Path

GuideMedallion Layer Contracts for ERP Data

GuideInventory Snapshot Patterns

Parent & satellites

MITLOC is a satellite of MITBAL — many rows here per MITBAL row; join on WHLO + ITNO plus the CONO (exact ON clause under Join details below).

Fields

8 fields · 6 key

8 fields.

Table fields: key flag, field name, column alias, description, data type, length, and decode flags. 8 fields.
Key	Field	Alias	Description	Type
Key	MLCONO	CONO	Company	numeric
Key	MLWHLO	WHLO	Warehouse	alphanumeric
Key	MLITNO	ITNO	Item number	alphanumeric
Key	MLWHSL	WHSL	Stock location within the warehouse	alphanumeric
Key	MLBANO	BANO	Lot (batch) number — blank for non-lot-controlled items	alphanumeric
Key	MLREPN	REPN	Receiving number distinguishing receipts of the same item and lot in the same location	numeric
	MLSTQT	STQT	On-hand balance at this location/lot grain, in the basic unit	numeric
	MLALQT	ALQT	Allocated quantity at this location/lot grain	numeric

Field provenance: hand-curated.

Boilerplate SQL

Starting point for reading MITLOC on Databricks — numeric YYYYMMDD dates are wrapped to NULL, verified status ladders are decoded, and the CONO anchor is in place. Set your Unity Catalog location, company, and filter values below; they’re substituted into the SQL and the copy button.

Query parameters

CatalogSchemaCompany (CONO)Warehouse (WHLO)Item number (ITNO)Location (WHSL)Lot number (BANO)

Include optional filters (4)

7 parameters not filled: <catalog>, <schema>, <company>, <WHLO>, <ITNO>, <WHSL>, <BANO>

-- ============================================================
-- Table  : MITLOC — The location-level balance — one row per item, warehouse, location, lot, and receipt, holding the physical on-hand at the finest grain M3 tracks stock; warehouse-level figures in MITBAL are the roll-up of these rows
-- Purpose: Column-selected read of MITLOC — auto-generated from field metadata
-- Grain  : One row per company (CONO) + MLWHLO + MLITNO + MLWHSL + MLBANO + MLREPN
-- 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. Audit columns (RGDT/RGTM/LMDT/CHNO/CHID) omitted — see the quirks guide.
-- ============================================================
SELECT
  il.MLCONO AS "Company",
  il.MLWHLO AS "Warehouse",
  il.MLITNO AS "Item number",
  il.MLWHSL AS "Stock location within the warehouse",
  il.MLBANO AS "Lot (batch) number — blank for non-lot-controlled items",
  il.MLREPN AS "Receiving number distinguishing receipts of the same item and lot in the same location",
  il.MLSTQT AS "On-hand balance at this location/lot grain, in the basic unit",
  il.MLALQT AS "Allocated quantity at this location/lot grain"
FROM <catalog>.<schema>.MITLOC il
WHERE
  il.MLCONO = <company>
  -- AND il.MLWHLO = '<WHLO>'
  -- AND il.MLITNO = '<ITNO>'
  -- AND il.MLWHSL = '<WHSL>'
  -- AND il.MLBANO = '<BANO>'
ORDER BY il.MLWHLO;

Verified September 2026

Relationships

Diagram of 1-hop neighbors — join details below. CSYTAB decode edges are highlighted; they’re the joins newcomers most often get wrong.

Join details

MITLOCMITMASforeign key · N:1
-- Uses this catalog's prefixed M3 column names; map raw Data Lake properties through Data Catalog first. ON MITLOC.MLITNO = MITMAS.MMITNO AND MITLOC.MLCONO = MITMAS.MMCONO
MITLOCMITWHLforeign key · N:1
-- Uses this catalog's prefixed M3 column names; map raw Data Lake properties through Data Catalog first. ON MITLOC.MLWHLO = MITWHL.MWWHLO AND MITLOC.MLCONO = MITWHL.MWCONO
MITLOCMITBALforeign key · N:1
-- Uses this catalog's prefixed M3 column names; map raw Data Lake properties through Data Catalog first. ON MITLOC.MLWHLO = MITBAL.MBWHLO AND MITLOC.MLITNO = MITBAL.MBITNO AND MITLOC.MLCONO = MITBAL.MBCONO
MITLOCMILOMAforeign key · N:1
-- Uses this catalog's prefixed M3 column names; map raw Data Lake properties through Data Catalog first. ON MITLOC.MLBANO = MILOMA.LMBANO AND MITLOC.MLITNO = MILOMA.LMITNO AND MITLOC.MLCONO = MILOMA.LMCONO