• oracle


    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;
  • 相关阅读:
    LeetCode344
    LeetCode18四数之和扩展N数之和
    LeetCode383赎金信
    2018-2020创业总结
    LeetCode454四数相加
    普通dll项目添加WPF的Window对象
    WPF中RadioButton的数据绑定
    02 C# 文件压缩与解压
    WPF 使用附加属性声明 ICommand
    自定义WPF分页控件
  • 原文地址:https://www.cnblogs.com/2333hh/p/5621174.html
Copyright © 2020-2023  润新知