HR_SRCH_NM_VW1 (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

HR_SRCH_NM_VW1

(SQL View)

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.PARTNER_LAST_NAME ,N.NAME_FORMAL ,A.ADDRESS_TYPE ,A.EFFDT ,A.EFF_STATUS ,A.COUNTRY ,C.DESCR , A.STATE ,S.DESCR ,A.CITY ,A.CITY_AC ,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 ,N.LASTUPDDTTM ,A.LASTUPDDTTM FROM 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 WHERE 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)

Related Language Record: HR_SRCH_NM_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 NOT NULL 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 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.

14 NAME_FORMAL Character(60) VARCHAR2(60) NOT NULL Formal Name - name formatted for Formal Display by COuntry. Normally used in Correspondence.

15 ADDRESS_TYPE Character(4) VARCHAR2(4) NOT NULL Address Type
Prompt Table: ADDRESS_TYP_TBL

16 DATE1 Date(10) DATE Date 1

17 STATUS Character(1) VARCHAR2(1) NOT NULL Status Indicator
A=Active
I=Inactive

18 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
Prompt Table: COUNTRY_TBL

19 COUNTRY_DESCR Character(30) VARCHAR2(30) NOT NULL Country Description

20 STATE Character(6) VARCHAR2(6) NOT NULL State
Prompt Table: STATE_TBL

21 STATE_DESCR Character(30) VARCHAR2(30) NOT NULL State Description

22 CITY Character(30) VARCHAR2(30) NOT NULL City

23 CITY_AC Character(30) VARCHAR2(30) NOT NULL Alternate Character City

24 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1

25 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2

26 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3

27 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4

28 ADDRESS1_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 1

29 ADDRESS2_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 2

30 ADDRESS3_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 3

31 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal

32 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Country Code

33 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone

34 EXTENSION Character(6) VARCHAR2(6) NOT NULL Phone number extension

35 EMAIL_ADDR Character(70) VARCHAR2(70) NOT NULL Email Address

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

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

#	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 NOT NULL	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	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.
14	NAME_FORMAL	Character(60)	VARCHAR2(60) NOT NULL	Formal Name - name formatted for Formal Display by COuntry. Normally used in Correspondence.
15	ADDRESS_TYPE	Character(4)	VARCHAR2(4) NOT NULL	Address Type Prompt Table: ADDRESS_TYP_TBL
16	DATE1	Date(10)	DATE	Date 1
17	STATUS	Character(1)	VARCHAR2(1) NOT NULL	Status Indicator A=Active I=Inactive
18	COUNTRY	Character(3)	VARCHAR2(3) NOT NULL	Country Prompt Table: COUNTRY_TBL
19	COUNTRY_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Country Description
20	STATE	Character(6)	VARCHAR2(6) NOT NULL	State Prompt Table: STATE_TBL
21	STATE_DESCR	Character(30)	VARCHAR2(30) NOT NULL	State Description
22	CITY	Character(30)	VARCHAR2(30) NOT NULL	City
23	CITY_AC	Character(30)	VARCHAR2(30) NOT NULL	Alternate Character City
24	ADDRESS1	Character(55)	VARCHAR2(55) NOT NULL	Address 1
25	ADDRESS2	Character(55)	VARCHAR2(55) NOT NULL	Address 2
26	ADDRESS3	Character(55)	VARCHAR2(55) NOT NULL	Address 3
27	ADDRESS4	Character(55)	VARCHAR2(55) NOT NULL	Address 4
28	ADDRESS1_AC	Character(55)	VARCHAR2(55) NOT NULL	Alternate Character Address 1
29	ADDRESS2_AC	Character(55)	VARCHAR2(55) NOT NULL	Alternate Character Address 2
30	ADDRESS3_AC	Character(55)	VARCHAR2(55) NOT NULL	Alternate Character Address 3
31	POSTAL	Character(12)	VARCHAR2(12) NOT NULL	Postal
32	COUNTRY_CODE	Character(3)	VARCHAR2(3) NOT NULL	Country Code
33	PHONE	Character(24)	VARCHAR2(24) NOT NULL	Telephone
34	EXTENSION	Character(6)	VARCHAR2(6) NOT NULL	Phone number extension
35	EMAIL_ADDR	Character(70)	VARCHAR2(70) NOT NULL	Email Address
36	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.
37	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.