1 PACKAGE BODY okl_securitization_pvt_w AS
2 /* $Header: OKLESZSB.pls 115.3 2003/10/21 00:21:07 mvasudev 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
6 -- this is to workaround the JDBC bug regarding IN DATE of value GMiss
7 FUNCTION rosetta_g_miss_date_in_map(d DATE) RETURN DATE AS
8 BEGIN
9 IF d = rosetta_g_mistake_date THEN RETURN fnd_api.g_miss_date; END IF;
10 RETURN d;
11 END;
12
13 FUNCTION rosetta_g_miss_num_map(n NUMBER) RETURN NUMBER AS
14 a NUMBER := fnd_api.g_miss_num;
15 b NUMBER := 0-1962.0724;
16 BEGIN
17 IF n=a THEN RETURN b; END IF;
18 IF n=b THEN RETURN a; END IF;
19 RETURN n;
20 END;
21
22 PROCEDURE rosetta_table_copy_in_p29(t OUT nocopy okl_securitization_pvt.inv_agmt_chr_id_tbl_type, a0 JTF_NUMBER_TABLE
23 , a1 JTF_VARCHAR2_TABLE_500
24 ) AS
25 ddindx BINARY_INTEGER; indx BINARY_INTEGER;
26 BEGIN
27 IF a0 IS NOT NULL AND a0.COUNT > 0 THEN
28 IF a0.COUNT > 0 THEN
29 indx := a0.first;
30 ddindx := 1;
31 WHILE TRUE LOOP
32 t(ddindx).khr_id := rosetta_g_miss_num_map(a0(indx));
33 t(ddindx).process_code := a1(indx);
34 ddindx := ddindx+1;
35 IF a0.last =indx
36 THEN EXIT;
37 END IF;
38 indx := a0.NEXT(indx);
39 END LOOP;
40 END IF;
41 END IF;
42 END rosetta_table_copy_in_p29;
43 PROCEDURE rosetta_table_copy_out_p29(t okl_securitization_pvt.inv_agmt_chr_id_tbl_type, a0 OUT nocopy JTF_NUMBER_TABLE
44 , a1 OUT nocopy JTF_VARCHAR2_TABLE_500
45 ) AS
46 ddindx BINARY_INTEGER; indx BINARY_INTEGER;
47 BEGIN
48 IF t IS NULL OR t.COUNT = 0 THEN
49 a0 := JTF_NUMBER_TABLE();
50 a1 := JTF_VARCHAR2_TABLE_500();
51 ELSE
52 a0 := JTF_NUMBER_TABLE();
53 a1 := JTF_VARCHAR2_TABLE_500();
54 IF t.COUNT > 0 THEN
55 a0.extend(t.COUNT);
56 a1.extend(t.COUNT);
57 ddindx := t.first;
58 indx := 1;
59 WHILE TRUE LOOP
60 a0(indx) := rosetta_g_miss_num_map(t(ddindx).khr_id);
61 a1(indx) := t(ddindx).process_code;
62 indx := indx+1;
63 IF t.last =ddindx
64 THEN EXIT;
65 END IF;
66 ddindx := t.NEXT(ddindx);
67 END LOOP;
68 END IF;
69 END IF;
70 END rosetta_table_copy_out_p29;
71
72 PROCEDURE check_khr_securitized(p_api_version NUMBER
73 , p_init_msg_list VARCHAR2
74 , x_return_status OUT nocopy VARCHAR2
75 , x_msg_count OUT nocopy NUMBER
76 , x_msg_data OUT nocopy VARCHAR2
77 , p_khr_id NUMBER
78 , p_effective_date DATE
79 , p_effective_date_operator VARCHAR2
80 , p_stream_type_subclass VARCHAR2
81 , x_value OUT nocopy VARCHAR2
82 , p10_a0 OUT nocopy JTF_NUMBER_TABLE
83 , p10_a1 OUT nocopy JTF_VARCHAR2_TABLE_500
84 )
85
86 AS
87 ddp_effective_date DATE;
88 ddx_inv_agmt_chr_id_tbl okl_securitization_pvt.inv_agmt_chr_id_tbl_type;
89 ddindx BINARY_INTEGER; indx BINARY_INTEGER;
90 BEGIN
91
92 -- copy data to the local IN or IN-OUT args, if any
93
94
95
96
97
98
99 ddp_effective_date := rosetta_g_miss_date_in_map(p_effective_date);
100
101
102
103
104
105 -- here's the delegated call to the old PL/SQL routine
106 okl_securitization_pvt.check_khr_securitized(p_api_version,
107 p_init_msg_list,
108 x_return_status,
109 x_msg_count,
110 x_msg_data,
111 p_khr_id,
112 ddp_effective_date,
113 p_effective_date_operator,
114 p_stream_type_subclass,
115 x_value,
116 ddx_inv_agmt_chr_id_tbl);
117
118 -- copy data back from the local variables to OUT or IN-OUT args, if any
119
120
121
122
123
124
125
126
127
128
129 okl_securitization_pvt_w.rosetta_table_copy_out_p29(ddx_inv_agmt_chr_id_tbl, p10_a0
130 , p10_a1
131 );
132 END;
133
134 PROCEDURE check_kle_securitized(p_api_version NUMBER
135 , p_init_msg_list VARCHAR2
136 , x_return_status OUT nocopy VARCHAR2
137 , x_msg_count OUT nocopy NUMBER
138 , x_msg_data OUT nocopy VARCHAR2
139 , p_kle_id NUMBER
140 , p_effective_date DATE
141 , p_effective_date_operator VARCHAR2
142 , p_stream_type_subclass VARCHAR2
143 , x_value OUT nocopy VARCHAR2
144 , p10_a0 OUT nocopy JTF_NUMBER_TABLE
145 , p10_a1 OUT nocopy JTF_VARCHAR2_TABLE_500
146 )
147
148 AS
149 ddp_effective_date DATE;
150 ddx_inv_agmt_chr_id_tbl okl_securitization_pvt.inv_agmt_chr_id_tbl_type;
151 ddindx BINARY_INTEGER; indx BINARY_INTEGER;
152 BEGIN
153
154 -- copy data to the local IN or IN-OUT args, if any
155
156
157
158
159
160
161 ddp_effective_date := rosetta_g_miss_date_in_map(p_effective_date);
162
163
164
165
166
167 -- here's the delegated call to the old PL/SQL routine
168 okl_securitization_pvt.check_kle_securitized(p_api_version,
169 p_init_msg_list,
170 x_return_status,
171 x_msg_count,
172 x_msg_data,
173 p_kle_id,
174 ddp_effective_date,
175 p_effective_date_operator,
176 p_stream_type_subclass,
177 x_value,
178 ddx_inv_agmt_chr_id_tbl);
179
180 -- copy data back from the local variables to OUT or IN-OUT args, if any
181
182
183
184
185
186
187
188
189
190
191 okl_securitization_pvt_w.rosetta_table_copy_out_p29(ddx_inv_agmt_chr_id_tbl, p10_a0
192 , p10_a1
193 );
194 END;
195
196 PROCEDURE check_sty_securitized(p_api_version NUMBER
197 , p_init_msg_list VARCHAR2
198 , x_return_status OUT nocopy VARCHAR2
199 , x_msg_count OUT nocopy NUMBER
200 , x_msg_data OUT nocopy VARCHAR2
201 , p_khr_id NUMBER
202 , p_effective_date DATE
203 , p_effective_date_operator VARCHAR2
204 , p_sty_id NUMBER
205 , x_value OUT nocopy VARCHAR2
206 , x_inv_agmt_chr_id OUT nocopy NUMBER
207 )
208
209 AS
210 ddp_effective_date DATE;
211 ddindx BINARY_INTEGER; indx BINARY_INTEGER;
212 BEGIN
213
214 -- copy data to the local IN or IN-OUT args, if any
215
216
217
218
219
220
221 ddp_effective_date := rosetta_g_miss_date_in_map(p_effective_date);
222
223
224
225
226
227 -- here's the delegated call to the old PL/SQL routine
228 okl_securitization_pvt.check_sty_securitized(p_api_version,
229 p_init_msg_list,
230 x_return_status,
231 x_msg_count,
232 x_msg_data,
233 p_khr_id,
234 ddp_effective_date,
235 p_effective_date_operator,
236 p_sty_id,
237 x_value,
238 x_inv_agmt_chr_id);
239
240 -- copy data back from the local variables to OUT or IN-OUT args, if any
241
242
243
244
245
246
247
248
249
250
251 END;
252
253 PROCEDURE check_stm_securitized(p_api_version NUMBER
254 , p_init_msg_list VARCHAR2
255 , x_return_status OUT nocopy VARCHAR2
256 , x_msg_count OUT nocopy NUMBER
257 , x_msg_data OUT nocopy VARCHAR2
258 , p_stm_id NUMBER
259 , p_effective_date DATE
260 , x_value OUT nocopy VARCHAR2
261 )
262
263 AS
264 ddp_effective_date DATE;
265 ddindx BINARY_INTEGER; indx BINARY_INTEGER;
266 BEGIN
267
268 -- copy data to the local IN or IN-OUT args, if any
269
270
271
272
273
274
275 ddp_effective_date := rosetta_g_miss_date_in_map(p_effective_date);
276
277
278 -- here's the delegated call to the old PL/SQL routine
279 okl_securitization_pvt.check_stm_securitized(p_api_version,
280 p_init_msg_list,
281 x_return_status,
282 x_msg_count,
283 x_msg_data,
284 p_stm_id,
285 ddp_effective_date,
286 x_value);
287
288 -- copy data back from the local variables to OUT or IN-OUT args, if any
289
290
291
292
293
294
295
296 END;
297
298 PROCEDURE check_sel_securitized(p_api_version NUMBER
299 , p_init_msg_list VARCHAR2
300 , x_return_status OUT nocopy VARCHAR2
301 , x_msg_count OUT nocopy NUMBER
302 , x_msg_data OUT nocopy VARCHAR2
303 , p_sel_id NUMBER
304 , p_effective_date DATE
305 , x_value OUT nocopy VARCHAR2
306 )
307
308 AS
309 ddp_effective_date DATE;
310 ddindx BINARY_INTEGER; indx BINARY_INTEGER;
311 BEGIN
312
313 -- copy data to the local IN or IN-OUT args, if any
314
315
316
317
318
319
320 ddp_effective_date := rosetta_g_miss_date_in_map(p_effective_date);
321
322
323 -- here's the delegated call to the old PL/SQL routine
324 okl_securitization_pvt.check_sel_securitized(p_api_version,
325 p_init_msg_list,
326 x_return_status,
327 x_msg_count,
328 x_msg_data,
329 p_sel_id,
330 ddp_effective_date,
331 x_value);
332
333 -- copy data back from the local variables to OUT or IN-OUT args, if any
334
335
336
337
338
339
340
341 END;
342
343 PROCEDURE buyback_asset(p_api_version NUMBER
344 , p_init_msg_list VARCHAR2
345 , x_return_status OUT nocopy VARCHAR2
346 , x_msg_count OUT nocopy NUMBER
347 , x_msg_data OUT nocopy VARCHAR2
348 , p_kle_id NUMBER
349 , p_effective_date DATE
350 )
351
352 AS
353 ddp_effective_date DATE;
354 ddindx BINARY_INTEGER; indx BINARY_INTEGER;
355 BEGIN
356
357 -- copy data to the local IN or IN-OUT args, if any
358
359
360
361
362
363
364 ddp_effective_date := rosetta_g_miss_date_in_map(p_effective_date);
365
366 -- here's the delegated call to the old PL/SQL routine
367 okl_securitization_pvt.buyback_asset(p_api_version,
368 p_init_msg_list,
369 x_return_status,
370 x_msg_count,
371 x_msg_data,
372 p_kle_id,
373 ddp_effective_date);
374
375 -- copy data back from the local variables to OUT or IN-OUT args, if any
376
377
378
379
380
381
382 END;
383
384 PROCEDURE buyback_contract(p_api_version NUMBER
385 , p_init_msg_list VARCHAR2
386 , x_return_status OUT nocopy VARCHAR2
387 , x_msg_count OUT nocopy NUMBER
388 , x_msg_data OUT nocopy VARCHAR2
389 , p_khr_id NUMBER
390 , p_effective_date DATE
391 )
392
393 AS
394 ddp_effective_date DATE;
395 ddindx BINARY_INTEGER; indx BINARY_INTEGER;
396 BEGIN
397
398 -- copy data to the local IN or IN-OUT args, if any
399
400
401
402
403
404
405 ddp_effective_date := rosetta_g_miss_date_in_map(p_effective_date);
406
407 -- here's the delegated call to the old PL/SQL routine
408 okl_securitization_pvt.buyback_contract(p_api_version,
409 p_init_msg_list,
410 x_return_status,
411 x_msg_count,
412 x_msg_data,
413 p_khr_id,
414 ddp_effective_date);
415
416 -- copy data back from the local variables to OUT or IN-OUT args, if any
417
418
419
420
421
422
423 END;
424
425 PROCEDURE process_khr_investor_rules(p_api_version NUMBER
426 , p_init_msg_list VARCHAR2
427 , x_return_status OUT nocopy VARCHAR2
428 , x_msg_count OUT nocopy NUMBER
429 , x_msg_data OUT nocopy VARCHAR2
430 , p_khr_id NUMBER
431 , p_effective_date DATE
432 , p_rgd_code VARCHAR2
433 , p_rdf_code VARCHAR2
434 , x_process_code OUT nocopy VARCHAR2
435 , p10_a0 OUT nocopy JTF_NUMBER_TABLE
436 , p10_a1 OUT nocopy JTF_VARCHAR2_TABLE_500
437 )
438
439 AS
440 ddp_effective_date DATE;
441 ddx_inv_agmt_chr_id_tbl okl_securitization_pvt.inv_agmt_chr_id_tbl_type;
442 ddindx BINARY_INTEGER; indx BINARY_INTEGER;
443 BEGIN
444
445 -- copy data to the local IN or IN-OUT args, if any
446
447
448
449
450
451
452 ddp_effective_date := rosetta_g_miss_date_in_map(p_effective_date);
453
454
455
456
457
458 -- here's the delegated call to the old PL/SQL routine
459 okl_securitization_pvt.process_khr_investor_rules(p_api_version,
460 p_init_msg_list,
461 x_return_status,
462 x_msg_count,
463 x_msg_data,
464 p_khr_id,
465 ddp_effective_date,
466 p_rgd_code,
467 p_rdf_code,
468 x_process_code,
469 ddx_inv_agmt_chr_id_tbl);
470
471 -- copy data back from the local variables to OUT or IN-OUT args, if any
472
473
474
475
476
477
478
479
480
481
482 okl_securitization_pvt_w.rosetta_table_copy_out_p29(ddx_inv_agmt_chr_id_tbl, p10_a0
483 , p10_a1
484 );
485 END;
486
487 PROCEDURE process_kle_investor_rules(p_api_version NUMBER
488 , p_init_msg_list VARCHAR2
489 , x_return_status OUT nocopy VARCHAR2
490 , x_msg_count OUT nocopy NUMBER
491 , x_msg_data OUT nocopy VARCHAR2
492 , p_kle_id NUMBER
493 , p_effective_date DATE
494 , p_rgd_code VARCHAR2
495 , p_rdf_code VARCHAR2
496 , x_process_code OUT nocopy VARCHAR2
497 , p10_a0 OUT nocopy JTF_NUMBER_TABLE
498 , p10_a1 OUT nocopy JTF_VARCHAR2_TABLE_500
499 )
500
501 AS
502 ddp_effective_date DATE;
503 ddx_inv_agmt_chr_id_tbl okl_securitization_pvt.inv_agmt_chr_id_tbl_type;
504 ddindx BINARY_INTEGER; indx BINARY_INTEGER;
505 BEGIN
506
507 -- copy data to the local IN or IN-OUT args, if any
508
509
510
511
512
513
514 ddp_effective_date := rosetta_g_miss_date_in_map(p_effective_date);
515
516
517
518
519
520 -- here's the delegated call to the old PL/SQL routine
521 okl_securitization_pvt.process_kle_investor_rules(p_api_version,
522 p_init_msg_list,
523 x_return_status,
524 x_msg_count,
525 x_msg_data,
526 p_kle_id,
527 ddp_effective_date,
528 p_rgd_code,
529 p_rdf_code,
530 x_process_code,
531 ddx_inv_agmt_chr_id_tbl);
532
533 -- copy data back from the local variables to OUT or IN-OUT args, if any
534
535
536
537
538
539
540
541
542
543
544 okl_securitization_pvt_w.rosetta_table_copy_out_p29(ddx_inv_agmt_chr_id_tbl, p10_a0
545 , p10_a1
546 );
547 END;
548
549 PROCEDURE buyback_pool_contents(p_api_version NUMBER
550 , p_init_msg_list VARCHAR2
551 , p_khr_id NUMBER
552 , p_pol_id NUMBER
553 , p_stream_type_subclass VARCHAR2
554 , p_effective_date DATE
555 , x_return_status OUT nocopy VARCHAR2
556 , x_msg_count OUT nocopy NUMBER
557 , x_msg_data OUT nocopy VARCHAR2
558 )
559
560 AS
561 ddp_effective_date DATE;
562 ddindx BINARY_INTEGER; indx BINARY_INTEGER;
563 BEGIN
564
565 -- copy data to the local IN or IN-OUT args, if any
566
567
568
569
570
571 ddp_effective_date := rosetta_g_miss_date_in_map(p_effective_date);
572
573
574
575
576 -- here's the delegated call to the old PL/SQL routine
577 okl_securitization_pvt.buyback_pool_contents(p_api_version,
578 p_init_msg_list,
579 p_khr_id,
580 p_pol_id,
581 p_stream_type_subclass,
582 ddp_effective_date,
583 x_return_status,
584 x_msg_count,
585 x_msg_data);
586
587 -- copy data back from the local variables to OUT or IN-OUT args, if any
588
589
590
591
592
593
594
595
596 END;
597
598 PROCEDURE modify_pool_contents(p_api_version NUMBER
599 , p_init_msg_list VARCHAR2
600 , p_transaction_reason VARCHAR2
601 , p_khr_id NUMBER
602 , p_kle_id NUMBER
603 , p_stream_type_subclass VARCHAR2
604 , p_transaction_date DATE
605 , p_effective_date DATE
606 , x_return_status OUT nocopy VARCHAR2
607 , x_msg_count OUT nocopy NUMBER
608 , x_msg_data OUT nocopy VARCHAR2
609 )
610
611 AS
612 ddp_transaction_date DATE;
613 ddp_effective_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
621
622
623
624 ddp_transaction_date := rosetta_g_miss_date_in_map(p_transaction_date);
625
626 ddp_effective_date := rosetta_g_miss_date_in_map(p_effective_date);
627
628
629
630
631 -- here's the delegated call to the old PL/SQL routine
632 okl_securitization_pvt.modify_pool_contents(p_api_version,
633 p_init_msg_list,
634 p_transaction_reason,
635 p_khr_id,
636 p_kle_id,
637 p_stream_type_subclass,
638 ddp_transaction_date,
639 ddp_effective_date,
640 x_return_status,
641 x_msg_count,
642 x_msg_data);
643
644 -- copy data back from the local variables to OUT or IN-OUT args, if any
645
646
647
648
649
650
651
652
653
654
655 END;
656
657 PROCEDURE modify_pool_contents(p_api_version NUMBER
658 , p_init_msg_list VARCHAR2
659 , p_transaction_reason VARCHAR2
660 , p_khr_id NUMBER
661 , p_kle_id NUMBER
662 , p5_a0 JTF_NUMBER_TABLE
663 , p_transaction_date DATE
664 , p_effective_date DATE
665 , x_return_status OUT nocopy VARCHAR2
666 , x_msg_count OUT nocopy NUMBER
667 , x_msg_data OUT nocopy VARCHAR2
668 )
669
670 AS
671 ddp_split_kle_ids okl_securitization_pvt.cle_tbl_type;
672 ddp_transaction_date DATE;
673 ddp_effective_date DATE;
674 ddindx BINARY_INTEGER; indx BINARY_INTEGER;
675 BEGIN
676
677 -- copy data to the local IN or IN-OUT args, if any
678
679
680
681
682
683 okl_split_asset_pvt_w.rosetta_table_copy_in_p10(ddp_split_kle_ids, p5_a0
684 );
685
686 ddp_transaction_date := rosetta_g_miss_date_in_map(p_transaction_date);
687
688 ddp_effective_date := rosetta_g_miss_date_in_map(p_effective_date);
689
690
691
692
693 -- here's the delegated call to the old PL/SQL routine
694 okl_securitization_pvt.modify_pool_contents(p_api_version,
695 p_init_msg_list,
696 p_transaction_reason,
697 p_khr_id,
698 p_kle_id,
699 ddp_split_kle_ids,
700 ddp_transaction_date,
701 ddp_effective_date,
702 x_return_status,
703 x_msg_count,
704 x_msg_data);
705
706 -- copy data back from the local variables to OUT or IN-OUT args, if any
707
708
709
710
711
712
713
714
715
716
717 END;
718
719 END okl_securitization_pvt_w;