<?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 * from(
                SELECT '抵扣记录：应付客户保证金的抵扣' AS write_off_type,
                o.write_off_classification,
                (select v.code_value_name from
                sys_code_values_v v
                where v.code ='DS_CSH_WRITE_OFF_CLASSIFICATION'
                AND v.code_value = o.write_off_classification)write_off_classification_n,
                o.JOURNAL_DATE write_off_date,
                o.csh_write_off_amount as write_off_due_amount,
                (SELECT m.bp_name FROM hls_bp_master m ,con_contract c WHERE m.bp_id = c.bp_id_tenant and c.contract_id = o.contract_id) bp_name,
                (SELECT cc.contract_number
                FROM con_contract cc
                WHERE cc.contract_id = o.contract_id) contract_number,
                o.times,
                (SELECT i.description
                FROM hls_cashflow_item i
                WHERE i.cf_item = o.cf_item) cf_item_n
                FROM csh_transaction t, csh_write_off o
                WHERE t.transaction_id = o.csh_transaction_id
                AND t.transaction_type = 'DEDUCTION'
                AND t.reversed_flag = 'N'
                and o.reversed_flag = 'N'

                AND EXISTS (SELECT 1
                      FROM csh_write_off oo
                     WHERE oo.csh_transaction_id = o.csh_transaction_id
                       AND oo.cashflow_id = ${@outflow_id})
                AND o.cashflow_id != ${@outflow_id}

                UNION
                SELECT '退款记录：应付保证金的支付' AS write_off_type,
                o.write_off_classification,
                (select v.code_value_name from
                sys_code_values_v v
                where v.code ='DS_CSH_WRITE_OFF_CLASSIFICATION'
                AND v.code_value = o.write_off_classification)write_off_classification_n,
                o.JOURNAL_DATE write_off_date,
                o.csh_write_off_amount as write_off_due_amount,
                (SELECT m.bp_name FROM hls_bp_master m ,con_contract c WHERE m.bp_id = c.bp_id_tenant and c.contract_id = o.contract_id) bp_name,
                (SELECT cc.contract_number
                FROM con_contract cc
                WHERE cc.contract_id = o.contract_id) contract_number,
                o.times,
                (SELECT i.description
                FROM hls_cashflow_item i
                WHERE i.cf_item = o.cf_item) cf_item_n
                FROM csh_transaction t, csh_write_off o
                WHERE t.transaction_id = o.csh_transaction_id
                AND t.transaction_type = 'PAYMENT'
                AND o.write_off_type = 'PAYMENT_DEBT'
                AND t.reversed_flag = 'N'
                and o.reversed_flag = 'N'
                AND EXISTS (SELECT 1
                      FROM csh_write_off oo
                     WHERE oo.csh_transaction_id = o.csh_transaction_id
                       AND oo.cashflow_id = ${@outflow_id})) t1

                #WHERE_CLAUSE#
                 #ORDER_BY_CLAUSE#
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>