Results for “jtf_rs_srp_groups_v”

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

Overview

JTF_RS_SRP_GROUPS_V is a CRM Foundation (JTF) view owned by the APPS schema and defined with VALID status. Its documented purpose is to fetch the default (primary) resource group associated with a sales representative. Because a sales representative may belong to multiple resource groups through role relations, this view resolves the "primary" assignment by ranking role type and role membership priority, allowing callers to obtain the single, most meaningful group context for a given resource, salesrep, or organization combination.

The view is widely consumed by CRM modules — Sales, TeleSales, and related field service flows — as well as by custom reports and integrations that must determine a salesrep's default group without re-implementing the underlying role-resolution logic. It is a reporting-oriented read layer over the JTF resource management tables rather than a transactional object, and it exists in both Oracle EBS 12.1.1 and 12.2.2 with the same APPS ownership and synonym-based dependencies.

Underlying Base Objects

The view is defined over the following documented base objects (all referenced as SYNONYMs in APPS):

• JTF_RS_ROLE_RELATIONS — supplies role assignment rows, start and end dates, and the RS_GROUP_MEMBER resource type linkage.
• JTF_RS_GROUP_MEMBERS — ties resources to groups and provides the group member identifier.
• JTF_RS_SALESREPS — returns the salesrep identifier and operating unit (ORG_ID).
• JTF_RS_GROUP_USAGES — restricts to group usages of type 'SALES'.
• JTF_RS_ROLES_B — provides role type and member/manager flags used for priority ranking.
• JTF_RS_GROUPS_DENORM — the denormalized group table that drives effective-date overlap logic.
• JTF_RS_RESOURCE_EXTNS — resolves the resource to its application USER_ID.
• JTF_RS_GROUPS_TL — supplies the translated group name for the session language.
• DUAL — used in the UNION ALL branch that synthesizes default rows.

Key Columns

• RESOURCE_ID, SALESREP_ID, ORG_ID, USER_ID — identity keys linking the resource, the salesrep record, the operating unit, and the FND user.
• GROUP_ID, GROUP_NAME — the resolved group and its language-specific display name.
• ROLE_ID, ROLE_TYPE_CODE — the role backing the membership and its classification (for example, SALES).
• ROLE_TYPE_PRIORITY — ranking derived via DECODE on ROLE_TYPE_CODE ('SALES' = 1, otherwise 2).
• ROLE_PRIORITY — ranking of member over manager (member = 1, manager = 2).
• ROLE_START_DATE, ROLE_END_DATE — the effective range of the role relation; ROLE_END_DATE defaults to 12/31/4712 when END_DATE_ACTIVE is null, representing an open-ended assignment. These columns are directly relevant to date-sensitive queries such as searching on "role_end_date".
• DENORM_COUNT — a count of denormalized group rows, useful for validating effective-date overlaps.

Common Use Cases and Queries

The most common requirement is to find the current primary group for a sales representative, filtering out expired role assignments:

SELECT resource_id, salesrep_id, org_id, group_id, group_name
FROM apps.jtf_rs_srp_groups_v
WHERE salesrep_id = :p_salesrep_id
  AND TRUNC(SYSDATE) BETWEEN role_start_date AND role_end_date;

To identify assignments that are currently active or that have already ended:

SELECT salesrep_id, group_id, role_start_date, role_end_date
FROM apps.jtf_rs_srp_groups_v
WHERE role_end_date < SYSDATE;

Ranking by the priority columns to reproduce the default selection:

SELECT *
FROM (SELECT v.*,
      ROW_NUMBER() OVER (PARTITION BY resource_id, org_id
        ORDER BY role_type_priority, role_priority, role_start_date DESC) rn
    FROM apps.jtf_rs_srp_groups_v v)
WHERE rn = 1;

Because the view exposes ROLE_START_DATE and ROLE_END_DATE explicitly, it is a convenient single source for date-window reporting on salesrep group membership without joining the JTF resource tables directly.