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
;