How to Send an E-Mail with CSV Attached with html E-Mail Body in IFS
- 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:






Comments