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 ;