CM_PROF_COMP_VW

(SQL View)
 Index Back

Competencies of Role

CM_ROLE_COMP_VW is a view that selects the competencies that profile a role (position, jobcode, job task, or job family).

SELECT X.JPM_PROFILE_ID ,CASE WHEN X.JPM_RLAT_NAME = 'POSITION' THEN %Substring(X.JPM_RLAT_KEY1 ,1 ,8) ELSE ' ' END ,CASE WHEN X.JPM_RLAT_NAME = 'JOB_FAMILY' THEN %Substring(X.JPM_RLAT_KEY1 ,1 ,6) ELSE ' ' END ,X.JPM_CAT_SETID ,CASE WHEN X.JPM_RLAT_NAME = 'JOB_CODE' THEN %Substring(X.JPM_RLAT_KEY1 ,1 ,6) ELSE ' ' END ,CASE WHEN X.JPM_RLAT_NAME = 'JOB_TASK' THEN %Substring(X.JPM_RLAT_KEY1 ,1 ,6) ELSE ' ' END ,X.EFFDT ,ITEMS.JPM_CAT_ITEM_ID ,%Substring(ITEMS.JPM_CAT_ITEM_ID ,1,8) ,ITEMS.JPM_IMPORTANCE ,0 ,ITEMS.JPM_RATING1 ,ITEMS.FP_SKIL_HIR ,ITEMS.FP_SKIL_TEN ,ITEMS.FP_SKIL_PRM , ITEMS.RATING_MODEL FROM PS_JPM_JP_ITEMS ITEMS LEFT OUTER JOIN PS_JPM_PROFILE P2 ON ITEMS.JPM_SOURCE_ID2 = P2.JPM_PROFILE_ID ,PS_JPM_JP_X_RLAT X ,PS_JPM_PROFILE WHERE X.JPM_PROFILE_ID = PS_JPM_PROFILE.JPM_PROFILE_ID AND X.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JPM_JP_X_RLAT X1 WHERE X1.JPM_PROFILE_ID = X.JPM_PROFILE_ID AND X1.JPM_CAT_SETID = X.JPM_CAT_SETID AND X1.JPM_RLAT_KEY1 = X.JPM_RLAT_KEY1 AND X1.JPM_RLAT_KEY2 = X.JPM_RLAT_KEY2 AND X1.JPM_RLAT_KEY3 = X.JPM_RLAT_KEY3 AND X1.JPM_RLAT_KEY4 = X.JPM_RLAT_KEY4 AND X1.JPM_RLAT_NAME = X.JPM_RLAT_NAME) AND X.EFF_STATUS = 'A' AND ITEMS.JPM_PROFILE_ID = PS_JPM_PROFILE.JPM_PROFILE_ID AND ITEMS.JPM_CAT_TYPE IN('COMPETENCY','NVQ UNIT') AND ITEMS.JPM_ITEM_KEY_ID = ( SELECT MAX(ITMKEY.JPM_ITEM_KEY_ID) FROM PS_JPM_JP_ITEMS ITMKEY WHERE ITEMS.JPM_PROFILE_ID = ITMKEY.JPM_PROFILE_ID AND ITEMS.JPM_CAT_TYPE = ITMKEY.JPM_CAT_TYPE AND ITEMS.JPM_CAT_ITEM_ID = ITMKEY.JPM_CAT_ITEM_ID) AND PS_JPM_PROFILE.JPM_PROFILE_USAGE= 'J'

  • Parent record: CM_ROLE

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 JPM_PROFILE_ID Character(12) VARCHAR2(12) NOT NULL The id, autoassigned or user assigned of the profile. This id is used to group items and other related material together into one coherent profile.
    2 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
    3 JOB_FAMILY Character(6) VARCHAR2(6) NOT NULL Job Family
    4 SETID Character(5) VARCHAR2(5) NOT NULL SetID
    5 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    6 JOB_TASK Character(6) VARCHAR2(6) NOT NULL Job Task
    7 EFFDT Date(10) DATE NOT NULL Effective Date
    8 JPM_CAT_ITEM_ID Character(12) VARCHAR2(12) NOT NULL Catalog Item Id. Identifies a unique catalog item defintion within a catalog type.
    9 COMPETENCY Character(8) VARCHAR2(8) NOT NULL Competency
    10 CM_IMPORTANCE Character(1) VARCHAR2(1) NOT NULL Competency Importance
    1=1-Low
    2=2-Below Average
    3=3-Average
    4=4-Above Average
    5=5-High

    Default Value: 3

    11 CM_IMPORTANCE_NBR Number(3,0) SMALLINT NOT NULL Competency Importance

    Default Value: 3

    12 REVIEW_RATING Character(1) VARCHAR2(1) NOT NULL Review Rating
    13 FP_SKIL_HIR Character(1) VARCHAR2(1) NOT NULL FPS Hiring Requirement
    N=No
    Y=Yes

    Y/N Table Edit

    Default Value: N

    14 FP_SKIL_TEN Character(1) VARCHAR2(1) NOT NULL FPS Tenure Requirement
    N=No
    Y=Yes

    Y/N Table Edit

    Default Value: N

    15 FP_SKIL_PRM Character(1) VARCHAR2(1) NOT NULL FPS Promotion Requirement
    N=No
    Y=Yes

    Y/N Table Edit

    Default Value: N

    16 RATING_MODEL Character(4) VARCHAR2(4) NOT NULL Rating Model