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 := ''; --根据省份循环插入报表 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 := ' | 各位领导， 请查看' || province.province || to_char(add_months(trunc(sysdate), -1), 'yyyymm') || '风控销售月报！ | |||||
| 一、客户质量报告 | |||||
| 1.多次借贷比例（上月客户在其它平台出现贷款申请的比例） | |||||
| 1)多次借贷比例_按城市排名 | |||||
城市 | 多次借贷比例 | ||||
| ' || khzlbg1.城市 || ' | ' || khzlbg1.多次借贷比例 || ' | ||||
| 2)多次借贷比例_按销售经理排名(最高前10位) | |||||
销售经理 | 多次借贷比例 | ||||
| ' || khzlbg2.销售经理 || ' | ' || khzlbg2.多次借贷比例 || ' | ||||
| *申请量不足100单的销售经理不在此排名中 | |||||
| 2.内部代码拒绝比例（上月各城市使用内部代码拒绝的比例） | |||||
| 1)内部代码拒绝比例_按城市排名 | |||||
城市 | 内部代码拒绝比例 | ||||
| ' || khzlbg3.城市 || ' | ' || khzlbg3.多次借贷比例 || ' | ||||
二、风险指标 | |||||
| 1.3PD30,FPD30（最近一个月的3PD30和FPD30） | |||||
| 1)按城市排名 | |||||
城市 | 3PD30 | FPD30 | |||
| ' || fxzb1.城市 || ' | ' || fxzb1.pd30_3 || ' | ' || fxzb1.fpd30 || ' | |||
| 2)按销售经理排名(最高前10位) | |||||
销售经理 | 3PD30 | FPD30 | |||
| ' || fxzb2.销售经理 || ' | ' || fxzb2.pd30_3 || ' | ' || fxzb2.fpd30 || ' | |||
| *申请量不足100单的销售经理不在此排名中 | |||||
三、销售处罚 | |||||
| 1.销售处罚（上月纪检会处罚人数） | |||||
| 1)销售（含销售代表及销售经理）处罚人数_按城市排名 | |||||
城市 | 开除 | 书面警告 | 口头警告 | ||
| ' || xscf1.城市 || ' | ' || xscf1.开除 || ' | ' || xscf1.书面警告 || ' | ' || xscf1.口头警告 || ' | ||
| 2)销售（含销售代表）处罚人数_按销售经理排名(处罚人数最多前10位） | |||||
销售经理 | 开除 | 书面警告 | 口头警告 | ||
| ' || xscf2.销售经理 || ' | ' || xscf2.开除 || ' | ' || xscf2.书面警告 || ' | ' || xscf2.口头警告 || ' | ||
| *销售处罚人数为0的销售经理不在此排名中 | |||||
| 2.销售处罚原因（上月纪检会销售处罚原因分布） | |||||
| 1)销售（含销售代表及销售经理）处罚原因分布_按城市排名 | |||||
城市 | 合同文件错误 | 风控指标超标 | 欺诈 | 违规 | 管理失职 |
| ' || xscf3.城市 || ' | ' || xscf3.合同文件错误 || ' | ' || xscf3.风控指标超标 || ' | ' || xscf3.欺诈 || ' | ' || xscf3.违规 || ' | ' || xscf3.管理失职 || ' |
| 2)销售（含销售代表）处罚原因分布_按销售经理排名(处罚人数最多前10位） | |||||
销售经理 | 合同文件错误 | 风控指标超标 | 欺诈 | 违规 | |
| ' || xscf4.销售经理 || ' | ' || xscf4.合同文件错误 || ' | ' || xscf4.风控指标超标 || ' | ' || xscf4.欺诈 || ' | ' || xscf4.违规 || ' | |
| *销售处罚人数为0的销售经理不在此排名中 | |||||
| 四、积极内部代码 | |||||
| 1.全国积极内部代码使用情况（上月的使用率、通过率及最近一个月的风控情况） | |||||
省份 | 使用率 | 通过率 | 3PD30 | FPD30 | |
| ' || jjnbdm.省份 || ' | ' || jjnbdm.使用率 || ' | ' || jjnbdm.通过率 || ' | ' || jjnbdm.PD30_3 || ' | ' || jjnbdm.FPD30 || ' | |
| *3PD30，FPD30定义：比率为风险指标，括号内容为(逾期量/单量），例3%(3/100)，指风险指标为3%，共有100单申请，其中3单逾期。 | |||||
| 五、欺诈与异常 | |||||
| 1.客户身份核查异常（上月后台核查发现的客户身份信息异常的申请，身份信息异常指身份信息不一致、照片不一致或照片出现PS） | |||||
| 1)身份核查异常_按城市排名 | |||||
城市 | 身份核查异常申请量 | ||||
| ' || qzyyc1.城市 || ' | ' || qzyyc1.身份核查异常比例 || ' | ||||
| 2)身份核查异常_按销售经理排名(最高前10位) | |||||
销售经理 | 身份核查异常申请量 | ||||
| ' || qzyyc2.销售经理 || ' | ' || qzyyc2.身份核查异常比例 || ' | ||||
| *身份核查异常申请量为0的销售经理不在此排名中 | |||||
| 2.全国结案案件分布（后台展开调查并于上月结案的案件中出现欺诈及违规的申请） | |||||
省份 | 违规申请量 | 欺诈申请量 | |||
| ' || qzyyc3.省份 || ' | ' || qzyyc3.违规申请量 || ' | ' || qzyyc3.欺诈申请量 || ' | |||
| 3.欺诈案件 | |||||
| ' || qzyyc4.欺诈案件 || ' | |||||
| 六、风险提示 | |||||
| ' || fxts1.风险提示 || ' | |||||