<?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 cbail.batch_hd_id,
			       cbail.batch_ln_id,
			       ccv.contract_id,
			       cbail.change_req_id,
			       rownum,
			       (SELECT cd.approval_status
			          FROM con_batch_adjust_interest_hd cd
			         WHERE cd.batch_hd_id = cbail.batch_hd_id) approval_status,
			       ccv.virtual_con_number,	--大合同号
			       ccv.virtual_con_number|| '-6' contract_number_one,--编号
			       ccv.contract_number,       --合同号
			       ccv.contract_number|| '-1' contract_number_two,--租赁附表编号
			       ccv.contract_number|| '-5' contract_number_three,--支付表编号
			       ccv.bp_id_tenant,
			       (SELECT hbm.bp_name FROM hls_bp_master hbm WHERE hbm.bp_id = ccv.bp_id_tenant) main_lessee, --主承租人
			       (select pc.pre_rental_business from prj_chance pc,prj_project pp where pp.chance_id = pc.chance_id and pp.project_id = ccv.project_id) pre_rental_business,
			       (CASE  WHEN 12 * PJ.LEASE_TERM <= 6
			              THEN '六个月以内（含6个月）'
			              WHEN 12 * PJ.LEASE_TERM > 6 AND
			                   12 * PJ.LEASE_TERM <= 12
			              THEN '六个月至一年（含1年）'
			              WHEN 12 * PJ.LEASE_TERM > 12 AND
			                   12 * PJ.LEASE_TERM <= 36
			              THEN '一至三年（含3年）'
			              WHEN 12 * PJ.LEASE_TERM > 36 AND
			                   12 * PJ.LEASE_TERM <= 60
			              THEN '三至五年（含5年）'
			              ELSE '五年以上'
			              END) term_description, --XX年期
			       (select cccrf.adj_date from con_contract_change_req_flt cccrf where cccrf.change_req_id = cbail.change_req_id) adj_date,--调整日期
			       to_char((select cccrf.adj_date from con_contract_change_req_flt cccrf where cccrf.change_req_id = cbail.change_req_id),'yyyy') adj_date_year,
			       to_char((select cccrf.adj_date from con_contract_change_req_flt cccrf where cccrf.change_req_id = cbail.change_req_id),'mm') adj_date_month,
			       to_char((select cccrf.adj_date from con_contract_change_req_flt cccrf where cccrf.change_req_id = cbail.change_req_id),'dd') adj_date_day,
			       (select cccrf.base_rate_new from con_contract_change_req_flt cccrf where cccrf.change_req_id = cbail.change_req_id)*100 || '%' base_rate_new,--调整后的基准利率
			       (round((select cccrf.int_rate_new from con_contract_change_req_flt cccrf where cccrf.change_req_id = cbail.change_req_id),6))*100 || '%' int_rate_new,--调整后的租赁利率
			       to_char((select t.adjust_date from (SELECT cc.contract_id,
			                             cc.int_rate_fixing_range,
			                             DECODE(hdh.usage_code,
			                                    'FLOATING_INTEREST_RATE',
			                                    (bgfl_special_parameter_pkg.get_con_contract_adj_date(p_contract_id => cc.contract_id)),
			                                    'HISTORY',
			                                    (SELECT cccr.req_date
			                                       FROM con_contract_change_req cccr
			                                      WHERE cccr.change_req_id =
			                                            (SELECT MAX(cch.contract_id)
			                                               FROM con_contract cch
			                                              WHERE cch.contract_id < cc.contract_id
			                                                AND cch.contract_number = cc.contract_number))) adjust_date
			                        FROM hls_document_history hdh, con_contract cc
			                       WHERE hdh.data_class = 'HISTORY'
			                         AND hdh.document_category = 'CONTRACT'
			                         AND cc.contract_id = hdh.document_history_id
			                      union
			                      SELECT cc.contract_id,
			                             cc.int_rate_fixing_range,
			                             bgfl_special_parameter_pkg.get_con_contract_adj_date(p_contract_id => cc.contract_id) adjust_date
			                        FROM con_contract cc
			                       WHERE cc.data_class = 'NORMAL' ) t where t.contract_id = ccv.contract_id),'yyyy') adjust_date_year,--利率生效日
			       to_char((select t.adjust_date from (SELECT cc.contract_id,
			                             cc.int_rate_fixing_range,
			                             DECODE(hdh.usage_code,
			                                    'FLOATING_INTEREST_RATE',
			                                    (bgfl_special_parameter_pkg.get_con_contract_adj_date(p_contract_id => cc.contract_id)),
			                                    'HISTORY',
			                                    (SELECT cccr.req_date
			                                       FROM con_contract_change_req cccr
			                                      WHERE cccr.change_req_id =
			                                            (SELECT MAX(cch.contract_id)
			                                               FROM con_contract cch
			                                              WHERE cch.contract_id < cc.contract_id
			                                                AND cch.contract_number = cc.contract_number))) adjust_date
			                        FROM hls_document_history hdh, con_contract cc
			                       WHERE hdh.data_class = 'HISTORY'
			                         AND hdh.document_category = 'CONTRACT'
			                         AND cc.contract_id = hdh.document_history_id
			                      union
			                      SELECT cc.contract_id,
			                             cc.int_rate_fixing_range,
			                             bgfl_special_parameter_pkg.get_con_contract_adj_date(p_contract_id => cc.contract_id) adjust_date
			                        FROM con_contract cc
			                       WHERE cc.data_class = 'NORMAL' ) t where t.contract_id = ccv.contract_id),'mm') adjust_date_month,--利率生效日
			       to_char((select t.adjust_date from (SELECT cc.contract_id,
			                             cc.int_rate_fixing_range,
			                             DECODE(hdh.usage_code,
			                                    'FLOATING_INTEREST_RATE',
			                                    (bgfl_special_parameter_pkg.get_con_contract_adj_date(p_contract_id => cc.contract_id)),
			                                    'HISTORY',
			                                    (SELECT cccr.req_date
			                                       FROM con_contract_change_req cccr
			                                      WHERE cccr.change_req_id =
			                                            (SELECT MAX(cch.contract_id)
			                                               FROM con_contract cch
			                                              WHERE cch.contract_id < cc.contract_id
			                                                AND cch.contract_number = cc.contract_number))) adjust_date
			                        FROM hls_document_history hdh, con_contract cc
			                       WHERE hdh.data_class = 'HISTORY'
			                         AND hdh.document_category = 'CONTRACT'
			                         AND cc.contract_id = hdh.document_history_id
			                      union
			                      SELECT cc.contract_id,
			                             cc.int_rate_fixing_range,
			                             bgfl_special_parameter_pkg.get_con_contract_adj_date(p_contract_id => cc.contract_id) adjust_date
			                        FROM con_contract cc
			                       WHERE cc.data_class = 'NORMAL' ) t where t.contract_id = ccv.contract_id),'dd') adjust_date_day,--利率生效日
			       nvl((SELECT TO_CHAR(SUM(cct.principal),'FM999,999,999,990.00')
			                 FROM con_contract_cashflow cct
			                WHERE cct.contract_id = ccv.contract_id
			                  AND cct.cf_item = 1),
			               0) sum_principal,
			       nvl((SELECT TO_CHAR(SUM(cct.interest),'FM999,999,999,990.00')
			                 FROM con_contract_cashflow cct
			                WHERE cct.contract_id = ccv.contract_id
			                  AND cct.cf_item = 1),
			               0) sum_interest,
			       NVL((SELECT TO_CHAR(SUM(cct.due_amount), 'FM999,999,999,990.00')
			             FROM con_contract_cashflow cct
			            WHERE cct.contract_id = ccv.contract_id
			              AND cct.cf_item = 3),
			           0) sum_lease_charge,
			       TO_CHAR((SELECT SUM(cct.principal)
			          FROM con_contract_cashflow cct
			         WHERE cct.contract_id = ccv.contract_id
			           AND cct.cf_item = 1) +
			       (SELECT SUM(cct.interest)
			          FROM con_contract_cashflow cct
			         WHERE cct.contract_id = ccv.contract_id
			           AND cct.cf_item = 1) +
			       NVL((SELECT SUM(cct.due_amount)
			             FROM con_contract_cashflow cct
			            WHERE cct.contract_id = ccv.contract_id
			              AND cct.cf_item = 3),
			           0),'FM999,999,999,990.00') sum_pay_amount,
			       pj.project_id
			  FROM con_batch_adjust_interest_ln cbail, con_contract_v ccv,prj_project pj
			 WHERE cbail.contract_id = ccv.contract_id and pj.project_id = ccv.project_id and cbail.batch_hd_id = ${@batch_hd_id}
			 	)t1 #WHERE_CLAUSE#
			 	]]></bm:query-sql>
            <bm:parameters>
                <bm:parameter name="batch_hd_id"/>
            </bm:parameters>
        </bm:operation>
    </bm:operations>
</bm:model>
