Search Results mtl_system_items_er3_v




Overview
Explore in AI Chat

Overview

MTL_SYSTEM_ITEMS_ER3_V is a view owned by the APPS schema within the Oracle E-Business Suite Inventory (INV) module. It is documented as an ERES (Electronic Records and Electronic Signatures) supporting view, purpose-built to serve the ERES framework for Items Catalog Group Assignment. It is explicitly identified in ETRM metadata as the "Summary View" for that functional area, meaning it presents a condensed, denormalized projection of item-to-catalog-group assignments rather than the full transactional structure found in the base tables.

The view is validated and present in both Oracle EBS 12.1.1 and 12.2.2. Its primary role is to expose the attributes required by the ERES signature and audit infrastructure, which captures the identity of items, their catalog group associations, and descriptive context at the time an electronic signature is applied to a catalog group assignment change. Because ERES functionality depends on presenting readable, key-flexfield-resolved values to the user during the signing process, the view resolves internal IDs into user-facing concatenated segment strings, sparing the ERES framework from performing that resolution itself.

Underlying Base Objects

The view is defined over three documented base objects:

  • MTL_SYSTEM_ITEMS_TL (SYNONYM) — the translation table holding language-specific item descriptions. The view joins on INVENTORY_ITEM_ID and ORGANIZATION_ID, and filters on T.LANGUAGE = USERENV('LANG') so that descriptions are returned in the session's current language.
  • MTL_SYSTEM_ITEMS_B_KFV (VIEW) — the key flexfield view over MTL_SYSTEM_ITEMS_B, which resolves the item key flexfield into its CONCATENATED_SEGMENTS representation. This supplies the human-readable item identifier and the ITEM_CATALOG_GROUP_ID foreign key.
  • MTL_ITEM_CATALOG_GROUPS_KFV (SYNONYM) — the key flexfield view over the catalog group definitions, supplying the concatenated catalog group segment string. The join to this object uses an outer join (+) on ITEM_CATALOG_GROUP_ID, so items without a catalog group assignment are still returned.

The use of DISTINCT in the view text indicates the developer anticipated possible row duplication arising from the joins, particularly across the flexfield views, and deliberately deduplicated the result set.

Key Columns

The view exposes four documented columns:

  • ITEM_CATALOG_GROUP — the concatenated key flexfield segments of the catalog group, sourced from MTL_ITEM_CATALOG_GROUPS_KFV.CONCATENATED_SEGMENTS. Nullable due to the outer join.
  • ITEM — the concatenated key flexfield segments of the inventory item, sourced from MTL_SYSTEM_ITEMS_B_KFV.CONCATENATED_SEGMENTS. This is the user-facing item identifier.
  • ITEM_DESCRIPTION — the language-appropriate description drawn from MTL_SYSTEM_ITEMS_TL.DESCRIPTION.
  • INVENTORY_ITEM_ID — the internal surrogate key of the item in MTL_SYSTEM_ITEMS_B, retained so downstream ERES processing can reference the item unambiguously regardless of flexfield display.

Common Use Cases and Queries

Typical use is diagnostic or reporting-oriented: confirming which catalog groups are associated with items, verifying that ERES summary data resolves correctly for a given operating unit, or driving custom ERES-related reports. A representative query is:

  • SELECT item, item_catalog_group, item_description, inventory_item_id FROM mtl_system_items_er3_v WHERE item_catalog_group = :catalog_group;
  • SELECT item, item_catalog_group FROM mtl_system_items_er3_v WHERE inventory_item_id = :item_id;

Because it is a summary view, it should not be treated as a substitute for MTL_SYSTEM_ITEMS_B or MTL_SYSTEM_ITEMS_TL in transactional or high-volume reporting. It is best consumed as the ERES framework intends: as a concise, signature-context projection of item catalog group assignments.