pears.MasterRosterLimitColour

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."MasterRosterLimitColour" IS 
{CREATE FUNCTION 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
}