NAMES_VW1 (SQL View)	Index Back
Effective Dated Names Vw This view retrieves the currently effective Names record for each Name Type.

NAMES_VW1

(SQL View)

Effective Dated Names Vw

This view retrieves the currently effective Names record for each Name Type.

SELECT A.EMPLID ,A.NAME_TYPE ,A.EFFDT ,A.LAST_NAME ,A.FIRST_NAME ,A.MIDDLE_NAME ,A.NAME_PREFIX ,A.NAME_SUFFIX ,A.LAST_NAME_SRCH ,A.FIRST_NAME_SRCH ,A.NAME_DISPLAY ,A.PREF_FIRST_NAME ,A.NAME_DISPLAY_SRCH ,A.SECOND_LAST_SRCH ,A.LASTUPDDTTM ,A.NAME_DISPLAY ,A.NAME_AC ,A.SECOND_LAST_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 <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
Prompt Table: PEOPLE_SRCH

2 NAME_TYPE Character(3) VARCHAR2(3) NOT NULL Type of Name
Prompt Table: HCR_NM_TYP_I

3 EFFDT Date(10) DATE NOT NULL Effective Date

4 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name

5 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name

6 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME

7 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix
Prompt Table: NAME_PREFIX_TBL

8 NAME_SUFFIX Character(15) VARCHAR2(15) NOT NULL Name Suffix
Prompt Table: NAME_SUFFIX_TBL

9 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name

10 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name

11 NAME Character(50) VARCHAR2(50) NOT NULL Name

12 PREF_FIRST_NAME Character(30) VARCHAR2(30) NOT NULL Preferred First Name

13 NAME_DISPLAY_SRCH Character(50) VARCHAR2(50) NOT NULL Uppercase version of Name to improve performance on searches.

14 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Last Name

15 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.

16 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country

17 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name

18 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: PEOPLE_SRCH
2	NAME_TYPE	Character(3)	VARCHAR2(3) NOT NULL	Type of Name Prompt Table: HCR_NM_TYP_I
3	EFFDT	Date(10)	DATE NOT NULL	Effective Date
4	LAST_NAME	Character(30)	VARCHAR2(30) NOT NULL	Last Name
5	FIRST_NAME	Character(30)	VARCHAR2(30) NOT NULL	First Name
6	MIDDLE_NAME	Character(30)	VARCHAR2(30) NOT NULL	MIDDLE_NAME
7	NAME_PREFIX	Character(4)	VARCHAR2(4) NOT NULL	Name Prefix Prompt Table: NAME_PREFIX_TBL
8	NAME_SUFFIX	Character(15)	VARCHAR2(15) NOT NULL	Name Suffix Prompt Table: NAME_SUFFIX_TBL
9	LAST_NAME_SRCH	Character(30)	VARCHAR2(30) NOT NULL	Last Name
10	FIRST_NAME_SRCH	Character(30)	VARCHAR2(30) NOT NULL	First Name
11	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
12	PREF_FIRST_NAME	Character(30)	VARCHAR2(30) NOT NULL	Preferred First Name
13	NAME_DISPLAY_SRCH	Character(50)	VARCHAR2(50) NOT NULL	Uppercase version of Name to improve performance on searches.
14	SECOND_LAST_SRCH	Character(30)	VARCHAR2(30) NOT NULL	Second Last Name
15	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.
16	NAME_DISPLAY	Character(50)	VARCHAR2(50) NOT NULL	Display Name - name formatted for Display based on the Country
17	NAME_AC	Character(50)	VARCHAR2(50) NOT NULL	Alternate Character Name
18	SECOND_LAST_NAME	Character(30)	VARCHAR2(30) NOT NULL	Second Last Name