HR_SRCH_PERS_VW

(SQL View)
 Index Back

Person Names View

View for Names. Used by Person Global Search for Company Directory. Joins data from Names, Address, Email and Phone

SELECT N.EMPLID ,N.NAME_TYPE ,N.EFFDT ,N.EFF_STATUS ,N.NAME_DISPLAY ,N.NAME ,N.NAME_AC ,N.LAST_NAME ,N.FIRST_NAME ,N.MIDDLE_NAME ,N.SECOND_LAST_NAME ,N.PREF_FIRST_NAME ,N.PREF_LAST_NAME ,N.PREF_MIDDLE_NAME ,N.PREF_SEC_LAST_NAME ,N.PREF_FIRST_NM_SRCH ,N.PREF_LAST_NM_SRCH ,N.PREF_MIDL_NM_SRCH ,N.PREF_LAST_SEC_SRCH ,N.PARTNER_LAST_NAME ,N.NAME_FORMAL ,A.ADDRESS_TYPE ,A.EFFDT ,A.EFF_STATUS ,A.COUNTRY ,COALESCE(C.DESCR ,M.MESSAGE_TEXT) ,A.STATE ,COALESCE(S.DESCR ,M.MESSAGE_TEXT) ,COALESCE(A.CITY ,M.MESSAGE_TEXT) ,COALESCE(A.CITY_AC ,M.MESSAGE_TEXT) ,A.ADDRESS1 ,A.ADDRESS2 ,A.ADDRESS3 ,A.ADDRESS4 ,A.ADDRESS1_AC ,A.ADDRESS2_AC ,A.ADDRESS3_AC ,A.POSTAL ,P.COUNTRY_CODE ,P.PHONE ,P.EXTENSION ,E.EMAIL_ADDR ,PRS.LAST_CHILD_UPDDTM ,N.LASTUPDDTTM FROM PS_PERSON PRS ,PS_NAMES N LEFT OUTER JOIN PS_HR_SRCH_ADDR A ON A.EMPLID=N.EMPLID LEFT OUTER JOIN PS_COUNTRY_TBL C ON A.COUNTRY=C.COUNTRY LEFT OUTER JOIN PS_STATE_TBL S ON A.COUNTRY=S.COUNTRY AND A.STATE=S.STATE LEFT OUTER JOIN PS_HR_SRCH_PHONE P ON P.EMPLID=N.EMPLID LEFT OUTER JOIN PS_HR_SRCH_EMAIL E ON E.EMPLID=N.EMPLID ,PSMSGCATDEFN M WHERE PRS.EMPLID=N.EMPLID AND N.NAME_TYPE='PRI' AND N.EFFDT = ( SELECT MAX(N_ED.EFFDT) FROM PS_NAMES N_ED WHERE N.EMPLID = N_ED.EMPLID AND N.NAME_TYPE = N_ED.NAME_TYPE AND N_ED.EFFDT <= %CurrentDateIn) AND M.MESSAGE_SET_NBR=1000 AND M.MESSAGE_NBR=1746

  • Related Language Record: HR_SRCH_PRS_LNG

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

    Prompt Table: PERSON

    2 NAME_TYPE Character(3) VARCHAR2(3) NOT NULL Type of Name

    Prompt Table: NAME_TYPE_TBL

    3 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    4 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive

    Default Value: A

    5 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    6 NAME Character(50) VARCHAR2(50) NOT NULL Name
    7 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
    8 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
    9 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
    10 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
    11 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
    12 PREF_FIRST_NAME Character(30) VARCHAR2(30) NOT NULL Preferred First Name
    13 PREF_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Preferred Last Name
    14 PREF_MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL Preferred Middle Name
    15 PREF_SEC_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Preferred Second Last Name
    16 PREF_FIRST_NM_SRCH Character(30) VARCHAR2(30) NOT NULL Preferred First Name
    17 PREF_LAST_NM_SRCH Character(30) VARCHAR2(30) NOT NULL Uppercase version of preferred last name to improve performance on searches.
    18 PREF_MIDL_NM_SRCH Character(30) VARCHAR2(30) NOT NULL Uppercase version of preferred middle name to improve performance on searches.
    19 PREF_LAST_SEC_SRCH Character(30) VARCHAR2(30) NOT NULL Uppercase version of preferred second last name to improve performance on searches.
    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 NAME_FORMAL Character(60) VARCHAR2(60) NOT NULL Formal Name - name formatted for Formal Display by COuntry. Normally used in Correspondence.
    22 ADDRESS_TYPE Character(4) VARCHAR2(4) NOT NULL Address Type

    Prompt Table: ADDRESS_TYP_TBL

    23 DATE1 Date(10) DATE Date 1
    24 STATUS Character(1) VARCHAR2(1) NOT NULL Status Indicator
    A=Active
    I=Inactive
    25 COUNTRY_CD Character(3) VARCHAR2(3) NOT NULL Country Code
    26 HR_SRCH_CNTRY_DESC Character(30) VARCHAR2(30) NOT NULL Country
    27 STATE Character(6) VARCHAR2(6) NOT NULL State

    Prompt Table: STATE_TBL

    28 HR_SRCH_STATE_DESC Character(30) VARCHAR2(30) NOT NULL State
    29 CITY Character(30) VARCHAR2(30) NOT NULL City
    30 CITY_AC Character(30) VARCHAR2(30) NOT NULL Alternate Character City
    31 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
    32 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
    33 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
    34 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
    35 ADDRESS1_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 1
    36 ADDRESS2_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 2
    37 ADDRESS3_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 3
    38 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    39 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Country Code
    40 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
    41 EXTENSION Character(6) VARCHAR2(6) NOT NULL Phone number extension
    42 EMAIL_ADDR Character(70) VARCHAR2(70) NOT NULL Email Address
    43 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
    44 LAST_UPDATE_DTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.