<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: wujun  
    $Date: 2017-2-17 16:25:30  
    $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 cbai.batch_hd_id,
               (select nvl(sum(((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 = cbail.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)))),0)
                  from con_batch_adjust_interest_ln cbail
                 where cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y') before_rent_aount,        --调整前租金余额
                 
               (select nvl(sum(((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 = cbail.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)) )),0)
                  from con_batch_adjust_interest_ln cbail
                 where cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y') after_rent_smount,           --调整后租金余额
                 
               ((select nvl(sum(((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 = cbail.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)))),0)
                  from con_batch_adjust_interest_ln cbail
                 where cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y') - 
               (select nvl(sum(((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 = cbail.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)) )),0)
                  from con_batch_adjust_interest_ln cbail
                 where cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')) rent_balance_changes,        --租金余额变动
                 
               (select nvl(sum(((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 = cbail.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)))),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y') before_wg_rent_aount,     --调整前系统内项目租金余额
                   
               (select nvl(sum(((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 = cbail.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)) )),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y') after_wg_rent_smount,    --调整后系统内项目租金余额
                   
               ((select nvl(sum(((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 = cbail.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)))),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y') -
               (select nvl(sum(((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 = cbail.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)) )),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')) rent_wg_balance_changes,    --系统内余额变动
                   
               (select nvl(sum(((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 = cbail.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)))),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y') before_ma_rent_aount,       --调整前市场化项目租金余额
                   
               (select nvl(sum(((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 = cbail.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)) )),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y') after_ma_rent_smount,      --调整后市场化项目租金余额
                   
               ((select nvl(sum(((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 = cbail.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)))),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y') -
                (select nvl(sum(((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 = cbail.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)) )),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')) market_balance_changes,    --市场化余额变动
                   
               --加权平均IRR（按投放本金）
               (select nvl(sum((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')),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y') amount,                           --本金总和
                   
               round(((select nvl(sum(((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 NVL(ccc.due_amount, 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.times = '0'
                   AND ccc.cf_item = '0'))),0)
                  from con_batch_adjust_interest_ln cbail
                 where cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum((SELECT NVL(ccc.due_amount, 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.times = '0'
                   AND ccc.cf_item = '0')),0)
                  from con_batch_adjust_interest_ln cbail
                 where cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')),6) before_irr,                   --本金调整前加权平均IRR
               
                 
               round(((select nvl(sum(((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)))*(SELECT NVL(ccc.due_amount, 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.times = '0'
                   AND ccc.cf_item = '0'))),0)
                  from con_batch_adjust_interest_ln cbail
                 where cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum((SELECT NVL(ccc.due_amount, 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.times = '0'
                   AND ccc.cf_item = '0')),0)
                  from con_batch_adjust_interest_ln cbail
                 where cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')),6) after_irr,                                      --调整后加权平均IRR
                 
               round((((select nvl(sum(((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 NVL(ccc.due_amount, 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.times = '0'
                   AND ccc.cf_item = '0'))),0)
                  from con_batch_adjust_interest_ln cbail
                 where cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum((SELECT NVL(ccc.due_amount, 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.times = '0'
                   AND ccc.cf_item = '0')),0)
                  from con_batch_adjust_interest_ln cbail
                 where cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')) -
               ((select nvl(sum(((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)))*(SELECT NVL(ccc.due_amount, 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.times = '0'
                   AND ccc.cf_item = '0'))),0)
                  from con_batch_adjust_interest_ln cbail
                 where cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum((SELECT NVL(ccc.due_amount, 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.times = '0'
                   AND ccc.cf_item = '0')),0)
                  from con_batch_adjust_interest_ln cbail
                 where cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'))),6) irr_balance_changes,             --IRR变动   
              
               decode( (select nvl(sum((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')),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),'0',0,
               round((select nvl(sum(((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 NVL(ccc.due_amount, 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.times = '0'
                   AND ccc.cf_item = '0')) ),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum((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')),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),6)) before_wg_irr,            --调整前系统内项目IRR
               
                decode((select nvl(sum((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')),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),'0',0,
               round((select nvl(sum(((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)))*(SELECT NVL(ccc.due_amount, 0)
                  FROM con_contract_cashflow ccc
                 WHERE ccc.contract_id = cc.contract_id
                   AND ccc.times = '0'
                   AND ccc.cf_item = '0')) ),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum((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')),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),6)) after_wg_irr,                               --调整后系统内项目IRR 
                  
               round((decode((select nvl(sum((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')),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),'0',0,
               (select nvl(sum(((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 NVL(ccc.due_amount, 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.times = '0'
                   AND ccc.cf_item = '0')) ),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum((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')),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')) -
                 decode((select nvl(sum((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')),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),'0',0,
               (select nvl(sum(((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)))*(SELECT NVL(ccc.due_amount, 0)
                  FROM con_contract_cashflow ccc
                 WHERE ccc.contract_id = cc.contract_id
                   AND ccc.times = '0'
                   AND ccc.cf_item = '0')) ),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum((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')),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'))),6) wg_irr_difference,                        --系统内项目IRR变动
                 
               decode((select nvl(sum((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')),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),'0',0,
               round((select nvl(sum(((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 NVL(ccc.due_amount, 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.times = '0'
                   AND ccc.cf_item = '0')) ),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum((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')),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),6)) before_ma_irr,         --调整前市场化项目IRR
                
               decode((select nvl(sum((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')),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),'0',0,
               round((select nvl(sum(((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)))*(SELECT NVL(ccc.due_amount, 0)
                  FROM con_contract_cashflow ccc
                 WHERE ccc.contract_id = cc.contract_id
                   AND ccc.times = '0'
                   AND ccc.cf_item = '0')) ),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum((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')),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),6)) after_ma_irr,                                   --调整后市场化项目IRR 
                     
               round((decode((select nvl(sum((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')),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),'0',0,
               (select nvl(sum(((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 NVL(ccc.due_amount, 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.times = '0'
                   AND ccc.cf_item = '0')) ),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum((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')),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')) -
                 decode((select nvl(sum((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')),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),'0',0,
               (select nvl(sum(((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)))*(SELECT NVL(ccc.due_amount, 0)
                  FROM con_contract_cashflow ccc
                 WHERE ccc.contract_id = cc.contract_id
                   AND ccc.times = '0'
                   AND ccc.cf_item = '0')) ),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum((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')),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'))),6) ma_irr_difference,                           --市场化项目IRR变动
              --加权平均IRR（按剩余本金）     
               
              (select nvl(sum(((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')) ),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y') sur_amount,                                        --调息后剩余本金
               
              (select nvl(sum(((SELECT NVL(ccc.due_amount, 0)
                  FROM con_contract_cashflow ccc
                 WHERE ccc.contract_id = cc.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))) ),0)
                  from con_batch_adjust_interest_ln cbail,con_contract cc
                 where cbail.contract_id = cc.contract_id
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y') s_bef_amount,                                       --调息前剩余本金
              
               decode((select nvl(sum(((SELECT NVL(ccc.due_amount, 0)
                  FROM con_contract_cashflow ccc
                 WHERE ccc.contract_id = cc.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))) ),0)
                  from con_batch_adjust_interest_ln cbail,con_contract cc
                 where cbail.contract_id = cc.contract_id
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),'0',0,
               round(((select nvl(sum(((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 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))))),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum(((SELECT NVL(ccc.due_amount, 0)
                  FROM con_contract_cashflow ccc
                 WHERE ccc.contract_id = cc.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))) ),0)
                  from con_batch_adjust_interest_ln cbail,con_contract cc
                 where cbail.contract_id = cc.contract_id
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')),6)) before_sur_irr,                              --调整前加权平均IRR
                   
               decode((select nvl(sum(((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')) ),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),'0',0,  
               round(((select nvl(sum(((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)))*((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')) )),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum(((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')) ),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')),6)) after_sur_irr,                                   --调整后加权平均IRR
                   
                   
               round(( decode((select nvl(sum(((SELECT NVL(ccc.due_amount, 0)
                  FROM con_contract_cashflow ccc
                 WHERE ccc.contract_id = cc.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))) ),0)
                  from con_batch_adjust_interest_ln cbail,con_contract cc
                 where cbail.contract_id = cc.contract_id
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),'0',0,
               round(((select nvl(sum(((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 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))))),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum(((SELECT NVL(ccc.due_amount, 0)
                  FROM con_contract_cashflow ccc
                 WHERE ccc.contract_id = cc.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))) ),0)
                  from con_batch_adjust_interest_ln cbail,con_contract cc
                 where cbail.contract_id = cc.contract_id
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')),6)) -
                decode((select nvl(sum(((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')) ),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),'0',0,  
               round(((select nvl(sum(((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)))*((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')) )),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum(((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')) ),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')),6))),6) sur_irr_balance_changes,                        --IRR变动  
                   
                   
               decode((select nvl(sum(((SELECT NVL(ccc.due_amount, 0)
                  FROM con_contract_cashflow ccc
                 WHERE ccc.contract_id = cc.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))) ),0)
                  from con_batch_adjust_interest_ln cbail,con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),'0',0,
               round(((select nvl(sum(((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 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))))),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum(((SELECT NVL(ccc.due_amount, 0)
                  FROM con_contract_cashflow ccc
                 WHERE ccc.contract_id = cc.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))) ),0)
                  from con_batch_adjust_interest_ln cbail,con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')),6)) before_sur_wg_irr,        --调整前系统内项目IRR
                   
               decode((select nvl(sum(((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')) ),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),'0',0,  
               round(((select nvl(sum(((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)))*((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')) )),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum(((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')) ),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')),6)) after_sur_wg_irr,                             --调整后系统内项目IRR    
                   
                   
               round((decode((select nvl(sum(((SELECT NVL(ccc.due_amount, 0)
                  FROM con_contract_cashflow ccc
                 WHERE ccc.contract_id = cc.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))) ),0)
                  from con_batch_adjust_interest_ln cbail,con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),'0',0,
                ((select nvl(sum(((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 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))))),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum(((SELECT NVL(ccc.due_amount, 0)
                  FROM con_contract_cashflow ccc
                 WHERE ccc.contract_id = cc.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))) ),0)
                  from con_batch_adjust_interest_ln cbail,con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'))) -
                   decode((select nvl(sum(((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')) ),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),'0',0,  
                ((select nvl(sum(((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)))*((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')) )),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum(((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')) ),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'WITHIN_GROUP'
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')))),6) sur_wg_irr_difference,                       --系统内项目IRR变动
                   
                   
               decode((select nvl(sum(((SELECT NVL(ccc.due_amount, 0)
                  FROM con_contract_cashflow ccc
                 WHERE ccc.contract_id = cc.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))) ),0)
                  from con_batch_adjust_interest_ln cbail,con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),'0',0,
               round(((select nvl(sum(((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 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))))),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum(((SELECT NVL(ccc.due_amount, 0)
                  FROM con_contract_cashflow ccc
                 WHERE ccc.contract_id = cc.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))) ),0)
                  from con_batch_adjust_interest_ln cbail,con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')),6)) before_sur_ma_irr,        --调整前市场化项目IRR
                   
                   
               decode((select nvl(sum(((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')) ),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),'0',0,  
               round(((select nvl(sum(((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)))*((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')) )),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum(((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')) ),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')),6)) after_sur_ma_irr,                              --调整后市场化项目IRR    
                   
                   
               round((decode((select nvl(sum(((SELECT NVL(ccc.due_amount, 0)
                  FROM con_contract_cashflow ccc
                 WHERE ccc.contract_id = cc.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))) ),0)
                  from con_batch_adjust_interest_ln cbail,con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),'0',0,
                 ((select nvl(sum(((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 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))))),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum(((SELECT NVL(ccc.due_amount, 0)
                  FROM con_contract_cashflow ccc
                 WHERE ccc.contract_id = cc.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))) ),0)
                  from con_batch_adjust_interest_ln cbail,con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'))) -
                   decode((select nvl(sum(((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')) ),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y'),'0',0,  
                 ((select nvl(sum(((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)))*((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')) )),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')/(select nvl(sum(((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')) ),0)
                  from con_batch_adjust_interest_ln cbail, con_contract cc
                 where cbail.contract_id = cc.contract_id and cc.hn_industry_classification = 'MARKETIZATION'
                   and cc.contract_id = (HN_REPORT_PKG.get_after_contract_id(p_change_req_id => cbail.change_req_id))
                   and cbail.batch_hd_id = cbai.batch_hd_id and cbail.adjust_flag = 'Y')))),6) sur_ma_irr_difference,     --市场化项目IRR变动
                
               cbai.batch_wfl_instance_id
              from con_batch_adjust_interest_hd cbai
			 where cbai.approval_status = 'APPROVED' and cbai.batch_hd_id = ${@batch_hd_id}
			   ]]></bm:query-sql>
            <bm:parameters>
                <bm:parameter name="batch_hd_id"/>
            </bm:parameters>
        </bm:operation>
    </bm:operations>
</bm:model>
