create or replace function CH22_dh_utilization(xslot in number) return varchar2 is wnenrollment number := 0; wstatus varchar2(15); wthreshold date; wutilization date; begin select stop_date into wthreshold from ch22_duty_hour where id = xslot; select count(*) into wnenrollment from ch22_dh_slot where slot_id = xslot; if (wnenrollment = 0) then wstatus:= 'NONE'; else select max(estim_stop) into wutilization from ch22_dh_slot where slot_id = xslot; if (wutilization < wthreshold) then wstatus:= 'AVAILABLE'; else wstatus:= 'OVERBOOKED'; end if; end if; return wstatus; end CH22_dh_utilization;