====== 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>
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."GetKeyShiftState" IS 
{create function 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>