pears.VacancyRecipientHeadline

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
Original SQL

CREATE FUNCTION "pears"."VacancyRecipientHeadline"( IN "vacid" CHAR(20),IN "recipid" CHAR(20) ) 
RETURNS long VARCHAR
BEGIN
  DECLARE "rv" long VARCHAR;
  DECLARE "Part" long VARCHAR;
  DECLARE "Ref" long VARCHAR;
  DECLARE "NewLine" CHAR(2);
  SET "NewLine" = "char"(13)+"Char"(10);
  IF "recipid" IS NULL THEN
    SELECT "department"."name","string"('Our Ref. ',"vacancy"."refcode","ifnull"("vacancy"."theirref",'','     Their Ref. '),"vacancy"."theirref") INTO "rv","ref" FROM "vacancy" KEY JOIN "department" WHERE "vacancy"."vacancyid" = "vacid";
    SELECT "staff"."userid" INTO "part" FROM "vacancy" KEY JOIN "staff" WHERE "vacancy"."vacancyid" = "vacid";
    SET "rv" = "string"("rv",'    ',"part");
    SELECT "status"."name" INTO "part" FROM "vacancy" JOIN "status" ON "status"."type" = 'V' AND "status"."status" = "vacancy"."status" WHERE "vacancy"."vacancyid" = "vacid";
    SET "rv" = "string"("rv",'    ',"part","newline","ref")
  ELSE
    SET "rv" = (SELECT "name" FROM "person" WHERE "personid" = "recipid");
    SELECT "list"(' '+"number") INTO "Part" FROM "phone" WHERE "Who" = 'P' AND "phonetypeid" = 'PH' AND "WhoID" = "RecipID";
    IF "isnull"("Part",'') <> '' THEN SET "rv" = "string"("rv","newline",'Telephone::',"Part")
    END IF END IF;
  SET "part" = '';
  SELECT "getquestanswer"('V','MAX',"VacID") INTO "part";
  IF "part" <> '' THEN SET "rv" = "rv"+"NewLine"+'Salary Range to: '+"part"
  END IF;
  SET "Part" = '';
  SELECT "string"("ifnull"("vacancy"."clientdepartment",'','Client Department: '),"vacancy"."clientdepartment") INTO "part" FROM "vacancy" KEY JOIN "department" WHERE "vacancy"."vacancyid" = "vacid";
  SET "rv" = "string"("rv","newline","part");
  RETURN("rv")
END