Results for “ben_bnfts_bal_d”

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

Overview

BEN_BNFTS_BAL_D is a reporting view owned by the APPS schema within the BEN (Advanced Benefits) product family of Oracle E-Business Suite, available in releases 12.1.1 and 12.2.2. The view presents descriptive and translated information for benefit balance definitions maintained in the underlying base table BEN_BNFTS_BAL_F. Its purpose is to expose human-readable values in place of coded identifiers: lookup meanings, currency names, and currency unit-of-measure descriptions are resolved at query time so that reports, concurrent programs, and integrations do not need to replicate the decode logic against HR_LOOKUPS and FND_CURRENCIES_TL. The _D suffix follows the Oracle Applications convention for descriptive (denormalized, translated) views built over a _F or _B base entity. The object carries a status of VALID in the ETRM repository; the documented description is marked "Retrofitted," indicating the view was brought forward into the current data model rather than newly authored for this release.

Underlying Base Objects

The view definition joins the following documented base objects:

• BEN_BNFTS_BAL_F (SYNONYM) — the primary source table holding benefit balance definition rows. It supplies BNFTS_BAL_ID, the effective date range, NAME, BNFTS_BAL_DESC, BNFTS_BAL_USG_CD, NNMNTRY_UOM, UOM, and audit columns.
• HR_LOOKUPS (VIEW) — joined twice with outer joins: once on LOOKUP_TYPE = 'BEN_BNFTS_BAL_USG' using BNFTS_BAL_USG_CD, and once on LOOKUP_TYPE = 'BEN_NNMNTRY_UOM' using NNMNTRY_UOM.
• FND_CURRENCIES_TL (SYNONYM) — outer-joined on CURRENCY_CODE = BNB.UOM to supply the currency name.
• FND_USER (SYNONYM) — outer-joined on USER_ID = LAST_UPDATED_BY to resolve the updating user.
• HR_API (PACKAGE) — a documented referenced object, typically supporting date-tracked (effective-dated) access to the underlying entity rather than appearing in the view's SELECT text.

All joins against FND_USER, HR_LOOKUPS, and FND_CURRENCIES_TL are outer joins, ensuring that a balance definition row is returned even when a lookup code, currency, or user identifier cannot be resolved.

Key Columns

• ROW_ID — the ROWID of the underlying BEN_BNFTS_BAL_F row, used for direct row addressing.
• BNFTS_BAL_ID — primary key of the benefit balance definition.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-tracked validity window of the definition.
• NAME — user-defined name of the balance.
• BNFTS_BAL_USG_MEANING — decoded meaning of the balance usage lookup (BEN_BNFTS_BAL_USG).
• BNFTS_BAL_DESC — free-text description of the balance; this is the column most commonly targeted by searches such as "bnfts_bal_desc."
• UOM_NAME — the currency name resolved from FND_CURRENCIES_TL.
• NNMNTRY_UOM_MEANING — decoded meaning of the monetary unit-of-measure lookup (BEN_NNMNTRY_UOM).
• LAST_UPDATE_DATE / LAST_UPDATED_BY — standard audit columns, the latter joined to FND_USER.

Common Use Cases and Queries

Typical usage includes validating configuration of benefit balances, building extracts that require display values rather than codes, and troubleshooting description text associated with a balance definition. Because the view is not date-filtered, callers must supply their own effective-date predicate.

• Listing all balances with descriptive attributes:

  SELECT bnbts_bal_id, name, bnbts_bal_desc,
         bnbts_bal_usg_meaning, uom_name, nnmntry_uom_meaning
  FROM   apps.ben_bnfts_bal_d
  ORDER  BY name;

• Searching on the description text:

  SELECT bnbts_bal_id, name, bnbts_bal_desc
  FROM   apps.ben_bnfts_bal_d
  WHERE  UPPER(bnbts_bal_desc) LIKE UPPER('%&search_text%');

• Restricting to definitions effective on a given date:

  SELECT bnbts_bal_id, name, bnbts_bal_desc
  FROM   apps.ben_bnfts_bal_d
  WHERE  TRUNC(:p_date) BETWEEN effective_start_date AND effective_end_date;

Join back to BEN_BNFTS_BAL_F on BNFTS_BAL_ID when additional undisclosed columns of the base entity are required.