[Home] [Help]
PACKAGE BODY: APPS.OKC_CONTRACT_DOCS_GRP
Source
1 PACKAGE BODY OKC_CONTRACT_DOCS_GRP AS
2 /* $Header: OKCGCONTRACTDOCB.pls 120.3.12010000.2 2008/10/24 08:00:40 ssreekum ship $ */
3
4
5 ---------------------------------------------------------------------------
6 -- GLOBAL MESSAGE CONSTANTS
7 ---------------------------------------------------------------------------
8 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
9 ---------------------------------------------------------------------------
10 -- GLOBAL VARIABLES
11 ---------------------------------------------------------------------------
12
13
14
15 ------------------------------------------------------------------------------
16 -- GLOBAL CONSTANTS
17 ------------------------------------------------------------------------------
18 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
19 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
20
21 G_RET_STS_SUCCESS CONSTANT varchar2(1) := FND_API.G_RET_STS_SUCCESS;
22 G_RET_STS_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_ERROR;
23 G_RET_STS_UNEXP_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
24
25 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
26 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
27 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
28 G_CONTRACT_DOC_CATEGORY CONSTANT VARCHAR2(200) := 'OKC_REPO_CONTRACT';
29 G_CONTRACT_IMAGE_CATEGORY CONSTANT VARCHAR2(200) := 'OKC_REPO_CONTRACT_IMAGE';
30 G_APP_ABSTRACT_CATEGORY CONSTANT VARCHAR2(200) := 'OKC_REPO_APP_ABSTRACT';
31 G_SUPPORTING_DOC_CATEGORY CONSTANT VARCHAR2(200) := 'OKC_REPO_SUPPORTING_DOC';
32 G_PKG_NAME CONSTANT VARCHAR2(40) := 'OKC_CONTRACT_DOCS_GRP';
33 G_MODULE CONSTANT VARCHAR2(200) := 'okc.plsql.'||G_PKG_NAME ||'.';
34 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
35 G_COPY_ALL_DOCS CONSTANT VARCHAR2(30) := 'OKC_REP_COPY_ALL_CON_DOCS';
36
37
38 G_QA_STS_SUCCESS CONSTANT varchar2(1) := 'S';
39 G_QA_STS_ERROR CONSTANT varchar2(1) := 'E';
40 G_QA_STS_WARNING CONSTANT varchar2(1) := 'W';
41
42 -------------------------------------
43 -- PROCEDURE Insert_Contract_Doc
44 -------------------------------------
45 PROCEDURE Insert_Contract_Doc(
46 p_api_version IN NUMBER,
47 p_init_msg_list IN VARCHAR2 ,
48 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
49 p_commit IN VARCHAR2 := FND_API.G_FALSE,
50
51 x_return_status OUT NOCOPY VARCHAR2,
52 x_msg_count OUT NOCOPY NUMBER,
53 x_msg_data OUT NOCOPY VARCHAR2,
54
55 p_business_document_type IN VARCHAR2,
56 p_business_document_id IN NUMBER,
57 p_business_document_version IN NUMBER,
58 p_attached_document_id IN NUMBER,
59 p_external_visibility_flag IN VARCHAR2,
60 p_effective_from_type IN VARCHAR2,
61 p_effective_from_id IN NUMBER,
62 p_effective_from_version IN NUMBER,
63 p_include_for_approval_flag IN VARCHAR2 := 'N',
64 p_create_fnd_attach IN VARCHAR2 := 'Y',
65 p_program_id IN NUMBER,
66 p_program_application_id IN NUMBER,
67 p_request_id IN NUMBER,
68 p_program_update_date IN DATE,
69 p_parent_attached_doc_id IN NUMBER := NULL,
70 p_generated_flag IN VARCHAR2 := 'N',
71 p_delete_flag IN VARCHAR2 := 'N',
72
73 p_primary_contract_doc_flag IN VARCHAR2 := 'N',
74 p_mergeable_doc_flag IN VARCHAR2 := 'N',
75 p_versioning_flag IN VARCHAR2 := 'N',
76
77 x_business_document_type OUT NOCOPY VARCHAR2,
78 x_business_document_id OUT NOCOPY NUMBER,
79 x_business_document_version OUT NOCOPY NUMBER,
80 x_attached_document_id OUT NOCOPY NUMBER
81
82 ) IS
83
84 l_api_version CONSTANT NUMBER := 1;
85 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Contract_Doc';
86 l_object_version_number OKC_CONTRACT_DOCS.OBJECT_VERSION_NUMBER%TYPE := 1;
87 l_created_by OKC_CONTRACT_DOCS.CREATED_BY%TYPE;
88 l_creation_date OKC_CONTRACT_DOCS.CREATION_DATE%TYPE;
89 l_last_updated_by OKC_CONTRACT_DOCS.LAST_UPDATED_BY%TYPE;
90 l_last_update_login OKC_CONTRACT_DOCS.LAST_UPDATE_LOGIN%TYPE;
91 l_last_update_date OKC_CONTRACT_DOCS.LAST_UPDATE_DATE%TYPE;
92 l_attached_document_id OKC_CONTRACT_DOCS.ATTACHED_DOCUMENT_ID%TYPE := p_attached_document_id;
93 l_media_id FND_DOCUMENTS.MEDIA_ID%TYPE;
94 l_pk2_value FND_ATTACHED_DOCUMENTS.pk2_value%TYPE;
95
96
97 -- The following variables are required for updating the pk3_value in fnd_attached_documents table
98 l_rowid VARCHAR2(120);
99 l_document_id FND_DOCUMENTS.DOCUMENT_ID%TYPE;
100 l_new_attachment_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
101 l_parent_attached_doc_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE := p_parent_attached_doc_id;
102 l_seq_num FND_ATTACHED_DOCUMENTS.SEQ_NUM%TYPE := 0;
103
104 CURSOR l_attachment_id_csr IS
105 SELECT fnd_attached_documents_s.nextval FROM dual;
106
107 CURSOR seq_csr(l_entity_name VARCHAR2, l_pk1_value VARCHAR2,
108 l_pk2_value NUMBER, l_pk3_value NUMBER) IS
109 SELECT max(seq_num) s_num
110 FROM FND_ATTACHED_DOCUMENTS
111 WHERE entity_name = l_entity_name
112 AND pk1_value = l_pk1_value
113 AND pk2_value = to_char(l_pk2_value)
114 AND pk3_value = to_char(l_pk3_value);
115
116
117
118 BEGIN
119
120 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
121 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
122 '600: Entered Insert_Contract_Doc');
123 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
124 '601: p_business_document_type : ' || p_business_document_type );
125 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
126 '602: p_business_document_id : ' || p_business_document_id );
127 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
128 '603: p_business_document_version : ' || p_business_document_version );
129 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
130 '604: p_attached_document_id : ' || p_attached_document_id );
131 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
132 '605: p_external_visibility_flag : ' || p_external_visibility_flag );
133 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
134 '606: p_effective_from_type : ' || p_effective_from_type );
135 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
136 '607: p_effective_from_id : ' || p_effective_from_id );
137 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
138 '608: p_effective_from_version : ' || p_effective_from_version );
139 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
140 '609: p_include_for_approval_flag : ' || p_include_for_approval_flag );
141 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
142 '610: p_create_fnd_attach : ' || p_create_fnd_attach );
143 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
144 '611: p_program_id : ' || p_program_id );
145 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
146 '612: p_program_application_id : ' || p_program_application_id );
147 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
148 '613: p_request_id : ' || p_request_id );
149 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
150 '614: p_program_update_date : ' || p_program_update_date );
151 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
152 '615: p_parent_attached_doc_id : ' || p_parent_attached_doc_id );
153 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
154 '616: p_generated_flag : ' || p_generated_flag );
155 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
156 '617: p_delete_flag : ' || p_delete_flag );
157 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
158 '618: p_primary_contract_doc_flag : ' || p_primary_contract_doc_flag );
159 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
160 '619: p_mergeable_doc_flag : ' || p_mergeable_doc_flag );
161 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
162 '620: p_versioning_flag : ' || p_versioning_flag );
163 END IF;
164
165 -- Standard Start of API savepoint
166 SAVEPOINT g_Insert_Contract_Doc_GRP;
167 -- Standard call to check for call compatibility.
168 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
169 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
170 END IF;
171 -- Initialize message list if p_init_msg_list is set to TRUE.
172 IF FND_API.to_Boolean( p_init_msg_list ) THEN
173 FND_MSG_PUB.initialize;
174 END IF;
175 -- Initialize API return status to success
176 x_return_status := FND_API.G_RET_STS_SUCCESS;
177
178 -- Do Not Update Primary Flag when Copying or Versioning
179 IF p_primary_contract_doc_flag = 'Y' AND p_versioning_flag = 'N'
180 THEN
181 Clear_Primary_Doc_Flag(
182 p_document_type => p_business_document_type,
183 p_document_id => p_business_document_id,
184 x_return_status => x_return_status);
185 END IF;
186 --------------------------------------------
187 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
189 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
190 RAISE FND_API.G_EXC_ERROR ;
191 END IF;
192 --------------------------------------------
193
194 -- Set the value for l_parent_attached_doc_id.
195 IF (p_parent_attached_doc_id IS NULL) THEN
196 IF (p_business_document_version <> p_effective_from_version) THEN
197 l_parent_attached_doc_id := p_attached_document_id;
198 END IF;
199 END IF;
200
201 l_creation_date := Sysdate;
202 l_created_by := Fnd_Global.User_Id;
203 l_last_update_date := l_creation_date;
204 l_last_updated_by := l_created_by;
205 l_last_update_login := Fnd_Global.Login_Id;
206
207 -- ADD LOGIC FOR CREATING RECORDS IN FND_ATTACHED_DOCUMENT table
208 IF (p_create_fnd_attach = 'Y') THEN
209
210 --Fetch new attachment id.
211 OPEN l_attachment_id_csr;
212 FETCH l_attachment_id_csr INTO l_new_attachment_id;
213 IF (l_attachment_id_csr%notfound) THEN
214 RAISE NO_DATA_FOUND;
215 END IF;
216 CLOSE l_attachment_id_csr;
217
218
219 -- Get document_id from FND_ATTACHED_DOCUMENTS table
220 SELECT document_id INTO l_document_id
221 FROM FND_ATTACHED_DOCUMENTS
222 WHERE attached_document_id = p_attached_document_id;
223
224 -- Get SEQ_NUM for the new record being added
225 FOR seq_rec IN seq_csr(G_ATTACH_ENTITY_NAME, p_business_document_type, p_business_document_id,
226 p_business_document_version)
227 LOOP
228 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
229 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'640: Max seq is: ' || seq_rec.s_num);
230 END IF;
231 l_seq_num := seq_rec.s_num;
232 END LOOP;
233 IF (l_seq_num IS NULL) THEN
234 l_seq_num := 0;
235 END IF;
236
237 -- Add 1 to the max value returned
238 l_seq_num := l_seq_num + 1;
239 -- If we are adding a soft-deleted document, we need to add 'D' to pk2_value
240 IF (p_delete_flag = 'Y') THEN
241 l_pk2_value := to_char(p_business_document_id) || 'D';
242 ELSE
243 l_pk2_value := to_char(p_business_document_id);
244 END IF; -- (delete_flag = 'Y')
245 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
246 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'651: document_id is: ' || l_document_id);
247 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'652: New seq_num is: ' || l_seq_num);
248 END IF;
249 fnd_attached_documents_pkg.insert_row(
250 x_rowid => l_rowid,
251 x_attached_document_id => l_new_attachment_id,
252 x_document_id => l_document_id,
253 x_creation_date => l_creation_date,
254 x_created_by => l_created_by,
255 x_last_update_date => l_last_update_date,
256 x_last_updated_by => l_last_updated_by,
257 x_last_update_login => l_last_update_login,
258 x_seq_num => l_seq_num,
259 x_entity_name => G_ATTACH_ENTITY_NAME,
260 x_column1 => NULL,
261 x_pk1_value => p_business_document_type,
262 x_pk2_value => l_pk2_value,
263 x_pk3_value => to_char(p_business_document_version),
264 x_pk4_value => NULL,
265 x_pk5_value => NULL,
266 x_automatically_added_flag => 'N',
267 x_datatype_id => NULL,
268 x_category_id => NULL,
269 x_security_type => NULL,
270 x_publish_flag => NULL,
271 x_usage_type => NULL,
272 x_language => NULL,
273 x_media_id => l_media_id,
274 x_doc_attribute_category => NULL,
275 x_doc_attribute1 => NULL,
276 x_doc_attribute2 => NULL,
277 x_doc_attribute3 => NULL,
278 x_doc_attribute4 => NULL,
279 x_doc_attribute5 => NULL,
280 x_doc_attribute6 => NULL,
281 x_doc_attribute7 => NULL,
282 x_doc_attribute8 => NULL,
283 x_doc_attribute9 => NULL,
284 x_doc_attribute10 => NULL,
285 x_doc_attribute11 => NULL,
286 x_doc_attribute12 => NULL,
287 x_doc_attribute13 => NULL,
288 x_doc_attribute14 => NULL,
289 x_doc_attribute15 => NULL,
290 X_create_doc => 'N'
291 );
292 l_attached_document_id := l_new_attachment_id;
293
294 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
295 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'653: new_attached_doc_id is: ' || l_new_attachment_id);
296 END IF;
297
298 END IF;
299
300
301 --------------------------------------------
302 -- Calling Simple API for Creating A Row
303 --------------------------------------------
304 OKC_CONTRACT_DOCS_PVT.Insert_Row(
305 p_validation_level => p_validation_level,
306 x_return_status => x_return_status,
307 p_business_document_type => p_business_document_type,
308 p_business_document_id => p_business_document_id,
309 p_business_document_version => p_business_document_version,
310 p_attached_document_id => l_attached_document_id,
311 p_external_visibility_flag => p_external_visibility_flag,
312 p_effective_from_type => p_effective_from_type,
313 p_effective_from_id => p_effective_from_id,
314 p_effective_from_version => p_effective_from_version,
315 p_include_for_approval_flag => p_include_for_approval_flag,
316 p_program_id => p_program_id,
317 p_program_application_id => p_program_application_id,
318 p_request_id => p_request_id,
319 p_program_update_date => p_program_update_date,
320 p_parent_attached_doc_id => l_parent_attached_doc_id,
321 p_generated_flag => p_generated_flag,
322 p_delete_flag => p_delete_flag,
323
324 p_primary_contract_doc_flag => p_primary_contract_doc_flag,
325 p_mergeable_doc_flag => p_mergeable_doc_flag,
326
327 x_business_document_type => x_business_document_type,
328 x_business_document_id => x_business_document_id,
329 x_business_document_version => x_business_document_version,
330 x_attached_document_id => x_attached_document_id
331 );
332 --------------------------------------------
333 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
335 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
336 RAISE FND_API.G_EXC_ERROR ;
337 END IF;
338 --------------------------------------------
339
340
341 -- Standard check of p_commit
342 IF FND_API.To_Boolean( p_commit ) THEN
343 COMMIT WORK;
344 END IF;
345 -- Standard call to get message count and if count is 1, get message info.
346 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
347
348 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
349 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700: Leaving Insert_Contract_Doc');
350 END IF;
351
352 EXCEPTION
353 WHEN FND_API.G_EXC_ERROR THEN
354 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
355 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800: Leaving Insert_Contract_Doc: OKC_API.G_EXCEPTION_ERROR Exception');
356 END IF;
357 IF l_attachment_id_csr%ISOPEN THEN
358 CLOSE l_attachment_id_csr;
359 END IF;
360 ROLLBACK TO g_Insert_Contract_Doc_GRP;
361 x_return_status := G_RET_STS_ERROR ;
362 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
363
364 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
365 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
366 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'900: Leaving Insert_Contract_Doc: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
367 END IF;
368 IF l_attachment_id_csr%ISOPEN THEN
369 CLOSE l_attachment_id_csr;
370 END IF;
371 ROLLBACK TO g_Insert_Contract_Doc_GRP;
372 x_return_status := G_RET_STS_UNEXP_ERROR ;
373 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
374
375 WHEN OTHERS THEN
376 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
377 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1000: Leaving Insert_Contract_Doc because of EXCEPTION: '||sqlerrm);
378 END IF;
379 IF l_attachment_id_csr%ISOPEN THEN
380 CLOSE l_attachment_id_csr;
381 END IF;
382 ROLLBACK TO g_Insert_Contract_Doc_GRP;
383 x_return_status := G_RET_STS_UNEXP_ERROR ;
384 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
385 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
386 END IF;
387 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
388
389 END Insert_Contract_Doc;
390
391
392
393
394
395
396 ---------------------------------------------------------------------------
397 -- PROCEDURE Update_Contract_Doc
398 ---------------------------------------------------------------------------
399 PROCEDURE Update_Contract_Doc(
400 p_api_version IN NUMBER,
401 p_init_msg_list IN VARCHAR2 ,
402 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
403 p_commit IN VARCHAR2 := FND_API.G_FALSE,
404
405 x_return_status OUT NOCOPY VARCHAR2,
406 x_msg_count OUT NOCOPY NUMBER,
407 x_msg_data OUT NOCOPY VARCHAR2,
408
409 p_business_document_type IN VARCHAR2,
410 p_business_document_id IN NUMBER,
411 p_business_document_version IN NUMBER,
412 p_attached_document_id IN NUMBER,
413 p_external_visibility_flag IN VARCHAR2,
414 p_effective_from_type IN VARCHAR2,
415 p_effective_from_id IN NUMBER,
416 p_effective_from_version IN NUMBER,
417 p_include_for_approval_flag IN VARCHAR2 := 'N',
418 p_program_id IN NUMBER,
419 p_program_application_id IN NUMBER,
420 p_request_id IN NUMBER,
421 p_program_update_date IN DATE,
422 p_parent_attached_doc_id IN NUMBER,
423 p_generated_flag IN VARCHAR2 := 'N',
424 p_delete_flag IN VARCHAR2 := 'N',
425 p_primary_contract_doc_flag IN VARCHAR2,
426 p_mergeable_doc_flag IN VARCHAR2,
427 p_object_version_number IN NUMBER,
428 p_versioning_flag IN VARCHAR2 := 'N'
429
430 ) IS
431
432 l_api_version CONSTANT NUMBER := 1;
433 l_api_name CONSTANT VARCHAR2(30) := 'Update_Contract_Doc';
434
435 l_primary_doc_yn VARCHAR2(1);
436
437 CURSOR contract_doc_csr IS
438 SELECT primary_contract_doc_flag
439 FROM OKC_CONTRACT_DOCS
440 WHERE business_document_type = p_business_document_type
441 AND business_document_id = p_business_document_id
442 AND business_document_version = p_business_document_version
443 AND attached_document_id = p_attached_document_id;
444
445
446 BEGIN
447
448 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
449 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
450 '1600: Entered Update_Contract_Doc');
451 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
452 '1601: p_business_document_type : ' || p_business_document_type );
453 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
454 '1602: p_business_document_id : ' || p_business_document_id );
455 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
456 '1603: p_business_document_version : ' || p_business_document_version );
457 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
458 '1604: p_attached_document_id : ' || p_attached_document_id );
459 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
460 '1605: p_external_visibility_flag : ' || p_external_visibility_flag );
461 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
462 '1606: p_effective_from_type : ' || p_effective_from_type );
463 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
464 '1607: p_effective_from_id : ' || p_effective_from_id );
465 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
466 '1608: p_effective_from_version : ' || p_effective_from_version );
467 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
468 '1609: p_include_for_approval_flag : ' || p_include_for_approval_flag );
469 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
470 '1611: p_program_id : ' || p_program_id );
471 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
472 '1612: p_program_application_id : ' || p_program_application_id );
473 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
474 '1613: p_request_id : ' || p_request_id );
475 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
476 '1614: p_program_update_date : ' || p_program_update_date );
477 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
478 '1615: p_parent_attached_doc_id : ' || p_parent_attached_doc_id );
479 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
480 '1616: p_generated_flag : ' || p_generated_flag );
481 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
482 '1617: p_delete_flag : ' || p_delete_flag );
483 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
484 '1618: p_primary_contract_doc_flag : ' || p_primary_contract_doc_flag );
485 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
486 '1619: p_mergeable_doc_flag : ' || p_mergeable_doc_flag );
487 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
488 '1620: p_versioning_flag : ' || p_versioning_flag );
489 END IF;
490
491 -- Standard Start of API savepoint
492 SAVEPOINT g_Update_Contract_Doc_GRP;
493 -- Standard call to check for call compatibility.
494 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
495 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
496 END IF;
497 -- Initialize message list if p_init_msg_list is set to TRUE.
498 IF FND_API.to_Boolean( p_init_msg_list ) THEN
499 FND_MSG_PUB.initialize;
500 END IF;
501 -- Initialize API return status to success
502 x_return_status := FND_API.G_RET_STS_SUCCESS;
503
504 -- Get the doc's primary_contract_doc_flag
505 OPEN contract_doc_csr;
506 FETCH contract_doc_csr INTO l_primary_doc_yn;
507 IF(contract_doc_csr%NOTFOUND) THEN
508 RAISE NO_DATA_FOUND;
509 END IF;
510 -- Do Not Clear Primary Flag when Copying or Versioning
511 -- or if the doc being modified is primary
512 IF p_primary_contract_doc_flag = 'Y' AND p_versioning_flag = 'N' AND l_primary_doc_yn = 'N'
513 THEN
514 -- Remove existing primary document flag.
515 Clear_Primary_Doc_Flag(
516 p_document_type => p_business_document_type,
517 p_document_id => p_business_document_id,
518 x_return_status => x_return_status);
519
520 END IF;
521 --------------------------------------------
522 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
523 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
524 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
525 RAISE FND_API.G_EXC_ERROR ;
526 END IF;
527 --------------------------------------------
528 CLOSE contract_doc_csr;
529
530 --------------------------------------------
531 -- Calling Simple API for Updating A Row
532 --------------------------------------------
533 OKC_CONTRACT_DOCS_PVT.Update_Row(
534 p_validation_level => p_validation_level,
535 x_return_status => x_return_status,
536 p_business_document_type => p_business_document_type,
537 p_business_document_id => p_business_document_id,
538 p_business_document_version => p_business_document_version,
539 p_attached_document_id => p_attached_document_id,
540 p_external_visibility_flag => p_external_visibility_flag,
541 p_effective_from_type => p_effective_from_type,
542 p_effective_from_id => p_effective_from_id,
543 p_effective_from_version => p_effective_from_version,
544 p_include_for_approval_flag => p_include_for_approval_flag,
545 p_program_id => p_program_id,
546 p_program_application_id => p_program_application_id,
547 p_request_id => p_request_id,
548 p_program_update_date => p_program_update_date,
549 p_parent_attached_doc_id => p_parent_attached_doc_id,
550 p_generated_flag => p_generated_flag,
551 p_delete_flag => p_delete_flag,
552 p_object_version_number => p_object_version_number,
553 p_primary_contract_doc_flag => p_primary_contract_doc_flag,
554 p_mergeable_doc_flag => p_mergeable_doc_flag
555 );
556 --------------------------------------------
557 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
558 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
559 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
560 RAISE FND_API.G_EXC_ERROR ;
561 END IF;
562 --------------------------------------------
563
564 -- Standard check of p_commit
565 IF FND_API.To_Boolean( p_commit ) THEN
566 COMMIT WORK;
567 END IF;
568 -- Standard call to get message count and if count is 1, get message info.
569 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
570
571 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
572 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Leaving Update_Contract_Doc');
573 END IF;
574
575 EXCEPTION
576 WHEN FND_API.G_EXC_ERROR THEN
577 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
578 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1900: Leaving Update_Contract_Doc: OKC_API.G_EXCEPTION_ERROR Exception');
579 END IF;
580 IF (contract_doc_csr%ISOPEN) THEN
581 CLOSE contract_doc_csr ;
582 END IF;
583 ROLLBACK TO g_Update_Contract_Doc_GRP;
584 x_return_status := G_RET_STS_ERROR ;
585 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
586
587 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
588 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
589 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2000: Leaving Update_Contract_Doc: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
590 END IF;
591 IF (contract_doc_csr%ISOPEN) THEN
592 CLOSE contract_doc_csr ;
593 END IF;
594 ROLLBACK TO g_Update_Contract_Doc_GRP;
595 x_return_status := G_RET_STS_UNEXP_ERROR ;
596 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
597
598 WHEN OTHERS THEN
599 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
600 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2100: Leaving Update_Contract_Doc because of EXCEPTION: '||sqlerrm);
601 END IF;
602 IF (contract_doc_csr%ISOPEN) THEN
603 CLOSE contract_doc_csr ;
604 END IF;
605 ROLLBACK TO g_Update_Contract_Doc_GRP;
606 x_return_status := G_RET_STS_UNEXP_ERROR ;
607 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
608 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
609 END IF;
610 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
611
612 END Update_Contract_Doc;
613
614
615
616 ---------------------------------------------------------------------------
617 -- PROCEDURE Delete_Contract_Doc
618 ---------------------------------------------------------------------------
619 PROCEDURE Delete_Contract_Doc(
620 p_api_version IN NUMBER,
621 p_init_msg_list IN VARCHAR2 ,
622 p_commit IN VARCHAR2 := FND_API.G_FALSE,
623
624 x_return_status OUT NOCOPY VARCHAR2,
625 x_msg_count OUT NOCOPY NUMBER,
626 x_msg_data OUT NOCOPY VARCHAR2,
627
628 p_business_document_type IN VARCHAR2,
629 p_business_document_id IN NUMBER,
630 p_business_document_version IN NUMBER,
631 p_attached_document_id IN NUMBER,
632 p_doc_approved_flag IN VARCHAR2 := 'N',
633 p_object_version_number IN NUMBER
634 ) IS
635 l_api_version CONSTANT NUMBER := 1;
636 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Contract_Doc';
637 l_effective_from_type OKC_CONTRACT_DOCS.EFFECTIVE_FROM_TYPE%TYPE;
638 l_effective_from_id OKC_CONTRACT_DOCS.EFFECTIVE_FROM_ID%TYPE;
639 l_effective_from_version OKC_CONTRACT_DOCS.EFFECTIVE_FROM_VERSION%TYPE;
640 l_datatype_id FND_DOCUMENTS.DATATYPE_ID%TYPE := -1;
641 l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE := -1;
642 l_object_version_number NUMBER := -1;
643 l_doc_approved_flag VARCHAR2(1);
644 l_msg_count NUMBER;
645 l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
646 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
647 l_parent_attached_doc_id OKC_CONTRACT_DOCS.PARENT_ATTACHED_DOC_ID%TYPE;
648 l_pk2_value FND_ATTACHED_DOCUMENTS.pk2_value%TYPE;
649
650 CURSOR attach_csr(l_bus_doc_type VARCHAR2, l_bus_doc_id NUMBER, l_bus_doc_version NUMBER, l_attached_doc_id NUMBER) IS
651 SELECT * FROM OKC_CONTRACT_DOCS
652 WHERE business_document_type = l_bus_doc_type
653 AND business_document_id = l_bus_doc_id
654 AND business_document_version = l_bus_doc_version
655 AND attached_document_id = l_attached_doc_id
656 AND delete_flag = 'N';
657
658 CURSOR child_ref_attach_csr(l_bus_doc_type VARCHAR2, l_bus_doc_id NUMBER, l_bus_doc_version NUMBER, l_attached_doc_id NUMBER) IS
659 SELECT * FROM OKC_CONTRACT_DOCS
660 WHERE business_document_type = l_bus_doc_type
661 AND business_document_id = l_bus_doc_id
662 AND effective_from_version = l_bus_doc_version
663 AND parent_attached_doc_id = l_attached_doc_id;
664
665 CURSOR rel_ref_attach_csr(l_bus_doc_type VARCHAR2, l_bus_doc_id NUMBER, l_parent_attached_doc_id NUMBER) IS
666 SELECT * FROM OKC_CONTRACT_DOCS
667 WHERE business_document_type = l_bus_doc_type
668 AND business_document_id = l_bus_doc_id
669 AND business_document_version = G_CURRENT_VERSION
670 AND parent_attached_doc_id = l_parent_attached_doc_id
671 AND delete_flag = 'N';
672
673 CURSOR fnd_attached_doc_csr IS
674 SELECT fad.rowid, fad.*
675 FROM FND_ATTACHED_DOCUMENTS fad
676 WHERE attached_document_id = p_attached_document_id;
677
678
679 CURSOR fnd_doc_csr(l_document_id NUMBER) IS
680 SELECT
681 B.datatype_id datatype_id,
682 B.category_id category_id,
683 B.security_type security_type,
684 B.security_id security_id,
685 B.publish_flag publish_flag,
686 B.image_type image_type,
687 B.storage_type storage_type,
688 B.usage_type usage_type,
689 B.start_date_active start_date_active,
690 B.end_date_active end_date_active,
691 TL.language language,
692 TL.description description,
693 B.file_name file_name,
694 B.url url,
695 B.media_id media_id
696 FROM fnd_documents B, fnd_documents_tl TL
697 WHERE B.document_id = l_document_id
698 AND TL.document_id = l_document_id
699 AND TL.language = USERENV('LANG');
700
701
702 fnd_attached_doc_rec fnd_attached_doc_csr%ROWTYPE;
703 fnd_doc_rec fnd_doc_csr%ROWTYPE;
704
705 BEGIN
706
707 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
708 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
709 '2200: Entered Delete_Contract_Doc');
710 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
711 '2201: p_business_document_type : ' || p_business_document_type );
712 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
713 '2202: p_business_document_id : ' || p_business_document_id );
714 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
715 '2203: p_business_document_version : ' || p_business_document_version );
716 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
717 '2204: p_attached_document_id : ' || p_attached_document_id );
718 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
719 '2205: p_doc_approved_flag : ' || p_doc_approved_flag );
720 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
721 '2206: p_object_version_number : ' || p_object_version_number );
722 END IF;
723
724 -- Standard Start of API savepoint
725 SAVEPOINT g_Delete_Contract_Doc_GRP;
726 -- Standard call to check for call compatibility.
727 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
728 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
729 END IF;
730 -- Initialize message list if p_init_msg_list is set to TRUE.
731 IF FND_API.to_Boolean( p_init_msg_list ) THEN
732 FND_MSG_PUB.initialize;
733 END IF;
734 -- Initialize API return status to success
735 x_return_status := FND_API.G_RET_STS_SUCCESS;
736
737 -- Get the parent attached_doc_id
738 SELECT parent_attached_doc_id
739 INTO l_parent_attached_doc_id
740 FROM OKC_CONTRACT_DOCS
741 WHERE business_document_type = p_business_document_type
742 AND business_document_id = p_business_document_id
743 AND business_document_version = p_business_document_version
744 AND attached_document_id = p_attached_document_id;
745
746 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
747 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2258: parent attached doc id is: ' || l_parent_attached_doc_id);
748 END IF;
749
750 -- Check to see if are deleting an archived record before deleting it's references in -99 version.
751 FOR child_ref_attach_rec IN child_ref_attach_csr(p_business_document_type, p_business_document_id,
752 p_business_document_version, p_attached_document_id)
753 LOOP
754 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
755 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2259-1: Fetching child reference record');
756 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2260-1:doc type is: ' || child_ref_attach_rec.business_document_type);
757 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2261-1:doc id is: ' || child_ref_attach_rec.business_document_id);
758 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2262-1:doc version is: ' || child_ref_attach_rec.business_document_version);
759 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2263-1:attached_document_id is: ' || child_ref_attach_rec.attached_document_id);
760 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2264-1:effective_from_version is: ' || child_ref_attach_rec.effective_from_version);
761 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2265-1:delete_flag is: ' || child_ref_attach_rec.delete_flag);
762 END IF;
763
764 OKC_CONTRACT_DOCS_GRP.delete_contract_doc(
765 p_api_version => p_api_version,
766 p_init_msg_list => p_init_msg_list,
767 p_business_document_type => child_ref_attach_rec.business_document_type,
768 p_business_document_id => child_ref_attach_rec.business_document_id,
769 p_business_document_version => child_ref_attach_rec.business_document_version,
770 p_attached_document_id => child_ref_attach_rec.attached_document_id,
771 p_doc_approved_flag => p_doc_approved_flag,
772 p_object_version_number => child_ref_attach_rec.object_version_number,
773 x_msg_data => l_msg_data,
774 x_msg_count => l_msg_count,
775 x_return_status => l_return_status);
776 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
777 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
778 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
779 RAISE FND_API.G_EXC_ERROR ;
780 END IF;
781
782 END LOOP;
783
784 -- If we are deleting a record in an archived version, we need to delete it's related records in -99 version.
785 IF ((p_business_document_version <> G_CURRENT_VERSION) AND (l_parent_attached_doc_id is not NULL)) THEN
786 FOR rel_ref_attach_rec IN rel_ref_attach_csr(p_business_document_type, p_business_document_id,
787 l_parent_attached_doc_id)
788 LOOP
789 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
790 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2259-2: Fetching related reference record');
791 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2260-2:doc type is: ' || rel_ref_attach_rec.business_document_type);
792 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2261-2:doc id is: ' || rel_ref_attach_rec.business_document_id);
793 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2262-2:doc version is: ' || rel_ref_attach_rec.business_document_version);
794 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2263-2:attached_document_id is: ' || rel_ref_attach_rec.attached_document_id);
795 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2264-2:effective_from_version is: ' || rel_ref_attach_rec.effective_from_version);
796 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2265-2:delete_flag is: ' || rel_ref_attach_rec.delete_flag);
797 END IF;
798
799 OKC_CONTRACT_DOCS_GRP.delete_contract_doc(
800 p_api_version => p_api_version,
801 p_init_msg_list => p_init_msg_list,
802 p_business_document_type => rel_ref_attach_rec.business_document_type,
803 p_business_document_id => rel_ref_attach_rec.business_document_id,
804 p_business_document_version => rel_ref_attach_rec.business_document_version,
805 p_attached_document_id => rel_ref_attach_rec.attached_document_id,
806 p_doc_approved_flag => p_doc_approved_flag,
807 p_object_version_number => rel_ref_attach_rec.object_version_number,
808 x_msg_data => l_msg_data,
809 x_msg_count => l_msg_count,
810 x_return_status => l_return_status);
811 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
812 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
813 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
814 RAISE FND_API.G_EXC_ERROR ;
815 END IF;
816
817 END LOOP;
818
819 END IF;
820
821
822 SELECT effective_from_type, effective_from_id, effective_from_version
823 INTO l_effective_from_type, l_effective_from_id, l_effective_from_version
824 FROM OKC_CONTRACT_DOCS
825 WHERE business_document_type = p_business_document_type
826 AND business_document_id = p_business_document_id
827 AND business_document_version = p_business_document_version
828 AND attached_document_id = p_attached_document_id;
829
830 -- For MS Word integration, we need to soft delete the ref. attachments. Using l_parent_attached_doc_id to check
831 -- for reference
832 if ((p_doc_approved_flag = 'Y') OR
833 (NVL(l_parent_attached_doc_id, p_attached_document_id) <> p_attached_document_id)) THEN
834 -- (l_effective_from_type = p_business_document_type AND
835 -- l_effective_from_id = p_business_document_id AND
836 -- l_effective_from_version <> p_business_document_version) THEN
837 -- DO not delete anything. Just update the OKC_CONTRACT_DOCS record to delete_flag='Y' and
838 -- add 'D' suffix to FND_ATTACHED_DOCUMENTS record's pk2_value column.
839 FOR attach_rec IN attach_csr(p_business_document_type, p_business_document_id,
840 p_business_document_version, p_attached_document_id)
841 LOOP
842 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
843 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2260:doc type is: ' || attach_rec.business_document_type);
844 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2261:doc id is: ' || attach_rec.business_document_id);
845 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2262:doc version is: ' || attach_rec.business_document_version);
846 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2263:attached_document_id is: ' || attach_rec.attached_document_id);
847 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2264:effective_from_version is: ' || attach_rec.effective_from_version);
848 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2265:delete_flag is: ' || attach_rec.delete_flag);
849 END IF;
850 -- Open the fnd_attached_doc_csr and fnd_doc_csr cursors to fetch the FND attachment record to be updated.
851 OPEN fnd_attached_doc_csr;
852 FETCH fnd_attached_doc_csr INTO fnd_attached_doc_rec;
853 IF(fnd_attached_doc_csr%NOTFOUND) THEN
854 RAISE NO_DATA_FOUND;
855 END IF;
856
857 OPEN fnd_doc_csr(fnd_attached_doc_rec.document_id);
858 FETCH fnd_doc_csr INTO fnd_doc_rec;
859 IF(fnd_doc_csr%NOTFOUND) THEN
860 RAISE NO_DATA_FOUND;
861 END IF;
862
863 -- Append 'D' to pk2_value
864 l_pk2_value := fnd_attached_doc_rec.pk2_value || 'D';
865 -- Update the corresponding record in FND_ATTACHED_DOCUMENTS tables. Add 'D' to pk2_value column.
866 fnd_attached_documents_pkg.Update_Row(
867 X_Rowid => fnd_attached_doc_rec.rowid,
868 X_attached_document_id => fnd_attached_doc_rec.attached_document_id,
869 X_document_id => fnd_attached_doc_rec.document_id,
870 X_last_update_date => fnd_attached_doc_rec.last_update_date,
871 X_last_updated_by => fnd_attached_doc_rec.last_updated_by,
872 X_last_update_login => fnd_attached_doc_rec.last_update_login,
873 X_seq_num => fnd_attached_doc_rec.seq_num,
874 X_entity_name => fnd_attached_doc_rec.entity_name,
875 X_column1 => fnd_attached_doc_rec.column1,
876 X_pk1_value => fnd_attached_doc_rec.pk1_value,
877 X_pk2_value => l_pk2_value,
878 X_pk3_value => fnd_attached_doc_rec.pk3_value,
879 X_pk4_value => fnd_attached_doc_rec.pk4_value,
880 X_pk5_value => fnd_attached_doc_rec.pk5_value,
881 X_automatically_added_flag => fnd_attached_doc_rec.automatically_added_flag,
882 X_request_id => fnd_attached_doc_rec.request_id,
883 X_program_application_id => fnd_attached_doc_rec.program_application_id,
884 X_program_id => fnd_attached_doc_rec.program_id,
885 /* columns necessary for updating a fnd_document*/
886 X_datatype_id => fnd_doc_rec.datatype_id,
887 X_category_id => fnd_doc_rec.category_id,
888 X_security_type => fnd_doc_rec.security_type,
889 X_security_id => fnd_doc_rec.security_id,
890 X_publish_flag => fnd_doc_rec.publish_flag,
891 X_image_type => fnd_doc_rec.image_type,
892 X_storage_type => fnd_doc_rec.storage_type,
893 X_usage_type => fnd_doc_rec.usage_type,
894 X_start_date_active => fnd_doc_rec.start_date_active,
895 X_end_date_active => fnd_doc_rec.end_date_active,
896 X_language => fnd_doc_rec.language,
897 X_description => fnd_doc_rec.description,
898 X_file_name => fnd_doc_rec.file_name,
899 X_url => fnd_doc_rec.url,
900 X_media_id => fnd_doc_rec.media_id);
901
902 -- Close cursors.
903 CLOSE fnd_attached_doc_csr;
904 CLOSE fnd_doc_csr;
905
906 -- Update the record in OKC_CONTRACT_DOCS table, set delete_flag='Y'
907 OKC_CONTRACT_DOCS_PVT.Update_Row(
908 x_return_status => l_return_status,
909 p_business_document_type => attach_rec.business_document_type,
910 p_business_document_id => attach_rec.business_document_id,
911 p_business_document_version => attach_rec.business_document_version,
912 p_attached_document_id => attach_rec.attached_document_id,
913 p_external_visibility_flag => attach_rec.external_visibility_flag,
914 p_effective_from_type => attach_rec.effective_from_type,
915 p_effective_from_id => attach_rec.effective_from_id,
916 p_effective_from_version => attach_rec.effective_from_version,
917 p_include_for_approval_flag => attach_rec.include_for_approval_flag,
918 p_program_id => attach_rec.program_id,
919 p_program_application_id => attach_rec.program_application_id,
920 p_request_id => attach_rec.request_id,
921 p_program_update_date => attach_rec.program_update_date,
922 p_parent_attached_doc_id => attach_rec.parent_attached_doc_id,
923 p_generated_flag => attach_rec.generated_flag,
924 p_primary_contract_doc_flag => attach_rec.primary_contract_doc_flag,
925 p_mergeable_doc_flag => attach_rec.mergeable_doc_flag,
926 p_delete_flag => 'Y',
927 p_object_version_number => p_object_version_number
928 );
929 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
930 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
931 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
932 RAISE FND_API.G_EXC_ERROR ;
933 END IF;
934 END LOOP;
935 ELSE
936 -- Delete the record
937 SELECT d.datatype_id INTO l_datatype_id
938 FROM fnd_documents d, fnd_attached_documents ad
939 WHERE d.document_id = ad.document_id
940 AND ad.attached_document_id = p_attached_document_id;
941
942 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
943 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2250: p_business_document_type is: ' || p_business_document_type);
944 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2251: p_business_document_id is: ' || p_business_document_id);
945 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2252: p_business_document_version is: ' || p_business_document_version);
946 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2253: p_attached_document_id is: ' || p_attached_document_id);
947 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2254: l_effective_from_type is: ' || l_effective_from_type);
948 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2255: l_effective_from_id is: ' || l_effective_from_id);
949 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2256: l_effective_from_version is: ' || l_effective_from_version);
950 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2257: l_datatype_id is: ' || l_datatype_id);
951 END IF;
952 -- If current document, delete data in FND tables.
953 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
954 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2258: Deleting FND Attachments');
955 END IF;
956 -- Call FND delete attachment API to delete Media as well
957 FND_ATTACHED_DOCUMENTS3_PKG.DELETE_ROW(
958 X_attached_document_id => p_attached_document_id,
959 X_datatype_id => l_datatype_id,
960 delete_document_flag => 'Y');
961
962 --------------------------------------------------------------------
963 -- Calling Simple API for Deleting A Row
964 --------------------------------------------------------------------
965 OKC_CONTRACT_DOCS_PVT.Delete_Row(
966 x_return_status => x_return_status,
967 p_business_document_type => p_business_document_type,
968 p_business_document_id => p_business_document_id,
969 p_business_document_version => p_business_document_version,
970 p_attached_document_id => p_attached_document_id,
971 p_object_version_number => p_object_version_number
972 );
973 END IF;
974
975 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
976 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
977 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
978 RAISE FND_API.G_EXC_ERROR ;
979 END IF;
980 --------------------------------------------
981
982 -- Standard check of p_commit
983 IF FND_API.To_Boolean( p_commit ) THEN
984 COMMIT WORK;
985 END IF;
986 -- Standard call to get message count and if count is 1, get message info.
987 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
988
989 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
990 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2300: Leaving Delete_Contract_Doc');
991 END IF;
992
993 EXCEPTION
994 WHEN FND_API.G_EXC_ERROR THEN
995 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
996 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2400: Leaving Delete_Contract_Doc: OKC_API.G_EXCEPTION_ERROR Exception');
997 END IF;
998 --close cursors
999 IF (fnd_attached_doc_csr%ISOPEN) THEN
1000 CLOSE fnd_attached_doc_csr ;
1001 END IF;
1002 IF (fnd_doc_csr%ISOPEN) THEN
1003 CLOSE fnd_doc_csr ;
1004 END IF;
1005 ROLLBACK TO g_Delete_Contract_Doc_GRP;
1006 x_return_status := G_RET_STS_ERROR ;
1007 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1008
1009 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1010 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1011 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2500: Leaving Delete_Contract_Doc: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1012 END IF;
1013 IF (fnd_attached_doc_csr%ISOPEN) THEN
1014 CLOSE fnd_attached_doc_csr ;
1015 END IF;
1016 IF (fnd_doc_csr%ISOPEN) THEN
1017 CLOSE fnd_doc_csr ;
1018 END IF;
1019 ROLLBACK TO g_Delete_Contract_Doc_GRP;
1020 x_return_status := G_RET_STS_UNEXP_ERROR ;
1021 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1022
1023 WHEN OTHERS THEN
1024 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1025 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2600: Leaving Delete_Contract_Doc because of EXCEPTION: '||sqlerrm);
1026 END IF;
1027 IF (fnd_attached_doc_csr%ISOPEN) THEN
1028 CLOSE fnd_attached_doc_csr ;
1029 END IF;
1030 IF (fnd_doc_csr%ISOPEN) THEN
1031 CLOSE fnd_doc_csr ;
1032 END IF;
1033 ROLLBACK TO g_Delete_Contract_Doc_GRP;
1034 x_return_status := G_RET_STS_UNEXP_ERROR ;
1035 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1036 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1037 END IF;
1038 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1039
1040 END Delete_Contract_Doc;
1041
1042
1043
1044
1045 ---------------------------------------------------------------------------
1046 -- PROCEDURE Version_Attachments
1047 ---------------------------------------------------------------------------
1048 PROCEDURE Version_Attachments(
1049 p_api_version IN NUMBER,
1050 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1051 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1052 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1053
1054 x_return_status OUT NOCOPY VARCHAR2,
1055 x_msg_count OUT NOCOPY NUMBER,
1056 x_msg_data OUT NOCOPY VARCHAR2,
1057
1058 p_business_document_type IN VARCHAR2,
1059 p_business_document_id IN NUMBER,
1060 p_business_document_version IN NUMBER,
1061 p_include_gen_attach IN VARCHAR2 DEFAULT 'Y'
1062 ) IS l_api_name CONSTANT VARCHAR2(30) := 'Version_Attachments';
1063 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1064
1065 l_msg_count NUMBER;
1066 l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
1067 l_api_version NUMBER := 1;
1068 l_business_document_type OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_TYPE%TYPE;
1069 l_business_document_id OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_ID%TYPE;
1070 l_business_document_version OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_VERSION%TYPE;
1071 l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
1072 l_datatype_id FND_DOCUMENTS.DATATYPE_ID%TYPE;
1073 l_effective_from_version OKC_CONTRACT_DOCS.effective_from_version%TYPE;
1074 l_parent_attached_doc_id OKC_CONTRACT_DOCS.parent_attached_doc_id%TYPE;
1075 l_non_ref_attachment_created VARCHAR2(1);
1076
1077
1078 -- Attachment cursor
1079 CURSOR attach_csr(l_bus_doc_type VARCHAR2, l_bus_doc_id NUMBER, l_bus_doc_version NUMBER) IS
1080 SELECT * FROM OKC_CONTRACT_DOCS
1081 WHERE business_document_type = l_bus_doc_type
1082 AND business_document_id = l_bus_doc_id
1083 AND business_document_version = l_bus_doc_version;
1084 -- AND delete_flag = 'N';
1085
1086 -- cursor for deleting prev. version deleted ref. attachments in -99
1087 CURSOR ref_del_csr(l_bus_doc_type VARCHAR2, l_bus_doc_id NUMBER,
1088 l_bus_doc_version NUMBER, l_curr_version NUMBER) IS
1089 SELECT * FROM OKC_CONTRACT_DOCS
1090 WHERE business_document_type = l_bus_doc_type
1091 AND business_document_id = l_bus_doc_id
1092 AND business_document_version = l_curr_version
1093 AND effective_from_type = l_bus_doc_type
1094 AND effective_from_id = l_bus_doc_id
1095 AND effective_from_version < l_bus_doc_version
1096 AND effective_from_version <> l_curr_version
1097 AND delete_flag = 'Y';
1098
1099 -- data_type cursor, will be required for deleting current version generated doc.
1100 CURSOR datatype_csr (l_attached_document_id NUMBER) IS
1101 SELECT d.datatype_id
1102 FROM fnd_documents d, fnd_attached_documents ad
1103 WHERE d.document_id = ad.document_id
1104 AND ad.attached_document_id = l_attached_document_id;
1105
1106 BEGIN
1107 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1108 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1109 '2700: Entered Version_Attachments');
1110 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1111 '2701: p_business_document_type : ' || p_business_document_type );
1112 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1113 '2702: p_business_document_id : ' || p_business_document_id );
1114 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1115 '2703: p_business_document_version : ' || p_business_document_version );
1116 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1117 '2704: p_include_gen_attach : ' || p_include_gen_attach );
1118 END IF;
1119
1120 -- Standard Start of API savepoint
1121 SAVEPOINT Version_Attachments_GRP;
1122 -- Standard call to check for call compatibility.
1123 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1124 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1125 END IF;
1126 -- Initialize message list if p_init_msg_list is set to TRUE.
1127 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1128 FND_MSG_PUB.initialize;
1129 END IF;
1130
1131 -- Initialize API return status to success
1132 x_return_status := FND_API.G_RET_STS_SUCCESS;
1133
1134 FOR attach_rec IN attach_csr(p_business_document_type, p_business_document_id,G_CURRENT_VERSION) LOOP
1135 l_non_ref_attachment_created := 'N';
1136 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1137 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2710:doc version is: ' || attach_rec.business_document_version);
1138 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2720:attch doc id is: ' || attach_rec.attached_document_id);
1139 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2730:effective_from_type is: ' || attach_rec.effective_from_type);
1140 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2730:effective_from_id is: ' || attach_rec.effective_from_id);
1141 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2730:effective_from_version is: ' || attach_rec.effective_from_version);
1142 END IF;
1143 -- Set the value of effective_from_version column
1144 IF (attach_rec.effective_from_version = G_CURRENT_VERSION) THEN
1145 l_effective_from_version := p_business_document_version;
1146 ELSE
1147 l_effective_from_version := attach_rec.effective_from_version;
1148 END IF; -- (attach_rec.effective_from_version = G_CURRENT_VERSION)
1149 -- Copy the existing current version attachment to the specific version
1150 IF (attach_rec.effective_from_version <> G_CURRENT_VERSION) THEN
1151 -- Create version attachment with p_parent_attached_doc_id
1152 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1153 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2731: Inserting New Ref recpord');
1154 END IF;
1155 Insert_Contract_Doc (
1156 p_api_version => l_api_version,
1157 p_init_msg_list => FND_API.G_FALSE,
1158 p_business_document_type => attach_rec.business_document_type,
1159 p_business_document_id => attach_rec.business_document_id,
1160 p_business_document_version => p_business_document_version,
1161 p_attached_document_id => attach_rec.attached_document_id,
1162 p_external_visibility_flag => attach_rec.external_visibility_flag,
1163 p_effective_from_type => attach_rec.effective_from_type,
1164 p_effective_from_id => attach_rec.effective_from_id,
1165 p_effective_from_version => l_effective_from_version,
1166 p_include_for_approval_flag => attach_rec.include_for_approval_flag,
1167 p_generated_flag => attach_rec.generated_flag,
1168 p_primary_contract_doc_flag => attach_rec.primary_contract_doc_flag,
1169 p_mergeable_doc_flag => attach_rec.mergeable_doc_flag,
1170 p_delete_flag => attach_rec.delete_flag,
1171 p_create_fnd_attach => 'Y',
1172 p_program_id => NULL,
1173 p_program_application_id => NULL,
1174 p_request_id => NULL,
1175 p_program_update_date => NULL,
1176 p_parent_attached_doc_id => attach_rec.parent_attached_doc_id,
1177 p_versioning_flag => 'Y',
1178 x_msg_data => l_msg_data,
1179 x_msg_count => l_msg_count,
1180 x_return_status => l_return_status,
1181 x_business_document_type => l_business_document_type,
1182 x_business_document_id => l_business_document_id,
1183 x_business_document_version => l_business_document_version,
1184 x_attached_document_id => l_attached_document_id);
1185 ELSE
1186 -- Create version attachment without p_parent_attached_doc_id
1187 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1188 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2732: Inserting New Non-Ref recpord');
1189 END IF;
1190 Insert_Contract_Doc (
1191 p_api_version => l_api_version,
1192 p_init_msg_list => FND_API.G_FALSE,
1193 p_business_document_type => attach_rec.business_document_type,
1194 p_business_document_id => attach_rec.business_document_id,
1195 p_business_document_version => p_business_document_version,
1196 p_attached_document_id => attach_rec.attached_document_id,
1197 p_external_visibility_flag => attach_rec.external_visibility_flag,
1198 p_effective_from_type => attach_rec.effective_from_type,
1199 p_effective_from_id => attach_rec.effective_from_id,
1200 p_effective_from_version => l_effective_from_version,
1201 p_include_for_approval_flag => attach_rec.include_for_approval_flag,
1202 p_generated_flag => attach_rec.generated_flag,
1203 p_primary_contract_doc_flag => attach_rec.primary_contract_doc_flag,
1204 p_mergeable_doc_flag => attach_rec.mergeable_doc_flag,
1205 p_delete_flag => attach_rec.delete_flag,
1206 p_create_fnd_attach => 'Y',
1207 p_program_id => NULL,
1208 p_program_application_id => NULL,
1209 p_request_id => NULL,
1210 p_program_update_date => NULL,
1211 p_versioning_flag => 'Y',
1212 x_msg_data => l_msg_data,
1213 x_msg_count => l_msg_count,
1214 x_return_status => l_return_status,
1215 x_business_document_type => l_business_document_type,
1216 x_business_document_id => l_business_document_id,
1217 x_business_document_version => l_business_document_version,
1218 x_attached_document_id => l_attached_document_id);
1219
1220 l_non_ref_attachment_created := 'Y';
1221
1222 END IF; -- (attach_rec.effective_from_version = G_CURRENT_VERSION)
1223 -- Check for errors
1224 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1225 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1226 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1227 RAISE FND_API.G_EXC_ERROR;
1228 END IF;
1229
1230 -- Set the value of parent_attached_doc_id column of new current version attachments
1231 IF (l_non_ref_attachment_created = 'Y') THEN
1232 l_parent_attached_doc_id := l_attached_document_id;
1233 ELSE
1234 l_parent_attached_doc_id := attach_rec.parent_attached_doc_id;
1235 END IF; -- (attach_rec.effective_from_version = G_CURRENT_VERSION)
1236
1237
1238
1239 IF NOT (p_include_gen_attach = 'N' AND attach_rec.generated_flag = 'Y') THEN
1240 -- Updated current version record's effective_from_version and parent_attached_doc_id columns
1241 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1242 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2733: Updating Current attachment');
1243 END IF;
1244 Update_Contract_Doc (
1245 p_api_version => l_api_version,
1246 p_init_msg_list => p_init_msg_list,
1247 p_validation_level => p_validation_level,
1248 p_business_document_type => attach_rec.business_document_type,
1249 p_business_document_id => attach_rec.business_document_id,
1250 p_business_document_version => attach_rec.business_document_version,
1251 p_attached_document_id => attach_rec.attached_document_id,
1252 p_external_visibility_flag => attach_rec.external_visibility_flag,
1253 p_effective_from_type => attach_rec.effective_from_type,
1254 p_effective_from_id => attach_rec.effective_from_id,
1255 p_effective_from_version => l_effective_from_version,
1256 p_include_for_approval_flag => attach_rec.include_for_approval_flag,
1257 p_generated_flag => attach_rec.generated_flag,
1258 p_primary_contract_doc_flag => attach_rec.primary_contract_doc_flag,
1259 p_mergeable_doc_flag => attach_rec.mergeable_doc_flag,
1260 p_delete_flag => attach_rec.delete_flag,
1261 p_program_id => NULL,
1262 p_program_application_id => NULL,
1263 p_request_id => NULL,
1264 p_program_update_date => NULL,
1265 p_parent_attached_doc_id => l_parent_attached_doc_id,
1266 p_object_version_number => attach_rec.object_version_number,
1267 p_versioning_flag => 'Y',
1268 x_msg_data => l_msg_data,
1269 x_msg_count => l_msg_count,
1270 x_return_status => l_return_status);
1271 -- Check for errors
1272 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1273 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1274 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1275 RAISE FND_API.G_EXC_ERROR;
1276 END IF;
1277 ELSE
1278 -- Delete the current generated record.
1279 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1280 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2734: Deleting generated Current attachment');
1281 END IF;
1282
1283 -- get the value of FND's datatype_id. Needed for
1284 OPEN datatype_csr(attach_rec.attached_document_id);
1285 FETCH datatype_csr INTO l_datatype_id;
1286 IF(datatype_csr%NOTFOUND) THEN
1287 RAISE NO_DATA_FOUND;
1288 END IF;
1289 CLOSE datatype_csr;
1290 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1291 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2736: Deleting FND Attachments');
1292 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2735: l_datatype_id is: '
1293 || l_datatype_id);
1294 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2735: attached_document_id is: '
1298 FND_ATTACHED_DOCUMENTS3_PKG.DELETE_ROW(
1295 || attach_rec.attached_document_id);
1296 END IF;
1297 -- Call FND delete attachment API to delete just the FND_ATTACHED_DOCUMENTS record
1299 X_attached_document_id => attach_rec.attached_document_id,
1300 X_datatype_id => l_datatype_id,
1301 delete_document_flag => 'N'); -- Don't delete FND_DOCUMENTS records
1302
1303 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1304 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2737: Deleting OKC_CONTRACT_DOCS record');
1305 END IF;
1306 -- Delete record in OKC_CONTRACT_DOCS table
1307 OKC_CONTRACT_DOCS_PVT.Delete_Row(
1308 x_return_status => l_return_status,
1309 p_business_document_type => attach_rec.business_document_type,
1310 p_business_document_id => attach_rec.business_document_id,
1311 p_business_document_version => attach_rec.business_document_version,
1312 p_attached_document_id => attach_rec.attached_document_id,
1313 p_object_version_number => attach_rec.object_version_number
1314 );
1315 -- Check for errors
1316 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1318 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1319 RAISE FND_API.G_EXC_ERROR;
1320 END IF;
1321 END IF; -- NOT (p_include_gen_attach = 'N' AND attach_rec.generated_flag = 'Y')
1322 END LOOP;
1323
1324
1325 -- Loop to delete the older ref. attachments in current version
1326 FOR ref_del_rec IN ref_del_csr(p_business_document_type, p_business_document_id,
1327 p_business_document_version, G_CURRENT_VERSION) LOOP
1328 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1329 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2740:doc version is: ' || ref_del_rec.business_document_version);
1330 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2741:attch doc id is: ' || ref_del_rec.attached_document_id);
1331 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2742:effective_from_type is: ' || ref_del_rec.effective_from_type);
1332 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2743:effective_from_id is: ' || ref_del_rec.effective_from_id);
1333 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2744:effective_from_version is: ' || ref_del_rec.effective_from_version);
1334 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2745: Deleting OKC_CONTRACT_DOCS ref. records');
1335 END IF;
1336 -- get the value of FND's datatype_id. Needed for
1337 OPEN datatype_csr(ref_del_rec.attached_document_id);
1338 FETCH datatype_csr INTO l_datatype_id;
1339 IF(datatype_csr%NOTFOUND) THEN
1340 RAISE NO_DATA_FOUND;
1341 END IF;
1342 CLOSE datatype_csr;
1343 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1344 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2746: Deleting FND Attachments');
1345 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2747: l_datatype_id is: '
1346 || l_datatype_id);
1347 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2748: attached_document_id is: '
1348 || ref_del_rec.attached_document_id);
1349 END IF;
1350 -- Call FND delete attachment API to delete just the FND_ATTACHED_DOCUMENTS record
1351 FND_ATTACHED_DOCUMENTS3_PKG.DELETE_ROW(
1352 X_attached_document_id => ref_del_rec.attached_document_id,
1353 X_datatype_id => l_datatype_id,
1354 delete_document_flag => 'N'); -- Don't delete FND_DOCUMENTS records
1355 -- Delete record in OKC_CONTRACT_DOCS table
1356 OKC_CONTRACT_DOCS_PVT.Delete_Row(
1357 x_return_status => l_return_status,
1358 p_business_document_type => ref_del_rec.business_document_type,
1359 p_business_document_id => ref_del_rec.business_document_id,
1360 p_business_document_version => ref_del_rec.business_document_version,
1361 p_attached_document_id => ref_del_rec.attached_document_id,
1362 p_object_version_number => ref_del_rec.object_version_number
1363 );
1364 -- Check for errors
1365 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1366 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1367 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1368 RAISE FND_API.G_EXC_ERROR;
1369 END IF;
1370 END LOOP;
1371
1372 -- Standard check of p_commit
1373 IF FND_API.To_Boolean( p_commit ) THEN
1374 COMMIT WORK;
1375 END IF;
1376 -- Standard call to get message count and if count is 1, get message info.
1377 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1378
1379 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1380 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2800: Leaving Version_Attachments');
1381 END IF;
1382
1383 EXCEPTION
1384 WHEN FND_API.G_EXC_ERROR THEN
1385 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1386 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2900: Leaving Version_Attachments: OKC_API.G_EXCEPTION_ERROR Exception');
1387 END IF;
1388 IF (datatype_csr%ISOPEN) THEN
1389 CLOSE datatype_csr ;
1390 END IF;
1391 ROLLBACK TO Version_Attachments_GRP;
1392 x_return_status := G_RET_STS_ERROR ;
1396 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1393 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1394
1395 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1397 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3000: Leaving Version_Attachments: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1398 END IF;
1399 IF (datatype_csr%ISOPEN) THEN
1400 CLOSE datatype_csr ;
1401 END IF;
1402 ROLLBACK TO Version_Attachments_GRP;
1403 x_return_status := G_RET_STS_UNEXP_ERROR ;
1404 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1405
1406 WHEN OTHERS THEN
1407 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1408 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3100: Leaving Version_Attachments because of EXCEPTION: '||sqlerrm);
1409 END IF;
1410 IF (datatype_csr%ISOPEN) THEN
1411 CLOSE datatype_csr ;
1412 END IF;
1413 ROLLBACK TO Version_Attachments_GRP;
1414 x_return_status := G_RET_STS_UNEXP_ERROR ;
1415 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1416 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1417 END IF;
1418 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1419 END Version_Attachments;
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430 ---------------------------------------------------------------------------
1431 -- PROCEDURE Delete_Ver_Attachments
1432 ---------------------------------------------------------------------------
1433 PROCEDURE Delete_Ver_Attachments(
1434 p_api_version IN NUMBER,
1435 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1436 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1437 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1438
1439 x_return_status OUT NOCOPY VARCHAR2,
1440 x_msg_count OUT NOCOPY NUMBER,
1441 x_msg_data OUT NOCOPY VARCHAR2,
1442
1443 p_business_document_type IN VARCHAR2,
1444 p_business_document_id IN NUMBER,
1445 p_business_document_version IN NUMBER
1446
1447 ) IS
1448 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Ver_Attachments';
1449 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1450 l_msg_count NUMBER;
1451 l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
1452 l_api_version NUMBER := 1;
1453
1454 -- Attachment cursor
1455 CURSOR attach_csr(l_bus_doc_type VARCHAR2, l_bus_doc_id NUMBER, l_bus_doc_version NUMBER) IS
1456 SELECT * FROM OKC_CONTRACT_DOCS
1457 WHERE business_document_type = l_bus_doc_type
1458 AND business_document_id = l_bus_doc_id
1459 AND business_document_version = l_bus_doc_version;
1460
1461
1462 BEGIN
1463 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1464 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1465 '3200: Entered Delete_Ver_Attachments');
1466 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1467 '3201: p_business_document_type : ' || p_business_document_type );
1468 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1469 '3202: p_business_document_id : ' || p_business_document_id );
1470 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1471 '3203: p_business_document_version : ' || p_business_document_version );
1472 END IF;
1473
1474 -- Standard Start of API savepoint
1475 SAVEPOINT Delete_Ver_Attachments_GRP;
1476
1477 -- Standard call to check for call compatibility.
1478 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1479 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1480 END IF;
1481 -- Initialize message list if p_init_msg_list is set to TRUE.
1482 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1483 FND_MSG_PUB.initialize;
1484 END IF;
1485
1486 -- Initialize API return status to success
1487 x_return_status := FND_API.G_RET_STS_SUCCESS;
1488
1489 FOR attach_rec IN attach_csr(p_business_document_type, p_business_document_id,
1490 p_business_document_version) LOOP
1491 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1492 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3210:doc version is: ' || attach_rec.business_document_version);
1493 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3220:attch doc id is: ' || attach_rec.attached_document_id);
1494 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3230:effective_from_version is: ' || attach_rec.effective_from_version);
1495 END IF;
1496
1497 -- Delete OKC_CONTRACT_DOCS record
1498 Delete_Contract_Doc (
1499 p_api_version => l_api_version,
1500 p_init_msg_list => p_init_msg_list,
1501
1502 p_business_document_type => p_business_document_type,
1503 p_business_document_id => p_business_document_id,
1507 p_object_version_number => NULL,
1504 p_business_document_version => p_business_document_version,
1505
1506 p_attached_document_id => attach_rec.attached_document_id,
1508 x_msg_data => l_msg_data,
1509 x_msg_count => l_msg_count,
1510 x_return_status => l_return_status);
1511
1512 -- Check for errors
1513 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1514 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1515 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1516 RAISE FND_API.G_EXC_ERROR;
1517 END IF;
1518 END LOOP;
1519
1520 -- Standard check of p_commit
1521 IF FND_API.To_Boolean( p_commit ) THEN
1522 COMMIT WORK;
1523 END IF;
1524 -- Standard call to get message count and if count is 1, get message info.
1525 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1526
1527 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1528 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3300: Leaving Delete_Ver_Attachments');
1529 END IF;
1530
1531 EXCEPTION
1532 WHEN FND_API.G_EXC_ERROR THEN
1533 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1534 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3400: Leaving Delete_Ver_Attachments: OKC_API.G_EXCEPTION_ERROR Exception');
1535 END IF;
1536 ROLLBACK TO Delete_Ver_Attachments_GRP;
1537 x_return_status := G_RET_STS_ERROR ;
1538 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1539
1540 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1541 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1542 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3500: Leaving Delete_Ver_Attachments: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1543 END IF;
1544 ROLLBACK TO Delete_Ver_Attachments_GRP;
1545 x_return_status := G_RET_STS_UNEXP_ERROR ;
1546 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1547
1548 WHEN OTHERS THEN
1549 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1550 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3600: Leaving Delete_Ver_Attachments because of EXCEPTION: '||sqlerrm);
1551 END IF;
1552
1553 ROLLBACK TO Delete_Ver_Attachments_GRP;
1554 x_return_status := G_RET_STS_UNEXP_ERROR ;
1555 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1556 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1557 END IF;
1558 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1559
1560 END Delete_Ver_Attachments;
1561
1562
1563
1564
1565
1566
1567 ---------------------------------------------------------------------------
1568 -- PROCEDURE Delete_Doc_Attachments
1569 ---------------------------------------------------------------------------
1570 PROCEDURE Delete_Doc_Attachments(
1571 p_api_version IN NUMBER,
1572 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1573 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1574 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1575
1576 x_return_status OUT NOCOPY VARCHAR2,
1577 x_msg_count OUT NOCOPY NUMBER,
1578 x_msg_data OUT NOCOPY VARCHAR2,
1579
1580 p_business_document_type IN VARCHAR2,
1581 p_business_document_id IN NUMBER
1582
1583 ) IS
1584 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Doc_Attachments';
1585 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1586 l_msg_count NUMBER;
1587 l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
1588 l_api_version NUMBER := 1;
1589
1590 -- Attachment cursor
1591 CURSOR attach_csr(l_bus_doc_type VARCHAR2, l_bus_doc_id NUMBER) IS
1592 SELECT distinct business_document_version FROM OKC_CONTRACT_DOCS
1593 WHERE business_document_type = l_bus_doc_type
1594 AND business_document_id = l_bus_doc_id
1595 AND business_document_version <> -99
1596 ORDER by business_document_version DESC ;
1597
1598
1599 BEGIN
1600 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1601 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1602 '3900: Entered Delete_Doc_Attachments');
1603 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1604 '3901: p_business_document_type: ' || p_business_document_type);
1605 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1606 '3902: p_business_document_id: ' || p_business_document_id);
1607 END IF;
1608
1609 -- Standard Start of API savepoint
1610 SAVEPOINT Delete_Doc_Attachments_GRP;
1611 -- Standard call to check for call compatibility.
1612 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1616 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1613 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1614 END IF;
1615 -- Initialize message list if p_init_msg_list is set to TRUE.
1617 FND_MSG_PUB.initialize;
1618 END IF;
1619
1620 -- Initialize API return status to success
1621 x_return_status := FND_API.G_RET_STS_SUCCESS;
1622
1623
1624
1625 -- Call delete version attachments for -99
1626 Delete_Ver_Attachments (
1627 p_api_version => l_api_version,
1628 p_init_msg_list => p_init_msg_list,
1629 p_validation_level => p_validation_level,
1630 p_commit => FND_API.G_FALSE,
1631
1632 p_business_document_type => p_business_document_type,
1633 p_business_document_id => p_business_document_id,
1634 p_business_document_version => -99,
1635
1636 x_msg_data => l_msg_data,
1637 x_msg_count => l_msg_count,
1638 x_return_status => l_return_status);
1639
1640 -- Check for errors
1641 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1642 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1643 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1644 RAISE FND_API.G_EXC_ERROR;
1645 END IF;
1646
1647 FOR attach_rec IN attach_csr(p_business_document_type, p_business_document_id) LOOP
1648
1649 -- Call delete version attachments for other versions
1650 Delete_Ver_Attachments (
1651 p_api_version => l_api_version,
1652 p_init_msg_list => p_init_msg_list,
1653 p_validation_level => p_validation_level,
1654 p_commit => FND_API.G_FALSE,
1655
1656 p_business_document_type => p_business_document_type,
1657 p_business_document_id => p_business_document_id,
1658 p_business_document_version => attach_rec.business_document_version,
1659
1660 x_msg_data => l_msg_data,
1661 x_msg_count => l_msg_count,
1662 x_return_status => l_return_status);
1663
1664 -- Check for errors
1665 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1666 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1667 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1668 RAISE FND_API.G_EXC_ERROR;
1669 END IF;
1670 END LOOP;
1671
1672 -- Standard check of p_commit
1673 IF FND_API.To_Boolean( p_commit ) THEN
1674 COMMIT WORK;
1675 END IF;
1676 -- Standard call to get message count and if count is 1, get message info.
1677 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1678
1679 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1680 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3900: Leaving Delete_Doc_Attachments');
1681 END IF;
1682
1683 EXCEPTION
1684 WHEN FND_API.G_EXC_ERROR THEN
1685 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1686 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4000: Leaving Delete_Doc_Attachments: OKC_API.G_EXCEPTION_ERROR Exception');
1687 END IF;
1688 ROLLBACK TO Delete_Doc_Attachments_GRP;
1689 x_return_status := G_RET_STS_ERROR ;
1690 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1691
1692 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1693 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1694 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4100: Leaving Delete_Doc_Attachments: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1695 END IF;
1696 ROLLBACK TO Delete_Doc_Attachments_GRP;
1697 x_return_status := G_RET_STS_UNEXP_ERROR ;
1698 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1699
1700 WHEN OTHERS THEN
1701 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1702 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4200: Leaving Delete_Doc_Attachments because of EXCEPTION: '||sqlerrm);
1703 END IF;
1704
1705 ROLLBACK TO Delete_Doc_Attachments_GRP;
1706 x_return_status := G_RET_STS_UNEXP_ERROR ;
1707 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1708 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1709 END IF;
1710 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1711 END Delete_Doc_Attachments;
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723 ---------------------------------------------------------------------------
1724 -- PROCEDURE Copy_Attachment_Docs - Deep copy for the attachments
1725 -- As part of the fix for bug 4115960, p_copy_primary_doc_flag='Y' will
1726 -- copy Contract category documents only.
1727 -- If p_copy_for_amendment = 'Y'
1728 -- copy all categories
1732 -- else if p_from_bus_doc_type = p_to_bus_doc_type
1729 -- else if p_copy_primary_doc_flag = 'Y"
1730 -- Copy PCD (in ref copy)
1731 -- Copy contract category docs (in deep copy)
1733 -- Copy only Contract and Support documents
1734 -- else
1735 -- copy all categories
1736 ---------------------------------------------------------------------------
1737 PROCEDURE Copy_Attachment_Docs(
1738 p_api_version IN NUMBER,
1739 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1740 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1741
1742 x_return_status OUT NOCOPY VARCHAR2,
1743 x_msg_count OUT NOCOPY NUMBER,
1744 x_msg_data OUT NOCOPY VARCHAR2,
1745
1746 p_from_bus_doc_type IN VARCHAR2,
1747 p_from_bus_doc_id IN NUMBER,
1748 p_from_bus_doc_version IN NUMBER := G_CURRENT_VERSION,
1749 p_to_bus_doc_type IN VARCHAR2,
1750 p_to_bus_doc_id IN NUMBER,
1751 p_to_bus_doc_version IN NUMBER := G_CURRENT_VERSION,
1752 p_copy_primary_doc_flag IN VARCHAR2 := 'N',
1753 p_copy_for_amendment IN VARCHAR2 := 'N'
1754
1755 ) IS l_api_name CONSTANT VARCHAR2(30) := 'Copy_Attachment_Docs';
1756 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1757 l_datatype_id NUMBER;
1758 l_msg_count NUMBER;
1759 l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
1760 l_api_version NUMBER := 1;
1761 l_business_document_type OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_TYPE%TYPE;
1762 l_business_document_id OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_ID%TYPE;
1763 l_business_document_version OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_VERSION%TYPE;
1764 l_from_version OKC_CONTRACT_DOCS.EFFECTIVE_FROM_VERSION%TYPE;
1765 l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
1766 l_contract_category_id FND_DOCUMENT_CATEGORIES.CATEGORY_ID%TYPE;
1767 l_supporting_category_id FND_DOCUMENT_CATEGORIES.CATEGORY_ID%TYPE;
1768 l_copy_all_docs_yn VARCHAR2(1);
1769 --Fix for bug 6468721 : Added code to copy SYSTEM-GENERATED ATTACHMENTS to the target Business Document.
1770 l_from_bus_doc_version_latest NUMBER;
1771 l_doc_version_view_name VARCHAR2(45);
1772 l_version_query VARCHAR2(1000);
1773
1774
1775 CURSOR doc_version_view_name_csr (l_from_bus_doc_type VARCHAR2) IS
1776 SELECT
1777 doc_version_view
1778 FROM okc_bus_doc_types_b
1779 WHERE document_type = l_from_bus_doc_type;
1780
1781
1782 CURSOR attach_csr(l_from_bus_doc_type VARCHAR2, l_from_bus_doc_id NUMBER,
1783 l_from_bus_doc_version NUMBER, l_to_bus_doc_type VARCHAR2,
1784 l_to_bus_doc_id NUMBER, l_to_bus_doc_version NUMBER,
1785 l_entity_name VARCHAR2, l_program_id NUMBER) IS
1786 SELECT
1787 KDOC.attached_document_id from_attached_document_id,
1788 KDOC.external_visibility_flag external_visibility_flag,
1789 KDOC.parent_attached_doc_id parent_attached_doc_id,
1790 KDOC.include_for_approval_flag include_for_approval_flag,
1791 KDOC.generated_flag generated_flag,
1792 KDOC.delete_flag delete_flag,
1793 FADB2.attached_document_id to_attached_document_id,
1794 KDOC.primary_contract_doc_flag,
1795 KDOC.mergeable_doc_flag
1796 FROM FND_ATTACHED_DOCUMENTS FADB1, OKC_CONTRACT_DOCS KDOC,
1797 FND_ATTACHED_DOCUMENTS FADB2
1798 WHERE
1799 KDOC.business_document_type = l_from_bus_doc_type
1800 AND KDOC.business_document_id = l_from_bus_doc_id
1801 AND KDOC.business_document_version = l_from_bus_doc_version
1802 AND KDOC.attached_document_id = FADB1.attached_document_id
1803 AND NVL(KDOC.delete_flag,'N') = 'N'
1804 AND FADB2.entity_name = l_entity_name
1805 AND FADB2.pk1_value = l_to_bus_doc_type
1806 AND FADB2.pk2_value = to_char(l_to_bus_doc_id)
1807 AND FADB2.pk3_value = to_char(l_to_bus_doc_version)
1808 AND FADB2.program_id = l_program_id
1809 AND FADB1.seq_num = FADB2.seq_num;
1810 -- Will fetch only contract and supporting docs
1811 CURSOR category_csr1 IS
1812 SELECT category_id from FND_DOCUMENT_CATEGORIES
1813 WHERE name in (G_CONTRACT_DOC_CATEGORY, G_SUPPORTING_DOC_CATEGORY);
1814
1815 -- Will fetch all seeded contract doc. categories
1816 CURSOR category_csr2 IS
1817 SELECT category_id from FND_DOCUMENT_CATEGORIES
1818 WHERE name in (G_CONTRACT_DOC_CATEGORY, G_SUPPORTING_DOC_CATEGORY, G_APP_ABSTRACT_CATEGORY, G_CONTRACT_IMAGE_CATEGORY);
1819
1820 -- Will fetch only 'Contract' category. Used if p_copy_primary_doc_flag='Y'
1821 CURSOR category_csr3 IS
1822 SELECT category_id from FND_DOCUMENT_CATEGORIES
1823 WHERE name in (G_CONTRACT_DOC_CATEGORY);
1824 -- Will fetch all seeded and custom (starting with 'OKC_REPO%') categories
1828 BEGIN
1825 CURSOR category_csr4 IS
1826 SELECT category_id from FND_DOCUMENT_CATEGORIES
1827 WHERE name like 'OKC_REPO_%';
1829 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1830 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1831 '4800: Entered Copy_Attachment_Docs');
1832 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1833 '4801: p_from_bus_doc_type: ' || p_from_bus_doc_type);
1834 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1835 '4802: p_from_bus_doc_id: ' || p_from_bus_doc_id);
1836 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1837 '4803: p_from_bus_doc_version: ' || p_from_bus_doc_version);
1838 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1839 '4804: p_to_bus_doc_type: ' || p_to_bus_doc_type);
1840 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1841 '4805: p_to_bus_doc_id: ' || p_to_bus_doc_id);
1842 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1843 '4806: p_to_bus_doc_version: ' || p_to_bus_doc_version);
1844 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1845 '4807: p_copy_primary_doc_flag: ' || p_copy_primary_doc_flag);
1846 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1847 '4808: p_copy_for_amendment: ' || p_copy_for_amendment);
1848 END IF;
1849
1850 -- Standard call to check for call compatibility.
1851 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1852 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1853 END IF;
1854
1855 -- Initialize API return status to success
1856 x_return_status := FND_API.G_RET_STS_SUCCESS;
1857 l_from_bus_doc_version_latest := p_from_bus_doc_version; --For bug 6468721
1858
1859 IF (p_copy_for_amendment = 'Y') THEN -- Copy called for amendment. Need to copy all categories
1860
1861
1862 FOR category_rec2 IN category_csr2
1863 LOOP
1864 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1865 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4820: Category Id : ' || category_rec2.category_id);
1866 END IF;
1867 -- Copy FND Attachments. Program Id is set to -9999.
1868 FND_ATTACHED_DOCUMENTS2_PKG.Copy_Attachments(
1869 X_from_entity_name => G_ATTACH_ENTITY_NAME,
1870 X_from_pk1_value => p_from_bus_doc_type,
1871 X_from_pk2_value => p_from_bus_doc_id,
1872 X_from_pk3_value => p_from_bus_doc_version,
1873 X_to_entity_name => G_ATTACH_ENTITY_NAME,
1874 X_to_pk1_value => p_to_bus_doc_type,
1875 X_to_pk2_value => p_to_bus_doc_id,
1876 X_to_pk3_value => p_to_bus_doc_version,
1877 X_program_id => G_COPY_PROGRAM_ID,
1878 X_from_category_id => category_rec2.category_id,
1879 X_to_category_id => category_rec2.category_id);
1880 END LOOP; -- category_rec2 IN category_csr2
1881 ELSIF (p_copy_primary_doc_flag = 'Y') THEN -- Copy Contract caregory only
1882 FOR category_rec3 IN category_csr3
1883 LOOP
1884 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1885 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4820: Category Id : ' || category_rec3.category_id);
1886 END IF;
1887 -- Copy FND Attachments. Program Id is set to -9999.
1888 FND_ATTACHED_DOCUMENTS2_PKG.Copy_Attachments(
1889 X_from_entity_name => G_ATTACH_ENTITY_NAME,
1890 X_from_pk1_value => p_from_bus_doc_type,
1891 X_from_pk2_value => p_from_bus_doc_id,
1892 X_from_pk3_value => p_from_bus_doc_version,
1893 X_to_entity_name => G_ATTACH_ENTITY_NAME,
1894 X_to_pk1_value => p_to_bus_doc_type,
1895 X_to_pk2_value => p_to_bus_doc_id,
1896 X_to_pk3_value => p_to_bus_doc_version,
1897 X_program_id => G_COPY_PROGRAM_ID,
1898 X_from_category_id => category_rec3.category_id,
1899 X_to_category_id => category_rec3.category_id);
1900 END LOOP; -- category_rec3 IN category_csr3
1901 ELSIF (p_from_bus_doc_type = p_to_bus_doc_type) THEN
1902 FND_PROFILE.GET(NAME => G_COPY_ALL_DOCS, VAL => l_copy_all_docs_yn);
1903 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1904 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1905 'Profile OKC_REP_COPY_ALL_CON_DOCS value is: '||l_copy_all_docs_yn);
1906 END IF;
1907 IF (l_copy_all_docs_yn = 'Y') THEN -- Need to copy all seeded and custom categories.
1908
1909 -- Fix for bug 6468721
1913 OPEN doc_version_view_name_csr(p_from_bus_doc_type);
1910 -- Get the doc_version_view name from okc_bus_doc_types_b
1911 -- to decide if l_version_query needs to be run
1912 IF NOT doc_version_view_name_csr%ISOPEN THEN
1914 FETCH doc_version_view_name_csr into l_doc_version_view_name;
1915 CLOSE doc_version_view_name_csr;
1916 END IF;
1917
1918 -- Get the value of l_from_bus_doc_version_latest only if the view name
1919 -- got from the previous step is not NULL.
1920
1921
1922 IF (l_doc_version_view_name IS NOT NULL) THEN
1923
1924 l_version_query := ' SELECT decode(a.archived_yn,''Y'',a.document_version,-99) latest_doc_version
1925 FROM '||l_doc_version_view_name||' a
1926 WHERE decode(a.document_version ,
1927 (SELECT max(b.document_version)
1928 FROM '||l_doc_version_view_name||' b
1929 WHERE b.document_type = a.document_type
1930 AND b.document_id = a.document_id) ,''Y'' ,''N'' ) = ''Y''
1931 AND a.document_type = '''||p_from_bus_doc_type||'''
1932 AND a.document_id = '''||p_from_bus_doc_id||'''';
1933
1934
1935 EXECUTE IMMEDIATE l_version_query INTO l_from_bus_doc_version_latest;
1936
1937 END IF;
1938
1939 FOR category_rec4 IN category_csr4
1940 LOOP
1941 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1942 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4820: Category Id : ' || category_rec4.category_id);
1943 END IF;
1944 -- Copy FND Attachments. Program Id is set to -9999.
1945 FND_ATTACHED_DOCUMENTS2_PKG.Copy_Attachments(
1946 X_from_entity_name => G_ATTACH_ENTITY_NAME,
1947 X_from_pk1_value => p_from_bus_doc_type,
1948 X_from_pk2_value => p_from_bus_doc_id,
1949 X_from_pk3_value => l_from_bus_doc_version_latest,
1950 X_to_entity_name => G_ATTACH_ENTITY_NAME,
1951 X_to_pk1_value => p_to_bus_doc_type,
1952 X_to_pk2_value => p_to_bus_doc_id,
1953 X_to_pk3_value => p_to_bus_doc_version,
1954 X_program_id => G_COPY_PROGRAM_ID,
1955 X_from_category_id => category_rec4.category_id,
1956 X_to_category_id => category_rec4.category_id);
1957 END LOOP; -- category_rec4 IN category_csr4
1958 ELSE
1959 FOR category_rec1 IN category_csr1 -- Copy only contract and supporting documents
1960 LOOP
1961 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1962 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4820: Category Id : ' || category_rec1.category_id);
1963 END IF;
1964 -- Copy FND Attachments. Program Id is set to -9999.
1965 FND_ATTACHED_DOCUMENTS2_PKG.Copy_Attachments(
1966 X_from_entity_name => G_ATTACH_ENTITY_NAME,
1967 X_from_pk1_value => p_from_bus_doc_type,
1968 X_from_pk2_value => p_from_bus_doc_id,
1969 X_from_pk3_value => p_from_bus_doc_version,
1970 X_to_entity_name => G_ATTACH_ENTITY_NAME,
1971 X_to_pk1_value => p_to_bus_doc_type,
1972 X_to_pk2_value => p_to_bus_doc_id,
1973 X_to_pk3_value => p_to_bus_doc_version,
1974 X_program_id => G_COPY_PROGRAM_ID,
1975 X_from_category_id => category_rec1.category_id,
1976 X_to_category_id => category_rec1.category_id);
1977 END LOOP; -- category_rec1 IN category_csr1
1978 END IF; -- l_copy_all_docs_yn = 'Y
1979 ELSE -- Copy all categories
1980 FOR category_rec2 IN category_csr2
1981 LOOP
1982 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1983 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4820: Category Id : ' || category_rec2.category_id);
1984 END IF;
1985 -- Copy FND Attachments. Program Id is set to -9999.
1986 FND_ATTACHED_DOCUMENTS2_PKG.Copy_Attachments(
1987 X_from_entity_name => G_ATTACH_ENTITY_NAME,
1988 X_from_pk1_value => p_from_bus_doc_type,
1989 X_from_pk2_value => p_from_bus_doc_id,
1990 X_from_pk3_value => p_from_bus_doc_version,
1991 X_to_entity_name => G_ATTACH_ENTITY_NAME,
1992 X_to_pk1_value => p_to_bus_doc_type,
1993 X_to_pk2_value => p_to_bus_doc_id,
1994 X_to_pk3_value => p_to_bus_doc_version,
1995 X_program_id => G_COPY_PROGRAM_ID,
1996 X_from_category_id => category_rec2.category_id,
2000
1997 X_to_category_id => category_rec2.category_id);
1998 END LOOP; -- category_rec2 IN category_csr2
1999 END IF; -- (p_copy_for_amendment = 'Y')
2001 -- Loop through the newly create record to get attached_doc_id in a table structure.
2002 FOR attach_rec IN attach_csr(p_from_bus_doc_type, p_from_bus_doc_id,
2003 l_from_bus_doc_version_latest, p_to_bus_doc_type, p_to_bus_doc_id,
2004 p_to_bus_doc_version, G_ATTACH_ENTITY_NAME, G_COPY_PROGRAM_ID)
2005 LOOP
2006
2007 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2008 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4830:doc type is: ' || p_to_bus_doc_type);
2009 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4831:doc id is: ' || p_to_bus_doc_id);
2010 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4832:doc version is: ' || p_to_bus_doc_version);
2011 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4833:The attached_doc_id being copied is: '
2012 || attach_rec.from_attached_document_id);
2013 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4834:The new attached_doc_id is: '
2014 || attach_rec.to_attached_document_id);
2015 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4835:The delete_flag is: '
2016 || attach_rec.delete_flag);
2017 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4836:The generated_flag is: '
2018 || attach_rec.generated_flag);
2019 END IF;
2020 Insert_Contract_Doc (
2021 p_api_version => l_api_version,
2022 p_init_msg_list => p_init_msg_list,
2023 p_validation_level => p_validation_level,
2024 p_business_document_type => p_to_bus_doc_type,
2025 p_business_document_id => p_to_bus_doc_id,
2026 p_business_document_version => p_to_bus_doc_version,
2027 p_attached_document_id => attach_rec.to_attached_document_id,
2028 p_external_visibility_flag => attach_rec.external_visibility_flag,
2029 p_effective_from_type => p_to_bus_doc_type,
2030 p_effective_from_id => p_to_bus_doc_id,
2031 p_effective_from_version => p_to_bus_doc_version,
2032 p_include_for_approval_flag => attach_rec.include_for_approval_flag,
2033 p_generated_flag => attach_rec.generated_flag,
2034 p_primary_contract_doc_flag => attach_rec.primary_contract_doc_flag,
2035 p_mergeable_doc_flag => attach_rec.mergeable_doc_flag,
2036 p_delete_flag => attach_rec.delete_flag,
2037 p_create_fnd_attach => 'N',
2038 p_program_id => NULL,
2039 p_program_application_id => NULL,
2040 p_request_id => NULL,
2041 p_program_update_date => NULL,
2042 p_versioning_flag => 'Y', -- Do Not Update Primary Flag when Copying or Versioning
2043 x_msg_data => l_msg_data,
2044 x_msg_count => l_msg_count,
2045 x_return_status => l_return_status,
2046 x_business_document_type => l_business_document_type,
2047 x_business_document_id => l_business_document_id,
2048 x_business_document_version => l_business_document_version,
2049 x_attached_document_id => l_attached_document_id);
2050
2051 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2052 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2053 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2054 RAISE FND_API.G_EXC_ERROR;
2055 END IF;
2056 END LOOP;
2057
2058 -- Standard call to get message count and if count is 1, get message info.
2059 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2060
2061 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2062 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4900: Leaving Copy_Attachment_Docs');
2063 END IF;
2064
2065 EXCEPTION
2066 WHEN FND_API.G_EXC_ERROR THEN
2067 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2068 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4840: Leaving Copy_Attachment_Docs: OKC_API.G_EXCEPTION_ERROR Exception');
2069 END IF;
2070
2071 --Bug 6468721
2072 IF (doc_version_view_name_csr%ISOPEN) THEN
2076 x_return_status := G_RET_STS_ERROR ;
2073 CLOSE doc_version_view_name_csr;
2074 END IF;
2075
2077 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2078
2079 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2080 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2081 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4841: Leaving Copy_Attachment_Docs: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2082 END IF;
2083
2084 --Bug 6468721
2085 IF (doc_version_view_name_csr%ISOPEN) THEN
2086 CLOSE doc_version_view_name_csr;
2087 END IF;
2088
2089 x_return_status := G_RET_STS_UNEXP_ERROR ;
2090 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2091
2092 WHEN OTHERS THEN
2093 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2094 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'482: Leaving Copy_Attachment_Docs because of EXCEPTION: '||sqlerrm);
2095 END IF;
2096
2097 --Bug 6468721
2098 IF (doc_version_view_name_csr%ISOPEN) THEN
2099 CLOSE doc_version_view_name_csr;
2100 END IF;
2101
2102 x_return_status := G_RET_STS_UNEXP_ERROR ;
2103 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2104 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2105 END IF;
2106 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2107
2108
2109 END Copy_Attachment_Docs;
2110
2111
2112
2113
2114 ---------------------------------------------------------------------------
2115 -- PROCEDURE Copy_Attachment_Refs - Reference copy for the attachments
2116 -- THIS API will be called by sourcing.
2117 -- If p_copy_for_amendment = 'Y'
2118 -- copy all categories
2119 -- else if p_copy_primary_doc_flag = 'Y"
2120 -- Copy PCD (in ref copy)
2121 -- Copy contract category docs (in deep copy)
2122 -- else if p_from_bus_doc_type = p_to_bus_doc_type
2123 -- Copy only Contract and Support documents
2124 -- else
2125 -- copy all categories
2126 ---------------------------------------------------------------------------
2127 PROCEDURE Copy_Attachment_Refs(
2128 p_api_version IN NUMBER,
2129 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2130 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2131 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2132
2133 x_return_status OUT NOCOPY VARCHAR2,
2134 x_msg_count OUT NOCOPY NUMBER,
2135 x_msg_data OUT NOCOPY VARCHAR2,
2136
2137 p_from_bus_doc_type IN VARCHAR2,
2138 p_from_bus_doc_id IN NUMBER,
2139 p_from_bus_doc_version IN NUMBER := G_CURRENT_VERSION,
2140 p_to_bus_doc_type IN VARCHAR2,
2141 p_to_bus_doc_id IN NUMBER,
2142 p_to_bus_doc_version IN NUMBER := G_CURRENT_VERSION,
2143 p_copy_primary_doc_flag IN VARCHAR2 := 'N',
2144 p_copy_for_amendment IN VARCHAR2 := 'N'
2145
2146 ) IS l_api_name CONSTANT VARCHAR2(30) := 'Copy_Attachment_Refs';
2147
2148 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2149 l_datatype_id NUMBER;
2150 l_msg_count NUMBER;
2151 l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
2152 l_api_version NUMBER := 1;
2153 l_business_document_type OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_TYPE%TYPE;
2154 l_business_document_id OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_ID%TYPE;
2155 l_business_document_version OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_VERSION%TYPE;
2156 l_from_version OKC_CONTRACT_DOCS.EFFECTIVE_FROM_VERSION%TYPE;
2157 l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
2158 l_category_name FND_DOCUMENT_CATEGORIES.NAME%TYPE;
2159 l_parent_attached_doc_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
2160
2161
2162 -- Attachment cursor to get the attachments for a given doc type, id and version
2163 CURSOR attach_csr(l_bus_doc_type VARCHAR2, l_bus_doc_id NUMBER, l_bus_doc_version NUMBER) IS
2164 SELECT * FROM OKC_CONTRACT_DOCS
2165 WHERE business_document_type = l_bus_doc_type
2166 AND business_document_id = l_bus_doc_id
2167 AND business_document_version = l_bus_doc_version
2168 AND delete_flag = 'N';
2169
2170 -- Get the category code for a given fnd attachment.
2171 CURSOR category_csr (l_attached_document_id NUMBER) IS
2172 SELECT fdc.name
2173 FROM fnd_attached_documents fad, fnd_documents fd, fnd_document_categories fdc
2174 WHERE fad.attached_document_id = l_attached_document_id
2178 BEGIN
2175 AND fad.document_id = fd.document_id
2176 AND fd.category_id = fdc.category_id;
2177
2179 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2180 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2181 '5000: Entered Copy_Attachment_Refs');
2182 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2183 '5001: p_from_bus_doc_type: ' || p_from_bus_doc_type);
2184 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2185 '5002: p_from_bus_doc_id: ' || p_from_bus_doc_id);
2186 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2187 '5003: p_from_bus_doc_version: ' || p_from_bus_doc_version);
2188 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2189 '5004: p_to_bus_doc_type: ' || p_to_bus_doc_type);
2190 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2191 '5005: p_to_bus_doc_id: ' || p_to_bus_doc_id);
2192 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2193 '5006: p_to_bus_doc_version: ' || p_to_bus_doc_version);
2194 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2195 '5007: p_copy_primary_doc_flag: ' || p_copy_primary_doc_flag);
2196 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2197 '5008: p_copy_for_amendment: ' || p_copy_for_amendment);
2198 END IF;
2199
2200 -- Standard call to check for call compatibility.
2201 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2202 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2203 END IF;
2204
2205 -- Initialize API return status to success
2206 x_return_status := FND_API.G_RET_STS_SUCCESS;
2207
2208
2209 -- Loop through the existing attachments to create new attachments
2210 FOR attach_rec IN attach_csr(p_from_bus_doc_type, p_from_bus_doc_id,
2211 p_from_bus_doc_version)
2212 LOOP
2213 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2214 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5030:doc type is: ' || p_to_bus_doc_type);
2215 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5040:doc id is: ' || p_to_bus_doc_id);
2216 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5050:doc version is: ' || p_to_bus_doc_version);
2217 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5060:attached_document_id is: ' || attach_rec.attached_document_id);
2218 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5070:effective_from_version is: ' || attach_rec.effective_from_version);
2219 END IF;
2220 -- Get the category name of the attachment being copied.
2221 OPEN category_csr(attach_rec.attached_document_id);
2222 FETCH category_csr INTO l_category_name;
2223 IF(category_csr%NOTFOUND) THEN
2224 RAISE NO_DATA_FOUND;
2225 END IF;
2226
2227 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2228 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5071: Category name is: ' || l_category_name);
2229 END IF;
2230
2231 -- Insert the record in OKC_CONTRACT_DOCS
2232 -- If p_copy_for_amendment = 'Y' copy everything
2233 -- p_copy_primary_doc_flag = 'Y' - copy only primary contract
2234 -- Do not copy attachments if source doc. type is same as destination doc. type and
2235 -- category is Approval abstract or Contract Image
2236 IF ((p_copy_for_amendment = 'Y') -- Copy everything
2237 OR
2238 ((p_copy_primary_doc_flag = 'N' OR attach_rec.primary_contract_doc_flag = 'Y' ) AND -- IF p_copy_primary_doc_flag, copy PCD
2239 (NOT ((p_from_bus_doc_type = p_to_bus_doc_type) -- IF doc types are same, copy only contract and supporting docs.
2240 AND (l_category_name = G_APP_ABSTRACT_CATEGORY OR l_category_name = G_CONTRACT_IMAGE_CATEGORY)))
2241 )
2242 ) THEN
2243 IF (attach_rec.parent_attached_doc_id IS NOT NULL) THEN
2244 l_parent_attached_doc_id := attach_rec.parent_attached_doc_id;
2245 ELSE
2246 l_parent_attached_doc_id := attach_rec.attached_document_id;
2247 END IF; -- attach_rec.parent_attached_doc_id <> NULL
2248 Insert_Contract_Doc (
2249 p_api_version => l_api_version,
2250 p_init_msg_list => p_init_msg_list,
2251 p_validation_level => p_validation_level,
2252 p_business_document_type => p_to_bus_doc_type,
2253 p_business_document_id => p_to_bus_doc_id,
2254 p_business_document_version => p_to_bus_doc_version,
2255 p_attached_document_id => attach_rec.attached_document_id,
2256 p_external_visibility_flag => attach_rec.external_visibility_flag,
2257 p_effective_from_type => attach_rec.effective_from_type,
2261 p_generated_flag => attach_rec.generated_flag,
2258 p_effective_from_id => attach_rec.effective_from_id,
2259 p_effective_from_version => attach_rec.effective_from_version,
2260 p_include_for_approval_flag => attach_rec.include_for_approval_flag,
2262 p_primary_contract_doc_flag => attach_rec.primary_contract_doc_flag,
2263 p_mergeable_doc_flag => attach_rec.mergeable_doc_flag,
2264 p_delete_flag => attach_rec.delete_flag,
2265 p_create_fnd_attach => 'Y',
2266 p_program_id => NULL,
2267 p_program_application_id => NULL,
2268 p_request_id => NULL,
2269 p_program_update_date => NULL,
2270 p_parent_attached_doc_id => l_parent_attached_doc_id,
2271 x_msg_data => l_msg_data,
2272 x_msg_count => l_msg_count,
2273 x_return_status => l_return_status,
2274 x_business_document_type => l_business_document_type,
2275 x_business_document_id => l_business_document_id,
2276 x_business_document_version => l_business_document_version,
2277 x_attached_document_id => l_attached_document_id);
2278 END IF; -- ((p_copy_for_amendment = 'Y') ......
2279 CLOSE category_csr;
2280 -- Check for errors
2281 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2283 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2284 RAISE FND_API.G_EXC_ERROR;
2285 END IF;
2286 END LOOP;
2287
2288
2289 -- Standard call to get message count and if count is 1, get message info.
2290 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2291 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2292 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5100: Leaving Copy_Attachment_Refs');
2293 END IF;
2294
2295 EXCEPTION
2296 WHEN FND_API.G_EXC_ERROR THEN
2297 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2298 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5081: Leaving Copy_Attachment_Refs: OKC_API.G_EXCEPTION_ERROR Exception');
2299 END IF;
2300 IF (category_csr%ISOPEN) THEN
2301 CLOSE category_csr ;
2302 END IF;
2303 x_return_status := G_RET_STS_ERROR ;
2304 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2305
2306 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2307 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2308 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5082: Leaving Copy_Attachment_Refs: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2309 END IF;
2310 IF (category_csr%ISOPEN) THEN
2311 CLOSE category_csr ;
2312 END IF;
2313 x_return_status := G_RET_STS_UNEXP_ERROR ;
2314 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2315
2316 WHEN OTHERS THEN
2317 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2318 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5083: Leaving Copy_Attachment_Refs because of EXCEPTION: '||sqlerrm);
2319 END IF;
2320 IF (category_csr%ISOPEN) THEN
2321 CLOSE category_csr ;
2322 END IF;
2323 x_return_status := G_RET_STS_UNEXP_ERROR ;
2324 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2325 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2326 END IF;
2327 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2328
2329 END Copy_Attachment_Refs;
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339 ---------------------------------------------------------------------------
2340 -- PROCEDURE Copy_Attachments - Copy attachments API
2341 -- If p_copy_for_amendment = 'Y'
2342 -- copy all categories
2343 -- else if p_copy_primary_doc_flag = 'Y"
2344 -- Copy PCD (in ref copy)
2345 -- Copy contract category docs (in deep copy)
2346 -- else if p_from_bus_doc_type = p_to_bus_doc_type
2347 -- Copy only Contract and Support documents
2348 -- else
2349 -- copy all categories
2350 ---------------------------------------------------------------------------
2351 PROCEDURE Copy_Attachments(
2352 p_api_version IN NUMBER,
2353 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2354 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2358 x_msg_count OUT NOCOPY NUMBER,
2355 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2356
2357 x_return_status OUT NOCOPY VARCHAR2,
2359 x_msg_data OUT NOCOPY VARCHAR2,
2360
2361 p_from_bus_doc_type IN VARCHAR2,
2362 p_from_bus_doc_id IN NUMBER,
2363 p_from_bus_doc_version IN NUMBER := G_CURRENT_VERSION,
2364 p_to_bus_doc_type IN VARCHAR2,
2365 p_to_bus_doc_id IN NUMBER,
2366 p_to_bus_doc_version IN NUMBER := G_CURRENT_VERSION,
2367 p_copy_by_ref IN VARCHAR2,
2368 p_copy_primary_doc_flag IN VARCHAR2,
2369 p_copy_for_amendment IN VARCHAR2
2370
2371 ) IS
2372 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Attachments';
2373 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2374 l_datatype_id FND_DOCUMENTS.DATATYPE_ID%TYPE;
2375 l_msg_count NUMBER;
2376 l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
2377 l_api_version NUMBER:= 1;
2378
2379
2380 BEGIN
2381 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2382 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2383 '5100: Entered Copy_Attachments');
2384 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2385 '5101: p_from_bus_doc_type: ' || p_from_bus_doc_type);
2386 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2387 '5102: p_from_bus_doc_id: ' || p_from_bus_doc_id);
2388 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2389 '5103: p_from_bus_doc_version: ' || p_from_bus_doc_version);
2390 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2391 '5104: p_to_bus_doc_type: ' || p_to_bus_doc_type);
2392 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2393 '5105: p_to_bus_doc_id: ' || p_to_bus_doc_id);
2394 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2395 '5106: p_to_bus_doc_version: ' || p_to_bus_doc_version);
2396 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2397 '5107: p_copy_by_ref: ' || p_copy_by_ref);
2398 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2399 '5108: p_copy_primary_doc_flag: ' || p_copy_primary_doc_flag);
2400 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2401 '5109: p_copy_for_amendment: ' || p_copy_for_amendment);
2402 END IF;
2403
2404 -- Standard Start of API savepoint
2405 SAVEPOINT Copy_Attachments_GRP;
2406 -- Standard call to check for call compatibility.
2407 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2408 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2409 END IF;
2410 -- Initialize message list if p_init_msg_list is set to TRUE.
2411 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2412 FND_MSG_PUB.initialize;
2413 END IF;
2414
2415 -- Initialize API return status to success
2416 x_return_status := FND_API.G_RET_STS_SUCCESS;
2417
2418 IF (p_copy_by_ref = 'Y') THEN
2419 Copy_Attachment_Refs(
2420 p_api_version => l_api_version,
2421 p_init_msg_list => p_init_msg_list,
2422 p_validation_level => p_validation_level,
2423 x_msg_data => l_msg_data,
2424 x_msg_count => l_msg_count,
2425 x_return_status => l_return_status,
2426 p_from_bus_doc_type => p_from_bus_doc_type,
2427 p_from_bus_doc_id => p_from_bus_doc_id,
2428 p_from_bus_doc_version => p_from_bus_doc_version,
2429 p_to_bus_doc_type => p_to_bus_doc_type,
2430 p_to_bus_doc_id => p_to_bus_doc_id,
2431 p_to_bus_doc_version => p_to_bus_doc_version,
2432 p_copy_primary_doc_flag => p_copy_primary_doc_flag,
2433 p_copy_for_amendment => p_copy_for_amendment
2434 );
2435 END IF;
2436 IF (p_copy_by_ref = 'N') THEN
2437
2438
2439 Copy_Attachment_Docs(
2440 p_api_version => l_api_version,
2441 p_init_msg_list => p_init_msg_list,
2442 p_validation_level => p_validation_level,
2443 x_msg_data => l_msg_data,
2444 x_msg_count => l_msg_count,
2445 x_return_status => l_return_status,
2446 p_from_bus_doc_type => p_from_bus_doc_type,
2447 p_from_bus_doc_id => p_from_bus_doc_id,
2448 p_from_bus_doc_version => p_from_bus_doc_version,
2449 p_to_bus_doc_type => p_to_bus_doc_type,
2450 p_to_bus_doc_id => p_to_bus_doc_id,
2454 );
2451 p_to_bus_doc_version => p_to_bus_doc_version,
2452 p_copy_primary_doc_flag => p_copy_primary_doc_flag,
2453 p_copy_for_amendment => p_copy_for_amendment
2455 END IF;
2456
2457
2458 -- Check for errors
2459 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2461 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2462 RAISE FND_API.G_EXC_ERROR;
2463 END IF;
2464
2465 -- Standard check of p_commit
2466 IF FND_API.To_Boolean( p_commit ) THEN
2467 COMMIT WORK;
2468 END IF;
2469 -- Standard call to get message count and if count is 1, get message info.
2470 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2471 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2472 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4400: Leaving Copy_Attachments');
2473 END IF;
2474
2475 EXCEPTION
2476 WHEN FND_API.G_EXC_ERROR THEN
2477 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2478 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4500: Leaving Copy_Attachments: OKC_API.G_EXCEPTION_ERROR Exception');
2479 END IF;
2480 ROLLBACK TO Copy_Attachments_GRP;
2481 x_return_status := G_RET_STS_ERROR ;
2482 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2483
2484 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2485 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2486 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4600: Leaving Copy_Attachments: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2487 END IF;
2488 ROLLBACK TO Copy_Attachments_GRP;
2489 x_return_status := G_RET_STS_UNEXP_ERROR ;
2490 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2491
2492 WHEN OTHERS THEN
2493 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2494 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4700: Leaving Copy_Attachments because of EXCEPTION: '||sqlerrm);
2495 END IF;
2496 ROLLBACK TO Copy_Attachments_GRP;
2497 x_return_status := G_RET_STS_UNEXP_ERROR ;
2498 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2499 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2500 END IF;
2501 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2502 END Copy_Attachments;
2503
2504
2505 PROCEDURE qa_doc(
2506 p_api_version IN NUMBER,
2507 x_return_status OUT NOCOPY VARCHAR2,
2508 x_msg_count OUT NOCOPY NUMBER,
2509 x_msg_data OUT NOCOPY VARCHAR2,
2510
2511 p_doc_type IN VARCHAR2,
2512 p_doc_id IN NUMBER,
2513
2514 x_qa_result_tbl OUT NOCOPY OKC_TERMS_QA_GRP.qa_result_tbl_type,
2515 x_qa_return_status OUT NOCOPY VARCHAR2
2516
2517 ) IS
2518
2519 l_api_name CONSTANT VARCHAR2(30) := 'qa_doc';
2520 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2521 l_msg_count NUMBER;
2522 l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
2523 l_api_version NUMBER:= 1;
2524 l_qa_name FND_LOOKUPS.MEANING%TYPE;
2525 l_severity_flag VARCHAR2(1) := 'W';
2526 l_perform_qa VARCHAR2(1) := 'N';
2527 l_indx NUMBER;
2528
2529 CURSOR l_get_qa_detail_csr IS
2530
2531 SELECT LOOKUPS.meaning qa_name,
2532 nvl(qa.severity_flag,G_QA_STS_WARNING) severity_flag ,
2533 decode(LOOKUPS.enabled_flag,'N','N','Y',decode(qa.enable_qa_yn,'N','N','Y'),'Y') perform_qa
2534 FROM OKC_LOOKUPS_V LOOKUPS, OKC_DOC_QA_LISTS QA
2535 WHERE QA.DOCUMENT_TYPE(+)=p_doc_type
2536 AND QA.QA_CODE(+) = LOOKUPS.LOOKUP_CODE
2537 AND LOOKUPS.LOOKUP_TYPE = 'OKC_TERM_QA_LIST'
2538 AND LOOKUPS.LOOKUP_CODE='CHECK_PRIMARY_CONTRACT';
2539
2540 BEGIN
2541 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2542 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4300: Entered qa_doc');
2543 END IF;
2544
2545 x_return_status := G_RET_STS_SUCCESS;
2546 x_qa_return_status := G_QA_STS_SUCCESS;
2547 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2548 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2549 END IF;
2550
2551 OPEN l_get_qa_detail_csr;
2552 FETCH l_get_qa_detail_csr INTO l_qa_name,l_severity_flag,l_perform_qa;
2553 CLOSE l_get_qa_detail_csr;
2554
2555 IF l_perform_qa = 'Y' THEN
2556 IF (OKC_TERMS_UTIL_GRP.get_contract_source_code(p_doc_type,p_doc_id) = 'ATTACHED' AND
2557 Has_Primary_Contract_Doc(p_doc_type,p_doc_id) = 'N' ) THEN
2558
2559 l_indx := x_qa_result_tbl.COUNT + 1;
2560
2561 x_qa_result_tbl(l_indx).document_type := p_doc_type;
2562 x_qa_result_tbl(l_indx).document_id := p_doc_id;
2563 x_qa_result_tbl(l_indx).error_record_type := 'DOCUMENT';
2564 x_qa_result_tbl(l_indx).article_id := Null;
2568 x_qa_result_tbl(l_indx).qa_code := 'CHECK_PRIMARY_CONTRACT';
2565 x_qa_result_tbl(l_indx).deliverable_id := Null;
2566 x_qa_result_tbl(l_indx).title := OKC_UTIL.DECODE_LOOKUP('OKC_CONTRACT_TERMS_SOURCES','ATTACHED');
2567 x_qa_result_tbl(l_indx).section_name := Null;
2569 x_qa_result_tbl(l_indx).message_name := 'OKC_REPO_DOC_QA_NO_PRIMARY';
2570 x_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message('OKC','OKC_REPO_DOC_QA_NO_PRIMARY_S');
2571 x_qa_result_tbl(l_indx).error_severity := l_severity_flag;
2572 x_qa_result_tbl(l_indx).problem_short_desc := l_qa_name;
2573 x_qa_result_tbl(l_indx).problem_details_short:= OKC_TERMS_UTIL_PVT.Get_Message('OKC', 'OKC_REPO_DOC_QA_NO_PRIMARY');
2574 x_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message('OKC', 'OKC_REPO_DOC_QA_NO_PRIMARY');
2575 x_qa_result_tbl(l_indx).creation_date := SYSDATE;
2576 x_qa_return_status := l_severity_flag;
2577 END IF;
2578
2579
2580 END IF;
2581
2582 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2583 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2300: Leaving QA_Doc');
2584 END IF;
2585 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2586
2587 EXCEPTION
2588 WHEN FND_API.G_EXC_ERROR THEN
2589 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2590 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2400: Leaving QA_Doc : OKC_API.G_EXCEPTION_ERROR Exception');
2591 END IF;
2592 IF (l_get_qa_detail_csr%ISOPEN) THEN
2593 CLOSE l_get_qa_detail_csr ;
2594 END IF;
2595 ROLLBACK TO g_QA_Doc;
2596 x_return_status := G_RET_STS_ERROR ;
2597 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2598
2599 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2600 ROLLBACK TO g_QA_Doc;
2601 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2602 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2500: Leaving QA_Doc : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2603 END IF;
2604 IF (l_get_qa_detail_csr%ISOPEN) THEN
2605 CLOSE l_get_qa_detail_csr ;
2606 END IF;
2607 ROLLBACK TO g_QA_Doc;
2608 x_return_status := G_RET_STS_UNEXP_ERROR ;
2609 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2610
2611 WHEN OTHERS THEN
2612 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2613 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2600: Leaving QA_Doc because of EXCEPTION: '||sqlerrm);
2614 END IF;
2615 IF (l_get_qa_detail_csr%ISOPEN) THEN
2616 CLOSE l_get_qa_detail_csr ;
2617 END IF;
2618 ROLLBACK TO g_QA_Doc;
2619 x_return_status := G_RET_STS_UNEXP_ERROR ;
2620 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2621 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2622 END IF;
2623 END QA_Doc;
2624
2625 -- Returns 'Y' - Attached document is oracle generated and mergeable.
2626 -- 'N' - Non recognised format, non mergeable.
2627 -- 'E' - Error.
2628 FUNCTION Is_Primary_Terms_Doc_Mergeable(
2629 p_document_type IN VARCHAR2,
2630 p_document_id IN NUMBER
2631 ) RETURN VARCHAR2 IS
2632 l_api_name CONSTANT VARCHAR2(30) := 'Is_Primary_Terms_Doc_mergeable';
2633 CURSOR contract_doc_csr IS
2634 SELECT 'Y'
2635 FROM okc_contract_docs
2636 WHERE business_document_type = p_document_type
2637 AND business_document_id = p_document_id
2638 AND mergeable_doc_flag='Y'
2639 AND primary_contract_doc_flag='Y'
2640 AND NVL(delete_flag,'N') = 'N'
2641 AND business_document_version = -99;
2642
2643 l_value VARCHAR2(1);
2644
2645 BEGIN
2646 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2647 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Entering Is_Primary_Terms_Doc_Mergeable');
2648 END IF;
2649 IF ( OKC_TERMS_UTIL_GRP.get_contract_source_code( p_document_type, p_document_id ) <> 'ATTACHED'
2650 OR OKC_TERMS_UTIL_GRP.get_authoring_party_code( p_document_type, p_document_id ) <> 'INTERNAL_ORG' )
2651 THEN
2652 RETURN 'N';
2653 END IF;
2654 OPEN contract_doc_csr;
2655 FETCH contract_doc_csr into l_value;
2656 CLOSE contract_doc_csr;
2657
2658 IF l_value = 'Y' THEN
2659 RETURN 'Y';
2660 ELSE
2661 RETURN 'N';
2662 END IF;
2663
2664 EXCEPTION
2665 WHEN OTHERS THEN
2666 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2667 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2000: Leaving Is_Primary_Terms_Doc_Mergeable because of EXCEPTION: '||sqlerrm);
2668 END IF;
2669 IF (contract_doc_csr%ISOPEN) THEN
2670 CLOSE contract_doc_csr ;
2671 END IF;
2672 RETURN 'E';
2673 END Is_Primary_Terms_Doc_Mergeable;
2674
2675 -- Returns FND_DOCUMENTS_TL.media_id of the Primary contract file for the current version of the document if it is non mergeable.
2676 -- 0 if document is mergeable.
2677 -- -1 if no primary document exists.
2678 FUNCTION Get_Primary_Terms_Doc_File_Id(
2679 p_document_type IN VARCHAR2,
2680 p_document_id IN NUMBER
2681 ) RETURN NUMBER IS
2682 l_api_name CONSTANT VARCHAR2(30) := 'Get_Primary_Terms_doc_File_Id';
2683 l_mergeable_doc_flag VARCHAR2(1) := '?';
2684 l_media_id FND_DOCUMENTS.MEDIA_ID%TYPE := -1;
2685
2686 CURSOR contract_doc_csr IS
2687
2688 SELECT b.media_id , docs.mergeable_doc_flag
2689 FROM OKC_CONTRACT_DOCS docs, FND_ATTACHED_DOCUMENTS fnd, FND_DOCUMENTS b
2690 WHERE docs.primary_contract_doc_flag = 'Y'
2691 AND NVL(docs.delete_flag,'N') = 'N'
2692 AND docs.business_document_version = -99
2693 AND docs.business_document_type = p_document_type
2694 AND docs.business_document_id = p_document_id
2695 AND docs.attached_document_id = fnd.attached_document_id
2696 AND fnd.document_id = b.document_id;
2697
2698 BEGIN
2699 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2700 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Entering Get_Primary_Terms_Doc_File_Id');
2701 END IF;
2702 OPEN contract_doc_csr;
2703 FETCH contract_doc_csr into l_media_id,l_mergeable_doc_flag;
2704 CLOSE contract_doc_csr;
2705
2706 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2707 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1900: Return File id value:'||l_media_id);
2708 END IF;
2709
2710 -- Start of fix for Bug 4085597
2711 IF OKC_TERMS_UTIL_GRP.get_contract_source_code( p_document_type, p_document_id ) = 'STRUCTURED'
2712 OR ( OKC_TERMS_UTIL_GRP.get_contract_source_code( p_document_type, p_document_id ) = 'ATTACHED'
2713 AND OKC_TERMS_UTIL_GRP.get_authoring_party_code( p_document_type, p_document_id ) = 'INTERNAL_ORG'
2714 AND l_mergeable_doc_flag = 'Y' )
2715 THEN
2716 l_media_id := 0;
2717 END IF;
2718 -- End of fix for Bug 4085597
2719
2720 RETURN l_media_id;
2721
2722 EXCEPTION
2723 WHEN OTHERS THEN
2724 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2725 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2000: Leaving Get_Primary_Terms_Doc_File_Id because of EXCEPTION: '||sqlerrm);
2726 END IF;
2727 IF (contract_doc_csr%ISOPEN) THEN
2728 CLOSE contract_doc_csr ;
2729 END IF;
2730 RETURN -1;
2731 END Get_Primary_Terms_Doc_File_Id;
2732
2733 FUNCTION Has_Primary_Contract_Doc(
2734 p_document_type IN VARCHAR2,
2735 p_document_id IN NUMBER
2736 ) RETURN VARCHAR2 IS
2737 l_api_name CONSTANT VARCHAR2(30) := 'Has_Primary_Contract_Doc';
2738 CURSOR contract_doc_csr IS
2739 SELECT '!'
2740 FROM OKC_CONTRACT_DOCS
2741 WHERE business_document_type = p_document_type
2742 AND business_document_id = p_document_id
2743 AND primary_contract_doc_flag = 'Y'
2744 AND NVL(delete_flag,'N') = 'N'
2745 AND business_document_version = -99;
2746
2747 l_result VARCHAR2(1) ;
2748
2749 BEGIN
2750 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2751 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Entering Has_Primary_Contract_Doc');
2752 END IF;
2753 OPEN contract_doc_csr;
2754 FETCH contract_doc_csr into l_result;
2755
2756 IF contract_doc_csr%FOUND THEN
2757 CLOSE contract_doc_csr;
2758 RETURN 'Y';
2759 ELSE
2760 CLOSE contract_doc_csr;
2761 RETURN 'N';
2762 END IF;
2763
2764 EXCEPTION
2765 WHEN OTHERS THEN
2766 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2767 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2000: Leaving Has_Primary_Contract_Doc because of EXCEPTION: '||sqlerrm);
2768 END IF;
2769 IF (contract_doc_csr%ISOPEN) THEN
2770 CLOSE contract_doc_csr ;
2771 END IF;
2772 RETURN 'E';
2773 END Has_Primary_Contract_Doc;
2774
2775 --Removes the Primary contract flag on the attachment for the latest business document version.
2776 PROCEDURE Clear_Primary_Doc_Flag(
2777 p_document_type IN VARCHAR2,
2778 p_document_id IN NUMBER,
2779
2780 x_return_status OUT NOCOPY VARCHAR2
2781 ) IS
2782 l_api_name CONSTANT VARCHAR2(30) := 'Clear_Primary_Doc_Flag';
2783 CURSOR primary_doc_csr IS
2784 SELECT attached_document_id,object_version_number
2785 FROM OKC_CONTRACT_DOCS
2786 WHERE business_document_type = p_document_type
2787 AND business_document_id = p_document_id
2788 AND primary_contract_doc_flag = 'Y'
2789 AND NVL(delete_flag,'N') = 'N'
2790 AND business_document_version = -99;
2791
2792 l_attached_document_id OKC_CONTRACT_DOCS.ATTACHED_DOCUMENT_ID%TYPE := NULL;
2793 l_object_version_number OKC_CONTRACT_DOCS.object_version_number%TYPE;
2794
2795 BEGIN
2796 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2797 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Entering Clear_Primary_Doc_Flag');
2798 END IF;
2799 x_return_status := G_RET_STS_SUCCESS;
2800
2801 OPEN primary_doc_csr;
2802 FETCH primary_doc_csr into l_attached_document_id,l_object_version_number;
2803 CLOSE primary_doc_csr;
2804
2805 IF l_attached_document_id IS NOT NULL THEN
2806 OKC_CONTRACT_DOCS_PVT.update_row(
2807 x_return_status => x_return_status,
2808 p_business_document_type => p_document_type,
2809 p_business_document_id => p_document_id,
2810 p_business_document_version => -99,
2811 p_attached_document_id => l_attached_document_id,
2812 p_primary_contract_doc_flag => 'N',
2813 p_object_version_number => l_object_version_number );
2814 END IF;
2815
2816
2817 EXCEPTION
2818 WHEN OTHERS THEN
2819 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2820 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2000: Leaving Clear_Primary_Doc_Flag because of EXCEPTION: '||sqlerrm);
2821 END IF;
2822 IF (primary_doc_csr%ISOPEN) THEN
2823 CLOSE primary_doc_csr ;
2824 END IF;
2825 x_return_status := G_RET_STS_UNEXP_ERROR ;
2826 END Clear_Primary_Doc_Flag;
2827
2828
2829 END OKC_CONTRACT_DOCS_GRP;