EBS FORM ： 发送邮件API
功能：不同的发送类型---抄送、发送、密送。CREATEORREPLACEPACKAGE send_email IS
FUNCTION fnk_mail_test(p_header_idINNUMBER,
ic_mail_from INVARCHAR2,--1. Mail from
ic_title INVARCHAR2,--4.title
ic_message INVARCHAR2,--5. Message
ic_filenameINVARCHAR2)RETURNVARCHAR2;
END send_email;
/
CREATEORREPLACEPACKAGEBODY send_email IS
PROCEDURElog(buff INVARCHAR2)IS
BEGIN
fnd_file.put(fnd_file.log, buff);
fnd_file.new_line(fnd_file.log,1);
EXCEPTION
WHENOTHERSTHEN
RETURN;
ENDlog;
FUNCTION fnk_mail_test(p_header_idINNUMBER,
ic_mail_from INVARCHAR2,--1. Mail from
ic_title INVARCHAR2,--4. title
ic_message INVARCHAR2,--5. Message
ic_filenameINVARCHAR2)RETURNVARCHAR2IS
cc_error CONSTANTCHAR(1):='1';--Return Code Value Error
cc_ok CONSTANTCHAR(1):='0';--Return Code Value Success
--Declare Variable
lv_mailtitle VARCHAR2(250);--Mail Title
lv_mailtextVARCHAR2(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 NUMBERDEFAULT9999999999;
mesg_length_exceeded BOOLEAN:=FALSE;
mesg_too_long EXCEPTION;
debugNUMBERDEFAULT0;
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'
ANDSYSDATEBETWEENnvl(effective_start_date,SYSDATE-1)ANDnvl(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'
ANDSYSDATEBETWEENnvl(effective_start_date,SYSDATE-1)ANDnvl(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'
ANDSYSDATEBETWEENnvl(effective_start_date,SYSDATE-1)ANDnvl(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 ISNOTNULLTHEN
lv_mailfrom := ic_mail_from;
ENDIF;
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 ||';';
ENDLOOP;
--获取发送地址
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 ||';';
ENDLOOP;
--获取抄送地址
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 ||';';
ENDLOOP;
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 ISNOTNULLTHEN
BEGIN
-- locate the final '/' or '\' in the pathname ...
ln_slash_pos :=instr(ic_filename,'/',-1);
IF ln_slash_pos =0THEN
ln_slash_pos :=instr(ic_filename,'\',-1);
ENDIF;
-- 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;
ENDIF;
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);
ENDLOOP;
EXCEPTION
WHEN utl_file.invalid_path THEN
err_msg :=SQLERRM;
IFdebug>0THEN
dbms_output.put_line('Error in opening attachment '|| ic_filename ||':'|| err_msg);
ENDIF;
-- All other exceptions are ignored ....
WHENOTHERSTHEN
NULL;
END;
ENDIF;
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
WHENOTHERSTHEN
error_msg :=SQLERRM;
lc_return := cc_error;
RETURN(lc_return);
END fnk_mail_test;
END;
/
页:
[1]