<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: qwm  
    $Date: 2013-6-3 下午05:18: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  r.return_id,
                                           t1.transaction_num,
                                           t1.transaction_date,
                                           t1.currency_code,
                                           (select gc.currency_name
                                              from gld_currency_vl gc
                                             where gc.currency_code = t1.currency_code) as currency_name,
                                           t1.transaction_amount,
                                           (select a.bank_account_code
                                              from csh_bank_account a
                                             where a.bank_account_id = t1.bank_account_id
                                               and a.company_id = t1.company_id) as bank_account_code,
                                           (select a.bank_account_name
                                              from csh_bank_account a
                                             where a.bank_account_id = t1.bank_account_id
                                               and a.company_id = t1.company_id) as bank_account_name,
                                           (select d.bp_name from hls_bp_master_v d where d.bp_id = t1.bp_id) as bp_name,
                                           (select b.description
                                              from hls_bp_category b
                                             where b.bp_category = t1.bp_category) as bp_category_display,
                                           t1.bank_slip_num,
                                           t1.period_name,
                                           (select cp.description
                                              from csh_payment_method cp
                                             where cp.payment_method_id = t1.payment_method_id) as payment_method_display,
                                           t1.exchange_rate,
                                           (select tv.type_name
                                              from gld_exchangerate_types_vl tv
                                             where tv.type_code = t1.exchange_rate_type) as exchange_rate_type_display,
                                           (select a.bank_account_code
                                              from csh_bank_account a
                                             where a.bank_account_id = t1.bank_account_id
                                               and a.company_id = t1.company_id) as bank_account_code,
                                           (select a.bank_account_name
                                              from csh_bank_account a
                                             where a.bank_account_id = t1.bank_account_id
                                               and a.company_id = t1.company_id) as bank_account_name,
                                           (select a.bank_account_num
                                              from csh_bank_account a
                                             where a.bank_account_id = t1.bank_account_id
                                               and a.company_id = t1.company_id) as bank_account_num,
                                           t1.bp_bank_account_num,
                                           (select a.bank_account_code
                                              from hls_bp_master_bank_account a
                                             where a.bank_account_id = t1.bp_bank_account_id) as bp_bank_account_code,
                                           (select a.bank_account_name
                                              from hls_bp_master_bank_account a
                                             where a.bank_account_id = t1.bp_bank_account_id) as bp_bank_account_name,
                                             (select u.employee_name from sys_user_v u where u.user_id=t1.created_by )user_desc,
                                             r.notes,
                                             (select h.bp_name
                                              from hls_bp_master h
                                             where h.bp_id = (select c.bp_id_tenant
                                              from con_contract c
                                             where contract_id = r.ref_contract_id)) bp_name_t,
                                              (select h.bp_name
                                              from hls_bp_master h
                                             where h.bp_id = (select c.bp_id_agent_level1
                                              from con_contract c
                                             where contract_id = r.ref_contract_id)) agent_name_t,
                                             (select c.contract_number
                                              from con_contract c
                                             where contract_id = r.ref_contract_id) contract_number_t
                                      from
                                        csh_transaction t1, csh_transaction_return r
                                      where
                                        t1.return_from_csh_trx_id = r.transaction_id and
                                        t1.transaction_id = r.return_transaction_id and
                                        r.status = 'APPROVED' and
                                        t1.return_from_csh_trx_id = ${@return_from_csh_trx_id}
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
