Search Results mtl_item_quantities_view




Overview
Explore in AI Chat

Overview

MTL_ITEM_QUANTITIES_VIEW is a read-only database view owned by the APPS schema in Oracle E-Business Suite. It is registered as VALID and belongs to the Inventory (INV) product family. As documented in the ETRM metadata for releases 12.1.1 and 12.2.2, its stated purpose is to present "Item onhand quantities" in an aggregated, reporting-friendly form. Rather than requiring callers to summarize the raw on-hand transaction detail themselves, the view delivers a pre-grouped quantity per item, organization, subinventory, locator, and revision combination.

Because the view is defined entirely in SQL over an existing Inventory table, it carries no storage of its own and imposes no maintenance overhead. It is most commonly consumed by reports, concurrent programs, discoverer workbooks, and custom integration code that need a lightweight, denormalized projection of on-hand balances without navigating the full complexity of the Inventory on-hand schema. It is important to note that the view exposes on-hand quantity only; it does not include costing, valuation, or reservation information.

Underlying Base Objects

The documented base object referenced by the view is MTL_ONHAND_QUANTITIES_DETAIL, accessed through a synonym. The ETRM definition lists this object as the sole source. MTL_ONHAND_QUANTITIES_DETAIL is the Inventory table that stores on-hand balances at the most granular level available — one row per organization, item, subinventory, locator, revision (and, in the underlying table, additional dimensions such as lot and ownership).

The view text documented in the metadata is:

This confirms that the view collapses finer-grained detail rows into a single summed quantity for each grouping key. The aggregation column is PRIMARY_TRANSACTION_QUANTITY, which holds the on-hand quantity expressed in the item's primary unit of measure.

Key Columns

  • ORGANIZATION_ID — The inventory organization in which the on-hand balance resides. This is the primary partitioning key for multi-organization queries.
  • INVENTORY_ITEM_ID — The surrogate key of the inventory item. Joins to MTL_SYSTEM_ITEMS_B on INVENTORY_ITEM_ID and ORGANIZATION_ID to resolve item number and description.
  • SUBINVENTORY_CODE — The subinventory within the organization where the material is held.
  • LOCATOR_ID — The locator (row, rack, bin) identifier. Joins to MTL_ITEM_LOCATIONS for the human-readable locator name.
  • REVISION — The item revision under which the quantity is held, where revision control applies.
  • QUANTITY — The aggregated on-hand quantity, computed as SUM(PRIMARY_TRANSACTION_QUANTITY) across the finer detail rows for the grouping key.

Common Use Cases and Queries

Typical uses include on-hand availability reports, replenishment and min-max analysis, inventory dashboards, and integration extracts that feed downstream planning or warehouse systems.

  • Total on-hand for one item in one organization:
    SELECT SUM(QUANTITY) FROM APPS.MTL_ITEM_QUANTITIES_VIEW WHERE ORGANIZATION_ID = :org AND INVENTORY_ITEM_ID = :item;
  • On-hand by subinventory and locator:
    SELECT SUBINVENTORY_CODE, LOCATOR_ID, QUANTITY FROM APPS.MTL_ITEM_QUANTITIES_VIEW WHERE ORGANIZATION_ID = :org AND INVENTORY_ITEM_ID = :item;
  • Items with positive on-hand in an organization:
    SELECT INVENTORY_ITEM_ID, SUM(QUANTITY) FROM APPS.MTL_ITEM_QUANTITIES_VIEW WHERE ORGANIZATION_ID = :org GROUP BY INVENTORY_ITEM_ID HAVING SUM(QUANTITY) > 0;

Because the view performs a GROUP BY over MTL_ONHAND_QUANTITIES_DETAIL, queries should filter aggressively on ORGANIZATION_ID and INVENTORY_ITEM_ID to limit the aggregation cost. For high-volume reporting, joining the view to MTL_SYSTEM_ITEMS_B and MTL_ITEM_LOCATIONS is standard practice to resolve descriptive attributes.