con_penalty_query.lwm 2.6 KB
Newer Older
gzj34291's avatar
gzj34291 committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
<?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>