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="width: 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; width: 20%;border:1px solid black;font-size:15px;"><p style="color: white;">城市</p ></td>
<td style="background-color: #0EAAAE; width: 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="width: 20%;border:1px solid black;font-size:15px;font-size:15px;">' ||
khzlbg1.城市 ||
'</td>
<td style="width: 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; width: 20%;border:1px solid black;font-size:15px;font-size:15px;"><p style="color: white;">销售经理</p></td>
<td style="background-color: #0EAAAE; width: 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="width: 20%;border:1px solid black;font-size:15px;font-size:15px;">' ||
khzlbg2.销售经理 ||
'</td><td style="width: 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; width: 20%;border:1px solid black;font-size:15px;font-size:15px;"><p style="color: white;">城市</p></td>
<td style="background-color: #0EAAAE; width: 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="width: 20%;border:1px solid black;font-size:15px;font-size:15px;">' ||
khzlbg3.城市 ||
'</td><td style="width: 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; width: 20%;border:1px solid black;font-size:15px;font-size:15px;"><p style="color: white;">城市</p></td>
<td style="background-color: #0EAAAE; width: 15%;border:1px solid black;font-size:15px;font-size:15px;"><p style="color: white;">3PD30</p></td>
<td style="background-color: #0EAAAE; width: 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="width: 20%;border:1px solid black;font-size:15px;font-size:15px;">' ||
fxzb1.城市 ||
'</td><td style="width: 15%;border:1px solid black;font-size:15px;font-size:15px;">' ||
fxzb1.pd30_3 ||
'</td><td style="width: 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; width: 20%;border:1px solid black;font-size:15px;font-size:15px;"><p style="color: white;">销售经理</p></td>
<td style="background-color: #0EAAAE; width: 15%;border:1px solid black;font-size:15px;font-size:15px;"><p style="color: white;">3PD30</p></td>
<td style="background-color: #0EAAAE; width: 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="width: 20%;border:1px solid black;font-size:15px;">' ||
fxzb2.销售经理 ||
'</td><td style="width: 15%;border:1px solid black;font-size:15px;">' ||
fxzb2.pd30_3 ||
'</td><td style="width: 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; width: 20%;border:1px solid black;font-size:15px;"><p style="color: white;">城市</p></td>
<td style="background-color: #0EAAAE; width: 15%;border:1px solid black;font-size:15px;"><p style="color: white;">开除</p></td>
<td style="background-color: #0EAAAE; width: 15%;border:1px solid black;font-size:15px;"><p style="color: white;">书面警告</p></td>
<td style="background-color: #0EAAAE; width: 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="width: 20%;border:1px solid black;font-size:15px;">' ||
xscf1.城市 ||
'</td><td style="width: 15%;border:1px solid black;font-size:15px;">' ||
xscf1.开除 ||
'</td><td style="width: 15%;border:1px solid black;font-size:15px;">' ||
xscf1.书面警告 ||
'</td><td style="width: 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; width: 20%;border:1px solid black;font-size:15px;"><p style="color: white;">销售经理</p>
</td>
<td style="background-color: #0EAAAE; width: 15%;border:1px solid black;font-size:15px;"><p style="color: white;">开除</p>
</td>
<td style="background-color: #0EAAAE; width: 15%;border:1px solid black;font-size:15px;"><p style="color: white;">书面警告</p>
</td>
<td style="background-color: #0EAAAE; width: 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="width: 20%;border:1px solid black;font-size:15px;">' ||
xscf2.销售经理 ||
'</td><td style="width: 15%;border:1px solid black;font-size:15px;">' ||
xscf2.开除 ||
'</td><td style="width: 15%;border:1px solid black;font-size:15px;">' ||
xscf2.书面警告 ||
'</td><td style="width: 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; width: 20%;border:1px solid black;font-size:15px;"><p style="color: white;">城市</p>
</td>
<td style="background-color: #0EAAAE; width: 15%;border:1px solid black;font-size:15px;"><p style="color: white;">合同文件错误</p>
</td>
<td style="background-color: #0EAAAE; width: 15%;border:1px solid black;font-size:15px;"><p style="color: white;">风控指标超标</p>
</td>
<td style="background-color: #0EAAAE; width: 15%;border:1px solid black;font-size:15px;"><p style="color: white;">欺诈</p>
</td>
<td style="background-color: #0EAAAE; width: 15%;border:1px solid black;font-size:15px;"><p style="color: white;">违规</p>
</td>
<td style="background-color: #0EAAAE; width: 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="width: 20%;border:1px solid black;font-size:15px;">' ||
xscf3.城市 ||
'</td><td style="width: 15%;border:1px solid black;font-size:15px;">' ||
xscf3.合同文件错误 ||
'</td><td style="width: 15%;border:1px solid black;font-size:15px;">' ||
xscf3.风控指标超标 ||
'</td><td style="width: 15%;border:1px solid black;font-size:15px;">' ||
xscf3.欺诈 ||
'</td><td style="width: 15%;border:1px solid black;font-size:15px;">' ||
xscf3.违规 ||
'</td><td style="width: 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; width: 20%;border:1px solid black;font-size:15px;"><p style="color: white;">销售经理</p>
</td>
<td style="background-color: #0EAAAE; width: 15%;border:1px solid black;font-size:15px;"><p style="color: white;">合同文件错误</p>
</td>
<td style="background-color: #0EAAAE; width: 15%;border:1px solid black;font-size:15px;"><p style="color: white;">风控指标超标</p>
</td>
<td style="background-color: #0EAAAE; width: 15%;border:1px solid black;font-size:15px;"><p style="color: white;">欺诈</p>
</td>
<td style="background-color: #0EAAAE; width: 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="width: 20%;border:1px solid black;font-size:15px;">' ||
xscf4.销售经理 ||
'</td><td style="width: 15%;border:1px solid black;font-size:15px;">' ||
xscf4.合同文件错误 ||
'</td><td style="width: 15%;border:1px solid black;font-size:15px;">' ||
xscf4.风控指标超标 ||
'</td><td style="width: 15%;border:1px solid black;font-size:15px;">' ||
xscf4.欺诈 ||
'</td><td style="width: 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; width: 20%;border:1px solid black;font-size:15px;"><p style="color: white;">省份</p>
</td>
<td style="background-color: #0EAAAE; width: 15%;border:1px solid black;font-size:15px;"><p style="color: white;">使用率</p>
</td>
<td style="background-color: #0EAAAE; width: 15%;border:1px solid black;font-size:15px;"><p style="color: white;">通过率</p>
</td>
<td style="background-color: #0EAAAE; width: 15%;border:1px solid black;font-size:15px;"><p style="color: white;">3PD30</p>
</td>
<td style="background-color: #0EAAAE; width: 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="width: 20%;border:1px solid black;font-size:15px;">' ||
jjnbdm.省份 ||
'</td><td style="width: 15%;border:1px solid black;font-size:15px;">' ||
jjnbdm.使用率 ||
'</td><td style="width: 15%;border:1px solid black;font-size:15px;">' ||
jjnbdm.通过率 ||
'</td><td style="width: 15%;border:1px solid black;font-size:15px;">' ||
jjnbdm.PD30_3 ||
'</td><td style="width: 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; width: 20%;border:1px solid black;font-size:15px;"><p style="color: white;">城市</p>
</td>
<td style="background-color: #0EAAAE; width: 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="width: 20%;border:1px solid black;font-size:15px;">' ||
qzyyc1.城市 ||
'</td><td style="width: 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; width: 20%;border:1px solid black;font-size:15px;"><p style="color: white;">销售经理</p>
</td>
<td style="background-color: #0EAAAE; width: 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="width: 20%;border:1px solid black;font-size:15px;">' ||
qzyyc2.销售经理 ||
'</td><td style="width: 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; width: 20%;border:1px solid black;font-size:15px;"><p style="color: white;">省份</p>
</td>
<td style="background-color: #0EAAAE; width: 15%;border:1px solid black;font-size:15px;"><p style="color: white;">违规申请量</p>
</td>
<td style="background-color: #0EAAAE; width: 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="width: 20%;border:1px solid black;font-size:15px;">' ||
qzyyc3.省份 ||
'</td><td style="width: 15%;border:1px solid black;font-size:15px;">' ||
qzyyc3.违规申请量 ||
'</td><td style="width: 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;
浙公网安备 33010602011771号