pears.GetKeyShiftPlanState

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."GetKeyShiftPlanState" IS 
{CREATE FUNCTION GetKeyShiftPlanState 
 
/* 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);
  IF bperson = 1 THEN
    RETURN NULL
  ELSE 
    SET rv = isnull( 
      (SELECT FIRST string(IF cascadelevel > 0 THEN 'Y' ELSE 'X' endif, tempshiftplanid) FROM tempshiftplan AS p WHERE 
        vacancyid = sid AND shiftdate = ddate AND 
        NOT tempshiftplanid = any(SELECT tempshiftplanid FROM tempshift WHERE vacancyid = p.vacancyid AND 
        shiftdate = ddate AND(NOT(state = 'C' AND crefill = 1)) AND tempshiftplanid IS NOT NULL) ORDER BY cascadelevel DESC), 
      (SELECT FIRST string(state,tempshiftid) FROM tempshift WHERE 
        vacancyid = sid AND shiftdate = ddate AND(NOT(tempshift.state = 'C' AND tempshift.clientconfirmed = 1)) AND tempshift.state <> 'U' ORDER BY (CASE state WHEN 'P' THEN 1 
        WHEN 'C' THEN 2 WHEN 'B' THEN 3 ELSE 4 END)) 
                 ) 
  END IF; 
  RETURN rv 
END
}