<?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="t1.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,
                        (SELECT scv.code_value_name
                           FROM sys_code_values_v           scv,
                                con_contract_lease_item     ci,
                                hls_product_plan_definition hd
                          WHERE scv.code = 'DISCOUNT_METHOD'
                            AND scv.code_value_enabled_flag = 'Y'
                            AND scv.code_value = hd.discount_method
                            AND hd.product_plan_id = ci.product_plan_id
                            AND ci.contract_id = t.contract_id) discount_method_n,
                        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,
                        h.document_category,
                        (SELECT hd.description
                           FROM hls_document_category hd
                          WHERE hd.document_category = h.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(max(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,
                        (SELECT COUNT(*)
                           FROM con_contract_cashflow h
                          WHERE h.contract_id = t.contract_id
                            AND h.write_off_flag = 'FULL'
                            AND h.cf_item = 1) received_times,
                        (SELECT su.description
                           FROM sys_user su
                          WHERE su.user_id = p.owner_user_id) owner,
                        t.lease_times,
                        trim((TO_CHAR(ROUND(t.int_rate_display, 4) * 100,
                                      '90.00') || '%')) int_rate_display,
                        t.invoice_price,
                        t.finance_amount,
                        t.down_payment,
                        trim((TO_CHAR(ROUND(t.down_payment_ratio, 4) * 100,
                                      '90.00') || '%')) down_payment_ratio,
                        t.deposit,
                        trim((TO_CHAR(ROUND(t.deposit_ratio, 4) * 100,
                                      '90.00') || '%')) deposit_ratio,
                        t.lease_charge,
                        trim((TO_CHAR(ROUND(t.lease_charge_ratio, 4) * 100,
                                      '90.00') || '%')) lease_charge_ratio,
                        (SELECT TO_CHAR(cc.due_date, 'yyyy-mm-dd')
                           FROM con_contract_cashflow cc
                          WHERE cc.contract_id = t.contract_id
                            AND cc.times = 1
                            AND cc.cf_item = 1) start_due_date,
                        t.bp_id_agent_level1,
                        (SELECT hls.bp_name
                           FROM hls_bp_master hls
                          WHERE hls.bp_id = t.bp_id_agent_level1) bp_agent,
                        (SELECT hd.product_name_write
                           FROM prj_project_lease_item      pi,
                                hls_product_plan_definition hd
                          WHERE pi.project_id = p.project_id
                            AND pi.product_plan_id = hd.product_plan_id) product_name_write,
                        (SELECT hls.bp_name
                           FROM hls_bp_master hls
                          WHERE hls.bp_id = 1) bp_name_1,
                        p.division,
                        (SELECT h8.description
                           FROM hls_division h8
                          WHERE h8.enabled_flag = 'Y'
                            AND p.division = h8.division) division_n,
                        p.bp_class,
                        (SELECT v.code_value_name
                           FROM sys_code_values_v v
                          WHERE v.code = 'CUSTOMER_TYPE'
                            AND v.code_value = p.bp_class
                            AND v.code_enabled_flag = 'Y'
                            AND v.code_value_enabled_flag = 'Y') AS bp_class_n,
                        (SELECT hbt.description
                           FROM hls_business_type hbt
                          WHERE hbt.enabled_flag = 'Y'
                            AND hbt.business_type = p.business_type) business_type_n,
                        (SELECT v.code_value_name
                           FROM sys_code_values_v v
                          WHERE v.code = 'CAR_TYPE'
                            AND v.code_value = p.car_type
                            AND v.code_enabled_flag = 'Y'
                            AND v.code_value_enabled_flag = 'Y') AS car_type_n,
                        (SELECT t1.description
                           FROM hls_lease_channel t1
                          WHERE t1.lease_channel = p.lease_channel
                            AND t1.enabled_flag = 'Y') lease_channel_n,
                        DECODE(h.et_method, 'ET', '提前结清', '正常结清') et_method_n,
                        h.et_irr,
                        t.annual_pay_times,
                        (SELECT v.code_value_name
                           FROM sys_code_values_v v
                          WHERE v.code = 'HLS500_ANNUAL_PAY_TIMES'
                            AND v.code_value = t.annual_pay_times) AS annual_pay_times_n,
                        h.overdue_amt,
                        t.lease_execution_date,
                        h.created_by,
                        (select su.description
                        from sys_user su
                        where su.user_id = h.created_by) created_by_n
                   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(+)) t1
                    #WHERE_CLAUSE#
          order by t1.creation_date desc
            ]]></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:field name="received_times"/>
        <bm:field name="owner"/>
        <bm:field name="start_due_date"/>
        <bm:field name="bp_agent"/>
        <bm:field name="division_n"/>
        <bm:field name="bp_name_1"/>
        <bm:field name="product_name_write"/>
        <bm:field name="int_rate_display"/>
        <bm:field name="invoice_price"/>
        <bm:field name="finance_amount"/>
        <bm:field name="down_payment"/>
        <bm:field name="down_payment_ratio"/>
        <bm:field name="deposit_ratio"/>
        <bm:field name="deposit"/>
        <bm:field name="lease_charge_ratio"/>
        <bm:field name="lease_charge"/>
        <bm:field name="discount_method_n"/>
        <bm:field name="bp_class_n"/>
        <bm:field name="business_type_n"/>
        <bm:field name="car_type_n"/>
        <bm:field name="lease_channel_n"/>
        <bm:field name="et_method_n"/>
        <bm:field name="et_irr"/>
        <bm:field name="lease_times"/>
        <bm:field name="annual_pay_times"/>
        <bm:field name="annual_pay_times_n"/>
        <bm:field name="overdue_amt"/>
        <bm:field name="lease_execution_date"/>
        <bm:field name="created_by"/>
        <bm:field name="created_by_n"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field field="creation_date" queryExpression="t1.creation_date =to_char(${@creation_date},'yyyy-mm-dd')"/>
        <bm:query-field field="et_agreement_id" queryOperator="="/>
        <bm:query-field name="bp_id_tenant" queryExpression="t1.bp_id = ${@bp_id_tenant}"/>
        <bm:query-field name="bp_id_agent_level1" queryExpression="t1.bp_id_agent_level1 = ${@bp_id_agent_level1}"/>
        <bm:query-field name="division" queryExpression="t1.division = ${@division}"/>
        <bm:query-field name="received_times" queryExpression="t1.received_times = ${@received_times}"/>
        <bm:query-field name="contract_status" queryExpression="t1.contract_status = ${@contract_status}"/>
        <bm:query-field name="overdue_status" datatype="java.lang.String" queryexpression="t1.overdue_status=${@overdue_status}"/>
        <bm:query-field name="lease_execution_date_from"  queryexpression="to_char(t1.lease_execution_date,'YYYY-MM-DD') &gt;= ${@lease_execution_date_from}"/>
        <bm:query-field name="lease_execution_date_to" queryexpression="to_char(t1.lease_execution_date,'YYYY-MM-DD') &lt;= ${@lease_execution_date_to}"/>
        <bm:query-field name="contract_number" queryExpression="upper(t1.contract_number) like &apos;%&apos;||upper(${@contract_number})||&apos;%&apos;"/>
        <bm:query-field name="status_filter" queryExpression="${@status_filter}=&apos;Y&apos;and t1.et_agreement_status in (&apos;NEW&apos;)"/>
        <bm:query-field name="et_agreement_status_desc" queryExpression="t1.et_agreement_status_desc = ${@et_agreement_status_desc}"/>
        <bm:query-field name="creation_date_from" queryExpression="t1.creation_date &gt;= to_char(to_date(${@creation_date_from},&apos;yyyy-mm-dd&apos;),'yyyy-mm-dd')"/>
        <bm:query-field name="creation_date_to" queryExpression="t1.creation_date &lt;= to_char(to_date(${@creation_date_to},&apos;yyyy-mm-dd&apos;),'yyyy-mm-dd')"/>
    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter enforceOperations="query" expression="t1.et_agreement_status IN (&apos;NEW&apos;,&apos;SUBMIT&apos;,&apos;REJECT&apos;,&apos;CANCEL&apos;,&apos;APPROVED&apos;,&apos;APPROVING&apos;)"/>
        <bm:data-filter enforceOperations="query" expression="t1.et_type IN (&apos;ET&apos;)"/>
    </bm:data-filters>
</bm:model>
