<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:s="leaf.plugin.script" xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false" defaultOrderBy="t1.write_off_date desc">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            select *
  from (select c.contract_id,
               c.contract_number,
               (select h.bp_name
                  from hls_bp_master h
                 where h.bp_id = c.bp_id_tenant) bp_name,
               (select h.bp_name
                  from hls_bp_master h
                 where h.bp_id = c.bp_id_agent_level1) bp_agent_name,
               (select ct.transaction_date
                  from csh_transaction ct
                 where ct.transaction_id = t.csh_transaction_id) transaction_date,
               t.cf_item,
               (SELECT hl.description
                  FROM hls_cashflow_item hl
                 WHERE hl.cf_item = t.cf_item) cf_item_n,
               t.times,
               t.write_off_classification,
               (SELECT v.code_value_name
                  FROM sys_code_values_v v
                 WHERE v.code = 'DS_CSH_WRITE_OFF_CLASSIFICATION'
                   AND v.code_value = t.write_off_classification) write_off_classification_n,
               t.write_off_due_amount,
               nvl(t.write_off_principal, 0) write_off_principal,
               nvl(t.write_off_interest, 0) write_off_interest,
               (select cc.due_date
                  from con_contract_cashflow cc
                 where cc.cashflow_id = t.cashflow_id) due_date,
               t.journal_date,
               t.write_off_date,
               NVL((select transaction_num
                     from csh_transaction
                    where transaction_id =
                          (select ct.source_csh_trx_id
                             from csh_transaction ct
                            where ct.transaction_id = t.csh_transaction_id)),
                   (select ct.transaction_num
                      from csh_transaction ct
                     where ct.transaction_id = t.csh_transaction_id)) transaction_num,
               (select sum(cwr.csh_write_off_amount)
                  from csh_write_off cwr
                 where cwr.csh_transaction_id = t.csh_transaction_id
                 and cwr.contract_id = t.contract_id
                 and cwr.write_off_type = 'RECEIPT_CREDIT'
                 and cwr.reversed_flag = 'N'
                 group by cwr.csh_transaction_id) csh_write_off_amount_sum
          from csh_write_off t, con_contract c
         where t.contract_id = c.contract_id
           and c.data_class = 'NORMAL'
           and t.write_off_type = 'RECEIPT_CREDIT'
           and t.reversed_flag = 'N') t1
  #WHERE_CLAUSE# #ORDER_BY_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="contract_id"/>
        <bm:field name="contract_number"/>
        <bm:field name="bp_name"/>
        <bm:field name="bp_agent_name"/>
        <bm:field name="transaction_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="cf_item"/>
        <bm:field name="cf_item_n"/>
        <bm:field name="times"/>
        <bm:field name="write_off_classification"/>
        <bm:field name="write_off_classification_n"/>
        <bm:field name="write_off_due_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="write_off_principal" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="write_off_interest" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="due_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="journal_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="write_off_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="transaction_num"/>
        <bm:field name="csh_write_off_amount_sum" databaseType="NUMBER" datatype="java.lang.Double"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field field="contract_number" queryOperator="like"/>
        <bm:query-field field="bp_name" queryOperator="like"/>
        <bm:query-field field="bp_agent_name" queryOperator="like"/>
        <bm:query-field field="transaction_num" queryOperator="like"/>
        <bm:query-field name="transaction_date_from" queryExpression="t1.transaction_date &gt;= to_date(${@transaction_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="transaction_date_to" queryExpression="t1.transaction_date &lt;= to_date(${@transaction_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="due_date_from" queryExpression="t1.due_date &gt;= to_date(${@due_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="due_date_to" queryExpression="t1.due_date &lt;= to_date(${@due_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="journal_date_from" queryExpression="t1.journal_date &gt;= to_date(${@journal_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="journal_date_to" queryExpression="t1.journal_date &lt;= to_date(${@journal_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="csh_write_off_amount_sum_from" queryExpression="t1.csh_write_off_amount_sum &gt;= ${@csh_write_off_amount_sum_from}"/>
        <bm:query-field name="csh_write_off_amount_sum_to"  queryExpression="t1.csh_write_off_amount_sum &lt;= ${@csh_write_off_amount_sum_to}"/>
    </bm:query-fields>
    <s:bm-script><![CDATA[
            var cx = Packages.leaf.javascript.Context.getCurrentContext();
            Packages.leaf.plugin.script.engine.ScriptImportor.defineExternScript(cx, this, $ctx.getData(), "aut_authority_bm_validate.js");
        ]]></s:bm-script>
</bm:model>
