====== pears.GetShiftStateDescription ======
<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"."GetShiftStateDescription" IS 
{create function GetShiftStateDescription 

/* Application Maintained Function / Procedure - DO NOT EDIT*/

(  in sid char(20),in ddate date,in bperson smallint ) 
returns char(70)
begin
  declare rv char(70);
  declare t char(70);
  declare tt char(70);
  declare s char(20);
  declare s1 char(1);
  -- bperson=2 means same as 1 but full description for Available and Unavailable
  if bperson = -1 then
    return getshiftstatedescriptionPlus(sid,ddate)
  else if bperson >= 1 then
      set rv = getkeyshiftstate(sid,ddate,1)
    else
      set rv = getkeyshiftplanstate(sid,ddate,bperson)
    end if end if;
  set s1 = left(rv,1);
  if length(rv) > 1 and s1 in( 'P','B','W','E','X','Y' ) then
    set s = stuff(rv,1,1,null);
    set t = null;
    if bperson >= 1 then
      if s1 = 'E' then -- Employment NOT Shift record
        select company.name into t from employment key join company where employment.employmentid = s
      else
        if s1 in( 'X','Y' ) then -- Plan - Y indicates cascaded
          select isnull(person.name,company.name) into t from tempshiftplan key join vacancy key join employment key join company,vacancy key left outer join person where tempshiftplan.tempshiftplanid = s
        else
          select isnull(person.name,company.name) into t from tempshift key join vacancy key join employment key join company,vacancy key left outer join person where tempshift.tempshiftid = s
        end if
      end if
    else if s1 not in( 'X','Y' ) then
        select gettempname(tempshift.personid) into t from tempshift where tempshift.tempshiftid = s
      end if end if;
    set rv = string(s1,t)
  end if;
  if s1 in( 'A','U' ) and bperson >= 1 then
    set s = nullif(stuff(rv,1,1,null),'');
    if s1 = 'A' then
      select string('Available ',a.availtemplateid),string('A: ',a.name,' ',dateformat(a.timefrom,'hh:nn'),'-',dateformat(a.timeto,'hh:nn'))
        into t,tt from tempshift as sh key join availabilitytemplate as a where sh.tempshiftid = s;
      set t = isnull(t,'Available')
    else
      set t = 'Unavailable';
      select string('U: ',u.name) into tt from tempshift as sh key join tempshiftunavailablereason as u where bperson = 2 and sh.tempshiftid = s
    end if;
    if bperson = 2 and tt is not null then
      set rv = tt
    else
      set rv = t
    end if
  end if;
  return rv
end
}
</code>