pears.GetShiftStateBookedDescriptionPlus

CREATE FUNCTION "pears"."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