Search Results ben_age_rt_f_pk

Overview

BEN_AGE_RT_F is a transactional table in the Oracle Advanced Benefits (BEN) module that stores the rates applied to age ranges within a benefits rate structure. It functions as the operational repository linking a defined age factor to a concrete rate value for a specific effective-dated interval, allowing plan administrators to model age-banded premium and contribution schedules (for example, rates that increase at age brackets such as 25, 35, 45, and 55).

The table resides in the BEN schema and is registered as VALID in the ETRM repository for both Oracle EBS 12.1.1 and 12.2.2. Its primary key, BEN_AGE_RT_F_PK, is composed of AGE_RT_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE, confirming that records are date-tracked and that the surrogate identifier is reused across multiple effective-dated versions. The documented foreign key BEN_AGE_RT_F.AGE_FCTR_ID → BEN_AGE_FCTR anchors each rate row to its parent age factor definition.

Under a Data Vault modeling heuristic derived from the FK structure, this object is classified as satellite-leaning. This suggests that BEN_AGE_RT_F behaves as a descriptive, attribute-bearing satellite attached to the age-factor hub/link (represented by BEN_AGE_FCTR), rather than as an independent business hub. The design intent is to capture the changing rate values that qualify an age factor over time.

Key Information Stored

The table exposes 45 documented columns. The most significant include:

• AGE_RT_ID — surrogate identifier for the age-rate record; the leading component of the primary key.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-effective boundary pair that, combined with AGE_RT_ID, forms the unique business key. These support Oracle's standard date-tracked (datetrack) pattern.
• AGE_FCTR_ID — foreign key to BEN_AGE_FCTR, tying the rate to its underlying age factor.
• VRBL_RT_PRFL_ID — identifies the variable rate profile that governs how the rate is calculated or applied.
• EXCLD_FLAG — indicates whether the age rate is excluded from use in the associated calculation.
• BUSINESS_GROUP_ID — the operating business group (enterprise) that owns the record, supporting multi-tenant isolation.
• ORDR_NUM — ordering sequence used to control evaluation precedence of the rate within its grouping.
• ART_ATTRIBUTE_CATEGORY and ART_ATTRIBUTE1 through ART_ATTRIBUTE30 — a category descriptor plus thirty flexible descriptive attributes for capturing customer-specific or plan-specific rate metadata (for example, tier labels or enrollment-class qualifiers).
• Standard WHO auditing columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, and OBJECT_VERSION_NUMBER — which support concurrency control and audit trail requirements.

The surrogate primary key (AGE_RT_ID plus the effective dates) should not be confused with the business-key candidate exposed by the unique index BEN_AGE_RT_F_PK; both resolve to the same composite. Business users typically reason about an age rate through its AGE_FCTR_ID and effective-date window rather than the numeric surrogate.

Common Use Cases and Queries

Typical scenarios include validating which rates apply to a covered person given their age and a given date, auditing rate changes across effective periods, and reconciling benefit premium calculations against configuration.

• Current effective rates for an age factor:

SELECT age_rt_id, age_fctr_id, effective_start_date, effective_end_date, ordr_num
FROM   ben.ben_age_rt_f
WHERE  age_fctr_id = :p_age_fctr_id
AND    TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date
ORDER  BY ordr_num;

• As-of-date rate lookup: substitute a parameterized date for SYSDATE to reproduce a historical calculation.
• Exclusion audit: query rows where EXCLD_FLAG = 'Y' to identify rates deliberately suppressed from a profile.
• Attribute reporting: report against ART_ATTRIBUTE_CATEGORY and selected ART_ATTRIBUTEn columns to expose plan-specific metadata.
• Change tracking: compare consecutive effective-dated rows sharing an AGE_RT_ID to detect rate changes.

Related Objects

• BEN_AGE_FCTR — parent object; joined via BEN_AGE_RT_F.AGE_FCTR_ID = BEN_AGE_FCTR.AGE_FCTR_ID. Defines the age factor to which rates attach.
• BEN_VRBL_RT_PRFL_F — variable rate profile referenced through VRBL_RT_PRFL_ID; governs rate derivation.
• BEN_VRBL_RT_PRFL_F dependent rate and profile detail tables — supply the calculation context in which age rates are consumed.
• BEN_PER_AGE_FCTR and related person-level factor tables — connect calculated factors to individual participants.
• BEN_AGE_RT_F API/form handlers (Advanced Benefits rate definition UI) — the forms and PL/SQL packages that maintain this table.

Together these objects form the age-banded rating chain: factor definition, rate values, profile association, and participant application.