pears.GetKeyShiftState

CREATE FUNCTION "pears"."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