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.