DEP_BEN_VW1

(SQL View)
 Index Back

EE Dependents/Beneficiaries

DEP_BEN_VW1 is used to record an employee's dependents and beneficiaries details as of any date.

SELECT A.EMPLID , A.DEPENDENT_BENEF , B.EFFDT , C.EFFDT , D.EFFDT , C.COUNTRY_NM_FORMAT , C.NAME , C.NAME_INITIALS , C.NAME_PREFIX , C.NAME_SUFFIX , C.NAME_ROYAL_PREFIX , C.NAME_ROYAL_SUFFIX , C.NAME_TITLE , C.LAST_NAME_SRCH , C.FIRST_NAME_SRCH , C.LAST_NAME , C.FIRST_NAME , C.MIDDLE_NAME , C.SECOND_LAST_NAME , C.SECOND_LAST_SRCH , C.NAME_AC , C.PREF_FIRST_NAME , C.PARTNER_LAST_NAME , C.PARTNER_ROY_PREFIX , C.LAST_NAME_PREF_NLD , C.NAME_DISPLAY , C.NAME_FORMAL , B.SAME_ADDRESS_EMPL , B.COUNTRY , B.ADDRESS1 , B.ADDRESS2 , B.ADDRESS3 , B.ADDRESS4 , B.CITY , B.NUM1 , B.NUM2 , B.HOUSE_TYPE , B.ADDR_FIELD1 , B.ADDR_FIELD2 , B.ADDR_FIELD3 , B.COUNTY , B.STATE , B.POSTAL , B.GEO_CODE , B.IN_CITY_LIMIT , A.COUNTRY_CODE , A.PHONE , D.RELATIONSHIP , D.DEP_BENEF_TYPE , D.MAR_STATUS , D.MAR_STATUS_DT , D.SEX , A.BIRTHDATE , A.BIRTHPLACE , A.BIRTHSTATE , A.BIRTHCOUNTRY , D.OCCUPATION , A.DT_OF_DEATH , D.STUDENT , D.DISABLED , A.MEDICARE_ENTLD_DT , A.COBRA_EVENT_DT , A.CSB_ELIG , A.COBRA_ACTION , A.COBRA_EMPLID , D.STUDENT_STATUS_DT , D.SMOKER , A.DEPBEN_RIDER_FLG , A.GVT_FEHB_IND , A.GVT_DEP_BENEF_PLAN , A.SAME_PHONE_EMPL , A.PHONE_TYPE , B.ADDRESS_TYPE , A.DEP_ELIGIBLE_UK , A.DEP_ADOPTED_UK , A.DEP_ADOPTION_DT_UK , A.DEP_CERTIFICATE_UK , A.FP_DEPEND_EMPLID , A.FP_STAT_FONC , A.FP_EMPLOYER_NAME , A.FP_SAME_PUBL_SCE , A.LAST_UPDATE_DATE FROM PS_DEP_BEN A , PS_DEP_BEN_ADDR B , PS_DEP_BEN_NAME C , PS_DEP_BEN_EFF D WHERE B.EMPLID = A.EMPLID AND B.DEPENDENT_BENEF = A.DEPENDENT_BENEF AND C.EMPLID = A.EMPLID AND C.DEPENDENT_BENEF = A.DEPENDENT_BENEF AND D.EMPLID = A.EMPLID AND D.DEPENDENT_BENEF = A.DEPENDENT_BENEF

  • Related Language Record: DEP_BEN_EFF_LNG

  • Parent record: PERSON

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Prompt Table: PERSON

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

    Default Value: 01

    3 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    4 EFFDT_NM Date(10) DATE Field for keeping the effdt for names field for a duplicate row check.
    5 EFFDT_NEW Date(10) DATE New Effective Date
    6 COUNTRY_NM_FORMAT Character(3) VARCHAR2(3) NOT NULL Supported Name Format Types

    Default Value: OPR_DEF_TBL_HR.COUNTRY_NM_FORMAT

    Prompt Table: NAME_FORMAT_TBL

    7 NAME Character(50) VARCHAR2(50) NOT NULL Name
    8 NAME_INITIALS Character(6) VARCHAR2(6) NOT NULL Name Initials
    9 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix

    Prompt Table: NAME_PREFIX_TBL

    10 NAME_SUFFIX Character(15) VARCHAR2(15) NOT NULL Name Suffix

    Prompt Table: NAME_SUFFIX_TBL

    11 NAME_ROYAL_PREFIX Character(15) VARCHAR2(15) NOT NULL Name Royal Prefix

    Prompt Table: NM_ROYPREF_GBL

    12 NAME_ROYAL_SUFFIX Character(15) VARCHAR2(15) NOT NULL Name - Royal Suffix

    Prompt Table: NM_ROYSUFF_GBL

    13 NAME_TITLE Character(30) VARCHAR2(30) NOT NULL Name Title

    Prompt Table: TITLE_TBL

    14 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
    15 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
    16 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
    17 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
    18 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
    19 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
    20 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Last Name
    21 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
    22 PREF_FIRST_NAME Character(30) VARCHAR2(30) NOT NULL Preferred First Name
    23 PARTNER_LAST_NAME Character(30) VARCHAR2(30) NOT NULL The Partner Last Name is being used by the Netherlands for the employee name formatting, where a person can choose to use the partners last name as their prefered last name.
    24 PARTNER_ROY_PREFIX Character(15) VARCHAR2(15) NOT NULL The Partner Royal Prefix is being used by the Netherlands for the employee name formatting, where a person can choose to use the partners last name and prefix as their prefered last name.

    Prompt Table: NM_ROYPREF_GBL

    25 LAST_NAME_PREF_NLD Character(1) VARCHAR2(1) NOT NULL The field Last Name Preference is used in the Netherlands specific for people who are married to indicated their preference regarding the last name, which can be 4 choices based on the own last name and the partners last name.
    1=Own Name
    2=Name Partner
    3=Name Partner + Own Name
    4=Own Name + Name Partner

    Default Value: 1

    26 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    27 NAME_FORMAL Character(60) VARCHAR2(60) NOT NULL Formal Name - name formatted for Formal Display by COuntry. Normally used in Correspondence.
    28 SAME_ADDRESS_EMPL Character(1) VARCHAR2(1) NOT NULL Same Address as Employee

    Y/N Table Edit

    Default Value: N

    29 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

    Prompt Table: COUNTRY_TBL

    30 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
    31 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
    32 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
    33 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
    34 CITY Character(30) VARCHAR2(30) NOT NULL City
    35 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
    36 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
    37 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
    AB=House Boat Reference
    WW=House Trailer Reference
    38 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
    39 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
    40 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
    41 COUNTY Character(30) VARCHAR2(30) NOT NULL County
    42 STATE Character(6) VARCHAR2(6) NOT NULL State

    Prompt Table: STATE_TBL

    43 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    44 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
    45 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

    Y/N Table Edit

    46 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Country Code
    47 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
    48 RELATIONSHIP Character(2) VARCHAR2(2) NOT NULL Relationship to Employee
    49 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
    50 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

    51 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. */
    52 SEX Character(1) VARCHAR2(1) NOT NULL Gender
    F=Female
    M=Male
    U=Unknown

    Default Value: M

    53 BIRTHDATE Date(10) DATE Date of Birth
    54 BIRTHPLACE Character(30) VARCHAR2(30) NOT NULL Birth Location
    55 BIRTHSTATE Character(6) VARCHAR2(6) NOT NULL Birth State

    Prompt Table: BIRTHSTATE_VW

    56 BIRTHCOUNTRY Character(3) VARCHAR2(3) NOT NULL Birth Country

    Prompt Table: COUNTRY_TBL

    57 OCCUPATION Character(40) VARCHAR2(40) NOT NULL Occup
    58 DT_OF_DEATH Date(10) DATE Date of Death
    59 STUDENT Character(1) VARCHAR2(1) NOT NULL Student
    N=No
    Y=Yes

    Y/N Table Edit

    Default Value: N

    60 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

    61 MEDICARE_ENTLD_DT Date(10) DATE Date Entitled to Medicare
    62 COBRA_EVENT_DT Date(10) DATE COBRA Event Date
    63 CSB_ELIG Character(1) VARCHAR2(1) NOT NULL Eligible for CSB

    Y/N Table Edit

    Default Value: N

    64 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.
    65 COBRA_EMPLID Character(11) VARCHAR2(11) NOT NULL COBRA Generated Emplid
    66 STUDENT_STATUS_DT Date(10) DATE Student Status Date
    67 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

    68 DEPBEN_RIDER_FLG Character(1) VARCHAR2(1) NOT NULL Court Order Flag

    Y/N Table Edit

    Default Value: N

    69 GVT_FEHB_IND Character(1) VARCHAR2(1) NOT NULL FEHB Participant

    Y/N Table Edit

    Default Value: N

    70 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
    71 SAME_PHONE_EMPL Character(1) VARCHAR2(1) NOT NULL Same Phone as Employee

    Y/N Table Edit

    Default Value: N

    72 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

    73 ADDRESS_TYPE Character(4) VARCHAR2(4) NOT NULL Address Type

    Prompt Table: PERSON_ADDRESS

    74 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

    75 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

    76 DEP_ADOPTION_DT_UK Date(10) DATE Date used in UK Parental Leave to indicate the adoption date for an adopted dependent.
    77 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

    78 FP_DEPEND_EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID of the Dependent

    Prompt Table: PERSON_NAME

    79 FP_STAT_FONC Character(1) VARCHAR2(1) NOT NULL Civil Servant Status

    Y/N Table Edit

    Default Value: N

    80 FP_EMPLOYER_NAME Character(30) VARCHAR2(30) NOT NULL Employer Name
    81 FP_SAME_PUBL_SCE Character(1) VARCHAR2(1) NOT NULL Same Public Service

    Y/N Table Edit

    Default Value: N

    82 LAST_UPDATE_DATE Date(10) DATE Date of last update

    Default Value: %Date