Skip to main content

Oracle ERP R12 Inventory: Top Queries Every Developer Should Know

Oracle Inventory Management, a core module of Oracle ERP R12, helps manage stock, track transactions, and optimize warehouse operations efficiently

In this article, i highlight real-world SQL queries that every developer should know to efficiently extract data, analyze inventory, and support daily operational needs. These queries provide actionable insights for professionals working with Oracle ERP R12 Inventory.

Note: In all queries, :ORG_ID is set to 84 for demonstration purposes.

1) Item Master Details

Purpose: Fetch item master data including item code, description, primary UOM, item status, organization details

SELECT

    msib.inventory_item_id,

    msib.segment1 AS item_code,

    msib.description AS item_description,

    msib.primary_uom_code,

    msib.inventory_item_status_code,

    msib.organization_id

FROM

    mtl_system_items_b msib

WHERE

    msib.organization_id = :ORG_ID;

 

2)  Item Category Assignment

Purpose: Retrieve item category assignments to support category-based inventory reporting and classification.

SELECT
    msib.segment1 AS item_code,
    mc.segment1 AS category
FROM
    mtl_system_items_b msib
JOIN
    mtl_item_categories mic
    ON msib.inventory_item_id = mic.inventory_item_id
    AND msib.organization_id = mic.organization_id
JOIN
    mtl_categories_b mc
    ON mic.category_id = mc.category_id
WHERE
    msib.organization_id = :ORG_ID;
 3) On-Hand Quantity

Purpose: Retrieve current on-hand stock quantity for items across organizations and subinventories.

    SELECT

    moq.inventory_item_id,

    msib.segment1 AS item_code,

    msib.description AS item_description,

    SUM(moq.transaction_quantity) AS onhand_qty,

    moq.subinventory_code

FROM

    mtl_onhand_quantities moq

JOIN

    mtl_system_items_b msib

    ON moq.inventory_item_id = msib.inventory_item_id

    AND moq.organization_id = msib.organization_id

WHERE

    moq.organization_id = :ORG_ID

GROUP BY

    moq.inventory_item_id,

    msib.segment1,

    msib.description,

    moq.subinventory_code;

 

4)  Subinventory Wise Stock

Purpose: Retrieve on-hand inventory quantity grouped by subinventory to support warehouse-level stock analysis.

SELECT
    moq.subinventory_code,
    SUM(moq.transaction_quantity) AS total_qty
FROM
    mtl_onhand_quantities moq
WHERE
    moq.organization_id = :ORG_ID
GROUP BY
    moq.subinventory_code;

 

 5)  Item Transactions History

Purpose: Fetch detailed history of inventory transactions including Transaction type, Transaction Date, quantity etc.

SELECT
     mmt.transaction_id,
     mmt.inventory_item_id,
     msib.segment1 AS item_code,
     mmt.transaction_quantity,
     mmt.transaction_date,
     mmt.subinventory_code,
     mtt.transaction_type_name
 FROM
     mtl_material_transactions mmt
 JOIN
     mtl_transaction_types mtt
     ON mmt.transaction_type_id = mtt.transaction_type_id
 JOIN
     mtl_system_items_b msib
     ON mmt.inventory_item_id = msib.inventory_item_id
     AND mmt.organization_id = msib.organization_id
 WHERE
     mmt.organization_id = :ORG_ID
 ORDER BY
     mmt.transaction_date DESC;

 
6)  Locator Wise Stock

Purpose: Retrieve on-hand inventory quantities at locator level for items within subinventories where locator control is enabled.

SELECT

    msib.segment1 AS item_code,

    msib.description AS item_description,

    mil.segment1 AS locator,

    SUM(moq.transaction_quantity) AS qty

FROM

    mtl_onhand_quantities moq

JOIN

    mtl_item_locations mil

    ON moq.locator_id = mil.inventory_location_id

    AND moq.organization_id = mil.organization_id

JOIN

    mtl_system_items_b msib

    ON moq.inventory_item_id = msib.inventory_item_id

    AND moq.organization_id = msib.organization_id

WHERE

    moq.organization_id = :ORG_ID

GROUP BY

    msib.segment1,

    msib.description,

    mil.segment1;

 

 7)   Lot Controlled Items Stock

Purpose: - Retrieve on-hand inventory quantities for lot-controlled items, including batch/lot numbers for tracking and control.

SELECT

    moqd.lot_number,

    moqd.inventory_item_id,

    msib.segment1 AS item_code,

    SUM(moqd.transaction_quantity) AS qty

FROM

    mtl_onhand_quantities_detail moqd

JOIN

    mtl_system_items_b msib

    ON moqd.inventory_item_id = msib.inventory_item_id

    AND moqd.organization_id = msib.organization_id

WHERE

    moqd.organization_id = :ORG_ID

GROUP BY

    moqd.lot_number,

    moqd.inventory_item_id,

    msib.segment1;

Conclusion

 These queries help in inventory tracking, stock monitoring, audit, and reporting. They can be used in Oracle APEX, Power BI dashboards, and ERP custom reports.

Comments

Post a Comment

Popular posts from this blog

Oracle R12 Inventory Configuration: Key points to Avoid Mistakes

Most Oracle ERP R12 inventory problems are not system failures they are process and configuration failures. The same categories of mistakes surface repeatedly across implementations: Focusing on master data control, disciplined transactions, proper OU setup, and strong internal controls prevents operational and financial discrepancies.   This article walks through the four most critical areas where Oracle R12 inventory implementations go wrong, explains why each mistake is costly, and provides recommendations to prevent them. Each section includes the relevant navigation path and configuration screenshots for hands-on reference   1.       Improper Item Master Configuration in Oracle R12   Oracle E‑Business Suite R12 , the Item Master Configuration serves as the foundation of the Inventory module. Two critical components under this configuration umbrella are: Define Master Item Setup Define Item Status Both setups control how items ...
Post a Comment
Read more

Monitoring and Troubleshooting Oracle APEX Performance with Real-World SQL Queries

    Introduction Oracle Application Express (APEX) is one of the most widely adopted low-code development platforms in the Oracle ecosystem. As organizations scale their APEX deployments, the need for robust monitoring and troubleshooting capabilities becomes critical. This article provides SQL queries that DBAs and developers can use to monitor, diagnose, and optimize their APEX environments.   What makes monitoring APEX unique is the three-tier architecture it operates on: the Oracle Database at the data layer, APEX at the application layer, and Oracle REST Data Services (ORDS) as the middleware. Understanding how sessions flow through these three layers is the key to effective monitoring.     Architecture Overview   Layer Component Version Role Database Oracle DB 19.28 Stores data, executes SQL, manages sessions Application Oracle AP...
Post a Comment
Read more
SQL Query Performance Tuning: A Practical Approach for Oracle DBAs Inefficient queries in Oracle Database can significantly impact system performance, resulting in higher response times and decreased user productivity. By closely monitoring memory usage and CPU consumption, database administrators can identify performance bottlenecks and determine the root causes of inefficient query execution. Oracle Query Performance Tuning Checklist 1.          Monitor Memory Usage:                Purpose: Ensure optimal memory allocation to prevent excessive disk I/O and performance degradation.             Actions: * Track memory consumption using Oracle Enterprise Manager *Automatic Workload Repository (AWR) reports to review SGA and PGA utilization, and identify abnormal memory usage patterns. Analyze AWR reports for: a.   ...
Post a Comment
Read more