NVQ_UNITS_VW (SQL View)	Index Back
Employee NVQ Units This view displays the NVQ units tied to an NVQ Review. The NVQ Review and NVQ Units data is stored in Profile Management.

NVQ_UNITS_VW

(SQL View)

Employee NVQ Units

This view displays the NVQ units tied to an NVQ Review. The NVQ Review and NVQ Units data is stored in Profile Management.

SELECT B.EMPLID ,%Substring(A.JPM_CAT_ITEM_ID,1,8) ,A.EFFDT ,A.JPM_ITEM_KEY_ID ,A.JPM_PARENT_KEY_ID ,A.JPM_RATING1 ,A.JPM_DATE_2 ,A.JPM_DATE_3 ,A.JPM_DATE_1 ,A.JPM_DATE_4 ,A.JPM_DATE_5 ,A.JPM_TEXT254_4 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 = '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)

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 COMPETENCY Character(8) VARCHAR2(8) NOT NULL Competency
Prompt Table: COMPETENCY_TBL

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

4 JPM_ITEM_KEY_ID Number(12,0) DECIMAL(12) NOT NULL Unique identifier of an item within a profile. This is the primary key of profile items. It is a generic autoassigned value (a number). The unique key permits the addition of items that are not inheritently unique. They may have the same effective date, item type, item id, yet differ in a property or other value. The concept of key fields in a profile is a loose term as an object in a profile can have very different needs in identification.

5 JPM_PARENT_KEY_ID Number(12,0) DECIMAL(12) NOT NULL This is the JPM_ITEM_KEY_ID of the item in the profile that is the parent or driving item of the item. Example- a sub competency may have the item key id of the competency as the parent key. It is used to link more than one item together in a profile where a relationship was established.

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

7 START_DT Date(10) DATE Start Date

8 COMP_DT Date(10) DATE Completion Date

9 EXP_DT Date(10) DATE Expected Completion Date

10 PRESENTED_DT Date(10) DATE Evidence Presented

11 SUBMIT_DT Date(10) DATE Submission Date

12 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	COMPETENCY	Character(8)	VARCHAR2(8) NOT NULL	Competency Prompt Table: COMPETENCY_TBL
3	EFFDT	Date(10)	DATE NOT NULL	Effective Date Default Value: %date
4	JPM_ITEM_KEY_ID	Number(12,0)	DECIMAL(12) NOT NULL	Unique identifier of an item within a profile. This is the primary key of profile items. It is a generic autoassigned value (a number). The unique key permits the addition of items that are not inheritently unique. They may have the same effective date, item type, item id, yet differ in a property or other value. The concept of key fields in a profile is a loose term as an object in a profile can have very different needs in identification.
5	JPM_PARENT_KEY_ID	Number(12,0)	DECIMAL(12) NOT NULL	This is the JPM_ITEM_KEY_ID of the item in the profile that is the parent or driving item of the item. Example- a sub competency may have the item key id of the competency as the parent key. It is used to link more than one item together in a profile where a relationship was established.
6	PROFICIENCY	Character(1)	VARCHAR2(1) NOT NULL	Proficiency Prompt Table: REVW_RATING_TBL
7	START_DT	Date(10)	DATE	Start Date
8	COMP_DT	Date(10)	DATE	Completion Date
9	EXP_DT	Date(10)	DATE	Expected Completion Date
10	PRESENTED_DT	Date(10)	DATE	Evidence Presented
11	SUBMIT_DT	Date(10)	DATE	Submission Date
12	DESCRLONG	Long Character	CLOB	Long Description