BEN_OE_ATT_VW (SQL View)	Index Back
Insights OE Attachments View Missing Attachment data for OE insights

BEN_OE_ATT_VW

(SQL View)

Insights OE Attachments View

Missing Attachment data for OE insights

SELECT A.EMPLID , A.BEN_EVENT_TYPE , A.BEN_EVENT_SEQ , CASE WHEN A.HR_ATT_SUB_ID = 'DEP' THEN 'DEPENDENT' END , A.BEN_HEADER_STATUS , CASE WHEN A.BEN_HEADER_STATUS = 'R' THEN 'Yet to Upload' ELSE 'Uploaded' END , CASE WHEN A.HR_ATT_SUB_ID = 'DEP' THEN 'DependentUpdates' END , %Coalesce((SELECT P.PHONE FROM PS_PERSONAL_PHONE P WHERE P.EMPLID = A.EMPLID AND P.PREF_PHONE_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 = C.EMPLID AND S1.BAS_ACTION_SOURCE = 'OE' AND S1.EVENT_CLASS = C.EVENT_CLASS AND S1.EVENT_DT = C.EVENT_DT AND S1.SCHED_ID = C.SCHED_ID AND S1.SCHED_ID = S2.SCHED_ID),'') , %CurrentDateTimeIn FROM PS_BEN_ATTACH_HDR A , PS_BEN_EVENT_VW C , PS_PTAI_LIST D WHERE A.BEN_EVENT_TYPE = 'DEPENDENT' AND A.EMPLID = C.EMPLID AND A.PTAI_LIST_ID = D.PTAI_LIST_ID AND D.PTAI_INST_PKGROOT <> 'W3EB_LIFE_EVENT_FL'

# 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 WORK_PHONE Character(24) VARCHAR2(24) NOT NULL Work Phone #

9 BENEFIT_PGM_DESCR Character(30) VARCHAR2(30) NOT NULL Benefit Program description

10 BAS_SCHED_DESCR Character(30) VARCHAR2(30) NOT NULL Bas Schedule description

11 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.

#	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	WORK_PHONE	Character(24)	VARCHAR2(24) NOT NULL	Work Phone #
9	BENEFIT_PGM_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Benefit Program description
10	BAS_SCHED_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Bas Schedule description
11	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.