====== 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>
create function "pears"."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>