Results for “sql_statement”

2 results




Overview
Explore in AI Chat

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IBE_CT_RELATION_RULES is a table owned by the IBE (iStore) schema in Oracle E-Business Suite, documented as VALID in both 12.1.1 and 12.2.2. It stores the mapping or SQL rules that define product relationships within iStore merchandising. When a store displays related items, cross-sells, up-sells, or accessories, the logic determining which items are related to an origin item is governed by a row in this table. Each rule defines an origin object, a destination object, and the relationship type between them, and may additionally supply a SQL statement that resolves the relationship dynamically at runtime rather than through static mapping.

The table therefore occupies an important position in the iStore content and merchandising model, sitting between the display section and category metadata on one side and the resolved related-items data on the other. The ETRM metadata classifies this object under a heuristic Data Vault classification as satellite-leaning. As a modeling suggestion, this implies the table behaves primarily as a descriptive satellite attached to a business key defined by the combination of origin and destination objects and the relation type, rather than acting as a central hub or an associative link in its own right.

Key Information Stored

The table contains 31 documented columns. The surrogate primary key is RELATION_RULE_ID, enforced by the unique index IBE_CT_RELATION_RULES_PK, which also serves as the single-column unique index IBE_CT_RELATION_RULES_U1. The business-key candidate is defined by the unique index IBE_CT_RELATION_RULES_U2, comprising ORIGIN_OBJECT_TYPE, DEST_OBJECT_TYPE, ORIGIN_OBJECT_ID, DEST_OBJECT_ID, and RELATION_TYPE_CODE. This composite uniquely identifies a rule independently of the surrogate identifier.

  • RELATION_RULE_ID — surrogate primary key; the column referenced by IBE_CT_RELATED_ITEMS.
  • RELATION_TYPE_CODE — identifies the nature of the relationship (for example related-to, cross-sell, or accessory).
  • ORIGIN_OBJECT_TYPE / DEST_OBJECT_TYPE — discriminate whether the origin and destination objects are display sections or categories.
  • ORIGIN_OBJECT_ID — the source object, referencing either IBE_DSP_SECTIONS_B or MTL_CATEGORIES_B.
  • DEST_OBJECT_ID — the target object, referencing either IBE_DSP_SECTIONS_B or MTL_CATEGORIES_B.
  • SQL_STATEMENT — an optional SQL rule that resolves product relationships dynamically.
  • DESCRIPTION — free-text description of the rule's purpose.
  • SECURITY_GROUP_ID — references FND_SECURITY_GROUPS, supporting multi-organization or security-group partitioning.
  • OBJECT_VERSION_NUMBER — optimistic locking control for concurrent updates.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard 15-column descriptive flexfield segment set.

Common Use Cases and Queries

The most common reporting scenario is enumerating the rules that drive related-item displays for a given category or section. A representative query joins the origin and destination identifiers to their respective lookup tables:

  • Listing all rules of a given RELATION_TYPE_CODE with their origin and destination object identifiers.
  • Identifying rules that use dynamic SQL by filtering on SQL_STATEMENT IS NOT NULL.
  • Auditing rule maintenance by ordering on LAST_UPDATE_DATE and joining to FND_USER via LAST_UPDATED_BY.
  • Tracing which rules produced a given related item through a join to IBE_CT_RELATED_ITEMS.
  • Isolating rules scoped to a particular SECURITY_GROUP_ID.

A sample pattern follows: select r.relation_rule_id, r.relation_type_code, r.origin_object_id, r.dest_object_id from ibe_ct_relation_rules r where r.relation_type_code = :type order by r.relation_rule_id.

Related Objects

The table participates in several documented foreign key relationships that define both its parents and its dependents:

  • IBE_CT_RELATED_ITEMS — references IBE_CT_RELATION_RULES via RELATION_RULE_ID; this is the primary dependent table, storing resolved related items generated from each rule.
  • IBE_DSP_SECTIONS_B — referenced through ORIGIN_OBJECT_ID and DEST_OBJECT_ID when the object type indicates a display section.
  • MTL_CATEGORIES_B — referenced through ORIGIN_OBJECT_ID and DEST_OBJECT_ID when the object type indicates a category.
  • FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID for security partitioning.

Together these relationships position IBE_CT_RELATION_RULES as the rule-definition layer feeding the related-items layer, with its parent objects drawn from iStore display configuration and Oracle Inventory category definitions.