Search Results jtf_rs_groups_denorm_n2

Overview

JTF.JTF_RS_GROUPS_DENORM is a denormalized resource-management table within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 JTF schema. It stores the flattened hierarchical relationship between groups and their ancestors, capturing the parent group identifier together with a flag indicating whether the stored parent is the immediate parent. This structure allows applications to resolve group hierarchies in a single lookup rather than recursively traversing the base group table, which is important for territory, resource, and assignment processing where group membership must be resolved quickly and repeatedly.

From a Data Vault modeling perspective, the mined foreign-key structure places this object as a link — it records relationships between group entities (group and parent group) rather than acting as a standalone hub or a descriptive satellite. It holds its own surrogate key and descriptive flags while resolving many-to-many hierarchical associations between groups. The table is owned by JTF, carries FND Design Data designation JTF.JTF_RS_GROUPS_DENORM, has a status of VALID, and resides in the APPS_TS_TX_DATA tablespace with PCT Free of 10.

Key Information Stored

Among the 33 documented columns, the following are the most significant:

• DENORM_GRP_ID — Surrogate primary key, defined by unique index JTF_RS_GROUPS_DENORM_U1. It uniquely identifies each denormalized hierarchy row. The metadata presents this unique index as the business-key candidate documented for the object.
• GROUP_ID — Identifier of the group for which an ancestor relationship is being recorded. Foreign key to JTF_RS_GROUPS_B.
• PARENT_GROUP_ID — Identifier of the related parent group. Foreign key to JTF_RS_GROUPS_B.
• IMMEDIATE_PARENT_FLAG — Indicates whether PARENT_GROUP_ID represents the immediate parent of the group or a higher-level ancestor.
• DENORM_LEVEL — Numeric depth of the ancestor relative to the group, supporting level-based queries and reporting.
• ACTUAL_PARENT_ID — The direct parent reference used when the stored relationship is indirect.
• START_DATE_ACTIVE and END_DATE_ACTIVE — Effective dating that governs when the relationship is active.
• ACTIVE_FLAG — Denotes whether the denormalized hierarchy row is currently active.
• LATEST_RELATIONSHIP_FLAG — Marks the most recent version of a relationship where history is retained.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, enforcing multi-tenant data security.
• Standard WHO columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN.
• OBJECT_VERSION_NUMBER — Optimistic locking control column.
• ATTRIBUTE1–ATTRIBUTE15 and ATTRIBUTE_CATEGORY — Descriptive flexfield segments for customer extension.

Common Use Cases and Queries

The table is typically used to report group hierarchies, resolve ancestor paths, and drive resource or territory assignments without recursive queries. A common pattern retrieves all ancestor relationships for a group by joining to the base group table:

• Query active parent groups: SELECT d.GROUP_ID, d.PARENT_GROUP_ID, d.DENORM_LEVEL FROM JTF.JTF_RS_GROUPS_DENORM d WHERE d.ACTIVE_FLAG = 'Y' AND SYSDATE BETWEEN d.START_DATE_ACTIVE AND NVL(d.END_DATE_ACTIVE, SYSDATE+1);
• Locate the immediate parent: filter on IMMEDIATE_PARENT_FLAG = 'Y' and GROUP_ID.
• Latest-only reporting: restrict on LATEST_RELATIONSHIP_FLAG to avoid historical duplicates.
• Security-scoped reporting: join SECURITY_GROUP_ID to FND_SECURITY_GROUPS for org-specific visibility.

Index JTF_RS_GROUPS_DENORM_N2 (PARENT_GROUP_ID, IMMEDIATE_PARENT_FLAG, DENORM_LEVEL, START/END_DATE_ACTIVE, GROUP_ID) and N3 (GROUP_ID, PARENT_GROUP_ID, dates) make these traversals efficient, while N5 supports ACTIVE_FLAG filtering and N6 supports latest-version queries.

Related Objects

• JTF.JTF_RS_GROUPS_B — Base group table; referenced by both GROUP_ID and PARENT_GROUP_ID foreign keys.
• FND_SECURITY_GROUPS — Referenced by SECURITY_GROUP_ID, controlling data access by security group.
• FND_USER — Source for CREATED_BY and LAST_UPDATED_BY WHO columns.
• FND_LOGINS — Source for LAST_UPDATE_LOGIN.
• JTF_RS_GROUPS_DENORM_U1 / N2–N6 — Associated indexes supporting primary-key lookups and hierarchy joins.
• JTF_RS_GROUPS_DENORM_PK — Primary key constraint on DENORM_GRP_ID.

Together these objects define the group hierarchy resolution layer used across JTF resource management and related assignment functions.