DEP_BEN_CBR_VW (SQL View)	Index Back
COBRA Dependent View3 DEP_BEN_CBRA_VW is a view that identifies all dependents covered under a COBRA participant's various Health plans. It joins data from the PS_DEPENDENT_BENEF table and the PS_BEN_PROG_PARTIC table. There may be multiple records for each employee, one for each dependent covered. This view is used by the Cobra Health 2 panel.

DEP_BEN_CBR_VW

(SQL View)

COBRA Dependent View3

DEP_BEN_CBRA_VW is a view that identifies all dependents covered under a COBRA participant's various Health plans. It joins data from the PS_DEPENDENT_BENEF table and the PS_BEN_PROG_PARTIC table. There may be multiple records for each employee, one for each dependent covered. This view is used by the Cobra Health 2 panel.

SELECT b.emplid ,b.EMPL_RCD ,b.cobra_event_id ,a.dependent_benef ,a.name ,a.relationship ,a.dep_benef_type ,a.birthdate ,a.disabled ,a.dt_of_death ,a.mar_status ,a.mar_status_dt ,a.student ,a.student_status_dt FROM PS_DEPENDENT_VW A , PS_BEN_PROG_PARTIC B WHERE a.emplid = b.emplid AND B.COBRA_EVENT_ID > 0 AND B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_BEN_PROG_PARTIC C WHERE B.EMPLID = C.EMPLID AND B.EMPL_RCD = C.EMPL_RCD AND B.COBRA_EVENT_ID = C.COBRA_EVENT_ID)

# 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

3 COBRA_EVENT_ID Number(3,0) SMALLINT NOT NULL Uniquely identifies a COBRA event within an employee's benefits records, and is system-generated.

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

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

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

7 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

8 BIRTHDATE Date(10) DATE Date of Birth

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

10 DT_OF_DEATH Date(10) DATE Date of Death

11 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

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

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

14 STUDENT_STATUS_DT Date(10) DATE Student 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	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record
3	COBRA_EVENT_ID	Number(3,0)	SMALLINT NOT NULL	Uniquely identifies a COBRA event within an employee's benefits records, and is system-generated.
4	DEPENDENT_BENEF	Character(2)	VARCHAR2(2) NOT NULL	Dependent Benefit 01=default
5	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
6	RELATIONSHIP	Character(2)	VARCHAR2(2) NOT NULL	Relationship to Employee
7	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
8	BIRTHDATE	Date(10)	DATE	Date of Birth
9	DISABLED	Character(1)	VARCHAR2(1) NOT NULL	A flag that indicates whether or not (Y or N) a dependent is a disabled.
10	DT_OF_DEATH	Date(10)	DATE	Date of Death
11	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
12	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. */
13	STUDENT	Character(1)	VARCHAR2(1) NOT NULL	Student N=No Y=Yes
14	STUDENT_STATUS_DT	Date(10)	DATE	Student Status Date