KEY_PROJ_METR

(SQL View)
 Index Back

Key Person Metrics

Key Person Metrics

SELECT A.OPRID , A.ROWSECCLASS , A.EMPLID , A.EMPL_RCD , B.HR_SUCC_PLAN_ID , B.HR_SUCC_PLAN_TYPE , B.HR_SUCC_PLAN_CTL , B.EFFDT , B.AVAILABILITY_DT , 0 , CP.READINESS , A.BUSINESS_UNIT , A.COMPANY , A.DEPTID , A.LOCATION , A.SETID_DEPT , A.SETID_LOCATION FROM PS_KEY_PERS_SRCH_D A LEFT OUTER JOIN PS_HR_SUCC_PLN_HDR B ON B.HR_SUCC_PLAN_TYPE = '2' , PS_CAREERPATH CP WHERE A.EMPLID = B.HR_SUCC_PLAN_CTL AND CP.EMPLID = B.HR_SUCC_PLAN_CTL AND CP.READINESS = ( SELECT C.READINESS FROM PS_CAREERPATH C WHERE C.EMPLID = CP.EMPLID AND C.CAREER_MOVE = '1' AND C.EVALUATION_TYPE = 'A' AND C.PRE_MOVE_OPTION = ( SELECT MIN(PRE_MOVE_OPTION) FROM PS_CAREERPATH WHERE EMPLID = C.EMPLID AND EVALUATION_TYPE = 'A' AND CAREER_MOVE = '1' AND JOB_OPTION_NBR = C.JOB_OPTION_NBR AND EFFDT = C.EFFDT) AND C.JOB_OPTION_NBR = ( SELECT MIN(JOB_OPTION_NBR) FROM PS_CAREERPATH WHERE EMPLID = C.EMPLID AND EVALUATION_TYPE = 'A' AND CAREER_MOVE = '1' AND PRE_MOVE_OPTION = C.PRE_MOVE_OPTION AND EFFDT = C.EFFDT) AND C.EFFDT=( SELECT MAX(C1.EFFDT) FROM PS_CAREERPATH C1 WHERE C1.EFFDT <= B.EFFDT AND C1.EMPLID = C.EMPLID))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 ROWSECCLASS Character(30) VARCHAR2(30) NOT NULL The class used to determine row level security
3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
5 HR_SUCC_PLAN_ID Character(11) VARCHAR2(11) NOT NULL Succession Plan ID
6 HR_SUCC_PLAN_TYPE Character(1) VARCHAR2(1) NOT NULL Succession Plan Type
1=Job Code
2=Person
3=Position
7 HR_SUCC_PLAN_CTL Character(11) VARCHAR2(11) NOT NULL Succession Plan Control Value
8 EFFDT Date(10) DATE Effective Date

Default Value: %date

9 AVAILABILITY_DT Date(10) DATE Availability Date
10 NO_OF_SUCC Number(3,0) SMALLINT NOT NULL Denotes the number of Successors
11 READINESS Character(1) VARCHAR2(1) NOT NULL Readiness
0=Ready Now
1=1 - 2 Years
2=3 - 5 Years
E=Emergency
R=Retirement
12 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
13 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
14 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
15 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
16 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
17 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID