pears.GetShiftStateDescriptionPlus

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