May I know my procedure any problem? I try to run the procedure and it works only for "CC" list receivers but "TO" list receivers cannot to receive the email. Any body can help me out will be great appreciate! Please provide me modified code.
create or replace PROCEDURE check_ap_reject_list (errbuf VARCHAR2, retcode NUMBER) IS
CURSOR c1 IS
SELECT distinct b.NAME AP_ORG, a.ORG_ID ORG_ID, a.source source,
a.INVOICE_ID INVOICE_ID,reject_lookup_code Reject_Reason,
a.INVOICE_NUM Invoice_Num, a.INVOICE_DATE Invoice_Date, a.GL_DATE GL_Date,
a.VENDOR_NAME Vendor_Name, a.VENDOR_SITE_CODE Vendor_Site_Code,
a.INVOICE_AMOUNT INVOICE_AMOUNT, a.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE, a.EXCHANGE_RATE EXCHANGE_RATE
FROM apps.ap_interface_rejections,
apps.ap_invoices_interface a,
apps.hr_all_organization_units b,
apps.ap_invoice_lines_interface d
WHERE (((parent_id = a.invoice_id AND parent_table = 'AP_INVOICES_INTERFACE') OR (parent_id = d.invoice_line_id AND parent_table = 'AP_INVOICE_LINES_INTERFACE')))
AND a.INVOICE_ID = d.INVOICE_ID
AND a.ORG_ID = b.ORGANIZATION_ID
AND a.source = 'RMS'
;
r1 c1%rowtype;
c utl_smtp.connection;
v_instance varchar2(9);
x_email_to varchar2(4000);
x_email_cc varchar2(4000);
v_temp_str varchar2(4000);
v_temp number;
v_r1_row_num number;
v_check number;
PROCEDURE send_data(p_data in varchar2) as
BEGIN
utl_smtp.write_raw_data(c, utl_raw.cast_to_raw(p_data || utl_tcp.crlf));
--fnd_file.put_line(fnd_file.output, p_data);
END;
BEGIN
SELECT count(distinct a.INVOICE_NUM)
into v_check
FROM apps.ap_interface_rejections, apps.ap_invoices_interface a, apps.hr_all_organization_units b, apps.ap_invoice_lines_interface d
WHERE (((parent_id = a.invoice_id AND parent_table = 'AP_INVOICES_INTERFACE') OR (parent_id = d.invoice_line_id AND parent_table = 'AP_INVOICE_LINES_INTERFACE')))
AND a.INVOICE_ID = d.INVOICE_ID
AND a.ORG_ID = b.ORGANIZATION_ID
AND a.source = 'RMS';
IF v_check > 0 THEN
select name into v_instance from V$DATABASE;
x_email_to := '[email protected], [email protected], [email protected]';
x_email_cc := '[email protected], [email protected]';
c := utl_smtp.open_connection('LCEX7',25);
--utl_smtp.open_connection('smtp.office365.com',587);
--utl_smtp.helo(c, 'LCEX7');
utl_smtp.helo(c, 'LCEX7');
utl_smtp.mail(c, '[email protected]');
-- utl_smtp.rcpt( c, x_email_to);
-- utl_smtp.rcpt( c, x_email_cc);
v_temp_str := replace(x_email_to, ',', ';');
v_temp_str := replace(x_email_cc, ',', ';');
while length(v_temp_str) > 0 loop
v_temp := instr(v_temp_str, ';', 1, 1);
if v_temp > 0 then
utl_smtp.rcpt(c, trim(substr(v_temp_str, 1, v_temp - 1)));
v_temp_str := substr(v_temp_str, v_temp 1, length(v_temp_str));
else
utl_smtp.rcpt(c, trim(v_temp_str));
v_temp_str := null;
end if;
end loop;
utl_smtp.open_data(c);
utl_smtp.write_data(c,'From: "Oracle Fin (' || v_instance ||') " <[email protected]>' || utl_tcp.CRLF);
utl_smtp.write_data(c, 'To: ' || x_email_to || utl_tcp.CRLF);
utl_smtp.write_data(c, 'Cc: ' || x_email_cc || utl_tcp.CRLF);
utl_smtp.write_data(c, 'Subject: AP invoice reject list ' || to_char(sysdate,'MM/dd/yyyy HH:mm:ss') || utl_tcp.CRLF);
utl_smtp.write_data(c, 'MIME-Version:' || '1.0' || utl_tcp.CRLF);
utl_smtp.write_data(c,'Content-Type: ' || 'text/html; charset=utf-8' ||utl_tcp.CRLF);
utl_smtp.write_data(c, 'Content-Transfer-Encoding: ' || '"8Bit"' || utl_tcp.CRLF);
send_data('<html><body>');
send_data('<p>Dear All,</p>');
send_data('<p>Please find the enclosed AP reject list and advise. Thanks!</p>');
send_data('<table border=''0'' width=''100%'' cellpadding=''1'' cellspacing=''1''>');
send_data('<tr style=''font-size:75%;font-weight:bold;''>');
send_data('<td>AP ORG</td>
<td>ORG ID</td>
<td>Source</td>
<td>INVOICE ID</td>
<td>Reject Reason</td>
<td>Invoice Num</td>
<td>Invoice Date</td>
<td>GL Date</td>
<td>Vendor Name</td>
<td>Vendor Site Code</td>
<td>INVOICE AMOUNT</td>
<td>INVOICE CURRENCY CODE</td>
<td>EXCHANGE RATE</td>');
v_r1_row_num := 0;
open c1;
loop
fetch c1
into r1;
exit when c1%notfound;
if (mod(v_r1_row_num,2)=0) then
send_data('<tr style=''font-size:75%;background-color:#E0EBFF;''>');
else
send_data('<tr style=''font-size:75%;''>');
end if;
send_data('<td>' || r1.AP_ORG || '</td>
<td>' || r1.ORG_ID || '</td>
<td>' || r1.source || '</td>
<td>' || r1.INVOICE_ID || '</td>
<td>' || r1.Reject_Reason || '</td>
<td>' || r1.Invoice_Num || '</td>
<td>' || r1.Invoice_Date || '</td>
<td>' || r1.GL_Date || '</td>
<td>' || r1.Vendor_Name || '</td>
<td>' || r1.Vendor_Site_Code|| '</td>
<td>' || r1.INVOICE_AMOUNT || '</td>
<td>' || r1.INVOICE_CURRENCY_CODE || '</td>
<td>' || r1.EXCHANGE_RATE || '</td></tr>');
v_r1_row_num := v_r1_row_num 1;
END LOOP;
CLOSE c1;
send_data('</table>');
send_data('<p>Best Regards,</p>');
send_data('<p>Technology Department</p>');
send_data('</body></html>');
utl_smtp.close_data(c);
utl_smtp.quit(c);
end if ;
END check_ap_reject_list;
CodePudding user response：
You need a RCPT for each email recipient (whether CC or TO), and then a line with "To" and "Cc" where appropriatein the email data. So some pseudo code would be along the lines of:
declare
x_email_to sys.odcivarchar2list := sys.odcivarchar2list('[email protected], [email protected], [email protected]');
x_email_cc sys.odcivarchar2list := sys.odcivarchar2list('[email protected], [email protected]');
begin
...
... normal email initialisation (open,helo,etc)
...
for i in 1 .. x_email_to.count loop
utl_smtp.rcpt(l_conn, x_email_to(i));
end loop;
for i in 1 .. x_email_cc.count loop
utl_smtp.rcpt(l_conn, x_email_cc(i));
end loop;
for i in 1 .. x_email_to.count loop
utl_smtp.write_data(c, 'To: ' || x_email_to(i) || utl_tcp.CRLF);
end loop;
for i in 1 .. x_email_cc.count loop
utl_smtp.write_data(c, 'Cc: ' || x_email_cc(i) || utl_tcp.CRLF);
end loop;
...
... rest of email
...
end;
I'm using an array so it easy to loop through each email address. In your case, you can just parse the string looking for each comment, but the concept is the same.
- One RCPT per recipient
- One write_date per CC and TO
CodePudding user response：
Connor's answer is spot on if you must use the utl_smtp package.
Unless you have a need to directly operate against the SMTP protocol, however, that's a relatively cumbersome way to send an email. In older versions of Oracle, that was the way you had to send emails. Even back in the 10g days, Oracle had the utl_mail.send procedure that let you pass in comma-separated lists of recipients for the to, cc, and bcc lists along with an HTML message
utl_mail.send( sender => '[email protected]',
recipients => x_email_to,
cc => x_email_cc,
subject => 'Subject: AP invoice reject list ' ||
to_char(sysdate,'MM/dd/yyyy HH:mm:ss'),
message => x_html_document,
mime_type => 'text/html' );
No need to worry about appending all the CRLF's manually, no need to make dozens of calls to write_data and/or write_raw_data. Just build the HTML document you want to send in a local variable and send it. Oracle already built the infrastructure for 99% of use cases.
If you happen to have APEX installed in your database (even if you don't have an APEX application), you can also use the apex_mail package which has a similar send package. That version simplifies the process if you want to send a HTML and a plain text version of the same email so that users whose email clients don't support HTML can view it. And since apex_mail enqueues messages to be sent rather than talking directly to the SMTP server, apex_mail calls are transactional so they'll roll back if the parent transaction fails which is nice to avoid spamming internal users if this is a process that can be retried in the event of an unexpected error.