HR_CP_MOBIL2_VW

(SQL View)
 Index Back

CP IDP Mobility View

Data used for Career Planning Individual Development Plan report.

SELECT DISTINCT B.EMPLID , C.JPM_CAT_TYPE , C.JPM_YN_1 ,' ' , C.JPM_YN_2 ,' ' , C.JPM_YN_3 ,' ' , C.JPM_LOC_BUNIT_1 , C.JPM_LOC_BUNIT_2 , C.JPM_LOCATION_1 , C.JPM_LOCATION_2 , ' ' ,' ' , C.JPM_OBSTACLE_1 ,' ' , C.JPM_CNTRY_PREF_1 , C.JPM_CNTRY_PREF_2 , C.JPM_CNTRY_PREF_3 , ' ' , ' ' , ' ' , C.JPM_AREA_PREF_1 , C.JPM_AREA_PREF_2 , C.JPM_AREA_PREF_3 , ' ' , ' ' , ' ' FROM PS_HR_CP_CAT_VW A , PS_JPM_PROFILE B , PS_JPM_JP_ITEMS C WHERE B.JPM_PROFILE_ID = C.JPM_PROFILE_ID AND A.JPM_PROFILE_ID = B.JPM_PROFILE_ID AND A.EMPLID = B.EMPLID AND A.JPM_CAT_TYPE = C.JPM_CAT_TYPE AND A.JPM_CAT_TYPE IN ('LOCATION','LOCATN_PREF','GEOG_PREF','INTL_PREF','TRAVEL') AND C.EFF_STATUS = 'A' AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_JPM_JP_ITEMS C1 WHERE C.JPM_PROFILE_ID = C1.JPM_PROFILE_ID AND C.JPM_CAT_TYPE = C1.JPM_CAT_TYPE AND C.JPM_CAT_ITEM_ID = C1.JPM_CAT_ITEM_ID AND C.JPM_CAT_ITEM_QUAL = C1.JPM_CAT_ITEM_QUAL AND C.JPM_CAT_ITEM_QUAL2 = C1.JPM_CAT_ITEM_QUAL2 AND C1.EFFDT <= %CurrentDateIn)

  • Parent record: PERSON

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Prompt Table: PERSON

    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_YN_1 Character(1) VARCHAR2(1) NOT NULL Generic Yes No property for profile items
    N=No
    Y=Yes
    4 DESCRSHORT1 Character(10) VARCHAR2(10) NOT NULL Descr Short One
    5 JPM_YN_2 Character(1) VARCHAR2(1) NOT NULL Generic Yes No property for profile items
    N=No
    Y=Yes
    6 DESCRSHORT2 Character(10) VARCHAR2(10) NOT NULL Descr Short 2
    7 JPM_YN_3 Character(1) VARCHAR2(1) NOT NULL Generic Yes No property for profile items
    N=No
    Y=Yes
    8 DESCRSHORT3 Character(10) VARCHAR2(10) NOT NULL Descr Short
    9 JPM_LOC_BUNIT_1 Character(5) VARCHAR2(5) NOT NULL generic profile item location BU property

    Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT

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

    Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT

    11 JPM_LOCATION_1 Character(10) VARCHAR2(10) NOT NULL generic profile item location property
    12 JPM_LOCATION_2 Character(10) VARCHAR2(10) NOT NULL generic profile item location property
    13 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    14 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    15 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
    16 LONGNAME Character(30) VARCHAR2(30) NOT NULL Long Name
    17 JPM_CNTRY_PREF_1 Character(3) VARCHAR2(3) NOT NULL Generic profile item property for country preference
    18 JPM_CNTRY_PREF_2 Character(3) VARCHAR2(3) NOT NULL Generic profile item property for country preference
    19 JPM_CNTRY_PREF_3 Character(3) VARCHAR2(3) NOT NULL Generic profile item property for country preference
    20 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
    21 DESCR7 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
    22 DESCR8 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
    23 JPM_AREA_PREF_1 Character(2) VARCHAR2(2) NOT NULL Generic profile item property
    24 JPM_AREA_PREF_2 Character(2) VARCHAR2(2) NOT NULL Generic profile item property
    25 JPM_AREA_PREF_3 Character(2) VARCHAR2(2) NOT NULL Generic profile item property
    26 DESCR4 Character(30) VARCHAR2(30) NOT NULL Descr 4
    27 DESCR5 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
    28 DESCR6 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.