Search Results mtl_onhand_items_v

Overview

ICX_MTL_DEMAND_SUMMARY_V is a database view owned by the APPS schema in Oracle E-Business Suite, defined within the ICX product family (Oracle iProcurement). Its documented purpose is to present Item Availability by Organization, aggregating on-hand balances and outstanding demand at the inventory item and organization level. The view serves as the data source for iProcurement availability checks and related requisition-time stock validation logic, allowing requisitioners to see whether an item is available, reserved, or committed across organizations before an order is placed.

The view is central to the search term completed_quantity, which is referenced repeatedly in the view definition via MTL_DEMAND.COMPLETED_QUANTITY. This column records the portion of a demand line already fulfilled, and the view subtracts it from LINE_ITEM_QUANTITY to derive remaining open demand. Because completed quantity is dynamic, the view reflects current open commitments rather than historical reservations.

Underlying Base Objects

Per the ETRM metadata for 12.2.2, the view is defined over the following objects, accessed through synonyms in the APPS schema:

• MTL_DEMAND — the primary demand driver; supplies LINE_ITEM_QUANTITY, COMPLETED_QUANTITY, and RESERVATION_TYPE.
• MTL_ONHAND_QUANTITIES_DETAIL — source of on-hand quantity aggregation (via the MTL_ONHAND_ITEMS_V construct in the view text).
• MTL_RESERVATIONS — reservation context for demand lines.
• MTL_PARAMETERS and MTL_SYSTEM_ITEMS_KFV — organization parameters and item key-flexfield descriptions.
• HR_ORGANIZATION_UNITS, HR_GENERAL, and HR_SECURITY — organization naming and security restrictions.
• OE_ORDER_LINES_ALL — order-line linkage for demand originating in Order Management.
• INV_DECIMALS_PUB — quantity rounding and UOM decimal handling.

The demand aggregation filters on MD.PARENT_DEMAND_ID IS NOT NULL, ensuring only child demand records are included, and retains rows where either quantity or completed quantity is greater than zero.

Key Columns

• ORGANIZATION_ID — inventory organization identifier.
• INVENTORY_ITEM_ID — item identifier.
• CONCATENATED_SEGMENTS and DESCRIPTION — item flexfield and description.
• PRIMARY_UNIT_OF_MEASURE — item UOM.
• ORGANIZATION_CODE and NAME — organization code and name.
• TOTAL_QOH — total quantity on hand, NVL'd to zero.
• Reservation-type buckets — three SUM(DECODE(...)) expressions computing open demand for reservation types 1, 2, and 3, each expressed as LINE_ITEM_QUANTITY − COMPLETED_QUANTITY.
• Net availability — TOTAL_QOH minus the relevant reservation buckets.

The completed_quantity subtraction is the mechanism that converts gross demand into open (unfulfilled) demand; without it, fully shipped or received lines would continue to appear as outstanding.

Common Use Cases and Queries

Typical scenarios include iProcurement availability display, requisition validation, and custom inventory reporting.

SELECT organization_code,
       concatenated_segments,
       description,
       total_qoh,
       primary_unit_of_measure
  FROM apps.icx_mtl_demand_summary_v
 WHERE inventory_item_id = :item_id
   AND organization_id  = :org_id;

Analysts investigating reserved versus available stock join the result to MTL_DEMAND to reconcile completed_quantity against open lines. Because the view applies HR security, queries executed by restricted responsibilities return only authorized organizations, making it suitable for multi-org sourcing decisions and requisition-time availability checks.