EMPLIDC=Closed to Processing
D=Disconnected from Job Record
O=Open for Processing
V=Void
BEN_OE_AG_VW(SQL View) |
Index Back |
|---|---|
OE Insights AGOE Insights Step data |
| SELECT A.EMPLID , C.PTAI_LIST_ID , %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 = 'OE' AND S1.EVENT_CLASS = A.EVENT_CLASS AND S1.EVENT_DT = A.EVENT_DT 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'),'') , A.EVENT_STATUS ,T1.XLATLONGNAME , %Coalesce((SELECT %CAST(T.PTSF_NEXTEXEC_DTTM, DateTime, CHARACTER) FROM PS_PTSF_SCHED_STAT T WHERE T.PTSF_SBO_NAME = 'HC_BEN_OE_AG'),'') FROM PS_BEN_EVENT_VW A , PS_PTAI_LIST B , PS_PTAI_CONTEXT C , PSXLATITEM T1 WHERE C.PTAI_LIST_ID = B.PTAI_LIST_ID AND C.RECNAME = 'BNE_OE_CONTEXT' AND C.FIELDNAME = 'EMPLID' AND C.PTAI_KEY_VALUE = A.EMPLID AND T1.FIELDNAME = 'BAS_PROCESS_STATUS' AND T1.FIELDVALUE = A.BAS_PROCESS_STATUS AND T1.EFFDT =( SELECT MAX(T2.EFFDT) FROM PSXLATITEM T2 WHERE T2.FIELDNAME = T1.FIELDNAME AND T2.FIELDVALUE = T1.FIELDVALUE AND T1.EFFDT <= %CurrentDateIn) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | PTAI_LIST_ID | Character(31) | VARCHAR2(31) NOT NULL | Action Items - Action Item List ID |
| 3 | BENEFIT_PGM_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Benefit Program description |
| 4 | BAS_SCHED_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Bas Schedule description |
| 5 | WORK_PHONE | Character(24) | VARCHAR2(24) NOT NULL | Work Phone # |
| 6 | EVENT_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Event Status
C=Closed to Processing D=Disconnected from Job Record O=Open for Processing V=Void |
| 7 | BAS_PROCESS_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Bas Process description |
| 8 | BEN_DTTM_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Benefits DateTime |