邮件公共包包体

CREATE OR REPLACE PACKAGE BODY "XXTP_HTML_MAIL_PKG" IS

p_Crlf VARCHAR2(20) := Chr(13) || Chr(10);

FUNCTION Validate_Email_Address(p_Email_Address VARCHAR2) RETURN VARCHAR2 IS
BEGIN
IF Regexp_Like(p_Email_Address,
'^\w+([\.-]?\w+)*@\w+([\.-]?\w+)*(\.\w{2,3})+$') THEN
RETURN 'T';
ELSE
RETURN 'F';
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END Validate_Email_Address;

--get receiver
PROCEDURE Get_Receiver(i_Receiver IN VARCHAR2,
x_Nei_Receiver OUT VARCHAR2,
x_Wai_Receiver OUT VARCHAR2) IS
Receiver VARCHAR2(4000) := i_Receiver;
v_Rec VARCHAR2(4000);
j NUMBER := 1;
i NUMBER := 1;
p_Suffix VARCHAR2(4000);
v_Receiver VARCHAR2(4000) := NULL;
p_Receiver VARCHAR2(4000) := NULL;
w_Receiver VARCHAR2(4000) := NULL;

BEGIN
SELECT Regexp_Replace(Receiver, '，', ',') INTO v_Rec FROM Dual;
IF Substr(v_Rec, Length(v_Rec)) <> ',' THEN
v_Rec := v_Rec || ',';
END IF;

WHILE j < Length(v_Rec) LOOP
p_Suffix := Substr(v_Rec,
Instr(v_Rec, '@', 1, i) + 1,
Instr(v_Rec, ',', 1, i) - Instr(v_Rec, '@', 1, i) - 1);

v_Receiver := Substr(v_Rec, j, Instr(v_Rec, ',', 1, i) - j);
IF p_Suffix IN ('tp-link.net') THEN
SELECT Decode(p_Receiver,
NULL,
v_Receiver,
p_Receiver || ',' || v_Receiver)
INTO p_Receiver
FROM Dual;
ELSE
SELECT Decode(w_Receiver,
NULL,
v_Receiver,
w_Receiver || ',' || v_Receiver)
INTO w_Receiver
FROM Dual;
END IF;
SELECT Instr(v_Rec, ',', 1, i) INTO j FROM Dual;
j := j + 1;
i := i + 1;
END LOOP;
x_Nei_Receiver := p_Receiver;
x_Wai_Receiver := w_Receiver;
EXCEPTION
WHEN OTHERS THEN
Dbms_Output.Put_Line('error');
END;
--chack information
PROCEDURE Info_Check(p_Error_Info_Tbl OUT Error_Info_Tbl_Type) IS
CURSOR Cur_Mail IS
SELECT * FROM Xxtp_Mail_Tmp;
CURSOR Cur_Header IS
SELECT * FROM Xxtp_Mail_Header_Tmp;
CURSOR Cur_Line IS
SELECT * FROM Xxtp_Mail_Line_Tmp;

p_Header_Num NUMBER;
p_Line_Num NUMBER;
p_Fonts_Type VARCHAR2(240);

i NUMBER;
j NUMBER;
BEGIN
FOR Mycur IN Cur_Mail LOOP
/* IF Mycur.Subject IS NULL THEN
p_Error_Info_Tbl(p_Error_Info_Tbl.Count) := '错误' ||
p_Error_Info_Tbl.Count ||
'：主题为空！';
END IF;*/
IF Mycur.Receiver IS NULL AND
Mycur.Copy_Receiver IS NULL THEN
p_Error_Info_Tbl(p_Error_Info_Tbl.Count) := '错误' ||
p_Error_Info_Tbl.Count ||
'：没有接收人！';
END IF;
END LOOP;

SELECT COUNT(1) INTO p_Header_Num FROM Xxtp_Mail_Header_Tmp;
SELECT COUNT(1) INTO p_Line_Num FROM Xxtp_Mail_Line_Tmp;

IF p_Header_Num = 0 AND
p_Line_Num = 0 THEN
p_Error_Info_Tbl(p_Error_Info_Tbl.Count) := '错误' ||
p_Error_Info_Tbl.Count ||
':邮件内容为空！';
END IF;
--check header
FOR Mycur IN Cur_Header LOOP
i := 1;
j := 1;
p_Fonts_Type := Mycur.Fonts_Type;
IF Substr(Mycur.Fonts_Type, Length(p_Fonts_Type)) <> ',' THEN
p_Fonts_Type := p_Fonts_Type || ',';
END IF;
WHILE j < Length(p_Fonts_Type) LOOP
IF (Substr(p_Fonts_Type, j, Instr(p_Fonts_Type, ',', 1, i) - j)) NOT IN
('斜体', '粗体', '加删除线', '加下划线') THEN
p_Error_Info_Tbl(p_Error_Info_Tbl.Count) := '错误' ||
p_Error_Info_Tbl.Count ||
':不存在 ' ||
Mycur.Fonts_Type ||
' 格式';
END IF;
SELECT Instr(p_Fonts_Type, ',', 1, i) INTO j FROM Dual;
j := j + 1;
i := i + 1;
END LOOP;

END LOOP;
--check line
FOR Mycur IN Cur_Line LOOP
i := 1;
j := 1;
p_Fonts_Type := Mycur.Fonts_Type;
IF Substr(Mycur.Fonts_Type, Length(p_Fonts_Type)) <> ',' THEN
p_Fonts_Type := p_Fonts_Type || ',';
END IF;
WHILE j < Length(p_Fonts_Type) LOOP
IF (Substr(p_Fonts_Type, j, Instr(p_Fonts_Type, ',', 1, i) - j)) NOT IN
('斜体', '粗体', '加删除线', '加下划线') THEN
p_Error_Info_Tbl(p_Error_Info_Tbl.Count) := '错误' ||
p_Error_Info_Tbl.Count ||
':不存在 ' ||
Substr(p_Fonts_Type,
j,
Instr(p_Fonts_Type,
',',
1,
i) - j) ||
' 格式';
END IF;
SELECT Instr(p_Fonts_Type, ',', 1, i) INTO j FROM Dual;
j := j + 1;
i := i + 1;
END LOOP;

END LOOP;

END;

--personalization
PROCEDURE Personalization(p_Size IN VARCHAR2,
p_Fonts IN VARCHAR2,
p_Color IN VARCHAR2,
p_Fonts_Type IN VARCHAR2,
p_Style IN OUT VARCHAR2) IS
v_Size VARCHAR2(240) := NULL;
v_Fonts VARCHAR2(240) := NULL;
v_Color VARCHAR2(240) := NULL;
v_Fonts_Type VARCHAR2(240) := p_Fonts_Type;
v_Type VARCHAR2(240) := NULL;
v_Chr VARCHAR2(240) := NULL;
v_Num NUMBER;
Fonts_Type VARCHAR2(4000) := NULL;
BEGIN
v_Size := 'font-size:' || Nvl(p_Size, 12) || 'px;';
v_Fonts := 'font-family:' || Nvl(p_Fonts, '宋体') || ';';
v_Color := 'color:' || Nvl(Lower(p_Color), 'black') || ';';

IF Substr(v_Fonts_Type, Length(v_Fonts_Type)) <> ',' THEN
v_Fonts_Type := v_Fonts_Type || ',';
END IF;
v_Num := Nvl(Length(Regexp_Replace(v_Fonts_Type, '[^,]+', '')), 0);
FOR i IN 1 .. v_Num LOOP
IF i = 1 THEN
v_Chr := Substr(v_Fonts_Type, 1, Instr(v_Fonts_Type, ',', 1, 1) - 1);
ELSE
v_Chr := Substr(v_Fonts_Type,
Instr(v_Fonts_Type, ',', 1, i - 1) + 1,
Instr(v_Fonts_Type, ',', 1, i) -
Instr(v_Fonts_Type, ',', 1, i - 1) - 1);
END IF;
SELECT Decode(v_Chr,
'斜体',
'font-style:italic;',
'粗体',
'font-weight:bold;',
'加下划线',
'text-decoration:underline;',
'加删除线',
'text-decoration:line-through;',
'font-weight:lighter;')
INTO Fonts_Type
FROM Dual;
v_Type := v_Type || Fonts_Type;
END LOOP;
p_Style := v_Size || v_Fonts || v_Color || v_Type;
END;

PROCEDURE Generate_Html(p_Mail_Id IN NUMBER,
p_Subject IN VARCHAR2,
p_Receiver IN VARCHAR2,
p_Copy_Receiver IN VARCHAR2,
p_Calls IN VARCHAR2) IS
CURSOR Cur_Header IS
SELECT t.*
FROM Xxtp_Mail_Header_Tmp t
WHERE t.Mail_Id = p_Mail_Id
ORDER BY t.Header_Id;

CURSOR Cur_Line IS
SELECT *
FROM Xxtp_Mail_Line_Tmp t
WHERE NOT EXISTS (SELECT 1
FROM Xxtp_Mail_Header_Tmp Tl
WHERE Tl.Mail_Id = p_Mail_Id
AND t.Header_Id = Tl.Header_Id)
AND t.Mail_Id = p_Mail_Id;

CURSOR Cur_Tail IS
SELECT * FROM Xxtp_Mail_Tail_Tmp WHERE Mail_Id = p_Mail_Id;

p_Table VARCHAR2(240) := '<table border=1 bordercolor="#000000" cellspacing="0" class="STYLE19">';
p_Style VARCHAR2(4000);
p_Data CLOB;
p_Text VARCHAR2(4000);
p_Call VARCHAR2(4000);
p_Column VARCHAR2(4000);
p_Value VARCHAR2(4000);
x_Value NUMBER;

v_Var VARCHAR2(4000);
v_Header_Id NUMBER := -1;
v_Max NUMBER;
v_Num NUMBER;

Nei_Receiver VARCHAR2(4000) := NULL;
Wai_Receiver VARCHAR2(4000) := NULL;
Copy_Nei_Receiver VARCHAR2(4000) := NULL;
Copy_Wai_Receiver VARCHAR2(4000) := NULL;

i NUMBER := 1;
j NUMBER := 1;

BEGIN
Dbms_Lob.Createtemporary(p_Data, TRUE);
p_Data := '<html>' || p_Crlf;
Dbms_Lob.Writeappend(p_Data,
Length('<style type="text/css" overflow:hidden>.STYLE19 { font-size: 12px}</style>
<head><meta http-equiv="Content-Type" content="text/html; charset=gb2312" /></head>' ||
p_Crlf),
'<style type="text/css" overflow:hidden>.STYLE19 { font-size: 12px}</style>
<head><meta http-equiv="Content-Type" content="text/html; charset=gb2312" /></head>' ||
p_Crlf);
Dbms_Lob.Writeappend(p_Data,
Length('<body>' || p_Crlf),
'<body>' || p_Crlf);
IF p_Calls IS NULL THEN
--默认称谓
SELECT Nvl(Length(Regexp_Replace(p_Receiver, '[^@]+', '')), 0) Counts
INTO v_Num
FROM Dual;
IF v_Num = 1 THEN
SELECT Substr(p_Receiver, 1, Instr(p_Receiver, '@', 1) - 1)
INTO p_Call
FROM Dual;
p_Call := p_Call || ',您好:';
ELSE
p_Call := '大家好:';
END IF;
ELSE
--输出称谓
SELECT Nvl(Length(Regexp_Replace(p_Calls, '[^您好||^你好]+', '')), 0) Counts
INTO v_Num
FROM Dual;
IF v_Num < 2 THEN
--判断是否为有点无逗号结尾
IF Substr(p_Calls, Length(p_Calls)) = ',' THEN
p_Call := p_Calls || '您好:';
ELSE
p_Call := p_Calls || ',您好:';
END IF;
ELSE
p_Call := p_Calls;
END IF;
END IF;
--print appellation
Dbms_Lob.Writeappend(p_Data,
Length('<p style="line-height:1.5;font-size:12px;font-weight:lighter;font-family:宋体;color:black;">' ||
p_Call || '</p>' || p_Crlf),
'<p style="line-height:1.5;font-size:12px;font-weight:lighter;font-family:宋体;color:black;">' ||
p_Call || '</p>' || p_Crlf);
--case1:match header and line information
FOR Mycur IN Cur_Header LOOP

Personalization(p_Size => Mycur.Wsize,
p_Fonts => Mycur.Fonts,
p_Color => Mycur.Color,
p_Fonts_Type => Mycur.Fonts_Type,
p_Style => p_Style);

Dbms_Lob.Writeappend(p_Data,
Length('<p style="line-height:1.5;' || p_Style || '">' ||
'    ' || Mycur.Text ||
'</p>' || p_Crlf),
'<p style="line-height:1.5;' || p_Style || '">' ||
'    ' || Mycur.Text ||
'</p>' || p_Crlf);
--line information
SELECT COUNT(*)
INTO v_Num
FROM Xxtp_Mail_Line_Tmp t
WHERE t.Mail_Id = p_Mail_Id
AND t.Header_Id = Mycur.Header_Id;

IF v_Num <> 0 THEN

v_Max := 0;
FOR j IN 1 .. 33 LOOP
EXECUTE IMMEDIATE 'select count(attribute' || j ||
') from xxtp_mail_line_tmp where mail_id = ' ||
p_Mail_Id || ' and header_id = ' ||
Mycur.Header_Id
INTO x_Value;
IF x_Value <> 0 THEN
v_Max := j;
END IF;
END LOOP;

Dbms_Lob.Writeappend(p_Data, Length(p_Table), p_Table);

FOR Mycur1 IN (SELECT *
FROM Xxtp_Mail_Line_Tmp t
WHERE t.Mail_Id = p_Mail_Id
AND t.Header_Id = Mycur.Header_Id) LOOP

Personalization(p_Size => Mycur1.Wsize,
p_Fonts => Mycur1.Fonts,
p_Color => Mycur1.Color,
p_Fonts_Type => Mycur1.Fonts_Type,
p_Style => p_Style);

p_Column := Mycur1.Wcolumn;
IF Substr(Mycur1.Wcolumn, Length(Mycur1.Wcolumn)) <> ',' THEN
p_Column := p_Column || ',';
END IF;
IF Substr(Mycur1.Wcolumn, 1, 1) <> ',' THEN
p_Column := ',' || p_Column;
END IF;

Dbms_Lob.Writeappend(p_Data,
Length('<tr>' || p_Crlf),
'<tr>' || p_Crlf);

FOR i IN 1 .. v_Max LOOP
EXECUTE IMMEDIATE 'select attribute' || i ||
' as v_value from xxtp_mail_line_tmp t where mail_id = ' ||
p_Mail_Id || ' and line_id=' ||
Mycur1.Line_Id
INTO p_Value;

SELECT COUNT(*)
INTO v_Num
FROM Dual
WHERE p_Column LIKE '%,' || i || ',%';

IF v_Num <> 0 OR
p_Column IS NULL THEN
Dbms_Lob.Writeappend(p_Data,
Length('<td height=25 style="' ||
p_Style || '">' ||
Nvl(p_Value, '&nbsp') || '</td>' ||
p_Crlf),
'<td height=25 style="' || p_Style || '">' ||
Nvl(p_Value, '&nbsp') || '</td>' ||
p_Crlf);
ELSE
Dbms_Lob.Writeappend(p_Data,
Length('<td height=25 style="font-size:12px;font-family:宋体;color:black;font-weight:lighter;">' ||
Nvl(p_Value, '&nbsp') || '</td>' ||
p_Crlf),
'<td height=25 style="font-size:12px;font-family:宋体;color:black;font-weight:lighter;">' ||
Nvl(p_Value, '&nbsp') || '</td>' ||
p_Crlf);
END IF;
END LOOP;
Dbms_Lob.Writeappend(p_Data,
Length('</tr>' || p_Crlf),
'</tr>' || p_Crlf);

END LOOP;

Dbms_Lob.Writeappend(p_Data,
Length('</table><br>' || p_Crlf),
'</table><br>' || p_Crlf);
END IF;
END LOOP;
--case 2:output line information
SELECT COUNT(*)
INTO v_Num
FROM Xxtp_Mail_Line_Tmp t
WHERE NOT EXISTS (SELECT 1
FROM Xxtp_Mail_Header_Tmp Tl
WHERE Tl.Mail_Id = p_Mail_Id
AND t.Header_Id = Tl.Header_Id)
AND Mail_Id = p_Mail_Id;
IF v_Num <> 0 THEN
Dbms_Lob.Writeappend(p_Data,
Length('<table border=1 bordercolor="#000000" cellspacing="0" class="STYLE19">' ||
p_Crlf),
'<table border=1 bordercolor="#000000" cellspacing="0" class="STYLE19">' ||
p_Crlf);

FOR Mycur2 IN Cur_Line LOOP

Personalization(p_Size => Mycur2.Wsize,
p_Fonts => Mycur2.Fonts,
p_Color => Mycur2.Color,
p_Fonts_Type => Mycur2.Fonts_Type,
p_Style => p_Style);

IF v_Header_Id = -1 OR
v_Header_Id <> Mycur2.Header_Id THEN
v_Max := 0;
FOR j IN 1 .. 33 LOOP
EXECUTE IMMEDIATE 'select count(attribute' || j ||
') from xxtp_mail_line_tmp where mail_id = ' ||
p_Mail_Id || ' and header_id =' ||
Mycur2.Header_Id
INTO x_Value;

IF x_Value <> 0 THEN
v_Max := j;
END IF;
END LOOP;
END IF;

IF v_Header_Id = -1 THEN
v_Header_Id := Mycur2.Header_Id;
END IF;

IF v_Header_Id <> Mycur2.Header_Id THEN
v_Header_Id := Mycur2.Header_Id;
Dbms_Lob.Writeappend(p_Data,
Length('</table><br>' || p_Crlf),
'</table><br>' || p_Crlf);
Dbms_Lob.Writeappend(p_Data,
Length('<table border=1 bordercolor="#000000" cellspacing="0" class="STYLE19">' ||
p_Crlf),
'<table border=1 bordercolor="#000000" cellspacing="0" class="STYLE19">' ||
p_Crlf);
END IF;

p_Column := Mycur2.Wcolumn;
IF p_Column IS NOT NULL THEN
IF Substr(Mycur2.Wcolumn, Length(Mycur2.Wcolumn)) <> ',' THEN
p_Column := p_Column || ',';
END IF;
IF Substr(Mycur2.Wcolumn, 1, 1) <> ',' THEN
p_Column := ',' || p_Column;
END IF;
END IF;
Dbms_Lob.Writeappend(p_Data,
Length('<tr>' || p_Crlf),
'<tr>' || p_Crlf);
FOR i IN 1 .. v_Max LOOP

EXECUTE IMMEDIATE 'select attribute' || i ||
' FROM xxtp_mail_line_tmp t WHERE mail_id = ' ||
p_Mail_Id || ' and line_id = ' ||
Mycur2.Line_Id
INTO p_Value;

SELECT COUNT(*)
INTO v_Num
FROM Dual
WHERE p_Column LIKE '%,' || i || ',%';

IF p_Column IS NULL OR
v_Num <> 0 THEN
Dbms_Lob.Writeappend(p_Data,
Length('<td height=25 style="' || p_Style || '">' ||
Nvl(p_Value, '&nbsp') || '</td>' ||
p_Crlf),
'<td height=25 style="' || p_Style || '">' ||
Nvl(p_Value, '&nbsp') || '</td>' || p_Crlf);
ELSE
Dbms_Lob.Writeappend(p_Data,
Length('<td height=25 style="font-size:12px;font-family:宋体;color:black;font-weight:lighter;">' ||
Nvl(p_Value, '&nbsp') || '</td>' ||
p_Crlf),
'<td height=25 style="font-size:12px;font-family:宋体;color:black;font-weight:lighter;">' ||
Nvl(p_Value, '&nbsp') || '</td>' || p_Crlf);
END IF;

END LOOP;
Dbms_Lob.Writeappend(p_Data,
Length('</tr>' || p_Crlf),
'</tr>' || p_Crlf);
END LOOP;
Dbms_Lob.Writeappend(p_Data,
Length('</table><br>' || p_Crlf),
'</table><br>' || p_Crlf);
END IF;
---the signature of the mail
SELECT COUNT(*)
INTO v_Num
FROM Xxtp_Mail_Tail_Tmp
WHERE Mail_Id = p_Mail_Id;
IF v_Num <> 0 THEN
Dbms_Lob.Writeappend(p_Data, Length('<br>'), '<br>');
FOR Mycur IN Cur_Tail LOOP

Personalization(p_Size => Mycur.Wsize,
p_Fonts => Mycur.Fonts,
p_Color => Mycur.Color,
p_Fonts_Type => '',
p_Style => p_Style);

IF Mycur.Text IS NOT NULL THEN
p_Text := Mycur.Text;
IF Substr(p_Text, Length(p_Text)) <> ',' THEN
p_Text := p_Text || ',';
END IF;
WHILE j < Length(p_Text) LOOP
SELECT Substr(p_Text, j, Instr(p_Text, ',', 1, i) - j)
INTO v_Var
FROM Dual;
Dbms_Lob.Writeappend(p_Data,
Length('<p style="line-height:5px;' ||
p_Style || '">' || v_Var || '</p>' ||
p_Crlf),
'<p style="line-height:5px;' || p_Style || '">' ||
v_Var || '</p>' || p_Crlf);
SELECT Instr(p_Text, ',', 1, i) INTO j FROM Dual;
j := j + 1;
i := i + 1;
END LOOP;
END IF;
END LOOP;
END IF;
Dbms_Lob.Writeappend(p_Data,
Length('</body>' || p_Crlf),
'</body>' || p_Crlf);
Dbms_Lob.Writeappend(p_Data, Length('</html>'), '</html>');

--将内网和外网用户邮箱分开发送
Get_Receiver(i_Receiver => p_Receiver,
x_Nei_Receiver => Nei_Receiver,
x_Wai_Receiver => Wai_Receiver);
--获取抄送人邮箱
Get_Receiver(i_Receiver => p_Copy_Receiver,
x_Nei_Receiver => Copy_Nei_Receiver,
x_Wai_Receiver => Copy_Wai_Receiver);

IF Nei_Receiver IS NOT NULL OR
Copy_Nei_Receiver IS NOT NULL THEN
--send mail
Xxtp_Html_Mail_Pkg.Mailing(p_Subject => p_Subject,
p_Receiver => Nei_Receiver,
p_Copy_Receiver => Copy_Nei_Receiver,
p_Data => p_Data);
END IF;
IF Wai_Receiver IS NOT NULL OR
Copy_Wai_Receiver IS NOT NULL THEN
--send mail
Xxtp_Html_Mail_Pkg.Mailing(p_Subject => p_Subject,
p_Receiver => Wai_Receiver,
p_Copy_Receiver => Copy_Wai_Receiver,
p_Data => p_Data);
END IF;

EXCEPTION
WHEN OTHERS THEN
Dbms_Output.Put_Line('Generate Html:' || SQLERRM);
END Generate_Html;

/*=======================================================
* FUNCTION / PROCEDURE
* Send_Mail
* DESCRIPTION:
* 发送HTML邮件
* ARGUMENT:
* p_Mail_Rec :邮件信息
* x_Error_Info_Tbl :错误信息
* RETURN:
* NULL
* HISTORY:
* 1.00 2016/07/01 mis_lzl
=========================================================*/
PROCEDURE Send_Mail(p_Mail_Rec IN Mail_Rec_Type,
x_Error_Info_Tbl OUT NOCOPY Error_Info_Tbl_Type) IS
p_Mail_Id NUMBER;
p_Header_Id NUMBER;
p_Subject VARCHAR2(4000);
p_Receiver VARCHAR2(4000);
p_Copy_Receiver VARCHAR2(4000);
p_Calls VARCHAR2(4000);
p_Line_Id NUMBER := 0;
l_Sitename VARCHAR2(400);--added by yixueyuan@2021-10-15 for 增加测试环境自动转换
BEGIN
EXECUTE IMMEDIATE 'truncate table tplink.xxtp_mail_tmp';
EXECUTE IMMEDIATE 'truncate table tplink.xxtp_mail_header_tmp';
EXECUTE IMMEDIATE 'truncate table tplink.xxtp_mail_line_tmp';
EXECUTE IMMEDIATE 'truncate table tplink.xxtp_mail_tail_tmp';
--init subject
SELECT Xxtp_Mail_Autoid_s.Nextval INTO p_Mail_Id FROM Dual;

--added by yixueyuan@2021-10-15 for 增加测试环境自动转换
BEGIN
--获取当前系统的名称
SELECT Upper(Prv.Profile_Option_Value)
INTO l_Sitename
FROM Apps.Fnd_Profile_Options_Vl Pro,
Applsys.Fnd_Profile_Option_Values Prv
WHERE Pro.Profile_Option_Id = Prv.Profile_Option_Id(+)
AND Prv.Level_Id = 10001
AND Prv.Level_Value = 0
AND Pro.Profile_Option_Name = 'SITENAME'
AND Rownum = 1;
EXCEPTION
WHEN OTHERS THEN
l_Sitename := NULL;
END;
IF l_Sitename = 'PROD'
THEN
--生产环境发送的邮件不需要注明
NULL;
ELSE
l_Sitename := '测试环境(' || l_Sitename || ')邮件';
END IF;
--end added by yixueyuan@2021-10-15 for 增加测试环境自动转换

INSERT INTO Xxtp_Mail_Tmp
VALUES
(p_Mail_Id,
l_Sitename || p_Mail_Rec.Subject,--modified by yixueyuan@2021-10-15 for 增加测试环境自动转换
Regexp_Replace(p_Mail_Rec.Receiver, '，', ','),
Regexp_Replace(p_Mail_Rec.Copy_Receiver, '，', ','),
Regexp_Replace(p_Mail_Rec.Calls, '，', ','));
--init header
IF p_Mail_Rec.Header_Info.Count <> 0 THEN
FOR i IN p_Mail_Rec.Header_Info.First .. p_Mail_Rec.Header_Info.Last LOOP
SELECT Xxtp_Mail_Header_s.Nextval INTO p_Header_Id FROM Dual;
IF p_Mail_Rec.Header_Info(i).Text IS NOT NULL THEN
INSERT INTO Xxtp_Mail_Header_Tmp
VALUES
(p_Mail_Id,
p_Header_Id,
p_Mail_Rec.Header_Info(i).Text,
p_Mail_Rec.Header_Info(i).Wsize,
p_Mail_Rec.Header_Info(i).Fonts,
p_Mail_Rec.Header_Info(i).Color,
Regexp_Replace(p_Mail_Rec.Header_Info(i).Fonts_Type, '，', ','));
END IF;
--init line
IF p_Mail_Rec.Header_Info(i).Line_Info.Count <> 0 THEN
FOR j IN p_Mail_Rec.Header_Info(i).Line_Info.First .. p_Mail_Rec.Header_Info(i)
.Line_Info.Last LOOP
p_Line_Id := p_Line_Id + 1;
INSERT INTO Xxtp_Mail_Line_Tmp
VALUES
(p_Mail_Id,
p_Header_Id,
p_Line_Id,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute1,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute2,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute3,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute4,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute5,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute6,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute7,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute8,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute9,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute10,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute11,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute12,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute13,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute14,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute15,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute16,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute17,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute18,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute19,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute20,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute21,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute22,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute23,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute24,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute25,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute26,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute27,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute28,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute29,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute30,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute31,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute32,
p_Mail_Rec.Header_Info(i).Line_Info(j).Attribute33,
Regexp_Replace(p_Mail_Rec.Header_Info(i).Line_Info(j).Column,
'，',
','),
p_Mail_Rec.Header_Info(i).Line_Info(j).Wsize,
p_Mail_Rec.Header_Info(i).Line_Info(j).Fonts,
p_Mail_Rec.Header_Info(i).Line_Info(j).Color,
Regexp_Replace(p_Mail_Rec.Header_Info(i).Line_Info(j)
.Fonts_Type,
'，',
','));
END LOOP;
END IF;
END LOOP;
END IF;
--init signature
IF p_Mail_Rec.Sign_Info.Text IS NOT NULL THEN
INSERT INTO Xxtp_Mail_Tail_Tmp
VALUES
(p_Mail_Id,
Regexp_Replace(p_Mail_Rec.Sign_Info.Text, '，', ','),
p_Mail_Rec.Sign_Info.Wsize,
p_Mail_Rec.Sign_Info.Fonts,
p_Mail_Rec.Sign_Info.Color);
END IF;
COMMIT;
--check error
Info_Check(p_Error_Info_Tbl => x_Error_Info_Tbl);
IF x_Error_Info_Tbl.Count = 0 THEN
SELECT *
INTO p_Mail_Id, p_Subject, p_Receiver, p_Copy_Receiver, p_Calls
FROM Xxtp_Mail_Tmp;
Xxtp_Html_Mail_Pkg.Generate_Html(p_Mail_Id => p_Mail_Id,
p_Subject => p_Subject,
p_Receiver => p_Receiver,
p_Copy_Receiver => p_Copy_Receiver,
p_Calls => p_Calls);
END IF;
END Send_Mail;
/*=======================================================
* FUNCTION / PROCEDURE
* Send_Mail
* DESCRIPTION:
* 发送HTML邮件
* ARGUMENT:
* p_Mail_Tbl :邮件信息
* x_Error_Info_Tbl :错误信息
* RETURN:
* NULL
* HISTORY:
* 1.00 2016/07/01 mis_lzl
=========================================================*/
PROCEDURE Send_Mail(p_Mail_Tbl IN Mail_Tbl_Type,
x_Error_Info_Tbl OUT NOCOPY Error_Info_Tbl_Type) IS
p_Mail_Id NUMBER;
p_Header_Id NUMBER;
p_Line_Id NUMBER;
l_Sitename VARCHAR2(400);--added by yixueyuan@2021-10-15 for 增加测试环境自动转换
BEGIN
EXECUTE IMMEDIATE 'truncate table tplink.xxtp_mail_tmp';
EXECUTE IMMEDIATE 'truncate table tplink.xxtp_mail_header_tmp';
EXECUTE IMMEDIATE 'truncate table tplink.xxtp_mail_line_tmp';
EXECUTE IMMEDIATE 'truncate table tplink.xxtp_mail_tail_tmp';
--init subject
IF p_Mail_Tbl.Count <> 0 THEN
FOR k IN p_Mail_Tbl.First .. p_Mail_Tbl.Last LOOP
p_Line_Id := 0;
SELECT Xxtp_Mail_Autoid_s.Nextval INTO p_Mail_Id FROM Dual;
--added by yixueyuan@2021-10-15 for 增加测试环境自动转换
BEGIN
--获取当前系统的名称
SELECT Upper(Prv.Profile_Option_Value)
INTO l_Sitename
FROM Apps.Fnd_Profile_Options_Vl Pro,
Applsys.Fnd_Profile_Option_Values Prv
WHERE Pro.Profile_Option_Id = Prv.Profile_Option_Id(+)
AND Prv.Level_Id = 10001
AND Prv.Level_Value = 0
AND Pro.Profile_Option_Name = 'SITENAME'
AND Rownum = 1;
EXCEPTION
WHEN OTHERS THEN
l_Sitename := NULL;
END;
IF l_Sitename = 'PROD'
THEN
--生产环境发送的邮件不需要注明
NULL;
ELSE
l_Sitename := '测试环境(' || l_Sitename || ')邮件';
END IF;
--end added by yixueyuan@2021-10-15 for 增加测试环境自动转换

INSERT INTO Xxtp_Mail_Tmp
VALUES
(p_Mail_Id,
l_Sitename || p_Mail_Tbl(k).Subject,--modified by yixueyuan@2021-10-15 for 增加测试环境自动转换
Regexp_Replace(p_Mail_Tbl(k).Receiver, '，', ','),
Regexp_Replace(p_Mail_Tbl(k).Copy_Receiver, '，', ','),
Regexp_Replace(p_Mail_Tbl(k).Calls, '，', ','));
--init header
IF p_Mail_Tbl(k).Header_Info.Count <> 0 THEN
FOR i IN p_Mail_Tbl(k).Header_Info.First .. p_Mail_Tbl(k)
.Header_Info.Last LOOP
SELECT Xxtp_Mail_Header_s.Nextval INTO p_Header_Id FROM Dual;
IF p_Mail_Tbl(k).Header_Info(i).Text IS NOT NULL THEN
INSERT INTO Xxtp_Mail_Header_Tmp
VALUES
(p_Mail_Id,
p_Header_Id,
p_Mail_Tbl(k).Header_Info(i).Text,
p_Mail_Tbl(k).Header_Info(i).Wsize,
p_Mail_Tbl(k).Header_Info(i).Fonts,
p_Mail_Tbl(k).Header_Info(i).Color,
Regexp_Replace(p_Mail_Tbl(k).Header_Info(i).Fonts_Type,
'，',
','));
END IF;
--init line
IF p_Mail_Tbl(k).Header_Info(i).Line_Info.Count <> 0 THEN
FOR j IN p_Mail_Tbl(k).Header_Info(i).Line_Info.First .. p_Mail_Tbl(k).Header_Info(i)
.Line_Info.Last LOOP
p_Line_Id := p_Line_Id + 1;
INSERT INTO Xxtp_Mail_Line_Tmp
VALUES
(p_Mail_Id,
p_Header_Id,
p_Line_Id,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute1,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute2,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute3,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute4,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute5,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute6,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute7,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute8,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute9,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute10,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute11,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute12,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute13,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute14,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute15,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute16,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute17,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute18,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute19,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute20,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute21,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute22,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute23,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute24,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute25,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute26,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute27,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute28,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute29,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute30,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute31,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute32,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Attribute33,
Regexp_Replace(p_Mail_Tbl(k).Header_Info(i).Line_Info(j)
.Column,
'，',
','),
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Wsize,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Fonts,
p_Mail_Tbl(k).Header_Info(i).Line_Info(j).Color,
Regexp_Replace(p_Mail_Tbl(k).Header_Info(i).Line_Info(j)
.Fonts_Type,
'，',
','));
END LOOP;
END IF;
END LOOP;
END IF;
--init signature
IF p_Mail_Tbl(k).Sign_Info.Text IS NOT NULL THEN
INSERT INTO Xxtp_Mail_Tail_Tmp
VALUES
(p_Mail_Id,
Regexp_Replace(p_Mail_Tbl(k).Sign_Info.Text, '，', ','),
p_Mail_Tbl(k).Sign_Info.Wsize,
p_Mail_Tbl(k).Sign_Info.Fonts,
p_Mail_Tbl(k).Sign_Info.Color);
END IF;
END LOOP;
END IF;

COMMIT;
--check error
Info_Check(p_Error_Info_Tbl => x_Error_Info_Tbl);
IF x_Error_Info_Tbl.Count = 0 THEN
-- dbms_output.put_line('没有错误信息，可以正常发送');
FOR Mycur IN (SELECT * FROM Xxtp_Mail_Tmp) LOOP
Xxtp_Html_Mail_Pkg.Generate_Html(p_Mail_Id => Mycur.Mail_Id,
p_Subject => Mycur.Subject,
p_Receiver => Mycur.Receiver,
p_Copy_Receiver => Mycur.Copy_Receiver,
p_Calls => Mycur.Calls);
END LOOP;
END IF;
END Send_Mail;

--split receiver address
PROCEDURE Split_Address(p_Address VARCHAR2,
p_Conn IN OUT Utl_Smtp.Connection) IS
v_Address VARCHAR2(4000);
i NUMBER := 1;
j NUMBER := 1;
BEGIN
SELECT Regexp_Replace(p_Address, '，', ',') INTO v_Address FROM Dual;
IF Substr(v_Address, Length(v_Address)) <> ',' THEN
v_Address := v_Address || ',';
END IF;
WHILE j < Length(v_Address) LOOP
Utl_Smtp.Rcpt(p_Conn,
Substr(v_Address, j, Instr(v_Address, ',', 1, i) - j));
SELECT Instr(v_Address, ',', 1, i) INTO j FROM Dual;
j := j + 1;
i := i + 1;
END LOOP;
END Split_Address;
/*=======================================================
* FUNCTION / PROCEDURE
* Mailing
* DESCRIPTION:
* 邮件发送
* ARGUMENT:
* p_Suject :邮件主题
* p_Receiver :邮件接收人
* p_Copy_Receiver :邮件抄送人
* p_data :邮件内容
* RETURN:
* NULL
* HISTORY:
* 1.00 2016/07/01 mis_lzl
=========================================================*/
PROCEDURE Mailing(p_Subject IN VARCHAR2,
p_Receiver IN VARCHAR2,
p_Copy_Receiver IN VARCHAR2,
p_Data IN CLOB) IS
v_Mail_Conn Utl_Smtp.Connection;
v_Receiver VARCHAR2(4000);
--v_Rec VARCHAR2(4000);
v_Mailhost VARCHAR2(100);
p_Sender VARCHAR2(240);
p_Password VARCHAR2(240) := 'noitcesti';
i NUMBER := 1;
Len NUMBER;
BEGIN
IF p_Receiver IS NOT NULL THEN
v_Receiver := p_Receiver;
ELSE
v_Receiver := p_Copy_Receiver;
END IF;
--判断发往的地址
/*SELECT Regexp_Replace(v_Receiver, '，', ',')
INTO v_Rec
FROM Dual;
IF Substr(v_Rec, Length(v_Rec)) <> ',' THEN
v_Rec := v_Rec || ',';
END IF;*/
/*IF (Substr(v_Rec,
Instr(v_Rec, '@', 1) + 1,
Instr(v_Rec, ',', 1) - Instr(v_Rec, '@', 1) - 1)) =
'tp-link.net' THEN
--v_Mailhost := '172.29.88.10'; --内网
v_Mailhost := 'smtp.tp-link.net';
p_Sender := 'itsection@tp-link.net';
ELSE*/
--v_Mailhost := '172.29.180.11'; --外网
v_Mailhost := 'smtp.tp-link.com.cn';
p_Sender := 'itsection@tp-link.com.cn';
/*END IF;*/

v_Mail_Conn := Utl_Smtp.Open_Connection(v_Mailhost);
Utl_Smtp.Ehlo(v_Mail_Conn, v_Mailhost);
--Verify Mail Server
Utl_Smtp.Command(v_Mail_Conn, 'AUTH LOGIN');
Utl_Smtp.Command(v_Mail_Conn,
Utl_Raw.Cast_To_Varchar2(Utl_Encode.Base64_Encode(Utl_Raw.Cast_To_Raw(p_Sender))));
Utl_Smtp.Command(v_Mail_Conn,
Utl_Raw.Cast_To_Varchar2(Utl_Encode.Base64_Encode(Utl_Raw.Cast_To_Raw(p_Password))));
--Set sender
Utl_Smtp.Mail(v_Mail_Conn, p_Sender);
--Split address
Xxtp_Html_Mail_Pkg.Split_Address(p_Address => p_Receiver,
p_Conn => v_Mail_Conn);
Xxtp_Html_Mail_Pkg.Split_Address(p_Address => p_Copy_Receiver,
p_Conn => v_Mail_Conn);

Utl_Smtp.Open_Data(v_Mail_Conn);
--Sender
Utl_Smtp.Write_Data(v_Mail_Conn, 'From:' || p_Sender || Utl_Tcp.Crlf);
--Receiver
SELECT Regexp_Replace(p_Receiver, '，', ',') INTO v_Receiver FROM Dual;
Utl_Smtp.Write_Data(v_Mail_Conn, 'To:' || v_Receiver || Utl_Tcp.Crlf);
--Copy receiver
SELECT Regexp_Replace(p_Copy_Receiver, '，', ',')
INTO v_Receiver
FROM Dual;
Utl_Smtp.Write_Data(v_Mail_Conn, 'Cc:' || v_Receiver || Utl_Tcp.Crlf);
--Subject
Utl_Smtp.Write_Raw_Data(v_Mail_Conn,
Utl_Raw.Cast_To_Raw(Convert('Subject:' ||
p_Subject ||
Utl_Tcp.Crlf,
'ZHS16GBK')));

Utl_Smtp.Write_Data(v_Mail_Conn,
'Content-type: text/html;Charset=gb2312;' ||
Chr(13) || Chr(10));

Utl_Smtp.Write_Data(v_Mail_Conn, Chr(13) || Chr(10));
Len := Dbms_Lob.Getlength(p_Data);
WHILE i < Len LOOP
Utl_Smtp.Write_Raw_Data(v_Mail_Conn,
Utl_Raw.Cast_To_Raw(Convert(Dbms_Lob.Substr(p_Data,
3000,
i),
'ZHS16GBK')));
i := i + 3000;
END LOOP;
Utl_Smtp.Close_Data(v_Mail_Conn);
Utl_Smtp.Quit(v_Mail_Conn);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
Utl_Smtp.Quit(v_Mail_Conn);
Dbms_Output.Put_Line('SEND_MAIL:' || SQLERRM);
END Mailing;
END Xxtp_Html_Mail_Pkg;

CREATE OR REPLACE PACKAGE BODY "XXTP_HTML_MAIL_PKG" IS

p_Crlf VARCHAR2(20) := Chr(13) || Chr(10);

BEGIN
SELECT Regexp_Replace(Receiver, '，', ',') INTO v_Rec FROM Dual;
IF Substr(v_Rec, Length(v_Rec)) <> ',' THEN
v_Rec := v_Rec || ',';
END IF;

WHILE j < Length(v_Rec) LOOP
p_Suffix := Substr(v_Rec,
Instr(v_Rec, '@', 1, i) + 1,
Instr(v_Rec, ',', 1, i) - Instr(v_Rec, '@', 1, i) - 1);

p_Header_Num NUMBER;
p_Line_Num NUMBER;
p_Fonts_Type VARCHAR2(240);

SELECT COUNT(1) INTO p_Header_Num FROM Xxtp_Mail_Header_Tmp;
SELECT COUNT(1) INTO p_Line_Num FROM Xxtp_Mail_Line_Tmp;

END LOOP;

END;

CURSOR Cur_Line IS
SELECT *
FROM Xxtp_Mail_Line_Tmp t
WHERE NOT EXISTS (SELECT 1
FROM Xxtp_Mail_Header_Tmp Tl
WHERE Tl.Mail_Id = p_Mail_Id
AND t.Header_Id = Tl.Header_Id)
AND t.Mail_Id = p_Mail_Id;

CURSOR Cur_Tail IS
SELECT * FROM Xxtp_Mail_Tail_Tmp WHERE Mail_Id = p_Mail_Id;

v_Var VARCHAR2(4000);
v_Header_Id NUMBER := -1;
v_Max NUMBER;
v_Num NUMBER;

Nei_Receiver VARCHAR2(4000) := NULL;
Wai_Receiver VARCHAR2(4000) := NULL;
Copy_Nei_Receiver VARCHAR2(4000) := NULL;
Copy_Wai_Receiver VARCHAR2(4000) := NULL;

i NUMBER := 1;
j NUMBER := 1;

Personalization(p_Size => Mycur.Wsize,
p_Fonts => Mycur.Fonts,
p_Color => Mycur.Color,
p_Fonts_Type => Mycur.Fonts_Type,
p_Style => p_Style);

IF v_Num <> 0 THEN

Dbms_Lob.Writeappend(p_Data, Length(p_Table), p_Table);

FOR Mycur1 IN (SELECT *
FROM Xxtp_Mail_Line_Tmp t
WHERE t.Mail_Id = p_Mail_Id
AND t.Header_Id = Mycur.Header_Id) LOOP

Personalization(p_Size => Mycur1.Wsize,
p_Fonts => Mycur1.Fonts,
p_Color => Mycur1.Color,
p_Fonts_Type => Mycur1.Fonts_Type,
p_Style => p_Style);

Dbms_Lob.Writeappend(p_Data,
Length('<tr>' || p_Crlf),
'<tr>' || p_Crlf);

FOR i IN 1 .. v_Max LOOP
EXECUTE IMMEDIATE 'select attribute' || i ||
' as v_value from xxtp_mail_line_tmp t where mail_id = ' ||
p_Mail_Id || ' and line_id=' ||
Mycur1.Line_Id
INTO p_Value;

SELECT COUNT(*)
INTO v_Num
FROM Dual
WHERE p_Column LIKE '%,' || i || ',%';

END LOOP;

FOR Mycur2 IN Cur_Line LOOP

Personalization(p_Size => Mycur2.Wsize,
p_Fonts => Mycur2.Fonts,
p_Color => Mycur2.Color,
p_Fonts_Type => Mycur2.Fonts_Type,
p_Style => p_Style);

IF x_Value <> 0 THEN
v_Max := j;
END IF;
END LOOP;
END IF;

IF v_Header_Id = -1 THEN
v_Header_Id := Mycur2.Header_Id;
END IF;

EXECUTE IMMEDIATE 'select attribute' || i ||
' FROM xxtp_mail_line_tmp t WHERE mail_id = ' ||
p_Mail_Id || ' and line_id = ' ||
Mycur2.Line_Id
INTO p_Value;

SELECT COUNT(*)
INTO v_Num
FROM Dual
WHERE p_Column LIKE '%,' || i || ',%';

Personalization(p_Size => Mycur.Wsize,
p_Fonts => Mycur.Fonts,
p_Color => Mycur.Color,
p_Fonts_Type => '',
p_Style => p_Style);

EXCEPTION
WHEN OTHERS THEN
Dbms_Output.Put_Line('Generate Html:' || SQLERRM);
END Generate_Html;

Utl_Smtp.Write_Data(v_Mail_Conn,
'Content-type: text/html;Charset=gb2312;' ||
Chr(13) || Chr(10));

posted @ 2021-12-02 09:39 官xing 阅读(31) 评论(0) 收藏举报

刷新页面返回顶部

官xing

邮件公共包包体

公告