BEN Dependent Eligibility Audit Log

            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