Results for “mtl_secinv_quantities_view”

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

Overview

MTL_SECINV_QUANTITIES_VIEW is a reporting view owned by the APPS schema in Oracle E-Business Suite, belonging to the Oracle Inventory (INV) product family. It is documented as a VALID object in ETRM for both release 12.1.1 and 12.2.2. As its name and description ("View of Item subinventory") indicate, the view presents an aggregated quantity position for each inventory item, segmented by organization and subinventory. Its principal purpose is to expose the on-hand quantity that an item holds within a given subinventory, consolidated into a single total per organization/item/subinventory combination.

Because it reduces detailed on-hand rows to one summarized row per subinventory, the view is well suited to reporting, dashboards, and integration extracts where a per-subinventory total is required rather than transaction-level granularity. It is a read-only object and carries no storage of its own; all results are derived at query time from its single underlying source.

Underlying Base Objects

The view is defined over one documented base object, exposed through a synonym: MTL_ONHAND_QUANTITIES_DETAIL. The view text executes a grouped aggregation against this detail table:

• SELECT ORGANIZATION_ID, INVENTORY_ITEM_ID, SUBINVENTORY_CODE, SUM(PRIMARY_TRANSACTION_QUANTITY) TOTAL_QUANTITY FROM MTL_ONHAND_QUANTITIES_DETAIL GROUP BY ORGANIZATION_ID, INVENTORY_ITEM_ID, SUBINVENTORY_CODE

The aggregation collapses the multiple on-hand detail records maintained for an item in a subinventory into a single row carrying the summed PRIMARY_TRANSACTION_QUANTITY. Since MTL_ONHAND_QUANTITIES_DETAIL stores quantities in the primary unit of measure, the resulting totals are expressed in the item's primary UOM. Because the definition is a straightforward GROUP BY over a detail table, the view inherits the freshness of that base data and should be queried with awareness that it reflects the state of the on-hand detail table at query time.

Key Columns

• ORGANIZATION_ID — The inventory organization identifier (from ORG_ORGANIZATION_DEFINITIONS) under which the on-hand quantity is held. It forms the first grouping key and is essential for filtering within a multi-organization installation.
• INVENTORY_ITEM_ID — The unique internal identifier of the inventory item. This is the item master key and should be joined to item-related views for descriptive attributes.
• SUBINVENTORY_CODE — The subinventory within the organization in which the quantity resides. Documented view text renders this as SUBINVENTORY_CODE (noted alternatively as SUBINVENTOR_CODE in the metadata column listing).
• TOTAL_QUANTITY — The summed PRIMARY_TRANSACTION_QUANTITY for the organization/item/subinventory combination. This is the column users typically target when searching for "total_quantity," and it represents the aggregate on-hand figure in the item's primary unit of measure.

Common Use Cases and Queries

The view supports on-hand reporting by subinventory, item availability checks, and data feeds to external systems. A typical query retrieving the total quantity for a single item across all subinventories would be:

• SELECT ORGANIZATION_ID, SUBINVENTORY_CODE, TOTAL_QUANTITY FROM APPS.MTL_SECINV_QUANTITIES_VIEW WHERE INVENTORY_ITEM_ID = :item_id AND ORGANIZATION_ID = :org_id ORDER BY SUBINVENTORY_CODE;

For a full listing of on-hand by organization:

• SELECT ORGANIZATION_ID, INVENTORY_ITEM_ID, SUBINVENTORY_CODE, TOTAL_QUANTITY FROM APPS.MTL_SECINV_QUANTITIES_VIEW WHERE ORGANIZATION_ID = :org_id ORDER BY INVENTORY_ITEM_ID, SUBINVENTORY_CODE;

Because the view exposes only numeric IDs for organization and item, joins to descriptive views (for example, item and organization definition views) are commonly added to produce human-readable output. When a consolidated organization-wide figure is needed, callers further aggregate TOTAL_QUANTITY across subinventories within a query. As with all APPS reporting views, access should be granted through the standard INV responsibility or a read-only synonym, and users should confirm the current on-hand picture against reconciliation reports, since the view reports the aggregate of the underlying detail records without separate validation of that detail.