POSN_VACANT

(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 ,A.DESCR ,A.POSN_STATUS ,A.STATUS_DT ,A.BUSINESS_UNIT ,A.JOBCODE ,A.COMPANY ,A.LOCATION ,A.FULL_PART_TIME ,A.REG_TEMP ,A.CONFIDENTIAL_POSN ,A.REPORTS_TO ,A.MAX_HEAD_COUNT ,A.DESCRLONG ,A.MANAGER_LEVEL ,A.SAL_ADMIN_PLAN ,A.STEP ,A.GRADE FROM PS_POSITION_DATA A , PS_DEPT_TBL D 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)

  • Related Language Record: POSN_VACANT_LNG

    • # 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 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 STATUS_DT Date(10) DATE Status Date
    7 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    8 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    9 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
    10 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    11 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
    D=On Demand
    F=Full-Time
    P=Part-Time
    12 REG_TEMP Character(1) VARCHAR2(1) NOT NULL Regular/Temporary
    R=Regular
    T=Temporary
    13 CONFIDENTIAL_POSN Character(1) VARCHAR2(1) NOT NULL Confidential Position
    14 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number
    15 MAX_HEAD_COUNT Number(4,0) SMALLINT NOT NULL Max Head Count
    16 DESCRLONG Long Character CLOB Long Description
    17 MANAGER_LEVEL Character(2) VARCHAR2(2) NOT NULL Manager Level
    0=Chief Operating Officer
    1=Director
    2=Senior Officer
    3=Vice President
    4=Senior Manager
    5=Mid-Level Manager
    6=First-Line Manager
    7=Supervisor
    8=All Other Positions
    9=Non-Manager
    18 SAL_ADMIN_PLAN Character(4) VARCHAR2(4) NOT NULL Salary Administration Plan
    19 STEP Number(2,0) SMALLINT NOT NULL Step
    20 GRADE Character(3) VARCHAR2(3) NOT NULL Salary Grade