HR_NAME_DTL_VW

(SQL View)
 Index Back

Name Change HTML Email View

Record being used with Workflow Name Change event

SELECT CUR.EMPLID , CUR.NAME_TYPE , NEW.ACTION_DT_SS ,NEW.EFFSEQ , %Sql(HR_HT_EMAIL_DATE,NEW.ACTION_DT_SS) , NF.DESCR , CASE WHEN NEW.COUNTRY_NM_FORMAT = CUR.COUNTRY_NM_FORMAT THEN ' ' ELSE NEW.COUNTRY_NM_FORMAT END , CASE WHEN NEW.COUNTRY_NM_FORMAT = CUR.COUNTRY_NM_FORMAT THEN ' ' ELSE CUR.COUNTRY_NM_FORMAT END , CASE WHEN NEW.FIRST_NAME=CUR.FIRST_NAME THEN ' ' ELSE NEW.FIRST_NAME END , CASE WHEN NEW.FIRST_NAME=CUR.FIRST_NAME THEN ' ' ELSE CUR.FIRST_NAME END , CASE WHEN NEW.LAST_NAME =CUR.LAST_NAME THEN ' ' ELSE NEW.LAST_NAME END , CASE WHEN NEW.LAST_NAME =CUR.LAST_NAME THEN ' ' ELSE CUR.LAST_NAME END , CASE WHEN NEW.SECOND_LAST_NAME =CUR.SECOND_LAST_NAME THEN ' ' ELSE NEW.SECOND_LAST_NAME END , CASE WHEN NEW.SECOND_LAST_NAME =CUR.SECOND_LAST_NAME THEN ' ' ELSE CUR.SECOND_LAST_NAME END , CASE WHEN NEW.NAME_INITIALS =CUR.NAME_INITIALS THEN ' ' ELSE NEW.NAME_INITIALS END , CASE WHEN NEW.NAME_INITIALS =CUR.NAME_INITIALS THEN ' ' ELSE CUR.NAME_INITIALS END , CASE WHEN NEW.NAME_PREFIX = CUR.NAME_PREFIX THEN ' ' ELSE NEW.NAME_PREFIX END , CASE WHEN NEW.NAME_PREFIX = CUR.NAME_PREFIX THEN ' ' ELSE CUR.NAME_PREFIX END , CASE WHEN NEW.NAME_SUFFIX = CUR.NAME_SUFFIX THEN ' ' ELSE NEW.NAME_SUFFIX END , CASE WHEN NEW.NAME_SUFFIX = CUR.NAME_SUFFIX THEN ' ' ELSE CUR.NAME_SUFFIX END , CASE WHEN NEW.NAME_ROYAL_PREFIX = CUR.NAME_ROYAL_PREFIX THEN ' ' ELSE NEW.NAME_ROYAL_PREFIX END , CASE WHEN NEW.NAME_ROYAL_PREFIX = CUR.NAME_ROYAL_PREFIX THEN ' ' ELSE CUR.NAME_ROYAL_PREFIX END , CASE WHEN NEW.NAME_TITLE = CUR.NAME_TITLE THEN ' ' ELSE NEW.NAME_TITLE END , CASE WHEN NEW.NAME_TITLE = CUR.NAME_TITLE THEN ' ' ELSE CUR.NAME_TITLE END ,CASE WHEN NEW.NAME_ROYAL_SUFFIX = CUR.NAME_ROYAL_SUFFIX THEN ' ' ELSE NEW.NAME_ROYAL_SUFFIX END , CASE WHEN NEW.NAME_ROYAL_SUFFIX = CUR.NAME_ROYAL_SUFFIX THEN ' ' ELSE CUR.NAME_ROYAL_SUFFIX END , CASE WHEN NEW.MIDDLE_NAME = CUR.MIDDLE_NAME THEN ' ' ELSE NEW.MIDDLE_NAME END , CASE WHEN NEW.MIDDLE_NAME = CUR.MIDDLE_NAME THEN ' ' ELSE CUR.MIDDLE_NAME END , CASE WHEN NEW.NAME_AC = CUR.NAME_AC THEN ' ' ELSE NEW.NAME_AC END , CASE WHEN NEW.NAME_AC = CUR.NAME_AC THEN ' ' ELSE CUR.NAME_AC END , CASE WHEN NEW.PREF_FIRST_NAME = CUR.PREF_FIRST_NAME THEN ' ' ELSE NEW.PREF_FIRST_NAME END , CASE WHEN NEW.PREF_FIRST_NAME = CUR.PREF_FIRST_NAME THEN ' ' ELSE CUR.PREF_FIRST_NAME END , CASE WHEN NEW.PREF_MIDDLE_NAME = CUR.PREF_MIDDLE_NAME THEN ' ' ELSE NEW.PREF_MIDDLE_NAME END , CASE WHEN NEW.PREF_MIDDLE_NAME = CUR.PREF_MIDDLE_NAME THEN ' ' ELSE CUR.PREF_MIDDLE_NAME END , CASE WHEN NEW.PREF_LAST_NAME = CUR.PREF_LAST_NAME THEN ' ' ELSE NEW.PREF_LAST_NAME END , CASE WHEN NEW.PREF_LAST_NAME = CUR.PREF_LAST_NAME THEN ' ' ELSE CUR.PREF_LAST_NAME END , CASE WHEN NEW.PREF_SEC_LAST_NAME = CUR.PREF_SEC_LAST_NAME THEN ' ' ELSE NEW.PREF_SEC_LAST_NAME END , CASE WHEN NEW.PREF_SEC_LAST_NAME = CUR.PREF_SEC_LAST_NAME THEN ' ' ELSE CUR.PREF_SEC_LAST_NAME END , CASE WHEN NEW.PREF_NAME_SUFFIX = CUR.PREF_NAME_SUFFIX THEN ' ' ELSE NEW.PREF_NAME_SUFFIX END , CASE WHEN NEW.PREF_NAME_SUFFIX = CUR.PREF_NAME_SUFFIX THEN ' ' ELSE CUR.PREF_NAME_SUFFIX END , CASE WHEN NEW.PREF_NAME_PREFIX = CUR.PREF_NAME_PREFIX THEN ' ' ELSE NEW.PREF_NAME_PREFIX END , CASE WHEN NEW.PREF_NAME_PREFIX = CUR.PREF_NAME_PREFIX THEN ' ' ELSE CUR.PREF_NAME_PREFIX END , CASE WHEN NEW.PREF_NAME_TITLE = CUR.PREF_NAME_TITLE THEN ' ' ELSE NEW.PREF_NAME_TITLE END , CASE WHEN NEW.PREF_NAME_TITLE = CUR.PREF_NAME_TITLE THEN ' ' ELSE CUR.PREF_NAME_TITLE END ,CASE WHEN NEW.PARTNER_LAST_NAME = CUR.PARTNER_LAST_NAME THEN ' ' ELSE NEW.PARTNER_LAST_NAME END ,CASE WHEN NEW.PARTNER_LAST_NAME = CUR.PARTNER_LAST_NAME THEN ' ' ELSE CUR.PARTNER_LAST_NAME END , CASE WHEN NEW.PARTNER_ROY_PREFIX = CUR.PARTNER_ROY_PREFIX THEN ' ' ELSE NEW.PARTNER_ROY_PREFIX END , CASE WHEN NEW.PARTNER_ROY_PREFIX = CUR.PARTNER_ROY_PREFIX THEN ' ' ELSE CUR.PARTNER_ROY_PREFIX END ,CASE WHEN NEW.LAST_NAME_PREF_NLD = CUR.LAST_NAME_PREF_NLD THEN ' ' ELSE NEW.LAST_NAME_PREF_NLD END ,CASE WHEN NEW.LAST_NAME_PREF_NLD = CUR.LAST_NAME_PREF_NLD THEN ' ' ELSE CUR.LAST_NAME_PREF_NLD END , CASE WHEN NEW.NAME = CUR.NAME_PSFORMAT THEN ' ' ELSE NEW.NAME END , CASE WHEN NEW.NAME =CUR.NAME_PSFORMAT THEN ' ' ELSE CUR.NAME_PSFORMAT END , CASE WHEN NEW.NAME_DISPLAY = CUR.NAME_DISPLAY THEN ' ' ELSE NEW.NAME_DISPLAY END , CASE WHEN NEW.NAME_DISPLAY = CUR.NAME_DISPLAY THEN ' ' ELSE CUR.NAME_DISPLAY END , CASE WHEN NEW.NAME_FORMAL = CUR.NAME_FORMAL THEN ' ' ELSE NEW.NAME_FORMAL END , CASE WHEN NEW.NAME_FORMAL = CUR.NAME_FORMAL THEN ' ' ELSE CUR.NAME_FORMAL END , CASE WHEN NEW.NAME_FORMAL_PSF = CUR.NAME_FORMAL_PSF THEN ' ' ELSE NEW.NAME_FORMAL_PSF END , CASE WHEN NEW.NAME_FORMAL_PSF = CUR.NAME_FORMAL_PSF THEN ' ' ELSE CUR.NAME_FORMAL_PSF END , NEW.WF_STATUS ,'X' ,NEW.APPROVAL_REQUIRED FROM PS_HR_NAME_DAT NEW , PS_PERSON_NAME CUR , PS_HR_NAME_STA STA , PS_NAME_FORMAT_TBL NF WHERE NEW.EMPLID=CUR.EMPLID AND NEW.EMPLID=STA.EMPLID AND STA.SEQNO=1 AND CUR.COUNTRY_NM_FORMAT=NF.COUNTRY_NM_FORMAT AND NF.EFF_STATUS='A' AND STA.EFFSEQ=NEW.EFFSEQ

# 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

Default Value: PRI

Prompt Table: NAME_TYPE_TBL

3 ACTION_DT_SS Date(10) DATE Termination Date
4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
6 DESCR Character(30) VARCHAR2(30) NOT NULL Description
7 COUNTRY_FORMAT_NW Character(3) VARCHAR2(3) NOT NULL Supported Name Format Types
8 COUNTRY_NM_FORMAT Character(3) VARCHAR2(3) NOT NULL Supported Name Format Types
9 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
10 FIRST_NAME_EDI Character(30) VARCHAR2(30) NOT NULL First Name
11 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
12 LAST_NAME_EDI Character(30) VARCHAR2(30) NOT NULL Last Name
13 SECONDLASTNAME_NW Character(30) VARCHAR2(30) NOT NULL Second Last Name
14 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
15 NAME_INITIALS_NW Character(6) VARCHAR2(6) NOT NULL Name Initials
16 NAME_INITIALS Character(6) VARCHAR2(6) NOT NULL Name Initials
17 NAME_PREFIX_1 Character(4) VARCHAR2(4) NOT NULL Name Prefix
18 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix
19 NAME_SUFFIX_1 Character(15) VARCHAR2(15) NOT NULL Name Suffix
20 NAME_SUFFIX Character(15) VARCHAR2(15) NOT NULL Name Suffix
21 NAMEROYALPREFIX_NW Character(15) VARCHAR2(15) NOT NULL Name Royal Prefix
22 NAME_ROYAL_PREFIX Character(15) VARCHAR2(15) NOT NULL Name Royal Prefix
23 NAME_TITLE_NW Character(30) VARCHAR2(30) NOT NULL Name Title
24 NAME_TITLE Character(30) VARCHAR2(30) NOT NULL Name Title
25 NAMEROYALSUFFIX_NW Character(15) VARCHAR2(15) NOT NULL Name - Royal Suffix
26 NAME_ROYAL_SUFFIX Character(15) VARCHAR2(15) NOT NULL Name - Royal Suffix
27 MIDDLE_NAME_NW Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
28 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
29 NAME_AC_SEC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
30 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
31 PREF_FIRST_NAME_NW Character(30) VARCHAR2(30) NOT NULL Preferred First Name
32 PREF_FIRST_NAME Character(30) VARCHAR2(30) NOT NULL Preferred First Name
33 PREF_MIDDLE_NAM_NW Character(30) VARCHAR2(30) NOT NULL Preferred Middle Name new, used to display changes in fluid approvals
34 PREF_MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL Preferred Middle Name
35 PREF_LAST_NAME_NW Character(30) VARCHAR2(30) NOT NULL Preferred Last Name new, used to display changes in fluid approvals
36 PREF_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Preferred Last Name
37 PREF_SEC_LST_NM_NW Character(30) VARCHAR2(30) NOT NULL Preferred Second Last Name new, used to display changes in fluid approvals
38 PREF_SEC_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Preferred Second Last Name
39 PREF_NAME_SUFFI_NW Character(15) VARCHAR2(15) NOT NULL Preferred Name Suffix new, used to display changes in fluid approvals
40 PREF_NAME_SUFFIX Character(15) VARCHAR2(15) NOT NULL Preferred Name Suffix
41 PREF_NAME_PREFI_NW Character(4) VARCHAR2(4) NOT NULL Preferred Name Prefix new, used to display changes in fluid approvals
42 PREF_NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Preferred Name Prefix
43 PREF_NAME_TITLE_NW Character(30) VARCHAR2(30) NOT NULL Preferred Name Title new, used to display changes in fluid approvals
44 PREF_NAME_TITLE Character(30) VARCHAR2(30) NOT NULL Preferred Name Title
45 PARTNERLASTNAME_NW 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.
46 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.
47 PARTNERROYPREFIX_N Character(15) VARCHAR2(15) NOT NULL The Partner Royal Prefix is being used by the Netherlands for the employee name formatting, where a person can choose to use the partners last name and prefix as their prefered last name.
48 PARTNER_ROY_PREFIX Character(15) VARCHAR2(15) NOT NULL The Partner Royal Prefix is being used by the Netherlands for the employee name formatting, where a person can choose to use the partners last name and prefix as their prefered last name.
49 LASTNAMEPREFNLD_NW Character(1) VARCHAR2(1) NOT NULL The field Last Name Preference is used in the Netherlands specific for people who are married to indicated their preference regarding the last name, which can be 4 choices based on the own last name and the partners last name.
1=Own Name
2=Name Partner
3=Name Partner + Own Name
4=Own Name + Name Partner
50 LAST_NAME_PREF_NLD Character(1) VARCHAR2(1) NOT NULL The field Last Name Preference is used in the Netherlands specific for people who are married to indicated their preference regarding the last name, which can be 4 choices based on the own last name and the partners last name.
1=Own Name
2=Name Partner
3=Name Partner + Own Name
4=Own Name + Name Partner
51 NAME_NEW Character(50) VARCHAR2(50) NOT NULL Name
52 NAME Character(50) VARCHAR2(50) NOT NULL Name
53 NAME_DISPLAY1 Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
54 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
55 NAME_FORMAL_NW Character(60) VARCHAR2(60) NOT NULL Formal Name - name formatted for Formal Display by COuntry. Normally used in Correspondence.
56 NAME_FORMAL Character(60) VARCHAR2(60) NOT NULL Formal Name - name formatted for Formal Display by COuntry. Normally used in Correspondence.
57 NAME_FORMAL_PSF_NW Character(60) VARCHAR2(60) NOT NULL Preferred Name (PS FORMAT) new, used to display changes in fluid approvals
58 NAME_FORMAL_PSF Character(60) VARCHAR2(60) NOT NULL Preferred Name (PS FORMAT)
59 WF_STATUS Character(1) VARCHAR2(1) NOT NULL Workflow Status
A=Approved
C=Cancelled
D=Denied
E=Error. Contact Administrator.
F=Awaiting final approval
I=In Approval Process
M=Administrator is Processing
N=Not Available
P=Rework
S=Submitted
V=Data Saved
60 DESCR_COMMENTS_HIS Long Character CLOB This field is used in a complex query that shows many descr fields.
61 APPROVAL_REQUIRED Character(1) VARCHAR2(1) NOT NULL Field for page WF_CO_CONFIG. Checkbox used for "Approval Required" on configuration page.
N=No Approval Required
Y=Approval Required

Default Value: N