Results for “jtf_rs_grp_relations_vl”

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

Overview

JTF_RS_GRP_RELATIONS_VL is a CRM Foundation (JTF) view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It is a "VL" (value-list) style view that presents one row per relationship defined between two resource groups. The view exists to support the Define Group form's Relations block, where administrators record that one group is related to another group under a specific relationship type. Because it joins the relationship table to the groups view and to FND_LOOKUPS in a single statement, it removes the need for form and report developers to resolve the related group's number and name and to translate the relationship type code into a user-facing meaning.

Users and developers commonly search for the column RELATED_GROUP_ID, which is the foreign key pointing to the group on the "other side" of a relationship. This column, together with GROUP_ID and RELATION_TYPE, forms the logical key of each relationship record. The view is read-only from the reporting perspective: it is defined as a SELECT over base objects, and DML is performed against the underlying table, not the view.

Underlying Base Objects

The view text selects from four sources. The driving table is JTF_RS_GRP_RELATIONS (exposed through a synonym in the APPS schema), aliased as B, which stores the relationship records themselves. Two instances of JTF_RS_GROUPS_VL, aliased A and C, are joined to that table: A on GROUP_ID = B.GROUP_ID and C on GROUP_ID = B.RELATED_GROUP_ID. These joins supply the related group number and name while also guaranteeing that both the owning group and the related group are valid.

FND_LOOKUPS, aliased D, is joined on B.RELATION_TYPE = D.LOOKUP_CODE with LOOKUP_TYPE = 'JTF_RS_RELATION_TYPE', which supplies MEANING as RELATION_TYPE_NAME. The documented metadata also lists FND_GLOBAL (PACKAGE) among the referenced objects, reflecting standard who-column population via FND_GLOBAL.USER_ID and related calls. Because JTF_RS_GROUPS_VL is itself built on the group tables with a translated-name layer, the view inherently reflects the language and security characteristics of that underlying object.

Key Columns

• ROW_ID — the ROWID of the underlying JTF_RS_GRP_RELATIONS row, used by Oracle Forms for row identification.
• GROUP_RELATE_ID — primary key of the relationship record.
• GROUP_ID — the group that owns the relationship (the "from" group).
• RELATED_GROUP_ID — the group on the other side of the relationship (the "to" group); the column most frequently queried.
• RELATED_GROUP_NUMBER / RELATED_GROUP_NAME — descriptive attributes of the related group, obtained from JTF_RS_GROUPS_VL.
• RELATION_TYPE — lookup code from FND_LOOKUPS; RELATION_TYPE_NAME — its translated meaning.
• START_DATE_ACTIVE / END_DATE_ACTIVE — date range during which the relationship is active.
• DELETE_FLAG — soft-delete indicator; active rows are typically filtered on 'N'.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the form.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard who columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — descriptive flexfield columns carried through from the base table.

Common Use Cases and Queries

The view is used to report group hierarchies and interdependencies, to validate relationship data during conversions, and to drive integration extracts published to external systems. Because RELATED_GROUP_NAME and RELATION_TYPE_NAME are pre-resolved, it is a convenient source for end-user reporting without additional joins.

To list every active relationship for a given group:

• SELECT related_group_id, related_group_number, related_group_name, relation_type_name FROM jtf_rs_grp_relations_vl WHERE group_id = :p_group_id AND NVL(delete_flag,'N') = 'N' AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active,TRUNC(SYSDATE)) AND NVL(end_date_active,TRUNC(SYSDATE));

To find which groups reference a particular group as the related group (a reverse lookup on RELATED_GROUP_ID):

• SELECT group_id, related_group_id, relation_type_name FROM jtf_rs_grp_relations_vl WHERE related_group_id = :p_related_group_id ORDER BY group_id;

To enumerate all relationships of one type, for example a manager or member relationship defined in the JTF_RS_RELATION_TYPE lookup:

• SELECT group_id, related_group_id, relation_type, relation_type_name FROM jtf_rs_grp_relations_vl WHERE relation_type = :p_lookup_code;

In all cases the view should be treated as a query-only interface; maintenance of relationship rows is performed through the Define Group form against JTF_RS_GRP_RELATIONS.