Search Results relation_rule_id




Overview
Explore in AI Chat

Overview

The IBE_CT_RELATED_ITEMS table is a core data repository within the Oracle E-Business Suite (EBS) 12.1.1/12.2.2, specifically for the iStore (IBE) module. It functions as a performance-optimized cache, storing pre-evaluated product relationships generated from configurable mapping rules. Instead of dynamically executing relationship logic at runtime, applications query this table directly to retrieve related product information, significantly enhancing the responsiveness of storefront catalogs, cross-sell/up-sell displays, and product comparison features. Its primary role is to materialize the results from the rule definitions stored in IBE_CT_RELATION_RULES for efficient data access.

Key Information Stored

Each row defines a directional relationship between two inventory items. The table's structure captures the source product, target product, relationship context, and the originating rule. Key columns include:

The primary key (IBE_CT_RELATED_ITEMS_PK) is a composite of INVENTORY_ITEM_ID, RELATED_ITEM_ID, RELATION_TYPE_CODE, and ORGANIZATION_ID, ensuring uniqueness for a given relationship type and rule context.

Common Use Cases and Queries

This table is central to dynamic product merchandising within iStore. A primary use case is retrieving all related products of a specific type for display on a product detail page. For example, to find all cross-sell items for a given product, applications would execute a query filtering on RELATION_TYPE_CODE. The presence of the RELATION_RULE_ID column is essential for debugging and auditing, allowing administrators to identify which specific business rule created a relationship. A typical query pattern involves joining with MTL_SYSTEM_ITEMS_B to fetch item details:

  • Sample Query: SELECT rel.related_item_id, msib.segment1 item_number FROM ibe_ct_related_items rel, mtl_system_items_b msib WHERE rel.inventory_item_id = :p_item_id AND rel.organization_id = :p_org_id AND rel.relation_type_code = 'CROSS_SELL' AND rel.related_item_id = msib.inventory_item_id AND rel.organization_id = msib.organization_id;

It is important to note that relationships based on dynamic SQL rules cannot be pre-evaluated into this table, as their bind variable values change per request.

Related Objects

The IBE_CT_RELATED_ITEMS table is integral to a defined schema, with documented foreign key relationships ensuring data integrity:

  • IBE_CT_RELATION_RULES: The source rule definitions. The RELATION_RULE_ID column in IBE_CT_RELATED_ITEMS references this parent table.
  • MTL_SYSTEM_ITEMS_B: The master item table. Two foreign keys exist: one from INVENTORY_ITEM_ID and another from RELATED_ITEM_ID (both combined with ORGANIZATION_ID) to MTL_SYSTEM_ITEMS_B, validating that both ends of the relationship are valid inventory items.
  • FND_LOOKUPS: The RELATION_TYPE_CODE column's domain values are defined in this table under the lookup type 'IBE_RELATIONSHIP_TYPES'.

The supporting indexes, particularly IBE_CT_RELATED_ITEMS_N1 on RELATION_RULE_ID, optimize queries that filter or join based on the originating rule.