Search Results ibe_ct_related_items




Overview
Explore in AI Chat

Overview

The IBE_CT_RELATED_ITEMS table is a core data object within the Oracle E-Business Suite iStore (IBE) module, serving as a performance-optimized repository for precomputed product relationships. Its primary role is to store the results of mapping relationship rules, which define associations between catalog items such as cross-sells, up-sells, accessories, or substitutes. By precomputing and storing these relationships, the table enables the iStore application to rapidly display related products on storefront pages without the performance overhead of dynamically evaluating complex business rules during each user session. This design is critical for delivering a responsive customer experience in high-volume e-commerce environments across both EBS releases 12.1.1 and 12.2.2.

Key Information Stored

The table's structure is designed to uniquely identify a relationship between two specific items within an organizational context. Its primary key is a composite of four columns: INVENTORY_ITEM_ID, RELATED_ITEM_ID, RELATION_TYPE_CODE, and ORGANIZATION_ID. The INVENTORY_ITEM_ID and RELATED_ITEM_ID columns hold the identifiers for the source and target items in the relationship, respectively. The RELATION_TYPE_CODE defines the nature of the association (e.g., 'CROSS_SELL', 'UP_SELL'). The ORGANIZATION_ID provides inventory master organization context, ensuring relationships are scoped correctly. A critical foreign key column, RELATION_RULE_ID, links the specific stored relationship back to its originating business rule defined in the IBE_CT_RELATION_RULES table, providing an audit trail from rule to result.

Common Use Cases and Queries

A primary use case is powering the "Customers Who Bought This Item Also Bought" or "Recommended Accessories" sections on an iStore product detail page. Application logic queries this table to retrieve all RELATED_ITEM_ID values for a given INVENTORY_ITEM_ID, ORGANIZATION_ID, and desired RELATION_TYPE_CODE. For reporting and analytics, administrators may query the table to analyze the volume and distribution of relationship types or validate rule execution. A common SQL pattern involves joining to MTL_SYSTEM_ITEMS_B to retrieve item details:

  • SELECT ri.*, msi1.segment1 source_item, msi2.segment1 related_item FROM ibe_ct_related_items ri, mtl_system_items_b msi1, mtl_system_items_b msi2 WHERE ri.inventory_item_id = msi1.inventory_item_id AND ri.related_item_id = msi2.inventory_item_id AND ri.organization_id = msi1.organization_id AND ri.organization_id = msi2.organization_id AND ri.inventory_item_id = :p_item_id AND ri.relation_type_code = :p_rel_type;

Related Objects

The IBE_CT_RELATED_ITEMS table has defined dependencies on several key inventory and iStore configuration tables, as documented in its foreign key constraints. It maintains referential integrity with the inventory master table through two separate foreign key relationships to MTL_SYSTEM_ITEMS_B. The first links the INVENTORY_ITEM_ID and ORGANIZATION_ID columns to validate the source item. The second links the RELATED_ITEM_ID and ORGANIZATION_ID columns to validate the target related item. Furthermore, it depends on the IBE_CT_RELATION_RULES table via the RELATION_RULE_ID foreign key, ensuring every stored relationship can be traced to a defined business rule. The table's primary key constraint is named IBE_CT_RELATED_ITEMS_PK.