CM_OTH_ITM_VW

(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 ITEMS.JPM_PROFILE_ID ,ITEMS.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_PROFILE WHERE 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 EFFDT Date(10) DATE NOT NULL Effective Date
    3 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.
    4 JPM_CAT_ITEM_ID Character(12) VARCHAR2(12) NOT NULL Catalog Item Id. Identifies a unique catalog item defintion within a catalog type.
    5 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

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

    Default Value: 3

    7 FP_SUBJECT_CD Character(3) VARCHAR2(3) NOT NULL FPS Subject Code

    Prompt Table: FPMSUBJECTS

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

    Y/N Table Edit

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

    Y/N Table Edit

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

    Y/N Table Edit

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

    Y/N Table Edit