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 |
