====== pears.GetVacancyCascadeTime ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."GetVacancyCascadeTime" IS 
{create function GetVacancyCascadeTime 

/* Application Maintained Function / Procedure - DO NOT EDIT*/

(in compid char(20),in vacid char(20),in entrytime timestamp,in starttime timestamp,in casctime timestamp,in requestlevel smallint)
returns timestamp
begin
  declare rv timestamp;
  declare frm char(1);
  declare mins double;
  declare fnc char(100);
  set rv=null;
  -- first look for vacancy level vacancy rule
  select first hoursfrom,isnull(cascadehours,0.0)*60.0,customfunction into frm,mins,fnc from cascaderule where vacancyid = vacid and cascadelevel = requestlevel and vacancyrule = 1;
  -- if that did not find anything , then check for a company level vacancy rule
  if frm is null then
    select first hoursfrom,isnull(cascadehours,0.0)*60.0,customfunction into frm,mins,fnc from cascaderule where companyid = compid and vacancyid is null and cascadelevel = requestlevel and vacancyrule = 1
  end if;
  case frm
  when 'E' then set rv=dateadd(minute,mins,entrytime)
  when 'S' then set rv=dateadd(minute,0-mins,starttime)
  when 'C' then set rv=dateadd(minute,mins,casctime)
  when 'X' then set rv=dateadd(year,150,current timestamp)
  when 'O' then begin
      execute immediate string('set rv=',fnc,'(compid,vacid,entrytime,starttime,casctime,requestlevel)')
    exception
      when others then
        set rv=dateadd(year,150,current timestamp)
    end
  end case;
  return rv
end
}
</code>