<?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 *
                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 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>