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:
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