pears.GetKeyShiftPlanState

CREATE FUNCTION "pears"."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) ASC))
  END IF;
  RETURN "rv"
END