pears.FormatCompanyPersonAddress

CREATE FUNCTION "pears"."FormatCompanyPersonAddress"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "ReqEmploymentID" CHAR(20),IN "Capitalise" SMALLINT,IN "Style" CHAR(1) ) 
RETURNS CHAR(400)
BEGIN
  DECLARE "FormattedAddress" CHAR(400);
  DECLARE "IncPosition" SMALLINT;
  DECLARE "IncDept" SMALLINT;
  DECLARE "PersonName" CHAR(30);
  DECLARE "CompanyName" CHAR(60);
  DECLARE "Post" CHAR(50);
  DECLARE "Dept" CHAR(30);
  DECLARE "NewLine" CHAR(2);
  DECLARE "ReqCoID" CHAR(20);
  /* Decide whether to use CR/LF or comma as seperator */
  IF
    "Style" = 'B' THEN
    SET "NewLine" = "char"(13)+"char"(10)
  ELSEIF "Style" = 'L' THEN SET "NewLine" = ', '
  END IF;
  /* Get option settings for Position & Department from params */
  SELECT FIRST "PositionInAddr","DeptInAddr" INTO "IncPosition","IncDept" FROM "Params";
  /* Get Company & Person details */
  SELECT "Person"."Name","Company"."Name","Employment"."Position","Employment"."Department","Employment"."CompanyID" INTO "PersonName",
    "CompanyName","Post","Dept",
    "ReqCoID" FROM "Pears"."Employment" KEY JOIN("Pears"."Person","Pears"."Company")
    WHERE "Employment"."EmploymentID" = "ReqEmploymentID";
  /* Initialise the address */
  SET "FormattedAddress" = "PersonName"+"NewLine";
  /* Add Position if required */
  IF
    "isnull"("IncPosition",0) = 1
    AND "length"("trim"("Post")) > 0 THEN
    SET "FormattedAddress" = "FormattedAddress"+"Post"+"Newline"
  END IF;
  /* Add department if required */
  IF
    "isnull"("IncDept",0) = 1
    AND "length"("trim"("Dept")) > 0 THEN
    SET "FormattedAddress" = "FormattedAddress"+"Dept"+"Newline"
  END IF;
  /* Get the rest of the address */
  SET "FormattedAddress" = "FormattedAddress"+"CompanyName"+"NewLine"+"FormatCompanyAddress"("ReqCoID","Capitalise","Style");
  RETURN("FormattedAddress")
END