POSN_SRCH_GSS

(SQL View)
 Index Back

Position Data Search for GSS

Search used for Guided Self Service to select a Reports To value. Based on POSITION_SRCH but adds a filter to make sure the position is active EFF_STATUS=A. Uses Department security.

SELECT SEC.OPRID , A.POSITION_NBR ,A.EFFDT ,A.DESCR ,A.POSN_STATUS ,A.BUSINESS_UNIT ,A.DEPTID ,A.JOBCODE ,A.REPORTS_TO 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.EFFDT>=%CurrentDateIn OR A.EFFDT= ( SELECT MAX(D.EFFDT) FROM PS_POSITION_DATA D WHERE A.POSITION_NBR = D.POSITION_NBR AND D.EFFDT<=%CurrentDateIn ) )

  • Related Language Record: POSN_SRCH_LANG

    • # 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 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    4 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    5 POSN_STATUS Character(1) VARCHAR2(1) NOT NULL Position Status
    A=Approved
    P=Proposed
    R=Frozen
    6 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Prompt Table: BUS_UNIT_TBL_HR

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

    Prompt Table: DEPT_TBL

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

    Prompt Table: JOBCODE_TBL

    9 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number