con_agent_query.lwm

<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:s="leaf.plugin.script" xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[

           select *
  from (select hbma.bp_code bp_code_lately, --客户项下合同中，执行日期离当前最近的一笔代理商编号
               hbma.bp_name bp_name_lately, --客户项下合同中，执行日期离当前最近的一笔代理商名称
               cct.contract_number contract_number_lately, --客户项下合同中，执行日期离当前最近的一笔的合同编号
               t.bp_code,
               t.bp_name,
               t.bp_class,
               (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE v.code = 'HLS211_BP_CLASS' AND v.code_value = t.bp_class)
             AS bp_class_n,
               cct.sys_level sys_level_lately, --该客户项下合同中执行日期离系统日期最近的那个合同的AS等级
               (select max(cc.bs_level)
                  from con_contract cc
                 where cc.data_class = 'NORMAL'
                   and cc.bp_id_tenant = t.bp_id
                   and cc.lease_execution_flag = 'Y'
                   and cc.lease_execution_date is not null) bs_level, --显示该客户项下所有执行中的合同里，BS分数数值最大的分数
               (select sum(nvl(li.quantity, 0))
                  from con_contract cc, con_contract_lease_item li
                 where cc.contract_id = li.contract_id
                   and cc.data_class = 'NORMAL'
                   and cc.bp_id_tenant = t.bp_id
                   and li.equipment_type = 'MAIN'
                   and cc.lease_execution_flag = 'Y'
                   and cc.lease_execution_date is not null) sum_quantity_terminate, --执行了的合同（包括结清的）的设备台数之和
               (select sum(li.quantity)
                  from con_contract cc, con_contract_lease_item li
                 where cc.contract_id = li.contract_id
                   and li.equipment_type = 'MAIN'
                   and cc.data_class = 'NORMAL'
                   and cc.bp_id_tenant = t.bp_id
                   and cc.contract_status = 'INCEPT') sum_quantity, --合同（不包括结清的）的设备台数之和
               (select min(cc.lease_execution_date)
                  from con_contract cc
                 where cc.data_class = 'NORMAL'
                   and cc.bp_id_tenant = t.bp_id
                   and cc.lease_execution_flag = 'Y'
                   and cc.lease_execution_date is not null) min_lease_execution_date, --该客户所有合同中执行日期最早的日期
               (select max(ccc.calc_date)
                  from con_contract cc, con_contract_cashflow ccc
                 where cc.contract_id = ccc.contract_id
                   and cc.bp_id_tenant = t.bp_id
                   and cc.data_class = 'NORMAL'
                   and cc.contract_status in
                       ('INCEPT',
                        'TERMINATE',
                        'ET',
                        'AGENT_TERMINATE',
                        'LOSSSHARE_TERMINATE',
                        'VIRTUAL_TERMINATE')
                   and ccc.times =
                       (select max(ccc2.times)
                          from con_contract_cashflow ccc2
                         where ccc2.contract_id = cc.contract_id)) max_calc_date, --该客户所有合同中最后一期的计算日最晚的日期
               (select nvl(sum(nvl(cc.finance_amount, 0)), 0)
                  from con_contract cc
                 where cc.bp_id_tenant = t.bp_id
                   and cc.lease_execution_flag = 'Y'
                   and cc.data_class = 'NORMAL') sum_finance_amount, --该客户所有合同的融资额之和
               (select nvl(sum(nvl(ccc.received_principal, 0)), 0)
                  from con_contract cc, con_contract_cashflow ccc
                 where cc.bp_id_tenant = t.bp_id
                   and ccc.contract_id = cc.contract_id
                   and cc.data_class = 'NORMAL'
                   and cc.lease_execution_flag = 'Y'
                   and ccc.cf_item in (1, 102, 103, 104)
                   and cc.data_class = 'NORMAL') sum_received_amount, --该客户所有合同已还本金之和
               (select ((select nvl(sum(nvl(cc.finance_amount, 0)), 0)
                           from con_contract cc
                          where cc.bp_id_tenant = t.bp_id
                            and cc.data_class = 'NORMAL') -
                       (select nvl(sum(nvl(ccc.received_principal, 0)), 0)
                           from con_contract cc, con_contract_cashflow ccc
                          where cc.bp_id_tenant = t.bp_id
                            and ccc.contract_id = cc.contract_id
                            and ccc.cf_item in (1, 102, 103, 104)
                            and cc.data_class = 'NORMAL'
                            AND ccc.cf_status = 'RELEASE'
                            AND ccc.cf_direction = 'INFLOW'))
                  from dual) financing_amount, --融资中金额
               (SELECT nvl(SUM(nvl(t2.principal, 0) -
                               nvl(t2.received_principal,0)),
                           0)
                  FROM con_contract_cashflow t2, con_contract t1
                 WHERE t2.contract_id = t1.contract_id
                   and t1.bp_id_tenant = t.bp_id
                   AND t1.data_class = 'NORMAL'
                   and t1.lease_execution_flag = 'Y'
                   AND t2.cf_item = 1
                      --AND t2.write_off_flag <> 'FULL'
                   AND trunc(t2.due_date) < trunc(SYSDATE)
                   AND t2.cf_status = 'RELEASE'
                   AND t2.cf_direction = 'INFLOW') principal_total, --该客户所有合同的逾期本金之和
               (SELECT nvl(sum(decode(t2.cf_item,
                                      1,
                                      nvl(t2.interest, 0) -
                                      nvl(t2.received_interest,0),
                                      nvl(t2.due_amount, 0) -
                                      nvl(t2.received_amount,0))),
                           0)
                  FROM con_contract_cashflow t2, con_contract t1
                 WHERE t2.contract_id = t1.contract_id
                   AND t1.bp_id_tenant = t.bp_id
                   AND t1.data_class = 'NORMAL'
                   and t1.lease_execution_flag = 'Y'
                   AND t2.cf_item in (1, 102, 103, 104)
                      --AND t2.write_off_flag <> 'FULL'
                   AND trunc(t2.due_date) < trunc(SYSDATE)
                   AND t2.cf_status = 'RELEASE'
                   AND t2.cf_direction = 'INFLOW') interest_total, --该客户所有合同的逾期利息之和
               (SELECT nvl(SUM(nvl(t2.due_amount, 0)), 0)
                  FROM con_contract_cashflow t2, con_contract t1
                 WHERE t2.contract_id = t1.contract_id
                   and t1.bp_id_tenant = t.bp_id
                   AND t1.data_class = 'NORMAL'
                   and t1.lease_execution_flag = 'Y'
                   and t2.times <> 0
                   AND t2.cf_item = 9
                      -- AND t2.write_off_flag <> 'FULL'
                   AND t2.cf_status = 'RELEASE'
                   AND t2.cf_direction = 'INFLOW') overdue_total, --该客户所有合同的总罚息金额
               (SELECT nvl(SUM(nvl(t2.principal, 0) -
                               nvl(t2.received_principal,0)),
                           0)
                  FROM con_contract_cashflow t2, con_contract t1
                 WHERE t2.contract_id = t1.contract_id
                   and t1.bp_id_tenant = t.bp_id
                   AND t1.data_class = 'NORMAL'
                   AND t1.contract_status NOT IN ('NEW', 'SIGN')
                   AND t2.cf_item in (1, 102, 103, 104)
                   AND t2.due_date >= trunc(sysdate)
                   AND t2.cf_status = 'RELEASE'
                   AND t2.cf_direction = 'INFLOW') no_principal_total, --该客户所有合同的未到期本金之和
               (SELECT nvl(sum(decode(t2.cf_item,
                                      1,
                                      nvl(t2.interest, 0) -
                                      nvl(t2.received_interest,0),
                                      nvl(t2.due_amount, 0) -
                                      nvl(t2.received_amount,0))),
                           0)
                  FROM con_contract_cashflow t2, con_contract t1
                 WHERE t2.contract_id = t1.contract_id
                   and t1.bp_id_tenant = t.bp_id
                   AND t1.data_class = 'NORMAL'
                   AND t1.contract_status NOT IN ('NEW', 'SIGN')
                   AND t2.cf_item = 1
                   AND t2.due_date >= trunc(sysdate)
                   AND t2.cf_status = 'RELEASE'
                   AND t2.cf_direction = 'INFLOW') no_interest_total, --该客户所有合同的未到期利息之和
               (Select nvl(Sum(nvl(ccc.received_amount, 0)), 0)
                  From con_contract_cashflow ccc, con_contract cc
                 Where ccc.cf_item = 9
                   And ccc.contract_id = cc.contract_id
                   and cc.data_class = 'NORMAL'
                   And cc.bp_id_tenant = t.bp_id
                   and ccc.times <> 0
                   And ccc.cf_direction = 'INFLOW'
                   And ccc.cf_status = 'RELEASE') received_penalty, --该客户所有合同已核销逾期罚金之和
               decode(t.bp_class,
                      'NP',
                      t.cell_phone,
                      'ORG',
                      t.work_unit_phone) contact_information,
               cct.deduct_bank, --该客户项下合同中，协议/绑定日期离当前时间最近的合同扣款账户的扣款方式
               (SELECT COUNT(1)
                  FROM con_contract_cashflow t2, con_contract t1
                 WHERE t2.contract_id = t1.contract_id
                   and t1.bp_id_tenant = t.bp_id
                   AND t1.data_class = 'NORMAL'
                   and t1.lease_execution_flag = 'Y'
                   AND t2.cf_item = 9
                   and t2.due_amount > 0
                   AND t2.cf_status = 'RELEASE'
                   AND t2.cf_direction = 'INFLOW') overdue_times, --该客户项下所有合同逾期次数之和
               (select customer_inquiry_report_pkg.CUSTOMER_INQUIRY_OVERDUE_TIMES(t.bp_id)
                  from dual) max_overdue_times, --该客户项下所有合同中逾期次数最多的合同的逾期数值
               decode(t.bp_class, 'NP', t.send_address, 'ORG', t.liv_street) mailing_address,
               t.bp_id,
               t.IS_EXCELLENT_CUSTOMER,

               (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE v.code = 'YES_NO' AND v.code_value = t.IS_EXCELLENT_CUSTOMER)
             AS IS_EXCELLENT_CUSTOMER_n,
               t.EXCELLENT_CUSTOMER_REMARK
          from hls_bp_master t
          left outer join con_contract cct
            on cct.contract_id =
               customer_inquiry_report_pkg.CUSTOMER_INQUIRY_CON(t.bp_id)
          left outer join hls_bp_master hbma
            on hbma.bp_id = cct.bp_id_agent_level1
         where exists (select 1
                  from hls_bp_master_role r
                 where r.bp_id = t.bp_id
                   and t.bp_category = 'TENANT'
                   and r.enabled_flag = 'Y')) t1
            #WHERE_CLAUSE#
#ORDER_BY_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="bp_code_lately"/>
        <bm:field name="bp_name_lately"/>
        <bm:field name="contract_number_lately"/>
        <bm:field name="bp_code"/>
        <bm:field name="bp_name"/>
        <bm:field name="bp_class"/>
        <bm:field name="bp_class_n"/>
        <bm:field name="sys_level_lately"/>
        <bm:field name="bs_level"/>
        <bm:field name="sum_quantity_terminate"/>
        <bm:field name="sum_quantity"/>
        <bm:field name="min_lease_execution_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="max_calc_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="sum_finance_amount"/>
        <bm:field name="sum_received_amount"/>
        <bm:field name="financing_amount"/>
        <bm:field name="principal_total"/>
        <bm:field name="interest_total"/>
        <bm:field name="overdue_total"/>
        <bm:field name="no_principal_total"/>
        <bm:field name="no_interest_total"/>
        <bm:field name="received_penalty"/>
        <bm:field name="contact_information"/>
        <bm:field name="deduct_bank"/>
        <bm:field name="overdue_times"/>
        <bm:field name="max_overdue_times"/>
        <bm:field name="mailing_address"/>
        <bm:field name="is_excellent_customer_n"/>
        <bm:field name="excellent_customer_remark"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field field="bp_code" queryOperator="like"/>
        <bm:query-field field="bp_name" queryOperator="like"/>
        <bm:query-field field="bp_code_lately" queryOperator="like"/>
        <bm:query-field field="bp_class" queryOperator="="/>
        <bm:query-field field="sys_level_lately" queryOperator="="/>
        <bm:query-field name="sum_quantity_terminate_from" queryExpression="t1.sum_quantity_terminate &gt;=${@sum_quantity_terminate_from}"/>
        <bm:query-field name="sum_quantity_terminate_to" queryExpression="t1.sum_quantity_terminate &lt;=${@sum_quantity_terminate_to}"/>
        <bm:query-field name="sum_quantity_from" queryExpression="t1.sum_quantity &gt;=${@sum_quantity_from}"/>
        <bm:query-field name="sum_quantity_to" queryExpression="t1.sum_quantity &lt;=${@sum_quantity_to}"/>
        <bm:query-field name="sum_finance_amount_from" queryExpression="t1.sum_finance_amount &gt;=${@sum_finance_amount_from}"/>
        <bm:query-field name="sum_finance_amount_to" queryExpression="t1.sum_finance_amount &lt;=${@sum_finance_amount_to}"/>
        <bm:query-field name="financing_amount_from" queryExpression="t1.financing_amount &gt;=${@financing_amount_from}"/>
        <bm:query-field name="financing_amount_to" queryExpression="t1.financing_amount &lt;=${@financing_amount_to}"/>
        <bm:query-field name="min_lease_execution_date_from" queryExpression="t1.min_lease_execution_date &gt;=to_date(${@min_lease_execution_date_from},&apos;YYYY-MM-DD&apos;)"/>
        <bm:query-field name="min_lease_execution_date_to" queryExpression="t1.min_lease_execution_date &lt;=to_date(${@min_lease_execution_date_to},&apos;YYYY-MM-DD&apos;)"/>
        <bm:query-field name="principal_total_from" queryExpression="t1.principal_total &gt;=${@principal_total_from}"/>
        <bm:query-field name="principal_total_to" queryExpression="t1.principal_total &lt;=${@principal_total_to}"/>
    </bm:query-fields>
    <s:bm-script><![CDATA[
            var cx = Packages.leaf.javascript.Context.getCurrentContext();
            Packages.leaf.plugin.script.engine.ScriptImportor.defineExternScript(cx, this, $ctx.getData(), "aut_authority_bm_validate.js");
        ]]></s:bm-script>

</bm:model>