====== pears.GetPersonAvailState ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
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
</code>