pears.FormatCompanyAddress1_0

CREATE FUNCTION "pears"."FormatCompanyAddress1_0"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "reqcompanyid" CHAR(20),IN "Capitalise" SMALLINT,IN "Style" CHAR(1) ) 
RETURNS CHAR(300)
BEGIN
  DECLARE "FormattedAddress" CHAR(300);
  DECLARE "addr1" CHAR(40);
  DECLARE "addr2" CHAR(40);
  DECLARE "addr3" CHAR(40);
  DECLARE "town" CHAR(30);
  DECLARE "county" CHAR(30);
  DECLARE "country" CHAR(30);
  DECLARE "postcode" CHAR(30);
  DECLARE "Separator" CHAR(2);
  DECLARE "PrePostCodeSeparator" CHAR(2);
  /* 
Set the seperator for block or line 
*/
  IF "style" = 'B' THEN
    SET "Separator" = "char"(13)+"char"(10);
    SET "PrePostCodeSeparator" = "Separator"
  ELSE SET "Separator" = ', ';
    SET "PrePostCodeSeparator" = ' '
  END IF;
  /*
Fetch address fields
*/
  SELECT "company"."addr1","company"."addr2","company"."addr3","company"."town","company"."county","company"."country","company"."postcode" INTO "addr1",
    "addr2","addr3","town","county","country","postcode" FROM "company"
    WHERE "companyid" = "reqcompanyid";
  /*
If capitalise option is set, capitalise town. 
*/
  IF "Capitalise" = 1 THEN SET "town" = "upper"("Town")
  END IF;
  /*
Build Formatted Address
*/
  IF "isnull"("addr1",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "trim"("addr1")
  END IF;
  IF "isnull"("FormattedAddress",'') <> '' AND "isnull"("addr2",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "Separator"
  END IF;
  IF "isnull"("addr2",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "trim"("addr2")
  END IF;
  IF "isnull"("FormattedAddress",'') <> '' AND "isnull"("addr3",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "Separator"
  END IF;
  IF "isnull"("addr3",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "trim"("addr3")
  END IF;
  IF "isnull"("FormattedAddress",'') <> '' AND "isnull"("town",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "Separator"
  END IF;
  IF "isnull"("town",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "trim"("town")
  END IF;
  IF "isnull"("FormattedAddress",'') <> '' AND "isnull"("county",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "Separator"
  END IF;
  IF "isnull"("county",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "trim"("county")
  END IF;
  IF "isnull"("FormattedAddress",'') <> '' AND "isnull"("country",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "Separator"
  END IF;
  IF "isnull"("country",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "trim"("country")
  END IF;
  IF "isnull"("FormattedAddress",'') <> '' AND "isnull"("postcode",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "PrePostCodeSeparator"
  END IF;
  IF "isnull"("postcode",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "trim"("postcode")
  END IF;
  RETURN "FormattedAddress"
END