<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: wangwei  
    $Date: 2015-1-15 下午4:41:14  
    $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 t1.* from (
  SELECT t.contract_id,
       (SELECT b.bp_code
          FROM con_contract_bp a,
               hls_bp_master   b
         WHERE a.contract_id = t.contract_id
               AND a.bp_id = b.bp_id
               AND a.bp_category = 'TENANT') tenant_code,
       t.contract_number,
       (SELECT b.bp_name
          FROM con_contract_bp a,
               hls_bp_master   b
         WHERE a.contract_id = t.contract_id
               AND a.bp_id = b.bp_id
               AND a.bp_category = 'TENANT') tenant_name,
       (select a.company_short_name from fnd_companies a where a.company_id=t.spv_company_id) spv_company, 
       (select us.description from yonda_doc_status_history_v his,sys_user us where his.document_category='CONTRACT'
        and his.document_id=t.contract_id and his.status='430' and his.user_id=us.user_id 
        and his.status_history_id=(select max(hi.status_history_id)  from
        yonda_doc_status_history_v hi where hi.document_category='CONTRACT'
        and hi.document_id=t.contract_id and his.status='430'  )) acr_user_name, 
        (select emp.mobil from yonda_doc_status_history_v his,sys_user us,exp_employees emp where his.document_category='CONTRACT'
        and his.document_id=t.contract_id and his.status='430' and his.user_id=us.user_id
        and us.employee_id=emp.employee_id and his.status_history_id=
        (select max(hi.status_history_id)  from
        yonda_doc_status_history_v hi where hi.document_category='CONTRACT'
        and hi.document_id=t.contract_id and his.status='430'  ) )  acr_user_mobil,    
       (SELECT b.organization_code
          FROM con_contract_bp a,
               hls_bp_master   b
         WHERE a.contract_id = t.contract_id
               AND a.bp_id = b.bp_id
               AND a.bp_category = 'TENANT'
               AND b.bp_class = 'ORG') tenant_organization_code,
       (SELECT b.id_card_no
          FROM con_contract_bp a,
               hls_bp_master   b
         WHERE a.contract_id = t.contract_id
               AND a.bp_id = b.bp_id
               AND a.bp_category = 'TENANT'
               AND b.bp_class = 'NP') tenant_id_card_no,
       (SELECT decode(b.bp_class,
                      'NP',
                      b.bp_name,
                      'ORG',
                      b.first_name)
          FROM con_contract_bp a,
               hls_bp_master   b
         WHERE a.contract_id = t.contract_id
               AND a.bp_id = b.bp_id
               AND a.bp_category = 'TENANT') teant_contact_person,
       (SELECT decode(b.bp_class,
                      'NP',
                      b.cell_phone,
                      'ORG',
                      b.phone_2)
          FROM con_contract_bp a,
               hls_bp_master   b
         WHERE a.contract_id = t.contract_id
               AND a.bp_id = b.bp_id
               AND a.bp_category = 'TENANT') teant_contact_phone,
       (SELECT decode(b.bp_class,
                      'NP',
                      b.living_address,
                      'ORG',
                      b.living_address)
          FROM con_contract_bp a,
               hls_bp_master   b
         WHERE a.contract_id = t.contract_id
               AND a.bp_id = b.bp_id
               AND a.bp_category = 'TENANT') teant_contact_address,
       (SELECT b.bp_name
          FROM con_contract_bp a,
               hls_bp_master   b
         WHERE a.contract_id = t.contract_id
               AND a.bp_id = b.bp_id
               AND a.bp_category = 'TENANT_SEC'
               AND rownum = 1) tenant_sec_name,
       (SELECT b.organization_code
          FROM con_contract_bp a,
               hls_bp_master   b
         WHERE a.contract_id = t.contract_id
               AND a.bp_id = b.bp_id
               AND a.bp_category = 'TENANT_SEC'
               AND b.bp_class = 'ORG'
               AND rownum = 1) tenant_sec_org_code,
       (SELECT b.id_card_no
          FROM con_contract_bp a,
               hls_bp_master   b
         WHERE a.contract_id = t.contract_id
               AND a.bp_id = b.bp_id
               AND a.bp_category = 'TENANT_SEC'
               AND b.bp_class = 'NP'
               AND rownum = 1) tenant_sec_id_card_no,
       
       (SELECT decode(b.bp_class,
                      'NP',
                      b.bp_name,
                      'ORG',
                      b.first_name)
          FROM con_contract_bp a,
               hls_bp_master   b
         WHERE a.contract_id = t.contract_id
               AND a.bp_id = b.bp_id
               AND a.bp_category = 'TENANT_SEC'
               AND rownum = 1) teant_sec_contact_person,
       (SELECT decode(b.bp_class,
                      'NP',
                      b.cell_phone,
                      'ORG',
                      b.phone_2)
          FROM con_contract_bp a,
               hls_bp_master   b
         WHERE a.contract_id = t.contract_id
               AND a.bp_id = b.bp_id
               AND a.bp_category = 'TENANT_SEC'
               AND rownum = 1) teant_sec_contact_phone,
       (SELECT decode(b.bp_class,
                      'NP',
                      b.living_address,
                      'ORG',
                      b.living_address)
          FROM con_contract_bp a,
               hls_bp_master   b
         WHERE a.contract_id = t.contract_id
               AND a.bp_id = b.bp_id
               AND a.bp_category = 'TENANT_SEC'
               AND rownum = 1) teant_sec_contact_address,
       (SELECT b.bp_name
          FROM con_contract_bp a,
               hls_bp_master   b,
               prj_project_bp  c
         WHERE a.bp_id = b.bp_id
               AND a.contract_id = t.contract_id
               AND a.bp_category = 'GUARANTOR'
               AND c.bp_id = b.bp_id
               AND c.project_id = t.project_id
               AND c.bp_seq = 1) guar_name_1,
       (SELECT b.organization_code
          FROM con_contract_bp a,
               hls_bp_master   b,
               prj_project_bp  c
         WHERE a.bp_id = b.bp_id
               AND a.contract_id = t.contract_id
               AND a.bp_category = 'GUARANTOR'
               AND c.bp_id = b.bp_id
               AND c.project_id = t.project_id
               AND c.bp_seq = 1) guar_organization_code_1,
       (SELECT b.id_card_no
          FROM con_contract_bp a,
               hls_bp_master   b,
               prj_project_bp  c
         WHERE a.bp_id = b.bp_id
               AND a.contract_id = t.contract_id
               AND a.bp_category = 'GUARANTOR'
               AND c.bp_id = b.bp_id
               AND c.project_id = t.project_id
               AND c.bp_seq = 1) guar_id_card_no_1,
       (SELECT decode(b.bp_class,
                      'NP',
                      b.bp_name,
                      'ORG',
                      b.first_name)
          FROM con_contract_bp a,
               hls_bp_master   b,
               prj_project_bp  c
         WHERE a.bp_id = b.bp_id
               AND a.contract_id = t.contract_id
               AND a.bp_category = 'GUARANTOR'
               AND c.bp_id = b.bp_id
               AND c.project_id = t.project_id
               AND c.bp_seq = 1) guar_contact_name_1,
       (SELECT decode(b.bp_class,
                      'NP',
                      b.cell_phone,
                      'ORG',
                      b.phone_2)
          FROM con_contract_bp a,
               hls_bp_master   b,
               prj_project_bp  c
         WHERE a.bp_id = b.bp_id
               AND a.contract_id = t.contract_id
               AND a.bp_category = 'GUARANTOR'
               AND c.bp_id = b.bp_id
               AND c.project_id = t.project_id
               AND c.bp_seq = 1) guar_contact_phone_1,
       (SELECT decode(b.BP_CLASS,
                      'NP',
                      b.living_address,
                      'ORG',
                      b.living_address)
          FROM con_contract_bp a,
               hls_bp_master   b,
               prj_project_bp  c
         WHERE a.bp_id = b.bp_id
               AND a.contract_id = t.contract_id
               AND a.bp_category = 'GUARANTOR'
               AND c.bp_id = b.bp_id
               AND c.project_id = t.project_id
               AND c.bp_seq = 1) guar_contact_address_1,
       (SELECT b.bp_name
          FROM con_contract_bp a,
               hls_bp_master   b,
               prj_project_bp  c
         WHERE a.bp_id = b.bp_id
               AND a.contract_id = t.contract_id
               AND a.bp_category = 'GUARANTOR'
               AND c.bp_id = b.bp_id
               AND c.project_id = t.project_id
               AND c.bp_seq = 2) guar_name_2,
       (SELECT b.organization_code
          FROM con_contract_bp a,
               hls_bp_master   b,
               prj_project_bp  c
         WHERE a.bp_id = b.bp_id
               AND a.contract_id = t.contract_id
               AND a.bp_category = 'GUARANTOR'
               AND c.bp_id = b.bp_id
               AND c.project_id = t.project_id
               AND c.bp_seq = 2) guar_organization_code_2,
       (SELECT b.id_card_no
          FROM con_contract_bp a,
               hls_bp_master   b,
               prj_project_bp  c
         WHERE a.bp_id = b.bp_id
               AND a.contract_id = t.contract_id
               AND a.bp_category = 'GUARANTOR'
               AND c.bp_id = b.bp_id
               AND c.project_id = t.project_id
               AND c.bp_seq = 2) guar_id_card_no_2,
       (SELECT decode(b.bp_class,
                      'NP',
                      b.bp_name,
                      'ORG',
                      b.first_name)
          FROM con_contract_bp a,
               hls_bp_master   b,
               prj_project_bp  c
         WHERE a.bp_id = b.bp_id
               AND a.contract_id = t.contract_id
               AND a.bp_category = 'GUARANTOR'
               AND c.bp_id = b.bp_id
               AND c.project_id = t.project_id
               AND c.bp_seq = 2) guar_contact_name_2,
       (SELECT decode(b.bp_class,
                      'NP',
                      b.cell_phone,
                      'ORG',
                      b.phone_2)
          FROM con_contract_bp a,
               hls_bp_master   b,
               prj_project_bp  c
         WHERE a.bp_id = b.bp_id
               AND a.contract_id = t.contract_id
               AND a.bp_category = 'GUARANTOR'
               AND c.bp_id = b.bp_id
               AND c.project_id = t.project_id
               AND c.bp_seq = 2) guar_contact_phone_2,
       (SELECT decode(b.BP_CLASS,
                      'NP',
                      b.LIVING_ADDRESS,
                      'ORG',
                      b.LIVING_ADDRESS)
          FROM con_contract_bp a,
               hls_bp_master   b,
               prj_project_bp  c
         WHERE a.bp_id = b.bp_id
               AND a.contract_id = t.contract_id
               AND a.bp_category = 'GUARANTOR'
               AND c.bp_id = b.bp_id
               AND c.project_id = t.project_id
               AND c.bp_seq = 2) guar_contact_address_2,
       (SELECT b.bp_name
          FROM con_contract_bp a,
               hls_bp_master   b,
               prj_project_bp  c
         WHERE a.bp_id = b.bp_id
               AND a.contract_id = t.contract_id
               AND a.bp_category = 'GUARANTOR'
               AND c.bp_id = b.bp_id
               AND c.project_id = t.project_id
               AND c.bp_seq = 3) guar_name_3,
       (SELECT b.organization_code
          FROM con_contract_bp a,
               hls_bp_master   b,
               prj_project_bp  c
         WHERE a.bp_id = b.bp_id
               AND a.contract_id = t.contract_id
               AND a.bp_category = 'GUARANTOR'
               AND c.bp_id = b.bp_id
               AND c.project_id = t.project_id
               AND c.bp_seq = 3) guar_organization_code_3,
       (SELECT b.id_card_no
          FROM con_contract_bp a,
               hls_bp_master   b,
               prj_project_bp  c
         WHERE a.bp_id = b.bp_id
               AND a.contract_id = t.contract_id
               AND a.bp_category = 'GUARANTOR'
               AND c.bp_id = b.bp_id
               AND c.project_id = t.project_id
               AND c.bp_seq = 3) guar_id_card_no_3,
       (SELECT decode(b.bp_class,
                      'NP',
                      b.bp_name,
                      'ORG',
                      b.first_name)
          FROM con_contract_bp a,
               hls_bp_master   b,
               prj_project_bp  c
         WHERE a.bp_id = b.bp_id
               AND a.contract_id = t.contract_id
               AND a.bp_category = 'GUARANTOR'
               AND c.bp_id = b.bp_id
               AND c.project_id = t.project_id
               AND c.bp_seq = 3) guar_contact_name_3,
       (SELECT decode(b.bp_class,
                      'NP',
                      b.cell_phone,
                      'ORG',
                      b.phone_2)
          FROM con_contract_bp a,
               hls_bp_master   b,
               prj_project_bp  c
         WHERE a.bp_id = b.bp_id
               AND a.contract_id = t.contract_id
               AND a.bp_category = 'GUARANTOR'
               AND c.bp_id = b.bp_id
               AND c.project_id = t.project_id
               AND c.bp_seq = 3) guar_contact_phone_3,
       (SELECT decode(b.BP_CLASS,
                      'NP',
                      b.LIVING_ADDRESS,
                      'ORG',
                      b.LIVING_ADDRESS)
          FROM con_contract_bp a,
               hls_bp_master   b,
               prj_project_bp  c
         WHERE a.bp_id = b.bp_id
               AND a.contract_id = t.contract_id
               AND a.bp_category = 'GUARANTOR'
               AND c.bp_id = b.bp_id
               AND c.project_id = t.project_id
               AND c.bp_seq = 3) guar_contact_address_3,
       t1.unit_id,
       t1.unit_id_des,
       t1.owner_user_id,
       t1.owner_user_id_des,
       (SELECT expp.mobil
          FROM exp_employees expp,
               sys_user      suser
         WHERE expp.employee_id = suser.employee_id
               AND suser.user_id = t1.owner_user_id) owner_user_id_mobil,
       t1.sale_consultant,
       (SELECT a.sale_consultant_tel FROM prj_project a WHERE a.project_id = t.project_id) sale_consultant_tel,
       t.division,
       (SELECT div.description FROM hls_division div WHERE div.division = t.division) division_des,
       t.business_type,
       (SELECT bus.description FROM hls_business_type bus WHERE bus.business_type = t.business_type) business_type_des,
       t1.price_list,
       t1.price_list_des,
       t1.brand_id_des,
       t1.series_id_des,
       t1.model_id_des,
       t1.item_frame_number,
       (SELECT detail.item_engine_number
          FROM con_contract_lease_item  item,
               con_contract_item_detail detail
         WHERE item.contract_lease_item_id = detail.contract_lease_item_id
               AND item.contract_id = t1.contract_id) item_engine_number,
       (SELECT decode(prj_l.plate_resource,
                      'LEASING_LICENCE',
                      '√'|| nvl2( prj_l.NONLOCAL_PLATE_DESC,'('||prj_l.NONLOCAL_PLATE_DESC||')',''))
          FROM prj_project_lease_item prj_l
         WHERE prj_l.project_id = t1.project_id) plate_resource_1,
       (SELECT decode(prj_l.plate_resource,
                      'YONDA_LICENCE',
                      '√'||nvl2( prj_l.NONLOCAL_PLATE_DESC,'('||prj_l.NONLOCAL_PLATE_DESC||')',''))
          FROM prj_project_lease_item prj_l
         WHERE prj_l.project_id = t1.project_id) plate_resource_2,
       (SELECT decode(prj_l.plate_resource,
                      'YONDA_OTHER_LICENCE',
                      '√'|| nvl2( prj_l.NONLOCAL_PLATE_DESC,'('||prj_l.NONLOCAL_PLATE_DESC||')',''))
          FROM prj_project_lease_item prj_l
         WHERE prj_l.project_id = t1.project_id) plate_resource_3,
       (SELECT decode(prj_l.plate_resource,
                      'OWN_LICENCE',
                      '√'||nvl2( prj_l.NONLOCAL_PLATE_DESC,'('||prj_l.NONLOCAL_PLATE_DESC||')','') ,
                      'OWN_SH_LICENCE',
                      '√'||nvl2( prj_l.NONLOCAL_PLATE_DESC,'('||prj_l.NONLOCAL_PLATE_DESC||')','') )
          FROM prj_project_lease_item prj_l
         WHERE prj_l.project_id = t1.project_id) plate_resource_4,
       (SELECT ast.license_number
          FROM con_contract_lease_item  item,
               con_contract_item_detail detail,
               ast_car_license          ast
         WHERE item.contract_lease_item_id = detail.contract_lease_item_id
               AND item.contract_id = t1.contract_id
               AND ast.item_detail_id = detail.item_detail_id
               AND ast.enabled_flag = 'Y') license_number,
       t1.other_fee,
       t1.other_fee3,
       t1.insurance_fee,
       t1.hd_user_col_n05,
       t1.pmt,
       t.lease_times,
       (SELECT to_char(cash.due_date,
                       'dd')
          FROM con_contract_cashflow cash
         WHERE cash.contract_id = t.contract_id
               AND cash.cf_item = 1
               AND cash.cf_type = 1
               AND rownum = 1) due_date,
       t.inception_of_lease,
       t.lease_end_date,
       t.contract_file_date,
       nvl2(t.contract_file_date,
            'Y',
            'N') finish_flag,
       nvl2(t.contract_file_date,
            '是',
            '否') finish_flag_desc
  FROM con_contract       t,
       con_contract_all_v t1
 WHERE t.data_class = 'NORMAL'
       AND t.contract_id = t1.contract_id
) 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="tenant_name" queryExpression="t1.tenant_name like ${@tenant_name}"/>
        <bm:query-field name="organization_code" queryExpression="t1.tenant_organization_code like ${@organization_code}"/>
        <bm:query-field name="id_card_no" queryExpression="t1.tenant_id_card_no like ${@id_card_no}"/>
        <bm:query-field name="finish_flag" queryExpression="t1.finish_flag = ${@finish_flag}"/>
        <bm:query-field name="acr_user_name" queryExpression="t1.acr_user_name like ${@acr_user_name}"/>
        <bm:query-field name="contract_file_date_from" queryExpression="t1.contract_file_date&gt; = trunc(to_date(${@contract_file_date_from},&apos;yyyy-mm-dd&apos;))"/>
        <bm:query-field name="contract_file_date_to" queryExpression="trunc(t1.contract_file_date) &lt;= to_date(${@contract_file_date_to},&apos;yyyy-mm-dd&apos;)"/>
    </bm:query-fields>
</bm:model>
