pears.PersonUnavailability

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