Results for “denorm_level”

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

Overview

JTF_RS_DBI_RES_GRP_VL is an APPS-owned database view within the JTF – CRM Foundation product family. It exposes denormalized Oracle Daily Business Intelligence (DBI) resource group data in a language-translated, security-filtered form suitable for reporting and integration. The view consolidates resource group hierarchy information, group membership details, and translated group and resource names into a single queryable interface. Its name suffix "_VL" indicates it is a "view language" object, meaning it joins to translation tables using the session language from USERENV('LANG') and exposes the appropriate translated value for the current user.

The view plays a central role in CRM resource group reporting by hiding the complexity of the underlying denormalization tables. It applies row-level filtering using FND_GLOBAL.USER_ID so that each user sees only the group rows to which they are entitled. Records with CURRENT_ID equal to -7777 are excluded, and a synthetic "DUMMY RECORD" row (ID -1111) is injected to support page-by-model-view (PMV) behavior in DBI dashboards. The view also enforces that non-manager, non-administrator sales representatives cannot see the group member list, a restriction implemented through an EXISTS subquery against JTF_RS_DBI_DENORM_RES_GROUPS.

Underlying Base Objects

The view is defined over the following documented objects:

• JTF_RS_DBI_DENORM_RES_GROUPS — the primary denormalized resource group table (referenced via synonym), supplying group identifiers, hierarchy links, usage, membership flags, denormalization levels, effective dates, and the USER_ID used for row filtering.
• JTF_RS_GROUPS_TL — the translated resource group name table, joined on GROUP_ID and the session language.
• JTF_RS_RESOURCE_EXTNS_TL — the translated resource name table, joined on RESOURCE_ID and the session language for the group member list rows.
• JTF_RS_DBI_MGR_GROUPS — a manager-oriented DBI group object referenced in the broader denormalization model.
• JTF_RS_GROUPS_DENORM — the denormalized group relationship object used in hierarchy assembly.
• JTF_RS_GROUP_USAGES — provides the USAGE classification (for example, sales or service group context).
• FND_GLOBAL — the standard EBS package supplying USER_ID for security filtering.
• DUAL — used to construct the PMV dummy row.

Key Columns

• VALUE — concatenated display value. For group header rows it is DRG.VALUE || GROUP_NAME; for member rows it is DRG.VALUE || RESOURCE_NAME || '.' || GROUP_NAME.
• ID — derived identifier, either the group ID or ID_FOR_GRP_MEM for member rows.
• CURRENT_ID — the effective group identifier used to resolve the current hierarchy position.
• PARENT_ID — the parent group in the denormalized hierarchy.
• USAGE — group usage classification.
• DENORM_LEVEL — the depth of the row within the denormalized hierarchy.
• START_DATE / END_DATE — effective date range of the group relationship.
• MEM_FLAG / MEM_STATUS — membership indicator and status flag.
• RESOURCE_ID — the resource associated with the row.
• ACTIVE_GRP_REL_ONLY — indicates whether only active group relationships are considered.
• DEBUG_COLUMN — diagnostic column (for example, 'ZZ-DUMMY-RECORD').

Common Use Cases and Queries

Typical consumers include DBI dashboards, CRM resource group reports, and integrations that need a security-filtered, language-translated group listing. A representative query follows:

• SELECT id, value, current_id, parent_id, denorm_level FROM jtf_rs_dbi_res_grp_vl WHERE usage = :p_usage ORDER BY value;
• SELECT value, resource_id FROM jtf_rs_dbi_res_grp_vl WHERE current_id = :p_group_id AND mem_flag = 'Y';
• SELECT DISTINCT current_id FROM jtf_rs_dbi_res_grp_vl WHERE active_grp_rel_only = 'Y';

Users searching on the term "latest_relationship_flag" should note that no column by that name appears in the documented view text. The closest functional equivalents are CURRENT_ID (the currently effective relationship identifier), ACTIVE_GRP_REL_ONLY (restricting output to active group relationships), and the START_DATE/END_DATE pair (which define the validity window of each relationship). Any query intending to retrieve the "latest relationship" should therefore combine CURRENT_ID with ACTIVE_GRP_REL_ONLY and the effective date columns, rather than referencing a nonexistent flag.