con_contract_sign_lv.sql 8.32 KB
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
;