<?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 '%'||${@agent_name}||'%'"/> <bm:query-field name="agent_code" queryExpression="t.agent_code like '%'||${@agent_code}||'%'"/> <bm:query-field name="balance_amount_from" queryExpression="(t.transaction_amount - t.deduction_amount - t.refund_amount) >= ${@balance_amount_from}"/> <bm:query-field name="balance_amount_to" queryExpression="(t.transaction_amount - t.deduction_amount - t.refund_amount) <= ${@balance_amount_to}"/> </bm:query-fields> </bm:model>