pears.psHealthInsertUpdateVacancy

CREATE PROCEDURE "pears"."psHealthInsertUpdateVacancy"( 
  IN @VacancyID CHAR(20) ) 
BEGIN
  DECLARE "recipid" CHAR(20);
  SELECT "recipientid" INTO "recipid" FROM "vacancy" WHERE "vacancyid" = @VacancyID;
  IF("recipid" IS NULL) OR EXISTS(SELECT * FROM "psHealthVacancy" WHERE "vacancyid" = @Vacancyid) THEN
    RETURN
  END IF; -- no resending
  IF "psHealthCanSendPerson"("recipid",'R') = 1 THEN
    CALL "psHealthInsertUpdatePerson"("recipid",'R');
    INSERT INTO "psHealthVacancy"( "psHealthVacancyID","startDateTime","Status","tenantCaseId","tenantCustomerId","transferbatch","vacancyid","tenantBranchID" ) 
      SELECT "uniquekey"('x'),"startdate",'Referred',
        "refcode",(SELECT "appnumber" FROM "person" WHERE "personid" = "recipientid"),0,"vacancyid","psHealthBranch"(NULL,NULL,"recipid",'R') FROM "vacancy" WHERE "vacancyid" = @vacancyid
  END IF
END