Results for “ben_bnft_prvdr_pool”

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

Overview

BEN_BNFT_PRVDR_POOL is a VALID database view owned by the APPS schema within the BEN (Advanced Benefits) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. The view provides a date-effective, session-aware presentation of benefit provider pool definitions. A provider pool represents a grouping of benefit providers that is used to distribute costs, coverage, and enrollment quantities across multiple providers within a benefit program or plan. The view exposes configuration that controls rounding, distribution minimums and maximums, deficit handling, exception treatment, and combination logic linking pools to plan-in-program (PLIP), plan type-in-program (PTIP), and other-in-plan-in-program (OIPLIP) constructs.

Because the view applies the FND_SESSIONS effective-date filter, it returns only the pool row that is effective for the current user session, making it suitable for runtime forms, concurrent processes, and reporting that must respect the effective-dating model. This makes it a reliable integration point for extracting pool setup without manually resolving effective date ranges.

Underlying Base Objects

The view is defined over two documented base objects, both referenced as synonyms:

• BEN_BNFT_PRVDR_POOL_F — the primary _F (date-effective) table that stores all provider pool definitions, including effective start and end dates and the descriptive, rounding, distribution, and combination attributes.
• FND_SESSIONS — the Oracle Application Object Library table used to retrieve the effective date (EFFECTIVE_DATE) associated with the current session ID via USERENV('SESSIONID').

The view text selects from the _F table aliased as BPP and applies two predicates: EFFECTIVE_START_DATE must be on or before the session effective date, and EFFECTIVE_END_DATE must be on or after it. This mirrors the standard Oracle date-effective (datetrack) pattern, ensuring only the currently effective version of each pool is returned. Note that the view does not include the _TL (translation) table, so it exposes the base-language NAME rather than a translated description.

Key Columns

• BNFT_PRVDR_POOL_ID — primary key identifying the provider pool record.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the datetrack range that governs row visibility.
• BUSINESS_GROUP_ID — the HR business group that owns the pool definition.
• NAME — the user-visible name of the provider pool.
• PGM_POOL_FLAG / USE_FOR_PGM_POOL_FLAG — indicate whether the pool is used for program-level pooling.
• PCT_RNDG_CD / VAL_RNDG_CD / PCT_RNDG_RL / VAL_RNDG_RL — rounding codes and rules applied to percentage and value distributions.
• PLIP_ID, PTIP_ID, CMBN_PLIP_ID, CMBN_PTIP_ID, CMBN_PTIP_OPT_ID, OIPLIP_ID — keys linking the pool to plan-in-program, plan type-in-program, and other plan-in-program constructs.
• MN_DSTRBL_PCT_NUM, MN_DSTRBL_VAL, MX_DSTRBL_PCT_NUM, MX_DSTRBL_VAL — minimum and maximum distributable percentages and values, with corresponding NO_*_FLAG indicators to suppress limits.
• MX_DFCIT_PCT_POOL_CRS_NUM / MX_DFCIT_PCT_COMP_NUM — maximum deficit percentages for pool and compensation cross-checks.
• EXCS_TRTMT_CD, DFLT_EXCS_TRTMT_CD, DFLT_EXCS_TRTMT_RL, RLOVR_RSTRCN_CD, AUTO_ALCT_EXCS_FLAG — exception treatment and rollover restriction settings.
• ALWS_NGTV_CRS_FLAG, USES_NET_CRS_MTHD_FLAG — control allowance for negative coverage values and use of the net cross method.
• BPP_ATTRIBUTE_CATEGORY and BPP_ATTRIBUTE1…30 — descriptive flexfield (DFF) context and segment columns.
• OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Oracle WHO columns for concurrency and audit.

Common Use Cases and Queries

The view is commonly used in benefits setup reporting, data migration validation, and integration extracts where provider pool configuration must be retrieved for the currently effective date. A typical query joins the view to related program or plan tables:

• List all currently effective provider pools for a business group: SELECT bpp.name, bpp.effective_start_date, bpp.effective_end_date FROM ben_bnft_prvdr_pool bpp WHERE bpp.business_group_id = :p_bg_id ORDER BY bpp.name.
• Retrieve distribution limits and rounding rules: SELECT name, mn_dstrbl_pct_num, mx_dstrbl_pct_num, mn_dstrbl_val, mx_dstrbl_val, pct_rndg_cd, val_rndg_cd FROM ben_bnft_prvdr_pool WHERE pgm_pool_flag = 'Y'.
• Inspect exception and deficit settings: SELECT name, excs_trtmt_cd, dflt_excs_trtmt_cd, auto_alct_excs_flag, mx_dfcit_pct_pool_crs_num, mx_dfcit_pct_comp_num FROM ben_bnft_prvdr_pool.
• Extract DFF attributes for integration: SELECT bnft_prvdr_pool_id, name, bpp_attribute_category, bpp_attribute1, bpp_attribute2 FROM ben_bnft_prvdr_pool.

Because the view resolves effective dating automatically against FND_SESSIONS, queries should be executed within an initialized EBS session context; running them outside a valid session may return no rows or unexpected results. For historical or multi-version analysis, querying BEN_BNFT_PRVDR_POOL_F directly is preferable, since the view intentionally collapses to the single session-effective row.