Search Results ibe_relationship_types




Overview
Explore in AI Chat

Overview

The IBE_CT_REL_EXCLUSIONS table is a core data object within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically for the Oracle iStore (IBE) module. It functions as a control table for managing product relationship exclusions. Its primary role is to store specific product-to-product relationships that must be excluded from display or consideration, even if they have been dynamically generated by the system's mapping rules. The table operates in conjunction with IBE_CT_RELATED_ITEMS, which contains pre-evaluated relationships, and IBE_CT_RELATION_RULES, which stores the rules for generating those relationships. By defining exclusions, administrators can fine-tune the product catalog experience, ensuring that only relevant cross-sell, up-sell, accessory, or substitute relationships are presented to users.

Key Information Stored

The table's structure is designed to uniquely identify a relationship to be excluded. The mandatory columns form a composite primary key and define the relationship's scope and participants. The ORGANIZATION_ID specifies the inventory organization. The RELATION_TYPE_CODE defines the category of the relationship (e.g., CROSS_SELL, UP_SELL), with valid values sourced from the FND_LOOKUPS view using the lookup type 'IBE_RELATIONSHIP_TYPES'. The INVENTORY_ITEM_ID and RELATED_ITEM_ID are foreign keys to MTL_SYSTEM_ITEMS_B, identifying the primary item and the related item to be excluded, respectively. Standard EBS "Who" columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) track audit history, while OBJECT_VERSION_NUMBER enables optimistic locking for concurrent data access.

Common Use Cases and Queries

A primary use case is the administrative override of system-generated product relationships. For instance, if a rule suggests "Laptop Bag" as an accessory for all "Laptop" items, but a specific model includes a bag, an exclusion record can prevent the redundant suggestion. Common reporting involves identifying all exclusions for a given relationship type or item. A typical query to audit exclusions for cross-sell relationships in a specific organization would be:

SELECT msil.segment1 item_number,
       msil.description item_description,
       rel_msil.segment1 excluded_related_item,
       rel_msil.description excluded_related_item_desc,
       excl.relation_type_code,
       excl.last_update_date
FROM ibe.ibe_ct_rel_exclusions excl,
     mtl_system_items_b msil,
     mtl_system_items_b rel_msil
WHERE excl.organization_id = :p_org_id
  AND excl.relation_type_code = 'CROSS_SELL'
  AND excl.inventory_item_id = msil.inventory_item_id
  AND excl.organization_id = msil.organization_id
  AND excl.related_item_id = rel_msil.inventory_item_id
  AND excl.organization_id = rel_msil.organization_id
ORDER BY msil.segment1;

Data maintenance is typically performed via the iStore administrator user interface, which manages relationship rules and exclusions holistically.

Related Objects

  • Primary Key: IBE_CT_REL_EXCLUSIONS_PK (ORGANIZATION_ID, RELATION_TYPE_CODE, INVENTORY_ITEM_ID, RELATED_ITEM_ID).
  • Foreign Key Dependencies (References):
    • MTL_SYSTEM_ITEMS_B: The INVENTORY_ITEM_ID column references MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID to validate the primary item.
    • MTL_SYSTEM_ITEMS_B: The RELATED_ITEM_ID column references MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID to validate the related item to be excluded.
  • Functional Dependencies:
    • IBE_CT_RELATED_ITEMS: Contains the pool of pre-evaluated relationships from which this table specifies exclusions.
    • IBE_CT_RELATION_RULES: Holds the mapping rules that generate the relationships potentially managed by exclusions.
    • FND_LOOKUPS: The RELATION_TYPE_CODE column derives its valid values from this view where LOOKUP_TYPE = 'IBE_RELATIONSHIP_TYPES'.