Include additional attachments in IFS e-mail report

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 (
-- Purpose		: Attach documents connected to InquirySupplier LU into the
--				  mail sent to customer who inquires for the quotation
-- App Version	: IFS Applications 8 SP2
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
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);

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

IF (error_msg_ IS NOT NULL) THEN --check with view access
--append attachment names with ";"
attachments_ := attachments_ || attachment_name_ || ';';

--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');


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


32 thoughts on “Include additional attachments in IFS e-mail report

Add yours

  1. Hi Damith, if only I had discovered your blog before !! I’m a developer coming from ibm machines and launched into ifs. The first months were hard but now I can do very complicated things, even without having ifsapp password and privileges. We’re running ifs 10 upd 17 and I have developed an interface to invoke the command_sys.mail just for logging every mail submitted by the user and to make it more frendly to use. But now I’m facing a problem: I’m required to send the general ledger in xls format by email. To do that I have created a migration job that read the GEN_LED_ACCOUNT_REPORT_REP and create a csv file. The file is stored into a folder called DATA_MIGRATION. When I try to attach it to the mail, the server is unable to find out that file. In the file name I tried something like the file name itself, “DATA_MIGRATION/filename”, “F:/DATA_MIGRATION/filename” but nothing worked. Do you have any suggestions ? Thanks !!!

    1. Hi Luca,

      Thanks for visiting my blog and glad to hear you found it useful. Seems you came so far with your request and there’s only few more things to do to get it work πŸ™‚

      In order for command_sys to find the attachment, the folder should be accessible from the application server. I suggest to make a file share for F:/DATA_MIGRATION/ and grant access to the user who runs the IFS windows service and use the share path in the filename.

      Otherwise, there’s an alternate method for this. Since you are using command_sys, it can be done without a migration job.
      In your interface, save the xls to a clob attribute and and in Apps 10, command_sys has on override method to attach a clob directly instead of reading form a file. Please let me know if you need any help. I’ll try to find a code sample for you πŸ™‚

      Good luck!

      1. Hi Damith,
        I’m using the migration job just because I don’t know any other way to create a csv file from a table. Could you please explain me how can I do it in another way ? Thanks

      2. Code for creating and attaching CLOB attachment to command_sys.mail can be found below. You can create a cursor to get the data from the table and add in the clob to create the csv content. Hope you can manage that part πŸ™‚

        attachments_ COMMAND_SYS.ATTACHMENT_ARR;
        attachment_ CLOB;
        Create CSV as a CLOB
        attachment_ := 'ADD YOUR CSV CONTENT HERE';
        Add the CSV as an attachment
        COMMAND_SYS.Add_Attachment(attachments_ => attachments_,
        filename_ => 'your_filename.csv',
        attachment_ => attachment_);
        COMMAND_SYS.Mail(sender_ => 'IFSAPP',
        from_ => 'FROM_USER',
        to_list_ => '',
        subject_ => 'MAIL SUBJECT',
        text_ => 'MAIL BODY',
        attachments_ => attachments_);

  2. Hi Damith, Thank you for this useful post and explaination. Do you know if there is a way to attach a file which is not from Docman, but from another location such as Media_Item? Do you know if there’s a similar method such as BATCH_TRANSFER_HANDLER_API.Download_From_DB() written to fetch data from Media files? I tried copying the media file to a folder location of the database machine and giving that path to Command_Sys.Mail() method, but somehow it seems that this method is unable to read that file.
    Thank you!

    1. Hi Macho,

      If you are in Apps10 or later, then this is not need to download the file and attach it. There are 3 procedures included in Command_SYS where you can add CLOB, BLOB or file path attachments.

      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 );

      You can try that for media items, where the media item BLOBs can be add to an attachment array and include in the mail. Hope it helps!


      1. Hi Damith! Thank you very much for the reply. Yes I think we can use the methods you have mentioned for our purpose. Thanks a lot!

  3. Hi Damith,
    Is there a possibility that we can print the documents form backend. Let’s say as soon as I print the Order Confirmation report, all the documents attached to the customer order lines must also be printed along with the confirmation report. Have you ever tried this? Could you please guide in achieving this?

      1. Hi,
        I have already gone through it. In the community post you shared, the code is useful if the documents are pdfs. But in our case, we need word documents to be printed.

  4. This is very useful Damith !!! Don’t you think the same version of this script would work for APP10? If not, what are the places you think should be updated to make it work for APP10?

    Thank you very much.

    1. Hi Dishna,

      Yes, this is compatible with APPS10 πŸ™‚
      But you could do an improved solution with apps10 πŸ˜‰ since in app10 allows to add array of attachments to an email and from docman side, there’s a function to get the document (edm_file_storage_api.get_file_data).By combining these two, you could develop a solution where you can add the documents as an BLOB array in the email. This would eliminate the requirement of downloading the documents as I’ve used in this blog post.

      Happy coding!

      1. Hi Damith,

        You idea was a great help!!! For our earlier scenario, your suggestion of using “Edm_File_Storage_API.Get_File_Data” worked perfectly fine when the repository is database. But in a case if customer is using windows shared folder as the repository, we would like to know if it is still possible to accomplish the email task using the same package / procedure?

        If not is there any other suggestion you have for us to get the PDF file and send an email via the PL/SQL Script?

        Thank you very much!!!


      2. Hi Dishna,

        Thanks for stopping by my blog and for the compliments πŸ™‚
        Edm_File_Storage_API.Get_File_Data works only for document storage is database and you are correct that it can’t be used for file share.

        If you are using a file share, I hope the best way is to get the file directly from the file storage in docman. You should be able to construct the file path inside the plsql and make sure that docman file share is accessible from application server. Hope it works πŸ™‚

  5. This is very useful Damith!!! Do you think the same version of the script can be used for APP10 as well? If not, what are the places you think should be updated to make it work for APP10?

    Thank you very much!

  6. Hi Damith,
    Thank you for a very helpfull, well structured blog.
    I have the same requirement to add all the attached documents to a Purchase Order and send email using pl/sql (background job)
    The documents are stored in FTP and I cannot use edm_file_strorage_tab to get BLOB.

    I wrote a test procedure to take the documents from FTP using Batch_Transfer_Handler_Api.Download_From_Ftp, and it worked well for one particular document, but its failling with the IOException – “The system cannot find the file specified” for other documents, which are in same class, same ftp location, attached to same Purchase Order Line. Details/setup in the Document Revision screen for both are also same.
    Which is very srange. 😦
    The failling documents are viewable on the application without any error.
    Debug information also showing the same path/fileName as in edm_file_tab for these failling documents.

    Could you please advise on any possible reasons to failing the ftp download? or please suggest another option to attach these docs in FTP to an email.

    Appreciate your help.

    Thank you!

    1. Hi Chathuri,

      It’s very strange that it worked for one file but not for the others. If everything looks good in the script, middleware j2ee logs can give more information on why it’s failing.

  7. Hi Damith,
    My issue is a bit different but this concept did help me get 99% to my ultimate goal. I am using an event to trigger multiple layouts for SHIPMENT_ORDER_LINE_REP (Shipment in IFS) based on the default Layout and use various criteria to create the additional layouts and then email the PDFs to the customer. These are not part of Docman but I have Routing/Report Rules set up to save the PDFs to a folder on the Application Server. My Event first uses a Transaction_SYS.Deferred_Call to process the Printnewlayout method located in a Util package I created for each layout required, then Report Rules save the PDF to the Application Server and at the end of the Event I again use Transaction_SYS.Deferred_Call to process the SendEmail method which is located in a Util package I created. All works great except this all happens too fast and not all the PDFs are created when it does the SendEmail method. I get Application Message “The system cannot find the file specified”. If I leave the already created files in place and process the same Shipment the email works with all 4 documents attached but 4 new PDFs are created with encrypted characters at the end because the documents were already in the location and aren’t overwritten. Is there a way to slow the Event before the call to the SendEmail process?

    1. Hi Shelly,

      Glad to hear it helped for you πŸ™‚
      If you want to ‘delay’ the execution, then batch schedule is the ultimate weapon. It also works in the background just as Transaction_SYS.Deferred_Call but you can schedule the execution to any date/time.
      Below I’ve added a code snippet on how to cerate the batch schedule and parameters. Hope it’ll be useful for you!

      schedule_id_ NUMBER;
      start_date_ DATE := SYSDATE + SOMETHING; –Add the delay here
      seq_no_ NUMBER;
      next_execution_date_ DATE := execution_date_;
      ‘ON ‘||to_char(execution_date_, ‘YYYY-MM-DD’)||’ AT ‘||to_char(execution_date_, ‘HH24:MI’));
      Batch_SYS.New_Batch_Schedule_Param(seq_no_, schedule_id_, ‘PARAM1’, VALUE1);
      Batch_SYS.New_Batch_Schedule_Param(seq_no_, schedule_id_, ‘PARAM2’, VALUE2);
      Batch_SYS.New_Batch_Schedule_Param(seq_no_, schedule_id_, ‘PARAM3’, VALUE3);

      1. Thank you for the quick response. Can you explain the last 3 statements and where the VALUE1, VALUE2, VALUE3 are from? do those equate to some of the attributes in the New_Batch_Schedule where it assigns the proper values? Also, I am currently using this: Transaction_SYS.Deferred_Call(‘Der_Shipment_Ord_Ln_Util_API.SendEmail’, param_attr_, der_layout_); which sets up and sends the email. Does this fit into the FUNCTION_NAME?

      2. Hi,

        In your scenario, Der_Shipment_Ord_Ln_Util_API.SendEmail is the method name. Forgot to mention that first you need to register the method as a database task.

        parameter names can then be found in the database task details window. Values are your inputs to the parameters as same as deferred call.


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

  9. 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,

    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 😐

  10. 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,
    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 πŸ™‚

  11. 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!

Leave a Reply

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

You are commenting using your 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

Website Powered by

Up ↑

%d bloggers like this: