BEN_OE_CURR_VW (SQL View)	Index Back
EE Enrollment Used in Insights for OE

BEN_OE_CURR_VW

(SQL View)

EE Enrollment

Used in Insights for OE

SELECT A.SCHED_ID ,A.EMPLID ,A.BENEFIT_RCD_NBR ,A.EVENT_ID , %DateAdd(A.STATUS_DT, 1) ,A.BAS_PROCESS_STATUS , CASE WHEN A.BAS_PROCESS_STATUS IN ('PR' , 'NT') AND A.EVENT_STATUS = 'O' THEN 'Unsubmitted' ELSE CASE WHEN A.BAS_PROCESS_STATUS IN ('ET' , 'FE' , 'EE' , 'RE') THEN 'Submitted' ELSE 'No Status' END END ,A.EVENT_STATUS ,A.BENEFIT_PROGRAM , B1.DESCR , D.DESCR , F.GVT_OS_END_DT , CASE WHEN ((%DateDiff(%CurrentDateIn, F.GVT_OS_END_DT) ) < 0) THEN 0 ELSE %DateDiff( %CurrentDateIn, F.GVT_OS_END_DT) END , %Coalesce((SELECT P.PHONE FROM PS_PERSONAL_PHONE P WHERE P.EMPLID = A.EMPLID AND P.PREF_PHONE_FLAG = 'Y'),'') , %Dttm(A.STATUS_DT, %TimePart(%CurrentDateTimeIn)) FROM PS_BAS_PARTIC A , PS_JOB B , PS_BAS_SCHED D , PSXLATITEM T1 , PS_BEN_DEFN_PGM B1 , PS_BAS_OE_DEFN F 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.BAS_EVT_DISCONNECT = 'N' AND A.ALLOW_SELF_SERVICE = 'Y' AND F.OE_DEFN_ID = D.OE_DEFN_ID AND F.GVT_OS_BEGIN_DT <= %CurrentDateIn AND %DateAdd(F.PERIOD_BEGIN_DT, 30) >= %CurrentDateIn 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 = 'O' 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 %DateAdd(F.PERIOD_BEGIN_DT, 30) >= %CurrentDateIn) 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) AND B1.BENEFIT_PROGRAM = A.BENEFIT_PROGRAM AND B1.EFFDT =( SELECT MAX(B2.EFFDT) FROM PS_BEN_DEFN_PGM B2 WHERE B2.BENEFIT_PROGRAM = B1.BENEFIT_PROGRAM AND B2.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

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

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

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

5 BEN_OE_BEGIN_DT Date(10) DATE Open Enrollment Begin DT

6 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

7 BAS_PROCESS_DESCR Character(30) VARCHAR2(30) NOT NULL Bas Process description

8 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

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

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

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

12 BEN_OE_END_DT Date(10) DATE Used in Insights for OE

13 NUM_DAYS Number(3,0) SMALLINT NOT NULL Number of Days

14 WORK_PHONE Character(24) VARCHAR2(24) NOT NULL Work Phone #

15 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	SCHED_ID	Character(6)	VARCHAR2(6) NOT NULL	Schedule ID
2	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
3	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.
4	EVENT_ID	Number(6,0)	INTEGER NOT NULL	Event Identification
5	BEN_OE_BEGIN_DT	Date(10)	DATE	Open Enrollment Begin DT
6	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
7	BAS_PROCESS_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Bas Process description
8	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
9	BENEFIT_PROGRAM	Character(3)	VARCHAR2(3) NOT NULL	Defines a collection of benefit plans and their associated rate and calculation rules.
10	BENEFIT_PGM_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Benefit Program description
11	BAS_SCHED_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Bas Schedule description
12	BEN_OE_END_DT	Date(10)	DATE	Used in Insights for OE
13	NUM_DAYS	Number(3,0)	SMALLINT NOT NULL	Number of Days
14	WORK_PHONE	Character(24)	VARCHAR2(24) NOT NULL	Work Phone #
15	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.