<?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" alias="t1" baseTable="RSC_FIN_STATEMENT_PRJ_OUTPUT" defaultOrderBy="f.line_number">
    <!-- <bm:fields>
        <bm:field name="fin_statement_templet_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="FIN_STATEMENT_TEMPLET_ID" prompt="RSC_FIN_STATEMENT_PRJ_OUTPUT.FIN_STATEMENT_TEMPLET_ID"/>
        <bm:field name="bp_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BP_ID" prompt="RSC_FIN_STATEMENT_PRJ_OUTPUT.BP_ID"/>
        <bm:field name="fin_statement_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="FIN_STATEMENT_TYPE" prompt="RSC_FIN_STATEMENT_PRJ_OUTPUT.FIN_STATEMENT_TYPE"/>
        
        <bm:field name="first_year_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="FIRST_YEAR_AMOUNT" prompt="RSC_FIN_STATEMENT_PRJ_OUTPUT.FIRST_YEAR_AMOUNT"/>
        <bm:field name="first_year_change_rate" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="FIRST_YEAR_CHANGE_RATE" prompt="RSC_FIN_STATEMENT_PRJ_OUTPUT.FIRST_YEAR_CHANGE_RATE"/>
        <bm:field name="first_year_change_value" databaseType="NUMBER" datatype="java.lang.Double" physicalName="FIRST_YEAR_CHANGE_VALUE" prompt="RSC_FIN_STATEMENT_PRJ_OUTPUT.FIRST_YEAR_CHANGE_VALUE"/>
        <bm:field name="first_year_proportion" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="FIRST_YEAR_PROPORTION" prompt="RSC_FIN_STATEMENT_PRJ_OUTPUT.FIRST_YEAR_PROPORTION"/>
        <bm:field name="second_year_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="SECOND_YEAR_AMOUNT" prompt="RSC_FIN_STATEMENT_PRJ_OUTPUT.SECOND_YEAR_AMOUNT"/>
        <bm:field name="second_year_change_rate" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="SECOND_YEAR_CHANGE_RATE" prompt="RSC_FIN_STATEMENT_PRJ_OUTPUT.SECOND_YEAR_CHANGE_RATE"/>
        <bm:field name="second_year_change_value" databaseType="NUMBER" datatype="java.lang.Double" physicalName="SECOND_YEAR_CHANGE_VALUE" prompt="RSC_FIN_STATEMENT_PRJ_OUTPUT.SECOND_YEAR_CHANGE_VALUE"/>
        <bm:field name="second_year_proportion" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="SECOND_YEAR_PROPORTION" prompt="RSC_FIN_STATEMENT_PRJ_OUTPUT.SECOND_YEAR_PROPORTION"/>
        <bm:field name="third_year_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="THIRD_YEAR_AMOUNT" prompt="RSC_FIN_STATEMENT_PRJ_OUTPUT.THIRD_YEAR_AMOUNT"/>
        <bm:field name="third_year_change_rate" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="THIRD_YEAR_CHANGE_RATE" prompt="RSC_FIN_STATEMENT_PRJ_OUTPUT.THIRD_YEAR_CHANGE_RATE"/>
        <bm:field name="third_year_change_value" databaseType="NUMBER" datatype="java.lang.Double" physicalName="THIRD_YEAR_CHANGE_VALUE" prompt="RSC_FIN_STATEMENT_PRJ_OUTPUT.THIRD_YEAR_CHANGE_VALUE"/>
        <bm:field name="third_year_proportion" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="THIRD_YEAR_PROPORTION" prompt="RSC_FIN_STATEMENT_PRJ_OUTPUT.THIRD_YEAR_PROPORTION"/>
        <bm:field name="fourth_year_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="FOURTH_YEAR_AMOUNT" prompt="RSC_FIN_STATEMENT_PRJ_OUTPUT.FOURTH_YEAR_AMOUNT"/>
        <bm:field name="fourth_year_change_rate" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="FOURTH_YEAR_CHANGE_RATE" prompt="RSC_FIN_STATEMENT_PRJ_OUTPUT.FOURTH_YEAR_CHANGE_RATE"/>
        <bm:field name="fourth_year_change_value" databaseType="NUMBER" datatype="java.lang.Double" physicalName="FOURTH_YEAR_CHANGE_VALUE" prompt="RSC_FIN_STATEMENT_PRJ_OUTPUT.FOURTH_YEAR_CHANGE_VALUE"/>
        <bm:field name="fourth_year_proportion" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="FOURTH_YEAR_PROPORTION" prompt="RSC_FIN_STATEMENT_PRJ_OUTPUT.FOURTH_YEAR_PROPORTION"/>
        <bm:field name="min_fiscal_year" databaseType="VARCHAR2" datatype="java.lang.String" expression="t1.first_fiscal_year || &apos;年&apos; || t1.first_fiscal_month || &apos;月&apos;"/>
        <bm:field name="second_fiscal_year" databaseType="VARCHAR2" datatype="java.lang.String" expression="t1.second_fiscal_year|| &apos;年&apos; || t1.second_fiscal_month || &apos;月&apos;"/>
        <bm:field name="third_fiscal_year" databaseType="VARCHAR2" datatype="java.lang.String" expression="t1.third_fiscal_year || &apos;年&apos; || t1.third_fiscal_month || &apos;月&apos;"/>
        <bm:field name="max_fiscal_year" databaseType="VARCHAR2" datatype="java.lang.String" expression="t1.fourth_fiscal_year || &apos;年&apos; || t1.fourth_fiscal_month || &apos;月&apos;"/>
    </bm:fields>
    <bm:features>
        <f:standard-who/>
    </bm:features>
    <bm:relations>
        <bm:relation name="f" joinType="INNER" refModel="rsc.RSC302.rsc_fin_statement_tmplt_lns">
            <bm:reference foreignField="fin_statement_templet_id" localField="fin_statement_templet_id"/>
            <bm:reference foreignField="fin_statement_item" localField="fin_statement_item"/>
            <bm:reference foreignField="fin_statement_type" localField="fin_statement_type"/>
        </bm:relation>
    </bm:relations>
    <bm:ref-fields>
        <bm:ref-field name="line_number" relationName="f" sourceField="line_number"/>
        <bm:ref-field name="fin_statement_item" relationName="f" sourceField="item_desc"/>
    </bm:ref-fields>
    <bm:query-fields>
        <bm:query-field field="fin_statement_templet_id" queryExpression="t1.fin_statement_templet_id = ${@fin_statement_templet_id}"/>
        <bm:query-field field="bp_id" queryExpression="t1.bp_id = ${@bp_id}"/>
        <bm:query-field field="fin_statement_type" queryExpression="t1.fin_statement_type = ${@fin_statement_type}"/>
    </bm:query-fields> -->
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                select * from (
 select ${@fin_statement_templet_id} fin_statement_templet_id,${@bp_id} bp_id,tl.line_number,tl.fin_statement_type,tl.account_type,tl.item_desc fin_statement_item,
       year_1.amount as first_year_amount,
       year_1.change_rate as first_year_change_rate,
       year_1.change_value as first_year_change_value,
       year_1.proportion  as first_year_proportion,
       year_2.amount as second_year_amount,
       year_2.change_rate as second_year_change_rate,
       year_2.change_value as second_year_change_value,
       year_2.proportion  as second_year_proportion,
       year_3.amount as third_year_amount,
       year_3.change_rate as third_year_change_rate,
       year_3.change_value as third_year_change_value,
       year_3.proportion  as third_year_proportion,
       year_4.amount as fourth_year_amount  ,
       year_4.change_rate as fourth_year_change_rate,
       year_4.change_value as fourth_year_change_value,
       year_4.proportion  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>
