<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: ZLF  
    $Date: 2015-1-15 下午1:23:22  
    $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,
               (select bp.bp_name from hls_bp_master bp where bp.bp_id=c.license_provider_id) license_provider_name,
               (select nvl(ds.creation_date,'') from yonda_doc_status_history ds where ds.document_category='CONTRACT' and ds.document_id=c.contract_id and ds.status='220' and rownum=1) pay_promise_date,
               (select nvl(min(l.creation_date),'') from ast_car_license l where l.item_detail_id=cid.item_detail_id and rownum=1 )first_license_date
               
               
        
          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="install_date" dataType="java.util.Date" queryExpression="t1.install_date = ${@install_date}"/>
        <bm:query-field name="gps_install_flag" queryExpression="nvl(t1.gps_install_flag,&apos;N&apos;) = ${@gps_install_flag}"/>
        <bm:query-field name="gps_installed_flag" queryExpression="nvl(t1.gps_installed_flag,&apos;N&apos;) = ${@gps_installed_flag}"/>
    </bm:query-fields>
</bm:model>
