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' ;