pears.GetPersonAvailState

CREATE FUNCTION "pears"."GetPersonAvailState"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "sid" CHAR(20),IN "ddate" DATE ) 
RETURNS CHAR(1)
BEGIN
  DECLARE "rv" CHAR(1);
  DECLARE "empid" CHAR(20);
  DECLARE "so" SMALLINT;
  DECLARE "dw" SMALLINT;
  DECLARE "Temp" tinyint;
  DECLARE "Wsun" tinyint;
  DECLARE "WMon" tinyint;
  DECLARE "WTue" tinyint;
  DECLARE "WWed" tinyint;
  DECLARE "WThu" tinyint;
  DECLARE "WFri" tinyint;
  DECLARE "WSat" tinyint;
  SET "rv" = NULL;
  SELECT FIRST "state",
    (CASE "state" WHEN 'P' THEN
      0
    WHEN 'B' THEN 2 WHEN 'W' THEN 3 WHEN 'H' THEN 4 WHEN 'U' THEN 5 ELSE 6 END) AS "sortorder" INTO "rv","so" FROM "tempshift"
    WHERE "personid" = "sid" AND "shiftdate" = "ddate" AND "state" <> 'C'
    ORDER BY "sortorder" ASC;
  IF "rv" IS NULL OR "rv" = 'A' THEN
    SET "empid" = NULL;
    SET "dw" = "dow"("ddate");
    SELECT FIRST "employmentid","Temp" INTO "empid","temp" FROM "employment" WHERE "isnull"("concurrent",0) = 0 AND "personid" = "sid" AND "startdate" <= "ddate" AND("leavedate" IS NULL OR "leavedate" >= "ddate");
    IF "empid" IS NOT NULL THEN
      IF "temp" = 0 THEN
        RETURN 'W'
      END IF;
      SELECT "isnull"("WorkSunday",0),"isnull"("WorkMonday",0),"isnull"("WorkTuesday",0),"isnull"("WorkWednesday",0),
        "isnull"("WorkThursday",0),"isnull"("WorkFriday",0),"isnull"("WorkSaturday",0) INTO "wSun","WMon","WTue","WWed","WThu","WFri","WSat"
        FROM "employment" KEY JOIN "placement" WHERE "placement"."employmentid" = "empid";
      IF("wsun" IS NULL) OR(("wsun"+"wmon"+"wTue"+"wWed"+"wThu"+"wFri"+"wSat") = 0) OR(("wsun" = 1 AND "dw" = 1)
        OR("wMon" = 1 AND "dw" = 2) OR("wTue" = 1 AND "dw" = 3) OR("wWed" = 1 AND "dw" = 4) OR("wThu" = 1 AND "dw" = 5)
        OR("wFri" = 1 AND "dw" = 6) OR("wsat" = 1 AND "dw" = 7)) THEN RETURN 'W'
      END IF
    END IF END IF;
  RETURN "rv"
END