DEP_BEN_NID_VW

(SQL View)
 Index Back

EE Dep/Ben with NID

DEP_BEN_NID_VW is a view joining DEPENDENT_BENEF and NID. Used for ADP Connection.

SELECT A.EMPLID ,A.DEPENDENT_BENEF ,B.COUNTRY ,B.NATIONAL_ID_TYPE ,B.NATIONAL_ID ,A.NAME ,A.FIRST_NAME ,A.LAST_NAME ,A.NAME_AC ,A.SAME_ADDRESS_EMPL ,A.ADDRESS1 ,A.ADDRESS2 ,A.ADDRESS3 ,A.ADDRESS4 ,A.CITY ,A.NUM1 ,A.NUM2 ,A.HOUSE_TYPE ,A.COUNTY ,A.STATE ,A.POSTAL ,A.GEO_CODE ,A.IN_CITY_LIMIT ,A.COUNTRY_CODE ,A.PHONE ,A.RELATIONSHIP ,A.DEP_BENEF_TYPE ,A.MAR_STATUS ,A.MAR_STATUS_DT ,A.SEX ,A.BIRTHDATE ,A.BIRTHPLACE ,A.BIRTHSTATE ,A.BIRTHCOUNTRY ,A.OCCUPATION ,A.DT_OF_DEATH ,A.STUDENT ,A.DISABLED ,A.MEDICARE_ENTLD_DT ,A.COBRA_EVENT_DT ,A.CSB_ELIG ,' ' ,' ' ,A.COBRA_ACTION ,A.COBRA_EMPLID ,A.STUDENT_STATUS_DT ,A.SMOKER ,B.SSN_KEY_FRA FROM PS_DEPENDENT_VW A , PS_DEP_BENEF_NID B WHERE A.EMPLID = B.EMPLID AND A.DEPENDENT_BENEF = B.DEPENDENT_BENEF AND A.COUNTRY = B.COUNTRY

  • Related Language Record: DEPEND_BEN_LNG

  • Parent record: PERSONAL_DATA

    • # 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 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
    4 NATIONAL_ID_TYPE Character(6) VARCHAR2(6) NOT NULL National ID Type
    5 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
    6 NAME Character(50) VARCHAR2(50) NOT NULL Name
    7 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
    8 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
    9 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
    10 SAME_ADDRESS_EMPL Character(1) VARCHAR2(1) NOT NULL Same Address as Employee
    11 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
    12 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
    13 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
    14 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
    15 CITY Character(30) VARCHAR2(30) NOT NULL City
    16 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
    17 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
    18 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
    AB=House Boat Reference
    WW=House Trailer Reference
    19 COUNTY Character(30) VARCHAR2(30) NOT NULL County
    20 STATE Character(6) VARCHAR2(6) NOT NULL State
    21 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    22 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
    23 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit
    24 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Country Code
    25 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
    26 RELATIONSHIP Character(2) VARCHAR2(2) NOT NULL Relationship to Employee
    27 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
    28 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
    29 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. */
    30 SEX Character(1) VARCHAR2(1) NOT NULL Gender
    F=Female
    M=Male
    U=Unknown
    31 BIRTHDATE Date(10) DATE Date of Birth
    32 BIRTHPLACE Character(30) VARCHAR2(30) NOT NULL Birth Location
    33 BIRTHSTATE Character(6) VARCHAR2(6) NOT NULL Birth State
    34 BIRTHCOUNTRY Character(3) VARCHAR2(3) NOT NULL Birth Country
    35 OCCUPATION Character(40) VARCHAR2(40) NOT NULL Occup
    36 DT_OF_DEATH Date(10) DATE Date of Death
    37 STUDENT Character(1) VARCHAR2(1) NOT NULL Student
    N=No
    Y=Yes
    38 DISABLED Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not (Y or N) a dependent is a disabled.
    39 MEDICARE_ENTLD_DT Date(10) DATE Date Entitled to Medicare
    40 COBRA_EVENT_DT Date(10) DATE COBRA Event Date
    41 CSB_ELIG Character(1) VARCHAR2(1) NOT NULL Eligible for CSB
    42 BIRTH_STATE_FRA Character(3) VARCHAR2(3) NOT NULL BirthState
    43 BIRTH_COUNTRY_FRA Character(3) VARCHAR2(3) NOT NULL BirthCountry
    44 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.
    45 COBRA_EMPLID Character(11) VARCHAR2(11) NOT NULL COBRA Generated Emplid
    46 STUDENT_STATUS_DT Date(10) DATE Student Status Date
    47 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
    48 SSN_KEY_FRA Character(2) VARCHAR2(2) NOT NULL Social Security Number Key