create or replace procedure prc_risk_control_sales_report(p_ReturnCode out varchar2) is error_info varchar2(4000); v_content clob; --一、客户质量报告 v_customer_quality_table1 clob; --表1 v_customer_quality_table2 clob; --表2 v_customer_quality_table3 clob; --表3 --二、风险指标 v_risk_indicator_table1 clob; --表1 v_risk_indicator_table2 clob; --表2 --三、销售处罚 v_sales_punishment_table1 clob; --表1 v_sales_punishment_table2 clob; --表2 v_sales_punishment_table3 clob; --表3 v_sales_punishment_table4 clob; --表4 --四、积极内部代码 v_part4_table1 clob; --表1 --五、欺诈与异常 v_part5_table1 clob; --表1 v_part5_table2 clob; --表2 v_part5_table3 clob; --表3 v_part5_label clob; --表4 --六、风险提示 v_part6_label clob; --表1 v_Enter varchar2(100); --换行 v_mail_to varchar2(3000); --收件人 v_count varchar2(100); --收件人数量 begin v_Enter := '<tr><td colspan="6" style="height: 10px"></td></tr>'; --根据省份循环插入报表 for province in (select distinct t.province from sellerplace t where status = 1) loop select count(1) into v_count from sys_user_organize a join sys_user_list b on b.id = a.user_id join sys_organize_city c on c.org_id = a.org_id join sys_organize d on d.id = a.org_id where b.role_id in ('BH', 'RSD', 'CM', 'SCM') and b.status = 1 and c.province = province.province; if nvl(v_count, 0) = 0 then v_mail_to := 'wangjunjie@dafycredit.com;wangxiaofeng@dafycredit.com'; else select listagg(email, ';') within group(order by province desc) into v_mail_to from (select distinct c.province,b.email from sys_user_organize a join sys_user_list b on b.id = a.user_id join sys_organize_city c on c.org_id = a.org_id join sys_organize d on d.id = a.org_id where b.role_id in ('BH', 'RSD', 'CM', 'SCM') and b.status = 1 and c.province = province.province); end if; v_content := '<meta name="viewport"content="width=device-width,initial-scale=1.0,maximum-scale=1.0,minimum-scale=1.0,user-scalable=no"> <table style=" 100%;border-collapse:collapse;font-family:Arial"> <tr> <td colspan="6"> 各位领导,<br/> 请查看' || province.province || to_char(add_months(trunc(sysdate), -1), 'yyyymm') || '风控销售月报! </td> </tr><tr><td colspan="6" style="height: 10px"></td></tr> <tr><td colspan="6" style="background-color: #0EAAAE;"> <p style="color: white; font-size: larger"><strong>一、客户质量报告</strong></p></td></tr><tr><td colspan="6"><strong>1.多次借贷比例</strong>(上月客户在其它平台出现贷款申请的比例)</td> </tr><tr style="font-size:15px"><td colspan="6" style="font-size:15px">1)多次借贷比例_按城市排名</td></tr><tr style="border:1px solid black;font-size:15px;"><td style="background-color: #0EAAAE; 20%;border:1px solid black;font-size:15px;"><p style="color: white;">城市</p ></td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">多次借贷比例</p></td> <td colspan="4"></td></tr>'; for khzlbg1 in (select id as 城市, to_char(rate * 100, 'fm9999990.0') || '%' as 多次借贷比例 from risk_control.df_risk_sales_rejectreason@rptdb01 where cate = 'CITY' and part = '多次借贷' and province = province.province order by rate desc) loop v_customer_quality_table1 := v_customer_quality_table1 || '<tr style="border:1px solid black;font-size:15px;font-size:15px;"><td style=" 20%;border:1px solid black;font-size:15px;font-size:15px;">' || khzlbg1.城市 || '</td> <td style=" 15%;border:1px solid black;font-size:15px;font-size:15px;">' || khzlbg1.多次借贷比例 || '</td> </tr>'; end loop; v_content := v_content || v_customer_quality_table1 || v_Enter || '<tr><td colspan="6" style="font-size:15px">2)多次借贷比例_按销售经理排名(最高前10位)</td></tr><tr style="border:1px solid black;font-size:15px;font-size:15px;"><td style="background-color: #0EAAAE; 20%;border:1px solid black;font-size:15px;font-size:15px;"><p style="color: white;">销售经理</p></td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;font-size:15px;"><p style="color: white;">多次借贷比例</p></td><td colspan="4"></td></tr>'; for khzlbg2 in (select id as 销售经理, to_char(rate * 100, 'fm9999990.0') || '%' as 多次借贷比例 from risk_control.df_risk_sales_rejectreason@rptdb01 where cate = 'DSM' and part = '多次借贷' and province = province.province order by rate desc) loop v_customer_quality_table2 := v_customer_quality_table2 || '<tr style="border:1px solid black;font-size:15px;font-size:15px;"><td style=" 20%;border:1px solid black;font-size:15px;font-size:15px;">' || khzlbg2.销售经理 || '</td><td style=" 15%;border:1px solid black;font-size:15px;font-size:15px;">' || khzlbg2.多次借贷比例 || '</td></tr>'; end loop; v_content := v_content || v_customer_quality_table2 || '<tr> <td colspan="6" style="font-size:13px"> *申请量不足100单的销售经理不在此排名中 </td> </tr>' || v_Enter || '<tr><td colspan="6"><strong>2.内部代码拒绝比例</strong>(上月各城市使用内部代码拒绝的比例)</td></tr><tr><td colspan="6" style="font-size:15px">1)内部代码拒绝比例_按城市排名</td></tr> <tr style="border:1px solid black;font-size:15px;font-size:15px;"><td style="background-color: #0EAAAE; 20%;border:1px solid black;font-size:15px;font-size:15px;"><p style="color: white;">城市</p></td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;font-size:15px;"><p style="color: white;">内部代码拒绝比例</p></td><td colspan="4"></td></tr>'; for khzlbg3 in (select id as 城市, to_char(rate * 100, 'fm9999990.0') || '%' as 多次借贷比例 from risk_control.df_risk_sales_rejectreason@rptdb01 where cate = 'CITY' and part = '内部代码' and province = province.province order by rate desc) loop v_customer_quality_table3 := v_customer_quality_table3 || '<tr style="border:1px solid black;font-size:15px;font-size:15px;"><td style=" 20%;border:1px solid black;font-size:15px;font-size:15px;">' || khzlbg3.城市 || '</td><td style=" 15%;border:1px solid black;font-size:15px;font-size:15px;">' || khzlbg3.多次借贷比例 || '</td></tr>'; end loop; v_content := v_content || v_customer_quality_table3 || v_Enter || '<tr><td colspan="6"style="background-color: #0EAAAE;"><p style="color: white; font-size: larger"><strong>二、风险指标</strong></p></td></tr> <tr><td colspan="6"><strong>1.3PD30,FPD30</strong>(最近一个月的3PD30和FPD30)</td></tr><tr><td colspan="6" style="font-size:15px">1)按城市排名</td></tr> <tr style="border:1px solid black;font-size:15px;font-size:15px;"><td style="background-color: #0EAAAE; 20%;border:1px solid black;font-size:15px;font-size:15px;"><p style="color: white;">城市</p></td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;font-size:15px;"><p style="color: white;">3PD30</p></td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;font-size:15px;"><p style="color: white;">FPD30</p></td><td colspan="3"></td></tr>'; for fxzb1 in (select id as 城市, pd30_3, fpd30 from risk_control.df_risk_sales_3pd@rptdb01 where province = province.province and cate = 'CITY' order by pd30_3 desc) loop v_risk_indicator_table1 := v_risk_indicator_table1 || '<tr style="border:1px solid black;font-size:15px;font-size:15px;"><td style=" 20%;border:1px solid black;font-size:15px;font-size:15px;">' || fxzb1.城市 || '</td><td style=" 15%;border:1px solid black;font-size:15px;font-size:15px;">' || fxzb1.pd30_3 || '</td><td style=" 15%;border:1px solid black;font-size:15px;font-size:15px;">' || fxzb1.fpd30 || '</td></tr>'; end loop; v_content := v_content || v_risk_indicator_table1 || v_Enter || '<tr><td colspan="6" style="font-size:15px">2)按销售经理排名(最高前10位)</td></tr> <tr style="border:1px solid black;font-size:15px;font-size:15px;"><td style="background-color: #0EAAAE; 20%;border:1px solid black;font-size:15px;font-size:15px;"><p style="color: white;">销售经理</p></td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;font-size:15px;"><p style="color: white;">3PD30</p></td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;font-size:15px;"><p style="color: white;">FPD30</p></td><td colspan="3"></td></tr>'; for fxzb2 in (select id as 销售经理, pd30_3, fpd30 from risk_control.df_risk_sales_3pd@rptdb01 where province = province.province and cate = 'DSM' order by pd30_3 desc) loop v_risk_indicator_table2 := v_risk_indicator_table2 || '<tr style="border:1px solid black;font-size:15px;font-size:15px;"><td style=" 20%;border:1px solid black;font-size:15px;">' || fxzb2.销售经理 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || fxzb2.pd30_3 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || fxzb2.fpd30 || '</td></tr>'; end loop; v_content := v_content || v_risk_indicator_table2 || '<tr> <td colspan="6" style="font-size:13px"> *申请量不足100单的销售经理不在此排名中 </td> </tr>' || v_Enter || '<tr><td colspan="6"style="background-color: #0EAAAE;"><p style="color: white; font-size: larger"><strong>三、销售处罚</strong></p></td></tr> <tr><td colspan="6"><strong>1.销售处罚</strong>(上月纪检会处罚人数)</td></tr> <tr><td colspan="6" style="font-size:15px">1)销售(含销售代表及销售经理)处罚人数_按城市排名</td></tr> <tr style="border:1px solid black;font-size:15px;"><td style="background-color: #0EAAAE; 20%;border:1px solid black;font-size:15px;"><p style="color: white;">城市</p></td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">开除</p></td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">书面警告</p></td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">口头警告</p></td></tr>'; for xscf1 in (select dsm as 城市, 开除, 书面警告, 口头警告 from risk_control.df_risk_sales_dmrate@rptdb01 where cate = 'CITY' and province = province.province order by dm_cnt desc) loop v_sales_punishment_table1 := v_sales_punishment_table1 || '<tr style="border:1px solid black;font-size:15px;"><td style=" 20%;border:1px solid black;font-size:15px;">' || xscf1.城市 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || xscf1.开除 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || xscf1.书面警告 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || xscf1.口头警告 || '</td></tr>'; end loop; v_content := v_content || v_sales_punishment_table1 || v_Enter || '<tr> <td colspan="6" style="font-size:15px">2)销售(含销售代表)处罚人数_按销售经理排名(处罚人数最多前10位) </td> </tr> <tr style="border:1px solid black;font-size:15px;"> <td style="background-color: #0EAAAE; 20%;border:1px solid black;font-size:15px;"><p style="color: white;">销售经理</p> </td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">开除</p> </td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">书面警告</p> </td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">口头警告</p> </td> </tr>'; for xscf2 in (select dsm as 销售经理, 开除, 书面警告, 口头警告 from risk_control.df_risk_sales_dmrate@rptdb01 where cate = 'DSM' and province = province.province order by dm_cnt desc) loop v_sales_punishment_table2 := v_sales_punishment_table2 || '<tr style="border:1px solid black;font-size:15px;"><td style=" 20%;border:1px solid black;font-size:15px;">' || xscf2.销售经理 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || xscf2.开除 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || xscf2.书面警告 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || xscf2.口头警告 || '</td></tr>'; end loop; v_content := v_content || v_sales_punishment_table2 || '<tr> <td colspan="6" style="font-size:13px">*销售处罚人数为0的销售经理不在此排名中 </td> </tr>' || v_Enter || '<tr> <td colspan="6"> <strong>2.销售处罚原因</strong>(上月纪检会销售处罚原因分布) </td> </tr> <tr> <td colspan="6" style="font-size:15px">1)销售(含销售代表及销售经理)处罚原因分布_按城市排名 </td> </tr> <tr style="border:1px solid black;font-size:15px;"> <td style="background-color: #0EAAAE; 20%;border:1px solid black;font-size:15px;"><p style="color: white;">城市</p> </td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">合同文件错误</p> </td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">风控指标超标</p> </td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">欺诈</p> </td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">违规</p> </td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">管理失职</p> </td> </tr>'; for xscf3 in (select DSM AS 城市, 合同文件错误, 风控指标超标, 欺诈, 违规, 管理失职 from risk_control.df_risk_sales_dmrate@rptdb01 where cate = 'CITY' and province = province.province order by dm_cnt desc) loop v_sales_punishment_table3 := v_sales_punishment_table3 || '<tr style="border:1px solid black;font-size:15px;"><td style=" 20%;border:1px solid black;font-size:15px;">' || xscf3.城市 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || xscf3.合同文件错误 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || xscf3.风控指标超标 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || xscf3.欺诈 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || xscf3.违规 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || xscf3.管理失职 || '</td></tr>'; end loop; v_content := v_content || v_sales_punishment_table3 || v_Enter || '<tr> <td colspan="6" style="font-size:15px">2)销售(含销售代表)处罚原因分布_按销售经理排名(处罚人数最多前10位) </td> </tr> <tr style="border:1px solid black;font-size:15px;"> <td style="background-color: #0EAAAE; 20%;border:1px solid black;font-size:15px;"><p style="color: white;">销售经理</p> </td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">合同文件错误</p> </td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">风控指标超标</p> </td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">欺诈</p> </td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">违规</p> </td> </tr>'; for xscf4 in (select DSM AS 销售经理, 合同文件错误, 风控指标超标, 欺诈, 违规 from risk_control.df_risk_sales_dmrate@rptdb01 where cate = 'DSM' and province = province.province order by dm_cnt desc) loop v_sales_punishment_table4 := v_sales_punishment_table4 || '<tr style="border:1px solid black;font-size:15px;"><td style=" 20%;border:1px solid black;font-size:15px;">' || xscf4.销售经理 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || xscf4.合同文件错误 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || xscf4.风控指标超标 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || xscf4.欺诈 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || xscf4.违规 || '</td></tr>'; end loop; v_content := v_content || v_sales_punishment_table4 || ' <tr> <td colspan="6" style="font-size:13px">*销售处罚人数为0的销售经理不在此排名中 </td> </tr>' || v_Enter || ' <tr> <td colspan="6" style="background-color: #0EAAAE;"> <p style="color: white; font-size: larger"><strong>四、积极内部代码</strong></p> </td> </tr> <tr> <td colspan="6"> <strong>1.全国积极内部代码使用情况</strong>(上月的使用率、通过率及最近一个月的风控情况) </td> </tr> <tr style="border:1px solid black;font-size:15px;"> <td style="background-color: #0EAAAE; 20%;border:1px solid black;font-size:15px;"><p style="color: white;">省份</p> </td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">使用率</p> </td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">通过率</p> </td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">3PD30</p> </td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">FPD30</p> </td> </tr>'; for jjnbdm in (select 省份, 使用率, 通过率, PD30_3, FPD30 from risk_control.df_risk_sales_intercode12@rptdb01) loop v_part4_table1 := v_part4_table1 || '<tr style="border:1px solid black;font-size:15px;"><td style=" 20%;border:1px solid black;font-size:15px;">' || jjnbdm.省份 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || jjnbdm.使用率 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || jjnbdm.通过率 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || jjnbdm.PD30_3 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || jjnbdm.FPD30 || '</td></tr>'; end loop; v_content := v_content || v_part4_table1 || '<tr> <td colspan="6" style="font-size:13px">*3PD30,FPD30定义:比率为风险指标,括号内容为(逾期量/单量),例3%(3/100),指风险指标为3%,共有100单申请,其中3单逾期。 </td> </tr>' || v_Enter || '<tr> <td colspan="6" style="background-color: #0EAAAE;"> <p style="color: white; font-size: larger"><strong>五、欺诈与异常</strong></p> </td> </tr> <tr> <td colspan="6"> <strong>1.客户身份核查异常</strong>(上月后台核查发现的客户身份信息异常的申请,身份信息异常指身份信息不一致、照片不一致或照片出现PS) </td> </tr> <tr> <td colspan="6" style="font-size:15px">1)身份核查异常_按城市排名 </td> </tr> <tr style="border:1px solid black;font-size:15px;"> <td style="background-color: #0EAAAE; 20%;border:1px solid black;font-size:15px;"><p style="color: white;">城市</p> </td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">身份核查异常申请量</p> </td> </tr>'; for qzyyc1 in (select id as 城市, rate as 身份核查异常比例 from risk_control.df_risk_sales_rejectreason@rptdb01 where cate = 'CITY' and part = '身份核查异常' and province = province.province order by rate desc) loop v_part5_table1 := v_part5_table1 || '<tr style="border:1px solid black;font-size:15px;"><td style=" 20%;border:1px solid black;font-size:15px;">' || qzyyc1.城市 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || qzyyc1.身份核查异常比例 || '</td></tr>'; end loop; v_content := v_content || v_part5_table1 || v_Enter || ' <tr> <td colspan="6" style="font-size:15px">2)身份核查异常_按销售经理排名(最高前10位) </td> </tr> <tr style="border:1px solid black;font-size:15px;"> <td style="background-color: #0EAAAE; 20%;border:1px solid black;font-size:15px;"><p style="color: white;">销售经理</p> </td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">身份核查异常申请量</p> </td> </tr>'; for qzyyc2 in (select id as 销售经理, rate as 身份核查异常比例 from risk_control.df_risk_sales_rejectreason@rptdb01 where cate = 'DSM' and part = '身份核查异常' and province = province.province order by rate desc) loop v_part5_table2 := v_part5_table2 || '<tr style="border:1px solid black;font-size:15px;"><td style=" 20%;border:1px solid black;font-size:15px;">' || qzyyc2.销售经理 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || qzyyc2.身份核查异常比例 || '</td></tr>'; end loop; v_content := v_content || v_part5_table2 || '<tr> <td colspan="6" style="font-size:13px"> *身份核查异常申请量为0的销售经理不在此排名中 </td> </tr>' || v_Enter || '<tr> <td colspan="6"><strong>2.全国结案案件分布</strong>(后台展开调查并于上月结案的案件中出现欺诈及违规的申请) </td> </tr> <tr style="border:1px solid black;font-size:15px;"> <td style="background-color: #0EAAAE; 20%;border:1px solid black;font-size:15px;"><p style="color: white;">省份</p> </td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">违规申请量</p> </td> <td style="background-color: #0EAAAE; 15%;border:1px solid black;font-size:15px;"><p style="color: white;">欺诈申请量</p> </td> </tr>'; for qzyyc3 in (select 省份, 违规申请量, 欺诈申请量 from risk_control.df_risk_sales_invedistr@rptdb01 where 省份 <> '全国') loop v_part5_table3 := v_part5_table3 || '<tr style="border:1px solid black;font-size:15px;"><td style=" 20%;border:1px solid black;font-size:15px;">' || qzyyc3.省份 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || qzyyc3.违规申请量 || '</td><td style=" 15%;border:1px solid black;font-size:15px;">' || qzyyc3.欺诈申请量 || '</td></tr>'; end loop; v_content := v_content || v_part5_table3 || v_Enter || '<tr> <td colspan="6"><strong>3.欺诈案件</strong> </td> </tr>'; for qzyyc4 in (select 欺诈案件 from df_risk_sales_invedistr@rptdb01 where 省份 = '全国') loop v_part5_label := v_part5_label || '<tr><td colspan="6" style="font-size:15px">' || qzyyc4.欺诈案件 || '</td></tr>'; end loop; v_content := v_content || v_part5_label || v_Enter || '<tr> <td colspan="6" style="background-color: #0EAAAE;"> <p style="color: white; font-size: larger"><strong>六、风险提示</strong></p> </td> </tr>'; for fxts1 in (select 风险提示 from df_risk_sales_invedistr@rptdb01 where 省份 = '全国') loop v_part6_label := v_part6_label || '<tr><td colspan="6" style="font-size:15px">' || fxts1.风险提示 || '</td></tr>'; end loop; v_content := v_content || v_part6_label || '</table>'; insert into sys_email_list (id, mail_type, key_word, from_user, mail_to, cc_to, bcc_to, subject, email_boby, status, create_time, plan_time, engine_type) values (seq_sys_email_list.nextval, 'Risk', '风控销售月报', 'report@mail.dafycredit.com.cn', v_mail_to,--v_mail_to, --v_mail_to lixiaoxi@dafycredit.com;zhangjinwen@dafycredit.com;luojingna@dafycredit.com 'sunhuawei@dafycredit.com;lingfei@dafycredit.com;liuguohua@dafycredit.com;shenmengqi@dafycredit.com;zhangjinwen@dafycredit.com;lixiaoxi@dafycredit.com',--'sunhuawei@dafycredit.com;lingfei@dafycredit.com;liuguohua@dafycredit.com;shenmengqi@dafycredit.com;zhangjinwen@dafycredit.com;lixiaoxi@dafycredit.com', --'sunhuawei@dafycredit.com;lingfei@dafycredit.com;liuguohua@dafycredit.com;shenmengqi@dafycredit.com;zhangjinwen@dafycredit.com;lixiaoxi@dafycredit.com', '', '风控销售月报' || to_char(add_months(trunc(sysdate), -1), 'yyyymm') || '【' || province.province || '】', v_content, 0, sysdate, sysdate, 1); v_content := ''; v_customer_quality_table1 := ''; v_customer_quality_table2 := ''; v_customer_quality_table3 := ''; v_risk_indicator_table1 := ''; v_risk_indicator_table2 := ''; v_sales_punishment_table1 := ''; v_sales_punishment_table2 := ''; v_sales_punishment_table3 := ''; v_sales_punishment_table4 := ''; v_part4_table1 := ''; v_part5_table1 := ''; v_part5_table2 := ''; v_part5_table3 := ''; v_part5_label := ''; v_part6_label := ''; end loop; commit; p_ReturnCode := 'A'; return; exception When others Then error_info := sqlerrm; p_ReturnCode := 'Z-' || error_info; rollback; end prc_risk_control_sales_report;