Search Results jtf_rs_hierarchy

Overview

The JTF.JTF_RS_REP_MANAGERS table is a denormalized resource-management structure within the Oracle E-Business Suite (12.1.1 / 12.2.2) JTF schema. It stores the reporting hierarchy of group members, associating individual resources (employees, contractors, or other resource categories) with their managers within a defined resource group. Because the hierarchy is persisted in flattened, denormalized form rather than derived recursively at query time, it supports efficient resolution of "who reports to whom" relationships across CRM and Resource Manager modules without repeated traversal of parent-child chains.

The table is owned by JTF, carries FND Design Data registration, and resides in the APPS_TS_TX_DATA tablespace. Under a heuristic Data Vault classification mined from the foreign-key structure, this object is best modeled as a link table — it connects resources (members), managers, and groups, and semantically represents a relationship rather than an immutable descriptive entity. Consumers should note that the primary key JTF_RS_REP_MANAGERS_PK is defined on DENORM_MGR_ID, a surrogate key generated for each denormalized relationship record.

Key Information Stored

Although the documented physical schema contains 37 columns, the following are the most significant for query and integration purposes:

• DENORM_MGR_ID — surrogate unique identifier for each denormalized manager record; serves as the primary key and is enforced by the unique index JTF_RS_REP_MANAGERS_U1.
• RESOURCE_ID — the resource identifier of the member (the subordinate) in the relationship.
• PERSON_ID — the person identifier of the member; maps to the source ID of the resource.
• MANAGER_PERSON_ID — the person identifier of the manager to whom the member reports.
• GROUP_ID — the resource group to which the record applies; scopes the hierarchy.
• CATEGORY — category of the resource, for example EMPLOYEE.
• HIERARCHY_TYPE — the classification of hierarchy, validated against the JTF_RS_HIERARCHY lookup type in FND_LOOKUPS.
• REPORTS_TO_FLAG — indicates whether the record represents an active reporting relationship.
• START_DATE_ACTIVE / END_DATE_ACTIVE — effective dating bounds for the relationship.
• PARENT_RESOURCE_ID — resource identifier of the parent in the hierarchy; supports traversal.
• PAR_ROLE_RELATE_ID / CHILD_ROLE_RELATE_ID — role-relationship identifiers for parent and child nodes.
• DENORM_LEVEL — depth of the member within the denormalized hierarchy.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS for multi-org / data-security scoping.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the framework.
• Standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) plus 15 attribute columns for extensibility.

Common Use Cases and Queries

Typical reporting scenarios include headcount roll-ups by manager, organizational chart rendering, and approval-routing resolution. A representative query retrieves all direct reports for a given manager within a group:

SELECT r.PERSON_ID, r.RESOURCE_ID, r.MANAGER_PERSON_ID, r.DENORM_LEVEL
FROM   JTF.JTF_RS_REP_MANAGERS r
WHERE  r.MANAGER_PERSON_ID = :manager_person_id
AND    r.GROUP_ID          = :group_id
AND    r.REPORTS_TO_FLAG   = 'Y'
AND    TRUNC(SYSDATE) BETWEEN NVL(r.START_DATE_ACTIVE, SYSDATE)
                          AND NVL(r.END_DATE_ACTIVE,   SYSDATE);

Because the table is denormalized, flattened hierarchy extracts are available without recursive CONNECT BY logic; consumers filter by DENORM_LEVEL to obtain an entire subtree under a given manager. Security-constrained reports should always include SECURITY_GROUP_ID to satisfy multi-org and data-security policies.

Related Objects

The table participates in the following documented foreign-key relationships and dependencies:

• JTF.JTF_RS_GROUPS_B — joined on GROUP_ID = JTF_RS_GROUPS_B.GROUP_ID; defines the resource group scope.
• JTF.JTF_RS_RESOURCE_EXTNS — joined on RESOURCE_ID = JTF_RS_RESOURCE_EXTNS.RESOURCE_ID; provides resource extension attributes for the member.
• FND.FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID; enforces data-security partitioning.
• FND_LOOKUPS — supplies valid values for HIERARCHY_TYPE via the JTF_RS_HIERARCHY lookup type.
• FND_USER — implicit WHO-column dependency for CREATED_BY and LAST_UPDATED_BY.

Indexes JTF_RS_REP_MANAGERS_N1 through _N9 provide access paths on combinations of RESOURCE_ID, MANAGER_PERSON_ID, GROUP_ID, PERSON_ID, PARENT_RESOURCE_ID, and role-relationship columns, supporting the hierarchy and security access patterns described above.