POSN_VACANT_LNG

(SQL View)
 Index Back

Vacant Budgeted Posns by Dept

POSN_VACANT is a view which retrieves a list of all currently vacant, active, budgeted positions by department.

SELECT D.SETID ,A.DEPTID ,A.POSITION_NBR ,L.LANGUAGE_CD ,L.DESCR ,L.DESCRLONG FROM PS_POSITION_DATA A , PS_DEPT_TBL D , PS_POSN_DATA_LANG L WHERE D.SETID=( SELECT Z.SETID FROM PS_SET_CNTRL_REC Z WHERE Z.SETCNTRLVALUE=A.BUSINESS_UNIT AND Z.RECNAME='DEPT_TBL') AND D.DEPTID=A.DEPTID AND D.EFFDT = ( SELECT MAX(E.EFFDT) FROM PS_DEPT_TBL E WHERE E.DEPTID = D.DEPTID AND E.SETID=D.SETID AND E.EFFDT<=%CurrentDateIn) AND A.EFF_STATUS = 'A' AND A.BUDGETED_POSN = 'Y' AND A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_POSITION_DATA B WHERE A.POSITION_NBR = B.POSITION_NBR AND B.EFFDT <= %CurrentDateIn) AND A.MAX_HEAD_COUNT > ( SELECT COUNT(*) FROM PS_POSN_INCUMBENT C WHERE A.POSITION_NBR = C.POSITION_NBR) AND A.POSITION_NBR = L.POSITION_NBR

  • Related Language Record for POSN_VACANT

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
    2 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    3 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
    4 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    5 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    6 DESCRLONG Long Character CLOB Long Description