Oracle Advance Benefits

In advanced benefits all the data is stored on person in life event basis (BEN_PER_IN_LER) which is a combination of person and life event.

So when ever any event occur in a person life it is trigger as a potential life event. After successful processing of life event life event data moves to person in life event which will further has transaction data attached to the life event like enrollments, rates, beneficiaries or dependents.

e.g like a person hires and add dependent at the same time there will be two life events "New Hire" and "Add Dependent". These are both potential life events. Once you run the participation process or run the life events only one might be processed (the way you set ur collapsing rules).

1. Enrollment Results

SELECT *

FROM ben_prtt_enrt_rslt_f pen

WHERE business_group_id = p_business_group_id

AND p_effective_date BETWEEN pen.enrt_cvg_strt_dt AND pen.enrt_cvg_thru_dt

AND pen.enrt_cvg_thru_dt <= pen.effective_end_date

AND pen.prtt_enrt_rslt_stat_cd IS NULL

AND pen.sspndd_flag = 'N'

AND pen.person_id = p_person_id

Same query will give you coverage amount stored as bnft_amt in the table.

2. Enrollment Rates

For each enrollment you can have different rates. Rates are store in table ben_prtt_rt_val

you can use the following query for rates

SELECT *

FROM ben_prtt_rt_val

WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;

But here main things to take care is rt_strt_dt and rt_end_dt. rt_strt_dt will always be less than equal to rt_end_date for a recurring rate but rt_strt_dt and rt_end_dt are equal when rt is non-recurring.

3. Eligibility Results

These results are store for each life event in person record. Also in this table you will find if a particular enrollments is set for default or auto enrollment with dflt_flag and auto_enrt_flag.

SELECT epe.*

FROM ben_elig_per_elctbl_chc epe,

ben_per_in_ler pil

WHERE pil.person_id = p_person_id

AND epe.per_in_ler_id = pil.per_in_ler_id;

4. Enrolled Dependents

SELECT *

FROM ben_elig_cvrd_dpnt_f dpnt,

ben_prtt_enrt_rslt_f pen

WHERE dpnt.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id

AND EXISTS (SELECT pil.per_in_ler_id

FROM ben_per_in_ler pil

WHERE pil.per_in_ler_id = pen.per_in_ler_id

AND pil.business_group_id = p_business_group_id

AND pil.per_in_ler_stat_cd NOT IN ('VOIDD','BCKDT'))

AND p_effective_date BETWEEN dpnt.cvg_strt_dt AND Nvl(dpnt.cvg_thru_dt,p_effective_date)

AND (Nvl(dpnt.cvg_thru_dt,p_effective_date) <= dpnt.effective_end_date

OR p_effective_date BETWEEN dpnt.effective_start_date AND Nvl(dpnt.effective_end_date,p_effective_date))

AND dpnt.dpnt_person_id = p_person_id

In above query you can dpnt_person_id is the dependent. You can see the enrolled person in ben_prtt_enrt_rslt_f table.

5. Pending Action Items

SELECT *

FROM ben_prtt_enrt_rslt_f pen,

ben_prtt_enrt_actn_f pea,

ben_per_in_ler pil

WHERE pen.prtt_enrt_rslt_id = pea.prtt_enrt_rslt_id

AND pil.per_in_ler_id (+) = pea.per_in_ler_id

AND (pil.per_in_ler_stat_cd NOT IN ('VOIDD','BCKDT')

OR pil.per_in_ler_stat_cd IS NULL)

AND pen.sspndd_flag = 'Y'

AND p_effective_date BETWEEN pen.enrt_cvg_strt_dt AND pen.enrt_cvg_thru_dt

AND pen.enrt_cvg_thru_dt <= pen.effective_end_date

AND pen.prtt_enrt_rslt_stat_cd IS NULL

Life Event:: Life event is any change in the life of an employee. that may be new hire, marriage, child, or even termination. All these events, in one or other organization, effect benefits given to employee. Any life event that can be processed is a Potential Life Event. Once we Process a potential Life event it is called Person in Life Event. Person in Life event is stored in BEN_PER_IN_LER. All Benefits data is stored with Primary key of this table (i.e. PER_IN_LER_ID), like eligibility records for each processed life events, enrollments, rate values, etc.

Plan: Any Benefit given to employee is defined as a Plan. It may be Medical Insurance, Vision/ Dental Insurance, 401 K Saving Plan, Car Insurance etc. A Program is a group of plans that have similar properties, like when to enroll, how to enroll etc.

Enrollment Requirements: For each Program or Plan we define Enrollment Requirements. These definitions tells the processes, when to start the coverage or rates (deductions), what all plans will be automatically enrolled, what all will be defaulted. Setting up these are mandatory. We can defined these properties at different levels, Program, Plan, Life event for Program, Life event for Plan, Option. Lowest level always takes precedence.

Standard Rate: We can attach a monetary value or any numeric value to a plan, this may be deduction or a bonus. We define this using a standard rate. Elements are also attached to a standard rate. This is the link where payroll and Benefits are integrated.

Coverage: Coverage is basically a kind of benefit that is given to an employee, like medical insurance, dental insurance etc.

.

.

Now I will try to use all above terms to be used in a benefit, say XX_Medical_Insurance. Say customer requirement is to give medical insurance once a person joins.

1. As setup a new life event will be setup, which will be triggered when a new employee is cretaed.

2. XX_Medical_Insurance Plan should be created. Then we will define enrollment requirements such that when a life event is processed person is enrolled as first date of next pay-period and deductions are also starting on first date of next payperiod.

3. Coverage is enterable such that employee will tell, I need $50,000 coverage on my medical insurance

4. Standard Rate is dependent on coverage, so whatever coverage employee chooses accordingly amount will be deducted. Deduction amount is attached with a element.

5. Now when person is hired, life event will trigger. This will be potential life event.

6. Now once this life event is processed, Person in Life event record will be created. At this time eligibility will be calculated.

7. Now once the person enrolls into benefit, benefits will stored again Person in Life event record.