Results for “ben_popl_rptg_grp_d”

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

Overview

The BEN_POPL_RPTG_GRP_D view is a reporting-layer database object owned by the APPS schema in Oracle E-Business Suite, belonging to the BEN (Advanced Benefits) product family. Its name denotes a "D" (denormalized/display) view over the population reporting group entity, and the ETRM documentation classifies it as "Retrofitted," indicating it was introduced or re-created to preserve backward compatibility with earlier releases of the Benefits module. The view exposes a flattened, human-readable representation of rows held in the BEN_POPL_RPTG_GRP_F table by resolving the underlying surrogate identifiers into their descriptive names, and by including audit columns such as LAST_UPDATE_DATE and the user who performed the last update. In release 12.1.1 and 12.2.2 the object remains VALID and is available for query in the APPS schema. Because it is a view rather than a table, it imposes no storage of its own and is intended primarily for read access in reporting, extracts, and lightweight integration scenarios where descriptive values are preferred over raw IDs.

Underlying Base Objects

The view is defined over five referenced base objects, all accessed through APPS synonyms: BEN_POPL_RPTG_GRP_F as the driving table, outer-joined to BEN_RPTG_GRP, BEN_PGM_F, BEN_PL_F, and FND_USER. The join predicates are all outer joins (denoted by the (+) operator), meaning that a population reporting group row is returned even when its related reporting group, program, plan, or user record is absent or not yet defined. BEN_POPL_RPTG_GRP_F holds the effective-dated association between a reporting group and the programs or plans that fall within a population, whereas BEN_RPTG_GRP, BEN_PGM_F, and BEN_PL_F are the descriptive, effective-dated definition tables for reporting groups, programs, and plans respectively. FND_USER supplies the application user name associated with the LAST_UPDATED_BY audit column. The joins to BEN_PGM_F and BEN_PL_F are on their single-column primary keys (PGM_ID and PL_ID), so no effective-date filter is applied within the view — callers must apply date criteria themselves if they require only currently effective reference rows.

Key Columns

The view returns ten columns. ROW_ID is the physical ROWID of the underlying BEN_POPL_RPTG_GRP_F row and provides a unique identifier suitable for updates or de-duplication. POPL_RPTG_GRP_ID is the primary key of the association record; EFFECTIVE_START_DATE and EFFECTIVE_END_DATE define its effective-dating window. The three descriptive columns — RPTG_GRP_NAME, PGM_NAME, and PL_NAME — are resolved from BEN_RPTG_GRP.NAME, BEN_PGM_F.NAME, and BEN_PL_F.NAME respectively, and may be null when the corresponding outer join yields no row. ORDR_NUM provides the display ordering of the population reporting group entry. LAST_UPDATE_DATE records when the underlying row was most recently modified, and LAST_UPDATED_BY is the FND_USER user identifier of the person responsible; the view text exposes the user ID rather than the user name, so a further join to FND_USER is required if the name itself is wanted.

Common Use Cases and Queries

Typical uses include reporting on which programs and plans belong to a given population reporting group, validating configuration during Benefits implementation, and extracting data for reconciliation or interface files. Because the view resolves names, it is convenient for ad hoc queries and BI Publisher data models without requiring the developer to join the definition tables manually.

• List all population reporting group entries with descriptive names:
  SELECT popl_rptg_grp_id, rptg_grp_name, pgm_name, pl_name, ordr_num FROM apps.ben_popl_rptg_grp_d ORDER BY rptg_grp_name, ordr_num;
• Return only currently effective rows:
  SELECT * FROM apps.ben_popl_rptg_grp_d WHERE SYSDATE BETWEEN effective_start_date AND effective_end_date;
• Identify rows with missing reference data (null names) for data-quality checks:
  SELECT popl_rptg_grp_id FROM apps.ben_popl_rptg_grp_d WHERE pgm_name IS NULL AND pl_name IS NULL;
• Resolve the auditing user's name via FND_USER, since the view exposes only the ID:
  SELECT d.popl_rptg_grp_id, u.user_name FROM apps.ben_popl_rptg_grp_d d, applsys.fnd_user u WHERE u.user_id = d.last_updated_by;

All queries should be executed against the APPS schema or through a synonym, and effective-date filtering should be applied explicitly wherever point-in-time accuracy is required.