prj_project_detail_bp_lv.sql 3 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77
create or replace view prj_project_detail_bp_lv as
Select t1.project_id,
       t1.prj_bp_id,
       t1.bp_id,
       (select hls.bp_name  from  hls_bp_master hls where hls.bp_id = t1.bp_id) bp_id_n,
       t1.bp_category,
       (select b.description
    from hls_bp_category b
   where b.bp_category = t1.bp_category) bp_category_n,
       (Select bt.description
          From hls_bp_type bt, hls_bp_master hls
         Where bt.bp_type = hls.bp_type
           And hls.bp_id = t1.bp_id) As bp_type_n,
       (Select scv.code_value_name
          From sys_code_values_v scv, hls_bp_master hls
         Where scv.code = 'HLS_SYS_UNIT_PROPERTY'
           And scv.code_value = hls.legal_form
           And hls.bp_id = t1.bp_id) legal_form_n,
       (select hls.legal_form  from hls_bp_master hls where hls.bp_id = t1.bp_id) legal_form,
       (Select hls.bp_code
          From hls_bp_master hls
         Where hls.bp_id = t1.bp_id) bp_code,
       (Select hls.bp_class
          From hls_bp_master hls
         Where hls.bp_id = t1.bp_id) bp_class,
       (Select v.code_value_name
          From sys_code_values_v v, hls_bp_master hls
         Where v.code = 'HLS211_BP_CLASS'
           And v.code_value = hls.bp_class
           And hls.bp_id = t1.bp_id) As bp_class_n,
       (Select hls.founded_date
          From hls_bp_master hls
         Where hls.bp_id = t1.bp_id) founded_date,
       (Select hls.registered_capital
          From hls_bp_master hls
         Where hls.bp_id = t1.bp_id) registered_capital,
       (Select hls.work_force
          From hls_bp_master hls
         Where hls.bp_id = t1.bp_id) work_force,
       (Select hls.work_unit_phone
          From hls_bp_master hls
         Where hls.bp_id = t1.bp_id) work_unit_phone,
       (Select hls.office_space
          From hls_bp_master hls
         Where hls.bp_id = t1.bp_id) office_space,
       (Select v.code_value_name
          From sys_code_values_v v, hls_bp_master hls
         Where v.code = 'PROPERTY_ATTRIBUTE'
           And v.code_enabled_flag = 'Y'
           And v.code_value_enabled_flag = 'Y'
           And v.code_value = hls.property_attribute
           And hls.bp_id = t1.bp_id) property_attribute_n,
        (select hls.property_attribute  from hls_bp_master hls where hls.bp_id = t1.bp_id) property_attribute,
       (Select hls.source_of_income_db
          From hls_bp_master hls
         Where hls.bp_id = t1.bp_id) source_of_income_db,
         (Select hls.business_license_num
          From hls_bp_master hls
         Where hls.bp_id = t1.bp_id) business_license_num,
         t1.Number_Of_Car,
         t1.Manage_Of_Car,
         t1.Number_Of_Cus,
         t1.PERIOD_OF_YEAR,
         t1.ASSET_SIZE,
         t1.Debt,
         t1.Revenue,
         t1.Net_Profit,
         t1.Land_Situation,
         t1.Mortgage_Guarantee,
         t1.Estimation_Lease,
         t1.Guarantee_Of_Repayment,
         t1.Risk_Measure,
         t1.CREDIT_RECORD,
         t1.Company_Credit_Record,
         t1.Criminal_Record,
         t1.EXECUTED_ASSET
  From prj_project_bp t1;