PERSONAL_DT_FST

(SQL View)
 Index Back

PERSONAL_DT_FST

This view is very similar to the PERSONAL_DATA View except that it is more efficient. But to do that it does have different criteria and fields than PERSONAL_DATA. It does not contain Address or Phone information so it does not have to do any outer joins.

SELECT PER.EMPLID ,%Sql(NAME_SBR_SQL,A) ,PDE.SEX ,PDE.MAR_STATUS ,PDE.MAR_STATUS_DT ,PER.BIRTHDATE ,PER.BIRTHPLACE ,PER.BIRTHCOUNTRY ,PER.BIRTHSTATE ,PER.DT_OF_DEATH ,PDE.HIGHEST_EDUC_LVL ,PDE.FT_STUDENT ,PDE.LANG_CD ,PDE.ALTER_EMPLID FROM PS_PERSON PER , PS_PERS_DATA_EFFDT PDE , PS_NAMES A WHERE PER.EMPLID = A.EMPLID AND PER.EMPLID = PDE.EMPLID AND PDE.EMPLID = A.EMPLID AND PDE.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_PERS_DATA_EFFDT B WHERE B.EMPLID = PDE.EMPLID AND B.EFFDT <= %CurrentDateIn ) AND A.NAME_TYPE = 'PRI' AND A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_NAMES B WHERE B.EMPLID = A.EMPLID AND B.NAME_TYPE = A.NAME_TYPE AND B.EFFDT <= %CurrentDateIn )

  • Parent record: PERSON

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

    Default Value: NEW

    2 COUNTRY_NM_FORMAT Character(3) VARCHAR2(3) NOT NULL Supported Name Format Types

    Prompt Table: NAME_FORMAT_TBL

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

    Prompt Table: NAME_PREFIX_TBL

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

    Prompt Table: NAME_SUFFIX_TBL

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

    Prompt Table: NM_ROYPREF_GBL

    8 NAME_ROYAL_SUFFIX Character(15) VARCHAR2(15) NOT NULL Name Royal Suffix

    Prompt Table: NM_ROYSUFF_GBL

    9 NAME_TITLE Character(30) VARCHAR2(30) NOT NULL Title

    Prompt Table: TITLE_TBL

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

    21 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

    22 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    23 NAME_FORMAL Character(60) VARCHAR2(60) NOT NULL Formal Name - name formatted for Formal Display by COuntry. Normally used in Correspondence.
    24 SEX Character(1) VARCHAR2(1) NOT NULL Gender
    F=Female
    M=Male
    U=Unknown
    X=Indeterminate/Intersex/Unspec
    25 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
    26 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. */
    27 BIRTHDATE Date(10) DATE Date of Birth
    28 BIRTHPLACE Character(30) VARCHAR2(30) NOT NULL Birth Location
    29 BIRTHCOUNTRY Character(3) VARCHAR2(3) NOT NULL Birth Country

    Prompt Table: COUNTRY_TBL

    30 BIRTHSTATE Character(6) VARCHAR2(6) NOT NULL Birth State

    Prompt Table: BIRTHSTATE_VW

    31 DT_OF_DEATH Date(10) DATE Date of Death
    32 HIGHEST_EDUC_LVL Character(2) VARCHAR2(2) NOT NULL Highest Education Level
    33 FT_STUDENT Character(1) VARCHAR2(1) NOT NULL Full-Time Student

    Y/N Table Edit

    34 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
    35 ALTER_EMPLID Character(11) VARCHAR2(11) NOT NULL Alternate Employee ID