Skip to main content

Requirement: We need to trigger an event to send a mail to a particular recipient with the documents attached to a Customer Order.

 

Problem Overview: We have set up an event action using online SQL to generate and send emails with relevant attachments. The process involves fetching the appropriate attachment and sending it along with the email. However, we've encountered a challenge where, even though the attachment is successfully fetched, it's not getting attached to the email. Consequently, when we trigger the event, we receive a mail without the expected attachment.

 

What's Been Done:

Please note that we have hard-coded some of the values for testing purposes.

 

  1. SQL query to fetch the relevant attachment.  
 DECLARE
                                                                                                                                                                                                                                                        keyref_     VARCHAR2(4000);
order_no_           VARCHAR2(20) := '&ORDER_NO';
                                                                                                                                                                                                                                                            CURSOR get_attachments IS
select a.doc_class, a.doc_no, a.doc_sheet, a.doc_rev, b.doc_type, b.file_no,  b.file_extention, b.file_name, b.user_file_name 
from doc_reference_object a, edm_file b
where a.lu_name = 'CustomerOrder'
and b.doc_class = a.doc_class
and b.doc_no = a.doc_no
and b.doc_sheet = a.doc_sheet
and b.doc_rev = a.doc_rev
and a.key_ref = keyref_ ;

BEGIN
keyref_ := 'ORDER_NO=' || order_no_  || '^';
FOR rec_ IN get_attachments LOOP
  SELECT
      SUBSTR(rec_.file_name , INSTR(rec_.file_name , '.') + 1) 
  INTO extention_
  FROM dual;
END LOOP;
END;

 

  1. SQL to Attach the documents (This is where we encounter the issue)
DECLARE
                                                                                                                                                                                                                                        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_ := 'ORDER_NO=' || order_no_  || '^';

FOR rec_ IN get_attachments LOOP

attachment_name_ := download_path_  ||  'attachment-' || rec_.doc_class || '_' || rec_.doc_no || '.' || extention_;

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 
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;
                                                                                                                                                                                                                                                            attachments_ := attachments_ || attachment_name_ || ';';
                                                                                                                                                                                                                                                                END LOOP;
                                                                                                                                                                                                                                                               END;

 

  1. SQL to generate the mail
DECLARE
                                                                                                                                                                                                                                                    to_user_            VARCHAR2(2000);
from_user_name_     VARCHAR2(200);
subject_            VARCHAR2(32000);
text_                        VARCHAR2(32000);

BEGIN

  subject_:= 'Customer Order Attachments';
  text_:= 'Attached Customer Order Details'  ;

  to_user_  := 'test@gmail.com';

    Command_SYS.MAIL
    (
       from_user_name_ => from_user_name_,
       to_user_name_   => to_user_ ,
       text_           =>  text_,
       subject_        =>  subject_ ,
       attach_         => attachments_ ||  '&PDF_FILE'
    );
END;

 

 

We are reaching out to the community to seek advice and insights into potential solutions for this issue. If you have experience with similar scenarios or have expertise in email attachment handling within the context of events, your input would be highly valuable.

DECLARE
   attachments_  Command_SYS.attachment_arr;
   pdf_   BLOB;
   doc_type_   edm_file_tab.doc_type%TYPE;

   file_ref_exist_   VARCHAR2(5);

   CURSOR get_documents IS
   SELECT dro.doc_class, dro.doc_no, dro.doc_sheet, dro.doc_rev
   FROM cust_order_inv_head_uiv_all cih, doc_reference_object dro
   WHERE cih.series_id||cih.invoice_no = '&PDF_PARAMETER_6'
      AND dro.lu_name = 'CustomerOrderInvHead'
      AND dro.key_ref = 'COMPANY='||cih.company||'^INVOICE_ID='||cih.invoice_id||'^'
      AND EXISTS (SELECT * FROM edm_file ef WHERE ef.doc_class = dro.doc_class AND ef.doc_no = dro.doc_no AND ef.doc_sheet = dro.doc_sheet)
   GROUP BY dro.doc_class, dro.doc_no, dro.doc_sheet, dro.doc_rev;

   CURSOR get_pdf IS
   SELECT pdf
   FROM PDF_ARCHIVE_TAB
   WHERE result_key = '&RESULT_KEY'
      AND print_job_id = '&PRINT_JOB_ID';

BEGIN

/* Get Invioce PDF from Report Archive */
   OPEN get_pdf;
   FETCH get_pdf INTO pdf_;
   CLOSE get_pdf;
   attachments_('&PDF_FILE_NAME').blob_ := pdf_;

/* Pick attachments connected to Invoice Head */
   FOR gt IN get_documents LOOP
      Edm_File_API.Reference_Exist(file_ref_exist_, gt.doc_class, gt.doc_no, gt.doc_sheet, gt.doc_rev, 'VIEW');
      IF file_ref_exist_ = 'TRUE' THEN 
         doc_type_ := 'VIEW';
      ELSE 
         doc_type_ := 'ORIGINAL';
      END IF;
      Edm_File_Op_Announce_API.Announce_File_Operation(gt.doc_class,gt.doc_no,gt.doc_sheet,gt.doc_rev,'READ');
      attachments_(Edm_File_API.Get_New_Local_File_Name(gt.doc_class, gt.doc_no, gt.doc_sheet, gt.doc_rev, doc_type_, '1')).blob_ := Edm_File_Storage_API.Get_File_Data(gt.doc_class, gt.doc_no, gt.doc_sheet, gt.doc_rev, doc_type_);
    END LOOP;


/* Send Email */
  Command_SYS.Mail(
   sender_        => 'IFSAPP',
   from_          => '&PDF_PARAMETER_1',
   to_list_       => '&PDF_PARAMETER_1',
   subject_       => 'Customer Invoice - ' || '&PDF_PARAMETER_6',
   text_          => 'INVOICE

Invoice: &PDF_PARAMETER_6

Dear &PDF_PARAMETER_4  

Please see attached Customer_Invoice &PDF_FILE_NAME 

Regards
IFS Applications

',
   attachments_   => attachments_);
END;

Here is an old example from APPS10. It is an event action to STD event PDF_REPORT_CREATED. It shows how to get the files and add them to an array of attachments and then send that into Command_SYS.Mail. The thing is that you really need to fetch the file contents as BLOB’s and add it to the array.

Be aware of that the join in the cursor get_documents is not good performance wise so I would recommend changing it if anyone wants to use it as is.

@NIMTLK 

Hi,

I think your current implementation does not work in IFS Cloud because the Batch_Transfer_Handler_Api is not fully working there, compared to Apps 10, and might never be. It's the part where you try to copy a file from the database to a folder that doesn't work.

Try out Tomas approach instead, I see it doesn't use the Batch_Transfer_Handler_Api.

Good luck!

 

Hi @Tomas Ruderfelt , This is indeed helpful for us and we started to work on it using your approach. Thank you so much and I will let you know if we can sort this. Thanks again...

Hi @Mathias Dahl , Thank you.

Hi @Tomas Ruderfelt , This is indeed helpful for us and we started to work on it using your approach. Thank you so much and I will let you know if we can sort this. Thanks again...

Hi @NIMTLK have you managed to solve your problem?
We are trying to do similar thing, and we get stuck. We are receiving mails, but without attachments aswell...

Reply


Terms & ConditionsCookie settings