<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: weiwuzhen  
    $Date: 2016-5-9 下午2:15:27  
    $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  
pbp.project_id,
pbp.prj_bp_id,
 ih1.fiscal_year fiscal_year_1, --年份1
 ih2.fiscal_year fiscal_year_2, --年份2
 (select  decode(lv.indicator_value,0,'0.00',rtrim(to_char(lv.indicator_value*100 ,'999999990.009999'),'0'))||'%' from rsc_fin_indicator_lns_v lv where
 lv.fin_statement_id=ih1.fin_statement_id
 AND LV.indicator_code='INDEX_33' ) index_33_1, --年份1对应财务报表
 (select  decode(lv.indicator_value,0,'0.00',rtrim(to_char(lv.indicator_value*100 ,'999999990.009999'),'0'))||'%' from rsc_fin_indicator_lns_v lv where
 lv.fin_statement_id=ih2.fin_statement_id
 AND LV.indicator_code='INDEX_33' ) index_33_2,--年份2对应财务包边
 (select  decode(lv.indicator_value,0,'0.00',rtrim(to_char(lv.indicator_value*100 ,'999999990.009999'),'0'))||'%' from rsc_fin_indicator_lns_v lv where
 lv.fin_statement_id=ih1.fin_statement_id
 AND LV.indicator_code='INDEX_31' ) index_31_1,
 (select  decode(lv.indicator_value,0,'0.00',rtrim(to_char(lv.indicator_value*100 ,'999999990.009999'),'0'))||'%' from rsc_fin_indicator_lns_v lv where
 lv.fin_statement_id=ih2.fin_statement_id
 AND LV.indicator_code='INDEX_31' ) index_31_2,
 (select  decode(lv.indicator_value,0,'0.00',rtrim(to_char(lv.indicator_value*100 ,'999999990.009999'),'0'))||'%' from rsc_fin_indicator_lns_v lv where
 lv.fin_statement_id=ih1.fin_statement_id
 AND LV.indicator_code='INDEX_27' ) index_27_1,
 (select  decode(lv.indicator_value,0,'0.00',rtrim(to_char(lv.indicator_value*100 ,'999999990.009999'),'0'))||'%' from rsc_fin_indicator_lns_v lv where
 lv.fin_statement_id=ih2.fin_statement_id
 AND LV.indicator_code='INDEX_27' ) index_27_2,
 (select  decode(lv.indicator_value,0,'0.00',rtrim(to_char(lv.indicator_value*100 ,'999999990.009999'),'0'))||'%' from rsc_fin_indicator_lns_v lv where
 lv.fin_statement_id=ih1.fin_statement_id
 AND LV.indicator_code='INDEX_23' ) index_23_1,
 (select  decode(lv.indicator_value,0,'0.00',rtrim(to_char(lv.indicator_value*100 ,'999999990.009999'),'0'))||'%' from rsc_fin_indicator_lns_v lv where
 lv.fin_statement_id=ih2.fin_statement_id
 AND LV.indicator_code='INDEX_23' ) index_23_2,
 (select  decode(lv.indicator_value,0,'0.00',rtrim(to_char(lv.indicator_value*100 ,'999999990.009999'),'0'))||'%' from rsc_fin_indicator_lns_v lv where
 lv.fin_statement_id=ih1.fin_statement_id
 AND LV.indicator_code='INDEX_19' ) index_19_1,
 (select  decode(lv.indicator_value,0,'0.00',rtrim(to_char(lv.indicator_value*100 ,'999999990.009999'),'0'))||'%' from rsc_fin_indicator_lns_v lv where
 lv.fin_statement_id=ih2.fin_statement_id
 AND LV.indicator_code='INDEX_19' ) index_19_2,
 (select  decode(lv.indicator_value,0,'0.00',rtrim(to_char(lv.indicator_value*100 ,'999999990.009999'),'0'))||'%' from rsc_fin_indicator_lns_v lv where
 lv.fin_statement_id=ih1.fin_statement_id
 AND LV.indicator_code='INDEX_17' ) index_17_1,
 (select  decode(lv.indicator_value,0,'0.00',rtrim(to_char(lv.indicator_value*100 ,'999999990.009999'),'0'))||'%' from rsc_fin_indicator_lns_v lv where
 lv.fin_statement_id=ih2.fin_statement_id
 AND LV.indicator_code='INDEX_17' ) index_17_2,
 (select  decode(lv.indicator_value,0,'0.00',rtrim(to_char(lv.indicator_value*100 ,'999999990.009999'),'0'))||'%' from rsc_fin_indicator_lns_v lv where
 lv.fin_statement_id=ih1.fin_statement_id
 AND LV.indicator_code='INDEX_08' ) index_08_1,
 (select  decode(lv.indicator_value,0,'0.00',rtrim(to_char(lv.indicator_value*100 ,'999999990.009999'),'0'))||'%' from rsc_fin_indicator_lns_v lv where
 lv.fin_statement_id=ih2.fin_statement_id
 AND LV.indicator_code='INDEX_08' ) index_08_2,
 (select  decode(lv.indicator_value,0,'0.00',rtrim(to_char(lv.indicator_value*100 ,'999999990.009999'),'0'))||'%' from rsc_fin_indicator_lns_v lv where
 lv.fin_statement_id=ih1.fin_statement_id
 AND LV.indicator_code='INDEX_01' ) index_01_1,
 (select  decode(lv.indicator_value,0,'0.00',rtrim(to_char(lv.indicator_value*100 ,'999999990.009999'),'0'))||'%' from rsc_fin_indicator_lns_v lv where
 lv.fin_statement_id=ih2.fin_statement_id
 AND LV.indicator_code='INDEX_01' ) index_01_2,
 (select  decode(lv.indicator_value,0,'0.00',rtrim(to_char(lv.indicator_value*100 ,'999999990.009999'),'0'))||'%' from rsc_fin_indicator_lns_v lv where
 lv.fin_statement_id=ih1.fin_statement_id
 AND LV.indicator_code='INDEX_35' ) index_35_1,
 (select  decode(lv.indicator_value,0,'0.00',rtrim(to_char(lv.indicator_value*100 ,'999999990.009999'),'0'))||'%' from rsc_fin_indicator_lns_v lv where
 lv.fin_statement_id=ih2.fin_statement_id
 AND LV.indicator_code='INDEX_35' ) index_35_2
 
 
 from
 prj_project_bp pbp,
 rsc_fin_indicator_hds IH1,
 rsc_fin_indicator_hds IH2,
 (select HD.FIN_STATEMENT_TEMPLET_ID from 
 rsc_fin_statement_tmplt_hds HD WHERE HD.FIN_STATEMENT_TEMPLET_CODE='HNTC_FIN_NORMAL') T1
 where pbp.bp_category='TENANT'
 AND PBP.BP_TYPE='TENANT'
 AND 
 rsc_fin_statement_prj_pkg.get_fin_statement_id(T1.FIN_STATEMENT_TEMPLET_ID, ${/session/@company_id}, PBP.BP_ID, 4)
 =IH1.FIN_STATEMENT_ID

 AND 
 rsc_fin_statement_prj_pkg.get_fin_statement_id(T1.FIN_STATEMENT_TEMPLET_ID, ${/session/@company_id}, PBP.BP_ID, 3)

 =IH2.FIN_STATEMENT_ID
and pbp.project_id=${/parameter/@project_id}
]]></bm:query-sql>
            <bm:parameters>
                <bm:parameter inputPath="/session/@company_id"/>
                <bm:parameter inputPath="/parameter/@project_id"/>
            </bm:parameters>
        </bm:operation>
    </bm:operations>
</bm:model>
