<?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
              tlc.LOAN_CON_BP,
              hbm.BP_NAME,
              tlc.LOAN_CONTRACT_NUMBER,
              twplan.WITHDRAWAL_NUMBER,
              tlc.LOAN_CONTRACT_TERM_TYPE,
              (select CODE_VALUE_NAME
               from SYS_CODE_VALUES_V
               where CODE = 'LOAN_CONTRACT_TERM_TYPE'
                     and CODE_VALUE = tlc.LOAN_CONTRACT_TERM_TYPE) LOAN_CONTRACT_TERM_TYPE_N,
              twplan.WIDTHDRAWAL_DATE,
              twplan.AMOUNT,
              trplan.times,
              trplan.MATURITY_DATE,
              trplan.PRINCIPAL,
              trplan.INTEREST,
              trplan.LEASE_CHARGE_AMOUNT,
              (case when twplan.WIDTHDRAWAL_DATE < to_date('2018-05-01', 'yyyy-MM-dd')
                then round(trplan.INTEREST / 1.17 * 0.17, 2)
               else
                 round(trplan.INTEREST / 1.16 * 0.16, 2)
               end
              ) tax_amount
            from
              TRE_LOAN_CONTRACT tlc,
              TRE_LOAN_CON_WITHDRAWAL_PLAN twplan,
              TRE_LOAN_CON_REPAYMENT_PLAN trplan,
              HLS_BP_MASTER hbm
            where twplan.LOAN_CONTRACT_ID = tlc.LOAN_CONTRACT_ID
                  and tlc.LOAN_CON_BP = hbm.BP_ID
                  and twplan.WIDTHDRAWAL_PLAN_ID = trplan.WITHDRAWAL_ID
                  ) t1

                  #WHERE_CLAUSE#
                  order by t1.loan_contract_number, t1.times asc


            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="bp_name"/>
        <bm:field name="loan_contract_number"/>
        <bm:field name="withdrawal_number"/>
        <bm:field name="loan_contract_term_type_n"/>
        <bm:field name="widthdrawal_date" databaseType="date" datatype="java.util.Date"/>
        <bm:field name="amount" databaseType="number" datatype="java.lang.Double"/>
        <bm:field name="times"/>
        <bm:field name="maturity_date" databaseType="date" datatype="java.util.Date"/>
        <bm:field name="principal" databaseType="number" datatype="java.lang.Double"/>
        <bm:field name="interest" databaseType="number" datatype="java.lang.Double"/>
        <bm:field name="lease_charge_amount" databaseType="number" datatype="java.lang.Double"/>
        <bm:field name="tax_amount" databaseType="number" datatype="java.lang.Double"/>

    </bm:fields>

    <bm:query-fields>
        <bm:query-field field="bp_name" queryOperator="like"/>
        <bm:query-field field="loan_contract_number" queryOperator="like"/>
        <bm:query-field field="withdrawal_number" queryOperator="like"/>
        <bm:query-field field="loan_contract_term_type_n" queryOperator="like"/>


    </bm:query-fields>



</bm:model>
