<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: gaoyang 
    $Date: 2014-12-18 上午11:10:05  
    $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 * FROM (
     select  t1.contract_id,
       t1.calc_session_id,
       t1.contract_number,
       t1.search_term_1,
       t1.contract_name,
       t1.business_type,
       (select bt.description
          from hls_business_type bt
         where bt.business_type = t1.business_type) as business_type_desc,
       t1.document_type,
       (select dt.description
          from hls_document_type dt
         where dt.document_type = t1.document_type) as document_type_desc,
       t1.document_category,
       (select ca.description
          from hls_document_category ca
         where ca.document_category = t1.document_category) as document_category_desc,
       t1.project_id,
       prj.project_number,
       prj.project_name,
       t1.company_id,
       t1.lease_organization,
       (select o.description
          from hls_lease_organization o
         where o.lease_organization = t1.lease_organization) as lease_organization_desc,
       t1.lease_channel,
       (select ch.description
          from hls_lease_channel ch
         where ch.lease_channel = t1.lease_channel) as lease_channel_desc,
       t1.division,
       (select d.description
          from hls_division d
         where d.division = t1.division) as division_desc,
       t1.bp_id_tenant,
       (select bpv.bp_name
          from HLS_BP_MASTER_V bpv
         where bpv.bp_id = t1.bp_id_tenant) bp_name,
       t1.bp_id_agent_level1,
       t1.bp_id_agent_level2,
       t1.bp_id_agent_level3,
       t1.owner_user_id,
       t1.employee_id,
       e.employee_code,
       e.name as employee_name,
       t1.unit_id,
       u.unit_code,
       u.description as unit_name,
       t1.employee_id_of_manager,
       mgr.employee_code as employee_code_of_manager,
       mgr.name as employee_name_of_manager,
       t1.factoring_type,
       t1.description,
       t1.price_list,
       t1.calc_method,
       t1.inception_of_lease,
       t1.lease_start_date,
       t1.first_pay_date,
       t1.last_pay_date,
       t1.lease_end_date,
       t1.lease_times,
       t1.pay_times,
       t1.annual_pay_times,
       t1.lease_term,
       t1.pay_type,
       t1.currency,
       (select g.currency_name
          from gld_currency_vl g
         where g.currency_code = t1.currency) as currency_desc,
       t1.currency_precision,
       t1.machinery_amount,
       t1.parts_amount,
       t1.lease_item_amount,
       t1.lease_item_cost,
       t1.down_payment,
       t1.down_payment_ratio,
       t1.finance_amount,
       t1.net_finance_amount,
       t1.total_interest,
       t1.total_rental,
       t1.total_fee,
       t1.contract_amount,
       t1.tax_type_id,
       (select tt.tax_type_code || '-' || tt.description
          from fnd_tax_type_codes tt
         where tt.tax_type_id = t1.tax_type_id) as tax_type_desc,
       t1.vat_flag,
       t1.vat_rate,
       t1.vat_input,
       t1.vat_total_interest,
       t1.vat_finance_amount as vat_total_principal,
       t1.vat_total_rental,
       t1.vat_total_fee,
       t1.net_total_interest,
       t1.net_finance_amount as net_net_total_principal,
       t1.net_total_rental,
       t1.net_total_fee,
       t1.net_lease_item_amount,
       t1.lease_charge,
       t1.lease_charge_ratio,
       t1.lease_mgt_fee,
       t1.lease_mgt_fee_ratio,
       t1.lease_mgt_fee_rule,
       t1.deposit,
       t1.deposit_ratio,
       t1.deposit_deduction,
       t1.residual_value,
       t1.residual_ratio,
       t1.balloon,
       t1.balloon_ratio,
       t1.interim_rent_period,
       t1.interim_times,
       t1.interim_rental,
       t1.insurance_fee,
       t1.insurance_rate,
       t1.commission_payable,
       t1.commission_receivable,
       t1.third_party_deposit,
       t1.promise_to_pay,
       t1.other_fee,
       t1.other_payment,
       t1.rounding_object,
       t1.rounding_method,
       t1.int_rate_fixing_way,
       t1.int_rate_fixing_range,
       t1.int_rate_display,
       t1.base_rate_type,
       t1.base_rate,
       t1.int_rate,
       t1.int_rate_implicit,
       t1.int_rate_type,
       t1.flt_rate_profile,
       t1.flt_rate_adj_method,
       t1.flt_simulate_step,
       t1.flt_simulate_range,
       t1.flt_unit_adj_amt,
       t1.flt_execute_times_rule,
       t1.flt_int_rate_adj_date,
       t1.int_rate_precision,
       t1.irr,
       t1.irr_after_tax,
       t1.int_rate_implicit_after_tax,
       t1.irr_reserved1,
       t1.irr_reserved2,
       t1.irr_reserved3,
       t1.pmt,
       t1.pmt_first,
       t1.annual_mean_rate,
       t1.total_salestax,
       t1.biz_day_convention,
       t1.calc_with_residual_value,
       t1.exchange_rate_type,
       (select v.rate_type_desc
          from gld_exchange_rate_type_v v
         where v.rate_type_code = t1.exchange_rate_type) as exchange_rate_type_desc,
       t1.exchange_rate_quotation,
       t1.exchange_rate,
       t1.penalty_profile,
       t1.grace_period,
       t1.penalty_rate,
       t1.penalty_calc_base,
       t1.penalty_total_base_ratio,
       t1.credit_write_off_order,
       t1.fin_income_recognize_method,
       t1.early_termination_profile,
       t1.payment_method_id,
       t1.telex_transfer_bank_id,
       t1.tt_bank_branch_name,
       t1.tt_bank_account_num,
       t1.tt_bank_account_name,
       t1.tt_remark,
       t1.direct_debit_bank_id,
       t1.dd_bank_branch_name,
       t1.dd_bank_account_num,
       t1.dd_bank_account_name,
       t1.dd_agreement_no,
       t1.dd_agreement_status,
       t1.dd_remark,
       t1.purchase_order_no,
       t1.contract_status,
       (select sc.code_value_name
          from sys_code_values_v sc
         where sc.code = 'CON500_CONTRACT_STATUS'
           and sc.code_value = t1.contract_status) contract_status_desc,
       t1.user_status_1,
       t1.user_status_2,
       t1.user_status_3,
       t1.print_status,
       t1.print_times,
       t1.first_print_date,
       t1.first_print_by,
       t1.delivery_status,
       t1.delivery_date,
       t1.billing_method,
       (select b.description
          from con_billing_method b
         where b.billing_method = t1.billing_method) as billing_method_desc,
       t1.billing_status,
       t1.signing_date,
       t1.original_recall_date,
       t1.lease_card_recall_date,
       t1.early_termination_date,
       t1.termination_date,
       t1.assignment_agreement_no,
       t1.assignment_date,
       t1.btb_payment_date,
       t1.btb_int_rate,
       t1.btb_int_rate_implicit,
       t1.btb_finance_amount,
       t1.btb_net_finance_amount,
       t1.btb_total_repayment,
       t1.btb_total_interest,
       t1.btb_total_fee_pv,
       t1.btb_total_fee,
       t1.btb_finance_ratio,
       t1.btb_interest_margin,
       t1.btb_vat_interest,
       t1.btb_vat_fee,
       t1.btb_interest_after_tax,
       t1.btb_fee_after_tax,
       t1.vat_rate_of_interest,
       t1.hd_user_col_d01,
       t1.hd_user_col_d02,
       t1.hd_user_col_d03,
       t1.hd_user_col_d04,
       t1.hd_user_col_d05,
       t1.hd_user_col_v01,
       t1.hd_user_col_v02,
       t1.hd_user_col_v03,
       t1.hd_user_col_v04,
       t1.hd_user_col_v05,
       t1.hd_user_col_v06,
       t1.hd_user_col_v07,
       t1.hd_user_col_v08,
       t1.hd_user_col_v09,
       t1.hd_user_col_v10,
       t1.hd_user_col_n01,
       t1.hd_user_col_n02,
       t1.hd_user_col_n03,
       t1.hd_user_col_n04,
       t1.hd_user_col_n05,
       t1.hd_user_col_n06,
       t1.hd_user_col_n07,
       t1.hd_user_col_n08,
       t1.hd_user_col_n09,
       t1.hd_user_col_n10,
       t1.hd_user_col_n11,
       t1.hd_user_col_n12,
       t1.hd_user_col_n13,
       t1.hd_user_col_n14,
       t1.hd_user_col_n15,
       t1.created_by,
       t1.creation_date,
       t1.last_updated_by,
       t1.last_update_date,
       prj.search_term_1 prj_search_term_1,
       prj.search_term_2 prj_search_term_2,
       t1.search_term_1 con_search_term_1,
       t1.search_term_2 con_search_term_2,
       t1.cdd_list_id,
       t1.OTHER_TMPT_CLAUSE,
        (select sc.code_value_name
          from sys_code_values_v sc
         where sc.code = 'OTHER_TMPT_CLAUSE'
           and sc.code_value = t1.other_tmpt_clause) other_tmpt_clause_desc,
       (select bp_name from hls_bp_master where bp_id = t1.bp_id_vender) bp_vender,
       t1.ZERO_TIMES_DATE,
       t1.SIGNING_USER_ID,
       (select sd.saleman_name
       from cwl_saleman_define sd
       where sd.sale_conselor_id = t1.signing_user_id) as signing_user,
       t1.SIGNING_LOCATION,
       (select scv.code_value_name from sys_code_values_v scv,hls_bp_master hbm
       where scv.code = 'HLS211_BP_CLASS'
       and scv.code_value = hbm.bp_class and hbm.bp_id = t1.bp_id_tenant) bp_tenant_class_n,
       (select hbm.bp_class from hls_bp_master hbm where hbm.bp_id = t1.bp_id_tenant)
       bp_tenant_class ,
       t1.data_class,
       t1.inception_category,
       t1.serial_number,
       t1.collector_user_id,
       (select sv.employee_name from sys_user_v sv where sv.user_id =  t1.collector_user_id) collector_user_id_n,
       t1.legal_status,
      (select t.code_value_name from sys_code_values_v t where t.code = 'LEG501_LEGAL_STATUS' and t.code_value=t1.legal_status) as  legal_status_n,
      t1.fin_period_num
  from con_contract    t1,
       prj_project     prj,
       exp_employees   e,
       exp_employees   mgr,
       exp_org_unit_vl u
 where t1.project_id = prj.project_id(+)
   and t1.employee_id = e.employee_id(+)
   and t1.employee_id_of_manager = mgr.employee_id(+)
   and t1.unit_id = u.unit_id(+)
   and t1.document_category = 'CONTRACT'
   and t1.data_class = 'NORMAL')T2
   #WHERE_CLAUSE#

            
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_id" queryExpression="contract_id=${@contract_id}"/>
    </bm:query-fields>
</bm:model>
