Search Results mtl_onhand_sub_v




Overview
Explore in AI Chat

Overview

MTL_ONHAND_SUB_V is an APPS-owned database view in the Oracle E-Business Suite Inventory (INV) product family. The ETRM repository lists the object as a VALID view with the description "10SC ONLY," indicating that its definition is scoped to a specific configuration or localization rather than the general on-hand inquiry stack. The view provides a denormalized, subinventory-level snapshot of on-hand balances by aggregating transaction quantities from the on-hand quantities detail table and joining that result to item, organization, and subinventory attribute data.

Unlike the standard on-hand inquiry views, MTL_ONHAND_SUB_V exposes a compact result set that combines quantity on hand (TOTAL_QOH) with subinventory material control attributes such as availability type, reservable type, ATP code, and locator type. It is therefore primarily used for reporting and integration scenarios in which quantity, item description, UOM, and subinventory behavioral attributes are required in a single query without traversing multiple base objects. Because the view groups by organization, item, revision, and subinventory, it returns one summarized row per combination rather than the granular row set found in MTL_ONHAND_QUANTITIES_DETAIL.

Underlying Base Objects

The documented view definition references several base objects, all resolved through APPS-private synonyms in the standard EBS schema model:

Joins are established on ORGANIZATION_ID across the item, parameter, subinventory, and organization tables, and on SUBINVENTORY_CODE to MTL_SECONDARY_INVENTORIES. The WHERE clause restricts results to subinventories where SUBINVENTORY_TYPE equals 1 or is null, which limits output to storage subinventories and excludes other subinventory classifications.

Key Columns

  • ORGANIZATION_ID / ORGANIZATION_CODE / ORGANIZATION_NAME — the inventory organization identifier and its code and descriptive name.
  • INVENTORY_ITEM_ID / PADDED_CONCATENATED_SEGMENTS / ITEM_DESCRIPTION — item identity in numeric and concatenated key-flexfield form, plus the description.
  • REVISION — the item revision for revision-controlled items.
  • SUBINVENTORY_CODE — the subinventory holding the balance.
  • TOTAL_QOH — the summed primary transaction quantity, representing total quantity on hand for the grouped combination.
  • PRIMARY_UOM_CODE — the item's primary unit of measure.
  • NET, RSV, ATP — mapped from AVAILABILITY_TYPE, RESERVABLE_TYPE, and INVENTORY_ATP_CODE respectively, describing subinventory availability, reservability, and ATP behavior.
  • LOCATOR_TYPE — the locator control setting of the subinventory.
  • ITEM_REV_CONTROL, ITEM_LOCATOR_CONTROL, ITEM_LOT_CONTROL, ITEM_SERIAL_CONTROL — item-level control flags mapped from the revision, location, lot, and serial number control codes.

Common Use Cases and Queries

Typical uses include subinventory-level on-hand reporting, reconciliation extracts, and integration interfaces that require quantity and material control attributes together. A representative query is:

  • SELECT organization_code, subinventory_code, padded_concatenated_segments, item_description, primary_uom_code, revision, total_qoh FROM mtl_onhand_sub_v WHERE organization_id = :p_org_id AND total_qoh > 0 ORDER BY subinventory_code, padded_concatenated_segments;
  • SELECT subinventory_code, SUM(total_qoh) FROM mtl_onhand_sub_v WHERE organization_id = :p_org_id GROUP BY subinventory_code;
  • SELECT padded_concatenated_segments, subinventory_code, total_qoh, net, rsv, atp FROM mtl_onhand_sub_v WHERE organization_id = :p_org_id AND inventory_item_id = :p_item_id;

Because the view reflects summed transaction quantities rather than a point-in-time costed balance, it is suited to operational reporting; costing inquiries should use the standard on-hand and cost views instead.