pears.NetGetVacancySiteDetails

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetGetVacancySiteDetails" IS 
{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 ', ' END IF)+addr1 END IF ),
        (IF isnull(addr2,'') != '' THEN ', ' + addr2 END IF ),
        (IF isnull(addr3,'') != '' THEN ', ' +addr3 END IF ),
        (IF isnull(Town,'') != '' THEN ', ' + town END IF ),
        (IF isnull(postcode,'') != '' THEN ', ' + postcode END IF )
    );
 
    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
*/
}