BEN_OE_EOI_VW (SQL View)	Index Back
Insights EOI Vw Employee EOI data

BEN_OE_EOI_VW

(SQL View)

Insights EOI Vw

Employee EOI data

SELECT B.SCHED_ID , B.EMPLID , B.BENEFIT_RCD_NBR , B.EVENT_ID , B.PLAN_TYPE , B.OPTION_ID , D.XLATLONGNAME , C.EMAIL_ADDR , C.WORK_PHONE , %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 = B.EMPLID AND B1.EMPL_RCD = B.BENEFIT_RCD_NBR AND B1.COBRA_EVENT_ID = 0 AND B1.EFFDT = (SELECT MAX(B2.EFFDT)FROM PS_BEN_PROG_PARTIC B2 WHERE B2.EMPLID = B1.EMPLID AND B2.EMPL_RCD = B.BENEFIT_RCD_NBR AND B2.COBRA_EVENT_ID = 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_SCHED S2 WHERE S2.SCHED_ID = B.SCHED_ID),'') , B.PROOF_REQ_IND , A.PROOF_RCVD , C.EVENT_STATUS , C.BAS_PROCESS_DESCR , %CurrentDateTimeIn FROM PS_BAS_PARTIC_OPTN B ,PS_BAS_PARTIC_PLAN A , PS_BEN_OE_PAR_VW C , PSXLATITEM D WHERE B.EMPLID = C.EMPLID AND B.EMPLID = A.EMPLID AND B.SCHED_ID = C.SCHED_ID AND B.SCHED_ID = A.SCHED_ID AND B.BENEFIT_RCD_NBR = C.BENEFIT_RCD_NBR AND B.BENEFIT_RCD_NBR = A.BENEFIT_RCD_NBR AND B.EVENT_ID = C.EVENT_ID AND B.EVENT_ID = A.EVENT_ID AND B.EVENT_DT = C.EVENT_DT AND B.EVENT_DT = A.EVENT_DT AND B.PLAN_TYPE = A.PLAN_TYPE AND B.OPTION_CD <> ' ' AND B.OPTION_CD = A.OPTION_CD AND D.FIELDNAME = 'PLAN_TYPE' AND D.FIELDVALUE = B.PLAN_TYPE AND %EffdtCheck(PSXLATITEM X, D, %CurrentDateIn) AND D.EFF_STATUS = 'A'

# 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 PLAN_TYPE Character(2) VARCHAR2(2) NOT NULL Identifies a category of benefit plan, such as Medical, Dental, and Life (Insurance). A set of plan type codes, as well as rules for creating new codes, is provided by PeopleSoft. Plan Type codes determine how the system processes and defines eligibility for the various benefit plans.

6 OPTION_ID Number(4,0) SMALLINT NOT NULL Option ID

7 PLAN_TYPE_DESCR Character(30) VARCHAR2(30) NOT NULL Plan Type Description

8 EMAIL_ADDR Character(70) VARCHAR2(70) NOT NULL Email Address

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

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 PROOF_REQ_IND Character(1) VARCHAR2(1) NOT NULL Proof Required

13 PROOF_RCVD Character(1) VARCHAR2(1) NOT NULL Proof Received

14 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

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

16 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	PLAN_TYPE	Character(2)	VARCHAR2(2) NOT NULL	Identifies a category of benefit plan, such as Medical, Dental, and Life (Insurance). A set of plan type codes, as well as rules for creating new codes, is provided by PeopleSoft. Plan Type codes determine how the system processes and defines eligibility for the various benefit plans.
6	OPTION_ID	Number(4,0)	SMALLINT NOT NULL	Option ID
7	PLAN_TYPE_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Plan Type Description
8	EMAIL_ADDR	Character(70)	VARCHAR2(70) NOT NULL	Email Address
9	WORK_PHONE	Character(24)	VARCHAR2(24) NOT NULL	Work Phone #
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	PROOF_REQ_IND	Character(1)	VARCHAR2(1) NOT NULL	Proof Required
13	PROOF_RCVD	Character(1)	VARCHAR2(1) NOT NULL	Proof Received
14	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
15	BAS_PROCESS_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Bas Process description
16	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.