CM_COMP_MATCH

(SQL View)
 Index Back

CM Competency Match

This view returns employee competencies by Operator. Only those competencies are selected, which are part of evaluations of the types (Approved, Self, Peer, ..) as specified under "Setup >> Match Evaluation Types".

SELECT C.OPRID , C.SEARCH_TYPE , B.EMPLID , %Substring(A.JPM_CAT_ITEM_ID,1,8) , AVG(E.REVIEW_POINTS) REVIEW_POINTS FROM PS_JPM_JP_ITEMS A , PS_JPM_PROFILE B , PS_CM_MATCH_TYPES C , PS_REVW_RATING_TBL E WHERE B.JPM_JP_TYPE = 'PERSON' AND A.JPM_PROFILE_ID = B.JPM_PROFILE_ID AND A.JPM_CAT_TYPE = 'COMPETENCY' AND A.JPM_CAT_ITEM_QUAL = C.EVALUATION_TYPE AND A.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JPM_JP_ITEMS A1 WHERE A1.JPM_PROFILE_ID = A.JPM_PROFILE_ID AND A1.JPM_CAT_TYPE = A.JPM_CAT_TYPE AND A1.JPM_CAT_ITEM_ID = A.JPM_CAT_ITEM_ID AND A1.JPM_CAT_ITEM_QUAL = A.JPM_CAT_ITEM_QUAL AND A1.JPM_CAT_ITEM_QUAL2 = A.JPM_CAT_ITEM_QUAL2 AND A1.EFFDT <= %CurrentDateIn) AND E.RATING_MODEL = A.RATING_MODEL AND E.REVIEW_RATING = A.JPM_RATING1 AND E.EFFDT = ( SELECT MAX(EFFDT) FROM PS_REVW_RATING_TBL E1 WHERE E.RATING_MODEL = E1.RATING_MODEL AND E.REVIEW_RATING = E1.REVIEW_RATING AND E1.EFFDT <= %CurrentDateIn) GROUP BY C.OPRID, C.SEARCH_TYPE ,B.EMPLID , A.JPM_CAT_ITEM_ID

# 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 SEARCH_TYPE Character(1) VARCHAR2(1) NOT NULL Search Type
M=Role/Person
S=Competency Search

Default Value: M

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

Prompt Table: PERSON

4 COMPETENCY Character(8) VARCHAR2(8) NOT NULL Competency

Prompt Table: COMPETENCY_TBL

5 REVIEW_POINTS Number(3,0) SMALLINT NOT NULL Review Points