Results for “ben_prtt_rt_val”

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

Overview

BEN_PRTT_RT_VAL is a core transaction table within the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the calculated or elected rate values for a participant enrolled in a compensation object, such as a benefit plan, option, or activity. Each row represents a rate instance tied to a specific enrollment result, capturing the effective period, rate type, and the resolved monetary or derived value. The table resides in the BEN schema and is defined as VALID across both releases.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign-key structure identifies BEN_PRTT_RT_VAL as a link entity. This suggests it functions primarily as a many-to-many association between participant enrollment records and rate definitions, rather than as a pure descriptive hub or satellite. The table carries 70 documented columns, reflecting the breadth of attributes required to model complex benefits rate calculations.

Key Information Stored

The surrogate primary key is PRTT_RT_VAL_ID, enforced by the unique index BEN_PRTT_RT_VAL_PK. This is the single documented business-key candidate and serves as the join anchor for dependent tables.

• PRTT_RT_VAL_ID — Surrogate primary key uniquely identifying each rate value record.
• PRTT_ENRT_RSLT_ID — Links the rate to the participant's enrollment result, establishing the core business context.
• RT_STRT_DT / RT_END_DT — Effective date range during which the stored rate applies.
• RT_TYP_CD / RT_VAL / ANN_RT_VAL — Rate type code and the associated rate value, including annualized rate where applicable.
• PER_IN_LER_ID / ENDED_PER_IN_LER_ID — Foreign keys to BEN_PER_IN_LER, identifying the active and ended participant enrollment relationships.
• COMP_LVL_FCTR_ID — References BEN_COMP_LVL_FCTR, linking the rate to a compensation level factor.
• PRTT_RT_VAL_STAT_CD — Status of the rate value record (e.g., processed, overridden).
• RT_OVRIDN_FLAG / RT_OVRIDN_THRU_DT — Indicates whether the rate was manually overridden and the date through which the override applies.
• ELCTNS_MADE_DT / DSPLY_ON_ENRT_FLAG — Election date and a flag controlling display during enrollment.
• BUSINESS_GROUP_ID — Multi-tenant partitioning key for the business group.
• OBJECT_VERSION_NUMBER — Optimistic locking column for concurrent update control.
• LAST_UPDATE_DATE / LAST_UPDATED_BY / CREATED_BY — Standard audit columns.

Thirty attribute columns (PRV_ATTRIBUTE1 through PRV_ATTRIBUTE30) provide flexible descriptive context via the DFF framework.

Common Use Cases and Queries

Typical usage centers on benefit enrollment validation, premium reconciliation, and rate auditing. A common query joins rates to enrollment results and participant enrollments:

• Retrieving active rates for a participant by joining PRTT_ENRT_RSLT_ID to the enrollment result table and filtering on SYSDATE BETWEEN RT_STRT_DT AND NVL(RT_END_DT, SYSDATE).
• Identifying overridden rates using RT_OVRIDN_FLAG = 'Y' for audit and compliance reporting.
• Aggregating ANN_RT_VAL by RT_TYP_CD for cost analysis and payroll reconciliation.
• Joining to BEN_COMP_LVL_FCTR via COMP_LVL_FCTR_ID to evaluate the impact of compensation level factors on final rates.
• Extracting DFF attributes for reporting on custom rate dimensions.

Related Objects

Several significant tables reference BEN_PRTT_RT_VAL through PRTT_RT_VAL_ID:

• BEN_ENRT_RT — Enrollment rate records consuming the rate value.
• BEN_ENRT_RT_RBV — Real-time benefit balance view rows tied to rate values.
• BEN_PRTT_RT_VAL_RBV — Real-time benefit rate value snapshots.
• BEN_CWB_STOCK_OPTN_DTLS — Stock option details referencing participant rates.
• BEN_PRTT_RT_VAL_CTFN_PRVDD — Certification data provided against a rate value.
• BEN_PER_IN_LER — Parent participant enrollment relationship table referenced by both PER_IN_LER_ID and ENDED_PER_IN_LER_ID.
• BEN_COMP_LVL_FCTR — Compensation level factor definitions referenced by COMP_LVL_FCTR_ID.