pears.PlacementDayHours

CREATE FUNCTION "pears"."PlacementDayHours"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "bProcess" tinyint,IN "placid" CHAR(20),IN "pdate" DATE,IN "startdate" DATE,IN "enddate" DATE ) 
RETURNS CHAR(10)
BEGIN
  DECLARE "bcanc" tinyint;
  DECLARE "scancid" CHAR(10);
  DECLARE "nhrs" REAL;
  DECLARE "d" SMALLINT;
  DECLARE "bdayticked" tinyint;
  IF "isnull"("bProcess",0) = 0 THEN
    RETURN ''
  END IF;
  IF "pdate" < "startdate" THEN
    RETURN ''
  END IF;
  IF "pdate" > "enddate" THEN
    RETURN ''
  END IF;
  SELECT FIRST "v"."WorkCancelled","isnull"("r"."shortcode","v"."TempShiftCancelReasonID"),"v"."WorkHours" INTO "bcanc","scancid",
    "nhrs" FROM "PlacementDayVariation" AS "v" KEY LEFT OUTER JOIN "tempshiftcancelreason" AS "r"
    WHERE "v"."placementid" = "placid" AND "v"."VariationDate" = "pdate";
  IF "bcanc" = 1 THEN
    RETURN "isnull"("scancid",'C')
  END IF;
  IF "nhrs" IS NOT NULL THEN
    RETURN "nhrs"
  END IF;
  SET "d" = "dow"("pdate")-1;
  SELECT(CASE "d" WHEN 0 THEN "worksunday" WHEN 1 THEN "workmonday" WHEN 2 THEN "worktuesday" WHEN 3 THEN "workwednesday" WHEN 4 THEN "workthursday" WHEN 5 THEN "workfriday" WHEN 6 THEN "worksaturday" END),
    "worknormalhours" INTO "bdayticked","nhrs" FROM "placement" WHERE "placementid" = "placid";
  IF "bdayticked" = 1 THEN
    RETURN "nhrs"
  END IF;
  RETURN ''
END