====== pears.GetKeyShiftPlanState ======
<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"."GetKeyShiftPlanState"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "sid" char(20),in "ddate" date,in "bperson" smallint ) 
returns char(21)
begin
  declare "rv" char(21);
  if "bperson" = 1 then
    return null
  else
    set "rv"
       = "isnull"(
      (select first "string"(if "cascadelevel" > 0 then 'Y' else 'X' endif,"tempshiftplanid") from "tempshiftplan" as "p"
        where "vacancyid" = "sid" and "shiftdate" = "ddate"
        and not "tempshiftplanid" = any(select "tempshiftplanid" from "tempshift" where "vacancyid" = "p"."vacancyid"
          and "shiftdate" = "ddate" and(not("state" = 'C' and "crefill" = 1)) and "tempshiftplanid" is not null) order by "cascadelevel" desc),
      (select first "string"("state","tempshiftid") from "tempshift"
        where "vacancyid" = "sid" and "shiftdate" = "ddate" and(not("tempshift"."state" = 'C' and "tempshift"."clientconfirmed" = 1)) and "tempshift"."state" <> 'U' order by(case "state" when 'P' then 1
        when 'C' then 2 when 'B' then 3 else 4 end) asc))
  end if;
  return "rv"
end
</code>