HR_SP_DTL_NAME

(SQL View)
 Index Back

Succ Plan Candidates w/ Names

Succession Plan candidates (HR_SUCC_PLN_DTL) joined to PERSON_NAME for "Person" candidate types, and to HRS_APP_NAME_I for "Applicant" candidate types.

SELECT SPP.HR_SUCC_PLAN_ID , SPP.HR_SUCC_PLAN_TYPE , SPP.HR_SUCC_PLAN_CTL , SPP.EFFDT , SPP.EMPLID , SPP.EMPL_RCD , SPP.STATUS , SPP.HR_SP_CAND_TYPE , SPP.RANK , SPP.HR_SP_READINESS , SPP.TARGETED_READY_DT , SPP.BLOCKED , SPP.HR_SP_IMP_OF_LOSS , NP.COUNTRY_NM_FORMAT , NP.NAME , NP.NAME_INITIALS , NP.NAME_PREFIX , NP.NAME_SUFFIX , NP.NAME_ROYAL_PREFIX , NP.NAME_ROYAL_SUFFIX , NP.NAME_TITLE , NP.LAST_NAME_SRCH , NP.FIRST_NAME_SRCH , NP.LAST_NAME , NP.FIRST_NAME , NP.MIDDLE_NAME , NP.SECOND_LAST_NAME , NP.SECOND_LAST_SRCH , NP.NAME_AC , NP.PREF_FIRST_NAME , NP.PARTNER_LAST_NAME , NP.PARTNER_ROY_PREFIX , NP.LAST_NAME_PREF_NLD , NP.NAME_DISPLAY , NP.NAME_FORMAL , NP.NAME_PSFORMAT FROM PS_HR_SUCC_PLN_DTL SPP , PS_PERSON_NAME NP WHERE SPP.EMPLID = NP.EMPLID AND SPP.HR_SP_CAND_TYPE = 'P' UNION SELECT SPA.HR_SUCC_PLAN_ID , SPA.HR_SUCC_PLAN_TYPE , SPA.HR_SUCC_PLAN_CTL , SPA.EFFDT , SPA.EMPLID , SPA.EMPL_RCD , SPA.STATUS , SPA.HR_SP_CAND_TYPE , SPA.RANK , SPA.HR_SP_READINESS , SPA.TARGETED_READY_DT , SPA.BLOCKED , SPA.HR_SP_IMP_OF_LOSS , NA.COUNTRY_NM_FORMAT , NA.NAME , NA.NAME_INITIALS , NA.NAME_PREFIX , NA.NAME_SUFFIX , NA.NAME_ROYAL_PREFIX , NA.NAME_ROYAL_SUFFIX , NA.NAME_TITLE , NA.LAST_NAME_SRCH , NA.FIRST_NAME_SRCH , NA.LAST_NAME , NA.FIRST_NAME , NA.MIDDLE_NAME , NA.SECOND_LAST_NAME , NA.SECOND_LAST_SRCH , NA.NAME_AC , NA.PREF_FIRST_NAME , NA.PARTNER_LAST_NAME , NA.PARTNER_ROY_PREFIX , NA.LAST_NAME_PREF_NLD , NA.NAME_DISPLAY , NA.NAME_FORMAL , ' ' FROM PS_HR_SUCC_PLN_DTL SPA , PS_HRS_APP_NAME_I NA WHERE SPA.EMPLID = %NumToChar(NA.HRS_PERSON_ID) AND SPA.HR_SP_CAND_TYPE = 'A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 HR_SUCC_PLAN_ID Character(11) VARCHAR2(11) NOT NULL Succession Plan ID
2 HR_SUCC_PLAN_TYPE Character(1) VARCHAR2(1) NOT NULL Succession Plan Type
1=Job Code
2=Person
3=Position
3 HR_SUCC_PLAN_CTL Character(11) VARCHAR2(11) NOT NULL Succession Plan Control Value

Prompt Table: %EDITTABLE

4 EFFDT Date(10) DATE NOT NULL Effective Date

Default Value: %date

5 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: HR_SP_PERS_SRCH

6 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
7 STATUS Character(1) VARCHAR2(1) NOT NULL Status Indicator
A=Active
I=Inactive

Default Value: A

8 HR_SP_CAND_TYPE Character(1) VARCHAR2(1) NOT NULL Succession Plan Candidate Type
A=Applicant
P=Person
9 RANK Number(3,0) SMALLINT NOT NULL Rank
10 HR_SP_READINESS Character(1) VARCHAR2(1) NOT NULL Displays Readiness of a Succession Plan Candidate.
0=Ready Now
1=1 - 2 Years
2=3 - 5 Years
E=Emergency
11 TARGETED_READY_DT Date(10) DATE Targeted Ready Date
12 BLOCKED Character(1) VARCHAR2(1) NOT NULL Blockage for Succession Planning

Y/N Table Edit

13 HR_SP_IMP_OF_LOSS Character(1) VARCHAR2(1) NOT NULL Impact of Loss: indicates the impact to an organization if an employee terminates. Used on Succession Planning and Talent Pool
1=High
2=Medium
3=Low
14 COUNTRY_NM_FORMAT Character(3) VARCHAR2(3) NOT NULL Supported Name Format Types

Prompt Table: NAME_FORMAT_TBL

15 NAME Character(50) VARCHAR2(50) NOT NULL Name
16 NAME_INITIALS Character(6) VARCHAR2(6) NOT NULL Name Initials
17 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix

Prompt Table: NAME_PREFIX_TBL

18 NAME_SUFFIX Character(15) VARCHAR2(15) NOT NULL Name Suffix

Prompt Table: NAME_SUFFIX_TBL

19 NAME_ROYAL_PREFIX Character(15) VARCHAR2(15) NOT NULL Name Royal Prefix

Prompt Table: NM_ROYPREF_GBL

20 NAME_ROYAL_SUFFIX Character(15) VARCHAR2(15) NOT NULL Name - Royal Suffix

Prompt Table: NM_ROYSUFF_GBL

21 NAME_TITLE Character(30) VARCHAR2(30) NOT NULL Name Title

Prompt Table: TITLE_TBL

22 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
23 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
24 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
25 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
26 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
27 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
28 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Last Name
29 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
30 PREF_FIRST_NAME Character(30) VARCHAR2(30) NOT NULL Preferred First Name
31 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.
32 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.

Prompt Table: NM_ROYPREF_GBL

33 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

Default Value: 1

34 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
35 NAME_FORMAL Character(60) VARCHAR2(60) NOT NULL Formal Name - name formatted for Formal Display by COuntry. Normally used in Correspondence.
36 NAME_PSFORMAT Character(50) VARCHAR2(50) NOT NULL A Person's name in the Peoplesoft Format (basically LN,FN MI