Search Results icx_mtl_onhand_sub_v




Overview
Explore in AI Chat

Overview

The APPS.ICX_MTL_ONHAND_SUB_V view is an Oracle E-Business Suite database object owned by the APPS schema and associated with the ICX – Oracle iProcurement product module. The ETRM documentation classifies the view status as VALID while explicitly marking its description as "Obsolete," meaning it was superseded by later on-hand availability constructs but may still exist in EBS 12.1.1 and 12.2.2 databases for backward compatibility. Its purpose is to present aggregated on-hand quantity information at the item, subinventory, and organization level, enriched with item master descriptive attributes, organization codes, and subinventory control flags. Because iProcurement requisition and catalog browsing screens required visibility into item availability and locator behavior, this view consolidated MTL_ONHAND_QUANTITIES transaction rows into a single summarized quantity of on-hand stock (TOTAL_QOH) per organization, item, and subinventory combination. It also exposes the item's locator control, lot control, serial control, and revision control settings, which allows downstream logic to determine whether a locator, lot, or serial must be supplied when the item is transacted.

Underlying Base Objects

The documented base objects referenced by the view are MTL_ONHAND_QUANTITIES, MTL_SYSTEM_ITEMS_KFV, MTL_PARAMETERS, MTL_SECONDARY_INVENTORIES, and HR_ORGANIZATION_UNITS, with supporting dependencies on the HR_GENERAL and HR_SECURITY packages. MTL_ONHAND_QUANTITIES supplies the transaction quantity and subinventory rows that are summed into TOTAL_QOH. MTL_SYSTEM_ITEMS_KFV, the key flexfield view over the item master, provides the concatenated item segment, description, primary unit of measure, and the control-code columns. MTL_PARAMETERS provides the inventory organization code, while MTL_SECONDARY_INVENTORIES contributes the subinventory-level availability type, reservable type, ATP code, and locator type. HR_ORGANIZATION_UNITS supplies the human-readable organization name. All joins are performed on ORGANIZATION_ID and INVENTORY_ITEM_ID, with SUBINVENTORY_CODE equated to SECONDARY_INVENTORY_NAME, and results are collapsed through a GROUP BY across all non-aggregated columns.

Key Columns

Common Use Cases and Queries

Typical usage involves determining on-hand availability for iProcurement catalog and requisition validation, particularly where locator control, lot control, or serial control affects whether a transaction is permitted. A representative query filtering on locator-controlled items is shown below.

SELECT organization_code,
       concatenated_segments,
       subinventory_code,
       item_locator_control,
       total_qoh
FROM   apps.icx_mtl_onhand_sub_v
WHERE  inventory_item_id = :item_id
AND    organization_id   = :org_id
AND    total_qoh > 0;

Because the view is documented as Obsolete, new development should generally reference the current on-hand availability views instead. Where the view remains in use, query it only for summarized subinventory-level availability and item control-flag lookups, and avoid relying on it for transactional or reservation processing.