W3EB_EVENT_VW2 (SQL View)	Index Back
Event Selection View Fluid This view selects any unfinalized events. It is used to determine whether the current date is within Open Enrollment period in Benefits fluid tile.

W3EB_EVENT_VW2

(SQL View)

Event Selection View Fluid

This view selects any unfinalized events. It is used to determine whether the current date is within Open Enrollment period in Benefits fluid tile.

SELECT A.EMPLID ,A.EVENT_DT ,A.SCHED_ID ,A.BENEFIT_RCD_NBR ,A.EVENT_ID ,A.EVENT_CLASS ,A.JOB_EFFDT ,A.BAS_PROCESS_STATUS ,A.PROCESS_IND ,A.EVENT_STATUS ,A.BENEFIT_PROGRAM ,B.SETID_JOBCODE ,B.JOBCODE ,D.BAS_TYPE ,D.OE_DEFN_ID FROM PS_BAS_PARTIC A , PS_JOB B , PS_BAS_SCHED D WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.JOB_EFFDT = B.EFFDT AND A.JOB_EFFSEQ = B.EFFSEQ AND A.SCHED_ID = D.SCHED_ID AND A.EVENT_STATUS <> 'V' AND A.BAS_EVT_DISCONNECT = 'N' AND A.ALLOW_SELF_SERVICE = 'Y' AND EXISTS ( SELECT 'X' FROM PS_BAS_PARTIC_PLAN E WHERE E.SCHED_ID = A.SCHED_ID AND E.EMPLID = A.EMPLID AND E.BENEFIT_RCD_NBR = A.BENEFIT_RCD_NBR AND E.EVENT_ID = A.EVENT_ID AND E.ELECT_ALLOWED = 'Y') AND ( ( D.BAS_TYPE = 'E' AND A.BAS_PROCESS_STATUS IN ('AE','AN','AS','PE','PR','RE','NT','ET','EE') ) OR ( D.BAS_TYPE = 'O' AND ( A.BAS_PROCESS_STATUS IN ('AE','AN','AS','PE','PR','RE','NT','ET','EE','FE') AND EXISTS ( SELECT 'X' FROM PS_BAS_OE_DEFN F WHERE F.OE_DEFN_ID = D.OE_DEFN_ID AND F.GVT_OS_BEGIN_DT <= %CurrentDateIn AND F.GVT_OS_END_DT >= %CurrentDateIn) ) OR ( A.BAS_PROCESS_STATUS IN ('AE','AN','AS','PE','PR','RE','NT','ET','EE') AND EXISTS ( SELECT 'X' FROM PS_BAS_OE_DEFN G WHERE G.OE_DEFN_ID = D.OE_DEFN_ID AND G.GVT_OS_BEGIN_DT IS NULL AND G.GVT_OS_END_DT IS NULL) ) ) )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

2 EVENT_DT Date(10) DATE Event Date

3 SCHED_ID Character(6) VARCHAR2(6) NOT NULL Schedule ID

4 BENEFIT_RCD_NBR Number(3,0) SMALLINT NOT NULL Specifies a sequence number associated with each of multiple concurrent jobs for a one employee. It is used to determine the applicable benefit program for each job. The first job entered for an employee is automatically assigned a Benefit Record Number of zero (0). This field is linked with EMPL_RCD#, the Employment Record Number.

5 EVENT_ID Number(6,0) INTEGER NOT NULL Event Identification

6 EVENT_CLASS Character(3) VARCHAR2(3) NOT NULL Event Classification

7 EFFDT Date(10) DATE Effective Date
Default Value: %date

8 BAS_PROCESS_STATUS Character(2) VARCHAR2(2) NOT NULL Process Status
AE=Program Elig Assign Error
AN=Program Elig Assign None
AS=Program Elig Assigned
EE=Election Error
ET=Entered
FA=Finalized - Benefit Pgm None
FE=Finalized - Enrolled
FP=Finalized - Prepared None
NT=Notified
PE=Prepare Error
PR=Prepared
RE=Re-Enter

9 PROCESS_IND Character(1) VARCHAR2(1) NOT NULL Process Indicator
A=Assign Benefit Program
E=Elect Options
N=Normal Processing
P=Prepare Options
R=Re-Enter
V=Void

10 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

11 BENEFIT_PROGRAM Character(3) VARCHAR2(3) NOT NULL Defines a collection of benefit plans and their associated rate and calculation rules.

12 SETID Character(5) VARCHAR2(5) NOT NULL SetID

13 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

14 BAS_TYPE Character(1) VARCHAR2(1) NOT NULL Ben Admin Type
E=Event Maintenance
O=Open Enrollment
S=Snapshot

15 OE_DEFN_ID Character(4) VARCHAR2(4) NOT NULL Open Enrollment Definition ID

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	EVENT_DT	Date(10)	DATE	Event Date
3	SCHED_ID	Character(6)	VARCHAR2(6) NOT NULL	Schedule ID
4	BENEFIT_RCD_NBR	Number(3,0)	SMALLINT NOT NULL	Specifies a sequence number associated with each of multiple concurrent jobs for a one employee. It is used to determine the applicable benefit program for each job. The first job entered for an employee is automatically assigned a Benefit Record Number of zero (0). This field is linked with EMPL_RCD#, the Employment Record Number.
5	EVENT_ID	Number(6,0)	INTEGER NOT NULL	Event Identification
6	EVENT_CLASS	Character(3)	VARCHAR2(3) NOT NULL	Event Classification
7	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
8	BAS_PROCESS_STATUS	Character(2)	VARCHAR2(2) NOT NULL	Process Status AE=Program Elig Assign Error AN=Program Elig Assign None AS=Program Elig Assigned EE=Election Error ET=Entered FA=Finalized - Benefit Pgm None FE=Finalized - Enrolled FP=Finalized - Prepared None NT=Notified PE=Prepare Error PR=Prepared RE=Re-Enter
9	PROCESS_IND	Character(1)	VARCHAR2(1) NOT NULL	Process Indicator A=Assign Benefit Program E=Elect Options N=Normal Processing P=Prepare Options R=Re-Enter V=Void
10	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
11	BENEFIT_PROGRAM	Character(3)	VARCHAR2(3) NOT NULL	Defines a collection of benefit plans and their associated rate and calculation rules.
12	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID
13	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code
14	BAS_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Ben Admin Type E=Event Maintenance O=Open Enrollment S=Snapshot
15	OE_DEFN_ID	Character(4)	VARCHAR2(4) NOT NULL	Open Enrollment Definition ID