Search Results jtf_rs_groups_denorm




Overview
Explore in AI Chat

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:

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.