<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: gaoyang  
    $Date: 2013-6-24 下午04:30:53  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:s="leaf.plugin.script" xmlns:f="leaf.database.features" xmlns:bm="http://www.leaf-framework.org/schema/bm" alias="t1" baseTable="CON_CONTRACT_V" defaultOrderBy="t1.contract_id desc">
    <bm:fields>
        <bm:field name="contract_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CONTRACT_ID" prompt="CON301.CON_CONTRACT_V.CONTRACT_ID"/>
        <bm:field name="calc_session_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CALC_SESSION_ID" prompt="CON301.CON_CONTRACT_V.CALC_SESSION_ID"/>
        <bm:field name="contract_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_NUMBER" prompt="CON301.CON_CONTRACT_V.CONTRACT_NUMBER"/>
        <bm:field name="contract_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_NAME" prompt="CON301.CON_CONTRACT_V.CONTRACT_NAME"/>
        <bm:field name="business_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BUSINESS_TYPE" prompt="CON301.CON_CONTRACT_V.BUSINESS_TYPE"/>
        <bm:field name="business_type_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BUSINESS_TYPE_DESC" prompt="CON301.CON_CONTRACT_V.BUSINESS_TYPE_DESC"/>
        <bm:field name="project_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="PROJECT_ID" prompt="CON301.CON_CONTRACT_V.PROJECT_ID"/>
        <bm:field name="project_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PROJECT_NUMBER" prompt="CON301.CON_CONTRACT_V.PROJECT_NUMBER"/>
        <bm:field name="project_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PROJECT_NAME" prompt="CON301.CON_CONTRACT_V.PROJECT_NAME"/>
        <bm:field name="company_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="COMPANY_ID" prompt="CON301.CON_CONTRACT_V.COMPANY_ID"/>
        <bm:field name="lease_organization" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="LEASE_ORGANIZATION" prompt="CON301.CON_CONTRACT_V.LEASE_ORGANIZATION"/>
        <bm:field name="lease_organization_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="LEASE_ORGANIZATION_DESC" prompt="CON301.CON_CONTRACT_V.LEASE_ORGANIZATION_DESC"/>
        <bm:field name="lease_channel" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="LEASE_CHANNEL" prompt="CON301.CON_CONTRACT_V.LEASE_CHANNEL"/>
        <bm:field name="lease_channel_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="LEASE_CHANNEL_DESC" prompt="CON301.CON_CONTRACT_V.LEASE_CHANNEL_DESC"/>
        <bm:field name="bp_id_tenant" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BP_ID_TENANT" prompt="CON301.CON_CONTRACT_V.BP_ID_TENANT"/>
        <bm:field name="bp_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_NAME" prompt="CON301.CON_CONTRACT_V.BP_NAME"/>
        <bm:field name="bp_id_agent_level1" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BP_ID_AGENT_LEVEL1" prompt="CON301.CON_CONTRACT_V.BP_ID_AGENT_LEVEL1"/>
        <bm:field name="bp_id_agent_level1_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_ID_AGENT_LEVEL1_N" prompt="CON301.CON_CONTRACT_V.BP_ID_AGENT_LEVEL1_N"/>
        <bm:field name="bp_id_agent_level2" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BP_ID_AGENT_LEVEL2" prompt="CON301.CON_CONTRACT_V.BP_ID_AGENT_LEVEL2"/>
        <bm:field name="bp_id_agent_level3" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BP_ID_AGENT_LEVEL3" prompt="CON301.CON_CONTRACT_V.BP_ID_AGENT_LEVEL3"/>
        <bm:field name="owner_user_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="OWNER_USER_ID" prompt="CON301.CON_CONTRACT_V.OWNER_USER_ID"/>
        <!-- <bm:field name="owner_user_id_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="OWNER_USER_ID_N" prompt="CON301.CON_CONTRACT_V.OWNER_USER_ID_N"/> -->
        <bm:field name="employee_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="EMPLOYEE_ID" prompt="CON301.CON_CONTRACT_V.EMPLOYEE_ID"/>
        <bm:field name="employee_code" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="EMPLOYEE_CODE" prompt="CON301.CON_CONTRACT_V.EMPLOYEE_CODE"/>
        <bm:field name="employee_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="EMPLOYEE_NAME" prompt="CON301.CON_CONTRACT_V.EMPLOYEE_NAME"/>
        <bm:field name="down_payment" databaseType="NUMBER" datatype="java.lang.Double" physicalName="DOWN_PAYMENT" prompt="CON301.CON_CONTRACT_V.DOWN_PAYMENT"/>
        <bm:field name="contract_status" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_STATUS" prompt="CON301.CON_CONTRACT_V.CONTRACT_STATUS"/>
        <bm:field name="contract_status_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_STATUS_DESC" prompt="CON301.CON_CONTRACT_V.CONTRACT_STATUS_DESC"/>
        <bm:field name="user_status_1" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="USER_STATUS_1" prompt="CON301.CON_CONTRACT_V.USER_STATUS_1"/>
        <!-- <bm:field name="user_status_1_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="USER_STATUS_1_DESC" prompt="CON301.CON_CONTRACT_V.USER_STATUS_1_DESC"/> -->
        <bm:field name="user_status_2" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="USER_STATUS_2" prompt="CON301.CON_CONTRACT_V.USER_STATUS_2"/>
        <bm:field name="user_status_3" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="USER_STATUS_3" prompt="CON301.CON_CONTRACT_V.USER_STATUS_3"/>
        <bm:field name="lease_item_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="LEASE_ITEM_AMOUNT" prompt="CON301.CON_CONTRACT_V.LEASE_ITEM_AMOUNT"/>
        <bm:field name="bp_id_vender" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BP_ID_VENDER" prompt="CON301.CON_CONTRACT_V.BP_ID_VENDER"/>
        <bm:field name="bp_vender" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_VENDER" prompt="CON301.CON_CONTRACT_V.BP_VENDER"/>
        <bm:field name="cdd_list_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CDD_LIST_ID" prompt="CON301.CON_CONTRACT_V.CDD_LIST_ID"/>
        <bm:field name="bp_tenant_class" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_TENANT_CLASS" prompt="CON301.CON_CONTRACT_V.BP_TENANT_CLASS"/>
        <bm:field name="bp_tenant_class_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_TENANT_CLASS_N" prompt="CON301.CON_CONTRACT_V.BP_TENANT_CLASS_N"/>
        <bm:field name="division_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DIVISION_DESC" prompt="CON301.CON_CONTRACT_V.DIVISION_DESC"/>
        <bm:field name="data_class" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DATA_CLASS" prompt="CON301.CON_CONTRACT_V.DATA_CLASS"/>
        <bm:field name="contract_file_date" databaseType="DATE" datatype="java.util.Date" physicalName="CONTRACT_FILE_DATE" prompt="CON301.CON_CONTRACT_V.CONTRACT_FILE_DATE"/>
        <bm:field name="finish_flag" datatype="java.lang.String" expression="nvl2(contract_file_date,&apos;Y&apos;,&apos;N&apos;)"/>
        <bm:field name="mortgage_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="MORTGAGE_FLAG"/>
        <bm:field name="license_type_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="LICENSE_TYPE_N"/>
        <bm:field name="signing_date" databaseType="DATE" datatype="java.util.Date" physicalName="SIGNING_DATE" prompt="CON301.CON_CONTRACT_V.SIGNING_DATE"/>
        <bm:field name="red_flag" expression="nvl2(CONTRACT_FILE_DATE,-1,(SIGN(TRUNC(sysdate)-add_months(NVL(t1.signing_date,sysdate),1))))"/>
        <bm:field name="finish_flag_desc"/>
        <bm:field name="item_frame_number"/>
        <bm:field name="spv_company_id"/>
        <bm:field name="spv_company_name"/>
        <bm:field name="unit_name"/>
        <bm:field name="unit_id"/>
        <bm:field name="license_number"/>
        <bm:field name="mortage_date"/>
    </bm:fields>
    <bm:features>
        <f:standard-who/>
        <!--    <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:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
    		select * from (
    			SELECT
    t1.CONTRACT_ID,
    t1.CALC_SESSION_ID,
    t1.CONTRACT_NUMBER,
    t1.CONTRACT_NAME,
    t1.BUSINESS_TYPE,
    t1.UNIT_ID,
    (SELECT unit_name FROM exp_org_unit_v tt WHERE tt.org_unit_type = '4S' and tt.unit_id = t1.unit_id) UNIT_NAME,
    t1.BUSINESS_TYPE_DESC,
    t1.PROJECT_ID,
    t1.PROJECT_NUMBER,
    t1.PROJECT_NAME,
    t1.COMPANY_ID,
    t1.LEASE_ORGANIZATION,
    t1.LEASE_ORGANIZATION_DESC,
    t1.LEASE_CHANNEL,
    t1.LEASE_CHANNEL_DESC,
    t1.BP_ID_TENANT,
    t1.BP_NAME,
    t1.BP_ID_AGENT_LEVEL1,
    t1.BP_ID_AGENT_LEVEL1_N,
    t1.BP_ID_AGENT_LEVEL2,
    t1.BP_ID_AGENT_LEVEL3,
    t1.OWNER_USER_ID,
    t1.EMPLOYEE_ID,
    t1.EMPLOYEE_CODE,
    t1.EMPLOYEE_NAME,
    t1.DOWN_PAYMENT,
    t1.CONTRACT_STATUS,
    t1.CONTRACT_STATUS_DESC,
    t1.USER_STATUS_1,
    t1.USER_STATUS_2,
    t1.USER_STATUS_3,
    t1.LEASE_ITEM_AMOUNT,
    t1.BP_ID_VENDER,
    t1.BP_VENDER,
    t1.CDD_LIST_ID,
    t1.BP_TENANT_CLASS,
    t1.BP_TENANT_CLASS_N,
    t1.DIVISION_DESC,
    t1.DATA_CLASS,
    t1.CONTRACT_FILE_DATE,
    t1.spv_company_id,
    t1.spv_company_name,
    v.license_type,
    v.license_type_n,
    (select cid.item_frame_number from con_contract_item_detail cid where cid.contract_id=t1.contract_id ) item_frame_number,
    nvl2(contract_file_date,'Y','N') AS finish_flag,
    (SELECT a.code_value_name
  FROM sys_code_values_v a
 WHERE a.code = 'FINISH_FLAG'
       AND a.code_enabled_flag = 'Y'
       AND a.code_value_enabled_flag = 'Y'
       AND A.code_value=nvl2(T1.contract_file_date,'Y','N')) finish_flag_desc,
    t1.SIGNING_DATE,
    v.mortgage_flag,
    nvl2(CONTRACT_FILE_DATE,-1,(SIGN(TRUNC(sysdate)-add_months(NVL(t1.signing_date,sysdate),1)))) AS red_flag,
    (select a.license_number from ast_car_license a,con_contract_item_detail b 
                                  where a.item_detail_id=b.item_detail_id and b.contract_id = t1.contract_id
                                  and a.enabled_flag = 'Y'
				  and rownum = 1) license_number,
    (select (case when a.mortgage_date is null then 'N' else 'Y' end) from ast_car_license a,con_contract_item_detail b 
                                  where a.item_detail_id=b.item_detail_id and b.contract_id = t1.contract_id
                                  and a.enabled_flag = 'Y'
          and rownum = 1) mortage_date
FROM
    CON_CONTRACT_V t1,AST_CAR_DISPATCH_V v
    where t1.contract_id=v.contract_id) t1 #WHERE_CLAUSE#
    		]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="emp_id" queryExpression="exists (select 1 from yonda_4s_area_director where employee_id = ${@emp_id} and unit_id = t1.unit_id and enable_flag = &apos;Y&apos;)"/>
        <bm:query-field name="is_shanghai_flag" queryExpression="((${@is_shanghai_flag} = &apos;Y&apos; and exists (select 1 from fnd_companies where company_code = &apos;1000&apos; and company_id = spv_company_id)) or (${@is_shanghai_flag} = &apos;N&apos; and exists (select 1 from fnd_companies where company_code != &apos;1000&apos; and company_id = spv_company_id)))"/>
        <bm:query-field field="unit_id" queryOperator="="/>
        <bm:query-field field="spv_company_id" queryOperator="="/>
        <!-- <bm:query-field field="spv_company_name" queryOperator="like"/> -->
        <bm:query-field name="license_type" queryExpression="t1.license_type = ${@license_type}"/>
        <bm:query-field name="bp_id_gua" queryExpression="exists (select 1 from con_contract_bp cbp where cbp.bp_id = ${@bp_id_gua} and cbp.bp_category=&apos;GUARANTOR&apos; and cbp.contract_id = t1.contract_id)"/>
        <bm:query-field field="contract_number" queryExpression="contract_number like upper(&apos;%&apos;||${@contract_number}||&apos;%&apos;)"/>
        <bm:query-field field="contract_name" queryExpression="contract_name like &apos;%&apos;||${@contract_name}||&apos;%&apos;"/>
        <bm:query-field field="item_frame_number" queryExpression="upper(item_frame_number) like &apos;%&apos; || upper(${@item_frame_number}) || &apos;%&apos;"/>
        <bm:query-field field="project_number" queryExpression="project_number like upper(&apos;%&apos;||${@project_number}||&apos;%&apos;)"/>
        <bm:query-field field="bp_id_tenant" queryOperator="="/>
        <bm:query-field name="lease_item_amount_from" queryExpression="lease_item_amount &gt;= ${@lease_item_amount_from}"/>
        <bm:query-field name="lease_item_amount_to" queryExpression="lease_item_amount &lt;= ${@lease_item_amount_to}"/>
        <bm:query-field field="lease_channel" queryOperator="="/>
        <bm:query-field field="business_type" queryOperator="="/>
        <bm:query-field field="lease_organization" queryOperator="="/>
        <bm:query-field field="contract_status" queryOperator="="/>
        <bm:query-field field="user_status_1" queryOperator="="/>
        <bm:query-field field="bp_id_agent_level1" queryOperator="="/>
        <!-- <bm:query-field field="contract_file_date" queryExpression="to_char(contract_file_date,&apos;yyyy-mm-dd&apos;) = to_char(${@contract_file_date},&apos;yyyy-mm-dd&apos;)"/> -->
        <bm:query-field name="contract_file_date_from" queryExpression="to_char(contract_file_date,&apos;yyyy-mm-dd&apos;) &gt;= ${@contract_file_date_from}"/>
        <bm:query-field name="contract_file_date_to" queryExpression="to_char(contract_file_date,&apos;yyyy-mm-dd&apos;) &lt;= ${@contract_file_date_to}"/>
        <bm:query-field field="finish_flag" queryExpression="${@finish_flag} =nvl2(contract_file_date,&apos;Y&apos;,&apos;N&apos;)"/>
        <bm:query-field field="project_number" queryExpression="t1.project_number like upper(&apos;%&apos;||${@project_number}||&apos;%&apos;)"/>
        <!--         <bm:query-field name="company_authority" queryExpression="t1.spv_company_id in (select company_id from fnd_companies start with company_id =${@company_authority} connect by prior company_id = parent_biz_company_id)"/>
 -->
        <bm:query-field field="license_number" queryExpression="${@license_number} =license_number"/>
    </bm:query-fields>
    <bm:data-filters>
        <!-- <bm:data-filter enforceOperations="query" expression="t1.spv_company_id = ${/session/@company_id}"/> -->
        <bm:data-filter enforceOperations="query" expression="t1.data_class = &apos;NORMAL&apos; and t1.contract_status=&apos;INCEPT&apos;"/>
        <!-- <bm:data-filter enforceOperations="query" expression="t.mortgage_flag=&apos;Y&apos;"/> -->
        <!-- <bm:data-filter enforceOperations="query" expression="t.contract_status = &apos;INCEPT&apos;"/> -->
        <!--<bm:data-filter enforceOperations="query" expression="t.created_by = ${/session/@user_id}" field="created_by"/>-->
    </bm:data-filters>
</bm:model>
