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

Infor M3 SQL Query Generator for Items, Balances and Open Orders

Most M3 reporting questions come down to four queries: which items match a pattern, what is the balance in a warehouse, which customer order lines are still open, and which purchase lines are outstanding for a supplier. This generator fills in the filters and returns SQL you can adapt.

Fill in

Company number (CONO)

Warehouse (WHLO)

Item number pattern

Use % as the wildcard

Item status (MMSTAT)

Highest open order line status

Lines with a lower status are treated as open

Requested delivery on or before (YYYYMMDD)

Supplier number

Generated code

Free, optional. Reopen it on any device.

Download:

1. Item master

SELECT MMCONO, MMITNO, MMITDS, MMITTY, MMITGR, MMUNMS, MMSTAT
FROM MITMAS
WHERE MMCONO = 100
  AND MMITNO LIKE 'AB%'
  AND MMSTAT = '20'
ORDER BY MMITNO;

2. Item balance by warehouse

SELECT MBCONO, MBWHLO, MBITNO, MBSTQT, MBALQT, MBREOP, MBSSQT
FROM MITBAL
WHERE MBCONO = 100
  AND MBWHLO = 'A01'
  AND MBITNO LIKE 'AB%'
ORDER BY MBITNO;

3. Open customer order lines

SELECT H.OAORNO, H.OACUNO, L.OBPONR, L.OBPOSX, L.OBITNO, L.OBORQT, L.OBORST, L.OBDWDT
FROM OOLINE L
JOIN OOHEAD H
  ON H.OACONO = L.OBCONO
 AND H.OAORNO = L.OBORNO
WHERE L.OBCONO = 100
  AND L.OBWHLO = 'A01'
  AND L.OBORST < '44'
  AND L.OBDWDT <= 20261231
ORDER BY L.OBDWDT, H.OAORNO, L.OBPONR;

4. Open purchase order lines for a supplier

SELECT H.IAPUNO, H.IASUNO, L.IBPNLI, L.IBPNLS, L.IBITNO, L.IBPUSL
FROM MPLINE L
JOIN MPHEAD H
  ON H.IACONO = L.IBCONO
 AND H.IAPUNO = L.IBPUNO
WHERE L.IBCONO = 100
  AND H.IASUNO = 'SUP001'
  AND L.IBPUSL < '75'
ORDER BY H.IAPUNO, L.IBPNLI;

These queries use the standard M3 table and column naming convention. Confirm column names and status values in your release, and note that in M3 Cloud you normally query through Data Fabric or Data Lake object names, which can differ from base table names. Test on a non-production tenant first.

Need M3 reports without the SQL?

We can review your most-used reporting queries and show how plain-language questions would cover them.

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

Was this tool useful?

How to use the generator

Enter your company number and warehouse, then set the item pattern and status filters. Copy the block you need into your SQL client, Data Lake query tool or reporting tool. The filters are plain literals so you can see exactly what is being asked; replace them with parameters in production reports.

Always filter on CONO first
Use LIKE with a wildcard for item families
Dates are YYYYMMDD numbers, not date types

Reading the status filters

M3 order and purchase lines carry a status code that rises as the line progresses from entry through allocation, picking, delivery and invoicing. Filtering for a status below a chosen threshold gives open lines. The exact thresholds depend on your order types and configuration, so agree the definition of open with the business before you publish the report.

Common mistakes

Joining header and line tables without the company number can return rows from another company. Another common mistake is sorting dates stored as numbers as if they were text, or comparing them with formatted dates. Finally, direct SQL against a transactional database should be read-only and run off-hours on large tables; many sites prefer an extract into a reporting store.

Frequently Asked Questions

Can I run these queries in M3 Cloud?

Cloud tenants normally expose data through Infor Data Fabric and Data Lake rather than direct database access. The logic is the same, but table and column names in the Data Lake can differ, so adapt the names to the objects you see there.

How do I define an open order line?

Open usually means the line status is below a delivered or invoiced threshold. Because statuses depend on your order type setup, check the status definitions in your configuration and confirm the threshold with the business.

Why are dates numbers in the filters?

M3 stores most dates as eight digit numbers in YYYYMMDD form. Comparing numbers works for ranges, but you must supply the filter in the same format.

Should I write to M3 with SQL?

No. Writes should go through M3 APIs or programs so business rules, statuses and audit data stay consistent. Use SQL for reading only.

If you would rather ask these questions in plain language and see the generated query, ERPray does this over M3 data with read-only access and your existing roles.

Talk to an Expert Explore More Free Tools