<?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 c.contract_number,
       c.business_type,
       (select bt.description
          from hls_business_type bt
         where bt.business_type = c.business_type) AS business_type_n,
       (Select ch.lease_channel
          from hls_lease_channel ch
         Where ch.lease_channel =
               (select cc.lease_channel
                  from con_contract cc
                 where cc.contract_id = c.contract_id)) As lease_channel,
       (Select ch.description
          from hls_lease_channel ch
         Where ch.lease_channel =
               (select cc.lease_channel
                  from con_contract cc
                 where cc.contract_id = c.contract_id)) As lease_channel_desc,
       (select ccli.machine_number
          from con_contract_lease_item ccli
         where ccli.contract_id = c.contract_id
           AND ccli.equipment_type = 'MAIN') machine_number,
       (select ccli.pattern
          from con_contract_lease_item ccli
         where ccli.contract_id = c.contract_id
           AND ccli.equipment_type = 'MAIN') pattern,
       (select h.bp_name from hls_bp_master h where h.bp_id = c.bp_id_tenant) bp_name,
       (select h.bp_name
          from hls_bp_master h
         where h.bp_id = c.bp_id_agent_level1) bp_agent_name,
       c.lease_execution_date,
       c.lease_item_amount,
       decode(cwo.write_off_classification,105,(select cti.storage_date
          from con_trailer_info cti
         where cti.contract_id = c.contract_id)) storage_date,
       decode(cwo.write_off_classification,105,(select cti.storage_amount
          from con_trailer_info cti
         where cti.contract_id = c.contract_id)) storage_amount,
       '虚拟收款' funds_source,
           cwo.write_off_date,
           cwo.write_off_due_amount,
           nvl(cwo.write_off_principal,0) write_off_principal,
           nvl(cwo.write_off_interest,0) write_off_interest,
           (select nvl(write_off_due_amount,0)
             from csh_write_off cw
            where cw.write_off_id = cwo.write_off_id
              and cw.cf_item = 9) penalty_interes,
            (select nvl(write_off_due_amount,0)
             from csh_write_off cw
            where cw.write_off_id = cwo.write_off_id
              and cw.cf_item in (3,11)) installation_charges,
            (select nvl(write_off_due_amount,0)
             from csh_write_off cw
            where cw.write_off_id = cwo.write_off_id
              and cw.cf_item =8) transfer_fee,
              (select nvl(write_off_due_amount,0)
             from csh_write_off cw
            where cw.write_off_id = cwo.write_off_id
              and cw.cf_item not in (1,3,8,9,11,200)) other_fee,
              cwo.times,
              cwo.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 = cwo.write_off_classification)write_off_classification_n
  from csh_write_off cwo, con_contract c
 where cwo.contract_id = c.contract_id
   and c.data_class ='NORMAL'
   and cwo.reversed_flag = 'N'
   and cwo.write_off_classification in (105,170)
                    ) t1 #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="write_off_classification"/>
        <bm:field name="write_off_classification_n"/>
        <bm:field name="times"/>
        <bm:field name="contract_number"/>
        <bm:field name="business_type"/>
        <bm:field name="business_type_n"/>
        <bm:field name="lease_channel"/>
        <bm:field name="lease_channel_desc"/>
        <bm:field name="machine_number"/>
        <bm:field name="pattern"/>
        <bm:field name="bp_name"/>
        <bm:field name="bp_agent_name"/>
        <bm:field name="lease_execution_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="lease_item_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="storage_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="storage_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="funds_source"/>
        <bm:field name="write_off_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="write_off_due_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="write_off_principal" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="write_off_interest" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="penalty_interes" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="installation_charges" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="transfer_fee" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="other_fee" databaseType="NUMBER" datatype="java.lang.Double"/>
    </bm:fields>
    <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 field="lease_channel" queryOperator="="/>
        <bm:query-field field="business_type" queryOperator="="/>
        <bm:query-field name="lease_execution_date_from" queryExpression="to_char(lease_execution_date,&apos;yyyy-mm-dd&apos;)&gt;=${@lease_execution_date_from}"/>
        <bm:query-field name="lease_execution_date_to" queryExpression="to_char(lease_execution_date,&apos;yyyy-mm-dd&apos;)&lt;=${@lease_execution_date_to}"/>
    </bm:query-fields>
</bm:model>
