Results for “inventory_item_id”

50+ results




Overview
Explore in AI Chat

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

ICX_MTL_ONHAND_ITEMS_DMMY_V is a database view owned by the APPS schema in Oracle E-Business Suite, registered against the ICX (Oracle iProcurement) product. The ETRM metadata documents it as the "Distinct Inventory Item Identifier View" and lists its status as VALID. As its name and description imply, the view does not expose on-hand quantities or item attributes; it exposes a deduplicated list of inventory item identifiers. The "DMMY" suffix indicates that it is a dummy or placeholder object, historically provided so that dependent iProcurement components, concurrent programs, or seeded lookup logic could reference a stable, named view rather than querying MTL_SYSTEM_ITEMS directly.

In the context of EBS 12.1.1 and 12.2.2, this view plays a supporting rather than a primary analytical role. iProcurement's requisition and catalog search flows rely on a set of item and on-hand related views; this particular object supplies only the distinct set of inventory_item_id values present in the master item table. Because the view is limited to a single column, its function is typically to drive validation lists, LOV-style helper queries, or existence checks rather than to return item descriptions, quantities, or organization-level availability.

Underlying Base Objects

The documented view text is minimal and unambiguous:

  • SELECT DISTINCT(INVENTORY_ITEM_ID) FROM MTL_SYSTEM_ITEMS
  • Referenced base object: MTL_SYSTEM_ITEMS, accessed through an APPS-owned SYNONYM.

MTL_SYSTEM_ITEMS is the core Oracle Inventory master item table, holding one row per item per organization plus a master organization row, keyed by INVENTORY_ITEM_ID and ORGANIZATION_ID. Because the view does not filter by ORGANIZATION_ID, the DISTINCT clause collapses the same item across all organizations into a single row. The resulting dataset can therefore be understood as the enterprise-wide set of distinct item identifiers defined in the master item table, regardless of the organizations in which they are assigned or enabled.

Key Columns

  • INVENTORY_ITEM_ID — The sole exposed column and the surrogate primary key of an inventory item in MTL_SYSTEM_ITEMS. It is a NUMBER value used throughout Oracle Inventory, Purchasing, and iProcurement as the join key to item-related tables such as MTL_SYSTEM_ITEMS_B, MTL_SYSTEM_ITEMS_TL, and MTL_ONHAND_QUANTITIES.

No organization identifier, item number, description, or quantity column is exposed, so any report requiring human-readable item attributes must join this identifier back to the base item tables.

Common Use Cases and Queries

Typical uses involve existence checks and building distinct item lists for iProcurement-related processing. A simple confirmation query returns the full distinct set:

  • SELECT INVENTORY_ITEM_ID FROM APPS.ICX_MTL_ONHAND_ITEMS_DMMY_V;
  • Counting distinct identifiers: SELECT COUNT(*) FROM APPS.ICX_MTL_ONHAND_ITEMS_DMMY_V;
  • Validating a specific item: SELECT INVENTORY_ITEM_ID FROM APPS.ICX_MTL_ONHAND_ITEMS_DMMY_V WHERE INVENTORY_ITEM_ID = :item_id;

To obtain meaningful item detail, the view is joined to MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL on INVENTORY_ITEM_ID. When troubleshooting iProcurement item visibility or diagnosing duplicates in custom code, this view serves as a convenient, deduplicated reference point. In 12.2.2 the same definition is retained, and because it is a simple single-table view, it carries no editioning or online-patching complications beyond those of its base synonym.