<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: dingzhichao2855 $
    $Date: 2011/04/15 11:45:05 $
    $Revision: 1.1 $
    $Purpose: 租赁申请增删改查 
-->
<bm:model xmlns:ns2="leaf.database.local.oracle" xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features" alias="t1" baseTable="HLS_PRJ_LEASE_REQUISITION" defaultorderby="lease_req_number  desc">
    <bm:fields>
        <bm:field name="lease_requisition_id" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="lease_req_type_id" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="lease_req_number" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="requisition_status" databaseType="VARCHAR2" datatype="java.lang.String" lookupCode="HLS_LEASE_REQUISITION_STAUTS" lookupField="requisition_status_display"/>
        <bm:field name="requisition_status_display" datatype="java.lang.String" forInsert="false" forUpdate="false"/>
        <bm:field name="customer_id" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="guarantor_id" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="dealer_id" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="province_id" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="province_name" datatype="java.lang.String" expression="(select description_text from hls_fnd_province t2,fnd_descriptions fn where t1.province_id=t2.province_id and t2.province_name_id=fn.description_id and fn.language = ${/session/@lang})" forInsert="false" forUpdate="false"/>
        <bm:field name="city_id" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="city_name" datatype="java.lang.String" expression="(select description_text from hls_fnd_city t2,fnd_descriptions fn where t1.city_id=t2.city_id and t2.city_name_id=fn.description_id and fn.language = ${/session/@lang})" forInsert="false" forUpdate="false" prompt=""/>
        <bm:field name="region_id" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="region_name" datatype="java.lang.String" expression="(select description_text from hls_fnd_region t2,fnd_descriptions fn where t1.region_id=t2.region_id and t2.region_name_id=fn.description_id and fn.language = ${/session/@lang})" forInsert="false" forUpdate="false" prompt=""/>
        <bm:field name="address" databaseType="VARCHAR2" datatype="java.lang.String" prompt=""/>
        <bm:field name="pattern_id" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="machine_number" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="sale_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="downpay_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="poundage_flag" databaseType="VARCHAR2" datatype="java.lang.String" lookupCode="HLS_POUNDAGE_FLAG" lookupField="poundage_flag_disp"/>
        <bm:field name="poundage_flag_disp" datatype="java.lang.String" forInsert="false" forUpdate="false"/>
        <bm:field name="deposit_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="lease_start_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="payment_start_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="periods_count" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="payment_type" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="fixed_flag" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="contract_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="stocking_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="manage_fee" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="premium_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="machine_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="interest_rate" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="interest_rate_disp" datatype="java.lang.Double" expression="(t1.interest_rate*100)" forInsert="false" forUpdate="false"/>
        <bm:field name="std_interest_rate" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="mach_saleprofit" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="lease_amt_sum" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="interest_amt_sum" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="commission_sum" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="business_tax_sum" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="submit_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="recycling_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="currency" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="poundage_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="downpay_rate" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="downpay_rate_disp" datatype="java.lang.Double" expression="(t1.downpay_rate*100)" forInsert="false" forUpdate="false"/>
        <bm:field name="customer_name" datatype="java.lang.String" expression="(select customer_name from hls_prj_customer cus where cus.customer_id=t1.customer_id)" forInsert="false" forUpdate="false"/>
        <bm:field name="customer_type" datatype="java.lang.String" expression="(select customer_type from hls_prj_customer cus where cus.customer_id=t1.customer_id)" forInsert="false" forUpdate="false"/>
        <bm:field name="dealer_name" datatype="java.lang.String" expression="(select description_text from fnd_descriptions fn where r2.dealer_name_id=fn.description_id and fn.language = ${/session/@lang})" forInsert="false" forUpdate="false"/>
        <bm:field name="guarantor_name" datatype="java.lang.String" expression="(select guarantor_name from HLS_PRJ_GUARANTOR_V gu where gu.guarantor_id=t1.guarantor_id)" forInsert="false" forUpdate="false" prompt=""/>
        <bm:field name="guarantor_type" datatype="java.lang.String" expression="(select guarantor_type from HLS_PRJ_GUARANTOR gu where gu.guarantor_id=t1.guarantor_id)" forInsert="false" forUpdate="false" prompt=""/>
        <bm:field name="lease_req_type" datatype="java.lang.String" expression="(select description from hls_prj_lease_req_type_vl  tp  where tp.lease_req_type_id=t1.lease_req_type_id)" forInsert="false" forUpdate="false"/>
        <bm:field name="company_id" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="eva_score_total" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="evaluate_level" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="evaluate_desc" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="payment_method" databaseType="VARCHAR2" datatype="java.lang.String" lookupCode="HLS_PRJ_PAMENTY_METHOD" lookupField="payment_method_disp" prompt=""/>
        <bm:field name="payment_method_disp" forInsert="false" forUpdate="false"/>
        <bm:field name="device_province_id" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="device_province_name" datatype="java.lang.String" expression="(select description_text from hls_fnd_province t2,fnd_descriptions fn where t1.device_province_id=t2.province_id and t2.province_name_id=fn.description_id and fn.language = ${/session/@lang})" forInsert="false" forUpdate="false" prompt=""/>
        <bm:field name="device_city_id" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="device_city_name" datatype="java.lang.String" expression="(select description_text from hls_fnd_city t2,fnd_descriptions fn where t1.device_city_id=t2.city_id and t2.city_name_id=fn.description_id and fn.language = ${/session/@lang})" forInsert="false" forUpdate="false" prompt=""/>
        <bm:field name="device_region_id" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="device_region_name" datatype="java.lang.String" expression="(select description_text from hls_fnd_region t2,fnd_descriptions fn where t1.device_region_id=t2.region_id and t2.region_name_id=fn.description_id and fn.language = ${/session/@lang})" forInsert="false" forUpdate="false" prompt=""/>
        <bm:field name="device_address" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="contract_number" datatype="java.lang.String" expression="(select contract_number from hls_prj_contract_hds ch where t1.lease_requisition_id=ch.lease_requisition_id)" forInsert="false" forUpdate="false"/>
        <bm:field name="currency_name" datatype="java.lang.String" expression="(select currency_name from gld_currency_vl gc,hls_fnd_dealer_company dc where dc.dealer_id=t1.dealer_id and dc.company_id=${/session/@company_id} and t1.currency=dc.currency_code and gc.currency_code=dc.currency_code)" forInsert="false" forUpdate="false"/>
        <bm:field name="vender_id" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="vender_name" datatype="java.lang.String" expression="(select description_text from hls_fnd_venders t2,fnd_descriptions fn where t1.vender_id=t2.vender_id and t2.vender_name_id=fn.description_id and fn.language = ${/session/@lang})" forInsert="false" forUpdate="false"/>
        <bm:field name="relation_type" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="relation_others_desc" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="detention_amount" databaseType="NUMBER" datatype="java.lang.Double" prompt="HLS_PRJ_CONTRACT_HDS.DETENTION_AMOUNT"/>
        <bm:field name="audit_comment" datatype="java.lang.String" expression="hls_wfl_pkg.get_wfl_req_audit(&apos;LEASE_REQ_HDS&apos;,t1.lease_requisition_id,${/parameter/@instance_id},${/session/@user_id})" forInsert="false" forUpdate="false"/>
        <bm:field name="document_id" datatype="java.lang.String" expression="t1.lease_requisition_id" forInsert="false" forUpdate="false"/>
        <bm:field name="document_type" datatype="java.lang.String" expression="&apos;LEASE_REQ_HDS&apos;" forInsert="false" forUpdate="false"/>
        <bm:field name="instance_id" datatype="java.lang.String" expression="${/parameter/@instance_id}" forInsert="false" forUpdate="false"/>
        <bm:field name="atm_file" databaseType="VARCHAR2" datatype="java.lang.String" expression="hls_sys_upload_pkg.get_atm_file_href(t1.lease_requisition_id,&apos;hls_prj_lease_requisition&apos;,${/session/@user_id})" forInsert="false" forUpdate="false"/>
        <bm:field name="financing_amount" datatype="java.lang.Double" expression="(t1.sale_amount - t1.downpay_amount)" forInsert="false" forUpdate="false"/>
        <bm:field name="first_submit_date" datatype="java.util.Date" expression="(select min(trunc(v.operation_time)) from hls_ctr_lease_req_handle_v v where v.document_id = t1.lease_requisition_id and v.operation_code = &apos;SUBMITTED&apos;)" forInsert="false" forUpdate="false"/>
        <bm:field name="first_approve_date" datatype="java.util.Date" expression="(select min(trunc(v.operation_time)) from hls_ctr_lease_req_handle_v v where v.document_id = t1.lease_requisition_id and v.operation_code in(&apos;APPROVE&apos;,&apos;APPROVAL_REJECT&apos;,&apos;SUPPLY&apos;))" forInsert="false" forUpdate="false"/>
        <bm:field name="lease_products_id" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="lease_products_code" datatype="java.lang.String" expression="(select lease_products_code from hls_lease_products_vl where lease_products_id = t1.lease_products_id)" forInsert="false" forUpdate="false"/>
        <bm:field name="lease_products_name" datatype="java.lang.String" expression="(select description from hls_lease_products_vl where lease_products_id = t1.lease_products_id)" forInsert="false" forUpdate="false"/>
        <bm:field name="lease_organization_id" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="lease_organization_name" datatype="java.lang.String" expression="( select description from hls_lease_organizations_vl where lease_organizations_id = t1.lease_organization_id)" forInsert="false" forUpdate="false"/>
        <bm:field name="lease_channel_id" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="lease_channel_name" datatype="java.lang.String" expression="( select description from hls_lease_channels_vl where lease_channel_id = t1.lease_channel_id )" forInsert="false" forUpdate="false"/>
        <bm:field name="division_id" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="division_name" datatype="java.lang.String" expression="( select description from hls_divisions_vl where division_id = t1.division_id )" forInsert="false" forUpdate="false"/>
        <bm:field name="lease_chance_id" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="lease_chance_code" datatype="java.lang.String" expression="(select lease_chance_code from lch_lease_chance_hds where lease_chance_id = t1.lease_chance_id)" forInsert="false" forUpdate="false"/>
    </bm:fields>
    <bm:features>
        <f:standard-who/>
        <f:multi-language-storage/>
        <ns2:sequence-pk/>
    </bm:features>
    <bm:primary-key>
        <bm:pk-field name="lease_requisition_id"/>
    </bm:primary-key>
    <bm:query-fields>
        <bm:query-field field="lease_requisition_id" queryExpression="t1.lease_requisition_id=${/parameter/@lease_requisition_id}"/>
        <bm:query-field field="machine_number" queryOperator="like"/>
        <bm:query-field field="lease_req_number" queryOperator="like"/>
        <bm:query-field field="requisition_status" queryOperator="="/>
        <bm:query-field name="lease_start_date_from" queryExpression="t1.lease_start_date &gt;= to_date(${@lease_start_date_from}, &apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="lease_start_date_to" queryExpression="t1.lease_start_date &lt;= to_date(${@lease_start_date_to}, &apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="dealer_name" prmopt="" queryExpression="exists (select 1 from fnd_descriptions fd,hls_fnd_dealer t2 where t1.dealer_id=t2.dealer_id and t2.dealer_name_id=fd.description_id and fd.description_text like ${@dealer_name} and fd.language = ${/session/@lang})"/>
        <bm:query-field name="guarantor_name" queryExpression="exists (select 1 from HLS_PRJ_GUARANTOR_V t2 where t1.guarantor_id=t2.guarantor_id and t2.guarantor_name like ${@guarantor_name})"/>
        <bm:query-field name="customer_name" queryExpression="exists (select 1 from hls_prj_customer t2 where t1.customer_id=t2.customer_id and t2.customer_name like ${@customer_name})"/>
        <bm:query-field name="contract_number" queryExpression="exists (select 1 from hls_prj_contract_hds t2 where t1.lease_requisition_id=t2.lease_requisition_id and t2.contract_number like ${@contract_number})"/>
        <bm:query-field name="vender_name" prmopt="" queryExpression="exists (select 1 from fnd_descriptions fd,hls_fnd_venders t2 where t1.vender_id=t2.vender_id and t2.vender_name_id=fd.description_id and fd.description_text like ${@vender_name} and fd.language = ${/session/@lang})"/>
        <bm:query-field name="first_submit_date_from" queryExpression="(select min(trunc(v.operation_time)) from hls_ctr_lease_req_handle_v v where v.document_id = t1.lease_requisition_id and v.operation_code = &apos;SUBMITTED&apos;) &gt;= to_date(${@first_submit_date_from}, &apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="first_submit_date_to" queryExpression="(select min(trunc(v.operation_time)) from hls_ctr_lease_req_handle_v v where v.document_id = t1.lease_requisition_id and v.operation_code = &apos;SUBMITTED&apos;) &lt;= to_date(${@first_submit_date_to}, &apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="first_approve_date_from" queryExpression="(select min(trunc(v.operation_time)) from hls_ctr_lease_req_handle_v v where v.document_id = t1.lease_requisition_id and v.operation_code in(&apos;APPROVE&apos;,&apos;APPROVAL_REJECT&apos;,&apos;SUPPLY&apos;)) &gt;= to_date(${@first_approve_date_from}, &apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="first_approve_date_to" queryExpression="(select min(trunc(v.operation_time)) from hls_ctr_lease_req_handle_v v where v.document_id = t1.lease_requisition_id and v.operation_code in(&apos;APPROVE&apos;,&apos;APPROVAL_REJECT&apos;,&apos;SUPPLY&apos;)) &lt;= to_date(${@first_approve_date_to}, &apos;yyyy-mm-dd&apos;)"/>
    </bm:query-fields>
    <bm:relations>
        <bm:relation name="r1" joinType="LEFT OUTER" refModel="hls.hls_fnd_pattern_for_lease_lov">
            <bm:reference foreignField="pattern_id" localField="pattern_id"/>
        </bm:relation>
        <bm:relation name="r2" joinType="LEFT OUTER" refModel="hls.hls_fnd_dealer_for_maintain">
            <bm:reference foreignField="dealer_id" localField="dealer_id"/>
        </bm:relation>
    </bm:relations>
    <bm:ref-fields>
        <bm:ref-field name="pattern_code" relationName="r1" sourceField="pattern_code"/>
        <bm:ref-field name="busines_unit" relationName="r1" sourceField="busines_unit"/>
        <bm:ref-field name="dealer_code" relationName="r2" sourceField="dealer_code"/>
    </bm:ref-fields>
    <bm:data-filters>
        <bm:data-filter enforceOperations="query" expression="t1.company_id=${/session/@company_id}"/>
        <bm:data-filter enforceOperations="query" expression=" hls_sys_common_pkg.check_access(${/session/@user_id},${/session/@company_id},${/session/@dealer_id},t1.dealer_id)=1"/>
    </bm:data-filters>
</bm:model>
