pears.SelfbillEMailBodyandSubject

CREATE FUNCTION "pears"."SelfbillEMailBodyandSubject"( 
  IN "docid" CHAR(20),IN "BodyorSubject" CHAR(1) ) 
RETURNS long VARCHAR
BEGIN
  DECLARE "rv" long VARCHAR;
  DECLARE "invno" CHAR(12);
  DECLARE "coname" CHAR(30);
  DECLARE "crlf" CHAR(2);
  SELECT "ourref" INTO "invno" FROM "iqacdocument" WHERE "documentid" = "docid";
  SELECT "dbname" INTO "coname" FROM "params";
  SET "crlf" = "char"(13)+"char"(10);
  IF "BodyorSubject" = 'B' THEN
    SET "rv" = "string"('Please find Invoice  ',"invno",' attached. ',"crlf");
    SET "rv" = "string"("rv","crlf");
    SET "rv" = "string"("rv",'Regards',"crlf");
    SET "rv" = "string"("rv","coname","crlf");
    SET "rv" = "string"("rv","crlf");
    SET "rv" = "string"("rv",'If you are not the addressee indicated in this message (or responsible for delivery of the message to such person),');
    SET "rv" = "string"("rv",'you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply email.')
  ELSE
    SET "rv" = "string"("coname",' Invoice ',"invno")
  END IF;
  RETURN("rv")
END