W3EB_EVENT_VW (SQL View)	Index Back
Event Selection View This view selects any unfinalized events. It is used to load the grid on the Event Selection page.

W3EB_EVENT_VW

(SQL View)

Event Selection View

This view selects any unfinalized events. It is used to load the grid on the Event Selection page.

SELECT A.EMPLID ,A.EVENT_DT ,C.NAME ,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 FROM PS_BAS_PARTIC A , PS_JOB B , PS_PERSON_NAME C , 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.EMPLID = C.EMPLID 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 NAME Character(50) VARCHAR2(50) NOT NULL Name

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

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

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

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

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

9 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

10 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

11 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

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

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

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

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

#	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	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
4	SCHED_ID	Character(6)	VARCHAR2(6) NOT NULL	Schedule ID
5	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.
6	EVENT_ID	Number(6,0)	INTEGER NOT NULL	Event Identification
7	EVENT_CLASS	Character(3)	VARCHAR2(3) NOT NULL	Event Classification
8	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
9	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
10	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
11	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
12	BENEFIT_PROGRAM	Character(3)	VARCHAR2(3) NOT NULL	Defines a collection of benefit plans and their associated rate and calculation rules.
13	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID
14	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code
15	BAS_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Ben Admin Type E=Event Maintenance O=Open Enrollment S=Snapshot