[Home] [Help]
PACKAGE BODY: APPS.PON_ATTACHMENTS
Source
1 PACKAGE BODY PON_ATTACHMENTS AS
2 /* $Header: PONATCHB.pls 120.2 2007/06/28 20:25:31 sssahai ship $ */
3
4 FUNCTION check_attachment_exists(p_entity_name IN VARCHAR2,
5 p_pk1_value IN VARCHAR2 DEFAULT NULL,
6 p_pk2_value IN VARCHAR2 DEFAULT NULL,
7 p_pk3_value IN VARCHAR2 DEFAULT NULL,
8 p_pk4_value IN VARCHAR2 DEFAULT NULL,
9 p_pk5_value IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
10
11 IS
12
13 l_has_attachments VARCHAR2(1);
14
15 BEGIN
16
17 SELECT decode(count(1), 0, 'N', 'Y')
18 INTO l_has_attachments
19 FROM fnd_attached_documents
20 WHERE entity_name = p_entity_name AND
21 decode(p_pk1_value, null, -1, pk1_value) = decode(p_pk1_value, null, -1, p_pk1_value) AND
22 decode(p_pk2_value, null, -1, pk2_value) = decode(p_pk2_value, null, -1, p_pk2_value) AND
23 decode(p_pk3_value, null, -1, pk3_value) = decode(p_pk3_value, null, -1, p_pk3_value) AND
24 decode(p_pk4_value, null, -1, pk4_value) = decode(p_pk4_value, null, -1, p_pk4_value) AND
25 decode(p_pk5_value, null, -1, pk5_value) = decode(p_pk5_value, null, -1, p_pk5_value) AND
26 rownum = 1;
27
28 RETURN l_has_attachments;
29
30 END check_attachment_exists;
31
32
33 PROCEDURE add_attachment_blob(
34 p_file_name in VARCHAR2,
35 p_file_content_type in VARCHAR2,
36 p_file_format in VARCHAR2,
37 p_file_id out nocopy NUMBER
38 ) IS
39 l_file_id NUMBER;
40 BEGIN
41 INSERT INTO fnd_lobs (
42 file_id,
43 file_content_type,
44 file_name,
45 file_format,
46 file_data) VALUES (
47 fnd_lobs_s.nextval,
48 p_file_content_type,
49 p_file_name,
50 p_file_format,
51 empty_blob())
52 RETURNING file_id INTO l_file_id;
53
54 p_file_id := l_file_id;
55 END add_attachment_blob;
56
57 -- without column1
58 PROCEDURE add_attachment(
59 p_seq_num in NUMBER,
60 p_category_id in NUMBER,
61 p_document_description in VARCHAR2,
62 p_datatype_id in NUMBER,
63 p_short_text in VARCHAR2,
64 p_file_name in VARCHAR2,
65 p_url in VARCHAR2,
66 p_entity_name in VARCHAR2,
67 p_pk1_value in VARCHAR2,
68 p_pk2_value in VARCHAR2,
69 p_pk3_value in VARCHAR2,
70 p_pk4_value in VARCHAR2,
71 p_pk5_value in VARCHAR2,
72 p_media_id in NUMBER,
73 p_user_id in NUMBER,
74 x_attached_document_id out nocopy NUMBER,
75 x_file_id out nocopy NUMBER
76 ) IS
77 BEGIN
78 PON_ATTACHMENTS.add_attachment(
79 p_seq_num => p_seq_num ,
80 p_category_id => p_category_id ,
81 p_document_description => p_document_description ,
82 p_datatype_id => p_datatype_id ,
83 p_short_text => p_short_text ,
84 p_file_name => p_file_name ,
85 p_url => p_url ,
86 p_entity_name => p_entity_name ,
87 p_pk1_value => p_pk1_value ,
88 p_pk2_value => p_pk2_value ,
89 p_pk3_value => p_pk3_value ,
90 p_pk4_value => p_pk4_value ,
91 p_pk5_value => p_pk5_value ,
92 p_media_id => p_media_id ,
93 p_user_id => p_user_id ,
94 p_column1 => NULL ,
95 x_attached_document_id => x_attached_document_id ,
96 x_file_id => x_file_id
97 );
98 END add_attachment;
99
100 -- with column1
101 PROCEDURE add_attachment(
102 p_seq_num in NUMBER,
103 p_category_id in NUMBER,
104 p_document_description in VARCHAR2,
105 p_datatype_id in NUMBER,
106 p_short_text in VARCHAR2,
107 p_file_name in VARCHAR2,
108 p_url in VARCHAR2,
109 p_entity_name in VARCHAR2,
110 p_pk1_value in VARCHAR2,
111 p_pk2_value in VARCHAR2,
112 p_pk3_value in VARCHAR2,
113 p_pk4_value in VARCHAR2,
114 p_pk5_value in VARCHAR2,
115 p_media_id in NUMBER,
116 p_user_id in NUMBER,
117 p_column1 IN VARCHAR2,
118 x_attached_document_id out nocopy NUMBER,
119 x_file_id out nocopy NUMBER
120 ) IS
121 l_rowid varchar2(30);
122 l_attached_document_id number;
123 l_media_id number:= add_attachment.p_media_id;
124 l_document_id number;
125
126 l_file_name varchar2(255);
127 l_creation_date date := SYSDATE;
128 l_created_by number;
129 l_last_update_date date := SYSDATE;
130 l_last_updated_by number;
131 l_lang varchar2(40);
132 BEGIN
133 -- Set file name
134 IF (p_datatype_id = 1) THEN
135 l_file_name := add_attachment.p_file_name;
136 ELSIF (p_datatype_id = 5 ) THEN
137 l_file_name := p_url;
138 l_media_id := NULL;
139 ELSIF (p_datatype_id in (6,7) ) THEN
140 l_file_name := add_attachment.p_file_name;
141 END IF;
142
143 -- Set the WHO Columns.
144 l_created_by := p_user_id;
145 l_last_updated_by := l_created_by;
146
147 -- Attached Document Id has to be populated from the sequence.
148 SELECT fnd_attached_documents_s.nextval
149 INTO l_attached_document_id
150 FROM sys.dual;
151
152 -- Set the language parameter
153 SELECT USERENV('LANG')
154 INTO l_lang
155 FROM dual;
156
157 -- Call the server side package for adding the attachment and documents.
158 fnd_attached_documents_pkg.insert_row (
159 x_rowid => l_rowid ,
160 x_attached_document_id => l_attached_document_id ,
161 x_document_id => l_document_id ,
162 x_creation_date => l_creation_date ,
163 x_created_by => l_created_by ,
164 x_last_update_date => l_last_update_date ,
165 x_last_updated_by => l_last_updated_by ,
166 x_last_update_login => NULL ,
167 x_seq_num => p_seq_num ,
168 x_entity_name => p_entity_name ,
169 x_column1 => p_column1 ,
170 x_pk1_value => p_pk1_value ,
171 x_pk2_value => p_pk2_value ,
172 x_pk3_value => p_pk3_value ,
173 x_pk4_value => p_pk4_value ,
174 x_pk5_value => p_pk5_value ,
175 x_automatically_added_flag => 'N' ,
176 x_request_id => NULL ,
177 x_program_application_id =>NULL ,
178 x_program_id => NULL ,
179 x_program_update_date => NULL ,
180 x_attribute_category => NULL ,
181 x_attribute1 => NULL ,
182 x_attribute2 => NULL ,
183 x_attribute3 => NULL ,
184 x_attribute4 => NULL ,
185 x_attribute5 => NULL ,
186 x_attribute6 => NULL ,
187 x_attribute7 => NULL ,
188 x_attribute8 => NULL ,
189 x_attribute9 => NULL ,
190 x_attribute10 => NULL ,
191 x_attribute11 => NULL ,
192 x_attribute12 => NULL ,
193 x_attribute13 => NULL ,
194 x_attribute14 => NULL ,
195 x_attribute15 => NULL ,
196 x_datatype_id => p_datatype_id ,
197 x_category_id => p_category_id ,
198 x_security_type => 4 ,
199 x_security_id => NULL ,
200 x_publish_flag => 'Y' ,
201 x_image_type => NULL ,
202 x_storage_type => NULL ,
203 x_usage_type => 'O' ,
204 x_language => l_lang ,
205 x_description => p_document_description ,
206 x_file_name => l_file_name ,
207 x_media_id => l_media_id ,
208 x_doc_attribute_category => NULL ,
209 x_doc_attribute1 => NULL ,
210 x_doc_attribute2 => NULL ,
211 x_doc_attribute3 => NULL ,
212 x_doc_attribute4 => NULL ,
213 x_doc_attribute5 => NULL ,
214 x_doc_attribute6 => NULL ,
215 x_doc_attribute7 => NULL ,
216 x_doc_attribute8 => NULL ,
217 x_doc_attribute9 => NULL ,
218 x_doc_attribute10 => NULL ,
219 x_doc_attribute11 => NULL ,
220 x_doc_attribute12 => NULL ,
221 x_doc_attribute13 => NULL ,
222 x_doc_attribute14 => NULL ,
223 x_doc_attribute15 => NULL
224 );
225
226 IF (p_datatype_id = PON_ATTACHMENTS.SHORT_TEXT) THEN
227 INSERT INTO fnd_documents_short_text(
228 media_id,
229 short_text)
230 VALUES (
231 l_media_id,
232 p_short_text);
233 END IF;
234
235 x_attached_document_id := l_attached_document_id;
236 x_file_id := l_media_id;
237
238 END add_attachment;
239
240
241 PROCEDURE add_long_text_attachment(
242 p_seq_num in NUMBER,
243 p_category_id in NUMBER,
244 p_document_description in VARCHAR2,
245 p_long_text in LONG,
246 p_file_name in VARCHAR2,
247 p_url in VARCHAR2,
248 p_entity_name in VARCHAR2,
249 p_pk1_value in VARCHAR2,
250 p_pk2_value in VARCHAR2,
251 p_pk3_value in VARCHAR2,
252 p_pk4_value in VARCHAR2,
253 p_pk5_value in VARCHAR2,
254 p_media_id in NUMBER,
255 p_user_id in NUMBER,
256 p_column1 IN VARCHAR2,
257 x_attached_document_id out nocopy NUMBER,
258 x_file_id out nocopy NUMBER
259 ) IS
260 l_rowid varchar2(30);
261 l_attached_document_id number;
262 l_media_id number:= add_long_text_attachment.p_media_id;
263 l_document_id number;
264
265 l_file_name varchar2(255);
266 l_creation_date date := SYSDATE;
267 l_created_by number;
268 l_last_update_date date := SYSDATE;
269 l_last_updated_by number;
270 l_lang varchar2(40);
271 l_progress NUMBER; -- Debug Purposes
272 BEGIN
273 -- Set file name
274 l_progress := 1;
275 l_file_name := add_long_text_attachment.p_file_name;
276
277 -- Set the WHO Columns.
278 l_progress := 2;
279 l_created_by := p_user_id;
280 l_last_updated_by := l_created_by;
281
282 -- Attached Document Id has to be populated from the sequence.
283 l_progress := 3;
284 SELECT fnd_attached_documents_s.nextval
285 INTO l_attached_document_id
286 FROM sys.dual;
287
288 -- Set the language parameter
289 l_progress := 4;
290 SELECT USERENV('LANG')
291 INTO l_lang
292 FROM dual;
293
294 -- Call the server side package for adding the attachment and documents.
295 l_progress := 5;
296 fnd_attached_documents_pkg.insert_row (
297 x_rowid => l_rowid ,
298 x_attached_document_id => l_attached_document_id ,
299 x_document_id => l_document_id ,
300 x_creation_date => l_creation_date ,
301 x_created_by => l_created_by ,
302 x_last_update_date => l_last_update_date ,
303 x_last_updated_by => l_last_updated_by ,
304 x_last_update_login => NULL ,
305 x_seq_num => p_seq_num ,
306 x_entity_name => p_entity_name ,
307 x_column1 => p_column1 ,
308 x_pk1_value => p_pk1_value ,
309 x_pk2_value => p_pk2_value ,
310 x_pk3_value => p_pk3_value ,
311 x_pk4_value => p_pk4_value ,
312 x_pk5_value => p_pk5_value ,
313 x_automatically_added_flag => 'N' ,
314 x_request_id => NULL ,
315 x_program_application_id =>NULL ,
316 x_program_id => NULL ,
317 x_program_update_date => NULL ,
318 x_attribute_category => NULL ,
319 x_attribute1 => NULL ,
320 x_attribute2 => NULL ,
321 x_attribute3 => NULL ,
322 x_attribute4 => NULL ,
323 x_attribute5 => NULL ,
324 x_attribute6 => NULL ,
325 x_attribute7 => NULL ,
326 x_attribute8 => NULL ,
327 x_attribute9 => NULL ,
328 x_attribute10 => NULL ,
329 x_attribute11 => NULL ,
330 x_attribute12 => NULL ,
331 x_attribute13 => NULL ,
332 x_attribute14 => NULL ,
333 x_attribute15 => NULL ,
334 x_datatype_id => PON_ATTACHMENTS.LONG_TEXT ,
335 x_category_id => p_category_id ,
336 x_security_type => 4 ,
337 x_security_id => NULL ,
338 x_publish_flag => 'Y' ,
339 x_image_type => NULL ,
340 x_storage_type => NULL ,
341 x_usage_type => 'O' ,
342 x_language => l_lang ,
343 x_description => p_document_description ,
347 x_doc_attribute1 => NULL ,
344 x_file_name => l_file_name ,
345 x_media_id => l_media_id ,
346 x_doc_attribute_category => NULL ,
348 x_doc_attribute2 => NULL ,
349 x_doc_attribute3 => NULL ,
350 x_doc_attribute4 => NULL ,
351 x_doc_attribute5 => NULL ,
352 x_doc_attribute6 => NULL ,
353 x_doc_attribute7 => NULL ,
354 x_doc_attribute8 => NULL ,
355 x_doc_attribute9 => NULL ,
356 x_doc_attribute10 => NULL ,
357 x_doc_attribute11 => NULL ,
358 x_doc_attribute12 => NULL ,
359 x_doc_attribute13 => NULL ,
360 x_doc_attribute14 => NULL ,
361 x_doc_attribute15 => NULL
362 );
363
364 -- Insert available Long Text into FND_DOCUMENTS_LONG_TEXT
365 l_progress := 6;
366 INSERT INTO fnd_documents_long_text
367 (
368 media_id,
369 long_text
370 ) VALUES (
371 l_media_id,
372 p_long_text
373 );
374
375 -- Save obtained values
376 l_progress := 7;
377 x_attached_document_id := l_attached_document_id;
378 x_file_id := l_media_id;
379
380 EXCEPTION
381 When Others Then
382 Raise_Application_Error(
383 -20001,
384 'Error at Step : ' || l_progress || ' ' ||
385 'in add_long_text_attachment(...) ' ||
386 SQLERRM,
387 true
388 );
389
390 END add_long_text_attachment;
391
392
393 PROCEDURE add_attachment_frm_doc_catalog(
394 p_seq_num in NUMBER,
395 p_entity_name in VARCHAR2,
396 p_pk1_value in VARCHAR2,
397 p_pk2_value in VARCHAR2,
398 p_pk3_value in VARCHAR2,
399 p_pk4_value in VARCHAR2,
400 p_pk5_value in VARCHAR2,
401 p_document_id in NUMBER,
402 p_column1 IN VARCHAR2,
403 x_attached_document_id out nocopy NUMBER
404 )
405 IS
406
407 l_rowid varchar2(30);
408 l_attached_document_id number;
409 l_media_id number;
410 l_document_id number:= add_attachment_frm_doc_catalog.p_document_id;
411 l_datatype_id number;
412 l_category_id number;
413 l_file_name varchar2(255);
414 l_description FND_DOCUMENTS_TL.DESCRIPTION%Type;
415 l_creation_date date := SYSDATE;
416 l_created_by number;
417 l_last_update_date date := SYSDATE;
418 l_last_updated_by number;
419 l_lang varchar2(40);
420 l_usage_type FND_DOCUMENTS.USAGE_TYPE%Type;
421
422 l_create_doc varchar2(1):= 'N';
423
424 l_doc_attribute_category FND_DOCUMENTS_TL.DOC_ATTRIBUTE_CATEGORY%Type;
425 l_doc_attribute1 FND_DOCUMENTS_TL.DOC_ATTRIBUTE1%Type;
426 l_doc_attribute2 FND_DOCUMENTS_TL.DOC_ATTRIBUTE2%Type;
427 l_doc_attribute3 FND_DOCUMENTS_TL.DOC_ATTRIBUTE3%Type;
428 l_doc_attribute4 FND_DOCUMENTS_TL.DOC_ATTRIBUTE4%Type;
429 l_doc_attribute5 FND_DOCUMENTS_TL.DOC_ATTRIBUTE5%Type;
430 l_doc_attribute6 FND_DOCUMENTS_TL.DOC_ATTRIBUTE6%Type;
431 l_doc_attribute7 FND_DOCUMENTS_TL.DOC_ATTRIBUTE7%Type;
432 l_doc_attribute8 FND_DOCUMENTS_TL.DOC_ATTRIBUTE8%Type;
433 l_doc_attribute9 FND_DOCUMENTS_TL.DOC_ATTRIBUTE9%Type;
434 l_doc_attribute10 FND_DOCUMENTS_TL.DOC_ATTRIBUTE10%Type;
435 l_doc_attribute11 FND_DOCUMENTS_TL.DOC_ATTRIBUTE11%Type;
436 l_doc_attribute12 FND_DOCUMENTS_TL.DOC_ATTRIBUTE12%Type;
437 l_doc_attribute13 FND_DOCUMENTS_TL.DOC_ATTRIBUTE13%Type;
438 l_doc_attribute14 FND_DOCUMENTS_TL.DOC_ATTRIBUTE14%Type;
439 l_doc_attribute15 FND_DOCUMENTS_TL.DOC_ATTRIBUTE15%Type;
440
441 l_progress number := 0; -- For debugging purposes
442
443 BEGIN
444
445 -- Select Language.
446 l_progress := 1;
447 Select USERENV('LANG') into l_lang
448 From Dual;
449
450 -- Attached Document ID population from sequence.
451 l_progress := 2;
452 Select FND_ATTACHED_DOCUMENTS_S.nextval
453 Into l_attached_document_id
454 From Dual
455 ;
456
457 -- Select Document Properties
458 l_progress := 3;
459 Select DTL.FILE_NAME, D.MEDIA_ID, DTL.DESCRIPTION,
460 D.DATATYPE_ID, D.CATEGORY_ID, D.USAGE_TYPE,
461 DTL.DOC_ATTRIBUTE_CATEGORY, DTL.DOC_ATTRIBUTE1,
462 DTL.DOC_ATTRIBUTE2, DTL.DOC_ATTRIBUTE3,
463 DTL.DOC_ATTRIBUTE4, DTL.DOC_ATTRIBUTE5,
464 DTL.DOC_ATTRIBUTE6, DTL.DOC_ATTRIBUTE7,
465 DTL.DOC_ATTRIBUTE8, DTL.DOC_ATTRIBUTE9,
466 DTL.DOC_ATTRIBUTE10, DTL.DOC_ATTRIBUTE11,
467 DTL.DOC_ATTRIBUTE12, DTL.DOC_ATTRIBUTE13,
471 l_doc_attribute_category, l_doc_attribute1,
468 DTL.DOC_ATTRIBUTE14, DTL.DOC_ATTRIBUTE15
469 Into l_file_name, l_media_id, l_description,
470 l_datatype_id, l_category_id, l_usage_type,
472 l_doc_attribute2, l_doc_attribute3,
473 l_doc_attribute4, l_doc_attribute5,
474 l_doc_attribute6, l_doc_attribute7,
475 l_doc_attribute8, l_doc_attribute9,
476 l_doc_attribute10, l_doc_attribute11,
477 l_doc_attribute12, l_doc_attribute13,
478 l_doc_attribute14, l_doc_attribute15
479 From FND_DOCUMENTS D, FND_DOCUMENTS_TL DTL
480 Where DTL.DOCUMENT_ID = D.DOCUMENT_ID
481 And DTL.DOCUMENT_ID = l_document_id
482 And DTL.LANGUAGE = l_lang
483 ;
484
485 -- Set WHO columns
486 l_progress := 4;
487 l_created_by := 1;
488 l_last_updated_by := l_created_by;
489
490 -- (Re)set relevant attributes (including desc. flex. segments) based upon
491 -- document properties retrieved above. We create only an attachment
492 -- in case if the document is of the usage type 'Standard'; in all
493 -- other cases a new document with the usage type 'One-Time' is
494 -- created and the attachment is made out of it. Excepting the usage type,
495 -- such a new document would be a replica of the original document.
496
497 If ( l_usage_type <> 'S') Then
498 l_progress := 5;
499 l_document_id := NULL; -- The following FND API call creates a new
500 -- document on containing a NULL value for its
501 -- 'x_document_id' parameter.
502 l_usage_type := 'O'; -- 'O' stands for a 'One-Time' Document.
503 l_create_doc := 'Y'; -- Create a 'copy' of the document.
504 End If;
505
506 -- Call FND Procedure to create the attachment.
507 l_progress := 6;
508 fnd_attached_documents_pkg.insert_row (
509 x_rowid => l_rowid ,
510 x_attached_document_id => l_attached_document_id ,
511 x_document_id => l_document_id ,
512 x_creation_date => l_creation_date ,
513 x_created_by => l_created_by ,
514 x_last_update_date => l_last_update_date ,
515 x_last_updated_by => l_last_updated_by ,
516 x_last_update_login => NULL ,
517 x_seq_num => p_seq_num ,
518 x_entity_name => p_entity_name ,
519 x_column1 => p_column1 ,
520 x_pk1_value => p_pk1_value ,
521 x_pk2_value => p_pk2_value ,
522 x_pk3_value => p_pk3_value ,
523 x_pk4_value => p_pk4_value ,
524 x_pk5_value => p_pk5_value ,
525 x_automatically_added_flag => 'N' ,
526 x_request_id => NULL ,
527 x_program_application_id =>NULL ,
528 x_program_id => NULL ,
529 x_program_update_date => NULL ,
530 x_attribute_category => NULL ,
531 x_attribute1 => NULL ,
532 x_attribute2 => NULL ,
533 x_attribute3 => NULL ,
534 x_attribute4 => NULL ,
535 x_attribute5 => NULL ,
536 x_attribute6 => NULL ,
537 x_attribute7 => NULL ,
538 x_attribute8 => NULL ,
539 x_attribute9 => NULL ,
540 x_attribute10 => NULL ,
541 x_attribute11 => NULL ,
542 x_attribute12 => NULL ,
543 x_attribute13 => NULL ,
544 x_attribute14 => NULL ,
545 x_attribute15 => NULL ,
546 x_datatype_id => l_datatype_id ,
547 x_category_id => l_category_id ,
548 x_security_type => 4 ,
549 x_security_id => NULL ,
550 x_publish_flag => 'Y' ,
551 x_image_type => NULL ,
552 x_storage_type => NULL ,
553 x_usage_type => l_usage_type ,
554 x_language => l_lang ,
555 x_description => l_description ,
556 x_file_name => l_file_name ,
557 x_media_id => l_media_id ,
558 x_doc_attribute_category => l_doc_attribute_category ,
559 x_doc_attribute1 => l_doc_attribute1 ,
560 x_doc_attribute2 => l_doc_attribute2 ,
561 x_doc_attribute3 => l_doc_attribute3 ,
562 x_doc_attribute4 => l_doc_attribute4 ,
566 x_doc_attribute8 => l_doc_attribute8 ,
563 x_doc_attribute5 => l_doc_attribute5 ,
564 x_doc_attribute6 => l_doc_attribute6 ,
565 x_doc_attribute7 => l_doc_attribute7 ,
567 x_doc_attribute9 => l_doc_attribute9 ,
568 x_doc_attribute10 => l_doc_attribute10 ,
569 x_doc_attribute11 => l_doc_attribute11 ,
570 x_doc_attribute12 => l_doc_attribute12 ,
571 x_doc_attribute13 => l_doc_attribute13 ,
572 x_doc_attribute14 => l_doc_attribute14 ,
573 x_doc_attribute15 => l_doc_attribute15 ,
574 x_create_doc => l_create_doc
575 );
576
577 -- Save Obtained Values
578 l_progress := 7;
579 x_attached_document_id := l_attached_document_id;
580
581 EXCEPTION
582 When Others Then
583 Raise_Application_Error(
584 -20001,
585 'Error at Step : ' || l_progress || ' ' ||
586 'in add_attachment_frm_doc_catalog(...) ' ||
587 SQLERRM,
588 true
589 );
590
591 END add_attachment_frm_doc_catalog;
592
593 --without column1
594 PROCEDURE add_attachment_frm_ui(
595 p_seq_num in NUMBER,
596 p_category_id in NUMBER,
597 p_document_description in VARCHAR2,
598 p_datatype_id in NUMBER,
599 p_short_text in VARCHAR2,
600 p_long_text in LONG,
601 p_file_name in VARCHAR2,
602 p_url in VARCHAR2,
603 p_entity_name in VARCHAR2,
604 p_pk1_value in VARCHAR2,
605 p_pk2_value in VARCHAR2,
606 p_pk3_value in VARCHAR2,
607 p_pk4_value in VARCHAR2,
608 p_pk5_value in VARCHAR2,
609 p_media_id in NUMBER,
610 p_user_id in NUMBER,
611 p_document_id in NUMBER,
612 x_attached_document_id out nocopy NUMBER,
613 x_file_id out nocopy NUMBER
614 ) IS
615 BEGIN
616 PON_ATTACHMENTS.add_attachment_frm_ui(
617 p_seq_num => p_seq_num ,
618 p_category_id => p_category_id ,
619 p_document_description => p_document_description ,
620 p_datatype_id => p_datatype_id ,
621 p_short_text => p_short_text ,
622 p_long_text => p_long_text ,
623 p_file_name => p_file_name ,
624 p_url => p_url ,
625 p_entity_name => p_entity_name ,
626 p_pk1_value => p_pk1_value ,
627 p_pk2_value => p_pk2_value ,
628 p_pk3_value => p_pk3_value ,
629 p_pk4_value => p_pk4_value ,
630 p_pk5_value => p_pk5_value ,
631 p_media_id => p_media_id ,
632 p_user_id => p_user_id ,
633 p_document_id => p_document_id ,
634 p_column1 => NULL ,
635 x_attached_document_id => x_attached_document_id ,
636 x_file_id => x_file_id
637 );
638 END add_attachment_frm_ui;
639
640
641 --with column1
642 PROCEDURE add_attachment_frm_ui(
643 p_seq_num in NUMBER,
644 p_category_id in NUMBER,
645 p_document_description in VARCHAR2,
646 p_datatype_id in NUMBER,
647 p_short_text in VARCHAR2,
648 p_long_text in LONG,
649 p_file_name in VARCHAR2,
650 p_url in VARCHAR2,
651 p_entity_name in VARCHAR2,
652 p_pk1_value in VARCHAR2,
653 p_pk2_value in VARCHAR2,
654 p_pk3_value in VARCHAR2,
655 p_pk4_value in VARCHAR2,
656 p_pk5_value in VARCHAR2,
657 p_media_id in NUMBER,
658 p_user_id in NUMBER,
659 p_document_id in NUMBER,
660 p_column1 IN VARCHAR2,
661 x_attached_document_id out nocopy NUMBER,
662 x_file_id out nocopy NUMBER
663 ) IS
664 l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%Type
665 := 0;
666 l_file_id FND_DOCUMENTS_TL.MEDIA_ID%Type
667 := 0;
668 l_datatype_id FND_DOCUMENTS.DATATYPE_ID%Type
669 := add_attachment_frm_ui.p_datatype_id;
670 l_progress NUMBER := 0; -- For debugging purposes
671 BEGIN
672
673 If ( p_document_id Is Not NULL) Then
674
675
676 -- Call the procedure to create attachment from Document Catalog
677
678 l_progress := 1;
679 PON_ATTACHMENTS.add_attachment_frm_doc_catalog(
680 p_seq_num => p_seq_num ,
681 p_entity_name => p_entity_name ,
685 p_pk4_value => p_pk4_value ,
682 p_pk1_value => p_pk1_value ,
683 p_pk2_value => p_pk2_value ,
684 p_pk3_value => p_pk3_value ,
686 p_pk5_value => p_pk5_value ,
687 p_document_id => p_document_id ,
688 p_column1 => p_column1 ,
689 x_attached_document_id => l_attached_document_id
690 );
691
692 ElsIf ( l_datatype_id = PON_ATTACHMENTS.LONG_TEXT ) Then
693
694 -- Call procedure to create a long text attachment
695
696 l_progress := 2;
697 PON_ATTACHMENTS.add_long_text_attachment (
698 p_seq_num => p_seq_num ,
699 p_category_id => p_category_id ,
700 p_document_description => p_document_description ,
701 p_long_text => p_long_text ,
702 p_file_name => p_file_name ,
703 p_url => p_url ,
704 p_entity_name => p_entity_name ,
705 p_pk1_value => p_pk1_value ,
706 p_pk2_value => p_pk2_value ,
707 p_pk3_value => p_pk3_value ,
708 p_pk4_value => p_pk4_value ,
709 p_pk5_value => p_pk5_value ,
710 p_media_id => p_media_id ,
711 p_user_id => p_user_id ,
712 p_column1 => p_column1 ,
713 x_attached_document_id => l_attached_document_id ,
714 x_file_id => l_file_id
715 );
716
717 Else
718
719 -- Default situation : create URL/Short Text/File type of attachment.
720
721 l_progress := 3;
722 PON_ATTACHMENTS.add_attachment(
723 p_seq_num => p_seq_num ,
724 p_category_id => p_category_id ,
725 p_document_description => p_document_description ,
726 p_datatype_id => p_datatype_id ,
727 p_short_text => p_short_text ,
728 p_file_name => p_file_name ,
729 p_url => p_url ,
730 p_entity_name => p_entity_name ,
731 p_pk1_value => p_pk1_value ,
732 p_pk2_value => p_pk2_value ,
733 p_pk3_value => p_pk3_value ,
734 p_pk4_value => p_pk4_value ,
735 p_pk5_value => p_pk5_value ,
736 p_media_id => p_media_id ,
737 p_user_id => p_user_id ,
738 p_column1 => p_column1 ,
739 x_attached_document_id => l_attached_document_id ,
740 x_file_id => l_file_id
741 );
742
743 End If;
744
745 l_progress := 4;
746 x_attached_document_id := l_attached_document_id;
747 x_file_id := l_file_id;
748
749 EXCEPTION
750 When Others Then
751 Raise_Application_Error(
752 -20001,
753 'Error at Step : ' || l_progress || ' ' ||
754 'in add_attachment_frm_ui(...) ' ||
755 SQLERRM,
756 true
757 );
758
759 END add_attachment_frm_ui;
760
761 PROCEDURE delete_attachment(
762 p_attached_document_id in NUMBER,
763 p_datatype_id in NUMBER
764 ) IS
765
766 l_attached_document_id Number := delete_attachment.p_attached_document_id;
767 l_datatype_id number;
768 l_file_name varchar2(255);
769 l_usage_type Varchar2(1);
770 l_attached_count Number := 0 ;
771
772 BEGIN
773
774 --
775 -- Deletion Logic :
776 --
777 -- 1. Do NOT delete the document associated with the attachment
778 -- if the document's usage type is not 'One-Time'.
779 --
780 -- 2. Do NOT delete a document if it is attached to multiple
781 -- attachments.
782 --
783
784 Select doc.Usage_Type Into l_usage_type
785 From Fnd_Documents doc
786 Where doc.Document_Id =
787 (
788 Select att.Document_Id
789 From Fnd_Attached_Documents att
790 Where att.Attached_Document_Id = l_attached_document_id
791 );
792
793 Select Count(*) Into l_attached_count
794 From Fnd_Attached_Documents
795 Where Document_Id =
796 (
797 Select a.Document_Id
798 From Fnd_Attached_Documents a
799 Where a.Attached_Document_Id = l_attached_document_id
800 );
801
802 If ( (l_usage_type = 'O') AND (l_attached_count <= 1) ) Then
803 -- Call the procedure to delete the attachment and document.
804 fnd_attached_documents3_pkg.delete_row (
805 l_attached_document_id,
806 p_datatype_id,
807 'Y'
811 fnd_attached_documents3_pkg.delete_row (
808 );
809 Else
810 -- Call the procedure to delete just the attachment.
812 l_attached_document_id,
813 p_datatype_id,
814 'N'
815 );
816 End If;
817
818 END delete_attachment;
819
820
821 END PON_ATTACHMENTS;