Search Results ben_enrt_prem




Overview
Explore in AI Chat

Overview

The BEN_ENRT_PREM table is a core data repository within the Oracle E-Business Suite Advanced Benefits (BEN) module. It serves as the primary table for storing premium information associated with benefit enrollments. When an employee elects a benefit plan, the system calculates and records the associated premium costs—which may include employee contributions, employer contributions, and any other applicable charges—in this table. Its role is critical for the financial tracking of benefit elections, feeding downstream processes such as payroll deductions, accounting, and benefits costing reports. The table's existence under the BEN schema and its foreign key relationships confirm its integral position in the enrollment data model.

Key Information Stored

The table's structure is designed to link premium details to specific enrollment records and organizational context. The primary key, ENRT_PREM_ID, uniquely identifies each premium record. Critical foreign key columns establish essential relationships: ENRT_BNFT_ID links the premium to a specific benefit enrollment record in the BEN_ENRT_BNFT table, ELIG_PER_ELCTBL_CHC_ID connects it to the employee's election choice record in BEN_ELIG_PER_ELCTBL_CHC, and BUSINESS_GROUP_ID ties the data to the organizational unit in HR_ALL_ORGANIZATION_UNITS for security and partitioning. While the provided metadata does not list all columns, typical data stored includes premium amounts, contribution rates, calculation factors, and effective dates.

Common Use Cases and Queries

This table is central to queries involving the financial aspects of benefits. Common use cases include generating reports on total benefit program costs, reconciling payroll deduction amounts, and auditing employee election costs. A typical reporting query would join BEN_ENRT_PREM to enrollment and person tables to summarize premiums by employee or plan. For example, to list an employee's enrollment premiums, one might use a SQL pattern such as:

  • SELECT per.full_name, ebnft.enrt_bnft_id, prem.* FROM ben_enrt_prem prem, ben_enrt_bnft ebnft, per_all_people_f per WHERE prem.enrt_bnft_id = ebnft.enrt_bnft_id AND ebnft.person_id = per.person_id AND SYSDATE BETWEEN per.effective_start_date AND per.effective_end_date;

Data from this table is also vital for interfaces with Oracle Payroll, where premium amounts are transferred for processing as deductions or employer liabilities.

Related Objects

As indicated by the foreign key metadata, BEN_ENRT_PREM has direct, dependent relationships with several key benefits tables. Its primary parent table is BEN_ENRT_BNFT (Enrollment Benefit), which holds the core enrollment record. It also relates to BEN_ELIG_PER_ELCTBL_CHC (Eligible Per Electable Choice), which stores the employee's election decisions. The BUSINESS_GROUP_ID foreign key enforces a relationship with the HR_ALL_ORGANIZATION_UNITS table. While not listed in the excerpt, it is common for this table to be referenced by costing summary tables and may be accessed by standard benefits views or APIs such as those in the BEN_BNFT_RATE package for premium calculation logic.