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

> Source: https://netray.co/tools/infor-m3-sql-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in company, warehouse and filters to get ready-to-adapt SQL against MITMAS, MITBAL, OOHEAD, OOLINE and MPLINE.

## Category

infor-cloudsuite

## Erp

Infor M3

## Audience

- M3 consultant
- M3 developer
- Report writer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Company number (CONO)

**Key:** cono

**Type:** number

**Default:** 100

#### Warehouse (WHLO)

**Key:** whlo

**Type:** text

**Default:** A01

#### Item number pattern

**Key:** item_pattern

**Type:** text

**Default:** AB%

**Help:** Use % as the wildcard

#### Item status (MMSTAT)

**Key:** item_status

**Type:** text

**Default:** 20

#### Highest open order line status

**Key:** max_line_status

**Type:** text

**Default:** 44

**Help:** Lines with a lower status are treated as open

#### Requested delivery on or before (YYYYMMDD)

**Key:** due_before

**Type:** text

**Default:** 20261231

#### Supplier number

**Key:** supplier

**Type:** text

**Default:** SUP001

**Blocks:**

#### 1. Item master

**Body:** SELECT MMCONO, MMITNO, MMITDS, MMITTY, MMITGR, MMUNMS, MMSTAT
FROM MITMAS
WHERE MMCONO = {{cono}}
  AND MMITNO LIKE '{{item_pattern}}'
  AND MMSTAT = '{{item_status}}'
ORDER BY MMITNO;

#### 2. Item balance by warehouse

**Body:** SELECT MBCONO, MBWHLO, MBITNO, MBSTQT, MBALQT, MBREOP, MBSSQT
FROM MITBAL
WHERE MBCONO = {{cono}}
  AND MBWHLO = '{{whlo}}'
  AND MBITNO LIKE '{{item_pattern}}'
ORDER BY MBITNO;

#### 3. Open customer order lines

**Body:** 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 = {{cono}}
  AND L.OBWHLO = '{{whlo}}'
  AND L.OBORST < '{{max_line_status}}'
  AND L.OBDWDT <= {{due_before}}
ORDER BY L.OBDWDT, H.OAORNO, L.OBPONR;

#### 4. Open purchase order lines for a supplier

**Body:** 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 = {{cono}}
  AND H.IASUNO = '{{supplier}}'
  AND L.IBPUSL < '75'
ORDER BY H.IAPUNO, L.IBPNLI;

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use the generator

**Body:** 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.

**Bullets:**

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

#### Reading the status filters

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### Can I run these queries in M3 Cloud?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

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

## Search terms

infor m3 sql query, m3 mitmas query, m3 open customer orders sql, m3 mitbal sql, m3 open purchase order lines query, m3 data lake sql, m3 report query examples

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json