DEPENDENT_VW

(SQL View)
 Index Back

Dependent Beneficiaries View


SELECT B.EMPLID , B.DEPENDENT_BENEF , B.DEPENDENT_BENEF , C.NAME_DISPLAY , C.NAME_AC , C.FIRST_NAME , C.LAST_NAME , E.SAME_ADDRESS_EMPL , %subrec(ADDRESS_SBR,E) , %subrec(PHONE_SBR,B) , D.RELATIONSHIP , D.DEP_BENEF_TYPE , D.MAR_STATUS , D.MAR_STATUS_DT , D.SEX , B.BIRTHDATE , B.BIRTHPLACE , B.BIRTHSTATE , B.BIRTHCOUNTRY , D.OCCUPATION , B.DT_OF_DEATH , D.STUDENT , D.DISABLED , B.MEDICARE_ENTLD_DT , B.COBRA_EVENT_DT , B.CSB_ELIG , B.COBRA_ACTION , B.COBRA_EMPLID , D.STUDENT_STATUS_DT , D.SMOKER , B.DEPBEN_RIDER_FLG , %subrec(DEPEND_FED_SBR,B) , B.SAME_PHONE_EMPL , B.PHONE_TYPE , E.ADDRESS_TYPE , %subrec(DEPEND_UK_SBR,B) , %subrec(DEPEND_FP_SBR,B) FROM PS_DEP_BEN B , PS_DEP_BEN_NAME C , PS_DEP_BEN_EFF D , PS_DEP_BEN_ADDR E WHERE B.EMPLID = C.EMPLID AND B.EMPLID = D.EMPLID AND B.EMPLID = E.EMPLID AND C.EMPLID = D.EMPLID AND C.EMPLID = E.EMPLID AND D.EMPLID = E.EMPLID AND B.DEPENDENT_BENEF = C.DEPENDENT_BENEF AND B.DEPENDENT_BENEF = D.DEPENDENT_BENEF AND B.DEPENDENT_BENEF = E.DEPENDENT_BENEF AND C.DEPENDENT_BENEF = D.DEPENDENT_BENEF AND C.DEPENDENT_BENEF = E.DEPENDENT_BENEF AND D.DEPENDENT_BENEF = E.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 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 E.EFFDT = ( SELECT MAX(E1.EFFDT) FROM PS_DEP_BEN_ADDR E1 WHERE E1.EMPLID = E.EMPLID AND E1.DEPENDENT_BENEF = E.DEPENDENT_BENEF)

  • Related Language Record: DEPEND_VW_LNG

    • # 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 DEPENDENT_ID Character(2) VARCHAR2(2) NOT NULL Dependent ID
    4 NAME Character(50) VARCHAR2(50) NOT NULL Name
    5 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
    6 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
    7 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
    8 SAME_ADDRESS_EMPL Character(1) VARCHAR2(1) NOT NULL Same Address as Employee

    Y/N Table Edit

    Default Value: N

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

    Prompt Table: SCC_CNT_ADFMTVW

    10 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
    11 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
    12 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
    13 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
    14 CITY Character(30) VARCHAR2(30) NOT NULL City
    15 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
    16 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
    17 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
    AB=House Boat Reference
    WW=House Trailer Reference
    18 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
    19 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
    20 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
    21 COUNTY Character(30) VARCHAR2(30) NOT NULL County
    22 STATE Character(6) VARCHAR2(6) NOT NULL State

    Prompt Table: STATE_TBL

    23 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    24 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
    25 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

    Y/N Table Edit

    26 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Country Code
    27 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
    28 RELATIONSHIP Character(2) VARCHAR2(2) NOT NULL Relationship to Employee
    29 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
    30 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

    31 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. */
    32 SEX Character(1) VARCHAR2(1) NOT NULL Gender
    F=Female
    M=Male
    U=Unknown
    X=Indeterminate/Intersex/Unspec

    Default Value: M

    33 BIRTHDATE Date(10) DATE Date of Birth
    34 BIRTHPLACE Character(30) VARCHAR2(30) NOT NULL Birth Location
    35 BIRTHSTATE Character(6) VARCHAR2(6) NOT NULL Birth State

    Prompt Table: BIRTHSTATE_VW

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

    Prompt Table: COUNTRY_TBL

    37 OCCUPATION Character(40) VARCHAR2(40) NOT NULL Occup
    38 DT_OF_DEATH Date(10) DATE Date of Death
    39 STUDENT Character(1) VARCHAR2(1) NOT NULL Student
    N=No
    Y=Yes

    Y/N Table Edit

    Default Value: N

    40 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

    41 MEDICARE_ENTLD_DT Date(10) DATE Date Entitled to Medicare
    42 COBRA_EVENT_DT Date(10) DATE COBRA Event Date
    43 CSB_ELIG Character(1) VARCHAR2(1) NOT NULL Eligible for CSB

    Y/N Table Edit

    Default Value: N

    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

    Y/N Table Edit

    Default Value: N

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

    Y/N Table Edit

    Default Value: N

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

    Y/N Table Edit

    Default Value: N

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

    Y/N Table Edit

    Default Value: N

    52 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

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

    Prompt Table: PERSON_ADDRESS

    54 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

    55 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

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

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

    Prompt Table: PERSON_NAME

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

    Y/N Table Edit

    Default Value: N

    60 FP_EMPLOYER_NAME Character(30) VARCHAR2(30) NOT NULL Employer Name
    61 FP_SAME_PUBL_SCE Character(1) VARCHAR2(1) NOT NULL Same Public Service

    Y/N Table Edit

    Default Value: N

    62 LAST_UPDATE_DATE Date(10) DATE Date of last update

    Default Value: %Date