[Home] [Help]
PACKAGE BODY: APPS.JTF_NOTES_PUB_W
Source
1 package body jtf_notes_pub_w as
2 /* $Header: jtfntswb.pls 120.2 2006/04/26 23:08 mpadhiar ship $ */
3 rosetta_g_mistake_date date := to_date('01/01/+4713', 'MM/DD/SYYYY');
4 rosetta_g_miss_date date := to_date('01/01/-4712', 'MM/DD/SYYYY');
5 rosetta_g_mistake_date_high date := to_date('01/01/+4710', 'MM/DD/SYYYY');
6 rosetta_g_mistake_date_low date := to_date('01/01/-4710', 'MM/DD/SYYYY');
7
8 -- this is to workaround the JDBC bug regarding IN DATE of value GMiss
9 function rosetta_g_miss_date_in_map(d date) return date as
10 begin
11 if d > rosetta_g_mistake_date_high then return fnd_api.g_miss_date; end if;
12 if d < rosetta_g_mistake_date_low then return fnd_api.g_miss_date; end if;
13 return d;
14 end;
15
16 procedure rosetta_table_copy_in_p1(t out nocopy jtf_notes_pub.jtf_note_contexts_tbl_type, a0 JTF_NUMBER_TABLE
17 , a1 JTF_NUMBER_TABLE
18 , a2 JTF_VARCHAR2_TABLE_300
19 , a3 JTF_NUMBER_TABLE
20 , a4 JTF_DATE_TABLE
21 , a5 JTF_NUMBER_TABLE
22 , a6 JTF_DATE_TABLE
23 , a7 JTF_NUMBER_TABLE
24 , a8 JTF_NUMBER_TABLE
25 ) as
26 ddindx binary_integer; indx binary_integer;
27 begin
28 if a0 is not null and a0.count > 0 then
29 if a0.count > 0 then
30 indx := a0.first;
31 ddindx := 1;
32 while true loop
33 t(ddindx).note_context_id := a0(indx);
34 t(ddindx).jtf_note_id := a1(indx);
35 t(ddindx).note_context_type := a2(indx);
36 t(ddindx).note_context_type_id := a3(indx);
37 t(ddindx).last_update_date := rosetta_g_miss_date_in_map(a4(indx));
38 t(ddindx).last_updated_by := a5(indx);
39 t(ddindx).creation_date := rosetta_g_miss_date_in_map(a6(indx));
40 t(ddindx).created_by := a7(indx);
41 t(ddindx).last_update_login := a8(indx);
42 ddindx := ddindx+1;
43 if a0.last =indx
44 then exit;
45 end if;
46 indx := a0.next(indx);
47 end loop;
48 end if;
49 end if;
50 end rosetta_table_copy_in_p1;
51 procedure rosetta_table_copy_out_p1(t jtf_notes_pub.jtf_note_contexts_tbl_type, a0 out nocopy JTF_NUMBER_TABLE
52 , a1 out nocopy JTF_NUMBER_TABLE
53 , a2 out nocopy JTF_VARCHAR2_TABLE_300
54 , a3 out nocopy JTF_NUMBER_TABLE
55 , a4 out nocopy JTF_DATE_TABLE
56 , a5 out nocopy JTF_NUMBER_TABLE
57 , a6 out nocopy JTF_DATE_TABLE
58 , a7 out nocopy JTF_NUMBER_TABLE
59 , a8 out nocopy JTF_NUMBER_TABLE
60 ) as
61 ddindx binary_integer; indx binary_integer;
62 begin
63 if t is null or t.count = 0 then
64 a0 := JTF_NUMBER_TABLE();
65 a1 := JTF_NUMBER_TABLE();
66 a2 := JTF_VARCHAR2_TABLE_300();
67 a3 := JTF_NUMBER_TABLE();
68 a4 := JTF_DATE_TABLE();
69 a5 := JTF_NUMBER_TABLE();
70 a6 := JTF_DATE_TABLE();
71 a7 := JTF_NUMBER_TABLE();
72 a8 := JTF_NUMBER_TABLE();
73 else
74 a0 := JTF_NUMBER_TABLE();
75 a1 := JTF_NUMBER_TABLE();
76 a2 := JTF_VARCHAR2_TABLE_300();
77 a3 := JTF_NUMBER_TABLE();
78 a4 := JTF_DATE_TABLE();
79 a5 := JTF_NUMBER_TABLE();
80 a6 := JTF_DATE_TABLE();
81 a7 := JTF_NUMBER_TABLE();
82 a8 := JTF_NUMBER_TABLE();
83 if t.count > 0 then
84 a0.extend(t.count);
85 a1.extend(t.count);
86 a2.extend(t.count);
87 a3.extend(t.count);
88 a4.extend(t.count);
89 a5.extend(t.count);
90 a6.extend(t.count);
91 a7.extend(t.count);
92 a8.extend(t.count);
93 ddindx := t.first;
94 indx := 1;
95 while true loop
96 a0(indx) := t(ddindx).note_context_id;
97 a1(indx) := t(ddindx).jtf_note_id;
98 a2(indx) := t(ddindx).note_context_type;
99 a3(indx) := t(ddindx).note_context_type_id;
100 a4(indx) := t(ddindx).last_update_date;
101 a5(indx) := t(ddindx).last_updated_by;
102 a6(indx) := t(ddindx).creation_date;
103 a7(indx) := t(ddindx).created_by;
104 a8(indx) := t(ddindx).last_update_login;
105 indx := indx+1;
106 if t.last =ddindx
107 then exit;
108 end if;
109 ddindx := t.next(ddindx);
110 end loop;
111 end if;
112 end if;
113 end rosetta_table_copy_out_p1;
114
115 procedure create_note(p_parent_note_id NUMBER
116 , p_jtf_note_id NUMBER
117 , p_api_version NUMBER
118 , p_init_msg_list VARCHAR2
119 , p_commit VARCHAR2
120 , p_validation_level NUMBER
121 , x_return_status out nocopy VARCHAR2
122 , x_msg_count out nocopy NUMBER
123 , x_msg_data out nocopy VARCHAR2
124 , p_org_id NUMBER
125 , p_source_object_id NUMBER
126 , p_source_object_code VARCHAR2
127 , p_notes VARCHAR2
128 , p_notes_detail VARCHAR2
129 , p_note_status VARCHAR2
130 , p_entered_by NUMBER
131 , p_entered_date date
132 , x_jtf_note_id out nocopy NUMBER
133 , p_last_update_date date
134 , p_last_updated_by NUMBER
135 , p_creation_date date
136 , p_created_by NUMBER
137 , p_last_update_login NUMBER
138 , p_attribute1 VARCHAR2
139 , p_attribute2 VARCHAR2
140 , p_attribute3 VARCHAR2
141 , p_attribute4 VARCHAR2
142 , p_attribute5 VARCHAR2
143 , p_attribute6 VARCHAR2
144 , p_attribute7 VARCHAR2
145 , p_attribute8 VARCHAR2
146 , p_attribute9 VARCHAR2
147 , p_attribute10 VARCHAR2
148 , p_attribute11 VARCHAR2
149 , p_attribute12 VARCHAR2
150 , p_attribute13 VARCHAR2
151 , p_attribute14 VARCHAR2
152 , p_attribute15 VARCHAR2
153 , p_context VARCHAR2
154 , p_note_type VARCHAR2
155 , p40_a0 JTF_NUMBER_TABLE
156 , p40_a1 JTF_NUMBER_TABLE
157 , p40_a2 JTF_VARCHAR2_TABLE_300
158 , p40_a3 JTF_NUMBER_TABLE
159 , p40_a4 JTF_DATE_TABLE
160 , p40_a5 JTF_NUMBER_TABLE
161 , p40_a6 JTF_DATE_TABLE
162 , p40_a7 JTF_NUMBER_TABLE
163 , p40_a8 JTF_NUMBER_TABLE
164 )
165
166 as
167 ddp_entered_date date;
168 ddp_last_update_date date;
169 ddp_creation_date date;
170 ddp_jtf_note_contexts_tab jtf_notes_pub.jtf_note_contexts_tbl_type;
171 ddindx binary_integer; indx binary_integer;
172 begin
173
174 -- copy data to the local IN or IN-OUT args, if any
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191 ddp_entered_date := rosetta_g_miss_date_in_map(p_entered_date);
192
193
194 ddp_last_update_date := rosetta_g_miss_date_in_map(p_last_update_date);
195
196
197 ddp_creation_date := rosetta_g_miss_date_in_map(p_creation_date);
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218 jtf_notes_pub_w.rosetta_table_copy_in_p1(ddp_jtf_note_contexts_tab, p40_a0
219 , p40_a1
220 , p40_a2
221 , p40_a3
222 , p40_a4
223 , p40_a5
224 , p40_a6
225 , p40_a7
226 , p40_a8
227 );
228
229 -- here's the delegated call to the old PL/SQL routine
230 jtf_notes_pub.create_note(p_parent_note_id,
231 p_jtf_note_id,
232 p_api_version,
233 p_init_msg_list,
234 p_commit,
235 p_validation_level,
236 x_return_status,
237 x_msg_count,
238 x_msg_data,
239 p_org_id,
240 p_source_object_id,
241 p_source_object_code,
242 p_notes,
243 p_notes_detail,
244 p_note_status,
245 p_entered_by,
246 ddp_entered_date,
247 x_jtf_note_id,
248 ddp_last_update_date,
249 p_last_updated_by,
250 ddp_creation_date,
251 p_created_by,
252 p_last_update_login,
253 p_attribute1,
254 p_attribute2,
255 p_attribute3,
256 p_attribute4,
257 p_attribute5,
258 p_attribute6,
259 p_attribute7,
260 p_attribute8,
261 p_attribute9,
262 p_attribute10,
263 p_attribute11,
264 p_attribute12,
265 p_attribute13,
266 p_attribute14,
267 p_attribute15,
268 p_context,
269 p_note_type,
270 ddp_jtf_note_contexts_tab);
271
272 -- copy data back from the local variables to OUT or IN-OUT args, if any
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313 end;
314
315 procedure update_note(p_api_version NUMBER
316 , p_init_msg_list VARCHAR2
317 , p_commit VARCHAR2
318 , p_validation_level NUMBER
319 , x_return_status out nocopy VARCHAR2
320 , x_msg_count out nocopy NUMBER
321 , x_msg_data out nocopy VARCHAR2
322 , p_jtf_note_id NUMBER
323 , p_entered_by NUMBER
324 , p_last_updated_by NUMBER
325 , p_last_update_date date
326 , p_last_update_login NUMBER
327 , p_notes VARCHAR2
328 , p_notes_detail VARCHAR2
329 , p_append_flag VARCHAR2
330 , p_note_status VARCHAR2
331 , p_note_type VARCHAR2
332 , p17_a0 JTF_NUMBER_TABLE
333 , p17_a1 JTF_NUMBER_TABLE
334 , p17_a2 JTF_VARCHAR2_TABLE_300
335 , p17_a3 JTF_NUMBER_TABLE
336 , p17_a4 JTF_DATE_TABLE
337 , p17_a5 JTF_NUMBER_TABLE
338 , p17_a6 JTF_DATE_TABLE
339 , p17_a7 JTF_NUMBER_TABLE
340 , p17_a8 JTF_NUMBER_TABLE
341 )
342
343 as
344 ddp_last_update_date date;
345 ddp_jtf_note_contexts_tab jtf_notes_pub.jtf_note_contexts_tbl_type;
346 ddindx binary_integer; indx binary_integer;
347 begin
348
349 -- copy data to the local IN or IN-OUT args, if any
350
351
352
353
354
355
356
357
358
359
360 ddp_last_update_date := rosetta_g_miss_date_in_map(p_last_update_date);
361
362
363
364
365
366
367
368 jtf_notes_pub_w.rosetta_table_copy_in_p1(ddp_jtf_note_contexts_tab, p17_a0
369 , p17_a1
370 , p17_a2
371 , p17_a3
372 , p17_a4
373 , p17_a5
374 , p17_a6
375 , p17_a7
376 , p17_a8
377 );
378
379 -- here's the delegated call to the old PL/SQL routine
380 jtf_notes_pub.update_note(p_api_version,
381 p_init_msg_list,
382 p_commit,
383 p_validation_level,
384 x_return_status,
385 x_msg_count,
386 x_msg_data,
387 p_jtf_note_id,
388 p_entered_by,
389 p_last_updated_by,
390 ddp_last_update_date,
391 p_last_update_login,
392 p_notes,
393 p_notes_detail,
394 p_append_flag,
395 p_note_status,
396 p_note_type,
397 ddp_jtf_note_contexts_tab);
398
399 -- copy data back from the local variables to OUT or IN-OUT args, if any
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417 end;
418
419 procedure create_note_context(p_validation_level NUMBER
420 , x_return_status out nocopy VARCHAR2
421 , p_jtf_note_id NUMBER
422 , p_last_update_date date
423 , p_last_updated_by NUMBER
424 , p_creation_date date
425 , p_created_by NUMBER
426 , p_last_update_login NUMBER
427 , p_note_context_type_id NUMBER
428 , p_note_context_type VARCHAR2
429 , x_note_context_id out nocopy NUMBER
430 )
431
432 as
433 ddp_last_update_date date;
434 ddp_creation_date date;
435 ddindx binary_integer; indx binary_integer;
436 begin
437
438 -- copy data to the local IN or IN-OUT args, if any
439
440
441
442 ddp_last_update_date := rosetta_g_miss_date_in_map(p_last_update_date);
443
444
445 ddp_creation_date := rosetta_g_miss_date_in_map(p_creation_date);
446
447
448
449
450
451
452 -- here's the delegated call to the old PL/SQL routine
453 jtf_notes_pub.create_note_context(p_validation_level,
454 x_return_status,
455 p_jtf_note_id,
456 ddp_last_update_date,
457 p_last_updated_by,
458 ddp_creation_date,
459 p_created_by,
460 p_last_update_login,
461 p_note_context_type_id,
462 p_note_context_type,
463 x_note_context_id);
464
465 -- copy data back from the local variables to OUT or IN-OUT args, if any
466
467
468
469
470
471
472
473
474
475
476 end;
477
478 procedure update_note_context(p_validation_level NUMBER
479 , x_return_status out nocopy VARCHAR2
480 , p_note_context_id NUMBER
481 , p_jtf_note_id NUMBER
482 , p_note_context_type_id NUMBER
483 , p_note_context_type VARCHAR2
484 , p_last_updated_by NUMBER
485 , p_last_update_date date
486 , p_last_update_login NUMBER
487 )
488
489 as
490 ddp_last_update_date date;
491 ddindx binary_integer; indx binary_integer;
492 begin
493
494 -- copy data to the local IN or IN-OUT args, if any
495
496
497
498
499
500
501
502 ddp_last_update_date := rosetta_g_miss_date_in_map(p_last_update_date);
503
504
505 -- here's the delegated call to the old PL/SQL routine
506 jtf_notes_pub.update_note_context(p_validation_level,
507 x_return_status,
508 p_note_context_id,
509 p_jtf_note_id,
510 p_note_context_type_id,
511 p_note_context_type,
512 p_last_updated_by,
513 ddp_last_update_date,
514 p_last_update_login);
515
516 -- copy data back from the local variables to OUT or IN-OUT args, if any
517
518
519
520
521
522
523
524
525 end;
526
527 procedure secure_create_note(p_parent_note_id NUMBER
528 , p_jtf_note_id NUMBER
529 , p_api_version NUMBER
530 , p_init_msg_list VARCHAR2
531 , p_commit VARCHAR2
532 , p_validation_level NUMBER
533 , x_return_status out nocopy VARCHAR2
534 , x_msg_count out nocopy NUMBER
535 , x_msg_data out nocopy VARCHAR2
536 , p_org_id NUMBER
537 , p_source_object_id NUMBER
538 , p_source_object_code VARCHAR2
539 , p_notes VARCHAR2
540 , p_notes_detail VARCHAR2
541 , p_note_status VARCHAR2
542 , p_entered_by NUMBER
543 , p_entered_date date
544 , x_jtf_note_id out nocopy NUMBER
545 , p_last_update_date date
546 , p_last_updated_by NUMBER
547 , p_creation_date date
548 , p_created_by NUMBER
549 , p_last_update_login NUMBER
550 , p_attribute1 VARCHAR2
551 , p_attribute2 VARCHAR2
552 , p_attribute3 VARCHAR2
553 , p_attribute4 VARCHAR2
554 , p_attribute5 VARCHAR2
555 , p_attribute6 VARCHAR2
556 , p_attribute7 VARCHAR2
557 , p_attribute8 VARCHAR2
558 , p_attribute9 VARCHAR2
559 , p_attribute10 VARCHAR2
560 , p_attribute11 VARCHAR2
561 , p_attribute12 VARCHAR2
562 , p_attribute13 VARCHAR2
563 , p_attribute14 VARCHAR2
564 , p_attribute15 VARCHAR2
565 , p_context VARCHAR2
566 , p_note_type VARCHAR2
567 , p40_a0 JTF_NUMBER_TABLE
568 , p40_a1 JTF_NUMBER_TABLE
569 , p40_a2 JTF_VARCHAR2_TABLE_300
570 , p40_a3 JTF_NUMBER_TABLE
571 , p40_a4 JTF_DATE_TABLE
572 , p40_a5 JTF_NUMBER_TABLE
573 , p40_a6 JTF_DATE_TABLE
574 , p40_a7 JTF_NUMBER_TABLE
575 , p40_a8 JTF_NUMBER_TABLE
576 , p_use_aol_security VARCHAR2
577 )
578
579 as
580 ddp_entered_date date;
581 ddp_last_update_date date;
582 ddp_creation_date date;
583 ddp_jtf_note_contexts_tab jtf_notes_pub.jtf_note_contexts_tbl_type;
584 ddindx binary_integer; indx binary_integer;
585 begin
586
587 -- copy data to the local IN or IN-OUT args, if any
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604 ddp_entered_date := rosetta_g_miss_date_in_map(p_entered_date);
605
606
607 ddp_last_update_date := rosetta_g_miss_date_in_map(p_last_update_date);
608
609
610 ddp_creation_date := rosetta_g_miss_date_in_map(p_creation_date);
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631 jtf_notes_pub_w.rosetta_table_copy_in_p1(ddp_jtf_note_contexts_tab, p40_a0
632 , p40_a1
633 , p40_a2
634 , p40_a3
635 , p40_a4
636 , p40_a5
637 , p40_a6
638 , p40_a7
639 , p40_a8
640 );
641
642
643 -- here's the delegated call to the old PL/SQL routine
644 jtf_notes_pub.secure_create_note(p_parent_note_id,
645 p_jtf_note_id,
646 p_api_version,
647 p_init_msg_list,
648 p_commit,
649 p_validation_level,
650 x_return_status,
651 x_msg_count,
652 x_msg_data,
653 p_org_id,
654 p_source_object_id,
655 p_source_object_code,
656 p_notes,
657 p_notes_detail,
658 p_note_status,
659 p_entered_by,
660 ddp_entered_date,
661 x_jtf_note_id,
662 ddp_last_update_date,
663 p_last_updated_by,
664 ddp_creation_date,
665 p_created_by,
666 p_last_update_login,
667 p_attribute1,
668 p_attribute2,
669 p_attribute3,
670 p_attribute4,
671 p_attribute5,
672 p_attribute6,
673 p_attribute7,
674 p_attribute8,
675 p_attribute9,
676 p_attribute10,
677 p_attribute11,
678 p_attribute12,
679 p_attribute13,
680 p_attribute14,
681 p_attribute15,
682 p_context,
683 p_note_type,
684 ddp_jtf_note_contexts_tab,
685 p_use_aol_security);
686
687 -- copy data back from the local variables to OUT or IN-OUT args, if any
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729 end;
730
731 procedure secure_update_note(p_api_version NUMBER
732 , p_init_msg_list VARCHAR2
733 , p_commit VARCHAR2
734 , p_validation_level NUMBER
735 , x_return_status out nocopy VARCHAR2
736 , x_msg_count out nocopy NUMBER
737 , x_msg_data out nocopy VARCHAR2
738 , p_jtf_note_id NUMBER
739 , p_entered_by NUMBER
740 , p_last_updated_by NUMBER
741 , p_last_update_date date
742 , p_last_update_login NUMBER
743 , p_notes VARCHAR2
744 , p_notes_detail VARCHAR2
745 , p_append_flag VARCHAR2
746 , p_note_status VARCHAR2
747 , p_note_type VARCHAR2
748 , p17_a0 JTF_NUMBER_TABLE
749 , p17_a1 JTF_NUMBER_TABLE
750 , p17_a2 JTF_VARCHAR2_TABLE_300
751 , p17_a3 JTF_NUMBER_TABLE
752 , p17_a4 JTF_DATE_TABLE
753 , p17_a5 JTF_NUMBER_TABLE
754 , p17_a6 JTF_DATE_TABLE
755 , p17_a7 JTF_NUMBER_TABLE
756 , p17_a8 JTF_NUMBER_TABLE
757 , p_attribute1 VARCHAR2
758 , p_attribute2 VARCHAR2
759 , p_attribute3 VARCHAR2
760 , p_attribute4 VARCHAR2
761 , p_attribute5 VARCHAR2
762 , p_attribute6 VARCHAR2
763 , p_attribute7 VARCHAR2
764 , p_attribute8 VARCHAR2
765 , p_attribute9 VARCHAR2
766 , p_attribute10 VARCHAR2
767 , p_attribute11 VARCHAR2
768 , p_attribute12 VARCHAR2
769 , p_attribute13 VARCHAR2
770 , p_attribute14 VARCHAR2
771 , p_attribute15 VARCHAR2
772 , p_context VARCHAR2
773 , p_use_aol_security VARCHAR2
774 )
775
776 as
777 ddp_last_update_date date;
778 ddp_jtf_note_contexts_tab jtf_notes_pub.jtf_note_contexts_tbl_type;
779 ddindx binary_integer; indx binary_integer;
780 begin
781
782 -- copy data to the local IN or IN-OUT args, if any
783
784
785
786
787
788
789
790
791
792
793 ddp_last_update_date := rosetta_g_miss_date_in_map(p_last_update_date);
794
795
796
797
798
799
800
801 jtf_notes_pub_w.rosetta_table_copy_in_p1(ddp_jtf_note_contexts_tab, p17_a0
802 , p17_a1
803 , p17_a2
804 , p17_a3
805 , p17_a4
806 , p17_a5
807 , p17_a6
808 , p17_a7
809 , p17_a8
810 );
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829 -- here's the delegated call to the old PL/SQL routine
830 jtf_notes_pub.secure_update_note(p_api_version,
831 p_init_msg_list,
832 p_commit,
833 p_validation_level,
834 x_return_status,
835 x_msg_count,
836 x_msg_data,
837 p_jtf_note_id,
838 p_entered_by,
839 p_last_updated_by,
840 ddp_last_update_date,
841 p_last_update_login,
842 p_notes,
843 p_notes_detail,
844 p_append_flag,
845 p_note_status,
846 p_note_type,
847 ddp_jtf_note_contexts_tab,
848 p_attribute1,
849 p_attribute2,
850 p_attribute3,
851 p_attribute4,
852 p_attribute5,
853 p_attribute6,
854 p_attribute7,
855 p_attribute8,
856 p_attribute9,
857 p_attribute10,
858 p_attribute11,
859 p_attribute12,
860 p_attribute13,
861 p_attribute14,
862 p_attribute15,
863 p_context,
864 p_use_aol_security);
865
866 -- copy data back from the local variables to OUT or IN-OUT args, if any
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901 end;
902
903 end jtf_notes_pub_w;