====== pears.MasterRosterLimitColour ======
<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"."MasterRosterLimitColour"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "VacID" char(20) ) 
returns char(1)
begin
  -- return G - OK, O - warning, R - problem
  -- R means over limit
  -- O means within 10%
  declare "ShiftsTot" integer;
  declare "RV" char(1);
  if not exists(select "tempshifttemplate"."tempshifttemplateid" from "tempshifttemplate" key join "templategrouptemplate"
        key join "shifttemplategroup" key join "vacancylimit" where "vacancyid" = "vacid" and exists(select * from "masterrostershift" key join "masterroster" where "vacancyid" = "vacid" and "nextdate"
         > current date and "isnull"("finaldate",current date)
         >= current date and "tempshifttemplateid" = "tempshifttemplate"."tempshifttemplateid")) then
    return 'B'
  end if;
  set "RV" = 'O';
  -- for each shift template find min limit (could be 2) then total current mr shifts	
  for "forlab" as "curs" no scroll cursor for select distinct "tempshifttemplate"."tempshifttemplateid" as "tid","target" as "vaclimit" from "tempshifttemplate" key join "templategrouptemplate"
        key join "shifttemplategroup" key join "vacancylimit" where "vacancyid" = "vacid" do
    select "sum"(60*"getshiftlength"("timefrom","timeto","breakminutes")/("daysincycle"/7)) into "ShiftsTot" from "masterrostershift" key join "masterroster" where "vacancyid" = "vacid"
      and "nextdate" > current date and "isnull"("finaldate",current date) >= current date and "tempshifttemplateid" = "tid";
    if "ShiftsTot" > "vaclimit" then return 'R'
    end if;
    if "ShiftsTot" > .9*"vaclimit" then set "RV" = 'G'
    end if end for;
  return "rv"
end
</code>