Employee's Potential Life Event Details in Oracle Advanced Benefits(OAB)

To get the Employee's Potential Life Event Details

Modules Involved : HRMS, OAB

Purpose    : To get the Employee's Potential Life Event Details
Description      : It gives the Life Event details for an employee, the Status,the date the life event was Started,
       the date the life event was closed (Processed), as well as Void or Back Out Dates.
Needs two parameters (Person_Id, Business Group ID)
****************************************************************** */
SELECT  PPF.PERSON_ID
       , PPF.FULL_NAME
       , bplp.lf_evt_ocrd_dt                  eventDate
       , BL.NAME || ' (' || BL.LER_ID  || ')' LIFEEVENT
       , BPLP.PTNL_LER_FOR_PER_STAT_CD  STATUS
       , bplp.NTFN_DT                         notified
       , bplp.DTCTD_DT                        detected
       , bplp.UNPROCD_DT                      unprocessed
       , bplp.PROCD_DT                        processed
       , BPLP.VOIDD_DT                        VOIDED
       , bplp.lf_evt_ocrd_dt                  Occured
      FROM
         ben_ptnl_ler_for_per bplp
       , BEN_LER_F            BL
       , PER_ALL_PEOPLE_F     PPF
    WHERE sysdate between ppf.effective_start_date and ppf.effective_end_date
        AND ppf.person_id = bplp.person_id
        AND sysdate between bl.effective_start_date and bl.effective_end_date
        AND bplp.ler_id = bl.ler_id
        and PPF.BUSINESS_GROUP_ID = :BUSINESS_GROUP_ID
        AND PPF.PERSON_ID = :Person_ID
      ORDER BY 1 desc;