GPS_DEP_EFF_VW (SQL View)	Index Back
EE Dependents/Beneficiaries DEP_BEN_EFF is used to record an employee's dependents and beneficiarie's effective dated information.

GPS_DEP_EFF_VW

(SQL View)

EE Dependents/Beneficiaries

DEP_BEN_EFF is used to record an employee's dependents and beneficiarie's effective dated information.

SELECT A.EMPLID , 'TBD ' , A.DEPENDENT_BENEF , A.EFFDT , A.RELATIONSHIP , A.DEP_BENEF_TYPE , A.MAR_STATUS , A.MAR_STATUS_DT , A.SEX , A.OCCUPATION , A.STUDENT , A.DISABLED , A.STUDENT_STATUS_DT , A.DISABLED_STATUS_DT , A.SMOKER , A.SMOKER_DT FROM PS_DEP_BEN_EFF A WHERE A.RELATIONSHIP IN ('AC','C','FC','GC','GH','OC','RC', 'SC')

Parent record: GPS_DEP_BEN

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

2 GPS_CHILD_ALLOW_NR Character(50) VARCHAR2(50) NOT NULL Child Allow. Nbr

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

4 EFFDT Date(10) DATE NOT NULL Effective Date

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

6 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

7 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
Default Value: S

8 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. */

9 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
Default Value: M

10 OCCUPATION Character(40) VARCHAR2(40) NOT NULL Occup

11 STUDENT Character(1) VARCHAR2(1) NOT NULL Student
N=No
Y=Yes
Y/N Table Edit
Default Value: N

12 DISABLED Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not (Y or N) a dependent is a disabled.
Y/N Table Edit
Default Value: N

13 STUDENT_STATUS_DT Date(10) DATE Student Status Date

14 DISABLED_STATUS_DT Date(10) DATE Disabled Status Date

15 SMOKER Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not (Y or N) an employee or dependent is a smoker.
N=Non Smoker
Y=Smoker
Y/N Table Edit
Default Value: N

16 SMOKER_DT Date(10) DATE Smoker Status Date

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: PERSON
2	GPS_CHILD_ALLOW_NR	Character(50)	VARCHAR2(50) NOT NULL	Child Allow. Nbr
3	DEPENDENT_BENEF	Character(2)	VARCHAR2(2) NOT NULL	Dependent Benefit 01=default Default Value: 01
4	EFFDT	Date(10)	DATE NOT NULL	Effective Date
5	RELATIONSHIP	Character(2)	VARCHAR2(2) NOT NULL	Relationship to Employee
6	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
7	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 Default Value: S
8	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. */
9	SEX	Character(1)	VARCHAR2(1) NOT NULL	Gender F=Female M=Male U=Unknown Default Value: M
10	OCCUPATION	Character(40)	VARCHAR2(40) NOT NULL	Occup
11	STUDENT	Character(1)	VARCHAR2(1) NOT NULL	Student N=No Y=Yes Y/N Table Edit Default Value: N
12	DISABLED	Character(1)	VARCHAR2(1) NOT NULL	A flag that indicates whether or not (Y or N) a dependent is a disabled. Y/N Table Edit Default Value: N
13	STUDENT_STATUS_DT	Date(10)	DATE	Student Status Date
14	DISABLED_STATUS_DT	Date(10)	DATE	Disabled Status Date
15	SMOKER	Character(1)	VARCHAR2(1) NOT NULL	A flag that indicates whether or not (Y or N) an employee or dependent is a smoker. N=Non Smoker Y=Smoker Y/N Table Edit Default Value: N
16	SMOKER_DT	Date(10)	DATE	Smoker Status Date