BEN Close Enrollments Audit Log

Select 'Name:' || b.full_name FULL_NAME,
          a.person_id person_id_Master1,
          'SSN:' || a.national_identifier NATIONAL_IDENTIFIER,
          'Summary Action:' ||  substr(a.TEXT,1,360) Summary_actn,
          'Person Type:' || ppt.user_person_type person_type,
          'Life Event:' || e.name Lf_ev_name,
          --'Occurred on date:' || d.lf_evt_ocrd_dt lf_ev_dt
          'Occurred on date:' || to_char(d.lf_evt_ocrd_dt,'DD-MON-YY') lf_ev_dt		
From     ben_reporting a,
          ben_benefit_actions c,
          per_all_people_f b,
          ben_per_in_ler d,
          per_person_types ppt,
          ben_ler_f e
Where  c.request_id = :P_CONCURRENT_REQUEST_ID
and      c.benefit_action_id = a.benefit_action_id
and      a.rep_typ_cd in ( 'ERROR', 'ERROR_C', 'CLSNNOACTN', 'CLSNODEF', 'CLSNDEFNOCHG', 'CLSNDEFWCHG', 'CLSDEFNOCHG', 'CLSDEFWCHG')
and      a.person_id = b.person_id
and      b.person_type_id = ppt.person_type_id 
and      ppt.business_group_id = b.business_group_id
and      c.process_date between 
               b.effective_start_date and b.effective_end_date 
and      c.process_date between 
               e.effective_start_date and e.effective_end_date 
and      d.person_id = a.person_id
and      nvl(d.procd_dt, c.process_date) = nvl(c.process_date, d.procd_dt) 
and      d.ler_id = nvl(c.ler_id, d.ler_id) 
and      d.lf_evt_ocrd_dt = nvl(c.lf_evt_ocrd_dt, d.lf_evt_ocrd_dt) 
and      d.ler_id = e.ler_id
and      e.typ_cd not in ('GSP','COMP','SCHEDDU','ABS')
and      d.per_in_Ler_stat_cd not in ( 'BCKDT' , 'VOIDD')
order    by a.national_identifier asc