Search Results jtf_rs_groups_denorm_pk

Overview

JTF_RS_GROUPS_DENORM is a denormalized table owned by the JTF schema (CRM Foundation) in Oracle EBS 12.1.1 and 12.2.2. It stores the flattened hierarchy relationship between a resource group and its parents, eliminating the need for recursive traversal of the base group table when resolving group ancestry. Each row captures a group-to-parent association together with attributes describing whether the parent is the immediate parent, the depth of the relationship, and the currency of the association.

Under the heuristic Data Vault classification mined from its foreign key structure, this object is best modeled as a link. It resolves a many-to-many relationship between group records in JTF_RS_GROUPS_B, one side acting as the child group and the other as the parent group, with descriptive attributes layered on top.

The table is populated and maintained by CRM Foundation group management logic rather than by direct user maintenance, and it exists purely to accelerate hierarchical lookups and reporting.

Key Information Stored

The table contains 33 documented columns. The most significant are:

• DENORM_GRP_ID — surrogate primary key, enforced by JTF_RS_GROUPS_DENORM_PK and backed by unique index JTF_RS_GROUPS_DENORM_U1. This is the only documented business-key candidate.
• GROUP_ID — the child group in the relationship; foreign key to JTF_RS_GROUPS_B.
• PARENT_GROUP_ID — the ancestor group; also a foreign key to JTF_RS_GROUPS_B.
• ACTUAL_PARENT_ID — identifies the true immediate parent when the row represents an indirect ancestor.
• IMMEDIATE_PARENT_FLAG — indicates whether the parent in this row is the direct parent of GROUP_ID.
• DENORM_LEVEL — the depth of the ancestor relative to the child group, supporting level-based reporting.
• LATEST_RELATIONSHIP_FLAG and ACTIVE_FLAG — mark whether the relationship is current and active.
• START_DATE_ACTIVE / END_DATE_ACTIVE — effective dating for the hierarchy relationship.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing multi-tenant data isolation.
• OBJECT_VERSION_NUMBER — optimistic locking for concurrent updates.
• Standard WHO columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the extensibility (flexfield) columns.

Common Use Cases and Queries

The primary use case is resolving group hierarchies without recursive SQL. Typical queries retrieve all ancestors or descendants of a given group:

• Retrieve the direct parent of a child group: SELECT PARENT_GROUP_ID FROM JTF_RS_GROUPS_DENORM WHERE GROUP_ID = :p_group AND IMMEDIATE_PARENT_FLAG = 'Y'.
• Retrieve all ancestors with depth: SELECT PARENT_GROUP_ID, DENORM_LEVEL FROM JTF_RS_GROUPS_DENORM WHERE GROUP_ID = :p_group ORDER BY DENORM_LEVEL.
• Filter to current relationships using LATEST_RELATIONSHIP_FLAG = 'Y' and ACTIVE_FLAG = 'Y'.
• Effective-dated reporting using START_DATE_ACTIVE and END_DATE_ACTIVE windows.
• Join to JTF_RS_GROUPS_B twice (child alias and parent alias) to render group names in hierarchical reports.

Because the table is denormalized, it is well suited to BI Publisher and OBIEE extracts where iterative parent lookups are impractical.

Related Objects

• JTF_RS_GROUPS_B — base group table; referenced via GROUP_ID and PARENT_GROUP_ID. The two foreign keys make it the central partner of this link.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID, controlling row-level visibility.
• JTF_RS_GROUPS_TL — translated group names, joined to JTF_RS_GROUPS_B for reporting.
• JTF_RS_GROUP_MEMBERS — resource-to-group assignments; combined with this table to resolve effective group membership through hierarchy.
• JTF_RS_RESOURCE_EXTNS — resources assigned to groups, frequently analyzed alongside group ancestry.
• JTF_RS_GROUPS_VL — the multilingual view over the group base and translation tables.
• CRM Foundation group management APIs — the PL/SQL interfaces that maintain both JTF_RS_GROUPS_B and this denormalized hierarchy.