Search Results ben_bnfts_bal_f




Overview
Explore in AI Chat

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

Overview

BEN_BNFTS_BAL_F is a dated (effective-dated) table in the BEN schema, the database home of Oracle Advanced Benefits (OAB) in E-Business Suite 12.1.1 and 12.2.2. The table's documented description is "Balance for a participant for a third party vendor," identifying it as a storage structure for third-party benefit balances associated with plan participants — for example, balances supplied by external providers such as flexible spending account administrators, health savings account trustees, or retirement vendors.

From a data-modeling perspective, the metadata classifies this object as a standalone entity based on the mined foreign-key structure: no foreign keys to other tables are documented, and the only unique index is the primary key. Applying Data Vault heuristics, this suggests treating BEN_BNFTS_BAL_F as a satellite-like table, since it is descriptive, effective-dated, and keyed in part by a surrogate identifier rather than positioned as a pure hub or link. Practitioners evaluating a Data Vault or warehouse recast should note this is a heuristic suggestion only; the absence of documented FKs does not preclude logical relationships enforced at the application layer.

Key Information Stored

The table is registered with 46 columns in the ETRM 12.2.2 physical schema. The most significant include:

Under the documented constraint set, the surrogate primary key is BEN_BNFTS_BAL_F_PK over (BNFTS_BAL_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE). The ETRM metadata lists this same index as the sole business-key candidate, so no alternate unique business key is documented; in practice the business identity would typically combine vendor, participant, and balance context, but those columns are not documented as subject to a unique constraint.

Common Use Cases and Queries

Typical scenarios include reporting third-party balances for a participant's life-to-date benefits summary, reconciling vendor-reported balances against OAB records, and driving eligibility or rate calculations where an external balance (FSA election, HSA contribution) affects processing. Date-effective queries must always constrain the effective range:

  • Current balance for a balance identifier: select BNFTS_BAL_ID, BNFTS_BAL_DESC, UOM, NNMNTRY_UOM, NAME from BEN_BNFTS_BAL_F where BNFTS_BAL_ID = :p_id and trunc(sysdate) between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
  • All balances for a business group: filter on BUSINESS_GROUP_ID with the same effective-date predicate and ORDER BY NAME.
  • Audit/change tracking: query rows where LAST_UPDATE_DATE > :since, ordered by LAST_UPDATE_DATE, using the WHO columns to identify the updater.
  • Flexfield reporting: pivot BNB_ATTRIBUTE_CATEGORY / BNB_ATTRIBUTE1-30 for ad hoc extraction of vendor-specific fields.

Because the table carries effective dates and OBJECT_VERSION_NUMBER, extracts should join to the current-dated row and avoid overlapping ranges; legacy corrections should be applied through supported OAB APIs rather than direct DML.

Related Objects

Documented FK/PK relationship data is absent — the object is classified as standalone, so no join columns to parent or child tables are stated in the ETRM metadata. Related objects identified here are therefore contextual rather than constraint-based:

  • BEN_BNFTS_BAL_F_PK — the primary key index on (BNFTS_BAL_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE), the authoritative access path.
  • BEN.BEN_BNFTS_BAL_F — the fully qualified implementation name used in DBA and development queries.
  • Other BEN Advanced Benefits objects — participant, plan, and enrollment structures in the BEN schema — that logically supply the third-party vendor context for these balances, though no FK is documented.
  • OAB concurrent programs and PL/SQL APIs in the BEN product that populate third-party balances from vendor interfaces.
  • Custom extracts and OBIEE/OTBI-style reports consuming BUSINESS_GROUP_ID, BNFTS_BAL_ID, and the flexfield attributes.

Given the standalone classification and the limited documented relationship data, integrators should validate join semantics against the actual 12.1.1 or 12.2.2 database before assuming referential links.