pears.GetTempRateByFlag

CREATE FUNCTION "pears"."GetTempRateByFlag"( IN "PayOrCharge" CHAR(1),IN "PlacOrVac" CHAR(1),IN "ID" CHAR(20),IN "flag" CHAR(20) ) 
RETURNS DOUBLE
BEGIN
  DECLARE "pay" DOUBLE;
  DECLARE "chg" DOUBLE;
  DECLARE "disc" DOUBLE;
  DECLARE "bandid" CHAR(20);
  DECLARE "vid" CHAR(20);
  SET "vid" = "id";
  SET "pay" = NULL;
  SET "disc" = 0;
  SELECT FIRST "temppaybandid" INTO "bandid" FROM "temppayband" WHERE "temppayband"."payrollflag" = "flag" ORDER BY "sortorder" ASC;
  IF "placorvac" = 'P' THEN
    SELECT FIRST "payrate","chargerate" INTO "pay","chg" FROM "tempjobrate" WHERE "placementid" = "ID" AND "temppaybandid" = "bandid"
      AND("startdate" <= CURRENT DATE OR "startdate" IS NULL) AND("enddate" >= CURRENT DATE OR "enddate" IS NULL);
    IF "pay" IS NULL THEN
      SELECT FIRST "payrate","chargerate" INTO "pay","chg" FROM "tempjobrate" WHERE "placementid" = "ID" AND "temppaybandid" = "bandid"
    END IF;
    IF "pay" IS NULL THEN
      SELECT "vacancyid" INTO "vid" FROM "placement" WHERE "placementid" = "id"
    END IF END IF;
  IF "pay" IS NULL THEN
    SELECT "isnull"("discount",0) INTO "disc" FROM "vacancy" WHERE "vacancyid" = "vid";
    SELECT FIRST "payrate","chargerate" INTO "pay","chg" FROM "tempjobrate" WHERE "vacancyid" = "vid" AND "temppaybandid" = "bandid"
      AND("startdate" <= CURRENT DATE OR "startdate" IS NULL) AND("enddate" >= CURRENT DATE OR "enddate" IS NULL)
  END IF;
  IF "pay" IS NULL THEN
    SELECT FIRST "payrate","chargerate" INTO "pay","chg" FROM "tempjobrate" WHERE "vacancyid" = "vid" AND "temppaybandid" = "bandid"
  END IF;
  IF "payorcharge" = 'P' THEN
    RETURN("pay")
  ELSE
    RETURN("round"("chg"-("chg"*"disc"/100),2))
  END IF
END