<?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,
             cbail.change_req_id,
             cc.contract_number,
             cc.contract_name,
             cc.hn_industry_classification,
             cc.price_list,
             cc.annual_pay_times,                      --支付频率(年)
             cc.adjust_rate_type,
             (SELECT yl.bp_name
                    FROM hls_bp_master yl
                   WHERE yl.bp_id = cc.bp_id_tenant) lessee_name, --主承租人
                sdic_report_pkg.get_contract_bp_name(p_contract_id => cc.contract_id,
                                                      p_bp_category => 'TENANT',
                                                      p_bp_type     => 'TENANT_SEC') lessee_name_sec, --次承租人
                sdic_report_pkg.get_prj_bp_name(p_project_id  => cc.project_id,
                                                 p_bp_category => 'GUARANTOR') guarantor_name, --主担保人
             (SELECT NVL(SUM(NVL(ccc.due_amount, 0)), 0)
                FROM con_contract_cashflow ccc
               WHERE ccc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                 AND ccc.cf_direction <> 'NONCASH'
                 AND ccc.cf_item in ( 1, 3, 15)) amount_rent, --调息后租金总额     
             (SELECT NVL(ccc.due_amount, 0)
                FROM con_contract_cashflow ccc
               WHERE cc.contract_id = ccc.contract_id
                 AND ccc.times = '0'
                 AND ccc.cf_item = '0') principal_amount, --本金总额
             ((SELECT NVL(ccc.due_amount, 0)
                FROM con_contract_cashflow ccc
               WHERE cc.contract_id = ccc.contract_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
                    WHERE cwo.contract_id = cc.contract_id
                      AND cwo.write_off_type = 'RECEIPT_CREDIT'
                      AND cwo.times <> '0'
                      AND cwo.cf_item = '1'
                      AND NVL(cwo.reversed_flag, 'N') = 'N'
                      AND cwo.write_off_date < =
                      (select cccrf.adj_date from con_contract_change_req_flt cccrf where cbail.change_req_id = cccrf.change_req_id))) surplus_principal,--截止调息日剩余本金
             (select cccrf.adj_date from con_contract_change_req_flt cccrf where cbail.change_req_id = cccrf.change_req_id) adj_date,--央行调息日
             (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 = cc.contract_id) adjust_date,--利率生效日(租金调整日期)
             (select ee.base_rate from con_contract ee where ee.contract_number = cc.contract_number and ee.data_class in ('NORMAL','HISTORY') and ee.flt_int_rate_adj_date is null)*100 || '%' base_rate,--第一个版本的央行基准利率 
             (select ee.int_rate from con_contract ee where ee.contract_number = cc.contract_number and ee.data_class in ('NORMAL','HISTORY') and ee.flt_int_rate_adj_date is null)*100 || '%' int_rate,--第一个版本的利率
             
             ((SELECT NVL(SUM(NVL(ccc.due_amount, 0)), 0)
                FROM con_contract_cashflow ccc
               WHERE ccc.contract_id = (HN_REPORT_PKG.get_contract_id(p_change_req_id => cbail.change_req_id))
                 AND ccc.cf_direction <> 'NONCASH'
                 AND ccc.cf_item in ( 1, 3, 15)) -
              (SELECT NVL(SUM(NVL(cwo.write_off_due_amount, '0')), 0)
                 FROM csh_write_off cwo
                WHERE cwo.contract_id = cc.contract_id
                  AND cwo.cf_item in (1, 3, 15)
                  AND NVL(cwo.reversed_flag, 'N') = 'N'
                  AND cwo.write_off_date < =
                      (select cccrf.adj_date
                         from con_contract_change_req_flt cccrf
                        where cbail.change_req_id = cccrf.change_req_id))) before_rent_amount,--调整前租金余额            
             round((select ir.irr from con_contract ir where ir.contract_id = (HN_REPORT_PKG.get_contract_id(p_change_req_id => cbail.change_req_id))),6) irr_reserved1,--调整前我司irr
             (select cct.base_rate from con_contract cct where cct.contract_id = (
              HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id)))*100 || '%' base_rate_new,--调整后基准利率（%） 
             (select ccr.int_rate from con_contract ccr where ccr.contract_id = (
              HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id)))*100 || '%' int_rate_new,--调整后基准利率（%）
             ((SELECT NVL(SUM(NVL(ccc.due_amount, 0)), 0)
                FROM con_contract_cashflow ccc
               WHERE ccc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                 AND ccc.cf_direction <> 'NONCASH'
                 AND ccc.cf_item in ( 1, 3, 15)) -
              (SELECT NVL(SUM(NVL(cwo.write_off_due_amount, '0')), 0)
                 FROM csh_write_off cwo
                WHERE cwo.contract_id = cc.contract_id
                  AND cwo.cf_item in (1, 3, 15)
                  AND NVL(cwo.reversed_flag, 'N') = 'N'
                  AND cwo.write_off_date < =
                      (select cccrf.adj_date
                         from con_contract_change_req_flt cccrf
                        where cbail.change_req_id = cccrf.change_req_id))) after_rent_amount, --调整后租金余额           
             round((select ir.irr from con_contract ir where ir.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))),6) after_irr_reserved1,--调整后我司IRR
             (((SELECT NVL(SUM(NVL(ccc.due_amount, 0)), 0)
                FROM con_contract_cashflow ccc
               WHERE ccc.contract_id = (HN_REPORT_PKG.get_contract_id(p_change_req_id => cbail.change_req_id))
                 AND ccc.cf_direction <> 'NONCASH'
                 AND ccc.cf_item in ( 1, 3, 15)) -
              (SELECT NVL(SUM(NVL(cwo.write_off_due_amount, '0')), 0)
                 FROM csh_write_off cwo
                WHERE cwo.contract_id = cc.contract_id
                  AND cwo.cf_item in (1, 3, 15)
                  AND NVL(cwo.reversed_flag, 'N') = 'N'
                  AND cwo.write_off_date < =
                      (select cccrf.adj_date
                         from con_contract_change_req_flt cccrf
                        where cbail.change_req_id = cccrf.change_req_id))) -
              ((SELECT NVL(SUM(NVL(ccc.due_amount, 0)), 0)
                FROM con_contract_cashflow ccc
               WHERE ccc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                 AND ccc.cf_direction <> 'NONCASH'
                 AND ccc.cf_item in ( 1, 3, 15)) -
              (SELECT NVL(SUM(NVL(cwo.write_off_due_amount, '0')), 0)
                 FROM csh_write_off cwo
                WHERE cwo.contract_id = cc.contract_id
                  AND cwo.cf_item in (1, 3, 15)
                  AND NVL(cwo.reversed_flag, 'N') = 'N'
                  AND cwo.write_off_date < =
                      (select cccrf.adj_date
                         from con_contract_change_req_flt cccrf
                        where cbail.change_req_id = cccrf.change_req_id)))) diversity_rent,--租金余额变动        
             round(((select ir.irr from con_contract ir where ir.contract_id = (HN_REPORT_PKG.get_contract_id(p_change_req_id => cbail.change_req_id))) -
              (select ir.irr from con_contract ir where ir.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id)))),6) diversity_irr, --我司IRR变动
             cc.project_id
        FROM con_batch_adjust_interest_ln cbail, con_contract cc
			 WHERE cbail.contract_id = cc.contract_id and cbail.adjust_flag = 'Y' and cbail.batch_hd_id = ${@batch_hd_id}
			 ) t1 #WHERE_CLAUSE# order by t1.contract_number
			 	]]></bm:query-sql>
            <bm:parameters>
                <bm:parameter name="batch_hd_id"/>
            </bm:parameters>
        </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="hn_industry_classification_n" queryExpression="t1.hn_industry_classification like &apos;%&apos;||${@hn_industry_classification}||&apos;%&apos;"/>
        <bm:query-field name="lessee_name" queryExpression="t1.lessee_name like &apos;%&apos;||${@lessee_name}||&apos;%&apos;"/>
        <bm:query-field name="lessee_name_sec" queryExpression="t1.lessee_name_sec like &apos;%&apos;||${@lessee_name_sec}||&apos;%&apos;"/>
        <bm:query-field name="guarantor_name" queryExpression="t1.guarantor_name like &apos;%&apos;||${@guarantor_name}||&apos;%&apos;"/>
        <bm:query-field name="annual_pay_times_n" queryExpression="t1.annual_pay_times like &apos;%&apos;||${@annual_pay_times}||&apos;%&apos;"/>
        <bm:query-field name="price_list_n" queryExpression="t1.price_list=${@price_list}"/>
        <bm:query-field name="adjust_rate_type_n" queryExpression="t1.adjust_rate_type like &apos;%&apos;||${@adjust_rate_type}||&apos;%&apos;"/>
    </bm:query-fields>
</bm:model>
