BEN_PRTCPGM_VW (SQL View)	Index Back
Program participation Program participation view per employee id

BEN_PRTCPGM_VW

(SQL View)

Program participation

Program participation view per employee id

SELECT A.EMPLID ,A.EMPL_RCD ,A.COBRA_EVENT_ID ,A.BENEFIT_PROGRAM ,A.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_BEN_PROG_PARTIC A , PS_BEN_DEFN_PGM C WHERE 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 <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record

3 COBRA_EVENT_ID Number(3,0) SMALLINT NOT NULL Uniquely identifies a COBRA event within an employee's benefits records, and is system-generated.

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

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

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

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

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

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

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

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

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

13 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	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record
3	COBRA_EVENT_ID	Number(3,0)	SMALLINT NOT NULL	Uniquely identifies a COBRA event within an employee's benefits records, and is system-generated.
4	BENEFIT_PROGRAM	Character(3)	VARCHAR2(3) NOT NULL	Defines a collection of benefit plans and their associated rate and calculation rules.
5	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
6	FSA_MAX_ANNL_PLDG	Number(9,2)	DECIMAL(8,2) NOT NULL	FSA Maximum Annual Pledge
7	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)
8	COST_FREQUENCY	Character(1)	VARCHAR2(1) NOT NULL	Cost Freq on Enrollment Form A=Annual Frequency D=Deduction Frequency
9	SHOW_CREDIT	Character(1)	VARCHAR2(1) NOT NULL	This field tells the system whether or not to show credits on the enrollment form.
10	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.
11	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.
12	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.
13	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