ERP Table and Field Explorer

Infor M3 Database Tables and Field Explorer

Search the core Infor M3 (formerly Movex) Business Engine tables by table name, column or description, see each table's key, important columns and joins, then pick two to four tables to get a JOIN skeleton. Every M3 column is a two-character table prefix plus a field name, so ITNO is MMITNO in MITMAS and OBITNO in OOLINE, and nearly every table starts its key with the company number CONO.

48 tables, 373 documented fields and 105 join paths across 11 modules. Free, runs in your browser, no login needed.

1Find a table by name, description or field

Naming: M3 table names are six characters (MITMAS, OOLINE). Each column is the table's two-character prefix plus a four-character field name: MM for MITMAS, MB for MITBAL, OA for OOHEAD, OB for OOLINE. The company number (xxCONO, numeric 0 to 999) leads almost every key and division (xxDIVI, up to 3 characters, blank for company level) leads the finance keys. On-premises installations keep the data in the MVXJDTA schema; dates are numeric YYYYMMDD.

Tables by module

2Pick tables

48 of 48

Items

MITMAS

Item master: one row per item and company with name, type, group, unit of measure and status. Maintained in MMS001 (Item. Open).

Also known as: MMS001, Item. Open, item master, ItemMaster BOD

Key:MMCONOMMITNO
FieldMeaning
MMCONOCompany number (numeric, 0 to 999). Always filter and join on it.
MMITNOItem number.
MMITDSItem name.
MMFUDSItem description (longer text, often used in searches).
MMSTATItem status.
MMITTYItem type (defined in the item type table MITTTY).
MMITGRItem group.
MMITCLProduct group.
MMUNMSBasic unit of measure. Quantities in most tables are in this unit.
MMRESPResponsible user.
MMTPCDItem category code.

Joins

  • MITMAS.MMCONO = MITBAL.MBCONO AND MITMAS.MMITNO = MITBAL.MBITNO

    One row per warehouse the item is connected to.

  • MITMAS.MMCONO = MITFAC.M9CONO AND MITMAS.MMITNO = MITFAC.M9ITNO

    One row per facility.

  • MITMAS.MMCONO = MITAUN.MUCONO AND MITMAS.MMITNO = MITAUN.MUITNO

    Alternate units of measure.

  • MITMAS.MMCONO = MITPOP.MPCONO AND MITMAS.MMITNO = MITPOP.MPITNO

    Alias numbers such as EAN or supplier item numbers.

  • MITMAS.MMCONO = MPDHED.PHCONO AND MITMAS.MMITNO = MPDHED.PHPRNO

    Product structure header; the product number is the item number.

  • MITMAS.MMCONO = MILOMA.LMCONO AND MITMAS.MMITNO = MILOMA.LMITNO

    Lots of the item.

  • MITMAS.MMCONO = MITLOC.MLCONO AND MITMAS.MMITNO = MITLOC.MLITNO
  • MITMAS.MMCONO = MILOIN.LICONO AND MITMAS.MMITNO = MILOIN.LIITNO
  • MITMAS.MMCONO = MITTRA.MTCONO AND MITMAS.MMITNO = MITTRA.MTITNO
  • MITMAS.MMCONO = MGLINE.MRCONO AND MITMAS.MMITNO = MGLINE.MRITNO
  • MITMAS.MMCONO = MHPICD.H6CONO AND MITMAS.MMITNO = MHPICD.H6ITNO
  • MITMAS.MMCONO = MPDMAT.PMCONO AND MITMAS.MMITNO = MPDMAT.PMMTNO
  • MITMAS.MMCONO = MWOHED.VHCONO AND MITMAS.MMITNO = MWOHED.VHPRNO
  • MITMAS.MMCONO = MWOMAT.VMCONO AND MITMAS.MMITNO = MWOMAT.VMMTNO
  • MITMAS.MMCONO = MMOPLP.ROCONO AND MITMAS.MMITNO = MMOPLP.ROPRNO
  • MITMAS.MMCONO = MMOHED.QHCONO AND MITMAS.MMITNO = MMOHED.QHPRNO
  • MITMAS.MMCONO = OOLINE.OBCONO AND MITMAS.MMITNO = OOLINE.OBITNO
  • MITMAS.MMCONO = MPLINE.IBCONO AND MITMAS.MMITNO = MPLINE.IBITNO
  • MITMAS.MMCONO = MPOPLP.POCONO AND MITMAS.MMITNO = MPOPLP.POITNO

Infor states that MITMAS, MITBAL and MITFAC are updated by MMS001 (Item. Open). Item names and code descriptions shown in reports come from CSYTAB for groups such as ITGR and ITCL. MMITNO, MMITDS, MMFUDS, MMSTAT, MMITTY, MMITGR, MMRESP, MMTPCD and MMUNMS appear in published M3 SQL and XtendM3 code; MMITCL follows the prefix rule.

3Generate a JOIN skeleton (2 to 4 tables)

Starters:
Output
-- Query skeleton generated by netray.co/tools/table-explorer
-- Dialect: SQL Server (T-SQL). Review keys, filters and indexes before running on production.
-- Filter the first table on its company column, for example WHERE MMCONO = 100. Joins below already match the company columns.
-- Dates are numeric YYYYMMDD and quantities are in the basic unit of measure unless the column says alternate.
-- Read only. Write through M3 API (MI) transactions or the programs, never with direct UPDATE statements. M3 CE (cloud) has no direct database access: publish the tables to Data Lake and query them there.

SELECT TOP 100
    oohe.OACONO,
    oohe.OAORNO,
    oohe.OADIVI,
    oohe.OAORTP,
    oohe.OAFACI,
    ooli.OBCONO,
    ooli.OBORNO,
    ooli.OBPONR,
    ooli.OBPOSX,
    ooli.OBITNO,
    ocus.OKCONO,
    ocus.OKCUNO,
    ocus.OKCUNM,
    ocus.OKSTAT,
    ocus.OKCUA1,
    mitm.MMCONO,
    mitm.MMITNO,
    mitm.MMITDS,
    mitm.MMFUDS,
    mitm.MMSTAT
FROM MVXJDTA.OOHEAD AS oohe
-- OOHEAD to OOLINE: Order lines.
LEFT JOIN MVXJDTA.OOLINE AS ooli
    ON oohe.OACONO = ooli.OBCONO
   AND oohe.OAORNO = ooli.OBORNO
LEFT JOIN MVXJDTA.OCUSMA AS ocus
    ON oohe.OACONO = ocus.OKCONO
   AND oohe.OACUNO = ocus.OKCUNO
LEFT JOIN MVXJDTA.MITMAS AS mitm
    ON ooli.OBCONO = mitm.MMCONO
   AND ooli.OBITNO = mitm.MMITNO
ORDER BY oohe.OACONO, oohe.OAORNO;
Free, optional. Reopen it on any device.
Download:

How to use it

  1. 1

    Search

    Type a table name such as OOLINE, a column such as MBSTQT, a field name such as ITNO, or a program such as MMS002. Column matches are highlighted under each table.

  2. 2

    Filter and inspect

    Narrow by module, then open a table to see its key, the columns that matter and the tables it joins to. Click a join to jump to that table.

  3. 3

    Build the join

    Add two to four tables with the plus button or a starter. Every generated join matches the company columns first (for example MMCONO = MBCONO) and then the documented keys, with the MVXJDTA schema.

  4. 4

    Keep it

    Star tables as favorites, export to PDF, Excel, Word or CSV, and optionally save the setup to your Netray account to reopen it later.

Questions about Infor M3 (formerly Movex) tables

Why do M3 column names start with two letters like MM or OB?

Every M3 table has a two-character prefix that is added to each of its field names: MM for MITMAS, MB for MITBAL, OA for OOHEAD, OB for OOLINE, OK for OCUSMA, IA and IB for MPHEAD and MPLINE. The four characters after the prefix are the field name used in M3 API transactions, so ITNO in MMS200MI or OIS100MI is MMITNO in MITMAS and OBITNO in OOLINE. Joins therefore always map one prefix to another, for example MMITNO = MBITNO.

What is CONO and why is it in every join?

CONO is the company number, an integer from 0 to 999. One M3 database holds several companies, so nearly every key starts with the company column (MMCONO, OBCONO). Filter the first table on it and join it between tables, or a query returns rows from every company. Finance tables add the division (DIVI) next, and user records in CMNUSR sit in company 0 with a blank division.

Which tables hold customer orders?

OOHEAD is the order header and OOLINE holds the lines keyed by order number, line and suffix (ORNO, PONR, POSX). Deliveries are in MHDISH and MHDISL for all order categories, and in ODHEAD and ODLINE for the customer order and invoicing view. Customers are in OCUSMA, maintained in CRS610, with delivery addresses in OCUSAD.

Where is the bill of material stored in M3?

M3 calls it the product structure. MPDHED is the header per facility, product and structure type, MPDMAT holds the materials by sequence number and MPDOPE the operations, with work centers in MPDWCT. A manufacturing order in MWOHED has its own material and operation rows in MWOMAT and MWOOPE, keyed by facility, product and MO number.

What is the difference between MITBAL and MITLOC?

MITBAL has one row per item and warehouse with totals and planning parameters such as reorder point and planner, maintained in MMS002. MITLOC has one row per balance identity: warehouse, item, location, lot, container and receiving number, with the on-hand quantity and status of that piece of stock. Allocations to order lines are in MITALO and every movement is in MITTRA.

How do I find the name of a code such as payment terms or item group?

Most code descriptions are in CSYTAB, keyed by company, division, a constant (STCO) that says which kind of code it is, the code value (STKY) and language. Join the coded column to CTSTKY and filter CTSTCO on the constant for that code type. Infor M3 Analytics reads its code names from CSYTAB the same way.

Can I query M3 tables with SQL in M3 CE (cloud)?

Not directly. In M3 CE the Business Engine database is not open to SQL. Select the tables in Data Lake Publisher (role M3-DLSubscriptionAdmin) so their changes stream to Infor Data Lake, run the initial load, and query them there, or read single tables through MDBREADMI and M3 API transactions. On-premises, read with SQL against a replica if you can, and write only through MI transactions.

Where can I see every column of an M3 table?

Use Metadata Publisher (MDP) for your release: it lists tables, columns, indexes and descriptions. The field help in a program also names the field, and the Smart Office monitor function shows which table and field sit behind a panel. This explorer covers the tables and columns used most in reports and integrations.

Sources

Table names and columns were checked against these public references. Tables whose key or columns we could not confirm say so in their notes. Column sets change between releases, so confirm against the data dictionary of your own version before you build on it.