BEN_OE_PAR_VW (SQL View)	Index Back
EE Bas Partic data EE Bas Partic data for Insights

BEN_OE_PAR_VW

(SQL View)

EE Bas Partic data

EE Bas Partic data for Insights

SELECT A.SCHED_ID , A.EMPLID , A.BENEFIT_RCD_NBR , A.EVENT_ID , A.EVENT_CLASS , A.EVENT_DT , %Coalesce((SELECT P.PHONE FROM PS_PERSONAL_PHONE P WHERE P.EMPLID = A.EMPLID AND P.PREF_PHONE_FLAG = 'Y'),'') , %Coalesce((SELECT H.EMAIL_ADDR FROM PS_EMAIL_ADDRESSES H WHERE H.EMPLID = A.EMPLID AND H.PREF_EMAIL_FLAG = 'Y'),'') , %Coalesce((SELECT T.XLATLONGNAME FROM PSXLATITEM T WHERE T.FIELDNAME = 'BAS_PROCESS_STATUS' AND T.FIELDVALUE = A.BAS_PROCESS_STATUS AND T.EFFDT =(SELECT MAX(T1.EFFDT) FROM PSXLATITEM T1 WHERE T1.FIELDNAME = T.FIELDNAME AND T1.FIELDVALUE = T.FIELDVALUE AND T1.EFFDT <= %CurrentDateIn)) , '') , A.EVENT_STATUS , A.EVENT_DT FROM PS_BAS_PARTIC A , PS_BEN_EVENT_VW B WHERE A.EMPLID = B.EMPLID AND A. EVENT_CLASS = B.EVENT_CLASS AND A. BAS_ACTION_SOURCE = 'OE' AND A.EVENT_DT = B.EVENT_DT

# 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 EVENT_CLASS Character(3) VARCHAR2(3) NOT NULL Event Classification

6 EVENT_DT Date(10) DATE Event Date

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

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

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

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 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	EVENT_CLASS	Character(3)	VARCHAR2(3) NOT NULL	Event Classification
6	EVENT_DT	Date(10)	DATE	Event Date
7	WORK_PHONE	Character(24)	VARCHAR2(24) NOT NULL	Work Phone #
8	EMAIL_ADDR	Character(70)	VARCHAR2(70) NOT NULL	Email Address
9	BAS_PROCESS_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Bas Process description
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	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.