Search Results mtl_categories_kfv




Overview
Explore in AI Chat

Overview

APPS.ICX_RELATED_CATEGORIES_V is a read-only reporting and integration view in the Oracle E-Business Suite APPS schema. It presents the relationships defined between item categories, resolving the internal numeric identifiers of both sides of each relationship into their user-facing concatenated segment strings and descriptions. The view is built on the category relationship table ICX_RELATED_CATEGORIES, which stores pairings of a category to a related category along with the nature of the association, and joins that table twice to the key flexfield view MTL_CATEGORIES_KFV so that both the primary and the related category are displayed in their flexfield form. This view is relevant to Oracle EBS 12.1.1 and 12.2.2 and is typically consumed by reporting tools, integrations, and custom extensions that need human-readable category relationship data without writing the underlying joins. Because the join is against the KFV (key flexfield) view rather than the base table, callers automatically inherit the concatenated segment display and any flexfield-related derivation handled by MTL_CATEGORIES_KFV.

Underlying Base Objects

The view is documented as being defined over the following referenced base objects:

  • ICX_RELATED_CATEGORIES (SYNONYM) — the driving table holding category relationships, including the category set, the two category identifiers, and the relationship type.
  • MTL_CATEGORIES_KFV (SYNONYM) — the key flexfield view over the item category flexfield, providing concatenated segments and descriptions per category identifier; referenced twice, once for each side of the relationship.

The view text confirms this structure: MTL_CATEGORIES_KFV is aliased as MC and again as MC2, joined to ICX_RELATED_CATEGORIES (IRC) on MC.CATEGORY_ID = IRC.CATEGORY_ID and MC2.CATEGORY_ID = IRC.RELATED_CATEGORY_ID. The relationship between the view and its bases is therefore a two-way self-join of the flexfield view across the relationship table, with no additional filters or aggregations applied.

Key Columns

  • CATEGORY_SET_ID — identifier of the category set to which the relationship belongs.
  • CATEGORY_ID — internal identifier of the primary category.
  • CONCATENATED_SEGMENTS (from MC) — the flexfield concatenated segment string of the primary category, i.e., its displayed category name.
  • DESCRIPTION (from MC) — description of the primary category.
  • RELATED_CATEGORY_ID — internal identifier of the related category.
  • CONCATENATED_SEGMENTS (from MC2) — the flexfield concatenated segment string of the related category.
  • DESCRIPTION (from MC2) — description of the related category.
  • RELATIONSHIP_TYPE — the type or nature of the relationship between the two categories, obtained from ICX_RELATED_CATEGORIES.

Note that both CONCATENATED_SEGMENTS and DESCRIPTION appear twice in the projection; consumers must qualify them by position or use aliasing when selecting.

Common Use Cases and Queries

Typical uses include reporting on configured category relationships, exporting category hierarchies for search or personalization features, and validating data prior to migration. Because the view resolves both category identifiers to flexfield strings, it is convenient wherever a category ID would otherwise require a separate lookup to MTL_CATEGORIES_KFV.

Sample query retrieving all readable relationships:

SELECT category_set_id,
       concatenated_segments AS primary_category,
       related_category_id,
       relationship_type
FROM   apps.icx_related_categories_v;

Sample query filtering to a specific category set and category:

SELECT v.concatenated_segments AS primary_category,
       v.relationship_type
FROM   apps.icx_related_categories_v v
WHERE  v.category_set_id = :category_set_id
AND    v.category_id     = :category_id;

Because the view is defined solely with inner joins, only categories that exist in MTL_CATEGORIES_KFV on both sides of the relationship are returned; orphaned relationship rows are excluded.