[Home] [Help]
PACKAGE BODY: APPS.CSP_EXCESS_LISTS_PKG_W
Source
1 package body csp_excess_lists_pkg_w as
2 /* $Header: csptexcwb.pls 120.1 2011/04/09 18:11:50 htank noship $ */
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_p2(t out nocopy csp_excess_lists_pkg.excess_tbl_type, a0 JTF_NUMBER_TABLE
17 , a1 JTF_NUMBER_TABLE
18 , a2 JTF_NUMBER_TABLE
19 , a3 JTF_NUMBER_TABLE
20 , a4 JTF_VARCHAR2_TABLE_100
21 , a5 JTF_NUMBER_TABLE
22 , a6 JTF_DATE_TABLE
23 , a7 JTF_NUMBER_TABLE
24 , a8 JTF_DATE_TABLE
25 , a9 JTF_NUMBER_TABLE
26 , a10 JTF_VARCHAR2_TABLE_100
27 , a11 JTF_NUMBER_TABLE
28 , a12 JTF_NUMBER_TABLE
29 , a13 JTF_NUMBER_TABLE
30 , a14 JTF_VARCHAR2_TABLE_100
31 , a15 JTF_VARCHAR2_TABLE_100
32 , a16 JTF_VARCHAR2_TABLE_200
33 , a17 JTF_VARCHAR2_TABLE_200
34 , a18 JTF_VARCHAR2_TABLE_200
35 , a19 JTF_VARCHAR2_TABLE_200
36 , a20 JTF_VARCHAR2_TABLE_200
37 , a21 JTF_VARCHAR2_TABLE_200
38 , a22 JTF_VARCHAR2_TABLE_200
39 , a23 JTF_VARCHAR2_TABLE_200
40 , a24 JTF_VARCHAR2_TABLE_200
41 , a25 JTF_VARCHAR2_TABLE_200
42 , a26 JTF_VARCHAR2_TABLE_200
43 , a27 JTF_VARCHAR2_TABLE_200
44 , a28 JTF_VARCHAR2_TABLE_200
45 , a29 JTF_VARCHAR2_TABLE_200
46 , a30 JTF_VARCHAR2_TABLE_200
47 , a31 JTF_NUMBER_TABLE
48 , a32 JTF_VARCHAR2_TABLE_300
49 , a33 JTF_NUMBER_TABLE
50 , a34 JTF_VARCHAR2_TABLE_100
51 ) as
52 ddindx binary_integer; indx binary_integer;
53 begin
54 if a0 is not null and a0.count > 0 then
55 if a0.count > 0 then
56 indx := a0.first;
57 ddindx := 1;
58 while true loop
59 t(ddindx).excess_line_id := a0(indx);
60 t(ddindx).organization_id := a1(indx);
61 t(ddindx).inventory_item_id := a2(indx);
62 t(ddindx).excess_quantity := a3(indx);
63 t(ddindx).condition_code := a4(indx);
64 t(ddindx).created_by := a5(indx);
65 t(ddindx).creation_date := rosetta_g_miss_date_in_map(a6(indx));
66 t(ddindx).last_updated_by := a7(indx);
67 t(ddindx).last_update_date := rosetta_g_miss_date_in_map(a8(indx));
68 t(ddindx).last_update_login := a9(indx);
69 t(ddindx).subinventory_code := a10(indx);
70 t(ddindx).returned_quantity := a11(indx);
71 t(ddindx).current_return_qty := a12(indx);
72 t(ddindx).requisition_line_id := a13(indx);
73 t(ddindx).excess_status := a14(indx);
74 t(ddindx).attribute_category := a15(indx);
75 t(ddindx).attribute1 := a16(indx);
76 t(ddindx).attribute2 := a17(indx);
77 t(ddindx).attribute3 := a18(indx);
78 t(ddindx).attribute4 := a19(indx);
79 t(ddindx).attribute5 := a20(indx);
80 t(ddindx).attribute6 := a21(indx);
81 t(ddindx).attribute7 := a22(indx);
82 t(ddindx).attribute8 := a23(indx);
83 t(ddindx).attribute9 := a24(indx);
84 t(ddindx).attribute10 := a25(indx);
85 t(ddindx).attribute11 := a26(indx);
86 t(ddindx).attribute12 := a27(indx);
87 t(ddindx).attribute13 := a28(indx);
88 t(ddindx).attribute14 := a29(indx);
89 t(ddindx).attribute15 := a30(indx);
90 t(ddindx).security_group_id := a31(indx);
91 t(ddindx).reason_code := a32(indx);
92 t(ddindx).return_organization_id := a33(indx);
93 t(ddindx).return_subinventory_name := a34(indx);
94 ddindx := ddindx+1;
95 if a0.last =indx
96 then exit;
97 end if;
98 indx := a0.next(indx);
99 end loop;
100 end if;
101 end if;
102 end rosetta_table_copy_in_p2;
103 procedure rosetta_table_copy_out_p2(t csp_excess_lists_pkg.excess_tbl_type, a0 out nocopy JTF_NUMBER_TABLE
104 , a1 out nocopy JTF_NUMBER_TABLE
105 , a2 out nocopy JTF_NUMBER_TABLE
106 , a3 out nocopy JTF_NUMBER_TABLE
107 , a4 out nocopy JTF_VARCHAR2_TABLE_100
108 , a5 out nocopy JTF_NUMBER_TABLE
109 , a6 out nocopy JTF_DATE_TABLE
110 , a7 out nocopy JTF_NUMBER_TABLE
111 , a8 out nocopy JTF_DATE_TABLE
112 , a9 out nocopy JTF_NUMBER_TABLE
113 , a10 out nocopy JTF_VARCHAR2_TABLE_100
114 , a11 out nocopy JTF_NUMBER_TABLE
115 , a12 out nocopy JTF_NUMBER_TABLE
116 , a13 out nocopy JTF_NUMBER_TABLE
117 , a14 out nocopy JTF_VARCHAR2_TABLE_100
118 , a15 out nocopy JTF_VARCHAR2_TABLE_100
119 , a16 out nocopy JTF_VARCHAR2_TABLE_200
120 , a17 out nocopy JTF_VARCHAR2_TABLE_200
121 , a18 out nocopy JTF_VARCHAR2_TABLE_200
122 , a19 out nocopy JTF_VARCHAR2_TABLE_200
123 , a20 out nocopy JTF_VARCHAR2_TABLE_200
124 , a21 out nocopy JTF_VARCHAR2_TABLE_200
125 , a22 out nocopy JTF_VARCHAR2_TABLE_200
126 , a23 out nocopy JTF_VARCHAR2_TABLE_200
127 , a24 out nocopy JTF_VARCHAR2_TABLE_200
128 , a25 out nocopy JTF_VARCHAR2_TABLE_200
129 , a26 out nocopy JTF_VARCHAR2_TABLE_200
130 , a27 out nocopy JTF_VARCHAR2_TABLE_200
131 , a28 out nocopy JTF_VARCHAR2_TABLE_200
132 , a29 out nocopy JTF_VARCHAR2_TABLE_200
133 , a30 out nocopy JTF_VARCHAR2_TABLE_200
134 , a31 out nocopy JTF_NUMBER_TABLE
135 , a32 out nocopy JTF_VARCHAR2_TABLE_300
136 , a33 out nocopy JTF_NUMBER_TABLE
137 , a34 out nocopy JTF_VARCHAR2_TABLE_100
138 ) as
139 ddindx binary_integer; indx binary_integer;
140 begin
141 if t is null or t.count = 0 then
142 a0 := JTF_NUMBER_TABLE();
143 a1 := JTF_NUMBER_TABLE();
144 a2 := JTF_NUMBER_TABLE();
145 a3 := JTF_NUMBER_TABLE();
146 a4 := JTF_VARCHAR2_TABLE_100();
147 a5 := JTF_NUMBER_TABLE();
148 a6 := JTF_DATE_TABLE();
149 a7 := JTF_NUMBER_TABLE();
150 a8 := JTF_DATE_TABLE();
151 a9 := JTF_NUMBER_TABLE();
152 a10 := JTF_VARCHAR2_TABLE_100();
153 a11 := JTF_NUMBER_TABLE();
154 a12 := JTF_NUMBER_TABLE();
155 a13 := JTF_NUMBER_TABLE();
156 a14 := JTF_VARCHAR2_TABLE_100();
157 a15 := JTF_VARCHAR2_TABLE_100();
158 a16 := JTF_VARCHAR2_TABLE_200();
159 a17 := JTF_VARCHAR2_TABLE_200();
160 a18 := JTF_VARCHAR2_TABLE_200();
161 a19 := JTF_VARCHAR2_TABLE_200();
162 a20 := JTF_VARCHAR2_TABLE_200();
163 a21 := JTF_VARCHAR2_TABLE_200();
164 a22 := JTF_VARCHAR2_TABLE_200();
165 a23 := JTF_VARCHAR2_TABLE_200();
166 a24 := JTF_VARCHAR2_TABLE_200();
167 a25 := JTF_VARCHAR2_TABLE_200();
168 a26 := JTF_VARCHAR2_TABLE_200();
169 a27 := JTF_VARCHAR2_TABLE_200();
170 a28 := JTF_VARCHAR2_TABLE_200();
171 a29 := JTF_VARCHAR2_TABLE_200();
172 a30 := JTF_VARCHAR2_TABLE_200();
173 a31 := JTF_NUMBER_TABLE();
174 a32 := JTF_VARCHAR2_TABLE_300();
175 a33 := JTF_NUMBER_TABLE();
176 a34 := JTF_VARCHAR2_TABLE_100();
177 else
178 a0 := JTF_NUMBER_TABLE();
179 a1 := JTF_NUMBER_TABLE();
180 a2 := JTF_NUMBER_TABLE();
181 a3 := JTF_NUMBER_TABLE();
182 a4 := JTF_VARCHAR2_TABLE_100();
183 a5 := JTF_NUMBER_TABLE();
184 a6 := JTF_DATE_TABLE();
185 a7 := JTF_NUMBER_TABLE();
186 a8 := JTF_DATE_TABLE();
187 a9 := JTF_NUMBER_TABLE();
188 a10 := JTF_VARCHAR2_TABLE_100();
189 a11 := JTF_NUMBER_TABLE();
190 a12 := JTF_NUMBER_TABLE();
191 a13 := JTF_NUMBER_TABLE();
192 a14 := JTF_VARCHAR2_TABLE_100();
193 a15 := JTF_VARCHAR2_TABLE_100();
194 a16 := JTF_VARCHAR2_TABLE_200();
195 a17 := JTF_VARCHAR2_TABLE_200();
196 a18 := JTF_VARCHAR2_TABLE_200();
197 a19 := JTF_VARCHAR2_TABLE_200();
198 a20 := JTF_VARCHAR2_TABLE_200();
199 a21 := JTF_VARCHAR2_TABLE_200();
200 a22 := JTF_VARCHAR2_TABLE_200();
201 a23 := JTF_VARCHAR2_TABLE_200();
202 a24 := JTF_VARCHAR2_TABLE_200();
203 a25 := JTF_VARCHAR2_TABLE_200();
204 a26 := JTF_VARCHAR2_TABLE_200();
205 a27 := JTF_VARCHAR2_TABLE_200();
206 a28 := JTF_VARCHAR2_TABLE_200();
207 a29 := JTF_VARCHAR2_TABLE_200();
208 a30 := JTF_VARCHAR2_TABLE_200();
209 a31 := JTF_NUMBER_TABLE();
210 a32 := JTF_VARCHAR2_TABLE_300();
211 a33 := JTF_NUMBER_TABLE();
212 a34 := JTF_VARCHAR2_TABLE_100();
213 if t.count > 0 then
214 a0.extend(t.count);
215 a1.extend(t.count);
216 a2.extend(t.count);
217 a3.extend(t.count);
218 a4.extend(t.count);
219 a5.extend(t.count);
220 a6.extend(t.count);
221 a7.extend(t.count);
222 a8.extend(t.count);
223 a9.extend(t.count);
224 a10.extend(t.count);
225 a11.extend(t.count);
226 a12.extend(t.count);
227 a13.extend(t.count);
228 a14.extend(t.count);
229 a15.extend(t.count);
230 a16.extend(t.count);
231 a17.extend(t.count);
232 a18.extend(t.count);
233 a19.extend(t.count);
234 a20.extend(t.count);
235 a21.extend(t.count);
236 a22.extend(t.count);
237 a23.extend(t.count);
238 a24.extend(t.count);
239 a25.extend(t.count);
240 a26.extend(t.count);
241 a27.extend(t.count);
242 a28.extend(t.count);
243 a29.extend(t.count);
244 a30.extend(t.count);
245 a31.extend(t.count);
246 a32.extend(t.count);
247 a33.extend(t.count);
248 a34.extend(t.count);
249 ddindx := t.first;
250 indx := 1;
251 while true loop
252 a0(indx) := t(ddindx).excess_line_id;
253 a1(indx) := t(ddindx).organization_id;
254 a2(indx) := t(ddindx).inventory_item_id;
255 a3(indx) := t(ddindx).excess_quantity;
256 a4(indx) := t(ddindx).condition_code;
257 a5(indx) := t(ddindx).created_by;
258 a6(indx) := t(ddindx).creation_date;
259 a7(indx) := t(ddindx).last_updated_by;
260 a8(indx) := t(ddindx).last_update_date;
261 a9(indx) := t(ddindx).last_update_login;
262 a10(indx) := t(ddindx).subinventory_code;
263 a11(indx) := t(ddindx).returned_quantity;
264 a12(indx) := t(ddindx).current_return_qty;
265 a13(indx) := t(ddindx).requisition_line_id;
266 a14(indx) := t(ddindx).excess_status;
267 a15(indx) := t(ddindx).attribute_category;
268 a16(indx) := t(ddindx).attribute1;
269 a17(indx) := t(ddindx).attribute2;
270 a18(indx) := t(ddindx).attribute3;
271 a19(indx) := t(ddindx).attribute4;
272 a20(indx) := t(ddindx).attribute5;
273 a21(indx) := t(ddindx).attribute6;
274 a22(indx) := t(ddindx).attribute7;
275 a23(indx) := t(ddindx).attribute8;
276 a24(indx) := t(ddindx).attribute9;
277 a25(indx) := t(ddindx).attribute10;
278 a26(indx) := t(ddindx).attribute11;
279 a27(indx) := t(ddindx).attribute12;
280 a28(indx) := t(ddindx).attribute13;
281 a29(indx) := t(ddindx).attribute14;
282 a30(indx) := t(ddindx).attribute15;
283 a31(indx) := t(ddindx).security_group_id;
284 a32(indx) := t(ddindx).reason_code;
285 a33(indx) := t(ddindx).return_organization_id;
286 a34(indx) := t(ddindx).return_subinventory_name;
287 indx := indx+1;
288 if t.last =ddindx
289 then exit;
290 end if;
291 ddindx := t.next(ddindx);
292 end loop;
293 end if;
294 end if;
295 end rosetta_table_copy_out_p2;
296
297 procedure insert_row(px_excess_line_id in out nocopy NUMBER
298 , p_created_by NUMBER
299 , p_creation_date date
300 , p_last_updated_by NUMBER
301 , p_last_update_date date
302 , p_last_update_login NUMBER
303 , p_organization_id NUMBER
304 , p_subinventory_code VARCHAR2
305 , p_condition_code VARCHAR2
306 , p_inventory_item_id NUMBER
307 , p_excess_quantity NUMBER
308 , p_requisition_line_id NUMBER
309 , p_returned_quantity NUMBER
310 , p_current_return_qty NUMBER
311 , p_excess_status VARCHAR2
312 , p_return_org_id NUMBER
313 , p_return_sub_inv VARCHAR2
314 , p_attribute_category VARCHAR2
315 , p_attribute1 VARCHAR2
316 , p_attribute2 VARCHAR2
317 , p_attribute3 VARCHAR2
318 , p_attribute4 VARCHAR2
319 , p_attribute5 VARCHAR2
320 , p_attribute6 VARCHAR2
321 , p_attribute7 VARCHAR2
322 , p_attribute8 VARCHAR2
323 , p_attribute9 VARCHAR2
324 , p_attribute10 VARCHAR2
325 , p_attribute11 VARCHAR2
326 , p_attribute12 VARCHAR2
327 , p_attribute13 VARCHAR2
328 , p_attribute14 VARCHAR2
329 , p_attribute15 VARCHAR2
330 )
331
332 as
333 ddp_creation_date date;
334 ddp_last_update_date date;
335 ddindx binary_integer; indx binary_integer;
336 begin
337
338 -- copy data to the local IN or IN-OUT args, if any
339
340
341 ddp_creation_date := rosetta_g_miss_date_in_map(p_creation_date);
342
343
344 ddp_last_update_date := rosetta_g_miss_date_in_map(p_last_update_date);
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374 -- here's the delegated call to the old PL/SQL routine
375 csp_excess_lists_pkg.insert_row(px_excess_line_id,
376 p_created_by,
377 ddp_creation_date,
378 p_last_updated_by,
379 ddp_last_update_date,
380 p_last_update_login,
381 p_organization_id,
382 p_subinventory_code,
383 p_condition_code,
384 p_inventory_item_id,
385 p_excess_quantity,
386 p_requisition_line_id,
387 p_returned_quantity,
388 p_current_return_qty,
389 p_excess_status,
390 p_return_org_id,
391 p_return_sub_inv,
392 p_attribute_category,
393 p_attribute1,
394 p_attribute2,
395 p_attribute3,
396 p_attribute4,
397 p_attribute5,
398 p_attribute6,
399 p_attribute7,
400 p_attribute8,
401 p_attribute9,
402 p_attribute10,
403 p_attribute11,
404 p_attribute12,
405 p_attribute13,
406 p_attribute14,
407 p_attribute15);
408
409 -- copy data back from the local variables to OUT or IN-OUT args, if any
410
411
412
413
414
415
416
417
418
419
420
421
425
422
423
424
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442 end;
443
444 procedure update_row(p_excess_line_id NUMBER
445 , p_created_by NUMBER
446 , p_creation_date date
447 , p_last_updated_by NUMBER
448 , p_last_update_date date
449 , p_last_update_login NUMBER
450 , p_organization_id NUMBER
451 , p_subinventory_code VARCHAR2
452 , p_condition_code VARCHAR2
453 , p_inventory_item_id NUMBER
454 , p_excess_quantity NUMBER
455 , p_requisition_line_id NUMBER
456 , p_returned_quantity NUMBER
457 , p_current_return_qty NUMBER
458 , p_attribute_category VARCHAR2
459 , p_attribute1 VARCHAR2
460 , p_attribute2 VARCHAR2
461 , p_attribute3 VARCHAR2
462 , p_attribute4 VARCHAR2
463 , p_attribute5 VARCHAR2
464 , p_attribute6 VARCHAR2
465 , p_attribute7 VARCHAR2
466 , p_attribute8 VARCHAR2
467 , p_attribute9 VARCHAR2
468 , p_attribute10 VARCHAR2
469 , p_attribute11 VARCHAR2
470 , p_attribute12 VARCHAR2
471 , p_attribute13 VARCHAR2
472 , p_attribute14 VARCHAR2
473 , p_attribute15 VARCHAR2
474 )
475
476 as
477 ddp_creation_date date;
478 ddp_last_update_date date;
479 ddindx binary_integer; indx binary_integer;
480 begin
481
482 -- copy data to the local IN or IN-OUT args, if any
483
484
485 ddp_creation_date := rosetta_g_miss_date_in_map(p_creation_date);
486
487
488 ddp_last_update_date := rosetta_g_miss_date_in_map(p_last_update_date);
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515 -- here's the delegated call to the old PL/SQL routine
516 csp_excess_lists_pkg.update_row(p_excess_line_id,
517 p_created_by,
518 ddp_creation_date,
519 p_last_updated_by,
520 ddp_last_update_date,
521 p_last_update_login,
522 p_organization_id,
523 p_subinventory_code,
524 p_condition_code,
525 p_inventory_item_id,
526 p_excess_quantity,
527 p_requisition_line_id,
528 p_returned_quantity,
529 p_current_return_qty,
530 p_attribute_category,
531 p_attribute1,
532 p_attribute2,
533 p_attribute3,
534 p_attribute4,
535 p_attribute5,
536 p_attribute6,
537 p_attribute7,
538 p_attribute8,
539 p_attribute9,
540 p_attribute10,
541 p_attribute11,
542 p_attribute12,
543 p_attribute13,
544 p_attribute14,
545 p_attribute15);
546
547 -- copy data back from the local variables to OUT or IN-OUT args, if any
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577 end;
578
579 procedure lock_row(p_excess_line_id NUMBER
580 , p_created_by NUMBER
581 , p_creation_date date
582 , p_last_updated_by NUMBER
583 , p_last_update_date date
584 , p_last_update_login NUMBER
585 , p_organization_id NUMBER
586 , p_subinventory_code VARCHAR2
587 , p_condition_code VARCHAR2
588 , p_inventory_item_id NUMBER
589 , p_excess_quantity NUMBER
590 , p_requisition_line_id NUMBER
591 , p_returned_quantity NUMBER
592 , p_current_return_qty NUMBER
593 , p_attribute_category VARCHAR2
594 , p_attribute1 VARCHAR2
595 , p_attribute2 VARCHAR2
596 , p_attribute3 VARCHAR2
597 , p_attribute4 VARCHAR2
598 , p_attribute5 VARCHAR2
599 , p_attribute6 VARCHAR2
600 , p_attribute7 VARCHAR2
601 , p_attribute8 VARCHAR2
602 , p_attribute9 VARCHAR2
603 , p_attribute10 VARCHAR2
604 , p_attribute11 VARCHAR2
605 , p_attribute12 VARCHAR2
606 , p_attribute13 VARCHAR2
607 , p_attribute14 VARCHAR2
608 , p_attribute15 VARCHAR2
609 )
610
611 as
612 ddp_creation_date date;
613 ddp_last_update_date date;
614 ddindx binary_integer; indx binary_integer;
615 begin
616
617 -- copy data to the local IN or IN-OUT args, if any
618
619
620 ddp_creation_date := rosetta_g_miss_date_in_map(p_creation_date);
621
622
623 ddp_last_update_date := rosetta_g_miss_date_in_map(p_last_update_date);
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650 -- here's the delegated call to the old PL/SQL routine
651 csp_excess_lists_pkg.lock_row(p_excess_line_id,
652 p_created_by,
653 ddp_creation_date,
654 p_last_updated_by,
655 ddp_last_update_date,
659 p_condition_code,
656 p_last_update_login,
657 p_organization_id,
658 p_subinventory_code,
660 p_inventory_item_id,
661 p_excess_quantity,
662 p_requisition_line_id,
663 p_returned_quantity,
664 p_current_return_qty,
665 p_attribute_category,
666 p_attribute1,
667 p_attribute2,
668 p_attribute3,
669 p_attribute4,
670 p_attribute5,
671 p_attribute6,
672 p_attribute7,
673 p_attribute8,
674 p_attribute9,
675 p_attribute10,
676 p_attribute11,
677 p_attribute12,
678 p_attribute13,
679 p_attribute14,
680 p_attribute15);
681
682 -- copy data back from the local variables to OUT or IN-OUT args, if any
683
684
685
686
687
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 end;
713
714 end csp_excess_lists_pkg_w;