W3EB_9XPLNDF_VW (SQL View)	Index Back
Enrollment 9x Plan Definition This view displays the benefit plan definitions for the 9x plan types. It only shows plan definitions for benefit plans available to the employee in this event.

W3EB_9XPLNDF_VW

(SQL View)

Enrollment 9x Plan Definition

This view displays the benefit plan definitions for the 9x plan types. It only shows plan definitions for benefit plans available to the employee in this event.

SELECT A.SCHED_ID ,A.EMPLID ,A.BENEFIT_RCD_NBR ,A.EVENT_ID ,A.PLAN_TYPE ,A.BENEFIT_PLAN ,C.EFFDT ,B.BUY_SELL ,B.PAY_FREQUENCY ,B.VACN_HOURS_INCR ,B.VACN_MIN_HOURS ,B.VACN_MAX_HOURS ,B.VACN_MAX_AMT ,B.VACN_PCT_OF_SALARY ,C.MIN_BENEFIC_ALLOC ,0 ,0 ,0 FROM PS_BAS_PARTIC_OPTN A , PS_VACATION_TBL B , PS_BENEF_PLAN_TBL C WHERE A.PLAN_TYPE LIKE '9%' AND A.OPTION_TYPE = 'O' AND A.HISTORY_ONLY = 'N' AND B.PLAN_TYPE = A.PLAN_TYPE AND B.BENEFIT_PLAN = A.BENEFIT_PLAN AND B.EFFDT <= A.EVENT_DT AND B.EFFDT = ( SELECT MAX(BB.EFFDT) FROM PS_VACATION_TBL BB WHERE BB.PLAN_TYPE = B.PLAN_TYPE AND BB.BENEFIT_PLAN = B.BENEFIT_PLAN AND BB.EFFDT <= A.EVENT_DT) AND C.PLAN_TYPE = A.PLAN_TYPE AND C.BENEFIT_PLAN = A.BENEFIT_PLAN AND C.EFFDT <= A.EVENT_DT AND C.EFFDT = ( SELECT MAX(CC.EFFDT) FROM PS_BENEF_PLAN_TBL CC WHERE CC.PLAN_TYPE = C.PLAN_TYPE AND CC.BENEFIT_PLAN = C.BENEFIT_PLAN 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 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 BENEFIT_PLAN Character(6) VARCHAR2(6) NOT NULL Benefit Plan

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

8 BUY_SELL Character(1) VARCHAR2(1) NOT NULL Vacation Buy or Sell
B=Vacation Buy
S=Vacation Sell

9 PAY_FREQUENCY Character(5) VARCHAR2(5) NOT NULL Pay Frequency
A=Annual
B=Biweekly
D=Daily
M=Monthly
Q=Quarterly
S=Semimonthly
W=Weekly

10 VACN_HOURS_INCR Number(6,2) DECIMAL(5,2) NOT NULL Vacation Hours Increment

11 VACN_MIN_HOURS Number(5,2) DECIMAL(4,2) NOT NULL Vacation Minimum Hours

12 VACN_MAX_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Vacation Maximum Hours

13 VACN_MAX_AMT Number(8,2) DECIMAL(7,2) NOT NULL Vacation Maximum Dollars

14 VACN_PCT_OF_SALARY Number(6,3) DECIMAL(5,3) NOT NULL Vacation Percent of Salary

15 MIN_BENEFIC_ALLOC Number(6,2) DECIMAL(5,2) NOT NULL This field is used to define the Minimum allocation a beneficiary ( usually the spouse ) must be assigned under a regulated benefit plan

16 PAY_PDS_REMAINING Number(3,0) SMALLINT NOT NULL Pay Periods Remaining

17 DEDN_PDS_PER_YEAR Number(3,0) SMALLINT NOT NULL Dedn Periods Per Year

18 PLAN_PDS_PER_YEAR Number(3,0) SMALLINT NOT NULL Plan Periods Per Year

#	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	BENEFIT_PLAN	Character(6)	VARCHAR2(6) NOT NULL	Benefit Plan
7	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
8	BUY_SELL	Character(1)	VARCHAR2(1) NOT NULL	Vacation Buy or Sell B=Vacation Buy S=Vacation Sell
9	PAY_FREQUENCY	Character(5)	VARCHAR2(5) NOT NULL	Pay Frequency A=Annual B=Biweekly D=Daily M=Monthly Q=Quarterly S=Semimonthly W=Weekly
10	VACN_HOURS_INCR	Number(6,2)	DECIMAL(5,2) NOT NULL	Vacation Hours Increment
11	VACN_MIN_HOURS	Number(5,2)	DECIMAL(4,2) NOT NULL	Vacation Minimum Hours
12	VACN_MAX_HOURS	Number(7,2)	DECIMAL(6,2) NOT NULL	Vacation Maximum Hours
13	VACN_MAX_AMT	Number(8,2)	DECIMAL(7,2) NOT NULL	Vacation Maximum Dollars
14	VACN_PCT_OF_SALARY	Number(6,3)	DECIMAL(5,3) NOT NULL	Vacation Percent of Salary
15	MIN_BENEFIC_ALLOC	Number(6,2)	DECIMAL(5,2) NOT NULL	This field is used to define the Minimum allocation a beneficiary ( usually the spouse ) must be assigned under a regulated benefit plan
16	PAY_PDS_REMAINING	Number(3,0)	SMALLINT NOT NULL	Pay Periods Remaining
17	DEDN_PDS_PER_YEAR	Number(3,0)	SMALLINT NOT NULL	Dedn Periods Per Year
18	PLAN_PDS_PER_YEAR	Number(3,0)	SMALLINT NOT NULL	Plan Periods Per Year