Search Results cn_rules_hierarchy_all




Overview
Explore in AI Chat

Overview

The CN_RULES_HIERARCHY_ALL table is a core data structure within the Oracle E-Business Suite Incentive Compensation (CN) module. It defines and manages the hierarchical relationships between compensation rules, which are stored in the CN_RULES_ALL_B table. This hierarchy is fundamental for modeling complex compensation plans where rules can be nested, allowing for the creation of parent rules that govern or aggregate the results of child rules. The table supports multi-organization data through its ALL suffix, meaning it stores data partitioned by operating unit (ORG_ID). Its primary role is to enable the calculation engine to correctly traverse and process rule dependencies during compensation plan runs.

Key Information Stored

The table's structure is designed to explicitly map parent-child rule linkages. The most critical columns, as defined by its primary and unique keys, are RULE_ID and PARENT_RULE_ID. The RULE_ID column identifies the child rule within the relationship and is the table's primary key. The PARENT_RULE_ID column holds the identifier of the rule that acts as the parent or container for the child rule. The SEQUENCE_NUMBER column, part of the unique key with PARENT_RULE_ID, dictates the processing or evaluation order of child rules under a common parent. Additional context columns include RULESET_ID, which groups rules into a compensation plan, and ORG_ID for multi-org partitioning.

Common Use Cases and Queries

A primary use case is analyzing the structure of a compensation plan to understand rule dependencies and execution flow. Administrators often query this table to generate a hierarchical report of all rules within a ruleset. A common SQL pattern retrieves the full parent-child tree for a specific ruleset using hierarchical queries (CONNECT BY or recursive CTEs in later database versions). Troubleshooting calculation results frequently involves verifying that a rule is correctly linked to its intended parent. Sample queries include finding all immediate children of a specific parent rule or identifying orphaned rules that exist in CN_RULES_ALL_B but have no corresponding entry in the hierarchy table.

Related Objects

The CN_RULES_HIERARCHY_ALL table has a tightly coupled relationship with the CN_RULES_ALL_B table, which stores the actual rule definitions. This is evidenced by two documented foreign key constraints:

  • The RULE_ID column references CN_RULES_ALL_B (CN_RULES_ALL_B.RULE_ID, CN_RULES_ALL_B.RULESET_ID, CN_RULES_ALL_B.ORG_ID). This ensures every child rule in the hierarchy is a valid, defined rule.
  • The PARENT_RULE_ID column also references CN_RULES_ALL_B with the same composite key. This ensures every parent rule referenced is also a valid, defined rule within the same ruleset and operating unit.
These relationships enforce referential integrity, guaranteeing that the hierarchy only links valid, existing rules. The table is central to the compensation engine's logic and is indirectly referenced by various CN APIs and user interfaces that manage compensation plans.