DEPRELATN_VW (SQL View)	Index Back
Dependent Beneficiaries View

DEPRELATN_VW

(SQL View)

Dependent Beneficiaries View

SELECT R.PLAN_TYPE , R.BENEFIT_PLAN , B.EMPLID , B.DEPENDENT_BENEF , B.DEPENDENT_BENEF , C.NAME_DISPLAY , C.NAME_AC , C.FIRST_NAME , C.LAST_NAME , E.SAME_ADDRESS_EMPL , %subrec(ADDRESS_SBR,E) , %subrec(PHONE_SBR,B) , D.RELATIONSHIP , D.DEP_BENEF_TYPE , D.MAR_STATUS , D.MAR_STATUS_DT , D.SEX , B.BIRTHDATE , B.BIRTHPLACE , B.BIRTHSTATE , B.BIRTHCOUNTRY , D.OCCUPATION , B.DT_OF_DEATH , D.STUDENT , D.DISABLED , B.MEDICARE_ENTLD_DT , B.COBRA_EVENT_DT , B.CSB_ELIG , B.COBRA_ACTION , B.COBRA_EMPLID , D.STUDENT_STATUS_DT , D.SMOKER , B.DEPBEN_RIDER_FLG , %subrec(DEPEND_FED_SBR,B) , B.SAME_PHONE_EMPL , B.PHONE_TYPE , E.ADDRESS_TYPE , %subrec(DEPEND_UK_SBR,B) , %subrec(DEPEND_FP_SBR,B) FROM PS_DEP_BEN B , PS_DEP_BEN_NAME C , PS_DEP_BEN_EFF D , PS_DEP_BEN_ADDR E , PS_LIFE_ADD_TBL P , PS_LIFE_ADD_RELATN R WHERE B.EMPLID = C.EMPLID AND B.EMPLID = D.EMPLID AND B.EMPLID = E.EMPLID AND C.EMPLID = D.EMPLID AND C.EMPLID = E.EMPLID AND D.EMPLID = E.EMPLID AND B.DEPENDENT_BENEF = C.DEPENDENT_BENEF AND B.DEPENDENT_BENEF = D.DEPENDENT_BENEF AND B.DEPENDENT_BENEF = E.DEPENDENT_BENEF AND C.DEPENDENT_BENEF = D.DEPENDENT_BENEF AND C.DEPENDENT_BENEF = E.DEPENDENT_BENEF AND D.DEPENDENT_BENEF = E.DEPENDENT_BENEF AND P.PLAN_TYPE = R.PLAN_TYPE AND P.BENEFIT_PLAN = R.BENEFIT_PLAN AND P.EFFDT = R.EFFDT AND P.SPEC_RELATION = 'Y' AND R.RELATIONSHIP = D.RELATIONSHIP AND P.EFFDT = ( SELECT MAX(P1.EFFDT) FROM PS_LIFE_ADD_TBL P1 WHERE P.PLAN_TYPE = P1.PLAN_TYPE AND P.BENEFIT_PLAN = P1.BENEFIT_PLAN AND P1.SPEC_RELATION = 'Y') AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_DEP_BEN_NAME C1 WHERE C1.EMPLID = C.EMPLID AND C1.DEPENDENT_BENEF = C.DEPENDENT_BENEF) AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_DEP_BEN_EFF D1 WHERE D1.EMPLID = D.EMPLID AND D1.DEPENDENT_BENEF = D.DEPENDENT_BENEF) AND E.EFFDT = ( SELECT MAX(E1.EFFDT) FROM PS_DEP_BEN_ADDR E1 WHERE E1.EMPLID = E.EMPLID AND E1.DEPENDENT_BENEF = E.DEPENDENT_BENEF)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

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

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

4 DEPENDENT_BENEF Character(2) VARCHAR2(2) NOT NULL Dependent Benefit
01=default

5 DEPENDENT_ID Character(2) VARCHAR2(2) NOT NULL Dependent ID

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

7 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name

8 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name

9 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name

10 SAME_ADDRESS_EMPL Character(1) VARCHAR2(1) NOT NULL Same Address as Employee
Y/N Table Edit
Default Value: N

11 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
Prompt Table: COUNTRY_TBL

12 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1

13 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2

14 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3

15 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4

16 CITY Character(30) VARCHAR2(30) NOT NULL City

17 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1

18 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2

19 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat Reference
WW=House Trailer Reference

20 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1

21 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2

22 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3

23 COUNTY Character(30) VARCHAR2(30) NOT NULL County

24 STATE Character(6) VARCHAR2(6) NOT NULL State
Prompt Table: STATE_TBL

25 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal

26 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code

27 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit
Y/N Table Edit

28 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Country Code

29 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone

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

31 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

32 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

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

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

35 BIRTHDATE Date(10) DATE Date of Birth

36 BIRTHPLACE Character(30) VARCHAR2(30) NOT NULL Birth Location

37 BIRTHSTATE Character(6) VARCHAR2(6) NOT NULL Birth State
Prompt Table: BIRTHSTATE_VW

38 BIRTHCOUNTRY Character(3) VARCHAR2(3) NOT NULL Birth Country
Prompt Table: COUNTRY_TBL

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

40 DT_OF_DEATH Date(10) DATE Date of Death

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

42 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

43 MEDICARE_ENTLD_DT Date(10) DATE Date Entitled to Medicare

44 COBRA_EVENT_DT Date(10) DATE COBRA Event Date

45 CSB_ELIG Character(1) VARCHAR2(1) NOT NULL Eligible for CSB
Y/N Table Edit
Default Value: N

46 COBRA_ACTION Character(3) VARCHAR2(3) NOT NULL Identifies a COBRA-related process or event that is associated with a personnel action action reason combination. This provides the link between various personnel changes (both job-related and personal) and COBRA benefits eligibility. A COBRA action may correspond to a COBRA event classification, which defines how a qualifying event is recognized and handled. Some examples of COBRA actions (and qualifying events) are Death, Divorce, Medicare, and Military Leave. U.S. only.

47 COBRA_EMPLID Character(11) VARCHAR2(11) NOT NULL COBRA Generated Emplid

48 STUDENT_STATUS_DT Date(10) DATE Student Status Date

49 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

50 DEPBEN_RIDER_FLG Character(1) VARCHAR2(1) NOT NULL Court Order Flag
Y/N Table Edit
Default Value: N

51 GVT_FEHB_IND Character(1) VARCHAR2(1) NOT NULL FEHB Participant
Y/N Table Edit
Default Value: N

52 GVT_DEP_BENEF_PLAN Character(3) VARCHAR2(3) NOT NULL Dependent's Fed Plan Type
CHA=CHAMPUS
FEH=FEHB
MCA=Medicare Plan A
MCB=Medicare Plan B
OTH=Other Federal Health Plan

53 SAME_PHONE_EMPL Character(1) VARCHAR2(1) NOT NULL Same Phone as Employee
Y/N Table Edit
Default Value: N

54 PHONE_TYPE Character(4) VARCHAR2(4) NOT NULL Phone Type
BUSN=Business
CAMP=Campus
CELL=Mobile
DORM=Dormitory
FAX=FAX
HOME=Home
MAIN=Main
OTR=Other
PGR1=Pager 1
PGR2=Pager 2
TELX=Telex
WORK=Work
Prompt Table: PERSONAL_PHONE

55 ADDRESS_TYPE Character(4) VARCHAR2(4) NOT NULL Address Type
Prompt Table: PERSON_ADDRESS

56 DEP_ELIGIBLE_UK Character(1) VARCHAR2(1) NOT NULL Checkbox to indicate if dependent is eligible for UK Parental Leave.
Y/N Table Edit
Default Value: N

57 DEP_ADOPTED_UK Character(1) VARCHAR2(1) NOT NULL Checkbox used in UK Parental Leave to indicate if dependent is adopted.
Y/N Table Edit
Default Value: N

58 DEP_ADOPTION_DT_UK Date(10) DATE Date used in UK Parental Leave to indicate the adoption date for an adopted dependent.

59 DEP_CERTIFICATE_UK Character(1) VARCHAR2(1) NOT NULL Checkbox used in UK Parental Leave to indicate if dependent's certificate(s) have been verified. The certificates can be: Birth Certificate, adoption papers or award of a disability allowance.
Y/N Table Edit
Default Value: N

60 FP_DEPEND_EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID of the Dependent
Prompt Table: PERSON_NAME

61 FP_STAT_FONC Character(1) VARCHAR2(1) NOT NULL Civil Servant Status
Y/N Table Edit
Default Value: N

62 FP_EMPLOYER_NAME Character(30) VARCHAR2(30) NOT NULL Employer Name

63 FP_SAME_PUBL_SCE Character(1) VARCHAR2(1) NOT NULL Same Public Service
Y/N Table Edit
Default Value: N

64 LAST_UPDATE_DATE Date(10) DATE Date of last update
Default Value: %Date

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	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.
2	BENEFIT_PLAN	Character(6)	VARCHAR2(6) NOT NULL	Benefit Plan
3	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
4	DEPENDENT_BENEF	Character(2)	VARCHAR2(2) NOT NULL	Dependent Benefit 01=default
5	DEPENDENT_ID	Character(2)	VARCHAR2(2) NOT NULL	Dependent ID
6	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
7	NAME_AC	Character(50)	VARCHAR2(50) NOT NULL	Alternate Character Name
8	FIRST_NAME	Character(30)	VARCHAR2(30) NOT NULL	First Name
9	LAST_NAME	Character(30)	VARCHAR2(30) NOT NULL	Last Name
10	SAME_ADDRESS_EMPL	Character(1)	VARCHAR2(1) NOT NULL	Same Address as Employee Y/N Table Edit Default Value: N
11	COUNTRY	Character(3)	VARCHAR2(3) NOT NULL	Country Prompt Table: COUNTRY_TBL
12	ADDRESS1	Character(55)	VARCHAR2(55) NOT NULL	Address 1
13	ADDRESS2	Character(55)	VARCHAR2(55) NOT NULL	Address 2
14	ADDRESS3	Character(55)	VARCHAR2(55) NOT NULL	Address 3
15	ADDRESS4	Character(55)	VARCHAR2(55) NOT NULL	Address 4
16	CITY	Character(30)	VARCHAR2(30) NOT NULL	City
17	NUM1	Character(6)	VARCHAR2(6) NOT NULL	Number 1
18	NUM2	Character(6)	VARCHAR2(6) NOT NULL	Number 2
19	HOUSE_TYPE	Character(2)	VARCHAR2(2) NOT NULL	House Type AB=House Boat Reference WW=House Trailer Reference
20	ADDR_FIELD1	Character(2)	VARCHAR2(2) NOT NULL	Address Field 1
21	ADDR_FIELD2	Character(4)	VARCHAR2(4) NOT NULL	Address Field 2
22	ADDR_FIELD3	Character(4)	VARCHAR2(4) NOT NULL	Address Field 3
23	COUNTY	Character(30)	VARCHAR2(30) NOT NULL	County
24	STATE	Character(6)	VARCHAR2(6) NOT NULL	State Prompt Table: STATE_TBL
25	POSTAL	Character(12)	VARCHAR2(12) NOT NULL	Postal
26	GEO_CODE	Character(11)	VARCHAR2(11) NOT NULL	Geo Code
27	IN_CITY_LIMIT	Character(1)	VARCHAR2(1) NOT NULL	In City Limit Y/N Table Edit
28	COUNTRY_CODE	Character(3)	VARCHAR2(3) NOT NULL	Country Code
29	PHONE	Character(24)	VARCHAR2(24) NOT NULL	Telephone
30	RELATIONSHIP	Character(2)	VARCHAR2(2) NOT NULL	Relationship to Employee
31	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
32	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
33	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. */
34	SEX	Character(1)	VARCHAR2(1) NOT NULL	Gender F=Female M=Male U=Unknown Default Value: M
35	BIRTHDATE	Date(10)	DATE	Date of Birth
36	BIRTHPLACE	Character(30)	VARCHAR2(30) NOT NULL	Birth Location
37	BIRTHSTATE	Character(6)	VARCHAR2(6) NOT NULL	Birth State Prompt Table: BIRTHSTATE_VW
38	BIRTHCOUNTRY	Character(3)	VARCHAR2(3) NOT NULL	Birth Country Prompt Table: COUNTRY_TBL
39	OCCUPATION	Character(40)	VARCHAR2(40) NOT NULL	Occup
40	DT_OF_DEATH	Date(10)	DATE	Date of Death
41	STUDENT	Character(1)	VARCHAR2(1) NOT NULL	Student N=No Y=Yes Y/N Table Edit Default Value: N
42	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
43	MEDICARE_ENTLD_DT	Date(10)	DATE	Date Entitled to Medicare
44	COBRA_EVENT_DT	Date(10)	DATE	COBRA Event Date
45	CSB_ELIG	Character(1)	VARCHAR2(1) NOT NULL	Eligible for CSB Y/N Table Edit Default Value: N
46	COBRA_ACTION	Character(3)	VARCHAR2(3) NOT NULL	Identifies a COBRA-related process or event that is associated with a personnel action action reason combination. This provides the link between various personnel changes (both job-related and personal) and COBRA benefits eligibility. A COBRA action may correspond to a COBRA event classification, which defines how a qualifying event is recognized and handled. Some examples of COBRA actions (and qualifying events) are Death, Divorce, Medicare, and Military Leave. U.S. only.
47	COBRA_EMPLID	Character(11)	VARCHAR2(11) NOT NULL	COBRA Generated Emplid
48	STUDENT_STATUS_DT	Date(10)	DATE	Student Status Date
49	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
50	DEPBEN_RIDER_FLG	Character(1)	VARCHAR2(1) NOT NULL	Court Order Flag Y/N Table Edit Default Value: N
51	GVT_FEHB_IND	Character(1)	VARCHAR2(1) NOT NULL	FEHB Participant Y/N Table Edit Default Value: N
52	GVT_DEP_BENEF_PLAN	Character(3)	VARCHAR2(3) NOT NULL	Dependent's Fed Plan Type CHA=CHAMPUS FEH=FEHB MCA=Medicare Plan A MCB=Medicare Plan B OTH=Other Federal Health Plan
53	SAME_PHONE_EMPL	Character(1)	VARCHAR2(1) NOT NULL	Same Phone as Employee Y/N Table Edit Default Value: N
54	PHONE_TYPE	Character(4)	VARCHAR2(4) NOT NULL	Phone Type BUSN=Business CAMP=Campus CELL=Mobile DORM=Dormitory FAX=FAX HOME=Home MAIN=Main OTR=Other PGR1=Pager 1 PGR2=Pager 2 TELX=Telex WORK=Work Prompt Table: PERSONAL_PHONE
55	ADDRESS_TYPE	Character(4)	VARCHAR2(4) NOT NULL	Address Type Prompt Table: PERSON_ADDRESS
56	DEP_ELIGIBLE_UK	Character(1)	VARCHAR2(1) NOT NULL	Checkbox to indicate if dependent is eligible for UK Parental Leave. Y/N Table Edit Default Value: N
57	DEP_ADOPTED_UK	Character(1)	VARCHAR2(1) NOT NULL	Checkbox used in UK Parental Leave to indicate if dependent is adopted. Y/N Table Edit Default Value: N
58	DEP_ADOPTION_DT_UK	Date(10)	DATE	Date used in UK Parental Leave to indicate the adoption date for an adopted dependent.
59	DEP_CERTIFICATE_UK	Character(1)	VARCHAR2(1) NOT NULL	Checkbox used in UK Parental Leave to indicate if dependent's certificate(s) have been verified. The certificates can be: Birth Certificate, adoption papers or award of a disability allowance. Y/N Table Edit Default Value: N
60	FP_DEPEND_EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID of the Dependent Prompt Table: PERSON_NAME
61	FP_STAT_FONC	Character(1)	VARCHAR2(1) NOT NULL	Civil Servant Status Y/N Table Edit Default Value: N
62	FP_EMPLOYER_NAME	Character(30)	VARCHAR2(30) NOT NULL	Employer Name
63	FP_SAME_PUBL_SCE	Character(1)	VARCHAR2(1) NOT NULL	Same Public Service Y/N Table Edit Default Value: N
64	LAST_UPDATE_DATE	Date(10)	DATE	Date of last update Default Value: %Date