pears.NetGetPlacementid

CREATE FUNCTION "pears"."NetGetPlacementid"( IN "vacid" CHAR(20),IN "persid" CHAR(20),IN "pweekstartdate" DATE ) 
RETURNS INTEGER
NOT deterministic
BEGIN
  DECLARE "placid" CHAR(20);
  SET "placid" = (SELECT FIRST "placement"."placementid" FROM "placement" KEY JOIN "employment" WHERE "placement"."vacancyid" = "vacid" AND "employment"."personid"
       = "persid" AND "placement"."temp" <> 0 ORDER BY "isnull"("employment"."leavedate",CURRENT DATE+3650) DESC);
  IF "placid" IS NULL THEN
    SET "placid" = "uniquekey"('pl');
    INSERT INTO "employment"( "employmentid","companyid","personid","position","leavedate","temp","concurrent" ) 
      SELECT "placid","employment"."companyid","persid","vacancy"."position","pweekstartdate"+6,1,1 FROM "vacancy" KEY JOIN "employment" WHERE "vacancy"."vacancy" = "vacid";
    INSERT INTO "placement"( "placementid","employmentid","vacancyid","departmentid","staffid","contractref","theirref","placedate","temp","daysperweek" ) 
      SELECT "placid","placid","vacid","vacancy"."departmentid","userstaffid","vacancy"."contractref","vacancy"."theirref",CURRENT DATE,1,5 FROM "vacancy" WHERE "vacancy"."vacancy" = "vacid";
    UPDATE "progress" JOIN "status" ON "progress"."status" = "status"."status" AND "status"."type" = 'R' SET "progress"."status" = 'X',"progress"."actiondate" = NULL,
      "progress"."placementid" = "placid" WHERE "progress"."vacancyid" = "vacid" AND "progress"."personid" = "persid"
      AND "isnull"("status"."final",0) = 0 AND "progress"."placementid" IS NULL
  ELSE
    UPDATE "placement" KEY JOIN "employment" SET "employment"."leavedate" = "pweekstartdate"+6
      WHERE "placement"."placementid" = "placid" AND "employment"."leavedate" < "pweekstartdate"+6 // NB - does NOT CHANGE IF NULL
  END IF;
  RETURN "placid"
END