[Home] [Help]
PACKAGE BODY: APPS.SO_ATT
Source
1 PACKAGE BODY so_att AS
2 /* $Header: oeatt04b.pls 115.2 99/07/16 08:25:20 porting shi $ */
3 PROCEDURE mark_record (
4 p_src_id NUMBER, -- media_id | document_id
5 p_short_long VARCHAR2, -- 'S' | 'L'
6 p_source VARCHAR2,-- 'DOCUMENT' | 'NOTE'
7 p_operation VARCHAR2,-- 'INSERT' | 'UPDATE'
8 p_version VARCHAR2 -- 'SO_10SC' | 'SO_R10'
9 ) IS
10 x_document_id NUMBER;
11 x_category_id NUMBER;
12 x_usage_id NUMBER;
13 x_usage_type VARCHAR2(1);
14 x_application_id NUMBER;
15
16 BEGIN
17 -- If source is DOCUMENT, then determine if it's
18 -- an OE-related document
19 IF (p_source = 'DOCUMENT') THEN
20 -- call get_note_info with media_id and short/long
21 get_note_info(p_src_id, p_short_long, x_document_id,
22 x_category_id, x_usage_id, x_usage_type,
23 x_application_id);
24 IF (x_application_id <> 300) THEN
25 RETURN;
26 END IF;
27 END IF;
28
29 INSERT INTO so_note_replication (
30 source_id,
31 datatype_id,
32 source_code,
33 operation_code,
34 version
35 ) VALUES (
36 p_src_id,
37 decode(p_short_long,'S',1,'L',2),
38 p_source,
39 p_operation,
40 p_version
41 );
42
43 END;
44
45 PROCEDURE clear_mark (
46 p_short_long VARCHAR2, -- 'S' | 'L'
47 p_source VARCHAR2,-- 'DOCUMENT' | 'NOTE'
48 p_operation VARCHAR2,-- 'INSERT' | 'UPDATE'
49 p_version VARCHAR2 -- 'SO_10SC' | 'SO_R10'
50 ) IS
51
52 BEGIN
53
54 DELETE FROM so_note_replication
55 WHERE datatype_id = decode(p_short_long,'S',1,'L',2)
56 AND source_code = p_source
57 AND operation_code = p_operation
58 AND version = p_version;
59
60 END;
61
62 FUNCTION get_note_name (
63 p_document_id NUMBER
64 ) RETURN VARCHAR2 IS
65 x_note_name VARCHAR2(2000);
66 x_doc_desc VARCHAR2(30);
67 x_language fnd_documents_tl.language%TYPE;
68 x_check NUMBER;
69 CURSOR doc IS
70 SELECT SUBSTR(description,1,30)
71 FROM fnd_documents_tl
72 WHERE document_id = p_document_id
73 AND language = x_language;
74 CURSOR check_unique IS
75 SELECT 1
76 FROM sys.dual
77 WHERE EXISTS (SELECT 1
78 FROM so_notes
79 WHERE name = x_doc_desc);
80 BEGIN
81
82 x_language := fnd_global.current_language;
83
84 fnd_message.set_name ('OE','OE_GEN_NOTE_NAME');
85 fnd_message.set_token('DOCUMENT_ID',to_char(p_document_id));
86 x_note_name := fnd_message.get;
87
88 -- get Description from document
89 OPEN doc;
90 FETCH doc INTO x_doc_desc;
91 CLOSE doc;
92
93 -- determine if description is already used in so_notes
94 -- so_notes has a non-unique index on name, but the
95 -- form enforces uniqueness on name
96 OPEN check_unique;
97 FETCH check_unique INTO x_check;
98 IF (check_unique%NOTFOUND) THEN
99 -- if row found, use description rather than
100 -- generated name
101 x_note_name := x_doc_desc;
102 END IF;
103
104 CLOSE check_unique;
105
106 RETURN substr( x_note_name, 1, 30 );
107 END;
108
109
110 FUNCTION get_note_error (
111 p_msg_name VARCHAR2,
112 p_document_id NUMBER DEFAULT NULL) RETURN VARCHAR2 IS
113 x_note_name VARCHAR2(2000);
114 BEGIN
115
116 IF (p_msg_name = 'OE_NOT_UPDATE_NOTE') THEN
117 fnd_message.set_name('OE', p_msg_name);
118 x_note_name := fnd_message.get;
119 ELSIF (p_msg_name IN ('OE_NOT_NOTE_TOO_LONG','OE_NOT_DOCUMENT_TOO_LONG')
120 ) THEN
121 fnd_message.set_name ('OE', p_msg_name);
122 fnd_message.set_token('DOCUMENT_ID',to_char(p_document_id));
123 x_note_name := fnd_message.get;
124 END IF;
125
126 RETURN(x_note_name);
127
128 END get_note_error;
129
130
131
132 FUNCTION get_document_usage_type (
133 p_note_id NUMBER
134 ) RETURN VARCHAR2 IS
135 x_note_type_code VARCHAR2(30);
136 x_override_flag VARCHAR2(1);
137 x_document_usage_type VARCHAR2(1);
138 BEGIN
139 /*
140 ** Note_Type_Code Override_Allowed_flag ==> document_usage_type
141 ** -------------- --------------------- -------------------
142 ** Standard(SN) Y Template (T)
143 ** Standard(SN) N Standard (S)
144 ** One-Time(OT) Y | N One-Time (O)
145 */
146
147 SELECT NOTE_TYPE_CODE, OVERRIDE_ALLOWED_FLAG
148 INTO x_note_type_code, x_override_flag
149 FROM so_notes
150 WHERE note_id = p_note_id;
151
152 If (x_note_type_code = 'SN') Then
153 If (x_override_flag = 'Y') Then
154 x_document_usage_type := 'T';
155 Else
156 x_document_usage_type := 'S';
157 End If;
158 Else
159 x_document_usage_type := 'O';
160 End If;
161
162 RETURN x_document_usage_type;
163 END;
164
165
166
167 FUNCTION get_entity_name (p_header_id IN NUMBER, p_line_id IN NUMBER)
168 RETURN VARCHAR2 IS
169 x_entity_name VARCHAR2(40);
170 x_order_category VARCHAR2(30);
171 CURSOR c1 IS
172 SELECT order_category
173 FROM so_headers
174 WHERE header_id = p_header_id;
175 BEGIN
176
177 -- Get order_category
178 OPEN c1;
179 FETCH c1 INTO x_order_category;
180 CLOSE c1;
181
182 IF (x_order_category = 'RMA') THEN
183 IF (p_line_id IS NOT NULL) THEN
184 x_entity_name := 'SO_RETURN_LINES';
185 ELSE
186 x_entity_name := 'SO_RETURNS';
187 END IF;
188 ELSE
189 IF (p_line_id IS NOT NULL) THEN
190 x_entity_name := 'SO_LINES';
191 ELSE
192 x_entity_name := 'SO_HEADERS';
193 END IF;
194 END IF;
195
196 RETURN x_entity_name;
197 END;
198
199
200 PROCEDURE get_category_id (
201 p_usage_id NUMBER,
202 p_category_id OUT NUMBER
203 ) IS
204 BEGIN
205
206 SELECT snu.category_id
207 INTO p_category_id
208 FROM so_note_usages snu
209 WHERE snu.usage_id = p_usage_id;
210
211
212 EXCEPTION
213 WHEN NO_DATA_FOUND THEN
214 p_category_id := -1;
215 END;
216
217
218 PROCEDURE get_usage_id (
219 p_category_id NUMBER,
220 p_usage_id OUT NUMBER
221 ) IS
222 BEGIN
223
224 SELECT snu.usage_id
225 INTO p_usage_id
226 FROM fnd_document_categories fdc,
227 so_note_usages snu
228 WHERE fdc.category_id = snu.category_id
229 AND fdc.category_id = p_category_id;
230
231 END;
232
233
234 PROCEDURE get_media_id (
235 p_document_id NUMBER,
236 p_media_id OUT NUMBER,
237 p_datatype_id OUT NUMBER
238 ) IS
239 BEGIN
240 SELECT fd.datatype_id, fdt.media_id
241 INTO p_datatype_id, p_media_id
242 FROM fnd_documents_tl fdt,
243 fnd_documents fd
244 WHERE fdt.language = fnd_global.current_language
245 AND fdt.document_id = fd.document_id
246 AND fd.document_id = p_document_id;
247
248 END;
249
250 FUNCTION get_doc_cat_application(p_document_id IN NUMBER) RETURN NUMBER
251 IS
252 x_category_application NUMBER;
253 CURSOR get_doc_cat_app IS
254 SELECT fdc.application_id
255 FROM fnd_document_categories fdc,
256 fnd_documents fd
257 WHERE fd.category_id = fdc.category_id
258 AND fd.document_id = p_document_id;
259 BEGIN
260
261 OPEN get_doc_cat_app;
262 FETCH get_doc_cat_app INTO x_category_application;
263 CLOSE get_doc_cat_app;
264
265 RETURN(NVL(x_category_application,-1));
266
267 EXCEPTION
268 WHEN OTHERS THEN CLOSE get_doc_cat_app;
269 RETURN(-1);
270
271 END;
272
273 FUNCTION get_cat_application(p_category_id IN NUMBER) RETURN NUMBER
274 IS
275 x_application_id NUMBER;
276 CURSOR get_app IS
277 SELECT application_id
278 FROM fnd_document_categories
279 WHERE category_id = p_category_id;
280 BEGIN
281 -- get category_application based on category_id
282 OPEN get_app;
283 FETCH get_app INTO x_application_id;
284 CLOSE get_app;
285
286 RETURN( NVL(x_application_id,-1));
287
288 EXCEPTION
289 WHEN OTHERS THEN CLOSE get_app;
290 RETURN(-1);
291 END;
292
293 PROCEDURE get_note_info (
294 p_media_id NUMBER,
295 p_short_long VARCHAR2,
296 p_document_id OUT NUMBER,
297 p_category_id OUT NUMBER,
298 p_usage_id OUT NUMBER,
299 p_usage_type OUT VARCHAR2, -- 'O' | 'S'
300 p_application_id OUT NUMBER
301 ) IS
302 x_category_id NUMBER;
303 x_application_id NUMBER;
304 CURSOR c1 IS
305 SELECT fd.document_id, fd.category_id,
306 fd.usage_type, fdc.application_id
307 FROM fnd_document_categories fdc,
308 fnd_documents_tl fdt,
309 fnd_documents fd
310 WHERE fdt.media_id = p_media_id
311 AND fdt.document_id = fd.document_id
312 AND fd.datatype_id = decode (p_short_long, 'S', 1, 2) --short/long
313 AND fd.category_id = fdc.category_id;
314 BEGIN
315 OPEN c1;
316 FETCH c1 INTO p_document_id, x_category_id,
317 p_usage_type, x_application_id;
318
319 CLOSE c1;
320 p_category_id := x_category_id;
321 p_application_id := NVL(x_application_id,-1);
322
323 IF (x_application_id = 300) THEN
324 get_usage_id (x_category_id, p_usage_id);
325 END IF;
326
327 RETURN;
328
329 EXCEPTION
330 WHEN OTHERS THEN
331 IF (c1%ISOPEN) THEN
332 CLOSE c1;
333 END IF;
334
335 p_application_id := -1;
336 RETURN;
337 END;
338
339 PROCEDURE get_note_type_code (
340 p_usage_type VARCHAR2,
341 p_note_type_code OUT VARCHAR2, -- 'SN' | 'OT'
342 p_override_flag OUT VARCHAR2 -- 'Y' | 'N'
343 ) IS
344 BEGIN
345 /*
346 ** document_usage_type ==> Note_Type_Code Override_Allowed_flag
347 ** ------------------- -------------- ---------------------
348 ** Template (T) Standard(SN) Y
349 ** Standard (S) Standard(SN) N
350 ** One-Time (O) One-Time(OT) Y
351 */
352
353 If (p_usage_type = 'T') Then
354 p_note_type_code := 'SN';
355 p_override_flag := 'Y';
356 ElsIf (p_usage_type = 'S') Then
357 p_note_type_code := 'SN';
358 p_override_flag := 'N';
359 ElsIf (p_usage_type = 'O') Then
360 p_note_type_code := 'OT';
361 p_override_flag := 'Y';
362 End If;
363
364 END;
365
366 PROCEDURE insert_document (
367 p_document_id NUMBER,
368 p_app_source_version VARCHAR2
369 ) IS
370
371 x_category_id NUMBER;
372 x_media_id NUMBER;
373 x_note_id NUMBER;
374 x_last_update_date DATE;
375 x_last_updated_by NUMBER;
376 x_last_update_login NUMBER;
377 x_creation_date DATE;
378 x_created_by NUMBER;
379 x_name VARCHAR2(30);
380 x_note_type_code VARCHAR2(30);
381 x_usage_id NUMBER;
382 x_override_flag VARCHAR2(1);
383 x_note VARCHAR2(32760);
384 x_start_date_active DATE;
385 x_end_date_active DATE;
386 x_context VARCHAR2(30);
387 x_attribute1 VARCHAR2(150);
388 x_attribute2 VARCHAR2(150);
389 x_attribute3 VARCHAR2(150);
390 x_attribute4 VARCHAR2(150);
391 x_attribute5 VARCHAR2(150);
392 x_attribute6 VARCHAR2(150);
393 x_attribute7 VARCHAR2(150);
394 x_attribute8 VARCHAR2(150);
395 x_attribute9 VARCHAR2(150);
396 x_attribute10 VARCHAR2(150);
397 x_attribute11 VARCHAR2(150);
398 x_attribute12 VARCHAR2(150);
399 x_attribute13 VARCHAR2(150);
400 x_attribute14 VARCHAR2(150);
401 x_attribute15 VARCHAR2(150);
402 x_datatype_id NUMBER;
403 x_document_usage_type VARCHAR2(1);
404
405
406 BEGIN
407
408 SELECT
409 note_id, last_update_date,
410 last_updated_by, last_update_login,
411 creation_date, created_by,
412 name, usage_id,
413 start_date_active, end_date_active,
414 context, attribute1,
415 attribute2, attribute3,
416 attribute4, attribute5,
417 attribute6, attribute7,
418 attribute8, attribute9,
419 attribute10, attribute11,
420 attribute12, attribute13,
421 attribute14, attribute15
422 INTO
423 x_note_id, x_last_update_date,
424 x_last_updated_by, x_last_update_login,
425 x_creation_date, x_created_by,
426 x_name, x_usage_id,
427 x_start_date_active, x_end_date_active,
428 x_context, x_attribute1,
429 x_attribute2, x_attribute3,
430 x_attribute4, x_attribute5,
431 x_attribute6, x_attribute7,
432 x_attribute8, x_attribute9,
433 x_attribute10, x_attribute11,
434 x_attribute12, x_attribute13,
435 x_attribute14, x_attribute15
436 FROM so_notes
437 WHERE document_id = p_document_id;
438
439 x_document_usage_type := get_document_usage_type (x_note_id);
440
441 BEGIN
442 SELECT note
443 INTO x_note
444 FROM so_notes
445 WHERE document_id = p_document_id;
446
447 x_datatype_id := 1; -- default is short text;
448
449 IF (x_note IS NULL) THEN
450 x_note := so_att.get_note_error('OE_NOT_UPDATE_NOTE');
451 ELSIF length (x_note) >= 2000 THEN
452 x_datatype_id := 2; -- long text
453 END IF;
454
455 EXCEPTION
456 WHEN VALUE_ERROR THEN -- long exceeds 32760
457 x_note := so_att.get_note_error('OE_NOT_NOTE_TOO_LONG',p_document_id);
458 x_datatype_id := 2;
459 WHEN OTHERS THEN
460 RAISE;
461 END;
462
463
464 get_category_id (x_usage_id, x_category_id);
465
466 INSERT INTO fnd_documents (
467 document_id, creation_date,
468 created_by, last_update_date,
469 last_updated_by, last_update_login,
470 datatype_id, category_id,
471 security_type, security_id,
472 publish_flag, storage_type,
473 usage_type, app_source_version,
474 start_date_active, end_date_active )
475 SELECT
476 p_document_id, x_creation_date,
477 x_created_by, sysdate,
478 1, 1,
479 x_datatype_id, x_category_id,
480 4, NULL,
481 'N', NULL,
482 x_document_usage_type, p_app_source_version,
483 x_start_date_active, x_end_date_active
484 FROM dual;
485
486 IF x_datatype_id = 1 THEN
487 SELECT fnd_documents_short_text_s.nextval
488 INTO x_media_id
489 FROM dual;
490 ELSE
491 SELECT fnd_documents_long_text_s.nextval
492 INTO x_media_id
493 FROM dual;
494 END IF;
495
496 INSERT INTO fnd_documents_tl (
497 document_id, creation_date,
498 created_by, last_update_date,
499 last_updated_by, last_update_login,
500 language, description,
501 file_name, media_id,
502 doc_attribute_category, doc_attribute1,
503 doc_attribute2, doc_attribute3,
504 doc_attribute4, doc_attribute5,
505 doc_attribute6, doc_attribute7,
506 doc_attribute8, doc_attribute9,
507 doc_attribute10, doc_attribute11,
508 doc_attribute12, doc_attribute13,
509 doc_attribute14, doc_attribute15,
510 app_source_version, source_lang )
511 SELECT
512 p_document_id, x_creation_date,
513 x_created_by, sysdate,
514 1, 1,
515 fnd_global.current_language, x_name,
516 NULL, x_media_id,
517 x_context, x_attribute1,
518 x_attribute2, x_attribute3,
519 x_attribute4, x_attribute5,
520 x_attribute6, x_attribute7,
521 x_attribute8, x_attribute9,
522 x_attribute10, x_attribute11,
523 x_attribute12, x_attribute13,
524 x_attribute14, x_attribute15,
525 p_app_source_version, fnd_global.current_language
526 FROM dual;
527
528 IF x_datatype_id = 1 THEN
529 INSERT INTO fnd_documents_short_text (
530 media_id, short_text, app_source_version )
531 SELECT
532 x_media_id, x_note, p_app_source_version
533 FROM dual;
534 ELSE
535 INSERT INTO fnd_documents_long_text (
536 media_id, long_text, app_source_version )
537 VALUES (
538 x_media_id, x_note, p_app_source_version);
539 END IF;
540
541 END;
542
543
544 PROCEDURE update_document (
545 p_document_id NUMBER,
546 p_app_source_version VARCHAR2
547 ) IS
548
549 x_category_id NUMBER;
550 x_media_id NUMBER;
551 x_note_id NUMBER;
552 x_last_update_date DATE;
553 x_last_updated_by NUMBER;
554 x_last_update_login NUMBER;
555 x_creation_date DATE;
556 x_created_by NUMBER;
557 x_name VARCHAR2(30);
558 x_note_type_code VARCHAR2(30);
559 x_usage_id NUMBER;
560 x_override_flag VARCHAR2(1);
561 x_note VARCHAR2(32760);
562 x_start_date_active DATE;
563 x_end_date_active DATE;
564 x_context VARCHAR2(30);
565 x_attribute1 VARCHAR2(150);
566 x_attribute2 VARCHAR2(150);
567 x_attribute3 VARCHAR2(150);
568 x_attribute4 VARCHAR2(150);
569 x_attribute5 VARCHAR2(150);
570 x_attribute6 VARCHAR2(150);
571 x_attribute7 VARCHAR2(150);
572 x_attribute8 VARCHAR2(150);
573 x_attribute9 VARCHAR2(150);
574 x_attribute10 VARCHAR2(150);
575 x_attribute11 VARCHAR2(150);
576 x_attribute12 VARCHAR2(150);
577 x_attribute13 VARCHAR2(150);
578 x_attribute14 VARCHAR2(150);
579 x_attribute15 VARCHAR2(150);
580 x_datatype_id NUMBER;
581 x_document_usage_type VARCHAR2(1);
582
583 BEGIN
584
585 SELECT
586 note_id, last_update_date,
587 last_updated_by, last_update_login,
588 creation_date, created_by,
589 name, usage_id, note_type_code,
590 start_date_active, end_date_active,
591 context, attribute1,
592 attribute2, attribute3,
593 attribute4, attribute5,
594 attribute6, attribute7,
595 attribute8, attribute9,
596 attribute10, attribute11,
597 attribute12, attribute13,
598 attribute14, attribute15,
599 override_allowed_flag
600 INTO
601 x_note_id, x_last_update_date,
602 x_last_updated_by, x_last_update_login,
603 x_creation_date, x_created_by,
604 x_name, x_usage_id, x_note_type_code,
605 x_start_date_active, x_end_date_active,
606 x_context, x_attribute1,
607 x_attribute2, x_attribute3,
608 x_attribute4, x_attribute5,
609 x_attribute6, x_attribute7,
610 x_attribute8, x_attribute9,
611 x_attribute10, x_attribute11,
612 x_attribute12, x_attribute13,
613 x_attribute14, x_attribute15,
614 x_override_flag
615 FROM so_notes
616 WHERE
617 document_id = p_document_id;
618
619 x_document_usage_type := get_document_usage_type (x_note_id);
620
621 BEGIN
622 SELECT note
623 INTO x_note
624 FROM so_notes
625 WHERE document_id = p_document_id;
626
627 EXCEPTION
628 WHEN VALUE_ERROR THEN -- long exceeds 32760
629 x_note := so_att.get_note_error('OE_NOT_NOTE_TOO_LONG',p_document_id);
630 WHEN OTHERS THEN
631 RAISE;
632 END;
633
634 get_category_id(x_usage_id, x_category_id);
635
636 UPDATE fnd_documents
637 SET app_source_version = 'SO_R10',
638 category_id = x_category_id,
639 usage_type = DECODE(x_note_type_code,'OT','O',
640 DECODE(x_override_flag,'Y','T',
641 'S')),
642 start_date_active = x_start_date_active,
643 end_date_active = x_end_date_active
644 WHERE document_id = p_document_id;
645
646 UPDATE fnd_documents_tl
647 SET app_source_version = 'SO_R10',
648 description = x_name,
649 doc_attribute_category = x_context,
650 doc_attribute1 = x_attribute1,
651 doc_attribute2 = x_attribute2,
652 doc_attribute3 = x_attribute3,
653 doc_attribute4 = x_attribute4,
654 doc_attribute5 = x_attribute5,
655 doc_attribute6 = x_attribute6,
656 doc_attribute7 = x_attribute7,
657 doc_attribute8 = x_attribute8,
658 doc_attribute9 = x_attribute9,
659 doc_attribute10 = x_attribute10,
660 doc_attribute11 = x_attribute11,
661 doc_attribute12 = x_attribute12,
662 doc_attribute13 = x_attribute13,
663 doc_attribute14 = x_attribute14,
664 doc_attribute15 = x_attribute15
665 WHERE document_id = p_document_id
666 AND language = fnd_global.current_language;
667
668 get_media_id (p_document_id, x_media_id, x_datatype_id);
669
670 IF x_datatype_id = 1 THEN
671 -- we could check if note >= 2000 even datatype_id=1, and set x_note to
672 -- 'note truncated'|| substr(x_note,1,1900). leave it this way for now.
673
674 UPDATE fnd_documents_short_text
675 SET app_source_version = 'SO_R10',
676 short_text = substr (x_note, 1, 1998)
677 WHERE media_id = x_media_id;
678 ELSE
679 UPDATE fnd_documents_long_text
680 SET app_source_version = 'SO_R10',
681 long_text = x_note
682 WHERE media_id = x_media_id;
683 END IF;
684
685 END;
686
687
688 PROCEDURE delete_document (
689 p_document_id NUMBER
690 ) IS
691
692 x_media_id NUMBER;
693 x_datatype_id NUMBER;
694
695 BEGIN
696
697 get_media_id (p_document_id, x_media_id, x_datatype_id);
698
699 DELETE FROM fnd_documents
700 WHERE document_id = p_document_id;
701
702 DELETE FROM fnd_documents_tl
703 WHERE document_id = p_document_id;
704
705 IF x_datatype_id = 1 THEN
706 DELETE FROM fnd_documents_short_text
707 WHERE media_id = x_media_id;
708 ELSE
709 DELETE FROM fnd_documents_long_text
710 WHERE media_id = x_media_id;
711 END IF;
712
713 END;
714
715
716 PROCEDURE insert_attached_document (
717 p_creation_date DATE,
718 p_created_by NUMBER,
719 p_note_id NUMBER,
720 p_usage_id NUMBER,
721 p_automatically_added_flag VARCHAR2,
722 p_header_id NUMBER,
723 p_line_id NUMBER,
724 p_program_application_id NUMBER,
725 p_program_id NUMBER,
726 p_program_update_date DATE,
727 p_request_id NUMBER,
728 p_sequence_number NUMBER,
729 p_context VARCHAR2,
730 p_attribute1 VARCHAR2,
731 p_attribute2 VARCHAR2,
732 p_attribute3 VARCHAR2,
733 p_attribute4 VARCHAR2,
734 p_attribute5 VARCHAR2,
735 p_attribute6 VARCHAR2,
736 p_attribute7 VARCHAR2,
737 p_attribute8 VARCHAR2,
738 p_attribute9 VARCHAR2,
739 p_attribute10 VARCHAR2,
740 p_attribute11 VARCHAR2,
741 p_attribute12 VARCHAR2,
742 p_attribute13 VARCHAR2,
743 p_attribute14 VARCHAR2,
744 p_attribute15 VARCHAR2,
745 p_app_source_version VARCHAR2,
746 p_attached_document_id IN OUT NUMBER
747 ) IS
748
749 x_document_id NUMBER;
750 x_entity_name fnd_attached_documents.entity_name%TYPE;
751
752 BEGIN
753
754 SELECT document_id
755 INTO x_document_id
756 FROM so_notes
757 WHERE note_id = p_note_id;
758
759 x_entity_name := get_entity_name(p_header_id, p_line_id);
760
761 SELECT fnd_attached_documents_s.nextval
762 INTO p_attached_document_id
763 FROM dual;
764
765 INSERT INTO fnd_attached_documents (
766 attached_document_id, document_id,
767 creation_date, created_by,
768 last_update_date, last_updated_by,
769 last_update_login, seq_num,
770 entity_name, pk1_value,
771 pk2_value,
772 program_application_id, program_id,
773 program_update_date, request_id,
774 automatically_added_flag, attribute_category,
775 attribute1, attribute2,
776 attribute3, attribute4,
777 attribute5, attribute6,
778 attribute7, attribute8,
779 attribute9, attribute10,
780 attribute11, attribute12,
781 attribute13, attribute14,
782 attribute15, app_source_version )
783 SELECT
784 p_attached_document_id, x_document_id,
785 p_creation_date, p_created_by,
786 sysdate, 1,
787 1, p_sequence_number,
788 x_entity_name, to_char(p_header_id),
789 to_char(p_line_id),
790 p_program_application_id, p_program_id,
791 p_program_update_date, p_request_id,
792 p_automatically_added_flag, p_context,
793 p_attribute1, p_attribute2,
794 p_attribute3, p_attribute4,
795 p_attribute5, p_attribute6,
796 p_attribute7, p_attribute8,
797 p_attribute9, p_attribute10,
798 p_attribute11, p_attribute12,
799 p_attribute13, p_attribute14,
800 p_attribute15, p_app_source_version
801 FROM dual;
802
803 END;
804
805 PROCEDURE update_attached_document (
806 p_attached_document_id NUMBER,
807 p_creation_date DATE,
808 p_created_by NUMBER,
809 p_note_id NUMBER,
810 p_usage_id NUMBER,
811 p_automatically_added_flag VARCHAR2,
812 p_header_id NUMBER,
813 p_line_id NUMBER,
814 p_program_application_id NUMBER,
815 p_program_id NUMBER,
816 p_program_update_date DATE,
817 p_request_id NUMBER,
818 p_sequence_number NUMBER,
819 p_context VARCHAR2,
820 p_attribute1 VARCHAR2,
821 p_attribute2 VARCHAR2,
822 p_attribute3 VARCHAR2,
823 p_attribute4 VARCHAR2,
824 p_attribute5 VARCHAR2,
825 p_attribute6 VARCHAR2,
826 p_attribute7 VARCHAR2,
827 p_attribute8 VARCHAR2,
828 p_attribute9 VARCHAR2,
829 p_attribute10 VARCHAR2,
830 p_attribute11 VARCHAR2,
831 p_attribute12 VARCHAR2,
832 p_attribute13 VARCHAR2,
833 p_attribute14 VARCHAR2,
834 p_attribute15 VARCHAR2,
835 p_app_source_version VARCHAR2
836 ) IS
837
838 x_document_id NUMBER;
839
840 BEGIN
841
842 SELECT document_id
843 INTO x_document_id
844 FROM so_notes
845 WHERE note_id = p_note_id;
846
847 -- you can only update flex fields and sequence number.
848 UPDATE fnd_attached_documents
849 SET last_update_date = SYSDATE,
850 seq_num = p_sequence_number,
851 document_id = x_document_id,
852 attribute_category = p_context,
853 attribute1 = p_attribute1,
854 attribute2 = p_attribute2,
855 attribute3 = p_attribute3,
856 attribute4 = p_attribute4,
857 attribute5 = p_attribute5,
858 attribute6 = p_attribute6,
859 attribute7 = p_attribute7,
860 attribute8 = p_attribute8,
861 attribute9 = p_attribute9,
862 attribute10 = p_attribute10,
863 attribute11 = p_attribute11,
864 attribute12 = p_attribute12,
865 attribute13 = p_attribute13,
866 attribute14 = p_attribute14,
867 attribute15 = p_attribute15,
868 app_source_version = 'SO_R10'
869 WHERE attached_document_id = p_attached_document_id;
870
871 END;
872
873 PROCEDURE delete_attached_document (
874 p_attached_document_id NUMBER
875 ) IS
876
877 BEGIN
878
879 DELETE FROM fnd_attached_documents
880 WHERE attached_document_id = p_attached_document_id;
881
882 END;
883
884
885 PROCEDURE insert_category (
886 p_creation_date DATE,
887 p_created_by NUMBER,
888 p_last_update_date DATE,
889 p_last_updated_by NUMBER,
890 p_last_update_login NUMBER,
891 p_name VARCHAR2,
892 p_description VARCHAR2,
893 p_start_date_active DATE,
894 p_end_date_active DATE,
895 p_context VARCHAR2,
896 p_attribute1 VARCHAR2,
897 p_attribute2 VARCHAR2,
898 p_attribute3 VARCHAR2,
899 p_attribute4 VARCHAR2,
900 p_attribute5 VARCHAR2,
901 p_attribute6 VARCHAR2,
902 p_attribute7 VARCHAR2,
903 p_attribute8 VARCHAR2,
904 p_attribute9 VARCHAR2,
905 p_attribute10 VARCHAR2,
906 p_attribute11 VARCHAR2,
907 p_attribute12 VARCHAR2,
908 p_attribute13 VARCHAR2,
909 p_attribute14 VARCHAR2,
910 p_attribute15 VARCHAR2,
911 p_app_source_version VARCHAR2,
912 p_category_id IN OUT NUMBER
913 ) IS
914 attach_function_id NUMBER;
915 BEGIN
916
917 SELECT fnd_document_categories_s.nextval
918 INTO p_category_id
919 FROM dual;
920
921 INSERT INTO fnd_document_categories (
922 category_id,
923 application_id, creation_date,
924 created_by, last_update_date,
925 last_updated_by, last_update_login,
926 name,
927 start_date_active, end_date_active,
928 default_datatype_id,
929 attribute_category,
930 attribute1, attribute2,
931 attribute3, attribute4,
932 attribute5, attribute6,
933 attribute7, attribute8,
934 attribute9, attribute10,
935 attribute11, attribute12,
936 attribute13, attribute14,
937 attribute15)
938 SELECT
939 p_category_id,
940 300, p_creation_date,
941 p_created_by, p_last_update_date,
942 p_last_updated_by, p_last_update_login,
943 p_name,
944 p_start_date_active, p_end_date_active,
945 1,
946 p_context,
947 p_attribute1, p_attribute2,
948 p_attribute3, p_attribute4,
949 p_attribute5, p_attribute6,
950 p_attribute7, p_attribute8,
951 p_attribute9, p_attribute10,
952 p_attribute11, p_attribute12,
953 p_attribute13, p_attribute14,
954 p_attribute15
955 FROM dual;
956
957 INSERT INTO fnd_document_categories_tl (
958 category_id, language,
959 name, user_name,
960 creation_date,
961 created_by, last_update_date,
962 last_updated_by, last_update_login,
963 app_source_version, source_lang )
964 SELECT
965 p_category_id, fnd_global.current_language,
966 p_name, p_name,
967 p_creation_date,
968 p_created_by, p_last_update_date,
969 p_last_updated_by, p_last_update_login,
970 p_app_source_version, fnd_global.current_language
971 FROM dual;
972
973 -- lookup the attachment_function_id of the OEXOEMOE form
974 SELECT attachment_function_id
975 INTO attach_function_id
976 FROM fnd_attachment_functions
977 WHERE function_name = 'OEXOEMOE'
978 AND function_type = 'O';
979
980 -- create fnd_doc_category_usages record to link category
981 -- to the OEXOEMOE form so documents with this category
982 -- can be viewed in 10SC
983 INSERT INTO fnd_doc_category_usages (
984 doc_category_usage_id,
985 category_id,
986 attachment_function_id, enabled_flag,
987 creation_date, created_by,
988 last_update_date, last_updated_by, last_update_login)
989 VALUES (
990 fnd_doc_category_usages_s.nextval,
991 p_category_id,
992 attach_function_id, 'Y',
993 SYSDATE, 1,
994 SYSDATE, 1, 1);
995
996 END;
997
998 PROCEDURE update_category (
999 p_category_id NUMBER,
1000 p_creation_date DATE,
1001 p_created_by NUMBER,
1002 p_last_update_date DATE,
1003 p_last_updated_by NUMBER,
1004 p_last_update_login NUMBER,
1005 p_name VARCHAR2,
1006 p_description VARCHAR2,
1007 p_start_date_active DATE,
1008 p_end_date_active DATE,
1009 p_context VARCHAR2,
1010 p_attribute1 VARCHAR2,
1011 p_attribute2 VARCHAR2,
1012 p_attribute3 VARCHAR2,
1013 p_attribute4 VARCHAR2,
1014 p_attribute5 VARCHAR2,
1015 p_attribute6 VARCHAR2,
1016 p_attribute7 VARCHAR2,
1017 p_attribute8 VARCHAR2,
1018 p_attribute9 VARCHAR2,
1019 p_attribute10 VARCHAR2,
1020 p_attribute11 VARCHAR2,
1021 p_attribute12 VARCHAR2,
1022 p_attribute13 VARCHAR2,
1023 p_attribute14 VARCHAR2,
1024 p_attribute15 VARCHAR2,
1025 p_app_source_version VARCHAR2
1026 ) IS
1027
1028 BEGIN
1029
1030 UPDATE fnd_document_categories
1031 SET last_update_date = p_last_update_date,
1032 last_updated_by = p_last_updated_by,
1033 last_update_login = p_last_update_login,
1034 name = p_name,
1035 start_date_active = p_start_date_active,
1036 end_date_active = p_end_date_active,
1037 attribute_category = p_context,
1038 attribute1 = p_attribute1,
1039 attribute2 = p_attribute2,
1040 attribute3 = p_attribute3,
1041 attribute4 = p_attribute4,
1042 attribute5 = p_attribute5,
1043 attribute6 = p_attribute6,
1044 attribute7 = p_attribute7,
1045 attribute8 = p_attribute8,
1046 attribute9 = p_attribute9,
1047 attribute10 = p_attribute10,
1048 attribute11 = p_attribute11,
1049 attribute12 = p_attribute12,
1050 attribute13 = p_attribute13,
1051 attribute14 = p_attribute14,
1052 attribute15 = p_attribute15
1053 WHERE category_id = p_category_id;
1054
1055 UPDATE fnd_document_categories_tl
1056 SET last_update_date = p_last_update_date,
1057 last_updated_by = p_last_updated_by,
1058 last_update_login = p_last_update_login,
1059 name = p_name,
1060 user_name = p_name,
1061 app_source_version = 'SO_R10'
1062 WHERE category_id = p_category_id;
1063
1064 END;
1065
1066 PROCEDURE insert_usage (
1067 p_creation_date DATE,
1068 p_created_by NUMBER,
1069 p_last_update_date DATE,
1070 p_last_updated_by NUMBER,
1071 p_last_update_login NUMBER,
1072 p_name VARCHAR2,
1073 p_user_name VARCHAR2,
1074 p_category_id NUMBER,
1075 p_app_source_version VARCHAR2,
1076 p_usage_id IN OUT NUMBER
1077 ) IS
1078 CURSOR c IS
1079 SELECT 1
1080 FROM so_note_usages
1081 WHERE name = substr(p_user_name,1,15);
1082 dummy number;
1083 BEGIN
1084
1085 -- so_note_usages.name is enforced to be unique
1086 -- by R10
1087 OPEN C;
1088 FETCH C INTO dummy;
1089 IF (C%FOUND) THEN
1090 CLOSE C;
1091 RAISE DUP_VAL_ON_INDEX;
1092 RETURN;
1093 END IF;
1094
1095 CLOSE C;
1096 SELECT so_note_usages_s.nextval
1097 INTO p_usage_id
1098 FROM dual;
1099
1100
1101
1102 INSERT INTO so_note_usages (
1103 usage_id, creation_date,
1104 created_by, last_update_date,
1105 last_updated_by, last_update_login,
1106 name, description,
1107 start_date_active, end_date_active,
1108 context,
1109 attribute1, attribute2,
1110 attribute3, attribute4,
1111 attribute5, attribute6,
1112 attribute7, attribute8,
1113 attribute9, attribute10,
1114 attribute11, attribute12,
1115 attribute13, attribute14,
1116 attribute15,
1117 category_id, app_source_version )
1118 SELECT
1119 p_usage_id, p_creation_date,
1120 p_created_by, p_last_update_date,
1121 p_last_updated_by, p_last_update_login,
1122 SUBSTR(p_user_name,1,15), SUBSTR(p_user_name,1,80),
1123 fdc.start_date_active, fdc.end_date_active,
1124 fdc.attribute_category,
1125 fdc.attribute1, fdc.attribute2,
1126 fdc.attribute3, fdc.attribute4,
1127 fdc.attribute5, fdc.attribute6,
1128 fdc.attribute7, fdc.attribute8,
1129 fdc.attribute9, fdc.attribute10,
1130 fdc.attribute11, fdc.attribute12,
1131 fdc.attribute13, fdc.attribute14,
1132 fdc.attribute15,
1133 p_category_id, p_app_source_version
1134 FROM fnd_document_categories fdc
1135 WHERE category_id = p_category_id;
1136
1137
1138 END;
1139
1140 PROCEDURE update_usage (
1141 p_category_id NUMBER,
1142 p_creation_date DATE,
1143 p_created_by NUMBER,
1144 p_last_update_date DATE,
1145 p_last_updated_by NUMBER,
1146 p_last_update_login NUMBER,
1147 p_name VARCHAR2,
1148 p_user_name VARCHAR2,
1149 p_app_source_version VARCHAR2
1150 ) IS
1151 x_start_date_active DATE;
1152 x_end_date_active DATE;
1153 x_attribute_category VARCHAR2(30);
1154 x_attribute1 VARCHAR2(150);
1155 x_attribute2 VARCHAR2(150);
1156 x_attribute3 VARCHAR2(150);
1157 x_attribute4 VARCHAR2(150);
1158 x_attribute5 VARCHAR2(150);
1159 x_attribute6 VARCHAR2(150);
1160 x_attribute7 VARCHAR2(150);
1161 x_attribute8 VARCHAR2(150);
1162 x_attribute9 VARCHAR2(150);
1163 x_attribute10 VARCHAR2(150);
1164 x_attribute11 VARCHAR2(150);
1165 x_attribute12 VARCHAR2(150);
1166 x_attribute13 VARCHAR2(150);
1167 x_attribute14 VARCHAR2(150);
1168 x_attribute15 VARCHAR2(150);
1169 CURSOR C IS
1170 SELECT 1
1171 FROM so_note_usages
1172 WHERE name = substr(p_user_name,1,15)
1173 AND category_id <> p_category_id;
1174 dummy number;
1175 BEGIN
1176
1177 -- check for violation of the application-enforced unique
1178 -- key in R10
1179 OPEN c;
1180 FETCH c INTO dummy;
1181 IF (c%FOUND) THEN
1182 CLOSE c;
1183 RAISE DUP_VAL_ON_INDEX;
1184 RETURN;
1185 END IF;
1186
1187 CLOSE c;
1188
1189 SELECT start_date_active, end_date_active,
1190 attribute_category,
1191 attribute1, attribute2,
1192 attribute3, attribute4,
1193 attribute5, attribute6,
1194 attribute7, attribute8,
1195 attribute9, attribute10,
1196 attribute11, attribute12,
1197 attribute13, attribute14,
1198 attribute15
1199 INTO
1200 x_start_date_active, x_end_date_active,
1201 x_attribute_category,
1202 x_attribute1, x_attribute2,
1203 x_attribute3, x_attribute4,
1204 x_attribute5, x_attribute6,
1205 x_attribute7, x_attribute8,
1206 x_attribute9, x_attribute10,
1207 x_attribute11, x_attribute12,
1208 x_attribute13, x_attribute14,
1209 x_attribute15
1210 FROM fnd_document_categories
1211 WHERE category_id = p_category_id;
1212
1213 UPDATE so_note_usages
1214 SET last_update_date = p_last_update_date,
1215 last_updated_by = p_last_updated_by,
1216 last_update_login = p_last_update_login,
1217 name = SUBSTR(p_user_name,1,15),
1218 start_date_active = x_start_date_active,
1219 end_date_active = x_end_date_active,
1220 context = x_attribute_category,
1221 attribute1 = x_attribute1,
1222 attribute2 = x_attribute2,
1223 attribute3 = x_attribute3,
1224 attribute4 = x_attribute4,
1225 attribute5 = x_attribute5,
1226 attribute6 = x_attribute6,
1227 attribute7 = x_attribute7,
1228 attribute8 = x_attribute8,
1229 attribute9 = x_attribute9,
1230 attribute10 = x_attribute10,
1231 attribute11 = x_attribute11,
1232 attribute12 = x_attribute12,
1233 attribute13 = x_attribute13,
1234 attribute14 = x_attribute14,
1235 attribute15 = x_attribute15,
1236 app_source_version = 'SO_R10'
1237 WHERE category_id = p_category_id;
1238
1239 END;
1240
1241 END;