CM_ROLE_COMPS

(SQL View)
 Index Back

Competencies of Role

CM_ROLE_COMPS is a table that contains the competencies or competency clusters that profile a role (position, jobcode, job task, or job family). Release 9 Job Profile Managment (JPM): Table became a view to JPM data. Ownership of table transferred from Human Resources to JPM. This view returns: 1) all Role items that are not tied to a Cluster. 2) the Clusters that tied to this Role.

SELECT 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 ,CASE WHEN ITEMS.JPM_SOURCE_ID2 = ' ' THEN %Substring(ITEMS.JPM_CAT_ITEM_ID ,1 ,8) ELSE ' ' END ,CASE WHEN ITEMS.JPM_SOURCE_ID2 = ' ' THEN ' ' ELSE %Substring(P2.JPM_LGCY_PRFL_ID ,1 ,6) END ,ITEMS.JPM_IMPORTANCE ,0 ,ITEMS.JPM_RATING1 ,ITEMS.FP_SKIL_HIR ,ITEMS.FP_SKIL_TEN ,ITEMS.FP_SKIL_PRM , ITEMS.JPM_PROFILE_ID 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 PS_JPM_PROFILE.JPM_JP_TYPE = 'ROLE' 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_SOURCE_ID2 = ' ' AND ITEMS.JPM_CAT_TYPE IN('COMPETENCY','NVQ UNIT')) OR (ITEMS.JPM_SOURCE_ID2 <> ' ' 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_SOURCE_ID2 = ITMKEY.JPM_SOURCE_ID2) ))

  • Parent record: CM_ROLE

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number

    Prompt Table: %RECNAME_EDIT

    2 JOB_FAMILY Character(6) VARCHAR2(6) NOT NULL Job Family

    Prompt Table: JOB_FAMILY_TBL

    3 SETID Character(5) VARCHAR2(5) NOT NULL SetID

    Prompt Table: SETID_TBL

    4 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

    Prompt Table: JOBCODE_TBL

    5 JOB_TASK Character(6) VARCHAR2(6) NOT NULL Job Task

    Prompt Table: JOB_TASK_TBL

    6 EFFDT Date(10) DATE NOT NULL Effective Date
    7 COMPETENCY Character(8) VARCHAR2(8) NOT NULL Competency

    Prompt Table: COMPETENCY_TBL

    8 CM_CLUSTER Character(6) VARCHAR2(6) NOT NULL Competency Cluster

    Prompt Table: CM_CLUSTER_TBL

    9 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
    10 CM_IMPORTANCE_NBR Number(3,0) SMALLINT NOT NULL Competency Importance
    11 PROFICIENCY Character(1) VARCHAR2(1) NOT NULL Proficiency

    Prompt Table: REVW_RATING_TBL

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

    Y/N Table Edit

    Default Value: N

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

    Y/N Table Edit

    Default Value: N

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

    Y/N Table Edit

    Default Value: N

    15 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.