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

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