W3EB_SVBENF_VW2 (SQL View)	Index Back
EE Savings Plan Beneficiaries This is a view which identifies the beneficiaries associated with a savings plan that a participant has been enrolled in.

W3EB_SVBENF_VW2

(SQL View)

EE Savings Plan Beneficiaries

This is a view which identifies the beneficiaries associated with a savings plan that a participant has been enrolled in.

SELECT S.EMPLID , S.EMPL_RCD , S.PLAN_TYPE , S.BENEFIT_NBR , S.EFFDT , E.EFFDT , D.EFFDT , S.DEPENDENT_BENEF , S.BENEF_PCT , S.CONTINGENT , S.FLAT_AMOUNT , S.EXCESS , E.NAME , D.RELATIONSHIP , F.BENEFIT_PLAN FROM PS_SAVINGS_BENEFIC S , PS_DEP_BEN_NAME E , PS_DEP_BEN_EFF D , PS_SAVINGS_PLAN F WHERE S.EMPLID = E.EMPLID AND S.EMPLID = D.EMPLID AND S.DEPENDENT_BENEF = E.DEPENDENT_BENEF AND S.DEPENDENT_BENEF = D.DEPENDENT_BENEF AND S.EFFDT=F.EFFDT AND S.EMPLID = F.EMPLID AND S.PLAN_TYPE=F.PLAN_TYPE

Parent record: SAVINGS_PLAN

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
Prompt Table: PERSON

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
Prompt Table: PER_ORG_ASGN_VW

3 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.
Default Value: 40

4 BENEFIT_NBR Number(3,0) SMALLINT NOT NULL Note! Although this field is part of the index schema for most Base Benefits enrollment-related records, it was in fact never functionally implemented. As such, it is CRITICAL that this field always be set to zero 0.

5 EFFDT Date(10) DATE NOT NULL Effective Date
Default Value: SAVINGS_PLAN.COVERAGE_BEGIN_DT

6 EFFDT_NM Date(10) DATE Field for keeping the effdt for names field for a duplicate row check.

7 EFFDT_NEW Date(10) DATE New Effective Date

8 DEPENDENT_BENEF Character(2) VARCHAR2(2) NOT NULL Dependent Benefit
01=default
Prompt Table: DEPENDENT_VW

9 BENEF_PCT Number(3,0) SMALLINT NOT NULL Percent Beneficiary Paid

10 CONTINGENT Character(1) VARCHAR2(1) NOT NULL Contingent
Y/N Table Edit
Default Value: N

11 FLAT_AMOUNT Signed Number(9,0) DECIMAL(8) NOT NULL Flat Amount

12 EXCESS Character(1) VARCHAR2(1) NOT NULL Excess
N=No
Y=Yes
Y/N Table Edit
Default Value: N

13 NAME Character(50) VARCHAR2(50) NOT NULL Name

14 RELATIONSHIP Character(2) VARCHAR2(2) NOT NULL Relationship to Employee

15 BENEFIT_PLAN Character(6) VARCHAR2(6) NOT NULL Benefit Plan

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: PERSON
2	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record Prompt Table: PER_ORG_ASGN_VW
3	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. Default Value: 40
4	BENEFIT_NBR	Number(3,0)	SMALLINT NOT NULL	Note! Although this field is part of the index schema for most Base Benefits enrollment-related records, it was in fact never functionally implemented. As such, it is CRITICAL that this field always be set to zero 0.
5	EFFDT	Date(10)	DATE NOT NULL	Effective Date Default Value: SAVINGS_PLAN.COVERAGE_BEGIN_DT
6	EFFDT_NM	Date(10)	DATE	Field for keeping the effdt for names field for a duplicate row check.
7	EFFDT_NEW	Date(10)	DATE	New Effective Date
8	DEPENDENT_BENEF	Character(2)	VARCHAR2(2) NOT NULL	Dependent Benefit 01=default Prompt Table: DEPENDENT_VW
9	BENEF_PCT	Number(3,0)	SMALLINT NOT NULL	Percent Beneficiary Paid
10	CONTINGENT	Character(1)	VARCHAR2(1) NOT NULL	Contingent Y/N Table Edit Default Value: N
11	FLAT_AMOUNT	Signed Number(9,0)	DECIMAL(8) NOT NULL	Flat Amount
12	EXCESS	Character(1)	VARCHAR2(1) NOT NULL	Excess N=No Y=Yes Y/N Table Edit Default Value: N
13	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
14	RELATIONSHIP	Character(2)	VARCHAR2(2) NOT NULL	Relationship to Employee
15	BENEFIT_PLAN	Character(6)	VARCHAR2(6) NOT NULL	Benefit Plan