create or replace procedure P_CLEANUP_MANAGERS is CURSOR cur_managers is select * from manager; current_manager MANAGER%ROWTYPE; nAvgRevenue NUMBER(14,2); nCurrRevenue NUMBER(14,2); begin OPEN cur_managers; LOOP FETCH cur_managers into current_manager; EXIT when cur_managers%NOTFOUND; select avg(sum(product_price * product_qty)) into nAvgRevenue from SALES_ORDER_LINE so inner join SALES_ORDER o on (so.sales_order_id = o.sales_order_id) group by o.manager_id having o.manager_id in (select manager_id from manager where office_id = current_manager.office_id); select sum(product_price * product_qty) into nCurrRevenue from SALES_ORDER_LINE so inner join SALES_ORDER o on (so.sales_order_id = o.sales_order_id) where o.manager_id = current_manager.manager_id; if (nCurrRevenue*1.1 < nAvgRevenue) then update MANAGER set IS_ACTIVE = 0 where manager_id = current_manager.manager_id; end if; if (nCurrRevenue > nAvgRevenue) then update MANAGER set SALARY = SALARY*1.1 where manager_id = current_manager.manager_id; end if; END LOOP; end P_CLEANUP_MANAGERS; /