Program and Plan Enrollment Requirement ad-hoc Queries - Oracle Advanced Benefits(OAB)

Program Enrollment Requirement Setup Query

SELECT bpf.NAME, blf.NAME life_event_name,
       blr.dys_aftr_end_to_dflt_num days_after_enrt_prd_for_dflt,
       hr_general.decode_lookup
                      ('BEN_CLS_ENRT_DT_TO_USE',
                       blr.cls_enrt_dt_to_use_cd
                      ) close_enrollment_date_to_use,
       hr_general.decode_lookup
                           ('BEN_ENRT_CVG_STRT',
                            blr.enrt_cvg_strt_dt_cd
                           ) enrollment_cvg_start_date,
       hr_general.decode_lookup
                              ('BEN_ENRT_CVG_END',
                               blr.enrt_cvg_end_dt_cd
                              ) enrollment_cvg_end_date,
       hr_general.decode_lookup ('BEN_RT_STRT',
                                 blr.rt_strt_dt_cd
                                ) rate_start_date,
       hr_general.decode_lookup ('BEN_RT_END',
                                 blr.rt_end_dt_cd) rate_end_date,
       hr_general.decode_lookup
                       ('BEN_ENRT_PERD_STRT',
                        blr.enrt_perd_strt_dt_cd
                       ) enrollment_period_start_date,
       hr_general.decode_lookup
                          ('BEN_ENRT_PERD_END',
                           blr.enrt_perd_end_dt_cd
                          ) enrollment_period_end_date
  FROM ben_lee_rsn_f blr,
       ben_popl_enrt_typ_cycl_f bpe,
       ben_pgm_f bpf,
       ben_ler_f blf
 WHERE bpe.popl_enrt_typ_cycl_id = blr.popl_enrt_typ_cycl_id
   AND bpf.pgm_id = bpe.pgm_id
   AND blf.ler_id = blr.ler_id;

Plan Enrollment Requirement Setup Query
  
SELECT bplf.NAME, blf.NAME life_event_name,
       blr.dys_aftr_end_to_dflt_num days_after_enrt_prd_for_dflt,
       hr_general.decode_lookup
                      ('BEN_CLS_ENRT_DT_TO_USE',
                       blr.cls_enrt_dt_to_use_cd
                      ) close_enrollment_date_to_use,
       hr_general.decode_lookup
                           ('BEN_ENRT_CVG_STRT',
                            blr.enrt_cvg_strt_dt_cd
                           ) enrollment_cvg_start_date,
       hr_general.decode_lookup
                              ('BEN_ENRT_CVG_END',
                               blr.enrt_cvg_end_dt_cd
                              ) enrollment_cvg_end_date,
       hr_general.decode_lookup ('BEN_RT_STRT',
                                 blr.rt_strt_dt_cd
                                ) rate_start_date,
       hr_general.decode_lookup ('BEN_RT_END',
                                 blr.rt_end_dt_cd) rate_end_date,
       hr_general.decode_lookup
                       ('BEN_ENRT_PERD_STRT',
                        blr.enrt_perd_strt_dt_cd
                       ) enrollment_period_start_date,
       hr_general.decode_lookup
                          ('BEN_ENRT_PERD_END',
                           blr.enrt_perd_end_dt_cd
                          ) enrollment_period_end_date,
       formula_name
  FROM ben_lee_rsn_f blr,
       ff_formulas_f fff,
       ben_popl_enrt_typ_cycl_f bpe,
       ben_pl_f bplf,
       ben_ler_f blf
 WHERE bpe.popl_enrt_typ_cycl_id = blr.popl_enrt_typ_cycl_id
   AND bplf.pl_id = bpe.pl_id
   AND fff.formula_id(+) = blr.enrt_cvg_end_dt_rl
   AND blf.ler_id = blr.ler_id