[Home] [Help]
PACKAGE BODY: APPS.DOM_ATTACHMENT_UTIL_PKG
Source
1 PACKAGE BODY DOM_ATTACHMENT_UTIL_PKG AS
2 /* $Header: DOMAUTLB.pls 120.13 2006/09/20 19:03:30 sabatra noship $ */
3 /*---------------------------------------------------------------------------+
4 | This package contains public APIs for Attachments functionality |
5 +---------------------------------------------------------------------------*/
6
7 G_WEBSERVICES VARCHAR2(80) := 'WEBSERVICES';
8 ----------------------------------------------------------------
9 --Private: sync_dom_file_ext
10 ----------------------------------------------------------------
11 procedure sync_dom_file_ext
12 (
13 p_repository_id IN number
14 ,p_version_id IN number
15 ,p_status IN varchar2
16 )
17 IS
18
19 BEGIN
20 update dom_file_ext
21 set status =p_status
22 where repository_id=p_repository_id
23 and version_id=p_version_id;
24 IF ( SQL%NOTFOUND ) THEN
25 insert into dom_file_ext
26 (
27 repository_id
28 ,version_id
29 ,status
30 )
31 values
32 (
33 p_repository_id
34 ,p_version_id
35 ,p_status
36 );
37 END IF;
38
39 END sync_dom_file_ext;
40 ----------------------------------------------------------------------
41
42
43 Procedure Attach (
44 p_Document_id IN Number
45 , p_Entity_name IN Varchar2
46 , p_Pk1_value IN Varchar2
47 , p_Pk2_value IN Varchar2 DEFAULT NULL
48 , p_Pk3_value IN Varchar2 DEFAULT NULL
49 , p_Pk4_value IN Varchar2 DEFAULT NULL
50 , p_Pk5_value IN Varchar2 DEFAULT NULL
51 , p_category_id IN Number
52 , p_created_by IN Number
53 , p_last_update_login IN Number DEFAULT NULL
54 , x_Attached_document_id OUT NOCOPY Number
55 )
56 IS
57
58
59 l_document_id number;
60 l_row_id VARCHAR2(2000);
61 l_category_id NUMBER;
62 l_created_by NUMBER;
63 l_entity_name VARCHAR2(40);
64 l_pk1_value VARCHAR2(100);
65 l_pk2_value VARCHAR2(100);
66 l_pk3_value VARCHAR2(100);
67 l_pk4_value VARCHAR2(100);
68 l_pk5_value VARCHAR2(100);
69 l_last_update_login NUMBER;
70 l_media_id NUMBER;
71 l_attached_document_id NUMBER;
72 BEGIN
73 l_document_id:=p_Document_id;
74
75
76 -- SBAG: When Attach action is called, the attached_document_id
77 -- will be null. So the id is created out of the sequence number
78 -- to pass to fnd_attached_documents_pkg.insert_row api.
79
80 select fnd_attached_documents_s.nextval
81 into l_attached_document_id from dual;
82
83 fnd_attached_documents_pkg.Insert_Row(
84 X_Rowid => l_row_id,
85 X_attached_document_id => l_Attached_document_id,
86 X_document_id => l_document_id,
87 X_creation_date => sysdate,
88 X_created_by => p_created_by,
89 X_last_update_date => sysdate,
90 X_last_updated_by => p_created_by,
91 X_last_update_login => p_last_update_login,
92 X_seq_num => 1,
93 X_entity_name => p_entity_name,
94 X_column1 => null,
95 X_pk1_value => p_pk1_value,
96 X_pk2_value => p_pk2_value,
97 X_pk3_value => p_pk3_value,
98 X_pk4_value => p_pk4_value,
99 X_pk5_value => p_pk5_value,
100 X_automatically_added_flag => 'N',
101 X_datatype_id => null,
102 X_category_id => p_category_id,
103 X_security_type => null,
104 X_publish_flag => null,
105 X_usage_type => null,
106 X_language => null,
107 X_media_id => l_media_id,
108 X_doc_attribute_Category => null,
109 X_doc_attribute1 => null,
110 X_doc_attribute2 => null,
111 X_doc_attribute3 => null,
112 X_doc_attribute4 => null,
113 X_doc_attribute5 => null,
114 X_doc_attribute6 => null,
115 X_doc_attribute7 => null,
116 X_doc_attribute8 => null,
117 X_doc_attribute9 => null,
118 X_doc_attribute10 => null,
119 X_doc_attribute11 => null,
120 X_doc_attribute12 => null,
121 X_doc_attribute13 => null,
122 X_doc_attribute14 => null,
123 X_doc_attribute15 => null,
124 X_create_doc => 'N' -- Fix for 3762710
125 );
126 x_Attached_document_id:=l_Attached_document_id;
127
128 END Attach;
129 ----------------------------------------------------------------------
130
131 Procedure Create_Attachment (
132 p_Document_id IN Number
133 , p_Entity_name IN Varchar2
134 , p_Pk1_value IN Varchar2
135 , p_Pk2_value IN Varchar2 DEFAULT NULL
136 , p_Pk3_value IN Varchar2 DEFAULT NULL
137 , p_Pk4_value IN Varchar2 DEFAULT NULL
138 , p_Pk5_value IN Varchar2 DEFAULT NULL
139 , p_category_id IN Number
140 , p_repository_id IN NUMBER
141 , p_version_id IN NUMBER
142 , p_family_id IN NUMBER
143 , p_file_name IN VARCHAR2
144 , p_created_by IN NUMBER
145 , p_last_update_login IN NUMBER DEFAULT NULL
146 , x_Attached_document_id OUT NOCOPY NUMBER
147 )
148 IS
149
150
151 l_document_id number;
152 l_row_id VARCHAR2(2000);
153 l_category_id NUMBER;
154 l_created_by NUMBER;
155 l_entity_name VARCHAR2(40);
156 l_pk1_value VARCHAR2(100);
157 l_pk2_value VARCHAR2(100);
158 l_pk3_value VARCHAR2(100);
159 l_pk4_value VARCHAR2(100);
160 l_pk5_value VARCHAR2(100);
161 l_last_update_login NUMBER;
162 l_media_id NUMBER;
163 l_attached_document_id NUMBER;
164 l_language VARCHAR2(10);
165 l_dm_document_id NUMBER;
166 BEGIN
167 l_document_id:=p_Document_id;
168
169
170 -- SBAG: When Attach action is called, the attached_document_id
171 -- will be null. So the id is created out of the sequence number
172 -- to pass to fnd_attached_documents_pkg.insert_row api.
173
174 select fnd_attached_documents_s.nextval
175 into l_attached_document_id from dual;
176
177 if ( l_document_id is null ) THEN
178
179 SELECT fnd_documents_s.nextval
180 INTO l_document_id
181 FROM dual;
182
183 l_category_id := p_category_id;
184 l_media_id := p_family_id;
185
186 INSERT INTO fnd_documents(
187 document_id,
188 creation_date,
189 created_by,
190 last_update_date,
191 last_updated_by,
192 last_update_login,
193 datatype_id,
194 category_id,
195 security_type,
196 publish_flag,
197 usage_type,
198 media_id,
199 dm_document_id,
200 file_name,
201 dm_node,
202 dm_type)
203 VALUES (
204 l_document_id,
205 sysdate,
206 p_created_by,
210 8,
207 sysdate,
208 p_created_by,
209 p_last_update_login,
211 l_category_id,
212 4,
213 'Y',
214 0,
215 p_family_id,
216 p_version_id,
217 p_file_name,
218 p_repository_id,
219 'FILE');
220
221 SELECT userenv('LANG') INTO l_language FROM dual;
222
223 fnd_documents_pkg.insert_tl_row(
224 X_document_id => l_document_id,
225 X_creation_date => sysdate,
226 X_created_by => p_created_by,
227 X_last_update_date => sysdate,
228 X_last_updated_by => p_created_by,
229 X_last_update_login => p_last_update_login,
230 X_language => l_language
231 );
232
233 else
234
235 INSERT INTO fnd_documents
236 (document_id,
237 creation_date,
238 created_by,
239 last_update_date,
240 last_updated_by,
241 last_update_login,
242 datatype_id,
243 category_id,
244 security_type,
245 publish_flag,
246 usage_type,
247 media_id,
248 dm_document_id,
249 file_name,
250 dm_node,
251 dm_type)
252 SELECT
253 fnd_documents_s.NEXTVAL,
254 creation_date,
255 created_by,
256 last_update_date,
257 last_updated_by,
258 last_update_login,
259 datatype_id,
260 category_id,
261 security_type,
262 publish_flag,
263 usage_type,
264 media_id,
265 decode(dm_document_id,0,p_version_id,dm_document_id),
266 file_name,
267 dm_node,
268 dm_type
269 FROM fnd_documents x
270 WHERE x.document_id = l_document_id;
271
272 select media_id, category_id, userenv('LANG') into l_media_id, l_category_id, l_language from fnd_documents where document_id = l_document_id;
273
274 select fnd_documents_s.CURRVAL into l_document_id from dual;
275
276 fnd_documents_pkg.insert_tl_row(
277 X_document_id => l_document_id,
278 X_creation_date => sysdate,
279 X_created_by => p_created_by,
280 X_last_update_date => sysdate,
281 X_last_updated_by => p_created_by,
282 X_last_update_login => p_last_update_login,
283 X_language => l_language
284 );
285
286
287
288
289 end if;
290
291 fnd_attached_documents_pkg.Insert_Row(
292 X_Rowid => l_row_id,
293 X_attached_document_id => l_Attached_document_id,
294 X_document_id => l_document_id,
295 X_creation_date => sysdate,
296 X_created_by => p_created_by,
297 X_last_update_date => sysdate,
298 X_last_updated_by => p_created_by,
299 X_last_update_login => p_last_update_login,
300 X_seq_num => 1,
301 X_entity_name => p_entity_name,
302 X_column1 => null,
303 X_pk1_value => p_pk1_value,
304 X_pk2_value => p_pk2_value,
305 X_pk3_value => p_pk3_value,
306 X_pk4_value => p_pk4_value,
307 X_pk5_value => p_pk5_value,
308 X_automatically_added_flag => 'N',
309 X_datatype_id => null,
310 X_category_id => l_category_id,
311 X_security_type => null,
312 X_publish_flag => null,
313 X_usage_type => null,
314 X_language => null,
315 X_media_id => l_media_id,
316 X_doc_attribute_Category => null,
317 X_doc_attribute1 => null,
318 X_doc_attribute2 => null,
319 X_doc_attribute3 => null,
320 X_doc_attribute4 => null,
321 X_doc_attribute5 => null,
322 X_doc_attribute6 => null,
323 X_doc_attribute7 => null,
324 X_doc_attribute8 => null,
325 X_doc_attribute9 => null,
326 X_doc_attribute10 => null,
327 X_doc_attribute11 => null,
328 X_doc_attribute12 => null,
329 X_doc_attribute13 => null,
330 X_doc_attribute14 => null,
331 X_doc_attribute15 => null,
332 X_create_doc => 'N' -- Fix for 3762710
333 );
334
335 UPDATE fnd_attached_documents set category_id = l_category_id where attached_document_id = l_Attached_document_id;
336
337 x_Attached_document_id:=l_Attached_document_id;
338
339 END Create_Attachment;
340 ----------------------------------------------------------------------
341
342 Procedure Detach(
343 p_Attached_document_id IN Number
344 )
345 IS
346 cursor get_document_id
347 is
348 select att.document_id,docs.datatype_id
349 from fnd_attached_documents att,
350 fnd_documents docs
351 where Attached_document_id=p_Attached_document_id
352 and docs.document_id=att.document_id;
353
354
355 cursor check_other_attachments (cp_document_id number)
356 IS
357 SELECT attached_document_id
358 FROM fnd_attached_documents
359 where document_id=cp_document_id;
360
361
362 l_document_id number;
363 l_dummy NUMBER;
364 l_datatype_id number;
365
366 BEGIN
367 open get_document_id;
368 fetch get_document_id INTO
369 l_document_id,l_datatype_id;
370 CLOSE get_document_id;
371
375 open check_other_attachments (l_document_id);
372 delete fnd_attached_documents
373 where attached_document_id = p_Attached_document_id;
374
376 fetch check_other_attachments INTO l_dummy;
377 CLOSE check_other_attachments;
378 if( l_dummy is null) THEN
379 fnd_documents_pkg.Delete_Row(
380 X_document_id =>l_document_id,
381 X_datatype_id =>l_datatype_id,
382 delete_ref_Flag => 'Y');
383 END if;
384
385
386 END Detach;
387 ----------------------------------------------------------------------
388
389
390
391 /* This will be called after the MODIFY action */
392 Procedure Update_Document(
393 p_Attached_document_id IN Number
394 , p_FileName IN Varchar2
395 , p_Description IN Varchar2 DEFAULT NULL
396 , p_Category IN Number
397 , p_last_updated_by IN Number
398 , p_last_update_login IN Number DEFAULT NULL
399 )
400 IS
401
402 cursor get_document_id(cp_Attached_document_id number)
403 is
404 select document_id
405 from fnd_attached_documents
406 where Attached_document_id=p_Attached_document_id;
407 l_document_id number;
408
409 BEGIN
410
411 open get_document_id (p_Attached_document_id);
412 fetch get_document_id INTO
413 l_document_id;
414 CLOSE get_document_id;
415
416
417
418 update fnd_documents_tl
419 set Description = p_Description
420 ,last_updated_by = p_last_updated_by
421 ,last_update_login =p_last_update_login
422 where document_id= l_document_id
423 and language=userenv('LANG');
424
425 update fnd_documents
426 set File_Name = p_FileName
427 ,Category_id= p_Category
428 ,last_updated_by = p_last_updated_by
429 ,last_update_login =p_last_update_login
430 where document_id= l_document_id;
431
432 update fnd_attached_documents
433 set Category_id= p_Category
434 ,last_updated_by = p_last_updated_by
435 ,last_update_login =p_last_update_login
436 where Attached_document_id=p_Attached_document_id;
437
438 END Update_Document;
439 ----------------------------------------------------------------------
440
441
442 /* To be called for change attach version */
443 Procedure Change_Version(
444 p_Attached_document_id IN Number
445 , p_Document_id IN Number
446 , p_last_updated_by IN Number
447 , p_last_update_login IN Number DEFAULT NULL
448 )
449
450 IS
451
452 BEGIN
453 UPDATE fnd_attached_documents
454 SET document_id = p_Document_id,
455 last_update_date = sysdate,
456 last_updated_by = p_last_updated_by,
457 last_update_login = p_last_update_login
458 WHERE attached_document_id = p_Attached_document_id;
459 END Change_Version;
460 ----------------------------------------------------------------------
461
462 /* This procedure is after approval / review to change fnd document
463 status */
464 Procedure Change_Status(
465 p_Attached_document_id IN Number
466 , p_Document_id IN Number
467 , p_Repository_id IN Number
468 , p_Status IN Varchar2
469 , p_submitted_by IN Number
470 , p_last_updated_by IN Number
471 , p_last_update_login IN Number DEFAULT NULL
472 )
473 IS
474
475 l_service_url VARCHAR2(255);
476 l_return_status VARCHAR2(10);
477 l_msg_count NUMBER;
478 l_msg_data VARCHAR2(2000);
479 l_user_name VARCHAR2(255);
480 l_Repository_id NUMBER;
481 l_repos_type VARCHAR(80);
482
483 cursor get_serivce_url(cp_Repository_id number)
484 IS
485 select service_url
486 from dom_repositories
487 where id=cp_Repository_id;
488
489 cursor get_user_name(cp_user_id number)
490 IS
491 select user_name
492 from fnd_user
493 where user_id=cp_user_id;
494
495 CURSOR get_affected_attachments(cp_dm_document_id NUMBER)
496 IS
497 select att.attached_document_id
498 from fnd_attached_documents att,
499 fnd_documents docs
500 where docs.document_id=att.document_id
501 and docs.dm_document_id=cp_dm_document_id;
502
503 CURSOR is_WebServices_document(cp_Repository_id NUMBER)
504 IS
505 SELECT n.protocol
506 from dom_repositories n
507 where n.id=cp_Repository_id;
508
509
510 BEGIN
511 l_Repository_id:=p_Repository_id;
512 IF(p_Repository_id is null) THEN
513 l_Repository_id:=0;
514 END IF;
515
516 OPEN is_WebServices_document(l_Repository_id);
517 FETCH is_WebServices_document into l_repos_type;
518 CLOSE is_WebServices_document;
519 --dbms_output.put_line('Repos type='||l_repos_type);
520 IF(l_repos_type =G_WEBSERVICES AND p_Document_id is NOT null ) THEN
521
522 sync_dom_file_ext(p_Repository_id,p_Document_id,p_Status);
523
524 /*
525 open get_user_name(p_submitted_by);
526 fetch get_user_name into l_user_name;
527 close get_user_name;
528
529 open get_serivce_url(p_Repository_id);
530 fetch get_serivce_url into l_service_url;
531 close get_serivce_url;
532 --call jsp with p_Document_id,p_Status, service url, p_submitted_by
533 DOM_WS_INTERFACE_PUB.Update_Files_Document_Status
534 (
535 p_api_version => 1.0,
536 p_service_url => l_service_url,
537 p_document_id => p_Document_id,
538 p_status => p_Status,
539 p_login_user_name => l_user_name,
540 x_return_status => l_return_status,
541 x_msg_count => l_msg_count,
542 x_msg_data => l_msg_data
543 );
544 */
545
546 --Update status for all the attached documents for the WS document.
547 FOR rec in get_affected_attachments(p_Document_id) LOOP
548 --dbms_output.put_line('Updating attachment id= '||rec.attached_document_id);
549 UPDATE fnd_attached_documents
550 SET status = p_Status,
551 last_update_date = sysdate,
552 last_updated_by = p_submitted_by,
553 last_update_login = p_last_update_login
554 WHERE attached_document_id = rec.attached_document_id;
555 END LOOP;
556 END IF;
557
558 IF( p_Attached_document_id IS NOT NULL) THEN
559 --Update status for Non-WebServices document
560 UPDATE fnd_attached_documents
561 SET status = p_Status,
562 last_update_date = sysdate,
563 last_updated_by = p_submitted_by,
564 last_update_login = p_last_update_login
565 WHERE attached_document_id = p_Attached_document_id;
566 END iF;
567
568 --EXCEPTION
569 --WHEN OTHERS THEN
570
571 END Change_Status;
572 ----------------------------------------------------------------------
573 FUNCTION get_repos_doc_view_url
574 (
575 p_document_id IN NUMBER
576 )RETURN VARCHAR2
577 IS
578 l_document_access_url VARCHAR2(800);
579 l_document_path VARCHAR2(500);
580 l_host_url VARCHAR2(200);
581 l_media_id NUMBER;
582 l_datatype_id NUMBER;
583 l_encrypt_key varchar2(30):='DM$Param'||'&'||'Key';
584 cursor get_doc_info (cp_document_id number)
585 IS
586 select media_id,datatype_id
587 from fnd_documents_vl
588 where document_id=cp_document_id;
589
590 BEGIN
591
592 OPEN get_doc_info (p_document_id);
593 fetch get_doc_info INTO l_media_id,l_datatype_id;
594 close get_doc_info;
595
596 if(l_datatype_id =8 OR l_datatype_id =9) THEN
597 l_document_access_url:=fnd_profile.value('APPS_FRAMEWORK_AGENT')||'/OA.jsp?page=/oracle/apps/dom/dmrepository/webui/DomSelfSecuredRenderPG'||'&'||'retainAM=Y';
598 l_document_access_url:=l_document_access_url||'&'||'DOM_PARAM_DOCUMENT_ID='||fnd_web_sec.encrypt(l_encrypt_key,p_document_id);
599 l_document_access_url:=l_document_access_url||'&'||'DOM_PARAM_MEDIA_ID='||fnd_web_sec.encrypt(l_encrypt_key,l_media_id);
600 l_document_access_url:=l_document_access_url||'&'||'DOM_PARAM_DATATYPE_ID='||fnd_web_sec.encrypt(l_encrypt_key,l_datatype_id);
601 return l_document_access_url;
602 END IF;
603
604 IF(l_datatype_id =7) THEN
605 SELECT repos.dav_url,
606 docs.dm_folder_path||'/'||docs.file_name
607 INTO l_host_url,l_document_path
608 FROM fnd_documents_vl docs,
609 dom_repositories repos
610 where repos.id=docs.dm_node
611 and docs.document_id=p_document_id;
612
613 l_document_path:=wfa_html.conv_special_url_chars(l_document_path);
614 l_document_path:=replace(l_document_path,'%2F','/');
615 IF(substr(l_document_path,0,1) <> '/') THEN
616 l_document_path:='/'||l_document_path;
617 END IF;
618
619 l_document_access_url:=l_host_url||l_document_path;
620 RETURN l_document_access_url;
621 END IF;
622
623 END get_repos_doc_view_url;
624 -------------------------------------------------------------------
625
626 PROCEDURE delete_attachments(X_entity_name IN VARCHAR2,
627 X_pk1_value IN VARCHAR2,
628 X_pk2_value IN VARCHAR2 DEFAULT NULL,
629 X_pk3_value IN VARCHAR2 DEFAULT NULL,
630 X_pk4_value IN VARCHAR2 DEFAULT NULL,
631 X_pk5_value IN VARCHAR2 DEFAULT NULL,
632 X_delete_document_flag IN VARCHAR2,
633 X_automatically_added_flag IN VARCHAR2 DEFAULT NULL) IS
634 l_delete_document_flag varchar2(1);
635 BEGIN
636 fnd_attached_documents2_pkg.delete_attachments(X_entity_name,
637 X_pk1_value,
638 X_pk2_value,
639 X_pk3_value,
640 X_pk4_value,
641 X_pk5_value,
642 X_delete_document_flag,
643 X_automatically_added_flag);
644
645 END delete_attachments;
646
647
648
649
650 PROCEDURE copy_documents(X_from_document_id IN OUT NOCOPY NUMBER,
651 X_created_by IN NUMBER DEFAULT NULL,
652 X_last_update_login IN NUMBER DEFAULT NULL,
653 X_program_application_id IN NUMBER DEFAULT NULL,
654 X_program_id IN NUMBER DEFAULT NULL,
655 X_request_id IN NUMBER DEFAULT NULL,
656 X_automatically_added_flag IN VARCHAR2 DEFAULT NULL,
657 X_from_category_id IN NUMBER DEFAULT NULL,
658 X_to_category_id IN NUMBER DEFAULT NULL) IS
659 l_delete_document_flag varchar2(1);
660 CURSOR docpk1 IS
661 SELECT fad.seq_num, fad.document_id,
662 fad.attribute_category, fad.attribute1, fad.attribute2,
663 fad.attribute3, fad.attribute4, fad.attribute5,
664 fad.attribute6, fad.attribute7, fad.attribute8,
665 fad.attribute9, fad.attribute10, fad.attribute11,
666 fad.attribute12, fad.attribute13, fad.attribute14,
667 fad.attribute15, fad.column1, fad.automatically_added_flag,
668 fad.category_id att_cat, fad.pk2_value, fad.pk3_value,
669 fad.pk4_value, fad.pk5_value,
670 fd.datatype_id, fd.category_id, fd.security_type, fd.security_id,
671 fd.publish_flag, fd.image_type, fd.storage_type,
672 fd.usage_type, fd.start_date_active, fd.end_date_active,
673 fdtl.language, fdtl.description, fd.file_name,
674 fd.media_id, fdtl.doc_attribute_category dattr_cat,
675 fdtl.doc_attribute1 dattr1, fdtl.doc_attribute2 dattr2,
676 fdtl.doc_attribute3 dattr3, fdtl.doc_attribute4 dattr4,
677 fdtl.doc_attribute5 dattr5, fdtl.doc_attribute6 dattr6,
678 fdtl.doc_attribute7 dattr7, fdtl.doc_attribute8 dattr8,
679 fdtl.doc_attribute9 dattr9, fdtl.doc_attribute10 dattr10,
680 fdtl.doc_attribute11 dattr11, fdtl.doc_attribute12 dattr12,
681 fdtl.doc_attribute13 dattr13, fdtl.doc_attribute14 dattr14,
682 fdtl.doc_attribute15 dattr15, fd.url, fdtl.title ,
683 FD.DM_NODE fd_dm_node,
684 fd.DM_FOLDER_PATH fd_DM_FOLDER_PATH,
685 fd.DM_TYPE fd_DM_TYPE,
686 fd.DM_DOCUMENT_ID fd_DM_DOCUMENT_ID,
687 fd.DM_VERSION_NUMBER fd_DM_VERSION_NUMBER,
688 fd.URL fd_URL,
689 fd.MEDIA_ID fd_MEDIA_ID
690 FROM fnd_attached_documents fad,
691 fnd_documents fd,
692 fnd_documents_tl fdtl
693 WHERE fad.document_id = fd.document_id
694 AND fd.document_id = fdtl.document_id
695 AND fdtl.language = userenv('LANG')
696 AND fd.document_id = X_from_document_id ;
697
698 CURSOR shorttext (mid NUMBER) IS
699 SELECT short_text
700 FROM fnd_documents_short_text
701 WHERE media_id = mid;
702
703 CURSOR longtext (mid NUMBER) IS
704 SELECT long_text
705 FROM fnd_documents_long_text
706 WHERE media_id = mid;
707
708 CURSOR fnd_lobs_cur (mid NUMBER) IS
709 SELECT file_id,
710 file_name,
711 file_content_type,
712 upload_date,
713 expiration_date,
714 program_name,
715 program_tag,
716 file_data,
717 language,
718 oracle_charset,
719 file_format
720 FROM fnd_lobs
721 WHERE file_id = mid;
722
723 media_id_tmp NUMBER;
724 document_id_tmp NUMBER;
725 row_id_tmp VARCHAR2(30);
726 short_text_tmp VARCHAR2(4000);
727 long_text_tmp LONG;
728 docrec docpk1%ROWTYPE;
729 fnd_lobs_rec fnd_lobs_cur%ROWTYPE;
730 BEGIN
731 -- Use cursor loop to get all attachments associated with
732
733
734 OPEN docpk1;
735 FETCH docpk1 INTO docrec;
736 --FOR docrec IN doclist LOOP
737 -- One-Time docs that Short Text or Long Text will have
738 -- to be copied into a new document (Long Text will be
739 -- truncated to 32K). Create the new document records
740 -- before creating the attachment record
741 --
745 FND_DOCUMENTS_PKG.Insert_Row(row_id_tmp,
742 IF (docrec.usage_type = 'O'
743 AND docrec.datatype_id IN (1,2,5,6,7,8,9) ) THEN
744 -- Create Documents records
746 document_id_tmp,
747 SYSDATE,
748 NVL(X_created_by,0),
749 SYSDATE,
750 NVL(X_created_by,0),
751 X_last_update_login,
752 docrec.datatype_id,
753 NVL(X_to_category_id, docrec.category_id),
754 docrec.security_type,
755 docrec.security_id,
756 docrec.publish_flag,
757 docrec.image_type,
758 docrec.storage_type,
759 docrec.usage_type,
760 docrec.start_date_active,
761 docrec.end_date_active,
762 X_request_id,
763 X_program_application_id,
764 X_program_id,
765 SYSDATE,
766 docrec.language,
767 docrec.description,
768 docrec.file_name,
769 media_id_tmp,
770 docrec.dattr_cat, docrec.dattr1,
771 docrec.dattr2, docrec.dattr3,
772 docrec.dattr4, docrec.dattr5,
773 docrec.dattr6, docrec.dattr7,
774 docrec.dattr8, docrec.dattr9,
775 docrec.dattr10, docrec.dattr11,
776 docrec.dattr12, docrec.dattr13,
777 docrec.dattr14, docrec.dattr15,
778 'N',docrec.url, docrec.title);
779
780 -- overwrite document_id from original
781 -- cursor for later insert into
782 -- fnd_attached_documents
783
784 docrec.document_id := document_id_tmp;
785
786 IF(docrec.datatype_id =7 OR docrec.datatype_id =8 OR docrec.datatype_id =9)
787 then
788 UPDATE fnd_documents
789 SET
790 DM_NODE = docrec.fd_dm_node,
791 DM_FOLDER_PATH =docrec.fd_dm_folder_path,
792 DM_TYPE = docrec.fd_dm_type,
793 DM_DOCUMENT_ID = docrec.fd_dm_document_id,
794 DM_VERSION_NUMBER = docrec.fd_dm_version_number,
795 URL = docrec.fd_url,
796 MEDIA_ID = docrec.fd_media_id
797 WHERE
798 document_id = document_id_tmp;
799 END IF; --end of update the document row for ws/webdav attachments
800
801
802 -- Duplicate short or long text
803 IF (docrec.datatype_id = 1) THEN
804 -- Handle short Text
805 -- get original data
806 OPEN shorttext(docrec.media_id);
807 FETCH shorttext INTO short_text_tmp;
808 CLOSE shorttext;
809
810 INSERT INTO fnd_documents_short_text (
811 media_id,
812 short_text)
813 VALUES (
814 media_id_tmp,
815 short_text_tmp);
816 media_id_tmp := '';
817
818 ELSIF (docrec.datatype_id = 2) THEN
819 -- Handle long text
820 -- get original data
821 OPEN longtext(docrec.media_id);
822 FETCH longtext INTO long_text_tmp;
823 CLOSE longtext;
824
825 INSERT INTO fnd_documents_long_text (
826 media_id,
827 long_text)
828 VALUES (
829 media_id_tmp,
830 long_text_tmp);
831 media_id_tmp := '';
832
833 ELSIF (docrec.datatype_id=6) THEN
834
835 OPEN fnd_lobs_cur(docrec.media_id);
836 FETCH fnd_lobs_cur
837 INTO fnd_lobs_rec.file_id,
838 fnd_lobs_rec.file_name,
839 fnd_lobs_rec.file_content_type,
840 fnd_lobs_rec.upload_date,
841 fnd_lobs_rec.expiration_date,
842 fnd_lobs_rec.program_name,
843 fnd_lobs_rec.program_tag,
844 fnd_lobs_rec.file_data,
845 fnd_lobs_rec.language,
846 fnd_lobs_rec.oracle_charset,
847 fnd_lobs_rec.file_format;
848 CLOSE fnd_lobs_cur;
849
850 INSERT INTO fnd_lobs (
851 file_id,
852 file_name,
853 file_content_type,
854 upload_date,
855 expiration_date,
856 program_name,
857 program_tag,
858 file_data,
859 language,
860 oracle_charset,
861 file_format)
862 VALUES (
863 media_id_tmp,
864 fnd_lobs_rec.file_name,
865 fnd_lobs_rec.file_content_type,
866 fnd_lobs_rec.upload_date,
867 fnd_lobs_rec.expiration_date,
868 fnd_lobs_rec.program_name,
869 fnd_lobs_rec.program_tag,
870 fnd_lobs_rec.file_data,
871 fnd_lobs_rec.language,
872 fnd_lobs_rec.oracle_charset,
873 fnd_lobs_rec.file_format);
874
875 media_id_tmp := '';
876
877 END IF; -- end of duplicating text
878
879
880 END IF; -- end if usage_type = 'O' and datatype in (1,2,6)
881
882 -- Update the document to be a std document if it
883 -- was an ole or image that wasn't already a std doc
884 -- (images should be created as Std, but just in case)
885 IF (docrec.datatype_id IN (3,4)
886 AND docrec.usage_type <> 'S') THEN
890 END IF;
887 UPDATE fnd_documents
888 SET usage_type = 'S'
889 WHERE document_id = docrec.document_id;
891
892 -- end of working through all attachments
893 -- close cursors.
894
895
896 CLOSE docpk1;
897
898
899 EXCEPTION WHEN OTHERS THEN
900 -- need to close all cursors
901 CLOSE docpk1;
902 CLOSE shorttext;
903 CLOSE longtext;
904 CLOSE fnd_lobs_cur;
905
906 END copy_documents ;
907
908
909 PROCEDURE copy_attachments_fnd(X_from_entity_name IN VARCHAR2,
910 X_from_pk1_value IN VARCHAR2,
911 X_from_pk2_value IN VARCHAR2 DEFAULT NULL,
912 X_from_pk3_value IN VARCHAR2 DEFAULT NULL,
913 X_from_pk4_value IN VARCHAR2 DEFAULT NULL,
914 X_from_pk5_value IN VARCHAR2 DEFAULT NULL,
915 X_from_attachment_id IN NUMBER DEFAULT NULL,
916 X_to_entity_name IN VARCHAR2,
917 X_to_pk1_value IN VARCHAR2,
918 X_to_pk2_value IN VARCHAR2 DEFAULT NULL,
919 X_to_pk3_value IN VARCHAR2 DEFAULT NULL,
920 X_to_pk4_value IN VARCHAR2 DEFAULT NULL,
921 X_to_pk5_value IN VARCHAR2 DEFAULT NULL,
922 X_to_attachment_id IN OUT NOCOPY NUMBER,
923 X_created_by IN NUMBER DEFAULT NULL,
924 X_last_update_login IN NUMBER DEFAULT NULL,
925 X_program_application_id IN NUMBER DEFAULT NULL,
926 X_program_id IN NUMBER DEFAULT NULL,
927 X_request_id IN NUMBER DEFAULT NULL,
928 X_automatically_added_flag IN VARCHAR2 DEFAULT NULL,
929 X_from_category_id IN NUMBER DEFAULT NULL,
930 X_to_category_id IN NUMBER DEFAULT NULL) IS
931
932 CURSOR docpk1 IS
933 SELECT fad.seq_num, fad.document_id,
934 fad.attribute_category, fad.attribute1, fad.attribute2,
935 fad.attribute3, fad.attribute4, fad.attribute5,
936 fad.attribute6, fad.attribute7, fad.attribute8,
937 fad.attribute9, fad.attribute10, fad.attribute11,
938 fad.attribute12, fad.attribute13, fad.attribute14,
939 fad.attribute15, fad.column1, fad.automatically_added_flag,
940 fad.category_id att_cat, fad.pk2_value, fad.pk3_value,
941 fad.pk4_value, fad.pk5_value,
942 fd.datatype_id, fd.category_id, fd.security_type, fd.security_id,
943 fd.publish_flag, fd.image_type, fd.storage_type,
944 fd.usage_type, fd.start_date_active, fd.end_date_active,
945 fdtl.language, fdtl.description, fd.file_name,
946 fd.media_id, fdtl.doc_attribute_category dattr_cat,
947 fdtl.doc_attribute1 dattr1, fdtl.doc_attribute2 dattr2,
948 fdtl.doc_attribute3 dattr3, fdtl.doc_attribute4 dattr4,
949 fdtl.doc_attribute5 dattr5, fdtl.doc_attribute6 dattr6,
950 fdtl.doc_attribute7 dattr7, fdtl.doc_attribute8 dattr8,
951 fdtl.doc_attribute9 dattr9, fdtl.doc_attribute10 dattr10,
952 fdtl.doc_attribute11 dattr11, fdtl.doc_attribute12 dattr12,
953 fdtl.doc_attribute13 dattr13, fdtl.doc_attribute14 dattr14,
954 fdtl.doc_attribute15 dattr15, fd.url, fdtl.title,
955 FD.DM_NODE fd_dm_node,
956 fd.DM_FOLDER_PATH fd_DM_FOLDER_PATH,
957 fd.DM_TYPE fd_DM_TYPE,
958 fd.DM_DOCUMENT_ID fd_DM_DOCUMENT_ID,
959 fd.DM_VERSION_NUMBER fd_DM_VERSION_NUMBER,
960 fd.URL fd_URL,
961 fd.MEDIA_ID fd_MEDIA_ID
962
963 FROM fnd_attached_documents fad,
964 fnd_documents fd,
965 fnd_documents_tl fdtl
966 WHERE fd.document_id = fad.document_id
967 AND fdtl.document_id = fd.document_id
968 AND fdtl.language = userenv('LANG')
969 AND fad.entity_name = X_from_entity_name
970 AND fad.pk1_value = X_from_pk1_value
971 AND (X_from_category_id IS NULL
972 OR (fad.category_id = X_from_category_id
973 OR (fad.category_id is NULL AND fd.category_id = X_from_category_id)))
974 AND fad.automatically_added_flag like decode(X_automatically_added_flag,NULL,'%',X_automatically_added_flag)
975 AND (X_from_attachment_id IS NULL OR fad.attached_document_id = X_from_attachment_id);
976
977
978 CURSOR docpk2 IS
979 SELECT fad.seq_num, fad.document_id,
980 fad.attribute_category, fad.attribute1, fad.attribute2,
981 fad.attribute3, fad.attribute4, fad.attribute5,
982 fad.attribute6, fad.attribute7, fad.attribute8,
983 fad.attribute9, fad.attribute10, fad.attribute11,
984 fad.attribute12, fad.attribute13, fad.attribute14,
985 fad.attribute15, fad.column1, fad.automatically_added_flag,
986 fad.category_id att_cat, fad.pk2_value, fad.pk3_value,
987 fad.pk4_value, fad.pk5_value,
988 fd.datatype_id, fd.category_id, fd.security_type, fd.security_id,
989 fd.publish_flag, fd.image_type, fd.storage_type,
990 fd.usage_type, fd.start_date_active, fd.end_date_active,
991 fdtl.language, fdtl.description, fd.file_name,
992 fd.media_id, fdtl.doc_attribute_category dattr_cat,
993 fdtl.doc_attribute1 dattr1, fdtl.doc_attribute2 dattr2,
994 fdtl.doc_attribute3 dattr3, fdtl.doc_attribute4 dattr4,
995 fdtl.doc_attribute5 dattr5, fdtl.doc_attribute6 dattr6,
996 fdtl.doc_attribute7 dattr7, fdtl.doc_attribute8 dattr8,
997 fdtl.doc_attribute9 dattr9, fdtl.doc_attribute10 dattr10,
998 fdtl.doc_attribute11 dattr11, fdtl.doc_attribute12 dattr12,
999 fdtl.doc_attribute13 dattr13, fdtl.doc_attribute14 dattr14,
1000 fdtl.doc_attribute15 dattr15, fd.url, fdtl.title,
1001 FD.DM_NODE fd_dm_node,
1002 fd.DM_FOLDER_PATH fd_DM_FOLDER_PATH,
1003 fd.DM_TYPE fd_DM_TYPE,
1004 fd.DM_DOCUMENT_ID fd_DM_DOCUMENT_ID,
1005 fd.DM_VERSION_NUMBER fd_DM_VERSION_NUMBER,
1006 fd.URL fd_URL,
1007 fd.MEDIA_ID fd_MEDIA_ID
1008 FROM fnd_attached_documents fad,
1009 fnd_documents fd,
1010 fnd_documents_tl fdtl
1011 WHERE fd.document_id = fad.document_id
1012 AND fdtl.document_id = fd.document_id
1013 AND fdtl.language = userenv('LANG')
1014 AND fad.entity_name = X_from_entity_name
1015 AND fad.pk1_value = X_from_pk1_value
1016 AND fad.pk2_value = X_from_pk2_value
1020 AND fad.automatically_added_flag like decode(X_automatically_added_flag,NULL,'%',X_automatically_added_flag)
1017 AND (X_from_category_id IS NULL
1018 OR (fad.category_id = X_from_category_id
1019 OR (fad.category_id is NULL AND fd.category_id = X_from_category_id)))
1021 AND (X_from_attachment_id IS NULL OR fad.attached_document_id = X_from_attachment_id);
1022
1023
1024
1025
1026
1027 CURSOR docpk3 IS
1028 SELECT fad.seq_num, fad.document_id,
1029 fad.attribute_category, fad.attribute1, fad.attribute2,
1030 fad.attribute3, fad.attribute4, fad.attribute5,
1031 fad.attribute6, fad.attribute7, fad.attribute8,
1032 fad.attribute9, fad.attribute10, fad.attribute11,
1033 fad.attribute12, fad.attribute13, fad.attribute14,
1034 fad.attribute15, fad.column1, fad.automatically_added_flag,
1035 fad.category_id att_cat, fad.pk2_value, fad.pk3_value,
1036 fad.pk4_value, fad.pk5_value,
1037 fd.datatype_id, fd.category_id, fd.security_type, fd.security_id,
1038 fd.publish_flag, fd.image_type, fd.storage_type,
1039 fd.usage_type, fd.start_date_active, fd.end_date_active,
1040 fdtl.language, fdtl.description, fd.file_name,
1041 fd.media_id, fdtl.doc_attribute_category dattr_cat,
1042 fdtl.doc_attribute1 dattr1, fdtl.doc_attribute2 dattr2,
1043 fdtl.doc_attribute3 dattr3, fdtl.doc_attribute4 dattr4,
1044 fdtl.doc_attribute5 dattr5, fdtl.doc_attribute6 dattr6,
1045 fdtl.doc_attribute7 dattr7, fdtl.doc_attribute8 dattr8,
1046 fdtl.doc_attribute9 dattr9, fdtl.doc_attribute10 dattr10,
1047 fdtl.doc_attribute11 dattr11, fdtl.doc_attribute12 dattr12,
1048 fdtl.doc_attribute13 dattr13, fdtl.doc_attribute14 dattr14,
1049 fdtl.doc_attribute15 dattr15, fd.url, fdtl.title ,
1050 FD.DM_NODE fd_dm_node,
1051 fd.DM_FOLDER_PATH fd_DM_FOLDER_PATH,
1052 fd.DM_TYPE fd_DM_TYPE,
1053 fd.DM_DOCUMENT_ID fd_DM_DOCUMENT_ID,
1054 fd.DM_VERSION_NUMBER fd_DM_VERSION_NUMBER,
1055 fd.URL fd_URL,
1056 fd.MEDIA_ID fd_MEDIA_ID
1057 FROM fnd_attached_documents fad,
1058 fnd_documents fd,
1059 fnd_documents_tl fdtl
1060 WHERE fd.document_id = fad.document_id
1061 AND fdtl.document_id = fd.document_id
1062 AND fdtl.language = userenv('LANG')
1063 AND fad.entity_name = X_from_entity_name
1064 AND fad.pk1_value = X_from_pk1_value
1065 AND fad.pk2_value = X_from_pk2_value
1066 AND fad.pk3_value = X_from_pk3_value
1067 -- AND (X_from_category_id IS NULL
1068 -- OR (fad.category_id = X_from_category_id
1069 -- OR (fad.category_id is NULL AND fd.category_id = X_from_category_id)))
1070 AND fad.automatically_added_flag like decode(X_automatically_added_flag,NULL,'%',X_automatically_added_flag)
1071 AND (X_from_attachment_id IS NULL OR fad.attached_document_id = X_from_attachment_id);
1072
1073
1074
1075
1076
1077 CURSOR doclist IS
1078 SELECT fad.seq_num, fad.document_id,
1079 fad.attribute_category, fad.attribute1, fad.attribute2,
1080 fad.attribute3, fad.attribute4, fad.attribute5,
1081 fad.attribute6, fad.attribute7, fad.attribute8,
1082 fad.attribute9, fad.attribute10, fad.attribute11,
1083 fad.attribute12, fad.attribute13, fad.attribute14,
1084 fad.attribute15, fad.column1, fad.automatically_added_flag,
1085 fad.category_id att_cat, fad.pk2_value, fad.pk3_value,
1086 fad.pk4_value, fad.pk5_value,
1087 fd.datatype_id, fd.category_id, fd.security_type, fd.security_id,
1088 fd.publish_flag, fd.image_type, fd.storage_type,
1089 fd.usage_type, fd.start_date_active, fd.end_date_active,
1090 fdtl.language, fdtl.description, fd.file_name,
1091 fd.media_id, fdtl.doc_attribute_category dattr_cat,
1092 fdtl.doc_attribute1 dattr1, fdtl.doc_attribute2 dattr2,
1093 fdtl.doc_attribute3 dattr3, fdtl.doc_attribute4 dattr4,
1094 fdtl.doc_attribute5 dattr5, fdtl.doc_attribute6 dattr6,
1095 fdtl.doc_attribute7 dattr7, fdtl.doc_attribute8 dattr8,
1096 fdtl.doc_attribute9 dattr9, fdtl.doc_attribute10 dattr10,
1097 fdtl.doc_attribute11 dattr11, fdtl.doc_attribute12 dattr12,
1098 fdtl.doc_attribute13 dattr13, fdtl.doc_attribute14 dattr14,
1099 fdtl.doc_attribute15 dattr15, fd.url, fdtl.title,
1100 FD.DM_NODE fd_dm_node,
1101 fd.DM_FOLDER_PATH fd_DM_FOLDER_PATH,
1102 fd.DM_TYPE fd_DM_TYPE,
1103 fd.DM_DOCUMENT_ID fd_DM_DOCUMENT_ID,
1104 fd.DM_VERSION_NUMBER fd_DM_VERSION_NUMBER,
1105 fd.URL fd_URL,
1106 fd.MEDIA_ID fd_MEDIA_ID
1107 FROM fnd_attached_documents fad,
1108 fnd_documents fd,
1109 fnd_documents_tl fdtl
1110 WHERE fd.document_id = fad.document_id
1111 AND fdtl.document_id = fd.document_id
1112 AND fdtl.language = userenv('LANG')
1113 AND fad.entity_name = X_from_entity_name
1114 AND fad.pk1_value = X_from_pk1_value
1115 AND (X_from_pk2_value IS NULL
1116 OR fad.pk2_value = X_from_pk2_value)
1117 AND (X_from_pk3_value IS NULL
1118 OR fad.pk3_value = X_from_pk3_value)
1119 AND (X_from_pk4_value IS NULL
1120 OR fad.pk4_value = X_from_pk4_value)
1121 AND (X_from_pk5_value IS NULL
1122 OR fad.pk5_value = X_from_pk5_value)
1123 AND (X_from_category_id IS NULL
1124 OR (fad.category_id = X_from_category_id
1125 OR (fad.category_id is NULL AND fd.category_id = X_from_category_id)))
1126 AND fad.automatically_added_flag like decode(X_automatically_added_flag,NULL,'%',X_automatically_added_flag)
1127 AND (X_from_attachment_id IS NULL OR fad.attached_document_id = X_from_attachment_id);
1128
1129
1130
1131
1132
1133 CURSOR shorttext (mid NUMBER) IS
1134 SELECT short_text
1135 FROM fnd_documents_short_text
1136 WHERE media_id = mid;
1137
1138 CURSOR longtext (mid NUMBER) IS
1139 SELECT long_text
1140 FROM fnd_documents_long_text
1141 WHERE media_id = mid;
1142
1143 CURSOR fnd_lobs_cur (mid NUMBER) IS
1144 SELECT file_id,
1148 expiration_date,
1145 file_name,
1146 file_content_type,
1147 upload_date,
1149 program_name,
1150 program_tag,
1151 file_data,
1152 language,
1153 oracle_charset,
1154 file_format
1155 FROM fnd_lobs
1156 WHERE file_id = mid;
1157
1158 media_id_tmp NUMBER;
1159 document_id_tmp NUMBER;
1160 row_id_tmp VARCHAR2(30);
1161 short_text_tmp VARCHAR2(4000);
1162 long_text_tmp LONG;
1163 docrec doclist%ROWTYPE;
1164 fnd_lobs_rec fnd_lobs_cur%ROWTYPE;
1165 l_to_attachment_id NUMBER;
1166 l_something VARCHAR2(2000);
1167 BEGIN
1168 -- Use cursor loop to get all attachments associated with
1169 -- the from_entity
1170 IF (X_from_entity_name IS NULL OR X_from_pk1_value IS NULL) THEN
1171 RETURN;
1172 END IF;
1173
1174 IF X_from_pk2_value IS NULL THEN -- performance change IF
1175 OPEN docpk1;
1176 ELSIF X_from_pk3_value IS NULL THEN
1177 OPEN docpk2;
1178 ELSIF X_from_pk4_value IS NULL THEN
1179 OPEN docpk3;
1180 ELSE
1181 OPEN doclist;
1182 END IF;
1183 <<pkloop>>
1184 LOOP
1185
1186 IF X_from_pk2_value IS NULL THEN -- performance change IF
1187 FETCH docpk1 INTO docrec;
1188 --EXIT
1189 IF (docpk1%notfound) THEN
1190 EXIT pkloop;
1191
1192 END IF;
1193 ELSIF X_from_pk3_value IS NULL THEN
1194 FETCH docpk2 INTO docrec;
1195 IF (docpk2%notfound) THEN
1196 EXIT pkloop;
1197
1198 END IF;
1199 ELSIF X_from_pk4_value IS NULL THEN
1200 FETCH docpk3 INTO docrec;
1201 IF (docpk3%notfound) THEN
1202 EXIT pkloop;
1203
1204 END IF;
1205 ELSE
1206 FETCH doclist INTO docrec;
1207 IF (doclist%notfound) THEN
1208 EXIT pkloop;
1209
1210 END IF;
1211 END IF;
1212
1213 --FOR docrec IN doclist LOOP
1214 -- One-Time docs that Short Text or Long Text will have
1215 -- to be copied into a new document (Long Text will be
1216 -- truncated to 32K). Create the new document records
1217 -- before creating the attachment record
1218 --
1219 IF (docrec.usage_type = 'O'
1220 AND docrec.datatype_id IN (1,2,5,6,7,8,9) ) THEN
1221 -- Create Documents records
1222 FND_DOCUMENTS_PKG.Insert_Row(row_id_tmp,
1223 document_id_tmp,
1224 SYSDATE,
1225 NVL(X_created_by,0),
1226 SYSDATE,
1227 NVL(X_created_by,0),
1228 X_last_update_login,
1229 docrec.datatype_id,
1230 NVL(X_to_category_id, docrec.category_id),
1231 docrec.security_type,
1232 docrec.security_id,
1233 docrec.publish_flag,
1234 docrec.image_type,
1235 docrec.storage_type,
1236 docrec.usage_type,
1237 docrec.start_date_active,
1238 docrec.end_date_active,
1239 X_request_id,
1240 X_program_application_id,
1241 X_program_id,
1242 SYSDATE,
1243 docrec.language,
1244 docrec.description,
1245 docrec.file_name,
1246 media_id_tmp,
1247 docrec.dattr_cat, docrec.dattr1,
1248 docrec.dattr2, docrec.dattr3,
1249 docrec.dattr4, docrec.dattr5,
1250 docrec.dattr6, docrec.dattr7,
1251 docrec.dattr8, docrec.dattr9,
1252 docrec.dattr10, docrec.dattr11,
1253 docrec.dattr12, docrec.dattr13,
1254 docrec.dattr14, docrec.dattr15,
1255 'N',docrec.url, docrec.title);
1256
1257 -- overwrite document_id from original
1258 -- cursor for later insert into
1259 -- fnd_attached_documents
1260
1261 --update the document row for ws/webdav attachments
1262 IF(docrec.datatype_id =7 OR docrec.datatype_id =8 OR docrec.datatype_id =9)
1263 then
1264 UPDATE fnd_documents
1265 SET
1266 DM_NODE = docrec.fd_dm_node,
1267 DM_FOLDER_PATH =docrec.fd_dm_folder_path,
1268 DM_TYPE = docrec.fd_dm_type,
1269 DM_DOCUMENT_ID = docrec.fd_dm_document_id,
1270 DM_VERSION_NUMBER = docrec.fd_dm_version_number,
1271 URL = docrec.fd_url,
1272 MEDIA_ID = docrec.fd_media_id
1273 WHERE
1274 document_id = document_id_tmp;
1275 END IF; --end of update the document row for ws/webdav attachments
1276 docrec.document_id := document_id_tmp;
1277 -- Duplicate short or long text
1278
1279 IF (docrec.datatype_id = 1) THEN
1280 -- Handle short Text
1281 -- get original data
1282 OPEN shorttext(docrec.media_id);
1283 FETCH shorttext INTO short_text_tmp;
1284 CLOSE shorttext;
1285
1286 INSERT INTO fnd_documents_short_text (
1287 media_id,
1288 short_text)
1289 VALUES (
1290 media_id_tmp,
1291 short_text_tmp);
1292 media_id_tmp := '';
1293
1294 ELSIF (docrec.datatype_id = 2) THEN
1295 -- Handle long text
1296 -- get original data
1297 OPEN longtext(docrec.media_id);
1298 FETCH longtext INTO long_text_tmp;
1299 CLOSE longtext;
1300 INSERT INTO fnd_documents_long_text (
1301 media_id,
1302 long_text)
1303 VALUES (
1304 media_id_tmp,
1305 long_text_tmp);
1306 media_id_tmp := '';
1307 ELSIF (docrec.datatype_id=6) THEN
1308
1309 OPEN fnd_lobs_cur(docrec.media_id);
1310 FETCH fnd_lobs_cur
1314 fnd_lobs_rec.upload_date,
1311 INTO fnd_lobs_rec.file_id,
1312 fnd_lobs_rec.file_name,
1313 fnd_lobs_rec.file_content_type,
1315 fnd_lobs_rec.expiration_date,
1316 fnd_lobs_rec.program_name,
1317 fnd_lobs_rec.program_tag,
1318 fnd_lobs_rec.file_data,
1319 fnd_lobs_rec.language,
1320 fnd_lobs_rec.oracle_charset,
1321 fnd_lobs_rec.file_format;
1322 CLOSE fnd_lobs_cur;
1323 INSERT INTO fnd_lobs (
1324 file_id,
1325 file_name,
1326 file_content_type,
1327 upload_date,
1328 expiration_date,
1329 program_name,
1330 program_tag,
1331 file_data,
1332 language,
1333 oracle_charset,
1334 file_format)
1335 VALUES (
1336 media_id_tmp,
1337 fnd_lobs_rec.file_name,
1338 fnd_lobs_rec.file_content_type,
1339 fnd_lobs_rec.upload_date,
1340 fnd_lobs_rec.expiration_date,
1341 fnd_lobs_rec.program_name,
1342 fnd_lobs_rec.program_tag,
1343 fnd_lobs_rec.file_data,
1344 fnd_lobs_rec.language,
1345 fnd_lobs_rec.oracle_charset,
1346 fnd_lobs_rec.file_format);
1347
1348 media_id_tmp := '';
1349 END IF; -- end of duplicating text
1350
1351 END IF; -- end if usage_type = 'O' and datatype in (1,2,6)
1352 -- Create attachment record
1353 SELECT fnd_attached_documents_s.NEXTVAL
1354 INTO l_to_attachment_id
1355 FROM dual;
1356 INSERT INTO fnd_attached_documents
1357 (attached_document_id,
1358 document_id,
1359 creation_date,
1360 created_by,
1361 last_update_date,
1362 last_updated_by,
1363 last_update_login,
1364 seq_num,
1365 entity_name,
1366 pk1_value, pk2_value, pk3_value,
1367 pk4_value, pk5_value,
1368 automatically_added_flag,
1369 program_application_id, program_id,
1370 program_update_date, request_id,
1371 attribute_category, attribute1,
1372 attribute2, attribute3, attribute4,
1373 attribute5, attribute6, attribute7,
1374 attribute8, attribute9, attribute10,
1375 attribute11, attribute12, attribute13,
1376 attribute14, attribute15, column1, category_id) VALUES
1377 (l_to_attachment_id,
1378 docrec.document_id,
1379 sysdate,
1380 NVL(X_created_by,0),
1381 sysdate,
1382 NVL(X_created_by,0),
1383 X_last_update_login,
1384 docrec.seq_num,
1385 X_to_entity_name,
1386 X_to_pk1_value,
1387 X_to_pk2_value,
1388 X_to_pk3_value,
1389 X_to_pk4_value,
1390 X_to_pk5_value,
1391 docrec.automatically_added_flag,
1392 X_program_application_id, X_program_id,
1393 sysdate, X_request_id,
1394 docrec.attribute_category, docrec.attribute1,
1395 docrec.attribute2, docrec.attribute3,
1396 docrec.attribute4, docrec.attribute5,
1397 docrec.attribute6, docrec.attribute7,
1398 docrec.attribute8, docrec.attribute9,
1399 docrec.attribute10, docrec.attribute11,
1400 docrec.attribute12, docrec.attribute13,
1401 docrec.attribute14, docrec.attribute15,
1402 docrec.column1,
1403 NVL(X_to_category_id, NVL(docrec.att_cat, docrec.category_id)));
1404
1405 X_to_attachment_id := l_to_attachment_id;
1406
1407 -- Update the document to be a std document if it
1408 -- was an ole or image that wasn't already a std doc
1409 -- (images should be created as Std, but just in case)
1410 IF (docrec.datatype_id IN (3,4)
1411 AND docrec.usage_type <> 'S') THEN
1412 UPDATE fnd_documents
1413 SET usage_type = 'S'
1414 WHERE document_id = docrec.document_id;
1415 END IF;
1416 END LOOP; -- end of working through all attachments
1417 -- close cursors.
1418 IF X_from_pk2_value IS NULL THEN -- performance change IF
1419 CLOSE docpk1;
1420 ELSIF X_from_pk3_value IS NULL THEN
1421 CLOSE docpk2;
1422 ELSIF X_from_pk4_value IS NULL THEN
1423 CLOSE docpk3;
1424 ELSE
1425 CLOSE doclist;
1426 END IF;
1427
1428 EXCEPTION WHEN OTHERS THEN
1429 -- need to close all cursors
1430 l_something := SQLERRM;
1431 CLOSE docpk1;
1432 CLOSE docpk2;
1433 CLOSE docpk3;
1434 CLOSE doclist;
1435 CLOSE shorttext;
1436 CLOSE longtext;
1437 CLOSE fnd_lobs_cur;
1438
1439 END copy_attachments_fnd;
1440
1441
1442
1443 PROCEDURE copy_attachments(X_from_entity_name IN VARCHAR2,
1444 X_from_pk1_value IN VARCHAR2,
1445 X_from_pk2_value IN VARCHAR2 DEFAULT NULL,
1446 X_from_pk3_value IN VARCHAR2 DEFAULT NULL,
1447 X_from_pk4_value IN VARCHAR2 DEFAULT NULL,
1448 X_from_pk5_value IN VARCHAR2 DEFAULT NULL,
1449 X_from_attachment_id IN NUMBER DEFAULT NULL,
1450 X_to_entity_name IN VARCHAR2,
1451 X_to_pk1_value IN VARCHAR2,
1452 X_to_pk2_value IN VARCHAR2 DEFAULT NULL,
1453 X_to_pk3_value IN VARCHAR2 DEFAULT NULL,
1454 X_to_pk4_value IN VARCHAR2 DEFAULT NULL,
1455 X_to_pk5_value IN VARCHAR2 DEFAULT NULL,
1459 X_program_application_id IN NUMBER DEFAULT NULL,
1456 X_to_attachment_id IN OUT NOCOPY NUMBER,
1457 X_created_by IN NUMBER DEFAULT NULL,
1458 X_last_update_login IN NUMBER DEFAULT NULL,
1460 X_program_id IN NUMBER DEFAULT NULL,
1461 X_request_id IN NUMBER DEFAULT NULL,
1462 X_automatically_added_flag IN VARCHAR2 DEFAULT NULL,
1463 X_from_category_id IN NUMBER DEFAULT NULL,
1464 X_to_category_id IN NUMBER DEFAULT NULL) IS
1465 l_delete_document_flag varchar2(1);
1466 BEGIN
1467
1468 copy_attachments_fnd(X_from_entity_name,
1469 X_from_pk1_value,
1470 X_from_pk2_value,
1471 X_from_pk3_value,
1472 X_from_pk4_value,
1473 X_from_pk5_value,
1474 X_from_attachment_id,
1475 X_to_entity_name,
1476 X_to_pk1_value,
1477 X_to_pk2_value,
1478 X_to_pk3_value,
1479 X_to_pk4_value,
1480 X_to_pk5_value,
1481 X_to_attachment_id,
1482 X_created_by,
1483 X_last_update_login,
1484 X_program_application_id,
1485 X_program_id,
1486 X_request_id,
1487 X_automatically_added_flag,
1488 X_from_category_id,
1489 X_to_category_id);
1490
1491
1492
1493 EXCEPTION WHEN OTHERS THEN
1494 NULL;
1495
1496
1497 END copy_attachments;
1498
1499
1500 END DOM_ATTACHMENT_UTIL_PKG;