JPM_PROF_COMPVW

(SQL View)
 Index Back

Non-pers Profile competencies

Selects the competencies of any non-person profile

SELECT ITEMS.JPM_PROFILE_ID ,ITEMS.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_PROFILE PROF , PS_JPM_JP_ITEMS ITEMS WHERE PROF.JPM_PROFILE_USAGE= 'J' AND ITEMS.JPM_PROFILE_ID = PROF.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)

# 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 EFFDT Date(10) DATE NOT NULL Effective Date
3 JPM_CAT_ITEM_ID Character(12) VARCHAR2(12) NOT NULL Catalog Item Id. Identifies a unique catalog item defintion within a catalog type.
4 COMPETENCY Character(8) VARCHAR2(8) NOT NULL Competency
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 REVIEW_RATING Character(1) VARCHAR2(1) NOT NULL Review Rating
8 FP_SKIL_HIR Character(1) VARCHAR2(1) NOT NULL FPS Hiring Requirement
N=No
Y=Yes

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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