CM_OTH_ITM_XVW

(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 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_TYPE ,ITEMS.JPM_CAT_ITEM_ID ,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 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 PS_JPM_PROFILE.JPM_PROFILE_USAGE = 'J' AND ITEMS.JPM_CAT_TYPE NOT IN ('COMPETENCY','NVQ UNIT', 'SUB_COMP') AND ITEMS.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JPM_JP_ITEMS ITM2 WHERE ITEMS.JPM_PROFILE_ID = ITM2.JPM_PROFILE_ID AND ITEMS.JPM_CAT_TYPE = ITM2.JPM_CAT_TYPE AND ITEMS.JPM_CAT_ITEM_ID = ITM2.JPM_CAT_ITEM_ID) AND ITEMS.EFF_STATUS = 'A'

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

    Prompt Table: JPM_PROFILE_VW

    2 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number

    Prompt Table: %RECNAME_EDIT

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

    Prompt Table: JOB_FAMILY_TBL

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

    Prompt Table: SETID_TBL

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

    Prompt Table: JOBCODE_TBL

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

    Prompt Table: JOB_TASK_TBL

    7 EFFDT Date(10) DATE NOT NULL Effective Date
    8 JPM_CAT_TYPE Character(12) VARCHAR2(12) NOT NULL The name of the type of items that will be used in a Catalog and or in a Profile.
    9 JPM_CAT_ITEM_ID Character(12) VARCHAR2(12) NOT NULL Catalog Item Id. Identifies a unique catalog item defintion within a catalog type.
    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 FP_SUBJECT_CD Character(3) VARCHAR2(3) NOT NULL FPS Subject Code

    Prompt Table: FPMSUBJECTS

    13 FP_DEGR_REQUIRED Character(1) VARCHAR2(1) NOT NULL FPS Degree
    N=No
    Y=Yes

    Y/N Table Edit

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

    Y/N Table Edit

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

    Y/N Table Edit

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

    Y/N Table Edit