====== 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>
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."GetKeyShiftPlanState" IS 
{create function 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)) 
                 ) 
  end if; 
  return rv 
end
}
</code>