Search Results por_src_subinv_lov_v




Overview
Explore in AI Chat

Overview

The view APPS.POR_SRC_SUBINV_LOV_V is a data dictionary object within the Oracle E-Business Suite Release 12.1.1 and 12.2.2 environments, owned by the APPS schema and categorized under the ICX (Oracle iProcurement) product module. As its name implies, it functions as a List of Values (LOV) source for subinventory selection on the iProcurement "Select Source" page. When users search for or attempt to requisition an item, the application must determine from which subinventories the item may be sourced, respecting item restrictions, subinventory disabling, and quantity-tracked status. This view encapsulates that logic into a single, queryable result set. For developers and technical consultants, it serves as a reference for understanding how iProcurement validates source subinventory availability and how on-hand quantities are presented in the LOV. Because it is a view rather than a table, it carries no storage of its own and reflects live transactional and setup data at runtime. It is documented as VALID in the ETRM metadata repository, indicating that it compiles successfully and its underlying dependencies are intact.

Underlying Base Objects

The view is defined over a set of base tables, views, synonyms, and packages. Documented referenced base objects include HR_GENERAL (package), HR_ORGANIZATION_UNITS (view), HR_SECURITY (package), INV_CONVERT (package), MTL_ITEM_SUB_INVENTORIES (synonym), MTL_ONHAND_SUB_V (view), MTL_RESERVATIONS (synonym), MTL_SECONDARY_INVENTORIES (synonym), MTL_SYSTEM_ITEMS (synonym), and MTL_UNITS_OF_MEASURE_VL (view). The synonyms resolve to the corresponding MTL and HR base tables in the INV and HR schemas respectively. The view text reveals a UNION of two queries: the first joins on-hand subinventory quantities against reservations, system items, and units of measure to compute an available quantity to issue; the second produces zero-quantity rows for valid subinventories that have no current on-hand balance. The INV_CONVERT.INV_UM_CONVERT package function is invoked to convert between the primary unit of measure and the unit of issue. Security packages HR_GENERAL and HR_SECURITY restrict organization access, and MTL_ITEM_SUB_INVENTORIES enforces item-level subinventory restrictions.

Key Columns

The view exposes columns essential for source selection. INVENTORY_ITEM_ID identifies the item. Two ORGANIZATION_ID columns appear — one from MTL_ONHAND_SUB_V (the on-hand organization) and one from MTL_SYSTEM_ITEMS — along with ORGANIZATION_NAME for display. SUBINVENTORY_CODE (exposed as SECONDARY_INVENTORY_NAME in the union branch) is the subinventory being selected. UNIT_OF_MEASURE_TL presents the translated unit of measure. The computed AVAIL_QUANTITY column is the crux of the LOV, calculated as total quantity on hand minus reserved quantity, converted to the item's unit of issue via INV_UM_CONVERT. In the union branch, this value is hard-coded to 0 for subinventories with no on-hand stock. The GROUP BY clause aggregates reservation quantities per item, organization, and subinventory. The filter MSUB.QUANTITY_TRACKED = 1 excludes non-quantity-tracked subinventories, and the date predicate excludes subinventories past their DISABLE_DATE.

Common Use Cases and Queries

Typical scenarios include diagnosing why a subinventory does not appear in the iProcurement Select Source LOV, auditing item-subinventory restrictions, or building custom reports of sourceable quantity. A representative query follows:

  • Available sources for an item: SELECT inventory_item_id, organization_id, subinventory_code, unit_of_measure_tl, avail_quantity FROM apps.por_src_subinventory_lov_v WHERE inventory_item_id = :item_id ORDER BY subinventory_code;
  • Check item-level restrictions: SELECT * FROM mtl_item_sub_inventories WHERE inventory_item_id = :item_id AND organization_id = :org_id; — used to interpret RESTRICT_SUBINVENTORIES_CODE = 1.
  • Zero-quantity valid subinventories: filter on avail_quantity = 0 to identify valid but empty sources.

Consultants should note that the view is read-only, performance-sensitive due to the INV_UM_CONVERT call per row, and should be queried with organization and item filters to avoid full scans on MTL_ONHAND_SUB_V.