top of page

How to Send an E-Mail with CSV Attached with html E-Mail Body in IFS

  • Writer: Rumesh Aponso (RMAX)
    Rumesh Aponso (RMAX)
  • Nov 7, 2024
  • 2 min read

Updated: Nov 13, 2024

-- IFS APP10
DECLARE
   new_line_               VARCHAR2(2) := CHR(13) || CHR(10);
   info_                   VARCHAR2(32000);
   attr_                   VARCHAR2(32000);
   objid_                  VARCHAR2(100);
   objversion_             VARCHAR2(100);
   alert_id_               NUMBER;
   email_from_             VARCHAR2(500) := Fnd_Session_API.Get_Fnd_User();
   email_to_               VARCHAR2(4000);
   email_subject_          VARCHAR2(500);
   email_body_             VARCHAR2(32000);
   users_notified_         VARCHAR2(4000);
   data_found_             BOOLEAN := FALSE;
   timestamp_              VARCHAR2(20) := TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS');
   db_name_                VARCHAR2(20) := Sys_Context('USERENV', 'DB_NAME');
   file_opened_            BOOLEAN;
   file_closed_            BOOLEAN;
   file_handle_            UTL_FILE.FILE_TYPE;
   file_dir_               CONSTANT all_directories.directory_name%TYPE := 'OUT_DIR';
   filename_               VARCHAR2(200)  := timestamp_ || '_rmax_test.csv';
   file_content_           CLOB;
   attachments_            COMMAND_SYS.ATTACHMENT_ARR;
   line_                   VARCHAR2(4000) := NULL;
   line_count_             NUMBER;
      
   CURSOR get_data IS
      SELECT t.order_no, t.contract, t.customer_no
      FROM   customer_order_tab t
      WHERE  t.date_entered > SYSDATE - 1;
BEGIN
   email_to_   := 'abcabc@rmax.com';
   email_body_ := NULL;
            
   -- set file_opened_ flag to default
   file_opened_ := FALSE;
   file_closed_ := FALSE;
   
   -- initialize counts
   line_count_ := 0;
   
   -- open file handle
   IF (file_opened_ = FALSE) THEN
      file_handle_ := UTL_FILE.FOPEN(file_dir_, filename_, 'w', 32767);
      file_opened_ := TRUE;
   
      -- set header line
      line_         := 'Order No;Contract;Customer No;';
               
      -- set attachment file content
      file_content_ := 'Order No;Contract;Customer No;' || new_line_;
               
      -- add header line to file
      UTL_FILE.PUT_LINE(file_handle_, line_);
               
      -- set email header line
      email_body_ := email_body_ || '<style> table { border: 2px solid black; } th, td { border: 1px solid gray; text-align: left; } </style>';
      email_body_ := email_body_ || '<table><tr>';
      email_body_ := email_body_ || '<th>Line No</th>'  ||
                                    '<th>Order No</th>' ||
                                    '<th>Contract</th>' ||
                                    '<th>Customer No</th>';
      email_body_ := email_body_ || '</tr>';
   END IF;
            
   FOR data_rec_ IN get_data LOOP
      -- set data found flag
      IF (NOT data_found_) THEN
         data_found_ := TRUE;
      END IF;
   
      -- increment line count
      line_count_ := line_count_ + 1;
               
      -- set line
      line_ := line_count_           || ';' || -- Line No
               data_rec_.order_no    || ';' || -- Order No
               data_rec_.contract    || ';' || -- Contract
               data_rec_.customer_no || ';';   -- Customer No
               
      -- set attachment file content
      file_content_ := file_content_ || line_ || new_line_;
               
      -- add line to file
      UTL_FILE.PUT_LINE(file_handle_, line_);
   
      -- set email header line
      email_body_ := email_body_ || '<tr>';
      email_body_ := email_body_ || '<td>' || line_count_           || '</td>'; -- Line No
      email_body_ := email_body_ || '<td>' || data_rec_.order_no    || '</td>'; -- Order No
      email_body_ := email_body_ || '<td>' || data_rec_.contract    || '</td>'; -- Contract
      email_body_ := email_body_ || '<td>' || data_rec_.customer_no || '</td>'; -- Customer No
      email_body_ := email_body_ || '</tr>';
   END LOOP;
            
   IF (file_opened_) THEN
      -- close and save the file
      UTL_FILE.FCLOSE(file_handle_);
   
      -- set file closed flag
      file_closed_ := TRUE;
   END IF;
            
   -- send email
   IF (file_closed_ AND data_found_) THEN
      email_subject_  := 'RMAX Test E-Mail';
               
      -- add attachment
      Command_SYS.Add_Attachment(attachments_, filename_, file_content_);
               
      -- send email
      Command_SYS.Mail(sender_      => email_from_,
                       from_        => email_from_,
                       to_list_     => email_to_,
                       subject_     => email_subject_,
                       text_        => email_body_,
                       attachments_ => attachments_);
   END IF;
END;

Output:

ree
ree

Comments


Copyright © 2025 RMAXOneNote

  • Online CV
  • LinkedIn
  • Youtube
  • GitHub
  • Blogger
bottom of page