SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[PS_PERSON_NAME] (EMPLID, COUNTRY_NM_FORMAT, NAME,
 NAME_INITIALS, NAME_PREFIX, NAME_SUFFIX, NAME_ROYAL_PREFIX,
 NAME_ROYAL_SUFFIX, NAME_TITLE, LAST_NAME_SRCH, FIRST_NAME_SRCH,
 LAST_NAME, FIRST_NAME, MIDDLE_NAME, SECOND_LAST_NAME,
 SECOND_LAST_SRCH, NAME_AC, PREF_FIRST_NAME, PARTNER_LAST_NAME,
 PARTNER_ROY_PREFIX, LAST_NAME_PREF_NLD, NAME_DISPLAY, NAME_FORMAL,
 NAME_TYPE, ASOFDATE, NAME_PSFORMAT) AS 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_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 <= { FN CURDATE()
 } )