Benefits Life Events In Oracle HCM Cloud

Benefits Life Events in Oracle HCM Cloud

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;