Results for “benbv_pl_bnf_v”

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

Overview

BENBV_PL_BNF_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 identifies the person or organization designated by a participant in a benefits plan as the beneficiary of plan benefits. The view exposes beneficiary designation records together with their effective dating, designation percentages and amounts, designation start and end dates, descriptive flexfield context, and the primary contingent relationship code that qualifies the designation.

Because it is used for reporting and integration rather than transactional maintenance, the view is declared WITH READ ONLY and filters rows through the business group security profile via NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, PBN.BUSINESS_GROUP_ID). This means a query returns only data for the business groups accessible to the session's security profile, or all business groups when no security profile restriction applies. The view therefore presents the beneficiary designation layer of the plan enrollment model in a denormalized, readable form suitable for extracts, analytics, and downstream interfaces.

Underlying Base Objects

The view is defined over a single base object, BEN_PL_BNF_F (accessed in the schema as a synonym), which stores plan beneficiary designation rows. The descriptive flexfield segment columns of that table are not enumerated individually; instead the view emits the token '_DF:BEN:BEN_PL_BNF_F:PBN', which instructs the flexfield mechanism to materialize the BEN_PL_BNF_F DFF context and its segments at query time. The second referenced object is the HR_BIS package, used in two places: HR_BIS.BIS_DECODE_LOOKUP('BEN_PRMRY_CNTNGNT', PBN.PRMRY_CNTNGNT_CD) translates the primary contingent code into a meaning, and HR_BIS.GET_SEC_PROFILE_BG_ID supplies the business group security profile predicate. The join to the base table is implicit — the view is a projection and decode of BEN_PL_BNF_F, with no additional table joins.

Key Columns

• TTEE_PERSON_ID — the participant (trustee/enrollee) person who made the designation; the value searched for by users. It links back to the person for whom plan benefits are being designated.
• BNF_PERSON_ID — the designated beneficiary person identifier.
• ORGANIZATION_ID — populated when the beneficiary is an organization rather than a person.
• PL_BNF_ID — primary key of the beneficiary designation record.
• PRTT_ENRT_RSLT_ID and PER_IN_LER_ID — links to the participation enrollment result and the person in the life event/learner record driving the designation.
• PCT_DSGD_NUM, AMT_DSGD_VAL, AMT_DSGD_UOM — percentage designated, designated amount, and its unit of measure.
• DSGN_STRT_DT, DSGN_THRU_DT — designation start and end dates.
• EFFECTIVE_START_DATE, EFFECTIVE_END_DATE — effective dating of the designation row.
• PRMRY_CNTNGNT_CD and PRMRY_CNTNGNT_CD_M — primary contingent relationship code and its decoded meaning.

Common Use Cases and Queries

Typical uses include beneficiary reporting for life and health plans, reconciliation of designations against enrollment results, and extracts feeding payroll or third-party administrators. A frequent pattern is finding all beneficiaries named by a given participant:

SELECT pl_bnf_id, ttee_person_id, bnf_person_id, organization_id, pct_dsgd_num, amt_dsgd_val, dsgn_strt_dt, dsgn_thru_dt, prmry_cntngnt_cd_m FROM apps.benbv_pl_bnf_v WHERE ttee_person_id = :p_person_id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date ORDER BY dsgn_strt_dt;

To list designations by relationship, filter on the decoded contingent code:

SELECT ttee_person_id, bnf_person_id, pct_dsgd_num, prmry_cntngnt_cd_m FROM apps.benbv_pl_bnf_v WHERE prmry_cntngnt_cd = 'SPOUSE';

Because the view enforces WITH READ ONLY and business group security, it must not be used for DML; updates belong to the underlying BEN_PL_BNF_F entity through the standard Advanced Benefits APIs.