Add Purchase Order Attachments to the Same Email , When Print Order

Thank you very much @EnzoFerrari .

Thread has given the answer for my question and I will give it a try.

That code is working and I had to do few modifications to fetch values.
Please find below code to use in PDF_REPORT_CREATED event action:

DECLARE

keyref_     VARCHAR2(4000);
extention_  VARCHAR2(260);

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 
from doc_reference_object a, edm_file b
where a.lu_name = 'PurchaseOrder'
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.doc_class between 4600 and 4699
and a.key_ref = keyref_;

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);
xml_               clob;
x_                 xmltype;

order_no_           VARCHAR2(20);
to_user_            VARCHAR2(2000);
from_user_name_     VARCHAR2(200);
supplier_id_        VARCHAR2(200);
subject_            VARCHAR2(32000);
text_                        VARCHAR2(32000);
company_name_      VARCHAR2(100);
company_                VARCHAR2(20);

BEGIN

select x.data 
 into xml_
 from  xml_report_data x 
 where x.result_key= '&RESULT_KEY';

  x_:= xmltype.createxml(xml_);

 SELECT extractvalue(x_, 'PURCHASE_ORDER_PRINT_REP/PO_HEADERS/PO_HEADER/ORDER_NO') INTO order_no_ FROM DUAL;
 SELECT extractvalue(x_, 'PURCHASE_ORDER_PRINT_REP/PO_HEADERS/PO_HEADER/SUPPLIER_NO') INTO supplier_id_ FROM DUAL;

 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;

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


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;

  subject_:= 'Purchase Order Report and Attachments';
  text_:= 'Report and Attachments of the Purchase Order are ready.';

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

COMMIT;

END; 

@MitDenukN which IFS version and update are you using? Looks like the event PDF_REPORT_CREATED generating more than 1 email to the recipient is an error in IFS 

@MitDenukN apologies for not being able to solve your last query. But I see you have initiated a new thread on that and a few options are suggested there. Hope you will soon get the overall clarification you are seeking. 

happy weekend! 

p.s. For anyone wondering where the new thread is, here’s the link. 

 

 Please refer and perhaps upvote the following idea: