W3EB_LIBENEF_VW (SQL View)	Index Back
Life Insurance Life Insurance beneficiary information for 2x plans

W3EB_LIBENEF_VW

(SQL View)

Life Insurance

Life Insurance beneficiary information for 2x plans

SELECT L.EMPLID , L.EMPL_RCD , L.PLAN_TYPE , L.BENEFIT_NBR , L.EFFDT , L.DEPENDENT_BENEF , L.BENEF_PCT , L.FLAT_AMOUNT FROM PS_LIFE_ADD_BENEFC L WHERE L.EFFDT = ( SELECT MAX(L1.EFFDT) FROM PS_LIFE_ADD_BENEFC L1 WHERE L1.EMPLID = L.EMPLID AND L1.EMPL_RCD = L.EMPL_RCD AND L.PLAN_TYPE = L1.PLAN_TYPE AND L.BENEFIT_NBR = L1.BENEFIT_NBR AND L.DEPENDENT_BENEF = L1.DEPENDENT_BENEF AND L1.EFFDT <= %CurrentDateIn) AND (L.BENEF_PCT > 0 OR L.FLAT_AMOUNT > 0)

Parent record: LIFE_ADD_BENEFC

# 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 DEPENDENT_BENEF Character(2) VARCHAR2(2) NOT NULL Dependent Benefit
01=default
Prompt Table: DEPENDENT_VW

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

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

#	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	DEPENDENT_BENEF	Character(2)	VARCHAR2(2) NOT NULL	Dependent Benefit 01=default Prompt Table: DEPENDENT_VW
7	BENEF_PCT	Number(3,0)	SMALLINT NOT NULL	Percent Beneficiary Paid
8	FLAT_AMOUNT	Signed Number(9,0)	DECIMAL(8) NOT NULL	Flat Amount