<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: wujun  
    $Date: 2017-2-21 16:54:09  
    $Revision: 1.0  
    $Purpose: 租后管控(抵质押)主界面
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            SELECT * FROM(
            select hvcm.mortgage_seq,      --序号
			       (select pp.virtual_con_number 
			          from hn_virtual_con_relate_hd hvcrh,prj_project pp 
			         where hvcrh.hd_id = hvcm.hd_id and pp.project_id = hvcrh.project_id)||'-'|| hvcm.mortgage_seq mortgage_seq_n,
			       hvcm.mortgage_name,      --抵质押物名称
			       (select pp.virtual_con_number 
			          from hn_virtual_con_relate_hd hvcrh,prj_project pp 
			         where hvcrh.hd_id = hvcm.hd_id and pp.project_id = hvcrh.project_id) virtual_con_number,      --合同号
			       (select pp.project_name
			          from hn_virtual_con_relate_hd hvcrh,prj_project pp 
			         where hvcrh.hd_id = hvcm.hd_id and pp.project_id = hvcrh.project_id) project_name,      --合同名称
			       (SELECT nvl(sum(NVL(ccc.due_amount, 0)),0)
			          FROM con_contract_cashflow ccc,con_contract cc,hn_virtual_con_relate_hd tt
			         WHERE cc.contract_id = ccc.contract_id
			           AND tt.project_id = cc.project_id
			           AND tt.hd_id = hvcm.hd_id
			           AND cc.data_class = 'NORMAL'
			           AND ccc.times = 0
			           AND ccc.cf_item = 0) principal_amount, --本金总额
			       ((SELECT nvl(sum(NVL(ccc.due_amount, 0)),0)
			          FROM con_contract_cashflow ccc,con_contract cc,hn_virtual_con_relate_hd tt
			         WHERE cc.contract_id = ccc.contract_id
			           AND tt.project_id = cc.project_id
			           AND tt.hd_id = hvcm.hd_id
			           AND ccc.times = 0
			           AND cc.data_class = 'NORMAL'
			           AND ccc.cf_item = 0) -
			       (SELECT NVL(SUM(NVL(cwo.write_off_principal, 0)), 0)
			          FROM csh_write_off cwo,con_contract cc,hn_virtual_con_relate_hd tt
			         WHERE cwo.contract_id = cc.contract_id
			           AND tt.hd_id = hvcm.hd_id
			           AND tt.project_id = cc.project_id
			           AND cwo.write_off_type = 'RECEIPT_CREDIT'
			           AND cc.data_class = 'NORMAL'
			           AND cwo.times <> '0'
			           AND cwo.cf_item = '1'
			           AND NVL(cwo.reversed_flag, 'N') = 'N'
			           AND cwo.write_off_date < to_date(${@query_date},'yyyy-mm-dd'))) surplus_amount,     --剩余本金
			       hvcm.bp_id_mortgagor,
			       (select hbm.bp_name
			          from hls_bp_master hbm
			         where hbm.bp_id = hvcm.bp_id_mortgagor) bp_id_mortgagor_n, --抵质押人名称
			       hvcm.mortgage_type,          --抵质押类型
			       hvcm.mortgage_ast_classfication,          --抵质押物分类
			       (select v.code_value_name
			          from sys_code_values_v v
			         where v.code in ('HLS209_ASSET_MORTGAGE', 'HLS209_ASSET_PLEDGE')
			           and v.code_value = hvcm.mortgage_ast_classfication) as mortgage_ast_classfication_n,
			       hvcm.mortgage_asset_detail,
			       (select v.code_value_name
			          from sys_code_values_v v
			         where v.code in ('HLS209_ASSET_DETAIL_IMMOVABLE_PROPERTY_M',
			                          'HLS209_ASSET_DETAIL_MOVABLE_PROPERTY_M',
			                          'HLS209_ASSET_DETAIL_RIGHT_P')
			           and v.code_value = hvcm.mortgage_asset_detail) as mortgage_asset_detail_n,--明细分类
			       hvcm.mortgage_value,                      --抵质押物价值(万元)
			       hvcm.currency,                            --币种
			       (select gcv.currency_name
			          from gld_currency_v gcv
			         where gcv.currency_code = hvcm.currency) currency_n,
			       hvcm.evaluator_name,                      --评估机构
			       hvcm.evaluate_value,                      --评估价值(万)
			       hvcm.evaluator_number,                    --评估编号
			       hvcm.mortgage_end_date,                   --抵质押期限
			       hvcm.mortgage_place,                      --抵质押物所在地
			       (case
			       when hvcm.mortgage_end_date is not null then
			       (nvl((select '已解除' from dual 
			              where trunc(hvcm.mortgage_end_date) < trunc(to_date(${@query_date},'yyyy-mm-dd'))),'抵质押中'))
			        else ''
			        END ) mortgage_status,     --抵质押状态
			       hvcm.mortgage_registed_dept, 		     --登记机关
			       hvcm.mortgage_number,         		 	 --抵质押编号
			       (hn_report_pkg.get_prj_filr_name(p_check_id => hvcm.mortgage_id,
                                              		p_table_name => 'hn_virtual_con_mortgage')) file_name,  --附件名
                   (hn_report_pkg.get_prj_attachment_id(p_check_id => hvcm.mortgage_id,
                                                  		p_table_name => 'hn_virtual_con_mortgage')) attachment_id,
			       hvcm.mortgage_id
			  from hn_virtual_con_mortgage hvcm,hn_virtual_con_relate_hd hvcrh
			 where hvcm.hd_id = hvcrh.hd_id and hvcrh.project_id = ${@project_id}
			  ) t1 #WHERE_CLAUSE# order by t1.virtual_con_number,mortgage_seq
			]]></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="project_name" queryExpression="t1.project_name like &apos;%&apos;||${@project_name}||&apos;%&apos;"/>
        <bm:query-field name="mortgage_type_n" queryExpression="t1.mortgage_type like &apos;%&apos;||${@mortgage_type}||&apos;%&apos;"/>
        <bm:query-field name="evaluator_number" queryExpression="t1.evaluator_number like &apos;%&apos;||${@evaluator_number}||&apos;%&apos;"/>
        <bm:query-field name="mortgage_status_n" queryExpression="t1.mortgage_status like &apos;%&apos;||${@mortgage_status}||&apos;%&apos;"/>
    </bm:query-fields>
</bm:model>
