Results for “current_subinventory_code”

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

Overview

POS_MTL_ONHAND_SERIAL_KFV is an APPS-owned key flexfield (KFV) reporting view in Oracle E-Business Suite, catalogued under the Purchasing (PO) product family. It presents serial-number-level on-hand information by joining serial number records in MTL_SERIAL_NUMBERS to their parent item definitions and organization/location identifiers. The view is implemented as a UNION of two SELECT statements, both of which project the same column list, ensuring a consistent shape for downstream consumers regardless of which branch produced the row. The first branch returns serial numbers that currently reside in a valid locator; the second returns serial numbers whose current status equals 3 (defined) but which have no current locator assigned. This dual-branch design lets the view represent both located inventory and unlocated or status-driven serials in a single result set.

Underlying Base Objects

The UNION is built from four principal data sources, all resolved through APPS. The primary driver is MTL_SERIAL_NUMBERS (referenced as a synonym in APPS), which supplies the serial identity and current on-hand attributes. MTL_SYSTEM_ITEMS_KFV (also a synonym) provides item description, primary unit of measure, the concatenated item flexfield segments, and — critically for the user's search term "item_url" — the ATTRIBUTE14 column exposed as ITEM_URL. HR_ORGANIZATION_UNITS (a view) supplies the organization NAME, while MTL_ITEM_LOCATIONS_KFV (a view) supplies the concatenated LOCATION value. The documented referenced base objects also include the HR_GENERAL and HR_SECURITY packages, which apply organization-level security filtering to HR_ORGANIZATION_UNITS so that only organizations visible to the querying responsibility are returned. This security integration is significant: row visibility is governed by the standard HR security model, not by an application-specific predicate in the view text itself.

Key Columns

• INVENTORY_ITEM_ID — Surrogate key linking the serial record to its item master definition.
• SERIAL_NUMBER and REVISION — The unique serial identifier and its revision, forming the operational identity of the tracked unit.
• CURRENT_STATUS — Numeric status code; the value 3 (defined) is used in the second UNION branch to capture serials without a current locator.
• LOT_NUMBER — Lot association for lot-controlled items.
• CURRENT_SUBINVENTORY_CODE, CURRENT_LOCATOR_ID, and CURRENT_ORGANIZATION_ID — Current storage and organizational context.
• ORGANIZATION_NAME — Descriptive name resolved from HR_ORGANIZATION_UNITS.
• ITEM_DESCRIPTION and PRIMARY_UOM_CODE — Item descriptive and unit-of-measure attributes.
• ITEM and LOCATION — Concatenated flexfield segment strings from the respective KFV views; LOCATION is NULL in the unlocated branch.
• ITEM_URL — Exposed from MTL_SYSTEM_ITEMS_KFV.ATTRIBUTE14, providing a configurable per-item hyperlink or reference URL. Because it derives from a DFF attribute, it is populated only when the item's attribute segment is maintained.

Common Use Cases and Queries

The view is typically used for serialized on-hand reporting, cycle-count preparation, and integration feeds requiring serial, item, location, and an associated item URL. A representative query follows:

SELECT serial_number, item, item_description, primary_uom_code,
       organization_name, current_subinventory_code, location,
       current_status, item_url
FROM   apps.pos_mtl_onhand_serial_kfv
WHERE  current_organization_id = :p_org_id
AND    serial_number = :p_serial;

To locate items with a maintained URL: WHERE item_url IS NOT NULL. To retrieve unlocated serials, filter on current_status = 3 AND location IS NULL. Because HR organization security is applied through HR_SECURITY, callers should verify the accessing responsibility has visibility to the target organization or rows may be silently excluded.