<?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
                    cc.contract_id,
                    cc.contract_number,
                    cc.contract_name,
                    cc.finance_amount,
                    cc.inception_of_lease,
                    to_char(round((CASE
				       WHEN (select count(1) from con_contract ccn where ccn.contract_number = cc.contract_number and ccn.contract_status <> 'CANCEL') > 1
				       then (case when (select count(1) from con_contract ccn where ccn.contract_number = cc.contract_number and ccn.contract_status <> 'CANCEL')  = 2 
				              then (case when cc.contract_status = 'PENDING'
				                    then cc.int_rate
				                      else (select ee.int_rate
				                              from con_contract ee
				                             where ee.contract_id =
				                               (select min(contract_id) from con_contract cco where cco.contract_number = cc.contract_number 
				                               and cco.contract_status <> 'CANCEL'
				                                   and cco.data_class <> 'NORMAL')) end)
				              else 
				            (select ee.int_rate
				          from con_contract ee
				         where ee.contract_id =
				           (select min(contract_id) from con_contract cco where cco.contract_number = cc.contract_number 
				               and cco.data_class <> 'NORMAL'
				               and cco.contract_status <> 'CANCEL')) end) 
				        else cc.int_rate end) * 100,2),'FM90.90') || '%' int_rate, --第一个版本的利率
                    to_char(round((select decode(hfch.irr,'#Formula Error!',0,'',0,hfch.irr)
			           from hls_fin_calculator_hd hfch
			          where hfch.calc_session_id = cc.calc_session_id)*100,6),'FM90.999990') || '%' tenant_irr, -- 承租人IRR
			        to_char(round((select decode(hfch.irr_reserved1,'#Formula Error!',0,'',0,hfch.irr_reserved1)
			           from hls_fin_calculator_hd hfch
			          where hfch.calc_session_id = cc.calc_session_id)*100,6),'FM90.999990') || '%' irr_reserved1, -- 我司IRR
                    cc.business_type,
                    (SELECT
                        v.description
                    FROM
                        hls_business_type_v v
                    WHERE
                        v.business_type = cc.business_type
                    ) business_type_n,
                    cc.bp_id_tenant,
                    (SELECT hbm.bp_name FROM hls_bp_master hbm WHERE hbm.bp_id = cc.bp_id_tenant
                    ) bp_id_tenant_n,
                    cc.employee_id,
                    (SELECT ee.name FROM exp_employees ee WHERE ee.employee_id = cc.employee_id
                    ) employee_id_n,
                     'v'
                        ||
                        (SELECT
                            COUNT(1)
                        FROM
                            con_contract cct
                        WHERE
                            cct.contract_number = cc.contract_number AND
                            cct.data_class     IN ('NORMAL', 'HISTORY')
                            and cct.contract_status <> 'CANCEL'
                        ) version
                FROM
                    con_contract cc
                WHERE
                    cc.data_class                      = 'NORMAL' AND
                    NVL(cc.hn_adjust_review_flag, 'N') = 'Y') t1
                    #WHERE_CLAUSE#
                ORDER BY
                    t1.contract_number DESC
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="t1.contract_number like &apos;%&apos;||${@contract_number}||&apos;%&apos;"/>
        <bm:query-field name="contract_name" queryExpression="t1.contract_name like &apos;%&apos;||${@contract_name}||&apos;%&apos;"/>
        <bm:query-field name="bp_id_tenant_n" queryExpression="t1.bp_id_tenant_n like &apos;%&apos;||${@bp_id_tenant_n}||&apos;%&apos;"/>
        <bm:query-field name="business_type" queryExpression="t1.business_type=${@business_type}"/>
        <bm:query-field name="inception_of_lease_from" queryExpression="t1.inception_of_lease &gt;=to_date(${@inception_of_lease_from},&apos;YYYY-MM-DD&apos;)"/>
        <bm:query-field name="inception_of_lease_to" queryExpression="t1.inception_of_lease &lt;=to_date(${@inception_of_lease_to},&apos;YYYY-MM-DD&apos;)"/>
        <bm:query-field name="finance_amount_from" queryExpression="t1.finance_amount &gt;=${@finance_amount_from}"/>
        <bm:query-field name="finance_amount_to" queryExpression="t1.finance_amount &lt;=${@finance_amount_to}"/>
    </bm:query-fields>
</bm:model>
