Search Results bnfts_bal

Overview

APPS.BEN_PER_BNFTS_BAL_D is a denormalized reporting view within the Oracle EBS Benefits (BEN) module. It presents per-person benefits balance records—the accumulated value a participant has accrued against a defined benefits balance such as a vacation, sick leave, or other accrual plan—alongside the balance definition name, the participant's full name, and audit information identifying who last modified the record. The "_D" suffix follows the Oracle Applications convention for a descriptive (denormalized) view, indicating that it is intended primarily for inquiry screens, reports, and extracts rather than for direct transactional maintenance.

In the Oracle EBS 12.1.1 and 12.2.2 reporting stacks, this view is typically consumed by Oracle Reports, BI Publisher data templates, and custom SQL extracts that need human-readable benefits balance information without performing the multi-table joins themselves. It is not a table and cannot be written to directly; all data changes must be made against the underlying _F tables through the standard Benefits forms or APIs.

Underlying Base Objects

The view is defined over four documented base objects, all surfaced through APPS synonyms:

• BEN_PER_BNFTS_BAL_F — the per-person benefits balance table, aliased PBB. It supplies the balance row, its surrogate key, effective dates, accumulated value, and audit columns. This is the driving table.
• BEN_BNFTS_BAL_F — the benefits balance definition table, aliased BNFTS_BAL. It supplies the balance name and is joined on BNFTS_BAL_ID with an outer join, so a balance row survives even when the definition cannot be resolved.
• PER_ALL_PEOPLE_F — the person/assignment dated table, aliased PERSON. It supplies FULL_NAME and is joined on PERSON_ID.
• FND_USER — the application user table, aliased FUSER. It is joined with an outer join on LAST_UPDATED_BY to resolve the user identifier.

A key characteristic of the view is that its person join is not a simple equality on PERSON_ID. The predicate PBB.EFFECTIVE_START_DATE BETWEEN PERSON.EFFECTIVE_START_DATE AND PERSON.EFFECTIVE_END_DATE restricts the person row to the version whose effective date range contains the balance row's effective start date, ensuring that the name returned is the one in force at the time the balance took effect. Because the balance definition and user joins are outer joins (marked with the Oracle (+) syntax), the view returns rows even when no matching definition or user exists.

Key Columns

• ROW_ID — the ROWID of the BEN_PER_BNFTS_BAL_F row, useful as a unique handle for updates and for building row-level navigation in forms or Discoverer workbooks.
• PER_BNFTS_BAL_ID — the primary key of the per-person benefits balance record.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the dated range governing the balance record's validity.
• VAL — the accumulated numeric balance value for the participant.
• NAME — the descriptive name of the benefits balance definition from BEN_BNFTS_BAL_F (for example, a leave accrual plan name).
• FULL_NAME — the person's full name from PER_ALL_PEOPLE_F.
• LAST_UPDATE_DATE / LAST_UPDATED_BY — audit columns; LAST_UPDATED_BY can be resolved to a user through FND_USER.

Common Use Cases and Queries

Typical uses include person-level accrual balance inquiries, period-end leave liability reporting, and data extracts feeding external payroll or time-and-labor systems. A representative query listing current balances for a specific balance definition is:

SELECT p.per_bnfts_bal_id,
       p.full_name,
       p.name            balance_name,
       p.val             balance_value,
       p.effective_start_date,
       p.effective_end_date
FROM   apps.ben_per_bnfts_bal_d p
WHERE  p.name = :balance_name
AND    TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
ORDER  BY p.full_name;

A second common pattern resolves the audit trail for reconciliation purposes:

SELECT p.full_name,
       p.name,
       p.val,
       p.last_update_date,
       f.user_name  last_updated_by_user
FROM   apps.ben_per_bnfts_bal_d p,
       apps.fnd_user f
WHERE  f.user_id = p.last_updated_by
AND    p.per_bnfts_bal_id = :per_bnfts_bal_id;

Because the dated person join restricts results to the person version matching the balance effective start date, queries should always filter on the balance effective dates to avoid ambiguity. For high-volume extracts, joining directly to the base _F tables and applying the same effective-date predicates is preferable, since the view's row-by-row dated join can be costly on large person populations.