COMPETENCIES (SQL View)	Index Back
Person Competencies COMPETENCIES contains the competency ratings for each employee, applicant or contractor evaluation. Release 9 Job Profile Managment (JPM): Table became a view to JPM data. Ownership of table transferred from Human Resources to JPM.

COMPETENCIES

(SQL View)

Person Competencies

COMPETENCIES contains the competency ratings for each employee, applicant or contractor evaluation. Release 9 Job Profile Managment (JPM): Table became a view to JPM data. Ownership of table transferred from Human Resources to JPM.

SELECT B.EMPLID ,A.EVALUATION_ID ,%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 ,%Round(A.JPM_DECIMAL_2, 1) ,A.JPM_INTEGER_2 ,A.JPM_DATE_2 ,A.JPM_DATE_3 ,A.JPM_DATE_1 ,A.JPM_DATE_4 ,A.JPM_DATE_5 ,A.JPM_TEXT254_5 FROM PS_JPM_JP_ITEMS A , PS_JPM_PROFILE B WHERE A.JPM_PROFILE_ID = B.JPM_PROFILE_ID AND B.JPM_JP_TYPE = 'PERSON' 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 EVALUATION_ID Character(2) VARCHAR2(2) NOT NULL Evaluation ID

3 COMPETENCY Character(8) VARCHAR2(8) NOT NULL Competency
Prompt Table: COMPETENCY_TBL

4 EFFDT Date(10) DATE NOT NULL Effective Date
Default Value: %date

5 YR_LAST_USED Number(4,0) SMALLINT NOT NULL Year Last Used

6 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

7 PROFICIENCY Character(1) VARCHAR2(1) NOT NULL Proficiency
Prompt Table: REVW_RATING_TBL

8 INTEREST_LEVEL Character(1) VARCHAR2(1) NOT NULL Interest Level
Default Value: 2
Prompt Table: INT_RATING_VW
Set Control Field: REVIEW_RATING

9 YEARS_OF_EXP Number(5,1) DECIMAL(4,1) NOT NULL Years of Work Experience

10 YR_ACQUIRED Number(4,0) SMALLINT NOT NULL Year Acquired

11 START_DT Date(10) DATE Start Date

12 COMP_DT Date(10) DATE Completion Date

13 EXP_DT Date(10) DATE Expected Completion Date

14 PRESENTED_DT Date(10) DATE Evidence Presented

15 SUBMIT_DT Date(10) DATE Submission Date

16 DESCRLONG Long Character CLOB Long Description

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: PERSON
2	EVALUATION_ID	Character(2)	VARCHAR2(2) NOT NULL	Evaluation ID
3	COMPETENCY	Character(8)	VARCHAR2(8) NOT NULL	Competency Prompt Table: COMPETENCY_TBL
4	EFFDT	Date(10)	DATE NOT NULL	Effective Date Default Value: %date
5	YR_LAST_USED	Number(4,0)	SMALLINT NOT NULL	Year Last Used
6	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
7	PROFICIENCY	Character(1)	VARCHAR2(1) NOT NULL	Proficiency Prompt Table: REVW_RATING_TBL
8	INTEREST_LEVEL	Character(1)	VARCHAR2(1) NOT NULL	Interest Level Default Value: 2 Prompt Table: INT_RATING_VW Set Control Field: REVIEW_RATING
9	YEARS_OF_EXP	Number(5,1)	DECIMAL(4,1) NOT NULL	Years of Work Experience
10	YR_ACQUIRED	Number(4,0)	SMALLINT NOT NULL	Year Acquired
11	START_DT	Date(10)	DATE	Start Date
12	COMP_DT	Date(10)	DATE	Completion Date
13	EXP_DT	Date(10)	DATE	Expected Completion Date
14	PRESENTED_DT	Date(10)	DATE	Evidence Presented
15	SUBMIT_DT	Date(10)	DATE	Submission Date
16	DESCRLONG	Long Character	CLOB	Long Description