DEPT_POSN_COUNT (SQL View)	Index Back
Active Position Count by Dept DEPT_POSN_COUNT is a view which counts approved and actual headcount by department based on the positions in that department.

DEPT_POSN_COUNT

(SQL View)

Active Position Count by Dept

DEPT_POSN_COUNT is a view which counts approved and actual headcount by department based on the positions in that department.

SELECT C.SETID ,A.DEPTID ,SUM(A.MAX_HEAD_COUNT) ,COUNT(*) FROM PS_POSITION_DATA A , PS_DEPT_TBL C WHERE C.SETID=( SELECT Z.SETID FROM PS_SET_CNTRL_REC Z WHERE Z.SETCNTRLVALUE=A.BUSINESS_UNIT AND Z.RECNAME='DEPT_TBL') AND C.DEPTID=A.DEPTID AND A.EFF_STATUS ='A' AND A.POSN_STATUS = 'A' AND A.BUDGETED_POSN = 'Y' AND A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_POSITION_DATA B WHERE B.POSITION_NBR = A.POSITION_NBR AND B.EFFDT <= %CurrentDateIn) AND C.EFFDT = ( SELECT MAX(D.EFFDT) FROM PS_DEPT_TBL D WHERE C.SETID=D.SETID AND C.DEPTID = D.DEPTID AND D.EFFDT <= %CurrentDateIn) GROUP BY C.SETID,A.DEPTID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
Prompt Table: SETID_TBL

2 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
Prompt Table: DEPT_TBL

3 APPRVD_HEADCOUNT Number(5,0) INTEGER NOT NULL Approved Head Count

4 TOTAL_COUNT Number(7,0) INTEGER NOT NULL Total Count

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID Prompt Table: SETID_TBL
2	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department Prompt Table: DEPT_TBL
3	APPRVD_HEADCOUNT	Number(5,0)	INTEGER NOT NULL	Approved Head Count
4	TOTAL_COUNT	Number(7,0)	INTEGER NOT NULL	Total Count