<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: weiwuzhen  
    $Date: 2016-5-20 下午5:24:41  
    $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 p.chance_id,
       p.project_id,
       p.business_type,
       (select hbt.description from hls_business_type hbt
       where hbt.business_type=p.business_type) business_type_n,
       (select value_name
          from (select code_value value_code, code_value_name value_name
         from sys_code_values_v
        where code = 'HLS500_CHARGE_PAY_TIMES'
          and code_value_enabled_flag = 'Y') t
         where t.value_code = hd.hd_user_col_v06) hd_user_col_v06, --手续费支付频率
         hd.HD_USER_COL_N07 hd_user_col_n07_n,
         to_char(hd.HD_USER_COL_N07* 100  , 'FM999999999990.00')  hd_user_col_n07,--手续费比例值 
       p.project_name, --项目名称
       (select value_name
          from (select employee_id as value_code, name as value_name
                  from hls_salesman_v
                 order by employee_code) t1
         where t1.value_code = p.employee_id) employee_name, --项目经理
         (select ''||'、'||hsv.name from hls_salesman_v hsv
       where hsv.employee_id=p.group_leader) group_leader_n,--项目组长
       (select o.description
          from hls_lease_organization o
         where o.lease_organization = p.lease_organization) as lease_organization_n, --业务部门
       p.Employee_Id_Of_Manager, --部门负责人      
       trim(to_char(hd.lease_item_amount, '999,999,999,990.99')) lease_item_amount, --融资租赁本金
       hd.deposit_ratio deposit_ratio_n,
       to_char(hd.deposit_ratio * 100, 'FM999999999990.00') deposit_ratio, --保证金
       to_char(hd.Lease_Charge_Ratio * 100, 'FM999999999990.00') lease_charge_ratio, --手续费 
       to_char(hd.int_rate * 100, 'FM999,999,999,990.00') || '%' int_rate, --租赁利率   
       (select value_name
          from (select a.code_value value_code, a.code_value_name value_name
                  from sys_code_values_v a
                 where a.code = 'TRE502_INT_RATE_FIXING_WAY'
                   and a.code_value in ('MUTIPLY', 'PLUS','DECREASE','FLOATING_DOWNWARD')) t
         where t.value_code = hd.Int_Rate_Fixing_Way) ||
       to_char(hd.Int_Rate_Fixing_Range * 100,'FM999,999,999,990.00')
       || '%' int_rate_range, --租金利率浮动 
       ('按' ||
       (select value_name
           from (select a.code_value value_code, a.code_value_name value_name
                   from sys_code_values_v a
                  where a.code = 'HLS500_ANNUAL_PAY_TIMES') t2
          where t2.value_code = hd.Annual_Pay_Times) ||
       (select d.description
           from hls_price_list d
          where Q.PRICE_LIST = d.price_list) ||
       (select value_name
           from (select a.code_value value_code, a.code_value_name value_name
                   from sys_code_values_v a
                  where a.code = 'HLS500_PAY_TYPE') t1
          where t1.value_code = hd.Pay_Type) || '还款') pay_way, --还款方式
       trim(to_char(hd.Hd_User_Col_N03, '999,999,999,990.99')) hd_user_col_n03, --残值/残值率
       to_char(trim((hd.irr_reserved2))*100 ,'FM90.00')|| '%' irr,
       ppl.lease_item_insurance_type, --租赁物险种
       hd.MACHINERY_AMOUNT*100||'%' MACHINERY_AMOUNT
  from prj_quotation                q,
       Prj_Project                  P,
       hls_fin_calculator_hd        hd,
       PRJ_LEASE_ITEM_BASIC_INFO_LV ppl
 where q.document_category = 'PROJECT'
   AND q.DOCUMENT_ID = P.PROJECT_ID(+)
   and q.calc_session_id = hd.calc_session_id(+)
   and q.quotation_type = 'MAJOR'
   and p.project_id = ppl.project_id(+)
   and p.project_id=${/parameter/@project_id}
   ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
