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