• Post category:Benefits
  • Post comments:0 Comments
  • Post last modified:October 19, 2021
  • Reading time:4 mins read
You are currently viewing SQL Query to fetch Benefit Life Events processed for employees
SQL Query to fetch Benefit Life Events processed for employees

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.
image - SQL Query to fetch Benefit Life Events processed for employees

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,
       le_org.name le_name,
       ler.name ler,
       ptnl.ptnl_ler_for_per_stat_cd ptnl_stat,
       pil.per_in_ler_stat_cd   pil_stat,
       ppos.date_start               hire_date,
       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
If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.