JPM_PITEMS_CV2

(SQL View)
 Index Back

JPM Profile Items Clairvia

Clone of JPM_JP_ITEMS record for Clairvia Fullsync message that allows both active and inactive current jpm items to be picked up

SELECT ITMS.JPM_PROFILE_ID , ITMS.JPM_CAT_TYPE , ITMS.JPM_CAT_ITEM_ID , ITMS.JPM_CAT_ITEM_QUAL , ITMS.JPM_CAT_ITEM_QUAL2 , ITMS.EFFDT , ITMS.EFF_STATUS , ITMS.JPM_ITEM_KEY_ID , ITMS.JPM_JP_ITEM_SRC , ITMS.JPM_SOURCE_ID1 , ITMS.JPM_SOURCE_ID2 JP , ITMS.JPM_SOURCE_ID3 , ITMS.JPM_JP_QUAL_SET , ITMS.JPM_JP_QUAL_SET2 , ITMS.JPM_PARENT_KEY_ID , ITMS.JPM_WF_STATUS , ITMS.COUNTRY , ITMS.JPM_DATE_1 , ITMS.JPM_DATE_2 , ITMS.JPM_EVAL_MTHD , ITMS.JPM_TEXT1325_1 , ITMS.JPM_TEXT1325_2 , ITMS.JPM_TEXT254_1 , ITMS.JPM_TEXT254_2 , ITMS.JPM_TEXT254_3 , ITMS.JPM_TEXT254_4 , ITMS.JPM_YN_1 , ITMS.JPM_YN_2 , ITMS.JPM_YN_3 , ITMS.JPM_YN_4 , ITMS.JPM_YN_5 , ITMS.RATING_MODEL , ITMS.APS_HEDUC_CD_AUS , ITMS.AVERAGE_GRADE , ITMS.BONUS_AMOUNT_FRA , ITMS.BONUS_DT_FRA , ITMS.BUSINESS_UNIT , ITMS.DEPTID , ITMS.EDUC_LVL_AUS , ITMS.EP_APPRAISAL_ID , ITMS.EVALUATION_ID , ITMS.FACULTY_CODE , ITMS.FACULTY_DESCR , ITMS.FP_DEGR_REQUIRED , ITMS.FP_SKIL_HIR , ITMS.FP_SKIL_PRM , ITMS.FP_SKIL_TEN , ITMS.FP_SUBJECT_CD , ITMS.GVT_CRED_HRS_TYPE , ITMS.GVT_CREDIT_HOURS , ITMS.IPE_SW , ITMS.JPM_ADHOC_DESCR , ITMS.JPM_AREA_PREF_1 , ITMS.JPM_AREA_PREF_2 , ITMS.JPM_AREA_PREF_3 , ITMS.JPM_CNTRY_PREF_1 , ITMS.JPM_CNTRY_PREF_2 , ITMS.JPM_CNTRY_PREF_3 , ITMS.JPM_DATE_3 , ITMS.JPM_DATE_4 , ITMS.JPM_DATE_5 , ITMS.JPM_DATE_6 , ITMS.JPM_DECIMAL_1 , ITMS.JPM_DECIMAL_2 , ITMS.JPM_IMPORTANCE , ITMS.JPM_INTEGER_1 , ITMS.JPM_INTEGER_2 , ITMS.JPM_INTEREST_LEVEL , ITMS.JPM_LOC_BUNIT_1 , ITMS.JPM_LOC_BUNIT_2 , ITMS.JPM_LOCATION_1 , ITMS.JPM_LOCATION_2 , ITMS.JPM_MANDATORY , ITMS.JPM_MINOR_CD , ITMS.JPM_OBSTACLE_1 , ITMS.JPM_PCT_1 , ITMS.JPM_PCT_2 , ITMS.JPM_PERSON_ID_1 , ITMS.JPM_RATING1 , ITMS.JPM_RATING2 , ITMS.JPM_RATING3 , ITMS.JPM_SETID_LOC_1 , ITMS.JPM_SETID_LOC_2 , ITMS.JPM_TEXT254_5 , ITMS.JPM_VERIFY_METHOD , ITMS.LASTUPDDTTM , ITMS.LASTUPDOPRID , ITMS.LOCATION , ITMS.MAJOR_CATEGORY , ITMS.MAJOR_CODE , ITMS.MAJOR_DESCR , ITMS.MINOR_DESCR , ITMS.NVQ_STATUS , ITMS.PRACTIC_GRADE_GER , ITMS.SCHOOL_CODE , ITMS.SCHOOL_DESCR , ITMS.SCHOOL_TYPE , ITMS.SETID_DEPT , ITMS.SETID_LOCATION , ITMS.STATE , ITMS.SUBFACULTY_CODE , ITMS.SUBFACULTY_DESCR , ITMS.THEORY_GRADE_GER FROM PS_JPM_JP_ITEMS ITMS , PS_JPM_PROFILE_I PRF , PS_JPM_JP_PERTYP_I PRIM , PS_INTEGR_CAT_TYPE ICAT WHERE %EffdtCheck(JPM_JP_ITEMS,ITMS,%CurrentDateIn) AND ITMS.JPM_PROFILE_ID = PRF.JPM_PROFILE_ID AND PRF.JPM_JP_PRFL_STATUS = 'A' AND PRIM.JPM_PRI_PERS_TYPE = PRF.JPM_JP_TYPE AND EXISTS ( SELECT 'X' FROM PS_PER_ORG_ASG_INT ASG WHERE ASG.EMPLID = PRF.EMPLID AND ASG.INT_APP_TYPE = 'SCH' AND ASG.INTEGR_PROD_CD = 'CV' AND ASG.ENROLLED_FLAG = 'Y') AND ICAT.INT_APP_TYPE = 'SCH' AND ICAT.INTEGR_PROD_CD = 'CV' AND ICAT.JPM_CAT_TYPE = ITMS.JPM_CAT_TYPE

  • Parent record: JPM_PROFILE

    • # 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_ITMS_VW

    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: %EDITTABLE3

    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

    Prompt Table: JPM_SYND_SRC_VW

    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

    Default Value: N

    17 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
    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
    28 JPM_YN_2 Character(1) VARCHAR2(1) NOT NULL Generic Yes No property for profile items
    N=No
    Y=Yes
    29 JPM_YN_3 Character(1) VARCHAR2(1) NOT NULL Generic Yes No property for profile items
    N=No
    Y=Yes
    30 JPM_YN_4 Character(1) VARCHAR2(1) NOT NULL Generic Yes No property for profile items
    N=No
    Y=Yes
    31 JPM_YN_5 Character(1) VARCHAR2(1) NOT NULL Generic Yes No property for profile items
    N=No
    Y=Yes
    32 RATING_MODEL Character(4) VARCHAR2(4) NOT NULL Rating Model
    33 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
    34 AVERAGE_GRADE Character(5) VARCHAR2(5) NOT NULL Average Grade
    35 BONUS_AMOUNT_FRA Number(8,2) DECIMAL(7,2) NOT NULL Bonus Amount
    36 BONUS_DT_FRA Date(10) DATE Bonus Date
    37 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    38 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    39 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
    40 EP_APPRAISAL_ID Number(8,0) INTEGER NOT NULL Document ID
    41 EVALUATION_ID Character(2) VARCHAR2(2) NOT NULL Evaluation ID
    42 FACULTY_CODE Character(10) VARCHAR2(10) NOT NULL Faculty Code (Japan HR)
    43 FACULTY_DESCR Character(100) VARCHAR2(100) NOT NULL Faculty description
    44 FP_DEGR_REQUIRED Character(1) VARCHAR2(1) NOT NULL FPS Degree
    N=No
    Y=Yes
    45 FP_SKIL_HIR Character(1) VARCHAR2(1) NOT NULL FPS Hiring Requirement
    N=No
    Y=Yes
    46 FP_SKIL_PRM Character(1) VARCHAR2(1) NOT NULL FPS Promotion Requirement
    N=No
    Y=Yes
    47 FP_SKIL_TEN Character(1) VARCHAR2(1) NOT NULL FPS Tenure Requirement
    N=No
    Y=Yes
    48 FP_SUBJECT_CD Character(3) VARCHAR2(3) NOT NULL FPS Subject Code
    49 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
    50 GVT_CREDIT_HOURS Character(3) VARCHAR2(3) NOT NULL Education Credit Hours
    51 IPE_SW Character(1) VARCHAR2(1) NOT NULL Intern Professional Education
    52 JPM_ADHOC_DESCR Character(80) VARCHAR2(80) NOT NULL Page Title
    53 JPM_AREA_PREF_1 Character(2) VARCHAR2(2) NOT NULL Generic profile item property
    54 JPM_AREA_PREF_2 Character(2) VARCHAR2(2) NOT NULL Generic profile item property
    55 JPM_AREA_PREF_3 Character(2) VARCHAR2(2) NOT NULL Generic profile item property
    56 JPM_CNTRY_PREF_1 Character(3) VARCHAR2(3) NOT NULL Generic profile item property for country preference
    57 JPM_CNTRY_PREF_2 Character(3) VARCHAR2(3) NOT NULL Generic profile item property for country preference
    58 JPM_CNTRY_PREF_3 Character(3) VARCHAR2(3) NOT NULL Generic profile item property for country preference
    59 JPM_DATE_3 Date(10) DATE generic profile item date property
    60 JPM_DATE_4 Date(10) DATE generic profile item date property
    61 JPM_DATE_5 Date(10) DATE generic profile item date property
    62 JPM_DATE_6 Date(10) DATE generic profile item date property
    63 JPM_DECIMAL_1 Number(10,2) DECIMAL(9,2) NOT NULL generic profile item decimal property
    64 JPM_DECIMAL_2 Number(10,2) DECIMAL(9,2) NOT NULL generic profile item decimal property
    65 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
    66 JPM_INTEGER_1 Number(8,0) INTEGER NOT NULL generic profile item integer property
    67 JPM_INTEGER_2 Number(8,0) INTEGER NOT NULL generic profile item integer property
    68 JPM_INTEREST_LEVEL Character(1) VARCHAR2(1) NOT NULL Profile item property stating intertest level of the employee in the content item
    69 JPM_LOC_BUNIT_1 Character(5) VARCHAR2(5) NOT NULL generic profile item location BU property
    70 JPM_LOC_BUNIT_2 Character(5) VARCHAR2(5) NOT NULL generic profile item location BU property
    71 JPM_LOCATION_1 Character(10) VARCHAR2(10) NOT NULL generic profile item location property
    72 JPM_LOCATION_2 Character(10) VARCHAR2(10) NOT NULL generic profile item location property
    73 JPM_MANDATORY Character(1) VARCHAR2(1) NOT NULL jpm
    N=No
    Y=Yes
    74 JPM_MINOR_CD Character(10) VARCHAR2(10) NOT NULL Minor for degree or education content type
    75 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
    76 JPM_PCT_1 Number(3,0) SMALLINT NOT NULL generic percentage property
    77 JPM_PCT_2 Number(3,0) SMALLINT NOT NULL generic percentage property
    78 JPM_PERSON_ID_1 Character(11) VARCHAR2(11) NOT NULL Person id property for profile items
    79 JPM_RATING1 Character(1) VARCHAR2(1) NOT NULL Generic rating field generally prompted by RATING_MODEL
    80 JPM_RATING2 Character(1) VARCHAR2(1) NOT NULL Generic rating field generally prompted by RATING_MODEL
    81 JPM_RATING3 Character(1) VARCHAR2(1) NOT NULL Generic rating field generally prompted by RATING_MODEL
    82 JPM_SETID_LOC_1 Character(5) VARCHAR2(5) NOT NULL Generic Location Setid property
    83 JPM_SETID_LOC_2 Character(5) VARCHAR2(5) NOT NULL Generic Location Setid property
    84 JPM_TEXT254_5 Character(254) VARCHAR2(254) NOT NULL Generic Text property for profile items
    85 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
    86 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.
    87 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.
    88 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    89 MAJOR_CATEGORY Character(1) VARCHAR2(1) NOT NULL Major Category(Japan HR)
    H=Humanities
    S=Sciences
    90 MAJOR_CODE Character(10) VARCHAR2(10) NOT NULL Major Code
    91 MAJOR_DESCR Character(100) VARCHAR2(100) NOT NULL Description of the major
    92 MINOR_DESCR Character(100) VARCHAR2(100) NOT NULL Description of the minor
    93 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
    94 PRACTIC_GRADE_GER Character(4) VARCHAR2(4) NOT NULL Track Practical Grade applicable in Germany.
    95 SCHOOL_CODE Character(10) VARCHAR2(10) NOT NULL School Code
    96 SCHOOL_DESCR Character(100) VARCHAR2(100) NOT NULL Name of the school
    97 SCHOOL_TYPE Character(3) VARCHAR2(3) NOT NULL School Type
    98 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
    99 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID
    100 STATE Character(6) VARCHAR2(6) NOT NULL State
    101 SUBFACULTY_CODE Character(10) VARCHAR2(10) NOT NULL Sub Faculty Code(Japan HR)
    102 SUBFACULTY_DESCR Character(100) VARCHAR2(100) NOT NULL Sub Faculty Name(Description)(Japan HR)
    103 THEORY_GRADE_GER Character(4) VARCHAR2(4) NOT NULL Track Theoretical Grade applicable in Germany.