BEN_KIB_ATT_VW

(SQL View)
 Index Back

Insights Attachments View

Missing Attachment data

SELECT A.EMPLID , A.BEN_EVENT_TYPE , A.BEN_EVENT_SEQ , CASE WHEN A.HR_ATT_SUB_ID = 'DEP' THEN 'DEPENDENT' ELSE 'LIFE EVENT' END , A.BEN_HEADER_STATUS , %Coalesce((SELECT T.XLATLONGNAME FROM PSXLATITEM T WHERE T.FIELDNAME = 'BEN_HEADER_STATUS' AND T.FIELDVALUE = A.BEN_HEADER_STATUS AND T.EFFDT =(SELECT MAX(T1.EFFDT) FROM PSXLATITEM T1 WHERE T1.FIELDNAME = T.FIELDNAME AND T1.FIELDVALUE = T.FIELDVALUE AND T1.EFFDT <= %CurrentDateIn)) , '') , CASE WHEN A.HR_ATT_SUB_ID = 'DEP' THEN 'DependentUpdates' ELSE 'BenefitsDocumentFiling' END , E.LIFE_EVENT_TYPE , F.DESCR20 , CASE WHEN ((%DateDiff( %CurrentDateIn, %DateAdd(A.ACTION_DT_SS, F.DAYS_TO_NOTIFY)) ) < 0) THEN 0 ELSE %DateDiff( %CurrentDateIn, %DateAdd(A.ACTION_DT_SS, F.DAYS_TO_NOTIFY)) END , CASE WHEN ((%DateDiff( %CurrentDateIn, %DateAdd(A.ACTION_DT_SS, F.DAYS_TO_NOTIFY)) ) < 1) THEN '0' ELSE CASE WHEN ((%DateDiff( %CurrentDateIn, %DateAdd(A.ACTION_DT_SS, F.DAYS_TO_NOTIFY)) ) BETWEEN 1 AND 7) THEN '01-07' ELSE CASE WHEN ((%DateDiff( %CurrentDateIn, %DateAdd(A.ACTION_DT_SS, F.DAYS_TO_NOTIFY)) ) BETWEEN 8 AND 14) THEN '08-14' ELSE CASE WHEN ((%DateDiff( %CurrentDateIn, %DateAdd(A.ACTION_DT_SS, F.DAYS_TO_NOTIFY)) ) BETWEEN 15 AND 21) THEN '15-21' ELSE CASE WHEN ((%DateDiff( %CurrentDateIn, %DateAdd(A.ACTION_DT_SS, F.DAYS_TO_NOTIFY)) ) BETWEEN 22 AND 28) THEN '22-28' ELSE CASE WHEN ((%DateDiff( %CurrentDateIn, %DateAdd(A.ACTION_DT_SS, F.DAYS_TO_NOTIFY)) ) > 28) THEN 'More than 28' END END END END END END , %Coalesce((SELECT P.PHONE FROM PS_PERSONAL_PHONE P WHERE P.EMPLID = A.EMPLID AND P.PREF_PHONE_FLAG = 'Y'),'') , E.OPRID , %Coalesce((SELECT H.EMAIL_ADDR FROM PS_EMAIL_ADDRESSES H WHERE H.EMPLID = A.EMPLID AND H.PREF_EMAIL_FLAG = 'Y'),'') , %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 = E.BAS_ACTION_SOURCE AND S1.EVENT_CLASS = E.BAS_ACTION AND S1.EVENT_DT = E.ACTION_DT_SS AND S1.EVENT_STATUS = 'O' AND S1.SCHED_ID = S2.SCHED_ID),'') , %CurrentDateTimeIn FROM PS_BEN_ATTACH_HDR A , PS_W3EB_LE_STATE92 E, PS_W3EB_LE_RULES F WHERE A.EMPLID = E.EMPLID AND E.LIFE_EVENT_TYPE = F.LIFE_EVENT_TYPE AND A.PTAI_LIST_ID <> ' '

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 BEN_EVENT_TYPE Character(10) VARCHAR2(10) NOT NULL Benefits event types for attachments
D=Dependent
L=Life Event
O=Open Enrollment
3 BEN_EVENT_SEQ Number(3,0) SMALLINT NOT NULL Benefits attachment sequence
4 BEN_EVENT_TYPE_DES Character(30) VARCHAR2(30) NOT NULL Benefits event types for attachments
5 BEN_HEADER_STATUS Character(1) VARCHAR2(1) NOT NULL Status for header record
C=Complete
D=Closed
I=In Progress
O=Complete - Admin override
R=Attachment Missing
6 BEN_HEADER_DESCR Character(30) VARCHAR2(30) NOT NULL Benefits header descr
7 EOAWPRCS_ID Character(30) VARCHAR2(30) NOT NULL Approval Process ID
8 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
9 LIFE_EVT_DESCR Character(20) VARCHAR2(20) NOT NULL Life Event Descr
10 NUM_DAYS Number(3,0) SMALLINT NOT NULL Number of Days
11 BNE_EXP_DT_DESCR Character(50) VARCHAR2(50) NOT NULL determine Life Event AG expiration date range
12 WORK_PHONE Character(24) VARCHAR2(24) NOT NULL Work Phone #
13 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
14 EMAIL_ADDR Character(70) VARCHAR2(70) NOT NULL Email Address
15 BENEFIT_PGM_DESCR Character(30) VARCHAR2(30) NOT NULL Benefit Program description
16 BAS_SCHED_DESCR Character(30) VARCHAR2(30) NOT NULL Bas Schedule description
17 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.