pears.NetGetVacancySiteDetails [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.NetGetVacancySiteDetails ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
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
*/
</code>