Search Results mtl_onhand_items_v




Overview
Explore in AI Chat

Overview

MTL_ONHAND_ITEMS_V is an Oracle E-Business Suite inventory reporting view owned by the APPS schema and registered under the INV – Inventory product. In the ETRM metadata it carries the functional description "10SC ONLY", indicating that the object was created for a specific, narrowly scoped requirement rather than as a general-purpose inventory interface. It is a valid object in both the 12.1.1 and 12.2.2 codelines, but it is not part of the standard documented inventory API surface, and Oracle does not publish it as a supported integration point.

The view presents a summarized, organization-level quantity-on-hand picture for each inventory item. Its distinguishing characteristic is that it collapses the detailed on-hand records held at subinventory, locator, lot, and serial level into one aggregated row per organization/item combination, while simultaneously denormalizing item description, unit of measure, organization code and name, and the item's control attributes (revision, locator, lot, and serial control). This makes it suitable for lightweight reporting or lookup scenarios where the caller needs a total quantity and the item's control characteristics without navigating the full inventory table hierarchy.

Underlying Base Objects

The view text joins four principal sources. MTL_ONHAND_QUANTITIES_DETAIL supplies the transaction quantity that is summed to produce the total on-hand figure. MTL_SYSTEM_ITEMS_VL supplies the item's padded concatenated segments, description, primary unit of measure, and the four control-code columns. MTL_PARAMETERS supplies the organization code, and HR_ORGANIZATION_UNITS supplies the organization name. The documented referenced base objects for the 12.2.2 metadata are HR_GENERAL (package), HR_ORGANIZATION_UNITS (view), HR_SECURITY (package), MTL_ONHAND_QUANTITIES_DETAIL (synonym), MTL_PARAMETERS (synonym), and MTL_SYSTEM_ITEMS_VL (view).

The HR_SECURITY and HR_GENERAL references are significant: because the view touches HR_ORGANIZATION_UNITS, it inherits the organization-level security model enforced by those packages. Query results are therefore filtered by the accessing user's organization security profile, so a user restricted to one inventory organization will not see quantities for others.

Key Columns

Common Use Cases and Queries

Typical usage is ad hoc reporting: listing total on-hand by item for an organization, or joining the view to item master data for a control-attribute-aware inventory listing.

  • Total on-hand for a given item across all organizations the user can access.
  • Organization-wide on-hand inventory listing with description and UOM for a simple stock report.
  • Identifying items with on-hand quantity where lot, serial, or locator control is enabled, to plan cycle counts.

Sample query:

SELECT organization_code, padded_concatenated_segments, item_description, primary_uom_code, total_qoh
FROM apps.mtl_onhand_items_v
WHERE organization_id = :p_org_id
AND total_qoh > 0
ORDER BY padded_concatenated_segments;

Because the object is marked "10SC ONLY" and is unsupported, it should be treated as a read-only convenience view for reporting, not as a foundation for custom application logic or interfaces.