CREATE OR REPLACE VIEW PRJ_PROJECT_LV AS SELECT t1.sign_condition, t1.pay_condition, t1.after_pay_affairs, t1.other_affairs, t1.project_attribute, t1.submit_date, to_char((SELECT zi.creation_date FROM zj_wfl_workflow_instance zi WHERE zi.instance_id = t1.wfl_instance_id)) submit_date_detail, t1.bp_id_tenant, (SELECT hm.bp_name FROM hls_bp_master hm WHERE hm.bp_id = t1.bp_id_tenant) bp_id_tenant_n, (SELECT hm.bp_name FROM hls_bp_master hm WHERE hm.bp_id = t1.bp_id_tenant) bp_id_tenant_n_n, (SELECT hm.bp_name FROM hls_bp_master hm WHERE hm.bp_id = t1.bp_id_tenant) bp_id_tenant_name, t1.bp_id_tenant bp_id, (SELECT hm.bp_name FROM hls_bp_master hm WHERE hm.bp_id = t1.bp_id_tenant) bp_id_n, (SELECT hm.id_card_no FROM hls_bp_master hm WHERE hm.bp_id = t1.bp_id_tenant) id_card_no, (SELECT hm.organization_code FROM hls_bp_master hm WHERE hm.bp_id = t1.bp_id_tenant) organization_code, (SELECT h1.code_value_name value_code FROM sys_code_values_v h1 WHERE h1.code = 'PRJ501_PROJECT_ATTRIBUTE' AND t1.project_attribute = h1.code_value) project_attribute_n, t1.apply_date, t1.purchase_type, (SELECT h1.code_value_name value_code FROM sys_code_values_v h1 WHERE h1.code = 'PRJ501_PURCHASE_TYPE' AND 10 = h1.code_value) purchase_type_n, t1.project_id, t1.guide_price, t1.financial_range_code, t1.district, t1.unit_code, nvl(t1.bp_class,(select bm.bp_class from hls_bp_master bm where bm.bp_id=t1.bp_id_tenant )) AS bp_class, --(select bm.bp_class from hls_bp_master bm where bm.bp_id=t1.bp_id_tenant ) bp_class, t1.business_type, t1.document_type, t1.company_id, t1.lease_organization, t1.division, t1.lease_channel, t1.employee_tel, t1.employee_id, t1.sale_consultant, t1.sale_consultant_tel, t1.owner_user_id, t1.owner_user_tel, t1.wfl_instance_id, -- add by liukang (SELECT d.description FROM fnd_district d WHERE d.district_id = t1.district) district_n, (SELECT h2.description FROM exp_org_unit_vl h2 WHERE h2.enabled_flag = 'Y' AND h2.org_unit_type = '4S' AND t1.unit_code = h2.unit_code) unit_code_n, (SELECT h3.code_value_name FROM sys_code_values_v h3, hls_bp_master bm WHERE h3.code = 'HLS211_BP_CLASS' AND h3.code_enabled_flag = 'Y' AND bm.bp_id = t1.bp_id_tenant AND bm.bp_class = h3.code_value AND h3.code_value_enabled_flag = 'Y') bp_class_n, (SELECT h4.description FROM hls_business_type_v h4 WHERE h4.enabled_flag = 'Y' AND t1.business_type = h4.business_type) business_type_n, (SELECT h5.document_type_desc FROM hls_document_type_v h5 WHERE h5.document_category = 'PROJECT' AND h5.enabled_flag = 'Y' AND t1.document_type = h5.document_type) document_type_n, (SELECT h6.company_short_name FROM fnd_companies_vl h6 WHERE h6.start_date_active < SYSDATE AND (h6.end_date_active IS NULL OR h6.end_date_active < SYSDATE) AND t1.company_id = h6.company_id) company_id_n, (SELECT h7.description FROM hls_lease_organization h7 WHERE h7.enabled_flag = 'Y' AND t1.lease_organization = h7.lease_organization) lease_organization_n, (SELECT h8.description FROM hls_division h8 WHERE h8.enabled_flag = 'Y' AND t1.division = h8.division) division_n, (SELECT h9.description FROM hls_lease_channel h9 WHERE h9.enabled_flag = 'Y' AND t1.lease_channel = h9.lease_channel) lease_channel_n, t1.sale_consultant sale_consultant_n, (SELECT h13.description FROM sys_user h13 WHERE h13.user_id = t1.owner_user_id) owner_user_id_n, (SELECT h14.name value_name FROM exp_employees h14 WHERE t1.employee_id = h14.employee_id) employee_id_n, t1.invoice_agent_id, (SELECT a.bp_name value_name FROM hls_bp_master_lv a WHERE a.bp_category = 'AGENT' AND t1.invoice_agent_id = a.bp_id) invoice_agent_id_n, (SELECT scv.code_value_name FROM sys_code_values_v scv WHERE scv.code = 'PRJ500D_PLATE_RESOURCE' AND t1.plate_resource = scv.code_value) plate_resource_n, (SELECT h16.code_value_name FROM hls_price_list_conds_financial h15, sys_code_values_v h16 WHERE h15.price_list = t1.price_list AND h15.financial_code = h16.code_value AND h16.code = 'HLS_052_FINANCIAL_SCALE' AND t1.financial_range_code = h16.code_value) financial_range_code_n, --t1.down_payment_ratio, (SELECT down_payment_ratio FROM prj_quotation p WHERE p.document_category = 'PROJECT' AND p.document_id = t1.project_id) down_payment_ratio, t1.residual_value, t1.invoice_price, t1.preferential_price, t1.purchase_tax, t1.plate_price, t1.plate_resource, t1.insurance_price, t1.nonlocal_plate_desc, t1.project_number, t1.project_name, t1.short_name, t1.document_category, t1.project_status, t1.search_term_1, t1.search_term_2, t1.chance_id, (SELECT c.chance_number FROM prj_chance c WHERE c.chance_id = t1.chance_id) chance_number, t1.unit_id, t1.employee_id_of_manager, --t1.finance_amount, (SELECT finance_amount FROM prj_quotation p WHERE p.document_category = 'PROJECT' AND p.document_id = t1.project_id) finance_amount, t1.financing_purpose, t1.description, t1.cdd_list_id, t1.summary_bp, t1.description_summary_bp, t1.summary_guarantee, t1.description_summary_guarantee, --modify by zlf nvl(t1.description_lease_item_clob, t1.description_lease_item) description_lease_item_clob, t1.description_mortgage, t1.desc_quotation, t1.risk_analysis_tenant, t1.risk_analysis_project, t1.analysis_primary_product, t1.forecast_sales, t1.forecast_profitability, t1.description_feasibility, t1.superiority, t1.weakness, t1.opportunity, t1.threat, t1.description_swot, t1.risk_market, t1.risk_operating, t1.risk_finance, t1.description_risk, t1.summarize, t1.description_summarize, t1.number_of_tenant, t1.number_of_guarantor, t1.number_of_actual_controller, t1.number_of_quotation, t1.lease_subject, t1.created_by, t1.creation_date create_date, t1.last_updated_by, t1.last_update_date, t1.ref_v01, t1.ref_v02, t1.ref_v03, t1.ref_v04, t1.ref_v05, t1.ref_v06, (SELECT v.code_value_name FROM sys_code_values_v v WHERE v.code = 'LEASE_HOHD' AND v.code_value = t1.ref_v06 AND v.code_enabled_flag = 'Y' AND v.code_value_enabled_flag = 'Y') AS ref_v06_n, t1.ref_v07, t1.ref_v08, t1.ref_v09, t1.ref_v10, t1.ref_n01, t1.ref_n02, t1.ref_n03, t1.ref_n04, t1.ref_n05, t1.ref_n06, t1.ref_n07, t1.ref_n08, t1.ref_n09, t1.ref_n10, t1.ref_d01, t1.ref_d02, t1.ref_d03, t1.ref_d04, t1.ref_d05, t1.ref_d06, t1.ref_d07, t1.ref_d08, t1.ref_d09, t1.ref_d10, t1.declare_flag, t1.currency, t1.price_list, --t1.lease_times, (SELECT lease_times FROM prj_quotation p WHERE p.document_category = 'PROJECT' AND p.document_id = t1.project_id) lease_times, (SELECT to_number(h17.code_value_name) value_name FROM sys_code_values_v h17 WHERE h17.code = 'PRJ500D_LEASE_TIMES' AND (SELECT lease_times FROM prj_quotation p WHERE p.document_category = 'PROJECT' AND p.document_id = t1.project_id) = h17.code_value) lease_times_n, to_char(t1.approved_date, 'yyyy-mm-dd hh24:mi:ss') approved_date, t1.int_rate_type, t1.risk_rating_t, t1.industry_status_t, t1.industry_cost_stru_t, t1.industry_maturity_t, t1.industry_periodicity_t, t1.industry_profitability_t, t1.industry_dependency_t, t1.industry_substitute_t, t1.industry_regulation_t, t1.energy_saving_factor_t, t1.risk_rating_p, t1.industry_status_p, t1.industry_cost_stru_p, t1.industry_maturity_p, t1.industry_periodicity_p, t1.industry_profitability_p, t1.industry_dependency_p, t1.industry_substitute_p, t1.industry_regulation_p, t1.energy_saving_factor_p, t1.production_flow, t1.material_future, t1.technology_future, t1.energy_utilization_future, t1.product_life_period, t1.primary_product_future, t1.competitiveness_product, t1.trend_profit_product, t1.profitability_future, t1.profitability_project, t1.cf_forecast, t1.financing_purposes_an, t1.repayment_ability_an, t1.summary__feasibility, t1.so_info, t1.wo_info, t1.st_info, t1.wt_info, (SELECT d.description FROM hls_document_category d WHERE d.document_category = t1.document_category) document_category_n, (SELECT v.code_value_name FROM sys_code_values_v v WHERE v.code = 'PRJ501_PRJ_STATUS' AND v.code_value = t1.project_status) AS project_status_n, (select u.unit_name from exp_org_unit_v u where u.unit_id = t1.unit_id) as unit_id_n, /* (SELECT h.bp_name FROM hls_bp_master h WHERE h.enabled_flag = 'Y' AND h.bp_id = t1.unit_id AND h.bp_category = 'VENDER') unit_id_n,*/ (SELECT e.name FROM exp_employees e WHERE e.employee_id = t1.employee_id_of_manager) AS employee_id_of_manager_n, (SELECT t.currency_name FROM gld_currency_v t WHERE t.currency_code = t1.currency) currency_n, (SELECT t.description FROM hls_price_list t WHERE t.price_list = t1.price_list) price_list_n, (SELECT sv.code_value_name FROM sys_code_values_v sv WHERE sv.code = 'CON500_INT_RATE_TYPE' AND sv.code_value = t1.int_rate_type) int_rate_type_n, t1.lease_start_date, t1.annual_pay_times, --add by zlf t1.prj_project_special_id, t1.license_provider_name, t1.license_provider_code, (SELECT ps.special_retail_number FROM prj_special_retail ps WHERE ps.special_retail_id = t1.prj_project_special_id) prj_project_special_id_n, (SELECT v.code_value_name FROM sys_code_values_v v WHERE v.code = 'HLS500_ANNUAL_PAY_TIMES' AND v.code_value = t1.annual_pay_times) annual_pay_times_n, (SELECT a.bp_name FROM prj_project_bp a WHERE a.project_id = t1.project_id AND a.bp_category = 'TENANT' AND rownum = 1) bp_name, (SELECT bm.bp_code FROM hls_bp_master bm WHERE bm.bp_id = t1.bp_id_tenant) bp_code, t1.organization_id, (SELECT t.organization_name value_name FROM hls_car_organization t WHERE t.enabled_flag = 'Y' AND t1.organization_id = t.organization_id) organization_id_n, t1.payment_file_check, (SELECT a.code_value_name FROM sys_code_values_v a WHERE a.code_enabled_flag = 'Y' AND a.code_value_enabled_flag = 'Y' AND a.code = 'PAYMENT_FILE_CHECK' AND a.code_value = t1.payment_file_check) payment_file_check_n, t1.project_source, t1.special_permit_flag, t1.creation_date, t1.first_trial_opinion, t1.second_trial_opinion, t1.risk_manager_opinion, t1.insurance_flag, (SELECT a.code_value_name value_name FROM sys_code_values_v a WHERE a.code = 'YES_NO' AND t1.insurance_flag = a.code_value) insurance_flag_n, t1.insurance_company, (SELECT v.code_value_name value_name FROM sys_code_values_v v WHERE v.code = 'INSURANCE_COMPANY' AND v.code_enabled_flag = 'Y' AND v.code_value_enabled_flag = 'Y' AND t1.insurance_company = v.code_value) insurance_company_n, t1.tci_place, (SELECT p.description AS value_name FROM fnd_province p WHERE t1.tci_place = to_char(p.province_id)) tci_place_n, t1.vci_palce, (SELECT p.description AS value_name FROM fnd_province p WHERE t1.vci_palce = to_char(p.province_id)) vci_palce_n, t1.tci_type, (SELECT v.code_value_name value_name FROM sys_code_values_v v WHERE v.code = 'INSURANCE_ISSUE_TYPE' AND v.code_enabled_flag = 'Y' AND v.code_value_enabled_flag = 'Y' AND t1.tci_type = v.code_value) tci_type_n, t1.vci_type, (SELECT v.code_value_name value_name FROM sys_code_values_v v WHERE v.code = 'INSURANCE_ISSUE_TYPE' AND v.code_enabled_flag = 'Y' AND v.code_value_enabled_flag = 'Y' AND t1.vci_type = v.code_value) vci_type_n, t1.insurance_pay_type, (SELECT v.code_value_name value_name FROM sys_code_values_v v WHERE v.code = 'INSURANCE_PAY_TYPE' AND v.code_enabled_flag = 'Y' AND v.code_value_enabled_flag = 'Y' AND t1.insurance_pay_type = v.code_value) insurance_pay_type_n, t1.insurance_commission_type, (SELECT v.code_value_name value_name FROM sys_code_values_v v WHERE v.code = 'COMMISSION_BALANCE_TYPE' AND v.code_enabled_flag = 'Y' AND v.code_value_enabled_flag = 'Y' AND t1.insurance_commission_type = v.code_value) insurance_commission_type_n, t1.prj_con_date, --项目审批通过时间 (SELECT pp.division FROM prj_project pp WHERE pp.project_id = t1.project_id) division1, -- 产品线1 t1.car_type, --车辆类型 (SELECT v.code_value_name value_name FROM sys_code_values_v v WHERE v.code = 'CAR_TYPE' AND v.code_enabled_flag = 'Y' AND v.code_value_enabled_flag = 'Y' AND t1.car_type = v.code_value) car_type_n, t1.note, (SELECT MAX(zw.last_update_date) FROM zj_wfl_approve_record_v zw WHERE zw.instance_id = t1.wfl_instance_id AND zw.action_type = 2) reject_date, (SELECT zw.comment_text FROM zj_wfl_approve_record_v zw WHERE zw.instance_id = t1.wfl_instance_id AND zw.action_type = 2 AND zw.creation_date = (SELECT MAX(b.creation_date) FROM zj_wfl_approve_record_v b WHERE b.action_type = 2 AND b.instance_id = t1.wfl_instance_id)) reject_comment_in, (SELECT zw.comment_text_out FROM zj_wfl_approve_record_v zw WHERE zw.instance_id = t1.wfl_instance_id AND zw.action_type = 2 AND zw.creation_date = (SELECT MAX(b.creation_date) FROM zj_wfl_approve_record_v b WHERE b.action_type = 2 AND b.instance_id = t1.wfl_instance_id)) reject_comment_out, t1.agent_general_comments, (SELECT scv.code_value_name FROM sys_code_values_v scv WHERE scv.code = 'HLS_CUS_DEALER_EVALUATION' AND scv.code_value = t1.agent_general_comments) agent_general_comments_n, t1.fin_manager fin_manager, (SELECT ee.name FROM exp_employees ee WHERE ee.employee_id = t1.fin_manager) fin_manager_n, t1.created_by user_id, t1.check_flag, t1.create_con_date, t1.return_flag, (SELECT pc.credit_flag FROM prj_project_credit pc WHERE pc.project_id = t1.project_id) credit_flag, (SELECT zv.comment_text_out FROM zj_wfl_approve_history_v zv WHERE zv.instance_id = t1.wfl_instance_id AND zv.record_id = (SELECT MAX(z.record_id) FROM zj_wfl_approve_history_v z WHERE z.instance_id = zv.instance_id)) comment_text_out, (CASE WHEN t1.return_flag = 'Y' AND t1.project_status = 'APPROVING' THEN 'Y' ELSE 'N' END) second_submit, t1.bd_customer_score, t1.bd_score_results, (SELECT scv.code_value_name FROM sys_code_values_v scv WHERE scv.code = 'PD_SCORE_RESULTS' AND scv.code_value = t1.bd_score_results) bd_score_results_n, t1.guaranty_mode, t1.mortgage_or_not, t1.mortgagee, t1.other_mortgage, t1.other_guarantee, (SELECT scv.code_value_name FROM sys_code_values_v scv WHERE scv.code = 'GUARANTY_MODE' AND scv.code_value = t1.bd_score_results) guaranty_mode_n, (SELECT scv.code_value_name FROM sys_code_values_v scv WHERE scv.code = 'YES_NO' AND scv.code_value = t1.bd_score_results) mortgage_or_not_n, (SELECT scv.code_value_name FROM sys_code_values_v scv WHERE scv.code = 'YES_NO' AND scv.code_value = t1.bd_score_results) other_mortgage_n, (SELECT mor.register_day FROM mor_register mor WHERE mor.district_id = t1.district) register_day, t1.province, t1.city, (SELECT p.description FROM fnd_province p WHERE p.province_id = t1.province) province_n, (SELECT c.description FROM fnd_city c WHERE c.city_id = t1.city) city_n, /*(SELECT pb.prj_bp_id FROM prj_project_bp pb WHERE pb.project_id = t1.project_id AND pb.bp_id = t1.bp_id_tenant AND pb.bp_type = 'TENANT' ) prj_bp_id,*/ t1.ratify_date, (SELECT hd.deposit FROM hls_fin_calculator_hd hd, prj_quotation q WHERE q.document_id = t1.project_id AND t1.document_category = 'PROJECT' AND hd.calc_session_id = q.calc_session_id) deposit, decode((SELECT p.price_list FROM prj_quotation p WHERE p.document_category = 'PROJECT' AND p.document_id = t1.project_id), 'DR_PRICE_10', (SELECT ln.rental FROM hls_fin_calculator_ln ln, prj_quotation q WHERE q.document_id = t1.project_id AND t1.document_category = 'PROJECT' AND ln.calc_session_id = q.calc_session_id AND ln.times = 1),null) rental_1, (SELECT hd.lease_charge FROM hls_fin_calculator_hd hd, prj_quotation q WHERE q.document_id = t1.project_id AND t1.document_category = 'PROJECT' AND hd.calc_session_id = q.calc_session_id) lease_charge, (SELECT hd.lease_item_amount FROM hls_fin_calculator_hd hd, prj_quotation q WHERE q.document_id = t1.project_id AND t1.document_category = 'PROJECT' AND hd.calc_session_id = q.calc_session_id) lease_item_amount, --add by chenlingfeng t1.product_name, t1.product_code, t1. product_type, t1.approve_note, t1.sign_method, t1.Sales_method, (select v.code_value_name as value_name from sys_code_values_v v where v.code = 'DS_SALES_METHOD' and t1.Sales_method = v.code_value) Sales_method_n, t1.Sales_method_note, t1.nature_of_work, t1.Engineering_area, t1.Actual_user, t1.outsider_work_flag, t1.work_income, t1.work_term, t1.work_Settlement_cycle, t1.work_Settlement_method, (select v.code_value_name as value_name from sys_code_values_v v where v.code = 'WORK_SETTLEMENT_METHOD' and t1.work_Settlement_method = v.code_value) work_Settlement_method_n, t1.signing_people, t1.signing_note, t1.billing_method, t1.sub_price_list,--商品名称2 (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.finance_type,--系统单据默认为0 --add by lijingjing t1.lease_item_location, t1.delivery_address, t1.occu_object, t1.payment_deduction, t1.send_message, t1.secondary_lease, (select v.code_value_name as value_name from sys_code_values_v v where v.code = 'OCCU_OBJECT' and t1.occu_object = v.code_value) occu_object_n, (select v.code_value_name as value_name from sys_code_values_v v where v.code = 'PAYMENT_DEDUCTION' and t1.payment_deduction = v.code_value) payment_deduction_n, (select v.code_value_name as value_name from sys_code_values_v v where v.code = 'SEND_MESSAGE' and t1.send_message = v.code_value) send_message_n, (select v.code_value_name as value_name from sys_code_values_v v where v.code = 'SECONDARY_LEASE' and t1.secondary_lease = v.code_value) secondary_lease_n, t1.pay_method, (select v.code_value_name as value_name from sys_code_values_v v where v.code = 'PAY_METHODS' and t1.pay_method = v.code_value) pay_method_n, t1.bank_branch_name, t1.bank_branch_name bank_branch_name_n, t1.bank_branch_num, t1.bank_account_name, (select description from hls_bp_category t where t.bp_category = (select h.bp_category from hls_bp_master h where h.bp_id = bp_id_tenant)) bp_category_n, /* (SELECT hm.bp_category FROM hls_bp_master hm WHERE hm.bp_id = t1.bp_id_tenant) bp_category, (select hls.description from hls_bp_category hls where hls.bp_category= (SELECT hm.bp_category FROM hls_bp_master hm WHERE hm.bp_id = t1.bp_id_tenant) ) bp_category_n*/ --end --add by t 20190924 t1.score_level, t1.score_result FROM prj_project t1 ;