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