Results for “icx_mtl_onhand_serial_kfv”

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

Overview

The ICX_MTL_ONHAND_SERIAL_KFV view is a key flexfield (KFF) presentation view owned by the APPS schema in Oracle E-Business Suite. It belongs to the ICX product family, Oracle iProcurement, and is documented as the "On-Hand Item Quantity View for Serialized Items." Its purpose is to expose a consolidated, query-friendly projection of on-hand serial numbers held in inventory, enriched with descriptive item, organization, and locator attributes so that iProcurement and other ICX-dependent modules can display serialized stock without performing multi-table joins at runtime.

Because it is a view rather than a table, ICX_MTL_ONHAND_SERIAL_KFV provides a read-only reporting and integration surface. It is valid in both the 12.1.1 and 12.2.2 releases, with the ETRM 12.2.2 metadata confirming APPS ownership and VALID status. It abstracts the underlying serial/lot/locator structures into descriptive KFF concatenated segments, making it suitable for reports, BI Publisher extracts, and iProcurement requisition or catalog screens that must reflect serialized on-hand stock.

Underlying Base Objects

The view is defined over several base objects, documented in ETRM 12.2.2 as HR_GENERAL (package), HR_ORGANIZATION_UNITS (view), HR_SECURITY (package), MTL_ITEM_LOCATIONS_KFV (view), MTL_SERIAL_NUMBERS (synonym), and MTL_SYSTEM_ITEMS_KFV (synonym).

• MTL_SERIAL_NUMBERS (A) — the primary driver, supplying the serial number, revision, current status, lot number, current subinventory, locator, and organization.
• MTL_SYSTEM_ITEMS_KFV (B) — supplies item description, primary UOM, the concatenated item segments (ITEM), and ATTRIBUTE14, which the view aliases as ITEM_URL.
• HR_ORGANIZATION_UNITS (C) — supplies the operating-unit NAME as ORGANIZATION_NAME.
• MTL_ITEM_LOCATIONS_KFV (D) — supplies the concatenated location segments (LOCATION).

The view text is a UNION of two branches. The first branch selects serials where CURRENT_LOCATOR_ID IS NOT NULL, joining MTL_SERIAL_NUMBERS to all three descriptive objects and returning a populated LOCATION. The second branch handles serials in status 3 (defined) where CURRENT_LOCATOR_ID IS NULL, joining only MTL_SYSTEM_ITEMS_KFV and HR_ORGANIZATION_UNITS and returning NULL LOCATION. The HR_GENERAL and HR_SECURITY packages are referenced indirectly through HR_ORGANIZATION_UNITS to enforce organizational security.

Key Columns

• INVENTORY_ITEM_ID, SERIAL_NUMBER, REVISION — the item identifier and the specific serial/revision of the unit.
• CURRENT_STATUS — the serial's current state (for example, 3 for defined), determining which UNION branch supplies the row.
• LOT_NUMBER — the lot to which the serial belongs, where lot control applies.
• CURRENT_SUBINVENTORY_CODE, CURRENT_LOCATOR_ID, CURRENT_ORGANIZATION_ID — the physical stock position of the serial.
• ORGANIZATION_NAME, ITEM_DESCRIPTION, PRIMARY_UOM_CODE — descriptive attributes from HR_ORGANIZATION_UNITS and MTL_SYSTEM_ITEMS_KFV.
• ITEM, LOCATION — concatenated key flexfield segments for the item and the stock locator.
• ITEM_URL — sourced from ATTRIBUTE14, used to link to an item's detail or media URL.

Common Use Cases and Queries

The view is typically consumed to determine which serialized items are available in a given organization or locator, to drive iProcurement catalog availability, and to feed serialized on-hand reporting. A representative query lists on-hand serials for one inventory item within an organization:

SELECT serial_number, revision, lot_number,
       current_subinventory_code, location, organization_name
FROM   apps.icx_mtl_onhand_serial_kfv
WHERE  inventory_item_id = :item_id
AND    current_organization_id = :org_id
AND    location IS NOT NULL;

Reports frequently aggregate by organization or item, filtering LOCATION IS NOT NULL to exclude the unlocated, defined-status branch. Because the view resolves item and locator KFF concatenations and applies organization security through HR_ORGANIZATION_UNITS, it remains the preferred ICX source for presenting serialized on-hand stock in Oracle EBS 12.1.1 and 12.2.2.