Results for “cn_rules_hierarchy_all”

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

Overview

CN_RULES_HIERARCHY_ALL is a transactional table in the Oracle Incentive Compensation (CN) module of Oracle E-Business Suite, owned by the CN schema. It stores the parent/child hierarchy that organizes rules defined in CN_RULES_ALL_B into a structured tree, allowing an incentive compensation plan to express layered, dependent, or nested rule logic rather than a flat rule list. Each row associates a child rule with a parent rule within a given ruleset and operating unit, and the SEQUENCE_NUMBER column establishes the ordering of siblings in the hierarchy.

From a Data Vault modeling perspective, the mined foreign-key structure suggests classifying this object as a link table. It resolves a recursive many-to-many relationship between rule entities, capturing the association between a parent rule and a child rule rather than functioning as an independent hub of business concepts. The table is multi-org enabled through ORG_ID and participates in the standard EBS audit and security column conventions.

Key Information Stored

The 11 documented columns support rule hierarchy definition, ordering, multi-tenancy, and auditability. The most significant are:

• RULE_ID — identifies the child rule in the hierarchy; also the surrogate primary key column via CN_RULES_HIERARCHY_PK.
• PARENT_RULE_ID — identifies the parent rule, enabling recursive traversal of the rule tree.
• SEQUENCE_NUMBER — defines sibling ordering under a common parent; part of the unique business key CN_RULES_HIERARCHY_UK1 (PARENT_RULE_ID, SEQUENCE_NUMBER).
• RULESET_ID — scopes the hierarchy to a particular ruleset.
• ORG_ID — the operating unit for multi-org partitioning.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting row-level security filtering.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY — standard EBS who-columns for audit tracking.

A separate unique index, CN_RULES_HIERARCHY_ALL_U1, covers (RULE_ID, PARENT_RULE_ID) and serves as an additional business-key candidate, guaranteeing that a child rule cannot appear twice under the same parent.

Common Use Cases and Queries

Typical use cases involve reconstructing rule trees for plan validation, reporting on rule nesting depth, and auditing hierarchy changes. A recursive query retrieves the full path from a root rule to its descendants:

• Identify all children of a given parent: SELECT RULE_ID, SEQUENCE_NUMBER FROM CN_RULES_HIERARCHY_ALL WHERE PARENT_RULE_ID = :p_rule AND ORG_ID = :p_org;
• Locate orphans or root rules with no parent: SELECT RULE_ID FROM CN_RULES_HIERARCHY_ALL WHERE PARENT_RULE_ID IS NULL;
• Resolve rule names by joining to the base rule table: SELECT h.RULE_ID, r.RULE_NAME FROM CN_RULES_HIERARCHY_ALL h, CN_RULES_ALL_B r WHERE h.RULE_ID = r.RULE_ID;
• Traverse the tree with CONNECT BY PRIOR RULE_ID = PARENT_RULE_ID to produce an indented hierarchy report.

Reporting scenarios include hierarchy depth analysis, sibling sequence review, and detection of duplicate or conflicting rule placements within a ruleset.

Related Objects

The following objects are most directly associated through documented foreign keys and business relationships:

• CN_RULES_ALL_B — the base rule table; joined on RULE_ID and PARENT_RULE_ID.
• CN_RULES_ALL_TL — translation table for rule names and descriptions.
• CN_RULESETS — defines the ruleset referenced by RULESET_ID.
• FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID for row-level access control.
• FND_ORG_ACCESS / HR_OPERATING_UNITS — used to resolve ORG_ID context.

These relationships make CN_RULES_HIERARCHY_ALL a central structural element linking rule definitions to their organizational and security context.