<?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.payment_req_number,
				       to_char(cc.req_date,'yyyy-mm-dd') req_date,
				       cc.req_date req_date1,
				       cc.contract_number,
				       cc.bp_id_tenant_n,
				       cc.payment_req_id,
				       cc.bp_name,
				       cc.bp_bank_account_name,
				       cc.bp_bank_account_num,
				       cc.cdd_list_id,
				       cc.amount
				  from csh_payment_req_hd_bond_lv cc 
				  where   cc.approval_status  is null 
				 AND  cc.req_date >= to_date(nvl(${@req_date_from},'1700-01-01'),'yyyy-mm-dd')
				  AND  cc.req_date <= to_date(nvl(${@req_date_to},'2900-12-30'),'yyyy-mm-dd')
				 AND cc.created_by = ${@user_id}
				  ) t #WHERE_CLAUSE#
                        ORDER BY
                            t.payment_req_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="payment_req_number"/>
        <bm:field name="req_date"/>
        <bm:field name="req_date1"/>
        <bm:field name="payment_req_id"/>
        <bm:field name="contract_number"/>
        <bm:field name="bp_id_tenant_n"/>
        <bm:field name="bp_name"/>
        <bm:field name="bp_bank_account_name"/>
        <bm:field name="bp_bank_account_num"/>
        <bm:field name="amount"/>
        <bm:field name="cdd_list_id"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field name="searchInput" queryExpression="(t.payment_req_number like &apos;%&apos;||${@searchInput}||&apos;%&apos; or t.contract_number like &apos;%&apos;||${@searchInput}||&apos;%&apos; or t.bp_id_tenant_n like &apos;%&apos;||${@searchInput}||&apos;%&apos;)"/>
        <bm:query-field name="payment_req_number" queryExpression="t.payment_req_number like &apos;%&apos;||upper(${@payment_req_number})||&apos;%&apos;"/>
        <bm:query-field name="payment_req_id" queryExpression="t.payment_req_id= ${@payment_req_id}"/>
        <bm:query-field name="contract_number" queryExpression="t.contract_number like &apos;%&apos;||upper(${@contract_number})||&apos;%&apos;"/>
        <bm:query-field name="bp_id_tenant_n" queryExpression="t.bp_id_tenant_n like &apos;%&apos;||${@bp_id_tenant_n}||&apos;%&apos;"/>
        <bm:query-field name="req_date1" queryExpression="t.req_date1 &gt;= to_date(${@req_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="req_date1" queryExpression="t.req_date1 &lt;= to_date(${@req_date_to},&apos;yyyy-mm-dd&apos;)"/>
    </bm:query-fields>
</bm:model>
