csh_agent_deposit.lwm

<?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 t1.*,
                        (t1.deposit - t1.deduction_amount - t1.refund_amount) balance_amount
                        FROM (SELECT
                                ccc.cashflow_id as inflow_id,
                                ca.cashflow_id as outflow_id,
                                c.contract_id,
                                c.contract_number,
                                c.contract_name,
                                c.finance_amount,
                                c.bp_id_tenant as bp_id,
                                (SELECT m.bp_name
                                    FROM hls_bp_master m
                                    WHERE m.bp_id = c.bp_id_tenant) tenant_name,
                                (SELECT m.bp_name
                                    FROM hls_bp_master m
                                    WHERE m.bp_id = c.bp_id_agent_level1) agent_name,
                                (SELECT m.bp_code
                                    FROM hls_bp_master m
                                    WHERE m.bp_id = c.bp_id_agent_level1) agent_code,
                                (select nvl(sum(nvl(o.write_off_due_amount,0)),0)
                  from csh_write_off o
                 where ccc.cashflow_id = o.cashflow_id) deposit,--nvl(ccc.due_amount, 0) deposit,
                                nvl((SELECT SUM(o.write_off_due_amount)
                                        FROM csh_transaction t, csh_write_off o
                                        WHERE o.cf_item = 509
                                        AND o.cf_type = 5
                                        AND t.transaction_id = o.csh_transaction_id
                                        AND t.transaction_type = 'DEDUCTION'
                                        AND t.reversed_flag = 'N'
                                        AND o.contract_id = c.contract_id
                                        AND o.times = ca.times),
                                0) deduction_amount,
                                nvl((SELECT SUM(o.write_off_due_amount)
                                        FROM csh_write_off o
                                        WHERE o.write_off_type = 'PAYMENT_DEBT'
                                        AND o.reversed_flag= 'N'
                                        AND o.cashflow_id = ca.cashflow_id
                                        AND NOT EXISTS
                                  (SELECT 1 FROM csh_transaction ct
                                          WHERE ct.transaction_id = o.csh_transaction_id
                                            AND ct.transaction_type = 'DEDUCTION'
                                            AND ct.reversed_flag = 'N')),
                                0) refund_amount
                                FROM con_contract c, con_contract_cashflow ccc,con_contract_cashflow ca
                                WHERE c.contract_id = ccc.contract_id
                                    AND ccc.cf_item = 508
                                    AND ccc.cf_type = 5
                                    and ca.cf_item = 509
                                    and ca.cf_type =5
                                    and c.data_class= 'NORMAL'
                                    and c.contract_status not in ('CANCEL','NEW')
                                    and ca.contract_id = ccc.contract_id

                    ) t1
                #WHERE_CLAUSE#
                #ORDER_BY_CLAUSE#
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="agent_name" queryExpression="t1.agent_name like  &apos;%&apos; || ${@agent_name} || &apos;%&apos; "/>
        <bm:query-field name ="agent_code" queryExpression="t1.agent_code like  &apos;%&apos; || ${@agent_code} || &apos;%&apos; "/>
        <bm:query-field name="contract_number" queryExpression="t1.contract_number like  &apos;%&apos; || ${@contract_number} || &apos;%&apos; "/>
        <bm:query-field name ="contract_name" queryExpression="t1.contract_name like  &apos;%&apos; || ${@contract_name} || &apos;%&apos; "/>
        <bm:query-field name ="cf_item" queryExpression="t1.cf_item like  &apos;%&apos; || ${@cf_item} || &apos;%&apos; "/>
        <bm:query-field name="tenant_name" queryExpression="t1.tenant_name like  &apos;%&apos; || ${@tenant_name} || &apos;%&apos; "/>
        <bm:query-field name="balance_amount_from" queryExpression="(t1.deposit - t1.deduction_amount - t1.refund_amount) &gt;= ${@balance_amount_from}"/>
        <bm:query-field name="balance_amount_to" queryExpression="(t1.deposit - t1.deduction_amount - t1.refund_amount) &lt;= ${@balance_amount_to}"/>
    </bm:query-fields>
</bm:model>