[Home] [Help]
PACKAGE BODY: APPS.FND_DOCUMENTS_PKG
Source
1 PACKAGE BODY fnd_documents_pkg as
2 /* $Header: AFAKADCB.pls 120.8 2011/12/14 19:09:42 ctilley ship $ */
3
4
5 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
6 X_document_id IN OUT NOCOPY NUMBER,
7 X_creation_date DATE,
8 X_created_by NUMBER,
9 X_last_update_date DATE,
10 X_last_updated_by NUMBER,
11 X_last_update_login NUMBER DEFAULT NULL,
12 X_datatype_id NUMBER,
13 X_category_id NUMBER,
14 X_security_type NUMBER,
15 X_security_id NUMBER DEFAULT NULL,
16 X_publish_flag VARCHAR2,
17 X_image_type VARCHAR2 DEFAULT NULL,
18 X_storage_type NUMBER DEFAULT NULL,
19 X_usage_type VARCHAR2,
20 X_start_date_active DATE DEFAULT NULL,
21 X_end_date_active DATE DEFAULT NULL,
22 X_request_id NUMBER DEFAULT NULL,
23 X_program_application_id NUMBER DEFAULT NULL,
24 X_program_id NUMBER DEFAULT NULL,
25 X_program_update_date DATE DEFAULT NULL,
26 X_language VARCHAR2,
27 X_description VARCHAR2 DEFAULT NULL,
28 X_file_name VARCHAR2 DEFAULT NULL,
29 X_media_id IN OUT NOCOPY NUMBER,
30 X_Attribute_Category VARCHAR2 DEFAULT NULL,
31 X_Attribute1 VARCHAR2 DEFAULT NULL,
32 X_Attribute2 VARCHAR2 DEFAULT NULL,
33 X_Attribute3 VARCHAR2 DEFAULT NULL,
34 X_Attribute4 VARCHAR2 DEFAULT NULL,
35 X_Attribute5 VARCHAR2 DEFAULT NULL,
36 X_Attribute6 VARCHAR2 DEFAULT NULL,
37 X_Attribute7 VARCHAR2 DEFAULT NULL,
38 X_Attribute8 VARCHAR2 DEFAULT NULL,
39 X_Attribute9 VARCHAR2 DEFAULT NULL,
40 X_Attribute10 VARCHAR2 DEFAULT NULL,
41 X_Attribute11 VARCHAR2 DEFAULT NULL,
42 X_Attribute12 VARCHAR2 DEFAULT NULL,
43 X_Attribute13 VARCHAR2 DEFAULT NULL,
44 X_Attribute14 VARCHAR2 DEFAULT NULL,
45 X_Attribute15 VARCHAR2 DEFAULT NULL,
46 X_create_doc VARCHAR2 DEFAULT 'N',
47 X_url VARCHAR2 DEFAULT NULL,
48 X_title VARCHAR2 DEFAULT NULL,
49 X_dm_node NUMBER DEFAULT NULL,
50 X_dm_folder_path VARCHAR2 DEFAULT NULL,
51 X_dm_type VARCHAR2 DEFAULT NULL,
52 X_dm_document_id NUMBER DEFAULT NULL,
53 X_dm_version_number VARCHAR2 DEFAULT NULL,
54 X_orig_doc_id IN NUMBER DEFAULT NULL
55 ) IS
56 CURSOR C IS SELECT rowid
57 FROM fnd_documents
58 WHERE document_id = X_document_id;
59
60 l_media_id number;
61 --l_longtxt varchar2(32767);
62 l_longtxt fnd_documents_long_text.long_text%TYPE;
63 l_app_s_v varchar2(255);
64
65 BEGIN
66
67 l_media_id := X_media_id;
68
69 -- Get document_id from sequence
70 SELECT fnd_documents_s.nextval
71 INTO X_document_id
72 FROM dual;
73
74
75 -- Get media_id from the correct sequence depending on if it's
76 -- a short-text, long-text, ole_object, or db-stored image
77
78 IF (X_datatype_id = 1) THEN
79 SELECT fnd_documents_short_text_s.nextval
80 INTO X_media_id
81 FROM dual;
82 IF (X_create_doc = 'Y') then
83 insert into fnd_documents_short_text
84 (media_id, short_text, app_source_version)
85 select X_media_id, short_text, app_source_version
86 from fnd_documents_short_text
87 where media_id = l_media_id;
88
89 END IF;
90 ELSIF (X_datatype_id = 2) THEN
91 SELECT fnd_documents_long_text_s.nextval
92 INTO X_media_id
93 FROM dual;
94 IF (X_create_doc = 'Y') then
95 select long_text,app_source_version into l_longtxt,l_app_s_v from fnd_documents_long_text
96 where media_id = l_media_id;
97 insert into fnd_documents_long_text
98 (media_id,long_text,app_source_version)
99 values (X_media_id,l_longtxt,l_app_s_v);
100 END IF;
101 ELSIF ( (X_datatype_id = 4)
102 OR ( X_datatype_id = 3
103 AND X_storage_type = 1) ) THEN
104 SELECT fnd_documents_long_raw_s.nextval
105 INTO X_media_id
106 FROM dual;
107 ELSIF (X_datatype_id = 6 and X_media_id is NULL) THEN
108 SELECT fnd_lobs_s.nextval
109 INTO X_media_id
110 FROM dual;
111 END IF;
112
113 -- First insert row into "base" table
114 INSERT INTO fnd_documents(
115 document_id,
116 creation_date,
117 created_by,
118 last_update_date,
119 last_updated_by,
120 last_update_login,
121 datatype_id,
122 category_id,
123 security_type,
124 security_id,
125 publish_flag,
126 image_type,
127 storage_type,
128 usage_type,
129 start_date_active,
130 end_date_active,
131 request_id,
132 program_application_id,
133 program_id,
134 program_update_date,
135 url,
136 media_id,
137 file_name,
138 dm_node,
139 dm_folder_path,
140 dm_type,
141 dm_document_id,
142 dm_version_number,
143 orig_doc_id) VALUES (
144 X_document_id,
145 X_creation_date,
146 X_created_by,
147 X_last_update_date,
148 X_last_updated_by,
149 X_last_update_login,
150 X_datatype_id,
151 X_category_id,
152 X_security_type,
153 X_security_id,
154 X_publish_flag,
155 X_image_type,
156 X_storage_type,
157 X_usage_type,
158 X_start_date_active,
159 X_end_date_active,
160 X_request_id,
161 X_program_application_id,
162 X_program_id,
163 X_program_update_date,
164 X_url,
165 X_media_id,
166 X_file_name,
167 X_dm_node,
168 X_dm_folder_path,
169 X_dm_type,
170 X_dm_document_id,
171 X_dm_version_number,
172 X_orig_doc_id);
173
174 -- Next call procedure to put row into
175 -- "language" table that has normalized language-specific
176 -- columns
177 insert_tl_row(X_document_id => X_document_id,
178 X_creation_date => X_creation_date,
179 X_created_by => X_created_by,
180 X_last_update_date => X_last_update_date,
181 X_last_updated_by => X_last_updated_by,
182 X_last_update_login => X_last_update_login,
183 X_language => X_language,
184 X_description => X_description,
185 X_request_id => X_request_id,
186 X_program_application_id => X_program_application_id,
187 X_program_id => X_program_id,
188 X_program_update_date => X_program_update_date,
189 X_attribute_category => X_attribute_category,
190 X_attribute1 => X_attribute1,
191 X_attribute2 => X_attribute2,
192 X_attribute3 => X_attribute3,
193 X_attribute4 => X_attribute4,
194 X_attribute5 => X_attribute5,
195 X_attribute6 => X_attribute6,
196 X_attribute7 => X_attribute7,
197 X_attribute8 => X_attribute8,
198 X_attribute9 => X_attribute9,
199 X_attribute10 => X_attribute10,
200 X_attribute11 => X_attribute11,
201 X_attribute12 => X_attribute12,
202 X_attribute13 => X_attribute13,
203 X_attribute14 => X_attribute14,
204 X_attribute15 => X_attribute15,
205 X_title => X_title);
206
207 -- get rowid to pass back to form
208 OPEN C;
209 FETCH C INTO X_Rowid;
210 if (C%NOTFOUND) then
211 CLOSE C;
212
213 RAISE NO_DATA_FOUND;
214 end if;
215 CLOSE C;
216 END Insert_Row;
217
218
219 PROCEDURE insert_tl_row(X_document_id NUMBER,
220 X_creation_date DATE,
221 X_created_by NUMBER,
222 X_last_update_date DATE,
223 X_last_updated_by NUMBER,
224 X_last_update_login NUMBER DEFAULT NULL,
225 X_language VARCHAR2,
226 X_description VARCHAR2 DEFAULT NULL,
227 X_request_id NUMBER DEFAULT NULL,
228 X_program_application_id NUMBER DEFAULT NULL,
229 X_program_id NUMBER DEFAULT NULL,
230 X_program_update_date DATE DEFAULT NULL,
231 X_Attribute_Category VARCHAR2 DEFAULT NULL,
232 X_Attribute1 VARCHAR2 DEFAULT NULL,
233 X_Attribute2 VARCHAR2 DEFAULT NULL,
234 X_Attribute3 VARCHAR2 DEFAULT NULL,
235 X_Attribute4 VARCHAR2 DEFAULT NULL,
236 X_Attribute5 VARCHAR2 DEFAULT NULL,
237 X_Attribute6 VARCHAR2 DEFAULT NULL,
238 X_Attribute7 VARCHAR2 DEFAULT NULL,
239 X_Attribute8 VARCHAR2 DEFAULT NULL,
240 X_Attribute9 VARCHAR2 DEFAULT NULL,
241 X_Attribute10 VARCHAR2 DEFAULT NULL,
242 X_Attribute11 VARCHAR2 DEFAULT NULL,
243 X_Attribute12 VARCHAR2 DEFAULT NULL,
244 X_Attribute13 VARCHAR2 DEFAULT NULL,
245 X_Attribute14 VARCHAR2 DEFAULT NULL,
246 X_Attribute15 VARCHAR2 DEFAULT NULL,
247 X_title VARCHAR2 DEFAULT NULL)
248 IS
249 BEGIN
250
251 -- insert into "language" specific table
252 INSERT INTO fnd_Documents_tl (
253 document_id,
254 creation_date,
255 created_by,
256 last_update_date,
257 last_updated_by,
258 last_update_login,
259 language,
260 description,
261 request_id,
262 program_application_id,
263 program_id,
264 program_update_date,
265 doc_attribute_category,
266 doc_attribute1,
267 doc_attribute2,
268 doc_attribute3,
269 doc_attribute4,
270 doc_attribute5,
271 doc_attribute6,
272 doc_attribute7,
273 doc_attribute8,
274 doc_attribute9,
275 doc_attribute10,
276 doc_attribute11,
277 doc_attribute12,
278 doc_attribute13,
279 doc_attribute14,
280 doc_attribute15,
281 source_lang,
282 title) SELECT
283 X_document_id,
284 X_creation_date,
285 X_created_by,
286 X_last_update_date,
287 X_last_updated_by,
288 X_last_update_login,
289 L.language_code,
290 X_description,
291 X_request_id,
292 X_program_application_id,
293 X_program_id,
294 X_program_update_date,
295 X_attribute_category,
296 X_attribute1,
297 X_attribute2,
298 X_attribute3,
299 X_attribute4,
300 X_attribute5,
301 X_attribute6,
302 X_attribute7,
303 X_attribute8,
304 X_attribute9,
305 X_attribute10,
306 X_attribute11,
307 X_attribute12,
308 X_attribute13,
309 X_attribute14,
310 X_attribute15,
311 userenv('LANG'),
312 X_title
313 FROM fnd_languages L
314 WHERE l.installed_flag IN ('I','B')
315 AND NOT EXISTS (SELECT null
316 FROM fnd_documents_tl TL
317 WHERE document_id = x_document_id
318 AND TL.language = l.language_code);
319
320 END Insert_tl_Row;
321
322
323
324 PROCEDURE Lock_Row(X_document_id NUMBER,
325 X_datatype_id NUMBER,
326 X_category_id NUMBER,
327 X_security_type NUMBER,
331 X_storage_type NUMBER DEFAULT NULL,
328 X_security_id NUMBER DEFAULT NULL,
329 X_publish_flag VARCHAR2,
330 X_image_type VARCHAR2 DEFAULT NULL,
332 X_usage_type VARCHAR2,
333 X_start_date_active DATE DEFAULT NULL,
334 X_end_date_active DATE DEFAULT NULL,
335 X_language VARCHAR2,
336 X_description VARCHAR2 DEFAULT NULL,
337 X_file_name VARCHAR2 DEFAULT NULL,
338 X_media_id NUMBER,
339 X_Attribute_Category VARCHAR2 DEFAULT NULL,
340 X_Attribute1 VARCHAR2 DEFAULT NULL,
341 X_Attribute2 VARCHAR2 DEFAULT NULL,
342 X_Attribute3 VARCHAR2 DEFAULT NULL,
343 X_Attribute4 VARCHAR2 DEFAULT NULL,
344 X_Attribute5 VARCHAR2 DEFAULT NULL,
345 X_Attribute6 VARCHAR2 DEFAULT NULL,
346 X_Attribute7 VARCHAR2 DEFAULT NULL,
347 X_Attribute8 VARCHAR2 DEFAULT NULL,
348 X_Attribute9 VARCHAR2 DEFAULT NULL,
349 X_Attribute10 VARCHAR2 DEFAULT NULL,
350 X_Attribute11 VARCHAR2 DEFAULT NULL,
351 X_Attribute12 VARCHAR2 DEFAULT NULL,
352 X_Attribute13 VARCHAR2 DEFAULT NULL,
353 X_Attribute14 VARCHAR2 DEFAULT NULL,
354 X_Attribute15 VARCHAR2 DEFAULT NULL,
355 X_url VARCHAR2 DEFAULT NULL,
356 X_title VARCHAR2 DEFAULT NULL) IS
357 CURSOR C IS
358 SELECT *
359 FROM fnd_documents
360 WHERE document_id = X_document_id
361 FOR UPDATE of document_id NOWAIT;
362 Recinfo C%ROWTYPE;
363 BEGIN
364 OPEN C;
365 FETCH C INTO Recinfo;
366 if (C%NOTFOUND) then
367 CLOSE C;
368 RAISE NO_DATA_FOUND;
369 end if;
370 CLOSE C;
371
372 if (
373 ( (Recinfo.document_id = X_document_id) )
374 AND ( (Recinfo.datatype_id = X_datatype_id) )
375 AND ( (Recinfo.category_id = X_category_Id) )
376 AND ( (Recinfo.security_type = X_security_type) )
377 AND ( (Recinfo.security_id = X_security_id)
378 OR ( (Recinfo.security_id IS NULL)
379 AND (X_security_id IS NULL)))
380 AND ( (Recinfo.publish_flag = X_publish_flag) )
381 AND ( (Recinfo.image_type = X_image_type)
382 OR ( (Recinfo.image_type IS NULL)
383 AND (X_image_type IS NULL)))
384 AND ( (Recinfo.storage_type = X_storage_type)
385 OR ( (Recinfo.storage_type IS NULL)
386 AND (X_storage_type IS NULL)))
387 AND ( (Recinfo.usage_type = X_usage_type)
388 OR ( (Recinfo.usage_type IS NULL)
389 AND (X_usage_type IS NULL)))
390 AND ( (Recinfo.start_date_active = X_start_date_active)
391 OR ( (Recinfo.start_date_active IS NULL)
392 AND (X_start_date_active IS NULL)))
393 AND ( (Recinfo.end_date_active = X_end_date_active)
394 OR ( (Recinfo.end_date_active IS NULL)
395 AND (X_end_date_active IS NULL)))
396 AND ( (Recinfo.file_name = X_file_name)
397 OR ( (Recinfo.file_name IS NULL)
398 AND (X_file_name IS NULL)))
399 AND ( (Recinfo.media_id = X_media_id)
400 OR ( (Recinfo.media_id IS NULL)
401 AND (X_media_id IS NULL)))
402 AND ( (Recinfo.url = X_url)
403 OR ( (Recinfo.url IS NULL)
404 AND (X_url IS NULL)))
405 ) then
406
407 lock_tl_row(X_document_id => X_document_id,
408 X_language => X_language,
409 X_description => X_description,
410 X_Attribute_Category => X_Attribute_Category,
411 X_Attribute1 => X_Attribute1,
412 X_Attribute2 => X_Attribute2,
413 X_Attribute3 => X_Attribute3,
414 X_Attribute4 => X_Attribute4,
415 X_Attribute5 => X_Attribute5,
416 X_Attribute6 => X_Attribute6,
417 X_Attribute7 => X_Attribute7,
418 X_Attribute8 => X_Attribute8,
419 X_Attribute9 => X_Attribute9,
420 X_Attribute10 => X_Attribute10,
421 X_Attribute11 => X_Attribute11,
422 X_Attribute12 => X_Attribute12,
423 X_Attribute13 => X_Attribute13,
424 X_Attribute14 => X_Attribute14,
425 X_Attribute15 => X_Attribute15,
426 X_title => X_title);
427
428 return;
429 else
430 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
431 APP_EXCEPTION.RAISE_EXCEPTION;
432 end if;
433 END Lock_Row;
434
435
436 PROCEDURE Lock_tl_Row(X_document_id NUMBER,
437 X_language VARCHAR2,
438 X_description VARCHAR2,
442 X_Attribute3 VARCHAR2 DEFAULT NULL,
439 X_Attribute_Category VARCHAR2 DEFAULT NULL,
440 X_Attribute1 VARCHAR2 DEFAULT NULL,
441 X_Attribute2 VARCHAR2 DEFAULT NULL,
443 X_Attribute4 VARCHAR2 DEFAULT NULL,
444 X_Attribute5 VARCHAR2 DEFAULT NULL,
445 X_Attribute6 VARCHAR2 DEFAULT NULL,
446 X_Attribute7 VARCHAR2 DEFAULT NULL,
447 X_Attribute8 VARCHAR2 DEFAULT NULL,
448 X_Attribute9 VARCHAR2 DEFAULT NULL,
449 X_Attribute10 VARCHAR2 DEFAULT NULL,
450 X_Attribute11 VARCHAR2 DEFAULT NULL,
451 X_Attribute12 VARCHAR2 DEFAULT NULL,
452 X_Attribute13 VARCHAR2 DEFAULT NULL,
453 X_Attribute14 VARCHAR2 DEFAULT NULL,
454 X_Attribute15 VARCHAR2 DEFAULT NULL,
455 X_title VARCHAR2 DEFAULT NULL
456 ) IS
457 CURSOR C IS
458 SELECT *
459 FROM fnd_documents_tl
460 WHERE document_id = X_document_id
461 AND language = X_language
462 FOR UPDATE of language NOWAIT;
463 Recinfo C%ROWTYPE;
464 BEGIN
465 OPEN C;
466 FETCH C INTO Recinfo;
467 if (C%NOTFOUND) then
468 CLOSE C;
469 RAISE NO_DATA_FOUND;
470 end if;
471 CLOSE C;
472
473 if (
474 ( (Recinfo.document_id = X_document_id) )
475 AND ( (Recinfo.language = X_language) )
476 AND ( (Recinfo.description = X_description)
477 OR ( (Recinfo.description IS NULL)
478 AND (X_description IS NULL)))
479 AND ( (Recinfo.doc_attribute_category = X_Attribute_Category)
480 OR ( (Recinfo.doc_attribute_category IS NULL)
481 AND (X_Attribute_Category IS NULL)))
482 AND ( (Recinfo.doc_attribute1 = X_Attribute1)
483 OR ( (Recinfo.doc_attribute1 IS NULL)
484 AND (X_Attribute1 IS NULL)))
485 AND ( (Recinfo.doc_attribute2 = X_Attribute2)
486 OR ( (Recinfo.doc_attribute2 IS NULL)
487 AND (X_Attribute2 IS NULL)))
488 AND ( (Recinfo.doc_attribute3 = X_Attribute3)
489 OR ( (Recinfo.doc_attribute3 IS NULL)
490 AND (X_Attribute3 IS NULL)))
491 AND ( (Recinfo.doc_attribute4 = X_Attribute4)
492 OR ( (Recinfo.doc_attribute4 IS NULL)
493 AND (X_Attribute4 IS NULL)))
494 AND ( (Recinfo.doc_attribute5 = X_Attribute5)
495 OR ( (Recinfo.doc_attribute5 IS NULL)
496 AND (X_Attribute5 IS NULL)))
497 AND ( (Recinfo.doc_attribute6 = X_Attribute6)
498 OR ( (Recinfo.doc_attribute6 IS NULL)
499 AND (X_Attribute6 IS NULL)))
500 AND ( (Recinfo.doc_attribute7 = X_Attribute7)
501 OR ( (Recinfo.doc_attribute7 IS NULL)
502 AND (X_Attribute7 IS NULL)))
503 AND ( (Recinfo.doc_attribute8 = X_Attribute8)
504 OR ( (Recinfo.doc_attribute8 IS NULL)
505 AND (X_Attribute8 IS NULL)))
506 AND ( (Recinfo.doc_attribute9 = X_Attribute9)
507 OR ( (Recinfo.doc_attribute9 IS NULL)
508 AND (X_Attribute9 IS NULL)))
509 AND ( (Recinfo.doc_attribute10 = X_Attribute10)
510 OR ( (Recinfo.doc_attribute10 IS NULL)
511 AND (X_Attribute10 IS NULL)))
512 AND ( (Recinfo.doc_attribute11 = X_Attribute11)
513 OR ( (Recinfo.doc_attribute11 IS NULL)
514 AND (X_Attribute11 IS NULL)))
515 AND ( (Recinfo.doc_attribute12 = X_Attribute12)
516 OR ( (Recinfo.doc_attribute12 IS NULL)
517 AND (X_Attribute12 IS NULL)))
518 AND ( (Recinfo.doc_attribute13 = X_Attribute13)
519 OR ( (Recinfo.doc_attribute13 IS NULL)
520 AND (X_Attribute13 IS NULL)))
521 AND ( (Recinfo.doc_attribute14 = X_Attribute14)
522 OR ( (Recinfo.doc_attribute14 IS NULL)
523 AND (X_Attribute14 IS NULL)))
524 AND ( (Recinfo.doc_attribute15 = X_Attribute15)
525 OR ( (Recinfo.doc_attribute15 IS NULL)
526 AND (X_Attribute15 IS NULL)))
527 AND ( (Recinfo.title = X_title)
528 OR ( (Recinfo.title IS NULL)
529 AND (X_title IS NULL)))
530 ) then
531 return;
532 else
533
534 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
535 APP_EXCEPTION.RAISE_EXCEPTION;
536 end if;
537 END Lock_tl_Row;
538
539
540 PROCEDURE Update_Row(X_document_id NUMBER,
541 X_last_update_date DATE,
542 X_last_updated_by NUMBER,
543 X_last_update_login NUMBER,
544 X_datatype_id NUMBER,
545 X_category_id NUMBER,
546 X_security_type NUMBER,
547 X_security_id NUMBER,
548 X_publish_flag VARCHAR2,
549 X_image_type VARCHAR2,
550 X_storage_type NUMBER,
551 X_usage_type VARCHAR2,
552 X_start_date_active DATE,
553 X_end_date_active DATE,
554 X_language VARCHAR2,
558 X_Attribute_Category VARCHAR2 DEFAULT NULL,
555 X_description VARCHAR2,
556 X_file_name VARCHAR2,
557 X_media_id NUMBER,
559 X_Attribute1 VARCHAR2 DEFAULT NULL,
560 X_Attribute2 VARCHAR2 DEFAULT NULL,
561 X_Attribute3 VARCHAR2 DEFAULT NULL,
562 X_Attribute4 VARCHAR2 DEFAULT NULL,
563 X_Attribute5 VARCHAR2 DEFAULT NULL,
564 X_Attribute6 VARCHAR2 DEFAULT NULL,
565 X_Attribute7 VARCHAR2 DEFAULT NULL,
566 X_Attribute8 VARCHAR2 DEFAULT NULL,
567 X_Attribute9 VARCHAR2 DEFAULT NULL,
568 X_Attribute10 VARCHAR2 DEFAULT NULL,
569 X_Attribute11 VARCHAR2 DEFAULT NULL,
570 X_Attribute12 VARCHAR2 DEFAULT NULL,
571 X_Attribute13 VARCHAR2 DEFAULT NULL,
572 X_Attribute14 VARCHAR2 DEFAULT NULL,
573 X_Attribute15 VARCHAR2 DEFAULT NULL,
574 X_url VARCHAR2 DEFAULT NULL,
575 X_title VARCHAR2 DEFAULT NULL,
576 X_dm_node NUMBER DEFAULT NULL,
577 X_dm_folder_path VARCHAR2 DEFAULT NULL,
578 X_dm_type VARCHAR2 DEFAULT NULL,
579 X_dm_document_id NUMBER DEFAULT NULL,
580 X_dm_version_number VARCHAR2 DEFAULT NULL
581 ) IS
582 BEGIN
583 UPDATE fnd_documents
584 SET document_id = X_document_id,
585 last_update_date = X_last_update_date,
586 last_updated_by = X_last_updated_by,
587 last_update_login = X_last_update_login,
588 datatype_id = X_datatype_id,
589 category_id = X_category_id,
590 security_type = X_security_type,
591 security_id = X_security_id,
592 publish_flag = X_publish_flag,
593 image_type = X_image_type,
594 storage_type = X_storage_type,
595 usage_type = X_usage_type,
596 start_date_active = X_start_date_Active,
597 end_date_active = X_end_date_Active,
598 url = X_url,
599 media_id = X_media_id,
600 file_name = X_file_name,
601 dm_node = X_dm_node,
602 dm_folder_path = X_dm_folder_path,
603 dm_type = X_dm_type,
604 dm_document_id = X_dm_document_id,
605 dm_version_number = X_dm_version_number
606 WHERE document_id = X_document_id;
607
608 if (SQL%NOTFOUND) then
609 RAISE NO_DATA_FOUND;
610 end if;
611
612 -- now update language-specific row
613 Update_tl_Row(X_document_id => X_document_id,
614 X_last_update_date => X_last_update_date,
615 X_last_updated_by => X_last_updated_by,
616 X_last_update_login => X_last_update_login,
617 X_language => X_language,
618 X_description => X_description,
619 X_Attribute_Category => X_Attribute_Category,
620 X_Attribute1 => X_Attribute1,
621 X_Attribute2 => X_Attribute2,
622 X_Attribute3 => X_Attribute3,
623 X_Attribute4 => X_Attribute4,
624 X_Attribute5 => X_Attribute5,
625 X_Attribute6 => X_Attribute6,
626 X_Attribute7 => X_Attribute7,
627 X_Attribute8 => X_Attribute8,
628 X_Attribute9 => X_Attribute9,
629 X_Attribute10 => X_Attribute10,
630 X_Attribute11 => X_Attribute11,
631 X_Attribute12 => X_Attribute12,
632 X_Attribute13 => X_Attribute13,
633 X_Attribute14 => X_Attribute14,
634 X_Attribute15 => X_Attribute15,
635 X_title => X_title);
636
637
638 END Update_Row;
639
640
641 PROCEDURE Update_tl_Row(X_document_id NUMBER,
642 X_last_update_date DATE,
643 X_last_updated_by NUMBER,
644 X_last_update_login NUMBER DEFAULT NULL,
645 X_language VARCHAR2,
646 X_description VARCHAR2 DEFAULT NULL,
647 X_Attribute_Category VARCHAR2 DEFAULT NULL,
648 X_Attribute1 VARCHAR2 DEFAULT NULL,
649 X_Attribute2 VARCHAR2 DEFAULT NULL,
650 X_Attribute3 VARCHAR2 DEFAULT NULL,
651 X_Attribute4 VARCHAR2 DEFAULT NULL,
652 X_Attribute5 VARCHAR2 DEFAULT NULL,
653 X_Attribute6 VARCHAR2 DEFAULT NULL,
654 X_Attribute7 VARCHAR2 DEFAULT NULL,
655 X_Attribute8 VARCHAR2 DEFAULT NULL,
656 X_Attribute9 VARCHAR2 DEFAULT NULL,
657 X_Attribute10 VARCHAR2 DEFAULT NULL,
658 X_Attribute11 VARCHAR2 DEFAULT NULL,
659 X_Attribute12 VARCHAR2 DEFAULT NULL,
663 X_title VARCHAR2 DEFAULT NULL
660 X_Attribute13 VARCHAR2 DEFAULT NULL,
661 X_Attribute14 VARCHAR2 DEFAULT NULL,
662 X_Attribute15 VARCHAR2 DEFAULT NULL,
664 ) IS
665 BEGIN
666 UPDATE fnd_documents_tl
667 SET document_id = X_document_id,
668 last_update_date = X_last_update_date,
669 last_updated_by = X_last_updated_by,
670 last_update_login = X_last_update_login,
671 language = X_language,
672 description = X_description,
673 doc_attribute_category = X_Attribute_Category,
674 doc_attribute1 = X_Attribute1,
675 doc_attribute2 = X_Attribute2,
676 doc_attribute3 = X_Attribute3,
677 doc_attribute4 = X_Attribute4,
678 doc_attribute5 = X_Attribute5,
679 doc_attribute6 = X_Attribute6,
680 doc_attribute7 = X_Attribute7,
681 doc_attribute8 = X_Attribute8,
682 doc_attribute9 = X_Attribute9,
683 doc_attribute10 = X_Attribute10,
684 doc_attribute11 = X_Attribute11,
685 doc_attribute12 = X_Attribute12,
686 doc_attribute13 = X_Attribute13,
687 doc_attribute14 = X_Attribute14,
688 doc_attribute15 = X_Attribute15,
689 title = X_title
690 WHERE document_id = X_document_id
691 AND language = X_language;
692
693 if (SQL%NOTFOUND) then
694 RAISE NO_DATA_FOUND;
695 end if;
696
697 END Update_tl_Row;
698
699 PROCEDURE Delete_Row(X_document_id NUMBER,
700 X_datatype_id NUMBER,
701 delete_ref_Flag VARCHAR2 DEFAULT 'N') IS
702 BEGIN
703 -- need to delete in this order for R10-10SC compatibility
704 -- triggers to operate properly
705 -- 1. fnd_attached_documents
706 -- 2. fnd_documents_short_text/long_text/long_raw
707 -- 3. fnd_documents_tl
708 -- 4. fnd_documents
709 -- Delete the Reference if flag set to Y
710 IF (delete_ref_flag = 'Y') THEN
711 DELETE FROM fnd_attached_documents
712 WHERE document_id = X_document_id;
713 END IF;
714
715 -- now go about the business of deleting the document from
716 -- the document tables
717 IF (X_datatype_id = 1) THEN
718 DELETE FROM fnd_documents_short_text
719 WHERE media_id IN
720 (SELECT media_id
721 FROM fnd_documents
722 WHERE document_id = x_document_id);
723 ELSIF (X_datatype_id = 2) THEN
724 DELETE FROM fnd_documents_long_text
725 WHERE media_id IN
726 (SELECT media_id
727 FROM fnd_documents
728 WHERE document_id = x_document_id);
729 ELSIF (X_datatype_id IN (3,4) ) THEN
730 DELETE FROM fnd_documents_long_raw
731 WHERE media_id IN
732 (SELECT media_id
733 FROM fnd_documents
734 WHERE document_id = x_document_id);
735 ELSIF (X_datatype_id = 6) THEN
736 DELETE FROM fnd_lobs
737 WHERE file_id in
738 (SELECT media_id
739 from fnd_documents
740 WHERE document_id = x_document_id);
741 END IF;
742
743 DELETE FROM fnd_documents_tl
744 WHERE document_id = X_document_id;
745
746 DELETE FROM fnd_documents
747 WHERE document_id = X_document_id;
748
749 END Delete_Row;
750
751 procedure ADD_LANGUAGE
752 is
753 begin
754 /* Mar/19/03 requested by Ric Ginsberg */
755 /* The following delete and update statements are commented out */
756 /* as a quick workaround to fix the time-consuming table handler issue */
757 /* Eventually we'll need to turn them into a separate fix_language procedure */
758 /*
759
760 delete from FND_DOCUMENTS_TL T
761 where not exists
762 (select NULL
763 from FND_DOCUMENTS B
764 where B.DOCUMENT_ID = T.DOCUMENT_ID
765 );
766
767 update FND_DOCUMENTS_TL T set (
768 DESCRIPTION
769 ) = (select
770 B.DESCRIPTION
771 from FND_DOCUMENTS_TL B
772 where B.DOCUMENT_ID = T.DOCUMENT_ID
773 and B.LANGUAGE = T.SOURCE_LANG)
774 where (
775 T.DOCUMENT_ID,
776 T.LANGUAGE
777 ) in (select
778 SUBT.DOCUMENT_ID,
779 SUBT.LANGUAGE
780 from FND_DOCUMENTS_TL SUBB, FND_DOCUMENTS_TL SUBT
781 where SUBB.DOCUMENT_ID = SUBT.DOCUMENT_ID
782 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
783 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
784 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
785 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
786 ));
787 */
788
789 insert /*+ append parallel(tt) */ into FND_DOCUMENTS_TL tt (
790 DOCUMENT_ID,
791 CREATION_DATE,
792 CREATED_BY,
793 LAST_UPDATE_DATE,
794 LAST_UPDATED_BY,
795 LAST_UPDATE_LOGIN,
796 DESCRIPTION,
797 REQUEST_ID,
798 PROGRAM_APPLICATION_ID,
799 PROGRAM_ID,
800 PROGRAM_UPDATE_DATE,
801 DOC_ATTRIBUTE_CATEGORY,
802 DOC_ATTRIBUTE1,
803 DOC_ATTRIBUTE2,
804 DOC_ATTRIBUTE3,
805 DOC_ATTRIBUTE4,
806 DOC_ATTRIBUTE5,
807 DOC_ATTRIBUTE6,
808 DOC_ATTRIBUTE7,
809 DOC_ATTRIBUTE8,
810 DOC_ATTRIBUTE9,
811 DOC_ATTRIBUTE10,
812 DOC_ATTRIBUTE11,
813 DOC_ATTRIBUTE12,
814 DOC_ATTRIBUTE13,
815 DOC_ATTRIBUTE14,
816 DOC_ATTRIBUTE15,
817 APP_SOURCE_VERSION,
818 SHORT_TEXT,
819 LANGUAGE,
820 SOURCE_LANG)
821 select /*+ parallel(v) parallel(t) use_nl(t) */ v.*
822 from( SELECT /*+ no_merge ordered parellel(b) */
823 B.DOCUMENT_ID,
824 B.CREATION_DATE,
828 B.LAST_UPDATE_LOGIN,
825 B.CREATED_BY,
826 B.LAST_UPDATE_DATE,
827 B.LAST_UPDATED_BY,
829 B.DESCRIPTION,
830 B.REQUEST_ID,
831 B.PROGRAM_APPLICATION_ID,
832 B.PROGRAM_ID,
833 B.PROGRAM_UPDATE_DATE,
834 B.DOC_ATTRIBUTE_CATEGORY,
835 B.DOC_ATTRIBUTE1,
836 B.DOC_ATTRIBUTE2,
837 B.DOC_ATTRIBUTE3,
838 B.DOC_ATTRIBUTE4,
839 B.DOC_ATTRIBUTE5,
840 B.DOC_ATTRIBUTE6,
841 B.DOC_ATTRIBUTE7,
842 B.DOC_ATTRIBUTE8,
843 B.DOC_ATTRIBUTE9,
844 B.DOC_ATTRIBUTE10,
845 B.DOC_ATTRIBUTE11,
846 B.DOC_ATTRIBUTE12,
847 B.DOC_ATTRIBUTE13,
848 B.DOC_ATTRIBUTE14,
849 B.DOC_ATTRIBUTE15,
850 B.APP_SOURCE_VERSION,
851 B.SHORT_TEXT,
852 L.LANGUAGE_CODE,
853 B.SOURCE_LANG
854 from FND_DOCUMENTS_TL B, FND_LANGUAGES L
855 where L.INSTALLED_FLAG in ('I', 'B')
856 and B.LANGUAGE = userenv('LANG')
857 ) v, fnd_documents_tl t
858 where T.DOCUMENT_ID(+) = v.DOCUMENT_ID
859 and T.LANGUAGE(+) = v.LANGUAGE_CODE
860 and t.document_id IS NULL;
861
862 end ADD_LANGUAGE;
863
864 END fnd_documents_pkg;