<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: zhangxing5129  
    $Date: 2013-9-9 下午02:50:38  
    $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.received_amount balance_amount  from 
(select con.bp_id_tenant,
       (select bp.bp_name
          from hls_bp_master bp
         where bp.bp_id = con.bp_id_tenant) bp_name,
       con.document_category,
       (select dc.description
          from hls_document_category dc
         where dc.document_category = con.document_category) document_category_desc,
       con.document_type,
       (select dt.description
          from hls_document_type dt
         where dt.document_type = con.document_type) document_type_desc,
       con.contract_number,
       nvl(con.total_rental, 0) total_rental,
       (select sum(nvl(cf.received_amount, 0))
          from con_contract_cashflow cf
         where cf.contract_id = con.contract_id
           and cf.cf_item = 1
           and cf.cf_type = 1) received_amount,
       (select sum(nvl(cf2.received_amount, 0))
          from con_contract_cashflow cf2
         where cf2.contract_id = con.contract_id
           and cf2.cf_item = 9
           and cf2.cf_type = 9) received_fine,
       
       (select sum(nvl(cf3.due_amount, 0))
          from con_contract_cashflow cf3
         where cf3.contract_id = con.contract_id
           and cf3.cf_item = 9
           and cf3.cf_type = 9) overdue_amount

  from con_contract con
 where con.contract_number = ${@contract_number}
   and con.company_id = ${/session/@company_id}) a
]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
