--------------------- -- HRPC PSMS Views -- --------------------- Create or Replace View ViewAuthorized As select D.DEPT_ID, D.Name as Department, sum(AF.AUTHORIZED_FILL) as Authorized from Departments D, Authorized_Fill AF where D.DEPT_ID = AF.DEPT_ID group by D.DEPT_ID, D.Name ; Create or Replace View ViewFilled As select D.DEPT_ID, D.Name as Department, sum(U.PCT)/100 as Filled from Departments D, Urc_Percentages U where D.DEPT_ID = U.DEPT_ID group by D.DEPT_ID, D.Name ; Create or Replace View ViewStaffingReport As select D.DEPT_ID, D.NAME, A.Authorized, F.Filled from Departments D, ViewAuthorized A, ViewFilled F where D.DEPT_ID = A.DEPT_ID(+) and D.DEPT_ID = F.DEPT_ID(+) ; Create or Replace View ViewStaffByDivision As select staff_id, division, sum(pct) as TotalPct from (select s.staff_id, s.last_name, s.first_name, u.URC_NUMBER, u.PCT, dept.NAME as Department, div.NAME as Division from staff s, URC_PERCENTAGES u, DEPARTMENTS dept, DIVISIONS div where s.STAFF_ID = u.STAFF_ID and u.DEPT_ID = dept.DEPT_ID and dept.DIV_NUMBER = div.DIV_NUMBER) group by staff_id, division ; Create or Replace View ViewStaffDeptPct As select S.STAFF_ID, U.DEPT_ID, sum(U.PCT) as MaxPct from Staff S, URC_PERCENTAGES U where S.STAFF_ID = U.STAFF_ID group by S.Staff_id, U.DEPT_ID ; Create or Replace View ViewPositionsByDepartment As select P.Position_Code, P.Position_Name, SDP.MaxPct, SDP.DEPT_ID from ViewStaffDeptPct SDP, Positions P, Staff S where SDP.STAFF_ID = S.STAFF_ID and S.POSITION_CODE = P.POSITION_CODE ; CREATE OR REPLACE VIEW ViewAuthorizedByDepartment ( DEPT_ID, POSITION_CODE, POSITION_NAME, AUTHORIZED_FILL ) AS select D.DEPT_ID, AF.POSITION_CODE, P.POSITION_NAME, AF.AUTHORIZED_FILL from Departments D, Authorized_Fill AF, Positions P where D.DEPT_ID = AF.DEPT_ID and AF.POSITION_CODE = P.POSITION_CODE ; Create or Replace View ViewStaffingReportDetail As select * from (select DEPT_ID, POSITION_CODE, AUTHORIZED_FILL, 'Auth' as Target from VIEWAUTHORIZEDBYDEPARTMENT) union (select DEPT_ID, POSITION_CODE, MAXPCT as AUTHORIZED_FILL, 'Filled' as Target from ViewPositionsByDepartment) ;