====== pears.GetKeyShiftState ======
<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"."GetKeyShiftState"( 
  /* 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);
  declare "id" char(20);
  declare "empid" char(20);
  declare "so" smallint;
  declare "wrkd" smallint;
  set "rv" = null;
  if "bperson" = 1 then
    select first "state","tempshiftid",
      (case "state" when 'P' then
        0
      when 'C' then 1 when 'B' then 2 when 'W' then 3 when 'H' then 4 when 'U' then 5 else 6 end) as "sortorder" into "rv","id","so" from "tempshift"
      where "personid" = "sid" and "shiftdate" = "ddate" and(not("state" = 'C' and "tempconfirmed" = 1))
      order by "sortorder" asc;
    if "rv" is null or "rv" = 'A' then
      select first "e"."employmentid","placementdayworked"("pl"."placementid","ddate") into "empid","wrkd"
        from "employment" as "e" key left outer join "placement" as "pl"
        where "isnull"("e"."concurrent",0) = 0 and "e"."personid" = "sid" and "e"."startdate" <= "ddate" and("e"."leavedate" is null or "e"."leavedate" >= "ddate")
        order by "wrkd" desc;
      if "empid" is not null and "wrkd" = 1 then
        return 'E'+"empid"
      end if
    end if
  else select first "state","tempshiftid",
      (case "state" when 'U' then
        1
      when 'P' then 2 when 'B' then 3 else 4 end) as "sortorder" into "rv","id","so" from "tempshift"
      where "vacancyid" = "sid" and "shiftdate" = "ddate" and "state" <> 'C'
      order by "sortorder" asc
  end if;
  if "id" is not null then
    set "rv" = "rv" || "id"
  end if;
  return("rv")
end
</code>