|
SELECT A.JPM_PROFILE_ID , A.JPM_CAT_TYPE , A.JPM_CAT_ITEM_ID , B.JPM_JP_TYPE , A.JPM_ITEM_KEY_ID , A.EFFDT , A.EFF_STATUS , 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_SRCH_ITM_SR,A) FROM PS_JPM_JP_ITEMS A , PS_JPM_PROFILE B WHERE A.JPM_PROFILE_ID = B.JPM_PROFILE_ID AND 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 A.JPM_ITEM_KEY_ID = ( SELECT MAX(A4.JPM_ITEM_KEY_ID) FROM PS_JPM_JP_ITEMS A4 WHERE A4.JPM_PROFILE_ID = A.JPM_PROFILE_ID AND A4.JPM_CAT_TYPE = A.JPM_CAT_TYPE AND A4.JPM_CAT_ITEM_ID = A.JPM_CAT_ITEM_ID AND A4.JPM_CAT_ITEM_QUAL = A.JPM_CAT_ITEM_QUAL AND A4.EFFDT = A.EFFDT) AND A.EFF_STATUS = 'A'
|
| # |
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_JP_TYPE |
Character(12) |
VARCHAR2(12) NOT NULL |
Profile Type name for a JPM Profile Type
|
| 5 |
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.
|
| 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_ITEM_SRC |
Character(4) |
VARCHAR2(4) NOT NULL |
Indicates the source of a JPM profile item
CGRP=Catalog Group
ELM=Learning Management
EP=ePerformance
PROF=Another Profile
|
| 9 |
JPM_SOURCE_ID1 |
Character(12) |
VARCHAR2(12) NOT NULL |
JPM Profile Item Source ID1
|
| 10 |
JPM_SOURCE_ID2 |
Character(12) |
VARCHAR2(12) NOT NULL |
JPM Profile Item Source ID3
|
| 11 |
JPM_SOURCE_ID3 |
Character(12) |
VARCHAR2(12) NOT NULL |
JPM Profile Item Source ID3
|
| 12 |
JPM_JP_QUAL_SET |
Character(12) |
VARCHAR2(12) NOT NULL |
1st Instance Qualifier Set Name for a JPM Profile Item
|
| 13 |
JPM_JP_QUAL_SET2 |
Character(12) |
VARCHAR2(12) NOT NULL |
2nd Instance Qualifier Set Name for a JPM Profile Item
|
| 14 |
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.
|
| 15 |
JPM_WF_STATUS |
Character(2) |
VARCHAR2(2) NOT NULL |
JPM Workflow Status field
A=Approved
D=Denied
N=New
P=Pending
S=Saved
|
| 16 |
JPM_MANDATORY |
Character(1) |
VARCHAR2(1) NOT NULL |
jpm
N=No
Y=Yes
|
| 17 |
RATING_MODEL |
Character(4) |
VARCHAR2(4) NOT NULL |
Rating Model
Prompt Table:
RATING_MDL_TBL
|
| 18 |
JPM_RATING1 |
Character(1) |
VARCHAR2(1) NOT NULL |
Rating 1
Prompt Table:
REVW_RATING_TBL
|
| 19 |
JPM_RATING2 |
Character(1) |
VARCHAR2(1) NOT NULL |
Rating 2
Prompt Table:
JPM_RATING_VW1
|
| 20 |
JPM_RATING3 |
Character(1) |
VARCHAR2(1) NOT NULL |
Rating 3
Prompt Table:
JPM_RATING_VW2
|
| 21 |
NVQ_STATUS |
Character(1) |
VARCHAR2(1) NOT NULL |
NVQ Status
A=Achieved
C=Cancelled
E=Enrolled
P=Previous Achievement
|
| 22 |
JPM_INTEREST_LEVEL |
Character(1) |
VARCHAR2(1) NOT NULL |
Interest Level
Prompt Table:
INT_RATING_VW
|
| 23 |
JPM_YN_1 |
Character(1) |
VARCHAR2(1) NOT NULL |
User Yes/No (1)
N=No
Y=Yes
|
| 24 |
JPM_YN_2 |
Character(1) |
VARCHAR2(1) NOT NULL |
User Yes/No (2)
N=No
Y=Yes
|
| 25 |
JPM_YN_3 |
Character(1) |
VARCHAR2(1) NOT NULL |
User Yes/No (3)
N=No
Y=Yes
|
| 26 |
JPM_YN_4 |
Character(1) |
VARCHAR2(1) NOT NULL |
User Yes/No (4)
N=No
Y=Yes
|
| 27 |
JPM_YN_5 |
Character(1) |
VARCHAR2(1) NOT NULL |
User Yes/No (5)
N=No
Y=Yes
|
| 28 |
COUNTRY |
Character(3) |
VARCHAR2(3) NOT NULL |
Country
Prompt Table:
COUNTRY_TBL
|
| 29 |
STATE |
Character(6) |
VARCHAR2(6) NOT NULL |
State
Prompt Table:
HCR_STONLY_I
|
| 30 |
SCHOOL_CODE |
Character(10) |
VARCHAR2(10) NOT NULL |
School Code
Prompt Table:
SCHOOL_TBL
|
| 31 |
MAJOR_CODE |
Character(10) |
VARCHAR2(10) NOT NULL |
Major Code
Prompt Table:
MAJOR_TBL
|
| 32 |
JPM_MINOR_CD |
Character(10) |
VARCHAR2(10) NOT NULL |
Minor Code
Prompt Table:
JPM_MAJOR_VW
|
| 33 |
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
|
| 34 |
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
|
| 35 |
FACULTY_CODE |
Character(10) |
VARCHAR2(10) NOT NULL |
Faculty Code (Japan HR)
Prompt Table:
FACULTY_TBL_JPN
|
| 36 |
SUBFACULTY_CODE |
Character(10) |
VARCHAR2(10) NOT NULL |
Sub Faculty Code(Japan HR)
Prompt Table:
SBFCLTY_TBL_JPN
|
| 37 |
MAJOR_CATEGORY |
Character(1) |
VARCHAR2(1) NOT NULL |
Major Category(Japan HR)
H=Humanities
S=Sciences
|
| 38 |
FP_SUBJECT_CD |
Character(3) |
VARCHAR2(3) NOT NULL |
Subject
Prompt Table:
FPMSUBJECTS
|
| 39 |
FP_SKIL_HIR |
Character(1) |
VARCHAR2(1) NOT NULL |
Hiring Requirements
N=No
Y=Yes
|
| 40 |
FP_SKIL_PRM |
Character(1) |
VARCHAR2(1) NOT NULL |
Promotion Requirements
N=No
Y=Yes
|
| 41 |
FP_SKIL_TEN |
Character(1) |
VARCHAR2(1) NOT NULL |
Tenure Requirements
N=No
Y=Yes
|
| 42 |
FP_DEGR_REQUIRED |
Character(1) |
VARCHAR2(1) NOT NULL |
Required ?
N=No
Y=Yes
|