pears.AWRPlacement

CREATE FUNCTION "pears"."AWRPlacement"( IN "vacid" CHAR(20),IN "persid" CHAR(20),IN "shiftid" CHAR(20) ) 
RETURNS CHAR(20)
BEGIN
  DECLARE "rv" CHAR(20);
  -- finds placementid for use in AWR 
  SET "rv" = NULL;
  IF "shiftid" IS NOT NULL THEN
    SELECT "placementid" INTO "rv" FROM "tempshift" WHERE "tempshiftid" = "shiftid"
  END IF;
  IF "rv" IS NULL THEN
    SELECT FIRST "placementid" INTO "rv" FROM "placement" KEY JOIN "employment" WHERE "personid" = "persid" AND "vacancyid" = "vacid" ORDER BY "isnull"("employment"."leavedate",CURRENT DATE+3650) DESC
  END IF;
  IF "rv" IS NULL THEN
    -- set rv = uniquekey(persid);
    -- insert into placement ()  values () ;
    -- might create one and return ID ???
  END IF;
  RETURN "rv"
END