con_waiver_details.lwm 6.14 KB
Newer Older
Spencer Chang's avatar
Spencer Chang 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 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138
<?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 &gt;= to_date(${@base_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="base_date_to" queryExpression="t1.base_date &lt;= to_date(${@base_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <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 &gt;= ${@reduce_amount_total_from}"/>
        <bm:query-field name="reduce_amount_total_to" queryExpression="t1.reduce_amount_total &lt;= ${@reduce_amount_total_to}"/>
    </bm:query-fields>
</bm:model>