====== 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>
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."GetPersonAvailState" IS 
{create function 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>