Search Results edw_items_item_rev_fkv

Overview

EDW_ITEMS_ITEM_REV_FKV is a reporting view owned by the APPS schema within the Oracle E-Business Suite Engineering (ENG) product family. Its purpose, as documented in the ETRM repository, is to hold item-to-revision information by joining inventory item master records with their corresponding revision definitions and inventory organization parameters. The view is classified as a flexible key flexfield (FKV) style construct, which in Oracle EBS terminology indicates that it exposes a concatenated, human-readable identifier derived from underlying key flexfield segments in addition to the raw numeric identifiers. This makes it well suited to enterprise data warehouse extraction, ad hoc reporting, and downstream integration where a descriptive, display-ready item-revision reference is preferred over separate surrogate key columns. The view is valid in both Oracle EBS 12.1.1 and 12.2.2, as its definition relies only on long-standing inventory and engineering base tables that exist in both releases. It is typically consumed by tools such as Oracle Discoverer, BI Publisher, OBIEE, or custom PL/SQL reporting routines that need a flattened, denormalized item revision label.

Underlying Base Objects

The view text exposes three referenced base objects:

• MTL_SYSTEM_ITEMS_KFV (aliased MSI) — the key flexfield view over MTL_SYSTEM_ITEMS, supplying the item primary key (INVENTORY_ITEM_ID), organization context (ORGANIZATION_ID), and the concatenated flexfield string (CONCATENATED_SEGMENTS) used as the descriptive item identifier.
• MTL_ITEM_REVISIONS (aliased MTR) — the engineering revision table that stores each revision code (REVISION) associated with a given item and organization.
• MTL_PARAMETERS (aliased MP) — the inventory organization parameters table, providing ORGANIZATION_CODE for the organization that owns the item revision.

The view joins these three objects on the composite key of INVENTORY_ITEM_ID and ORGANIZATION_ID, ensuring that each returned row reflects an item revision scoped to the correct inventory organization and carries the corresponding organization code. No other base tables are documented as referenced.

Key Columns

• INVENTORY_ITEM_ID — Numeric primary key of the inventory item in MTL_SYSTEM_ITEMS.
• ORGANIZATION_ID — Numeric identifier of the inventory organization in which the item and revision are defined.
• REVISION — The engineering revision code (for example, "A", "B", "01") assigned to the item within that organization.
• ITEM_REV_FK — A concatenated display string formed as REVISION || '-' || CONCATENATED_SEGMENTS || '-' || ORGANIZATION_CODE. This derived column provides a single, human-readable flexible key that uniquely labels an item revision across organizations.

Common Use Cases and Queries

Typical uses include populating revision dimensions in a data warehouse, validating revision assignments during item import, and generating pick-list or quality reports that must display both the item and its controlling revision.

A simple listing for one organization:

• SELECT item_rev_fk, inventory_item_id, revision FROM apps.edw_items_item_rev_fkv WHERE organization_id = :org_id ORDER BY item_rev_fk;

Retrieving all revisions for a specific item across organizations:

• SELECT organization_id, revision, item_rev_fk FROM apps.edw_items_item_rev_fkv WHERE inventory_item_id = :item_id;

Joining to other reporting views, the ITEM_REV_FK column serves as a convenient natural key, eliminating the need for the consumer to reconstruct the revision-item-organization concatenation manually.