pears.GetShiftStateDescriptionPlus

CREATE FUNCTION "pears"."GetShiftStateDescriptionPlus"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "sid" 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","isnull"("s"."tempconfirmed",0) AS "tempconf" FROM "tempshift" AS "s" WHERE "s"."vacancyid" = "sid" AND "s"."shiftdate" = "ddate" UNION ALL
    SELECT(IF "p"."cascadelevel" > 0 THEN 'Y' ELSE 'X' endif) AS "shiftstatus",0 AS "cliconf",0 AS "tempconf" FROM "tempshiftplan" AS "p" WHERE "p"."vacancyid" = "sid" AND "p"."shiftdate" = "ddate"
      AND NOT "p"."tempshiftplanid" = any(SELECT "tempshiftplanid" FROM "tempshift" WHERE "vacancyid" = "sid"
        AND "shiftdate" = "ddate" AND(NOT("state" = 'C' AND "crefill" = 1)) AND "tempshiftplanid" IS NOT NULL) 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) OR("tempconf" = 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