<?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 >=${@sum_quantity_terminate_from}"/> <bm:query-field name="sum_quantity_terminate_to" queryExpression="t1.sum_quantity_terminate <=${@sum_quantity_terminate_to}"/> <bm:query-field name="sum_quantity_from" queryExpression="t1.sum_quantity >=${@sum_quantity_from}"/> <bm:query-field name="sum_quantity_to" queryExpression="t1.sum_quantity <=${@sum_quantity_to}"/> <bm:query-field name="sum_finance_amount_from" queryExpression="t1.sum_finance_amount >=${@sum_finance_amount_from}"/> <bm:query-field name="sum_finance_amount_to" queryExpression="t1.sum_finance_amount <=${@sum_finance_amount_to}"/> <bm:query-field name="financing_amount_from" queryExpression="t1.financing_amount >=${@financing_amount_from}"/> <bm:query-field name="financing_amount_to" queryExpression="t1.financing_amount <=${@financing_amount_to}"/> <bm:query-field name="min_lease_execution_date_from" queryExpression="t1.min_lease_execution_date >=to_date(${@min_lease_execution_date_from},'YYYY-MM-DD')"/> <bm:query-field name="min_lease_execution_date_to" queryExpression="t1.min_lease_execution_date <=to_date(${@min_lease_execution_date_to},'YYYY-MM-DD')"/> <bm:query-field name="principal_total_from" queryExpression="t1.principal_total >=${@principal_total_from}"/> <bm:query-field name="principal_total_to" queryExpression="t1.principal_total <=${@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>