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