Results for “dsgn_strt_dt”

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

Overview

BEN_PL_BNF_D is a denormalized reporting view owned by the APPS schema within the Oracle Advanced Benefits (BEN) module. It presents designated beneficiary information for participant elections and plan enrollments, resolving raw foreign key identifiers into human-readable attributes. The view draws its data from BEN_PL_BNF_F, the base table that stores beneficiary designations, and enriches each row with the beneficiary's full name, the associated organization name, the trustee's full name, and the decoded meaning of the primary contingent indicator. Because it exposes a stable, join-ready projection over volatile person and organization records, BEN_PL_BNF_D is primarily used for reporting, extract generation, and integ

ration interfaces where beneficiary detail must be presented without embedding effective-dating logic in the calling code. The object holds VALID status and is documented in ETRM for both Oracle EBS 12.1.1 and 12.2.2.

Underlying Base Objects

The view is defined over several documented base objects. The primary driver is BEN_PL_BNF_F (referenced via a synonym), which supplies the beneficiary designation rows including PL_BNF_ID, the designation dates, and the designated percentage or amount. BEN_PER_IN_LER (synonym) is outer-joined to provide the underlying plan enrollment record, with a filter that excludes rows whose PER_IN_LER_STAT_CD is 'VOIDD' or 'BCKDT'. PER_ALL_PEOPLE_F is joined twice through synonyms: once aliased BNF_PERSON to resolve the beneficiary, and once aliased TTEE_PERSON to resolve the trustee. HR_ALL_ORGANIZATION_UNITS_VL (a view) supplies the organization name, while HR_LOOKUPS (a view) decodes PRMRY_CNTNGNT_CD against the lookup type 'BEN_PRMRY_CNTNGNT'. FND_USER (synonym) is outer-joined on LAST_UPDATED_BY to support who-column display. HR_API is listed as a referenced package dependency. All person and organization joins are outer joins and are additionally constrained by effective dates, ensuring that the name returned is the one valid as of the designation's effective start date.

Key Columns

• ROW_ID — the ROWID of the underlying BEN_PL_BNF_F row, useful for row-level identification.
• PL_BNF_ID — primary key of the beneficiary designation record.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date range for which the designation is valid.
• BNF_PERSON_FULL_NAME — full name of the designated beneficiary.
• ORGANIZATION_NAME — name of the organization designated as beneficiary, where applicable.
• TTEE_PERSON_FULL_NAME — full name of the trustee associated with the designation.
• PRMRY_CNTNGNT_MEANING — decoded meaning of the primary contingent beneficiary code.
• PCT_DSGD_NUM — percentage of the benefit designated to this beneficiary.
• AMT_DSGD_VAL / AMT_DSGD_UOM — designated amount and its unit of measure.
• ADDL_INSTRN_TXT — free-text additional instructions.
• DSGN_STRT_DT / DSGN_THRU_DT — start and end dates of the designation itself.
• LAST_UPDATE_DATE / LAST_UPDATED_BY — audit columns identifying the last modifying user.

Common Use Cases and Queries

Typical uses include beneficiary roster reports, audit extracts confirming designated percentages, and integration feeds that require resolved names rather than IDs. A standard query retrieves active designations for a given plan enrollment or participant:

SELECT pl_bnf_id, bnf_person_full_name, organization_name, tt ee_person_full_name, prmry_cntngnt_meaning, pct_dsgd_num, amt_dsgd_val, dsgn_strt_dt, dsgn_thru_dt FROM apps.ben_pl_bnf_d WHERE effective_start_date <= SYSDATE AND effective_end_date >= SYSDATE ORDER BY bnf_person_full_name;

For reconciliation reporting, analysts frequently aggregate designated percentages by beneficiary to verify that total allocations equal 100 percent. Because the view already excludes voided and backdated enrollment rows and applies effective-date matching against person and organization records, consumers can query it directly without replicating those predicates. Joins back to BEN_PL_BNF_F on PL_BNF_ID should be used when additional base-table attributes are required beyond those projected by the view.