<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: weiwuzhen  
    $Date: 2016-5-8 下午2:43:26  
    $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 
         (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_v03,--调息规则
       ((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) ||
         trim(to_char((hd.int_rate_fixing_range) * 100, 'FM990.09999999')) || '%') rate_range, --较基准利率浮动比例
            p.project_id,            
        (select value_name
           from (select business_type as value_code,
                        description   as value_name
                   from hls_business_type a
                  where business_type in ('LEASE', 'LEASEBACK')) t
          where t.value_code = hd.business_type) business_type, --租赁类型   

        trim(to_char(hd.lease_item_amount, 'FM999,999,999,990.09')) lease_item_amount, --租赁金额
        --融资用途    
        trim(to_char((hd.int_rate) * 100,'FM999,999,990.099999')) || '%' 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 , 'FM990.09999999')|| '%') int_rate_range, --租金利率浮动          
        hd.hd_user_col_n20, --宽限期数    
       	to_char((hd.hd_user_col_n07) * 100, 'FM990.09') || '%' hd_user_col_n07, --租赁服务费比例
        to_char(hd.net_lease_charge,'FM999,999,999,990.99') net_lease_charge, --租赁服务费金额
        to_char(hd.hd_user_col_n03,'FM999,999,999,990.99') hd_user_col_n03, --余值或残值
        trim(to_char((hd.deposit_ratio) * 100, '990.99')) || '%' deposit_ratio, --保证金比例 
        to_char(hd.deposit,'FM999,999,999,990.99') deposit, --保证金金额   
         (select value_name from (select b.code_value value_code, b.code_value_name value_name
      from sys_codes a, sys_code_values_vl b
     where b.code_id = a.code_id
       and a.code = 'HLS500_DEPOSIT_DEDUCTION'
     order by b.code_value) t3
     where t3.value_code= HD.deposit_deduction ) deposit_deduction, --保证金退还方式
        trim(to_char(HD.LEASE_TERM,'FM999,999,990.09999')) lease_term, --租赁期限(年)
        hd.lease_times, --还款期数
        hd.pay_type,
        (' 按' || (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 t1.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, --还款方式
        hd.hd_user_col_v10, --返点收入       
        to_char(hd.net_total_rental,'FM999,999,999,990.99') net_total_rental,--概算租赁本息额
        trim((hd.irr) * 100) || '%' irr        
   from prj_quotation t1, Prj_Project P, hls_fin_calculator_hd hd
  where t1.document_category = 'PROJECT'
    AND T1.DOCUMENT_ID = P.PROJECT_ID(+)
    and t1.calc_session_id = hd.calc_session_id(+)
    and p.project_id=${/parameter/@project_id}
   ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
