pears.GetCascadeTime

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.

Original SQL

CREATE FUNCTION "pears"."GetCascadeTime"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "compid" CHAR(20),IN "vacid" CHAR(20),IN "entrytime" TIMESTAMP,IN "shifttime" 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;
  SELECT FIRST "hoursfrom","isnull"("cascadehours",0.0)*60.0,"customfunction" INTO "frm","mins","fnc" FROM "cascaderule" WHERE "vacancyid" = "vacid" AND "cascadelevel" = "requestlevel" AND "isnull"("vacancyrule",0) = 0;
  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 "isnull"("vacancyrule",0) = 0
  END IF;
  CASE "frm" WHEN 'E' THEN
    SET "rv" = "dateadd"("minute","mins","entrytime") WHEN 'S' THEN
    SET "rv" = "dateadd"("minute",0-"mins","shifttime") 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,shifttime,casctime,requestlevel)')
    exception
      WHEN others THEN
        SET "rv" = "dateadd"("year",150,CURRENT TIMESTAMP)
    END
  END CASE;
  RETURN "rv"
END