Search Results ibe_ct_relation_rules_pk

Overview

The IBE_CT_RELATION_RULES table is a core repository within the Oracle E-Business Suite iStore (IBE) module, specifically for versions 12.1.1 and 12.2.2. It serves as the master definition table for rules that govern product relationships within the e-commerce storefront. These rules are central to implementing dynamic merchandising features, such as cross-selling, up-selling, accessory recommendations, and product bundles. The table stores both mapping-based rules (direct item-to-item or category-to-category links) and SQL-based rules (complex, query-driven relationships), providing a flexible framework for defining how products are logically associated in the catalog.

Key Information Stored

The table's primary key, RELATION_RULE_ID, uniquely identifies each relationship rule. Critical columns include ORIGIN_OBJECT_ID and DEST_OBJECT_ID, which store the identifiers for the source and target entities of the relationship. These IDs are polymorphic, referencing either items, categories, or storefront sections, as indicated by their foreign key relationships. Other significant columns typically include RULE_TYPE to distinguish between mapping and SQL rules, a STATUS flag to control active/inactive rules, and attributes defining the rule's context, display order, and relationship type (e.g., "Accessory," "Cross-Sell"). The rule's definition, such as a WHERE clause for SQL rules, is also stored within the table structure.

Common Use Cases and Queries

A primary use case is generating real-time product recommendations on product detail pages. The iStore application queries this table to find all active rules where a viewed product is the ORIGIN_OBJECT_ID, then retrieves the related DEST_OBJECT_ID items for display. Administrators use this table to configure and manage merchandising campaigns. Common reporting queries analyze rule effectiveness or audit configurations.

• Finding all active cross-sell rules for a specific product: SELECT * FROM ibe_ct_relation_rules WHERE origin_object_id = :p_item_id AND rule_type = 'CROSS_SELL' AND status = 'ACTIVE';
• Listing all SQL-based relationship rules: SELECT relation_rule_id, rule_name FROM ibe_ct_relation_rules WHERE rule_type = 'SQL' ORDER BY creation_date;
• Identifying rules sourcing from a specific category: SELECT rule_name, dest_object_id FROM ibe_ct_relation_rules WHERE origin_object_id = :p_category_id;

Related Objects

The IBE_CT_RELATION_RULES table is central to the iStore product relationship architecture, with documented foreign key relationships as follows:

• Primary Key: IBE_CT_RELATION_RULES_PK on column RELATION_RULE_ID.
• Foreign Keys (This Table References):
  • IBE_CT_RELATION_RULES.ORIGIN_OBJECT_ID references IBE_DSP_SECTIONS_B (Storefront Sections).
  • IBE_CT_RELATION_RULES.DEST_OBJECT_ID references IBE_DSP_SECTIONS_B (Storefront Sections).
  • IBE_CT_RELATION_RULES.ORIGIN_OBJECT_ID references MTL_CATEGORIES_B (Inventory Categories).
  • IBE_CT_RELATION_RULES.DEST_OBJECT_ID references MTL_CATEGORIES_B (Inventory Categories).
• Foreign Key (Table Referencing This):
  • IBE_CT_RELATED_ITEMS.RELATION_RULE_ID references IBE_CT_RELATION_RULES.RELATION_RULE_ID. This child table stores the specific item instances generated or mapped by a parent rule.