BN_M_EBX_EMP_VW(SQL View) |
Index Back |
|---|---|
eBenX employee InformationThis 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 , B.NATIONAL_ID , B.NAME , B.FIRST_NAME , B.LAST_NAME , P.NAME_SUFFIX , B.BIRTHDATE , B.SEX , P.MAR_STATUS , P.DT_OF_DEATH , P.LANG_CD , C.MEDICARE_ENTLD_DT , D.SMOKER FROM PS_BN_M_SNAP_FILTR A , PS_BN_SNAP_PER B , PS_PERS_DATA_US_VW C , PS_PERSON_NAME N , PS_PERSONAL_DT_FST P , PS_PERS_SMOKER_VW3 D WHERE A.EMPLID = B.EMPLID AND A.EMPLID = C.EMPLID AND A.EMPLID = P.EMPLID AND A.EMPLID = D.EMPLID 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 | NATIONAL_ID | Character(20) | VARCHAR2(20) NOT NULL | National ID |
| 3 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 4 | FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | First Name |
| 5 | LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 6 | NAME_SUFFIX | Character(15) | VARCHAR2(15) NOT NULL | Name Suffix |
| 7 | BIRTHDATE | Date(10) | DATE | Date of Birth |
| 8 | SEX | Character(1) | VARCHAR2(1) NOT NULL |
Gender
F=Female M=Male U=Unknown X=Indeterminate/Intersex/Unspec |
| 9 | 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 |
| 10 | DT_OF_DEATH | Date(10) | DATE | Date of Death |
| 11 | LANG_CD | Character(3) | VARCHAR2(3) NOT NULL |
Language Code
CFR=Canadian French DAN=Danish DUT=Dutch ENG=English ESP=Spanish FRA=French GER=German GRK=Greek INE=International English ITA=Italian JPN=Japanese KOR=Korean POR=Portuguese SVE=Swedish THA=Thai ZHS=Simplified Chinese ZHT=Traditional Chinese |
| 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 |