Results for “jtf_rs_resource_denorm”

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

Overview

JTF_RS_RESOURCE_DENORM is a denormalized table owned by the JTF (CRM Foundation) schema in Oracle E-Business Suite, valid in both release 12.1.1 and 12.2.2. It stores hierarchical information about resources — the people, groups, and organizations modeled as resources in the CRM Foundation layer — in a pre-flattened form. Rather than resolving parent-child relationships through repeated self-joins against JTF_RS_RESOURCE_EXTNS, the denormalized table carries the ancestor path, level, and rollup counts for each resource, enabling fast hierarchical reporting, territory and resource-group expansion, and organizational roll-up queries across CRM modules such as Sales, Service, and Territories.

From a Data Vault modeling perspective, the mined FK structure classifies this object heuristically as a link. Each row associates a resource with its parent resource, representing the recursive relationship within the resource hierarchy. A Data Vault practitioner would therefore treat the table as a link between hub-style resource entities rather than as a standalone hub or a pure descriptive satellite.

Key Information Stored

The primary key is the surrogate DENORM_RESOURCE_ID, enforced by JTF_RS_RESOURCE_DENORM_PK and uniquely indexed by JTF_RS_RESOURCE_DENORM_U1. This column is an internal row identifier and carries no business meaning; it is the only documented unique-index column, so it is also the sole documented business-key candidate.

• RESOURCE_ID — the resource this row describes; foreign key to JTF_RS_RESOURCE_EXTNS.
• PARENT_RESOURCE_ID — the immediate parent resource; foreign key to JTF_RS_RESOURCE_EXTNS, forming the recursive hierarchy edge.
• CATEGORY — classifies the hierarchy or resource grouping to which the row belongs.
• LEVEL_FROM_ROOT — depth of the resource beneath the hierarchy root, enabling level-based filtering without recursion.
• TOTAL_COUNT — total number of resources rolled up beneath the node.
• DIRECTS_COUNT — count of immediate children only.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting multi-org / security-group scoping.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns.

Common Use Cases and Queries

Typical usage includes resource roll-up reporting, manager-to-subordinate navigation, and exposing hierarchy depth in CRM list pages. Because LEVEL_FROM_ROOT and the count columns are precomputed, reports avoid costly CONNECT BY recursion.

• Retrieve direct reports for a manager: SELECT RESOURCE_ID FROM JTF_RS_RESOURCE_DENORM WHERE PARENT_RESOURCE_ID = :resource_id.
• Count all descendants beneath a node: SELECT TOTAL_COUNT FROM JTF_RS_RESOURCE_DENORM WHERE RESOURCE_ID = :resource_id.
• Filter by hierarchy depth: SELECT RESOURCE_ID FROM JTF_RS_RESOURCE_DENORM WHERE LEVEL_FROM_ROOT <= :max_depth.
• Join to descriptive attributes: SELECT d.RESOURCE_ID, e.* FROM JTF_RS_RESOURCE_DENORM d, JTF_RS_RESOURCE_EXTNS e WHERE d.RESOURCE_ID = e.RESOURCE_ID.
• Scope by security group: WHERE SECURITY_GROUP_ID = :org_id.

Related Objects

The table is tightly coupled to the resource master and security infrastructure:

• JTF_RS_RESOURCE_EXTNS — referenced by both RESOURCE_ID and PARENT_RESOURCE_ID; holds the descriptive resource attributes.
• FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID for security scoping.
• JTF_RS_GROUPS / JTF_RS_GROUP_MEMBERS — resource group definitions commonly joined for group roll-ups.
• JTF_RS_TEAMS / JTF_RS_TEAM_MEMBERS — team membership consumed alongside hierarchy data.
• JTF_RS_RESOURCE_DENORM_PK / _U1 — the primary key constraint and unique index enforcing row identity.