<?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_code
                                FROM hls_bp_master m
                               WHERE m.bp_id = c.bp_id_tenant) tenant_code,
                             (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 = 52
                                    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 = 51
                         AND ccc.cf_type = 5
                         AND ca.cf_item = 52
                         AND ca.cf_type = 5
                         AND ca.contract_id = ccc.contract_id
                         and c.contract_status not in ('CANCEL','NEW')
                         AND c.data_class = 'NORMAL'

                      ) t1

                #WHERE_CLAUSE#
                #ORDER_BY_CLAUSE#
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="inflow_id" queryExpression="t1.inflow_id= ${@inflow_id}"/>
        <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="tenant_name" queryExpression="t1.tenant_name like  &apos;%&apos; || ${@tenant_name} || &apos;%&apos; "/>
        <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="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>
