Search Results total_qoh




Overview
Explore in AI Chat

Overview

ICX_MTL_ONHAND_LOT_KFV is an APPS-owned database view in Oracle E-Business Suite that presents on-hand item quantities organized by lot. It belongs to the ICX product family, Oracle iProcurement, and is therefore relevant to requisition, catalog, and sourcing flows where lot-controlled inventory availability must be surfaced to the application. The view consolidates quantity information from the on-hand quantities table and enriches it with descriptive attributes drawn from item, organization, lot, and locator master data, producing a denormalized result set suited for reporting and integration rather than transactional processing.

The object holds a VALID status in both 12.1.1 and 12.2.2, and it is documented in ETRM as a view rather than a table, which means it stores no data of its own and always reflects the current state of its underlying sources. The view text shows a UNION of two SELECT statements against the same set of tables, differing chiefly in how the locator is handled. The first branch restricts rows to records where LOCATOR_ID is not null and joins to MTL_ITEM_LOCATIONS_KFV to retrieve a concatenated location string; the second branch permits a null locator and substitutes a NULL LOCATION value. This design ensures that on-hand lots held in locator-controlled subinventories and those held without a specific locator both appear in the result set.

Underlying Base Objects

The view is defined over a combination of views, synonyms, and packaged objects. Documented references include HR_GENERAL (PACKAGE), HR_ORGANIZATION_UNITS (VIEW), HR_SECURITY (PACKAGE), MTL_ITEM_LOCATIONS_KFV (VIEW), MTL_LOT_NUMBERS (SYNONYM), MTL_ONHAND_QUANTITIES (VIEW), MTL_PARAMETERS (SYNONYM), and MTL_SYSTEM_ITEMS_KFV (SYNONYM).

MTL_ONHAND_QUANTITIES supplies the core quantity and lot dimension data, including transaction quantity, subinventory, locator, revision, and lot number. MTL_SYSTEM_ITEMS_KFV provides concatenated item segments, description, primary unit of measure, and attribute columns. MTL_LOT_NUMBERS contributes expiration date and validates lot-to-item relationships. MTL_PARAMETERS supplies the organization code, while HR_ORGANIZATION_UNITS provides the organization name, establishing the security context used by the HR_SECURITY and HR_GENERAL packages. The joins are driven by ORGANIZATION_ID and INVENTORY_ITEM_ID across these objects, with LOT_NUMBER linking to MTL_LOT_NUMBERS.

Key Columns

  • ORGANIZATION_ID — Inventory organization identifier.
  • INVENTORY_ITEM_ID — Surrogate key of the item.
  • ITEM — Concatenated item segments from MTL_SYSTEM_ITEMS_KFV.
  • REVISION — Item revision, where revision control applies.
  • TOTAL_QOH — Sum of TRANSACTION_QUANTITY, the total on-hand quantity for the item, lot, subinventory, and locator combination. This is the column frequently requested in searches for total_qoh.
  • SUBINVENTORY_CODE — Subinventory holding the stock.
  • LOCATOR_ID / LOCATION — Locator identifier (defaulted to -1 when null) and its concatenated description.
  • LOT — Lot number.
  • ITEM_DESCRIPTION and PRIMARY_UOM_CODE — Descriptive item attributes.
  • ORGANIZATION_CODE and ORGANIZATION_NAME — Organization identifiers for reporting.
  • EXPIRATION_DATE — Lot expiration date.
  • ITEM_URL — Derived from ATTRIBUTE14, typically used for item hyperlinks in iProcurement pages.

Common Use Cases and Queries

The view supports lot-level availability reporting, expiration monitoring, and integration into iProcurement catalog displays. A typical query retrieving total on-hand by item and lot is:

SELECT item, lot, subinventory_code, location, total_qoh, expiration_date
FROM apps.icx_mtl_onhand_lot_kfv
WHERE organization_id = :org_id
AND total_qoh > 0;

To isolate lots nearing expiration, filter on EXPIRATION_DATE within a date range while preserving the same grouping. Joins to MTL_SYSTEM_ITEMS_KFV attributes through ITEM and ITEM_URL allow the view to be embedded in custom OAF or JSP pages without additional lookups. Because the view applies a GROUP BY over quantity, callers should treat TOTAL_QOH as an aggregate and avoid assuming row uniqueness across locator and subinventory combinations.