COMPETENCY_V2

(SQL View)
 Index Back

Competencies w/in Eval Type

Selects the latest effective dated profile Competency item for each evaluation type (JPM_CAT_ITEM_QUAL). This is used for integration with Career Planning.

SELECT B.EMPLID , %Substring(A.JPM_CAT_ITEM_QUAL,1,1) , %Substring(A.JPM_CAT_ITEM_ID,1,8) , A.EFFDT , A.JPM_RATING1 , C.CAREER_STRDVLP FROM PS_JPM_JP_ITEMS A , PS_JPM_PROFILE B , PS_REVW_RATING_TBL C WHERE A.JPM_PROFILE_ID = B.JPM_PROFILE_ID AND B.JPM_JP_PRFL_STATUS = 'A' AND B.JPM_JP_TYPE = 'PERSON' AND A.JPM_CAT_TYPE = 'COMPETENCY' AND A.EFFDT = ( SELECT MAX(A3.EFFDT) FROM PS_JPM_JP_ITEMS A3 WHERE A3.JPM_PROFILE_ID = A.JPM_PROFILE_ID AND A3.JPM_CAT_TYPE = A.JPM_CAT_TYPE AND A3.JPM_CAT_ITEM_ID = A.JPM_CAT_ITEM_ID AND A3.JPM_CAT_ITEM_QUAL = A.JPM_CAT_ITEM_QUAL AND A3.EFF_STATUS = 'A') AND A.JPM_ITEM_KEY_ID = ( SELECT MAX(A4.JPM_ITEM_KEY_ID) FROM PS_JPM_JP_ITEMS A4 WHERE A4.JPM_PROFILE_ID = A.JPM_PROFILE_ID AND A4.JPM_CAT_TYPE = A.JPM_CAT_TYPE AND A4.JPM_CAT_ITEM_ID = A.JPM_CAT_ITEM_ID AND A4.JPM_CAT_ITEM_QUAL = A.JPM_CAT_ITEM_QUAL AND A4.EFFDT = A.EFFDT) AND C.RATING_MODEL = A.RATING_MODEL AND C.EFFDT = ( SELECT MAX(EFFDT) FROM PS_REVW_RATING_TBL C1 WHERE C1.RATING_MODEL = C.RATING_MODEL AND EFFDT <= %CurrentDateIn) AND C.REVIEW_RATING = A.JPM_RATING1

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EVALUATION_TYPE Character(1) VARCHAR2(1) NOT NULL Evaluation Type
A=Approved/Official
B=Subordinate
C=Customer
E=Executive Committee
L=Learning
M=Mentor
P=Peer
R=Supervisor/Manager
S=Self
3 COMPETENCY Character(8) VARCHAR2(8) NOT NULL Competency
4 EFFDT Date(10) DATE Effective Date

Default Value: %date

5 PROFICIENCY Character(1) VARCHAR2(1) NOT NULL Proficiency
6 CAREER_STRDVLP Character(1) VARCHAR2(1) NOT NULL Career Strength/ Development
D=Development
S=Strength