BEN Default Enrollment Audit Log

select 
a.person_id,
pgm.name                dd_pgm_name,
pl.name                    dd_pl_name,
opt.name                  dd_opt_name,
b.full_name               dd_dpnt_name,
a.enrt_cvg_strt_dt    dd_dpnt_strt_dt,
a.enrt_cvg_thru_dt   dd_dpnt_thru_dt,
pgm.name || ' - ' || pl.name || ' - ' || opt.name comp_name
from ben_batch_dpnt_info a,
        per_people_f b,
        ben_benefit_actions c,
        ben_pgm_f pgm,
        ben_pl_f pl,
        ben_oipl_f oipl,
        ben_opt_f opt
where c.request_id = :P_CONCURRENT_REQUEST_ID
and  c.benefit_action_id = a.benefit_action_id
and   a.dpnt_person_id = b.person_id
and   c.process_date between 
               b.effective_start_date and b.effective_end_date 
and a.pgm_id = pgm.pgm_id(+)
and c.process_date between
          nvl(pgm.effective_start_date, c.process_date) and nvl(pgm.effective_end_date,c.process_date)
and a.pl_id = pl.pl_id(+)
and c.process_date between
          nvl(pl.effective_start_date, c.process_date) and nvl(pl.effective_end_date,c.process_date)
and a.oipl_id = oipl.oipl_id(+)
and c.process_date between
          nvl(oipl.effective_start_date, c.process_date) and nvl(oipl.effective_end_date,c.process_date)
and oipl.opt_id = opt.opt_id(+)
and c.process_date between
          nvl(opt.effective_start_date, c.process_date) and nvl(opt.effective_end_date,c.process_date)
and a.actn_cd = 'DEL'
 and a.person_id=:person_id5 
 and pgm.name || ' - ' || pl.name || ' - ' || opt.name=:comp_name_M2