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

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

(in sid char(20),in pid char(20), in ddate date)
returns char(70)
begin
  declare rv char(70);
  declare s1 char(1);
  declare p1 smallint;
  declare px smallint;
  declare punf smallint;
  declare pprov smallint;
  declare pbkd smallint;
  declare pcanc smallint;
  declare pwkd smallint;
  set punf=0;
  set pprov=0;
  set pbkd=0;
  set pcanc=0;
  set pwkd=0;
  set s1=' ';
  set p1=0;
  for fetchfor as fetchcursor no scroll cursor for
    select s.state as shiftstatus,isnull(s.clientconfirmed,0) as cliconf from tempshift as s where s.vacancyid = sid and s.personid=pid and s.shiftdate = ddate  for read only do
    set px=0;
    case shiftstatus when 'X' then
      set px=10;
      set punf=punf+1 when 'Y' then
      set px=9;
      set punf=punf+1 when 'P' then
      set px=8;
      set pprov=pprov+1 when 'C' then
      if cliconf = 0 then
        set px=7;
        set pcanc=pcanc+1
      end if when 'B' then
      set px=6;
      set pbkd=pbkd+1 when 'W' then
      set px=5;
      set pwkd=pwkd+1
    end case
    ;
    if px > p1 then
      set s1=shiftstatus;
      set p1=px
    end if end for;
  set rv=s1;
  if punf > 0 then
    set rv=rv+trim(str(punf))+'U '
  end if;
  if pprov > 0 then
    set rv=rv+trim(str(pprov))+'P '
  end if;
  if pbkd > 0 then
    set rv=rv+trim(str(pbkd))+'B '
  end if;
  if pwkd > 0 then
    set rv=rv+trim(str(pwkd))+'W '
  end if;
  if pcanc > 0 then
    set rv=rv+trim(str(pcanc))+'C '
  end if;
  set rv=rv+'   '; -- Extra spaces to stop ellipsis malfunction on grid display
  return rv
end
}
</code>