<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: jack  
    $Date: 2014-6-10 上午9:45:31  
    $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 t1.* ,
            decode(amount2,0,'','','',round((amount3-amount2)/amount2,4)*100||'%') change_rate_1,
            decode(amount3,0,'','','',round((amount4-amount3)/amount3,4)*100||'%') change_rate_2
            from(
            SELECT a.*,
         round(a.amount,2)           amount1,
       round(b.amount,2)             amount2,
       round(c.amount,2)             amount3,
       round(d.amount,2)             amount4
  FROM (SELECT a.*,
               c.amount,
               c.fin_statement_type,
               c.fin_statement_item,
               c.line_number,
               d.item_desc
          FROM (SELECT DISTINCT b.fin_statement_templet_id,
                                b.bp_id,
                                a.bp_category,
                                a.bp_class,
                                A.BP_TYPE,
                                a.project_id
                  FROM prj_project_bp            a,
                       rsc_fin_statement_prj_hds b
                 WHERE a.bp_id = b.bp_id) a,
               rsc_fin_statement_prj_hds b,
               rsc_fin_statement_prj_lns c,
                rsc_fin_statement_tmplt_lns d
         WHERE b.fin_statement_id = rsc_fin_statement_prj_pkg.get_fin_statement_id(a.fin_statement_templet_id,
                                                                                   ${/session/@company_id},
                                                                                   a.bp_id,
                                                                                   1)
               AND b.fin_statement_id = c.fin_statement_id
                AND (A.BP_CATEGORY='TENANT' or A.BP_CATEGORY='TENANT_SEC')
                 and d.fin_statement_item=c.fin_statement_item
                and d.fin_statement_templet_id=b.fin_statement_templet_id
               AND a.project_id = ${@project_id}) a,
       (SELECT a.*,
               c.amount,
               c.fin_statement_type,
               c.fin_statement_item
          FROM (SELECT DISTINCT b.fin_statement_templet_id,
                                b.bp_id,
                                a.bp_category,
                                a.bp_class,
                                A.BP_TYPE,
                                a.project_id
                  FROM prj_project_bp            a,
                       rsc_fin_statement_prj_hds b
                 WHERE a.bp_id = b.bp_id) a,
               rsc_fin_statement_prj_hds b,
               rsc_fin_statement_prj_lns c
         WHERE b.fin_statement_id = rsc_fin_statement_prj_pkg.get_fin_statement_id(a.fin_statement_templet_id,
                                                                                   ${/session/@company_id},
                                                                                   a.bp_id,
                                                                                   2)
               AND b.fin_statement_id = c.fin_statement_id
                 AND (A.BP_CATEGORY='TENANT' or A.BP_CATEGORY='TENANT_SEC')
               AND a.project_id = ${@project_id}) b,
       (SELECT a.*,
               c.amount,
               c.fin_statement_type,
               c.fin_statement_item
          FROM (SELECT DISTINCT b.fin_statement_templet_id,
                                b.bp_id,
                                a.bp_category,
                                a.bp_class,
                                A.BP_TYPE,
                                a.project_id
                  FROM prj_project_bp            a,
                       rsc_fin_statement_prj_hds b
                 WHERE a.bp_id = b.bp_id) a,
               rsc_fin_statement_prj_hds b,
               rsc_fin_statement_prj_lns c
         WHERE b.fin_statement_id = rsc_fin_statement_prj_pkg.get_fin_statement_id(a.fin_statement_templet_id,
                                                                                   ${/session/@company_id},
                                                                                   a.bp_id,
                                                                                   3)
               AND b.fin_statement_id = c.fin_statement_id
                AND (A.BP_CATEGORY='TENANT' or A.BP_CATEGORY='TENANT_SEC')
               AND a.project_id = ${@project_id}) c,
       (SELECT a.*,
               c.amount,
               c.fin_statement_type,
               c.fin_statement_item
          FROM (SELECT DISTINCT b.fin_statement_templet_id,
                                b.bp_id,
                                a.bp_category,
                                A.BP_TYPE,
                                a.bp_class,
                                a.project_id
                  FROM prj_project_bp            a,
                       rsc_fin_statement_prj_hds b
                 WHERE a.bp_id = b.bp_id) a,
               rsc_fin_statement_prj_hds b,
               rsc_fin_statement_prj_lns c
         WHERE b.fin_statement_id = rsc_fin_statement_prj_pkg.get_fin_statement_id(a.fin_statement_templet_id,
                                                                                   ${/session/@company_id},
                                                                                   a.bp_id,
                                                                                   4)
               AND b.fin_statement_id = c.fin_statement_id
               AND (A.BP_CATEGORY='TENANT' or A.BP_CATEGORY='TENANT_SEC')
               AND a.project_id = ${@project_id}) d
 WHERE a.fin_statement_type = b.fin_statement_type
       
       AND a.fin_statement_item = b.fin_statement_item
       AND a.bp_id = b.bp_id
       AND b.fin_statement_item = c.fin_statement_item
       AND b.fin_statement_type = c.fin_statement_type
       AND b.bp_id = c.bp_id
       AND c.fin_statement_item = d.fin_statement_item
       AND c.fin_statement_type = d.fin_statement_type
       AND c. bp_id = d.bp_id
        order by a.line_number
        ) t1 #WHERE_CLAUSE#]]></bm:query-sql>
            <bm:parameters>
                <bm:parameter inputPath="/session/@company_id"/>
                <bm:parameter name="project_id"/>
            </bm:parameters>
        </bm:operation>
    </bm:operations>
</bm:model>
