Search Results mtl_categories_kfv




Overview
Explore in AI Chat

Overview

ICX_RELATED_CATEGORIES_V is a view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the ICX (Oracle iProcurement) product family. It is documented in ETRM as the "Related Categories View" and carries a VALID status in both 12.1.1 and 12.2.2. The view exposes the associations between purchasing categories, allowing consumers to resolve a base category and its related categories into human-readable concatenated segment values and descriptions rather than raw numeric identifiers.

In Oracle EBS reporting and integration, this view serves as a denormalized read layer over the intersection table ICX_RELATED_CATEGORIES. Rather than joining the intersection table to the category key flexfield view three times, developers and report authors query ICX_RELATED_CATEGORIES_V to retrieve both sides of a category relationship in a single pass. iProcurement uses related category relationships to broaden search results, suggest substitute or complementary items, and support cross-category navigation. The view therefore underpins both the transactional search experience and any downstream analytics that must respect the same category relationships.

Underlying Base Objects

Per the documented 12.2.2 metadata, the view is defined over two referenced base objects, each of which is a SYNONYM within the APPS schema:

  • ICX_RELATED_CATEGORIES — the intersection table storing the actual category-to-related-category pairings, along with the relationship type and the owning category set.
  • MTL_CATEGORIES_KFV — the key flexfield view over MTL_CATEGORIES, which resolves the category ID into its concatenated segment string and description. The view definition references this object twice, once as MC for the primary category and once as MC2 for the related category.

The join condition is MC.CATEGORY_ID = IRC.CATEGORY_ID for the primary side and MC2.CATEGORY_ID = IRC.RELATED_CATEGORY_ID for the related side. The view performs no filtering, so it returns every row present in ICX_RELATED_CATEGORIES for which both category IDs resolve in MTL_CATEGORIES_KFV.

Key Columns

  • CATEGORY_SET_ID — identifier of the category set to which the relationship belongs. Category sets define the flexfield structure and the controlled value set, so this column is essential when restricting a query to a specific purchasing or product category set.
  • CATEGORY_ID — the numeric identifier of the primary category.
  • CATEGORY_NAME — the concatenated segment value of the primary category, sourced from MTL_CATEGORIES_KFV.CONCATENATED_SEGMENTS.
  • CATEGORY_DESCRIPTION — the descriptive text of the primary category.
  • RELATED_CATEGORY_ID — the numeric identifier of the associated category.
  • RELATED_CATEGORY_NAME — the concatenated segment value of the related category.
  • RELATED_CATEGORY_DESCRIPTION — the descriptive text of the related category.
  • RELATIONSHIP_TYPE — the classification of the association as stored on the intersection table, distinguishing the nature of the linkage between the two categories.

Common Use Cases and Queries

The most frequent requirement is to list all relationships for a given category set or a given category, using the resolved names for readability.

SELECT category_id, category_name, related_category_id, related_category_name, relationship_type FROM apps.icx_related_categories_v WHERE category_set_id = :p_category_set_id ORDER BY category_name, related_category_name;

A second scenario resolves a single category by its concatenated name and returns everything related to it:

SELECT related_category_name, related_category_description FROM apps.icx_related_categories_v WHERE category_set_id = :p_set AND category_name = :p_category_name;

Because the view is a simple non-filtered join, it is safe to use in ad hoc reporting, custom concurrent programs, and interface extracts. The main performance consideration is that MTL_CATEGORIES_KFV is referenced twice; queries that do not constrain CATEGORY_SET_ID or CATEGORY_ID may scan the full set of relationships. Adding a predicate on CATEGORY_SET_ID is recommended in all production queries, since category sets scope the flexfield structure and prevent cross-set ambiguity.