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
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" alias="t1" baseTable="CON_CONTRACT_FILE">
<bm:fields>
<bm:field name="contract_id"/>
<bm:field name="pk"/>
<bm:field name="file_code"/>
<bm:field name="file_name"/>
<bm:field name="save_table"/>
<bm:field name="save_table_pk"/>
<bm:field name="attach_file_name"/>
<bm:field name="atm_count"/>
<bm:field name="note"/>
</bm:fields>
<bm:operations>
<bm:operation name="query">
<bm:query-sql><![CDATA[
Select v.*, cn.note
From (Select to_number(${@contract_id}) contract_id,
t1.cdd_item_id pk,
t1.cdd_item file_code,
t1.description file_name,
'PRJ_CDD_ITEM_CHECK' save_table,
t2.check_id save_table_pk,
hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => t2.check_id,
p_source_type => 'PRJ_CDD_ITEM_CHECK',
p_user_id => 1) As attach_file_name,
(ds_common_get_data_pkg.get_atm_counts(p_table_name => 'PRJ_CDD_ITEM_CHECK',
p_table_pk_value => t2.check_id)) As atm_count
From prj_cdd_item t1, prj_cdd_item_check t2
Where t1.cdd_item_id = t2.cdd_item_id
And t1.cdd_list_id =
(Select cc.cdd_list_id
From con_contract cc
Where cc.data_class = 'NORMAL'
And cc.contract_id = ${@contract_id}) --合同cdd_list_id 信审阶段上传
Union All
Select to_number(${@contract_id}) contract_id,
t3.contract_attch_id pk,
'' file_code,
'承租人变更附件' file_name,
'CCR_UNIVERSAL_LEASSE_ATTH' save_table,
t3.contract_attch_id save_table_pk,
hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => t3.contract_attch_id,
p_source_type => 'CCR_UNIVERSAL_LEASSE_ATTH',
p_user_id => 1) As attach_file_name,
(ds_common_get_data_pkg.get_atm_counts(p_table_name => 'CCR_UNIVERSAL_LEASSE_ATTH',
p_table_pk_value => t3.contract_attch_id)) As atm_count
From ccr_universal_leasse_atth t3
Where t3.contract_id In
(Select ccr.change_req_id
From con_contract_change_req ccr
Where ccr.contract_id = ${@contract_id}
And ccr.req_status = 'APPROVED'
And ccr.document_type = 'LEASSE_CHAG') ----合同变更申请-承租人变更
Union All
Select to_number(${@contract_id}) contract_id,
t4.contract_attch_id pk,
'' file_code,
'冬雨季延期附件' file_name,
'CCR_UNIVERSAL_E_ATTH' save_table,
t4.contract_attch_id save_table_pk,
hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => t4.contract_attch_id,
p_source_type => 'CCR_UNIVERSAL_E_ATTH',
p_user_id => 1) As attach_file_name,
(ds_common_get_data_pkg.get_atm_counts(p_table_name => 'CCR_UNIVERSAL_E_ATTH',
p_table_pk_value => t4.contract_attch_id)) As atm_count
From ccr_universal_e_atth t4
Where t4.contract_id In
(Select ccr.change_req_id
From con_contract_change_req ccr
Where ccr.contract_id = ${@contract_id}
And ccr.req_status = 'APPROVED'
And ccr.document_type = 'LEASSE_CHAG') -----合同变更申请-冬雨季延期
Union All
Select to_number(${@contract_id}) contract_id,
t5.contract_attch_id pk,
'' file_code,
'非月付转月付附件' file_name,
'CCR_UNIVERSAL_PAY_ATTH' save_table,
t5.contract_attch_id save_table_pk,
hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => t5.contract_attch_id,
p_source_type => 'CCR_UNIVERSAL_PAY_ATTH',
p_user_id => 1) As attach_file_name,
(ds_common_get_data_pkg.get_atm_counts(p_table_name => 'CCR_UNIVERSAL_PAY_ATTH',
p_table_pk_value => t5.contract_attch_id)) As atm_count
From ccr_universal_pay_atth t5
Where t5.contract_id In
(Select ccr.change_req_id
From con_contract_change_req ccr
Where ccr.contract_id = ${@contract_id}
And ccr.req_status = 'APPROVED'
And ccr.document_type = 'PAY_CHAG') ----合同变更申请-非月付转月付
Union All
Select to_number(${@contract_id}) contract_id,
t6.contract_attch_id pk,
'' file_code,
'租赁物变更附件' file_name,
'CCR_UNIVERSAL_LH_ATTH' save_table,
t6.contract_attch_id save_table_pk,
hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => t6.contract_attch_id,
p_source_type => 'CCR_UNIVERSAL_LH_ATTH',
p_user_id => 1) As attach_file_name,
(ds_common_get_data_pkg.get_atm_counts(p_table_name => 'CCR_UNIVERSAL_LH_ATTH',
p_table_pk_value => t6.contract_attch_id)) As atm_count
From ccr_universal_lh_atth t6
Where t6.contract_id In
(Select ccr.change_req_id
From con_contract_change_req ccr
Where ccr.contract_id = ${@contract_id}
And ccr.req_status = 'APPROVED'
And ccr.document_type = 'LEASEHOLD_CHAG') --租赁物变更
Union All
Select to_number(${@contract_id}) contract_id,
t7.contract_attch_id pk,
'' file_code,
'债权债务变更附件' file_name,
'CCR_UNIVERSAL_DEPT_ATTH' save_table,
t7.contract_attch_id save_table_pk,
hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => t7.contract_attch_id,
p_source_type => 'CCR_UNIVERSAL_DEPT_ATTH',
p_user_id => 1) As attach_file_name,
(ds_common_get_data_pkg.get_atm_counts(p_table_name => 'CCR_UNIVERSAL_DEPT_ATTH',
p_table_pk_value => t7.contract_attch_id)) As atm_count
From ccr_universal_dept_atth t7
Where t7.contract_id In
(Select ccr.change_req_id
From con_contract_change_req ccr
Where ccr.contract_id = ${@contract_id}
And ccr.req_status = 'APPROVED'
And ccr.document_type = 'DEBT_CHAG') --债权债务变更
Union All
Select to_number(${@contract_id}) contract_id,
t8.contract_attch_id pk,
'' file_code,
'部分变更附件' file_name,
'CCR_UNIVERSAL_PART_ATTH' save_table,
t8.contract_attch_id save_table_pk,
hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => t8.contract_attch_id,
p_source_type => 'CCR_UNIVERSAL_PART_ATTH',
p_user_id => 1) As attach_file_name,
(ds_common_get_data_pkg.get_atm_counts(p_table_name => 'CCR_UNIVERSAL_PART_ATTH',
p_table_pk_value => t8.contract_attch_id)) As atm_count
From ccr_universal_part_atth t8
Where t8.contract_id In
(Select ccr.change_req_id
From con_contract_change_req ccr
Where ccr.contract_id = ${@contract_id}
And ccr.req_status = 'APPROVED'
And ccr.document_type = 'PART_CHAG') --合同变更申请-部分变更
Union All
Select to_number(${@contract_id}) contract_id,
t9.contract_id pk,
'' file_code,
'保单附件' file_name,
'CONTRACT_INSURANCE' save_table,
t9.contract_id save_table_pk,
hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => t9.contract_id,
p_source_type => 'CONTRACT_INSURANCE',
p_user_id => 1) As attach_file_name,
(ds_common_get_data_pkg.get_atm_counts(p_table_name => 'CONTRACT_INSURANCE',
p_table_pk_value => t9.contract_id)) As atm_count
From contract_insurance_lv t9
Where t9.contract_id = ${@contract_id} --保单附件
Union All
select t10.contract_id,
t10.content_id pk,
'CON_CONTRACT_CONTENT' file_code,
t10.content_number file_name,
'CON_CONTRACT_CONTENT' save_table,
t10.content_id save_table_pk,
hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => t10.content_id,
p_source_type => 'CON_CONTRACT_CONTENT',
p_user_id => 1) As attach_file_name,
(ds_common_get_data_pkg.get_atm_counts(p_table_name => 'CON_CONTRACT_CONTENT',
p_table_pk_value => t10.content_id)) As atm_count
from con_contract_content t10
WHERE t10.contract_id = ${@contract_id}
Union All
select t11.contract_id,
t11.content_id pk,
'CON_CONTRACT_CONTENT_SIGN_ATTCH' file_code,
t11.content_number||'扫描件' file_name,
'CON_CONTRACT_CONTENT_SIGN_ATTCH' save_table,
t11.content_id save_table_pk,
hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => t11.content_id,
p_source_type => 'CON_CONTRACT_CONTENT_SIGN_ATTCH',
p_user_id => 1) As attach_file_name,
(ds_common_get_data_pkg.get_atm_counts(p_table_name => 'CON_CONTRACT_CONTENT_SIGN_ATTCH',
p_table_pk_value => t11.content_id)) As atm_count
from con_contract_content t11
WHERE t11.content_print_flag = 'Y'
and t11.contract_id = ${@contract_id}
union all
Select t12.contract_id contract_id,
t12.invoice_line_id pk,
'ACP_INVOICE_LN' file_code,
'进项发票' file_name,
'ACP_INVOICE_LN' save_table,
t12.invoice_line_id save_table_pk,
hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => t12.invoice_line_id,
p_source_type => 'ACP_INVOICE_LN',
p_user_id => 1) As attach_file_name,
(ds_common_get_data_pkg.get_atm_counts(p_table_name => 'ACP_INVOICE_LN',
p_table_pk_value => t12.invoice_line_id)) As atm_count
From ACP_INVOICE_LN t12
Where t12.contract_id = ${@contract_id}
) v,
con_archive_atth_note cn
Where v.contract_id = cn.contract_id(+)
and v.save_table = cn.save_table(+)
And v.save_table_pk = cn.save_table_pk(+)
#ORDER_BY_CLAUSE#
]]></bm:query-sql>
</bm:operation>
<bm:operation name="update">
<bm:update-sql><![CDATA[
begin
con_contract_archive_pkg.save_archive_data(p_contract_id => ${@contract_id},
p_ele_doc_archive_flag => ${@ele_doc_archive_flag},
p_archive_flag => ${@archive_flag},
p_complete_flag => ${@complete_flag},
p_note => ${@note},
p_cert_receive_flag => ${@cert_receive_flag},
p_doc_warehouse => ${@doc_warehouse},
p_doc_cabinet => ${@doc_cabinet},
p_archive_date => to_date(${@archive_date},'yyyy-mm-dd'),
p_doc_layers => ${@doc_layers},
p_doc_order => ${@doc_order},
p_res_doc_cabinet => ${@res_doc_cabinet},
p_res_doc_layers => ${@res_doc_layers},
p_res_doc_order => ${@res_doc_order},
p_user_id => ${/session/@user_id});
end;
]]></bm:update-sql>
</bm:operation>
<bm:operation name="execute">
<bm:update-sql><![CDATA[
begin
con_contract_file_pkg.get_all_contract_file(p_contract_id => ${@contract_id},
p_project_id => '',
p_quotation_id => '',
p_cdd_list_id => ${@cdd_list_id},
p_user_id => ${/session/@user_id});
end;
]]></bm:update-sql>
</bm:operation>
</bm:operations>
</bm:model>