Results for “jtf_rs_dbi_denorm_res_groups”

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

Overview

The JTF_RS_DBI_DENORM_RES_GROUPS table is a CRM Foundation (JTF) resource-management structure that stores a denormalized, pre-flattened representation of resource group hierarchies and their membership relationships. It resides in the JTF schema alongside the core Resource Manager (RS) tables and exists to support the DBI (Database Interface / Business Intelligence) layer used by Oracle EBS CRM resource reporting and group-resolution features in releases 12.1.1 and 12.2.2.

Resource groups in Oracle Resource Manager are inherently recursive: a group may contain sub-groups, which in turn contain further sub-groups and individual resources. Resolving membership across many levels requires expensive recursive joins. This table materializes that result set so downstream consumers can query flattened group membership directly. The DBI prefix and the DENORM suffix confirm its role as a reporting/summary object rather than a transaction-of-record table.

From a heuristic Data Vault modeling perspective, the metadata classifies this object as standalone. In Data Vault terms the flattened membership rows function closest to a link-and-satellite composite: the pairing of group and resource identifiers records a relationship, while the surrounding flag, status, and audit columns record its state at a point in time. Because there is no declared FK to a resource hub, the standalone classification is a modeling suggestion rather than a strict dependency.

Key Information Stored

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

• DENORM_ID — the surrogate primary key for each denormalized membership row.
• ID / CURRENT_ID — the identifier of the group relationship and its current-version pointer, supporting historical rows.
• PARENT_ID — the parent group relationship, used to express hierarchy.
• DENORM_LEVEL — the depth of the row in the flattened hierarchy.
• START_DATE / END_DATE — the effective-dating window for the membership.
• RESOURCE_ID / GRP_MEM_RESOURCE_ID — the member resource and the group-member resource.
• ID_FOR_GRP_MEM — the identifier linking the row to the group-member assignment.
• MEM_FLAG / MEM_STATUS / MEM_ACTIVE_GRP_REL_ONLY — membership type, status, and an active-relationship-only filter.
• ACTIVE_GRP_REL_ONLY / USAGE / VALUE — usage-scoping and attribute-value columns consumed by reporting.
• SECURITY_GROUP_ID — the security group context, referencing FND_SECURITY_GROUPS.

Business-key candidates are typically the combination of the group relationship identifier, PARENT_ID, RESOURCE_ID, and the effective dates, though only the surrogate DENORM_ID is documented here as a unique identifier.

Common Use Cases and Queries

Typical uses include flattening resource group hierarchies for CRM reporting, resolving indirect resource membership, and driving territory or assignment rules.

SELECT d.denorm_id, d.parent_id, d.resource_id, d.denorm_level
FROM   jtf.jtf_rs_dbi_denorm_res_groups d
WHERE  d.mem_status   = 'ACTIVE'
AND    d.denorm_level <= 3;

Because it is pre-flattened, the table serves as an efficient replacement for recursive CONNECT BY queries against the base RS group tables.

Related Objects

• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID = SECURITY_GROUP_ID to enforce security group scoping.
• JTF_RS_GROUPS_B / JTF_RS_GROUPS_TL — the base group definition and translated name tables.
• JTF_RS_GROUP_MEMBERS — the operational membership table this denormalization summarizes.
• JTF_RS_RESOURCE_EXTNS / PER_ALL_PEOPLE_F — source of resource details for joined reporting.
• JTF_RS_DBI_* — sibling DBI denormalization tables used together in CRM BI extracts.