CM_ROLE_ACCOMPS

(SQL View)
 Index Back

Accomplishments of Role

CM_ROLE_ACCOMPS is a table that contains the accomplishments 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 only returns those Role accomplishments that are not tied to a Cluster.

SELECT CASE X.JPM_RLAT_NAME WHEN 'POSITION' THEN %Substring(X.JPM_RLAT_KEY1,1,8) ELSE ' ' END ,CASE X.JPM_RLAT_NAME WHEN 'JOB_FAMILY' THEN %Substring(X.JPM_RLAT_KEY1,1,6) ELSE ' ' END ,X.JPM_CAT_SETID ,CASE X.JPM_RLAT_NAME WHEN 'JOB_CODE' THEN %Substring(X.JPM_RLAT_KEY1,1,6) ELSE ' ' END ,CASE X.JPM_RLAT_NAME WHEN 'JOB_TASK' THEN %Substring(X.JPM_RLAT_KEY1,1,6) ELSE ' ' END ,X.EFFDT ,%Substring(ITEMS.JPM_CAT_ITEM_ID,1,8) ,ITEMS.JPM_IMPORTANCE ,0 ,ITEMS.FP_SUBJECT_CD ,ITEMS.FP_DEGR_REQUIRED ,ITEMS.FP_SKIL_HIR ,ITEMS.FP_SKIL_TEN ,ITEMS.FP_SKIL_PRM FROM PS_JPM_JP_ITEMS ITEMS ,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 ITEMS.JPM_PROFILE_ID = PS_JPM_PROFILE.JPM_PROFILE_ID AND (ITEMS.JPM_SOURCE_ID2 = ' ' AND ITEMS.JPM_CAT_TYPE IN( SELECT FIELDVALUE FROM PSXLATITEM WHERE FIELDNAME = 'ACCOMP_CATEGORY') 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 ITEMS.EFFDT = ITMKEY.EFFDT))

  • 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 ACCOMPLISHMENT Character(8) VARCHAR2(8) NOT NULL Accomplishment

    Prompt Table: ACCOMP_TBL

    8 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

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

    Default Value: 3

    10 FP_SUBJECT_CD Character(3) VARCHAR2(3) NOT NULL Subject

    Prompt Table: FPMSUBJECTS

    11 FP_DEGR_REQUIRED Character(1) VARCHAR2(1) NOT NULL Required ?
    N=No
    Y=Yes

    Y/N Table Edit

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

    Y/N Table Edit

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

    Y/N Table Edit

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

    Y/N Table Edit