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