pears.PersonUnavailability

CREATE FUNCTION "pears"."PersonUnavailability"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "pid" CHAR(20),IN "PorT" CHAR(1),IN "dfrom" DATE,IN "dto" DATE ) 
RETURNS CHAR(100)
BEGIN
  DECLARE "rv" CHAR(100);
  DECLARE "ss" CHAR(1);
  DECLARE "so" SMALLINT;
  SET "rv" = '';
  IF "pid" IS NULL OR "port" IS NULL OR "port" = 'P' OR "dfrom" IS NULL OR "dto" IS NULL THEN
    RETURN("rv")
  END IF;
  IF EXISTS(SELECT "employmentid" FROM "employment" WHERE "personid" = "pid" AND "isnull"("concurrent",0) = 0 AND "startdate" <= "dto"
      AND("leavedate" IS NULL OR "leavedate" >= "dfrom")) THEN
    SET "rv" = 'Working'
  ELSE
    SET "ss" = NULL;
    SELECT FIRST "state",
      (CASE "state" WHEN 'P' THEN 2
      WHEN 'U' THEN 3 WHEN 'H' THEN 4 WHEN 'A' THEN 5 ELSE 1 END) AS "sortorder" INTO "ss","so" FROM "tempshift"
      WHERE "personid" = "pid" AND "shiftdate" BETWEEN "dfrom" AND "dto" ORDER BY "sortorder" ASC;
    IF "ss" IS NULL OR "ss" = 'A' THEN
      SET "rv" = ''
    ELSE IF "ss" = 'P' THEN
        SET "rv" = 'Shifts Provisionally Booked'
      ELSE IF "ss" = 'U' THEN
          SET "rv" = 'Partially Unavailable'
        ELSE IF "ss" = 'H' THEN
            SET "rv" = 'Holidays Booked'
          ELSE
            SET "rv" = 'Shifts Booked'
          END IF
        END IF
      END IF
    END IF END IF;
  IF "rv" <> '' THEN
    SET "rv" = "rv"+' '
  END IF;
  RETURN("rv")
END