W3EB_PRTCPGM_VW (SQL View)	Index Back
Setup Level 0 This view sits at level 0 on all the setup tables and establishes the base keys for all the other rowsets. This view holds information from the BAS_PARTIC and BEN_DEFN_PGM table.

W3EB_PRTCPGM_VW

(SQL View)

Setup Level 0

This view sits at level 0 on all the setup tables and establishes the base keys for all the other rowsets. This view holds information from the BAS_PARTIC and BEN_DEFN_PGM table.

SELECT A.SCHED_ID ,A.EMPLID ,A.BENEFIT_RCD_NBR ,A.EVENT_ID ,A.ADDR_EFFDT ,A.EMPL_RCD ,A.JOB_EFFDT ,A.JOB_EFFSEQ ,A.BENEFIT_PROGRAM ,A.EVENT_CLASS ,A.EVENT_STATUS ,A.BAS_PROCESS_STATUS ,A.PROCESS_IND ,A.BAS_EVT_DISCONNECT ,A.CREDIT_ROLLOVER ,A.ELECT_SOURCE ,A.EVENT_DT ,A.EVENT_PRIORITY ,A.STATUS_DT ,A.OPTION_NOTIFY_DT ,A.ELECT_RCVD_DT ,A.ALLOW_SELF_SERVICE ,B.BAS_TYPE ,C.EFFDT ,C.FSA_MAX_ANNL_PLDG ,C.DFLT_CREDIT_RLLOVR ,C.COST_FREQUENCY ,C.SHOW_CREDIT ,C.BAS_SHOW_ER_COSTS ,C.BAS_SHOW_TAX_IMPCT ,C.HANDBOOK_URL_ID ,C.INCLD_CNSLSTX FROM PS_BAS_PARTIC A , PS_BAS_SCHED B , PS_BEN_DEFN_PGM C WHERE A.SCHED_ID = B.SCHED_ID AND A.BENEFIT_PROGRAM = C.BENEFIT_PROGRAM AND C.EFF_STATUS = 'A' AND C.EFFDT = ( SELECT MAX(CC.EFFDT) FROM PS_BEN_DEFN_PGM CC WHERE CC.BENEFIT_PROGRAM = C.BENEFIT_PROGRAM AND CC.EFFDT <= A.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 ADDR_EFFDT Date(10) DATE Address Effective Date

6 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr

7 JOB_EFFDT Date(10) DATE Job Effective Date

8 JOB_EFFSEQ Number(3,0) SMALLINT NOT NULL Job Effective Sequence

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

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

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

13 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

14 BAS_EVT_DISCONNECT Character(1) VARCHAR2(1) NOT NULL Event Disconnected

15 CREDIT_ROLLOVER Character(1) VARCHAR2(1) NOT NULL Excess Credit Rollover To
C=Cash
D=FSA - Dependent Care
F=Forfeit Excess Credits
H=FSA - Health Care
R=FSA - Retirement Counseling
S=Savings - 401K Plan

16 ELECT_SOURCE Character(1) VARCHAR2(1) NOT NULL Election Source
N=None Entered
O=Online
V=Voice Response
W=Web

17 EVENT_DT Date(10) DATE Event Date

18 EVENT_PRIORITY Number(3,0) SMALLINT NOT NULL Event Priority

19 STATUS_DT Date(10) DATE Status Date

20 OPTION_NOTIFY_DT Date(10) DATE Date Notified of Options

21 ELECT_RCVD_DT Date(10) DATE Elections Received Date

22 ALLOW_SELF_SERVICE Character(1) VARCHAR2(1) NOT NULL Available through Self Service
N=No Self Service
X=Event Created Prior to HRMS7.5
Y=Self Service Allowed

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

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

25 FSA_MAX_ANNL_PLDG Number(9,2) DECIMAL(8,2) NOT NULL FSA Maximum Annual Pledge

26 DFLT_CREDIT_RLLOVR Character(1) VARCHAR2(1) NOT NULL Field to Apply Excess Credits to
C=Cash
D=FSA - Dependent Care
F=Forfeit Excess Credits
H=FSA - Health Care
R=FSA-Retirement Counseling
S=Savings - 401(k)

27 COST_FREQUENCY Character(1) VARCHAR2(1) NOT NULL Cost Freq on Enrollment Form
A=Annual Frequency
D=Deduction Frequency

28 SHOW_CREDIT Character(1) VARCHAR2(1) NOT NULL This field tells the system whether or not to show credits on the enrollment form.

29 BAS_SHOW_ER_COSTS Character(1) VARCHAR2(1) NOT NULL This field tells the system whether or not to show the employer's contributions ("costs") on the enrollment form.

30 BAS_SHOW_TAX_IMPCT Character(1) VARCHAR2(1) NOT NULL This field tells the system whether or not to alert the employee to any potential tax impacts (due to the employer's contributions) on the enrollment form. Most typically, this would be if there is a T-Tax (imputed Income) component.

31 HANDBOOK_URL_ID Character(30) VARCHAR2(30) NOT NULL This field holds the ID of a URL object. The URL object points to the companies enrollment handbook.

32 INCLD_CNSLSTX Character(1) VARCHAR2(1) NOT NULL Indicator that designates whether to Include the Canadian Sales Tax on the enrollment form or not.
Y/N Table Edit
Default Value: N

#	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	ADDR_EFFDT	Date(10)	DATE	Address Effective Date
6	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Rcd Nbr
7	JOB_EFFDT	Date(10)	DATE	Job Effective Date
8	JOB_EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Job Effective Sequence
9	BENEFIT_PROGRAM	Character(3)	VARCHAR2(3) NOT NULL	Defines a collection of benefit plans and their associated rate and calculation rules.
10	EVENT_CLASS	Character(3)	VARCHAR2(3) NOT NULL	Event Classification
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	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
13	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
14	BAS_EVT_DISCONNECT	Character(1)	VARCHAR2(1) NOT NULL	Event Disconnected
15	CREDIT_ROLLOVER	Character(1)	VARCHAR2(1) NOT NULL	Excess Credit Rollover To C=Cash D=FSA - Dependent Care F=Forfeit Excess Credits H=FSA - Health Care R=FSA - Retirement Counseling S=Savings - 401K Plan
16	ELECT_SOURCE	Character(1)	VARCHAR2(1) NOT NULL	Election Source N=None Entered O=Online V=Voice Response W=Web
17	EVENT_DT	Date(10)	DATE	Event Date
18	EVENT_PRIORITY	Number(3,0)	SMALLINT NOT NULL	Event Priority
19	STATUS_DT	Date(10)	DATE	Status Date
20	OPTION_NOTIFY_DT	Date(10)	DATE	Date Notified of Options
21	ELECT_RCVD_DT	Date(10)	DATE	Elections Received Date
22	ALLOW_SELF_SERVICE	Character(1)	VARCHAR2(1) NOT NULL	Available through Self Service N=No Self Service X=Event Created Prior to HRMS7.5 Y=Self Service Allowed
23	BAS_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Ben Admin Type E=Event Maintenance O=Open Enrollment S=Snapshot
24	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
25	FSA_MAX_ANNL_PLDG	Number(9,2)	DECIMAL(8,2) NOT NULL	FSA Maximum Annual Pledge
26	DFLT_CREDIT_RLLOVR	Character(1)	VARCHAR2(1) NOT NULL	Field to Apply Excess Credits to C=Cash D=FSA - Dependent Care F=Forfeit Excess Credits H=FSA - Health Care R=FSA-Retirement Counseling S=Savings - 401(k)
27	COST_FREQUENCY	Character(1)	VARCHAR2(1) NOT NULL	Cost Freq on Enrollment Form A=Annual Frequency D=Deduction Frequency
28	SHOW_CREDIT	Character(1)	VARCHAR2(1) NOT NULL	This field tells the system whether or not to show credits on the enrollment form.
29	BAS_SHOW_ER_COSTS	Character(1)	VARCHAR2(1) NOT NULL	This field tells the system whether or not to show the employer's contributions ("costs") on the enrollment form.
30	BAS_SHOW_TAX_IMPCT	Character(1)	VARCHAR2(1) NOT NULL	This field tells the system whether or not to alert the employee to any potential tax impacts (due to the employer's contributions) on the enrollment form. Most typically, this would be if there is a T-Tax (imputed Income) component.
31	HANDBOOK_URL_ID	Character(30)	VARCHAR2(30) NOT NULL	This field holds the ID of a URL object. The URL object points to the companies enrollment handbook.
32	INCLD_CNSLSTX	Character(1)	VARCHAR2(1) NOT NULL	Indicator that designates whether to Include the Canadian Sales Tax on the enrollment form or not. Y/N Table Edit Default Value: N