BNE_LE_AG_EP_VW

(SQL View)
 Index Back

Life Event AG expiration days

Life Event Step data

SELECT A.EMPLID , A.LIFE_EVENT_TYPE , D.DESCR20 , C.PTAI_LIST_ID , CASE WHEN ((%DateDiff( %CurrentDateIn, %DateAdd(A.ACTION_DT_SS, D.DAYS_TO_NOTIFY)) ) < 0) THEN 0 ELSE %DateDiff( %CurrentDateIn, %DateAdd(A.ACTION_DT_SS, D.DAYS_TO_NOTIFY)) END , CASE WHEN ((%DateDiff( %CurrentDateIn, %DateAdd(A.ACTION_DT_SS, D.DAYS_TO_NOTIFY)) ) < 6) THEN 'Y' ELSE 'N' END , CASE WHEN ((%DateDiff( %CurrentDateIn, %DateAdd(A.ACTION_DT_SS, D.DAYS_TO_NOTIFY)) ) < 1) THEN '0' ELSE CASE WHEN ((%DateDiff( %CurrentDateIn, %DateAdd(A.ACTION_DT_SS, D.DAYS_TO_NOTIFY)) ) BETWEEN 1 AND 7) THEN '01-07' ELSE CASE WHEN ((%DateDiff( %CurrentDateIn, %DateAdd(A.ACTION_DT_SS, D.DAYS_TO_NOTIFY)) ) BETWEEN 8 AND 14) THEN '08-14' ELSE CASE WHEN ((%DateDiff( %CurrentDateIn, %DateAdd(A.ACTION_DT_SS, D.DAYS_TO_NOTIFY)) ) BETWEEN 15 AND 21) THEN '15-21' ELSE CASE WHEN ((%DateDiff( %CurrentDateIn, %DateAdd(A.ACTION_DT_SS, D.DAYS_TO_NOTIFY)) ) BETWEEN 22 AND 28) THEN '22-28' ELSE CASE WHEN ((%DateDiff( %CurrentDateIn, %DateAdd(A.ACTION_DT_SS, D.DAYS_TO_NOTIFY)) ) > 28) THEN 'More than 28' END END END END END END ,'None' ,'0-100' , CASE WHEN ((%DateDiff( %CurrentDateIn, %DateAdd(A.ACTION_DT_SS, D.DAYS_TO_NOTIFY)) ) < 1) THEN 'Expired' ELSE 'Active' END , %Coalesce((SELECT A1.DESCR FROM PS_BEN_DEFN_PGM A1, PS_BEN_PROG_PARTIC B1 WHERE A1.BENEFIT_PROGRAM = B1.BENEFIT_PROGRAM AND B1.EMPLID = A.EMPLID AND B1.EMPL_RCD = 0 AND B1.EFFDT = (SELECT MAX(B2.EFFDT) FROM PS_BEN_PROG_PARTIC B2 WHERE B2.EMPLID = B1.EMPLID AND B2.EMPL_RCD = 0 AND B2.EFFDT <= %CurrentDateIn) AND A1.EFFDT = (SELECT MAX(A2.EFFDT) FROM PS_BEN_DEFN_PGM A2 WHERE A2.BENEFIT_PROGRAM = A1.BENEFIT_PROGRAM AND A2.EFFDT <= %CurrentDateIn)), '') , %Coalesce((SELECT S2.DESCR FROM PS_BAS_PARTIC S1, PS_BAS_SCHED S2 WHERE S1.EMPLID = A.EMPLID AND S1.BAS_ACTION_SOURCE = A.BAS_ACTION_SOURCE AND S1.EVENT_CLASS = A.BAS_ACTION AND S1.EVENT_DT = A.ACTION_DT_SS AND S1.EVENT_STATUS = 'O' AND S1.SCHED_ID = S2.SCHED_ID),'') , %Coalesce((SELECT P.PHONE FROM PS_PERSONAL_PHONE P WHERE P.EMPLID = A.EMPLID AND P.PREF_PHONE_FLAG = 'Y'),'') , %Coalesce((SELECT %CAST(T.PTSF_NEXTEXEC_DTTM, DateTime, CHARACTER) FROM PS_PTSF_SCHED_STAT T WHERE T.PTSF_SBO_NAME = 'HC_BNE_LIFEEVT_EXPIRE'),'') FROM PS_W3EB_LE_STATE92 A , PS_PTAI_LIST B , PS_PTAI_CONTEXT C , PS_W3EB_LE_RULES D WHERE C.PTAI_LIST_ID = B.PTAI_LIST_ID AND C.RECNAME = 'W3EB_LE_CONTEXT' AND C.FIELDNAME = 'EMPLID' AND C.PTAI_KEY_VALUE = A.EMPLID AND A.LIFE_EVENT_TYPE = D.LIFE_EVENT_TYPE

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 LIFE_EVENT_TYPE Character(3) VARCHAR2(3) NOT NULL Type of Life Event (e.g. Birth, Adoption, Marriage)
A=Adoption
B=Birth
D=Divorce
M=Marriage
3 LIFE_EVT_DESCR Character(20) VARCHAR2(20) NOT NULL Life Event Descr
4 PTAI_LIST_ID Character(31) VARCHAR2(31) NOT NULL Action Items - Action Item List ID
5 NUM_DAYS Number(3,0) SMALLINT NOT NULL Number of Days
6 BNE_LE_EXP_DESCR Character(1) VARCHAR2(1) NOT NULL Benefits Expire description
7 BNE_EXP_DT_DESCR Character(50) VARCHAR2(50) NOT NULL determine Life Event AG expiration date range
8 BNE_SUMMARY_LNK Character(100) VARCHAR2(100) NOT NULL Benefits Summary Link
9 BEN_DT_RANGE Character(50) VARCHAR2(50) NOT NULL Benefits date range
10 TYPENAME Character(30) VARCHAR2(30) NOT NULL Type Name
11 BENEFIT_PGM_DESCR Character(30) VARCHAR2(30) NOT NULL Benefit Program description
12 BAS_SCHED_DESCR Character(30) VARCHAR2(30) NOT NULL Bas Schedule description
13 WORK_PHONE Character(24) VARCHAR2(24) NOT NULL Work Phone #
14 BEN_DTTM_DESCR Character(30) VARCHAR2(30) NOT NULL Benefits DateTime