JPM_CATIMAXD_VW (SQL View)	Index Back
Catalog Items - Talent Mgmt Defines all of the Catalog Items for a particular Catalog Type. Important- All fields that are property fields yet not contained in the property subrecords (required properties) must exist before the property subrecords. All fields that are not properties but need to be fields in the record should be after the properties subrecord/s. Any field placed before the properties subrecords will become required properties for catalog types.

JPM_CATIMAXD_VW

(SQL View)

Catalog Items - Talent Mgmt

Defines all of the Catalog Items for a particular Catalog Type. Important- All fields that are property fields yet not contained in the property subrecords (required properties) must exist before the property subrecords. All fields that are not properties but need to be fields in the record should be after the properties subrecord/s. Any field placed before the properties subrecords will become required properties for catalog types.

SELECT C.JPM_CAT_TYPE ,C.JPM_CAT_ITEM_ID ,C.EFFDT ,C.EFF_STATUS ,C.JPM_DESCR90 , %subrec(JPM_CMN_PRP_SBR, C) , %subrec(JPM_CAT_PRP_SBR, C) , C.LASTUPDDTTM , C.LASTUPDOPRID FROM PS_JPM_CAT_ITEMS C WHERE C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_JPM_CAT_ITEMS C1 WHERE C1.EFF_STATUS = 'A' AND C1.JPM_CAT_TYPE = C.JPM_CAT_TYPE AND C1.JPM_CAT_ITEM_ID = C.JPM_CAT_ITEM_ID)

Related Language Record: JPM_CAT_ITM_LNG

Parent record: JPM_CAT_TYPES

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 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_CT_NOHOC_VW

2 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_ITEMS

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

4 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive
Default Value: A

5 JPM_DESCR90 Character(90) VARCHAR2(90) NOT NULL jpm

6 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
Prompt Table: COUNTRY_TBL

7 JPM_DATE_1 Date(10) DATE User Date (1)

8 JPM_DATE_2 Date(10) DATE User Date (2)

9 JPM_EVAL_MTHD Character(4) VARCHAR2(4) NOT NULL Evaluation Method

10 JPM_TEXT1325_1 Long Character(1325) VARCHAR2(1325) User Text - 1325 Char

11 JPM_TEXT1325_2 Long Character(1325) VARCHAR2(1325) User Text - 1325 Char

12 JPM_TEXT254_1 Character(254) VARCHAR2(254) NOT NULL User Text - 254 Char

13 JPM_TEXT254_2 Character(254) VARCHAR2(254) NOT NULL User Text - 254 Char

14 JPM_TEXT254_3 Character(254) VARCHAR2(254) NOT NULL User Text - 254 Char

15 JPM_TEXT254_4 Character(254) VARCHAR2(254) NOT NULL User Text - 254 Char

16 JPM_YN_1 Character(1) VARCHAR2(1) NOT NULL User Yes/No (1)
N=No
Y=Yes
Y/N Table Edit
Default Value: N

17 JPM_YN_2 Character(1) VARCHAR2(1) NOT NULL User Yes/No (2)
N=No
Y=Yes
Y/N Table Edit
Default Value: N

18 JPM_YN_3 Character(1) VARCHAR2(1) NOT NULL User Yes/No (3)
N=No
Y=Yes
Y/N Table Edit
Default Value: N

19 JPM_YN_4 Character(1) VARCHAR2(1) NOT NULL User Yes/No (4)
N=No
Y=Yes
Y/N Table Edit
Default Value: N

20 JPM_YN_5 Character(1) VARCHAR2(1) NOT NULL User Yes/No (5)
N=No
Y=Yes
Y/N Table Edit
Default Value: N

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

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

23 JPM_CAT_ITEM_SRC Character(4) VARCHAR2(4) NOT NULL Content Item Source
Prompt Table: JPM_CAT_SRC

24 CM_CATEGORY Character(1) VARCHAR2(1) NOT NULL Competency Category
A=Ability
K=Knowledge
N=NVQ - Unit
O=Other
S=Skill
Prompt Table: JPM_CM_CATEGORY

25 JPM_DURATION_1 Number(3,0) SMALLINT NOT NULL Duration (1)

26 JPM_DURATION_TYPE1 Character(1) VARCHAR2(1) NOT NULL Duration Type (1)
D=Days
H=Hours
M=Months
Y=Years

27 JPM_DURATION_2 Number(3,0) SMALLINT NOT NULL Duration (2)

28 JPM_DURATION_TYPE2 Character(1) VARCHAR2(1) NOT NULL Duration Type (2)
D=Days
H=Hours
M=Months
Y=Years

29 EDUCATION_LVL Character(2) VARCHAR2(2) NOT NULL Education Level
01=No Formal Education
02=Elementary School Completed
03=Some High School
04=High School Graduate
05=Terminal Occupation Prog DNC
06=Terminal Occupation Program
07=Some College - Less than 1 Yr
08=One Year College
09=Two Years College
10=Associate Degree
11=Three Years College
12=Four Years College
13=Bachelor Degree
14=Post Bachelors
15=First Professional
16=Post-first Professional
17=Master's Degree
18=Post Master's
19=Sixth-year Degree
20=Post-sixth Year
21=Doctorate Degree
22=Post-Doctorate
A=University - Advanced
T=Trade School
U=University

30 NVQ_LEVEL Character(1) VARCHAR2(1) NOT NULL Level
1=1
2=2
3=3
4=4
5=5

31 HP_STATS_DEG_LVL Character(2) VARCHAR2(2) NOT NULL Stats-Can Degree Level
01=Doctoral
02=Professional Para-Medical
03=Masters & Equivalent License
04=Graduate Program
05=Bachelor
06=Professional Designation
07=Undergraduate Diploma
08=No Degree/Diploma/Prof.Desgnt
09=Unknown

32 FP_DEGREE_LVL Character(1) VARCHAR2(1) NOT NULL Level
1=Primary Leaving Certificate
2=VcalTrainCertifandAlternatives
3=TechSchool CertifandAlternativ
4=High School Diploma
5=2 Years' Higher Education
6=3 Years' Higher Education
7=4 Years' Higher Education
8=5 + > Years' Higher Education

33 FIELD_OF_STUDY_GER Character(3) VARCHAR2(3) NOT NULL Field Of Study
COM=Commercial
IDN=Industrial
TEC=Technical

34 TYPE_OF_STUDY_GER Character(3) VARCHAR2(3) NOT NULL Type Of Study
CHE=Chemistry/Physics/Mat. Appl.
COM=Commerce/Banks
ELE=Electronics/Data Processing
FOD=Food/Kindered Products
FOR=Forestry/Timber/Agriculture
GLA=Glass/Ceramics
HOT=Hotels/Restaurants
MET=Metalworker/Mechanic/Engineer.
MIN=Construction/Mining
OTH=Other
PRI=Printing/Paper
PUB=Public Sector
TEX=Textiles/Clothing

35 SATISFACTION_MTHD Character(1) VARCHAR2(1) NOT NULL Satisfaction Method
D=Dispensation Given
F=Off the Job Training
L=Possess Licence/Certificate
O=On the Job Training
R=Recognition of Prior Learning
S=Supervisor Assessment
T=Attend Training Course

36 EP_SUB_LEVEL Character(1) VARCHAR2(1) NOT NULL Sub-Competency level
A=Advanced
B=Basic
I=Intermediate

37 DESCRLONG Long Character CLOB Long Description

38 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.

39 LASTUPDOPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	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_CT_NOHOC_VW
2	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_ITEMS
3	EFFDT	Date(10)	DATE NOT NULL	Effective Date Default Value: %date
4	EFF_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Effective Status A=Active I=Inactive Default Value: A
5	JPM_DESCR90	Character(90)	VARCHAR2(90) NOT NULL	jpm
6	COUNTRY	Character(3)	VARCHAR2(3) NOT NULL	Country Prompt Table: COUNTRY_TBL
7	JPM_DATE_1	Date(10)	DATE	User Date (1)
8	JPM_DATE_2	Date(10)	DATE	User Date (2)
9	JPM_EVAL_MTHD	Character(4)	VARCHAR2(4) NOT NULL	Evaluation Method
10	JPM_TEXT1325_1	Long Character(1325)	VARCHAR2(1325)	User Text - 1325 Char
11	JPM_TEXT1325_2	Long Character(1325)	VARCHAR2(1325)	User Text - 1325 Char
12	JPM_TEXT254_1	Character(254)	VARCHAR2(254) NOT NULL	User Text - 254 Char
13	JPM_TEXT254_2	Character(254)	VARCHAR2(254) NOT NULL	User Text - 254 Char
14	JPM_TEXT254_3	Character(254)	VARCHAR2(254) NOT NULL	User Text - 254 Char
15	JPM_TEXT254_4	Character(254)	VARCHAR2(254) NOT NULL	User Text - 254 Char
16	JPM_YN_1	Character(1)	VARCHAR2(1) NOT NULL	User Yes/No (1) N=No Y=Yes Y/N Table Edit Default Value: N
17	JPM_YN_2	Character(1)	VARCHAR2(1) NOT NULL	User Yes/No (2) N=No Y=Yes Y/N Table Edit Default Value: N
18	JPM_YN_3	Character(1)	VARCHAR2(1) NOT NULL	User Yes/No (3) N=No Y=Yes Y/N Table Edit Default Value: N
19	JPM_YN_4	Character(1)	VARCHAR2(1) NOT NULL	User Yes/No (4) N=No Y=Yes Y/N Table Edit Default Value: N
20	JPM_YN_5	Character(1)	VARCHAR2(1) NOT NULL	User Yes/No (5) N=No Y=Yes Y/N Table Edit Default Value: N
21	RATING_MODEL	Character(4)	VARCHAR2(4) NOT NULL	Rating Model Prompt Table: RATING_MDL_TBL
22	DESCRSHORT	Character(10)	VARCHAR2(10) NOT NULL	Short Description
23	JPM_CAT_ITEM_SRC	Character(4)	VARCHAR2(4) NOT NULL	Content Item Source Prompt Table: JPM_CAT_SRC
24	CM_CATEGORY	Character(1)	VARCHAR2(1) NOT NULL	Competency Category A=Ability K=Knowledge N=NVQ - Unit O=Other S=Skill Prompt Table: JPM_CM_CATEGORY
25	JPM_DURATION_1	Number(3,0)	SMALLINT NOT NULL	Duration (1)
26	JPM_DURATION_TYPE1	Character(1)	VARCHAR2(1) NOT NULL	Duration Type (1) D=Days H=Hours M=Months Y=Years
27	JPM_DURATION_2	Number(3,0)	SMALLINT NOT NULL	Duration (2)
28	JPM_DURATION_TYPE2	Character(1)	VARCHAR2(1) NOT NULL	Duration Type (2) D=Days H=Hours M=Months Y=Years
29	EDUCATION_LVL	Character(2)	VARCHAR2(2) NOT NULL	Education Level 01=No Formal Education 02=Elementary School Completed 03=Some High School 04=High School Graduate 05=Terminal Occupation Prog DNC 06=Terminal Occupation Program 07=Some College - Less than 1 Yr 08=One Year College 09=Two Years College 10=Associate Degree 11=Three Years College 12=Four Years College 13=Bachelor Degree 14=Post Bachelors 15=First Professional 16=Post-first Professional 17=Master's Degree 18=Post Master's 19=Sixth-year Degree 20=Post-sixth Year 21=Doctorate Degree 22=Post-Doctorate A=University - Advanced T=Trade School U=University
30	NVQ_LEVEL	Character(1)	VARCHAR2(1) NOT NULL	Level 1=1 2=2 3=3 4=4 5=5
31	HP_STATS_DEG_LVL	Character(2)	VARCHAR2(2) NOT NULL	Stats-Can Degree Level 01=Doctoral 02=Professional Para-Medical 03=Masters & Equivalent License 04=Graduate Program 05=Bachelor 06=Professional Designation 07=Undergraduate Diploma 08=No Degree/Diploma/Prof.Desgnt 09=Unknown
32	FP_DEGREE_LVL	Character(1)	VARCHAR2(1) NOT NULL	Level 1=Primary Leaving Certificate 2=VcalTrainCertifandAlternatives 3=TechSchool CertifandAlternativ 4=High School Diploma 5=2 Years' Higher Education 6=3 Years' Higher Education 7=4 Years' Higher Education 8=5 + > Years' Higher Education
33	FIELD_OF_STUDY_GER	Character(3)	VARCHAR2(3) NOT NULL	Field Of Study COM=Commercial IDN=Industrial TEC=Technical
34	TYPE_OF_STUDY_GER	Character(3)	VARCHAR2(3) NOT NULL	Type Of Study CHE=Chemistry/Physics/Mat. Appl. COM=Commerce/Banks ELE=Electronics/Data Processing FOD=Food/Kindered Products FOR=Forestry/Timber/Agriculture GLA=Glass/Ceramics HOT=Hotels/Restaurants MET=Metalworker/Mechanic/Engineer. MIN=Construction/Mining OTH=Other PRI=Printing/Paper PUB=Public Sector TEX=Textiles/Clothing
35	SATISFACTION_MTHD	Character(1)	VARCHAR2(1) NOT NULL	Satisfaction Method D=Dispensation Given F=Off the Job Training L=Possess Licence/Certificate O=On the Job Training R=Recognition of Prior Learning S=Supervisor Assessment T=Attend Training Course
36	EP_SUB_LEVEL	Character(1)	VARCHAR2(1) NOT NULL	Sub-Competency level A=Advanced B=Basic I=Intermediate
37	DESCRLONG	Long Character	CLOB	Long Description
38	LASTUPDDTTM	DateTime(26)	TIMESTAMP	Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
39	LASTUPDOPRID	Character(30)	VARCHAR2(30) NOT NULL	Specifies the User ID which made the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.