PRJ_PROJECT_LV.sql

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
;