HR_CP_COMP_VW (SQL View)	Index Back
Employee Resume Competencies This view is created from JPM_JP_ITEMS, COMPETENCY_TBL and the REVW_RATING_TBL and is used to display the Competencies grid on the Employee Self Service Internal Resume.

HR_CP_COMP_VW

(SQL View)

Employee Resume Competencies

This view is created from JPM_JP_ITEMS, COMPETENCY_TBL and the REVW_RATING_TBL and is used to display the Competencies grid on the Employee Self Service Internal Resume.

SELECT SK.EMPLID , SK.CAREER_COMPETENCY , SK.EFFDT , CRT.DESCRSHORT , SK.CP_YR_LST_USED FROM PS_HR_CP_COMPCS_VW SK , PS_COMPETENCY_TBL CT , PS_REVW_RATING_TBL CRT WHERE CT.COMPETENCY = SK.CAREER_COMPETENCY AND CRT.RATING_MODEL = CT.RATING_MODEL AND CRT.REVIEW_RATING = SK.PROFICIENCY AND SK.EFFDT = ( SELECT MAX(EFFDT) FROM PS_HR_CP_COMPCS_VW SK1 WHERE SK1.EMPLID = SK.EMPLID AND SK1.CAREER_COMPETENCY = SK.CAREER_COMPETENCY AND SK1.EFFDT <= %CurrentDateIn) AND CRT.EFFDT = ( SELECT MAX(EFFDT) FROM PS_RATING_MDL_TBL WHERE RATING_MODEL = CRT.RATING_MODEL AND EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

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

3 EFFDT Date(10) DATE Effective Date
Default Value: %date

4 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description

5 CP_YR_LST_USED Number(8,0) INTEGER NOT NULL Year Last Used

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	CAREER_COMPETENCY	Character(12)	VARCHAR2(12) NOT NULL	Competency
3	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
4	DESCRSHORT	Character(10)	VARCHAR2(10) NOT NULL	Short Description
5	CP_YR_LST_USED	Number(8,0)	INTEGER NOT NULL	Year Last Used