select
a.person_id person_id1,
pgm.name dpnt_pgm_name,
pl.name dpnt_pl_name,
opt.name dpnt_opt_name,
b.full_name dpnt_name,
a.enrt_cvg_strt_dt dpnt_strt_dt,
a.enrt_cvg_thru_dt dpnt_thru_dt,
b.date_of_birth date_of_birth,
HR_GENERAL.DECODE_LOOKUP('CONTACT',a.contact_typ_cd) relationship,
HR_GENERAL.DECODE_LOOKUP('STUDENT_STATUS',b.student_status) student_status,
hrl3.meaning disability_status
from
per_all_people_f b,
ben_pgm_f pgm,
ben_pl_f pl,
ben_oipl_f oipl,
ben_opt_f opt,
hr_lookups hrl3
, ben_batch_dpnt_info a
where a.benefit_action_id = :cp_benefit_action_id
and a.dpnt_person_id = b.person_id
and :cp_process_date between
b.effective_start_date and b.effective_end_date
and a.pgm_id = pgm.pgm_id(+)
and :cp_process_date between
nvl(pgm.effective_start_date, :cp_process_date) and
nvl(pgm.effective_end_date,:cp_process_date)
and a.pl_id = pl.pl_id(+)
and :cp_process_date between
nvl(pl.effective_start_date, :cp_process_date) and
nvl(pl.effective_end_date, :cp_process_date)
and a.oipl_id = oipl.oipl_id(+)
and :cp_process_date between
nvl(oipl.effective_start_date, :cp_process_date) and
nvl(oipl.effective_end_date, :cp_process_date)
and oipl.opt_id = opt.opt_id(+)
and :cp_process_date between
nvl(opt.effective_start_date, :cp_process_date) and
nvl(opt.effective_end_date,:cp_process_date)
and a.actn_cd != 'DEL'
and hrl3.lookup_type(+)='YES_NO'
and hrl3.lookup_code(+)=b.registered_disabled_flag |
