1 PACKAGE BODY fnd_attached_documents2_pkg as
2 /* $Header: AFAKATDB.pls 120.7 2006/11/13 19:32:50 blash ship $ */
3
4
5
6 -- API to delete attachments for a given entity
7 PROCEDURE delete_attachments(X_entity_name IN VARCHAR2,
8 X_pk1_value IN VARCHAR2,
9 X_pk2_value IN VARCHAR2 DEFAULT NULL,
10 X_pk3_value IN VARCHAR2 DEFAULT NULL,
11 X_pk4_value IN VARCHAR2 DEFAULT NULL,
12 X_pk5_value IN VARCHAR2 DEFAULT NULL,
13 X_delete_document_flag IN VARCHAR2,
14 X_automatically_added_flag IN VARCHAR2 DEFAULT NULL) IS
15 l_delete_document_flag varchar2(1);
16
17 BEGIN
18 l_delete_document_flag := X_delete_document_flag;
19 IF l_delete_document_flag IS NULL THEN
20 l_delete_document_flag := 'N';
21 END IF;
22 -- Check that entity_name and pk1_value have values
23 IF (X_entity_name IS NULL
24 OR X_pk1_value IS NULL) THEN
25 RETURN;
26 END IF;
27
28 -- If X_delete_doc_flag is Y, then need to delete
29 -- document records too
30 IF X_pk2_value IS NULL THEN -- performance change IF.
31
32 IF (l_delete_document_flag = 'Y') THEN
33 -- need to delete from each sub-table holding the data
34 -- doing this in a loop to reduce amount of code
35 DELETE FROM fnd_documents_short_text
36 WHERE media_id IN
37 (SELECT fd.media_id
38 FROM fnd_documents_tl fdtl,
39 fnd_documents fd,
40 fnd_attached_documents fad
41 WHERE fdtl.document_id = fd.document_id
42 AND fd.document_id = fad.document_id
43 AND fd.usage_type = 'O'
44 AND fd.datatype_id = 1
45 AND fad.entity_name = X_entity_name
46 AND fad.pk1_value = X_pk1_value);
47
48
49 DELETE FROM fnd_documents_long_text
50 WHERE media_id IN
51 (SELECT fd.media_id
52 FROM fnd_documents_tl fdtl,
53 fnd_documents fd,
54 fnd_attached_documents fad
55 WHERE fdtl.document_id = fd.document_id
56 AND fd.document_id = fad.document_id
57 AND fd.usage_type = 'O'
58 AND fd.datatype_id = 2
59 AND fad.entity_name = X_entity_name
60 AND fad.pk1_value = X_pk1_value);
61
62 DELETE FROM fnd_documents_long_raw
63 WHERE media_id IN
64 (SELECT fd.media_id
65 FROM fnd_documents_tl fdtl,
66 fnd_documents fd,
67 fnd_attached_documents fad
68 WHERE fdtl.document_id = fd.document_id
69 AND fd.document_id = fad.document_id
70 AND fd.usage_type = 'O'
71 AND fd.datatype_id IN (3,4)
72 AND fad.entity_name = X_entity_name
73 AND fad.pk1_value = X_pk1_value);
74
75 DELETE FROM fnd_lobs
76 WHERE file_id IN
77 (SELECT fd.media_id
78 FROM fnd_documents_tl fdtl,
79 fnd_documents fd,
80 fnd_attached_documents fad
81 WHERE fdtl.document_id = fd.document_id
82 AND fd.document_id = fad.document_id
83 AND fd.usage_type = 'O'
84 AND fd.datatype_id = 6
85 AND fad.entity_name = X_entity_name
86 AND fad.pk1_value = X_pk1_value);
87
88 -- Delete from FND_DOCUMENTS_TL table
89 DELETE FROM fnd_documents_tl
90 WHERE document_id IN
91 (SELECT fad.document_id
92 FROM fnd_attached_documents fad, fnd_documents fd
93 WHERE fad.document_id = fd.document_id
94 AND fd.usage_type = 'O'
95 AND fad.entity_name = X_entity_name
96 AND fad.pk1_value = X_pk1_value);
97
98 -- Delete from FND_DOCUMENTS table
99 DELETE FROM fnd_documents
100 WHERE usage_type = 'O'
101 AND document_id IN
102 (SELECT document_id
103 FROM fnd_attached_documents fad
104 WHERE fad.entity_name = X_entity_name
105 AND fad.pk1_value = X_pk1_value);
106 END IF; -- end of if l_delete_document_flag is Y
107
108 -- delete from FND_ATTACHED_DOCUMENTS table
109 DELETE FROM fnd_attached_documents fad
110 WHERE fad.entity_name = X_entity_name
111 AND fad.automatically_added_flag like decode(X_automatically_added_flag,NULL,'%',X_automatically_added_flag)
112 AND fad.pk1_value = X_pk1_value;
113
114 ELSIF X_pk3_value IS NULL THEN -- performance change IF
115 IF (l_delete_document_flag = 'Y') THEN
116 -- need to delete from each sub-table holding the data
117 -- doing this in a loop to reduce amount of code
118 DELETE FROM fnd_documents_short_text
119 WHERE media_id IN
120 (SELECT fd.media_id
121 FROM fnd_documents_tl fdtl,
122 fnd_documents fd,
123 fnd_attached_documents fad
124 WHERE fdtl.document_id = fd.document_id
125 AND fd.document_id = fad.document_id
126 AND fd.usage_type = 'O'
127 AND fd.datatype_id = 1
128 AND fad.entity_name = X_entity_name
129 AND fad.pk1_value = X_pk1_value
130 AND fad.pk2_value = X_pk2_value);
131
132
133 DELETE FROM fnd_documents_long_text
134 WHERE media_id IN
135 (SELECT fd.media_id
136 FROM fnd_documents_tl fdtl,
137 fnd_documents fd,
138 fnd_attached_documents fad
139 WHERE fdtl.document_id = fd.document_id
140 AND fd.document_id = fad.document_id
141 AND fd.usage_type = 'O'
142 AND fd.datatype_id = 2
143 AND fad.entity_name = X_entity_name
144 AND fad.pk1_value = X_pk1_value
145 AND fad.pk2_value = X_pk2_value);
146
147 DELETE FROM fnd_documents_long_raw
148 WHERE media_id IN
149 (SELECT fd.media_id
150 FROM fnd_documents_tl fdtl,
151 fnd_documents fd,
152 fnd_attached_documents fad
153 WHERE fdtl.document_id = fd.document_id
154 AND fd.document_id = fad.document_id
155 AND fd.usage_type = 'O'
156 AND fd.datatype_id IN (3,4)
157 AND fad.entity_name = X_entity_name
158 AND fad.pk1_value = X_pk1_value
159 AND fad.pk2_value = X_pk2_value);
160
161 DELETE FROM fnd_lobs
162 WHERE file_id IN
163 (SELECT fd.media_id
164 FROM fnd_documents_tl fdtl,
165 fnd_documents fd,
166 fnd_attached_documents fad
167 WHERE fdtl.document_id = fd.document_id
168 AND fd.document_id = fad.document_id
169 AND fd.usage_type = 'O'
170 AND fd.datatype_id = 6
171 AND fad.entity_name = X_entity_name
172 AND fad.pk1_value = X_pk1_value
173 AND fad.pk2_value = X_pk2_value);
174
175 -- Delete from FND_DOCUMENTS_TL table
176 DELETE FROM fnd_documents_tl
177 WHERE document_id IN
178 (SELECT fad.document_id
179 FROM fnd_attached_documents fad, fnd_documents fd
180 WHERE fad.document_id = fd.document_id
181 AND fd.usage_type = 'O'
182 AND fad.entity_name = X_entity_name
183 AND fad.pk1_value = X_pk1_value
184 AND fad.pk2_value = X_pk2_value);
185
186 -- Delete from FND_DOCUMENTS table
187 DELETE FROM fnd_documents
188 WHERE usage_type = 'O'
189 AND document_id IN
190 (SELECT document_id
191 FROM fnd_attached_documents fad
192 WHERE fad.entity_name = X_entity_name
193 AND fad.pk1_value = X_pk1_value
194 AND fad.pk2_value = X_pk2_value);
195 END IF; -- end of if l_delete_document_flag is Y
196
197 -- delete from FND_ATTACHED_DOCUMENTS table
198 DELETE FROM fnd_attached_documents fad
199 WHERE fad.entity_name = X_entity_name
200 AND fad.automatically_added_flag like decode(X_automatically_added_flag,NULL,'%',X_automatically_added_flag)
201 AND fad.pk1_value = X_pk1_value
202 AND fad.pk2_value = X_pk2_value;
203
204 ELSIF X_pk4_value IS NULL THEN -- performance change if
205 IF (l_delete_document_flag = 'Y') THEN
206 -- need to delete from each sub-table holding the data
207 -- doing this in a loop to reduce amount of code
208 DELETE FROM fnd_documents_short_text
209 WHERE media_id IN
210 (SELECT fd.media_id
211 FROM fnd_documents_tl fdtl,
212 fnd_documents fd,
213 fnd_attached_documents fad
214 WHERE fdtl.document_id = fd.document_id
215 AND fd.document_id = fad.document_id
216 AND fd.usage_type = 'O'
217 AND fd.datatype_id = 1
218 AND fad.entity_name = X_entity_name
219 AND fad.pk1_value = X_pk1_value
220 AND fad.pk2_value = X_pk2_value
221 AND fad.pk3_value = X_pk3_value);
222
223
224 DELETE FROM fnd_documents_long_text
225 WHERE media_id IN
226 (SELECT fd.media_id
227 FROM fnd_documents_tl fdtl,
228 fnd_documents fd,
229 fnd_attached_documents fad
230 WHERE fdtl.document_id = fd.document_id
231 AND fd.document_id = fad.document_id
232 AND fd.usage_type = 'O'
233 AND fd.datatype_id = 2
234 AND fad.entity_name = X_entity_name
235 AND fad.pk1_value = X_pk1_value
236 AND fad.pk2_value = X_pk2_value
237 AND fad.pk3_value = X_pk3_value);
238
239 DELETE FROM fnd_documents_long_raw
240 WHERE media_id IN
241 (SELECT fd.media_id
242 FROM fnd_documents_tl fdtl,
243 fnd_documents fd,
244 fnd_attached_documents fad
245 WHERE fdtl.document_id = fd.document_id
246 AND fd.document_id = fad.document_id
247 AND fd.usage_type = 'O'
248 AND fd.datatype_id IN (3,4)
249 AND fad.entity_name = X_entity_name
250 AND fad.pk1_value = X_pk1_value
251 AND fad.pk2_value = X_pk2_value
252 AND fad.pk3_value = X_pk3_value);
253
254 DELETE FROM fnd_lobs
255 WHERE file_id IN
256 (SELECT fd.media_id
257 FROM fnd_documents_tl fdtl,
258 fnd_documents fd,
259 fnd_attached_documents fad
260 WHERE fdtl.document_id = fd.document_id
261 AND fd.document_id = fad.document_id
262 AND fd.usage_type = 'O'
263 AND fd.datatype_id = 6
264 AND fad.entity_name = X_entity_name
265 AND fad.pk1_value = X_pk1_value
266 AND fad.pk2_value = X_pk2_value
267 AND fad.pk3_value = X_pk3_value);
268
269 -- Delete from FND_DOCUMENTS_TL table
270 DELETE FROM fnd_documents_tl
271 WHERE document_id IN
272 (SELECT fad.document_id
273 FROM fnd_attached_documents fad, fnd_documents fd
274 WHERE fad.document_id = fd.document_id
275 AND fd.usage_type = 'O'
276 AND fad.entity_name = X_entity_name
277 AND fad.pk1_value = X_pk1_value
278 AND fad.pk2_value = X_pk2_value
279 AND fad.pk3_value = X_pk3_value);
280
281 -- Delete from FND_DOCUMENTS table
282 DELETE FROM fnd_documents
283 WHERE usage_type = 'O'
284 AND document_id IN
285 (SELECT document_id
286 FROM fnd_attached_documents fad
287 WHERE fad.entity_name = X_entity_name
288 AND fad.pk1_value = X_pk1_value
289 AND fad.pk2_value = X_pk2_value
290 AND fad.pk3_value = X_pk3_value);
291 END IF; -- end of if l_delete_document_flag is Y
292
293 -- delete from FND_ATTACHED_DOCUMENTS table
294 DELETE FROM fnd_attached_documents fad
295 WHERE fad.entity_name = X_entity_name
296 AND fad.automatically_added_flag like decode(X_automatically_added_flag,NULL,'%',X_automatically_added_flag)
297 AND fad.pk1_value = X_pk1_value
298 AND fad.pk2_value = X_pk2_value
299 AND fad.pk3_value = X_pk3_value;
300
301 ELSE -- performance change if
302 IF (l_delete_document_flag = 'Y') THEN
303 -- need to delete from each sub-table holding the data
304 -- doing this in a loop to reduce amount of code
305 DELETE FROM fnd_documents_short_text
306 WHERE media_id IN
307 (SELECT fd.media_id
308 FROM fnd_documents_tl fdtl,
309 fnd_documents fd,
310 fnd_attached_documents fad
311 WHERE fdtl.document_id = fd.document_id
312 AND fd.document_id = fad.document_id
313 AND fd.usage_type = 'O'
314 AND fd.datatype_id = 1
315 AND fad.entity_name = X_entity_name
316 AND fad.pk1_value = X_pk1_value
317 AND fad.pk2_value = X_pk2_value
318 AND fad.pk3_value = X_pk3_value
319 OR X_pk4_value IS NULL
320 AND fad.pk4_value = X_pk4_value
321 OR X_pk5_value IS NULL
322 AND fad.pk5_value = X_pk5_value);
323
324
325 DELETE FROM fnd_documents_long_text
326 WHERE media_id IN
327 (SELECT fd.media_id
328 FROM fnd_documents_tl fdtl,
329 fnd_documents fd,
330 fnd_attached_documents fad
331 WHERE fdtl.document_id = fd.document_id
332 AND fd.document_id = fad.document_id
333 AND fd.usage_type = 'O'
334 AND fd.datatype_id = 2
335 AND fad.entity_name = X_entity_name
336 AND fad.pk1_value = X_pk1_value
337 AND fad.pk2_value = X_pk2_value
338 AND fad.pk3_value = X_pk3_value
339 OR X_pk4_value IS NULL
340 AND fad.pk4_value = X_pk4_value
341 OR X_pk5_value IS NULL
342 AND fad.pk5_value = X_pk5_value);
343
344 DELETE FROM fnd_documents_long_raw
345 WHERE media_id IN
346 (SELECT fd.media_id
347 FROM fnd_documents_tl fdtl,
348 fnd_documents fd,
349 fnd_attached_documents fad
350 WHERE fdtl.document_id = fd.document_id
351 AND fd.document_id = fad.document_id
352 AND fd.usage_type = 'O'
353 AND fd.datatype_id IN (3,4)
354 AND fad.entity_name = X_entity_name
355 AND fad.pk1_value = X_pk1_value
356 AND fad.pk2_value = X_pk2_value
357 AND fad.pk3_value = X_pk3_value
358 OR X_pk4_value IS NULL
359 AND fad.pk4_value = X_pk4_value
360 OR X_pk5_value IS NULL
361 AND fad.pk5_value = X_pk5_value);
362
363 DELETE FROM fnd_lobs
364 WHERE file_id IN
365 (SELECT fd.media_id
366 FROM fnd_documents_tl fdtl,
367 fnd_documents fd,
368 fnd_attached_documents fad
369 WHERE fdtl.document_id = fd.document_id
370 AND fd.document_id = fad.document_id
371 AND fd.usage_type = 'O'
372 AND fd.datatype_id = 6
373 AND fad.entity_name = X_entity_name
374 AND fad.pk1_value = X_pk1_value
375 AND fad.pk2_value = X_pk2_value
376 AND fad.pk3_value = X_pk3_value
377 OR X_pk4_value IS NULL
378 AND fad.pk4_value = X_pk4_value
379 OR X_pk5_value IS NULL
380 AND fad.pk5_value = X_pk5_value);
381
382 -- Delete from FND_DOCUMENTS_TL table
383 -- BUG#5060588 added Parens around OR's.
384 DELETE FROM fnd_documents_tl
385 WHERE document_id IN
386 (SELECT fad.document_id
387 FROM fnd_attached_documents fad, fnd_documents fd
388 WHERE fad.document_id = fd.document_id
389 AND fd.usage_type = 'O'
390 AND fad.entity_name = X_entity_name
391 AND fad.pk1_value = X_pk1_value
392 AND fad.pk2_value = X_pk2_value
393 AND (fad.pk3_value = X_pk3_value
394 OR X_pk4_value IS NULL)
395 AND (fad.pk4_value = X_pk4_value
396 OR X_pk5_value IS NULL)
397 AND fad.pk5_value = X_pk5_value);
398
399 -- Delete from FND_DOCUMENTS table
400 -- BUG#5060588 added Parens around OR's.
401 DELETE FROM fnd_documents
402 WHERE usage_type = 'O'
403 AND document_id IN
404 (SELECT document_id
405 FROM fnd_attached_documents fad
406 WHERE fad.entity_name = X_entity_name
407 AND fad.pk1_value = X_pk1_value
408 AND fad.pk2_value = X_pk2_value
409 AND (fad.pk3_value = X_pk3_value
410 OR X_pk4_value IS NULL)
411 AND (fad.pk4_value = X_pk4_value
412 OR X_pk5_value IS NULL)
413 AND fad.pk5_value = X_pk5_value);
414 END IF; -- end of if l_delete_document_flag is Y
415
416 -- delete from FND_ATTACHED_DOCUMENTS table
417 -- BUG#5060588 added Parens around OR's.
418 DELETE FROM fnd_attached_documents fad
419 WHERE fad.entity_name = X_entity_name
420 AND fad.automatically_added_flag like decode(X_automatically_added_flag,NULL,'%',X_automatically_added_flag)
421 AND fad.pk1_value = X_pk1_value
422 AND fad.pk2_value = X_pk2_value
426 OR X_pk5_value IS NULL)
423 AND (fad.pk3_value = X_pk3_value
424 OR X_pk4_value IS NULL)
425 AND (fad.pk4_value = X_pk4_value
427 AND fad.pk5_value = X_pk5_value;
428
429 END IF; -- performance change IF
430
431 END delete_attachments;
432
433
434 -- API to copy attachments from one record to another
435 -- BUG#2790775
436 PROCEDURE copy_attachments(X_from_entity_name IN VARCHAR2,
437 X_from_pk1_value IN VARCHAR2,
438 X_from_pk2_value IN VARCHAR2 DEFAULT NULL,
439 X_from_pk3_value IN VARCHAR2 DEFAULT NULL,
440 X_from_pk4_value IN VARCHAR2 DEFAULT NULL,
441 X_from_pk5_value IN VARCHAR2 DEFAULT NULL,
442 X_to_entity_name IN VARCHAR2,
443 X_to_pk1_value IN VARCHAR2,
444 X_to_pk2_value IN VARCHAR2 DEFAULT NULL,
445 X_to_pk3_value IN VARCHAR2 DEFAULT NULL,
446 X_to_pk4_value IN VARCHAR2 DEFAULT NULL,
447 X_to_pk5_value IN VARCHAR2 DEFAULT NULL,
448 X_created_by IN NUMBER DEFAULT NULL,
449 X_last_update_login IN NUMBER DEFAULT NULL,
450 X_program_application_id IN NUMBER DEFAULT NULL,
451 X_program_id IN NUMBER DEFAULT NULL,
452 X_request_id IN NUMBER DEFAULT NULL,
453 X_automatically_added_flag IN VARCHAR2 DEFAULT NULL,
454 X_from_category_id IN NUMBER DEFAULT NULL,
455 X_to_category_id IN NUMBER DEFAULT NULL) IS
456 CURSOR docpk1 IS
457 SELECT fad.seq_num, fad.document_id,
458 fad.attribute_category, fad.attribute1, fad.attribute2,
459 fad.attribute3, fad.attribute4, fad.attribute5,
460 fad.attribute6, fad.attribute7, fad.attribute8,
461 fad.attribute9, fad.attribute10, fad.attribute11,
462 fad.attribute12, fad.attribute13, fad.attribute14,
463 fad.attribute15, fad.column1, fad.automatically_added_flag,
464 fad.category_id att_cat, fad.pk2_value, fad.pk3_value,
465 fad.pk4_value, fad.pk5_value,
466 fd.datatype_id, fd.category_id, fd.security_type, fd.security_id,
467 fd.publish_flag, fd.image_type, fd.storage_type,
468 fd.usage_type, fd.start_date_active, fd.end_date_active,
469 fdtl.language, fdtl.description, fd.file_name,
470 fd.media_id, fdtl.doc_attribute_category dattr_cat,
471 fdtl.doc_attribute1 dattr1, fdtl.doc_attribute2 dattr2,
472 fdtl.doc_attribute3 dattr3, fdtl.doc_attribute4 dattr4,
473 fdtl.doc_attribute5 dattr5, fdtl.doc_attribute6 dattr6,
474 fdtl.doc_attribute7 dattr7, fdtl.doc_attribute8 dattr8,
475 fdtl.doc_attribute9 dattr9, fdtl.doc_attribute10 dattr10,
476 fdtl.doc_attribute11 dattr11, fdtl.doc_attribute12 dattr12,
477 fdtl.doc_attribute13 dattr13, fdtl.doc_attribute14 dattr14,
478 fdtl.doc_attribute15 dattr15, fd.url, fdtl.title
479 FROM fnd_attached_documents fad,
480 fnd_documents fd,
481 fnd_documents_tl fdtl
482 WHERE fad.document_id = fd.document_id
483 AND fd.document_id = fdtl.document_id
484 AND fdtl.language = userenv('LANG')
485 AND fad.entity_name = X_from_entity_name
486 AND fad.pk1_value = X_from_pk1_value
487 AND (X_from_category_id IS NULL
488 OR (fad.category_id = X_from_category_id
489 OR (fad.category_id is NULL AND fd.category_id = X_from_category_id)))
490 AND fad.automatically_added_flag like decode(X_automatically_added_flag,NULL,'%',X_automatically_added_flag);
491
492
493 CURSOR docpk2 IS
494 SELECT fad.seq_num, fad.document_id,
495 fad.attribute_category, fad.attribute1, fad.attribute2,
496 fad.attribute3, fad.attribute4, fad.attribute5,
497 fad.attribute6, fad.attribute7, fad.attribute8,
498 fad.attribute9, fad.attribute10, fad.attribute11,
499 fad.attribute12, fad.attribute13, fad.attribute14,
500 fad.attribute15, fad.column1, fad.automatically_added_flag,
501 fad.category_id att_cat, fad.pk2_value, fad.pk3_value,
502 fad.pk4_value, fad.pk5_value,
503 fd.datatype_id, fd.category_id, fd.security_type, fd.security_id,
504 fd.publish_flag, fd.image_type, fd.storage_type,
505 fd.usage_type, fd.start_date_active, fd.end_date_active,
506 fdtl.language, fdtl.description, fd.file_name,
507 fd.media_id, fdtl.doc_attribute_category dattr_cat,
508 fdtl.doc_attribute1 dattr1, fdtl.doc_attribute2 dattr2,
509 fdtl.doc_attribute3 dattr3, fdtl.doc_attribute4 dattr4,
510 fdtl.doc_attribute5 dattr5, fdtl.doc_attribute6 dattr6,
511 fdtl.doc_attribute7 dattr7, fdtl.doc_attribute8 dattr8,
512 fdtl.doc_attribute9 dattr9, fdtl.doc_attribute10 dattr10,
513 fdtl.doc_attribute11 dattr11, fdtl.doc_attribute12 dattr12,
514 fdtl.doc_attribute13 dattr13, fdtl.doc_attribute14 dattr14,
515 fdtl.doc_attribute15 dattr15, fd.url, fdtl.title
516 FROM fnd_attached_documents fad,
517 fnd_documents fd,
518 fnd_documents_tl fdtl
519 WHERE fad.document_id = fd.document_id
520 AND fd.document_id = fdtl.document_id
521 AND fdtl.language = userenv('LANG')
522 AND fad.entity_name = X_from_entity_name
523 AND fad.pk1_value = X_from_pk1_value
524 AND fad.pk2_value = X_from_pk2_value
525 AND (X_from_category_id IS NULL
526 OR (fad.category_id = X_from_category_id
527 OR (fad.category_id is NULL AND fd.category_id = X_from_category_id)))
528 AND fad.automatically_added_flag like decode(X_automatically_added_flag,NULL,'%',X_automatically_added_flag);
529
530
531
532
533 CURSOR docpk3 IS
534 SELECT fad.seq_num, fad.document_id,
535 fad.attribute_category, fad.attribute1, fad.attribute2,
536 fad.attribute3, fad.attribute4, fad.attribute5,
540 fad.attribute15, fad.column1, fad.automatically_added_flag,
537 fad.attribute6, fad.attribute7, fad.attribute8,
538 fad.attribute9, fad.attribute10, fad.attribute11,
539 fad.attribute12, fad.attribute13, fad.attribute14,
541 fad.category_id att_cat, fad.pk2_value, fad.pk3_value,
542 fad.pk4_value, fad.pk5_value,
543 fd.datatype_id, fd.category_id, fd.security_type, fd.security_id,
544 fd.publish_flag, fd.image_type, fd.storage_type,
545 fd.usage_type, fd.start_date_active, fd.end_date_active,
546 fdtl.language, fdtl.description, fd.file_name,
547 fd.media_id, fdtl.doc_attribute_category dattr_cat,
548 fdtl.doc_attribute1 dattr1, fdtl.doc_attribute2 dattr2,
549 fdtl.doc_attribute3 dattr3, fdtl.doc_attribute4 dattr4,
550 fdtl.doc_attribute5 dattr5, fdtl.doc_attribute6 dattr6,
551 fdtl.doc_attribute7 dattr7, fdtl.doc_attribute8 dattr8,
552 fdtl.doc_attribute9 dattr9, fdtl.doc_attribute10 dattr10,
553 fdtl.doc_attribute11 dattr11, fdtl.doc_attribute12 dattr12,
554 fdtl.doc_attribute13 dattr13, fdtl.doc_attribute14 dattr14,
555 fdtl.doc_attribute15 dattr15, fd.url, fdtl.title
556 FROM fnd_attached_documents fad,
557 fnd_documents fd,
558 fnd_documents_tl fdtl
559 WHERE fad.document_id = fd.document_id
560 AND fd.document_id = fdtl.document_id
561 AND fdtl.language = userenv('LANG')
562 AND fad.entity_name = X_from_entity_name
563 AND fad.pk1_value = X_from_pk1_value
564 AND fad.pk2_value = X_from_pk2_value
565 AND fad.pk3_value = X_from_pk3_value
566 AND (X_from_category_id IS NULL
567 OR (fad.category_id = X_from_category_id
568 OR (fad.category_id is NULL AND fd.category_id = X_from_category_id)))
569 AND fad.automatically_added_flag like decode(X_automatically_added_flag,NULL,'%',X_automatically_added_flag);
570
571
572
573
574 CURSOR doclist IS
575 SELECT fad.seq_num, fad.document_id,
576 fad.attribute_category, fad.attribute1, fad.attribute2,
577 fad.attribute3, fad.attribute4, fad.attribute5,
578 fad.attribute6, fad.attribute7, fad.attribute8,
579 fad.attribute9, fad.attribute10, fad.attribute11,
580 fad.attribute12, fad.attribute13, fad.attribute14,
581 fad.attribute15, fad.column1, fad.automatically_added_flag,
582 fad.category_id att_cat, fad.pk2_value, fad.pk3_value,
583 fad.pk4_value, fad.pk5_value,
584 fd.datatype_id, fd.category_id, fd.security_type, fd.security_id,
585 fd.publish_flag, fd.image_type, fd.storage_type,
586 fd.usage_type, fd.start_date_active, fd.end_date_active,
587 fdtl.language, fdtl.description, fd.file_name,
588 fd.media_id, fdtl.doc_attribute_category dattr_cat,
589 fdtl.doc_attribute1 dattr1, fdtl.doc_attribute2 dattr2,
590 fdtl.doc_attribute3 dattr3, fdtl.doc_attribute4 dattr4,
591 fdtl.doc_attribute5 dattr5, fdtl.doc_attribute6 dattr6,
592 fdtl.doc_attribute7 dattr7, fdtl.doc_attribute8 dattr8,
593 fdtl.doc_attribute9 dattr9, fdtl.doc_attribute10 dattr10,
594 fdtl.doc_attribute11 dattr11, fdtl.doc_attribute12 dattr12,
595 fdtl.doc_attribute13 dattr13, fdtl.doc_attribute14 dattr14,
596 fdtl.doc_attribute15 dattr15, fd.url, fdtl.title
597 FROM fnd_attached_documents fad,
598 fnd_documents fd,
599 fnd_documents_tl fdtl
600 WHERE fad.document_id = fd.document_id
601 AND fd.document_id = fdtl.document_id
602 AND fdtl.language = userenv('LANG')
603 AND fad.entity_name = X_from_entity_name
604 AND fad.pk1_value = X_from_pk1_value
605 AND (X_from_pk2_value IS NULL
606 OR fad.pk2_value = X_from_pk2_value)
607 AND (X_from_pk3_value IS NULL
608 OR fad.pk3_value = X_from_pk3_value)
609 AND (X_from_pk4_value IS NULL
610 OR fad.pk4_value = X_from_pk4_value)
611 AND (X_from_pk5_value IS NULL
612 OR fad.pk5_value = X_from_pk5_value)
613 AND (X_from_category_id IS NULL
614 OR (fad.category_id = X_from_category_id
615 OR (fad.category_id is NULL AND fd.category_id = X_from_category_id)))
616 AND fad.automatically_added_flag like decode(X_automatically_added_flag,NULL,'%',X_automatically_added_flag);
617
618
619
620
621 CURSOR shorttext (mid NUMBER) IS
622 SELECT short_text
623 FROM fnd_documents_short_text
624 WHERE media_id = mid;
625
626 CURSOR longtext (mid NUMBER) IS
627 SELECT long_text
628 FROM fnd_documents_long_text
629 WHERE media_id = mid;
630
631 CURSOR fnd_lobs_cur (mid NUMBER) IS
632 SELECT file_id,
633 file_name,
634 file_content_type,
635 upload_date,
636 expiration_date,
637 program_name,
638 program_tag,
639 file_data,
640 language,
641 oracle_charset,
642 file_format
643 FROM fnd_lobs
644 WHERE file_id = mid;
645
646 media_id_tmp NUMBER;
647 document_id_tmp NUMBER;
648 row_id_tmp VARCHAR2(30);
649 short_text_tmp VARCHAR2(4000);
650 long_text_tmp VARCHAR2(32767);
651 docrec doclist%ROWTYPE;
652 fnd_lobs_rec fnd_lobs_cur%ROWTYPE;
653 BEGIN
654 -- Use cursor loop to get all attachments associated with
655 -- the from_entity
656 IF (X_from_entity_name IS NULL OR X_from_pk1_value IS NULL) THEN
657 RETURN;
658 END IF;
659
660 IF X_from_pk2_value IS NULL THEN -- performance change IF
661 OPEN docpk1;
665 OPEN docpk3;
662 ELSIF X_from_pk3_value IS NULL THEN
663 OPEN docpk2;
664 ELSIF X_from_pk4_value IS NULL THEN
666 ELSE
667 OPEN doclist;
668 END IF;
669
670 <<pkloop>>
671 LOOP
672
673 IF X_from_pk2_value IS NULL THEN -- performance change IF
674 FETCH docpk1 INTO docrec;
675 --EXIT
676 IF (docpk1%notfound) then
677 EXIT pkloop;
678 END IF;
679 ELSIF X_from_pk3_value IS NULL THEN
680 FETCH docpk2 INTO docrec;
681 IF (docpk2%notfound) then
682 EXIT pkloop;
683 END IF;
684 ELSIF X_from_pk4_value IS NULL THEN
685 FETCH docpk3 INTO docrec;
686 IF (docpk3%notfound) then
687 EXIT pkloop;
688 END IF;
689 ELSE
690 FETCH doclist INTO docrec;
691 IF (doclist%notfound) then
692 EXIT pkloop;
693 END IF;
694 END IF;
695
696 --FOR docrec IN doclist LOOP
697 -- One-Time docs that Short Text or Long Text will have
698 -- to be copied into a new document (Long Text will be
699 -- truncated to 32K). Create the new document records
700 -- before creating the attachment record
701 --
702 IF (docrec.usage_type = 'O'
703 AND docrec.datatype_id IN (1,2,5,6) ) THEN
704 -- Create Documents records
705 FND_DOCUMENTS_PKG.Insert_Row(row_id_tmp,
706 document_id_tmp,
707 SYSDATE,
708 NVL(X_created_by,0),
709 SYSDATE,
710 NVL(X_created_by,0),
711 X_last_update_login,
712 docrec.datatype_id,
713 NVL(X_to_category_id, docrec.category_id),
714 docrec.security_type,
715 docrec.security_id,
716 docrec.publish_flag,
717 docrec.image_type,
718 docrec.storage_type,
719 docrec.usage_type,
720 docrec.start_date_active,
721 docrec.end_date_active,
722 X_request_id,
723 X_program_application_id,
724 X_program_id,
725 SYSDATE,
726 docrec.language,
727 docrec.description,
728 docrec.file_name,
729 media_id_tmp,
730 docrec.dattr_cat, docrec.dattr1,
731 docrec.dattr2, docrec.dattr3,
732 docrec.dattr4, docrec.dattr5,
733 docrec.dattr6, docrec.dattr7,
734 docrec.dattr8, docrec.dattr9,
735 docrec.dattr10, docrec.dattr11,
736 docrec.dattr12, docrec.dattr13,
737 docrec.dattr14, docrec.dattr15,
738 'N',docrec.url, docrec.title);
739
740 -- overwrite document_id from original
741 -- cursor for later insert into
742 -- fnd_attached_documents
743 docrec.document_id := document_id_tmp;
744
745 -- Duplicate short or long text
746 IF (docrec.datatype_id = 1) THEN
747 -- Handle short Text
748 -- get original data
749 OPEN shorttext(docrec.media_id);
750 FETCH shorttext INTO short_text_tmp;
751 CLOSE shorttext;
752
753 INSERT INTO fnd_documents_short_text (
754 media_id,
755 short_text)
756 VALUES (
757 media_id_tmp,
758 short_text_tmp);
759 media_id_tmp := '';
760
761 ELSIF (docrec.datatype_id = 2) THEN
762 -- Handle long text
763 -- get original data
764 OPEN longtext(docrec.media_id);
765 FETCH longtext INTO long_text_tmp;
766 CLOSE longtext;
767
768 INSERT INTO fnd_documents_long_text (
769 media_id,
770 long_text)
771 VALUES (
772 media_id_tmp,
773 long_text_tmp);
774 media_id_tmp := '';
775
776 ELSIF (docrec.datatype_id=6) THEN
777
778 OPEN fnd_lobs_cur(docrec.media_id);
779 FETCH fnd_lobs_cur
780 INTO fnd_lobs_rec.file_id,
781 fnd_lobs_rec.file_name,
782 fnd_lobs_rec.file_content_type,
783 fnd_lobs_rec.upload_date,
784 fnd_lobs_rec.expiration_date,
785 fnd_lobs_rec.program_name,
786 fnd_lobs_rec.program_tag,
787 fnd_lobs_rec.file_data,
788 fnd_lobs_rec.language,
789 fnd_lobs_rec.oracle_charset,
790 fnd_lobs_rec.file_format;
791 CLOSE fnd_lobs_cur;
792
793 INSERT INTO fnd_lobs (
794 file_id,
795 file_name,
796 file_content_type,
797 upload_date,
798 expiration_date,
799 program_name,
800 program_tag,
801 file_data,
802 language,
803 oracle_charset,
804 file_format)
805 VALUES (
809 fnd_lobs_rec.upload_date,
806 media_id_tmp,
807 fnd_lobs_rec.file_name,
808 fnd_lobs_rec.file_content_type,
810 fnd_lobs_rec.expiration_date,
811 fnd_lobs_rec.program_name,
812 fnd_lobs_rec.program_tag,
813 fnd_lobs_rec.file_data,
814 fnd_lobs_rec.language,
815 fnd_lobs_rec.oracle_charset,
816 fnd_lobs_rec.file_format);
817
818 media_id_tmp := '';
819
820 END IF; -- end of duplicating text
821
822
823 END IF; -- end if usage_type = 'O' and datatype in (1,2,6)
824
825 -- Create attachment record
826 INSERT INTO fnd_attached_documents
827 (attached_document_id,
828 document_id,
829 creation_date,
830 created_by,
831 last_update_date,
832 last_updated_by,
833 last_update_login,
834 seq_num,
835 entity_name,
836 pk1_value, pk2_value, pk3_value,
837 pk4_value, pk5_value,
838 automatically_added_flag,
839 program_application_id, program_id,
840 program_update_date, request_id,
841 attribute_category, attribute1,
842 attribute2, attribute3, attribute4,
843 attribute5, attribute6, attribute7,
844 attribute8, attribute9, attribute10,
845 attribute11, attribute12, attribute13,
846 attribute14, attribute15, column1, category_id) VALUES
847 (fnd_attached_documents_s.nextval,
848 docrec.document_id,
849 sysdate,
850 NVL(X_created_by,0),
851 sysdate,
852 NVL(X_created_by,0),
853 X_last_update_login,
854 docrec.seq_num,
855 X_to_entity_name,
856 X_to_pk1_value,
857 X_to_pk2_value,
858 X_to_pk3_value,
859 X_to_pk4_value,
860 X_to_pk5_value,
861 docrec.automatically_added_flag,
862 X_program_application_id, X_program_id,
863 sysdate, X_request_id,
864 docrec.attribute_category, docrec.attribute1,
865 docrec.attribute2, docrec.attribute3,
866 docrec.attribute4, docrec.attribute5,
867 docrec.attribute6, docrec.attribute7,
868 docrec.attribute8, docrec.attribute9,
869 docrec.attribute10, docrec.attribute11,
870 docrec.attribute12, docrec.attribute13,
871 docrec.attribute14, docrec.attribute15,
872 docrec.column1,
873 NVL(X_to_category_id, NVL(docrec.att_cat, docrec.category_id)));
874
875 -- Update the document to be a std document if it
876 -- was an ole or image that wasn't already a std doc
877 -- (images should be created as Std, but just in case)
878 IF (docrec.datatype_id IN (3,4)
879 AND docrec.usage_type <> 'S') THEN
880 UPDATE fnd_documents
881 SET usage_type = 'S'
882 WHERE document_id = docrec.document_id;
883 END IF;
884
885 END LOOP; -- end of working through all attachments
886 -- close cursors.
887 IF X_from_pk2_value IS NULL THEN -- performance change IF
888 CLOSE docpk1;
889 ELSIF X_from_pk3_value IS NULL THEN
890 CLOSE docpk2;
891 ELSIF X_from_pk4_value IS NULL THEN
892 CLOSE docpk3;
893 ELSE
894 CLOSE doclist;
895 END IF;
896
897 EXCEPTION WHEN OTHERS THEN
898 -- need to close all cursors
899 CLOSE docpk1;
900 CLOSE docpk2;
901 CLOSE docpk3;
902 CLOSE doclist;
903 CLOSE shorttext;
904 CLOSE longtext;
905 CLOSE fnd_lobs_cur;
906
907 END copy_attachments;
908
909
910 END fnd_attached_documents2_pkg;
[Home] [Help]