<?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 null*b.project_id project_id,
			       0 lease_item_seq,
			       '其他' full_name,
			       null unique_code, --编号
			       null land_certificate_num, --土地证号
			       null property_certificate_num, --房产证号
			       null site_area, --占地面积
			       null building_area, --建筑面积
			       null quantity, --数量
            	   sum(b.original_asset_value) original_asset_value,
            	   sum(b.lease_transfer_price) lease_transfer_price,
            	   null last_delivery_date, --投入使用时间
			       '' using_place, --地址
			       '5.00%' depreciation_method, --折旧方法
			       null remaining_value_ratio, --残值率
			       null start_rent_date, --首次核销日(起租日)
			       '' pattern, --规格型号
			       '' vender_name, --供应商
			       null reasonable_deprecition_years, --可使用年限
			       null guarantee_period_year, --剩余使用年限
			       null due_date, --剩余期限
			       null depreciation_rate,
			       null accumuluated_depreciation,--累计折旧
			       null surp_depreciation, --租赁物账面净值
                   null project_lease_item_id 
              from (
                   select 
                          nvl(ppli.original_asset_value, 0) original_asset_value, --租赁物原值
                          nvl(ppli.lease_transfer_price, 0) lease_transfer_price, --租赁物账面净值(原值)
                          ppli.project_id
			         from prj_project_lease_item ppli, prj_project pp
			        where ppli.ref_v05 = 'LEASE'
			          and pp.project_id = ppli.project_id
			          and pp.project_id = ${@project_id}
			          and not exists (select 1
			                 from prj_project_lease_item ppl
			                where ppl.project_lease_item_id =
			                      ppli.project_lease_item_id
			                  and (ppl.full_name like '%建筑物%' or
			                      ppl.full_name like '%土地%')))b group by b.project_id
			       union
			       select ppli.project_id,
			              rank() over(partition by pp.project_id order by ppli.project_lease_item_id) lease_item_seq, --序号
			              ppli.full_name, --设备名称
			              ppli.unique_code, --编号
			              ppli.land_certificate_num, --土地证号
			              ppli.property_certificate_num, --房产证号
			              ppli.site_area, --占地面积
			              ppli.building_area, --建筑面积
			              ppli.quantity, --数量
			              ppli.original_asset_value, --租赁物原值
			              ppli.lease_transfer_price, --租赁物账面净值(原值)
			              ppli.last_delivery_date, --投入使用时间
			              
			              --投放前剩余使用年限
			              ppli.using_place, --地址
			              ppli.depreciation_method, --折旧方法
			              to_char((nvl(ppli.remaining_value_ratio,0.05)) * 100, 'FM90.00') || '%' remaining_value_ratio, --残值率
			              (select MIN(cwo.write_off_date)
			                 from csh_write_off cwo, con_contract cc
			                where cwo.contract_id = cc.contract_id
			                  and cwo.times = 0
			                  and NVL(cwo.reversed_flag, 'N') = 'N'
			                  and cwo.write_off_type = 'PAYMENT_DEBT'
			                  and cc.project_id = pp.project_id) start_rent_date, --首次核销日(起租日)
			              ppli.pattern, --规格型号
			              ppli.vender_name, --供应商
			              ppli.reasonable_deprecition_years, --可使用年限
			              ppli.guarantee_period_year, --剩余使用年限 
			              to_char(round((((select MAX(ccc.due_date) from con_contract_cashflow ccc,con_contract cc where ccc.contract_id = cc.contract_id and cc.project_id = pp.project_id)
			        		-to_date(${@query_date},'yyyy-mm-dd'))/365),2),'FM90.00') due_date, --剩余期限
			        		
			        	  (to_char((HN_REPORT_PKG.get_depreciation_rate(p_project_lease_item_id  => ppli.project_lease_item_id,p_query_date => to_date(${@query_date},'YYYY-MM-DD'),p_business_type => 'LEASEBACK'))*100,'FM90.000000')
				             )|| '%' depreciation_rate,--折旧率
		             
		                  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 => 'LEASEBACK') accumuluated_depreciation,--累计折旧
				          (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 => 'LEASEBACK'))) surp_depreciation, --租赁物账面净值
			        	  
			              ppli.project_lease_item_id
			         from prj_project_lease_item ppli, prj_project pp
			        where ppli.ref_v05 = 'LEASE'
			          and pp.project_id = ppli.project_id
			          and pp.project_id = ${@project_id}
			          and exists (select 1
			                 from prj_project_lease_item ppl
			                where ppl.project_lease_item_id =
			                      ppli.project_lease_item_id
			                  and (ppl.full_name like '%建筑物%' or
			                      ppl.full_name like '%土地%'))
			     ) t1 #WHERE_CLAUSE# ORDER BY t1.lease_item_seq
			]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="full_name" queryExpression="t1.full_name like &apos;%&apos;||${@full_name}||&apos;%&apos;"/>
        <bm:query-field name="lease_transfer_price_from" queryExpression="t1.lease_transfer_price &gt;= ${@lease_transfer_price_from}"/>
        <bm:query-field name="lease_transfer_price_to" queryExpression="t1.lease_transfer_price &lt;= ${@lease_transfer_price_to}"/>
        <bm:query-field name="surp_depreciation_from" queryExpression="t1.surp_depreciation &gt;= ${@surp_depreciation_from}"/>
        <bm:query-field name="surp_depreciation_to" queryExpression="t1.surp_depreciation &lt;= ${@surp_depreciation_to}"/>
        <!-- <bm:query-field name="binary_classification_n" queryExpression="t1.full_name like &apos;%&apos;||${@binary_classification_n}||&apos;%&apos;"/> -->
    </bm:query-fields>
</bm:model>
