Search Results icx_related_categories




Overview
Explore in AI Chat

Overview

ICX_RELATED_CATEGORIES is a table in the ICX schema (Oracle iProcurement) used to define category hierarchies and the relationships between purchasing categories in Oracle E-Business Suite 12.1.1 and 12.2.2. It records how one category relates to another within a given category set, enabling iProcurement and related modules to navigate from a parent or source category to a related category. This underpins category browsing, guided purchasing, and category-based content presentation.

From a Data Vault modeling perspective, the mined FK structure suggests a link classification: the table primarily connects two references to MTL_CATEGORIES_B (the base category via CATEGORY_ID and the related category via RELATED_CATEGORY_ID) within a specific MTL_CATEGORY_SETS_B context. The link type is qualified by RELATIONSHIP_TYPE, which distinguishes the kind of association recorded between the two categories.

Key Information Stored

The table is documented with nine columns. The most significant are:

  • RELATED_CATEGORY_ID — identifies the related (target) category; participates in the composite primary key and carries an FK to MTL_CATEGORIES_B.
  • RELATIONSHIP_TYPE — the type of relationship between the categories; a key discriminator and part of the composite primary key.
  • CATEGORY_ID — the source category; an FK to MTL_CATEGORIES_B and part of the composite primary key.
  • CATEGORY_SET_ID — the category set defining the hierarchy context; an FK to MTL_CATEGORY_SETS_B and part of the composite primary key.
  • CREATED_BY, CREATION_DATE — standard WHO/audit columns capturing the creating user and timestamp.
  • LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO/audit columns capturing the last modification user, timestamp, and login.

The surrogate-style primary key is ICX_RELATED_CATEGORIES_PK, composed of (RELATED_CATEGORY_ID, RELATIONSHIP_TYPE, CATEGORY_ID, CATEGORY_SET_ID). A unique business-key candidate is defined by ICX_RELATED_CATEGORIES_U1 over (CATEGORY_SET_ID, CATEGORY_ID, RELATED_CATEGORY_ID, RELATIONSHIP_TYPE), which enforces that a given source/related pair within a set carries a single relationship-type record.

Common Use Cases and Queries

Typical scenarios involve resolving the hierarchy around a category for reporting, browsing, or migration validation.

  • List all categories related to a given source category within a category set:
SELECT r.RELATED_CATEGORY_ID, r.RELATIONSHIP_TYPE
FROM   ICX_RELATED_CATEGORIES r
WHERE  r.CATEGORY_ID = :category_id
AND    r.CATEGORY_SET_ID = :category_set_id;
  • Join to base category details to render names and descriptions:
SELECT c.CATEGORY_NAME, rc.CATEGORY_NAME AS related_name, r.RELATIONSHIP_TYPE
FROM   ICX_RELATED_CATEGORIES r
JOIN   MTL_CATEGORIES_B c  ON c.CATEGORY_ID = r.CATEGORY_ID
JOIN   MTL_CATEGORIES_B rc ON rc.CATEGORY_ID = r.RELATED_CATEGORY_ID
WHERE  r.CATEGORY_SET_ID = :category_set_id;
  • Audit relationship maintenance activity by user and date for change-control reporting.
  • Validate hierarchy integrity before or after a category data migration into iProcurement.

Related Objects

The following objects are most significant, based directly on the documented foreign-key relationships:

  • MTL_CATEGORY_SETS_B — referenced via ICX_RELATED_CATEGORIES.CATEGORY_SET_ID; provides the category set context.
  • MTL_CATEGORIES_B — referenced twice, via CATEGORY_ID (source category) and RELATED_CATEGORY_ID (related category); supplies the category definitions.
  • MTL_CATEGORIES_TL and MTL_CATEGORY_SETS_TL — translation tables commonly joined to resolve names in reports.
  • Availability and content tables in iProcurement that consume these category relationships when presenting catalogs and guided navigation.

Because both category columns resolve to MTL_CATEGORIES_B, any reporting query must alias that table twice to distinguish the source category from the related category.