Oracle Advanced Benefit (OAB) Queries

I will try to give the basic queries without joining with plans or options or programs. So that everyone can understand the basic underlying conditions. Later on you can add all plan and program joins to add to the reports.

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



I will come up with some queries. Please let me know what kind of queries I can add here.

Basic Definition of Oracle Advanced Benefits (OAB)Terminology

Hi Guys
I have received lot of questions regarding Benefits module. So I thought I will go through some basics terminology used in Benefits following with some details and queries.

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.


I will be coming up with various queries to find enrollments, enrollment rates, eligibility etc.