====== pears.PersonUnavailability ======
<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"."PersonUnavailability"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "pid" char(20),in "PorT" char(1),in "dfrom" date,in "dto" date ) 
returns char(100)
begin
  declare "rv" char(100);
  declare "ss" char(1);
  declare "so" smallint;
  set "rv" = '';
  if "pid" is null or "port" is null or "port" = 'P' or "dfrom" is null or "dto" is null then
    return("rv")
  end if;
  if exists(select "employmentid" from "employment" where "personid" = "pid" and "isnull"("concurrent",0) = 0 and "startdate" <= "dto"
      and("leavedate" is null or "leavedate" >= "dfrom")) then
    set "rv" = 'Working'
  else
    set "ss" = null;
    select first "state",
      (case "state" when 'P' then 2
      when 'U' then 3 when 'H' then 4 when 'A' then 5 else 1 end) as "sortorder" into "ss","so" from "tempshift"
      where "personid" = "pid" and "shiftdate" between "dfrom" and "dto" order by "sortorder" asc;
    if "ss" is null or "ss" = 'A' then
      set "rv" = ''
    else if "ss" = 'P' then
        set "rv" = 'Shifts Provisionally Booked'
      else if "ss" = 'U' then
          set "rv" = 'Partially Unavailable'
        else if "ss" = 'H' then
            set "rv" = 'Holidays Booked'
          else
            set "rv" = 'Shifts Booked'
          end if
        end if
      end if
    end if end if;
  if "rv" <> '' then
    set "rv" = "rv"+' '
  end if;
  return("rv")
end
</code>