DEPENDENT_BENEF

(SQL View)
 Index Back

EE Dependents/Beneficiaries

DEPENDENT_BENEF is used to record an employee's dependents and beneficiaries.

SELECT A.EMPLID , A.DEPENDENT_BENEF , 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 (B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_DEP_BEN_ADDR B1 WHERE B1.EMPLID = B.EMPLID AND B1.DEPENDENT_BENEF = B.DEPENDENT_BENEF AND B1.EFFDT <= %CurrentDateIn)) AND C.EMPLID = A.EMPLID AND C.DEPENDENT_BENEF = A.DEPENDENT_BENEF AND (C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_DEP_BEN_NAME C1 WHERE C1.EMPLID = C.EMPLID AND C1.DEPENDENT_BENEF = C.DEPENDENT_BENEF AND C1.EFFDT <= %CurrentDateIn)) AND D.EMPLID = A.EMPLID AND D.DEPENDENT_BENEF = A.DEPENDENT_BENEF AND (D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_DEP_BEN_EFF D1 WHERE D1.EMPLID = D.EMPLID AND D1.DEPENDENT_BENEF = D.DEPENDENT_BENEF AND D1.EFFDT <= %CurrentDateIn))

  • Related Language Record: DEPEND_BEN_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 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

    4 NAME Character(50) VARCHAR2(50) NOT NULL Name
    5 NAME_INITIALS Character(6) VARCHAR2(6) NOT NULL Name Initials
    6 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix

    Prompt Table: NAME_PREFIX_TBL

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

    Prompt Table: NAME_SUFFIX_TBL

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

    Prompt Table: NM_ROYPREF_GBL

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

    Prompt Table: NM_ROYSUFF_GBL

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

    Prompt Table: TITLE_TBL

    11 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
    12 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
    13 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
    14 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
    15 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
    16 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
    17 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Last Name
    18 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
    19 PREF_FIRST_NAME Character(30) VARCHAR2(30) NOT NULL Preferred First Name
    20 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.
    21 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

    22 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

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

    Y/N Table Edit

    Default Value: N

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

    Prompt Table: COUNTRY_TBL

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

    Prompt Table: STATE_TBL

    40 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    41 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
    42 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

    Y/N Table Edit

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

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

    Default Value: M

    50 BIRTHDATE Date(10) DATE Date of Birth
    51 BIRTHPLACE Character(30) VARCHAR2(30) NOT NULL Birth Location
    52 BIRTHSTATE Character(6) VARCHAR2(6) NOT NULL Birth State

    Prompt Table: BIRTHSTATE_VW

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

    Prompt Table: COUNTRY_TBL

    54 OCCUPATION Character(40) VARCHAR2(40) NOT NULL Occup
    55 DT_OF_DEATH Date(10) DATE Date of Death
    56 STUDENT Character(1) VARCHAR2(1) NOT NULL Student
    N=No
    Y=Yes

    Y/N Table Edit

    Default Value: N

    57 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

    58 MEDICARE_ENTLD_DT Date(10) DATE Date Entitled to Medicare
    59 COBRA_EVENT_DT Date(10) DATE COBRA Event Date
    60 CSB_ELIG Character(1) VARCHAR2(1) NOT NULL Eligible for CSB

    Y/N Table Edit

    Default Value: N

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

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

    Y/N Table Edit

    Default Value: N

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

    Y/N Table Edit

    Default Value: N

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

    Y/N Table Edit

    Default Value: N

    69 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

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

    Prompt Table: PERSON_ADDRESS

    71 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

    72 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

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

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

    Prompt Table: PERSON_NAME

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

    Y/N Table Edit

    Default Value: N

    77 FP_EMPLOYER_NAME Character(30) VARCHAR2(30) NOT NULL Employer Name
    78 FP_SAME_PUBL_SCE Character(1) VARCHAR2(1) NOT NULL Same Public Service

    Y/N Table Edit

    Default Value: N

    79 LAST_UPDATE_DATE Date(10) DATE Date of last update

    Default Value: %Date