1 PACKAGE BODY fnd_attached_documents2_pkg as
2 /* $Header: AFAKATDB.pls 120.12 2011/11/17 18:46:42 ctilley 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 AND fad.pk4_value = X_pk4_value
320 AND (X_pk5_value IS NULL
321 OR fad.pk5_value = X_pk5_value));
322
323
324 DELETE FROM fnd_documents_long_text
325 WHERE media_id IN
326 (SELECT fd.media_id
327 FROM fnd_documents_tl fdtl,
328 fnd_documents fd,
329 fnd_attached_documents fad
330 WHERE fdtl.document_id = fd.document_id
331 AND fd.document_id = fad.document_id
332 AND fd.usage_type = 'O'
333 AND fd.datatype_id = 2
334 AND fad.entity_name = X_entity_name
335 AND fad.pk1_value = X_pk1_value
336 AND fad.pk2_value = X_pk2_value
337 AND fad.pk3_value = X_pk3_value
338 AND fad.pk4_value = X_pk4_value
339 AND (X_pk5_value IS NULL
340 OR fad.pk5_value = X_pk5_value));
341
342 DELETE FROM fnd_documents_long_raw
343 WHERE media_id IN
344 (SELECT fd.media_id
345 FROM fnd_documents_tl fdtl,
346 fnd_documents fd,
347 fnd_attached_documents fad
348 WHERE fdtl.document_id = fd.document_id
349 AND fd.document_id = fad.document_id
350 AND fd.usage_type = 'O'
351 AND fd.datatype_id IN (3,4)
352 AND fad.entity_name = X_entity_name
353 AND fad.pk1_value = X_pk1_value
354 AND fad.pk2_value = X_pk2_value
355 AND fad.pk3_value = X_pk3_value
356 AND fad.pk4_value = X_pk4_value
357 AND (X_pk5_value IS NULL
358 OR fad.pk5_value = X_pk5_value));
359
360 DELETE FROM fnd_lobs
361 WHERE file_id IN
362 (SELECT fd.media_id
363 FROM fnd_documents_tl fdtl,
364 fnd_documents fd,
365 fnd_attached_documents fad
366 WHERE fdtl.document_id = fd.document_id
367 AND fd.document_id = fad.document_id
368 AND fd.usage_type = 'O'
369 AND fd.datatype_id = 6
370 AND fad.entity_name = X_entity_name
371 AND fad.pk1_value = X_pk1_value
372 AND fad.pk2_value = X_pk2_value
373 AND fad.pk3_value = X_pk3_value
374 AND fad.pk4_value = X_pk4_value
375 AND (X_pk5_value IS NULL
376 OR fad.pk5_value = X_pk5_value));
377
378 -- Delete from FND_DOCUMENTS_TL table
379 -- BUG#5060588 added Parens around OR's.
380 DELETE FROM fnd_documents_tl
381 WHERE document_id IN
382 (SELECT fad.document_id
383 FROM fnd_attached_documents fad, fnd_documents fd
384 WHERE fad.document_id = fd.document_id
385 AND fd.usage_type = 'O'
386 AND fad.entity_name = X_entity_name
387 AND fad.pk1_value = X_pk1_value
388 AND fad.pk2_value = X_pk2_value
389 AND fad.pk3_value = X_pk3_value
390 AND fad.pk4_value = X_pk4_value
391 ANd (X_pk5_value IS NULL
392 OR fad.pk5_value = X_pk5_value));
393
394 -- Delete from FND_DOCUMENTS table
395 -- BUG#5060588 added Parens around OR's.
396 DELETE FROM fnd_documents
397 WHERE usage_type = 'O'
398 AND document_id IN
399 (SELECT document_id
400 FROM fnd_attached_documents fad
401 WHERE fad.entity_name = X_entity_name
402 AND fad.pk1_value = X_pk1_value
403 AND fad.pk2_value = X_pk2_value
404 AND fad.pk3_value = X_pk3_value
405 AND fad.pk4_value = X_pk4_value
406 AND (X_pk5_value IS NULL
407 OR fad.pk5_value = X_pk5_value));
408 END IF; -- end of if l_delete_document_flag is Y
409
410 -- delete from FND_ATTACHED_DOCUMENTS table
411 -- BUG#5060588 added Parens around OR's.
412 DELETE FROM fnd_attached_documents fad
413 WHERE fad.entity_name = X_entity_name
414 AND fad.automatically_added_flag like decode(X_automatically_added_flag,NULL,'%',X_automatically_added_flag)
415 AND fad.pk1_value = X_pk1_value
416 AND fad.pk2_value = X_pk2_value
417 AND fad.pk3_value = X_pk3_value
418 AND fad.pk4_value = X_pk4_value
419 AND (X_pk5_value IS NULL
420 OR fad.pk5_value = X_pk5_value);
421
422 END IF; -- performance change IF
423
424 END delete_attachments;
425
429 X_from_pk1_value IN VARCHAR2,
426 -- API to copy attachments from one record to another
427 -- BUG#2790775
428 PROCEDURE copy_attachments(X_from_entity_name IN VARCHAR2,
430 X_from_pk2_value IN VARCHAR2 DEFAULT NULL,
431 X_from_pk3_value IN VARCHAR2 DEFAULT NULL,
432 X_from_pk4_value IN VARCHAR2 DEFAULT NULL,
433 X_from_pk5_value IN VARCHAR2 DEFAULT NULL,
434 X_to_entity_name IN VARCHAR2,
435 X_to_pk1_value IN VARCHAR2,
436 X_to_pk2_value IN VARCHAR2 DEFAULT NULL,
437 X_to_pk3_value IN VARCHAR2 DEFAULT NULL,
438 X_to_pk4_value IN VARCHAR2 DEFAULT NULL,
439 X_to_pk5_value IN VARCHAR2 DEFAULT NULL,
440 X_created_by IN NUMBER DEFAULT NULL,
441 X_last_update_login IN NUMBER DEFAULT NULL,
442 X_program_application_id IN NUMBER DEFAULT NULL,
443 X_program_id IN NUMBER DEFAULT NULL,
444 X_request_id IN NUMBER DEFAULT NULL,
445 X_automatically_added_flag IN VARCHAR2 DEFAULT NULL,
446 X_from_category_id IN NUMBER DEFAULT NULL,
447 X_to_category_id IN NUMBER DEFAULT NULL) IS
448
449 BEGIN
450
451 copy_attachments(X_from_entity_name => X_from_entity_name,
452 X_from_pk1_value => X_from_pk1_value,
453 X_from_pk2_value => X_from_pk2_value,
454 X_from_pk3_value => X_from_pk3_value,
455 X_from_pk4_value => X_from_pk4_value,
456 X_from_pk5_value => X_from_pk5_value,
457 X_to_entity_name => X_to_entity_name,
458 X_to_pk1_value => X_to_pk1_value,
459 X_to_pk2_value => X_to_pk2_value,
460 X_to_pk3_value => X_to_pk3_value,
461 X_to_pk4_value => X_to_pk4_value,
462 X_to_pk5_value => X_to_pk5_value,
463 X_created_by => X_created_by,
464 X_last_update_login => X_last_update_login,
465 X_program_application_id => X_program_application_id,
466 X_program_id =>X_program_id,
467 X_request_id => X_request_id,
468 X_automatically_added_flag => X_automatically_added_flag,
469 X_from_category_id => X_from_category_id,
470 X_to_category_id => X_to_category_id,
471 X_orig_attach_doc_id => NULL);
472
473 END copy_attachments;
474
475
476 -- API to copy attachments from one record to another storing
477 -- the originating attachment and document ids
478 -- BUG#12910292
479 PROCEDURE copy_attachments(X_from_entity_name IN VARCHAR2,
480 X_from_pk1_value IN VARCHAR2,
481 X_from_pk2_value IN VARCHAR2 DEFAULT NULL,
482 X_from_pk3_value IN VARCHAR2 DEFAULT NULL,
483 X_from_pk4_value IN VARCHAR2 DEFAULT NULL,
484 X_from_pk5_value IN VARCHAR2 DEFAULT NULL,
485 X_to_entity_name IN VARCHAR2,
486 X_to_pk1_value IN VARCHAR2,
487 X_to_pk2_value IN VARCHAR2 DEFAULT NULL,
488 X_to_pk3_value IN VARCHAR2 DEFAULT NULL,
489 X_to_pk4_value IN VARCHAR2 DEFAULT NULL,
490 X_to_pk5_value IN VARCHAR2 DEFAULT NULL,
491 X_created_by IN NUMBER DEFAULT NULL,
492 X_last_update_login IN NUMBER DEFAULT NULL,
493 X_program_application_id IN NUMBER DEFAULT NULL,
494 X_program_id IN NUMBER DEFAULT NULL,
495 X_request_id IN NUMBER DEFAULT NULL,
496 X_automatically_added_flag IN VARCHAR2 DEFAULT NULL,
497 X_from_category_id IN NUMBER DEFAULT NULL,
498 X_to_category_id IN NUMBER DEFAULT NULL,
499 X_orig_attach_doc_id IN NUMBER) IS
500 CURSOR docpk1 IS
501 SELECT fad.seq_num, fad.document_id, fad.attached_document_id,
502 fad.attribute_category, fad.attribute1, fad.attribute2,
503 fad.attribute3, fad.attribute4, fad.attribute5,
504 fad.attribute6, fad.attribute7, fad.attribute8,
505 fad.attribute9, fad.attribute10, fad.attribute11,
506 fad.attribute12, fad.attribute13, fad.attribute14,
507 fad.attribute15, fad.column1, fad.automatically_added_flag,
508 fad.category_id att_cat, fad.pk2_value, fad.pk3_value,
509 fad.pk4_value, fad.pk5_value,
510 fd.datatype_id, fd.category_id, fd.security_type, fd.security_id,
511 fd.publish_flag, fd.image_type, fd.storage_type,
512 fd.usage_type, fd.start_date_active, fd.end_date_active,
513 fdtl.language, fdtl.description, fd.file_name,
514 fd.media_id, fdtl.doc_attribute_category dattr_cat,
515 fdtl.doc_attribute1 dattr1, fdtl.doc_attribute2 dattr2,
516 fdtl.doc_attribute3 dattr3, fdtl.doc_attribute4 dattr4,
517 fdtl.doc_attribute5 dattr5, fdtl.doc_attribute6 dattr6,
518 fdtl.doc_attribute7 dattr7, fdtl.doc_attribute8 dattr8,
519 fdtl.doc_attribute9 dattr9, fdtl.doc_attribute10 dattr10,
520 fdtl.doc_attribute11 dattr11, fdtl.doc_attribute12 dattr12,
521 fdtl.doc_attribute13 dattr13, fdtl.doc_attribute14 dattr14,
522 fdtl.doc_attribute15 dattr15, fd.url, fdtl.title, fd.dm_node,
523 fd.dm_folder_path,fd.dm_type, fd.dm_document_id,fd.dm_version_number
524 FROM fnd_attached_documents fad,
525 fnd_documents fd,
526 fnd_documents_tl fdtl
527 WHERE fad.document_id = fd.document_id
531 AND fad.pk1_value = X_from_pk1_value
528 AND fd.document_id = fdtl.document_id
529 AND fdtl.language = userenv('LANG')
530 AND fad.entity_name = X_from_entity_name
532 AND (X_from_category_id IS NULL
533 OR (fad.category_id = X_from_category_id
534 OR (fad.category_id is NULL AND fd.category_id = X_from_category_id)))
535 AND fad.automatically_added_flag like decode(X_automatically_added_flag,NULL,'%',X_automatically_added_flag)
536 AND (X_orig_attach_doc_id is NULL OR fad.attached_document_id = X_orig_attach_doc_id);
537
538
539 CURSOR docpk2 IS
540 SELECT fad.seq_num, fad.document_id, fad.attached_document_id,
541 fad.attribute_category, fad.attribute1, fad.attribute2,
542 fad.attribute3, fad.attribute4, fad.attribute5,
543 fad.attribute6, fad.attribute7, fad.attribute8,
544 fad.attribute9, fad.attribute10, fad.attribute11,
545 fad.attribute12, fad.attribute13, fad.attribute14,
546 fad.attribute15, fad.column1, fad.automatically_added_flag,
547 fad.category_id att_cat, fad.pk2_value, fad.pk3_value,
548 fad.pk4_value, fad.pk5_value,
549 fd.datatype_id, fd.category_id, fd.security_type, fd.security_id,
550 fd.publish_flag, fd.image_type, fd.storage_type,
551 fd.usage_type, fd.start_date_active, fd.end_date_active,
552 fdtl.language, fdtl.description, fd.file_name,
553 fd.media_id, fdtl.doc_attribute_category dattr_cat,
554 fdtl.doc_attribute1 dattr1, fdtl.doc_attribute2 dattr2,
555 fdtl.doc_attribute3 dattr3, fdtl.doc_attribute4 dattr4,
556 fdtl.doc_attribute5 dattr5, fdtl.doc_attribute6 dattr6,
557 fdtl.doc_attribute7 dattr7, fdtl.doc_attribute8 dattr8,
558 fdtl.doc_attribute9 dattr9, fdtl.doc_attribute10 dattr10,
559 fdtl.doc_attribute11 dattr11, fdtl.doc_attribute12 dattr12,
560 fdtl.doc_attribute13 dattr13, fdtl.doc_attribute14 dattr14,
561 fdtl.doc_attribute15 dattr15, fd.url, fdtl.title, fd.dm_node,
562 fd.dm_folder_path,fd.dm_type, fd.dm_document_id, fd.dm_version_number
563 FROM fnd_attached_documents fad,
564 fnd_documents fd,
565 fnd_documents_tl fdtl
566 WHERE fad.document_id = fd.document_id
567 AND fd.document_id = fdtl.document_id
568 AND fdtl.language = userenv('LANG')
569 AND fad.entity_name = X_from_entity_name
570 AND fad.pk1_value = X_from_pk1_value
571 AND fad.pk2_value = X_from_pk2_value
572 AND (X_from_category_id IS NULL
573 OR (fad.category_id = X_from_category_id
574 OR (fad.category_id is NULL AND fd.category_id = X_from_category_id)))
575 AND fad.automatically_added_flag like decode(X_automatically_added_flag,NULL,'%',X_automatically_added_flag)
576 AND (X_orig_attach_doc_id is NULL OR fad.attached_document_id = X_orig_attach_doc_id);
577
578
579
580
581 CURSOR docpk3 IS
582 SELECT fad.seq_num, fad.document_id, fad.attached_document_id,
583 fad.attribute_category, fad.attribute1, fad.attribute2,
584 fad.attribute3, fad.attribute4, fad.attribute5,
585 fad.attribute6, fad.attribute7, fad.attribute8,
586 fad.attribute9, fad.attribute10, fad.attribute11,
587 fad.attribute12, fad.attribute13, fad.attribute14,
588 fad.attribute15, fad.column1, fad.automatically_added_flag,
589 fad.category_id att_cat, fad.pk2_value, fad.pk3_value,
590 fad.pk4_value, fad.pk5_value,
591 fd.datatype_id, fd.category_id, fd.security_type, fd.security_id,
592 fd.publish_flag, fd.image_type, fd.storage_type,
593 fd.usage_type, fd.start_date_active, fd.end_date_active,
594 fdtl.language, fdtl.description, fd.file_name,
595 fd.media_id, fdtl.doc_attribute_category dattr_cat,
596 fdtl.doc_attribute1 dattr1, fdtl.doc_attribute2 dattr2,
597 fdtl.doc_attribute3 dattr3, fdtl.doc_attribute4 dattr4,
598 fdtl.doc_attribute5 dattr5, fdtl.doc_attribute6 dattr6,
599 fdtl.doc_attribute7 dattr7, fdtl.doc_attribute8 dattr8,
600 fdtl.doc_attribute9 dattr9, fdtl.doc_attribute10 dattr10,
601 fdtl.doc_attribute11 dattr11, fdtl.doc_attribute12 dattr12,
602 fdtl.doc_attribute13 dattr13, fdtl.doc_attribute14 dattr14,
603 fdtl.doc_attribute15 dattr15, fd.url, fdtl.title, fd.dm_node,
604 fd.dm_folder_path,fd.dm_type, fd.dm_document_id, fd.dm_version_number
605 FROM fnd_attached_documents fad,
606 fnd_documents fd,
607 fnd_documents_tl fdtl
608 WHERE fad.document_id = fd.document_id
609 AND fd.document_id = fdtl.document_id
610 AND fdtl.language = userenv('LANG')
611 AND fad.entity_name = X_from_entity_name
612 AND fad.pk1_value = X_from_pk1_value
613 AND fad.pk2_value = X_from_pk2_value
614 AND fad.pk3_value = X_from_pk3_value
615 AND (X_from_category_id IS NULL
616 OR (fad.category_id = X_from_category_id
617 OR (fad.category_id is NULL AND fd.category_id = X_from_category_id)))
618 AND fad.automatically_added_flag like decode(X_automatically_added_flag,NULL,'%',X_automatically_added_flag)
619 AND (X_orig_attach_doc_id is NULL OR fad.attached_document_id = X_orig_attach_doc_id);
620
621
622
623
624 CURSOR doclist IS
625 SELECT fad.seq_num, fad.document_id, fad.attached_document_id,
626 fad.attribute_category, fad.attribute1, fad.attribute2,
627 fad.attribute3, fad.attribute4, fad.attribute5,
628 fad.attribute6, fad.attribute7, fad.attribute8,
629 fad.attribute9, fad.attribute10, fad.attribute11,
630 fad.attribute12, fad.attribute13, fad.attribute14,
631 fad.attribute15, fad.column1, fad.automatically_added_flag,
635 fd.publish_flag, fd.image_type, fd.storage_type,
632 fad.category_id att_cat, fad.pk2_value, fad.pk3_value,
633 fad.pk4_value, fad.pk5_value,
634 fd.datatype_id, fd.category_id, fd.security_type, fd.security_id,
636 fd.usage_type, fd.start_date_active, fd.end_date_active,
637 fdtl.language, fdtl.description, fd.file_name,
638 fd.media_id, fdtl.doc_attribute_category dattr_cat,
639 fdtl.doc_attribute1 dattr1, fdtl.doc_attribute2 dattr2,
640 fdtl.doc_attribute3 dattr3, fdtl.doc_attribute4 dattr4,
641 fdtl.doc_attribute5 dattr5, fdtl.doc_attribute6 dattr6,
642 fdtl.doc_attribute7 dattr7, fdtl.doc_attribute8 dattr8,
643 fdtl.doc_attribute9 dattr9, fdtl.doc_attribute10 dattr10,
644 fdtl.doc_attribute11 dattr11, fdtl.doc_attribute12 dattr12,
645 fdtl.doc_attribute13 dattr13, fdtl.doc_attribute14 dattr14,
646 fdtl.doc_attribute15 dattr15, fd.url, fdtl.title, fd.dm_node,
647 fd.dm_folder_path,fd.dm_type, fd.dm_document_id, fd.dm_version_number
648 FROM fnd_attached_documents fad,
649 fnd_documents fd,
650 fnd_documents_tl fdtl
651 WHERE fad.document_id = fd.document_id
652 AND fd.document_id = fdtl.document_id
653 AND fdtl.language = userenv('LANG')
654 AND fad.entity_name = X_from_entity_name
655 AND fad.pk1_value = X_from_pk1_value
656 AND (X_from_pk2_value IS NULL
657 OR fad.pk2_value = X_from_pk2_value)
658 AND (X_from_pk3_value IS NULL
659 OR fad.pk3_value = X_from_pk3_value)
660 AND (X_from_pk4_value IS NULL
661 OR fad.pk4_value = X_from_pk4_value)
662 AND (X_from_pk5_value IS NULL
663 OR fad.pk5_value = X_from_pk5_value)
664 AND (X_from_category_id IS NULL
665 OR (fad.category_id = X_from_category_id
666 OR (fad.category_id is NULL AND fd.category_id = X_from_category_id)))
667 AND fad.automatically_added_flag like decode(X_automatically_added_flag,NULL,'%',X_automatically_added_flag)
668 AND (X_orig_attach_doc_id is NULL OR fad.attached_document_id = X_orig_attach_doc_id);
669
670
671
672 CURSOR shorttext (mid NUMBER) IS
673 SELECT short_text
674 FROM fnd_documents_short_text
675 WHERE media_id = mid;
676
677 CURSOR longtext (mid NUMBER) IS
678 SELECT long_text
679 FROM fnd_documents_long_text
680 WHERE media_id = mid;
681
682 CURSOR fnd_lobs_cur (mid NUMBER) IS
683 SELECT file_id,
684 file_name,
685 file_content_type,
686 upload_date,
687 expiration_date,
688 program_name,
689 program_tag,
690 file_data,
691 language,
692 oracle_charset,
693 file_format
694 FROM fnd_lobs
695 WHERE file_id = mid;
696
697 media_id_tmp NUMBER;
698 document_id_tmp NUMBER;
699 row_id_tmp VARCHAR2(30);
700 short_text_tmp VARCHAR2(4000);
701 --long_text_tmp VARCHAR2(32767);
702 long_text_tmp fnd_documents_long_text.long_text%TYPE;
703 docrec doclist%ROWTYPE;
704 fnd_lobs_rec fnd_lobs_cur%ROWTYPE;
705 BEGIN
706 -- Use cursor loop to get all attachments associated with
707 -- the from_entity
708 IF (X_from_entity_name IS NULL OR X_from_pk1_value IS NULL) THEN
709 RETURN;
710 END IF;
711
712 IF X_from_pk2_value IS NULL THEN -- performance change IF
713 OPEN docpk1;
714 ELSIF X_from_pk3_value IS NULL THEN
715 OPEN docpk2;
716 ELSIF X_from_pk4_value IS NULL THEN
717 OPEN docpk3;
718 ELSE
719 OPEN doclist;
720 END IF;
721
722 <<pkloop>>
723 LOOP
724
725 IF X_from_pk2_value IS NULL THEN -- performance change IF
726 FETCH docpk1 INTO docrec;
727 --EXIT
728 IF (docpk1%notfound) then
729 EXIT pkloop;
730 END IF;
731 ELSIF X_from_pk3_value IS NULL THEN
732 FETCH docpk2 INTO docrec;
733 IF (docpk2%notfound) then
734 EXIT pkloop;
735 END IF;
736 ELSIF X_from_pk4_value IS NULL THEN
737 FETCH docpk3 INTO docrec;
738 IF (docpk3%notfound) then
739 EXIT pkloop;
740 END IF;
741 ELSE
742 FETCH doclist INTO docrec;
743 IF (doclist%notfound) then
744 EXIT pkloop;
745 END IF;
746 END IF;
747
748 --FOR docrec IN doclist LOOP
749 -- One-Time docs that Short Text or Long Text will have
750 -- to be copied into a new document (Long Text will be
751 -- truncated to 32K). Create the new document records
752 -- before creating the attachment record
753 --
754 IF (docrec.usage_type = 'O'
755 AND docrec.datatype_id IN (1,2,5,6) ) THEN
756 -- Create Documents records
757 FND_DOCUMENTS_PKG.Insert_Row(row_id_tmp,
758 document_id_tmp,
759 SYSDATE,
760 NVL(X_created_by,0),
761 SYSDATE,
762 NVL(X_created_by,0),
763 X_last_update_login,
764 docrec.datatype_id,
765 NVL(X_to_category_id, docrec.category_id),
766 docrec.security_type,
770 docrec.storage_type,
767 docrec.security_id,
768 docrec.publish_flag,
769 docrec.image_type,
771 docrec.usage_type,
772 docrec.start_date_active,
773 docrec.end_date_active,
774 X_request_id,
775 X_program_application_id,
776 X_program_id,
777 SYSDATE,
778 docrec.language,
779 docrec.description,
780 docrec.file_name,
781 media_id_tmp,
782 docrec.dattr_cat, docrec.dattr1,
783 docrec.dattr2, docrec.dattr3,
784 docrec.dattr4, docrec.dattr5,
785 docrec.dattr6, docrec.dattr7,
786 docrec.dattr8, docrec.dattr9,
787 docrec.dattr10, docrec.dattr11,
788 docrec.dattr12, docrec.dattr13,
789 docrec.dattr14, docrec.dattr15,
790 'N',docrec.url, docrec.title,
791 docrec.dm_node, docrec.dm_folder_path,
792 docrec.dm_type, docrec.dm_document_id,
793 docrec.dm_version_number,
794 docrec.document_id);
795
796 -- overwrite document_id from original
797 -- cursor for later insert into
798 -- fnd_attached_documents
799 docrec.document_id := document_id_tmp;
800
801 -- Duplicate short or long text
802 IF (docrec.datatype_id = 1) THEN
803 -- Handle short Text
804 -- get original data
805 OPEN shorttext(docrec.media_id);
806 FETCH shorttext INTO short_text_tmp;
807 CLOSE shorttext;
808
809 INSERT INTO fnd_documents_short_text (
810 media_id,
811 short_text)
812 VALUES (
813 media_id_tmp,
814 short_text_tmp);
815 media_id_tmp := '';
816
817 ELSIF (docrec.datatype_id = 2) THEN
818 -- Handle long text
819 -- get original data
820 OPEN longtext(docrec.media_id);
821 FETCH longtext INTO long_text_tmp;
822 CLOSE longtext;
823
824 INSERT INTO fnd_documents_long_text (
825 media_id,
826 long_text)
827 VALUES (
828 media_id_tmp,
829 long_text_tmp);
830 media_id_tmp := '';
831
832 ELSIF (docrec.datatype_id=6) THEN
833
834 OPEN fnd_lobs_cur(docrec.media_id);
835 FETCH fnd_lobs_cur
836 INTO fnd_lobs_rec.file_id,
837 fnd_lobs_rec.file_name,
838 fnd_lobs_rec.file_content_type,
839 fnd_lobs_rec.upload_date,
840 fnd_lobs_rec.expiration_date,
841 fnd_lobs_rec.program_name,
842 fnd_lobs_rec.program_tag,
843 fnd_lobs_rec.file_data,
844 fnd_lobs_rec.language,
845 fnd_lobs_rec.oracle_charset,
846 fnd_lobs_rec.file_format;
847 CLOSE fnd_lobs_cur;
848
849 INSERT INTO fnd_lobs (
850 file_id,
851 file_name,
852 file_content_type,
853 upload_date,
854 expiration_date,
855 program_name,
856 program_tag,
857 file_data,
858 language,
859 oracle_charset,
860 file_format)
861 VALUES (
862 media_id_tmp,
863 fnd_lobs_rec.file_name,
864 fnd_lobs_rec.file_content_type,
865 fnd_lobs_rec.upload_date,
866 fnd_lobs_rec.expiration_date,
867 fnd_lobs_rec.program_name,
868 fnd_lobs_rec.program_tag,
869 fnd_lobs_rec.file_data,
870 fnd_lobs_rec.language,
871 fnd_lobs_rec.oracle_charset,
872 fnd_lobs_rec.file_format);
873
874 media_id_tmp := '';
875
876 END IF; -- end of duplicating text
877
878
879 END IF; -- end if usage_type = 'O' and datatype in (1,2,6)
880
881 -- Create attachment record
882 INSERT INTO fnd_attached_documents
883 (attached_document_id,
884 document_id,
885 creation_date,
886 created_by,
887 last_update_date,
888 last_updated_by,
889 last_update_login,
890 seq_num,
891 entity_name,
892 pk1_value, pk2_value, pk3_value,
893 pk4_value, pk5_value,
894 automatically_added_flag,
895 program_application_id, program_id,
896 program_update_date, request_id,
897 attribute_category, attribute1,
898 attribute2, attribute3, attribute4,
899 attribute5, attribute6, attribute7,
900 attribute8, attribute9, attribute10,
901 attribute11, attribute12, attribute13,
902 attribute14, attribute15, column1, category_id, orig_attach_doc_id) VALUES
903 (fnd_attached_documents_s.nextval,
904 docrec.document_id,
905 sysdate,
906 NVL(X_created_by,0),
907 sysdate,
908 NVL(X_created_by,0),
909 X_last_update_login,
910 docrec.seq_num,
911 X_to_entity_name,
912 X_to_pk1_value,
913 X_to_pk2_value,
914 X_to_pk3_value,
915 X_to_pk4_value,
916 X_to_pk5_value,
917 docrec.automatically_added_flag,
918 X_program_application_id, X_program_id,
919 sysdate, X_request_id,
920 docrec.attribute_category, docrec.attribute1,
921 docrec.attribute2, docrec.attribute3,
922 docrec.attribute4, docrec.attribute5,
923 docrec.attribute6, docrec.attribute7,
924 docrec.attribute8, docrec.attribute9,
925 docrec.attribute10, docrec.attribute11,
926 docrec.attribute12, docrec.attribute13,
927 docrec.attribute14, docrec.attribute15,
928 docrec.column1,
929 NVL(X_to_category_id, NVL(docrec.att_cat, docrec.category_id)),
930 docrec.attached_document_id);
931
932 -- Update the document to be a std document if it
933 -- was an ole or image that wasn't already a std doc
934 -- (images should be created as Std, but just in case)
935 IF (docrec.datatype_id IN (3,4)
936 AND docrec.usage_type <> 'S') THEN
937 UPDATE fnd_documents
938 SET usage_type = 'S'
939 WHERE document_id = docrec.document_id;
940 END IF;
941
942 END LOOP; -- end of working through all attachments
943 -- close cursors.
944 IF X_from_pk2_value IS NULL THEN -- performance change IF
945 CLOSE docpk1;
946 ELSIF X_from_pk3_value IS NULL THEN
947 CLOSE docpk2;
948 ELSIF X_from_pk4_value IS NULL THEN
949 CLOSE docpk3;
950 ELSE
951 CLOSE doclist;
952 END IF;
953
954 EXCEPTION WHEN OTHERS THEN
955 -- need to close all cursors
956 CLOSE docpk1;
957 CLOSE docpk2;
958 CLOSE docpk3;
959 CLOSE doclist;
960 CLOSE shorttext;
961 CLOSE longtext;
962 CLOSE fnd_lobs_cur;
963
964 END copy_attachments;
965
966
967 END fnd_attached_documents2_pkg;
[Home] [Help]