HRS_JITEM_VW3 (SQL View)	Index Back
Job Opening Srchable Props If JPM Integration is turned on, Populates search criteria with the intersection of job opening's category type/items with primary person profile type category types and TAM content sections for JPM section properties that are searchable

HRS_JITEM_VW3

(SQL View)

Job Opening Srchable Props

If JPM Integration is turned on, Populates search criteria with the intersection of job opening's category type/items with primary person profile type category types and TAM content sections for JPM section properties that are searchable

SELECT DISTINCT JOITEM.HRS_JOB_OPENING_ID , HRSCTCFG.JPM_CAT_TYPE , JOITEM.JPM_CAT_ITEM_ID , JOITEM.JPM_CAT_ITEM_QUAL , JOITEM.JPM_CAT_ITEM_QUAL2 , JOITEM.EFFDT , JOITEM.EFF_STATUS , JPMPRIPI.JPM_JP_TYPE , JOITEM.JPM_ITEM_KEY_ID , JOITEM.JPM_JP_ITEM_SRC , JOITEM.JPM_SOURCE_ID1 , JOITEM.JPM_SOURCE_ID2 , JOITEM.JPM_SOURCE_ID3 , JOITEM.JPM_JP_QUAL_SET , JOITEM.JPM_JP_QUAL_SET2 , JOITEM.JPM_PROFILE_ID , JOITEM.JPM_PARENT_KEY_ID , JOITEM.JPM_WF_STATUS , %subrec(HRS_SRCH_ITM_SR,JOITEM) FROM PS_HRS_SCT_CFG_VW HRSCTCFG , PS_JPM_PRI_P_PRP_I JPMPRIPI , PS_HRS_JO_ITEMS JOITEM WHERE HRSCTCFG.JPM_JP_TYPE = JPMPRIPI.JPM_JP_TYPE AND HRSCTCFG.JPM_CAT_TYPE = JPMPRIPI.JPM_CAT_TYPE AND JPMPRIPI.JPM_JP_SRCH_FLG = 'Y' AND JOITEM.JPM_CAT_TYPE = JPMPRIPI.JPM_CAT_TYPE AND JOITEM.EFF_STATUS = 'A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 HRS_JOB_OPENING_ID Number(15,0) DECIMAL(15) NOT NULL Job Opening Clone

2 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

3 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

4 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).

5 JPM_CAT_ITEM_QUAL2 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).

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

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

8 JPM_JP_TYPE Character(12) VARCHAR2(12) NOT NULL Profile Type name for a JPM Profile Type

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

10 JPM_JP_ITEM_SRC Character(4) VARCHAR2(4) NOT NULL Indicates the source of a JPM profile item so that changes to the profile item will be blocked in JPM. The changes can only be made through the source product.
ELM=Learning Management
EP=ePerformance
NVQ=Manage NVQ GBR
PROF=Another Profile
WADM=Workforce Administration

11 JPM_SOURCE_ID1 Character(12) VARCHAR2(12) NOT NULL JPM Profile Item Source ID1

12 JPM_SOURCE_ID2 Character(12) VARCHAR2(12) NOT NULL JPM Profile Item Source ID3

13 JPM_SOURCE_ID3 Character(12) VARCHAR2(12) NOT NULL JPM Profile Item Source ID3

14 JPM_JP_QUAL_SET Character(12) VARCHAR2(12) NOT NULL 1st Instance Qualifier Set Name for a JPM Profile Item

15 JPM_JP_QUAL_SET2 Character(12) VARCHAR2(12) NOT NULL 2nd Instance Qualifier Set Name for a JPM Profile Item

16 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

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

18 JPM_WF_STATUS Character(2) VARCHAR2(2) NOT NULL JPM Workflow Status field
A=Approved
D=Denied
N=New
P=Pending
S=Saved

19 JPM_MANDATORY Character(1) VARCHAR2(1) NOT NULL jpm
N=No
Y=Yes

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

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

22 JPM_RATING2 Character(1) VARCHAR2(1) NOT NULL Generic rating field generally prompted by RATING_MODEL
Prompt Table: JPM_RATING_VW1

23 JPM_RATING3 Character(1) VARCHAR2(1) NOT NULL Generic rating field generally prompted by RATING_MODEL
Prompt Table: JPM_RATING_VW2

24 NVQ_STATUS Character(1) VARCHAR2(1) NOT NULL The status of the employee's NVQ
A=Achieved
C=Cancelled
E=Enrolled
P=Previous Achievement
R=Reviewed

25 JPM_INTEREST_LEVEL Character(1) VARCHAR2(1) NOT NULL Profile item property stating intertest level of the employee in the content item
Prompt Table: INT_RATING_VW

26 JPM_YN_1 Character(1) VARCHAR2(1) NOT NULL Generic Yes No property for profile items
N=No
Y=Yes

27 JPM_YN_2 Character(1) VARCHAR2(1) NOT NULL Generic Yes No property for profile items
N=No
Y=Yes

28 JPM_YN_3 Character(1) VARCHAR2(1) NOT NULL Generic Yes No property for profile items
N=No
Y=Yes

29 JPM_YN_4 Character(1) VARCHAR2(1) NOT NULL Generic Yes No property for profile items
N=No
Y=Yes

30 JPM_YN_5 Character(1) VARCHAR2(1) NOT NULL Generic Yes No property for profile items
N=No
Y=Yes

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

32 STATE Character(6) VARCHAR2(6) NOT NULL State
Prompt Table: HCR_STONLY_I

33 SCHOOL_CODE Character(10) VARCHAR2(10) NOT NULL School Code
Prompt Table: SCHOOL_TBL

34 MAJOR_CODE Character(10) VARCHAR2(10) NOT NULL Major Code
Prompt Table: MAJOR_TBL

35 JPM_MINOR_CD Character(10) VARCHAR2(10) NOT NULL Minor for degree or education content type
Prompt Table: JPM_MAJOR_VW

36 EDUC_LVL_AUS Character(1) VARCHAR2(1) NOT NULL This field refers to the highest education attained by the member of staff. This is an Australia specific code which is needed for DETYA electronic reporting purposes.
0=Non-Academic
1=Higher Degree
2=Bachelor's Degree
3=Other
4=No Info

37 APS_HEDUC_CD_AUS Character(2) VARCHAR2(2) NOT NULL The APS maintains a list of education codes that should be used for reporting purposes. Users should select the APS code to an existing education code in the system.
01=Doctorate
02=Masters
03=Postgraduate Diploma
04=Bachelor Degree
05=Undergraduate Diploma
06=Associate Diploma
07=Skilled Vocational Qualificatn
08=Basic Vocational Qualification
09=Year 12 (Higher School Cert)
11=Year 11
12=Year 10 (Leaving/School Cert)
13=Less than Year 10
97=Chose not to Give

38 FACULTY_CODE Character(10) VARCHAR2(10) NOT NULL Faculty Code (Japan HR)
Prompt Table: FACULTY_TBL_JPN

39 SUBFACULTY_CODE Character(10) VARCHAR2(10) NOT NULL Sub Faculty Code(Japan HR)
Prompt Table: SBFCLTY_TBL_JPN

40 MAJOR_CATEGORY Character(1) VARCHAR2(1) NOT NULL Major Category(Japan HR)
H=Humanities
S=Sciences

41 FP_SUBJECT_CD Character(3) VARCHAR2(3) NOT NULL FPS Subject Code
Prompt Table: FPMSUBJECTS

42 FP_SKIL_HIR Character(1) VARCHAR2(1) NOT NULL FPS Hiring Requirement
N=No
Y=Yes

43 FP_SKIL_PRM Character(1) VARCHAR2(1) NOT NULL FPS Promotion Requirement
N=No
Y=Yes

44 FP_SKIL_TEN Character(1) VARCHAR2(1) NOT NULL FPS Tenure Requirement
N=No
Y=Yes

45 FP_DEGR_REQUIRED Character(1) VARCHAR2(1) NOT NULL FPS Degree
N=No
Y=Yes

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	HRS_JOB_OPENING_ID	Number(15,0)	DECIMAL(15) NOT NULL	Job Opening Clone
2	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
3	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
4	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).
5	JPM_CAT_ITEM_QUAL2	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).
6	EFFDT	Date(10)	DATE NOT NULL	Effective Date Default Value: %date
7	EFF_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Effective Status A=Active I=Inactive Default Value: A
8	JPM_JP_TYPE	Character(12)	VARCHAR2(12) NOT NULL	Profile Type name for a JPM Profile Type
9	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.
10	JPM_JP_ITEM_SRC	Character(4)	VARCHAR2(4) NOT NULL	Indicates the source of a JPM profile item so that changes to the profile item will be blocked in JPM. The changes can only be made through the source product. ELM=Learning Management EP=ePerformance NVQ=Manage NVQ GBR PROF=Another Profile WADM=Workforce Administration
11	JPM_SOURCE_ID1	Character(12)	VARCHAR2(12) NOT NULL	JPM Profile Item Source ID1
12	JPM_SOURCE_ID2	Character(12)	VARCHAR2(12) NOT NULL	JPM Profile Item Source ID3
13	JPM_SOURCE_ID3	Character(12)	VARCHAR2(12) NOT NULL	JPM Profile Item Source ID3
14	JPM_JP_QUAL_SET	Character(12)	VARCHAR2(12) NOT NULL	1st Instance Qualifier Set Name for a JPM Profile Item
15	JPM_JP_QUAL_SET2	Character(12)	VARCHAR2(12) NOT NULL	2nd Instance Qualifier Set Name for a JPM Profile Item
16	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
17	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.
18	JPM_WF_STATUS	Character(2)	VARCHAR2(2) NOT NULL	JPM Workflow Status field A=Approved D=Denied N=New P=Pending S=Saved
19	JPM_MANDATORY	Character(1)	VARCHAR2(1) NOT NULL	jpm N=No Y=Yes
20	RATING_MODEL	Character(4)	VARCHAR2(4) NOT NULL	Rating Model Prompt Table: RATING_MDL_TBL
21	JPM_RATING1	Character(1)	VARCHAR2(1) NOT NULL	Generic rating field generally prompted by RATING_MODEL Prompt Table: REVW_RATING_TBL
22	JPM_RATING2	Character(1)	VARCHAR2(1) NOT NULL	Generic rating field generally prompted by RATING_MODEL Prompt Table: JPM_RATING_VW1
23	JPM_RATING3	Character(1)	VARCHAR2(1) NOT NULL	Generic rating field generally prompted by RATING_MODEL Prompt Table: JPM_RATING_VW2
24	NVQ_STATUS	Character(1)	VARCHAR2(1) NOT NULL	The status of the employee's NVQ A=Achieved C=Cancelled E=Enrolled P=Previous Achievement R=Reviewed
25	JPM_INTEREST_LEVEL	Character(1)	VARCHAR2(1) NOT NULL	Profile item property stating intertest level of the employee in the content item Prompt Table: INT_RATING_VW
26	JPM_YN_1	Character(1)	VARCHAR2(1) NOT NULL	Generic Yes No property for profile items N=No Y=Yes
27	JPM_YN_2	Character(1)	VARCHAR2(1) NOT NULL	Generic Yes No property for profile items N=No Y=Yes
28	JPM_YN_3	Character(1)	VARCHAR2(1) NOT NULL	Generic Yes No property for profile items N=No Y=Yes
29	JPM_YN_4	Character(1)	VARCHAR2(1) NOT NULL	Generic Yes No property for profile items N=No Y=Yes
30	JPM_YN_5	Character(1)	VARCHAR2(1) NOT NULL	Generic Yes No property for profile items N=No Y=Yes
31	COUNTRY	Character(3)	VARCHAR2(3) NOT NULL	Country Prompt Table: COUNTRY_TBL
32	STATE	Character(6)	VARCHAR2(6) NOT NULL	State Prompt Table: HCR_STONLY_I
33	SCHOOL_CODE	Character(10)	VARCHAR2(10) NOT NULL	School Code Prompt Table: SCHOOL_TBL
34	MAJOR_CODE	Character(10)	VARCHAR2(10) NOT NULL	Major Code Prompt Table: MAJOR_TBL
35	JPM_MINOR_CD	Character(10)	VARCHAR2(10) NOT NULL	Minor for degree or education content type Prompt Table: JPM_MAJOR_VW
36	EDUC_LVL_AUS	Character(1)	VARCHAR2(1) NOT NULL	This field refers to the highest education attained by the member of staff. This is an Australia specific code which is needed for DETYA electronic reporting purposes. 0=Non-Academic 1=Higher Degree 2=Bachelor's Degree 3=Other 4=No Info
37	APS_HEDUC_CD_AUS	Character(2)	VARCHAR2(2) NOT NULL	The APS maintains a list of education codes that should be used for reporting purposes. Users should select the APS code to an existing education code in the system. 01=Doctorate 02=Masters 03=Postgraduate Diploma 04=Bachelor Degree 05=Undergraduate Diploma 06=Associate Diploma 07=Skilled Vocational Qualificatn 08=Basic Vocational Qualification 09=Year 12 (Higher School Cert) 11=Year 11 12=Year 10 (Leaving/School Cert) 13=Less than Year 10 97=Chose not to Give
38	FACULTY_CODE	Character(10)	VARCHAR2(10) NOT NULL	Faculty Code (Japan HR) Prompt Table: FACULTY_TBL_JPN
39	SUBFACULTY_CODE	Character(10)	VARCHAR2(10) NOT NULL	Sub Faculty Code(Japan HR) Prompt Table: SBFCLTY_TBL_JPN
40	MAJOR_CATEGORY	Character(1)	VARCHAR2(1) NOT NULL	Major Category(Japan HR) H=Humanities S=Sciences
41	FP_SUBJECT_CD	Character(3)	VARCHAR2(3) NOT NULL	FPS Subject Code Prompt Table: FPMSUBJECTS
42	FP_SKIL_HIR	Character(1)	VARCHAR2(1) NOT NULL	FPS Hiring Requirement N=No Y=Yes
43	FP_SKIL_PRM	Character(1)	VARCHAR2(1) NOT NULL	FPS Promotion Requirement N=No Y=Yes
44	FP_SKIL_TEN	Character(1)	VARCHAR2(1) NOT NULL	FPS Tenure Requirement N=No Y=Yes
45	FP_DEGR_REQUIRED	Character(1)	VARCHAR2(1) NOT NULL	FPS Degree N=No Y=Yes