<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: qwm  
    $Date: 2013-8-15 下午8:09:18  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:s="leaf.plugin.script" xmlns:bm="http://www.leaf-framework.org/schema/bm" defaultOrderBy="v.et_agreement_id desc">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[select * from (select h.et_agreement_number,
		       h.et_agreement_id,  
		       b.bp_id,   
		       b.bp_name,
		       b.bp_code,
		       t.contract_number,
		       t.contract_name,
		       (select project_number from prj_project where project_id = t.project_id)project_number,
		       t.contract_id,
		       t.document_type,
		       to_char(t.inception_of_lease,'yyyy-mm-dd') inception_of_lease,
		       (select hd.document_type_desc from hls_document_type_v hd where hd.document_type = t.document_type) document_type_desc,
		       t.document_category,
		       (select hd.description from hls_document_category hd where hd.document_category = t.document_category) document_category_desc,
		       to_char(h.creation_date,'YYYY-MM-DD') creation_date,
		       p.employee_id,
		       p.project_name,
		       (select u.name from exp_employees u where u.employee_id = p.employee_id) employee_name,
		       t.contract_status,
		       (select v.code_value_name
		          from sys_code_values_v v
		         where v.code = 'CON500_CONTRACT_STATUS'
		           and v.code_value = t.contract_status) as contract_status_desc,
		       to_char(h.termination_date,'YYYY-MM-DD') termination_date,
		       h.status et_agreement_status,
		       (select v.code_value_name
		          from sys_code_values_v v
		         where v.code = 'CON_ET_STATUS'
		           and v.code_value = h.status) as et_agreement_status_desc,
		       h.document_type et_type,
		       h.et_profile,    
		       (select hd.document_type_desc from hls_document_type_v hd where hd.document_type = h.document_type) et_type_desc,
		       (select cp.description from con_contract_et_profile cp where cp.et_profile = h.et_profile)  et_profile_desc,
		       h.ref_n01 et_ratio,
		       t.company_id,
		       t.spv_company_id,
		       (select decode(count(*),0,'N','Y') from con_contract_cashflow f where f.contract_id =t.contract_id and f.overdue_status='Y') overdue_status,
		       (select to_char(due_date,'YYYY-MM-DD') from con_contract tt1,con_contract_cashflow tt2 where tt1.contract_id = tt2.contract_id and t.contract_id = tt1.contract_id and tt2.cf_item = 1 and tt2.cf_type = 1 and tt2.times = tt1.lease_times and tt2.cf_direction = 'INFLOW') lease_end_date,
		       (SELECT tt3.license_number FROM con_contract_lease_item  tt1,con_contract_item_detail tt2,ast_car_license tt3 WHERE tt1.contract_id = t.contract_id AND tt1.contract_lease_item_id = tt2.contract_lease_item_id AND tt2.item_detail_id = tt3.item_detail_id AND tt1.enabled_flag = 'Y' AND tt3.enabled_flag = 'Y')license_number,
		       (SELECT a.company_code FROM fnd_companies a WHERE a.company_id = t.spv_company_id) spv_company_code
		  from con_contract_et_hd h, hls_bp_master b, con_contract t, prj_project p
		 where h.contract_id = t.contract_id(+)
		   and t.bp_id_tenant = b.bp_id(+)
		   and t.project_id = p.project_id(+)
		   ) v #WHERE_CLAUSE#
   #ORDER_BY_CLAUSE#]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:features>
        <s:bm-script><![CDATA[
            var cx = Packages.leaf.javascript.Context.getCurrentContext();
            Packages.leaf.plugin.script.engine.ScriptImportor.defineExternScript(cx, this, $ctx.getData(), "aut_authority_bm_validate.js");
        ]]></s:bm-script>
    </bm:features>
    <bm:fields>
        <bm:field name="et_agreement_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="et_agreement_id"/>
        <bm:field name="et_agreement_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="et_agreement_number"/>
        <bm:field name="bp_id" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="bp_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="bp_name"/>
        <bm:field name="bp_code" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="bp_code"/>
        <bm:field name="contract_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="contract_id"/>
        <bm:field name="contract_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="contract_name"/>
        <bm:field name="contract_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="contract_number"/>
        <bm:field name="creation_date" databaseType="DATE" datatype="java.util.Date" physicalName="creation_date"/>
        <bm:field name="employee_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="EMPLOYEE_ID"/>
        <bm:field name="employee_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="employee_name"/>
        <bm:field name="contract_status" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="contract_status"/>
        <bm:field name="termination_date" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="termination_date"/>
        <bm:field name="et_agreement_status" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="et_agreement_status"/>
        <bm:field name="et_agreement_status_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="et_agreement_status_desc"/>
        <bm:field name="et_profile_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="et_profile_desc"/>
        <bm:field name="document_type_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="document_type_desc"/>
        <bm:field name="et_profile" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="et_profile"/>
        <bm:field name="document_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="document_type"/>
        <bm:field name="contract_status_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="contract_status_desc"/>
        <bm:field name="project_name"/>
        <bm:field name="document_category"/>
        <bm:field name="document_category_desc"/>
        <bm:field name="et_type"/>
        <bm:field name="et_type_desc"/>
        <bm:field name="inception_of_lease"/>
        <bm:field name="et_ratio"/>
        <bm:field name="company_id"/>
        <bm:field name="spv_company_id"/>
        <bm:field name="spv_company_code"/>
        <bm:field name="lease_end_date"/>
        <bm:field name="license_number"/>
        <bm:field name="project_number"/>
        <bm:field name="overdue_status"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field field="et_agreement_id" queryOperator="="/>
        <bm:query-field field="et_agreement_number" queryExpression="upper(v.et_agreement_number) like &apos;%&apos; || upper(${@et_agreement_number}) || &apos;%&apos;"/>
        <bm:query-field field="bp_id" queryOperator="="/>
        <bm:query-field name="guarantor_id" queryExpression="exists (select 1 from con_contract_bp where contract_id = v.contract_id and bp_id = ${@guarantor_id} and bp_category = &apos;GUARANTOR&apos;)"/>
        <bm:query-field name="contract_number" queryExpression="v.contract_number like &apos;%&apos;||upper(${@contract_number})||&apos;%&apos;"/>
        <bm:query-field name="contract_name" queryExpression="v.contract_name like &apos;%&apos;||upper(${@contract_name})||&apos;%&apos;"/>
        <bm:query-field name="project_number" queryExpression="v.project_number like &apos;%&apos;||upper(${@project_number})||&apos;%&apos;"/>
        <bm:query-field name="license_number" datatype="java.lang.String" queryexpression="upper(v.license_number) like &apos;%&apos; || upper(${@license_number}) || &apos;%&apos;"/>
        <bm:query-field field="et_agreement_status" queryOperator="="/>
        <bm:query-field name="termination_date_from" queryExpression="v.termination_date &gt;= ${@termination_date_from}"/>
        <bm:query-field name="termination_date_to" queryExpression="v.termination_date &lt;= ${@termination_date_to}"/>
        <bm:query-field name="date_from" datatype="java.lang.String" queryexpression="v.inception_of_lease &gt;= ${@date_from}"/>
        <bm:query-field name="date_to" datatype="java.lang.String" queryexpression="v.inception_of_lease &lt;= ${@date_to}"/>
        <bm:query-field name="lease_end_date_from" datatype="java.lang.String" queryexpression="v.lease_end_date &gt;= ${@lease_end_date_from}"/>
        <bm:query-field name="lease_end_date_to" datatype="java.lang.String" queryexpression="v.lease_end_date &lt;= ${@lease_end_date_to}"/>
        <bm:query-field name="creation_date_from" datatype="java.lang.String" queryexpression="v.creation_date &gt;= ${@creation_date_from}"/>
        <bm:query-field name="creation_date_to" datatype="java.lang.String" queryexpression="v.creation_date &lt;= ${@creation_date_to}"/>
    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter enforceOperations="query" expression="nvl(v.et_agreement_status,&apos;BEFORE_NEW&apos;) not in (&apos;BEFORE_NEW&apos;,&apos;CANCEL&apos;)"/>
        <bm:data-filter enforceOperations="query" expression="v.et_type IN (&apos;ET&apos;)"/>
    </bm:data-filters>
</bm:model>
