JPM_SRCH_ITM_VW

(SQL View)
 Index Back

SES Prioritized Profile Items

Selects the latest effective dated searchable Profile Item row within content type, item id, instance qualifier 1, and instance qualifier 2. The highest priority Instance Qualifier1 within content type and item id is selected. (This is based on the prioritization defined in the Instance Qualifier Set table). The result set unions parent profile items and related items. Keys: content type, item id, instance qualifier 2, related item id.

SELECT A.JPM_PROFILE_ID , A.JPM_CAT_TYPE , A.JPM_CAT_ITEM_ID , A.JPM_CAT_ITEM_QUAL2 , ' ' , %subrec(JPM_SRCH_ITM_SR,A) FROM PS_JPM_JP_ITEMS A WHERE A.JPM_PARENT_KEY_ID = 0 AND (A.JPM_CAT_ITEM_QUAL = ( SELECT MAX(Q.JPM_CAT_ITEM_QUAL) FROM PS_JPM_JP_ITEMS A1 , PS_JPM_JP_QUAL_INS Q WHERE A1.JPM_PROFILE_ID = A.JPM_PROFILE_ID AND A1.JPM_CAT_TYPE = A.JPM_CAT_TYPE AND A1.JPM_CAT_ITEM_ID = A.JPM_CAT_ITEM_ID AND Q.JPM_CAT_TYPE = A.JPM_CAT_TYPE AND Q.JPM_JP_QUAL_SET = A.JPM_JP_QUAL_SET AND Q.JPM_CAT_ITEM_QUAL = A1.JPM_CAT_ITEM_QUAL AND Q.JPM_SRCH_FLG = 'Y' AND Q.SEQNO = ( SELECT MIN(Q2.SEQNO) FROM PS_JPM_JP_ITEMS A2 , PS_JPM_JP_QUAL_INS Q2 WHERE A2.JPM_PROFILE_ID = A.JPM_PROFILE_ID AND A2.JPM_CAT_TYPE = A.JPM_CAT_TYPE AND A2.JPM_CAT_ITEM_ID = A.JPM_CAT_ITEM_ID AND Q2.JPM_CAT_TYPE = A.JPM_CAT_TYPE AND Q2.JPM_JP_QUAL_SET = A.JPM_JP_QUAL_SET AND Q2.JPM_CAT_ITEM_QUAL = A2.JPM_CAT_ITEM_QUAL AND Q2.JPM_SRCH_FLG = 'Y')) OR A.JPM_CAT_ITEM_QUAL = ' ') AND A.EFFDT = ( SELECT MAX(A3.EFFDT) FROM PS_JPM_JP_ITEMS A3 WHERE A3.JPM_PROFILE_ID = A.JPM_PROFILE_ID AND A3.JPM_CAT_TYPE = A.JPM_CAT_TYPE AND A3.JPM_CAT_ITEM_ID = A.JPM_CAT_ITEM_ID AND A3.JPM_CAT_ITEM_QUAL = A.JPM_CAT_ITEM_QUAL AND A3.JPM_CAT_ITEM_QUAL2 = A.JPM_CAT_ITEM_QUAL2) AND A.EFF_STATUS = 'A' UNION SELECT B.JPM_PROFILE_ID ,B.JPM_CAT_TYPE ,A.JPM_CAT_ITEM_ID ,A.JPM_CAT_ITEM_QUAL2 ,B.JPM_CAT_ITEM_ID , %subrec(JPM_SRCH_ITM_SR,B) FROM PS_JPM_JP_ITEMS A , PS_JPM_JP_ITEMS B WHERE A.JPM_PARENT_KEY_ID = 0 AND (A.JPM_CAT_ITEM_QUAL = ( SELECT MAX(Q.JPM_CAT_ITEM_QUAL) FROM PS_JPM_JP_ITEMS A1 , PS_JPM_JP_QUAL_INS Q WHERE A1.JPM_PROFILE_ID = A.JPM_PROFILE_ID AND A1.JPM_CAT_TYPE = A.JPM_CAT_TYPE AND A1.JPM_CAT_ITEM_ID = A.JPM_CAT_ITEM_ID AND Q.JPM_CAT_TYPE = A.JPM_CAT_TYPE AND Q.JPM_JP_QUAL_SET = A.JPM_JP_QUAL_SET AND Q.JPM_CAT_ITEM_QUAL = A1.JPM_CAT_ITEM_QUAL AND Q.JPM_SRCH_FLG = 'Y' AND Q.SEQNO = ( SELECT MIN(Q2.SEQNO) FROM PS_JPM_JP_ITEMS A2 , PS_JPM_JP_QUAL_INS Q2 WHERE A2.JPM_PROFILE_ID = A.JPM_PROFILE_ID AND A2.JPM_CAT_TYPE = A.JPM_CAT_TYPE AND A2.JPM_CAT_ITEM_ID = A.JPM_CAT_ITEM_ID AND Q2.JPM_CAT_TYPE = A.JPM_CAT_TYPE AND Q2.JPM_JP_QUAL_SET = A.JPM_JP_QUAL_SET AND Q2.JPM_CAT_ITEM_QUAL = A2.JPM_CAT_ITEM_QUAL AND Q2.JPM_SRCH_FLG = 'Y')) OR A.JPM_CAT_ITEM_QUAL = ' ') AND A.EFFDT = ( SELECT MAX(A3.EFFDT) FROM PS_JPM_JP_ITEMS A3 WHERE A3.JPM_PROFILE_ID = A.JPM_PROFILE_ID AND A3.JPM_CAT_TYPE = A.JPM_CAT_TYPE AND A3.JPM_CAT_ITEM_ID = A.JPM_CAT_ITEM_ID AND A3.JPM_CAT_ITEM_QUAL = A.JPM_CAT_ITEM_QUAL AND A3.JPM_CAT_ITEM_QUAL2 = A.JPM_CAT_ITEM_QUAL2) AND A.EFF_STATUS = 'A' AND B.JPM_PARENT_KEY_ID > 0 AND B.JPM_PARENT_KEY_ID = A.JPM_ITEM_KEY_ID

# 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 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_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).
5 JPM_CAT_ITEM_ID2 Character(12) VARCHAR2(12) NOT NULL Secondary content item id (as in a related item- such as a child or a supporting item of another item).
6 RATING_MODEL Character(4) VARCHAR2(4) NOT NULL Rating Model

Prompt Table: RATING_MDL_TBL

7 JPM_RATING1 Character(1) VARCHAR2(1) NOT NULL Generic rating field generally prompted by RATING_MODEL

Prompt Table: REVW_RATING_TBL

8 JPM_RATING2 Character(1) VARCHAR2(1) NOT NULL Generic rating field generally prompted by RATING_MODEL

Prompt Table: JPM_RATING_VW1

9 JPM_RATING3 Character(1) VARCHAR2(1) NOT NULL Generic rating field generally prompted by RATING_MODEL

Prompt Table: JPM_RATING_VW2

10 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
11 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

12 JPM_YN_1 Character(1) VARCHAR2(1) NOT NULL Generic Yes No property for profile items
N=No
Y=Yes
13 JPM_YN_2 Character(1) VARCHAR2(1) NOT NULL Generic Yes No property for profile items
N=No
Y=Yes
14 JPM_YN_3 Character(1) VARCHAR2(1) NOT NULL Generic Yes No property for profile items
N=No
Y=Yes
15 JPM_YN_4 Character(1) VARCHAR2(1) NOT NULL Generic Yes No property for profile items
N=No
Y=Yes
16 JPM_YN_5 Character(1) VARCHAR2(1) NOT NULL Generic Yes No property for profile items
N=No
Y=Yes
17 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: COUNTRY_TBL

18 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: HCR_STONLY_I

19 SCHOOL_CODE Character(10) VARCHAR2(10) NOT NULL School Code

Prompt Table: SCHOOL_TBL

20 MAJOR_CODE Character(10) VARCHAR2(10) NOT NULL Major Code

Prompt Table: MAJOR_TBL

21 JPM_MINOR_CD Character(10) VARCHAR2(10) NOT NULL Minor for degree or education content type

Prompt Table: JPM_MAJOR_VW

22 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
23 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
24 FACULTY_CODE Character(10) VARCHAR2(10) NOT NULL Faculty Code (Japan HR)

Prompt Table: FACULTY_TBL_JPN

25 SUBFACULTY_CODE Character(10) VARCHAR2(10) NOT NULL Sub Faculty Code(Japan HR)

Prompt Table: SBFCLTY_TBL_JPN

26 MAJOR_CATEGORY Character(1) VARCHAR2(1) NOT NULL Major Category(Japan HR)
H=Humanities
S=Sciences
27 FP_SUBJECT_CD Character(3) VARCHAR2(3) NOT NULL FPS Subject Code

Prompt Table: FPMSUBJECTS

28 FP_SKIL_HIR Character(1) VARCHAR2(1) NOT NULL FPS Hiring Requirement
N=No
Y=Yes
29 FP_SKIL_PRM Character(1) VARCHAR2(1) NOT NULL FPS Promotion Requirement
N=No
Y=Yes
30 FP_SKIL_TEN Character(1) VARCHAR2(1) NOT NULL FPS Tenure Requirement
N=No
Y=Yes
31 FP_DEGR_REQUIRED Character(1) VARCHAR2(1) NOT NULL FPS Degree
N=No
Y=Yes
32 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: BUSUNIT_HR_VW

33 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

Prompt Table: LOCATION_TBL
Set Control Field: SETID_LOCATION

34 JPM_PROMPT_1 Character(12) VARCHAR2(12) NOT NULL User Prompt (1)

Prompt Table: JPM_PROMPT_DVW

35 JPM_PROMPT_2 Character(12) VARCHAR2(12) NOT NULL User Prompt (2)

Prompt Table: JPM_PROMPT_DVW

36 JPM_PROMPT_3 Character(12) VARCHAR2(12) NOT NULL User Prompt (3)

Prompt Table: JPM_PROMPT_DVW

37 JPM_PROMPT_4 Character(12) VARCHAR2(12) NOT NULL User Prompt (4)

Prompt Table: JPM_PROMPT_DVW

38 JPM_PROMPT_5 Character(12) VARCHAR2(12) NOT NULL User Prompt (5)

Prompt Table: JPM_PROMPT_DVW

39 JPM_PROMPT_6 Character(12) VARCHAR2(12) NOT NULL User Prompt (6)

Prompt Table: JPM_PROMPT_DVW

40 JPM_PROMPT_7 Character(12) VARCHAR2(12) NOT NULL User Prompt (7)

Prompt Table: JPM_PROMPT_DVW

41 JPM_PROMPT_8 Character(12) VARCHAR2(12) NOT NULL User Prompt (8)

Prompt Table: JPM_PROMPT_DVW

42 JPM_PROMPT_9 Character(12) VARCHAR2(12) NOT NULL User Prompt (9)

Prompt Table: JPM_PROMPT_DVW

43 JPM_PROMPT_10 Character(12) VARCHAR2(12) NOT NULL User Prompt (10)

Prompt Table: JPM_PROMPT_DVW

44 JPM_PROMPT_11 Character(12) VARCHAR2(12) NOT NULL User Prompt (11)

Prompt Table: JPM_PROMPT_DVW

45 JPM_PROMPT_12 Character(12) VARCHAR2(12) NOT NULL User Prompt (12)

Prompt Table: JPM_PROMPT_DVW

46 JPM_PROMPT_13 Character(12) VARCHAR2(12) NOT NULL User Prompt (13)

Prompt Table: JPM_PROMPT_DVW

47 JPM_PROMPT_14 Character(12) VARCHAR2(12) NOT NULL User Prompt (14)

Prompt Table: JPM_PROMPT_DVW

48 JPM_PROMPT_15 Character(12) VARCHAR2(12) NOT NULL User Prompt (15)

Prompt Table: JPM_PROMPT_DVW

49 JPM_PROMPT_16 Character(12) VARCHAR2(12) NOT NULL User Prompt (16)

Prompt Table: JPM_PROMPT_DVW

50 JPM_PROMPT_17 Character(12) VARCHAR2(12) NOT NULL User Prompt (17)

Prompt Table: JPM_PROMPT_DVW

51 JPM_PROMPT_18 Character(12) VARCHAR2(12) NOT NULL User Prompt (18)

Prompt Table: JPM_PROMPT_DVW

52 JPM_PROMPT_19 Character(12) VARCHAR2(12) NOT NULL User Prompt (19)

Prompt Table: JPM_PROMPT_DVW

53 JPM_PROMPT_20 Character(12) VARCHAR2(12) NOT NULL User Prompt (20)

Prompt Table: JPM_PROMPT_DVW