How to Include additional attachments in e-mail reports in IFS Cloud

Include additional attachments in IFS e-mail report is one of the top posts in my blog and received many good comments. One of the questions was that the methods used here are not compatible in IFS Cloud.

Script used in above post was developed for Apps8. By that time, Command_SYS.mail has limited capability of handling attachments. It could handle only files stored in extended server and add them as file path, and add the report pdf file.

Form IFS Application 10 onwards, Command_SYS mail functionality has several improvements. One major improvement is the way it handle attachments in emails. Now, it is possible to add attachments as an array of CLOBs or BLOBs or a file path (applied only for on-prem IFS cloud installations).

Below are the overrides of COMMAND_SYS.Add_Attachment for attaching documents stored in different data formats


PROCEDURE Add_Attachment (attachments_ IN OUT NOCOPY attachment_arr, filename_ IN VARCHAR2, attachment_ IN CLOB );

PROCEDURE Add_Attachment (attachments_ IN OUT NOCOPY attachment_arr, filename_ IN VARCHAR2, attachment_ IN BLOB );

PROCEDURE Add_Attachment (attachments_ IN OUT NOCOPY attachment_arr,  filepath_ IN VARCHAR2,  i_  IN NUMBER );

In this post, we will look into two common scenarios of including additional attachments in IFS emails.

Create a CSV and attach as a file in IFS email

Suppose it’s needed to create a CSV file with order details and attach with the Purchase Order email once it’s printed. We can create the CSV inside our event script and attach as a CLOB in the email. Below script shows the approach in detail.

— Author : Damith Jinasena (damithsj@gmail.com)
— Purpose : Event action based on PDF_REPORT_CREATED event for Purchase Order report.
— Email is sent with purchase order report as well as a CSV containing line information
— App Version : Apps10, IFS Cloud 2X RX
declare
po_no_ VARCHAR2(100):= '&PDF_PARAMETER_6';
attachments_ COMMAND_SYS.ATTACHMENT_ARR;
csv_header_ VARCHAR2(32000);
csv_line_ VARCHAR2(32000);
csv_file_ CLOB;
file_name_ VARCHAR2(2000) := 'po_no_'|| po_no_ || '_order_lines.csv';
mail_body_ VARCHAR2(32000);
CURSOR get_po_lines IS
select * from PURCHASE_ORDER_LINE_TAB
where order_no = po_no_;
begin
— Create CSV header columns
csv_header_ := 'ORDER_NO;LINE_NO;RELEASE_NO;DESCRIPTION;BUY_QTY_DUE;BUY_UNIT_PRICE;STATE' ||chr(13)||chr(10);
csv_file_ := csv_header_;
— Create the CSV
FOR rec_ IN get_po_lines LOOP
csv_line_ := NULL;
csv_line_ := rec_.ORDER_NO || ';' || rec_.LINE_NO || ';' || rec_.RELEASE_NO || ';' || rec_.DESCRIPTION || ';' || rec_.BUY_QTY_DUE || ';' || rec_.BUY_UNIT_PRICE || ';' || rec_.ROWSTATE|| ';' ||chr(13)||chr(10);
csv_file_ := csv_file_ || csv_line_;
END LOOP;
—Add the CSV as an attachment
COMMAND_SYS.Add_Attachment(attachments_ => attachments_,
filename_ => file_name_,
attachment_ => csv_file_);
—Add the report PDF as an attachment
COMMAND_SYS.Add_Attachment(attachments_ => attachments_,
filepath_ => '&PDF_FILE',
i_ => 1);
— Mail
mail_body_ := 'Purchase order details from IFS';
COMMAND_SYS.Mail(sender_ => 'IFSAPP',
from_ => 'IFSAPP',
to_list_ => 'xxx@yyy.com',
subject_ => 'Purchase Order ' || po_no_,
text_ => mail_body_ ,
attachments_ => attachments_);
end;
view raw ifs_cloud_create_csv_and attach_to_email.sql hosted with ❤ by GitHub

Here’s the resultant email and the content of the CSV looks like

Send Email including Documents and media attached to the record

In this example, we are attaching the documents and media attached to the Work Order when printing the Work Order report.

— Author : Damith Jinasena (damithsj@gmail.com)
— Purpose : Event action based on PDF_REPORT_CREATED event for Work Order report.
— Email is sent with Work Order report with documents and media items attached to work order
— App Version : Apps10, IFS Cloud 2X RX
declare
wo_nos_ VARCHAR2(100) := LTRIM('&NOTES', 'WO No List: ');
wo_no_ VARCHAR2(100);
keyref_ VARCHAR2(2000);
file_name_ VARCHAR2(2000);
attachments_ COMMAND_SYS.ATTACHMENT_ARR;
mail_body_ VARCHAR2(32000);
wo_no_list_ Utility_SYS.STRING_TABLE;
count_ NUMBER;
— get connected documents
CURSOR get_connected_docs IS
select *
from edm_file_storage_tab
where (DOC_CLASS, DOC_NO, DOC_SHEET, DOC_REV) IN
(select DOC_CLASS, DOC_NO, DOC_SHEET, DOC_REV
from DOC_REFERENCE_OBJECT
where LU_NAME = 'WorkOrder'
and KEY_REF = keyref_);
— get connected media items
CURSOR get_connected_media IS
select *
from MEDIA_ITEM_TAB
where item_id IN (select item_id
from media_library_join
where LU_NAME = 'WorkOrder'
and KEY_REF = keyref_);
begin
— It'spossible to print more than one Work order together.
— We can tokenize the notes and get the work order no list
Utility_SYS.Tokenize(wo_nos_, ';', wo_no_list_, count_);
FOR i_ IN 1..wo_no_list_.COUNT LOOP
wo_no_ := wo_no_list_(i_);
keyref_ := 'WO_NO=' || wo_no_ || '^';
— Add all documents attached to the Work Order
FOR docs_ IN get_connected_docs LOOP
file_name_ := Edm_File_Util_API.Generate_Docman_File_Name_(doc_class_ => docs_.doc_class,
doc_no_ => docs_.doc_no,
doc_sheet_ => docs_.doc_sheet,
doc_rev_ => docs_.doc_rev,
doc_type_ => docs_.doc_type);
COMMAND_SYS.Add_Attachment(attachments_ => attachments_,
filename_ => file_name_,
attachment_ => docs_.file_data);
END LOOP;
— Add all media items in the Work Order
FOR media_ IN get_connected_media LOOP
COMMAND_SYS.Add_Attachment(attachments_ => attachments_,
filename_ => media_.media_file,
attachment_ => media_.media_object);
END LOOP;
END LOOP;
—Add the report PDF as an attachment
COMMAND_SYS.Add_Attachment(attachments_ => attachments_,
filepath_ => '&PDF_FILE',
i_ => 1);
— Mail
mail_body_ := 'Work order details with attachments from IFS';
COMMAND_SYS.Mail(sender_ => 'IFSAPP',
from_ => 'IFSAPP',
to_list_ => 'xxx@yyy.com',
subject_ => '&NOTES',
text_ => mail_body_,
attachments_ => attachments_);
end;
view raw ifs_cloud_send_email_with_doc_attachments_and_media_items.sql hosted with ❤ by GitHub

I hope this would give a better idea on dealing with attachments in IFS report emails. You can mix and match the scripts and create emails with the information you need. Please share your thoughts or if you find any cool tricks with handling attachments below 😎

Advertisement

3 thoughts on “How to Include additional attachments in e-mail reports in IFS Cloud”

Add yours

  2. Pingback: Include additional attachments in IFS e-mail report – DSJ's Blog

Leave a Reply

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

Gravatar
WordPress.com Logo

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

Facebook photo

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

Cancel

Connecting to %s

Website Powered by WordPress.com.

Up ↑

%d bloggers like this: