Results for “pos_mtl_onhand_rev_kfv”

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

Overview

APPS.POS_MTL_ONHAND_REV_KFV is a Purchasing (PO) module view in Oracle E-Business Suite 12.1.1 and 12.2.2 that exposes on-hand inventory balances broken down by inventory organization, item, and revision. The view is a consolidated, key-flexfield-enabled reporting layer over the core inventory on-hand tables, and its central computed column is TOTAL_QOH — the summed on-hand quantity per item and revision combination. Because the object name carries the _KFV suffix, the item identifier exposed is the concatenated key flexfield description rather than the raw segment structure, which makes the view directly usable in Oracle Reports, BI Publisher, Discoverer, and ad-hoc SQL without additional flexfield concatenation logic. The user search term "total_qoh" maps directly to the aggregate column defined in the view text, confirming that the typical entry point for this object is the requirement to retrieve total on-hand quantity per item/revision within an organization.

The view is defined and owned by APPS, is documented as VALID, and joins across four operational areas: inventory on-hand quantities, item master information, inventory parameters, and HR organization units. Its product designation as PO - Purchasing reflects its use in procurement-side supply and availability reporting rather than as a core Inventory inquiry view.

Underlying Base Objects

Per the documented ETRM metadata, the view is defined over the following objects:

• MTL_ONHAND_QUANTITIES (VIEW) — the primary fact source supplying TRANSACTION_QUANTITY, ORGANIZATION_ID, INVENTORY_ITEM_ID, and REVISION.
• MTL_SYSTEM_ITEMS_KFV (SYNONYM) — supplies the concatenated item flexfield (CONCATENATED_SEGMENTS), DESCRIPTION, PRIMARY_UOM_CODE, and ATTRIBUTE14 (used as ITEM_URL).
• MTL_PARAMETERS (SYNONYM) — supplies the ORGANIZATION_CODE.
• HR_ORGANIZATION_UNITS (VIEW) — supplies the organization NAME.
• HR_SECURITY (PACKAGE) and HR_GENERAL (PACKAGE) — supporting objects referenced indirectly, typically for organization-level security filtering and HR hierarchy resolution behind HR_ORGANIZATION_UNITS.

The join conditions link all four primary sources on ORGANIZATION_ID and link on-hand balances to the item master on both INVENTORY_ITEM_ID and ORGANIZATION_ID. A GROUP BY clause produces one row per organization/item/revision combination, with SUM(A.TRANSACTION_QUANTITY) forming TOTAL_QOH.

Key Columns

• ORGANIZATION_ID / ORGANIZATION_CODE / ORGANIZATION_NAME — the inventory organization identifiers, coded value, and descriptive name.
• INVENTORY_ITEM_ID — the internal surrogate key for the item.
• ITEM — the concatenated item key flexfield segment string.
• REVISION — the item revision; for non-revision-controlled items this is typically NULL, so a single item may appear on multiple rows when revisions exist.
• TOTAL_QOH — the summed on-hand transaction quantity for the item/revision in the organization; this is the requested metric.
• ITEM_DESCRIPTION, PRIMARY_UOM_CODE — item description and unit of measure.
• ITEM_URL — sourced from item ATTRIBUTE14, intended as a hyperlink reference for drill-down.

Common Use Cases and Queries

Typical uses include on-hand availability checks, revision-level inventory reporting, and supply validation during purchasing and planning. A representative query retrieving total on-hand for a specific item is:

• SELECT organization_code, revision, total_qoh, primary_uom_code FROM apps.pos_mtl_onhand_rev_kfv WHERE item = :item_flexfield AND organization_code = :org_code;
• SELECT item, item_description, revision, SUM(total_qoh) FROM apps.pos_mtl_onhand_rev_kfv GROUP BY item, item_description, revision;
• SELECT * FROM apps.pos_mtl_onhand_rev_kfv WHERE total_qoh > 0 ORDER BY organization_code, item;

Because rows are pre-aggregated by organization, item, and revision, additional filtering or pivoting by organization is straightforward, and the view is well suited for embedding in report data models where TOTAL_QOH is the primary output measure.