<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: hyb  
    $Date: 2012-10-11 下午4:21:04  
    $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 t.company_id,
					   t2.company_code,
					   t2.company_short_name,
				       t.responsibility_center_id,
				       c.responsibility_center_code,
				       c.responsibility_center_name,
				       t.internal_period_num,
				       t.set_of_books_id,
				       t.account_id,
				       a.account_code,
				       a.description as account_desc,
				       t.period_name,
				       t.period_year,
				       t.period_num,
				       nvl(t.entered_project_to_date_amt, 0) -
				       nvl(t.entered_period_to_date_amt, 0) as entered_to_date_amt, --原币期初余额
				       nvl(t.entered_period_net_dr_amt, 0) as entered_period_net_dr_amt, --原币借方发生额
				       nvl(t.entered_period_net_cr_amt, 0) as entered_period_net_cr_amt, --原币贷方发生额
				       nvl(t.entered_project_to_date_amt, 0) as entered_project_to_date_amt, --原币余额
				       nvl(t.functional_project_to_date, 0) -
				       nvl(t.functional_period_to_date, 0) as functional_to_date_amt,
				       nvl(t.functional_period_net_dr, 0) as functional_period_net_dr,
				       nvl(t.functional_period_net_cr, 0) as functional_period_net_cr,
				       nvl(t.functional_project_to_date, 0) as functional_project_to_date,
				       nvl(t.entered_quarter_net_dr_amt, 0) as entered_quarter_net_dr_amt, --原币季度借方累计
				       nvl(t.entered_quarter_net_cr_amt, 0) as entered_quarter_net_cr_amt, --原币季度贷方累计
				       nvl(t.entered_year_net_dr_amt, 0) as entered_year_net_dr_amt, --原币年度借方累计
				       nvl(t.entered_year_net_cr_amt, 0) as entered_year_net_cr_amt, --原币年度贷方累计
				       nvl(t.entered_project_net_dr_amt, 0) as entered_project_net_dr_amt, --原币借方累计
				       nvl(t.entered_project_net_cr_amt, 0) as entered_project_net_cr_amt, --原币贷方累计
				       nvl(t.functional_quarter_net_dr, 0) as functional_quarter_net_dr,
				       nvl(t.functional_quarter_net_cr, 0) as functional_quarter_net_cr,
				       nvl(t.functional_year_net_dr, 0) as functional_year_net_dr,
				       nvl(t.functional_year_net_cr, 0) as functional_year_net_cr,
				       nvl(t.functional_project_net_dr, 0) as functional_project_net_dr,
				       nvl(t.functional_project_net_cr, 0) as functional_project_net_cr
				  from gld_balances t, gld_accounts_vl a, fnd_responsibility_centers_vl c, fnd_companies_vl t2
				 where t.company_id = ${@company_id}
				   and t.account_id = a.account_id
				   and t.responsibility_center_id = c.responsibility_center_id
				   and t.internal_period_num BETWEEN ${@internal_period_num_from} AND ${@internal_period_num_to}
				   and a.account_code BETWEEN NVL(${@account_code_from}, a.account_code) AND
				       NVL(${@account_code_to}, a.account_code)
				   and c.responsibility_center_code BETWEEN NVL(${@resp_center_code_from}, c.responsibility_center_code) AND
				       NVL(${@resp_center_code_to}, c.responsibility_center_code)
				   and t.currency_code = ${@currency_code}
				   and (
					      (${@account_summary_flag}='Y' AND ${@account_sub_flag}='Y') 
					   or (${@account_summary_flag}='Y' AND ${@account_sub_flag}='N' AND a.summary_flag='Y')
					   or (${@account_summary_flag}='N' AND ${@account_sub_flag}='Y' AND nvl(a.summary_flag,'N')='N')
					   or (${@account_summary_flag}='N' AND ${@account_sub_flag}='N' AND 1!=1)
				   )
				   and (
					      (${@resp_summary_flag}='Y' and ${@resp_sub_flag}='Y') 
					   or (${@resp_summary_flag}='Y' AND ${@resp_sub_flag}='N' AND c.summary_flag='Y')
					   or (${@resp_summary_flag}='N' AND ${@resp_sub_flag}='Y' AND nvl(c.summary_flag,'N')='N')
					   or (${@resp_summary_flag}='N' AND ${@resp_sub_flag}='N' AND 1!=1)
				   )
				   and t2.company_id = t.company_id
				 order by t.company_id,
				          c.responsibility_center_code,
				          c.responsibility_center_name,
				          t2.company_code,
					      t2.company_short_name,
					      t.internal_period_num,
				          a.account_code,
				          t.period_year,
				          t.period_num
         ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
