One of our customers came up with a bit of a challenging requirement. Upon emailing a report to their clients, they need to attach documents connected to that record instance along with the mail. To attach a file in the event mail, you need to either have it as a binary object or the file must exist in the application server.

Requirement in detail:

Customer wants to attach documents connected to inquired supplier tab to the Order Quotation email which can send from IFS.

This functionality uses the application defined event PDF_REPORT_CREATED  to send the email. By default,  the archived report PDF can attached as an attachment. In order to attach additional attachments to the Email,  either you need to add the object as a binary object or need to give the path where the document exist in the disk of application server machine.

My proposed solution was to downloads the file(s) to the application server and then appends the file downloaded file paths in the event action. Luckily there are set of standard methods available which were develop for a different purpose, to move files between repositories, but it served my requirement very well.

I’ve used the FILE_PATH_FOR_BATCH_TRANSFER in Document Basic – Document default values as the file download location since then this can be configured from the application.

Given below is the event action SQL for the proposed solution

-- Author		: Damith Jinasena (damithsj@gmail.com)
-- Purpose		: Attach documents connected to InquirySupplier LU into the
--				  mail sent to customer who inquires for the quotation
-- App Version	: IFS Applications 8 SP2
DECLARE
keyref_ VARCHAR2(4000);
CURSOR get_attachments IS
SELECT a1.doc_class, a1.doc_no, a1.doc_sheet, a1.doc_rev, a1.doc_type, a1.file_no, a1.FILE_EXTENTION
FROM IFSAPP.DOC_REFERENCE_OBJ_EXT_DETAILS a1
WHERE (a1.LU_NAME = 'InquirySupplier'
AND a1.KEY_REF = keyref_);

--Download path, this can be any folder location in Application Server. But I've used parameter FILE_PATH_FOR_BATCH_TRANSFER in Document BasicDocument default values.
--remember to add trailing "" when defining the path
download_path_ VARCHAR2(4000) := DOCMAN_DEFAULT_API.Get_Default_Value_('BatchTransferHandler', 'FILE_PATH_FOR_BATCH_TRANSFER');
error_msg_ VARCHAR2(4000);

attachment_name_ varchar2(4000);

attachments_ varchar2(4000);

BEGIN
keyref_ := 'INQUIRY_NO=' || '&PDF_PARAMETER_3' || '^VENDOR_NO=' || '&PDF_PARAMETER_2' || '^';

FOR rec_ IN get_attachments LOOP
attachment_name_ := download_path_ || 'attachment-' || rec_.doc_class || '_' || rec_.doc_no || '.' || rec_.FILE_EXTENTION;
error_msg_ := NULL;
-- Download files from DOCMAN into folder in Applicaiton Server
Batch_Transfer_Handler_Api.Download_From_Db(error_msg_,
rec_.doc_class,
rec_.doc_no,
rec_.doc_sheet,
rec_.doc_rev,
rec_.doc_type,
rec_.file_no,
attachment_name_);

IF (error_msg_ IS NOT NULL) THEN --check with view access
Batch_Transfer_Handler_Api.Download_From_Db(error_msg_,
rec_.doc_class,
rec_.doc_no,
rec_.doc_sheet,
rec_.doc_rev,
'VIEW',
rec_.file_no,
attachment_name_);
END IF;
--append attachment names with ";"
attachments_ := attachments_ || attachment_name_ || ';';

END LOOP;
--Attach doduments to the mail and finally include the report PDF file and send mail
Command_SYS.Mail(   from_user_name_ => '&USER_MAIL_ADDRESS',
to_user_name_ => '&PDF_PARAMETER_1',
text_ => '&MAIL_TEXT',
attach_=> attachments_ || '&PDF_FILE',
subject_ => '&MAIL_SUBJECT');

END;

It worked pretty well and could get all the attachments in the mail.

7 Comments

  1. Damith, Have you done similar functions in IFS 10. We have IFS 10, update 10 and our sales teams want to email documents attached to the business Opportunity header from with in the Business Op. Thanks for your Blogs, that have been informative.
    Bill Hemingway
    R.E. Mason Co, Charlotte, NC

    1. Hi Bill,
      Thanks for visiting my blog 🙂

      I suppose this should work in Apps10 as well.
      But Apps10 has several improvements to Command_SYS.mail including the capability of adding attachments directly. It involve bit of coding but following are the main points to consider.
      * retrieve DOCMAN file data from EDM_FILE_STORAGE FILE_DATA column providing the keys of the document.
      * create a attachment array variable (attachments_ command_sys.attachment_arr;) and add each document BLOBs using command_sys.add_attachment
      * call command_sys.mail with the attachment array created above

      Hope it helps!
      Damith

  2. Hi Damith,

    Thank you for well written Blog, very informative and interesting.
    “need to give the path where the document exist in the disk of application server machine.” This is an eye opener to the question I’m facing for my client.
    An email send as HTML formatted to the client, images retrieve through share points. eg: company logo
    For some users this is not visible. What is the away to add these images to DOCMAN and retrieve as mention.

    Keep posting valuable posts.

    Best Regards,
    Udeni
    Former IFS

    1. Hi Udeni,

      Thanks for visiting my blog and really appreciate your comments 🙂
      For adding images in html formatted email body tag, easiest solution would be to add the image as base64 encoded inline image instead of shared path or url
      Please check below IFS community link for more details and some example event actions 🙂
      https://community.ifs.com/framework-experience-infrastructure-cloud-integration-dev-tools-50/logo-in-email-signature-triggered-from-ifs-2484?postid=9224#post9224

  3. Hi Damith,

    Thank you the guiding, greatly appreciated.
    BTW my client is in APP9 UPD17.

    I have 2 images and 1 background image. As in the community feedback feels like I will face string size issue. you have comment “I have seen a customer who developed a custom LU to store base64 strings of images and other html tags so they can create email body dynamically.”
    Is it supported by BLOB ? Since VARCHAR2 can store only 32000.
    Appreciate your feedback.

    Thanks in advance and Best Regards,
    Udeni

    1. Hi Udani,

      Command_Sys.mail email body can accept CLOB from Apps10 onwards. Unfortunately you’ll hit the varchar2 32000 limit if you are trying to include a base64 encoded image in Apps9 😐

  4. Hi Damith,
    Thank you. Is there any other way, saving the images to DOCMAN and retrieve them the HTML formatted email.?
    Best Regards,
    Udeni

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s