Developer ToolsFree generatorPDF, Excel, Word exportOracle E-Business Suite

Oracle EBS Onhand Quantity SQL Query Generator

Onhand queries look simple until you add lots, subinventories, reservations and history. This generator gives you three working SQL patterns for Oracle EBS Inventory that you can adjust by changing a few fields.

Fill in

Organization ID

ORGANIZATION_ID from MTL_PARAMETERS

Item segment1 pattern

Use % as wildcard

Subinventory pattern

Use % for all subinventories

Minimum onhand to show

Transaction history days

Generated code

Download:

1. Onhand by item, subinventory and lot

SELECT msi.segment1 AS item,
       msi.primary_uom_code AS uom,
       moq.subinventory_code,
       moq.lot_number,
       SUM(moq.transaction_quantity) AS onhand_qty
FROM   mtl_onhand_quantities_detail moq,
       mtl_system_items_b msi
WHERE  msi.inventory_item_id = moq.inventory_item_id
AND    msi.organization_id = moq.organization_id
AND    moq.organization_id = 207
AND    msi.segment1 LIKE 'BRK-%'
AND    moq.subinventory_code LIKE '%'
GROUP BY msi.segment1, msi.primary_uom_code, moq.subinventory_code, moq.lot_number
HAVING SUM(moq.transaction_quantity) > 0
ORDER BY msi.segment1, moq.subinventory_code, moq.lot_number;

2. Onhand versus reserved

SELECT msi.segment1 AS item,
       oh.onhand_qty,
       NVL(rs.reserved_qty, 0) AS reserved_qty,
       oh.onhand_qty - NVL(rs.reserved_qty, 0) AS unreserved_qty
FROM   mtl_system_items_b msi,
       (SELECT inventory_item_id, organization_id, SUM(transaction_quantity) AS onhand_qty
        FROM   mtl_onhand_quantities_detail
        WHERE  organization_id = 207
        AND    subinventory_code LIKE '%'
        GROUP BY inventory_item_id, organization_id) oh,
       (SELECT inventory_item_id, organization_id, SUM(primary_reservation_quantity) AS reserved_qty
        FROM   mtl_reservations
        WHERE  organization_id = 207
        GROUP BY inventory_item_id, organization_id) rs
WHERE  msi.inventory_item_id = oh.inventory_item_id
AND    msi.organization_id = oh.organization_id
AND    msi.inventory_item_id = rs.inventory_item_id (+)
AND    msi.organization_id = rs.organization_id (+)
AND    msi.segment1 LIKE 'BRK-%'
ORDER BY msi.segment1;

3. Recent material transactions

SELECT mmt.transaction_id,
       mmt.transaction_date,
       msi.segment1 AS item,
       mtt.transaction_type_name,
       mmt.subinventory_code,
       mmt.transaction_quantity,
       mmt.transaction_uom
FROM   mtl_material_transactions mmt,
       mtl_transaction_types mtt,
       mtl_system_items_b msi
WHERE  mmt.transaction_type_id = mtt.transaction_type_id
AND    mmt.inventory_item_id = msi.inventory_item_id
AND    mmt.organization_id = msi.organization_id
AND    mmt.organization_id = 207
AND    msi.segment1 LIKE 'BRK-%'
AND    mmt.transaction_date >= SYSDATE - 30
ORDER BY mmt.transaction_date DESC;

Run as APPS or a read-only reporting user. Onhand is in the item primary UoM. Reservation tables and columns differ slightly by release, so test on a non-production instance first. For very large organizations, add date or subinventory filters to avoid full scans.

Skip the SQL for everyday inventory questions

Optional: see read-only natural-language onhand queries over your EBS data.

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

How to use the generator

Enter the organization id from MTL_PARAMETERS, an item pattern, and a subinventory pattern. Copy the SQL from each block into SQL Developer or Toad. Block one gives onhand by lot and subinventory, block two compares onhand with reservations, and block three lists recent transactions so you can explain why a quantity changed.

Use % as a wildcard in item and subinventory patterns
Set the minimum quantity above zero to hide empty rows
Adjust the history window to the period under review

Why onhand is summed

MTL_ONHAND_QUANTITIES_DETAIL stores multiple rows per item for different subinventories, locators, lots and revisions, and can hold several rows for the same combination. You must sum TRANSACTION_QUANTITY to get the onhand number the forms show. The item master join uses both INVENTORY_ITEM_ID and ORGANIZATION_ID to avoid duplicates across organizations.

Reading the reservation comparison

Unreserved quantity is onhand minus reserved. A negative result usually means reservations exist against supply that has moved, or reservations in another subinventory. Oracle availability also considers pending transactions and, depending on setup, nettable subinventories, so this comparison is a diagnostic and not a replacement for the Material Workbench.

Common mistakes

Forgetting the organization filter returns data from every organization. Using LIKE on item without checking the item flexfield segments can miss multi-segment items. Reading onhand while the transaction manager has pending rows can look wrong for a short time, so check MTL_MATERIAL_TRANSACTIONS_TEMP before raising a data defect.

Frequently Asked Questions

Which table has onhand quantity in Oracle EBS?

MTL_ONHAND_QUANTITIES_DETAIL is the base table. Group by item, organization, subinventory, locator and lot, and sum TRANSACTION_QUANTITY. Do not rely on a single row per combination.

Why does my SQL onhand differ from the form?

Common causes are pending transactions, a different UoM, nettable settings, lot expiry rules or missing organization filters. Compare with the onhand quantities form for the same item and subinventory, then check the pending temp table.

Can I query onhand by locator?

Yes. Add LOCATOR_ID to the select and group by clauses, and join to MTL_ITEM_LOCATIONS for the locator segments when locator control is on for the subinventory.

Is it safe to run these queries in production?

They are read-only selects, but large organizations can have millions of rows. Run them with tight item filters, ideally against a reporting copy, and avoid peak transaction processing hours.

If your planners keep asking for the same inventory query, we can put it behind a read-only natural-language interface on your own servers.

Talk to an Expert Explore More Free Tools

Related Tools

Oracle

More Developer Tools tools

See all

Back to All Tools