CM_NP_PROF_VW (SQL View)	Index Back
Role Comps/Accomp Parent CM_ROLE is the parent record to CM_ROLE_COMPS and CM_ROLE_ACCOMPS that define the positoins, jobcodes, job tasks, and job families profiled in the Competency Management Module. Release 9 Job Profile Managment (JPM): Table became a view to JPM data. Ownership of table transferred from Human Resources to JPM. Note: Eval_method is not mapped to a JPM field because it was determined that this field was not actually used in roles.

CM_NP_PROF_VW

(SQL View)

Role Comps/Accomp Parent

CM_ROLE is the parent record to CM_ROLE_COMPS and CM_ROLE_ACCOMPS that define the positoins, jobcodes, job tasks, and job families profiled in the Competency Management Module. Release 9 Job Profile Managment (JPM): Table became a view to JPM data. Ownership of table transferred from Human Resources to JPM. Note: Eval_method is not mapped to a JPM field because it was determined that this field was not actually used in roles.

SELECT CASE WHEN A.JPM_RLAT_NAME = 'POSITION' THEN %Substring(A.JPM_RLAT_KEY1,1,8) ELSE ' ' END ,CASE WHEN A.JPM_RLAT_NAME = 'JOB_FAMILY' THEN %Substring(A.JPM_RLAT_KEY1,1,6) ELSE ' ' END ,A.JPM_CAT_SETID ,CASE WHEN A.JPM_RLAT_NAME = 'JOB_CODE' THEN %Substring(A.JPM_RLAT_KEY1,1,6) ELSE ' ' END ,CASE WHEN A.JPM_RLAT_NAME = 'JOB_TASK' THEN %Substring(A.JPM_RLAT_KEY1,1,6) ELSE ' ' END ,A.EFFDT ,A.JPM_PROFILE_ID ,' ' FROM PS_JPM_JP_X_RLAT A ,PS_JPM_PROFILE WHERE A.JPM_PROFILE_ID = PS_JPM_PROFILE.JPM_PROFILE_ID AND PS_JPM_PROFILE.JPM_PROFILE_USAGE = 'J' AND A.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JPM_JP_X_RLAT A1 WHERE A1.JPM_PROFILE_ID = A.JPM_PROFILE_ID AND A1.JPM_CAT_SETID = A.JPM_CAT_SETID AND A1.JPM_RLAT_KEY1 = A.JPM_RLAT_KEY1 AND A1.JPM_RLAT_KEY2 = A.JPM_RLAT_KEY2 AND A1.JPM_RLAT_KEY3 = A.JPM_RLAT_KEY3 AND A1.JPM_RLAT_KEY4 = A.JPM_RLAT_KEY4 AND A1.JPM_RLAT_NAME = A.JPM_RLAT_NAME) AND A.EFF_STATUS = 'A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
Prompt Table: %RECNAME_EDIT

2 JOB_FAMILY Character(6) VARCHAR2(6) NOT NULL Job Family
Prompt Table: JOB_FAMILY_TBL

3 SETID Character(5) VARCHAR2(5) NOT NULL SetID
Prompt Table: SETID_TBL

4 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
Prompt Table: JOBCODE_TBL

5 JOB_TASK Character(6) VARCHAR2(6) NOT NULL Job Task
Prompt Table: JOB_TASK_TBL

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

7 JPM_PROFILE_ID2 Character(12) VARCHAR2(12) NOT NULL Job Profile ID

8 EVAL_METHOD Character(1) VARCHAR2(1) NOT NULL Method of Evaluation

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	POSITION_NBR	Character(8)	VARCHAR2(8) NOT NULL	Position Number Prompt Table: %RECNAME_EDIT
2	JOB_FAMILY	Character(6)	VARCHAR2(6) NOT NULL	Job Family Prompt Table: JOB_FAMILY_TBL
3	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID Prompt Table: SETID_TBL
4	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code Prompt Table: JOBCODE_TBL
5	JOB_TASK	Character(6)	VARCHAR2(6) NOT NULL	Job Task Prompt Table: JOB_TASK_TBL
6	EFFDT	Date(10)	DATE NOT NULL	Effective Date Default Value: %date
7	JPM_PROFILE_ID2	Character(12)	VARCHAR2(12) NOT NULL	Job Profile ID
8	EVAL_METHOD	Character(1)	VARCHAR2(1) NOT NULL	Method of Evaluation