Results for “benbv_pl_regn_v”

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

Overview

BENBV_PL_REGN_V is a read-only Advanced Benefits (BEN) view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It presents the plan-in-reporting-group assignment records that associate a benefit plan with a regulatory reporting group, and it exposes the legislative, regulatory, rule, code, or policy attributes pertinent to the proper management of that plan or reporting group. Its internal name follows the BENBV (Benefits View) convention for business-facing views in Advanced Benefits.

The view serves as the primary reporting and integration surface for the pl_regn_id identifier, which is the term users most commonly search for when locating this object. Rather than querying the underlying table directly, consumers use this view to obtain security-filtered, decode-enriched, and flexfield-aware rows. Security filtering is enforced at the row level through the business group identifier: only rows belonging to the session's security profile business group are returned. The view is declared WITH READ ONLY, so it is intended exclusively for query, reporting, and extraction, not for DML.

Underlying Base Objects

The view is defined over BEN_PL_REGN_F (referenced in the metadata as a synonym), the plan-in-reporting-group entity table in the BEN schema, aliased as PRG in the view text. All persistent data columns originate from that table; the view contributes no materialized storage of its own.

• BEN_PL_REGN_F — supplies effective-dated plan/reporting-group rows, the descriptive flexfield context, the regulatory plan type code, the identifier columns, and the standard WHO audit columns.
• HR_BIS (Package) — invoked twice. HR_BIS.BIS_DECODE_LOOKUP translates the REGY_PL_TYP_CD lookup code into its meaning, and HR_BIS.GET_SEC_PROFILE_BG_ID supplies the business group identifier used in the security predicate.

The relationship is a straightforward restricted projection: every row in the view corresponds to one row in BEN_PL_REGN_F that satisfies the business group security condition.

Key Columns

• PL_REGN_ID — the unique plan-in-reporting-group identifier and primary key of the underlying record.
• PL_ID, REGN_ID, RPTG_GRP_ID, BUSINESS_GROUP_ID — foreign key references to the benefit plan, the regulatory/legislation entity, the reporting group, and the business group.
• EFFECTIVE_START_DATE, EFFECTIVE_END_DATE — the date range for which the assignment is valid.
• REGY_PL_TYP_CD — the regulatory plan type lookup code; REGY_PL_TYP_CD_M is the decoded, user-facing meaning returned by BIS_DECODE_LOOKUP.
• HGHLY_COMPD_DET_RL, KEY_EE_DET_RL, CNTR_NNDSCRN_RL, CVG_NNDSCRN_RL, FIVE_PCT_OWNR_RL — rule or code text describing highly compensated determination, key employee determination, and non-discrimination testing for contributions, coverage, and five-percent owners.
• "_DF:PRG" — the descriptive flexfield column for the BEN_PL_REGN_F flexfield, exposed as '_DF:BEN:BEN_PL_REGN_F:PRG'.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE — WHO audit columns.

Common Use Cases and Queries

Typical uses include regulatory and non-discrimination reporting, plan configuration review, data migration validation, and integration extracts keyed by pl_regn_id.

Retrieve assignments for a plan:

• SELECT pl_regn_id, pl_id, regn_id, rptg_grp_id, regy_pl_typ_cd_m, effective_start_date, effective_end_date FROM apps.benbv_pl_regn_v WHERE pl_id = :p_plan_id;

Locate a specific row by identifier:

• SELECT * FROM apps.benbv_pl_regn_v WHERE pl_regn_id = :p_pl_regn_id;

List active non-discrimination rule text for a reporting group:

• SELECT pl_regn_id, cntr_nndscrn_rl, cvg_nndscrn_rl, five_pct_ownr_rl FROM apps.benbv_pl_regn_v WHERE rptg_grp_id = :p_rptg_grp_id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

Because the view is read-only and security-filtered, it is safe for concurrent reporting use; insert, update, and delete operations must target BEN_PL_REGN_F through the supported Advanced Benefits APIs.