Results for “jtf_rs_role_relations”

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

Overview

JTF_RS_ROLE_RELATIONS is a core table within the JTF (CRM Foundation) schema of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. It stores the association between resources and roles, defining which role a given resource is assigned to within the CRM resource management model. The primary key is documented as ROLE_RELATE_ID (constraint JTF_RS_ROLE_RELATIONS_PK), while the conceptual business rule states that each resource may be assigned to multiple roles. The table therefore functions as a many-to-many intersection between role definitions and the various resource-like entities in the CRM Foundation, including resources, teams, team members, groups, and group members.

From a data modeling perspective, the metadata's heuristic Data Vault classification is satellite-leaning. This suggests that JTF_RS_ROLE_RELATIONS is best treated as a satellite or link-style table describing the relationship between roles and resource-holding entities, rather than as a standalone hub of independent business keys. Its START_DATE_ACTIVE, END_DATE_ACTIVE, DELETE_FLAG, and ACTIVE_FLAG columns reinforce this interpretation, since they capture the lifecycle and effective dating of the assignment rather than immutable identity data.

Key Information Stored

The most important columns in this table are:

• ROLE_RELATE_ID — Surrogate primary key, backed by unique index JTF_RS_ROLE_RELATIONS_U1 and constraint JTF_RS_ROLE_RELATIONS_PK.
• ROLE_ID — Foreign key to JTF_RS_ROLES_B, identifying the role being related.
• ROLE_RESOURCE_ID — Foreign key identifying the resource, team, or group to which the role is attached; it references JTF_RS_RESOURCE_EXTNS, JTF_RS_TEAMS_B, JTF_RS_TEAM_MEMBERS, JTF_RS_GROUPS_B, and JTF_RS_GROUP_MEMBERS.
• ROLE_RESOURCE_TYPE — Foreign key to JTF_OBJECTS_B, disambiguating the type of the ROLE_RESOURCE_ID entity (resource, team, group, etc.).
• START_DATE_ACTIVE and END_DATE_ACTIVE — Effective dating for the role assignment.
• ACTIVE_FLAG and DELETE_FLAG — Denormalized status indicators controlling whether the relation is currently active or logically deleted.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, enforcing Multi-Org Access Control on the relationship.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the OAF/BC4J layer.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard audit who-columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — Descriptive flexfield segments for client-specific extensions.

Common Use Cases and Queries

Typical use cases include determining which roles a resource currently holds, resolving whether a specific agent qualifies for a role at a point in time, and reporting role coverage across teams and groups. Because the table is effectively dated, effective-date filtering is essential.

For active roles held by a resource:

• SELECT rr.role_id, rr.start_date_active, rr.end_date_active FROM jtf_rs_role_relations rr WHERE rr.role_resource_id = :resource_id AND rr.role_resource_type = :object_id AND NVL(rr.active_flag,'Y') = 'Y' AND TRUNC(SYSDATE) BETWEEN NVL(rr.start_date_active, SYSDATE) AND NVL(rr.end_date_active, SYSDATE + 1) AND NVL(rr.delete_flag,'N') = 'N';

For role-to-resource rollup reporting, join to JTF_RS_ROLES_B on ROLE_ID and to JTF_RS_RESOURCE_EXTNS on ROLE_RESOURCE_ID to resolve the resource name. Security-group scoping should be applied via SECURITY_GROUP_ID when running multi-org reports.

Related Objects

• JTF_RS_ROLES_B — Parent role definition table; joined on ROLE_ID = JTF_RS_ROLES_B.ROLE_ID.
• JTF_RS_RESOURCE_EXTNS — Resource extension table; joined on ROLE_RESOURCE_ID when the resource type denotes a resource.
• JTF_RS_TEAMS_B and JTF_RS_TEAM_MEMBERS — Referenced through ROLE_RESOURCE_ID when a role is granted to a team or team member.
• JTF_RS_GROUPS_B and JTF_RS_GROUP_MEMBERS — Referenced through ROLE_RESOURCE_ID for group-level role assignments.
• JTF_OBJECTS_B — Joined on ROLE_RESOURCE_TYPE to resolve the entity type of the related resource.
• FND_SECURITY_GROUPS — Joined on SECURITY_GROUP_ID to enforce Multi-Org security.
• JTF_RS_ROLE_RELATE_AUD — Audit trail (data vault "satellite") table keyed by ROLE_RELATE_ID, capturing historical changes to role relationships.