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

ifs_cloud_send_email_with_doc_attachments_and_media_items.sql

-- Author       : Damith Jinasena ([email protected])
-- 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_     => '[email protected]',
                   subject_     => '&NOTES',
                   text_        => mail_body_,
                   attachments_ => attachments_);

end;