pears.FormatPersonAddress1_0

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."FormatPersonAddress1_0" IS 
{CREATE FUNCTION FormatPersonAddress1_0 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(IN reqpersonid 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 person.addr1,person.addr2,person.addr3,person.town,person.county,person.country,person.postcode INTO addr1,
    addr2,addr3,town,county,country,postcode FROM person WHERE
    personid = reqpersonid;
  /*
  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
}