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

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

(in sid 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,isnull(s.tempconfirmed,0) as tempconf from tempshift as s where s.vacancyid = sid and s.shiftdate = ddate union all
    select(if p.cascadelevel > 0 then 'Y' else 'X' endif) as shiftstatus,0 as cliconf, 0 as tempconf from tempshiftplan as p where p.vacancyid = sid and p.shiftdate = ddate and
      not p.tempshiftplanid = any(select tempshiftplanid from tempshift where vacancyid = sid and
        shiftdate = ddate and(not(state = 'C' and crefill = 1)) and tempshiftplanid is not null) 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) or (tempconf = 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>