-- function
create or replace function employee_profit(emp_id in NUMBER)
return number is
profit number(10);

begin
		--if emp_id does not exist in sl.EMPLOYEE_ID , profit := 0 ; return profit ;
		--else
		select sum(sp.PRICE - nvl(sp."BUYING PRICE",0)) into profit
		from SALES sl, STOCK stk, PRODUCT_SPECIFICATION sp
		where sl.PRODUCT_IMEI_NO = stk.STOCK_IMEI and stk.STOCK_PRODUCT_ID = sp.PRODUCT_ID
		group by sl.EMPLOYEE_ID
		having sl.EMPLOYEE_ID = emp_id ;
	  return profit ;

exception
		when no_data_found then
				profit := 0 ;
				return profit;
end;


select EMPLOYEE_ID, employee_profit(EMPLOYEE_ID)
from EMPLOYEES
where username like '%sales%' ;