<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: qwm  
    $Date: 2013-7-30 下午4:05:25  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                select * from (
 select tl.line_number,tl.fin_statement_type,tl.account_type,tl.item_desc fin_statement_item,
       to_char(year_1.amount,'FM99,999,999,990.00') as first_year_amount,
       to_char(year_1.change_rate*100,'FM99,999,999,990.00')||decode(year_1.change_rate,null,'','%') as first_year_change_rate,
       to_char(year_1.change_value,'FM99,999,999,990.00') as first_year_change_value,
       to_char(year_1.proportion*100,'FM99,999,999,990.00')||decode(year_1.proportion,null,'','%')  as first_year_proportion,
       to_char(year_2.amount,'FM99,999,999,990.00') as second_year_amount,
       to_char(year_2.change_rate*100,'FM99,999,999,990.00')||decode(year_2.change_rate,null,'','%') as second_year_change_rate,
       to_char(year_2.change_value,'FM99,999,999,990.00') as second_year_change_value,
       to_char(year_2.proportion*100,'FM99,999,999,990.00')||decode(year_2.proportion,null,'','%')  as second_year_proportion,
       to_char(year_3.amount,'FM99,999,999,990.00') as third_year_amount,
       to_char(year_3.change_rate*100,'FM99,999,999,990.00')||decode(year_3.change_rate,null,'','%') as third_year_change_rate,
       to_char(year_3.change_value,'FM99,999,999,990.00') as third_year_change_value,
       to_char(year_3.proportion*100,'FM99,999,999,990.00')||decode(year_3.proportion,null,'','%')  as third_year_proportion,
       to_char(year_4.amount,'FM99,999,999,990.00') as fourth_year_amount  ,
       to_char(year_4.change_rate*100,'FM99,999,999,990.00')||decode(year_4.change_rate,null,'','%') as fourth_year_change_rate,
       to_char(year_4.change_value,'FM99,999,999,990.00') as fourth_year_change_value,
       to_char(year_4.proportion*100,'FM99,999,999,990.00')||decode(year_4.proportion,null,'','%')  as fourth_year_proportion   
  from rsc_fin_statement_tmplt_lns tl,
  (select *
    from rsc_fin_statement_prj_lns l
   where l.fin_statement_id = rsc_fin_statement_prj_pkg.get_fin_statement_id(${@fin_statement_templet_id},${/session/@company_id},${@bp_id},1) ) year_1,
   (select *
    from rsc_fin_statement_prj_lns l
   where l.fin_statement_id = rsc_fin_statement_prj_pkg.get_fin_statement_id(${@fin_statement_templet_id},${/session/@company_id},${@bp_id},2) ) year_2,
   (select *
    from rsc_fin_statement_prj_lns l
   where l.fin_statement_id = rsc_fin_statement_prj_pkg.get_fin_statement_id(${@fin_statement_templet_id},${/session/@company_id},${@bp_id},3) ) year_3,
   (select *
    from rsc_fin_statement_prj_lns l 
   where l.fin_statement_id = rsc_fin_statement_prj_pkg.get_fin_statement_id(${@fin_statement_templet_id},${/session/@company_id},${@bp_id},4) ) year_4
 where tl.fin_statement_templet_id = ${@fin_statement_templet_id}
   and tl.fin_statement_type = year_1.fin_statement_type(+)
   and tl.fin_statement_item = year_1.fin_statement_item(+)
   and tl.fin_statement_item = year_2.fin_statement_item(+)
   and tl.fin_statement_type = year_2.fin_statement_type(+)
   and tl.fin_statement_item = year_3.fin_statement_item(+)
   and tl.fin_statement_type = year_3.fin_statement_type(+)
   and tl.fin_statement_item = year_4.fin_statement_item(+)
   and tl.fin_statement_type = year_4.fin_statement_type(+)
   order by  tl.line_number
   )#WHERE_CLAUSE#

            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="fin_statement_type" datatype="java.lang.String" queryExpression="fin_statement_type = ${@fin_statement_type}"/>
    </bm:query-fields>
</bm:model>
