<?xml version="1.0" encoding="UTF-8"?> <bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false"> <bm:operations> <bm:operation name="query"> <bm:query-sql><![CDATA[ SELECT * FROM ( SELECT cr.record_id, cr.penalty_batch_number, --批次号 (SELECT t1.contract_number FROM con_contract t1 WHERE t1.contract_id = t.contract_id) contract_number, (SELECT hbm.extra_nam FROM hls_bp_master hbm WHERE hbm.bp_id = (SELECT t1.bp_id_agent_level1 FROM con_contract t1 WHERE t1.contract_id = t.contract_id)) bp_name_agent, (SELECT bp_name FROM hls_bp_master hbm WHERE hbm.bp_id = (SELECT bp_id_tenant FROM con_contract t1 WHERE t1.contract_id = t.contract_id)) bp_name_tenant, t.breach_type, (SELECT scv.code_value_name FROM sys_code_values_v scv WHERE scv.code = 'BREACH_TYPE' AND scv.code_value = t.breach_type) breach_type_n, --违约金类型 to_char(nvl(t.base_date, sysdate),'yyyy-mm-dd') base_date, --基准日 t.reduce_amount_total, --减免总金额 (select t1.description from sys_user t1 where t1.user_id = cr.created_by) created_by_n, --创建人 cr.creation_date, cr.status, (SELECT v.code_value_name value_name FROM sys_code_values_v v WHERE v.code = 'CASE_STATUS' AND v.code_value = cr.status) status_n FROM con_contract_case_record cr, con_contract_penalty_reduce t WHERE cr.document_table = 'CON_CONTRACT_PENALTY_REDUCE' and cr.record_id = t.record_id )t where (t.penalty_batch_number = ${/parameter/@penalty_batch_number} or ${/parameter/@penalty_batch_number} = 'undefined' or ${/parameter/@penalty_batch_number} is null) and (t.created_by_n = ${/parameter/@created_by_n} or ${/parameter/@created_by_n} = 'undefined' or ${/parameter/@created_by_n} is null) and (t.status = ${/parameter/@status} or ${/parameter/@status} = 'undefined' or ${/parameter/@status} is null) and (trunc(t.creation_date) >= to_date(${/parameter/@creationdate_from}, 'yyyy-mm-dd') or ${/parameter/@creationdate_from} = 'undefined' or to_date(${/parameter/@creationdate_from},'yyyy-mm-dd') is null) and (trunc(t.creation_date) <= to_date(${/parameter/@creationdate_to},'yyyy-mm-dd') or ${/parameter/@creationdate_to} = 'undefined' or to_date(${/parameter/@creationdate_to},'yyyy-mm-dd') is null) ]]></bm:query-sql> </bm:operation> </bm:operations> </bm:model>