pears.GetKeyShiftState

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."GetKeyShiftState" IS 
{CREATE FUNCTION GetKeyShiftState 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(  IN sid CHAR(20),IN ddate DATE,IN bperson SMALLINT ) 
RETURNS CHAR(21)
BEGIN
  DECLARE rv CHAR(21);
  DECLARE id CHAR(20);
  DECLARE empid CHAR(20);
  DECLARE so SMALLINT;
  DECLARE wrkd SMALLINT;
  SET rv = NULL;
  IF bperson = 1 THEN
    SELECT FIRST state,tempshiftid,
      (CASE state WHEN 'P' THEN
        0
      WHEN 'C' THEN 1 WHEN 'B' THEN 2 WHEN 'W' THEN 3 WHEN 'H' THEN 4 WHEN 'U' THEN 5 ELSE 6 END) AS sortorder INTO rv,id,so FROM tempshift
      WHERE personid = sid AND shiftdate = ddate AND(NOT(state = 'C' AND tempconfirmed = 1))
      ORDER BY sortorder ASC;
    IF rv IS NULL OR rv = 'A' THEN
      SELECT FIRST e.employmentid,placementdayworked(pl.placementid,ddate) INTO empid,wrkd
        FROM employment AS e KEY LEFT OUTER JOIN placement AS pl
        WHERE isnull(e.concurrent,0) = 0 AND e.personid = sid AND e.startdate <= ddate AND(e.leavedate IS NULL OR e.leavedate >= ddate)
        ORDER BY wrkd DESC;
      IF empid IS NOT NULL AND wrkd = 1 THEN
        RETURN 'E'+empid
      END IF
    END IF
  ELSE SELECT FIRST state,tempshiftid,
      (CASE state WHEN 'U' THEN
        1
      WHEN 'P' THEN 2 WHEN 'B' THEN 3 ELSE 4 END) AS sortorder INTO rv,id,so FROM tempshift
      WHERE vacancyid = sid AND shiftdate = ddate AND state <> 'C'
      ORDER BY sortorder ASC
  END IF;
  IF id IS NOT NULL THEN
    SET rv = rv || id
  END IF;
  RETURN(rv)
END
}