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