pears.NetGetVacancySiteDetails

CREATE FUNCTION "pears"."NetGetVacancySiteDetails"( IN "pVacancyID" CHAR(20) DEFAULT NULL ) 
RETURNS long VARCHAR
deterministic
BEGIN
  DECLARE "siteAddress" long VARCHAR;
  DECLARE "siteName" CHAR(50);
  DECLARE "addr1" CHAR(40);
  DECLARE "addr2" CHAR(40);
  DECLARE "addr3" CHAR(40);
  DECLARE "town" CHAR(30);
  DECLARE "postcode" CHAR(20);
  DECLARE "companyID" CHAR(20);
  SELECT "v"."siteName","v"."addr1","v"."addr2","v"."addr3","v"."town","v"."postcode"
    INTO "siteName","addr1","addr2","addr3","town","postcode"
    FROM "vacancy" AS "v"
    WHERE "v"."vacancyID" = "pVacancyID";
  SET "siteAddress"
     = "string"(
    "sitename",
    (IF "isnull"("addr1",'') <> '' THEN(IF "Sitename" <> '' THEN ', ' endif)+"addr1" endif),
    (IF "isnull"("addr2",'') <> '' THEN ', '+"addr2" endif),
    (IF "isnull"("addr3",'') <> '' THEN ', '+"addr3" endif),
    (IF "isnull"("Town",'') <> '' THEN ', '+"town" endif),
    (IF "isnull"("postcode",'') <> '' THEN ', '+"postcode" endif));
  IF "siteAddress" IS NULL THEN
    SELECT "e"."companyID"
      INTO "companyID"
      FROM "vacancy" AS "v"
        KEY JOIN "employment" AS "e"
      WHERE "v"."vacancyID" = "pVacancyID";
    SET "siteAddress" = "GetCompanyAddressOnLine"("companyID")
  END IF;
  RETURN "siteAddress"
END /* DOC
If the site address is blank then address of the company is used.
 
2019-06-12 ET Created function for IW-890
*/