<?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 t.*,
                           (t.transaction_amount - t.deduction_amount - t.refund_amount) balance_amount
                      FROM (SELECT (SELECT m.bp_name
                                      FROM hls_bp_master m
                                     WHERE m.bp_id = t1.bp_id) AS agent_name,
                                   (SELECT m.bp_code
                                      FROM hls_bp_master m
                                     WHERE m.bp_id = t1.bp_id) AS agent_code,
                                   t1.transaction_amount,
                                   t1.transaction_id,
                                   t1.transaction_date,
                                   t1.transaction_type,
                                   t1.bank_slip_num,
                                   nvl((SELECT SUM(o.write_off_due_amount)
                                     FROM csh_write_off o
                                    WHERE o.csh_transaction_id = t1.transaction_id
                                      AND o.reversed_flag = 'N'
                                      AND o.write_off_type = 'RECEIPT_CREDIT'),
                                   0) deduction_amount,
                                   nvl((SELECT SUM(o.csh_write_off_amount)
                                         FROM csh_write_off o
                                      WHERE o.csh_transaction_id = t1.transaction_id
                                          AND o.reversed_flag = 'N'
                                          AND o.write_off_type = 'CSH_RETURN'),
                                       0) refund_amount,
                                   t1.company_id,
                                   t1.internal_period_num,
                                   t1.returned_amount,
                                   t1.write_off_amount,
                                   --csh_deposit_pkg.get_balance_amount_5(t1.bp_id) AS balance_amount_5,
                                   t1.balance_amount_5,
                                   (SELECT a.bank_account_num
                                      FROM hls_bp_master_bank_account a
                                     WHERE a.bank_account_id = t1.bp_bank_account_id) AS bp_bank_account_num,
                                   (SELECT a.bank_account_code
                                      FROM hls_bp_master_bank_account a
                                     WHERE a.bank_account_id = t1.bp_bank_account_id) AS bp_bank_account_code,
                                   (SELECT a.bank_account_name
                                      FROM hls_bp_master_bank_account a
                                     WHERE a.bank_account_id = t1.bp_bank_account_id) AS bp_bank_account_name,
                                   (SELECT a.bank_account_code
                                      FROM csh_bank_account a
                                     WHERE a.bank_account_id = t1.bank_account_id
                                       AND a.company_id = t1.company_id) AS bank_account_code,
                                   (SELECT a.bank_account_name
                                      FROM csh_bank_account a
                                     WHERE a.bank_account_id = t1.bank_account_id
                                       AND a.company_id = t1.company_id) AS bank_account_name,
                                   (SELECT a.bank_account_num
                                      FROM csh_bank_account a
                                     WHERE a.bank_account_id = t1.bank_account_id
                                       AND a.company_id = t1.company_id) AS bank_account_num,

                                   gld_exchange_rate_pkg.get_currency_exchange(t1.transaction_amount,
                                                                               t1.exchange_rate,
                                                                               t1.exchange_rate_quotation) AS transaction_functional_amount,
                                   t1.exchange_rate_type,
                                   t1.exchange_rate_quotation,
                                   t1.exchange_rate,
                                   t1.period_name,
                                   t1.transaction_num,
                                   (t1.transaction_amount - nvl(t1.returned_amount, 0) -
                                   nvl(t1.write_off_amount, 0)) remain_total_amount,
                                   (SELECT gc.currency_name
                                      FROM gld_currency_vl gc
                                     WHERE gc.currency_code = t1.currency_code) AS currency_name,
                                   t1.bp_id,
                                   (SELECT d.bp_code
                                      FROM hls_bp_master d
                                     WHERE d.bp_id = t1.bp_id) AS bp_code,
                                   (SELECT d.bp_name
                                      FROM hls_bp_master d
                                     WHERE d.bp_id = t1.bp_id) AS bp_name,

                                   (SELECT b.description
                                      FROM hls_bp_category b
                                     WHERE b.bp_category = t1.bp_category) AS bp_category_display,

                                   (SELECT tv.type_name
                                      FROM gld_exchangerate_types_vl tv
                                     WHERE tv.type_code = t1.exchange_rate_type) AS exchange_rate_type_display

                              FROM csh_transaction t1
                             WHERE t1.company_id = 1
                               AND transaction_category = 'BUSINESS'
                               AND t1.deposit_flag = 'Y'
                             --  AND t1.write_off_flag != 'FULL'
                               AND t1.returned_flag != 'FULL'
                               AND t1.posted_flag = 'Y'
                               AND t1.reversed_flag = 'N') t

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