<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: zhangxing5129  
    $Date: 2013-9-6 下午03:29:09  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            select a.*, (a.total_rental - a.total_received) total_balance
  from (select ct.contract_id,
               ct.contract_number,
               ct.document_category,
               (select dc.description
                  from hls_document_category dc
                 where dc.document_category = ct.document_category) document_category_desc,
               ct.document_type,
               (select dt.document_type_desc
                  from hls_document_type_v dt
                 where dt.document_type = ct.document_type) document_type_desc,
               ct.bp_id_tenant,
               ct.lease_start_date,
               (select bpv.bp_name
                  from hls_bp_master_v bpv
                 where bpv.bp_id = ct.bp_id_tenant) bp_name,
               
               nvl(ct.total_rental, 0) total_rental,
               
               nvl((select sum(nvl(cf.received_amount, 0))
                     from con_contract_cashflow cf
                    where cf.contract_id = ct.contract_id
                      and cf.cf_type = 1
                      and cf.cf_item = 1
                      and cf.cf_status in ('BLOCK', 'RELEASE')),
                   0) total_received,
               
               nvl((select sum(nvl(cf2.overdue_amount, 0))
                     from con_contract_cashflow cf2
                    where cf2.overdue_status = 'Y'
                      and cf2.contract_id = ct.contract_id
                      and cf2.cf_item = 1
                      and cf2.cf_type = 1
                      and cf2.cf_status in ('BLOCK', 'RELEASE')),
                   0) overdue_amount,
               
               nvl((select sum(nvl(cf3.due_amount, 0))
                     from con_contract_cashflow cf3
                    where cf3.cf_item = 9
                      and cf3.cf_type = 9
                      and cf3.contract_id = ct.contract_id),
                   0) received_fine_amount,
               
               ct.contract_status
          from con_contract ct
         where ct.contract_status = 'INCEPT'
           and ct.contract_number = ${@contract_number}
           and ct.company_id = ${/session/@company_id}) a
]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="contract_number"/>
        <bm:field name="document_category"/>
        <bm:field name="document_type"/>
        <bm:field name="document_category_desc"/>
        <bm:field name="document_type_desc"/>
        <bm:field name="contract_status"/>
        <bm:field name="contract_id"/>
        <bm:field name="bp_id_tenant"/>
        <bm:field name="lease_start_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="bp_name"/>
        <bm:field name="total_received"/>
        <bm:field name="overdue_amount"/>
        <bm:field name="total_rental"/>
        <bm:field name="total_balance"/>
    </bm:fields>
</bm:model>
