Results for “denorm_mgr_id”

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

Overview

JTF_RS_REP_MANAGERS is a denormalized table in the JTF (CRM Foundation) schema that stores the reporting hierarchy of group members within Oracle E-Business Suite 12.1.1 and 12.2.2. It is a core component of the Resource Manager infrastructure, which underpins assignment, escalation, and territory management across CRM modules such as Sales, Service, and TeleSales. Rather than traversing a normalized recursive relationship each time a manager chain is needed, the table materializes the reporting lines — including hierarchical level depth — for members of resource groups.

From the ETRM relationship data, the object references JTF_RS_GROUPS_B, JTF_RS_RESOURCE_EXTNS, and FND_SECURITY_GROUPS via foreign keys, and carries a 37-column schema with the surrogate primary key JTF_RS_REP_MANAGERS_PK on DENORM_MGR_ID. Because the primary key is a surrogate and the object links resources to managers within groups, the heuristic Data Vault classification is link — it resolves many-to-many relationships between resources and their managers rather than describing a single business entity as a hub would.

Key Information Stored

The table's most significant columns fall into identity, hierarchy, and audit categories:

• DENORM_MGR_ID — the surrogate primary key (JTF_RS_REP_MANAGERS_PK); the unique index JTF_RS_REP_MANAGERS_U1 also targets this column.
• RESOURCE_ID — the group member (resource) whose reporting line is being stored; FK to JTF_RS_RESOURCE_EXTNS.
• PARENT_RESOURCE_ID — the reported-to resource at the immediate parent level.
• MANAGER_PERSON_ID and PERSON_ID — person-level identifiers for the manager and member, useful when joining to HR/PER tables.
• GROUP_ID — the resource group context for the hierarchy; FK to JTF_RS_GROUPS_B.
• HIERARCHY_TYPE — differentiates the kind of hierarchy modeled (for example, sales or service reporting).
• REPORTS_TO_FLAG — indicates whether the referenced manager is the active reporting target.
• DENORM_LEVEL — the pre-computed depth of the resource in the hierarchy tree, the denormalization that gives the table its name.
• PAR_ROLE_RELATE_ID and CHILD_ROLE_RELATE_ID — role-based relationship identifiers for the parent/child role linkage.
• START_DATE_ACTIVE / END_DATE_ACTIVE — effective dating of each reporting relationship.
• SECURITY_GROUP_ID — multi-tenant/security partitioning; FK to FND_SECURITY_GROUPS.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER — standard EBS concurrent-safe audit and optimistic locking columns; ATTRIBUTE1–15 and ATTRIBUTE_CATEGORY provide the usual flex placeholders.

Common Use Cases and Queries

Typical scenarios include building manager roll-up reports for sales teams, driving assignment rules in territory and lead management, and validating escalation routing. A common pattern retrieves a resource's full manager chain within a group, ordered by DENORM_LEVEL:

• SELECT RESOURCE_ID, PARENT_RESOURCE_ID, MANAGER_PERSON_ID, DENORM_LEVEL FROM JTF_RS_REP_MANAGERS WHERE GROUP_ID = :p_group AND REPORTS_TO_FLAG = 'Y' ORDER BY DENORM_LEVEL;
• Joining to JTF_RS_RESOURCE_EXTNS to resolve resource names, and to JTF_RS_GROUPS_B to filter by group name.
• Filtering on START_DATE_ACTIVE / END_DATE_ACTIVE to obtain point-in-time hierarchy snapshots for backdated reporting.
• Using MANAGER_PERSON_ID for HR-aligned headcount and span-of-control analysis.

Because the table is denormalized, reporting queries avoid recursive CONNECT BY traversal, which improves performance for high-volume CRM dashboards.

Related Objects

• JTF_RS_GROUPS_B — joined on GROUP_ID; defines the resource group context for each hierarchy entry.
• JTF_RS_RESOURCE_EXTNS — joined on RESOURCE_ID; the extended resource definition supplying names and attributes.
• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID; enforces security partitioning.
• JTF_RS_RESOURCE_GROUPS and other resource membership views — frequently combined to reconcile group membership against the reporting hierarchy.
• JTF_RS_TEAMS / team assignment tables — consume manager relationships when resolving escalation and assignment chains.
• JTF_RS_REP_MANAGERS_PK / _U1 indexes — enforce uniqueness on DENORM_MGR_ID and support lookup performance.