Results for “mtl_item_locations_view”

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

Overview

MTL_ITEM_LOCATIONS_VIEW is an Oracle E-Business Suite database view owned by the APPS schema and registered under the Inventory (INV) product module. Its documented purpose is to present a consolidated picture of item quantities by locator. In Oracle EBS 12.1.1 and 12.2.2, this view functions as a reporting and integration convenience layer rather than a transactional object: it aggregates on-hand quantity data across the key dimensions of locator, organization, item, and subinventory, producing a single row per unique combination of those attributes.

Because on-hand inventory in EBS is physically tracked at a finer granularity — by locator, lot, revision, and other descriptive attributes — the view abstracts away that detail to expose a summed quantity per locator. This makes it suitable for queries, custom reports, and interface programs that need a straightforward "how much of this item sits in this locator" answer without joining and aggregating the base on-hand tables themselves.

Underlying Base Objects

The view is defined over a single referenced object: MTL_ONHAND_QUANTITIES_DETAIL, accessed through a synonym in the APPS schema. The view definition groups the detail rows by LOCATOR_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID, and SUBINVENTORY_CODE, summing PRIMARY_TRANSACTION_QUANTITY (with NVL to zero) into a derived TOTAL_QUANTITY column.

MTL_ONHAND_QUANTITIES_DETAIL is the base on-hand table that stores quantities at the most granular level, including lot, revision, and locator detail. Because the view groups at the locator level, any multiple detail rows that share the same locator, organization, item, and subinventory combination are collapsed into one aggregated row. This relationship means the view inherits the transactional accuracy of the underlying on-hand data while reducing it to a coarser reporting grain.

Key Columns

• LOCATOR_ID — The inventory locator (physical bin or storage position) identifier. Combined with organization and item, this is the principal grouping key.
• ORGANIZATION_ID — The inventory organization to which the on-hand quantity belongs; essential for multi-organization environments.
• INVENTORY_ITEM_ID — The unique identifier of the inventory item. This is the internal key, not the user-facing item number, so joins to MTL_SYSTEM_ITEMS_B are typically required to display segment descriptions.
• TOTAL_QUANTITY — The summed primary transaction quantity for the grouping combination, defaulted to zero when no quantity is present. This is the central measure exposed by the view.
• SUBINVENTORY_CODE — The subinventory in which the locator resides, included in the grouping and therefore part of each row's uniqueness.

Common Use Cases and Queries

Typical applications include locator-level stock inquiries, custom inventory reports, and integration extracts that feed external systems or data warehouses. The view is especially useful when the desired output grain matches locator-level aggregation and the consumer does not need lot or revision breakdowns.

A representative query joining to the item master to resolve the item name for a given organization:

• SELECT v.ORGANIZATION_ID, v.INVENTORY_ITEM_ID, m.SEGMENT1 ITEM_NUMBER, v.SUBINVENTORY_CODE, v.LOCATOR_ID, v.TOTAL_QUANTITY
• FROM APPS.MTL_ITEM_LOCATIONS_VIEW v, APPS.MTL_SYSTEM_ITEMS_B m
• WHERE v.INVENTORY_ITEM_ID = m.INVENTORY_ITEM_ID
• AND v.ORGANIZATION_ID = m.ORGANIZATION_ID
• AND v.ORGANIZATION_ID = :org_id
• AND v.TOTAL_QUANTITY > 0
• ORDER BY m.SEGMENT1, v.SUBINVENTORY_CODE;

Because TOTAL_QUANTITY reflects only summed primary transaction quantity at the locator grain, report developers should confirm whether their requirements include negative quantities, lot or revision detail, or reserved and available distinctions — all of which reside in the underlying on-hand detail tables rather than in this aggregated view. Where such granularity is required, querying MTL_ONHAND_QUANTITIES_DETAIL directly, or the standard on-hand query objects, is the appropriate alternative.