DEP_BENEF_VW2 (SQL View)	Index Back
EE Dependent Only View DEP_BENEF_VW2 is a select-only view used to display name of the employee's dependent's in the pension plan page.

DEP_BENEF_VW2

(SQL View)

EE Dependent Only View

DEP_BENEF_VW2 is a select-only view used to display name of the employee's dependent's in the pension plan page.

SELECT A.EMPLID , A.DEPENDENT_BENEF , B.NAME , A.RELATIONSHIP , A.DEP_BENEF_TYPE , A.MAR_STATUS , A.MAR_STATUS_DT , A.BIRTHDATE , A.DT_OF_DEATH , A.STUDENT , A.STUDENT_STATUS_DT , A.DISABLED FROM PS_DEPENDENT_VW A , PS_DEP_BEN_NAME B WHERE A.EMPLID = B.EMPLID AND A.DEPENDENT_BENEF = B.DEPENDENT_BENEF AND A.DEPENDENT_BENEF <> '00' AND B.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_DEP_BEN_NAME C1 WHERE C1.EMPLID = A.EMPLID AND C1.DEPENDENT_BENEF = A.DEPENDENT_BENEF AND C1.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

2 DEPENDENT_BENEF Character(2) VARCHAR2(2) NOT NULL Dependent Benefit
01=default
Default Value: 01

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

4 RELATIONSHIP Character(2) VARCHAR2(2) NOT NULL Relationship to Employee
Default Value: O

5 DEP_BENEF_TYPE Character(1) VARCHAR2(1) NOT NULL Dependent Beneficiary Type
B=Beneficiary
C=COBRA Dependent Only
D=Dependent
E=QDRO Estate
N=None
O=Both
Q=QDRO Representative - Employee
R=QDRO Representative -Recipient
Default Value: D

6 MAR_STATUS Character(1) VARCHAR2(1) NOT NULL Marital Status
C=Common-Law
D=Divorced
E=Separated
H=Head of Household
L=DissDeclLost Civil Partner
M=Married
P=Civil Partnership
S=Single
T=Surviving Civil Partner
U=Unknown
V=Dissolved Civil Partnership
W=Widowed

7 MAR_STATUS_DT Date(10) DATE This is the date that the employee's Marital status is/was effective. /* Marital Status date is stored redundantly on PERS_DATA_EFFDT for two reasons. 1. To allow the date to be entered prior to the first PERS_DATA_EFFDT so that the actual date for the marital status on the hire row can be entered. This is the only time that the field is enterable by the user. 2. To allow for easier reporting on the data via SQR and Crystal. This is denormalizing the PERS_DATA_EFFDT record, but the number of requests for this redundancy coupled with the need for #1, has provided the justification for denormalizing this information and taking on the added maintenance. */

8 BIRTHDATE Date(10) DATE Date of Birth

9 DT_OF_DEATH Date(10) DATE Date of Death

10 STUDENT Character(1) VARCHAR2(1) NOT NULL Student
N=No
Y=Yes

11 STUDENT_STATUS_DT Date(10) DATE Student Status Date

12 DISABLED Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not (Y or N) a dependent is a disabled.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: PERSON_NAME
2	DEPENDENT_BENEF	Character(2)	VARCHAR2(2) NOT NULL	Dependent Benefit 01=default Default Value: 01
3	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
4	RELATIONSHIP	Character(2)	VARCHAR2(2) NOT NULL	Relationship to Employee Default Value: O
5	DEP_BENEF_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Dependent Beneficiary Type B=Beneficiary C=COBRA Dependent Only D=Dependent E=QDRO Estate N=None O=Both Q=QDRO Representative - Employee R=QDRO Representative -Recipient Default Value: D
6	MAR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Marital Status C=Common-Law D=Divorced E=Separated H=Head of Household L=DissDeclLost Civil Partner M=Married P=Civil Partnership S=Single T=Surviving Civil Partner U=Unknown V=Dissolved Civil Partnership W=Widowed
7	MAR_STATUS_DT	Date(10)	DATE	This is the date that the employee's Marital status is/was effective. /* Marital Status date is stored redundantly on PERS_DATA_EFFDT for two reasons. 1. To allow the date to be entered prior to the first PERS_DATA_EFFDT so that the actual date for the marital status on the hire row can be entered. This is the only time that the field is enterable by the user. 2. To allow for easier reporting on the data via SQR and Crystal. This is denormalizing the PERS_DATA_EFFDT record, but the number of requests for this redundancy coupled with the need for #1, has provided the justification for denormalizing this information and taking on the added maintenance. */
8	BIRTHDATE	Date(10)	DATE	Date of Birth
9	DT_OF_DEATH	Date(10)	DATE	Date of Death
10	STUDENT	Character(1)	VARCHAR2(1) NOT NULL	Student N=No Y=Yes
11	STUDENT_STATUS_DT	Date(10)	DATE	Student Status Date
12	DISABLED	Character(1)	VARCHAR2(1) NOT NULL	A flag that indicates whether or not (Y or N) a dependent is a disabled.