con_contract_sign_lv.sql 8.32 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 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214
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
;