<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                select
				decode(v.vender_project_number,null,null,v.bp_agent_name) vender_name,
                v.*
                from (select
                          cr.return_id,
                          to_char(cr.return_date, 'yyyy-mm-dd')                                      return_date,
                          cr.return_amount,
                          ct.period_name,
                          cr.opposite_band_na,
                          cr.bp_bank_account_num,
                          (select cp.description
                           from csh_payment_method cp
                           where cp.payment_method_id =
                                 ct.payment_method_id)                                               payment_method_display,
                          ct.exchange_rate,
                          ct.bank_slip_num,
                          (select gc.currency_name
                           from gld_currency_vl gc
                           where gc.currency_code = ct.currency_code),
                          (select tv.type_name
                           from gld_exchangerate_types_vl tv
                           where tv.type_code = ct.exchange_rate_type),
                          (select a.bank_account_code
                           from csh_bank_account a
                           where a.bank_account_id = ct.bank_account_id)                             bank_account_code,
                          (select a.bank_account_name
                           from csh_bank_account a
                           where a.bank_account_id = ct.bank_account_id)                             bank_account_name,
                          (select a.bank_account_num
                           from csh_bank_account a
                           where a.bank_account_id = ct.bank_account_id)                             bank_account_num,
                          (select a.bank_account_code
                           from hls_bp_master_bank_account a
                           where a.bank_account_id =
                                 ct.bp_bank_account_id)                                              bp_bank_account_code,
                          cr.bp_bank_account_name,
                          cr.notes                                                                   description,
                          cr.csh_return_atm_id,
                          cr.csh_return_bp_id,
                          (select a.bp_name
                           from hls_bp_master a
                           where a.bp_id = cr.csh_return_bp_id)                                      csh_return_bp_name,
                          (select hb.bp_code
                           from hls_bp_master hb, con_contract cc
                           where cc.contract_id = ct.ref_contract_id and hb.bp_id = cc.bp_id_tenant) bp_id_telnet_code,
                          (select hb.bp_name
                           from hls_bp_master hb, con_contract cc
                           where cc.contract_id = ct.ref_contract_id and hb.bp_id = cc.bp_id_tenant) bp_id_telnet_n,
                          (select cc.contract_number
                           from con_contract cc
                           where cc.contract_id = ct.ref_contract_id)                                contract_number,
						   (select cc.search_term_1
                                from con_contract cc
                               where cc.contract_id = ct.ref_contract_id) AS vender_contract_number,
							   ct.deposit_trans_type,
							 (select v.code_value_name
          from sys_code_values_v v
         where v.code = 'DEPOSIT_TRANS_TYPE'
           and v.code_value = ct.deposit_trans_type) deposit_trans_type_desc,
                           (select p.project_number
                                     from prj_project_lease_item  pi,
                                          prj_project             p,
                                          con_contract_lease_item ci
                                    where pi.source_project_id = p.project_id
                                      and pi.project_lease_item_id = ci.project_lease_item_id
                                      and ci.contract_id = ct.ref_contract_id) AS vender_project_number,
                          (select a.bp_name
                           from hls_bp_master a
                           where a.bp_id = ct.bp_id)                                                 bp_agent_name,
                          nvl(cr.left_amount, 0) as                                                  left_amount,
                          cr.finance_flag,
                          --cr.left_amount - cr.return_amount remain_amount,
                          (nvl(ct.transaction_amount,0)-nvl(ct.write_off_amount,0)-nvl(ct.returned_amount,0)) -cr.return_amount remain_amount,
                          cr.bp_bank_branch_name
                      from csh_transaction ct, csh_transaction_return cr
                      where ct.transaction_id = cr.transaction_id
                     ) v
                #WHERE_CLAUSE#
                ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:update-sql><![CDATA[
                begin
                update csh_transaction_return cr
                set  cr.finance_flag = ${@finance_flag}
                where cr.return_id = ${@return_id};
                end;
             ]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="return_id" queryExpression="v.return_id=${@return_id}"/>

    </bm:query-fields>
</bm:model>