<?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 deposit_type,
                               '核销为保证金池' 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,
                               '代理商名称' AS agent_name,
                               o.write_off_date,
                               o.csh_write_off_amount as write_off_due_amount,
                               (SELECT m.bp_name FROM hls_bp_master m WHERE m.bp_id = t.bp_id) bp_name,
                               NULL AS contract_number,
                               NULL AS times,
                               NULL AS cf_item,
                               NULL AS cf_item_n,
                               o.description
                          FROM csh_transaction t, csh_write_off o
                         WHERE t.transaction_id = o.write_off_id
                           AND t.deposit_flag = 'Y'
                           AND o.write_off_type = 'RECEIPT_CREDIT'
                           AND t.reversed_flag = 'N'
                           AND o.reversed_flag = 'N'
                        UNION
                        SELECT '保证金池' AS deposit_type,
                               '（抵扣记录：保证金池核销债权）' 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,
                               '代理商名称' AS agent_name,
                               o.write_off_date,
                               o.csh_write_off_amount,
                               (SELECT m.bp_name FROM hls_bp_master m WHERE m.bp_id = t.bp_id) bp_name,
                               NULL AS contract_number,
                               NULL AS times,
                               NULL AS cf_item,
                               NULL AS cf_item_n,
                               o.description
                          FROM csh_transaction t, csh_write_off o
                         WHERE t.transaction_id = o.write_off_id
                           AND t.transaction_type = 'DEDUCTION'
                           AND o.write_off_type = 'RECEIPT_CREDIT'
                           AND t.deposit_flag = 'Y'
                           AND t.reversed_flag = 'N'
                           AND o.reversed_flag = 'N'
                        UNION
                        SELECT '保证金池' AS deposit_type,
                               '（退款记录：保证金池退款）' 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,
                               '代理商名称' AS agent_name,
                               o.write_off_date,
                               o.csh_write_off_amount,
                               (SELECT m.bp_name FROM hls_bp_master m WHERE m.bp_id = t.bp_id) bp_name,
                               NULL AS contract_number,
                               NULL AS times,
                               NULL AS cf_item,
                               NULL AS cf_item_n,
                               o.description
                          FROM csh_transaction t, csh_write_off o
                         WHERE t.transaction_id = o.write_off_id
                           AND t.deposit_flag = 'Y'
                           AND o.write_off_type = 'CSH_RETURN'
                           AND t.reversed_flag = 'N'
                           AND o.reversed_flag = 'N'

                        UNION
                        SELECT '客户保证金' AS deposit_type,
                               '核销为客户保证金' 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,
                               (SELECT m.bp_name
                                  FROM con_contract c, hls_bp_master m
                                 WHERE c.contract_id = o.contract_id
                                   AND c.bp_id_agent_level1 = m.bp_id) agent_name,
                               o.write_off_date,
                               o.csh_write_off_amount,
                               (SELECT m.bp_name FROM hls_bp_master m WHERE m.bp_id = t.bp_id) bp_name,
                               (SELECT cc.contract_number
                                  FROM con_contract cc
                                 WHERE cc.contract_id = o.contract_id) contract_number,
                               o.times,
                               o.cf_item,
                               (SELECT i.description
                                  FROM hls_cashflow_item i
                                 WHERE i.cf_item = o.cf_item) cf_item_n,
                               o.description
                          FROM csh_transaction t, csh_write_off o
                         WHERE t.transaction_id = o.csh_transaction_id
                           AND t.transaction_type = 'RECEIPT'
                           AND t.reversed_flag = 'N'
                           AND o.reversed_flag = 'N'
                           AND o.cf_item = 51
                           AND o.cf_type = 5

                        UNION
                        SELECT '客户保证金' AS deposit_type,
                               '（抵扣记录：应付客户保证金的抵扣）' 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,
                               (SELECT m.bp_name
                                  FROM con_contract c, hls_bp_master m
                                 WHERE c.contract_id = o.contract_id
                                   AND c.bp_id_agent_level1 = m.bp_id) agent_name,
                               o.write_off_date,
                               o.csh_write_off_amount,
                               (SELECT m.bp_name FROM hls_bp_master m WHERE m.bp_id = t.bp_id) bp_name,
                               (SELECT cc.contract_number
                                  FROM con_contract cc
                                 WHERE cc.contract_id = o.contract_id) contract_number,
                               o.times,
                               o.cf_item,
                               (SELECT i.description
                                  FROM hls_cashflow_item i
                                 WHERE i.cf_item = o.cf_item) cf_item_n,
                               o.description
                          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 o.cf_item = 52
                           AND o.cf_type = 5

                        UNION
                        SELECT

                         '客户保证金' AS deposit_type,
                         '（退款记录：应付客户保证金的支付）' 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,
                         (SELECT m.bp_name
                            FROM con_contract c, hls_bp_master m
                           WHERE c.contract_id = o.contract_id
                             AND c.bp_id_agent_level1 = m.bp_id) agent_name,
                         o.write_off_date,
                         o.csh_write_off_amount,
                         (SELECT m.bp_name FROM hls_bp_master m WHERE m.bp_id = t.bp_id) bp_name,
                         (SELECT cc.contract_number
                            FROM con_contract cc
                           WHERE cc.contract_id = o.contract_id) contract_number,
                         o.times,
                         o.cf_item,
                         (SELECT i.description
                            FROM hls_cashflow_item i
                           WHERE i.cf_item = o.cf_item) cf_item_n,
                         o.description
                          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 o.cf_item = 52
                           AND o.cf_type = 5

                        UNION
                        SELECT

                         '代理商代付客户保证金' AS deposit_type,
                         '核销为代理商代付客户保证金' 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,
                         (SELECT m.bp_name
                            FROM con_contract c, hls_bp_master m
                           WHERE c.contract_id = o.contract_id
                             AND c.bp_id_agent_level1 = m.bp_id) agent_name,
                         o.write_off_date,
                         o.csh_write_off_amount,
                         (SELECT m.bp_name FROM hls_bp_master m WHERE m.bp_id = t.bp_id) bp_name,
                         (SELECT cc.contract_number
                            FROM con_contract cc
                           WHERE cc.contract_id = o.contract_id) contract_number,
                         o.times,
                         o.cf_item,
                         (SELECT i.description
                            FROM hls_cashflow_item i
                           WHERE i.cf_item = o.cf_item) cf_item_n,
                         o.description
                          FROM csh_transaction t, csh_write_off o
                         WHERE t.transaction_id = o.csh_transaction_id
                           AND t.transaction_type = 'RECEIPT'
                           AND t.reversed_flag = 'N'
                           AND o.reversed_flag = 'N'
                           AND o.cf_item = 508
                           AND o.cf_type = 5
                        UNION
                        SELECT

                         '代理商代付客户保证金' AS deposit_type,
                         '（抵扣记录：代理商代付应付客户保证金的抵扣）' 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,
                         (SELECT m.bp_name
                            FROM con_contract c, hls_bp_master m
                           WHERE c.contract_id = o.contract_id
                             AND c.bp_id_agent_level1 = m.bp_id) agent_name,
                         o.write_off_date,
                         o.csh_write_off_amount,
                         (SELECT m.bp_name FROM hls_bp_master m WHERE m.bp_id = t.bp_id) bp_name,
                         (SELECT cc.contract_number
                            FROM con_contract cc
                           WHERE cc.contract_id = o.contract_id) contract_number,
                         o.times,
                         o.cf_item,
                         (SELECT i.description
                            FROM hls_cashflow_item i
                           WHERE i.cf_item = o.cf_item) cf_item_n,
                         o.description
                          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 o.cf_item = 509
                           AND o.cf_type = 5

                        UNION
                        SELECT '代理商代付客户保证金' AS deposit_type,

                               '（退款记录：代理商代付应付客户保证金的支付）' 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,
                               (SELECT m.bp_name
                                  FROM con_contract c, hls_bp_master m
                                 WHERE c.contract_id = o.contract_id
                                   AND c.bp_id_agent_level1 = m.bp_id) agent_name,
                               o.write_off_date,
                               o.csh_write_off_amount,
                               (SELECT m.bp_name FROM hls_bp_master m WHERE m.bp_id = t.bp_id) bp_name,
                               (SELECT cc.contract_number
                                  FROM con_contract cc
                                 WHERE cc.contract_id = o.contract_id) contract_number,
                               o.times,
                               o.cf_item,
                               (SELECT i.description
                                  FROM hls_cashflow_item i
                                 WHERE i.cf_item = o.cf_item) cf_item_n,
                               o.description
                          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 o.cf_item = 509
                           AND o.cf_type = 5) t1

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

    <bm:query-fields>
        <bm:query-field name="deposit_type" queryExpression="t1.deposit_type=${@deposit_type}"/>
        <bm:query-field name="write_off_type" queryExpression="t1.write_off_type=${@write_off_type}"/>
        <bm:query-field name="deposit_type" queryExpression="t1.deposit_type=${@deposit_type}"/>
        <bm:query-field name="cf_item" queryExpression="t1.cf_item=${@cf_item}"/>
        <bm:query-field name="contract_number" queryExpression="t1.contract_number like  &apos;%&apos; || ${@contract_number} || &apos;%&apos; "/>
        <bm:query-field name="agent_name" queryExpression="t1.agent_name like  &apos;%&apos; || ${@agent_name} || &apos;%&apos; "/>
        <bm:query-field name="write_off_date_from" queryExpression="t1.write_off_date &gt;= to_date(${@write_off_date_from},'yyyy-mm-dd')"/>
        <bm:query-field name="write_off_date_to" queryExpression="t1.write_off_date &lt;= to_date(${@write_off_date_to},'yyyy-mm-dd')"/>
        <bm:query-field name="write_off_due_amount" queryExpression="t1.write_off_due_amount=${@write_off_due_amount}"/>
        <bm:query-field name="bp_name" queryExpression="t1.bp_name like  &apos;%&apos; || ${@bp_name} || &apos;%&apos; "/>
    </bm:query-fields>
</bm:model>
