<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: jack  
    $Date: 2014-6-3 下午4:42:15  
    $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 f.indicator_type ,
       f.indicator_name ,
       f.indicator_code,
       f.line_number rnum,
       f.percent_flag,
       (select t.code_value_name from sys_code_values_v t where t.code ='INDICATOR_TYPE' and t.code_value=f.indicator_type ) indicator_type_desc,
       decode(f.percent_flag,'Y',year_1.indicator_value*100||'%','N',year_1.indicator_value) first_year_change_rate,
       decode(f.percent_flag,'Y',year_2.indicator_value*100||'%','N',year_2.indicator_value) second_year_change_rate,
       decode(f.percent_flag,'Y',year_3.indicator_value*100||'%','N',year_3.indicator_value) third_year_change_rate,
       decode(f.percent_flag,'Y',year_4.indicator_value*100||'%','N',year_4.indicator_value) fourth_year_change_rate,
       year_1.indicator_line_id first_year_indicator_line_id,
       year_2.indicator_line_id second_year_indicator_line_id,
       year_3.indicator_line_id third_year_indicator_line_id,
       year_4.indicator_line_id fourth_year_indicator_line_id,
       year_1.description,
       f.reference_results
      
  from rsc_fin_indicator_formula f,
       (select l.*
          from rsc_fin_indicator_lns l, rsc_fin_indicator_hds h
         where h.fin_indicator_id = l.fin_indicator_id
           and h.fin_statement_id =rsc_fin_statement_prj_pkg.get_fin_statement_id(${/model/get_tenant_id/record/@fin_statement_templet_id}, ${/session/@company_id}, ${/model/get_tenant_id/record/@bp_id}, 1)) year_1,
        (select l.*
          from rsc_fin_indicator_lns l, rsc_fin_indicator_hds h
         where h.fin_indicator_id = l.fin_indicator_id
           and h.fin_statement_id =rsc_fin_statement_prj_pkg.get_fin_statement_id(${/model/get_tenant_id/record/@fin_statement_templet_id}, ${/session/@company_id}, ${/model/get_tenant_id/record/@bp_id}, 2)) year_2,
           (select l.*
          from rsc_fin_indicator_lns l, rsc_fin_indicator_hds h
         where h.fin_indicator_id = l.fin_indicator_id
           and h.fin_statement_id =rsc_fin_statement_prj_pkg.get_fin_statement_id(${/model/get_tenant_id/record/@fin_statement_templet_id}, ${/session/@company_id}, ${/model/get_tenant_id/record/@bp_id}, 3)) year_3,
           (select l.*
          from rsc_fin_indicator_lns l, rsc_fin_indicator_hds h
         where h.fin_indicator_id = l.fin_indicator_id
           and h.fin_statement_id =rsc_fin_statement_prj_pkg.get_fin_statement_id(${/model/get_tenant_id/record/@fin_statement_templet_id}, ${/session/@company_id}, ${/model/get_tenant_id/record/@bp_id}, 4)) year_4   
 where f.fin_statement_templet_id = ${/model/get_tenant_id/record/@fin_statement_templet_id}
   and f.indicator_code = year_1.indicator_code(+)
   and f.indicator_code = year_2.indicator_code(+)
   and f.indicator_code = year_3.indicator_code(+)
   and f.indicator_code = year_4.indicator_code(+)
   order by f.line_number]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
