[Home] [Help]
PACKAGE BODY: APPS.AMS_ATTACHMENT_PVT
Source
1 PACKAGE BODY AMS_Attachment_PVT as
2 /* $Header: amsvatcb.pls 115.15 2004/03/27 02:24:28 julou ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_Attachment_PVT
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_Attachment_PVT';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvatcb.pls';
19
20 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
21 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
22 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
23
24 -- Hint: Primary key needs to be returned.
25 PROCEDURE Create_Fnd_Attachment(
26 p_api_version_number IN NUMBER,
27 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
28 p_commit IN VARCHAR2 := FND_API.G_FALSE,
29 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
30
31 x_return_status OUT NOCOPY VARCHAR2,
32 x_msg_count OUT NOCOPY NUMBER,
33 x_msg_data OUT NOCOPY VARCHAR2,
34
35 p_Fnd_Attachment_rec IN fnd_attachment_rec_type,
36 x_document_id OUT NOCOPY NUMBER,
37 x_attached_document_id OUT NOCOPY NUMBER
38 )
39
40 IS
41 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Fnd_Attachment';
42 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
43 l_return_status_full VARCHAR2(1);
44 l_document_ID NUMBER;
45 l_media_ID NUMBER;
46 l_attached_document_ID NUMBER;
47 l_dummy NUMBER;
48 l_seq_num NUMBER := 10;
49 l_row_id VARCHAR2(255);
50 l_Fnd_Attachment_rec fnd_attachment_rec_type;
51 l_create_Attached_Doc boolean := true;
52
53 CURSOR c_attached_doc_id IS
54 SELECT FND_ATTACHED_DOCUMENTS_S.nextval
55 FROM dual;
56
57 CURSOR c_attached_doc_id_exists (l_id IN NUMBER) IS
58 SELECT 1
59 FROM FND_ATTACHED_DOCUMENTS
60 WHERE document_id = l_id;
61
62 BEGIN
63 -- Standard Start of API savepoint
64 SAVEPOINT CREATE_Fnd_Attachment_PVT;
65
66 -- Standard call to check for call compatibility.
67 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
68 p_api_version_number,
69 l_api_name,
70 G_PKG_NAME)
71 THEN
72 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
73 END IF;
74
75 -- Initialize message list if p_init_msg_list is set to TRUE.
76 IF FND_API.to_Boolean( p_init_msg_list )
77 THEN
78 FND_MSG_PUB.initialize;
79 END IF;
80
81 -- Debug Message
82 IF (AMS_DEBUG_HIGH_ON) THEN
83 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
84 END IF;
85
86
87 -- Initialize API return status to SUCCESS
88 x_return_status := FND_API.G_RET_STS_SUCCESS;
89
90 -- Local variable initialization
91
92 -- =========================================================================
93 -- Validate Environment
94 -- =========================================================================
95
96 IF FND_GLOBAL.User_Id IS NULL
97 THEN
98 AMS_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
99 RAISE FND_API.G_EXC_ERROR;
100 END IF;
101
102 -- Debug Message
103 IF (AMS_DEBUG_HIGH_ON) THEN
104 AMS_UTILITY_PVT.debug_message( 'Private API: Calling table handler fnd_documents_pkg.insert_row');
105 END IF;
106
107 l_media_id := p_Fnd_Attachment_rec.media_id;
108
109 -- Invoke table handler
110 fnd_documents_pkg.insert_row(
111 X_rowid => l_row_id,
112 X_document_id => x_document_id,
113 X_creation_date => sysdate,
114 X_created_by => FND_GLOBAL.USER_ID,
115 X_last_update_date => sysdate,
116 X_last_updated_by => FND_GLOBAL.USER_ID,
117 X_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
118 X_datatype_id => p_Fnd_Attachment_rec.datatype_id,
119 X_category_id => p_Fnd_Attachment_rec.category_id,
120 X_security_type => p_Fnd_Attachment_rec.security_type,
121 X_publish_flag => p_Fnd_Attachment_rec.publish_flag,
122 X_usage_type => p_Fnd_Attachment_rec.usage_type,
123 X_language => p_Fnd_Attachment_rec.language,
124 X_description =>p_Fnd_Attachment_rec.description,
125 X_file_name => p_Fnd_Attachment_rec.file_name,
126 X_media_id => l_media_id
127 );
128 if (p_Fnd_Attachment_rec.datatype_id = 1) then
129
130 /* Verify if the media_id is not null */
131 if (p_Fnd_Attachment_rec.media_id is null) then
132 /* It means that a new text needs to be created, otherwise not */
133 /* Populate Short Text */
134 insert into
135 fnd_documents_short_text
136 (media_id,
137 short_text
138 )
139 values
140 (l_media_id,
141 p_Fnd_Attachment_rec.short_text
142 );
143 else
144 /*
145 Update fnd_documents_tl because FND_API inserts newly generated
146 media_id into that table.
147 */
148 update fnd_documents_tl
149 set media_id = p_Fnd_Attachment_rec.media_id
150 where document_id = x_document_id;
151
152 end if;
153
154 elsif (p_Fnd_Attachment_rec.datatype_id = 6) then /* File */
155 /* For File we have already generated a file id - the fnd_documents_pkg.insert_row
156 table handler has generated a fnd_lobs_s.nextval but that's not what shoule be the
157 reference to the FND_LOBS table - because the upload program has already generated a
158 sequence */
159 /**
160 update fnd_documents_tl
161 set media_id = p_Fnd_Attachment_rec.media_id
162 where document_id = l_document_id;
163 **/
164 null;
165 end if;
166
167 if (p_Fnd_Attachment_rec.attachment_type is not null) then
168
169 if ((p_Fnd_Attachment_rec.attachment_type = 'WEB_TEXT') OR
170 (p_Fnd_Attachment_rec.attachment_type = 'WEB_IMAGE')) then
171
172 l_create_Attached_Doc := false;
173
174 end if;
175
176 end if;
177
178 if (l_create_Attached_Doc) then
179
180 /*
181 IF p_Fnd_Attachment_rec.attached_DOCUMENT_ID IS NULL THEN
182 */
183 LOOP
184 l_dummy := NULL;
185 OPEN c_attached_doc_id;
186 FETCH c_attached_doc_id INTO l_attached_document_ID;
187 CLOSE c_attached_doc_id;
188
189 OPEN c_attached_doc_id_exists(l_attached_document_ID);
190 FETCH c_attached_doc_id_exists INTO l_dummy;
191 CLOSE c_attached_doc_id_exists;
192 EXIT WHEN l_dummy IS NULL;
193 END LOOP;
194
195 l_Fnd_Attachment_rec.attached_document_id := l_attached_document_id;
196 x_attached_document_id := l_attached_document_id;
197
198
199 /* Populate FND Attachments */
200 fnd_attached_documents_pkg.Insert_Row
201 ( x_rowid => l_row_id,
202 X_attached_document_id => l_attached_document_ID,
203 X_document_id => x_document_ID,
204 X_creation_date => sysdate,
205 X_created_by => FND_GLOBAL.USER_ID,
206 X_last_update_date => sysdate,
207 X_last_updated_by => FND_GLOBAL.USER_ID,
208 X_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
209 X_seq_num => l_seq_num,
210 X_entity_name => p_Fnd_Attachment_rec.entity_name,
211 x_column1 => null,
212 X_pk1_value => p_Fnd_Attachment_rec.pk1_value,
213 X_pk2_value => null,
214 X_pk3_value => null,
215 X_pk4_value => null,
216 X_pk5_value => null,
217 X_automatically_added_flag => p_Fnd_Attachment_rec.automatically_added_flag,
218 X_datatype_id => null,
219 X_category_id => null,
220 X_security_type => null,
221 X_publish_flag => null,
222 X_usage_type => p_Fnd_Attachment_rec.usage_type,
223 X_language => null,
224 X_media_id => l_media_id,
225 X_doc_attribute_Category => null,
226 X_doc_attribute1 => null,
227 X_doc_attribute2 => null,
228 X_doc_attribute3 => null,
229 X_doc_attribute4 => null,
230 X_doc_attribute5 => null,
231 X_doc_attribute6 => null,
232 X_doc_attribute7 => null,
233 X_doc_attribute8 => null,
234 X_doc_attribute9 => null,
235 X_doc_attribute10 => null,
236 X_doc_attribute11 => null,
237 X_doc_attribute12 => null,
238 X_doc_attribute13 => null,
239 X_doc_attribute14 => null,
240 X_doc_attribute15 => null
241 );
242 end if;
243
244
245 -- Standard check for p_commit
246 IF FND_API.to_Boolean( p_commit )
247 THEN
248 COMMIT WORK;
249 END IF;
250
251
252 -- Debug Message
253 IF (AMS_DEBUG_HIGH_ON) THEN
254 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
255 END IF;
256
257 -- Standard call to get message count and if count is 1, get message info.
258 FND_MSG_PUB.Count_And_Get
259 (p_count => x_msg_count,
260 p_data => x_msg_data
261 );
262
263
264 EXCEPTION
265
266 WHEN AMS_Utility_PVT.resource_locked THEN
267 x_return_status := FND_API.g_ret_sts_error;
268 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
269
270 WHEN FND_API.G_EXC_ERROR THEN
271 ROLLBACK TO CREATE_Fnd_Attachment_PVT;
272 x_return_status := FND_API.G_RET_STS_ERROR;
273 -- Standard call to get message count and if count=1, get the message
274 FND_MSG_PUB.Count_And_Get (
275 p_encoded => FND_API.G_FALSE,
276 p_count => x_msg_count,
277 p_data => x_msg_data
278 );
279
280 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
281 ROLLBACK TO CREATE_Fnd_Attachment_PVT;
282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
283 -- Standard call to get message count and if count=1, get the message
284 FND_MSG_PUB.Count_And_Get (
285 p_encoded => FND_API.G_FALSE,
286 p_count => x_msg_count,
287 p_data => x_msg_data
288 );
289
290 WHEN OTHERS THEN
291 ROLLBACK TO CREATE_Fnd_Attachment_PVT;
292 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
293 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
294 THEN
295 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
296 END IF;
297 -- Standard call to get message count and if count=1, get the message
298 FND_MSG_PUB.Count_And_Get (
299 p_encoded => FND_API.G_FALSE,
300 p_count => x_msg_count,
301 p_data => x_msg_data
302 );
303 End Create_Fnd_Attachment;
304
305 /**
306 This procedure updates FND_DOCUMENTS,FND_DOCUMENTS_TL and FND_DOCUMENTS_SHORT_TEX T. FND_ATTACHED_DOCUMENTS is not updated because it maintains the link between
307 FND_DOCUMENTS and FND_DOCUMENT_ENTITIES. In Sales and Marketing the association cannot be updated.
308
309 **/
310
311 PROCEDURE Update_Fnd_Attachment(
312 p_api_version_number IN NUMBER,
313 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
314 p_commit IN VARCHAR2 := FND_API.G_FALSE,
315 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
316
317 x_return_status OUT NOCOPY VARCHAR2,
318 x_msg_count OUT NOCOPY NUMBER,
319 x_msg_data OUT NOCOPY VARCHAR2,
320
321 p_Fnd_Attachment_rec IN fnd_attachment_rec_type
322 )
323
324 IS
325 CURSOR c_get_Fnd_Documents(l_document_id NUMBER) IS
326 SELECT *
327 FROM fnd_documents
328 where document_id = l_document_id;
329
330 -- Hint: Developer need to provide Where clause
331 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Fnd_Attachment';
332 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
333 -- Local Variables
334 l_document_id number;
335 l_attached_document_ID NUMBER;
336 l_ref_Fnd_Attachment_rec c_get_fnd_Documents%ROWTYPE ;
337 l_tar_Fnd_Attachment_rec AMS_attachment_PVT.fnd_attachment_rec_type := P_fnd_attachment_rec;
338 l_rowid ROWID;
339 l_media_id Number;
340
341 BEGIN
342 -- Standard Start of API savepoint
343 SAVEPOINT UPDATE_Fnd_Attachment_PVT;
344
345 -- Standard call to check for call compatibility.
346 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
347 p_api_version_number,
348 l_api_name,
349 G_PKG_NAME)
350 THEN
351 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
352 END IF;
353
354 -- Initialize message list if p_init_msg_list is set to TRUE.
355 IF FND_API.to_Boolean( p_init_msg_list )
356 THEN
357 FND_MSG_PUB.initialize;
358 END IF;
359
360 -- Debug Message
361 IF (AMS_DEBUG_HIGH_ON) THEN
362 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
363 END IF;
364
365
366 -- Initialize API return status to SUCCESS
367 x_return_status := FND_API.G_RET_STS_SUCCESS;
368
369 -- Debug Message
370 IF (AMS_DEBUG_HIGH_ON) THEN
371 AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
372 END IF;
373
374 OPEN c_get_Fnd_Documents( l_tar_fnd_attachment_rec.document_id);
375
376 FETCH c_get_Fnd_Documents INTO l_ref_fnd_attachment_rec ;
377
378 If ( c_get_Fnd_Documents%NOTFOUND) THEN
379 AMS_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
380 p_token_name => 'INFO',
381 p_token_value => 'Fnd_Attachment') ;
382 RAISE FND_API.G_EXC_ERROR;
383 END IF;
384
385
386 IF (p_Fnd_Attachment_rec.concur_last_update_date <> l_ref_fnd_attachment_rec.last_update_date) THEN
387 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RECORD_NOT_FOUND') ;
388 RAISE FND_API.G_EXC_ERROR;
389 END IF;
390
391 -- Debug Message
392 IF (AMS_DEBUG_HIGH_ON) THEN
393 AMS_UTILITY_PVT.debug_message('Private API: - Close Cursor');
394 END IF;
395 CLOSE c_get_Fnd_documents;
396
397 -- Debug Message
398 --IF (AMS_DEBUG_HIGH_ON) THEN AMS_UTILITY_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling update table handler');END IF;
399
400 if (p_Fnd_Attachment_rec.datatype_id = 1) then
401 if (p_Fnd_Attachment_rec.media_id is null) then
402 SELECT fnd_documents_short_text_s.nextval
403 INTO l_media_id
404 from dual;
405 end if;
406 end if;
407
408 -- Invoke table handler(fnd_documents_pkg.update_row)
409 fnd_documents_pkg.update_row(
413 X_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
410 X_document_id => p_Fnd_Attachment_rec.document_id,
411 X_last_update_date => sysdate,
412 X_last_updated_by => FND_GLOBAL.USER_ID,
414 X_datatype_id => p_Fnd_Attachment_rec.datatype_id,
415 X_category_id => p_Fnd_Attachment_rec.category_id,
416 X_security_type => p_Fnd_Attachment_rec.security_type,
417 X_security_id => null,
418 X_publish_flag => p_Fnd_Attachment_rec.publish_flag,
419 X_image_type => null,
420 X_storage_type => null,
421 X_usage_type => p_Fnd_Attachment_rec.usage_type,
422 X_start_date_active => null,
423 X_end_date_active => null,
424 X_language => p_Fnd_Attachment_rec.language,
425 X_description =>p_Fnd_Attachment_rec.description,
426 X_file_name => p_Fnd_Attachment_rec.file_name,
427 x_media_id => NVL(p_Fnd_Attachment_rec.media_id,l_media_id)
428 );
429
430 /* Bug# 2072584 */
431 /* Need to call the previous method call before actually updating
432 FND_DOCUMENTS_SHORT_TEXT in case of text - otherwise
433 the trigger was failing */
434
435 -- Update fnd_documents_short_text based on the datatype_id
436 if (p_Fnd_Attachment_rec.datatype_id = 1) then
437
438 if (p_Fnd_Attachment_rec.media_id is null) then
439 -- Create a record
440
441 /* Populate Short Text */
442 insert into
443 fnd_documents_short_text
444 (media_id,
445 short_text
446 )
447 values
448 (l_media_id,
449 p_Fnd_Attachment_rec.short_text
450 );
451
452 else
453 update fnd_documents_short_text
454 set short_text = p_Fnd_Attachment_rec.short_text
455 where media_id = p_Fnd_Attachment_rec.media_id;
456
457 if SQL%NOTFOUND then
458
459 AMS_Utility_PVT.Error_Message(
460 p_message_name => 'API_MISSING_UPDATE_TARGET',
461 p_token_name => 'INFO',
462 p_token_value => 'Fnd_Documents_Short_Text') ;
463 RAISE FND_API.G_EXC_ERROR;
464
465 end if;
466
467 end if;
468
469
470 end if;
471
472
473 -- End of API body.
474 --
475
476 -- Standard check for p_commit
477 IF FND_API.to_Boolean( p_commit )
478 THEN
479 COMMIT WORK;
480 END IF;
481
482
483 -- Debug Message
484 IF (AMS_DEBUG_HIGH_ON) THEN
485 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
486 END IF;
487
488 -- Standard call to get message count and if count is 1, get message info.
489 FND_MSG_PUB.Count_And_Get
490 (p_count => x_msg_count,
491 p_data => x_msg_data
492 );
493 EXCEPTION
494
495 WHEN AMS_Utility_PVT.resource_locked THEN
496 x_return_status := FND_API.g_ret_sts_error;
497 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
498
499 WHEN FND_API.G_EXC_ERROR THEN
500 ROLLBACK TO UPDATE_Fnd_Attachment_PVT;
501 x_return_status := FND_API.G_RET_STS_ERROR;
502 -- Standard call to get message count and if count=1, get the message
503 FND_MSG_PUB.Count_And_Get (
504 p_encoded => FND_API.G_FALSE,
505 p_count => x_msg_count,
506 p_data => x_msg_data
507 );
508
509 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
510 ROLLBACK TO UPDATE_Fnd_Attachment_PVT;
511 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
512 -- Standard call to get message count and if count=1, get the message
513 FND_MSG_PUB.Count_And_Get (
514 p_encoded => FND_API.G_FALSE,
515 p_count => x_msg_count,
516 p_data => x_msg_data
517 );
518
519 WHEN OTHERS THEN
520 ROLLBACK TO UPDATE_Fnd_Attachment_PVT;
521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
522 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
523 THEN
524 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
525 END IF;
526 -- Standard call to get message count and if count=1, get the message
527 FND_MSG_PUB.Count_And_Get (
528 p_encoded => FND_API.G_FALSE,
529 p_count => x_msg_count,
530 p_data => x_msg_data
531 );
532 End Update_Fnd_Attachment;
533
534
535 PROCEDURE Delete_Fnd_Attachment(
536 p_api_version_number IN NUMBER,
537 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
538 p_commit IN VARCHAR2 := FND_API.G_FALSE,
539 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
540 x_return_status OUT NOCOPY VARCHAR2,
541 x_msg_count OUT NOCOPY NUMBER,
542 x_msg_data OUT NOCOPY VARCHAR2,
543 p_document_id IN NUMBER,
544 p_datatype_id IN NUMBER,
545 p_delete_attachment_ref_flag IN VARCHAR2
546 )
547
548 IS
549 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Fnd_Attachment';
550 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
551 l_datatype_id number;
555 l_use_fnd_api boolean;
552 l_media_id number;
553 l_datatype_id number;
554 l_media_exists number;
556
557 cursor c_get_media_id(p_document_id number)
558 is
559 select media_id
560 from fnd_documents_tl
561 where document_id= p_document_id
562 and language = userenv('LANG');
563
564 /* Need to check if the file/text is referenced by any other document */
565 cursor c_check_media_id_reference(p_media_id number,p_document_id number,p_datatype_id number)
566 is
567 select 1
568 from dual
569 where exists
570 (select 1
571 from fnd_documents_tl tl
572 ,fnd_documents b
573 where b.document_id=tl.document_id
574 and tl.media_id = p_media_id
575 and b.datatype_id = p_datatype_id
576 and b.document_id <> p_document_id
577 );
578
579 BEGIN
580 -- Standard Start of API savepoint
581 SAVEPOINT DELETE_Fnd_Attachment_PVT;
582
583 -- Standard call to check for call compatibility.
584 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
585 p_api_version_number,
586 l_api_name,
587 G_PKG_NAME)
588 THEN
589 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
590 END IF;
591
592 -- Initialize message list if p_init_msg_list is set to TRUE.
593 IF FND_API.to_Boolean( p_init_msg_list )
594 THEN
595 FND_MSG_PUB.initialize;
596 END IF;
597
598 -- Debug Message
599 IF (AMS_DEBUG_HIGH_ON) THEN
600 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
601 END IF;
602
603
604 -- Initialize API return status to SUCCESS
605 x_return_status := FND_API.G_RET_STS_SUCCESS;
606
607 --
608 -- Api body
609 --
610 -- Debug Message
611 IF (AMS_DEBUG_HIGH_ON) THEN
612 AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
613 END IF;
614
615 IF ((p_datatype_id = 6) OR (p_datatype_id = 1)) THEN
616
617 l_use_fnd_api := false;
618
619 open c_get_media_id(p_document_id);
620 fetch c_get_media_id into l_media_id;
621 close c_get_media_id;
622
623 open c_check_media_id_reference(l_media_id,p_document_id,p_datatype_id);
624 fetch c_check_media_id_reference into l_media_exists;
625 close c_check_media_id_reference;
626
627 /*
628 For FILE/TEXT, checking if file_id/text_id exists in fnd_documents_tl,
629 if so not deleting the lob/text but otherwise delete the content as well
630 */
631
632 if (l_media_exists is null) then
633 /* Call FND API to delete everything */
634 l_use_fnd_api := true;
635 end if;
636
637 if (l_use_fnd_api) then
638
639 FND_DOCUMENTS_PKG.Delete_Row(
640 X_document_id => p_document_id,
641 X_datatype_id => p_datatype_id,
642 delete_ref_Flag => p_delete_attachment_ref_flag
643 );
644 else
645
646 delete from fnd_documents
647 where document_id=p_document_id;
648
649 delete from fnd_documents_tl
650 where document_id=p_document_id;
651
652 delete from fnd_attached_documents
653 where document_id=p_document_id;
654
655 end if;
656
657
658 ELSE
659 /* For URL calling the FND API */
660 FND_DOCUMENTS_PKG.Delete_Row(
661 X_document_id => p_document_id,
662 X_datatype_id => p_datatype_id,
663 delete_ref_Flag => p_delete_attachment_ref_flag
664 );
665
666
667
668 END IF;
669 --
670 -- End of API body
671 --
672
673 -- Standard check for p_commit
674 IF FND_API.to_Boolean( p_commit )
675 THEN
676 COMMIT WORK;
677 END IF;
678
679
680 -- Debug Message
681 IF (AMS_DEBUG_HIGH_ON) THEN
682 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
683 END IF;
684
685 -- Standard call to get message count and if count is 1, get message info.
686 FND_MSG_PUB.Count_And_Get
687 (p_count => x_msg_count,
688 p_data => x_msg_data
689 );
690 EXCEPTION
691
692 WHEN AMS_Utility_PVT.resource_locked THEN
693 x_return_status := FND_API.g_ret_sts_error;
694 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
695
696 WHEN FND_API.G_EXC_ERROR THEN
697 ROLLBACK TO DELETE_Fnd_Attachment_PVT;
698 x_return_status := FND_API.G_RET_STS_ERROR;
699 -- Standard call to get message count and if count=1, get the message
700 FND_MSG_PUB.Count_And_Get (
701 p_encoded => FND_API.G_FALSE,
702 p_count => x_msg_count,
703 p_data => x_msg_data
704 );
705
706 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
707 ROLLBACK TO DELETE_Fnd_Attachment_PVT;
708 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
709 -- Standard call to get message count and if count=1, get the message
710 FND_MSG_PUB.Count_And_Get (
711 p_encoded => FND_API.G_FALSE,
712 p_count => x_msg_count,
713 p_data => x_msg_data
714 );
715
716 WHEN OTHERS THEN
717 ROLLBACK TO DELETE_Fnd_Attachment_PVT;
718 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
719 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
720 THEN
721 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
722 END IF;
723 -- Standard call to get message count and if count=1, get the message
724 FND_MSG_PUB.Count_And_Get (
725 p_encoded => FND_API.G_FALSE,
726 p_count => x_msg_count,
727 p_data => x_msg_data
728 );
729 End Delete_Fnd_Attachment;
730
731 /**
732 Need to check if I need this or Data Source can provide all the information */
733 /**
734 PROCEDURE Complete_Fnd_Document_Rec (
735 p_Fnd_Attachment_rec IN fnd_attachment_rec_type,
736 x_complete_rec OUT NOCOPY Fnd_Attachment_rec_type)
737 IS
738 l_return_status VARCHAR2(1);
739
740 CURSOR c_complete IS
741 SELECT *
742 FROM fnd_d
743 WHERE prompt_id = p_Fnd_Attachment_rec.prompt_id;
744 l_Fnd_Attachment_rec c_complete%ROWTYPE;
745 BEGIN
746 x_complete_rec := p_Fnd_Attachment_rec;
747
748
749 OPEN c_complete;
750 FETCH c_complete INTO l_Fnd_Attachment_rec;
751 CLOSE c_complete;
752
753 -- prompt_id
757
754 IF p_Fnd_Attachment_rec.prompt_id = FND_API.g_miss_num THEN
755 x_complete_rec.prompt_id := l_Fnd_Attachment_rec.prompt_id;
756 END IF;
758 -- score
759 IF p_Fnd_Attachment_rec.score = FND_API.g_miss_num THEN
760 x_complete_rec.score := l_Fnd_Attachment_rec.score;
761 END IF;
762
763 -- icon_file_name
764 IF p_Fnd_Attachment_rec.icon_file_name = FND_API.g_miss_char THEN
765 x_complete_rec.icon_file_name := l_Fnd_Attachment_rec.icon_file_name;
766 END IF;
767
768 -- last_update_date
769 IF p_Fnd_Attachment_rec.last_update_date = FND_API.g_miss_date THEN
770 x_complete_rec.last_update_date := l_Fnd_Attachment_rec.last_update_date;
771 END IF;
772
773 -- last_updated_by
774 IF p_Fnd_Attachment_rec.last_updated_by = FND_API.g_miss_num THEN
775 x_complete_rec.last_updated_by := l_Fnd_Attachment_rec.last_updated_by;
776 END IF;
777
778 -- creation_date
779 IF p_Fnd_Attachment_rec.creation_date = FND_API.g_miss_date THEN
780 x_complete_rec.creation_date := l_Fnd_Attachment_rec.creation_date;
781 END IF;
782
783 -- created_by
784 IF p_Fnd_Attachment_rec.created_by = FND_API.g_miss_num THEN
785 x_complete_rec.created_by := l_Fnd_Attachment_rec.created_by;
786 END IF;
787
788 -- last_update_login
789 IF p_Fnd_Attachment_rec.last_update_login = FND_API.g_miss_num THEN
790 x_complete_rec.last_update_login := l_Fnd_Attachment_rec.last_update_login;
791 END IF;
792
793 -- object_version_number
794 IF p_Fnd_Attachment_rec.object_version_number = FND_API.g_miss_num THEN
795 x_complete_rec.object_version_number := l_Fnd_Attachment_rec.object_version_number;
796 END IF;
797
798 -- security_group_id
799 IF p_Fnd_Attachment_rec.security_group_id = FND_API.g_miss_num THEN
800 x_complete_rec.security_group_id := l_Fnd_Attachment_rec.security_group_id;
801 END IF;
802 -- Note: Developers need to modify the procedure
803 -- to handle any business specific requirements.
804 END Complete_Fnd_Attachment_Rec;
805 */
806
807
808 END AMS_ATTACHMENT_PVT;