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

BEN_KIB_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 , B.LIFE_EVENT_TYPE , C.DESCR20 , C.DAYS_TO_NOTIFY , %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_DT FROM PS_BAS_PARTIC A , PS_W3EB_LE_STATE92 B , PS_W3EB_LE_RULES C WHERE A.EMPLID = B.EMPLID AND A. EVENT_CLASS = B.BAS_ACTION AND A. BAS_ACTION_SOURCE = B.BAS_ACTION_SOURCE AND A.EVENT_STATUS = 'O' AND A.EVENT_DT = B.ACTION_DT_SS AND B.LIFE_EVENT_TYPE = C.LIFE_EVENT_TYPE

# 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 LIFE_EVENT_TYPE Character(3) VARCHAR2(3) NOT NULL Type of Life Event (e.g. Birth, Adoption, Marriage)
A=Adoption
B=Birth
D=Divorce
M=Marriage

8 LIFE_EVT_DESCR Character(20) VARCHAR2(20) NOT NULL Life Event Descr

9 DAYS_TO_NOTIFY Number(3,0) SMALLINT NOT NULL Used in eBenefits Life Events. The maximum number of days that may elapse between the event date, and the date it is processed.

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

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

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

13 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	LIFE_EVENT_TYPE	Character(3)	VARCHAR2(3) NOT NULL	Type of Life Event (e.g. Birth, Adoption, Marriage) A=Adoption B=Birth D=Divorce M=Marriage
8	LIFE_EVT_DESCR	Character(20)	VARCHAR2(20) NOT NULL	Life Event Descr
9	DAYS_TO_NOTIFY	Number(3,0)	SMALLINT NOT NULL	Used in eBenefits Life Events. The maximum number of days that may elapse between the event date, and the date it is processed.
10	WORK_PHONE	Character(24)	VARCHAR2(24) NOT NULL	Work Phone #
11	EMAIL_ADDR	Character(70)	VARCHAR2(70) NOT NULL	Email Address
12	BAS_PROCESS_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Bas Process description
13	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.