SELECT ppln.full_name, ppln.display_name, ppl.person_number, paf.assignment_number, le_org.name le_name, ptnl.ptnl_ler_for_per_id, pil.per_in_ler_id, ler.name ler, ptnl.ptnl_ler_for_per_stat_cd ptnl_stat, pil.per_in_ler_stat_cd pil_stat, ptnl.lf_evt_ocrd_dt, ptnl.dtctd_dt, ptnl.ntfn_dt, ptnl.voidd_dt, ptnl.procd_dt, ppos.date_start hire_date, ptnl.creation_date, ptnl.created_by, ptnl.last_update_date, ptnl.last_updated_by, pil.creation_date pil_creation_date, pil.created_by pil_created_by, pil.last_update_date pil_last_update_date, pil.last_updated_by pil_last_updated_by FROM ben_ptnl_ler_for_per ptnl, ben_per_in_ler pil, ben_ler_f_vl ler, per_all_assignments_f paf, per_all_people_f ppl, hr_all_organization_units org, per_jobs jobs, per_person_names_f_v ppln, ben_benefit_relations_f brn, per_periods_of_service ppos, hr_all_organization_units le_org WHERE ptnl.person_id = ppl.person_id AND Trunc (SYSDATE) BETWEEN ppl.effective_start_date AND ppl.effective_end_date AND ler.ler_id = ptnl.ler_id AND ptnl.benefit_relation_id = brn.benefit_relation_id AND ptnl.ptnl_ler_for_per_id = pil.ptnl_ler_for_per_id (+) AND brn.legal_entity_id = le_org.organization_id (+) AND Trunc(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date AND Trunc (SYSDATE) BETWEEN ppl.effective_start_date AND ppl.effective_end_date AND Trunc (SYSDATE) BETWEEN ler.effective_start_date AND ler.effective_end_date AND Trunc (SYSDATE) BETWEEN ppln.effective_start_date AND ppln.effective_end_date AND Trunc(SYSDATE) BETWEEN brn.effective_start_date AND brn.effective_end_date AND brn.rel_prmry_asg_id = paf.assignment_id AND ppl.person_id = paf.person_id AND paf.organization_id = org.organization_id (+) AND paf.job_id = jobs.job_id(+) AND paf.period_of_service_id = ppos.period_of_service_id AND ppl.person_id = ppln.person_id (+) ORDER BY ppl.person_number, ptnl.creation_date DESC;
SQL
x
SELECT ppln.full_name,
ppln.display_name,
ppl.person_number,
paf.assignment_number,
le_org.name le_name,
ptnl.ptnl_ler_for_per_id,
pil.per_in_ler_id,
ler.name ler,
ptnl.ptnl_ler_for_per_stat_cd ptnl_stat,
pil.per_in_ler_stat_cd pil_stat,
ptnl.lf_evt_ocrd_dt,
ptnl.dtctd_dt,
ptnl.ntfn_dt,
ptnl.voidd_dt,
ptnl.procd_dt,
ppos.date_start hire_date,
ptnl.creation_date,
ptnl.created_by,
ptnl.last_update_date,
ptnl.last_updated_by,
pil.creation_date pil_creation_date,
pil.created_by pil_created_by,
pil.last_update_date pil_last_update_date,
pil.last_updated_by pil_last_updated_by
FROM ben_ptnl_ler_for_per ptnl,
ben_per_in_ler pil,
ben_ler_f_vl ler,
per_all_assignments_f paf,
per_all_people_f ppl,
hr_all_organization_units org,
per_jobs jobs,
per_person_names_f_v ppln,
ben_benefit_relations_f brn,
per_periods_of_service ppos,
hr_all_organization_units le_org
WHERE ptnl.person_id = ppl.person_id
AND Trunc (SYSDATE) BETWEEN ppl.effective_start_date AND ppl.effective_end_date
AND ler.ler_id = ptnl.ler_id
AND ptnl.benefit_relation_id = brn.benefit_relation_id
AND ptnl.ptnl_ler_for_per_id = pil.ptnl_ler_for_per_id (+)
AND brn.legal_entity_id = le_org.organization_id (+)
AND Trunc(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
AND Trunc (SYSDATE) BETWEEN ppl.effective_start_date AND ppl.effective_end_date
AND Trunc (SYSDATE) BETWEEN ler.effective_start_date AND ler.effective_end_date
AND Trunc (SYSDATE) BETWEEN ppln.effective_start_date AND ppln.effective_end_date
AND Trunc(SYSDATE) BETWEEN brn.effective_start_date AND brn.effective_end_date
AND brn.rel_prmry_asg_id = paf.assignment_id
AND ppl.person_id = paf.person_id
AND paf.organization_id = org.organization_id (+)
AND paf.job_id = jobs.job_id(+)
AND paf.period_of_service_id = ppos.period_of_service_id
AND ppl.person_id = ppln.person_id (+)
ORDER BY ppl.person_number,
ptnl.creation_date DESC;