动态SQL，嵌套游标，INSTR，SendMail

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>'
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> '
38

|| ' Information Support Department '
39

|| ' '
40

|| TO_CHAR( SYSDATE, 'YYYY-MM-DD' )
41

|| '';
42

v_mail_suject :=
43

'Inventory Alert : Over-aged inventory-Action Required';
44

v_html_log := '';
45

--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

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

EXIT WHEN cur_detailed_group%NOTFOUND;
73

--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

--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

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