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

bsbforever

　　

  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