<?xml version="1.0" encoding="UTF-8"?> <bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false"> <bm:fields> <bm:field name="contract_number"/> <bm:field name="bp_code"/> <bm:field name="bp_name"/> <bm:field name="bp_agent_code"/> <bm:field name="bp_agent_name"/> <bm:field name="division_n"/> <bm:field name="lease_channel"/> <bm:field name="lease_channel_n"/> <bm:field name="business_type"/> <bm:field name="business_type_n"/> <bm:field name="pattern"/> <bm:field name="machine_number"/> <bm:field name="equipment_number"/> <bm:field name="base_date" databaseType="DATE" datatype="java.util.Date"/> <bm:field name="penalty_reduction"/> <bm:field name="penalty_reduction_n"/> <bm:field name="reduce_amount_total" databaseType="NUMBER" datatype="java.lang.Double"/> <bm:field name="created_by"/> <bm:field name="created_by_n"/> <bm:field name="penalty_batch_number"/> <bm:field name="active_flag"/> <bm:field name="active_flag_n"/> <bm:field name="reduce_amount_total_ratio"/> </bm:fields> <bm:operations> <bm:operation name="query"> <bm:query-sql><![CDATA[ select * from (SELECT (SELECT t1.contract_number FROM con_contract t1 WHERE t1.contract_id = t.contract_id) contract_number, (SELECT hbm.bp_code 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_code, (SELECT hbm.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, (SELECT hbm.bp_code 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_agent_code, (SELECT hbm.bp_name 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_agent_name, (SELECT t1.description AS value_name FROM hls_division t1 WHERE t1.division = (SELECT t1.division FROM con_contract t1 WHERE t1.contract_id = t.contract_id)) division_n, (SELECT t1.lease_channel FROM con_contract t1 WHERE t1.contract_id = t.contract_id) lease_channel, (Select ch.description from hls_lease_channel ch Where ch.lease_channel = (SELECT t1.lease_channel FROM con_contract t1 WHERE t1.contract_id = t.contract_id)) lease_channel_n, (SELECT t1.business_type FROM con_contract t1 WHERE t1.contract_id = t.contract_id) business_type, (select bt.description from hls_business_type bt where bt.business_type = (SELECT t1.business_type FROM con_contract t1 WHERE t1.contract_id = t.contract_id)) AS business_type_n, (SELECT i.pattern FROM con_contract_lease_item i WHERE i.contract_id = t.contract_id AND i.equipment_type = 'MAIN') pattern, --机型 (SELECT i.machine_number FROM con_contract_lease_item i WHERE i.contract_id = t.contract_id AND i.equipment_type = 'MAIN') machine_number, --机号 (SELECT i.equipment_number FROM con_contract_lease_item i WHERE i.contract_id = t.contract_id AND i.equipment_type = 'MAIN') equipment_number, --装备号 t.base_date, t.penalty_reduction, (SELECT scv.code_value_name FROM sys_code_values_v scv WHERE scv.code = 'PENALTY_REDUCTION' AND scv.code_value = t.penalty_reduction) penalty_reduction_n, t.reduce_amount_total, (select cc.created_by from con_contract_case_record cc WHERE cc.record_id = t.record_id) created_by, (select t1.description from sys_user t1 where t1.user_id = (select cc.created_by from con_contract_case_record cc WHERE cc.record_id = t.record_id)) created_by_n, (select cc.penalty_batch_number from con_contract_case_record cc WHERE cc.record_id = t.record_id) penalty_batch_number, t.active_flag, (SELECT v.code_value_name AS value_name FROM sys_code_values_v v WHERE v.code = 'PENALTY_ACTIVE_FLAG' AND v.code_value = t.active_flag) active_flag_n, to_char(t.reduce_amount_total_ratio*100,'FM990.00')||decode(reduce_amount_total_ratio,null,null,'%') reduce_amount_total_ratio FROM con_contract_penalty_reduce t) t1 #WHERE_CLAUSE# #ORDER_BY_CLAUSE# ]]></bm:query-sql> </bm:operation> </bm:operations> <bm:query-fields> <bm:query-field field="contract_number" queryOperator="like"/> <bm:query-field field="bp_name" queryOperator="like"/> <bm:query-field field="bp_agent_name" queryOperator="like"/> <bm:query-field name="base_date_from" queryExpression="t1.base_date >= to_date(${@base_date_from},'yyyy-mm-dd')"/> <bm:query-field name="base_date_to" queryExpression="t1.base_date <= to_date(${@base_date_to},'yyyy-mm-dd')"/> <bm:query-field field="penalty_reduction_n" queryOperator="="/> <bm:query-field field="created_by_n" queryOperator="like"/> <bm:query-field field="active_flag_n" queryOperator="="/> <bm:query-field name="reduce_amount_total_from" queryExpression="t1.reduce_amount_total >= ${@reduce_amount_total_from}"/> <bm:query-field name="reduce_amount_total_to" queryExpression="t1.reduce_amount_total <= ${@reduce_amount_total_to}"/> </bm:query-fields> </bm:model>