动态SQL，嵌套游标，INSTR，SendMail
1
PROCEDURE send_detail_mail(
2 p_single_user IN VARCHAR2,
3 p_admin_user IN VARCHAR2,
4 p_current_day IN DATE )
5 IS
6 conn UTL_SMTP.connection;
7 v_current_day DATE;
8 v_html_header VARCHAR2( 8000 );
9 v_html_body VARCHAR2( 8000 );
10 v_html_tail VARCHAR2( 2000 );
11 v_mail_suject VARCHAR2( 255 );
12 v_user_name VARCHAR2( 60 );
13 v_user_nick_name VARCHAR2( 60 );
14 v_user_mail_address VARCHAR2( 100 );
15 v_html_log VARCHAR2( 1000 );
16 v_ro_site_group VARCHAR2( 100 );
17 v_ns_site_group VARCHAR2( 100 );
18 v_product_line_group VARCHAR2( 200 );
19 v_user_group VARCHAR2( 200 );
20 v_get_mail_data_sql_string VARCHAR2( 500 );
21 v_ro_site VARCHAR2( 100 );
22 v_ns_site VARCHAR2( 100 );
23 v_mail_data CLOB;
24 v_get_mail_data_sql VARCHAR2( 500 );
25 v_send_mail_sql VARCHAR2( 500 );
26 BEGIN
27 ----initial v_currentday
28 v_current_day := NVL( TRUNC( p_current_day ), TRUNC( SYSDATE - 1 ));
29 v_html_header := '';
30 v_html_tail :=
31 '<ul><font class="inf">'
32 || '<li>slow moving ratio = over 15 days inventory qty / total in warehouse inventory qty(not include MIT)</li>'
33 || '<li>ADS days (Historic) =inventory qty*30/ prior 30 ship qty</li>'
34 || '<li>ADS days (Forecast)=inventory qty*28/ next 4-week forecast </li>'
35 || '<li>NFP : No Forecast Provided or available on Demand Forecast System</li>'
36 || '<li>Resource : EIS/DFS</li>'
37 || '<li>For BQP business model concern, we put BQP finish goods in service warehouse in "Non-Saleable" section on this report</li></ul><br>'
38 || ' <FONT face=Arial color=#000080 size=2><STRONG>Information Support Department</STRONG></FONT><br>'
39 || ' <FONT face=Arial color=#000080 size=2><STRONG>'
40 || TO_CHAR( SYSDATE, 'YYYY-MM-DD' )
41 || '</STRONG></FONT>';
42 v_mail_suject :=
43 'Inventory Alert : Over-aged inventory-Action Required';
44 v_html_log := '';
45
46 --send mail by mail group
47 DECLARE
48 CURSOR cur_detailed_group
49 IS
50 SELECT '''' || REPLACE( ro_site,
51 ',',
52 ''',''' ) || '''',
53 '''' || REPLACE( ns_site,
54 ',',
55 ''',''' ) || '''',
56 '''' || REPLACE( product_line,
57 ',',
58 ''',''' ) || '''',
59 '''' || REPLACE( user_id,
60 ',',
61 ''',''' ) || ''''
62 FROM eis_hq_invhl_mail_group
63 WHERE report_type = 'Detailed';
64 BEGIN
65 OPEN cur_detailed_group;
66
67 LOOP
68 FETCH cur_detailed_group
69 INTO v_ro_site_group, v_ns_site_group, v_product_line_group,
70 v_user_group;
71
72 EXIT WHEN cur_detailed_group%NOTFOUND;
73
74 --get ro_site, ns_site
75 DECLARE
76 CURSOR cur_ns_site
77 IS
78 SELECT DISTINCT ro_site, ns_site
79 FROM eis_hq_invhl_mail_data
80 WHERE report_type = 'Detailed'
81 AND INSTR( v_ns_site_group, ns_site ) > 0;
82 BEGIN
83 OPEN cur_ns_site;
84
85 --EXECUTE IMMEDIATE v_sql;
86 --BEGIN
87 -- OPEN cur_mail_site;
88 LOOP
89 FETCH cur_ns_site
90 INTO v_ro_site, v_ns_site;
91
92 EXIT WHEN cur_ns_site%NOTFOUND;
93 -- DBMS_OUTPUT.put_line( v_ro_site || ' ----------- '
94 -- || v_ns_site );
95 --Saleable
96 --title
97 v_get_mail_data_sql :=
98 'select STRING_AGGREGATE_FUN(mail_data) from eis_hq_invhl_mail_data where ns_site = '''
99 || v_ns_site
100 || '''and wh_type = ''Saleable''and report_type = ''Detailed'' and product_line in ('
101 || v_product_line_group
102 || ') group by ns_site';
103
104 --DBMS_OUTPUT.put_line( v_product_line_group );
105 --DBMS_OUTPUT.put_line( v_sql );
106 EXECUTE IMMEDIATE v_get_mail_data_sql
107 INTO v_mail_data;
108 --Non-Saleable
109 --title
110
111 -- DBMS_OUTPUT.put_line( v_product_line );
112 END LOOP;
113 --DBMS_OUTPUT.put_line( v_ro_site || ' ----------- ' || v_ns_site );
114 END;
115
116
117 DECLARE
118 CURSOR cur_send_mail
119 IS
120 SELECT DISTINCT email, attribute2
121 FROM wscuser
122 WHERE INSTR( v_user_group, userid ) > 0;
123 BEGIN
124 OPEN cur_send_mail;
125
126 LOOP
127 FETCH cur_send_mail
128 INTO v_user_mail_address, v_user_nick_name;
129
130 EXIT WHEN cur_send_mail%NOTFOUND;
131 conn :=
132 eis_mail_pkg.begin_mail
133 ( sender => 'e@163.com',
134 recipients => 'Sammy@163.com',
135 subject => 'EIS INVHL',
136 mime_type => 'text/html;charset=utf-8' );
137 eis_mail_pkg.write_mb_text( conn, v_html_tail );
138 eis_mail_pkg.end_mail( conn );
139 END LOOP;
140 END;
141 END LOOP;
142 END;
143 END;
144
145
PROCEDURE send_detail_mail(2
p_single_user IN VARCHAR2,3
p_admin_user IN VARCHAR2,4
p_current_day IN DATE )5
IS6
conn UTL_SMTP.connection;7
v_current_day DATE;8
v_html_header VARCHAR2( 8000 );9
v_html_body VARCHAR2( 8000 );10
v_html_tail VARCHAR2( 2000 );11
v_mail_suject VARCHAR2( 255 );12
v_user_name VARCHAR2( 60 );13
v_user_nick_name VARCHAR2( 60 );14
v_user_mail_address VARCHAR2( 100 );15
v_html_log VARCHAR2( 1000 );16
v_ro_site_group VARCHAR2( 100 );17
v_ns_site_group VARCHAR2( 100 );18
v_product_line_group VARCHAR2( 200 );19
v_user_group VARCHAR2( 200 );20
v_get_mail_data_sql_string VARCHAR2( 500 );21
v_ro_site VARCHAR2( 100 );22
v_ns_site VARCHAR2( 100 );23
v_mail_data CLOB;24
v_get_mail_data_sql VARCHAR2( 500 );25
v_send_mail_sql VARCHAR2( 500 );26
BEGIN27
----initial v_currentday28
v_current_day := NVL( TRUNC( p_current_day ), TRUNC( SYSDATE - 1 ));29
v_html_header := '';30
v_html_tail :=31
'<ul><font class="inf">'32
|| '<li>slow moving ratio = over 15 days inventory qty / total in warehouse inventory qty(not include MIT)</li>'33
|| '<li>ADS days (Historic) =inventory qty*30/ prior 30 ship qty</li>'34
|| '<li>ADS days (Forecast)=inventory qty*28/ next 4-week forecast </li>'35
|| '<li>NFP : No Forecast Provided or available on Demand Forecast System</li>'36
|| '<li>Resource : EIS/DFS</li>'37
|| '<li>For BQP business model concern, we put BQP finish goods in service warehouse in "Non-Saleable" section on this report</li></ul><br>'38
|| ' <FONT face=Arial color=#000080 size=2><STRONG>Information Support Department</STRONG></FONT><br>'39
|| ' <FONT face=Arial color=#000080 size=2><STRONG>'40
|| TO_CHAR( SYSDATE, 'YYYY-MM-DD' )41
|| '</STRONG></FONT>';42
v_mail_suject :=43
'Inventory Alert : Over-aged inventory-Action Required';44
v_html_log := '';45
46
--send mail by mail group47
DECLARE48
CURSOR cur_detailed_group49
IS50
SELECT '''' || REPLACE( ro_site,51
',',52
''',''' ) || '''',53
'''' || REPLACE( ns_site,54
',',55
''',''' ) || '''',56
'''' || REPLACE( product_line,57
',',58
''',''' ) || '''',59
'''' || REPLACE( user_id,60
',',61
''',''' ) || ''''62
FROM eis_hq_invhl_mail_group63
WHERE report_type = 'Detailed';64
BEGIN65
OPEN cur_detailed_group;66
67
LOOP68
FETCH cur_detailed_group69
INTO v_ro_site_group, v_ns_site_group, v_product_line_group,70
v_user_group;71
72
EXIT WHEN cur_detailed_group%NOTFOUND;73
74
--get ro_site, ns_site75
DECLARE76
CURSOR cur_ns_site77
IS78
SELECT DISTINCT ro_site, ns_site79
FROM eis_hq_invhl_mail_data80
WHERE report_type = 'Detailed'81
AND INSTR( v_ns_site_group, ns_site ) > 0;82
BEGIN83
OPEN cur_ns_site;84
85
--EXECUTE IMMEDIATE v_sql;86
--BEGIN87
-- OPEN cur_mail_site;88
LOOP89
FETCH cur_ns_site90
INTO v_ro_site, v_ns_site;91
92
EXIT WHEN cur_ns_site%NOTFOUND;93
-- DBMS_OUTPUT.put_line( v_ro_site || ' ----------- '94
-- || v_ns_site );95
--Saleable96
--title97
v_get_mail_data_sql :=98
'select STRING_AGGREGATE_FUN(mail_data) from eis_hq_invhl_mail_data where ns_site = '''99
|| v_ns_site100
|| '''and wh_type = ''Saleable''and report_type = ''Detailed'' and product_line in ('101
|| v_product_line_group102
|| ') group by ns_site';103
104
--DBMS_OUTPUT.put_line( v_product_line_group );105
--DBMS_OUTPUT.put_line( v_sql );106
EXECUTE IMMEDIATE v_get_mail_data_sql107
INTO v_mail_data;108
--Non-Saleable109
--title110
111
-- DBMS_OUTPUT.put_line( v_product_line );112
END LOOP;113
--DBMS_OUTPUT.put_line( v_ro_site || ' ----------- ' || v_ns_site );114
END;115
116
117
DECLARE118
CURSOR cur_send_mail119
IS120
SELECT DISTINCT email, attribute2121
FROM wscuser122
WHERE INSTR( v_user_group, userid ) > 0;123
BEGIN124
OPEN cur_send_mail;125
126
LOOP127
FETCH cur_send_mail128
INTO v_user_mail_address, v_user_nick_name;129
130
EXIT WHEN cur_send_mail%NOTFOUND;131
conn :=132
eis_mail_pkg.begin_mail133
( sender => 'e@163.com',134
recipients => 'Sammy@163.com',135
subject => 'EIS INVHL',136
mime_type => 'text/html;charset=utf-8' );137
eis_mail_pkg.write_mb_text( conn, v_html_tail );138
eis_mail_pkg.end_mail( conn );139
END LOOP;140
END;141
END LOOP;142
END;143
END;144
145
