<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: hp  
    $Date: 2013-11-12 下午06:19: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 hd.calc_session_id,
       hd.billing_method,
       hd.tax_type_id,
       to_char(nvl(hd.int_rate_display, 0) * 100, 'FM990.0099') || '%' int_rate_display,
       decode(hd.tax_type_id,
              1,
              '营业税',
              2,
              '增值税',
              3,
              '增值税',
              4,
              '增值税',
              '') tax_type_id_desc,
       to_char(hd.lease_item_amount, 'FM99,999,999,990.00') lease_item_amount,
       to_char(decode(to_number(hd.net_lease_charge),
                      0,
                      '',
                      hd.net_lease_charge),
               'FM99,999,999,990.00') net_lease_charge,
       to_char(hd.finance_amount, 'FM99,999,999,990.00') finance_amount,
       to_char(hd.deposit, 'FM99,999,999,990.00') deposit,
       to_char((hd.net_residual_value / hd.finance_amount) * 100,
               'FM990.0099') || '%' residual_ratio_n,
       hd.lease_term,
       to_char(hd.npv_using_cof, 'FM99,999,999,990.00') npv_using_cof,
       to_char(hd.irr * 100, 'FM990.0099') || '%' irr,
       to_char(hd.irr_after_tax * 100, 'FM990.0099') || '%' irr_after_tax,
       to_char(hd.annual_mean_rate * 100, 'FM99,999,999,990.0099') || '%' annual_mean_rate,
       to_char(hd.down_payment, 'FM99,999,999,990.00') down_payment,
       to_char(hd.down_payment_ratio * 100, 'FM990.0099') || '%' down_payment_ratio,
       (to_number(hd.lease_times) * 12 / hd.annual_pay_times) lease_times_n,
       decode(hd.annual_pay_times, 12, '月', 4, '季', 2, '半年', 1, '年') annual_pay_times_n,
       decode(hd.pay_type, 1, '期初', 0, '期末') pay_type_n,
       to_char((hd.deposit_ratio) * 100, 'FM990.0099') || '%' deposit_ratio_n,
       hd.lease_times,
       to_char(hd.net_total_interest + hd.finance_amount,
               'FM99,999,999,999,990.00') finance_amount_n,
       (select v.code_value_name
          from sys_code_values_v v
         where v.code = 'HLS500_DEPOSIT_DEDUCTION'
           and v.code_value = hd.deposit_deduction) deposit_deduction_n,
       to_char(hd.net_residual_value, 'FM99,999,999,999,990.00') net_residual_value,
       (select v.currency_name
          from gld_currency_vl v
         where v.currency_code = hd.currency) currency_n,
       to_char(sysdate, 'yyyy/mm/dd') print_date,
       (select cv.currency_symbol
          from gld_currency_vl cv
         where cv.currency_code = hd.currency) currency_symbol,
       (select sum(1)
          from hls_fin_calculator_ln ln
         where ln.calc_session_id = hd.calc_session_id
           and nvl(ln.net_lease_mgt_fee, 0) != 0) net_lease_mgt_fee_flag,
       hd.lease_mgt_fee_rule,
       (select v.code_value_name
          from sys_code_values_v v
         where v.code = 'HLS500_MGT_FEE_RULE'
           and v.code_value = hd.lease_mgt_fee_rule) lease_mgt_fee_rule_desc,
       (select to_char(sum(nvl(i.original_asset_value, 0)),
                       'FM99,999,999,990.00')
          from prj_project_lease_item i
         where i.project_id = ${@project_id}
           and i.contract_seq = q.contract_seq) lease_original_asset_value,
       
       (select to_char(sum(nvl(i.net_asset_value, 0)), 'FM99,999,999,990.00')
          from prj_project_lease_item i
         where i.project_id = ${@project_id}
           and i.contract_seq = q.contract_seq) lease_net_asset_value,
       
       to_char(nvl(hd.finance_amount, 0) - nvl(hd.deposit, 0),
               'FM99,999,999,990.00') net_finance_amount,
       to_char(nvl(hd.base_rate, 0) * 100, 'FM990.0099') || '%' base_rate,
       (select v.code_value_name
          from sys_code_values_v v
         where v.code = 'TRE502_INT_RATE_FIXING_WAY'
           and v.code_value = hd.int_rate_fixing_way) int_rate_fixing_way_n,
       to_char(nvl(hd.int_rate_fixing_range, 0) * 100, 'FM990.0099') || '%' int_rate_fixing_range,
       q.quotation_type,
       prj305_save_pkg.get_prj_quotation_lease_item(q.quotation_id) quotation_lease_item_name,
	   q.business_type quotation_business_type
  from hls_fin_calculator_hd hd, prj_quotation q
 where q.calc_session_id = hd.calc_session_id
   and hd.calc_session_id in
       (select pq.calc_session_id
          from prj_quotation pq
         where pq.document_category = 'PROJECT'
           and pq.document_id =${@project_id}
           and pq.internal_confirm = 'Y'
           and pq.external_confirm = 'Y'
           and pq.enabled_flag = 'Y')

            
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
