<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[select * from (
                select v1.*,v1.due_rental_deposit-v1.received_rental_deposit undue_rental_deposit from (
                select cc.bp_id_agent_level1,
                (select hbm.bp_name
                from HLS_BP_MASTER HBM
                where cc.bp_id_agent_level1 = hbm.bp_id) agent_name,
                cc.contract_number,
                cc.bp_id_tenant,
                (select hbm.bp_name
                from HLS_BP_MASTER HBM
                where cc.bp_id_tenant = hbm.bp_id) bp_name,
                t.session_id,
                t.overdue_max_days,
                f.times,
                to_char(t.calc_date,'yyyy-mm-dd') calc_date,
                to_char(f.due_date,'yyyy-mm-dd') due_date,
                nvl(t.f_overdue_amount,0) f_overdue_amount,
                nvl(t.f_overdue_penalty_amt,0) f_overdue_penalty_amt,
                nvl(t.overdue_principal, 0) overdue_principal,
                nvl(t.overdue_interest, 0) overdue_interest,
                nvl(t.overdue_amount, 0) overdue_amount,
                nvl(t.overdue_penalty_amt, 0) overdue_penalty_amt,
                nvl(t.f_overdue_amount, 0) due_rental_deposit,
                (select nvl(sum(cwo.write_off_due_amount),0)
                from csh_transaction ctr, csh_write_off cwo
                where cwo.contract_id = t.contract_id
                AND cwo.cashflow_id = t.cashflow_id
                AND cwo.csh_transaction_id = ctr.transaction_id
                and ctr.transaction_type = 'DEPOSIT') received_rental_deposit
                from CUS_DEPOSIT_CALC_OVERDUE_TMP t,
                CON_CONTRACT                 CC,
                con_contract_cashflow        f
                where t.cashflow_id = f.cashflow_id
                and f.contract_id = cc.contract_id
                and t.contract_id = cc.contract_id
                and cc.data_class = 'NORMAL')v1
                order by v1.contract_number,v1.times)v
                #WHERE_CLAUSE#
                #ORDER_BY_CLAUSE#
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="v.contract_number like &apos;%&apos;||${@contract_number}||&apos;%&apos;"/>
        <bm:query-field name="bp_id_agent_level1" queryExpression="v.bp_id_agent_level1 = ${@bp_id_agent_level1}"/>
        <bm:query-field name="bp_id_tenant" queryExpression="v.bp_id_tenant =${@bp_id_tenant}"/>
        <bm:query-field name="overdue_max_days_from" queryExpression="v.overdue_max_days &lt;= ${@overdue_max_days_from}"/>
        <bm:query-field name="overdue_max_days_to" queryExpression="v.overdue_max_days &gt;= ${@overdue_max_days_to}"/>
        <bm:query-field name="due_date_from" queryExpression="v.due_date &lt;= ${@due_date_from}"/>
        <bm:query-field name="due_date_to" queryExpression="v.due_date &gt;= ${@due_date_to}"/>
    </bm:query-fields>
<!--    <bm:data-filters>-->
<!--        <bm:data-filter name="session_id" expression="${/session/@session_id}"/>-->
<!--    </bm:data-filters>-->
</bm:model>