<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            SELECT * FROM(
            select pp.project_id,
			       pp.virtual_con_number, --大合同号
			       pp.virtual_con_name, --大合同名称
			       pp.project_name, --项目名称
			       pp.hn_industry_classification, --行业分类
			       pp.binary_classification,
			       (SELECT scvv.code_value_name
			          FROM sys_code_values_v scvv
			         WHERE scvv.code IN ('WITHIN_GROUP', 'MARKETIZATION')
			           AND scvv.code_value = pp.binary_classification) binary_classification_n, --行业细分
			       pp.business_type,
			       (SELECT hbtv.description
			          FROM hls_business_type_v hbtv
			         WHERE hbtv.business_type = pp.business_type) lease_form, --租赁形式
			       pp.lease_term, --租期
			       (select NVL(sum(nvl(ccc.due_amount,0)),0)
			          from con_contract_cashflow ccc, con_contract cc
			         where ccc.contract_id = cc.contract_id
			           and cc.project_id = pp.project_id
			           and ccc.times = 0
			           AND ccc.cf_item = 0) amount_principal,      --投放本金
			       ((select NVL(sum(nvl(ccc.due_amount,0)),0)
			          from con_contract_cashflow ccc, con_contract cc
			         where ccc.contract_id = cc.contract_id
			           and cc.project_id = pp.project_id
			           and ccc.times = 0
			           AND ccc.cf_item = 0) -
			       (select NVL(sum(nvl(cwo.write_off_principal,0)),0)
			          from csh_write_off cwo, con_contract cc
			         where cwo.contract_id = cc.contract_id
			           and cc.project_id = pp.project_id
			           and cwo.write_off_type = 'RECEIPT_CREDIT'
			           and cwo.times <> 0
			           AND cwo.cf_item = 1 and NVL(cwo.reversed_flag,'N')='N')) surplus_amount,--剩余本金
			       (HN_REPORT_PKG.get_prj_full_name(p_project_id => pp.project_id,p_ref_v05 => 'LEASEBACK')) full_name_leb, --租赁物名称(动产)
			       (select nvl(sum(nvl(ppli.original_asset_value,0)),0) from prj_project_lease_item ppli
			         where ppli.project_id = pp.project_id and ppli.ref_v05 = 'LEASEBACK') original_asset_value_leb,--租赁物原值(采购总价格)
			       (select nvl(sum(nvl(ppli.lease_transfer_price,0)),0) from prj_project_lease_item ppli
			         where ppli.project_id = pp.project_id and ppli.ref_v05 = 'LEASEBACK') lease_transfer_price_leb,--租赁物账面净值(投放前租赁物账面净值)
			       
			       (select sum((ppli.lease_transfer_price -(HN_REPORT_PKG.get_accumuluated_depreciation(p_project_lease_item_id  => ppli.project_lease_item_id,
			        p_query_date => to_date(${@query_date},'YYYY-MM-DD'),p_business_type => ppli.ref_v05))))
		                from prj_project_lease_item ppli
		               where ppli.project_id = pp.project_id and ppli.ref_v05 = 'LEASEBACK') sur_transfer_price_leb,	--租赁物账面净值
		               
			       (HN_REPORT_PKG.get_prj_full_name(p_project_id => pp.project_id,p_ref_v05 => 'LEASE')) full_name, --租赁物名称(不动产)
			       (select nvl(sum(nvl(ppli.original_asset_value,0)),0) from prj_project_lease_item ppli
			         where ppli.project_id = pp.project_id and ppli.ref_v05 = 'LEASE') original_asset_value,--租赁物原值(采购总价格)
			       (select nvl(sum(nvl(ppli.lease_transfer_price,0)),0) from prj_project_lease_item ppli
			         where ppli.project_id = pp.project_id and ppli.ref_v05 = 'LEASE') lease_transfer_price,--租赁物账面净值(投放前租赁物账面净值)
			         
			       (select sum((ppli.lease_transfer_price -(HN_REPORT_PKG.get_accumuluated_depreciation(p_project_lease_item_id  => ppli.project_lease_item_id,
			        p_query_date => to_date(${@query_date},'YYYY-MM-DD'),p_business_type => ppli.ref_v05))))
		                from prj_project_lease_item ppli
		               where ppli.project_id = pp.project_id and ppli.ref_v05 = 'LEASE') sur_transfer_price,  --租赁物账面净值
			       pp.company_id
			  from prj_project pp
			 where pp.data_class = 'VIRTUAL_CON'
			   AND pp.project_status = 'APPROVED'
			   ) t1 #WHERE_CLAUSE#
			   order by t1.virtual_con_number
			]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="virtual_con_number" queryExpression="t1.virtual_con_number like &apos;%&apos;||${@virtual_con_number}||&apos;%&apos;"/>
        <bm:query-field name="virtual_con_name" queryExpression="t1.virtual_con_name like &apos;%&apos;||${@virtual_con_name}||&apos;%&apos;"/>
        <bm:query-field name="project_name" queryExpression="t1.project_name like &apos;%&apos;||${@project_name}||&apos;%&apos;"/>
        <bm:query-field name="hn_industry_classification_n" queryExpression="t1.hn_industry_classification like &apos;%&apos;||${@hn_industry_classification}||&apos;%&apos;"/>
        <bm:query-field name="binary_classification_n" queryExpression="t1.binary_classification_n like &apos;%&apos;||${@binary_classification_n}||&apos;%&apos;"/>
        <bm:query-field name="business_type_n" queryExpression="t1.business_type=${@business_type}"/>
        <bm:query-field name="amount_principal_from" queryExpression="t1.amount_principal &gt;= ${@amount_principal_from}"/>
        <bm:query-field name="amount_principal_to" queryExpression="t1.amount_principal &lt;= ${@amount_principal_to}"/>
        <bm:query-field name="surplus_amount_from" queryExpression="t1.surplus_amount &gt;= ${@surplus_amount_from}"/>
        <bm:query-field name="surplus_amount_to" queryExpression="t1.surplus_amount &lt;= ${@surplus_amount_to}"/>
        <bm:query-field name="sur_transfer_price_leb_from" queryExpression="t1.sur_transfer_price_leb &gt;= ${@sur_transfer_price_leb_from}"/>
        <bm:query-field name="sur_transfer_price_leb_to" queryExpression="t1.sur_transfer_price_leb &lt;= ${@sur_transfer_price_leb_to}"/>
    </bm:query-fields>
</bm:model>
