<?xml version="1.0" encoding="UTF-8"?> <bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false"> <bm:operations> <bm:operation name="query"> <bm:query-sql><![CDATA[ select (select nvl(sum(nvl(ci.set_number, 1)), 0) from con_contract cc, con_contract_lease_item ci where cc.lease_start_date = TRUNC(sysdate ) and ci.contract_id = cc.contract_id and cc.contract_status = 'INCEPT' and cc.data_class = 'NORMAL') day_num, --日台数 nvl((select to_char(round(sum(nvl(cc.total_rental, 0)) + sum(nvl(cc.residual_value, 0)),2),'fm99999999999990.00') from con_contract cc where cc.lease_start_date = TRUNC(sysdate ) and cc.contract_status = 'INCEPT' and cc.data_class = 'NORMAL'), 0) day_money, --日金额 (select nvl(sum(nvl(ci.set_number, 1)),0) from con_contract cc, con_contract_lease_item ci where cc.lease_start_date between trunc(sysdate, 'mm') and trunc(sysdate) and ci.contract_id = cc.contract_id and cc.contract_status = 'INCEPT' and cc.data_class = 'NORMAL') month_num, --月数量 nvl((select to_char(round(sum(nvl(cc.total_rental, 0)) + sum(nvl(cc.residual_value, 0)),2),'fm99999999999990.00') from con_contract cc where cc.lease_start_date between trunc(sysdate, 'mm') and trunc(sysdate) and cc.contract_status = 'INCEPT' and cc.data_class = 'NORMAL'), 0) month_money, --月金额 case when trunc(sysdate) > to_date(to_char(sysdate, 'yyyy') || '0331', 'yyyymmdd') then (select sum(nvl(ci.set_number, 1)) from con_contract cc, con_contract_lease_item ci where cc.lease_start_date between to_date(to_char(sysdate, 'yyyy') || '0401', 'yyyymmdd') and to_date(to_char(add_months(sysdate, 12), 'yyyy') || '0331', 'yyyymmdd') and ci.contract_id = cc.contract_id and cc.contract_status = 'INCEPT' and cc.data_class = 'NORMAL') else (select sum(nvl(ci.set_number, 1)) from con_contract cc, con_contract_lease_item ci where cc.lease_start_date between to_date(to_char(add_months(sysdate, -12), 'yyyy') || '0401', 'yyyymmdd') and to_date(to_char(sysdate, 'yyyy') || '0331', 'yyyymmdd') and cc.contract_status = 'INCEPT' and ci.contract_id = cc.contract_id and cc.data_class = 'NORMAL') end as year_num, --年台数 (case when trunc(sysdate) > to_date(to_char(sysdate, 'yyyy') || '0331', 'yyyymmdd') then (select to_char(round(sum(nvl(cc.total_rental, 0)) + sum(nvl(cc.residual_value, 0)),2),'fm99999999999990.00') from con_contract cc where cc.lease_start_date between to_date(to_char(sysdate, 'yyyy') || '0401', 'yyyymmdd') and to_date(to_char(add_months(sysdate, 12), 'yyyy') || '0331', 'yyyymmdd') and cc.contract_status = 'INCEPT' and cc.data_class = 'NORMAL') else (select to_char(round(sum(nvl(cc.total_rental, 0)) + sum(nvl(cc.residual_value, 0)),2),'fm99999999999990.00') from con_contract cc where cc.lease_start_date between to_date(to_char(add_months(sysdate, -12), 'yyyy') || '0401', 'yyyymmdd') and to_date(to_char(sysdate, 'yyyy') || '0331', 'yyyymmdd') and cc.contract_status = 'INCEPT' and cc.data_class = 'NORMAL') end) year_money, --年金额 (select nvl(sum(nvl(ci.set_number, 1)), 0) from con_contract cc, con_contract_lease_item ci where (cc.contract_status in ('INCEPT', 'REPURING', 'ETING') or (cc.contract_status = 'PENDING' and cc.project_id is not null and exists (select 1 from zj_wfl_workflow_instance_v zw, ZJ_WFL_WORKFLOW_INSTANCE_PARA zi, zj_wfl_workflow_type_parameter zp where zw.workflow_code = 'CONTRACT_INCEPT_WFL' and zw.instance_status = '10' and zi.workflow_type_para_id = zp.workflow_type_para_id and zp.parameter_code = 'CONTRACT_ID' and zw.instance_id = zi.instance_id and to_char(cc.contract_id) = zi.parameter_value))or (cc.contract_status = 'PENDING' and cc.project_id is null)) and ci.contract_id = cc.contract_id and cc.lease_start_date <= TRUNC(sysdate ) and cc.data_class = 'NORMAL') all_num, --昨日资产台数 nvl((select to_char(round(sum(ccc.due_amount) - sum(nvl(ccc.received_amount, 0)),2),'fm99999999999990.00') from con_contract_cashflow ccc where ccc.contract_id in (select cc.contract_id from con_contract cc where (cc.contract_status in ('INCEPT', 'REPURING', 'ETING') or (cc.contract_status = 'PENDING' and cc.project_id is not null and exists (select 1 from zj_wfl_workflow_instance_v zw, ZJ_WFL_WORKFLOW_INSTANCE_PARA zi, zj_wfl_workflow_type_parameter zp where zw.workflow_code = 'CONTRACT_INCEPT_WFL' and zw.instance_status = '10' and zi.workflow_type_para_id = zp.workflow_type_para_id and zp.parameter_code = 'CONTRACT_ID' and zw.instance_id = zi.instance_id and to_char(cc.contract_id) = zi.parameter_value))or (cc.contract_status = 'PENDING' and cc.project_id is null)) and cc.lease_start_date <= TRUNC(sysdate) and cc.data_class = 'NORMAL') and ccc.cf_status = 'RELEASE' and ccc.cf_item in (1, 8, 9,250,200,11)), 0) all_money --昨日资产金额 from dual ]]></bm:query-sql> </bm:operation> </bm:operations> </bm:model>