Search Results item_url




Overview
Explore in AI Chat

Overview

APPS.ICX_MTL_ONHAND_SERIAL_KFV is a key flexfield (KFV) view within the Oracle E-Business Suite inventory schema that presents serialized on-hand material in a denormalized, user-friendly form. It joins serial number records to descriptive flexfield-enabled system items, organization units, and item locations, exposing both the raw identifiers and their human-readable concatenated segment values. The view is used primarily by Oracle iProcurement, Inventory, and related self-service modules to resolve serial-controlled inventory into comprehensible rows that carry item descriptions, organization names, subinventory and locator context, and — notably for the search term in question — an application-defined item URL stored in the item's ATTRIBUTE14 column. Because it is a view and not a table, it provides real-time representation of on-hand serialized stock rather than a snapshot, making it suitable for reporting, integration extracts, and UI lookups that need joined serial-plus-item context in a single pass.

Underlying Base Objects

The documented base objects underlying this view are MTL_SERIAL_NUMBERS (SYNONYM), MTL_SYSTEM_ITEMS_KFV (SYNONYM), MTL_ITEM_LOCATIONS_KFV (VIEW), HR_ORGANIZATION_UNITS (VIEW), and the packages HR_SECURITY and HR_GENERAL. The view text is a UNION of two SELECT branches. The first branch joins MTL_SERIAL_NUMBERS A to MTL_SYSTEM_ITEMS_KFV B on INVENTORY_ITEM_ID and CURRENT_ORGANIZATION_ID, to HR_ORGANIZATION_UNITS C on organization, and to MTL_ITEM_LOCATIONS_KFV D on locator and organization, returning the concatenated LOCATION. The second branch deliberately restricts to CURRENT_STATUS = 3 with a NULL current locator, and returns NULL for LOCATION — capturing serialized units that are defined but not tied to a specific locator, such as inventory in transit or awaiting placement. HR_SECURITY and HR_GENERAL are referenced by the organization view to enforce organization-level access security, so query results are filtered according to the operating unit and organization hierarchy context of the connected user.

Key Columns

  • INVENTORY_ITEM_ID — Primary inventory item identifier, the join key to MTL_SYSTEM_ITEMS_KFV and the underlying item master.
  • SERIAL_NUMBER — The unique serial number assigned to the tracked unit.
  • REVISION — Revision level of the serialized item, where revision control applies.
  • CURRENT_STATUS — Numeric serial status code; status 3 identifies units handled by the locator-less branch of the UNION.
  • LOT_NUMBER — Associated lot or batch number, if the item is lot-controlled.
  • CURRENT_SUBINVENTORY_CODE — The subinventory where the unit currently resides.
  • CURRENT_LOCATOR_ID — Foreign key to the locator; NULL in the status-3 branch.
  • CURRENT_ORGANIZATION_ID / ORGANIZATION_NAME — Owning inventory organization identifier and its HR organization name.
  • ITEM_DESCRIPTION / PRIMARY_UOM_CODE — Item master description and primary unit of measure.
  • ITEM — Concatenated flexfield segments forming the display item number from MTL_SYSTEM_ITEMS_KFV.
  • LOCATION — Concatenated locator flexfield segments from MTL_ITEM_LOCATIONS_KFV, or NULL.
  • ITEM_URL — Mapped from B.ATTRIBUTE14 on the item, supplying an application-defined hyperlink or document reference for the item. This is the column users search for under the term "item_url" and serves as a convenient hook for launching external content from an on-hand query.

Common Use Cases and Queries

Typical uses include on-hand serialized inventory reporting, drill-down from iProcurement receipt or inventory pages, and integration extracts that need item plus serial plus location context. Because ITEM_URL flows from ATTRIBUTE14, a frequent pattern is retrieving the stored URL alongside serial detail:

  • Listing all serialized on-hand units for an organization: SELECT item, serial_number, organization_name, current_subinventory_code, location, item_url FROM apps.icx_mtl_onhand_serial_kfv WHERE current_organization_id = :org_id ORDER BY item, serial_number;
  • Locating a specific serial across organizations: SELECT item, serial_number, organization_name, current_status, item_url FROM apps.icx_mtl_onhand_serial_kfv WHERE serial_number = :serial;
  • Extracting items carrying a defined URL: SELECT DISTINCT item, item_description, item_url FROM apps.icx_mtl_onhand_serial_kfv WHERE item_url IS NOT NULL;
  • Identifying locator-less units: SELECT item, serial_number, current_status FROM apps.icx_mtl_onhand_serial_kfv WHERE location IS NULL AND current_status = 3;

Queries should account for organization security enforced through HR_SECURITY and note that the UNION may return the same serial twice under different locator conditions. Restricting by CURRENT_ORGANIZATION_ID improves performance, since the view performs no distinct aggregation and relies on the underlying joins for filtering.