In this article, we will look into how to retrieve the life events processed for employees in Benefits Module.
Life events are events that happen to Participants, with which a Participant can get a chance to change his benefits elections. There are three types of life events:
- Scheduled: The Life events that are run on a particular date for all Participants in the system. Examples of these Life Events would be Open, Administrative, etc. These Life events are usually once in a Year kinds of life events, those are run to give enrolment opportunities to all Participants.
- Explicit: These life events are caused by database column updates.
- Person Initiated: The changes in Participant’s life, which are updated by the Participant himself, fall in this category. Examples would be the Birth of a Child, Marriage, Divorce, and Change in Address, etc.
- System Initiated: The Changes in the Participant’s life that are updated by the HR department of the Enterprise fall in this category. Examples would be, New Hire, Rehire, Leave of Absence, Change in Position, etc.
- Temporal: The changes in participants’ data due to time. These fall in this category. Examples would be Age, Length of Service, etc. The changes in Derived Factor brackets (to be discussed) cause temporal.
The base table which stores the life events is BEN_LER_F and BEN_PER_IN_LER identifies all life events for a person.
SQL Query to extract Processed Life Events Information:
If you like the content, please follow us on LinkedIn, Facebook, and Twitter to get updated with the latest content.
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
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM