# Oracle EBS On-Hand vs Material Transactions Reconciliation SQL Generator

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

## Short answer

Generate a SQL query that compares on-hand quantity to the net of material transactions for an organization, to spot unexplained differences.

## Category

oracle

## Erp

Oracle E-Business Suite

## Industry

Discrete Manufacturing

## Audience

- EBS developer
- EBS consultant
- Inventory analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Organization ID

**Key:** org_id

**Type:** number

**Default:** 207

#### Subinventory

**Key:** subinv

**Type:** text

**Default:** STORES

#### Item segment1 pattern

**Key:** item_like

**Type:** text

**Default:** AS-%

#### Transactions from date (YYYY-MM-DD)

**Key:** from_date

**Type:** text

**Default:** 2025-01-01

**Blocks:**

#### Reconciliation query

**Body:** -- On-hand quantity versus net material transactions
-- Verify table and column names in your release before running
SELECT msi.segment1 AS item,
       oh.subinventory_code,
       oh.onhand_qty,
       NVL(tx.net_txn_qty, 0) AS net_txn_qty,
       oh.onhand_qty - NVL(tx.net_txn_qty, 0) AS difference
  FROM (SELECT organization_id, inventory_item_id, subinventory_code,
               SUM(transaction_quantity) AS onhand_qty
          FROM mtl_onhand_quantities_detail
         WHERE organization_id = {{org_id}}
           AND subinventory_code = '{{subinv}}'
         GROUP BY organization_id, inventory_item_id, subinventory_code) oh
  JOIN mtl_system_items_b msi
    ON msi.inventory_item_id = oh.inventory_item_id
   AND msi.organization_id = oh.organization_id
  LEFT JOIN (SELECT organization_id, inventory_item_id, subinventory_code,
                    SUM(transaction_quantity) AS net_txn_qty
               FROM mtl_material_transactions
              WHERE organization_id = {{org_id}}
                AND subinventory_code = '{{subinv}}'
                AND transaction_date >= TO_DATE('{{from_date}}', 'YYYY-MM-DD')
              GROUP BY organization_id, inventory_item_id, subinventory_code) tx
    ON tx.inventory_item_id = oh.inventory_item_id
   AND tx.organization_id = oh.organization_id
   AND tx.subinventory_code = oh.subinventory_code
 WHERE msi.segment1 LIKE '{{item_like}}'
 ORDER BY ABS(oh.onhand_qty - NVL(tx.net_txn_qty, 0)) DESC;

#### Drill-down for one item

**Body:** -- Replace :item_id with the inventory_item_id from the query above
SELECT mmt.transaction_id,
       mmt.transaction_date,
       mmt.transaction_type_id,
       mmt.subinventory_code,
       mmt.transaction_quantity,
       mmt.transaction_uom
  FROM mtl_material_transactions mmt
 WHERE mmt.organization_id = {{org_id}}
   AND mmt.subinventory_code = '{{subinv}}'
   AND mmt.inventory_item_id = :item_id
 ORDER BY mmt.transaction_date, mmt.transaction_id;

**Notes:** The comparison is only meaningful if your date range starts at a point where the opening balance was zero, such as go-live, or if you add an opening balance. Test in a non-production instance, and check the table names and views against your EBS release. Read-only SELECT statements only.

## Content

**Intro:** When an on-hand figure does not agree with the transaction history, consultants reach for SQL. This generator builds a reconciliation query between on-hand detail and material transactions, plus a drill-down you can run for one item.

**Sections:**

#### How to use the generator

**Body:** Enter the organization ID, subinventory, an item pattern and the date from which transactions should be summed. Copy the generated query into SQL Developer or your reporting tool and run it as a read-only user in a test or reporting instance first. Items with a non-zero difference need investigation. Use the second query to list the transactions for one item and spot duplicates, unit of measure issues or missing transactions.

**Bullets:**

- Use organization ID, not organization code, in the filter
- Run it against a reporting copy for large datasets
- Start with a narrow item pattern to keep runtime short

#### The tables involved

**Body:** MTL_ONHAND_QUANTITIES_DETAIL stores current on-hand by item, subinventory, locator, lot and revision. MTL_MATERIAL_TRANSACTIONS stores the transaction history, with quantity signed positive for receipts and negative for issues. MTL_SYSTEM_ITEMS_B is the item master and provides the item number in segment1. Summing signed transaction quantities from go-live should equal the current on-hand if every movement was transacted.

#### Reading the differences

**Body:** A positive difference means on-hand exceeds the net of transactions in the range, which often reflects a conversion opening balance loaded outside the range or through an interface. A negative difference means the transactions net to more than on-hand, so check for pending or unprocessed transactions, subinventory transfers and cycle count adjustments. Oracle also keeps pending transactions in MTL_MATERIAL_TRANSACTIONS_TEMP, which this query does not include.

#### Common mistakes

**Body:** Do not compare across different date ranges for each side. Be careful with transfers, because a transfer produces a negative transaction in the source subinventory and a positive one in the destination, so filtering to one subinventory shows only half. Remember mixed unit of measure and lot-controlled items, where detail is by lot. Keep the query read-only and avoid running it at month-end peak on production.

**Cta:** If your team wants to ask these reconciliation questions in plain English against EBS data, Netray can set up a private, read-only query layer.

**Faq:**

#### Will this work in every EBS release?

**Answer:** The tables named exist across the 11i and R12 families, but columns and security can vary. Check them in your release and run in a non-production instance first.

#### Why is my difference never zero?

**Answer:** Typical causes are an opening balance outside the date range, pending transactions in the temp table, and transactions in transfers or intransit. Widen the range or add the opening balance to explain it.

#### Is it safe to run on production?

**Answer:** It is a SELECT only, but large unfiltered runs can load the database. Use a reporting copy or off-peak window, and narrow by item pattern.

#### Can I extend it to locators and lots?

**Answer:** Yes. Add locator_id and lot_number to the select and group by clauses on both sides, since the on-hand detail table holds them, and the transactions table has equivalents.

## Search terms

oracle ebs onhand reconciliation sql, mtl_onhand_quantities_detail query, mtl_material_transactions sql, oracle inventory on hand query, ebs inventory reconciliation script, oracle ebs onhand vs transactions

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