pears.GetVacancyCascadeTime

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