BN_M_EBX_DEP_VW

(SQL View)
 Index Back

eBenX Dependents View

This view is used for the eBenX integration with Peoplesoft Benefits. This view joins to BN_M_SNAP_FILTR which is only populated during the EBNX_EXTRACT Application Engine Process

SELECT DISTINCT A.EMPLID , A.DEPENDENT_BENEF , B.NATIONAL_ID , C.NAME , B.BIRTHDATE , B.SEX , B.RELATIONSHIP , C.STUDENT , C.STUDENT_STATUS_DT , C.DISABLED , C.DT_OF_DEATH , C.MEDICARE_ENTLD_DT , C.SMOKER , B.COUNTRY , B.ADDRESS1 , B.ADDRESS2 , B.ADDRESS3 , B.ADDRESS4 , B.CITY , B.STATE , B.POSTAL , B.HOME_PHONE FROM PS_BN_M_SNAP_FILTR A , PS_BN_SNAP_PER B , PS_DEPENDENT_VW C WHERE A.EMPLID = B.EMPLID AND A.DEPENDENT_BENEF = B.DEPENDENT_BENEF AND A.EMPLID = C.EMPLID AND A.DEPENDENT_BENEF = C.DEPENDENT_BENEF AND B.DEPENDENT_BENEF <> ' '

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 DEPENDENT_BENEF Character(2) VARCHAR2(2) NOT NULL Dependent Benefit
01=default
3 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
4 NAME Character(50) VARCHAR2(50) NOT NULL Name
5 BIRTHDATE Date(10) DATE Date of Birth
6 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
7 RELATIONSHIP Character(2) VARCHAR2(2) NOT NULL Relationship to Employee
8 STUDENT Character(1) VARCHAR2(1) NOT NULL Student
N=No
Y=Yes
9 STUDENT_STATUS_DT Date(10) DATE Student Status Date
10 DISABLED Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not (Y or N) a dependent is a disabled.
11 DT_OF_DEATH Date(10) DATE Date of Death
12 MEDICARE_ENTLD_DT Date(10) DATE Date Entitled to Medicare
13 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
14 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
15 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
16 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
17 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
18 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
19 CITY Character(30) VARCHAR2(30) NOT NULL City
20 STATE Character(6) VARCHAR2(6) NOT NULL State
21 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
22 HOME_PHONE Character(24) VARCHAR2(24) NOT NULL Home Phone #