(CS) is not part of the table name. A version of this table, PERSON_NAME - Current Primary Name View, is also available in HR.
Data Dictionary Links
Clicking the linked (blue) fields in any table will take you to the Data Dictionary and provide you with more information about that field as well as field values if they are available.
| Key | Record.Fieldname | Format | XLAT | Heading Text |
|---|---|---|---|---|
| ASOFDATE - As Of Date | Date | As Of | ||
| COUNTRY_NM_FORMAT - Format Using | Char3 | Use | ||
| → | EMPLID - Empl ID | Char11 | ID | |
| FIRST_NAME - First Name | Char30 | First Name | ||
| FIRST_NAME_SRCH - First Name | Char30 | First Name | ||
| LAST_NAME - Last Name | Char30 | Last | ||
| LAST_NAME_PREF_NLD - Last Name Preference | Char1 | N | Preference | |
| LAST_NAME_SRCH - Last Name | Char30 | Last | ||
| MIDDLE_NAME - Middle Name | Char30 | Middle | ||
| NAME - Name | Char50 | Name | ||
| NAME_AC - Alternate Character Name | Char50 | AC Name | ||
| NAME_DISPLAY - Display Name | Char50 | Display Name | ||
| NAME_DISPLAY_SRCH - Name | Char50 | Name | ||
| NAME_FORMAL - Formal Name | Char60 | Formal Name | ||
| NAME_INITIALS - Name Initials | Char6 | Initials | ||
| NAME_PREFIX - Name Prefix | Char4 | Prefix | ||
| NAME_PSFORMAT - Name (LN,FN) | Char50 | LN,FN | ||
| NAME_ROYAL_PREFIX - Name Royal Prefix | Char15 | Royal Prefix | ||
| NAME_ROYAL_SUFFIX - Name Royal Suffix | Char15 | Royal Suffix | ||
| NAME_SUFFIX - Name Suffix | Char15 | Suffix | ||
| NAME_TITLE - Title | Char30 | Title | ||
| NAME_TYPE - Type of Name | Char3 | Name Type | ||
| PARTNER_LAST_NAME - Last Name Partner | Char30 | Last Name | ||
| PARTNER_ROY_PREFIX - Prefix Partner | Char15 | Prefix | ||
| PREF_FIRST_NAME - Preferred First Name | Char30 | Preferred First | ||
| SECOND_LAST_NAME - Second Last Name | Char30 | Second Last | ||
| SECOND_LAST_SRCH - Second Name | Char30 | Second Name |
In CS, there are two core tables from which all name reporting tables are derived:
- NAMES
- NAME_TYPE_TBL
These tables are not available for reporting, but there are alternatives available in CS. The best choices are the PERSON_NAME - Current Primary Name View (CS) table which displays primary name only, and the UM_NAMES_VW - Person Names Query View which includes name history. Both of these pull from the NAMES and the NAME_TYPE_TBLs.
Additionally, there exists SCC_NAMES_QVW - Person Names Query View which is a field by field view of the NAMES table, and HCR_NM_TYP_I - Name Type Table Interface VW which is a field by field view of the NAME_TYPE_TBL.
*The NAMES table shows up on search in PS Query Manager, however, there are no data in the table in CS.
SELECT A.EMPLID,
A.COUNTRY_NM_FORMAT,
A.NAME_DISPLAY,
A.NAME_INITIALS,
A.NAME_PREFIX,
A.NAME_SUFFIX,
A.NAME_ROYAL_PREFIX,
A.NAME_ROYAL_SUFFIX,
A.NAME_TITLE,
A.LAST_NAME_SRCH,
A.FIRST_NAME_SRCH,
A.LAST_NAME,
A.FIRST_NAME,
A.MIDDLE_NAME,
A.SECOND_LAST_NAME,
A.SECOND_LAST_SRCH,
A.NAME_AC,
A.PREF_FIRST_NAME,
A.PARTNER_LAST_NAME,
A.PARTNER_ROY_PREFIX,
A.LAST_NAME_PREF_NLD,
A.NAME_DISPLAY,
A.NAME_FORMAL,
A.NAME_DISPLAY_SRCH,
A.NAME_TYPE,
A.EFFDT,
A.NAME
FROM PS_NAMES A
WHERE 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 <= TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'),
'YYYY-MM-DD'));