pears.GetShiftStateBookedDescriptionPlus

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."GetShiftStateBookedDescriptionPlus" IS 
{CREATE FUNCTION GetShiftStateBookedDescriptionPlus 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(IN sid CHAR(20),IN pid CHAR(20), IN ddate DATE)
RETURNS CHAR(70)
BEGIN
  DECLARE rv CHAR(70);
  DECLARE s1 CHAR(1);
  DECLARE p1 SMALLINT;
  DECLARE px SMALLINT;
  DECLARE punf SMALLINT;
  DECLARE pprov SMALLINT;
  DECLARE pbkd SMALLINT;
  DECLARE pcanc SMALLINT;
  DECLARE pwkd SMALLINT;
  SET punf=0;
  SET pprov=0;
  SET pbkd=0;
  SET pcanc=0;
  SET pwkd=0;
  SET s1=' ';
  SET p1=0;
  FOR fetchfor AS fetchcursor no scroll cursor FOR
    SELECT s.state AS shiftstatus,isnull(s.clientconfirmed,0) AS cliconf FROM tempshift AS s WHERE s.vacancyid = sid AND s.personid=pid AND s.shiftdate = ddate  FOR READ ONLY do
    SET px=0;
    CASE shiftstatus WHEN 'X' THEN
      SET px=10;
      SET punf=punf+1 WHEN 'Y' THEN
      SET px=9;
      SET punf=punf+1 WHEN 'P' THEN
      SET px=8;
      SET pprov=pprov+1 WHEN 'C' THEN
      IF cliconf = 0 THEN
        SET px=7;
        SET pcanc=pcanc+1
      END IF WHEN 'B' THEN
      SET px=6;
      SET pbkd=pbkd+1 WHEN 'W' THEN
      SET px=5;
      SET pwkd=pwkd+1
    END CASE
    ;
    IF px > p1 THEN
      SET s1=shiftstatus;
      SET p1=px
    END IF END FOR;
  SET rv=s1;
  IF punf > 0 THEN
    SET rv=rv+TRIM(str(punf))+'U '
  END IF;
  IF pprov > 0 THEN
    SET rv=rv+TRIM(str(pprov))+'P '
  END IF;
  IF pbkd > 0 THEN
    SET rv=rv+TRIM(str(pbkd))+'B '
  END IF;
  IF pwkd > 0 THEN
    SET rv=rv+TRIM(str(pwkd))+'W '
  END IF;
  IF pcanc > 0 THEN
    SET rv=rv+TRIM(str(pcanc))+'C '
  END IF;
  SET rv=rv+'   '; -- Extra spaces to stop ellipsis malfunction on grid display
  RETURN rv
END
}