Search Results ben_actl_prem_f_pk

Overview

The BEN_ACTL_PREM_F table is a core data object within the Oracle E-Business Suite Advanced Benefits (BEN) module. It serves as the primary repository for storing premium information associated with benefit plans and their specific options. This table is structured as a date-effective (or date-tracked) table, as indicated by the '_F' suffix and the presence of EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns in its primary key. This design enables the maintenance of a historical record of premium changes over time, which is critical for accurate benefits administration, retroactive calculations, and auditing. Its role is to provide the system with the definitive source of premium cost data for enrolled employees and their dependents.

Key Information Stored

The table's central entity is the premium record, uniquely identified by the ACTL_PREM_ID. The date-effective nature of the table is governed by the EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns, which define the period for which a given premium record is valid. A critical foreign key relationship is established via the COMP_LVL_FCTR_ID column, which links the premium to a compensation level factor defined in the BEN_COMP_LVL_FCTR table. This linkage is essential for determining how premiums are calculated based on an employee's compensation. While the provided metadata is concise, typical columns in such a table would also include references to the specific plan and option (PLAN_ID, OPT_ID), the premium amount or rate, and potentially the calculation frequency.

Common Use Cases and Queries

This table is central to benefits costing, enrollment processing, and payroll deduction feeds. Common operational and reporting scenarios include generating cost projections during open enrollment, calculating actual premium deductions for payroll, and auditing premium history. A typical query would join this table to plan and participant enrollment tables to retrieve the current premium for an employee. For example, to find active premiums for a specific plan, a SQL pattern would filter on the effective dates and the relevant plan identifier.

• SELECT * FROM ben_actl_prem_f WHERE sysdate BETWEEN effective_start_date AND effective_end_date AND plan_id = :p_plan_id;
• Reporting on premium changes over time by querying historical records where ACTL_PREM_ID is constant but effective dates differ.
• Joining to BEN_COMP_LVL_FCTR via COMP_LVL_FCTR_ID to understand the compensation-based pricing tier applied to a premium.

Related Objects

The primary documented relationship for the BEN_ACTL_PREM_F table is a foreign key dependency on the BEN_COMP_LVL_FCTR table. This relationship is defined by the COMP_LVL_FCTR_ID column in BEN_ACTL_PREM_F, which references the corresponding key in BEN_COMP_LVL_FCTR. This link is fundamental, as it ties the premium amount to the specific compensation level factor used in its calculation. Given its central function, this table is also logically related to core benefits objects such as benefit plans (BEN_PL_F), plan types (BEN_PL_TYP_F), and participant enrollments (BEN_PRTT_ENRT_RSLT_F), though these specific foreign keys are not detailed in the provided excerpt. The table's primary key constraint is named BEN_ACTL_PREM_F_PK.