<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: ZLF  
    $Date: 2015-1-15 下午8:46:58  
    $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 * from (
            select c.contract_id,
       c.contract_number,
       c.bp_id_tenant,
       (select h.bp_name from hls_bp_master h where h.bp_id = c.bp_id_tenant) bp_name,
       cli.brand_id,
       (select bv.description
          from hls_car_brands_vl bv
         where bv.brand_id = cli.brand_id) brand_des,
       cli.series_id,
       (select sv.description
          from hls_car_series_vl sv
         where sv.series_id = cli.series_id) series_des,
       cli.model_id,
       (select mv.description
          from hls_car_model_vl mv
         where mv.model_id = cli.model_id) model_des,
       cid.item_frame_number,
       c.other_fee,
       c.lease_item_amount,
       c.deposit,
       c.down_payment,
       c.residual_value,
       c.pmt,
       c.lease_times,
       (select count(*)
          from con_contract_cashflow ccc
         where ccc.contract_id = c.contract_id
           and ccc.write_off_flag = 'FULL'
           and cf_item = 1
           and cf_direction = 'INFLOW') received_times,
       nvl((SELECT nvl(SUM(principal), 0) - nvl(sum(received_principal), 0)
             FROM con_contract_cashflow
            WHERE contract_id = c.contract_id
              AND cf_item = 1
              and cf_direction = 'INFLOW'),
           0) remain_principal,
       nvl((SELECT COUNT(*)
             FROM con_contract_cashflow
            WHERE contract_id = c.contract_id
              AND write_off_flag <> 'FULL'
              AND cf_item = 1
              and cf_direction = 'INFLOW'
              and due_date <= sysdate),
           0) overdue_times,
       nvl(c.overdue_max_days, 0) overdue_days,
       (SELECT nvl(SUM(due_amount), 0) - nvl(sum(received_amount), 0)
          FROM con_contract_cashflow
         WHERE contract_id = c.contract_id
           AND cf_item = 9
           and cf_direction = 'INFLOW') penalty

  from con_contract             c,
       con_contract_lease_item  cli,
       con_contract_item_detail cid
 where c.contract_id = cli.contract_id
   and cli.contract_lease_item_id = cid.contract_lease_item_id
   and c.data_class = 'NORMAL') t1 #WHERE_CLAUSE#
   
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="t1.contract_number like ${@contract_number}"/>
        <bm:query-field name="bp_name" queryExpression="t1.bp_name like ${@bp_name}"/>
        <bm:query-field name="overdue_times" queryExpression="t1.overdue_times = ${@overdue_times}"/>
    </bm:query-fields>
</bm:model>
