Search Results jtf_rs_active_grp_mbrs_n2

Overview

The JTF_RS_ACTIVE_GRP_MBRS table is a core data object within the Oracle E-Business Suite (EBS) Resource Manager (JTF) module, specifically for versions 12.1.1 and 12.2.2. As explicitly stated in the ETRM documentation, this table's sole purpose is to store active group membership details. It functions as a transactional table that maintains the current, active relationships between resources (employees, partners, teams) and the resource groups to which they are assigned. This data is critical for routing service requests, managing territories, assigning tasks, and defining sales teams within the application's operational flows.

Key Information Stored

The table's structure is designed to capture the membership relationship, standard auditing information, and system-level data. The primary columns and their significance are:

• GROUP_MEMBER_ID: The unique primary key identifier for each active membership record.
• GROUP_ID: A foreign key referencing the JTF_RS_GROUPS table, identifying the specific resource group.
• RESOURCE_ID: The identifier for the resource (from JTF_RS_RESOURCE_EXTNS) assigned to the group.
• PERSON_ID: The corresponding employee identifier, linking to the HR person record (PER_ALL_PEOPLE_F).
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN: Standard Oracle EBS "Who" columns for auditing.
• OBJECT_VERSION_NUMBER: A number used for optimistic locking to prevent data overwrite conflicts.
• SECURITY_GROUP_ID: A column reserved for Applications Hosting (multi-org) purposes.

The presence of non-unique indexes on GROUP_ID/RESOURCE_ID and RESOURCE_ID alone indicates common access paths for finding all members of a group or all groups for a specific resource.

Common Use Cases and Queries

This table is central to queries that determine resource availability and assignment. Common operational and reporting scenarios include listing all active members of a specific service or sales group, verifying a resource's current group affiliations before assigning a task, and auditing group membership changes. A fundamental query pattern retrieves active group memberships with descriptive information:

SELECT agm.GROUP_MEMBER_ID,
       agm.GROUP_ID,
       rg.GROUP_NAME,
       agm.RESOURCE_ID,
       re.RESOURCE_NAME,
       agm.PERSON_ID
FROM   jtf.jtf_rs_active_grp_mbrs agm,
       jtf.jtf_rs_groups rg,
       jtf.jtf_rs_resource_extns re
WHERE  agm.GROUP_ID = rg.GROUP_ID
AND    agm.RESOURCE_ID = re.RESOURCE_ID
AND    rg.GROUP_ID = :p_group_id; -- For a specific group

Another critical use case is identifying all groups a particular resource belongs to by filtering on the RESOURCE_ID column, leveraging the JTF_RS_ACTIVE_GRP_MBRS_N2 index for performance.

Related Objects

Based on the provided metadata, JTF_RS_ACTIVE_GRP_MBRS has defined foreign key relationships and dependencies central to the Resource Manager schema.

• Primary Reference (Foreign Key): The GROUP_ID column is documented as a "foreign key to JTF_RS_GROUPS." This table (JTF.JTF_RS_GROUPS) is the master definition table for all resource groups.
• Key Dependencies: The ETRM states the table is referenced by the APPS synonym JTF_RS_ACTIVE_GRP_MBRS. This public synonym allows other application modules to access this table seamlessly within the APPS schema context.
• Implicit Relationships: While not listed as a formal foreign key in the snippet, the RESOURCE_ID column typically joins to JTF_RS_RESOURCE_EXTNS (resources), and PERSON_ID joins to PER_ALL_PEOPLE_F (employees). These relationships are essential for enriching membership data with resource and person details in reports and application logic.