Search Results ben_aplcn_to_bnft_pool_f




Overview
Explore in AI Chat

Overview

BEN_APLCN_TO_BNFT_POOL_F is a date-effective ( "_F" suffix) table in the BEN schema of Oracle Advanced Benefits (Oracle EBS 12.1.1 and 12.2.2). Its documented purpose is to show which rates are reducing the credits in a benefit pool. In practical terms, the table records the association between an activity base rate (ACTY_BASE_RT_ID) and a benefit provider pool (BNFT_PRVDR_POOL_ID), allowing the application to determine how a given rate — for example, an employee contribution, an imputed income rate, or a coverage-based rate — consumes (reduces) the credits allocated to a participant's flexible benefits pool.

The heuristic Data Vault classification mined from the FK structure is standalone. In Data Vault modeling terms this object is therefore best treated as neither a classic hub nor a link, but as a standalone reference/relationship table that can be modelled as its own entity (effectively a satellite-like or link-like construct) keyed by its own surrogate identifier. The absence of an explicit parent hub suggests that the table functions primarily as a configuration join between rates and pools, dated by effective start and end dates.

The table is registered as VALID in the BEN product module and is physically documented in ETRM 12.2.2 with 43 columns.

Key Information Stored

The documented primary key is the unique index BEN_APLCN_TO_BNFT_POOL_F_PK, comprising three columns: APLCN_TO_BNFT_POOL_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. APLCN_TO_BNFT_POOL_ID is the surrogate key that uniquely identifies a row, while the effective date pair provides the temporal (datetrack) dimension that qualifies the business key.

  • APLCN_TO_BNFT_POOL_ID — surrogate primary key identifier for the association record.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-effective range over which the rate-to-pool association is valid; together with the ID they form the primary key.
  • BNFT_PRVDR_POOL_ID — the benefit provider pool whose credits are being reduced.
  • ACTY_BASE_RT_ID — the activity base rate that reduces the pool credits.
  • BUSINESS_GROUP_ID — the legislative/business group owning the configuration row.
  • ABP_ATTRIBUTE_CATEGORY, ABP_ATTRIBUTE1 … ABP_ATTRIBUTE30 — a 30-column descriptive flexfield (DFF) block storing flexfield context and segment values used for customer-specific extension of the rate/pool mapping.
  • OBJECT_VERSION_NUMBER — optimistic locking column used by the Oracle Application Framework (OAF) and concurrent processing to detect row changes.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard WHO (audit) columns tracking who created and last modified the record.

Common Use Cases and Queries

The most common use is diagnosing why a participant's benefit pool credits do not reconcile. Reporting queries typically join the pool ID and rate ID to their respective master tables and filter by the as-of date. A representative pattern is:

  • List all rates reducing a given pool: SELECT ap.acty_base_rt_id, ap.bnft_prvdr_pool_id FROM ben.ben_aplcn_to_bnft_pool_f ap WHERE ap.bnft_prvdr_pool_id = :pool_id AND TRUNC(SYSDATE) BETWEEN ap.effective_start_date AND ap.effective_end_date;
  • Audit changes over time by comparing effective date ranges for a single APLCN_TO_BNFT_POOL_ID.
  • Validate configuration loads by checking that no overlapping date ranges exist for the same pool/rate combination.
  • Extract the descriptive flexfield context and segment values for downstream reporting on custom attributes.

Related Objects

The FK/relationship metadata classifies this object as standalone, so no direct foreign-key parents are documented. In practice, the columns below reference the following significant BEN objects: