HR_CP_COMPCS_VW

(SQL View)
 Index Back

Employee Resume Competencies

This view is created by cloning the COMPETENCIES table and then adding HR_CP_INSTALL and HR_CP_CONTENT to show to proper Profile and Content Type access. All Competencies used in the Employee Self Service Internal Resume utilize this view.

SELECT B.EMPLID ,%Substring(A.JPM_CAT_ITEM_ID,1,8) ,A.EFFDT ,A.JPM_INTEGER_1 ,A.JPM_VERIFY_METHOD ,A.JPM_RATING1 ,A.JPM_INTEREST_LEVEL FROM PS_JPM_JP_ITEMS A , PS_JPM_PROFILE B , PS_HR_CP_INSTALL C , PS_HR_CP_CONTENT D WHERE A.JPM_PROFILE_ID = B.JPM_PROFILE_ID AND B.JPM_JP_TYPE = C.JPM_JP_TYPE AND A.JPM_CAT_TYPE = D.JPM_CAT_TYPE AND A.JPM_CAT_TYPE IN ('COMPETENCY','NVQ UNIT') AND A.JPM_ITEM_KEY_ID = ( SELECT MAX(X.JPM_ITEM_KEY_ID) FROM PS_JPM_JP_ITEMS X WHERE A.JPM_PROFILE_ID = X.JPM_PROFILE_ID AND A.JPM_CAT_TYPE = X.JPM_CAT_TYPE AND A.JPM_CAT_ITEM_ID = X.JPM_CAT_ITEM_ID AND A.EFFDT = X.EFFDT AND A.EVALUATION_ID = X.EVALUATION_ID )

  • Parent record: CM_EVALUATIONS

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Prompt Table: PERSON

    2 CAREER_COMPETENCY Character(12) VARCHAR2(12) NOT NULL Competency

    Prompt Table: COMPETENCY_TBL

    3 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

    4 CP_YR_LST_USED Number(8,0) INTEGER NOT NULL Year Last Used
    5 HOW_VERIFIED Character(1) VARCHAR2(1) NOT NULL Verified By
    E=Employee Claim
    J=On the Job Training
    N=Not Verified
    O=Employer Observation
    T=By Test
    V=NVQ Authority
    6 PROFICIENCY Character(1) VARCHAR2(1) NOT NULL Proficiency

    Prompt Table: REVW_RATING_TBL

    7 INTEREST_LEVEL Character(1) VARCHAR2(1) NOT NULL Interest Level

    Default Value: 2

    Prompt Table: INT_RATING_VW
    Set Control Field: REVIEW_RATING