Search Results ben_beneficiaries_f




Overview
Explore in AI Chat

Overview

BEN_BENEFICIARIES_F is a date-tracked (datetrack) table owned by the HR schema in Oracle E-Business Suite, belonging to the PER – Human Resources product (Advanced Benefits, or OAB). It stores the beneficiary designations associated with a benefit program, capturing the individuals or entities designated to receive benefits under a participant's enrollment. The _F suffix confirms a DateTrack-enabled table, meaning every row is bounded by EFFECTIVE_START_DATE and EFFECTIVE_END_DATE and subject to the standard Oracle HRMS date-effectivity model used throughout the Benefits and Human Resources modules.

Under the heuristic Data Vault classification mined from the foreign-key structure, the object is designated standalone. In Data Vault modeling terms, this suggests it does not cleanly resolve into a pure hub, link, or satellite: it carries its own surrogate key (BENEFICIARY_ID) while also holding descriptive attributes (such as BENEFIT_LEVEL and PROPORTION) and external references (SOURCE_ID, SOURCE_TYPE, ELEMENT_ENTRY_ID). A data modeler would typically treat it as a link-style component with satellite attributes rather than a distinct hub.

Key Information Stored

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

  • BENEFICIARY_ID — the surrogate identifier for a beneficiary record; the lead column of the primary key.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the DateTrack validity window; both are part of the primary key.
  • ELEMENT_ENTRY_ID — the pay-element or benefit-element entry associated with the beneficiary, linking the designation to a specific compensation or benefit component.
  • BENEFIT_LEVEL — the tier or level of coverage or benefit assigned to the beneficiary.
  • PROPORTION — the fractional or percentage share allocated to the beneficiary.
  • SOURCE_ID and SOURCE_TYPE — the originating record and its type, identifying the source object from which the beneficiary derives.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — the standard Oracle HRMS Who columns for auditing and concurrency.

The surrogate primary key is BEN_BENEFICIARIES_F_PK (BENEFICIARY_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE). A business-key candidate is defined by the unique index BEN_BENEFICIARIES_F_UK2 over (SOURCE_TYPE, SOURCE_ID, ELEMENT_ENTRY_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE), which enforces one beneficiary designation per source/element-entry combination per effective period.

Common Use Cases and Queries

Typical reporting scenarios include listing active beneficiary designations for a participant's benefit enrollment, calculating proportional allocations across multiple beneficiaries, and reconciling beneficiaries to pay elements or element entries. A representative query retrieving current designations is:

  • SELECT beneficiary_id, benefit_level, proportion, element_entry_id FROM hr.ben_beneficiaries_f WHERE TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
  • Join to element entries via ELEMENT_ENTRY_ID to trace a beneficiary to the underlying payroll or benefit element.
  • Filter by SOURCE_TYPE and SOURCE_ID to isolate beneficiaries originating from a particular source record.

Because the table is DateTrack-enabled, queries that omit effective-date predicates will return all historical versions; reporting logic should always constrain on EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.

Related Objects

The following objects are commonly referenced alongside this table; joins are grounded in the documented key columns:

  • HR.PAY_ELEMENT_ENTRIES_F — joined on ELEMENT_ENTRY_ID to resolve the associated element entry.
  • HR.BEN_PRTT_ENRT_RSLT_F — participant enrollment results, related through the SOURCE_ID/SOURCE_TYPE reference.
  • HR.BEN_BENEFICIARIES view — the user-facing view over the _F table exposing current (non-dated) rows.
  • PER_ALL_PEOPLE_F — the person records behind beneficiary designations, linked via element-entry ownership.
  • HR.BEN_BENFTS_GRPS_F — benefit groups and programs that organize beneficiary designations.
  • APIs such as BEN_BENEFICIARY_API — the supported programmatic interface for inserting and maintaining beneficiary records without direct table DML.

Direct DML against BEN_BENEFICIARIES_F is discouraged in favor of the Benefits API, given the DateTrack integrity requirements enforced by the HRMS date-effectivity model.