CREATE OR REPLACE VIEW PRJ_PROJECT_BP_TENANT_LV AS
SELECT t1."PRJ_BP_ID",
       t1."PROJECT_ID",
       t1."BP_CATEGORY",
       t1."BP_ID",
       t1."CONTRACT_SEQ",
       t1."BP_SEQ",
       t1."CREAT_BP_FLG",
       t1."BP_CODE",
       t1."BP_TYPE",
       (select h.description from hls_bp_type h where h.enabled_flag = 'Y'
       and h.bp_type = t1.bp_type)  bp_typ_n,
       (select scv.code_value_name
          from sys_code_values_v scv
         where scv.code = 'HLS211_BP_CLASS'
           and scv.code_value = t1.bp_class) bp_class_n,
       t1."BP_CLASS",
       t1."BP_NAME",
       t1."EXTRA_NAM",
       t1."EXTERNAL_BP_CODE",
       t1."ID_TYPE",
       t1."ID_CARD_NO",
       t1."GENDER",
       t1."AGE",
       t1."DATE_OF_BIRTH",
       t1."PLACE_OF_BIRTH",
       t1."LIV_PROVINCE",
       t1."LIV_CITY",
       t1."LIV_DISTRICT",
       t1."LIV_STREET",
       t1."LIVING_ADDRESS",
       t1."ADD_CITY",
       t1."ADD_PROVINCE",
       t1."RESIDENT_ADDRES",
       t1."RESIDENT_STATUS",
       t1."STATE_OF_HEALTH",
       t1."ACADEMIC_BACKGROUND",
       t1."MARITAL_STATUS",
       t1."ETHNIC_GROUP",
       t1."FERTILITY_STATUS",
       t1."NUMBER_OF_CHILDREN",
       t1."CONTACT_PERSON",
       t1."POSITION",
       t1."PHONE",
       t1."PHONE_2",
       t1."CELL_PHONE",
       t1."FAX",
       t1."ZIPCODE",
       t1."EMAIL",
       t1."CELL_PHONE_2",
       t1."WEBSITE",
       t1."E_MAIL",
       t1."QQ",
       t1."WEI_CHAT",
       t1."SOURCE_OF_INCOME",
       t1."ANNUAL_INCOME",
       t1."OTHER_ANNUAL_INCOME",
       t1."OTHER_ASSET",
       t1."CAPITAL_OF_FAMILY",
       t1."LIABILITY_OF_FAMILY",
       t1."GUARANTEE_AMOUNT",
       t1."OWNSHIP_OF_HOUSE",
       t1."HOUSE_LOANS_FLAG",
       t1."SEND_ADDRESS",
       t1."WORK_TYPE",
       t1."WORK_UNIT",
       t1."WORK_PROVINCE",
       t1."WORK_CITY",
       t1."WORK_UNIT_ADDRESS",
       t1."WORK_UNIT_PHONE",
       t1."WORK_UNIT_ZIP",
       t1."COMPANY_NATURE",
       t1."INDUSTRY_WORK_EXPER",
       t1."MAIN_DRIVER_OF_CAR",
       t1."DRIVER_LICENSE_NO",
       t1."LIVING_SITUATION",
       t1."SOURCE_OF_INCOME_DB",
       t1."RELEASE_FORM",
       t1."BUSINESS_LICENSE_NUM",
       t1."ORGANIZATION_CODE",
       t1."TAX_REGISTRY_NUM",
       t1."LOAN_CARD_NUM",
       t1."TAXPAYER_TYPE",
       t1."CORPORATE_CODE",
       t1."REGISTERED_PLACE",
       t1."FOUNDED_DATE",
       t1."REGISTERED_CAPITAL",
       t1."PAID_UP_CAPITAL",
       t1."CURRENCY",
       t1."LEGAL_FORM",
       t1."INDUSTRY",
       t1."ENTERPRISE_SCALE",
       t1."SHAREHOLDERS_BACKGROUND",
       t1."FOA_RATE",
       t1."PORPORTION_OF_GUARANTEE",
       t1."EARNINGS_CONDITION",
       t1."MAIN_BUSINESS_GROWTH",
       t1."ROE",
       t1."CURRENT_RATIO",
       t1."INTEREST_COVER_RATIO",
       t1."DEBT_TO_ASSET_RATIO",
       t1."BP_NAME_SP",
       t1."CARD_TYPE_SP",
       t1."ID_NO_SP",
       t1."GENDER_SP",
       t1."DATE_OF_BIRTH_SP",
       t1."AGE_SP",
       t1."CELL_PHONE_SP",
       t1."ACADEMIC_BACKGROUND_SP",
       t1."LIVING_ADD_CITY",
       t1."LIVING_ADD_PROVINCE",
       t1."LIVING_ADDRESS_SP",
       t1."RESIDENT_ADD_PROVINCE",
       t1."RESIDENT_ADD_CITY",
       t1."RESIDENT_ADDRES_SP",
       t1."WORK_TYPE_SP",
       t1."INDUSTRY_SP",
       t1."INDUSTRY_WORK_EXPERIENCE_SP",
       t1."WORK_UNIT_NAME_SP",
       t1."WORK_UNIT_PHONE_SP",
       t1."WORK_PROVINCE_SP",
       t1."WORK_CITY_SP",
       t1."WORK_UNIT_ADDRESS_SP",
       t1."POSITION_SP",
       t1."SOURCE_OF_INCOME_SP",
       t1."ANNUAL_INCOME_SP",
       t1."OTHER_ANNUAL_INCOME_SP",
       t1."RELATIONSHIP_SP",
       t1."BANK_ID",
       t1."BANK_ACCOUNT_NAME",
       t1."BANK_ACCOUNT_NUM",
       t1."INVOICE_BP_ADDRESS_PHONE_NUM",
       t1."INVOICE_BP_BANK_ACCOUNT",
       t1."INVOICE_TITLE",
       t1."INVOICE_SEND_ADDRESS",
       t1."BP_NAME_LEG",
       t1."ID_CARD_NO_LEG",
       t1."GENDER_LEG",
       t1."DATE_OF_BIRTH_LEG",
       t1."AGE_LEG",
       t1."MARITAL_STATUS_LEG",
       t1."ACADEMIC_BACKGROUND_LEG",
       t1."YEARS_OF_LIVING_HOUSE_LEG",
       t1."RESIDENT_STATUS_LEG",
       t1."CELL_PHONE_LEG",
       t1."EMAIL_LEG",
       t1."START_WORK_LEG",
       t1."LIV_PROVINCE_LEG",
       t1."LIV_CITY_LEG",
       t1."LIV_DISTRICT_LEG",
       t1."LIV_STREET_LEG",
       t1."NET_MONTHLY_INCOME_LEG",
       t1."OWNERSHIP_OF_HOUSE_LEG",
       t1."OTHER_ASSET_LEG",
       t1."CAPITAL_OF_FAMILY_LEG",
       t1."LIABILITY_OF_FAMILY_LEG",
       t1."AGENT_TYPE",
       t1."CREATED_BY",
       t1."CREATION_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",
       t1."REF_N01",
       t1."REF_N02",
       t1."REF_N03",
       t1."REF_N04",
       t1."REF_N05",
       t1."REF_N06",
       t1."REF_N07",
       t1."REF_N10",
       t1."REF_D01",
       t1."REF_D02",
       t1."REF_D03",
       t1."REF_D04",
       t1."REF_D05",
       t1."NET_MONTHLY_INCOME",
       t1."YEARS_OF_LIVING_HOUSE",
       t1."TOTAL_ASSETS",
       t1."COMPANY_ID",
       t1."WORK_DISTRICT",
       t1."WOKR_STREET",
       t1."NAME_LEGAL",
       t1."WORK_STREET",
       t1."ID_TYPE_LEG",
       t1."NET_MONTHLY_INCOME_GUA",
       t1."REGISTERED_CAPITAL_ORG",
       (select scv.code_value_name
          from sys_code_values_v scv
         where scv.code = 'SYS_SEX_TYPE'
           and scv.code_value = t1.gender) gender_n,
       (select scv.code_value_name
          from sys_code_values_v scv
         where scv.code = 'HLS_CUS_MARRIAGE_STATUS'
           and scv.code_value = t1.marital_status) marital_status_n,
       (select scv.code_value_name
          from sys_code_values_v scv
         where scv.code = 'HLS_SYS_EDUCATIONAL'
           and scv.code_value = t1.academic_background) academic_background_n,
       (select scv.code_value_name
          from sys_code_values_v scv
         where scv.code = 'HLS_SYS_HOUSEHOLD_CODE'
           and scv.code_value = t1.RESIDENT_STATUS) RESIDENT_STATUS_n,
       (select scv.code_value_name
          from sys_code_values_v scv
         where scv.code = 'HLS_CUS_HEALTH_STATUS'
           and scv.code_value = t1.STATE_OF_HEALTH) STATE_OF_HEALTH_n,

       (select fp.description
          from fnd_province fp
         where fp.province_id = t1.Liv_Province) Liv_Province_n,
       (select fc.description
          from fnd_city fc
         where fc.city_id = t1.liv_city) liv_city_n,
       (select fd.description
          from fnd_district fD
         where fD.District_Id = t1.LIV_DISTRICT) LIV_DISTRICT_n,
       (select scv.code_value_name
          from sys_code_values_v scv
         where scv.code = 'HLS_CUS_WORK_TYPE'
           and scv.code_value = t1.work_type) work_type_n,
       (select scv.code_value_name
          from sys_code_values_v scv
         where scv.code = 'WORK_UNIT'
           and scv.code_value = t1.WORK_UNIT) WORK_UNIT_n,
       (select scv.code_value_name
          from sys_code_values_v scv
         where scv.code = 'HLS_SYS_LEGAL_UNIT_PROPERTY'
           and scv.code_value = t1.COMPANY_NATURE) COMPANY_NATURE_n,
       (select scv.code_value_name
          from sys_code_values_v scv
         where scv.code = 'HLS_CUS_WORKING_AGE_TYPE'
           and scv.code_value = t1.INDUSTRY_WORK_EXPER) INDUSTRY_WORK_EXPER_n,
       (select scv.code_value_name
          from sys_code_values_v scv
         where scv.code = 'HLS_CUS_HOUSE_OWNERSHIP'
           and scv.code_value = t1.OWNSHIP_OF_HOUSE) OWNSHIP_OF_HOUSE_n,
       (select scv.code_value_name
          from sys_code_values_v scv
         where scv.code = 'SYS_SEX_TYPE'
           and scv.code_value = t1.GENDER_SP) GENDER_SP_n,
       (select scv.code_value_name
          from sys_code_values_v scv
         where scv.code = 'HLS_CUS_WORK_TYPE'
           and scv.code_value = t1.WORK_TYPE_SP) WORK_TYPE_SP_n,
       (select scv.code_value_name
          from sys_code_values_v scv
         where scv.code = 'HLS_CUS_LIVE_DEADLINE'
           and scv.code_value = t1.years_of_living_house) years_of_living_house_n,
       (select scv.code_value_name
          from sys_code_values_v scv
         where scv.code = 'HLS_CUS_LIVE_DEADLINE'
           and scv.code_value = t1.years_of_living_house_leg) years_of_living_house_leg_n,
       (select scv.code_value_name
          from sys_code_values_v scv
         where scv.code = 'HLS211_ID_TYPE'
           and scv.code_value = t1.id_type) id_type_n,
       (select scv.code_value_name
          from sys_code_values_v scv
         where scv.code = 'HLS211_ID_TYPE'
           and scv.code_value = t1.card_type_sp) card_type_sp_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'HLS_SYS_UNIT_PROPERTY'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.legal_form) legal_form_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'ENTERPRISES_SCALE'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.enterprise_scale) enterprise_scale_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'INDUSTRY'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.industry) industry_n,
       (select p.description value_name
          FROM fnd_province p
         where p.province_id = t1.work_province) work_province_n,
       (select f.description value_name
          from fnd_city f
         where f.enabled_flag = 'Y'
           and f.city_id = t1.work_city) work_city_n,
       (select fd.description value_name
          from fnd_district fd
         where fd.enabled_flag = 'Y'
           and fd.district_id = t1.work_district) work_district_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'HLS_CUS_STOCKHOLDER_BACKGROUND'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.shareholders_background) shareholders_background_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'HLS_PRJ_LEASE_ASSET_RATE'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.foa_rate) foa_rate_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'HLS_PRJ_GUARANT_ASSET_RATE'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.porportion_of_guarantee) porportion_of_guarantee_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'HLS_CUS_FIN_PROFIT_CONDITION'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.earnings_condition) earnings_condition_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'HLS_CUS_MAIN_GROWTH'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.main_business_growth) main_business_growth_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'HLS_CUS_RIGHTS_NET_RATE'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.roe) roe_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'HLS_CUS_CURRENT_RATIO'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.current_ratio) current_ratio_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'HLS_CUS_INTEREST_SAFEGUARD'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.interest_cover_ratio) interest_cover_ratio_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'HLS_CUS_ASSET_RATIO'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.debt_to_asset_ratio) debt_to_asset_ratio_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'SYS_SEX_TYPE'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.gender_leg) gender_leg_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'HLS_CUS_MARRIAGE_STATUS'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.marital_status_leg) marital_status_leg_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'HLS_SYS_EDUCATIONAL'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.academic_background_leg) academic_background_leg_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'HLS_SYS_HOUSEHOLD_CODE'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.resident_status_leg) resident_status_leg_n,
       (select p.description value_name
          FROM fnd_province p
         where p.province_id = t1.liv_province_leg) liv_province_leg_n,
       (select f.description value_name
          from fnd_city f
         where f.enabled_flag = 'Y'
           and f.city_id = t1.liv_city_leg) liv_city_leg_n,
       (select fd.description as value_name
          from fnd_district fd
         where fd.enabled_flag = 'Y'
           and fd.district_id = t1.liv_district_leg) liv_district_leg_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'HLS_CUS_LEGAL_INCOME_TYPE'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.net_monthly_income_leg) net_monthly_income_leg_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'HLS_CUS_HOUSE_OWNERSHIP'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.ownership_of_house_leg) ownership_of_house_leg_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'HLS_CUS_FAMILY_ASSET'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.capital_of_family_leg) capital_of_family_leg_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'HLS_CUS_FAMILY_ASSET'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.liability_of_family_leg) liability_of_family_leg_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'HLS211_ID_TYPE'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.id_type_leg) id_type_leg_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'HLS_CUS_FAMILY_ASSET'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.registered_capital) registered_capital_n,
       (select v.code_value_name value_name
          from sys_code_values_v v
         where v.code = 'HLS211_TAXPAYER_TYPE'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.taxpayer_type) taxpayer_type_n,
       (select code_value_name
          from sys_code_values_v scv
         where scv.code = 'HLS_CUS_LEGAL_INCOME_TYPE'
           and scv.code_value = t1.net_monthly_income_gua) net_monthly_income_gua_n,
       t1.driving_flag,
       (select code_value_name
          from sys_code_values_v scv
         where scv.code = 'YES_NO'
           and scv.code_value = t1.driving_flag) driving_flag_n,
       t1.driving_provement,
       --add by Harry 9952 2017/3/1
       t1.credit_bank,
       (select v.code_value_name
          from sys_code_values_v v
         where v.code = 'CREDIT_BANK'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = t1.credit_bank) credit_bank_n, --征信查询银行
       (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 cc.bp_name from hls_bp_master cc where cc.bp_id = t1.bp_id) bp_name_n,
        (select cc.bp_name from hls_bp_master cc where cc.bp_id = t1.bp_id) bp_id_n,
        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,
        t1.house_area,
        t1.house_property,
        t1.mortgage_state,
        t1.other_property,
        t1.owner,
        t1.ownership_nature,
        t1.present_valuation,
        t1.property_address,
        t1.property_status,
        t1.time_buying,
        t1.bs_level,
        t1.score_level,
        t1.score_result,
        t1.funnel_score,
        t1.pay_method,
        (select v.code_value_name  from sys_code_values_v v where v.code = 'DS_PAYMENT_METHOD'
        and v.code_value =  t1.pay_method
         ) pay_method_n,
         (select  hb.description  from hls_bp_category  hb  where  hb.bp_category=t1.bp_category)  bp_category_n
  from prj_project_bp t1,prj_project t2
 WHERE T1.BP_CATEGORY = 'TENANT'
 and t1.bp_id = t2.bp_id_tenant
 and t1.project_id = t2.project_id
;