<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: chenlingfeng
    $Date: 2018-12-31 18:17:30
    $Revision: 1.0
    $Purpose:
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" defaultOrderBy="ct.transaction_date desc">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
	    	  select
ct.transaction_id,
ct.transaction_num,
       ct.bank_slip_num,
       ct.collection_classes,

       (select v.code_value_name from sys_code_values_v v where v.code = 'DS_COLLECTION_CLASSES' and v.code_value =ct.collection_classes) collection_classes_desc,
       ct.transaction_date,
       ct.bp_bank_account_num,
       ct.description || ct.purpose description_P,
       ct.transaction_amount,
       (select sum(cwor.csh_write_off_amount)
          from csh_write_off cwor
         where cwor.csh_transaction_id = ct.transaction_id
           and cwor.write_off_type = 'CSH_RETURN'
           and cwor.write_off_date <= to_date(${@posted_date_to},'yyyy-mm-dd')) returned_amount,
       ((select sum(cwo.csh_write_off_amount)
           from csh_write_off cwo
          where cwo.csh_transaction_id = ct.transaction_id
            and cwo.write_off_type <> 'CSH_RETURN'
            and cwo.journal_date <= to_date(${@posted_date_to},'yyyy-mm-dd'))) write_off_amount,
       (ct.transaction_amount - nvl((select sum(cwor.csh_write_off_amount)
          from csh_write_off cwor
         where cwor.csh_transaction_id = ct.transaction_id
           and cwor.write_off_type = 'CSH_RETURN'
           and cwor.write_off_date <= to_date(${@posted_date_to},'yyyy-mm-dd')), 0) -
       nvl((select sum(cwo.csh_write_off_amount)
           from csh_write_off cwo
          where cwo.csh_transaction_id = ct.transaction_id
            and cwo.write_off_type <> 'CSH_RETURN'
            and cwo.journal_date <= to_date(${@posted_date_to},'yyyy-mm-dd')), 0)) un_write_off_amount,
       cba.bank_account_id,
       cba.bank_branch_name,
       ct.opposite_band_na,
       cba.bank_account_num,
       ct.ref_v05,
       ct.bp_bank_account_name,
       ct.transaction_category,
       ct.transaction_type,
       ct.posted_flag,
       ct.posted_date,
       ct.zuonr
  from csh_transaction ct, csh_bank_account_v cba
#WHERE_CLAUSE#
#ORDER_BY_CLAUSE#

	    	]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="transaction_id"/>
        <bm:field name="transaction_num"/>
        <bm:field name="bank_slip_num"/>
        <bm:field name="collection_classes"/>
        <bm:field name="collection_classes_desc"/>
        <bm:field name="transaction_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="bp_bank_account_num"/>
        <bm:field name="bp_bank_account_name"/>
        <bm:field name="description_P"/>
        <bm:field name="transaction_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="returned_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="write_off_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="un_write_off_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="bank_account_id"/>
        <bm:field name="bank_branch_name"/>
        <bm:field name="bank_account_num"/>
        <bm:field name="ref_v05"/>
        <bm:field name="transaction_category"/>
        <bm:field name="transaction_type"/>
        <bm:field name="posted_flag"/>
        <bm:field name="opposite_band_na"/>
        <bm:field name="posted_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="zuonr"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field name="bp_bank_account_name" queryExpression="ct.bp_bank_account_name like ${@bp_bank_account_name}"/>
        <bm:query-field name="transaction_num" queryExpression="ct.transaction_num like ${@transaction_num}"/>
        <bm:query-field name="bank_slip_num" queryExpression="ct.bank_slip_num like ${@bank_slip_num}"/>
        <bm:query-field name="collection_classes" queryExpression="ct.collection_classes = ${@collection_classes}"/>
        <bm:query-field name="transaction_date_from" queryExpression="ct.transaction_date &gt;= to_date(${@transaction_date_from},'yyyy-mm-dd')"/>
        <bm:query-field name="transaction_date_to" queryExpression="ct.transaction_date &lt;=  to_date(${@transaction_date_to},'yyyy-mm-dd')"/>
        <bm:query-field name="bp_bank_account_num" queryExpression="ct.bp_bank_account_num like ${@bp_bank_account_num}"/>
        <bm:query-field name="description_P" queryExpression="(ct.description || ct.purpose) like ${@description_P}"/>
        <bm:query-field name="transaction_amount_from" queryExpression="ct.transaction_amount &gt;= ${@transaction_amount_from}"/>
        <bm:query-field name="transaction_amount_to" queryExpression="ct.transaction_amount &lt;= ${@transaction_amount_to}"/>
        <bm:query-field name="un_write_off_amount_from" queryExpression="(ct.transaction_amount - nvl((select sum(cwor.csh_write_off_amount)
          from csh_write_off cwor
         where cwor.csh_transaction_id = ct.transaction_id
           and cwor.write_off_type = 'CSH_RETURN'
           and cwor.write_off_date &lt;= to_date(${@posted_date_to},'yyyy-mm-dd')), 0) -
       nvl((select sum(cwo.csh_write_off_amount)
           from csh_write_off cwo
          where cwo.csh_transaction_id = ct.transaction_id
            and cwo.write_off_type &lt;&gt; 'CSH_RETURN'
            and cwo.journal_date &lt;= to_date(${@posted_date_to},'yyyy-mm-dd')), 0))  &gt;= ${@un_write_off_amount_from}"/>
        <bm:query-field name="un_write_off_amount_to" queryExpression="(ct.transaction_amount - nvl((select sum(cwor.csh_write_off_amount)
          from csh_write_off cwor
         where cwor.csh_transaction_id = ct.transaction_id
           and cwor.write_off_type = 'CSH_RETURN'
           and cwor.write_off_date &lt;= to_date(${@posted_date_to},'yyyy-mm-dd')), 0) -
       nvl((select sum(cwo.csh_write_off_amount)
           from csh_write_off cwo
          where cwo.csh_transaction_id = ct.transaction_id
            and cwo.write_off_type &lt;&gt; 'CSH_RETURN'
            and cwo.journal_date &lt;= to_date(${@posted_date_to},'yyyy-mm-dd')), 0))  &lt;= ${@un_write_off_amount_to}"/>
        <bm:query-field name="bank_account_id" queryExpression="ct.bank_account_id = ${@bank_account_id}"/>
        <bm:query-field name="posted_date_from" queryExpression="ct.posted_date &gt;= to_date(${@posted_date_from},'yyyy-mm-dd')"/>
        <bm:query-field name="posted_date_to" queryExpression="ct.posted_date &lt;=  to_date(${@posted_date_to},'yyyy-mm-dd')"/>
        <bm:query-field name="opposite_band_na" queryExpression="ct.opposite_band_na = ${@opposite_band_na}"/>
    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter name="query" expression=" ct.bank_account_id = cba.bank_account_id(+)
   and ct.transaction_category = 'BUSINESS'
   and ct.transaction_type = 'RECEIPT'
   and nvl(ct.deposit_flag,'N') ='N'
   and ct.returned_flag &lt;&gt; 'RETURN'"/>
    </bm:data-filters>
</bm:model>
