This employee life events query displays employee and benefits life event details with statuses and key dates for active personnel, supporting monitoring and management of employee benefit events.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Retrieves employee and benefits life event details with statuses and key dates
-- PERSON INFORMATION
PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
PPNFV.FULL_NAME EMPLOYEE_FULL_NAME,
PPNFV.DISPLAY_NAME EMPLOYEE_DISPLAY_NAME,
PPOS.DATE_START HIRE_DATE,
-- ASSIGNMENT & JOB INFORMATION
PAAF.ASSIGNMENT_NUMBER ,
-- LEGAL ENTITY INFORMATION
HAOU2.NAME LEGAL_EMPLOYER_NAME,
-- LIFE EVENT DETAILS
BLFV.NAME LIFE_EVENT_NAME,
BPLFP.PTNL_LER_FOR_PER_STAT_CD POTENTIAL_LIFE_EVENT_STATUS,
BPIL.PER_IN_LER_STAT_CD LIFE_EVENT_STATUS,
-- LIFE EVENT DATES
BPLFP.LF_EVT_OCRD_DT LIFE_EVENT_OCCURRED_DATE,
BPLFP.DTCTD_DT DETECTED_DATE,
BPLFP.NTFN_DT NOTIFICATION_DATE,
BPLFP.VOIDD_DT VOIDED_DATE,
BPLFP.PROCD_DT PROCESSED_DATE
FROM BEN_PTNL_LER_FOR_PER BPLFP,
BEN_PER_IN_LER BPIL,
BEN_LER_F_VL BLFV,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_ALL_PEOPLE_F PAPF,
HR_ALL_ORGANIZATION_UNITS HAOU,
PER_JOBS PJ,
PER_PERSON_NAMES_F_V PPNFV,
BEN_BENEFIT_RELATIONS_F BBRF,
PER_PERIODS_OF_SERVICE PPOS,
HR_ALL_ORGANIZATION_UNITS HAOU2
WHERE BPLFP.PERSON_ID = PAPF.PERSON_ID
AND BLFV.LER_ID = BPLFP.LER_ID
AND BPLFP.BENEFIT_RELATION_ID = BBRF.BENEFIT_RELATION_ID
AND BPLFP.PTNL_LER_FOR_PER_ID = BPIL.PTNL_LER_FOR_PER_ID
AND BBRF.LEGAL_ENTITY_ID = HAOU2.ORGANIZATION_ID
AND BBRF.REL_PRMRY_ASG_ID = PAAF.ASSIGNMENT_ID
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAAF.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND PAAF.JOB_ID = PJ.JOB_ID
AND PAAF.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND PAPF.PERSON_ID = PPNFV.PERSON_ID
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN BPIL.LF_EVT_OCRD_DT AND NVL(BPIL.PROCD_DT, SYSDATE)
AND SYSDATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN BLFV.EFFECTIVE_START_DATE AND BLFV.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PPNFV.EFFECTIVE_START_DATE AND PPNFV.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN BBRF.EFFECTIVE_START_DATE AND BBRF.EFFECTIVE_END_DATE
ORDER BY PAPF.PERSON_NUMBER ;