<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: qianming  
    $Date: 2014-8-27 下午6:22:32  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" alias="t1">
    <bm:fields>
        <bm:field name="transaction_id"/>
        <bm:field name="contract_number"/>
        <bm:field name="bp_name"/>
        <bm:field name="write_off_type_dis"/>
        <bm:field name="write_off_due_amount"/>
        <bm:field name="write_off_date"/>
        <bm:field name="lease_times"/>
        <bm:field name="description"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
				  SELECT transaction_id,contract_number,bp_name,write_off_type_dis,write_off_due_amount,write_off_date,
				  lease_times,description
				  FROM (SELECT t.csh_transaction_id transaction_id,
                       (SELECT contract_number FROM con_contract WHERE contract_id = t.contract_id) contract_number,
                       (SELECT a.bp_name
                          FROM hls_bp_master   a,
                               con_contract_bp b
                         WHERE a.bp_id = b.bp_id
                               AND b.bp_category = 'TENANT'
                               AND b.contract_id = t.contract_id) bp_name,
                       (SELECT b.bp_id
                          FROM con_contract_bp b
                         WHERE b.bp_category = 'TENANT'
                               AND b.contract_id = t.contract_id) bp_id,
                       (SELECT code_value_name
                          FROM sys_code_values_v
                         WHERE code = 'CSH513_WRITE_OFF_TYPE'
                               AND code_value = t.write_off_type) write_off_type_dis,
                       decode(nvl(t.csh_write_off_amount,0),0,'0.00',to_char(t.csh_write_off_amount,'FM999,999,999.00'))write_off_due_amount,
                       to_char(t.write_off_date,'yyyy-mm-dd')write_off_date,
                       t.times lease_times,
                       t.description
                  FROM csh_write_off t
                 WHERE t.write_off_type in ('DEPOSIT_CREDIT','CSH_RETURN')
                 and t.reversed_flag = 'N'
                 and t.csh_transaction_id in (select ct.transaction_id from csh_transaction ct where ct.bp_id=${@bp_id})
                 union
                 SELECT t.csh_transaction_id transaction_id,
                       (SELECT contract_number FROM con_contract WHERE contract_id = t.contract_id) contract_number,
                       (SELECT a.bp_name
                          FROM hls_bp_master   a,
                               con_contract_bp b
                         WHERE a.bp_id = b.bp_id
                               AND b.bp_category = 'TENANT'
                               AND b.contract_id = t.contract_id) bp_name,
                       (SELECT b.bp_id
                          FROM con_contract_bp b
                         WHERE b.bp_category = 'TENANT'
                               AND b.contract_id = t.contract_id) bp_id,
                       (SELECT code_value_name
                          FROM sys_code_values_v
                         WHERE code = 'CSH513_WRITE_OFF_TYPE'
                               AND code_value = t.write_off_type) write_off_type_dis,
                       decode(nvl(t.csh_write_off_amount,0),0,'0.00',to_char(t.csh_write_off_amount,'FM999,999,999.00'))write_off_due_amount,
                       to_char(t.write_off_date,'yyyy-mm-dd')write_off_date,
                       t.times lease_times,
                       t.description
                  FROM csh_write_off t
                 WHERE t.write_off_type = 'DEPOSIT'
                 and t.reversed_flag = 'N'
                 and t.csh_transaction_id in (select ct.transaction_id from csh_transaction ct where ct.bp_id=${@bp_id} and ct.transaction_type = 'DEDUCTION_DEPOSIT')
                 order by write_off_date desc
				         ) t1 #WHERE_CLAUSE#
    		]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields><![CDATA[
    ]]></bm:query-fields>
</bm:model>
