<?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
                        A.*,
                        ROWNUM RN
                    FROM
                        (SELECT
                            *
                        FROM
                            (
                select cc.contract_number,
			       cc.bp_id_tenant_n,
			       cc.contract_id,
			       cc.overdue_amount,
			       cc.received_amount,
			       cc.outstanding_principal,
			       cc.outstanding_rental,
			       c.created_by,
			       cc.penalty_total_amount
			  from con_contract_cf_app_lv cc,con_contract c
        where cc.contract_id = c.contract_id
        and 
        exists (select 1 from aut_trx_user_authorize a1,aut_owner_user_authorize a2 where a1.trx_category = 'CONTRACT' 
            and a1.trx_id = c.contract_id  
            and trunc(sysdate) between a1.start_date and nvl(a1.end_date,trunc(sysdate)) 
            and a1.user_id = a2.owner_user_id 
            and a1.trx_category = a2.trx_category  
            and a2.authorized_user_id =${@user_id} 
            and trunc(sysdate) between a2.start_date and nvl(a2.end_date,trunc(sysdate)))
        ) t #WHERE_CLAUSE#
                        ORDER BY
                            t.contract_id DESC
                        )A
                    WHERE
                        ROWNUM <= NVL(${@pagesize},10) * NVL(${@offsize},1)
                    )
                WHERE
                    RN >= 1 + ((NVL(${@offsize},1)-1) * NVL(${@pagesize},10))

            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="created_by"/>
        <bm:field name="contract_number"/>
        <bm:field name="bp_id_tenant_n"/>
        <bm:field name="contract_id"/>
        <bm:field name="overdue_amount"/>
        <bm:field name="received_amount"/>
        <bm:field name="outstanding_principal"/>
        <bm:field name="outstanding_rental"/>
        <bm:field name="penalty_total_amount"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field name="created_by" queryExpression="t.created_by=${@user_id}"/>
        <bm:query-field name="searchInput" queryExpression="t.bp_id_tenant_n like &apos;%&apos;||${@searchInput}||&apos;%&apos; or t.contract_number like &apos;%&apos;||${@searchInput}||&apos;%&apos;"/>
    </bm:query-fields>
</bm:model>
