Results for “ben_pl_bnf_f”

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

Overview

BEN_PL_BNF_F is a date-tracked (non-federated, "_F" suffix) table within the Oracle Advanced Benefits (BEN) application, owned by the BEN schema. It stores the entities designated as beneficiaries of a plan, capturing the association between a plan enrollment context and the person who is named to receive benefits. In Oracle EBS 12.1.1 and 12.2.2 the table is validated and active, and it functions as a core configuration/transaction record consumed by benefits eligibility, enrollment, and life-event processing logic.

The primary key BEN_PL_BNF_F_PK is composed of PL_BNF_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE, reflecting the standard EBS date-effective (datetrack) pattern. The single foreign key documented from this table, BEN_PL_BNF_F.PER_IN_LER_ID, references BEN_PER_IN_LER, linking each beneficiary designation to a person-in-learned-earned-relationship record. Under a Data Vault modeling heuristic, the mined FK structure classifies BEN_PL_BNF_F as satellite-leaning, suggesting it is best modeled as a satellite hanging off a person or enrollment hub rather than as an independent hub or link.

Key Information Stored

The table contains 57 documented columns. The most significant are:

• PL_BNF_ID — surrogate identifier for the beneficiary designation; part of the composite primary key and the column most commonly referenced by the user search term "pl_bnf_id".
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the datetrack range that completes the primary key and governs row visibility.
• PER_IN_LER_ID — foreign key to BEN_PER_IN_LER, identifying the person relationship that owns the designation.
• BNF_PERSON_ID — the person designated as beneficiary.
• PRMRY_CNTNGNT_CD — primary contingent code indicating the tier of beneficiary.
• TTEE_PERSON_ID — the trustee person associated with the designation.
• PCT_DSGD_NUM — the percentage of benefit designated.
• AMT_DSGD_VAL / AMT_DSGD_UOM — the designated amount and its unit of measure.
• DSGN_STRT_DT / DSGN_THRU_DT — the designation's own start and end dates.
• PRTT_ENRT_RSLT_ID — reference to the participation/enrollment result that drives the designation.
• BUSINESS_GROUP_ID, ORGANIZATION_ID — multi-tenant and HR organization context.
• ADDL_INSTRN_TXT — free-form additional instructions.
• PBN_ATTRIBUTE_CATEGORY and PBN_ATTRIBUTE1–30 — the DFF/descriptive flexfield segment for extensible beneficiary attributes.

The business-key candidate is the primary key combination itself; PL_BNF_ID alone is the surrogate, while the date-effective pair provides the temporal uniqueness.

Common Use Cases and Queries

Typical use cases include beneficiary reporting for a plan, validation of percentage allocations across multiple beneficiaries, and audits of life-event driven designation changes.

• Fetching the current designation for a person:

SELECT pl_bnf_id, bnf_person_id, pct_dsgd_num, prmry_cntngnt_cd
FROM ben_pl_bnf_f
WHERE per_in_ler_id = :p_per_in_ler_id
  AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

• Summing designated percentages to detect over-allocation (should not exceed 100):

SELECT per_in_ler_id, SUM(pct_dsgd_num)
FROM ben_pl_bnf_f
WHERE TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date
GROUP BY per_in_ler_id
HAVING SUM(pct_dsgd_num) > 100;

• Historical/as-of analysis using the datetrack pair, and extraction of DFF values via PBN_ATTRIBUTE_CATEGORY and PBN_ATTRIBUTE1–30.

Related Objects

• BEN_PER_IN_LER — referenced via BEN_PL_BNF_F.PER_IN_LER_ID; the person relationship that anchors the designation.
• BEN_PRTT_ENRT_RSLT_F — referenced by PRTT_ENRT_RSLT_ID; the enrollment result driving the beneficiary record.
• PER_ALL_PEOPLE_F — resolved through BNF_PERSON_ID and TTEE_PERSON_ID for person details.
• BEN_PL_BNF_F_PK — the unique index enforcing the composite primary key.
• BEN_PL_BNF and related beneficiary views/APIs — for user-facing and programmatic access to designation data.