Search Results ben_per_bnfts_bal_f




Overview
Explore in AI Chat

Overview

BEN_PER_BNFTS_BAL_F is a datable (date-effective) table in the BEN (Advanced Benefits) product schema of Oracle E-Business Suite, available in both 12.1.1 and 12.2.2. It stores benefits balance information for a person, providing the temporal, per-person record set against which benefit coverage, eligibility, and plan-year accumulation logic are evaluated. Each row represents a person's benefit balance state as of a defined effective period, and the table is designed so that historical changes are preserved rather than overwritten.

The table is documented with a physical schema of 44 columns and a single documented unique index, BEN_PER_BNFTS_BAL_F_PK, defined on the composite key (PER_BNFTS_BAL_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE). From a Data Vault modeling perspective, the metadata's heuristic classification is standalone — no foreign-key relationships were mined. In Data Vault terms this suggests treating the table as a self-contained satellite-like structure (or, given the absence of detected parent links, as an independent hub/satellite pairing driven by its own key), where the effective-dated nature supplies the temporal grain and PER_BNFTS_BAL_ID supplies the logical identity of the balance record.

Key Information Stored

The table's durable identity is carried by the surrogate primary key column PER_BNFTS_BAL_ID, combined with the date-effective range columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. These three columns constitute the documented unique index and are the only business-key candidates present in the ETRM metadata.

  • PER_BNFTS_BAL_ID — surrogate identifier for the person benefits balance record.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the effective-dated validity window; together with the ID they form the composite primary key.
  • BNFTS_BAL_ID — reference to the benefits balance definition against which the person-level balance is recorded.
  • PERSON_ID — the person for whom the benefit balance is maintained.
  • BUSINESS_GROUP_ID — the business group (operating unit / HR security grouping) that owns the record.
  • PBB_ATTRIBUTE_CATEGORY and PBB_ATTRIBUTE1 through PBB_ATTRIBUTE30 — a thirty-one-column descriptive flexfield block used to capture client-specific balance attributes without structural change.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard EBS WHO columns providing audit lineage.
  • OBJECT_VERSION_NUMBER — optimistic locking control used by the Oracle Forms/ADF layer during concurrent updates.
  • VAL — the stored balance value.

The surrogate key distinguishes physical rows, while the composite unique index (ID + effective dates) governs temporal uniqueness of the business record.

Common Use Cases and Queries

Typical usage centers on point-in-time balance reporting, plan-year eligibility checks, and reconciliation of benefit balances against enrollment and coverage records. Because the table is date-effective, queries must constrain the effective window to avoid returning multiple historical versions of the same balance.

  • Current balance by person: SELECT person_id, bnfts_bal_id, val FROM ben.ben_per_bnfts_bal_f WHERE person_id = :p_person AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
  • As-of-date (historical) balance: substitute the desired date for SYSDATE in the BETWEEN predicate.
  • Business-group roll-up: join on BUSINESS_GROUP_ID and aggregate VAL for a benefits balance reporting extract.
  • Concurrent-update diagnostics: compare OBJECT_VERSION_NUMBER against the expected value to detect stale edits.
  • Flexfield reporting: query PBB_ATTRIBUTE_CATEGORY with the relevant PBB_ATTRIBUTEn columns to surface client-defined balance attributes.

Related Objects

The ETRM metadata records no FK relationships for this table (classification: standalone), so related objects are identified by the documented column semantics rather than by declared constraints.

  • PER_ALL_PEOPLE_F — joined on PERSON_ID to resolve person details.
  • PER_BUSINESS_GROUPS — joined on BUSINESS_GROUP_ID for operating-unit context.
  • BEN_BNFTS_BAL — the benefits balance definition referenced by BNFTS_BAL_ID.
  • BEN_PER_BNFTS_BAL_F_PK — the unique index enforcing the composite identity of the row.
  • BEN_PERSON_BENEFIT_GROUPS / BEN_PER_IN_ENRLD_PL_F — person-level benefits and enrollment tables commonly correlated with balances during eligibility and coverage processing.
  • BEN_BALANCE_API — the API layer typically used to create and maintain balance records rather than direct DML.

Consumers should treat BEN_PER_BNFTS_BAL_F as the authoritative, effective-dated person balance store and always filter on the effective dates when selecting current-state data.