EBS FORM ： 发送邮件API

功能：不同的发送类型---抄送、发送、密送。

 

 

 

 

 

CREATE OR REPLACE PACKAGE send_email IS

 

FUNCTION fnk_mail_test(p_header_id IN NUMBER,

ic_mail_from IN VARCHAR2, --1. Mail from

ic_title IN VARCHAR2, --4.title

ic_message IN VARCHAR2, --5. Message

ic_filename IN VARCHAR2) RETURN VARCHAR2;

 

END send_email;

/

CREATE OR REPLACE PACKAGE BODY send_email IS

 

PROCEDURE log(buff IN VARCHAR2) IS

BEGIN

fnd_file.put(fnd_file.log, buff);

fnd_file.new_line(fnd_file.log, 1);

EXCEPTION

WHEN OTHERS THEN

RETURN;

END log;

 

FUNCTION fnk_mail_test(p_header_id IN NUMBER,

ic_mail_from IN VARCHAR2, --1. Mail from

ic_title IN VARCHAR2, --4. title

ic_message IN VARCHAR2, --5. Message

ic_filename IN VARCHAR2) RETURN VARCHAR2 IS

cc_error CONSTANT CHAR(1) := '1'; --Return Code Value Error

cc_ok CONSTANT CHAR(1) := '0'; --Return Code Value Success

--Declare Variable

lv_mailtitle VARCHAR2(250); --Mail Title

lv_mailtext VARCHAR2(2550); --Content of Mail

lv_mailto VARCHAR2(2550); --All Address of the Recipients---all

lv_mailto_to VARCHAR2(2550); --All Address of the Recipients---to

lv_mailto_cc VARCHAR2(2550); --All Address of the Recipients---cc

 

lv_mailfrom VARCHAR2(100) := 'out-xuxiaoxiao@cn.panasonic.com'; --Sender Address

lv_smtpserver VARCHAR2(30) := 'smtp.cn.panasonic.com'; --'10.195.74.239'; --'129.249.64.64' --SMTP Server Name or IP

ln_smtpport NUMBER(10) := 25; --SMTP Server Port

lc_return CHAR(1); --Return Code

c_mail utl_smtp.connection;

lv_directory_name VARCHAR2(100);

lv_file_name VARCHAR2(100);

lv_file_handle utl_file.file_type;

lv_line VARCHAR2(1000);

ln_slash_pos NUMBER;

crlf VARCHAR2(2) := chr(13) || chr(10);

mesg VARCHAR2(32767);

mesg_len NUMBER;

max_size NUMBER DEFAULT 9999999999;

mesg_length_exceeded BOOLEAN := FALSE;

mesg_too_long EXCEPTION;

debug NUMBER DEFAULT 0;

err_msg VARCHAR2(200);

error_msg VARCHAR2(250);

--所有的邮箱

CURSOR c_get_mail_address IS

SELECT email

FROM zzom_send_email

WHERE header_id = p_header_id

AND enabled_flag = 'Y'

AND SYSDATE BETWEEN nvl(effective_start_date, SYSDATE - 1) AND nvl(effective_end_date, SYSDATE + 1);

--发送

CURSOR c_get_mail_address_to IS

SELECT email

FROM zzom_send_email

WHERE header_id = p_header_id

AND send_type = '发送'

AND enabled_flag = 'Y'

AND SYSDATE BETWEEN nvl(effective_start_date, SYSDATE - 1) AND nvl(effective_end_date, SYSDATE + 1);

--抄送

CURSOR c_get_mail_address_cc IS

SELECT email

FROM zzom_send_email

WHERE header_id = p_header_id

AND send_type = '抄送'

AND enabled_flag = 'Y'

AND SYSDATE BETWEEN nvl(effective_start_date, SYSDATE - 1) AND nvl(effective_end_date, SYSDATE + 1);

BEGIN

log('12311111');

--Get Mail Title

lv_mailtitle := 'Subject:' || ic_title;

--Get Mail Text

lv_mailtext := ic_message;

log('12311112');

--Connection

c_mail := utl_smtp.open_connection(lv_smtpserver, ln_smtpport);

log('12311113');

--Set SMTP Server

utl_smtp.helo(c_mail, lv_smtpserver);

log('12311114' || lv_mailfrom);

--Set Sender Address

IF ic_mail_from IS NOT NULL THEN

lv_mailfrom := ic_mail_from;

END IF;

log('12311115' || lv_mailfrom);

utl_smtp.mail(c_mail, lv_mailfrom);

log('123' || lv_mailtitle);

--Set Recipients Address--for all

--获取全部收件人地址-并发送

FOR v_get_mail_address IN c_get_mail_address

LOOP

utl_smtp.rcpt(c_mail, v_get_mail_address.email);

lv_mailto := lv_mailto || v_get_mail_address.email || ';';

END LOOP;

--获取发送地址

FOR v_get_mail_address_to IN c_get_mail_address_to

LOOP

lv_mailto_to := lv_mailto_to || v_get_mail_address_to.email || ';';

END LOOP;

--获取抄送地址

FOR v_get_mail_address_cc IN c_get_mail_address_cc

LOOP

lv_mailto_cc := lv_mailto_cc || v_get_mail_address_cc.email || ';';

END LOOP;

 

log('124' || lv_mailto);

utl_smtp.rcpt(c_mail, lv_mailfrom);

--Open Data

utl_smtp.open_data(c_mail);

mesg := 'Date: ' || to_char(SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf || 'From: ' || lv_mailfrom || crlf;

--|| lv_mailtitle || crlf ||

utl_smtp.write_data(c_mail, mesg);

utl_smtp.write_raw_data(c_mail, utl_raw.cast_to_raw(convert(lv_mailtitle || crlf, 'ZHS16CGB231280')));

mesg := 'To: ' || lv_mailto_to || crlf || 'Cc: ' || lv_mailto_cc || crlf || 'Mime-Version: 1.0' || crlf ||

'Content-Type: multipart/mixed; boundary="DMW.Boundary.605592468"' || crlf || '' || crlf ||

'--DMW.Boundary.605592468' || crlf ||

 

--US-ASCII

'Content-Type: text/plain; name="lv_mailtext.txt"; charset=GB2312' || crlf ||

'Content-Disposition: inline; filename="lv_mailtext.txt"' || crlf || 'Content-Transfer-Encoding: 32bit' || crlf || '' || crlf;

utl_smtp.write_data(c_mail, mesg);

utl_smtp.write_raw_data(c_mail, utl_raw.cast_to_raw(convert(utl_tcp.crlf || lv_mailtext, 'ZHS16CGB231280')));

--=============================================================

-- reade file

--=============================================================

IF ic_filename IS NOT NULL THEN

BEGIN

-- locate the final '/' or '' in the pathname ...

ln_slash_pos := instr(ic_filename, '/', -1);

IF ln_slash_pos = 0 THEN

ln_slash_pos := instr(ic_filename, '', -1);

END IF;

-- separate the filename from the directory name ...

lv_directory_name := substr(ic_filename, 1, ln_slash_pos - 1);

lv_file_name := substr(ic_filename, ln_slash_pos + 1);

-- open the file ...

lv_file_handle := utl_file.fopen(lv_directory_name, lv_file_name, 'r');

mesg := crlf || '--DMW.Boundary.605592468' || crlf ||

'Content-Type: application/octet-stream; name="' || lv_file_name || '"' || crlf ||

'Content-Disposition: attachment; filename="' || lv_file_name || '"' || crlf ||

'Content-Transfer-Encoding: 16bit' || crlf || crlf;

mesg_len := mesg_len + length(mesg);

--utl_smtp.write_data ( c_mail, mesg );

utl_smtp.write_raw_data(c_mail, utl_raw.cast_to_raw(convert(utl_tcp.crlf || mesg, 'ZHS16CGB231280')));

-- and append the file contents to the end of the message ...

LOOP

utl_file.get_line(lv_file_handle, lv_line);

IF mesg_len + length(lv_line) > max_size THEN

mesg := '*** truncated ***' || crlf;

--utl_smtp.write_data ( c_mail, mesg );

utl_smtp.write_raw_data(c_mail, utl_raw.cast_to_raw(convert(utl_tcp.crlf || mesg, 'ZHS16CGB231280')));

mesg_length_exceeded := TRUE;

RAISE mesg_too_long;

END IF;

mesg := lv_line || crlf;

--utl_smtp.write_data ( c_mail, mesg );

utl_smtp.write_raw_data(c_mail, utl_raw.cast_to_raw(convert(mesg, 'ZHS16CGB231280')));

mesg_len := mesg_len + length(mesg);

END LOOP;

EXCEPTION

WHEN utl_file.invalid_path THEN

err_msg := SQLERRM;

IF debug > 0 THEN

dbms_output.put_line('Error in opening attachment ' || ic_filename || ':' || err_msg);

END IF;

-- All other exceptions are ignored ....

WHEN OTHERS THEN

NULL;

END;

END IF;

mesg := crlf;

--utl_smtp.write_data ( c_mail, mesg );

utl_smtp.write_raw_data(c_mail, utl_raw.cast_to_raw(convert(utl_tcp.crlf || mesg, 'ZHS16CGB231280')));

--Close Data

utl_smtp.close_data(c_mail);

--Quit UTL_SMTP

utl_smtp.quit(c_mail);

--Return Success

lc_return := cc_ok;

RETURN(lc_return);

EXCEPTION

WHEN OTHERS THEN

error_msg := SQLERRM;

lc_return := cc_error;

RETURN(lc_return);

END fnk_mail_test;

END;

/