pears.NetVacancyRoleAllowed

CREATE FUNCTION "pears"."NetVacancyRoleAllowed"( IN "pWebUserID" CHAR(20),IN "pVacancyID" CHAR(20),IN "pVacancyRoleExternalCode" CHAR(20) ) 
RETURNS SMALLINT
BEGIN
  IF NOT EXISTS(SELECT * FROM "VacancyRoleAllocation" AS "a" KEY JOIN "VacancyRole" AS "r" WHERE "a"."VacancyID" = "pVacancyID" AND "r"."ExternalCode" = "pVacancyRoleExternalCode") THEN
    RETURN 1 -- Nobody assigned to this role - anyone can do
  END IF;
  IF EXISTS(SELECT * FROM "VacancyRoleAllocation" AS "a" KEY JOIN "VacancyRole" AS "r","VacancyRoleAllocation" AS "a" KEY JOIN "employment" KEY JOIN "iqxnetuserlink" AS "x" WHERE "a"."VacancyID" = "pVacancyID" AND "r"."ExternalCode" = "pVacancyRoleExternalCode" AND "x"."IQXNetUserID" = "pWebUserID") THEN
    RETURN 1
  END IF;
  RETURN 0
END