<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: Administrator  
    $Date: 2015-5-11 下午5:53:36  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
SELECT *
  FROM (SELECT pp.project_id,
               pp.bp_name, --承租人
               pp.lease_form_n, --承租模式
               (SELECT u.user_name FROM sys_user u WHERE u.user_id = 1) user_name, --调查人员
               nvl(DECODE(to_char(calc.finance_amount,
                                  'FM999,999,999,999.00'),
                          '.00',
                          '0',
                          to_char(calc.finance_amount, 'FM999,999,999,999.00')),
                   0) finance_amount, --租赁本金
               calc.lease_term, --租赁期限
               to_char(calc.deposit_ratio * 100, 'FM990.00') || '%' deposit_ratio, --保证金比例
               ''deposit,--保证金利率
               to_char(calc.int_rate * 100, 'FM990.00') || '%' int_rate, --年租息率
                 
               (SELECT v.code_value_name
                  FROM sys_code_values_v v
                 WHERE v.code = 'HLS500_ANNUAL_PAY_TIMES'
                   AND v.code_enabled_flag = 'Y'
                   AND v.code_value_enabled_flag = 'Y'
                   AND v.code_value = calc.annual_pay_times) annual_pay_times, --还款周期
                   
               to_char(calc.lease_charge_ratio * 100, 'FM990.00') || '%' lease_charge_ratio, --手续费比例
               CASE
                 WHEN (SELECT COUNT(*)
                         FROM hls_fin_calculator_ln l
                        WHERE l.calc_session_id = calc.calc_session_id
                          AND nvl(l.lease_charge, 0) <> 0) > 1 THEN
                 
                  '多次'
                 ELSE
                  '一次'
               END lease_charge_type, --手续费收取方式
               to_char(calc.machinery_amount * 100, 'FM990.00') || '%' machinery_amount,  --咨询服务费比例
               
               CASE
                 WHEN (SELECT COUNT(*)
                         FROM hls_fin_calculator_ln l
                        WHERE l.calc_session_id = calc.calc_session_id
                          AND nvl(l.other_fee2, 0) <> 0) > 1 THEN
                 
                  '多次'
                 ELSE
                  '一次'
               END other_fee2_type, --咨询服务费收取方式
               
               nvl(DECODE(to_char(calc.down_payment, 'FM999,999,999,999.00'),
                          '.00',
                          '0',
                          to_char(calc.down_payment, 'FM999,999,999,999.00')),
                   0) down_payment, --首次租金
                   
                   
               nvl(DECODE(to_char((select sum(nvl(l.main_business_cost,0))  from hls_fin_calculator_ln l where l.calc_session_id =calc.calc_session_id),
                                  'FM999,999,999,999.00'),
                          '.00',
                          '0',
                          to_char((select sum(nvl(l.main_business_cost,0))  from hls_fin_calculator_ln l where l.calc_session_id =calc.calc_session_id), 'FM999,999,999,999.00')),
                   0)interim_rental, --租前息
               
               nvl(DECODE(to_char(calc.hd_user_col_n03,
                                  'FM999,999,999,999.00'),
                          '.00',
                          '0',
                          to_char(calc.hd_user_col_n03,
                                  'FM999,999,999,999.00')),
                   0) hd_user_col_n03, --留购价款
               nvl(DECODE(to_char(calc.residual_value,
                                  'FM999,999,999,999.00'),
                          '.00',
                          '0',
                          to_char(calc.residual_value, 'FM999,999,999,999.00')),
                   0) residual_value, --留购价款优惠措施
               
               (SELECT i.full_name
                  FROM prj_project_lease_item i
                 WHERE i.project_id = pp.project_id
                   AND rownum = 1) || ' ' ||
               (SELECT i.pattern
                  FROM prj_project_lease_item i
                 WHERE i.project_id = pp.project_id
                   AND rownum = 1) || ' ' ||
               (SELECT i.specification
                  FROM prj_project_lease_item i
                 WHERE i.project_id = pp.project_id
                   AND rownum = 1) || '等' full_name, --租赁物名称种类、型号
               
               (SELECT m.bp_name
                  FROM prj_project_lease_item i, hls_bp_master m
                 WHERE i.project_id = pp.project_id
                   and i.vender_id = m.bp_id
                   AND rownum = 1) || '等' vender_name, --生产厂商
               
               (SELECT nvl(DECODE(to_char(I.original_asset_value,
                                          'FM999,999,999,999.00'),
                                  '.00',
                                  '0',
                                  to_char(I.original_asset_value,
                                          'FM999,999,999,999.00')),
                           0)
                  FROM prj_project_lease_item i
                 WHERE i.project_id = pp.project_id
                   AND rownum = 1) || '(含税)' original_asset_value, --租赁物原值
               ''used_time,--已使用年限
               
               (SELECT nvl(DECODE(to_char(I.net_asset_value,
                                          'FM999,999,999,999.00'),
                                  '.00',
                                  '0',
                                  to_char(I.net_asset_value,
                                          'FM999,999,999,999.00')),
                           0)
                  FROM prj_project_lease_item i
                 WHERE i.project_id = pp.project_id
                   AND rownum = 1) || '(含税)' net_asset_value , --租赁物净值
               
               to_char(calc.irr * 100, 'FM990.00') || '%' irr, --内涵收益率
             '' trust_way --增信方式
          FROM prj_project_lv        pp,
               prj_quotation         pq,
               hls_fin_calculator_hd calc 
         WHERE pq.document_category = 'PROJECT'
           AND pq.document_id = pp.project_id
           AND pq.calc_session_id = calc.calc_session_id
           AND pq.quotation_type = 'MAJOR') tt


   
   
   
   #WHERE_CLAUSE#]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="project_id"/>
        <bm:field name="bp_name"/>
        <bm:field name="lease_form_n"/>
        <bm:field name="user_name"/>
        <bm:field name="finance_amount"/>
        <bm:field name="lease_term"/>
        <bm:field name="annual_pay_times"/>
        <bm:field name="deposit_ratio"/>
        <bm:field name="int_rate"/>
        <bm:field name="lease_charge_ratio"/>
        <bm:field name="lease_charge_type"/>
        <bm:field name="machinery_amount"/>
        <bm:field name="other_fee2_type"/>
        <bm:field name="down_payment"/>
        <bm:field name="interim_rental"/>
        <bm:field name="hd_user_col_n03"/>
        <bm:field name="residual_value"/>
        <bm:field name="full_name"/>
        <bm:field name="vender_name"/>
        <bm:field name="used_time"/>
        <bm:field name="original_asset_value"/>
        <bm:field name="net_asset_value"/>
        <bm:field name="irr"/>
        <bm:field name="trust_way"/>
    </bm:fields>
    <bm:data-filters>
        <bm:data-filter enforceOperations="query" expression="tt.project_id=${@project_id}"/>
    </bm:data-filters>
</bm:model>
