Results for “benbv_prtn_elig_prfl_v”

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

Overview

BENBV_PRTN_ELIG_PRFL_V is a read-only Oracle EBS Advanced Benefits (BEN) view owned by the APPS schema. As documented in the ETRM metadata for 12.1.1 and 12.2.2, the view "identifies one or more eligibility profiles attached to a compensation object for the purpose of determining a person's eligibility to participate." In practical terms, it exposes the association between a participation eligibility record and the eligibility profile that governs it, allowing report authors, integrators, and functional consultants to resolve which eligibility profiles drive participation in a compensation object such as a plan, program, or option.

The view is defined as WITH READ ONLY, so it can be queried for reporting and integration purposes but cannot be used as a DML target. It carries the standard effective-dating columns (EFFECTIVE_START_DATE, EFFECTIVE_END_DATE) and the WHO audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE), making it usable in date-tracked reporting and in extracts that require audit lineage.

Underlying Base Objects

The view is defined over two documented objects:

• BEN_PRTN_ELIG_PRFL_F (accessed via synonym as CEP) — the participation eligibility profile base table. It stores the effective-dated rows linking a participation eligibility record to an eligibility profile.
• HR_BIS (package) — referenced for two decode lookups (BIS_DECODE_LOOKUP) and for the security predicate HR_BIS.GET_SEC_PROFILE_BG_ID.

Because the driving table is the _F (date-effective) table, the view returns effective-dated rows rather than a single current row per association. The WHERE clause restricts rows to the business group returned by HR_BIS.GET_SEC_PROFILE_BG_ID, applying the caller's security profile; if no security profile business group is returned, the NVL fallback allows the row's own BUSINESS_GROUP_ID to pass. The descriptive flexfield reference '_DF:BEN:BEN_PRTN_ELIG_PRFL_F:CEP' signals that the view participates in the BEN descriptive flexfield framework.

Key Columns

• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the effective-dating range for the association row.
• ELIG_PRFL_TYPE_CD / ELIG_PRFL_TYPE_CD_M — the eligibility profile type code and its decoded meaning via the BEN_ELIG_PRFL_TYPE lookup.
• MNDTRY_FLAG / MNDTRY_FLAG_M — whether the eligibility profile is mandatory, plus its decoded YES_NO value.
• PRTN_ELIG_PRFL_ID — primary identifier of the participation eligibility profile association.
• ELIGY_PRFL_ID — the eligibility profile being attached.
• PRTN_ELIG_ID — the participation eligibility record to which the profile is attached (the compensation object link).
• BUSINESS_GROUP_ID — the business group owning the row, also used for security filtering.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE — WHO audit columns.

Common Use Cases and Queries

Typical uses include auditing which eligibility profiles are attached to a participation eligibility record, producing eligibility configuration extracts, and tracing mandatory versus optional profile assignments. The security-restricted BUSINESS_GROUP_ID predicate makes the view safe for multi-org reporting without additional predicates.

List all profiles attached to a participation eligibility record:

• SELECT prtn_elig_id, eligy_prfl_id, elig_prfl_type_cd_m, mndtry_flag_m, effective_start_date, effective_end_date FROM apps.benbv_prtn_elig_prfl_v ORDER BY prtn_elig_id, effective_start_date;

Filter to mandatory profiles only:

• SELECT prtn_elig_prfl_id, prtn_elig_id, eligy_prfl_id, effective_start_date, effective_end_date FROM apps.benbv_prtn_elig_prfl_v WHERE mndtry_flag = 'Y' AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

Report profiles by type for a business group:

• SELECT elig_prfl_type_cd_m, COUNT(*) FROM apps.benbv_prtn_elig_prfl_v GROUP BY elig_prfl_type_cd_m;

Because the view is read-only and security-filtered, it is well suited to concurrent-program extracts and BI Publisher data models that must respect the requesting user's business group.