Search Results bnft_prvdr_pool_name




Overview
Explore in AI Chat

Overview

APPS.BEN_APLCN_TO_BNFT_POOL_D is a reporting view within the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite, validated as of releases 12.1.1 and 12.2.2. The view exposes the relationship between an activity base rate and the benefit provider pool to which it is assigned, presenting translated descriptive names rather than internal numeric identifiers alone. This makes it particularly suitable for reporting, extract, and integration scenarios where a readable pool name is required. The object is flagged internally by Oracle Corporation and is not published for direct customer access except through standard Oracle Applications programs; nevertheless, it is widely referenced by technical consultants building ad-hoc queries, custom OAF pages, and interface extracts against the BEN schema. Because it is a view and not a table, it carries no storage of its own, and its row population is entirely derived from the BEN base tables at query time. The "_D" suffix conventionally denotes a view that resolves foreign keys into their display or description values, and BEN_APLCN_TO_BNFT_POOL_D follows this convention by surfacing the human-readable pool name alongside the association record.

Underlying Base Objects

The ETRM dependency metadata documents that APPS.BEN_APLCN_TO_BNFT_POOL_D is defined over four referenced objects: BEN_APLCN_TO_BNFT_POOL_F, BEN_BNFT_PRVDR_POOL_F, BEN_ACTY_BASE_RT_F, and FND_USER, each resolved through APPS synonyms. The primary driving entity is BEN_APLCN_TO_BNFT_POOL_F, the base ("_F") table that stores the application-to-benefit-pool assignment record with its effective dating. The view joins to BEN_BNFT_PRVDR_POOL_F to retrieve the provider pool name, and to BEN_ACTY_BASE_RT_F to retrieve the activity base rate name. FND_USER supplies the standard WHO audit attribute for the user who last updated the row. The view is not referenced by any other database object according to the documentation, confirming that it functions as a terminal, read-only reporting layer with no downstream database dependents.

Key Columns

  • ROW_ID (ROWID) — the physical row identifier of the underlying base record.
  • APLCN_TO_BNFT_POOL_ID (NUMBER 15) — the primary key of the association between the application and the benefit provider pool.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE (DATE) — the date-effective range governing when the association is valid.
  • ACTY_BASE_RT_NAME (VARCHAR2 240) — the descriptive name of the activity base rate linked to the pool association.
  • BNFT_PRVDR_POOL_NAME (VARCHAR2 240) — the descriptive name of the benefit provider pool, the column most commonly used to satisfy the "bnft_prvdr_pool_name" search term.
  • LAST_UPDATE_DATE / LAST_UPDATED_BY — standard WHO audit columns recording the last modification date and the FND_USER.USER_ID of the modifier.

Common Use Cases and Queries

Typical usage includes reconciling pool assignments for a given effective date, exporting pool-to-rate mappings for downstream payroll or carrier feeds, and validating configuration after a patch or data load. Because the name column is already resolved, the view eliminates the need to join BEN_BNFT_PRVDR_POOL_F manually, which simplifies ad-hoc SQL and reduces the risk of missing the effective-dating join condition.

A representative query retrieving active pool assignments as of the current date is shown below:

SELECT aplcn_to_bnft_pool_id,
      bnft_prvdr_pool_name,
      acty_base_rt_name,
      effective_start_date,
      effective_end_date
  FROM apps.ben_aplcn_to_bnft_pool_d
 WHERE SYSDATE BETWEEN effective_start_date AND effective_end_date
 ORDER BY bnft_prvdr_pool_name;

To audit recent changes, the WHO columns can be combined with FND_USER to identify the modifying user, and a filter on LAST_UPDATE_DATE can isolate rows touched within a given period. Consultants should note the Oracle internal-use warning and restrict production access to supported, read-only reporting patterns.