动态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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
145![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
51
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
52
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
53
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
54
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
55
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
56
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
57
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
58
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
59
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
60
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
61
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
62
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
63
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
64
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
65
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
66
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
67
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
68
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
69
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
70
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
71
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
72
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
73
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
74
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
75
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
76
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
77
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
78
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
79
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
80
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
81
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
82
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
83
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
84
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
85
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
86
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
87
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
88
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
89
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
90
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
91
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
92
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
93
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
94
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
95
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
96
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
97
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
98
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
99
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
100
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
101
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
102
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
103
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
104
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
105
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
106
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
107
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
108
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
109
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
110
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
111
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
112
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
113
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
114
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
115
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
116
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
117
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
118
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
119
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
120
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
121
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
122
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
123
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
124
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
125
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
126
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
127
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
128
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
129
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
130
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
131
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
132
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
133
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
134
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
135
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
136
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
137
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
138
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
139
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
140
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
141
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
142
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
143
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
144
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
145
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)