pears.GetPersonAvailState

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."GetPersonAvailState" IS 
{CREATE FUNCTION GetPersonAvailState 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(IN sid CHAR(20),IN ddate DATE)
RETURNS CHAR(1)
BEGIN
  DECLARE rv CHAR(1);
  DECLARE empid CHAR(20);
  DECLARE so SMALLINT;
  DECLARE dw SMALLINT; 
 DECLARE Temp TinyInt;
  DECLARE Wsun TinyInt;
  DECLARE WMon TinyInt;
  DECLARE WTue TinyInt;
  DECLARE WWed TinyInt;
  DECLARE WThu TinyInt;
  DECLARE WFri TinyInt; 
  DECLARE WSat TinyInt; 
  SET rv=NULL;
  SELECT FIRST state,
    (CASE state WHEN 'P' THEN
      0
    WHEN 'B' THEN 2 WHEN 'W' THEN 3 WHEN 'H' THEN 4 WHEN 'U' THEN 5 ELSE 6 END) AS sortorder INTO rv,so FROM tempshift WHERE
    personid = sid AND shiftdate = ddate AND state <> 'C' ORDER BY
    sortorder ASC;
  IF rv IS NULL OR rv = 'A' THEN
    SET empid=NULL;
    SET dw  = dow (ddate);
    SELECT FIRST employmentid, Temp INTO empid , temp FROM employment WHERE isnull(concurrent,0) = 0 AND personid = sid AND startdate <= ddate AND(leavedate IS NULL OR leavedate >= ddate);
    IF empid IS NOT NULL THEN 
      IF temp=0  THEN
       RETURN 'W' 
      END IF; 
      SELECT isnull(WorkSunday,0),  isnull(WorkMonday,0),  isnull(WorkTuesday,0),  isnull(WorkWednesday,0),
             isnull(WorkThursday,0),  isnull(WorkFriday,0),  isnull(WorkSaturday,0) INTO wSun, WMon, WTue, WWed, WThu, WFri, WSat 
      FROM employment KEY JOIN placement WHERE placement.employmentid =empid; 
      IF   (wsun IS NULL) OR ((wsun+wmon+wTue+wWed+wThu+wFri+wSat)=0) OR ( (wsun=1 AND dw=1) OR  
           (wMon=1 AND dw=2) OR  (wTue=1 AND dw=3)  OR  (wWed=1 AND dw=4) OR  (wThu=1 AND dw=5)  OR 
           (wFri=1 AND dw=6)  OR  (wsat=1 AND dw=7)) THEN  RETURN 'W' 
      END IF;
   END IF END IF; 
   RETURN rv; 
END
}