BEN Back-out Life Events Audit Log

select ppf.full_name person_full_name,
          ler.name||'('||ler.ler_id||')' life_event_name,
          ppf.person_id person_id_Master,
          ppf.national_identifier,
          ppf.date_of_birth,
          hr_general.decode_lookup('SEX',ppf.sex) gender,
          hr_general.decode_lookup('MAR_STATUS',ppf.marital_status) marital_status,
          ppt.user_person_type person_type,
          pps.date_start hire_date,
          pad.postal_code postal_code,
          job.name job_name, 
          pos.name position_name,
          gra.name grade_name,
          pap.payroll_name,
          loc.location_code,
          org.name org_name,
          org2.name legal_entity_name,
          bng.name benefit_group_name
from    per_all_people_f ppf,
           ben_benfts_grp bng,
           per_periods_of_service pps,
           per_addresses pad,
           per_all_assignments_f paf,
           per_jobs_vl job,
           hr_all_positions_f_vl pos,
           per_grades_vl gra,
           pay_all_payrolls_f pap,
           hr_all_organization_units_vl org,
           hr_all_organization_units_vl org2,
           hr_soft_coding_keyflex soft,
           hr_locations loc,
           per_person_types ppt,
           ben_ler_f ler,
                     ben_benefit_actions bac,
           ben_reporting rpt
where  bac.request_id = :p_concurrent_request_id
and      bac.business_group_id = nvl(ler.business_group_id,bac.business_group_id)
and      ler.ler_id(+) = rpt.ler_id
and      paf.person_id(+) = ppf.person_id
and      nvl(paf.primary_flag,'Y') = 'Y'
and      paf.assignment_type <> 'C'
and      bac.process_date
            between nvl(paf.effective_start_date,bac.process_date)
            and nvl(paf.effective_end_date,bac.process_date)
and      paf.job_id = job.job_id(+)
and      bac.process_date
            between nvl(job.date_from,bac.process_date)
            and nvl(job.date_to,bac.process_date)
and      paf.position_id = pos.position_id(+)
and      bac.process_date
            between nvl(pos.effective_start_date,bac.process_date)
            and         nvl(pos.effective_end_date,bac.process_date)
and      paf.grade_id = gra.grade_id(+)
and      bac.process_date
            between nvl(gra.date_from,bac.process_date)
            and         nvl(gra.date_to,bac.process_date)
and      paf.payroll_id = pap.payroll_id(+)
and      bac.process_date
            between nvl(pap.effective_start_date,bac.process_date)
            and         nvl(pap.effective_end_date,bac.process_date)
and      paf.location_id = loc.location_id(+)
and      paf.organization_id = org.organization_id(+)
and      bac.process_date
            between nvl(org.date_from,bac.process_date)
            and         nvl(org.date_to,bac.process_date) 
and      paf.soft_coding_keyflex_id = soft.soft_coding_keyflex_id(+)
and      org2.organization_id(+) = soft.segment1
and      bac.process_date
            between nvl(ler.effective_start_date,bac.process_date)
            and         nvl(ler.effective_end_date,bac.process_date)
and     rpt.person_id = ppf.person_id
and      bac.business_group_id = ppf.business_group_id +0
and      bac.process_date
            between ppf.effective_start_date
            and         ppf.effective_end_date
and      ppf.benefit_group_id = bng.benfts_grp_id(+)
and      ppf.person_type_id = ppt.person_type_id
and      ppt.business_group_id = ppf.business_group_id
and      pps.person_id(+) = ppf.person_id
and      ppf.business_group_id = nvl(pps.business_group_id,
                                                         ppf.business_group_id)
and      pad.person_id(+) = ppf.person_id
and      nvl(pad.primary_flag,'Y') = 'Y'
and      bac.process_date
            between nvl(pad.date_from,bac.process_date)
            and        nvl(pad.date_to,bac.process_date)    
and      bac.benefit_action_id= rpt.benefit_action_id
and      rpt.rep_typ_cd='LFBO' 
order    by ppf.full_name