<?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 '%' || ${@contract_number} || '%' "/> <bm:query-field name="agent_name" queryExpression="t1.agent_name like '%' || ${@agent_name} || '%' "/> <bm:query-field name="write_off_date_from" queryExpression="t1.write_off_date >= to_date(${@write_off_date_from},'yyyy-mm-dd')"/> <bm:query-field name="write_off_date_to" queryExpression="t1.write_off_date <= 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 '%' || ${@bp_name} || '%' "/> </bm:query-fields> </bm:model>