Results for “ben_ppl_grp_rt_f”

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

Overview

BEN_PPL_GRP_RT_F is a table in the BEN (Advanced Benefits) schema of Oracle E-Business Suite, holding the rates that apply to a people group. In Oracle Advanced Benefits, people groups segment the eligible population by demographic or employment criteria, and variable rates can be attached to those groups to drive contribution or premium calculations. This table therefore stores the intersection of a people group definition and the variable rate profile that governs rating for that population.

From a Data Vault modeling perspective, the provided metadata classifies this object heuristically as standalone. That classification should be treated as a modeling suggestion rather than a hard rule; the table carries both a surrogate key and a foreign key to a people group, so it behaves more like a reference or link-style structure around the people group hub and the variable rate profile.

Key Information Stored

The table is dated, with effective date control exercised through EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, which are also part of the primary key BEN_PPL_GRP_RT_F_PK together with PPL_GRP_RT_ID. PPL_GRP_RT_ID is the surrogate identifier for a rate row, while the composite of PPL_GRP_RT_ID plus the two date columns is the documented unique business-key candidate. The remaining columns of practical importance include:

• PEOPLE_GROUP_ID — foreign key to PAY_PEOPLE_GROUPS, identifying the population to which the rate applies.
• VRBL_RT_PRFL_ID — the variable rate profile used to determine the rate value.
• EXCLD_FLAG — indicates whether the row is excluded from processing.
• PGR_ATTRIBUTE_CATEGORY and PGR_ATTRIBUTE1 through PGR_ATTRIBUTE30 — descriptive flexfield segments capturing additional group-rate attributes.
• ORDR_NUM — sequencing value for ordering evaluated rate rows.
• BUSINESS_GROUP_ID — the operating unit or business group context.
• OBJECT_VERSION_NUMBER — optimistic locking indicator for concurrent updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN — the standard Oracle EBS audit columns.

In total the documented physical schema contains 45 columns in ETRM 12.2.2.

Common Use Cases and Queries

Typical uses include validating that a people group has the correct variable rate profile in effect, reproducing contribution or premium calculations, and reporting on rate configuration changes over time. Because the table is date-effective, queries must filter on the effective window.

SELECT ppl_grp_rt_id, people_group_id, vrbl_rt_prfl_id,
       effective_start_date, effective_end_date, ordr_num
FROM   ben.ben_ppl_grp_rt_f
WHERE  people_group_id = :p_people_group_id
AND    TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

A second pattern joins to PAY_PEOPLE_GROUPS to resolve the group name and to BEN_VRBL_RT_PRFL for the rate profile description, supporting configuration audit reports or reconciliation of expected versus actual deductions.

Related Objects

The most significant related objects and the join columns that connect them are:

• PAY_PEOPLE_GROUPS — joined via BEN_PPL_GRP_RT_F.PEOPLE_GROUP_ID = PAY_PEOPLE_GROUPS.PEOPLE_GROUP_ID.
• BEN_VRBL_RT_PRFL_F — the variable rate profile referenced through VRBL_RT_PRFL_ID.
• BEN_PPL_GRP_RT_F_PK — the unique index enforcing the primary key on PPL_GRP_RT_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE.
• BEN_PPL_GRP_RT_F_TL — the translatable counterpart carrying language-specific descriptive text, where present.
• BEN_PERSON_PPL_GRP_F — the person-to-people-group assignment used to resolve which individuals fall under the rated group.
• BEN_PPL_GRP_RT_F records are commonly consumed by the Advanced Benefits rating and benefit calculation API family, including the BEN_RATE and BEN_CALCULATION routines.