create or replace view prj_quotation_new_lv as
select "QUOTATION_ID",
       t1.document_category,
       t1.calc_session_id,
       "DOCUMENT_ID",
       t1.lease_start_date,
       t1.price_list,
       (select l.description as value_name
          from hls_price_list l
         where l.price_list = t1.price_list) price_list_n,
       t1.lease_times,
       t1.annual_pay_times,
       "DOWN_PAYMENT",
       t1.down_payment_ratio,
       t1.residual_value,
       t1.guide_price,
       t1.invoice_price,
       t1.purchase_tax,
       t1.insurance_price,
       t1.plate_price,
       "LEASE_CHARGE",
       "LEASE_CHARGE_RATIO",
       "MAIN_CONTRACT_FLAG",
       "DOCUMENT_NUMBER",
       "LEASE_ITEM_AMOUNT",
       "REVERSE_FLAG",
       "WFL_STATUS",
       t1.wfl_instance_id,
       "REVIEW_FLAG",
       "DOCUMENT_STATUS",
       "REVIEW_BY",
       "REVERSE_DATE",
       "RENT_TYPE",
       "RENT_DATE",
       "RENT_DAY",
       "INSURANCE_FEE",
       "BP_ID_INSURE",
       "INSURE_TYPE",
       "CREATED_CONTRACT_FLAG",
       "HD_USER_COL_V09",
       "HD_USER_COL_N06",
       "HD_USER_COL_N15",
       "HD_USER_COL_N14",
       "HD_USER_COL_N13",
       "DEPOSIT_DEDUCTION",
       "TOTAL_SALESTAX",
       "HD_USER_COL_N08",
       "INT_RATE_DISPLAY",
       "PAY_TYPE",
       "HD_USER_COL_N02",
       "HD_USER_COL_N07",
       "LEASE_ITEM_PRICE_AGENT",
       "GPS_INSTALL",
       "HD_USER_COL_N05",
       "GPS_FEE_PAY",
       "GPS_FEE",
       "INVESTIGATION_FEE",
       "FIRST_INSURANCE_FEE",
       "OTHER_FEE",
       "LEASE_TERM",
       "HD_USER_COL_V01",
       "HD_USER_COL_N12",
       "HD_USER_COL_N04",
       "BTB_TOTAL_FEE_PV",
       "BTB_INTEREST_MARGIN",
       "DEPOSIT_RATIO",
       "NOTARIAL_FEE_PAYABLE",
       "OTHER_FEE3",
       t1.btb_fee_after_tax,
       (select h1.code_value_name value_code
          from sys_code_values_v h1
         where h1.code = 'HLS500_ANNUAL_PAY_TIMES'
           and t1.annual_pay_times = h1.code_value) annual_pay_times_n,
       -- (select h1.code_value_name value_code
       --    from sys_code_values_v h1
       --   where h1.code = 'HLS500_ANNUAL_PAY_TIMES'
       --     and t1.btb_fee_after_tax = h1.code_value) btb_fee_after_tax_n,
       (select h1.code_value_name value_code
          from sys_code_values_v h1
         where h1.code = 'HLS500_ANNUAL_PAY_TIMES'
           and h1.code_value = t1.btb_fee_after_tax) btb_fee_after_tax_n,
       (select h1.code_value_name value_code
          from sys_code_values_v h1
         where h1.code = 'HLS_052_FINANCIAL_SCALE'
           and t1.hd_user_col_v01 = h1.code_value) hd_user_col_v01_n,
       /*  decode(t1.lease_times, 12, 12, 24, 24, 36, 36, 48, 48) lease_times_n,*/
       (select h1.code_value_name value_code
          from sys_code_values_v h1
         where h1.code = 'TIMES'
           and t1.lease_times = h1.code_value) lease_times_n,
       a.project_id,
       t1.product_id,
       (select t2.special_note
          from hls_product_plan_definition t2
         where t2.product_plan_id = t1.product_id) product_id_n,
       (select t2.if_self_definition
          from hls_product_plan_definition t2
         where t2.product_plan_id = t1.product_id) if_self_definition, --是否自定义产品
       (select decode(t2.if_self_definition, 'N', '否', 'Y', '是', '否')
          from hls_product_plan_definition t2
         where t2.product_plan_id = t1.product_id) if_self_definition_n, --是否自定义产品
       t1.base_rate, t1.base_rate_type,
       (select d.description
          from fnd_base_rate_type d
         where d.base_rate_type = t1.base_rate_type) base_rate_type_n,
       t1.currency,
       t1.btb_net_finance_amount,
       t1.btb_finance_amount,
       (select hd.deposit
          from hls_fin_calculator_hd hd
         where hd.calc_session_id = t1.calc_session_id) deposit,
       (select hd.irr
          from hls_fin_calculator_hd hd
         where hd.calc_session_id = t1.calc_session_id) irr,
       t1.third_party_deposit,
       (select b.code_value_name value_name
          from sys_code_values_v b
         where b.code = 'HLS500_DEPOSIT_DEDUCTION'
           and b.code_value = t1.deposit_deduction) deposit_deduction_n,
       a.business_type,
       t1.commission_receivable,
       t1.finance_amount,
       t1.other_payment3,
       t1.btb_total_fee,
       t1.hd_user_col_v03,
       t1.hd_user_col_v05,
       (select b.code_value_name
          from sys_codes a, sys_code_values_vl b
         where b.code_id = a.code_id
           and a.code = 'MORTGAGE_TIME'
           and b.code_value = t1.hd_user_col_v05) hd_user_col_v05_n,
       nvl(t1.promise_to_pay, 0) promise_to_pay,
       decode(t1.price_list, 'DR_PRICE_25', 'Y', 'N') quote_rent,
       --add by chenlingfeng
       t1.pmt_first,
       t1.direct_lease_charge,
       t1.cus_charge_allocate_flag,
       t1.insurance_pck_flag,
       t1.product_name,
       t1.product_type,
       t1.INSURANCE_METHOD,
       (select  v.code_value_name
  from sys_code_values_v v
 where v.code = 'INSURANCE_METHOD' and
 v.code_value =  t1.INSURANCE_METHOD) INSURANCE_METHOD_n,
       (select h.price_list from hls_fin_calculator_hd h where h.calc_session_id= t1.calc_session_id) cal_hd_price_list,
       t1.sub_price_list,--商品名2type
        (select  v.code_value_name
               from sys_code_values_v v
               where v.code = 'PRICE_LIST_NAME'
                and v.code_value=t1.sub_price_list)sub_price_list_n,
        t1.lease_mgt_fee_ratio,--代理商手续费比例
        --add  by  lijingjing
        t1.other_fee2,
        t1.total_rental,
         t1.hd_user_col_n11
       --  t1.lease_start_date


  from prj_quotation t1, prj_project a
 where t1.document_id = a.project_id
   and t1.document_category = 'PROJECT'
;