[Home] [Help]
PACKAGE BODY: APPS.OKC_CONTRACT_DOCS_GRP
Source
1 PACKAGE BODY OKC_CONTRACT_DOCS_GRP AS
2 /* $Header: OKCGCONTRACTDOCB.pls 120.5.12020000.4 2013/03/22 14:58:48 harchand 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: '
1295 || attach_rec.attached_document_id);
1296 END IF;
1297 -- Call FND delete attachment API to delete just the FND_ATTACHED_DOCUMENTS record
1298 FND_ATTACHED_DOCUMENTS3_PKG.DELETE_ROW(
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 -- For Bug 7045227
1340 -- IF(datatype_csr%NOTFOUND) THEN
1341 IF(datatype_csr%NOTFOUND AND ref_del_rec.delete_flag <> 'Y') THEN
1342 RAISE NO_DATA_FOUND;
1343 END IF;
1344 CLOSE datatype_csr;
1345 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1346 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2746: Deleting FND Attachments');
1347 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2747: l_datatype_id is: '
1348 || l_datatype_id);
1349 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2748: attached_document_id is: '
1350 || ref_del_rec.attached_document_id);
1351 END IF;
1352 -- Call FND delete attachment API to delete just the FND_ATTACHED_DOCUMENTS record
1353 FND_ATTACHED_DOCUMENTS3_PKG.DELETE_ROW(
1354 X_attached_document_id => ref_del_rec.attached_document_id,
1355 X_datatype_id => l_datatype_id,
1356 delete_document_flag => 'N'); -- Don't delete FND_DOCUMENTS records
1357 -- Delete record in OKC_CONTRACT_DOCS table
1358 OKC_CONTRACT_DOCS_PVT.Delete_Row(
1359 x_return_status => l_return_status,
1360 p_business_document_type => ref_del_rec.business_document_type,
1361 p_business_document_id => ref_del_rec.business_document_id,
1362 p_business_document_version => ref_del_rec.business_document_version,
1363 p_attached_document_id => ref_del_rec.attached_document_id,
1364 p_object_version_number => ref_del_rec.object_version_number
1365 );
1366 -- Check for errors
1367 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1368 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1369 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1370 RAISE FND_API.G_EXC_ERROR;
1371 END IF;
1372 END LOOP;
1373
1374 -- Standard check of p_commit
1375 IF FND_API.To_Boolean( p_commit ) THEN
1376 COMMIT WORK;
1377 END IF;
1378 -- Standard call to get message count and if count is 1, get message info.
1379 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1380
1381 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1382 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2800: Leaving Version_Attachments');
1383 END IF;
1384
1385 EXCEPTION
1386 WHEN FND_API.G_EXC_ERROR THEN
1387 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1388 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2900: Leaving Version_Attachments: OKC_API.G_EXCEPTION_ERROR Exception');
1389 END IF;
1390 IF (datatype_csr%ISOPEN) THEN
1391 CLOSE datatype_csr ;
1392 END IF;
1393 ROLLBACK TO Version_Attachments_GRP;
1394 x_return_status := G_RET_STS_ERROR ;
1395 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1396
1397 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1398 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1399 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3000: Leaving Version_Attachments: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1400 END IF;
1401 IF (datatype_csr%ISOPEN) THEN
1402 CLOSE datatype_csr ;
1403 END IF;
1404 ROLLBACK TO Version_Attachments_GRP;
1405 x_return_status := G_RET_STS_UNEXP_ERROR ;
1406 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1407
1408 WHEN OTHERS THEN
1409 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1410 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3100: Leaving Version_Attachments because of EXCEPTION: '||sqlerrm);
1411 END IF;
1412 IF (datatype_csr%ISOPEN) THEN
1413 CLOSE datatype_csr ;
1414 END IF;
1415 ROLLBACK TO Version_Attachments_GRP;
1416 x_return_status := G_RET_STS_UNEXP_ERROR ;
1417 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1418 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1419 END IF;
1420 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1421 END Version_Attachments;
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432 ---------------------------------------------------------------------------
1433 -- PROCEDURE Delete_Ver_Attachments
1434 ---------------------------------------------------------------------------
1435 PROCEDURE Delete_Ver_Attachments(
1436 p_api_version IN NUMBER,
1437 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1438 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1439 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1440
1441 x_return_status OUT NOCOPY VARCHAR2,
1442 x_msg_count OUT NOCOPY NUMBER,
1443 x_msg_data OUT NOCOPY VARCHAR2,
1444
1445 p_business_document_type IN VARCHAR2,
1446 p_business_document_id IN NUMBER,
1447 p_business_document_version IN NUMBER
1448
1449 ) IS
1450 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Ver_Attachments';
1451 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1452 l_msg_count NUMBER;
1453 l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
1454 l_api_version NUMBER := 1;
1455
1456 -- Attachment cursor
1457 CURSOR attach_csr(l_bus_doc_type VARCHAR2, l_bus_doc_id NUMBER, l_bus_doc_version NUMBER) IS
1458 SELECT * FROM OKC_CONTRACT_DOCS
1459 WHERE business_document_type = l_bus_doc_type
1460 AND business_document_id = l_bus_doc_id
1461 AND business_document_version = l_bus_doc_version;
1462
1463
1464 BEGIN
1465 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1466 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1467 '3200: Entered Delete_Ver_Attachments');
1468 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1469 '3201: p_business_document_type : ' || p_business_document_type );
1470 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1471 '3202: p_business_document_id : ' || p_business_document_id );
1472 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1473 '3203: p_business_document_version : ' || p_business_document_version );
1474 END IF;
1475
1476 -- Standard Start of API savepoint
1477 SAVEPOINT Delete_Ver_Attachments_GRP;
1478
1479 -- Standard call to check for call compatibility.
1480 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1481 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1482 END IF;
1483 -- Initialize message list if p_init_msg_list is set to TRUE.
1484 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1485 FND_MSG_PUB.initialize;
1486 END IF;
1487
1488 -- Initialize API return status to success
1489 x_return_status := FND_API.G_RET_STS_SUCCESS;
1490
1491 FOR attach_rec IN attach_csr(p_business_document_type, p_business_document_id,
1492 p_business_document_version) LOOP
1493 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1494 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3210:doc version is: ' || attach_rec.business_document_version);
1495 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3220:attch doc id is: ' || attach_rec.attached_document_id);
1496 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3230:effective_from_version is: ' || attach_rec.effective_from_version);
1497 END IF;
1498
1499 -- Delete OKC_CONTRACT_DOCS record
1500 Delete_Contract_Doc (
1501 p_api_version => l_api_version,
1502 p_init_msg_list => p_init_msg_list,
1503
1504 p_business_document_type => p_business_document_type,
1505 p_business_document_id => p_business_document_id,
1506 p_business_document_version => p_business_document_version,
1507
1508 p_attached_document_id => attach_rec.attached_document_id,
1509 p_object_version_number => NULL,
1510 x_msg_data => l_msg_data,
1511 x_msg_count => l_msg_count,
1512 x_return_status => l_return_status);
1513
1514 -- Check for errors
1515 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1516 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1517 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1518 RAISE FND_API.G_EXC_ERROR;
1519 END IF;
1520 END LOOP;
1521
1522 -- Standard check of p_commit
1523 IF FND_API.To_Boolean( p_commit ) THEN
1524 COMMIT WORK;
1525 END IF;
1526 -- Standard call to get message count and if count is 1, get message info.
1527 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1528
1529 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1530 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3300: Leaving Delete_Ver_Attachments');
1531 END IF;
1532
1533 EXCEPTION
1534 WHEN FND_API.G_EXC_ERROR THEN
1535 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1536 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3400: Leaving Delete_Ver_Attachments: OKC_API.G_EXCEPTION_ERROR Exception');
1537 END IF;
1538 ROLLBACK TO Delete_Ver_Attachments_GRP;
1539 x_return_status := G_RET_STS_ERROR ;
1540 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1541
1542 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1543 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1544 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3500: Leaving Delete_Ver_Attachments: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1545 END IF;
1546 ROLLBACK TO Delete_Ver_Attachments_GRP;
1547 x_return_status := G_RET_STS_UNEXP_ERROR ;
1548 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1549
1550 WHEN OTHERS THEN
1551 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1552 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3600: Leaving Delete_Ver_Attachments because of EXCEPTION: '||sqlerrm);
1553 END IF;
1554
1555 ROLLBACK TO Delete_Ver_Attachments_GRP;
1556 x_return_status := G_RET_STS_UNEXP_ERROR ;
1557 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1558 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1559 END IF;
1560 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1561
1562 END Delete_Ver_Attachments;
1563
1564
1565
1566
1567
1568
1569 ---------------------------------------------------------------------------
1570 -- PROCEDURE Delete_Doc_Attachments
1571 ---------------------------------------------------------------------------
1572 PROCEDURE Delete_Doc_Attachments(
1573 p_api_version IN NUMBER,
1574 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1575 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1576 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1577
1578 x_return_status OUT NOCOPY VARCHAR2,
1579 x_msg_count OUT NOCOPY NUMBER,
1580 x_msg_data OUT NOCOPY VARCHAR2,
1581
1582 p_business_document_type IN VARCHAR2,
1583 p_business_document_id IN NUMBER
1584
1585 ) IS
1586 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Doc_Attachments';
1587 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1588 l_msg_count NUMBER;
1589 l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
1590 l_api_version NUMBER := 1;
1591
1592 -- Attachment cursor
1593 CURSOR attach_csr(l_bus_doc_type VARCHAR2, l_bus_doc_id NUMBER) IS
1594 SELECT distinct business_document_version FROM OKC_CONTRACT_DOCS
1595 WHERE business_document_type = l_bus_doc_type
1596 AND business_document_id = l_bus_doc_id
1597 AND business_document_version <> -99
1598 ORDER by business_document_version DESC ;
1599
1600
1601 BEGIN
1602 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1603 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1604 '3900: Entered Delete_Doc_Attachments');
1605 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1606 '3901: p_business_document_type: ' || p_business_document_type);
1607 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1608 '3902: p_business_document_id: ' || p_business_document_id);
1609 END IF;
1610
1611 -- Standard Start of API savepoint
1612 SAVEPOINT Delete_Doc_Attachments_GRP;
1613 -- Standard call to check for call compatibility.
1614 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1615 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1616 END IF;
1617 -- Initialize message list if p_init_msg_list is set to TRUE.
1618 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1619 FND_MSG_PUB.initialize;
1620 END IF;
1621
1622 -- Initialize API return status to success
1623 x_return_status := FND_API.G_RET_STS_SUCCESS;
1624
1625
1626
1627 -- Call delete version attachments for -99
1628 Delete_Ver_Attachments (
1629 p_api_version => l_api_version,
1630 p_init_msg_list => p_init_msg_list,
1631 p_validation_level => p_validation_level,
1632 p_commit => FND_API.G_FALSE,
1633
1634 p_business_document_type => p_business_document_type,
1635 p_business_document_id => p_business_document_id,
1636 p_business_document_version => -99,
1637
1638 x_msg_data => l_msg_data,
1639 x_msg_count => l_msg_count,
1640 x_return_status => l_return_status);
1641
1642 -- Check for errors
1643 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1644 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1645 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1646 RAISE FND_API.G_EXC_ERROR;
1647 END IF;
1648
1649 FOR attach_rec IN attach_csr(p_business_document_type, p_business_document_id) LOOP
1650
1651 -- Call delete version attachments for other versions
1652 Delete_Ver_Attachments (
1653 p_api_version => l_api_version,
1654 p_init_msg_list => p_init_msg_list,
1655 p_validation_level => p_validation_level,
1656 p_commit => FND_API.G_FALSE,
1657
1658 p_business_document_type => p_business_document_type,
1659 p_business_document_id => p_business_document_id,
1660 p_business_document_version => attach_rec.business_document_version,
1661
1662 x_msg_data => l_msg_data,
1663 x_msg_count => l_msg_count,
1664 x_return_status => l_return_status);
1665
1666 -- Check for errors
1667 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1668 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1669 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1670 RAISE FND_API.G_EXC_ERROR;
1671 END IF;
1672 END LOOP;
1673
1674 -- Standard check of p_commit
1675 IF FND_API.To_Boolean( p_commit ) THEN
1676 COMMIT WORK;
1677 END IF;
1678 -- Standard call to get message count and if count is 1, get message info.
1679 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1680
1681 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1682 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3900: Leaving Delete_Doc_Attachments');
1683 END IF;
1684
1685 EXCEPTION
1686 WHEN FND_API.G_EXC_ERROR THEN
1687 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1688 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4000: Leaving Delete_Doc_Attachments: OKC_API.G_EXCEPTION_ERROR Exception');
1689 END IF;
1690 ROLLBACK TO Delete_Doc_Attachments_GRP;
1691 x_return_status := G_RET_STS_ERROR ;
1692 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1693
1694 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1695 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1696 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4100: Leaving Delete_Doc_Attachments: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1697 END IF;
1698 ROLLBACK TO Delete_Doc_Attachments_GRP;
1699 x_return_status := G_RET_STS_UNEXP_ERROR ;
1700 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1701
1702 WHEN OTHERS THEN
1703 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1704 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4200: Leaving Delete_Doc_Attachments because of EXCEPTION: '||sqlerrm);
1705 END IF;
1706
1707 ROLLBACK TO Delete_Doc_Attachments_GRP;
1708 x_return_status := G_RET_STS_UNEXP_ERROR ;
1709 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1710 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1711 END IF;
1712 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1713 END Delete_Doc_Attachments;
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725 ---------------------------------------------------------------------------
1726 -- PROCEDURE Copy_Attachment_Docs - Deep copy for the attachments
1727 -- As part of the fix for bug 4115960, p_copy_primary_doc_flag='Y' will
1728 -- copy Contract category documents only.
1729 -- If p_copy_for_amendment = 'Y'
1730 -- copy all categories
1731 -- else if p_copy_primary_doc_flag = 'Y"
1732 -- Copy PCD (in ref copy)
1733 -- Copy contract category docs (in deep copy)
1734 -- else if p_from_bus_doc_type = p_to_bus_doc_type
1735 -- Copy only Contract and Support documents
1736 -- else
1737 -- copy all categories
1738 ---------------------------------------------------------------------------
1739 PROCEDURE Copy_Attachment_Docs(
1740 p_api_version IN NUMBER,
1741 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1742 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1743
1744 x_return_status OUT NOCOPY VARCHAR2,
1745 x_msg_count OUT NOCOPY NUMBER,
1746 x_msg_data OUT NOCOPY VARCHAR2,
1747
1748 p_from_bus_doc_type IN VARCHAR2,
1749 p_from_bus_doc_id IN NUMBER,
1750 p_from_bus_doc_version IN NUMBER := G_CURRENT_VERSION,
1751 p_to_bus_doc_type IN VARCHAR2,
1752 p_to_bus_doc_id IN NUMBER,
1753 p_to_bus_doc_version IN NUMBER := G_CURRENT_VERSION,
1754 p_copy_primary_doc_flag IN VARCHAR2 := 'N',
1755 p_copy_for_amendment IN VARCHAR2 := 'N'
1756
1757 ) IS l_api_name CONSTANT VARCHAR2(30) := 'Copy_Attachment_Docs';
1758 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1759 l_datatype_id NUMBER;
1760 l_msg_count NUMBER;
1761 l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
1762 l_api_version NUMBER := 1;
1763 l_business_document_type OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_TYPE%TYPE;
1764 l_business_document_id OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_ID%TYPE;
1765 l_business_document_version OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_VERSION%TYPE;
1766 l_from_version OKC_CONTRACT_DOCS.EFFECTIVE_FROM_VERSION%TYPE;
1767 l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
1768 l_contract_category_id FND_DOCUMENT_CATEGORIES.CATEGORY_ID%TYPE;
1769 l_supporting_category_id FND_DOCUMENT_CATEGORIES.CATEGORY_ID%TYPE;
1770 l_copy_all_docs_yn VARCHAR2(1);
1771 --Fix for bug 6468721 : Added code to copy SYSTEM-GENERATED ATTACHMENTS to the target Business Document.
1772 l_from_bus_doc_version_latest NUMBER;
1773 l_doc_version_view_name VARCHAR2(45);
1774 l_version_query VARCHAR2(1000);
1775
1776
1777 CURSOR doc_version_view_name_csr (l_from_bus_doc_type VARCHAR2) IS
1778 SELECT
1779 doc_version_view
1780 FROM okc_bus_doc_types_b
1781 WHERE document_type = l_from_bus_doc_type;
1782
1783
1784 CURSOR attach_csr(l_from_bus_doc_type VARCHAR2, l_from_bus_doc_id NUMBER,
1785 l_from_bus_doc_version NUMBER, l_to_bus_doc_type VARCHAR2,
1786 l_to_bus_doc_id NUMBER, l_to_bus_doc_version NUMBER,
1787 l_entity_name VARCHAR2, l_program_id NUMBER) IS
1788 SELECT
1789 KDOC.attached_document_id from_attached_document_id,
1790 KDOC.external_visibility_flag external_visibility_flag,
1791 KDOC.parent_attached_doc_id parent_attached_doc_id,
1792 KDOC.include_for_approval_flag include_for_approval_flag,
1793 KDOC.generated_flag generated_flag,
1794 KDOC.delete_flag delete_flag,
1795 FADB2.attached_document_id to_attached_document_id,
1796 KDOC.primary_contract_doc_flag,
1797 KDOC.mergeable_doc_flag
1798 FROM FND_ATTACHED_DOCUMENTS FADB1, OKC_CONTRACT_DOCS KDOC,
1799 FND_ATTACHED_DOCUMENTS FADB2,
1800 FND_DOCUMENTS_vl doc1,
1801 FND_DOCUMENTS_vl doc2
1802 WHERE
1803 KDOC.business_document_type = l_from_bus_doc_type
1804 AND KDOC.business_document_id = l_from_bus_doc_id
1805 AND KDOC.business_document_version = l_from_bus_doc_version
1806 AND KDOC.attached_document_id = FADB1.attached_document_id
1807 AND NVL(KDOC.delete_flag,'N') = 'N'
1808 AND FADB2.entity_name = l_entity_name
1809 AND FADB2.pk1_value = l_to_bus_doc_type
1810 AND FADB2.pk2_value = to_char(l_to_bus_doc_id)
1811 AND FADB2.pk3_value = to_char(l_to_bus_doc_version)
1812 AND FADB2.program_id = l_program_id
1813 --Bug 16240419 AND FADB1.seq_num = FADB2.seq_num;
1814 AND FADB1.document_id = doc1.document_id
1815 AND FADB2.document_id = doc2.document_id
1816 AND doc1.datatype_name = doc2.datatype_name
1817 AND Nvl(doc1.file_name,'@') = Nvl(doc2.file_name,'@')
1818 AND doc1.category_id = doc2.category_id
1819 AND Nvl(doc1.url,'@') = Nvl(doc2.url,'@');
1820
1821 -- Will fetch only contract and supporting docs
1822 CURSOR category_csr1 IS
1823 SELECT category_id from FND_DOCUMENT_CATEGORIES
1824 WHERE name in (G_CONTRACT_DOC_CATEGORY, G_SUPPORTING_DOC_CATEGORY);
1825
1826 -- Will fetch all seeded contract doc. categories
1827 CURSOR category_csr2 IS
1828 SELECT category_id from FND_DOCUMENT_CATEGORIES
1829 WHERE name in (G_CONTRACT_DOC_CATEGORY, G_SUPPORTING_DOC_CATEGORY, G_APP_ABSTRACT_CATEGORY, G_CONTRACT_IMAGE_CATEGORY);
1830
1831 -- Will fetch only 'Contract' category. Used if p_copy_primary_doc_flag='Y'
1832 CURSOR category_csr3 IS
1833 SELECT category_id from FND_DOCUMENT_CATEGORIES
1834 WHERE name in (G_CONTRACT_DOC_CATEGORY);
1835 -- Will fetch all seeded and custom (starting with 'OKC_REPO%') categories
1836 CURSOR category_csr4 IS
1837 SELECT category_id from FND_DOCUMENT_CATEGORIES
1838 WHERE name like 'OKC_REPO_%';
1839 BEGIN
1840 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1841 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1842 '4800: Entered Copy_Attachment_Docs');
1843 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1844 '4801: p_from_bus_doc_type: ' || p_from_bus_doc_type);
1845 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1846 '4802: p_from_bus_doc_id: ' || p_from_bus_doc_id);
1847 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1848 '4803: p_from_bus_doc_version: ' || p_from_bus_doc_version);
1849 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1850 '4804: p_to_bus_doc_type: ' || p_to_bus_doc_type);
1851 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1852 '4805: p_to_bus_doc_id: ' || p_to_bus_doc_id);
1853 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1854 '4806: p_to_bus_doc_version: ' || p_to_bus_doc_version);
1855 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1856 '4807: p_copy_primary_doc_flag: ' || p_copy_primary_doc_flag);
1857 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1858 '4808: p_copy_for_amendment: ' || p_copy_for_amendment);
1859 END IF;
1860
1861 -- Standard call to check for call compatibility.
1862 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1863 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1864 END IF;
1865
1866 -- Initialize API return status to success
1867 x_return_status := FND_API.G_RET_STS_SUCCESS;
1868 l_from_bus_doc_version_latest := p_from_bus_doc_version; --For bug 6468721
1869
1870 IF (p_copy_for_amendment = 'Y') THEN -- Copy called for amendment. Need to copy all categories
1871
1872
1873 FOR category_rec2 IN category_csr2
1874 LOOP
1875 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1876 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4820: Category Id : ' || category_rec2.category_id);
1877 END IF;
1878 -- Copy FND Attachments. Program Id is set to -9999.
1879 FND_ATTACHED_DOCUMENTS2_PKG.Copy_Attachments(
1880 X_from_entity_name => G_ATTACH_ENTITY_NAME,
1881 X_from_pk1_value => p_from_bus_doc_type,
1882 X_from_pk2_value => p_from_bus_doc_id,
1883 X_from_pk3_value => p_from_bus_doc_version,
1884 X_to_entity_name => G_ATTACH_ENTITY_NAME,
1885 X_to_pk1_value => p_to_bus_doc_type,
1886 X_to_pk2_value => p_to_bus_doc_id,
1887 X_to_pk3_value => p_to_bus_doc_version,
1888 X_program_id => G_COPY_PROGRAM_ID,
1889 X_from_category_id => category_rec2.category_id,
1890 X_to_category_id => category_rec2.category_id);
1891 END LOOP; -- category_rec2 IN category_csr2
1892 ELSIF (p_copy_primary_doc_flag = 'Y') THEN -- Copy Contract caregory only
1893 FOR category_rec3 IN category_csr3
1894 LOOP
1895 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1896 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4820: Category Id : ' || category_rec3.category_id);
1897 END IF;
1898 -- Copy FND Attachments. Program Id is set to -9999.
1899 FND_ATTACHED_DOCUMENTS2_PKG.Copy_Attachments(
1900 X_from_entity_name => G_ATTACH_ENTITY_NAME,
1901 X_from_pk1_value => p_from_bus_doc_type,
1902 X_from_pk2_value => p_from_bus_doc_id,
1903 X_from_pk3_value => p_from_bus_doc_version,
1904 X_to_entity_name => G_ATTACH_ENTITY_NAME,
1905 X_to_pk1_value => p_to_bus_doc_type,
1906 X_to_pk2_value => p_to_bus_doc_id,
1907 X_to_pk3_value => p_to_bus_doc_version,
1908 X_program_id => G_COPY_PROGRAM_ID,
1909 X_from_category_id => category_rec3.category_id,
1910 X_to_category_id => category_rec3.category_id);
1911 END LOOP; -- category_rec3 IN category_csr3
1912 ELSIF (p_from_bus_doc_type = p_to_bus_doc_type) THEN
1913 FND_PROFILE.GET(NAME => G_COPY_ALL_DOCS, VAL => l_copy_all_docs_yn);
1914 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1915 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1916 'Profile OKC_REP_COPY_ALL_CON_DOCS value is: '||l_copy_all_docs_yn);
1917 END IF;
1918 IF (l_copy_all_docs_yn = 'Y') THEN -- Need to copy all seeded and custom categories.
1919
1920 -- Fix for bug 6468721
1921 -- Get the doc_version_view name from okc_bus_doc_types_b
1922 -- to decide if l_version_query needs to be run
1923 IF NOT doc_version_view_name_csr%ISOPEN THEN
1924 OPEN doc_version_view_name_csr(p_from_bus_doc_type);
1925 FETCH doc_version_view_name_csr into l_doc_version_view_name;
1926 CLOSE doc_version_view_name_csr;
1927 END IF;
1928
1929 -- Get the value of l_from_bus_doc_version_latest only if the view name
1930 -- got from the previous step is not NULL.
1931
1932
1933 IF (l_doc_version_view_name IS NOT NULL) THEN
1934
1935 l_version_query := ' SELECT decode(a.archived_yn,''Y'',a.document_version,-99) latest_doc_version
1936 FROM '||l_doc_version_view_name||' a
1937 WHERE decode(a.document_version ,
1938 (SELECT max(b.document_version)
1939 FROM '||l_doc_version_view_name||' b
1940 WHERE b.document_type = a.document_type
1941 AND b.document_id = a.document_id) ,''Y'' ,''N'' ) = ''Y''
1942 AND a.document_type = '''||p_from_bus_doc_type||'''
1943 AND a.document_id = '''||p_from_bus_doc_id||'''';
1944
1945
1946 EXECUTE IMMEDIATE l_version_query INTO l_from_bus_doc_version_latest;
1947
1948 END IF;
1949
1950 FOR category_rec4 IN category_csr4
1951 LOOP
1952 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1953 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4820: Category Id : ' || category_rec4.category_id);
1954 END IF;
1955 -- Copy FND Attachments. Program Id is set to -9999.
1956 FND_ATTACHED_DOCUMENTS2_PKG.Copy_Attachments(
1957 X_from_entity_name => G_ATTACH_ENTITY_NAME,
1958 X_from_pk1_value => p_from_bus_doc_type,
1959 X_from_pk2_value => p_from_bus_doc_id,
1960 X_from_pk3_value => l_from_bus_doc_version_latest,
1961 X_to_entity_name => G_ATTACH_ENTITY_NAME,
1962 X_to_pk1_value => p_to_bus_doc_type,
1963 X_to_pk2_value => p_to_bus_doc_id,
1964 X_to_pk3_value => p_to_bus_doc_version,
1965 X_program_id => G_COPY_PROGRAM_ID,
1966 X_from_category_id => category_rec4.category_id,
1967 X_to_category_id => category_rec4.category_id);
1968 END LOOP; -- category_rec4 IN category_csr4
1969 ELSE
1970 FOR category_rec1 IN category_csr1 -- Copy only contract and supporting documents
1971 LOOP
1972 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1973 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4820: Category Id : ' || category_rec1.category_id);
1974 END IF;
1975 -- Copy FND Attachments. Program Id is set to -9999.
1976 FND_ATTACHED_DOCUMENTS2_PKG.Copy_Attachments(
1977 X_from_entity_name => G_ATTACH_ENTITY_NAME,
1978 X_from_pk1_value => p_from_bus_doc_type,
1979 X_from_pk2_value => p_from_bus_doc_id,
1980 X_from_pk3_value => p_from_bus_doc_version,
1981 X_to_entity_name => G_ATTACH_ENTITY_NAME,
1982 X_to_pk1_value => p_to_bus_doc_type,
1983 X_to_pk2_value => p_to_bus_doc_id,
1984 X_to_pk3_value => p_to_bus_doc_version,
1985 X_program_id => G_COPY_PROGRAM_ID,
1986 X_from_category_id => category_rec1.category_id,
1987 X_to_category_id => category_rec1.category_id);
1988 END LOOP; -- category_rec1 IN category_csr1
1989 END IF; -- l_copy_all_docs_yn = 'Y
1990 ELSE -- Copy all categories
1991 FOR category_rec2 IN category_csr2
1992 LOOP
1993 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1994 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4820: Category Id : ' || category_rec2.category_id);
1995 END IF;
1996 -- Copy FND Attachments. Program Id is set to -9999.
1997 FND_ATTACHED_DOCUMENTS2_PKG.Copy_Attachments(
1998 X_from_entity_name => G_ATTACH_ENTITY_NAME,
1999 X_from_pk1_value => p_from_bus_doc_type,
2000 X_from_pk2_value => p_from_bus_doc_id,
2001 X_from_pk3_value => p_from_bus_doc_version,
2002 X_to_entity_name => G_ATTACH_ENTITY_NAME,
2003 X_to_pk1_value => p_to_bus_doc_type,
2004 X_to_pk2_value => p_to_bus_doc_id,
2005 X_to_pk3_value => p_to_bus_doc_version,
2006 X_program_id => G_COPY_PROGRAM_ID,
2007 X_from_category_id => category_rec2.category_id,
2008 X_to_category_id => category_rec2.category_id);
2009 END LOOP; -- category_rec2 IN category_csr2
2010 END IF; -- (p_copy_for_amendment = 'Y')
2011
2012 -- Loop through the newly create record to get attached_doc_id in a table structure.
2013 FOR attach_rec IN attach_csr(p_from_bus_doc_type, p_from_bus_doc_id,
2014 l_from_bus_doc_version_latest, p_to_bus_doc_type, p_to_bus_doc_id,
2015 p_to_bus_doc_version, G_ATTACH_ENTITY_NAME, G_COPY_PROGRAM_ID)
2016 LOOP
2017
2018 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2019 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4830:doc type is: ' || p_to_bus_doc_type);
2020 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4831:doc id is: ' || p_to_bus_doc_id);
2021 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4832:doc version is: ' || p_to_bus_doc_version);
2022 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4833:The attached_doc_id being copied is: '
2023 || attach_rec.from_attached_document_id);
2024 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4834:The new attached_doc_id is: '
2025 || attach_rec.to_attached_document_id);
2026 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4835:The delete_flag is: '
2027 || attach_rec.delete_flag);
2028 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4836:The generated_flag is: '
2029 || attach_rec.generated_flag);
2030 END IF;
2031 Insert_Contract_Doc (
2032 p_api_version => l_api_version,
2033 p_init_msg_list => p_init_msg_list,
2034 p_validation_level => p_validation_level,
2035 p_business_document_type => p_to_bus_doc_type,
2036 p_business_document_id => p_to_bus_doc_id,
2037 p_business_document_version => p_to_bus_doc_version,
2038 p_attached_document_id => attach_rec.to_attached_document_id,
2039 p_external_visibility_flag => attach_rec.external_visibility_flag,
2040 p_effective_from_type => p_to_bus_doc_type,
2041 p_effective_from_id => p_to_bus_doc_id,
2042 p_effective_from_version => p_to_bus_doc_version,
2043 p_include_for_approval_flag => attach_rec.include_for_approval_flag,
2044 p_generated_flag => attach_rec.generated_flag,
2045 p_primary_contract_doc_flag => attach_rec.primary_contract_doc_flag,
2046 p_mergeable_doc_flag => attach_rec.mergeable_doc_flag,
2047 p_delete_flag => attach_rec.delete_flag,
2048 p_create_fnd_attach => 'N',
2049 p_program_id => NULL,
2050 p_program_application_id => NULL,
2051 p_request_id => NULL,
2052 p_program_update_date => NULL,
2053 p_versioning_flag => 'Y', -- Do Not Update Primary Flag when Copying or Versioning
2054 x_msg_data => l_msg_data,
2055 x_msg_count => l_msg_count,
2056 x_return_status => l_return_status,
2057 x_business_document_type => l_business_document_type,
2058 x_business_document_id => l_business_document_id,
2059 x_business_document_version => l_business_document_version,
2060 x_attached_document_id => l_attached_document_id);
2061
2062 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2063 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2064 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2065 RAISE FND_API.G_EXC_ERROR;
2066 END IF;
2067 END LOOP;
2068
2069 -- Standard call to get message count and if count is 1, get message info.
2070 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2071
2072 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2073 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4900: Leaving Copy_Attachment_Docs');
2074 END IF;
2075
2076 EXCEPTION
2077 WHEN FND_API.G_EXC_ERROR THEN
2078 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2079 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4840: Leaving Copy_Attachment_Docs: OKC_API.G_EXCEPTION_ERROR Exception');
2080 END IF;
2081
2082 --Bug 6468721
2083 IF (doc_version_view_name_csr%ISOPEN) THEN
2084 CLOSE doc_version_view_name_csr;
2085 END IF;
2086
2087 x_return_status := G_RET_STS_ERROR ;
2088 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2089
2090 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2091 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2092 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4841: Leaving Copy_Attachment_Docs: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2093 END IF;
2094
2095 --Bug 6468721
2096 IF (doc_version_view_name_csr%ISOPEN) THEN
2097 CLOSE doc_version_view_name_csr;
2098 END IF;
2099
2100 x_return_status := G_RET_STS_UNEXP_ERROR ;
2101 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2102
2103 WHEN OTHERS THEN
2104 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2105 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'482: Leaving Copy_Attachment_Docs because of EXCEPTION: '||sqlerrm);
2106 END IF;
2107
2108 --Bug 6468721
2109 IF (doc_version_view_name_csr%ISOPEN) THEN
2110 CLOSE doc_version_view_name_csr;
2111 END IF;
2112
2113 x_return_status := G_RET_STS_UNEXP_ERROR ;
2114 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2115 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2116 END IF;
2117 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2118
2119
2120 END Copy_Attachment_Docs;
2121
2122
2123
2124
2125 ---------------------------------------------------------------------------
2126 -- PROCEDURE Copy_Attachment_Refs - Reference copy for the attachments
2127 -- THIS API will be called by sourcing.
2128 -- If p_copy_for_amendment = 'Y'
2129 -- copy all categories
2130 -- else if p_copy_primary_doc_flag = 'Y"
2131 -- Copy PCD (in ref copy)
2132 -- Copy contract category docs (in deep copy)
2133 -- else if p_from_bus_doc_type = p_to_bus_doc_type
2134 -- Copy only Contract and Support documents
2135 -- else
2136 -- copy all categories
2137 ---------------------------------------------------------------------------
2138 PROCEDURE Copy_Attachment_Refs(
2139 p_api_version IN NUMBER,
2140 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2141 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2142 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2143
2144 x_return_status OUT NOCOPY VARCHAR2,
2145 x_msg_count OUT NOCOPY NUMBER,
2146 x_msg_data OUT NOCOPY VARCHAR2,
2147
2148 p_from_bus_doc_type IN VARCHAR2,
2149 p_from_bus_doc_id IN NUMBER,
2150 p_from_bus_doc_version IN NUMBER := G_CURRENT_VERSION,
2151 p_to_bus_doc_type IN VARCHAR2,
2152 p_to_bus_doc_id IN NUMBER,
2153 p_to_bus_doc_version IN NUMBER := G_CURRENT_VERSION,
2154 p_copy_primary_doc_flag IN VARCHAR2 := 'N',
2155 p_copy_for_amendment IN VARCHAR2 := 'N'
2156
2157 ) IS l_api_name CONSTANT VARCHAR2(30) := 'Copy_Attachment_Refs';
2158
2159 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2160 l_datatype_id NUMBER;
2161 l_msg_count NUMBER;
2162 l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
2163 l_api_version NUMBER := 1;
2164 l_business_document_type OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_TYPE%TYPE;
2165 l_business_document_id OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_ID%TYPE;
2166 l_business_document_version OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_VERSION%TYPE;
2167 l_from_version OKC_CONTRACT_DOCS.EFFECTIVE_FROM_VERSION%TYPE;
2168 l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
2169 l_category_name FND_DOCUMENT_CATEGORIES.NAME%TYPE;
2170 l_parent_attached_doc_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
2171
2172
2173 -- Attachment cursor to get the attachments for a given doc type, id and version
2174 CURSOR attach_csr(l_bus_doc_type VARCHAR2, l_bus_doc_id NUMBER, l_bus_doc_version NUMBER) IS
2175 SELECT * FROM OKC_CONTRACT_DOCS
2176 WHERE business_document_type = l_bus_doc_type
2177 AND business_document_id = l_bus_doc_id
2178 AND business_document_version = l_bus_doc_version
2179 AND delete_flag = 'N';
2180
2181 -- Get the category code for a given fnd attachment.
2182 CURSOR category_csr (l_attached_document_id NUMBER) IS
2183 SELECT fdc.name
2184 FROM fnd_attached_documents fad, fnd_documents fd, fnd_document_categories fdc
2185 WHERE fad.attached_document_id = l_attached_document_id
2186 AND fad.document_id = fd.document_id
2187 AND fd.category_id = fdc.category_id;
2188
2189 BEGIN
2190 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2191 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2192 '5000: Entered Copy_Attachment_Refs');
2193 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2194 '5001: p_from_bus_doc_type: ' || p_from_bus_doc_type);
2195 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2196 '5002: p_from_bus_doc_id: ' || p_from_bus_doc_id);
2197 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2198 '5003: p_from_bus_doc_version: ' || p_from_bus_doc_version);
2199 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2200 '5004: p_to_bus_doc_type: ' || p_to_bus_doc_type);
2201 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2202 '5005: p_to_bus_doc_id: ' || p_to_bus_doc_id);
2203 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2204 '5006: p_to_bus_doc_version: ' || p_to_bus_doc_version);
2205 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2206 '5007: p_copy_primary_doc_flag: ' || p_copy_primary_doc_flag);
2207 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2208 '5008: p_copy_for_amendment: ' || p_copy_for_amendment);
2209 END IF;
2210
2211 -- Standard call to check for call compatibility.
2212 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2213 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2214 END IF;
2215
2216 -- Initialize API return status to success
2217 x_return_status := FND_API.G_RET_STS_SUCCESS;
2218
2219
2220 -- Loop through the existing attachments to create new attachments
2221 FOR attach_rec IN attach_csr(p_from_bus_doc_type, p_from_bus_doc_id,
2222 p_from_bus_doc_version)
2223 LOOP
2224 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2225 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5030:doc type is: ' || p_to_bus_doc_type);
2226 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5040:doc id is: ' || p_to_bus_doc_id);
2227 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5050:doc version is: ' || p_to_bus_doc_version);
2228 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5060:attached_document_id is: ' || attach_rec.attached_document_id);
2229 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5070:effective_from_version is: ' || attach_rec.effective_from_version);
2230 END IF;
2231 -- Get the category name of the attachment being copied.
2232 OPEN category_csr(attach_rec.attached_document_id);
2233 FETCH category_csr INTO l_category_name;
2234 IF(category_csr%NOTFOUND) THEN
2235 RAISE NO_DATA_FOUND;
2236 END IF;
2237
2238 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2239 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5071: Category name is: ' || l_category_name);
2240 END IF;
2241
2242 -- Insert the record in OKC_CONTRACT_DOCS
2243 -- If p_copy_for_amendment = 'Y' copy everything
2244 -- p_copy_primary_doc_flag = 'Y' - copy only primary contract
2245 -- Do not copy attachments if source doc. type is same as destination doc. type and
2246 -- category is Approval abstract or Contract Image
2247 IF ((p_copy_for_amendment = 'Y') -- Copy everything
2248 OR
2249 ((p_copy_primary_doc_flag = 'N' OR attach_rec.primary_contract_doc_flag = 'Y' ) AND -- IF p_copy_primary_doc_flag, copy PCD
2250 (NOT ((p_from_bus_doc_type = p_to_bus_doc_type) -- IF doc types are same, copy only contract and supporting docs.
2251 AND (l_category_name = G_APP_ABSTRACT_CATEGORY OR l_category_name = G_CONTRACT_IMAGE_CATEGORY)))
2252 )
2253 ) THEN
2254 IF (attach_rec.parent_attached_doc_id IS NOT NULL) THEN
2255 l_parent_attached_doc_id := attach_rec.parent_attached_doc_id;
2256 ELSE
2257 l_parent_attached_doc_id := attach_rec.attached_document_id;
2258 END IF; -- attach_rec.parent_attached_doc_id <> NULL
2259 Insert_Contract_Doc (
2260 p_api_version => l_api_version,
2261 p_init_msg_list => p_init_msg_list,
2262 p_validation_level => p_validation_level,
2263 p_business_document_type => p_to_bus_doc_type,
2264 p_business_document_id => p_to_bus_doc_id,
2265 p_business_document_version => p_to_bus_doc_version,
2266 p_attached_document_id => attach_rec.attached_document_id,
2267 p_external_visibility_flag => attach_rec.external_visibility_flag,
2268 p_effective_from_type => attach_rec.effective_from_type,
2269 p_effective_from_id => attach_rec.effective_from_id,
2270 p_effective_from_version => attach_rec.effective_from_version,
2271 p_include_for_approval_flag => attach_rec.include_for_approval_flag,
2272 p_generated_flag => attach_rec.generated_flag,
2273 p_primary_contract_doc_flag => attach_rec.primary_contract_doc_flag,
2274 p_mergeable_doc_flag => attach_rec.mergeable_doc_flag,
2275 p_delete_flag => attach_rec.delete_flag,
2276 p_create_fnd_attach => 'Y',
2277 p_program_id => NULL,
2278 p_program_application_id => NULL,
2279 p_request_id => NULL,
2280 p_program_update_date => NULL,
2281 p_parent_attached_doc_id => l_parent_attached_doc_id,
2282 x_msg_data => l_msg_data,
2283 x_msg_count => l_msg_count,
2284 x_return_status => l_return_status,
2285 x_business_document_type => l_business_document_type,
2286 x_business_document_id => l_business_document_id,
2287 x_business_document_version => l_business_document_version,
2288 x_attached_document_id => l_attached_document_id);
2289 END IF; -- ((p_copy_for_amendment = 'Y') ......
2290 CLOSE category_csr;
2291 -- Check for errors
2292 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2294 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2295 RAISE FND_API.G_EXC_ERROR;
2296 END IF;
2297 END LOOP;
2298
2299
2300 -- Standard call to get message count and if count is 1, get message info.
2301 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2302 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2303 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5100: Leaving Copy_Attachment_Refs');
2304 END IF;
2305
2306 EXCEPTION
2307 WHEN FND_API.G_EXC_ERROR THEN
2308 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2309 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5081: Leaving Copy_Attachment_Refs: OKC_API.G_EXCEPTION_ERROR Exception');
2310 END IF;
2311 IF (category_csr%ISOPEN) THEN
2312 CLOSE category_csr ;
2313 END IF;
2314 x_return_status := G_RET_STS_ERROR ;
2315 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2316
2317 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2318 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2319 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5082: Leaving Copy_Attachment_Refs: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2320 END IF;
2321 IF (category_csr%ISOPEN) THEN
2322 CLOSE category_csr ;
2323 END IF;
2324 x_return_status := G_RET_STS_UNEXP_ERROR ;
2325 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2326
2327 WHEN OTHERS THEN
2328 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2329 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5083: Leaving Copy_Attachment_Refs because of EXCEPTION: '||sqlerrm);
2330 END IF;
2331 IF (category_csr%ISOPEN) THEN
2332 CLOSE category_csr ;
2333 END IF;
2334 x_return_status := G_RET_STS_UNEXP_ERROR ;
2335 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2336 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2337 END IF;
2338 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2339
2340 END Copy_Attachment_Refs;
2341
2342
2343
2344
2345
2346
2347
2348
2349
2350 ---------------------------------------------------------------------------
2351 -- PROCEDURE Copy_Attachments - Copy attachments API
2352 -- If p_copy_for_amendment = 'Y'
2353 -- copy all categories
2354 -- else if p_copy_primary_doc_flag = 'Y"
2355 -- Copy PCD (in ref copy)
2356 -- Copy contract category docs (in deep copy)
2357 -- else if p_from_bus_doc_type = p_to_bus_doc_type
2358 -- Copy only Contract and Support documents
2359 -- else
2360 -- copy all categories
2361 ---------------------------------------------------------------------------
2362 PROCEDURE Copy_Attachments(
2363 p_api_version IN NUMBER,
2364 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2365 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2366 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2367
2368 x_return_status OUT NOCOPY VARCHAR2,
2369 x_msg_count OUT NOCOPY NUMBER,
2370 x_msg_data OUT NOCOPY VARCHAR2,
2371
2372 p_from_bus_doc_type IN VARCHAR2,
2373 p_from_bus_doc_id IN NUMBER,
2374 p_from_bus_doc_version IN NUMBER := G_CURRENT_VERSION,
2375 p_to_bus_doc_type IN VARCHAR2,
2376 p_to_bus_doc_id IN NUMBER,
2377 p_to_bus_doc_version IN NUMBER := G_CURRENT_VERSION,
2378 p_copy_by_ref IN VARCHAR2,
2379 p_copy_primary_doc_flag IN VARCHAR2,
2380 p_copy_for_amendment IN VARCHAR2
2381
2382 ) IS
2383 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Attachments';
2384 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2385 l_datatype_id FND_DOCUMENTS.DATATYPE_ID%TYPE;
2386 l_msg_count NUMBER;
2387 l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
2388 l_api_version NUMBER:= 1;
2389
2390
2391 BEGIN
2392 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2393 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2394 '5100: Entered Copy_Attachments');
2395 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2396 '5101: p_from_bus_doc_type: ' || p_from_bus_doc_type);
2397 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2398 '5102: p_from_bus_doc_id: ' || p_from_bus_doc_id);
2399 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2400 '5103: p_from_bus_doc_version: ' || p_from_bus_doc_version);
2401 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2402 '5104: p_to_bus_doc_type: ' || p_to_bus_doc_type);
2403 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2404 '5105: p_to_bus_doc_id: ' || p_to_bus_doc_id);
2405 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2406 '5106: p_to_bus_doc_version: ' || p_to_bus_doc_version);
2407 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2408 '5107: p_copy_by_ref: ' || p_copy_by_ref);
2409 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2410 '5108: p_copy_primary_doc_flag: ' || p_copy_primary_doc_flag);
2411 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2412 '5109: p_copy_for_amendment: ' || p_copy_for_amendment);
2413 END IF;
2414
2415 -- Standard Start of API savepoint
2416 SAVEPOINT Copy_Attachments_GRP;
2417 -- Standard call to check for call compatibility.
2418 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2419 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2420 END IF;
2421 -- Initialize message list if p_init_msg_list is set to TRUE.
2422 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2423 FND_MSG_PUB.initialize;
2424 END IF;
2425
2426 -- Initialize API return status to success
2427 x_return_status := FND_API.G_RET_STS_SUCCESS;
2428
2429 IF (p_copy_by_ref = 'Y') THEN
2430 Copy_Attachment_Refs(
2431 p_api_version => l_api_version,
2432 p_init_msg_list => p_init_msg_list,
2433 p_validation_level => p_validation_level,
2434 x_msg_data => l_msg_data,
2435 x_msg_count => l_msg_count,
2436 x_return_status => l_return_status,
2437 p_from_bus_doc_type => p_from_bus_doc_type,
2438 p_from_bus_doc_id => p_from_bus_doc_id,
2439 p_from_bus_doc_version => p_from_bus_doc_version,
2440 p_to_bus_doc_type => p_to_bus_doc_type,
2441 p_to_bus_doc_id => p_to_bus_doc_id,
2442 p_to_bus_doc_version => p_to_bus_doc_version,
2443 p_copy_primary_doc_flag => p_copy_primary_doc_flag,
2444 p_copy_for_amendment => p_copy_for_amendment
2445 );
2446 END IF;
2447 IF (p_copy_by_ref = 'N') THEN
2448
2449
2450 Copy_Attachment_Docs(
2451 p_api_version => l_api_version,
2452 p_init_msg_list => p_init_msg_list,
2453 p_validation_level => p_validation_level,
2454 x_msg_data => l_msg_data,
2455 x_msg_count => l_msg_count,
2456 x_return_status => l_return_status,
2457 p_from_bus_doc_type => p_from_bus_doc_type,
2458 p_from_bus_doc_id => p_from_bus_doc_id,
2459 p_from_bus_doc_version => p_from_bus_doc_version,
2460 p_to_bus_doc_type => p_to_bus_doc_type,
2461 p_to_bus_doc_id => p_to_bus_doc_id,
2462 p_to_bus_doc_version => p_to_bus_doc_version,
2463 p_copy_primary_doc_flag => p_copy_primary_doc_flag,
2464 p_copy_for_amendment => p_copy_for_amendment
2465 );
2466 END IF;
2467
2468
2469 -- Check for errors
2470 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2471 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2472 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2473 RAISE FND_API.G_EXC_ERROR;
2474 END IF;
2475
2476 -- Standard check of p_commit
2477 IF FND_API.To_Boolean( p_commit ) THEN
2478 COMMIT WORK;
2479 END IF;
2480 -- Standard call to get message count and if count is 1, get message info.
2481 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2482 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2483 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4400: Leaving Copy_Attachments');
2484 END IF;
2485
2486 EXCEPTION
2487 WHEN FND_API.G_EXC_ERROR THEN
2488 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2489 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4500: Leaving Copy_Attachments: OKC_API.G_EXCEPTION_ERROR Exception');
2490 END IF;
2491 ROLLBACK TO Copy_Attachments_GRP;
2492 x_return_status := G_RET_STS_ERROR ;
2493 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2494
2495 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2496 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2497 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4600: Leaving Copy_Attachments: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2498 END IF;
2499 ROLLBACK TO Copy_Attachments_GRP;
2500 x_return_status := G_RET_STS_UNEXP_ERROR ;
2501 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2502
2503 WHEN OTHERS THEN
2504 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2505 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4700: Leaving Copy_Attachments because of EXCEPTION: '||sqlerrm);
2506 END IF;
2507 ROLLBACK TO Copy_Attachments_GRP;
2508 x_return_status := G_RET_STS_UNEXP_ERROR ;
2509 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2510 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2511 END IF;
2512 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2513 END Copy_Attachments;
2514
2515
2516 PROCEDURE qa_doc(
2517 p_api_version IN NUMBER,
2518 x_return_status OUT NOCOPY VARCHAR2,
2519 x_msg_count OUT NOCOPY NUMBER,
2520 x_msg_data OUT NOCOPY VARCHAR2,
2521
2522 p_doc_type IN VARCHAR2,
2523 p_doc_id IN NUMBER,
2524
2525 x_qa_result_tbl OUT NOCOPY OKC_TERMS_QA_GRP.qa_result_tbl_type,
2526 x_qa_return_status OUT NOCOPY VARCHAR2
2527
2528 ) IS
2529
2530 l_api_name CONSTANT VARCHAR2(30) := 'qa_doc';
2531 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2532 l_msg_count NUMBER;
2533 l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
2534 l_api_version NUMBER:= 1;
2535 l_qa_name FND_LOOKUPS.MEANING%TYPE;
2536 l_severity_flag VARCHAR2(1) := 'W';
2537 l_perform_qa VARCHAR2(1) := 'N';
2538 l_indx NUMBER;
2539
2540 CURSOR l_get_qa_detail_csr IS
2541
2542 SELECT LOOKUPS.meaning qa_name,
2543 nvl(qa.severity_flag,G_QA_STS_WARNING) severity_flag ,
2544 decode(LOOKUPS.enabled_flag,'N','N','Y',decode(qa.enable_qa_yn,'N','N','Y'),'Y') perform_qa
2545 FROM OKC_LOOKUPS_V LOOKUPS, OKC_DOC_QA_LISTS QA
2546 WHERE QA.DOCUMENT_TYPE(+)=p_doc_type
2547 AND QA.QA_CODE(+) = LOOKUPS.LOOKUP_CODE
2548 AND LOOKUPS.LOOKUP_TYPE = 'OKC_TERM_QA_LIST'
2549 AND LOOKUPS.LOOKUP_CODE='CHECK_PRIMARY_CONTRACT';
2550
2551 BEGIN
2552 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2553 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4300: Entered qa_doc');
2554 END IF;
2555
2556 x_return_status := G_RET_STS_SUCCESS;
2557 x_qa_return_status := G_QA_STS_SUCCESS;
2558 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2560 END IF;
2561
2562 OPEN l_get_qa_detail_csr;
2563 FETCH l_get_qa_detail_csr INTO l_qa_name,l_severity_flag,l_perform_qa;
2564 CLOSE l_get_qa_detail_csr;
2565
2566 IF l_perform_qa = 'Y' THEN
2567 IF (OKC_TERMS_UTIL_GRP.get_contract_source_code(p_doc_type,p_doc_id) = 'ATTACHED' AND
2568 Has_Primary_Contract_Doc(p_doc_type,p_doc_id) = 'N' ) THEN
2569
2570 l_indx := x_qa_result_tbl.COUNT + 1;
2571
2572 x_qa_result_tbl(l_indx).document_type := p_doc_type;
2573 x_qa_result_tbl(l_indx).document_id := p_doc_id;
2574 x_qa_result_tbl(l_indx).error_record_type := 'DOCUMENT';
2575 x_qa_result_tbl(l_indx).article_id := Null;
2576 x_qa_result_tbl(l_indx).deliverable_id := Null;
2577 x_qa_result_tbl(l_indx).title := OKC_UTIL.DECODE_LOOKUP('OKC_CONTRACT_TERMS_SOURCES','ATTACHED');
2578 x_qa_result_tbl(l_indx).section_name := Null;
2579 x_qa_result_tbl(l_indx).qa_code := 'CHECK_PRIMARY_CONTRACT';
2580 x_qa_result_tbl(l_indx).message_name := 'OKC_REPO_DOC_QA_NO_PRIMARY';
2581 x_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message('OKC','OKC_REPO_DOC_QA_NO_PRIMARY_S');
2582 x_qa_result_tbl(l_indx).error_severity := l_severity_flag;
2583 x_qa_result_tbl(l_indx).problem_short_desc := l_qa_name;
2584 x_qa_result_tbl(l_indx).problem_details_short:= OKC_TERMS_UTIL_PVT.Get_Message('OKC', 'OKC_REPO_DOC_QA_NO_PRIMARY');
2585 x_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message('OKC', 'OKC_REPO_DOC_QA_NO_PRIMARY');
2586 x_qa_result_tbl(l_indx).creation_date := SYSDATE;
2587 x_qa_return_status := l_severity_flag;
2588 END IF;
2589
2590
2591 END IF;
2592
2593 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2594 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2300: Leaving QA_Doc');
2595 END IF;
2596 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2597
2598 EXCEPTION
2599 WHEN FND_API.G_EXC_ERROR THEN
2600 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2601 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2400: Leaving QA_Doc : OKC_API.G_EXCEPTION_ERROR Exception');
2602 END IF;
2603 IF (l_get_qa_detail_csr%ISOPEN) THEN
2604 CLOSE l_get_qa_detail_csr ;
2605 END IF;
2606 ROLLBACK TO g_QA_Doc;
2607 x_return_status := G_RET_STS_ERROR ;
2608 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2609
2610 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2611 ROLLBACK TO g_QA_Doc;
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,'2500: Leaving QA_Doc : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
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 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2621
2622 WHEN OTHERS THEN
2623 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2624 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2600: Leaving QA_Doc because of EXCEPTION: '||sqlerrm);
2625 END IF;
2626 IF (l_get_qa_detail_csr%ISOPEN) THEN
2627 CLOSE l_get_qa_detail_csr ;
2628 END IF;
2629 ROLLBACK TO g_QA_Doc;
2630 x_return_status := G_RET_STS_UNEXP_ERROR ;
2631 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2632 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2633 END IF;
2634 END QA_Doc;
2635
2636 -- Returns 'Y' - Attached document is oracle generated and mergeable.
2637 -- 'N' - Non recognised format, non mergeable.
2638 -- 'E' - Error.
2639 FUNCTION Is_Primary_Terms_Doc_Mergeable(
2640 p_document_type IN VARCHAR2,
2641 p_document_id IN NUMBER
2642 ) RETURN VARCHAR2 IS
2643 l_api_name CONSTANT VARCHAR2(30) := 'Is_Primary_Terms_Doc_mergeable';
2644 CURSOR contract_doc_csr IS
2645 SELECT 'Y'
2646 FROM okc_contract_docs
2647 WHERE business_document_type = p_document_type
2648 AND business_document_id = p_document_id
2649 AND mergeable_doc_flag='Y'
2650 AND primary_contract_doc_flag='Y'
2651 AND NVL(delete_flag,'N') = 'N'
2652 AND business_document_version = -99;
2653
2654 l_value VARCHAR2(1);
2655
2656 BEGIN
2657 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2658 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Entering Is_Primary_Terms_Doc_Mergeable');
2659 END IF;
2660 IF ( OKC_TERMS_UTIL_GRP.get_contract_source_code( p_document_type, p_document_id ) <> 'ATTACHED'
2661 OR OKC_TERMS_UTIL_GRP.get_authoring_party_code( p_document_type, p_document_id ) <> 'INTERNAL_ORG' )
2662 THEN
2663 RETURN 'N';
2664 END IF;
2665 OPEN contract_doc_csr;
2666 FETCH contract_doc_csr into l_value;
2667 CLOSE contract_doc_csr;
2668
2669 IF l_value = 'Y' THEN
2670 RETURN 'Y';
2671 ELSE
2672 RETURN 'N';
2673 END IF;
2674
2675 EXCEPTION
2676 WHEN OTHERS THEN
2677 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2678 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2000: Leaving Is_Primary_Terms_Doc_Mergeable because of EXCEPTION: '||sqlerrm);
2679 END IF;
2680 IF (contract_doc_csr%ISOPEN) THEN
2681 CLOSE contract_doc_csr ;
2682 END IF;
2683 RETURN 'E';
2684 END Is_Primary_Terms_Doc_Mergeable;
2685
2686 -- Returns FND_DOCUMENTS_TL.media_id of the Primary contract file for the current version of the document if it is non mergeable.
2687 -- 0 if document is mergeable.
2688 -- -1 if no primary document exists.
2689 FUNCTION Get_Primary_Terms_Doc_File_Id(
2690 p_document_type IN VARCHAR2,
2691 p_document_id IN NUMBER
2692 ) RETURN NUMBER IS
2693 l_api_name CONSTANT VARCHAR2(30) := 'Get_Primary_Terms_doc_File_Id';
2694 l_mergeable_doc_flag VARCHAR2(1) := '?';
2695 l_media_id FND_DOCUMENTS.MEDIA_ID%TYPE := -1;
2696
2697 CURSOR contract_doc_csr IS
2698
2699 SELECT b.media_id , docs.mergeable_doc_flag
2700 FROM OKC_CONTRACT_DOCS docs, FND_ATTACHED_DOCUMENTS fnd, FND_DOCUMENTS b
2701 WHERE docs.primary_contract_doc_flag = 'Y'
2702 AND NVL(docs.delete_flag,'N') = 'N'
2703 AND docs.business_document_version = -99
2704 AND docs.business_document_type = p_document_type
2705 AND docs.business_document_id = p_document_id
2706 AND docs.attached_document_id = fnd.attached_document_id
2707 AND fnd.document_id = b.document_id;
2708
2709 BEGIN
2710 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2711 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Entering Get_Primary_Terms_Doc_File_Id');
2712 END IF;
2713 OPEN contract_doc_csr;
2714 FETCH contract_doc_csr into l_media_id,l_mergeable_doc_flag;
2715 CLOSE contract_doc_csr;
2716
2717 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2718 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1900: Return File id value:'||l_media_id);
2719 END IF;
2720
2721 -- Start of fix for Bug 4085597
2722 IF OKC_TERMS_UTIL_GRP.get_contract_source_code( p_document_type, p_document_id ) = 'STRUCTURED'
2723 OR ( OKC_TERMS_UTIL_GRP.get_contract_source_code( p_document_type, p_document_id ) = 'ATTACHED'
2724 AND OKC_TERMS_UTIL_GRP.get_authoring_party_code( p_document_type, p_document_id ) = 'INTERNAL_ORG'
2725 AND l_mergeable_doc_flag = 'Y' )
2726 THEN
2727 l_media_id := 0;
2728 END IF;
2729 -- End of fix for Bug 4085597
2730
2731 RETURN l_media_id;
2732
2733 EXCEPTION
2734 WHEN OTHERS THEN
2735 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2736 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2000: Leaving Get_Primary_Terms_Doc_File_Id because of EXCEPTION: '||sqlerrm);
2737 END IF;
2738 IF (contract_doc_csr%ISOPEN) THEN
2739 CLOSE contract_doc_csr ;
2740 END IF;
2741 RETURN -1;
2742 END Get_Primary_Terms_Doc_File_Id;
2743
2744 FUNCTION Has_Primary_Contract_Doc(
2745 p_document_type IN VARCHAR2,
2746 p_document_id IN NUMBER
2747 ) RETURN VARCHAR2 IS
2748 l_api_name CONSTANT VARCHAR2(30) := 'Has_Primary_Contract_Doc';
2749 CURSOR contract_doc_csr IS
2750 SELECT '!'
2751 FROM OKC_CONTRACT_DOCS
2752 WHERE business_document_type = p_document_type
2753 AND business_document_id = p_document_id
2754 AND primary_contract_doc_flag = 'Y'
2755 AND NVL(delete_flag,'N') = 'N'
2756 AND business_document_version = -99;
2757
2758 l_result VARCHAR2(1) ;
2759
2760 BEGIN
2761 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2762 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Entering Has_Primary_Contract_Doc');
2763 END IF;
2764 OPEN contract_doc_csr;
2765 FETCH contract_doc_csr into l_result;
2766
2767 IF contract_doc_csr%FOUND THEN
2768 CLOSE contract_doc_csr;
2769 RETURN 'Y';
2770 ELSE
2771 CLOSE contract_doc_csr;
2772 RETURN 'N';
2773 END IF;
2774
2775 EXCEPTION
2776 WHEN OTHERS THEN
2777 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2778 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2000: Leaving Has_Primary_Contract_Doc because of EXCEPTION: '||sqlerrm);
2779 END IF;
2780 IF (contract_doc_csr%ISOPEN) THEN
2781 CLOSE contract_doc_csr ;
2782 END IF;
2783 RETURN 'E';
2784 END Has_Primary_Contract_Doc;
2785
2786 --Removes the Primary contract flag on the attachment for the latest business document version.
2787 PROCEDURE Clear_Primary_Doc_Flag(
2788 p_document_type IN VARCHAR2,
2789 p_document_id IN NUMBER,
2790
2791 x_return_status OUT NOCOPY VARCHAR2
2792 ) IS
2793 l_api_name CONSTANT VARCHAR2(30) := 'Clear_Primary_Doc_Flag';
2794 CURSOR primary_doc_csr IS
2795 SELECT attached_document_id,object_version_number
2796 FROM OKC_CONTRACT_DOCS
2797 WHERE business_document_type = p_document_type
2798 AND business_document_id = p_document_id
2799 AND primary_contract_doc_flag = 'Y'
2800 AND NVL(delete_flag,'N') = 'N'
2801 AND business_document_version = -99;
2802
2803 l_attached_document_id OKC_CONTRACT_DOCS.ATTACHED_DOCUMENT_ID%TYPE := NULL;
2804 l_object_version_number OKC_CONTRACT_DOCS.object_version_number%TYPE;
2805
2806 BEGIN
2807 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2808 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Entering Clear_Primary_Doc_Flag');
2809 END IF;
2810 x_return_status := G_RET_STS_SUCCESS;
2811
2812 OPEN primary_doc_csr;
2813 FETCH primary_doc_csr into l_attached_document_id,l_object_version_number;
2814 CLOSE primary_doc_csr;
2815
2816 IF l_attached_document_id IS NOT NULL THEN
2817 OKC_CONTRACT_DOCS_PVT.update_row(
2818 x_return_status => x_return_status,
2819 p_business_document_type => p_document_type,
2820 p_business_document_id => p_document_id,
2821 p_business_document_version => -99,
2822 p_attached_document_id => l_attached_document_id,
2823 p_primary_contract_doc_flag => 'N',
2824 p_object_version_number => l_object_version_number );
2825 END IF;
2826
2827
2828 EXCEPTION
2829 WHEN OTHERS THEN
2830 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2831 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2000: Leaving Clear_Primary_Doc_Flag because of EXCEPTION: '||sqlerrm);
2832 END IF;
2833 IF (primary_doc_csr%ISOPEN) THEN
2834 CLOSE primary_doc_csr ;
2835 END IF;
2836 x_return_status := G_RET_STS_UNEXP_ERROR ;
2837 END Clear_Primary_Doc_Flag;
2838
2839
2840 END OKC_CONTRACT_DOCS_GRP;