<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: 26887cz
    $Date: 2021-10-14 下午03:16:44
    $Revision: 1.0
    $Purpose: 保证金退款打印
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false" baseTable="CSH_DEPOSIT_CASHFLOW" alias="t">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
    select * from (select (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_agent_level1) AS bp_name_n,
       (select cc.contract_number
          from con_contract cc
         where cc.contract_id = ct.ref_contract_id) AS contract_number,
       (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) AS 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) AS bp_id_telnet_n,
       cr.return_date,
       to_char(cr.return_date,'yyyy-MM-dd') AS return_date_n,
       cr.this_return_amount,
       cr.left_amount,
       cr.bp_bank_account_name,
       cr.opposite_band_na,
       (select a.bank_account_num
          from csh_bank_account a
         where a.bank_account_id = cr.csh_return_atm_id) AS bank_account_num,
       (select su.description
          from sys_user su
         where su.user_id = cr.created_by) AS user_desc,
       decode((select count(1)
                from HLS_JOURNAL_HEADER hh, hls_journal_detail hd
               where hh.journal_header_id = hd.journal_header_id
                 and hh.source_table = 'CSH_TRANSACTION'
                 and hh.source_id = cr.transaction_id
                 and hh.je_transaction_code = 'CSH_WRITE_OFF_CM'
                 and hd.amount_cr = cr.return_amount),
              0,
              '否',
              '是') journal_flag,
       cr.return_id,
       cr.transaction_id,
       cr.csh_return_atm_id
  from csh_transaction_return cr, csh_transaction ct
 where cr.status = 'APPROVED'
   and cr.return_type = 'DEPOSIT'
   and cr.transaction_id = ct.transaction_id
 order by cr.return_date desc) t1
        #WHERE_CLAUSE#
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="return_date_from" queryExpression="t1.return_date &gt;= to_date(${@return_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="return_date_to" queryExpression="t1.return_date &lt;= to_date(${@return_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="this_return_amount_from" queryExpression="t1.this_return_amount &gt;= ${@this_return_amount_from}"/>
        <bm:query-field name="this_return_amount_to" queryExpression="t1.this_return_amount &lt;= ${@this_return_amount_to}"/>
        <bm:query-field name="left_amount_from" queryExpression="t1.left_amount &gt;= ${@left_amount_from}"/>
        <bm:query-field name="left_amount_to" queryExpression="t1.left_amount &lt;= ${@left_amount_to}"/>
        <bm:query-field name="contract_number" queryExpression="t1.contract_number like &apos;%&apos;||${@contract_number}||&apos;%&apos;"/>
        <bm:query-field name="bp_id_telnet_n" queryExpression="t1.bp_id_telnet_n like &apos;%&apos;||${@bp_id_telnet_n}||&apos;%&apos;"/>
        <bm:query-field name="bp_name_n" queryExpression="t1.bp_name_n like &apos;%&apos;||${@bp_name_n}||&apos;%&apos;"/>
        <bm:query-field name="user_desc" queryExpression="t1.user_desc like &apos;%&apos;||${@user_desc}||&apos;%&apos;"/>

        <bm:query-field name="bp_bank_account_name" queryExpression="t1.bp_bank_account_name like &apos;%&apos;||${@bp_bank_account_name}||&apos;%&apos;"/>
        <bm:query-field name="opposite_band_na" queryExpression="t1.opposite_band_na like &apos;%&apos;||${@opposite_band_na}||&apos;%&apos;"/>
        <bm:query-field name="bank_account_num" queryExpression="t1.bank_account_num like &apos;%&apos;||${@bank_account_num}||&apos;%&apos;"/>

        <bm:query-field name="return_id" queryExpression="t1.return_id in (${:@return_id})"/>
    </bm:query-fields>
</bm:model>
