create or replace view con_contract_sign_lv as Select t1.data_class, (Select sc.code_value_name From sys_code_values_v sc Where sc.code = 'HLS_DATA_CLASS' And sc.code_value = t1.data_class) data_class_n, t1.contract_id, t1.calc_session_id, t1.contract_number, t1.contract_name, t1.business_type, t1.district, t1.finance_amount, --融资金额 (Select p.province From prj_project p Where p.project_id = t1.project_id) As province, (Select tg.description From fnd_province tg Where tg.province_id = (Select p.province From prj_project p Where p.project_id = t1.project_id)) As province_n, --省 (Select p.city From prj_project p Where p.project_id = t1.project_id) As city, (Select tc.description From fnd_city tc Where tc.city_id = (Select p.city From prj_project p Where p.project_id = t1.project_id)) As city_n, --市 t1.departing_date, t1.departed_date, t1.depart_status, --li.item_frame_number, (Select v.code_value_name From sys_code_values_v v Where v.code = 'DEPART_STATUS' And v.code_value = t1.depart_status) As depart_status_n, (Select h1.code_value_name From sys_code_values_v h1 Where h1.code = 'PRJ500N_DISTRICT_AREAS' And t1.district = h1.code_value) district_n, (Select bt.description From hls_business_type bt Where bt.business_type = t1.business_type) As business_type_n, t1.document_type, (Select p.document_type From prj_project p Where p.project_id = t1.project_id) As prj_document_type, (Select dt.description From hls_document_type dt Where dt.document_type = t1.document_type) As document_type_n, t1.document_category, (Select dc.description From hls_document_category dc Where dc.document_category = t1.document_category) As document_category_n, t1.project_id, (Select p.project_number From prj_project p Where t1.project_id = p.project_id) As project_id_c, (Select p.project_name From prj_project p Where t1.project_id = p.project_id) As project_id_n, t1.company_id, (Select c.company_short_name From fnd_companies_vl c Where c.company_id = t1.company_id) As company_id_n, t1.spv_company_id, (Select c.company_code From fnd_companies_vl c Where c.company_id = t1.spv_company_id) As spv_company_code, (Select c.company_short_name From fnd_companies_vl c Where c.company_id = t1.spv_company_id) As spv_company_id_n, t1.lease_organization, (Select o.description From hls_lease_organization o Where o.lease_organization = t1.lease_organization) As lease_organization_n, t1.lease_channel, (Select p.lease_channel From prj_project p Where p.project_id = t1.project_id) As prj_lease_channel, (Select p.special_permit_flag From prj_project p Where p.project_id = t1.project_id) As special_permit_flag, (Select ch.description From hls_lease_channel ch Where ch.lease_channel = t1.lease_channel) As lease_channel_n, t1.division, (Select d.description From hls_division d Where d.division = t1.division) As division_n, t1.bp_id_tenant, (Select m.bp_code From hls_bp_master m Where m.bp_id = t1.bp_id_tenant) As bp_code, (Select m.bp_name From hls_bp_master m Where m.bp_id = t1.bp_id_tenant) As bp_id_tenant_n, (Select m.bp_name From hls_bp_master m Where m.bp_id = t1.bp_id_tenant) As bp_name, (Select ma.bp_name From hls_bp_master ma Where ma.bp_id = t1.bp_id_tenant) bp_id_tenant_name, t1.bp_id_agent_level1, (Select m.bp_name From hls_bp_master m Where m.bp_id = t1.bp_id_agent_level1) As bp_id_agent_level1_n, t1.bp_id_agent_level2, (Select m.bp_code From hls_bp_master m Where m.bp_id = t1.bp_id_agent_level2) As bp_id_agent_level2_n, t1.bp_id_agent_level3, (Select m.bp_code From hls_bp_master m Where m.bp_id = t1.bp_id_agent_level3) As bp_id_agent_level3_n, t1.owner_user_id, (Select a.description From sys_user a Where a.user_id = t1.owner_user_id) owner_user_id_n, t1.employee_id, (Select e.name From exp_employees e Where e.employee_id = t1.employee_id) As employee_id_n, t1.unit_id, (Select h.bp_name From hls_bp_master h Where h.bp_id = t1.unit_id) As unit_id_n, t1.employee_id_of_manager, (Select e.name From exp_employees e Where e.employee_id = t1.employee_id_of_manager) As employee_id_of_manager_n, t1.factoring_type, t1.description, t1.price_list, (Select l.description From hls_price_list l Where l.price_list = t1.price_list) As price_list_n, t1.calc_method, to_date(to_char(t1.inception_of_lease, 'yyyy-mm-dd'), 'yyyy-mm-dd') inception_of_lease, --起租日 t1.contract_status, ------------------------ (Select v.code_value_name From sys_code_values_v v Where v.code = 'CON500_CONTRACT_STATUS' And v.code_value = t1.contract_status) As contract_status_n, ---------------------- t1.con_maintain_flag, ------------------------ (Select a.project_number From prj_project a Where a.project_id = t1.project_id) project_number, (Select a.document_type From prj_project a Where a.project_id = t1.project_id) project_document_type, ----------------- (Select bm.bp_class From hls_bp_master bm Where bm.bp_id = t1.bp_id_tenant) bp_class, ----------------- (Select bm.bp_class_n From hls_bp_master_lv bm Where bm.bp_id = t1.bp_id_tenant) bp_class_n, ------------------- Null version_number, (Select su.description From sys_user su Where su.user_id = t1.owner_user_id) owner, -- 单据所有者 -- t1.cancel_reason, --合同取消原因 t1.closed_date, -- 合同取消日期---------------------------------- (Select to_date(to_char(pp.create_con_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') From prj_project pp Where pp.project_id = t1.project_id) contract_creation_date, -- 项目创建合同时间-------------------------------- t1.wfl_instance_id, -------------------------- (Select to_date(to_char(zi.creation_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') From zj_wfl_workflow_instance zi Where zi.instance_id = t1.wfl_instance_id) submit_date_detail, --------------------------- nvl(t1.archive_status, '10') archive_status, ------------------- nvl((Select v.code_value_name As value_name From sys_code_values_v v Where v.code = 'ARCHIVE_STATUS' And v.code_value = t1.archive_status), '未归档') archive_status_n, ------------------------ --add by chenlingfeng (Select hbm.large_area From hls_bp_master hbm Where hbm.bp_id = t1.bp_id_tenant) large_area, --end t1.content_type, (Select v.code_value_name From sys_code_values_v v Where v.code = 'CON1010_CONTENT_TYPE' And v.code_value = t1.content_type) content_type_n, t1.sys_level, t1.bs_level, t1.lease_execution_date,--执行日期 (Select m.extra_nam From hls_bp_master m Where m.bp_id = t1.bp_id_agent_level1) As extra_nam, t1.wfl_contract_status, (Select v.code_value_name From sys_code_values_v v Where v.code = 'HLS303_WFL_STATUS' And v.code_value = t1.wfl_contract_status and v.code_enabled_flag='Y' and v.code_value_enabled_flag='Y') wfl_contract_status_n From con_contract t1 Where t1.data_class = 'NORMAL' --t1.contract_status In ('NEW','SIGN','INCEPT') -- And order by t1.creation_date desc ;