EMPLIDNAME_TYPEPrompt Table: NAME_TYPE_TBL
EFFDTDefault Value: %date
LANGUAGE_CDHR_SRCH_PRS_LNG(SQL View) |
Index Back |
|---|---|
Rel Lang Vw for HR_SRCH_NM_VW1 |
| SELECT P.EMPLID , N.NAME_TYPE , N.EFFDT , L.LANGUAGE_CD , 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 ,COALESCE(( SELECT CL.DESCR FROM PS_COUNTRYTBL_LANG CL WHERE CL.COUNTRY = A.COUNTRY AND CL.LANGUAGE_CD=L.LANGUAGE_CD), ( SELECT C.DESCR FROM PS_COUNTRY_TBL C WHERE C.COUNTRY = A.COUNTRY), ( SELECT M.MESSAGE_TEXT FROM PSMSGCATLANG M WHERE M.MESSAGE_SET_NBR=1000 AND M.MESSAGE_NBR=1746 AND M.LANGUAGE_CD=L.LANGUAGE_CD), ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR=1000 AND M.MESSAGE_NBR=1746)) , COALESCE(( SELECT SL.DESCR FROM PS_STATE_TBL_LANG SL WHERE SL.COUNTRY = A.COUNTRY AND SL.STATE = A.STATE AND SL.LANGUAGE_CD=L.LANGUAGE_CD), ( SELECT S.DESCR FROM PS_STATE_TBL S WHERE S.COUNTRY = A.COUNTRY AND S.STATE = A.STATE), ( SELECT M.MESSAGE_TEXT FROM PSMSGCATLANG M WHERE M.MESSAGE_SET_NBR=1000 AND M.MESSAGE_NBR=1746 AND M.LANGUAGE_CD=L.LANGUAGE_CD), ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR=1000 AND M.MESSAGE_NBR=1746)) FROM PS_PERSON P LEFT OUTER JOIN PS_HR_SRCH_ADDR A ON A.EMPLID=P.EMPLID, PS_NAMES_LNG N, PSLANGUAGES L WHERE N.EMPLID = P.EMPLID AND N.NAME_TYPE = 'PRI' AND N.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(NAMES_LNG N1, N, %CurrentDateIn) AND L.INSTALLED = 1 AND L.LANGUAGE_CD <> ( SELECT O.LANGUAGE_CD FROM PSOPTIONS O) UNION SELECT P.EMPLID , N.NAME_TYPE , N.EFFDT , L.LANGUAGE_CD , 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 ,COALESCE(( SELECT CL.DESCR FROM PS_COUNTRYTBL_LANG CL WHERE CL.COUNTRY = A.COUNTRY AND CL.LANGUAGE_CD=L.LANGUAGE_CD), ( SELECT C.DESCR FROM PS_COUNTRY_TBL C WHERE C.COUNTRY = A.COUNTRY), ( SELECT M.MESSAGE_TEXT FROM PSMSGCATLANG M WHERE M.MESSAGE_SET_NBR=1000 AND M.MESSAGE_NBR=1746 AND M.LANGUAGE_CD=L.LANGUAGE_CD), ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR=1000 AND M.MESSAGE_NBR=1746)) , COALESCE(( SELECT SL.DESCR FROM PS_STATE_TBL_LANG SL WHERE SL.COUNTRY = A.COUNTRY AND SL.STATE = A.STATE AND SL.LANGUAGE_CD=L.LANGUAGE_CD), ( SELECT S.DESCR FROM PS_STATE_TBL S WHERE S.COUNTRY = A.COUNTRY AND S.STATE = A.STATE), ( SELECT M.MESSAGE_TEXT FROM PSMSGCATLANG M WHERE M.MESSAGE_SET_NBR=1000 AND M.MESSAGE_NBR=1746 AND M.LANGUAGE_CD=L.LANGUAGE_CD), ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR=1000 AND M.MESSAGE_NBR=1746)) FROM PS_PERSON P LEFT OUTER JOIN PS_HR_SRCH_ADDR A ON A.EMPLID=P.EMPLID, PS_NAMES N, PSLANGUAGES L WHERE N.EMPLID = P.EMPLID AND N.NAME_TYPE = 'PRI' AND %EffdtCheck(NAMES N1, N, %CurrentDateIn) AND L.INSTALLED = 1 AND L.LANGUAGE_CD <> ( SELECT O.LANGUAGE_CD FROM PSOPTIONS O) AND NOT EXISTS ( SELECT 'X' FROM PS_NAMES_LNG NL WHERE NL.EMPLID = P.EMPLID AND NL.NAME_TYPE = 'PRI' AND %EffdtCheck(NAMES_LNG NL1, NL, %CurrentDateIn)) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | NAME_TYPE |
Character(3) | VARCHAR2(3) NOT NULL |
Type of Name
Prompt Table: NAME_TYPE_TBL |
| 3 | EFFDT |
Date(10) | DATE NOT NULL |
Effective Date
Default Value: %date |
| 4 | LANGUAGE_CD |
Character(3) | VARCHAR2(3) NOT NULL | Language Code |
| 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 | HR_SRCH_CNTRY_DESC | Character(30) | VARCHAR2(30) NOT NULL | Country |
| 23 | HR_SRCH_STATE_DESC | Character(30) | VARCHAR2(30) NOT NULL | State |