pears.VacancyExtraHols

CREATE FUNCTION "pears"."VacancyExtraHols"( IN @TempTimeSheetID CHAR(20) ) 
RETURNS DOUBLE
BEGIN
  DECLARE "rv" DOUBLE;
  -- check earns hol pay
  IF EXISTS(SELECT * FROM "temptimesheetline" KEY JOIN "temppayband" WHERE "temptimesheetid" = @temptimesheetid AND "includeinholidaypay" = 1) THEN
    SELECT "vacancy"."ExtraHols" INTO "rv" FROM "temptimesheet" KEY JOIN "placement" KEY JOIN "vacancy" WHERE "temptimesheetid" = @TempTimeSheetID
  END IF;
  -- deal with contras
  IF(SELECT "sum"("unitspaid"*"payrate") FROM "temptimesheetline" KEY JOIN "temppayband" WHERE "temptimesheetid" = @temptimesheetid AND "includeinholidaypay" = 1) < 0 THEN
    SET "rv" = 0
  END IF;
  RETURN "isnull"("rv",0)
END