<?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 nvl(sum(nvl(t1.un_write_off_amount,0)),0) un_write_off_amount_sum
	    from (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.purpose,
       ct.description
  from csh_transaction ct, csh_bank_account_v cba
  where 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 <> 'RETURN') t1
#WHERE_CLAUSE#
#ORDER_BY_CLAUSE#

	    	]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="un_write_off_amount_sum" databaseType="NUMBER" datatype="java.lang.Double"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field name="bp_bank_account_name" queryExpression="t1.bp_bank_account_name like ${@bp_bank_account_name}"/>
        <bm:query-field name="transaction_num" queryExpression="t1.transaction_num like ${@transaction_num}"/>
        <bm:query-field name="bank_slip_num" queryExpression="t1.bank_slip_num like ${@bank_slip_num}"/>
        <bm:query-field name="collection_classes" queryExpression="t1.collection_classes = ${@collection_classes}"/>
        <bm:query-field name="transaction_date_from" queryExpression="t1.transaction_date &gt;= to_date(${@transaction_date_from},'yyyy-mm-dd')"/>
        <bm:query-field name="transaction_date_to" queryExpression="t1.transaction_date &lt;=  to_date(${@transaction_date_to},'yyyy-mm-dd')"/>
        <bm:query-field name="bp_bank_account_num" queryExpression="t1.bp_bank_account_num like ${@bp_bank_account_num}"/>
        <bm:query-field name="description_P" queryExpression="(t1.description || t1.purpose) like ${@description_P}"/>
        <bm:query-field name="transaction_amount_from" queryExpression="t1.transaction_amount &gt;= ${@transaction_amount_from}"/>
        <bm:query-field name="transaction_amount_to" queryExpression="t1.transaction_amount &lt;= ${@transaction_amount_to}"/>
        <bm:query-field name="un_write_off_amount_from" queryExpression="(t1.transaction_amount - nvl((select sum(cwor.csh_write_off_amount)
          from csh_write_off cwor
         where cwor.csh_transaction_id = t1.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 = t1.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="(t1.transaction_amount - nvl((select sum(cwor.csh_write_off_amount)
          from csh_write_off cwor
         where cwor.csh_transaction_id = t1.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 = t1.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="t1.bank_account_id = ${@bank_account_id}"/>
        <bm:query-field name="posted_date_from" queryExpression="t1.posted_date &gt;= to_date(${@posted_date_from},'yyyy-mm-dd')"/>
        <bm:query-field name="posted_date_to" queryExpression="t1.posted_date &lt;=  to_date(${@posted_date_to},'yyyy-mm-dd')"/>
        <bm:query-field name="opposite_band_na" queryExpression="t1.opposite_band_na = ${@opposite_band_na}"/>
    </bm:query-fields>
</bm:model>
