JPM_POSPRF_SRCH

(SQL View)
 Index Back

Position profile search view

Search record selects those profiles that have a position PIO defined. Used by Succession Planning.

SELECT SEC.OPRID , P.JPM_PROFILE_ID , P.JPM_JP_TYPE , POS.POSITION_NBR , POS.DESCR , POS.BUSINESS_UNIT , POS.DEPTID FROM PS_JPM_PROFILE P , PS_JPM_JP_X_RLAT R , PS_DEPT_SEC_SRCH SEC , PS_POSITION_DATA POS , PS_JPM_SRCH_CFG JPCF WHERE P.JPM_JP_PRFL_STATUS = 'A' AND R.JPM_JP_XRLAT_TYPE = 'PIDO' AND R.JPM_RLAT_NAME = 'POSITION' AND R.JPM_PROFILE_ID = P.JPM_PROFILE_ID AND %EffdtCheck(JPM_JP_X_RLAT R1, R, %Currentdatein) AND %Substring(R.JPM_RLAT_KEY1,1,8) = POS.POSITION_NBR AND POS.KEY_POSITION = 'Y' AND SEC.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC SC WHERE SC.SETCNTRLVALUE = POS.BUSINESS_UNIT AND SC.RECNAME = 'DEPT_TBL') AND POS.DEPTID = SEC.DEPTID AND (POS.EFFDT>=%CurrentDateIn OR POS.EFFDT= ( SELECT MAX(P1.EFFDT) FROM PS_POSITION_DATA P1 WHERE P1.POSITION_NBR = POS.POSITION_NBR AND P1.EFFDT<=%CurrentDateIn)) AND JPCF.JPM_SOURCE_JP = P.JPM_JP_TYPE AND JPCF.OBJECTOWNERID='HHR' AND JPM_SRCH_SUB_ID='SUCC'

  • Related Language Record: JPM_POSPF_LSRCH

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    2 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

    3 JPM_JP_TYPE Character(12) VARCHAR2(12) NOT NULL Profile Type name for a JPM Profile Type

    Prompt Table: JPM_JP_TYPES

    4 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
    5 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    6 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    7 DEPTID Character(10) VARCHAR2(10) NOT NULL Department