JPM_CUR_ITMS_VW

(SQL View)
Index Back

Current Profile Items

Returns the latest effective dated row for each active profile item.

SELECT A.JPM_PROFILE_ID , A.JPM_CAT_TYPE , A.JPM_CAT_ITEM_ID , A.JPM_CAT_ITEM_QUAL , A.JPM_CAT_ITEM_QUAL2 , A.EFFDT , A.EFF_STATUS , A.JPM_ITEM_KEY_ID , A.JPM_JP_ITEM_SRC , A.JPM_SOURCE_ID1 , A.JPM_SOURCE_ID2 , A.JPM_SOURCE_ID3 , A.JPM_JP_QUAL_SET , A.JPM_JP_QUAL_SET2 , A.JPM_PARENT_KEY_ID , A.JPM_WF_STATUS , %subrec(JPM_ITM_PRP_SBR,A) FROM PS_JPM_JP_ITEMS A WHERE 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 A.JPM_PARENT_KEY_ID = 0

# 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.
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_ITM_DV3

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

Prompt Table: JPM_JP_QUAL_INS

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

Prompt Table: JPM_PROMPT_DVW

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_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 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
10 JPM_SOURCE_ID1 Character(12) VARCHAR2(12) NOT NULL JPM Profile Item Source ID1
11 JPM_SOURCE_ID2 Character(12) VARCHAR2(12) NOT NULL JPM Profile Item Source ID3
12 JPM_SOURCE_ID3 Character(12) VARCHAR2(12) NOT NULL JPM Profile Item Source ID3
13 JPM_JP_QUAL_SET Character(12) VARCHAR2(12) NOT NULL 1st Instance Qualifier Set Name for a JPM Profile Item
14 JPM_JP_QUAL_SET2 Character(12) VARCHAR2(12) NOT NULL 2nd Instance Qualifier Set Name for a JPM Profile Item
15 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.
16 JPM_WF_STATUS Character(2) VARCHAR2(2) NOT NULL JPM Workflow Status field
A=Approved
D=Denied
N=New
P=Pending
S=Saved
17 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: COUNTRY_TBL

18 JPM_DATE_1 Date(10) DATE generic profile item date property
19 JPM_DATE_2 Date(10) DATE generic profile item date property
20 JPM_EVAL_MTHD Character(4) VARCHAR2(4) NOT NULL Competency evaluation method.
21 JPM_TEXT1325_1 Long Character(1325) VARCHAR2(1325) Generic Text property for profile items
22 JPM_TEXT1325_2 Long Character(1325) VARCHAR2(1325) Generic Text property for profile items
23 JPM_TEXT254_1 Character(254) VARCHAR2(254) NOT NULL Generic Text property for profile items
24 JPM_TEXT254_2 Character(254) VARCHAR2(254) NOT NULL Generic Text property for profile items
25 JPM_TEXT254_3 Character(254) VARCHAR2(254) NOT NULL Generic Text property for profile items
26 JPM_TEXT254_4 Character(254) VARCHAR2(254) NOT NULL Generic Text property for profile items
27 JPM_YN_1 Character(1) VARCHAR2(1) NOT NULL Generic Yes No property for profile items
N=No
Y=Yes

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

32 RATING_MODEL Character(4) VARCHAR2(4) NOT NULL Rating Model

Prompt Table: RATING_MDL_TBL

33 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

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

Y/N Table Edit

Default Value: N

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

Prompt Table: REVW_RATING_TBL

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

Prompt Table: REVW_RATING_TBL

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

Prompt Table: REVW_RATING_TBL

38 JPM_TEXT254_5 Character(254) VARCHAR2(254) NOT NULL Generic Text property for profile items
39 JPM_DATE_3 Date(10) DATE generic profile item date property
40 JPM_DATE_4 Date(10) DATE generic profile item date property
41 JPM_DATE_5 Date(10) DATE generic profile item date property
42 JPM_DATE_6 Date(10) DATE generic profile item date property
43 JPM_INTEGER_1 Number(8,0) INTEGER NOT NULL generic profile item integer property
44 JPM_INTEGER_2 Number(8,0) INTEGER NOT NULL generic profile item integer property
45 JPM_PCT_1 Number(3,0) SMALLINT NOT NULL generic percentage property
46 JPM_PCT_2 Number(3,0) SMALLINT NOT NULL generic percentage property
47 JPM_DECIMAL_1 Number(10,2) DECIMAL(9,2) NOT NULL generic profile item decimal property
48 JPM_DECIMAL_2 Number(10,2) DECIMAL(9,2) NOT NULL generic profile item decimal property
49 JPM_PROMPT_1 Character(12) VARCHAR2(12) NOT NULL User Prompt (1)

Prompt Table: JPM_PROMPT_DVW

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

Prompt Table: JPM_PROMPT_DVW

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

Prompt Table: JPM_PROMPT_DVW

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

Prompt Table: JPM_PROMPT_DVW

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

Prompt Table: JPM_PROMPT_DVW

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

Prompt Table: JPM_PROMPT_DVW

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

Prompt Table: JPM_PROMPT_DVW

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

Prompt Table: JPM_PROMPT_DVW

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

Prompt Table: JPM_PROMPT_DVW

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

Prompt Table: JPM_PROMPT_DVW

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

Prompt Table: JPM_PROMPT_DVW

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

Prompt Table: JPM_PROMPT_DVW

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

Prompt Table: JPM_PROMPT_DVW

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

Prompt Table: JPM_PROMPT_DVW

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

Prompt Table: JPM_PROMPT_DVW

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

Prompt Table: JPM_PROMPT_DVW

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

Prompt Table: JPM_PROMPT_DVW

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

Prompt Table: JPM_PROMPT_DVW

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

Prompt Table: JPM_PROMPT_DVW

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

Prompt Table: JPM_PROMPT_DVW

69 JPM_PERSON_ID_1 Character(11) VARCHAR2(11) NOT NULL Person id property for profile items

Prompt Table: JPM_PERSON_NAME

70 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
71 JPM_VERIFY_METHOD Character(1) VARCHAR2(1) NOT NULL Property for how the profile item was verified.
E=Employee Claim
J=On the Job Training
N=Not Verified
O=Employer Observation
T=By Test
V=NVQ Authority
72 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: HCR_STONLY_I

73 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
74 SCHOOL_CODE Character(10) VARCHAR2(10) NOT NULL School Code

Prompt Table: JPM_SCHOOL_TBL

75 SCHOOL_DESCR Character(100) VARCHAR2(100) NOT NULL Name of the school
76 MAJOR_CODE Character(10) VARCHAR2(10) NOT NULL Major Code

Prompt Table: MAJOR_TBL

77 MAJOR_DESCR Character(100) VARCHAR2(100) NOT NULL Description of the major
78 JPM_MINOR_CD Character(10) VARCHAR2(10) NOT NULL Minor for degree or education content type

Prompt Table: JPM_MAJOR_VW

79 MINOR_DESCR Character(100) VARCHAR2(100) NOT NULL Description of the minor
80 AVERAGE_GRADE Character(5) VARCHAR2(5) NOT NULL Average Grade
81 PRACTIC_GRADE_GER Character(4) VARCHAR2(4) NOT NULL Track Practical Grade applicable in Germany.
82 THEORY_GRADE_GER Character(4) VARCHAR2(4) NOT NULL Track Theoretical Grade applicable in Germany.
83 IPE_SW Character(1) VARCHAR2(1) NOT NULL Intern Professional Education

Y/N Table Edit

Default Value: N

84 EVALUATION_ID Character(2) VARCHAR2(2) NOT NULL Evaluation ID
85 GVT_CREDIT_HOURS Character(3) VARCHAR2(3) NOT NULL Education Credit Hours
86 GVT_CRED_HRS_TYPE Character(1) VARCHAR2(1) NOT NULL Type of education credit hours
C=Continuing Education
O=Other Pay
Q=Quarter Hours
S=Semester Hours
87 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
88 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
89 FACULTY_CODE Character(10) VARCHAR2(10) NOT NULL Faculty Code (Japan HR)

Prompt Table: FACULTY_TBL_JPN

90 FACULTY_DESCR Character(100) VARCHAR2(100) NOT NULL Faculty description
91 SUBFACULTY_CODE Character(10) VARCHAR2(10) NOT NULL Sub Faculty Code(Japan HR)

Prompt Table: SBFCLTY_TBL_JPN

92 SUBFACULTY_DESCR Character(100) VARCHAR2(100) NOT NULL Sub Faculty Name(Description)(Japan HR)
93 MAJOR_CATEGORY Character(1) VARCHAR2(1) NOT NULL Major Category(Japan HR)
H=Humanities
S=Sciences
94 FP_SUBJECT_CD Character(3) VARCHAR2(3) NOT NULL FPS Subject Code

Prompt Table: FPMSUBJECTS

95 EP_APPRAISAL_ID Number(8,0) INTEGER NOT NULL Document ID
96 SCHOOL_TYPE Character(3) VARCHAR2(3) NOT NULL School Type

Prompt Table: SCHOOL_TYPE_TBL

97 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID

Default Value: OPR_DEF_TBL_HR.SETID

Prompt Table: SETID_TBL

98 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPT_TBL
Set Control Field: SETID_DEPT

99 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: BUSUNIT_HR_VW

100 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID
101 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

Prompt Table: LOCATION_TBL
Set Control Field: SETID_LOCATION

102 JPM_ADHOC_DESCR Character(80) VARCHAR2(80) NOT NULL Page Title
103 FP_SKIL_HIR Character(1) VARCHAR2(1) NOT NULL FPS Hiring Requirement
N=No
Y=Yes

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

107 BONUS_AMOUNT_FRA Number(8,2) DECIMAL(7,2) NOT NULL Bonus Amount
108 BONUS_DT_FRA Date(10) DATE Bonus Date
109 JPM_OBSTACLE_1 Character(2) VARCHAR2(2) NOT NULL Obstacle property
FE=Family Member's Education
FH=Family Member's Health
FJ=Family Member's Job
H=Homeowner
N=None
OT=Other
P=Prefer Current Location
110 JPM_AREA_PREF_1 Character(2) VARCHAR2(2) NOT NULL Generic profile item property

Prompt Table: JPM_GEOG_PRF_VW

111 JPM_AREA_PREF_2 Character(2) VARCHAR2(2) NOT NULL Generic profile item property

Prompt Table: JPM_GEOG_PRF_VW

112 JPM_AREA_PREF_3 Character(2) VARCHAR2(2) NOT NULL Generic profile item property

Prompt Table: JPM_GEOG_PRF_VW

113 JPM_CNTRY_PREF_1 Character(3) VARCHAR2(3) NOT NULL Generic profile item property for country preference

Prompt Table: COUNTRY_TBL

114 JPM_CNTRY_PREF_2 Character(3) VARCHAR2(3) NOT NULL Generic profile item property for country preference

Prompt Table: COUNTRY_TBL

115 JPM_CNTRY_PREF_3 Character(3) VARCHAR2(3) NOT NULL Generic profile item property for country preference

Prompt Table: COUNTRY_TBL

116 JPM_LOC_BUNIT_1 Character(5) VARCHAR2(5) NOT NULL generic profile item location BU property

Prompt Table: BUSUNIT_HR_VW

117 JPM_SETID_LOC_1 Character(5) VARCHAR2(5) NOT NULL Generic Location Setid property
118 JPM_LOCATION_1 Character(10) VARCHAR2(10) NOT NULL generic profile item location property

Prompt Table: LOCATION_TBL
Set Control Field: JPM_SETID_LOC_1

119 JPM_LOC_BUNIT_2 Character(5) VARCHAR2(5) NOT NULL generic profile item location BU property

Prompt Table: BUSUNIT_HR_VW

120 JPM_SETID_LOC_2 Character(5) VARCHAR2(5) NOT NULL Generic Location Setid property
121 JPM_LOCATION_2 Character(10) VARCHAR2(10) NOT NULL generic profile item location property

Prompt Table: LOCATION_TBL
Set Control Field: JPM_SETID_LOC_2

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

Default Value: %Date

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