KEY_POSN_SRCH

(SQL View)
 Index Back

Search Vw-Succsn Plan-Key Posn

KEY_POSN_SRCH is a view which is used as the search record for key positions in the Succession Planning menu and Succession Plan trees. Only positions which have been designated as key positions will be retrieved by this view. KEY_POSN_SRCH also includes departmental security on positions as defined in the departmental security tree. Only positions which are BOTH key and in departments the user is cleared to see will appear for a particular user.

SELECT SEC.OPRID , A.POSITION_NBR , A.DESCR , A.POSN_STATUS , A.BUSINESS_UNIT , A.DEPTID , A.LOCATION , A.COMPANY , A.JOBCODE FROM PS_POSITION_DATA A , PS_DEPT_SEC_SRCH SEC WHERE SEC.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC SC WHERE SC.SETCNTRLVALUE = A.BUSINESS_UNIT AND SC.RECNAME = 'DEPT_TBL') AND A.DEPTID = SEC.DEPTID AND A.EFF_STATUS = 'A' AND A.KEY_POSITION = 'Y' AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_POSITION_DATA A1 WHERE A1.POSITION_NBR = A.POSITION_NBR)

  • Related Language Record: KEY_POSN_S_LNG

    • # 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 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
    3 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    4 POSN_STATUS Character(1) VARCHAR2(1) NOT NULL Position Status
    A=Approved
    P=Proposed
    R=Frozen
    5 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT

    Prompt Table: BUS_UNIT_TBL_HR

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

    Prompt Table: DEPT_TBL

    7 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

    Prompt Table: LOCATION_TBL

    8 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

    Prompt Table: COMPANY_TBL

    9 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

    Prompt Table: JOBCODE_TBL