HR_CP_JPROFILE2 (SQL View)	Index Back
Job Profile Career Planning Self Service Job Profile

HR_CP_JPROFILE2

(SQL View)

Job Profile

Career Planning Self Service Job Profile

SELECT A.JPM_PROFILE_ID , B.JPM_CAT_SETID , B.JPM_RLAT_KEY1 , C.JPM_KEY_RECNAME , A.DESCR , D.JPM_CAT_TYPE , D.JPM_CAT_ITEM_ID , D.JPM_ITEM_KEY_ID , D.RATING_MODEL , D.JPM_CAT_ITEM_QUAL , D.JPM_RATING1 , D.JPM_IMPORTANCE FROM PS_JPM_PROFILE A , PS_JPM_JP_X_RLAT B , PS_JPM_RLAT_CFG C , PS_JPM_JP_ITEMS D WHERE A.JPM_JP_PRFL_STATUS = 'A' AND A.JPM_PROFILE_ID = B.JPM_PROFILE_ID AND B.EFFDT = ( SELECT MAX(E.EFFDT) FROM PS_JPM_JP_X_RLAT E WHERE B.JPM_PROFILE_ID = E.JPM_PROFILE_ID AND E.EFFDT <= %CurrentDateIn) AND B.JPM_RLAT_NAME = C.JPM_RLAT_NAME AND C.JPM_KEY_RECNAME IN ('JOBCODE_TBL', 'POSITION_DATA') AND A.JPM_PROFILE_ID = D.JPM_PROFILE_ID AND D.EFF_STATUS = 'A' AND D.EFFDT = ( SELECT F.EFFDT FROM PS_JPM_JP_ITEMS F WHERE D.JPM_PROFILE_ID = F.JPM_PROFILE_ID AND D.JPM_CAT_TYPE = F.JPM_CAT_TYPE AND D.JPM_CAT_ITEM_ID = F.JPM_CAT_ITEM_ID AND D.JPM_ITEM_KEY_ID = F.JPM_ITEM_KEY_ID AND F.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 JPM_PROFILE_ID Character(12) VARCHAR2(12) NOT NULL The id, autoassigned or user assigned of the profile. This id is used to group items and other related material together into one coherent profile.
Prompt Table: JPM_PROFILE

2 SETID Character(5) VARCHAR2(5) NOT NULL SetID

3 JPM_RLAT_KEY1 Character(30) VARCHAR2(30) NOT NULL Key value identifying the external data being related to JPM data. Example- Job Codes (external data) being related to a Profile of a Job (JPM data).

4 JPM_KEY_RECNAME Character(15) VARCHAR2(15) NOT NULL JPM - Record Name

5 DESCR Character(30) VARCHAR2(30) NOT NULL Description

6 JPM_CAT_TYPE Character(12) VARCHAR2(12) NOT NULL The name of the type of items that will be used in a Catalog and or in a Profile.
Prompt Table: JPM_CAT_TYPES

7 JPM_CAT_ITEM_ID Character(12) VARCHAR2(12) NOT NULL Catalog Item Id. Identifies a unique catalog item defintion within a catalog type.
Prompt Table: JPM_CAT_ITMS_VW

8 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.

9 RATING_MODEL Character(4) VARCHAR2(4) NOT NULL Rating Model
Prompt Table: RATING_MDL_TBL

10 JPM_CAT_ITEM_QUAL Character(12) VARCHAR2(12) NOT NULL Low Order Item key for Catalog Items when appearing in a Profile. For most Catalog Types, this will be blank - but is available if an Item needs to have multiple instances for a given Effdt in a Profile. Example: for CatalogType = Compentency, there can be multiple instances for a particular Competency, each representing a different evaluation source (Self-Eval, Mgr Eval, Approved/Official, Learning, Recruiting).
Prompt Table: JPM_JP_QUAL_INS

11 JPM_RATING1 Character(1) VARCHAR2(1) NOT NULL Generic rating field generally prompted by RATING_MODEL
Prompt Table: REVW_RATING_TBL

12 JPM_IMPORTANCE Character(1) VARCHAR2(1) NOT NULL profile item importance rating
1=1- Low
2=2 - Below Average
3=3 - Average
4=4 - Above Average
5=5 - High

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	JPM_PROFILE_ID	Character(12)	VARCHAR2(12) NOT NULL	The id, autoassigned or user assigned of the profile. This id is used to group items and other related material together into one coherent profile. Prompt Table: JPM_PROFILE
2	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID
3	JPM_RLAT_KEY1	Character(30)	VARCHAR2(30) NOT NULL	Key value identifying the external data being related to JPM data. Example- Job Codes (external data) being related to a Profile of a Job (JPM data).
4	JPM_KEY_RECNAME	Character(15)	VARCHAR2(15) NOT NULL	JPM - Record Name
5	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description
6	JPM_CAT_TYPE	Character(12)	VARCHAR2(12) NOT NULL	The name of the type of items that will be used in a Catalog and or in a Profile. Prompt Table: JPM_CAT_TYPES
7	JPM_CAT_ITEM_ID	Character(12)	VARCHAR2(12) NOT NULL	Catalog Item Id. Identifies a unique catalog item defintion within a catalog type. Prompt Table: JPM_CAT_ITMS_VW
8	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.
9	RATING_MODEL	Character(4)	VARCHAR2(4) NOT NULL	Rating Model Prompt Table: RATING_MDL_TBL
10	JPM_CAT_ITEM_QUAL	Character(12)	VARCHAR2(12) NOT NULL	Low Order Item key for Catalog Items when appearing in a Profile. For most Catalog Types, this will be blank - but is available if an Item needs to have multiple instances for a given Effdt in a Profile. Example: for CatalogType = Compentency, there can be multiple instances for a particular Competency, each representing a different evaluation source (Self-Eval, Mgr Eval, Approved/Official, Learning, Recruiting). Prompt Table: JPM_JP_QUAL_INS
11	JPM_RATING1	Character(1)	VARCHAR2(1) NOT NULL	Generic rating field generally prompted by RATING_MODEL Prompt Table: REVW_RATING_TBL
12	JPM_IMPORTANCE	Character(1)	VARCHAR2(1) NOT NULL	profile item importance rating 1=1- Low 2=2 - Below Average 3=3 - Average 4=4 - Above Average 5=5 - High