[Home] [Help]
PACKAGE BODY: APPS.PO_ATT
Source
1 PACKAGE BODY po_att AS
2 /* $Header: poatt04b.pls 120.1 2006/02/23 04:01:05 aagupta noship $ */
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 -- 'PO_10SC' | 'PO_R10'
9 ) IS
10
11 BEGIN
12
13 INSERT INTO po_att_tmp_records (
14 src_id,
15 short_long,
16 source,
17 operation,
18 version
19 ) VALUES (
20 p_src_id,
21 p_short_long,
22 p_source,
23 p_operation,
24 p_version
25 );
26
27 END;
28
29 PROCEDURE clear_mark (
30 p_short_long VARCHAR2, -- 'S' | 'L'
31 p_source VARCHAR2,-- 'DOCUMENT' | 'NOTE'
32 p_operation VARCHAR2,-- 'INSERT' | 'UPDATE'
33 p_version VARCHAR2 -- 'PO_10SC' | 'PO_R10'
34 ) IS
35
36 BEGIN
37
38 DELETE FROM po_att_tmp_records;
39 /* all tmp records should be deleted */
40
41 END;
42
43
44 FUNCTION get_table_name (
45 p_entity_name VARCHAR2
46 ) RETURN VARCHAR2 IS
47 BEGIN
48
49 IF p_entity_name = 'REQ_HEADERS' THEN return 'PO_REQUISITION_HEADERS';
50 ELSIF p_entity_name = 'REQ_LINES' THEN return 'PO_REQUISITION_LINES';
51 ELSIF p_entity_name = 'RCV_LINES' THEN return 'RCV_SHIPMENT_LINES';
52 ELSIF p_entity_name = 'RCV_TRANSACTIONS' THEN return 'RCV_TRANSACTIONS';
53 ELSIF p_entity_name = 'RCV_TRANSACTIONS_INTERFACE' THEN return 'RCV_TRANSACTIONS_INTERFACE';
54 ELSIF p_entity_name = 'PO_HEADERS' THEN return 'PO_HEADERS';
55 ELSIF p_entity_name = 'PO_LINES' THEN return 'PO_LINES';
56 ELSIF p_entity_name = 'PO_RELEASES' THEN return 'PO_RELEASES';
57 ELSIF p_entity_name = 'PO_SHIPMENTS' THEN return 'PO_LINE_LOCATIONS';
58 ELSIF p_entity_name = 'RCV_HEADERS' THEN return 'RCV_SHIPMENT_HEADERS';
59 ELSIF p_entity_name = 'MTL_SYSTEM_ITEMS' THEN return 'MTL_SYSTEM_ITEMS';
60 ELSIF p_entity_name = 'PO_VENDORS' THEN return 'PO_VENDORS';
61 ELSE return 'NOT_PO_TABLE';
62 END IF;
63
64 END;
65
66 FUNCTION get_table_name (
67 p_document_id NUMBER
68 ) RETURN VARCHAR2 IS
69 x_table_name VARCHAR2(30);
70 BEGIN
71
72 -- the following select may return more than one record. use this api
73 -- only you are sure there is only one return. for instance, when
74 -- insert a new attachment.
75 BEGIN
76 SELECT decode ( entity_name,
77 'REQ_HEADERS', 'PO_REQUISITION_HEADERS',
78 'REQ_LINES', 'PO_REQUISITION_LINES',
79 'RCV_LINES', 'RCV_SHIPMENT_LINES',
80 'RCV_TRANSACTIONS', 'RCV_TRANSACTIONS',
81 'RCV_TRANSACTIONS_INTERFACE', 'RCV_TRANSACTIONS_INTERFACE',
82 'PO_HEADERS', 'PO_HEADERS',
83 'PO_LINES', 'PO_LINES',
84 'PO_RELEASES', 'PO_RELEASES',
85 'PO_SHIPMENTS', 'PO_LINE_LOCATIONS',
86 'RCV_HEADERS', 'RCV_SHIPMENT_HEADERS',
87 'MTL_SYSTEM_ITEMS', 'MTL_SYSTEM_ITEMS',
88 'PO_VENDORS', 'PO_VENDORS',
89 'NOT_PO_TABLE' )
90 INTO x_table_name
91 FROM fnd_attached_documents
92 WHERE document_id = p_document_id
93 AND rownum < 2; -- make sure zero or one record returned
94
95 EXCEPTION
96 WHEN NO_DATA_FOUND THEN
97 x_table_name := 'NOT_ATTACHED';
98 END;
99
100 RETURN x_table_name;
101 END;
102
103 FUNCTION get_column_name (
104 p_entity_name VARCHAR2
105 ) RETURN VARCHAR2 IS
106 x_column_name VARCHAR2(30);
107 BEGIN
108
109 -- the following select may return more than one record. use this api
110 -- only you are sure there is only one return. for instance, when
111 -- insert a new attachment.
112 IF p_entity_name = 'REQ_HEADERS' THEN return 'REQUISITION_HEADER_ID';
113 ELSIF p_entity_name = 'REQ_LINES' THEN return 'REQUISITION_LINE_ID';
114 ELSIF p_entity_name = 'RCV_LINES' THEN return 'SHIPMENT_LINE_ID';
115 ELSIF p_entity_name = 'RCV_TRANSACTIONS' THEN return 'TRANSACTION_ID';
116 ELSIF p_entity_name = 'RCV_TRANSACTIONS_INTERFACE' THEN RETURN 'INTERFACE_TRANSACTION_ID';
117 ELSIF p_entity_name = 'PO_HEADERS' THEN RETURN 'PO_HEADER_ID';
118 ELSIF p_entity_name = 'PO_LINES' THEN RETURN 'PO_LINE_ID';
119 ELSIF p_entity_name = 'PO_RELEASES' THEN RETURN 'PO_RELEASE_ID';
120 ELSIF p_entity_name = 'PO_SHIPMENTS' THEN RETURN 'LINE_LOCATION_ID';
121 ELSIF p_entity_name = 'RCV_HEADERS' THEN RETURN 'SHIPMENT_HEADER_ID';
122 ELSIF p_entity_name = 'MTL_SYSTEM_ITEMS' THEN RETURN 'INVENTORY_ITEM_ID';
123 ELSIF p_entity_name = 'PO_VENDORS' THEN RETURN 'VENDOR_ID';
124 ELSE RETURN 'NOT_PO_COLUMN';
125 END IF;
126
127 END;
128
129
130 FUNCTION get_column_name (
131 p_document_id NUMBER
132 ) RETURN VARCHAR2 IS
133 x_column_name VARCHAR2(30);
134 BEGIN
135
136 BEGIN
137 SELECT decode ( entity_name,
138 'REQ_HEADERS', 'REQUISITION_HEADER_ID',
139 'REQ_LINES', 'REQUISITION_LINE_ID',
140 'RCV_LINES', 'SHIPMENT_LINE_ID',
141 'RCV_TRANSACTIONS', 'TRANSACTION_ID',
142 'RCV_TRANSACTIONS_INTERFACE', 'INTERFACE_TRANSACTION_ID',
143 'PO_HEADERS', 'PO_HEADER_ID',
144 'PO_LINES', 'PO_LINE_ID',
145 'PO_RELEASES', 'PO_RELEASE_ID',
146 'PO_SHIPMENTS', 'LINE_LOCATION_ID',
147 'RCV_HEADERS', 'SHIPMENT_HEADER_ID',
148 'MTL_SYSTEM_ITEMS', 'INVENTORY_ITEM_ID',
149 'PO_VENDORS', 'VENDOR_ID',
150 'NOT_PO_COLUMN' )
151 INTO x_column_name
152 FROM fnd_attached_documents
153 WHERE document_id = p_document_id
154 AND rownum < 2;
155
156 EXCEPTION
157 WHEN NO_DATA_FOUND THEN
158 x_column_name := 'NOT_ATTACHED';
159 END;
160
161 RETURN x_column_name;
162 END;
163
164
165 FUNCTION get_entity_name (
166 p_table_name VARCHAR2
167 ) RETURN VARCHAR2 IS
168 x_entity_name VARCHAR2(40);
169 BEGIN
170
171 IF p_table_name='PO_HEADERS' THEN RETURN 'PO_HEADERS';
172 ELSIF p_table_name='PO_LINES' THEN RETURN 'PO_LINES';
173 ELSIF p_table_name='PO_LINE_LOCATIONS' THEN RETURN 'PO_SHIPMENTS';
174 ELSIF p_table_name='PO_RELEASES' THEN RETURN 'PO_RELEASES';
175 ELSIF p_table_name='PO_REQUISITION_HEADERS' THEN RETURN 'REQ_HEADERS';
176 ELSIF p_table_name='PO_REQUISITION_LINES' THEN RETURN 'REQ_LINES';
177 ELSIF p_table_name='RCV_SHIPMENT_HEADERS' THEN RETURN 'RCV_HEADERS';
178 ELSIF p_table_name='RCV_SHIPMENT_LINES' THEN RETURN 'RCV_LINES';
179 ELSIF p_table_name='RCV_TRANSACTIONS' THEN RETURN 'RCV_TRANSACTIONS';
180 ELSIF p_table_name='RCV_TRANSACTIONS_INTERFACE' THEN RETURN 'RCV_TRANSACTIONS_INTERFACE';
181 ELSIF p_table_name='MTL_SYSTEM_ITEMS' THEN RETURN 'MTL_SYSTEM_ITEMS';
182 ELSIF p_table_name = 'PO_VENDORS' THEN return 'PO_VENDORS';
183 ELSE RETURN 'NOT_PO_ENTITY';
184 END IF;
185
186 END;
187
188
189 PROCEDURE get_category_id (
190 p_usage_id NUMBER,
191 p_category_id OUT NOCOPY NUMBER
192 ) IS
193 BEGIN
194 SELECT fdc.category_id
195 INTO p_category_id
196 FROM fnd_document_categories fdc
197 WHERE upper(fdc.name) = decode(p_usage_id, 2, 'VENDOR' ,
198 3, 'BUYER' ,
199 4, 'RECEIVER' ,
200 5, 'APPROVER' ,
201 6, 'REQ INTERNAL' ,
202 7, 'PO INTERNAL' ,
203 8, 'RFQ INTERNAL' ,
204 9, 'QUOTE INTERNAL' ,
205 10, 'ITEM INTERNAL' ,
206 11, 'RCV INTERNAL' ,
207 12, 'INVOICE INTERNAL' ,
208 13, 'PAYABLES');
209
210 EXCEPTION
211 WHEN NO_DATA_FOUND THEN
212 p_category_id := -1;
213 END;
214
215
216 PROCEDURE get_usage_id (
217 p_category_id NUMBER,
218 p_usage_id OUT NOCOPY NUMBER
219 ) IS
220
221 usg_name VARCHAR2(30);
222
223 BEGIN
224
225 SELECT decode(upper(fdc.name),'VENDOR' ,2,
226 'BUYER' ,3,
227 'RECEIVER' ,4,
228 'APPROVER' ,5,
229 'REQ INTERNAL' ,6,
230 'PO INTERNAL' ,7,
231 'RFQ INTERNAL' ,8,
232 'QUOTE INTERNAL' ,9,
233 'ITEM INTERNAL' ,10,
234 'RCV INTERNAL' ,11,
235 'INVOICE INTERNAL' ,12,
236 'PAYABLES' ,13)
237
238 INTO p_usage_id
239 FROM fnd_document_categories fdc
240 WHERE fdc.category_id = p_category_id;
241
242 EXCEPTION
243 WHEN NO_DATA_FOUND THEN
244 p_usage_id := -1;
245 END;
246
247
248 PROCEDURE get_media_id (
249 p_document_id NUMBER,
250 p_media_id OUT NOCOPY NUMBER,
251 p_datatype_id OUT NOCOPY NUMBER
252 ) IS
253 BEGIN
254
255 SELECT fd.datatype_id, fd.media_id
256 INTO p_datatype_id, p_media_id
257 FROM fnd_documents_tl fdt,
258 fnd_documents fd
259 WHERE fdt.language = userenv('LANG')
260 AND fdt.document_id = fd.document_id
261 AND fd.document_id = p_document_id;
262
263 END;
264
265
266 PROCEDURE get_document_id (
267 p_media_id NUMBER,
268 p_datatype_id VARCHAR2,
269 p_document_id OUT NOCOPY NUMBER
270 ) IS
271 BEGIN
272
273 SELECT fd.document_id
274 INTO p_document_id
275 FROM fnd_documents_tl fdt,
276 fnd_documents fd
277 WHERE fd.media_id = p_media_id
278 AND fdt.document_id = fd.document_id
279 AND fd.datatype_id = decode (p_datatype_id, 'S', 1, 2) --short/long
280 AND rownum = 1; -- make sure only one record returns
281
282 END;
283
284
285 PROCEDURE get_note_info (
286 p_media_id NUMBER,
287 p_short_long VARCHAR2,
288 p_document_id OUT NOCOPY NUMBER,
289 p_category_id OUT NOCOPY NUMBER,
290 p_usage_id OUT NOCOPY NUMBER,
291 p_note_type OUT NOCOPY VARCHAR2 -- 'O' | 'S'
292 ) IS
293
294 x_num NUMBER;
295 x_num2 NUMBER;
296
297 BEGIN
298
299 get_document_id (p_media_id, p_short_long, x_num);
300
301 p_document_id := x_num;
302
303 SELECT category_id, usage_type
304 INTO x_num2, p_note_type
305 FROM fnd_documents
306 WHERE document_id = x_num;
307
308 p_category_id := x_num2;
309
310 get_usage_id (x_num2, p_usage_id);
311 /* p_usage_id = -1 if no corresponding po usages found */
312 END;
313
314
315 PROCEDURE insert_document (
316 p_note_id NUMBER,
317 p_app_source_version VARCHAR2
318 ) IS
319
320 x_category_id NUMBER;
321 x_media_id NUMBER;
322 x_po_note_id NUMBER;
323 x_last_update_date DATE;
324 x_last_updated_by NUMBER;
325 x_last_update_login NUMBER;
326 x_creation_date DATE;
327 x_created_by NUMBER;
328 x_title VARCHAR2(80);
329 x_usage_id NUMBER;
330 x_note_type VARCHAR2(25);
331 x_note VARCHAR2(32760);
332 x_start_date_active DATE;
333 x_end_date_active DATE;
334 x_request_id NUMBER;
335 x_program_application_id NUMBER;
336 x_program_id NUMBER;
337 x_program_update_date DATE;
338 x_attribute_category VARCHAR2(30);
339 x_attribute1 VARCHAR2(150);
340 x_attribute2 VARCHAR2(150);
341 x_attribute3 VARCHAR2(150);
342 x_attribute4 VARCHAR2(150);
343 x_attribute5 VARCHAR2(150);
344 x_attribute6 VARCHAR2(150);
345 x_attribute7 VARCHAR2(150);
346 x_attribute8 VARCHAR2(150);
347 x_attribute9 VARCHAR2(150);
348 x_attribute10 VARCHAR2(150);
349 x_attribute11 VARCHAR2(150);
350 x_attribute12 VARCHAR2(150);
351 x_attribute13 VARCHAR2(150);
352 x_attribute14 VARCHAR2(150);
353 x_attribute15 VARCHAR2(150);
354 x_datatype_id NUMBER;
355 x_document_id NUMBER;
356
357 BEGIN
358
359 SELECT
360 document_id, last_update_date,
361 last_updated_by, last_update_login,
362 nvl(creation_date,sysdate),
363 nvl(created_by,1),
364 title, usage_id,
365 note_type,
366 start_date_active, end_date_active,
367 request_id, program_application_id,
368 program_id, program_update_date,
369 attribute_category, attribute1,
370 attribute2, attribute3,
371 attribute4, attribute5,
372 attribute6, attribute7,
373 attribute8, attribute9,
374 attribute10, attribute11,
375 attribute12, attribute13,
376 attribute14, attribute15
377 INTO
378 x_document_id, x_last_update_date,
379 x_last_updated_by, x_last_update_login,
380 x_creation_date, x_created_by,
381 x_title, x_usage_id,
382 x_note_type,
383 x_start_date_active, x_end_date_active,
384 x_request_id, x_program_application_id,
385 x_program_id, x_program_update_date,
386 x_attribute_category, x_attribute1,
387 x_attribute2, x_attribute3,
388 x_attribute4, x_attribute5,
389 x_attribute6, x_attribute7,
390 x_attribute8, x_attribute9,
391 x_attribute10, x_attribute11,
392 x_attribute12, x_attribute13,
393 x_attribute14, x_attribute15
394 FROM po_notes
395 WHERE po_note_id = p_note_id;
396
397 BEGIN
398 SELECT note
399 INTO x_note
400 FROM po_notes
401 WHERE po_note_id = p_note_id;
402
403 /* Mdas, 3/12/97, Bug#441412, make the default datatype as long text. */
404
405 -- x_datatype_id := 1; -- default is short text; Commented out by Mdas
406 x_datatype_id := 2;
407
408 IF x_note IS NULL THEN
409 x_note := 'R10-POXNOEEN.inp-#FND STORELONG PO_NOTES NOTE PO_NOTE_ID' ||
410 ' should update this text later';
411 ELSIF length (x_note) >= 1900 THEN -- leave 10 safe chars
412 x_datatype_id := 2; -- long text
413 END IF;
414
415 EXCEPTION
416 WHEN VALUE_ERROR THEN -- long exceeds 32760
417 x_note := 'This long text exceeds 32760. ' ||
418 'Refer to po_notes. ' ||
419 'note id = ' || to_char(p_note_id);
420 x_datatype_id := 2;
421 WHEN OTHERS THEN
422 RAISE;
423 END;
424
425 get_category_id (x_usage_id, x_category_id);
426 /* -1 if cannot find corresponding category */
427
428 IF x_datatype_id = 1 THEN
429 SELECT fnd_documents_short_text_s.nextval
430 INTO x_media_id
431 FROM sys.dual;
432 ELSE
433 SELECT fnd_documents_long_text_s.nextval
434 INTO x_media_id
435 FROM sys.dual;
436 END IF;
437
438
442 last_updated_by, last_update_login,
439 INSERT INTO fnd_documents (
440 document_id, creation_date,
441 created_by, last_update_date,
443 datatype_id, category_id,
444 security_type, security_id,
445 publish_flag, storage_type,
446 usage_type, app_source_version,
447 file_name, media_id
448 )
449 VALUES (
450 x_document_id, x_creation_date,
451 x_created_by, sysdate,
452 1, 1,
453 x_datatype_id, x_category_id,
454 4, null,
455 'N', 1,
456 x_note_type, p_app_source_version,
457 NULL, x_media_id
458 );
459
460 INSERT INTO fnd_documents_tl (
461 document_id, creation_date,
462 created_by, last_update_date,
463 last_updated_by, last_update_login,
464 language, description,
465 doc_attribute_category, doc_attribute1,
466 doc_attribute2, doc_attribute3,
467 doc_attribute4, doc_attribute5,
468 doc_attribute6, doc_attribute7,
469 doc_attribute8, doc_attribute9,
470 doc_attribute10, doc_attribute11,
471 doc_attribute12, doc_attribute13,
472 doc_attribute14, doc_attribute15,
473 source_lang, app_source_version )
474 VALUES (
475 x_document_id, x_creation_date,
476 x_created_by, sysdate,
477 1, 1,
478 userenv('LANG'), x_title,
479 x_attribute_category, x_attribute1,
480 x_attribute2, x_attribute3,
481 x_attribute4, x_attribute5,
482 x_attribute6, x_attribute7,
483 x_attribute8, x_attribute9,
484 x_attribute10, x_attribute11,
485 x_attribute12, x_attribute13,
486 x_attribute14, x_attribute15,
487 userenv('LANG'), p_app_source_version
488 );
489
490 IF x_datatype_id = 1 THEN
491 INSERT INTO fnd_documents_short_text ( media_id, short_text, app_source_version )
492 VALUES ( x_media_id, x_note, p_app_source_version );
493 ELSE
494 INSERT INTO fnd_documents_long_text ( media_id, long_text, app_source_version )
495 VALUES ( x_media_id, x_note, p_app_source_version );
496 END IF;
497
498 END;
499
500
501 PROCEDURE update_document (
502 p_note_id NUMBER,
503 p_app_source_version VARCHAR2
504 ) IS
505
506 x_category_id NUMBER;
507 x_media_id NUMBER;
508 x_datatype_id NUMBER;
509 x_po_note_id NUMBER;
510 x_last_update_date DATE;
511 x_last_updated_by NUMBER;
512 x_last_update_login NUMBER;
513 x_creation_date DATE;
514 x_created_by NUMBER;
515 x_title VARCHAR2(80);
516 x_usage_id NUMBER;
517 x_note_type VARCHAR2(25);
518 x_note VARCHAR2(32760);
519 x_start_date_active DATE;
520 x_end_date_active DATE;
521 x_request_id NUMBER;
522 x_program_application_id NUMBER;
523 x_program_id NUMBER;
524 x_program_update_date DATE;
525 x_attribute_category VARCHAR2(30);
526 x_attribute1 VARCHAR2(150);
527 x_attribute2 VARCHAR2(150);
528 x_attribute3 VARCHAR2(150);
529 x_attribute4 VARCHAR2(150);
530 x_attribute5 VARCHAR2(150);
531 x_attribute6 VARCHAR2(150);
532 x_attribute7 VARCHAR2(150);
533 x_attribute8 VARCHAR2(150);
534 x_attribute9 VARCHAR2(150);
535 x_attribute10 VARCHAR2(150);
536 x_attribute11 VARCHAR2(150);
537 x_attribute12 VARCHAR2(150);
538 x_attribute13 VARCHAR2(150);
539 x_attribute14 VARCHAR2(150);
540 x_attribute15 VARCHAR2(150);
541 x_document_id NUMBER;
542
543 BEGIN
544
545 SELECT
546 document_id, last_update_date,
547 last_updated_by, last_update_login,
548 creation_date, created_by,
549 title, usage_id,
550 note_type,
551 start_date_active, end_date_active,
552 request_id, program_application_id,
553 program_id, program_update_date,
554 attribute_category, attribute1,
555 attribute2, attribute3,
556 attribute4, attribute5,
557 attribute6, attribute7,
558 attribute8, attribute9,
559 attribute10, attribute11,
560 attribute12, attribute13,
561 attribute14, attribute15
562 INTO
563 x_document_id, x_last_update_date,
564 x_last_updated_by, x_last_update_login,
565 x_creation_date, x_created_by,
566 x_title, x_usage_id,
567 x_note_type,
568 x_start_date_active, x_end_date_active,
569 x_request_id, x_program_application_id,
570 x_program_id, x_program_update_date,
571 x_attribute_category, x_attribute1,
572 x_attribute2, x_attribute3,
573 x_attribute4, x_attribute5,
577 x_attribute12, x_attribute13,
574 x_attribute6, x_attribute7,
575 x_attribute8, x_attribute9,
576 x_attribute10, x_attribute11,
578 x_attribute14, x_attribute15
579 FROM po_notes
580 WHERE
581 po_note_id = p_note_id;
582
583 BEGIN
584 SELECT note
585 INTO x_note
586 FROM po_notes
587 WHERE po_note_id = p_note_id;
588
589 EXCEPTION
590 WHEN VALUE_ERROR THEN -- long exceeds 32760
591 x_note := 'This long text exceeds 32760. ' ||
592 'Refer to po_notes. ' ||
593 'note id = ' || to_char(p_note_id);
594 WHEN OTHERS THEN
595 RAISE;
596 END;
597
598 get_category_id (x_usage_id, x_category_id);
599 /* -1 if cannot find corresponding category */
600
601 UPDATE fnd_documents
602 SET app_source_version = 'PO_R10',
603 category_id = x_category_id
604 WHERE document_id = x_document_id;
605
606 UPDATE fnd_documents_tl
607 SET app_source_version = 'PO_R10',
608 description = x_title,
609 doc_attribute_category = x_attribute_category,
610 doc_attribute1 = x_attribute1,
611 doc_attribute2 = x_attribute2,
612 doc_attribute3 = x_attribute3,
613 doc_attribute4 = x_attribute4,
614 doc_attribute5 = x_attribute5,
615 doc_attribute6 = x_attribute6,
616 doc_attribute7 = x_attribute7,
617 doc_attribute8 = x_attribute8,
618 doc_attribute9 = x_attribute9,
619 doc_attribute10 = x_attribute10,
620 doc_attribute11 = x_attribute11,
621 doc_attribute12 = x_attribute12,
622 doc_attribute13 = x_attribute13,
623 doc_attribute14 = x_attribute14,
624 doc_attribute15 = x_attribute15
625 WHERE document_id = x_document_id
626 AND language = userenv('LANG');
627
628 get_media_id (x_document_id, x_media_id, x_datatype_id);
629
630 IF x_datatype_id = 1 THEN
631 -- we could check if note >= 2000 even datatype_id=1, and set x_note to
632 -- 'note truncated'|| substr(x_note,1,1900). leave it this way for now.
633 UPDATE fnd_documents_short_text
634 SET app_source_version = 'PO_R10',
635 short_text = substr (x_note, 1, 1998)
636 WHERE media_id = x_media_id;
637 ELSE
638 UPDATE fnd_documents_long_text
639 SET app_source_version = 'PO_R10',
640 long_text = x_note
641 WHERE media_id = x_media_id;
642 END IF;
643
644 END;
645
646
647 PROCEDURE delete_document (
648 p_document_id NUMBER
649 ) IS
650
651 x_media_id NUMBER;
652 x_datatype_id NUMBER;
653
654 BEGIN
655
656 get_media_id (p_document_id, x_media_id, x_datatype_id);
657
658 DELETE FROM fnd_documents
659 WHERE document_id = p_document_id;
660
661 DELETE FROM fnd_documents_tl
662 WHERE document_id = p_document_id;
663
664 IF x_datatype_id = 1 THEN
665 DELETE FROM fnd_documents_short_text
666 WHERE media_id = x_media_id;
667 ELSE
668 DELETE FROM fnd_documents_long_text
669 WHERE media_id = x_media_id;
670 END IF;
671
672 END;
673
674
675 PROCEDURE insert_attached_document (
676 p_creation_date DATE,
677 p_created_by NUMBER,
678 p_po_note_id NUMBER,
679 p_table_name VARCHAR2,
680 p_column_name VARCHAR2,
681 p_foreign_id NUMBER,
682 p_sequence_num NUMBER,
683 p_attribute_category VARCHAR2,
684 p_attribute1 VARCHAR2,
685 p_attribute2 VARCHAR2,
686 p_attribute3 VARCHAR2,
687 p_attribute4 VARCHAR2,
688 p_attribute5 VARCHAR2,
689 p_attribute6 VARCHAR2,
690 p_attribute7 VARCHAR2,
691 p_attribute8 VARCHAR2,
692 p_attribute9 VARCHAR2,
693 p_attribute10 VARCHAR2,
694 p_attribute11 VARCHAR2,
695 p_attribute12 VARCHAR2,
696 p_attribute13 VARCHAR2,
697 p_attribute14 VARCHAR2,
698 p_attribute15 VARCHAR2,
699 p_app_source_version VARCHAR2,
700 p_attached_doc_id OUT NOCOPY NUMBER
701 ) IS
702
703 x_document_id NUMBER;
704 x_entity_name fnd_attached_documents.entity_name%TYPE;
705
706 BEGIN
707
708 SELECT document_id
709 INTO x_document_id
710 FROM po_notes
711 WHERE po_note_id = p_po_note_id;
712
713 x_entity_name := get_entity_name ( p_table_name );
714
715 SELECT fnd_attached_documents_s.nextval
716 INTO p_attached_doc_id
717 FROM sys.dual;
718
719 INSERT INTO fnd_attached_documents (
720 attached_document_id, document_id,
721 creation_date, created_by,
722 last_update_date, last_updated_by,
723 last_update_login, seq_num,
724 entity_name, pk1_value,
725 automatically_added_flag, attribute_category,
729 attribute7, attribute8,
726 attribute1, attribute2,
727 attribute3, attribute4,
728 attribute5, attribute6,
730 attribute9, attribute10,
731 attribute11, attribute12,
732 attribute13, attribute14,
733 attribute15, app_source_version )
734 VALUES (
735 p_attached_doc_id, x_document_id,
736 p_creation_date, p_created_by,
737 sysdate, 1,
738 1, p_sequence_num,
739 x_entity_name, p_foreign_id,
740 'Y', p_attribute_category,
741 p_attribute1, p_attribute2,
742 p_attribute3, p_attribute4,
743 p_attribute5, p_attribute6,
744 p_attribute7, p_attribute8,
745 p_attribute9, p_attribute10,
746 p_attribute11, p_attribute12,
747 p_attribute13, p_attribute14,
748 p_attribute15, p_app_source_version
749 );
750
751 END;
752
753 PROCEDURE insert_attached_document_item (
754 p_creation_date DATE,
755 p_created_by NUMBER,
756 p_po_note_id NUMBER,
757 p_table_name VARCHAR2,
758 p_column_name VARCHAR2,
759 p_foreign_id NUMBER,
760 p_sequence_num NUMBER,
761 p_attribute_category VARCHAR2,
762 p_attribute1 VARCHAR2,
763 p_attribute2 VARCHAR2,
764 p_attribute3 VARCHAR2,
765 p_attribute4 VARCHAR2,
766 p_attribute5 VARCHAR2,
767 p_attribute6 VARCHAR2,
768 p_attribute7 VARCHAR2,
769 p_attribute8 VARCHAR2,
770 p_attribute9 VARCHAR2,
771 p_attribute10 VARCHAR2,
772 p_attribute11 VARCHAR2,
773 p_attribute12 VARCHAR2,
774 p_attribute13 VARCHAR2,
775 p_attribute14 VARCHAR2,
776 p_attribute15 VARCHAR2,
777 p_app_source_version VARCHAR2,
778 p_attached_doc_id OUT NOCOPY NUMBER
779 ) IS
780
781 x_document_id NUMBER;
782 x_entity_name fnd_attached_documents.entity_name%TYPE;
783 x_organization_id NUMBER;
784
785 BEGIN
786
787 SELECT document_id
788 INTO x_document_id
789 FROM po_notes
790 WHERE po_note_id = p_po_note_id;
791
792 x_entity_name := get_entity_name ( p_table_name );
793
794 SELECT fnd_attached_documents_s.nextval
795 INTO p_attached_doc_id
796 FROM sys.dual;
797
798 /* MDAS In multi org scenario, this may fetch more than one row - Watch out */
799 BEGIN
800 SELECT inventory_organization_id
801 INTO x_organization_id
802 FROM financials_system_parameters;
803 EXCEPTION
804 WHEN NO_DATA_FOUND THEN
805 x_organization_id :=-1;
806 END;
807
808 IF (x_organization_id = -1) THEN
809 NULL;
810 ELSE
811
812 INSERT INTO fnd_attached_documents (
813 attached_document_id, document_id,
814 creation_date, created_by,
815 last_update_date, last_updated_by,
816 last_update_login, seq_num,
817 entity_name, pk1_value,
818 pk2_value,
819 automatically_added_flag, attribute_category,
820 attribute1, attribute2,
821 attribute3, attribute4,
822 attribute5, attribute6,
823 attribute7, attribute8,
824 attribute9, attribute10,
825 attribute11, attribute12,
826 attribute13, attribute14,
827 attribute15, app_source_version )
828 VALUES (
829 p_attached_doc_id, x_document_id,
830 p_creation_date, p_created_by,
831 sysdate, 1,
832 1, p_sequence_num,
833 x_entity_name, x_organization_id,
834 p_foreign_id,
835 'Y', p_attribute_category,
836 p_attribute1, p_attribute2,
837 p_attribute3, p_attribute4,
838 p_attribute5, p_attribute6,
839 p_attribute7, p_attribute8,
840 p_attribute9, p_attribute10,
841 p_attribute11, p_attribute12,
842 p_attribute13, p_attribute14,
843 p_attribute15, p_app_source_version
844 );
845 END IF;
846
847 END;
848
849 PROCEDURE update_attached_document (
850 p_attached_doc_id NUMBER,
851 p_creation_date DATE,
852 p_created_by NUMBER,
853 p_po_note_id NUMBER,
854 p_table_name VARCHAR2,
855 p_column_name VARCHAR2,
856 p_foreign_id NUMBER,
857 p_sequence_num NUMBER,
858 p_attribute_category VARCHAR2,
859 p_attribute1 VARCHAR2,
860 p_attribute2 VARCHAR2,
861 p_attribute3 VARCHAR2,
862 p_attribute4 VARCHAR2,
863 p_attribute5 VARCHAR2,
864 p_attribute6 VARCHAR2,
865 p_attribute7 VARCHAR2,
869 p_attribute11 VARCHAR2,
866 p_attribute8 VARCHAR2,
867 p_attribute9 VARCHAR2,
868 p_attribute10 VARCHAR2,
870 p_attribute12 VARCHAR2,
871 p_attribute13 VARCHAR2,
872 p_attribute14 VARCHAR2,
873 p_attribute15 VARCHAR2,
874 p_app_source_version VARCHAR2
875 ) IS
876
877 x_document_id NUMBER;
878
879 BEGIN
880
881 SELECT document_id
882 INTO x_document_id
883 FROM po_notes
884 WHERE po_note_id = p_po_note_id;
885
886 -- you can only update flex fields and sequence number.
887 UPDATE fnd_attached_documents
888 SET last_update_date = SYSDATE,
889 seq_num = p_sequence_num,
890 document_id = x_document_id,
891 attribute_category = p_attribute_category,
892 attribute1 = p_attribute1,
893 attribute2 = p_attribute2,
894 attribute3 = p_attribute3,
895 attribute4 = p_attribute4,
896 attribute5 = p_attribute5,
897 attribute6 = p_attribute6,
898 attribute7 = p_attribute7,
899 attribute8 = p_attribute8,
900 attribute9 = p_attribute9,
901 attribute10 = p_attribute10,
902 attribute11 = p_attribute11,
903 attribute12 = p_attribute12,
904 attribute13 = p_attribute13,
905 attribute14 = p_attribute14,
906 attribute15 = p_attribute15,
907 app_source_version = 'PO_R10'
908 WHERE attached_document_id = p_attached_doc_id;
909
910 END;
911
912 PROCEDURE delete_attached_document (
913 p_attached_doc_id NUMBER
914 ) IS
915
916 BEGIN
917
918 DELETE FROM fnd_attached_documents
919 WHERE attached_document_id = p_attached_doc_id;
920
921 END;
922
923
924 END;