Search Results avail_quantity

Overview

APPS.POR_SRC_SUBINV_LOV_V is a Purchasing (POR) view that exposes a list of valid inventory source subinventories for a given item and organization. Its primary purpose is to drive a List of Values (LOV) in Oracle E-Business Suite, allowing users to select a source subinventory when creating or maintaining requisition, purchase order, or internal requisition lines that are sourced from inventory. The view returns, for each item/organization/subinventory combination, the current availability quantity converted into the item's issuance unit of measure, alongside descriptive identifiers such as the organization name and subinventory code. In Oracle EBS 12.1.1 and 12.2.2 the object is defined in the APPS schema and remains logically consistent across both releases.

The view is a UNION of two query blocks. The first block computes a live available quantity by subtracting reservation quantities from total quantity on hand; the second block returns a set of zero-quantity rows so that subinventories with no on-hand stock are still selectable. This dual behavior makes it suitable both for displaying quantity information and for populating an LOV that must include all valid subinventories.

Underlying Base Objects

The documented base objects referenced by the view, as catalogued in ETRM for 12.2.2, are:

• MTL_ONHAND_SUB_V (VIEW) — supplies total quantity on hand (TOTAL_QOH) at the subinventory level.
• MTL_RESERVATIONS (SYNONYM) — provides reservation quantities netted against on-hand.
• MTL_SYSTEM_ITEMS (SYNONYM) — item master attributes including unit of issue, primary unit of measure, and the restrict-subinventories flag.
• MTL_SECONDARY_INVENTORIES (SYNONYM) — subinventory definition, including the quantity-tracked flag and disable date.
• MTL_UNITS_OF_MEASURE_VL (VIEW) — used twice (aliases UOM and UOM2) to resolve UOM codes and descriptions.
• MTL_ITEM_SUB_INVENTORIES (SYNONYM) — the item-to-subinventory assignment list, referenced in the existence check.
• HR_ORGANIZATION_UNITS (VIEW) — resolves the organization name.
• INV_CONVERT (PACKAGE) — the INV_UM_CONVERT function converts on-hand/reservation quantities into the target UOM.
• HR_GENERAL (PACKAGE) and HR_SECURITY (PACKAGE) — support organization-level security and context resolution.

The joins are outer joins ((+)) on MTL_RESERVATIONS, so subinventories without reservations still appear. Filtering ensures only quantity-tracked subinventories that are not yet disabled are returned, and honors item-level subinventory restrictions.

Key Columns

• INVENTORY_ITEM_ID — the item identifier.
• ORGANIZATION_ID — the inventory organization identifier (note the view exposes both MSI.ORGANIZATION_ID and MOS.ORGANIZATION_ID).
• ORGANIZATION_NAME — descriptive name of the organization.
• SUBINVENTORY_CODE — the subinventory being offered for selection.
• UNIT_OF_MEASURE_TL — the unit of measure description.
• AVAIL_QUANTITY — the computed availability. The first UNION branch computes this via ROUND(INV_CONVERT.INV_UM_CONVERT(...,(TOTAL_QOH - SUM(NVL(RESERVATION_QUANTITY,0))),...)); the second branch returns a literal 0. This is the column users typically filter or sort on when searching for avail_quantity.

Common Use Cases and Queries

The view is most commonly queried to populate source-subinventory LOVs and to display availability during sourcing. A typical query retrieving available subinventories for an item is:

• SELECT SUBINVENTORY_CODE, ORGANIZATION_NAME, AVAIL_QUANTITY
• FROM APPS.POR_SRC_SUBINV_LOV_V
• WHERE INVENTORY_ITEM_ID = :item_id
• AND ORGANIZATION_ID = :org_id
• ORDER BY SUBINVENTORY_CODE;

Because the view already joins to MTL_SECONDARY_INVENTORIES and item restrictions, callers do not need to re-validate subinventory eligibility. Reports may filter on AVAIL_QUANTITY > 0 to show only stocked subinventories, or omit the predicate to list all valid selections. Integration programs reading AVAIL_QUANTITY should be aware that the zero-quantity rows originate from the second UNION branch, ensuring complete LOV coverage regardless of on-hand balances.