EDM File - Attach to email using command_sys

Hi @JannetteC 

If you are sending mails for different POs inside a loop, you need to clear the attachment array first. Can you share your code, so it could be easy to find what’s wrong :)

DECLARE

info_ varchar2(200) :=null ;
email_addr_ VARCHAR2(2000) :=null ;
file_name_ varchar2(32000)  :=null ;
email_body_ VARCHAR2(2000)  :=null ;
error_message_ VARCHAR2(32000)  :=null ;
attachments_  ifsapp.COMMAND_SYS.ATTACHMENT_ARR;

CURSOR get_inv
Is

SELECT po_ref_number, invoice_id, company, invoice_no
, ifsapp.purchase_order_cfp.Get_Cf$_Buyer_Name(ifsapp.purchase_order_cfp.Get_Objkey(ifsapp.man_supp_invoice_api.Get_Purchase_Order_Ref_Number(company,invoice_id))) Buyers_name
, ifsapp.purchase_order_api.Get_Buyer_Code(po_ref_number) Buyers_code
, dr.doc_class, dr.doc_no, dr.doc_rev, dr.doc_sheet, edm.file_data, edm.doc_type
FROM ifsapp.POSTING_PROPOSAL_INV_OVERVIEW pp Left Outer Join ifsapp.doc_reference_object dr On dr.Lu_Name='ManSuppInvoice' and dr.key_ref =  'COMPANY='||company||'^INVOICE_ID='||invoice_id||'^'
    left outer join ifsapp.edm_file_storage edm On edm.DOC_CLASS= dr.doc_class and edm.DOC_NO = dr.doc_no and edm.dOC_SHEET=dr.doc_sheet and edm.DOC_REV=dr.doc_rev
where message_id='No Arrival' --and po_ref_number not like 'E%' 
--and po_ref_numBer In ('C776839','C778141','C775591','C774557','C771026');
and po_ref_numBer In ('C776839','C775591','C774557');

BEGIN
  For rec in get_inv loop

  email_addr_ := ifsapp.fnd_user_property_api.get_value(rec.Buyers_code ,'SMTP_MAIL_ADDRESS' ) ;

      
     file_name_ := ifsapp.Edm_File_Util_API.Generate_Docman_File_Name_( rec.doc_class, rec.doc_no, rec.doc_sheet, rec.doc_rev, rec.doc_type);
     ifsapp.COMMAND_SYS.Add_Attachment(attachments_, file_name_, file_data_);

         ifsapp.COMMAND_SYS.Mail(sender_      => 'IFSAPP',
                   from_        => 'IFSAPP',
                   to_list_     => 'xxxxxxxx' ,
                   cc_list_  => info_ ,
                   bcc_list_ => info_ ,
                   subject_     => 'No Arrivals for PO: ' || rec.po_ref_Number || ', Invoice: ' || rec.invoice_no ,
                   text_        => 'The following invoice/s have been received and the receipts have not yet been received.  Please receipt in so the invoices can be matched as the invoice will not be paid until received.  <br /> 
                                   Regards <br /> Accounts Payable' ,
                   attachments_ => attachments_);
                   
  If Nvl(email_addr_ ,'NO EMAIL') != 'NO EMAIL' Then
   Begin
     ifsapp.Transaction_SYS.Log_Status_Info('PO Ref: ' || rec.po_ref_Number || ', Invoice: ' || rec.invoice_no || ' email sent to ' || email_addr_ , 'INFO');
     exception when others then
     error_message_ := 'PO Ref: ' || rec.po_ref_Number || ', Invoice: ' || rec.invoice_no || ' - '||substr(SQLERRM, 1, 1990);
     ifsapp.Transaction_SYS.Log_Status_Info(error_message_ , 'WARNING');
   End ;
   
   End If ; 
   
  End Loop;
  
  End;

Hi @dsj 

Perfect, fixed my issue