Infor M3 & CloudSuiteFree referencePDF, Excel, Word exportInfor M3All Infor M3 tools

Infor M3 Inventory and Allocation Tables: MITBAL, MITLOC, MITALO, MITTRA

This reference covers the M3 13.x tables behind stock and allocation: MITBAL for item and warehouse balances, MITLOC for balance identities, MITALO for allocations, MITPLO for the material plan and MITTRA for stock transactions, with lot, warehouse and location masters. It lists the key columns, joins and maintaining programs.

Download:

Table	Column	Description	Key	Join to	Maintained by program	Notes
MITMAS	MMITNO	Item number (item master)	PK with MMCONO	All tables below on ITNO	MMS001	Link only; use for item name, type, basic U/M
MITWHL	MWWHLO	Warehouse	PK with MWCONO	MITBAL.MBWHLO	MMS005
MITWHL	MWWHNM	Warehouse name	-	-	MMS005
MITWHL	MWFACI	Facility of the warehouse	-	CFACIL	MMS005
MITBAL	MBWHLO / MBITNO	Warehouse / item	PK with MBCONO	MITLOC, MITALO, MITPLO on WHLO and ITNO	MMS002	One row per item and warehouse
MITBAL	MBSTQT	On-hand balance	-	-	Updated by transactions	Sum of the warehouse; detail is in MITLOC
MITBAL	MBALQT	Allocated quantity	-	-	Updated by allocation	Should equal the sum of MLALQT for the item and warehouse
MITBAL	MBREOP	Reorder point	-	-	MMS002
MITBAL	MBSSQT	Safety stock	-	-	MMS002
MITBAL	MBWHSL	Default location	-	MITPCE.MSWHSL	MMS002
MITBAL	MBSTAT	Item / warehouse status	-	-	MMS002
MITLOC	MLWHLO / MLITNO	Warehouse / item	PK part	MITBAL	MMS060	One row per balance identity
MITLOC	MLWHSL	Location	PK part	MITPCE.MSWHSL	MMS060
MITLOC	MLBANO	Lot number	PK part	MILOMA.LMBANO	MMS060	Blank for items without lot control
MITLOC	MLCAMU	Container	PK part	-	MMS060
MITLOC	MLREPN	Receiving number	PK part	MITTRA	MMS060
MITLOC	MLSTQT	On-hand balance for the balance ID	-	-	Updated by transactions
MITLOC	MLALQT	Allocated quantity for the balance ID	-	-	Updated by allocation	Available on a balance ID = MLSTQT - MLALQT if its status allows allocation
MITLOC	MLSTAS	Balance ID status	-	-	MMS060	Only approved stock is normally allocatable
MITALO	MQWHLO / MQITNO	Warehouse / item	Key part	MITBAL	Allocation	One row per allocation of a balance ID to an order line
MITALO	MQTTYP	Transaction type of the demand	Key part	-	Allocation	Same numbering as MITTRA, for example 31 for customer order
MITALO	MQRIDN / MQRIDL	Order number / order line	Key part	OOLINE.OBORNO / OBPONR when MQTTYP is 31	Allocation
MITALO	MQWHSL / MQBANO	Allocated location / lot	-	MITLOC	Allocation	Ties the allocation to a balance ID
MITALO	MQALQT	Allocated quantity	-	-	Allocation
MITPLO	MOWHLO / MOITNO	Warehouse / item	Key part	MITBAL	MMS080 Material Plan	Planned supply and demand lines
MITPLO	MOPLDT	Planning date	Key part	-	MMS080
MITPLO	MOORCA	Order category	-	-	MMS080	Tells which order type created the line
MITPLO	MORIDN / MORIDL	Order number / line	-	Order tables	MMS080
MITPLO	MOTRQT	Transaction quantity	-	-	MMS080	Positive supply, negative demand
MITTRA	MTWHLO / MTITNO	Warehouse / item	Key part	MITBAL	Every stock transaction	Stock transaction history
MITTRA	MTTRDT	Transaction date (YYYYMMDD)	Key part	-	Every stock transaction	Filter by date range; the table is large
MITTRA	MTTTYP	Transaction type	-	-	Every stock transaction	For example 10 MO receipt, 11 MO issue, 25 PO receipt, 31 CO issue; read the full list in the field help for your release
MITTRA	MTTRQT	Transaction quantity	-	-	Every stock transaction	Signed: issues negative, receipts positive
MITTRA	MTRIDN / MTRIDL	Reference order number / line	-	OOLINE, MPLINE, MWOHED by type	Every stock transaction
MITTRA	MTWHSL / MTBANO	Location / lot	-	MITLOC	Every stock transaction
MILOMA	LMITNO / LMBANO	Item / lot number	PK with LMCONO	MITLOC.MLITNO / MLBANO	MMS235	Lot master, shared across warehouses
MILOMA	LMEXPI	Expiration date	-	-	MMS235	Used in FEFO allocation
MITPCE	MSWHLO / MSWHSL	Warehouse / stock location	PK with MSCONO	MITLOC.MLWHSL	MMS010
MITPCE	MSSLTP	Stock zone	-	-	MMS010

Column names follow the M3 convention of a two-letter table prefix plus the field name (MB for MITBAL, ML for MITLOC, MQ for MITALO, MO for MITPLO, MT for MITTRA). Confirm each column and the transaction type list in M3 Metadata Publisher (MDP) and field help for your 13.x release.

Get your M3 stock query reviewed

Send us your availability or allocation query and we will check it against how MITBAL, MITLOC and MITALO relate in your release.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Search by table, column or concept (allocated, lot, location). Read the tables top down: MITBAL gives one row per item and warehouse with totals, MITLOC breaks that into balance identities (location, lot, container, receiving number), MITALO shows which balance IDs are allocated to which order lines, MITPLO is the time-phased material plan, and MITTRA is the history of every movement. The Notes column explains how quantities relate, which is where most report errors come from.

Where this lives in M3

MMS002 maintains item and warehouse data (MITBAL), MMS060 shows balance identities (MITLOC), MMS080 is the material plan (MITPLO), MMS010 stock locations (MITPCE), MMS005 warehouses (MITWHL) and MMS235 lots (MILOMA). Allocation, automatic or manual, writes MITALO. Stock transactions from receipts, issues, counts and moves land in MITTRA; transactions from a WMS arrive through MHS850MI or MMS850MI and land in MITTRA once processed.

MBSTQT is the sum of MLSTQT for the item and warehouse
MBALQT matches the sum of MLALQT and of MQALQT
MITALO links allocations to order lines via TTYP, RIDN, RIDL
MITTRA grows fast; always filter on date

Worked example: why is stock not available?

A planner sees 500 on hand for an item in warehouse 100 but cannot allocate. MITBAL shows MBSTQT 500 and MBALQT 300. MITLOC shows three balance IDs: one with 300 fully allocated (MLALQT 300), and two with 100 each where MLSTAS is not approved, so they cannot be allocated. MITALO rows with MQTTYP 31 show which customer order lines hold the 300. The fix is quality approval of the two lots, not a stock adjustment.

Version differences and gotchas

Do not read available stock from MITBAL alone; balance ID status, lot expiry and allocation rules decide what is allocatable. Transaction quantities in MITTRA are signed, so sum them as they are. Lot masters (MILOMA) are not per warehouse. Some columns are added between 13.x releases, so confirm in MDP. For writes, use MMS850MI, MHS850MI or the programs, never direct SQL.

Frequently Asked Questions

What is the M3 allocation table?

MITALO holds allocations: each row links a balance identity (warehouse, location, lot) to a demand line identified by transaction type, order number and line, with the allocated quantity. MITBAL.MBALQT and MITLOC.MLALQT hold the totals.

What is the difference between MITBAL and MITLOC?

MITBAL has one row per item and warehouse with total on-hand and allocated quantities and planning parameters. MITLOC has one row per balance identity, meaning location, lot, container and receiving number, with the quantities at that level. Totals in MITBAL equal the sum of MITLOC rows.

Where are stock transactions stored in M3?

MITTRA stores every stock transaction with warehouse, item, date, transaction type, signed quantity and the reference order. Transaction types use M3 numbering, for example 31 for customer order issues and 25 for purchase order receipts.

How do I get a list of M3 database tables?

M3 Metadata Publisher (MDP) lists every table, column and index for your release. For an overview of the main tables by area see the M3 core database tables reference; this page goes to column level for inventory and allocation.

If you want planners to ask M3 why stock is not available in plain English on your own servers, we can build that on these tables with read-only access.

Talk to an Expert Explore More Free Tools