====== pears.GetShiftStateDescription ======
<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"."GetShiftStateDescription"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "sid" char(20),in "ddate" date,in "bperson" smallint ) 
returns char(70)
begin
  declare "rv" char(70);
  declare "t" char(70);
  declare "tt" char(70);
  declare "s" char(20);
  declare "s1" char(1);
  -- bperson=2 means same as 1 but full description for Available and Unavailable
  if "bperson" = -1 then
    return "getshiftstatedescriptionPlus"("sid","ddate")
  else if "bperson" >= 1 then
      set "rv" = "getkeyshiftstate"("sid","ddate",1)
    else
      set "rv" = "getkeyshiftplanstate"("sid","ddate","bperson")
    end if end if;
  set "s1" = "left"("rv",1);
  if "length"("rv") > 1 and "s1" in( 'P','B','W','E','X','Y' ) then
    set "s" = "stuff"("rv",1,1,null);
    set "t" = null;
    if "bperson" >= 1 then
      if "s1" = 'E' then -- Employment NOT Shift record
        select "company"."name" into "t" from "employment" key join "company" where "employment"."employmentid" = "s"
      else
        if "s1" in( 'X','Y' ) then -- Plan - Y indicates cascaded
          select "isnull"("person"."name","company"."name") into "t" from "tempshiftplan" key join "vacancy" key join "employment" key join "company","vacancy" key left outer join "person" where "tempshiftplan"."tempshiftplanid" = "s"
        else
          select "isnull"("person"."name","company"."name") into "t" from "tempshift" key join "vacancy" key join "employment" key join "company","vacancy" key left outer join "person" where "tempshift"."tempshiftid" = "s"
        end if
      end if
    else if "s1" not in( 'X','Y' ) then
        select "gettempname"("tempshift"."personid") into "t" from "tempshift" where "tempshift"."tempshiftid" = "s"
      end if end if;
    set "rv" = "string"("s1","t")
  end if;
  if "s1" in( 'A','U' ) and "bperson" >= 1 then
    set "s" = "nullif"("stuff"("rv",1,1,null),'');
    if "s1" = 'A' then
      select "string"('Available ',"a"."availtemplateid"),"string"('A: ',"a"."name",' ',"dateformat"("a"."timefrom",'hh:nn'),'-',"dateformat"("a"."timeto",'hh:nn'))
        into "t","tt" from "tempshift" as "sh" key join "availabilitytemplate" as "a" where "sh"."tempshiftid" = "s";
      set "t" = "isnull"("t",'Available')
    else
      set "t" = 'Unavailable';
      select "string"('U: ',"u"."name") into "tt" from "tempshift" as "sh" key join "tempshiftunavailablereason" as "u" where "bperson" = 2 and "sh"."tempshiftid" = "s"
    end if;
    if "bperson" = 2 and "tt" is not null then
      set "rv" = "tt"
    else
      set "rv" = "t"
    end if end if;
  return "rv"
end
</code>