Event action based on PDF_REPORT_CREATED event for Purchase Order report. Email is sent with purchase order report as well as a CSV containing line information

ifs_cloud_create_csv_and attach_to_email.sql

-- Author       : Damith Jinasena ([email protected])
-- Purpose      : Event action based on PDF_REPORT_CREATED event for Purchase Order report.
--                Email is sent with purchase order report as well as a CSV containing line information
-- App Version  : Apps10, IFS Cloud 2X RX

declare
  po_no_ VARCHAR2(100):=  '&PDF_PARAMETER_6';
  attachments_ COMMAND_SYS.ATTACHMENT_ARR;
  csv_header_ VARCHAR2(32000);
  csv_line_ VARCHAR2(32000);
  csv_file_ CLOB;
  file_name_  VARCHAR2(2000) := 'po_no_'|| po_no_ || '_order_lines.csv';
 mail_body_ VARCHAR2(32000);
  
  CURSOR get_po_lines IS
  select * from PURCHASE_ORDER_LINE_TAB
  where order_no = po_no_;  
begin

  -- Create CSV header columns
  csv_header_ := 'ORDER_NO;LINE_NO;RELEASE_NO;DESCRIPTION;BUY_QTY_DUE;BUY_UNIT_PRICE;STATE' ||chr(13)||chr(10);
  csv_file_ := csv_header_;
  -- Create the CSV
  FOR rec_ IN get_po_lines LOOP
    csv_line_ := NULL;
    csv_line_ := rec_.ORDER_NO || ';' || rec_.LINE_NO || ';' || rec_.RELEASE_NO || ';' || rec_.DESCRIPTION || ';' || rec_.BUY_QTY_DUE || ';' || rec_.BUY_UNIT_PRICE || ';' || rec_.ROWSTATE|| ';' ||chr(13)||chr(10);
    csv_file_ := csv_file_ || csv_line_;
  END LOOP;  
  
  --Add the CSV as an attachment
  COMMAND_SYS.Add_Attachment(attachments_ => attachments_,
                             filename_    => file_name_,
                             attachment_  => csv_file_);
  --Add the report PDF as an attachment                           
    COMMAND_SYS.Add_Attachment(attachments_ => attachments_,
                               filepath_    => '&PDF_FILE',
                               i_           => 1);
  -- Mail
 mail_body_ := 'Purchase order details from IFS';
  COMMAND_SYS.Mail(sender_      => 'IFSAPP',
                   from_        => 'IFSAPP',
                   to_list_     => '[email protected]',
                   subject_     => 'Purchase Order ' || po_no_,
                   text_        => mail_body_ ,
                   attachments_ => attachments_);
end;