[Home] [Help]
PACKAGE BODY: APPS.OKL_STREAM_MIGRATION_PVT
Source
1 PACKAGE BODY OKL_STREAM_MIGRATION_PVT AS
2 /* $Header: OKLRSMGB.pls 120.9 2006/07/18 10:55:51 dkagrawa noship $ */
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE create_strm_gen_template
6 ---------------------------------------------------------------------------
7 -- Start of comments
8 --
9 -- Procedure Name : create_strm_gen_template
10 -- Description : Procedure to create new stream templates and lines
11 -- Business Rules :
12 -- Parameters :
13 -- Version : 1.0
14 -- End of comments
15 ---------------------------------------------------------------------------
16
17 PROCEDURE create_strm_gen_template(p_api_version IN NUMBER
18 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
19 ,x_return_status OUT NOCOPY VARCHAR2
20 ,x_msg_count OUT NOCOPY NUMBER
21 ,x_msg_data OUT NOCOPY VARCHAR2
22 ,p_gtsv_rec IN gtsv_rec_type
23 ,p_gttv_rec IN gttv_rec_type
24 ,p_gtpv_tbl IN gtpv_tbl_type
25 ,p_gtlv_tbl IN gtlv_tbl_type
26 ,x_gttv_rec OUT NOCOPY gttv_rec_type -- Return the Template Info
27 ) IS
28
29 l_api_name CONSTANT VARCHAR2(40) := 'create_strm_gen_template';
30 l_api_version CONSTANT NUMBER := 1.0;
31 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
32
33 l_init_msg_list VARCHAR2(1);
34 l_msg_count NUMBER;
35 l_msg_data VARCHAR2(2000);
36
37 l_gtsv_rec_in gtsv_rec_type;
38 l_gttv_rec_in gttv_rec_type;
39 l_gtpv_tbl_in gtpv_tbl_type;
40 l_gtlv_tbl_in gtlv_tbl_type;
41
42 l_gtsv_rec_out gtsv_rec_type;
43 l_gttv_rec_out gttv_rec_type;
44 l_gtpv_tbl_out gtpv_tbl_type;
45 l_gtlv_tbl_out gtlv_tbl_type;
46 i NUMBER;
47
48 BEGIN
49 -- Perform the Initializations
50 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
51
52 l_return_status := Okl_Api.START_ACTIVITY( l_api_name
53 ,g_pkg_name
54 ,p_init_msg_list
55 ,l_api_version
56 ,p_api_version
57 ,'_PVT'
58 ,x_return_status);
59 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
60 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
61 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
62 RAISE Okl_Api.G_EXCEPTION_ERROR;
63 END IF;
64
65 l_gtsv_rec_in := p_gtsv_rec;
66 -- Call the insert method of the Stream Generation Template Sets
67 Okl_Gts_Pvt.insert_row(
68 p_api_version => l_api_version
69 ,p_init_msg_list => p_init_msg_list
70 ,x_return_status => l_return_status
71 ,x_msg_count => l_msg_count
72 ,x_msg_data => l_msg_data
73 ,p_gtsv_rec => l_gtsv_rec_in
74 ,x_gtsv_rec => l_gtsv_rec_out
75 );
76 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
77 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
78 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
79 RAISE Okl_Api.G_EXCEPTION_ERROR;
80 END IF;
81
82 -- Populate the Stream Generate Template Records GTS_ID
83 -- with the ID returned into the l_gtsv_rec_out
84 l_gttv_rec_in := p_gttv_rec;
85 l_gttv_rec_in.gts_id := l_gtsv_rec_out.id;
86 l_gttv_rec_in.version := '1.0';
87 l_gttv_rec_in.tmpt_status := 'NEW';
88
89 -- Call the insert method of the Stream Generation Template
90 Okl_Gtt_Pvt.insert_row(
91 p_api_version => l_api_version
92 ,p_init_msg_list => p_init_msg_list
93 ,x_return_status => l_return_status
94 ,x_msg_count => l_msg_count
95 ,x_msg_data => l_msg_data
96 ,p_gttv_rec => l_gttv_rec_in
97 ,x_gttv_rec => l_gttv_rec_out
98 );
99 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
100 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
101 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
102 RAISE Okl_Api.G_EXCEPTION_ERROR;
103 END IF;
104
105
106 -- Now Need to loop through the entire table and update the gtt_id
107 -- in the pricing parameters.
108 -- Making sure PL/SQL table has records in it before passing
109 IF (p_gtpv_tbl.COUNT > 0) THEN
110
111 i := p_gtpv_tbl.FIRST;
112 LOOP
113 l_gtpv_tbl_in(i) := p_gtpv_tbl(i);
114 l_gtpv_tbl_in(i).gtt_id := l_gttv_rec_out.id;
115 EXIT WHEN (i = p_gtpv_tbl.LAST);
116 i := p_gtpv_tbl.NEXT(i);
117 END LOOP;
118
119 -- Call the TAPI Procedcure to perform the actual inserts
120 Okl_Gtp_Pvt.insert_row(
121 p_api_version => l_api_version
122 ,p_init_msg_list => p_init_msg_list
123 ,x_return_status => l_return_status
124 ,x_msg_count => l_msg_count
125 ,x_msg_data => l_msg_data
126 ,p_gtpv_tbl => l_gtpv_tbl_in
127 ,x_gtpv_tbl => l_gtpv_tbl_out
128 );
129 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
130 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
131 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
132 RAISE Okl_Api.G_EXCEPTION_ERROR;
133 END IF;
134 END IF;
135
136 -- Making sure PL/SQL table has records in it before passing
137 IF (p_gtlv_tbl.COUNT > 0) THEN
138 i := p_gtlv_tbl.FIRST;
139 LOOP
140 l_gtlv_tbl_in(i) := p_gtlv_tbl(i);
141 l_gtlv_tbl_in(i).gtt_id := l_gttv_rec_out.id;
142
143 -- Commented out by santonyr
144 -- l_gtlv_tbl_in(i).primary_yn := G_INIT_PRIMARY_YN_YES;
145
146 EXIT WHEN (i = p_gtlv_tbl.LAST);
147 i := p_gtlv_tbl.NEXT(i);
148 END LOOP;
149
150 -- Call the TAPI Procedcure to perform the actual inserts
151 Okl_Gtl_Pvt.insert_row(
152 p_api_version => l_api_version
153 ,p_init_msg_list => p_init_msg_list
154 ,x_return_status => l_return_status
155 ,x_msg_count => l_msg_count
156 ,x_msg_data => l_msg_data
157 ,p_gtlv_tbl => l_gtlv_tbl_in
158 ,x_gtlv_tbl => l_gtlv_tbl_out
159 );
160 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
161 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
162 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
163 RAISE Okl_Api.G_EXCEPTION_ERROR;
164 END IF;
165 END IF;
166
167 x_gttv_rec := l_gttv_rec_out;
168 x_return_status := l_return_status;
169 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
170
171
172 EXCEPTION
173 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
174 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
175 (l_api_name,
176 G_PKG_NAME,
177 'OKL_API.G_RET_STS_ERROR',
178 x_msg_count,
179 x_msg_data,
180 '_PVT');
181 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
182 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
183 ( l_api_name,
184 G_PKG_NAME,
185 'OKL_API.G_RET_STS_UNEXP_ERROR',
186 x_msg_count,
187 x_msg_data,
188 '_PVT');
189 WHEN OTHERS THEN
190 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
191 ( l_api_name,
192 G_PKG_NAME,
193 'OTHERS',
194 x_msg_count,
195 x_msg_data,
196 '_PVT');
197
198 END create_strm_gen_template;
199
200 ---------------------------------------------------------------------------
201 -- PROCEDURE insert_template_lines
202 ---------------------------------------------------------------------------
203 -- Start of comments
204 --
205 -- Procedure Name : insert_template_lines
206 -- Description : Procedure to create/update stream templates and lines
207 -- Business Rules :
208 -- Parameters :
209 -- Version : 1.0
210 -- End of comments
211 ---------------------------------------------------------------------------
212
213
214 PROCEDURE insert_template_lines(p_api_version IN NUMBER
215 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
216 ,x_return_status OUT NOCOPY VARCHAR2
217 ,x_msg_count OUT NOCOPY NUMBER
218 ,x_msg_data OUT NOCOPY VARCHAR2
219 ,p_gtlv_tbl IN gtlv_tbl_type)
220 IS
221
222 l_api_name CONSTANT VARCHAR2(40) := 'create_strm_gen_template';
223 l_api_version CONSTANT NUMBER := 1.0;
224 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
225
226 l_init_msg_list VARCHAR2(1);
227 l_msg_count NUMBER;
228 l_msg_data VARCHAR2(2000);
229
230 l_gtlv_tbl_in gtlv_tbl_type;
231 l_gtlv_tbl_out gtlv_tbl_type;
232
233 i NUMBER;
234
235 BEGIN
236 -- Perform the Initializations
237 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
238
239 l_return_status := Okl_Api.START_ACTIVITY( l_api_name
240 ,g_pkg_name
241 ,p_init_msg_list
242 ,l_api_version
243 ,p_api_version
244 ,'_PVT'
245 ,x_return_status);
246 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
247 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
248 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
249 RAISE Okl_Api.G_EXCEPTION_ERROR;
250 END IF;
251
252 -- Making sure PL/SQL table has records in it before passing
253 IF (p_gtlv_tbl.COUNT > 0) THEN
254 i := p_gtlv_tbl.FIRST;
255 LOOP
256 l_gtlv_tbl_in(i) := p_gtlv_tbl(i);
257 EXIT WHEN (i = p_gtlv_tbl.LAST);
258 i := p_gtlv_tbl.NEXT(i);
259 END LOOP;
260
261 -- Call the TAPI Procedcure to perform the actual inserts
262 Okl_Gtl_Pvt.insert_row(
263 p_api_version => l_api_version
264 ,p_init_msg_list => p_init_msg_list
265 ,x_return_status => l_return_status
266 ,x_msg_count => l_msg_count
267 ,x_msg_data => l_msg_data
268 ,p_gtlv_tbl => l_gtlv_tbl_in
269 ,x_gtlv_tbl => l_gtlv_tbl_out);
270
271 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
272 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
273 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
274 RAISE Okl_Api.G_EXCEPTION_ERROR;
275 END IF;
276 END IF;
277
278 x_return_status := l_return_status;
279 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
280
281
282 EXCEPTION
283 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
284 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
285 (l_api_name,
286 G_PKG_NAME,
287 'OKL_API.G_RET_STS_ERROR',
288 x_msg_count,
289 x_msg_data,
290 '_PVT');
291 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
292 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
293 ( l_api_name,
294 G_PKG_NAME,
295 'OKL_API.G_RET_STS_UNEXP_ERROR',
296 x_msg_count,
297 x_msg_data,
298 '_PVT');
299 WHEN OTHERS THEN
300 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
301 ( l_api_name,
302 G_PKG_NAME,
303 'OTHERS',
304 x_msg_count,
305 x_msg_data,
306 '_PVT');
307
308 END insert_template_lines;
309
310
311 ---------------------------------------------------------------------------
312 -- PROCEDURE Migrate_Sty_Fee_Line
313 ---------------------------------------------------------------------------
314 -- Start of comments
315 --
316 -- Procedure Name : Migrate_Sty_Fee_Line
317 -- Description : Procedure to migrate Feel Line stream types
318 -- Business Rules :
319 -- Parameters :
320 -- Version : 1.0
321 -- End of comments
322 ---------------------------------------------------------------------------
323
324
325 PROCEDURE Migrate_Sty_Fee_Line (p_sty_id NUMBER,
326 x_sty_purpose OUT NOCOPY VARCHAR2)
327 IS
328
329 l_sty_used_in_pth_fee VARCHAR2(1);
330 l_sty_used_in_non_pth_fee VARCHAR2(1);
331 l_sty_used_in_pth_service VARCHAR2(1);
332 l_dummy_id NUMBER;
333 l_new_sty_purpose VARCHAR2(100);
334 l_fee_type VARCHAR2(100);
335
336 -- Cursor to find if a stream type is used in Fee Lines.
337
338 CURSOR fee_pth_csr (l_sty_id NUMBER) IS
339 SELECT
340 DISTINCT
341 kle.fee_type
342 FROM
343 okc_k_items cim,
344 okl_k_lines kle,
345 okc_k_lines_b cleb,
346 okc_line_styles_b lseb,
347 okc_k_headers_b chrb,
348 okl_k_headers khr,
349 okl_product_parameters_v pdt
350 WHERE
351 cim.cle_id = cleb.id AND
352 cleb.lse_id = lseb.id AND
353 lseb.lty_code = 'FEE' AND
354 cleb.chr_id = chrb.id AND
355 kle.id = cleb.id AND
356 chrb.scs_code IN ('LEASE','QUOTE') AND
357 kle.fee_type = 'PASSTHROUGH' AND
358 chrb.id = khr.id AND
359 khr.pdt_id = pdt.id AND
360 cim.object1_id1 = l_sty_id AND
361 pdt.DEAL_TYPE IN ('LEASEOP', 'LEASEDF', 'LEASEST', 'LOAN', 'LOAN-REVOLVING', 'SALE') AND
362 pdt.TAX_OWNER IN ('LESSOR', 'LESSEE');
363
364
365 CURSOR fee_non_pth_csr (l_sty_id NUMBER) IS
366 SELECT
367 DISTINCT
368 kle.fee_type
369 FROM
370 okc_k_items cim,
371 okl_k_lines kle,
372 okc_k_lines_b cleb,
373 okc_line_styles_b lseb,
374 okc_k_headers_b chrb,
375 okl_k_headers khr,
376 okl_product_parameters_v pdt
377 WHERE
378 cim.cle_id = cleb.id AND
379 cleb.lse_id = lseb.id AND
380 lseb.lty_code = 'FEE' AND
381 cleb.chr_id = chrb.id AND
382 kle.id = cleb.id AND
383 chrb.scs_code IN ('LEASE','QUOTE') AND
384 kle.fee_type <> 'PASSTHROUGH' AND
385 chrb.id = khr.id AND
386 khr.pdt_id = pdt.id AND
387 cim.object1_id1 = l_sty_id AND
388 pdt.DEAL_TYPE IN ('LEASEOP', 'LEASEDF', 'LEASEST', 'LOAN', 'LOAN-REVOLVING', 'SALE') AND
389 pdt.TAX_OWNER IN ('LESSOR', 'LESSEE');
390
391 -- Cursor to find if a stream type is used in Passthrough service
392
393 CURSOR pth_service_csr (l_sty_id NUMBER )
394 IS
395 SELECT rul.object1_id1
396 FROM okc_rules_b rul,
397 okc_rule_groups_b rgp,
398 okc_k_lines_b cleb,
399 okc_line_styles_b lseb,
400 okc_k_headers_b chrb,
401 okl_k_headers khr,
402 okl_product_parameters_v pdt
403 WHERE rgp.cle_id = cleb.id
404 AND rul.rgp_id = rgp.id
405 AND rul.rule_information_category = 'LASTRM'
406 AND rgp.dnz_chr_id = chrb.id
407 AND rgp.rgd_code = 'LAPSTH'
408 AND rul.dnz_chr_id = chrb.id
409 AND cleb.lse_id = lseb.id
410 AND lseb.lty_code = 'SOLD_SERVICE'
411 AND cleb.chr_id = chrb.id
412 AND chrb.scs_code IN ('LEASE','QUOTE')
413 AND chrb.id = khr.id
414 AND khr.pdt_id = pdt.id
415 AND rul.object1_id1 = l_sty_id
416 AND pdt.DEAL_TYPE IN ('LEASEOP', 'LEASEDF', 'LEASEST', 'LOAN', 'LOAN-REVOLVING', 'SALE')
417 AND pdt.TAX_OWNER IN ('LESSOR', 'LESSEE');
418
419 BEGIN
420
421 OPEN fee_pth_csr (p_sty_id) ;
422 FETCH fee_pth_csr INTO l_fee_type;
423 IF fee_pth_csr%NOTFOUND THEN
424 l_sty_used_in_pth_fee := 'N';
425 ELSE
426 l_sty_used_in_pth_fee := 'Y';
427 END IF;
428 CLOSE fee_pth_csr;
429
430 OPEN fee_non_pth_csr (p_sty_id);
431 FETCH fee_non_pth_csr INTO l_fee_type;
432 IF fee_non_pth_csr%NOTFOUND THEN
433 l_sty_used_in_non_pth_fee := 'N';
434 ELSE
435 l_sty_used_in_non_pth_fee := 'Y';
436 END IF;
437 CLOSE fee_non_pth_csr;
438
439 OPEN pth_service_csr (p_sty_id);
440 FETCH pth_service_csr INTO l_dummy_id;
441 IF pth_service_csr%NOTFOUND THEN
442 l_sty_used_in_pth_service := 'N';
443 ELSE
444 l_sty_used_in_pth_service := 'Y';
445 END IF;
446 CLOSE pth_service_csr;
447
448 IF l_sty_used_in_pth_fee = 'Y' AND
449 l_sty_used_in_non_pth_fee = 'N' AND
450 l_sty_used_in_pth_service = 'N' THEN
451 l_new_sty_purpose := 'PASS_THROUGH_FEE';
452 ELSIF l_sty_used_in_pth_fee = 'N' AND
453 l_sty_used_in_non_pth_fee = 'Y' AND
454 l_sty_used_in_pth_service = 'N' THEN
455 l_new_sty_purpose := 'EXPENSE';
456 ELSIF l_sty_used_in_pth_fee = 'N' AND
457 l_sty_used_in_non_pth_fee = 'N' AND
458 l_sty_used_in_pth_service = 'Y' THEN
459 l_new_sty_purpose := 'PASS_THROUGH_SERVICE';
460 ELSIF l_sty_used_in_pth_fee = 'N' AND
461 l_sty_used_in_non_pth_fee = 'N' AND
462 l_sty_used_in_pth_service = 'N' THEN
463 l_new_sty_purpose := NULL;
464 ELSE
465 l_new_sty_purpose := 'GENERAL';
466 END IF;
467
468 x_sty_purpose := l_new_sty_purpose;
469
470 EXCEPTION
471 WHEN OTHERS THEN
472 Fnd_File.put_line(Fnd_File.LOG, SQLERRM);
473
474 END Migrate_Sty_Fee_Line;
475
476 ---------------------------------------------------------------------------
477 -- PROCEDURE Migrate_Sty_Subsidy
478 ---------------------------------------------------------------------------
479 -- Start of comments
480 --
481 -- Procedure Name : Migrate_Sty_Subsidy
482 -- Description : Procedure to migrate Subsidy stream types
483 -- Business Rules :
484 -- Parameters :
485 -- Version : 1.0
486 -- End of comments
487 ---------------------------------------------------------------------------
488
489 PROCEDURE Migrate_Sty_Subsidy (p_sty_id NUMBER,
490 x_sty_purpose OUT NOCOPY VARCHAR2)
491 IS
492
493 l_dummy VARCHAR2(100);
494
495 -- Cursor to find if a stream type is used in Subsidy.
496
497 CURSOR subsidy_csr (l_sty_id NUMBER) IS
498 SELECT stream_type_class
499 FROM okl_strm_type_v
500 WHERE stream_type_class = 'SUBSIDY'
501 AND id = l_sty_id;
502
503 BEGIN
504
505 OPEN subsidy_csr (p_sty_id);
506 FETCH subsidy_csr INTO l_dummy;
507 IF subsidy_csr%FOUND THEN
508 x_sty_purpose := 'SUBSIDY';
509 END IF;
510 CLOSE subsidy_csr;
511
512 EXCEPTION
513 WHEN OTHERS THEN
514 Fnd_File.put_line(Fnd_File.LOG, SQLERRM);
515
516 END Migrate_Sty_Subsidy;
517
518
519 ---------------------------------------------------------------------------
520 -- PROCEDURE Migrate_Sty_Subsidy_Income
521 ---------------------------------------------------------------------------
522 -- Start of comments
523 --
524 -- Procedure Name : Migrate_Sty_Subsidy_Income
525 -- Description : Procedure to migrate Subsidy Income stream types
526 -- Business Rules :
527 -- Parameters :
528 -- Version : 1.0
529 -- End of comments
530 ---------------------------------------------------------------------------
531
532 PROCEDURE Migrate_Sty_Subsidy_Income(p_sty_id NUMBER,
533 x_sty_purpose OUT NOCOPY VARCHAR2)
534 IS
535
536 l_dummy VARCHAR2(100);
537
538 -- Cursor to find if a stream type is used in Subsidy.
539
540 CURSOR subsidy_inc_csr (l_sty_id NUMBER) IS
541 SELECT '1'
542 FROM okl_sgn_translations sgn
543 WHERE sgn.jtot_object1_code = 'OKL_STRMTYP' AND
544 sgn.value = TO_CHAR(l_sty_id);
545
546 BEGIN
547
548 OPEN subsidy_inc_csr (p_sty_id);
549 FETCH subsidy_inc_csr INTO l_dummy;
550 IF subsidy_inc_csr%FOUND THEN
551 x_sty_purpose := 'SUBSIDY_INCOME';
552 END IF;
553 CLOSE subsidy_inc_csr;
554
555 EXCEPTION
556 WHEN OTHERS THEN
557 Fnd_File.put_line(Fnd_File.LOG, SQLERRM);
558
559 END Migrate_Sty_Subsidy_Income;
560
561
562 ---------------------------------------------------------------------------
563 -- PROCEDURE Migrate_Sty_Payments
564 ---------------------------------------------------------------------------
565 -- Start of comments
566 --
567 -- Procedure Name : Migrate_Sty_Payments
568 -- Description : Procedure to migrate Payment stream types
569 -- Business Rules :
570 -- Parameters :
571 -- Version : 1.0
572 -- End of comments
573 ---------------------------------------------------------------------------
574
575
576 PROCEDURE Migrate_Sty_Payments (p_sty_id NUMBER,
577 x_sty_purpose OUT NOCOPY VARCHAR2)
578 IS
579
580 l_sty_used_in_fee_pmt VARCHAR2(1);
581 l_sty_used_in_srv_pmt VARCHAR2(1);
582 l_dummy_id NUMBER;
583 l_new_sty_purpose VARCHAR2(100);
584
585 -- Cursor to find if a stream type is used in Fee Lines.
586
587 CURSOR srv_pmt_csr (l_sty_id NUMBER) IS
588 --Service Payments
589 SELECT rul.object1_id1 --strm_type_id,
590 FROM okc_rules_b rul,
591 okc_rule_groups_b rgp,
592 okc_k_lines_b cleb,
593 okc_line_styles_b lseb,
594 okc_k_headers_b chrb,
595 okl_k_headers khr,
596 okl_product_parameters_v pdt
597 WHERE rgp.cle_id = cleb.id
598 AND rul.rgp_id = rgp.id
599 AND rul.rule_information_category = 'LASLH'
600 AND rgp.dnz_chr_id = chrb.id
601 AND rul.dnz_chr_id = chrb.id
602 AND rgp.rgd_code = 'LALEVL'
603 AND cleb.lse_id = lseb.id
604 AND lseb.lty_code = 'SOLD_SERVICE'
605 AND cleb.chr_id = chrb.id
606 AND chrb.scs_code IN ('LEASE','QUOTE')
607 AND chrb.id = khr.id
608 AND khr.pdt_id = pdt.id
609 AND rul.object1_id1 = l_sty_id
610 AND pdt.deal_type IN ('LEASEOP', 'LEASEDF', 'LEASEST', 'LOAN', 'LOAN-REVOLVING', 'SALE')
611 AND pdt.tax_owner IN ('LESSOR', 'LESSEE')
612 UNION ALL
613 --Link Service Payments
614 SELECT rul.object1_id1 --strm_type_id,
615 FROM okc_rules_b rul,
616 okc_rule_groups_b rgp,
617 okc_k_lines_b cleb,
618 okc_line_styles_b lseb,
619 okc_k_headers_b chrb,
620 okl_k_headers khr,
621 okl_product_parameters_v pdt
622 WHERE rgp.cle_id = cleb.id
623 AND rul.rgp_id = rgp.id
624 AND rul.rule_information_category = 'LASLH'
625 AND rgp.dnz_chr_id = chrb.id
626 AND rul.dnz_chr_id = chrb.id
627 AND rgp.rgd_code = 'LALEVL'
628 AND cleb.lse_id = lseb.id
629 AND lseb.lty_code = 'LINK_SERV_ASSET'
630 AND cleb.dnz_chr_id = chrb.id
631 AND chrb.scs_code IN ('LEASE','QUOTE')
632 AND chrb.id = khr.id
633 AND khr.pdt_id = pdt.id
634 AND rul.object1_id1 = l_sty_id
635 AND pdt.deal_type IN ('LEASEOP', 'LEASEDF', 'LEASEST', 'LOAN', 'LOAN-REVOLVING', 'SALE')
636 AND pdt.tax_owner IN ('LESSOR', 'LESSEE');
637
638
639
640 CURSOR fee_pmt_csr (l_sty_id NUMBER) IS
641 --Fee Payments
642 SELECT rul.object1_id1 --strm_type_id,
643 FROM okc_rules_b rul,
644 okc_rule_groups_b rgp,
645 okc_k_lines_b cleb,
646 okc_line_styles_b lseb,
647 okc_k_headers_b chrb,
648 okl_k_headers khr,
649 okl_product_parameters_v pdt
650 WHERE rgp.cle_id = cleb.id
651 AND rul.rgp_id = rgp.id
652 AND rul.rule_information_category = 'LASLH'
653 AND rgp.dnz_chr_id = chrb.id
654 AND rul.dnz_chr_id = chrb.id
655 AND rgp.rgd_code = 'LALEVL'
656 AND cleb.lse_id = lseb.id
657 AND lseb.lty_code = 'FEE'
658 AND cleb.chr_id = chrb.id
659 AND chrb.scs_code IN ('LEASE','QUOTE')
660 AND chrb.id = khr.id
661 AND khr.pdt_id = pdt.id
662 AND rul.object1_id1 = l_sty_id
663 AND pdt.deal_type IN ('LEASEOP', 'LEASEDF', 'LEASEST', 'LOAN', 'LOAN-REVOLVING', 'SALE')
664 AND pdt.tax_owner IN ('LESSOR', 'LESSEE')
665 UNION ALL
666 --Link Fee Payments
667 SELECT rul.object1_id1 --strm_type_id,
668 FROM okc_rules_b rul,
669 okc_rule_groups_b rgp,
670 okc_k_lines_b cleb,
671 okc_line_styles_b lseb,
672 okc_k_headers_b chrb,
673 okl_k_headers khr,
674 okl_product_parameters_v pdt
675 WHERE rgp.cle_id = cleb.id
676 AND rul.rgp_id = rgp.id
677 AND rul.rule_information_category = 'LASLH'
678 AND rgp.dnz_chr_id = chrb.id
679 AND rul.dnz_chr_id = chrb.id
680 AND rgp.rgd_code = 'LALEVL'
681 AND cleb.lse_id = lseb.id
682 AND lseb.lty_code = 'LINK_FEE_ASSET'
683 AND cleb.dnz_chr_id = chrb.id
684 AND chrb.scs_code IN ('LEASE','QUOTE')
685 AND chrb.id = khr.id
686 AND khr.pdt_id = pdt.id
687 AND rul.object1_id1 = l_sty_id
688 AND pdt.deal_type IN ('LEASEOP', 'LEASEDF', 'LEASEST', 'LOAN', 'LOAN-REVOLVING', 'SALE')
689 AND pdt.tax_owner IN ('LESSOR', 'LESSEE')
690 UNION ALL
691 --Contract level payments
692 SELECT rul.object1_id1 --strm_type_id,
693 FROM okc_rules_b rul,
694 okc_rule_groups_b rgp,
695 okc_k_headers_b chrb,
696 okl_k_headers khr,
697 okl_product_parameters_v pdt
698 WHERE rgp.chr_id = chrb.id
699 AND rul.rgp_id = rgp.id
700 AND rul.rule_information_category = 'LASLH'
701 AND rgp.dnz_chr_id = chrb.id
702 AND rul.dnz_chr_id = chrb.id
703 AND rgp.rgd_code = 'LALEVL'
704 AND chrb.scs_code IN ('LEASE','QUOTE')
705 AND chrb.id = khr.id
706 AND khr.pdt_id = pdt.id
707 AND rul.object1_id1 = l_sty_id
708 AND pdt.deal_type IN ('LEASEOP', 'LEASEDF', 'LEASEST', 'LOAN', 'LOAN-REVOLVING', 'SALE')
709 AND pdt.tax_owner IN ('LESSOR', 'LESSEE')
710 UNION ALL
711 --Asset level payments not RENT
712 SELECT rul.object1_id1 --strm_type_id,
713 FROM okc_rules_b rul,
714 okc_rule_groups_b rgp,
715 okc_k_lines_b cleb,
716 okc_line_styles_b lseb,
717 okc_k_headers_b chrb,
718 okl_k_headers khr,
719 okl_product_parameters_v pdt
720 WHERE rgp.cle_id = cleb.id
721 AND rul.rgp_id = rgp.id
722 AND rul.rule_information_category = 'LASLH'
723 AND rgp.dnz_chr_id = chrb.id
724 AND rul.dnz_chr_id = chrb.id
725 AND rgp.rgd_code = 'LALEVL'
726 AND cleb.lse_id = lseb.id
727 AND lseb.lty_code = 'FREE_FORM1'
728 AND cleb.chr_id = chrb.id
729 AND chrb.scs_code IN ('LEASE','QUOTE')
730 AND rul.object1_id1 NOT IN (SELECT id FROM
731 okl_strm_type_b WHERE code = 'RENT')
732 AND chrb.id = khr.id
733 AND khr.pdt_id = pdt.id
734 AND rul.object1_id1 = l_sty_id
735 AND pdt.deal_type IN ('LEASEOP', 'LEASEDF', 'LEASEST', 'LOAN', 'LOAN-REVOLVING', 'SALE')
736 AND pdt.tax_owner IN ('LESSOR', 'LESSEE');
737
738
739 BEGIN
740
741 OPEN srv_pmt_csr (p_sty_id);
742 FETCH srv_pmt_csr INTO l_dummy_id;
743 IF srv_pmt_csr%NOTFOUND THEN
744 l_sty_used_in_srv_pmt := 'N';
745 ELSE
746 l_sty_used_in_srv_pmt := 'Y';
747 END IF;
748 CLOSE srv_pmt_csr;
749
750 OPEN fee_pmt_csr (p_sty_id);
751 FETCH fee_pmt_csr INTO l_dummy_id;
752 IF fee_pmt_csr%NOTFOUND THEN
753 l_sty_used_in_fee_pmt := 'N';
754 ELSE
755 l_sty_used_in_fee_pmt := 'Y';
756 END IF;
757 CLOSE fee_pmt_csr;
758
759
760 IF l_sty_used_in_srv_pmt = 'Y' AND
761 l_sty_used_in_fee_pmt = 'N' THEN
762 x_sty_purpose := 'SERVICE_PAYMENT';
763 ELSIF l_sty_used_in_srv_pmt = 'N' AND
764 l_sty_used_in_fee_pmt = 'Y' THEN
765 x_sty_purpose := 'FEE_PAYMENT';
766 ELSIF l_sty_used_in_srv_pmt = 'Y' AND
767 l_sty_used_in_fee_pmt = 'Y' THEN
768 x_sty_purpose := 'GENERAL';
769 END IF;
770
771 EXCEPTION
772 WHEN OTHERS THEN
773 Fnd_File.put_line(Fnd_File.LOG, SQLERRM);
774
775 END Migrate_Sty_Payments;
776
777 ---------------------------------------------------------------------------
778 -- PROCEDURE Migrate_Stream_Types
779 ---------------------------------------------------------------------------
780 -- Start of comments
781 --
782 -- Procedure Name : Migrate_Stream_Types
783 -- Description : Procedure to migrate stream types
784 -- Business Rules :
785 -- Parameters :
786 -- Version : 1.0
787 -- End of comments
788 ---------------------------------------------------------------------------
789
790 PROCEDURE Migrate_Stream_Types(x_return_status OUT NOCOPY VARCHAR2)
791 IS
792
793 -- Cursor to get all the stream types avilable in the system
794
795 CURSOR strm_type_csr IS
796 SELECT
797 id,
798 name,
799 code,
800 stream_type_subclass,
801 stream_type_purpose,
802 start_date,
803 stream_type_class
804 FROM
805 okl_strm_type_v
806 WHERE
807 short_description IS NULL;
808
809 l_new_sty_purpose VARCHAR2(100);
810 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
811
812 BEGIN
813
814 Fnd_File.put_line(Fnd_File.LOG,'Migrating Stream Types ');
815 Fnd_File.put_line(Fnd_File.LOG,'-----------------------');
816
817 FOR strm_type_rec IN strm_type_csr LOOP
818 l_new_sty_purpose := NULL;
819
820 IF strm_type_rec.name = 'SECURITY DEPOSIT' THEN
821 l_new_sty_purpose := 'SECURITY_DEPOSIT';
822
823 ELSIF strm_type_rec.name = 'USAGE CHARGE' THEN
824 l_new_sty_purpose := 'USAGE_PAYMENT';
825
826 ELSE
827 IF strm_type_rec.stream_type_class = 'SUBSIDY' THEN
828 Migrate_Sty_Subsidy (p_sty_id => strm_type_rec.id,
829 x_sty_purpose => l_new_sty_purpose) ;
830
831 ELSIF strm_type_rec.stream_type_class = 'EXPENSE' THEN
832 Migrate_Sty_Fee_Line (p_sty_id => strm_type_rec.id,
833 x_sty_purpose => l_new_sty_purpose);
834
835 ELSIF strm_type_rec.stream_type_class = 'FEE' THEN
836 Migrate_Sty_Payments (p_sty_id => strm_type_rec.id,
837 x_sty_purpose => l_new_sty_purpose);
838
839 ELSIF strm_type_rec.stream_type_class = 'ACCRUAL' THEN
840 Migrate_Sty_Subsidy_Income (p_sty_id => strm_type_rec.id,
841 x_sty_purpose => l_new_sty_purpose) ;
842 END IF;
843 END IF;
844
845 -- Update stream type with proper purpose
846 IF l_new_sty_purpose IS NOT NULL THEN
847 Fnd_File.put_line(Fnd_File.LOG,'Updating Stream Type Purpose of Stream Type - ' || strm_type_rec.name || ' to - ' || l_new_sty_purpose);
848 BEGIN
849 UPDATE OKL_STRM_TYPE_B
850 SET STREAM_TYPE_PURPOSE =l_new_sty_purpose,
851 LAST_UPDATE_DATE = SYSDATE,
852 LAST_UPDATE_LOGIN = Fnd_Global.login_id
853 WHERE ID = strm_type_rec.id;
854
855 EXCEPTION
856 WHEN OTHERS THEN
857 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
858 Fnd_File.PUT_LINE(Fnd_File.LOG, SQLERRM);
859 END;
860 END IF;
861
862 -- Update stream type if it has been upgraded
863
864 Fnd_File.put_line(Fnd_File.LOG,'Updating Short Description of Stream Type - ' || strm_type_rec.name);
865
866 BEGIN
867 UPDATE OKL_STRM_TYPE_TL
868 SET SHORT_DESCRIPTION ='UPGRADED SUCCESSFULLY',
869 LAST_UPDATE_DATE = SYSDATE,
870 LAST_UPDATE_LOGIN = Fnd_Global.login_id
871 WHERE ID = strm_type_rec.id
872 AND LANGUAGE = USERENV('LANG');
873
874 EXCEPTION
875 WHEN OTHERS THEN
876 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
877 Fnd_File.PUT_LINE(Fnd_File.LOG, SQLERRM);
878 END;
879
880 END LOOP; -- End for strm_type_csr
881
882 x_return_status := l_return_status;
883
884
885 EXCEPTION
886 WHEN OTHERS THEN
887 Fnd_File.put_line(Fnd_File.LOG, SQLERRM);
888 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
889
890 END Migrate_Stream_Types;
891
892 ---------------------------------------------------------------------------
893 -- PROCEDURE Check_If_Used
894 ---------------------------------------------------------------------------
895 -- Start of comments
896 --
897 -- Procedure Name : Check_If_Used
898 -- Description : Procedure to check if a stream type is used on a contract
899 -- of a specific deal type
900 -- Business Rules :
901 -- Parameters :
902 -- Version : 1.0
903 -- End of comments
904 ---------------------------------------------------------------------------
905
906 FUNCTION Check_If_Used (p_sty_id IN NUMBER,
907 p_book_class IN VARCHAR2,
908 p_tax_owner IN VARCHAR2)
909 RETURN VARCHAR2
910
911 IS
912
913 CURSOR st_gen_tmpt_csr IS
914 SELECT
915 'Y'
916 FROM
917 okl_st_gen_tmpt_lns gtl,
918 okl_st_gen_templates gtt,
919 okl_st_gen_tmpt_sets gts
920 WHERE
921 gtl.primary_sty_id = p_sty_id AND
922 gtl.gtt_id = gtt.id AND
923 gtt.gts_id = gts.id AND
924 gts.name = p_book_class || '-' || p_tax_owner;
925
926
927 CURSOR passthru_fee_csr IS
928 SELECT
929 'Y'
930 FROM
931 okc_k_items cim,
932 okl_k_lines kle,
933 okc_k_lines_b cleb,
934 okc_line_styles_b lseb,
935 okc_k_headers_b chrb,
936 okl_k_headers khr,
937 okl_product_parameters_v pdt
938 WHERE
939 cim.cle_id = cleb.id AND
940 cleb.lse_id = lseb.id AND
941 lseb.lty_code = 'FEE' AND
942 cleb.chr_id = chrb.id AND
943 kle.id = cleb.id AND
944 chrb.scs_code IN ('LEASE','QUOTE') AND
945 kle.fee_type = 'PASSTHROUGH' AND
946 chrb.id = khr.id AND
947 khr.pdt_id = pdt.id AND
948 cim.object1_id1 = p_sty_id AND
949 pdt.DEAL_TYPE = p_book_class AND
950 pdt.TAX_OWNER = p_tax_owner;
951
952
953 CURSOR fee_non_pth_csr IS
954 SELECT
955 'Y'
956 FROM
957 okc_k_items cim,
958 okl_k_lines kle,
959 okc_k_lines_b cleb,
960 okc_line_styles_b lseb,
961 okc_k_headers_b chrb,
962 okl_k_headers khr,
963 okl_product_parameters_v pdt
964 WHERE
965 cim.cle_id = cleb.id AND
966 cleb.lse_id = lseb.id AND
967 lseb.lty_code = 'FEE' AND
968 cleb.chr_id = chrb.id AND
969 kle.id = cleb.id AND
970 chrb.scs_code IN ('LEASE','QUOTE') AND
971 kle.fee_type <> 'PASSTHROUGH' AND
972 chrb.id = khr.id AND
973 khr.pdt_id = pdt.id AND
974 cim.object1_id1 = p_sty_id AND
975 pdt.DEAL_TYPE = p_book_class AND
976 pdt.TAX_OWNER = p_tax_owner;
977
978
979
980 -- Cursor to find if a stream type is used in Passthrough service
981
982 CURSOR pth_service_csr
983 IS
984 SELECT 'Y'
985 FROM okc_rules_b rul,
986 okc_rule_groups_b rgp,
987 okc_k_lines_b cleb,
988 okc_line_styles_b lseb,
989 okc_k_headers_b chrb,
990 okl_k_headers khr,
991 okl_product_parameters_v pdt
992 WHERE rgp.cle_id = cleb.id
993 AND rul.rgp_id = rgp.id
994 AND rul.rule_information_category = 'LASTRM'
995 AND rgp.dnz_chr_id = chrb.id
996 AND rgp.rgd_code = 'LAPSTH'
997 AND rul.dnz_chr_id = chrb.id
998 AND cleb.lse_id = lseb.id
999 AND lseb.lty_code = 'SOLD_SERVICE'
1000 AND cleb.chr_id = chrb.id
1001 AND chrb.scs_code IN ('LEASE','QUOTE')
1002 AND chrb.id = khr.id
1003 AND khr.pdt_id = pdt.id
1004 AND rul.object1_id1 = p_sty_id
1005 AND pdt.DEAL_TYPE = p_book_class
1006 AND pdt.TAX_OWNER = p_tax_owner;
1007
1008
1009 CURSOR srv_pmt_csr IS
1010 --Service Payments
1011 SELECT 'Y'
1012 FROM okc_rules_b rul,
1013 okc_rule_groups_b rgp,
1014 okc_k_lines_b cleb,
1015 okc_line_styles_b lseb,
1016 okc_k_headers_b chrb,
1017 okl_k_headers khr,
1018 okl_product_parameters_v pdt
1019 WHERE rgp.cle_id = cleb.id
1020 AND rul.rgp_id = rgp.id
1021 AND rul.rule_information_category = 'LASLH'
1022 AND rgp.dnz_chr_id = chrb.id
1023 AND rul.dnz_chr_id = chrb.id
1024 AND rgp.rgd_code = 'LALEVL'
1025 AND cleb.lse_id = lseb.id
1026 AND lseb.lty_code = 'SOLD_SERVICE'
1027 AND cleb.chr_id = chrb.id
1028 AND chrb.scs_code IN ('LEASE','QUOTE')
1029 AND rul.object1_id1 = p_sty_id
1030 AND chrb.id = khr.id
1031 AND khr.pdt_id = pdt.id
1032 AND pdt.DEAL_TYPE = p_book_class
1033 AND pdt.TAX_OWNER = p_tax_owner
1034 UNION ALL
1035 --Link Service Payments
1036 SELECT 'Y'
1037 FROM okc_rules_b rul,
1038 okc_rule_groups_b rgp,
1039 okc_k_lines_b cleb,
1040 okc_line_styles_b lseb,
1041 okc_k_headers_b chrb,
1042 okl_k_headers khr,
1043 okl_product_parameters_v pdt
1044 WHERE rgp.cle_id = cleb.id
1045 AND rul.rgp_id = rgp.id
1046 AND rul.rule_information_category = 'LASLH'
1047 AND rgp.dnz_chr_id = chrb.id
1048 AND rul.dnz_chr_id = chrb.id
1049 AND rgp.rgd_code = 'LALEVL'
1050 AND cleb.lse_id = lseb.id
1051 AND lseb.lty_code = 'LINK_SERV_ASSET'
1052 AND cleb.dnz_chr_id = chrb.id
1053 AND chrb.scs_code IN ('LEASE','QUOTE')
1054 AND rul.object1_id1 = p_sty_id
1055 AND chrb.id = khr.id
1056 AND khr.pdt_id = pdt.id
1057 AND pdt.DEAL_TYPE = p_book_class
1058 AND pdt.TAX_OWNER = p_tax_owner;
1059
1060
1061
1062 CURSOR fee_pmt_csr IS
1063 --Fee Payments
1064 SELECT 'Y'
1065 FROM okc_rules_b rul,
1066 okc_rule_groups_b rgp,
1067 okc_k_lines_b cleb,
1068 okc_line_styles_b lseb,
1069 okc_k_headers_b chrb,
1070 okl_k_headers khr,
1071 okl_product_parameters_v pdt
1072 WHERE rgp.cle_id = cleb.id
1073 AND rul.rgp_id = rgp.id
1074 AND rul.rule_information_category = 'LASLH'
1075 AND rgp.dnz_chr_id = chrb.id
1076 AND rul.dnz_chr_id = chrb.id
1077 AND rgp.rgd_code = 'LALEVL'
1078 AND cleb.lse_id = lseb.id
1079 AND lseb.lty_code = 'FEE'
1080 AND cleb.chr_id = chrb.id
1081 AND chrb.scs_code IN ('LEASE','QUOTE')
1082 AND rul.object1_id1 = p_sty_id
1083 AND chrb.id = khr.id
1084 AND khr.pdt_id = pdt.id
1085 AND pdt.DEAL_TYPE = p_book_class
1086 AND pdt.TAX_OWNER = p_tax_owner
1087 UNION ALL
1088 --Link Fee Payments
1089 SELECT 'Y'
1090 FROM okc_rules_b rul,
1091 okc_rule_groups_b rgp,
1092 okc_k_lines_b cleb,
1093 okc_line_styles_b lseb,
1094 okc_k_headers_b chrb,
1095 okl_k_headers khr,
1096 okl_product_parameters_v pdt
1097 WHERE rgp.cle_id = cleb.id
1098 AND rul.rgp_id = rgp.id
1099 AND rul.rule_information_category = 'LASLH'
1100 AND rgp.dnz_chr_id = chrb.id
1101 AND rul.dnz_chr_id = chrb.id
1102 AND rgp.rgd_code = 'LALEVL'
1103 AND cleb.lse_id = lseb.id
1104 AND lseb.lty_code = 'LINK_FEE_ASSET'
1105 AND cleb.dnz_chr_id = chrb.id
1106 AND chrb.scs_code IN ('LEASE','QUOTE')
1107 AND rul.object1_id1 = p_sty_id
1108 AND chrb.id = khr.id
1109 AND khr.pdt_id = pdt.id
1110 AND pdt.DEAL_TYPE = p_book_class
1111 AND pdt.TAX_OWNER = p_tax_owner
1112 UNION ALL
1113 --Contract level payments
1114 SELECT 'Y'
1115 FROM okc_rules_b rul,
1116 okc_rule_groups_b rgp,
1117 okc_k_headers_b chrb,
1118 okl_k_headers khr,
1119 okl_product_parameters_v pdt
1120 WHERE rgp.chr_id = chrb.id
1121 AND rul.rgp_id = rgp.id
1122 AND rul.rule_information_category = 'LASLH'
1123 AND rgp.dnz_chr_id = chrb.id
1124 AND rul.dnz_chr_id = chrb.id
1125 AND rgp.rgd_code = 'LALEVL'
1126 AND chrb.scs_code IN ('LEASE','QUOTE')
1127 AND rul.object1_id1 = p_sty_id
1128 AND chrb.id = khr.id
1129 AND khr.pdt_id = pdt.id
1130 AND pdt.DEAL_TYPE = p_book_class
1131 AND pdt.TAX_OWNER = p_tax_owner
1132 UNION ALL
1133 --Asset level payments not RENT
1134 SELECT 'Y'
1135 FROM okc_rules_b rul,
1136 okc_rule_groups_b rgp,
1137 okc_k_lines_b cleb,
1138 okc_line_styles_b lseb,
1139 okc_k_headers_b chrb,
1140 okl_k_headers khr,
1141 okl_product_parameters_v pdt
1142 WHERE rgp.cle_id = cleb.id
1143 AND rul.rgp_id = rgp.id
1144 AND rul.rule_information_category = 'LASLH'
1145 AND rgp.dnz_chr_id = chrb.id
1146 AND rul.dnz_chr_id = chrb.id
1147 AND rgp.rgd_code = 'LALEVL'
1148 AND cleb.lse_id = lseb.id
1149 AND lseb.lty_code = 'FREE_FORM1'
1150 AND cleb.chr_id = chrb.id
1151 AND chrb.scs_code IN ('LEASE','QUOTE')
1152 AND rul.object1_id1 NOT IN (SELECT id FROM
1153 okl_strm_type_b WHERE code = 'RENT')
1154 AND rul.object1_id1 = p_sty_id
1155 AND chrb.id = khr.id
1156 AND khr.pdt_id = pdt.id
1157 AND pdt.DEAL_TYPE = p_book_class
1158 AND pdt.TAX_OWNER = p_tax_owner;
1159
1160 CURSOR subsidy_csr IS
1161 SELECT 'Y'
1162 FROM okl_subsidies_v sub
1163 WHERE sub.stream_type_id = p_sty_id;
1164
1165 l_sty_code VARCHAR2(100);
1166 l_sty_added VARCHAR2(1) := 'N';
1167
1168 BEGIN
1169
1170 OPEN st_gen_tmpt_csr;
1171 FETCH st_gen_tmpt_csr INTO l_sty_added;
1172 CLOSE st_gen_tmpt_csr;
1173
1174 IF l_sty_added = 'Y' THEN
1175 RETURN G_FALSE;
1176 ELSE
1177
1178 OPEN passthru_fee_csr;
1179 FETCH passthru_fee_csr INTO l_sty_added;
1180 CLOSE passthru_fee_csr;
1181
1182 IF l_sty_added = 'Y' THEN
1183 RETURN G_TRUE;
1184 END IF;
1185
1186 OPEN fee_non_pth_csr;
1187 FETCH fee_non_pth_csr INTO l_sty_added;
1188 CLOSE fee_non_pth_csr;
1189
1190 IF l_sty_added = 'Y' THEN
1191 RETURN G_TRUE;
1192 END IF;
1193
1194 OPEN pth_service_csr;
1195 FETCH pth_service_csr INTO l_sty_added;
1196 CLOSE pth_service_csr;
1197
1198 IF l_sty_added = 'Y' THEN
1199 RETURN G_TRUE;
1200 END IF;
1201
1202 OPEN srv_pmt_csr;
1203 FETCH srv_pmt_csr INTO l_sty_added;
1204 CLOSE srv_pmt_csr;
1205
1206 IF l_sty_added = 'Y' THEN
1207 RETURN G_TRUE;
1208 END IF;
1209
1210 OPEN fee_pmt_csr;
1211 FETCH fee_pmt_csr INTO l_sty_added;
1212 CLOSE fee_pmt_csr;
1213
1214 IF l_sty_added = 'Y' THEN
1215 RETURN G_TRUE;
1216 END IF;
1217
1218 OPEN subsidy_csr;
1219 FETCH subsidy_csr INTO l_sty_added;
1220 CLOSE subsidy_csr;
1221
1222 IF l_sty_added = 'Y' THEN
1223 RETURN G_TRUE;
1224 END IF;
1225
1226 RETURN G_FALSE;
1227
1228 END IF;
1229
1230 EXCEPTION
1231 WHEN OTHERS THEN
1232 Fnd_File.put_line(Fnd_File.LOG, SQLERRM);
1233 RETURN G_FALSE;
1234
1235
1236 END Check_If_Used;
1237
1238 ---------------------------------------------------------------------------
1239 -- PROCEDURE Get_Pricing_Name
1240 ---------------------------------------------------------------------------
1241 -- Start of comments
1242 --
1243 -- Procedure Name : Get_Pricing_Name
1244 -- Description : Function to return the pricing name for a stream type
1245 -- Business Rules :
1246 -- Parameters :
1247 -- Version : 1.0
1248 -- End of comments
1249 ---------------------------------------------------------------------------
1250
1251
1252 FUNCTION Get_Pricing_Name (p_sty_code IN VARCHAR2)
1253 RETURN VARCHAR2
1254
1255 IS
1256 l_pricing_name okl_st_gen_tmpt_lns.pricing_name%TYPE;
1257 BEGIN
1258
1259 IF p_sty_code = 'PRE-TAX INCOME' THEN
1260 l_pricing_name := 'Single Rent Accrual';
1261 ELSIF p_sty_code = 'INTEREST INCOME' THEN
1262 l_pricing_name := 'Single Lending Loan Accrual';
1263 ELSIF p_sty_code = 'RENT' THEN
1264 l_pricing_name := 'Rent';
1265 ELSIF p_sty_code = 'PRINCIPAL BALANCE' THEN
1266 l_pricing_name := 'Lending Loans Balance';
1267 ELSIF p_sty_code = 'INTEREST PAYMENT' THEN
1268 l_pricing_name := 'Lending Loans Interest';
1269 ELSIF p_sty_code = 'PRINCIPAL PAYMENT' THEN
1270 l_pricing_name := 'Lending Loans Principal';
1271 ELSIF p_sty_code = 'RENTAL ACCRUAL' THEN
1272 l_pricing_name := 'Single Rent Accrual';
1273 ELSIF p_sty_code = 'TERMINATION VALUE' THEN
1274 l_pricing_name := 'Termination Value';
1275 ELSIF p_sty_code = 'STIP LOSS VALUE' THEN
1276 l_pricing_name := 'StipLoss Value';
1277 ELSIF p_sty_code = 'BOOK DEPRECIATION' THEN
1278 l_pricing_name := 'Book Depreciation';
1279 ELSIF p_sty_code = 'FEDERAL DEPRECIATION' THEN
1280 l_pricing_name := 'Federal Depreciation';
1281 ELSIF p_sty_code = 'STATE DEPRECIATION' THEN
1282 l_pricing_name := 'State Depreciation';
1283 ELSIF p_sty_code = 'LOAN PAYMENT' THEN
1284 l_pricing_name := 'Lending Loans Debt Service';
1285 -- Santonyr Bug 4107753
1286 ELSIF p_sty_code = 'SECURITY DEPOSIT' THEN
1287 l_pricing_name := 'Security Deposits';
1288 ELSIF p_sty_code = 'UNSCHEDULED PRINCIPAL PAYMENT' THEN
1289 l_pricing_name := 'Principal Paydowns';
1290 ELSE
1291 l_pricing_name := NULL;
1292 END IF;
1293
1294 RETURN l_pricing_name;
1295
1296 EXCEPTION
1297 WHEN OTHERS THEN
1298 Fnd_File.put_line(Fnd_File.LOG, SQLERRM);
1299 RETURN NULL;
1300
1301 END Get_Pricing_Name;
1302
1303 ---------------------------------------------------------------------------
1304 -- PROCEDURE Get_dependent_sty
1305 ---------------------------------------------------------------------------
1306 -- Start of comments
1307 --
1308 -- Procedure Name : Get_dependent_sty
1309 -- Description : Procedure to get the dependent stream types for a primary
1310 -- Business Rules :
1311 -- Parameters :
1312 -- Version : 1.0
1313 -- End of comments
1314 ---------------------------------------------------------------------------
1315
1316
1317 PROCEDURE Get_dependent_sty (p_sty_id IN VARCHAR2,
1318 p_book_class IN VARCHAR2,
1319 x_dep_sty_tbl OUT NOCOPY dep_sty_tbl)
1320 IS
1321
1322 CURSOR primary_sty_csr IS
1323 SELECT id, code, stream_type_purpose
1324 FROM okl_strm_type_b
1325 WHERE id = p_sty_id;
1326
1327 CURSOR act_prpty_tax_csr IS
1328 SELECT ID, CODE , stream_type_purpose
1329 FROM OKL_STRM_TYPE_V
1330 WHERE CODE IN
1331 ('ADJUSTED PROPERTY TAX');
1332
1333 CURSOR est_prpty_tax_csr IS
1334 SELECT ID, CODE , stream_type_purpose
1335 FROM OKL_STRM_TYPE_V
1336 WHERE CODE IN
1337 ('RENEWAL PROPERTY TAX');
1338
1339 CURSOR expense_csr IS
1340 SELECT ID, CODE , stream_type_purpose
1341 FROM OKL_STRM_TYPE_V
1342 WHERE CODE IN
1343 ('ACCRUED FEE EXPENSE',
1344 'AMORTIZED EXPENSE');
1345
1346 CURSOR fee_payment_csr IS
1347 SELECT ID, CODE , stream_type_purpose
1348 FROM OKL_STRM_TYPE_V
1349 WHERE CODE IN
1350 (
1351 'FEE INCOME',
1352 'AMORTIZED FEE INCOME',
1353 'FEE RENEWAL',
1354 'PASS THROUGH EXPENSE ACCRUAL',
1355 'PASS THROUGH EVERGREEN FEE',
1356 'LOAN PAYMENT',
1357 'PRINCIPAL PAYMENT',
1358 'INTEREST PAYMENT',
1359 'PRINCIPAL BALANCE',
1360 'INTEREST INCOME',
1361 'PASS THROUGH REVENUE ACCRUAL');
1362
1363 CURSOR passthru_srv_csr IS
1364 SELECT ID, CODE , stream_type_purpose
1365 FROM OKL_STRM_TYPE_V
1366 WHERE CODE IN
1367 ('PASS THROUGH SERVICE EXPENSE ACCRUAL');
1368
1369 CURSOR srv_payment_csr IS
1370 SELECT ID, CODE , stream_type_purpose
1371 FROM OKL_STRM_TYPE_V
1372 WHERE CODE IN
1373 ('PASS THROUGH EVERGREEN SERVICE',
1374 'PASS THROUGH SERVICE REVENUE ACCRUAL',
1375 'SERVICE INCOME',
1376 'SERVICE AND MAINTENANCE EVERGREEN');
1377
1378 CURSOR subsidy_csr IS
1379 SELECT STY.ID, STY.CODE , STY.stream_type_purpose
1380 FROM OKL_STRM_TYPE_V STY, okl_sgn_translations sgn
1381 WHERE sgn.object1_id1 = TO_CHAR(p_sty_id)
1382 AND sgn.value = TO_CHAR(sty.id);
1383
1384 l_dep_sty_tbl dep_sty_tbl;
1385 l_sty_purpose OKL_STRM_TYPE_B.stream_type_purpose%TYPE;
1386
1387 j NUMBER := 0;
1388
1389 BEGIN
1390
1391
1392 FOR primary_sty_rec IN primary_sty_csr LOOP
1393 l_sty_purpose := primary_sty_rec.stream_type_purpose;
1394 END LOOP;
1395
1396 IF l_sty_purpose = 'ACTUAL_PROPERTY_TAX' THEN
1397
1398 FOR act_prpty_tax_rec IN act_prpty_tax_csr LOOP
1399 j := j + 1;
1400 l_dep_sty_tbl(j).sty_id := act_prpty_tax_rec.id;
1401 l_dep_sty_tbl(j).sty_code := act_prpty_tax_rec.code;
1402 l_dep_sty_tbl(j).stream_type_purpose := act_prpty_tax_rec.stream_type_purpose;
1403 END LOOP;
1404
1405 ELSIF l_sty_purpose = 'ESTIMATED_PROPERTY_TAX' THEN
1406
1407 FOR est_prpty_tax_rec IN est_prpty_tax_csr LOOP
1408 j := j + 1;
1409 l_dep_sty_tbl(j).sty_id := est_prpty_tax_rec.id;
1410 l_dep_sty_tbl(j).sty_code := est_prpty_tax_rec.code;
1411 l_dep_sty_tbl(j).stream_type_purpose := est_prpty_tax_rec.stream_type_purpose;
1412 END LOOP;
1413
1414
1415 ELSIF l_sty_purpose = 'EXPENSE' THEN
1416
1417 FOR expense_rec IN expense_csr LOOP
1418 j := j + 1;
1419 l_dep_sty_tbl(j).sty_id := expense_rec.id;
1420 l_dep_sty_tbl(j).sty_code := expense_rec.code;
1421 l_dep_sty_tbl(j).stream_type_purpose := expense_rec.stream_type_purpose;
1422 END LOOP;
1423
1424
1425 ELSIF l_sty_purpose = 'FEE_PAYMENT' THEN
1426
1427 FOR fee_payment_rec IN fee_payment_csr LOOP
1428 IF p_book_class = 'LOAN' AND fee_payment_rec.code = 'FEE RENEWAL' THEN
1429 NULL;
1430 ELSE
1431 j := j + 1;
1432 l_dep_sty_tbl(j).sty_id := fee_payment_rec.id;
1433 l_dep_sty_tbl(j).sty_code := fee_payment_rec.code;
1434 l_dep_sty_tbl(j).stream_type_purpose := fee_payment_rec.stream_type_purpose;
1435 END IF;
1436 END LOOP;
1437
1438
1439 ELSIF l_sty_purpose = 'PASS_THROUGH_SERVICE' THEN
1440
1441 FOR passthru_srv_rec IN passthru_srv_csr LOOP
1442 j := j + 1;
1443 l_dep_sty_tbl(j).sty_id := passthru_srv_rec.id;
1444 l_dep_sty_tbl(j).sty_code := passthru_srv_rec.code;
1445 l_dep_sty_tbl(j).stream_type_purpose := passthru_srv_rec.stream_type_purpose;
1446 END LOOP;
1447
1448
1449 ELSIF l_sty_purpose = 'SERVICE_PAYMENT' THEN
1450
1451 FOR srv_payment_rec IN srv_payment_csr LOOP
1452 j := j + 1;
1453 l_dep_sty_tbl(j).sty_id := srv_payment_rec.id;
1454 l_dep_sty_tbl(j).sty_code := srv_payment_rec.code;
1455 l_dep_sty_tbl(j).stream_type_purpose := srv_payment_rec.stream_type_purpose;
1456 END LOOP;
1457
1458 ELSIF l_sty_purpose = 'SUBSIDY' THEN
1459 ---------------------------------------------------------------------
1460 -- Need to change the cursor
1461 ---------------------------------------------------------------------
1462
1463 FOR subsidy_rec IN subsidy_csr LOOP
1464 j := j + 1;
1465 l_dep_sty_tbl(j).sty_id := subsidy_rec.id;
1466 l_dep_sty_tbl(j).sty_code := subsidy_rec.code;
1467 l_dep_sty_tbl(j).stream_type_purpose := subsidy_rec.stream_type_purpose;
1468 END LOOP;
1469
1470 END IF;
1471
1472 x_dep_sty_tbl := l_dep_sty_tbl;
1473
1474 EXCEPTION
1475 WHEN OTHERS THEN
1476 Fnd_File.put_line(Fnd_File.LOG, SQLERRM);
1477
1478 END Get_dependent_sty;
1479
1480
1481 ---------------------------------------------------------------------------
1482 -- PROCEDURE Get_Sty_Purpose_Prc_Name
1483 ---------------------------------------------------------------------------
1484 -- Start of comments
1485 --
1486 -- Procedure Name : Get_Sty_Purpose_Prc_Name
1487 -- Description : Function to return the pricing name for a stream type purpose
1488 -- Business Rules :
1489 -- Parameters :
1490 -- Version : 1.0
1491 -- End of comments
1492 ---------------------------------------------------------------------------
1493
1494
1495 FUNCTION Get_Sty_Purpose_Prc_Name (p_sty_purpose IN VARCHAR2)
1496 RETURN VARCHAR2
1497
1498 IS
1499 l_pricing_name okl_st_gen_tmpt_lns.pricing_name%TYPE;
1500 BEGIN
1501
1502 IF p_sty_purpose = 'FEE_PAYMENT' THEN
1503 l_pricing_name := 'Periodic Income';
1504 ELSIF p_sty_purpose = 'SUBSIDY_INCOME' THEN
1505 l_pricing_name := 'Single Subsidy Accrual';
1506 END IF;
1507
1508 RETURN l_pricing_name;
1509
1510 EXCEPTION
1511 WHEN OTHERS THEN
1512 Fnd_File.put_line(Fnd_File.LOG, SQLERRM);
1513 RETURN NULL;
1514
1515
1516 END Get_Sty_Purpose_Prc_Name;
1517
1518
1519 ---------------------------------------------------------------------------
1520 -- PROCEDURE Get_Sty_Id
1521 ---------------------------------------------------------------------------
1522 -- Start of comments
1523 --
1524 -- Procedure Name : Get_Sty_Id
1525 -- Description : Procedure to return sty_id for a sty code
1526 -- Business Rules :
1527 -- Parameters :
1528 -- Version : 1.0
1529 -- End of comments
1530 ---------------------------------------------------------------------------
1531
1532
1533 FUNCTION Get_Sty_Id (p_sty_code IN VARCHAR2,
1534 p_sty_purpose IN VARCHAR2)
1535 RETURN NUMBER
1536 IS
1537
1538 CURSOR sty_id_csr IS
1539 SELECT id
1540 FROM okl_strm_type_b
1541 WHERE code = p_sty_code
1542 AND stream_type_purpose = p_sty_purpose;
1543
1544 l_sty_id NUMBER;
1545
1546 BEGIN
1547
1548 OPEN sty_id_csr;
1549 FETCH sty_id_csr INTO l_sty_id;
1550 CLOSE sty_id_csr;
1551
1552 RETURN l_sty_id;
1553
1554 END Get_Sty_Id;
1555
1556
1557
1558 ---------------------------------------------------------------------------
1559 -- PROCEDURE Add_Mandatory
1560 ---------------------------------------------------------------------------
1561 -- Start of comments
1562 --
1563 -- Procedure Name : Add_Mandatory
1564 -- Description : Procedure to add the mandatory primary and dependent
1565 -- stream template lines to a stream template
1566 -- Business Rules :
1567 -- Parameters :
1568 -- Version : 1.0
1569 -- End of comments
1570 ---------------------------------------------------------------------------
1571
1572
1573 PROCEDURE Add_Mandatory (p_book_class IN VARCHAR2,
1574 p_gttv_rec IN gttv_rec_type,
1575 x_gtlv_tbl OUT NOCOPY gtlv_tbl_type)
1576 IS
1577
1578 -- Cursor to fetch the mandatory stream type for a lease contract
1579
1580 CURSOR lease_primary_csr (l_gtt_id NUMBER) IS
1581 SELECT id, code, stream_type_purpose FROM okl_strm_type_v
1582 WHERE id NOT IN
1583 (SELECT primary_sty_id FROM okl_st_gen_tmpt_lns WHERE gtt_id = l_gtt_id)
1584 AND code IN (
1585 'ASSET REPAIR CHARGE',
1586 'BILLING ADJUSTMENT',
1587 'BOOK DEPRECIATION',
1588 'CURE',
1589 'DOCUMENT REQUEST FEE - AMORTIZATION SCHEDULE',
1590 'DOCUMENT REQUEST FEE - AUDIT LETTER',
1591 'EQUIPMENT EXCHANGE REQUEST FEE',
1592 'INTEREST RATE CONVERSION FEE',
1593 'DOCUMENTS REQUEST FEE - INVOICE ON DEMAND',
1594 'DOCUMENTS REQUEST FEE - INVOICE REPRINT',
1595 'PAYMENT SETUP CHANGE FEES',
1596 'RESTRUCTURE REQUEST FEE',
1597 'TERMINATION REQUEST FEE',
1598 'TRANSFER FEE',
1599 'DOCUMENT REQUEST FEE - VARIABLE RATE STATEMENT',
1600 'DOCUMENT REQUEST FEE - VAT SCHEDULE',
1601 'SERVICE FEE - DOCUMENT REQUEST',
1602 'SERVICE FEE',
1603 'FEDERAL DEPRECIATION',
1604 'FUNDING',
1605 'INSURANCE ADJUSTMENT',
1606 'INSURANCE ACCRUAL ADJUSTMENT',
1607 'INSURANCE EXPENSE',
1608 'INSURANCE INCOME',
1609 'INSURANCE PAYABLE',
1610 'INSURANCE RECEIVABLE',
1611 'INSURANCE REFUND',
1612 'INTEREST RATE CONVERSION FEE',
1613 'INVESTOR PRE-TAX INCOME',
1614 'INVESTOR RENTAL ACCRUAL',
1615 'LATE FEE',
1616 'LATE INTEREST',
1617 'PRE-FUNDING',
1618 'RENT',
1619 'RESIDUAL VALUE',
1620 'STATE DEPRECIATION',
1621 'INTERIM INTEREST')
1622 AND created_by = 1;
1623
1624
1625 -- Cursor to fetch the mandatory stream type for a loan contract
1626
1627 CURSOR loan_csr (l_gtt_id NUMBER) IS
1628 SELECT id, code, stream_type_purpose FROM okl_strm_type_v
1629 WHERE id NOT IN
1630 (SELECT primary_sty_id FROM okl_st_gen_tmpt_lns WHERE gtt_id = l_gtt_id)
1631 AND code IN (
1632 'BILLING ADJUSTMENT',
1633 'CURE',
1634 'FUNDING',
1635 'INSURANCE ADJUSTMENT',
1636 'INSURANCE ACCRUAL ADJUSTMENT',
1637 'INSURANCE EXPENSE',
1638 'INSURANCE INCOME',
1639 'INSURANCE PAYABLE',
1640 'INSURANCE RECEIVABLE',
1641 'INSURANCE REFUND',
1642 'LATE FEE',
1643 'LATE INTEREST',
1644 'PRE-FUNDING',
1645 'VARIABLE INTEREST SCHEDULE',
1646 'ASSET REPAIR CHARGE',
1647 'DOCUMENT REQUEST FEE - AMORTIZATION SCHEDULE',
1648 'DOCUMENT REQUEST FEE - AUDIT LETTER',
1649 'EQUIPMENT EXCHANGE REQUEST FEE',
1650 'INTEREST RATE CONVERSION FEE',
1651 'DOCUMENTS REQUEST FEE - INVOICE ON DEMAND',
1652 'DOCUMENTS REQUEST FEE - INVOICE REPRINT',
1653 'PAYMENT SETUP CHANGE FEES',
1654 'RESTRUCTURE REQUEST FEE',
1655 'TERMINATION REQUEST FEE',
1656 'TRANSFER FEE',
1657 'DOCUMENT REQUEST FEE - VARIABLE RATE STATEMENT',
1658 'DOCUMENT REQUEST FEE - VAT SCHEDULE',
1659 'SERVICE FEE - DOCUMENT REQUEST',
1660 'SERVICE FEE',
1661 'RENT',
1662 'VARIABLE INTEREST CHARGE',
1663 'INTERIM INTEREST')
1664 AND created_by = 1;
1665
1666 -- Cursor to fetch the mandatory stream type for a investor contract
1667
1668 CURSOR investor_csr (l_gtt_id NUMBER) IS
1669 SELECT id, code FROM okl_strm_type_v
1670 WHERE id NOT IN
1671 (SELECT primary_sty_id FROM okl_st_gen_tmpt_lns WHERE gtt_id = l_gtt_id)
1672 AND code IN (
1673 'INVESTOR CONTRACT OBLIGATION PAYABLE',
1674 'INVESTOR DISBURSEMENT ADJUSTMENT',
1675 'INVESTOR EVERGREEN RENT PAYABLE',
1676 'INVESTOR INTEREST PAYABLE',
1677 'INVESTOR LATE FEE PAYABLE',
1678 'INVESTOR LATE INTEREST PAYABLE',
1679 'INVESTOR PAYABLE',
1680 'INVESTOR PRINCIPAL PAYABLE',
1681 'INVESTOR RECEIVABLE',
1682 'INVESTOR RENT BUYBACK',
1683 'INVESTOR RENT DISBURSEMENT BASIS',
1684 'INVESTOR RENT PAYABLE',
1685 'INVESTOR RESIDUAL BUYBACK',
1686 'INVESTOR RESIDUAL DISBURSEMENT BASIS',
1687 'INVESTOR RESIDUAL PAYABLE',
1688 'PRESENT VALUE SECURITIZED RENT',
1689 'PRESENT VALUE SECURITIZED RESIDUAL')
1690 AND created_by = 1;
1691
1692 -- Cursor to fetch the dependent stream type for a leaseop contract
1693
1694 CURSOR leaseop_dep_csr (l_gtt_id NUMBER) IS
1695 SELECT id, code FROM okl_strm_type_v
1696 WHERE id NOT IN
1697 (SELECT dependent_sty_id FROM okl_st_gen_tmpt_lns WHERE gtt_id = l_gtt_id)
1698 AND code IN (
1699 'RENTAL ACCRUAL',
1700 'ADVANCED RENTALS',
1701 'PRESENT VALUE RENT',
1702 'EVERGREEN RENT',
1703 'PASS THROUGH RENEWAL RENT',
1704 'STIP LOSS VALUE',
1705 'TERMINATION VALUE',
1706 'PRESENT VALUE RESIDUAL',
1707 'GUARANTEED RESIDUAL THIRD PARTY',
1708 --'GUARANTEED RESIDUAL INSURED',
1709 --'PV GUARANTEE',
1710 --'PV UNGUARANTEED RESIDUAL',
1711 'PV GUARANTEED RESIDUAL',
1712 'RESIDUAL VALUE INSURANCE PREMIUM',
1713 'PRESENT VALUE UNINSURED RESIDUAL',
1714 'PRESENT VALUE INSURED RESIDUAL',
1715 'PRESENT VALUE UNGUARANTEED RESIDUAL')
1716 AND created_by = 1;
1717
1718 -- Cursor to fetch the dependent stream type for a leasest/leasedf contract
1719
1720 CURSOR leasedf_st_dep_csr (l_gtt_id NUMBER) IS
1721 SELECT id, code FROM okl_strm_type_v
1722 WHERE id NOT IN
1723 (SELECT dependent_sty_id FROM okl_st_gen_tmpt_lns WHERE gtt_id = l_gtt_id)
1724 AND code IN (
1725 'ADVANCED RENTALS',
1726 'PRESENT VALUE RENT',
1727 'EVERGREEN RENT',
1728 'STIP LOSS VALUE',
1729 'TERMINATION VALUE',
1730 'PRE-TAX INCOME',
1731 'PASS THROUGH RENEWAL RENT',
1732 'PRESENT VALUE RESIDUAL',
1733 --'GUARANTEED RESIDUAL INSURED',
1734 --'PV GUARANTEE',
1735 --'PV UNGUARANTEED RESIDUAL',
1736 'GUARANTEED RESIDUAL THIRD PARTY',
1737 'PV GUARANTEED RESIDUAL',
1738 'RESIDUAL VALUE INSURANCE PREMIUM',
1739 'PRESENT VALUE INSURED RESIDUAL',
1740 'PRESENT VALUE UNINSURED RESIDUAL',
1741 'PRESENT VALUE UNGUARANTEED RESIDUAL')
1742 AND created_by = 1;
1743
1744 -- Cursor to fetch the dependent stream type for a loan contract
1745
1746 CURSOR loan_dep_csr (l_gtt_id NUMBER) IS
1747 SELECT id, code FROM okl_strm_type_v
1748 WHERE id NOT IN
1749 (SELECT dependent_sty_id FROM okl_st_gen_tmpt_lns WHERE gtt_id = l_gtt_id)
1750 AND code IN (
1751 'VARIABLE INCOME NON-ACCRUAL',
1752 'VARIABLE INCOME ACCRUAL',
1753 'ADVANCED RENTALS',
1754 'PRINCIPAL CATCH UP',
1755 'INTEREST INCOME',
1756 'INTEREST PAYMENT',
1757 'LOAN PAYMENT',
1758 'UNSCHEDULED PRINCIPAL PAYMENT',
1759 'PRINCIPAL BALANCE',
1760 'PRINCIPAL PAYMENT')
1761 AND created_by = 1;
1762
1763 l_gtlv_tbl gtlv_tbl_type;
1764 l_rent_sty_id NUMBER;
1765 l_rv_sty_id NUMBER;
1766 l_variable_sty_id NUMBER;
1767 j NUMBER := 0;
1768
1769
1770 BEGIN
1771
1772 j := 0;
1773
1774 -- Add mandatory stream type for a lease contract
1775
1776 IF p_book_class IN ('LEASEOP', 'LEASEDF', 'LEASEST') THEN
1777 FOR lease_primary_rec IN lease_primary_csr (p_gttv_rec.id) LOOP
1778 IF lease_primary_rec.code = 'RENT' THEN
1779 l_rent_sty_id := lease_primary_rec.id;
1780 ELSIF lease_primary_rec.code ='RESIDUAL VALUE' THEN
1781 l_rv_sty_id := lease_primary_rec.id;
1782 END IF;
1783
1784 j := j +1;
1785
1786 IF lease_primary_rec.code = 'DOCUMENT REQUEST FEE - AMORTIZATION SCHEDULE' AND
1787 lease_primary_rec.stream_type_purpose <> 'SERVICE_FEE_AMORT_SCHEDULE' THEN
1788 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - AMORTIZATION SCHEDULE', 'SERVICE_FEE_AMORT_SCHEDULE');
1789 l_gtlv_tbl(j).pricing_name := NULL;
1790 ELSIF lease_primary_rec.code = 'DOCUMENT REQUEST FEE - AUDIT LETTER' AND
1791 lease_primary_rec.stream_type_purpose <> 'SERVICE_FEE_AUDIT_LETTER' THEN
1792 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - AUDIT LETTER', 'SERVICE_FEE_AUDIT_LETTER');
1793 l_gtlv_tbl(j).pricing_name := NULL;
1794 ELSIF lease_primary_rec.code = 'EQUIPMENT EXCHANGE REQUEST FEE' AND
1795 lease_primary_rec.stream_type_purpose <> 'SERVICE_FEE_EXCHG_REQUEST' THEN
1796 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - EXCHANGE REQUEST', 'SERVICE_FEE_EXCHG_REQUEST');
1797 l_gtlv_tbl(j).pricing_name := NULL;
1798 ELSIF lease_primary_rec.code = 'SERVICE FEE' AND
1799 lease_primary_rec.stream_type_purpose <> 'SERVICE_FEE_GENERAL' THEN
1800 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - GENERAL', 'SERVICE_FEE_GENERAL');
1801 l_gtlv_tbl(j).pricing_name := NULL;
1802 ELSIF lease_primary_rec.code = 'INTEREST RATE CONVERSION FEE' AND
1803 lease_primary_rec.stream_type_purpose <> 'SERVICE_FEE_INTEREST_CONV' THEN
1804 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - INTEREST CONVERSION', 'SERVICE_FEE_INTEREST_CONV');
1805 l_gtlv_tbl(j).pricing_name := NULL;
1806 ELSIF lease_primary_rec.code = 'DOCUMENTS REQUEST FEE - INVOICE ON DEMAND' AND
1807 lease_primary_rec.stream_type_purpose <> 'SERVICE_FEE_INVOICE_DEMAND' THEN
1808 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - INVOICE ON DEMAND', 'SERVICE_FEE_INVOICE_DEMAND');
1809 l_gtlv_tbl(j).pricing_name := NULL;
1810 ELSIF lease_primary_rec.code = 'DOCUMENTS REQUEST FEE - INVOICE REPRINT' AND
1811 lease_primary_rec.stream_type_purpose <> 'SERVICE_FEE_INVOICE_REPRINT' THEN
1812 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - INVOICE REPRINT', 'SERVICE_FEE_INVOICE_REPRINT');
1813 l_gtlv_tbl(j).pricing_name := NULL;
1814 ELSIF lease_primary_rec.code = 'PAYMENT SETUP CHANGE FEES' AND
1815 lease_primary_rec.stream_type_purpose <> 'SERVICE_FEE_PMT_CHANGE' THEN
1816 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - PAYMENT CHANGE', 'SERVICE_FEE_PMT_CHANGE');
1817 l_gtlv_tbl(j).pricing_name := NULL;
1818 ELSIF lease_primary_rec.code = 'RESTRUCTURE REQUEST FEE' AND
1819 lease_primary_rec.stream_type_purpose <> 'SERVICE_FEE_REST_REQUEST' THEN
1820 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - RESTRUCTURE REQUEST', 'SERVICE_FEE_REST_REQUEST');
1821 l_gtlv_tbl(j).pricing_name := NULL;
1822 ELSIF lease_primary_rec.code = 'TERMINATION REQUEST FEE' AND
1823 lease_primary_rec.stream_type_purpose <> 'SERVICE_FEE_TERM_REQUEST' THEN
1824 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - TERMINATION REQUEST', 'SERVICE_FEE_TERM_REQUEST');
1825 l_gtlv_tbl(j).pricing_name := NULL;
1826 ELSIF lease_primary_rec.code = 'TRANSFER FEE' AND
1827 lease_primary_rec.stream_type_purpose <> 'SERVICE_FEE_TRANS_REQUEST' THEN
1828 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - TRANSFER REQUEST', 'SERVICE_FEE_TRANS_REQUEST');
1829 l_gtlv_tbl(j).pricing_name := NULL;
1830 ELSIF lease_primary_rec.code = 'DOCUMENT REQUEST FEE - VARIABLE RATE STATEMENT' AND
1831 lease_primary_rec.stream_type_purpose <> 'SERVICE_FEE_VAR_RATE_STMNT' THEN
1832 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - VARIABLE RATE STATEMENT', 'SERVICE_FEE_VAR_RATE_STMNT');
1833 l_gtlv_tbl(j).pricing_name := NULL;
1834 ELSIF lease_primary_rec.code = 'DOCUMENT REQUEST FEE - VAT SCHEDULE' AND
1835 lease_primary_rec.stream_type_purpose <> 'SERVICE_FEE_VAT_SCHEDULE' THEN
1836 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - VAT SCHEDULE', 'SERVICE_FEE_VAT_SCHEDULE');
1837 l_gtlv_tbl(j).pricing_name := NULL;
1838 ELSE
1839 l_gtlv_tbl(j).primary_sty_id := lease_primary_rec.id;
1840 l_gtlv_tbl(j).pricing_name := Get_Pricing_Name(lease_primary_rec.code);
1841 END IF;
1842
1843 l_gtlv_tbl(j).object_version_number := 1;
1844 l_gtlv_tbl(j).primary_yn := 'Y';
1845 l_gtlv_tbl(j).dependent_sty_id := NULL;
1846 l_gtlv_tbl(j).gtt_id := p_gttv_rec.id;
1847
1848 END LOOP; -- End for lease_primary_rec
1849
1850 -- Add mandatory stream type for a loan contract
1851
1852 ELSIF p_book_class IN ('LOAN', 'LOAN-REVOLVING') THEN
1853 FOR loan_rec IN loan_csr (p_gttv_rec.id) LOOP
1854 IF loan_rec.code = 'RENT' THEN
1855 l_rent_sty_id := loan_rec.id;
1856 ELSIF loan_rec.code ='VARIABLE INTEREST CHARGE' THEN
1857 l_variable_sty_id := loan_rec.id;
1858 END IF;
1859
1860 j := j +1;
1861
1862 IF loan_rec.code = 'DOCUMENT REQUEST FEE - AMORTIZATION SCHEDULE' AND
1863 loan_rec.stream_type_purpose <> 'SERVICE_FEE_AMORT_SCHEDULE' THEN
1864 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - AMORTIZATION SCHEDULE', 'SERVICE_FEE_AMORT_SCHEDULE');
1865 l_gtlv_tbl(j).pricing_name := NULL;
1866 ELSIF loan_rec.code = 'DOCUMENT REQUEST FEE - AUDIT LETTER' AND
1867 loan_rec.stream_type_purpose <> 'SERVICE_FEE_AUDIT_LETTER' THEN
1868 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - AUDIT LETTER', 'SERVICE_FEE_AUDIT_LETTER');
1869 l_gtlv_tbl(j).pricing_name := NULL;
1870 ELSIF loan_rec.code = 'EQUIPMENT EXCHANGE REQUEST FEE' AND
1871 loan_rec.stream_type_purpose <> 'SERVICE_FEE_EXCHG_REQUEST' THEN
1872 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - EXCHANGE REQUEST', 'SERVICE_FEE_EXCHG_REQUEST');
1873 l_gtlv_tbl(j).pricing_name := NULL;
1874 ELSIF loan_rec.code = 'SERVICE FEE' AND
1875 loan_rec.stream_type_purpose <> 'SERVICE_FEE_GENERAL' THEN
1876 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - GENERAL', 'SERVICE_FEE_GENERAL');
1877 l_gtlv_tbl(j).pricing_name := NULL;
1878 ELSIF loan_rec.code = 'INTEREST RATE CONVERSION FEE' AND
1879 loan_rec.stream_type_purpose <> 'SERVICE_FEE_INTEREST_CONV' THEN
1880 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - INTEREST CONVERSION', 'SERVICE_FEE_INTEREST_CONV');
1881 l_gtlv_tbl(j).pricing_name := NULL;
1882 ELSIF loan_rec.code = 'DOCUMENTS REQUEST FEE - INVOICE ON DEMAND' AND
1883 loan_rec.stream_type_purpose <> 'SERVICE_FEE_INVOICE_DEMAND' THEN
1884 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - INVOICE ON DEMAND', 'SERVICE_FEE_INVOICE_DEMAND');
1885 l_gtlv_tbl(j).pricing_name := NULL;
1886 ELSIF loan_rec.code = 'DOCUMENTS REQUEST FEE - INVOICE REPRINT' AND
1887 loan_rec.stream_type_purpose <> 'SERVICE_FEE_INVOICE_REPRINT' THEN
1888 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - INVOICE REPRINT', 'SERVICE_FEE_INVOICE_REPRINT');
1889 l_gtlv_tbl(j).pricing_name := NULL;
1890 ELSIF loan_rec.code = 'PAYMENT SETUP CHANGE FEES' AND
1891 loan_rec.stream_type_purpose <> 'SERVICE_FEE_PMT_CHANGE' THEN
1892 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - PAYMENT CHANGE', 'SERVICE_FEE_PMT_CHANGE');
1893 l_gtlv_tbl(j).pricing_name := NULL;
1894 ELSIF loan_rec.code = 'RESTRUCTURE REQUEST FEE' AND
1895 loan_rec.stream_type_purpose <> 'SERVICE_FEE_REST_REQUEST' THEN
1896 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - RESTRUCTURE REQUEST', 'SERVICE_FEE_REST_REQUEST');
1897 l_gtlv_tbl(j).pricing_name := NULL;
1898 ELSIF loan_rec.code = 'TERMINATION REQUEST FEE' AND
1899 loan_rec.stream_type_purpose <> 'SERVICE_FEE_TERM_REQUEST' THEN
1900 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - TERMINATION REQUEST', 'SERVICE_FEE_TERM_REQUEST');
1901 l_gtlv_tbl(j).pricing_name := NULL;
1902 ELSIF loan_rec.code = 'TRANSFER FEE' AND
1903 loan_rec.stream_type_purpose <> 'SERVICE_FEE_TRANS_REQUEST' THEN
1904 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - TRANSFER REQUEST', 'SERVICE_FEE_TRANS_REQUEST');
1905 l_gtlv_tbl(j).pricing_name := NULL;
1906 ELSIF loan_rec.code = 'DOCUMENT REQUEST FEE - VARIABLE RATE STATEMENT' AND
1907 loan_rec.stream_type_purpose <> 'SERVICE_FEE_VAR_RATE_STMNT' THEN
1908 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - VARIABLE RATE STATEMENT', 'SERVICE_FEE_VAR_RATE_STMNT');
1909 l_gtlv_tbl(j).pricing_name := NULL;
1910 ELSIF loan_rec.code = 'DOCUMENT REQUEST FEE - VAT SCHEDULE' AND
1911 loan_rec.stream_type_purpose <> 'SERVICE_FEE_VAT_SCHEDULE' THEN
1912 l_gtlv_tbl(j).primary_sty_id := Get_Sty_Id('SERVICE FEE - VAT SCHEDULE', 'SERVICE_FEE_VAT_SCHEDULE');
1913 l_gtlv_tbl(j).pricing_name := NULL;
1914 ELSE
1915 l_gtlv_tbl(j).primary_sty_id := loan_rec.id;
1916 l_gtlv_tbl(j).pricing_name := Get_Pricing_Name(loan_rec.code);
1917 END IF;
1918
1919 l_gtlv_tbl(j).object_version_number := 1;
1920 l_gtlv_tbl(j).primary_yn := 'Y';
1921 l_gtlv_tbl(j).dependent_sty_id := NULL;
1922 l_gtlv_tbl(j).gtt_id := p_gttv_rec.id;
1923 END LOOP; -- End for lease_primary_rec
1924
1925 -- Add mandatory stream type for a investor contract
1926
1927 ELSIF p_book_class IN ('SALE') THEN
1928 FOR investor_rec IN investor_csr (p_gttv_rec.id) LOOP
1929
1930 j := j +1;
1931 l_gtlv_tbl(j).object_version_number := 1;
1932 l_gtlv_tbl(j).primary_yn := 'Y';
1933 l_gtlv_tbl(j).primary_sty_id := investor_rec.id;
1934 l_gtlv_tbl(j).dependent_sty_id := NULL;
1935 l_gtlv_tbl(j).pricing_name := NULL;
1936 l_gtlv_tbl(j).gtt_id := p_gttv_rec.id;
1937 END LOOP; -- End for lease_primary_rec
1938 END IF; -- For book class
1939
1940
1941 -- Add dependent stream type for a leaseop contract
1942
1943 IF p_book_class IN ('LEASEOP') THEN
1944 FOR leaseop_dep_rec IN leaseop_dep_csr (p_gttv_rec.id)LOOP
1945 j := j +1;
1946 IF leaseop_dep_rec.code IN ('RENTAL ACCRUAL', 'ADVANCED RENTALS',
1947 'PRESENT VALUE RENT', 'EVERGREEN RENT',
1948 'STIP LOSS VALUE', 'TERMINATION VALUE',
1949 'PASS THROUGH RENEWAL RENT') THEN
1950 l_gtlv_tbl(j).primary_sty_id := l_rent_sty_id;
1951
1952 ELSIF leaseop_dep_rec.code IN ('PRESENT VALUE RESIDUAL',
1953 'GUARANTEED RESIDUAL INSURED',
1954 'GUARANTEED RESIDUAL THIRD PARTY',
1955 'PV GUARANTEE',
1956 'PV GUARANTEED RESIDUAL',
1957 'RESIDUAL VALUE INSURANCE PREMIUM',
1958 'PRESENT VALUE INSURED RESIDUAL',
1959 'PRESENT VALUE UNINSURED RESIDUAL',
1960 'PRESENT VALUE UNGUARANTEED RESIDUAL',
1961 'PV UNGUARANTEED RESIDUAL') THEN
1962 l_gtlv_tbl(j).primary_sty_id := l_rv_sty_id;
1963 END IF;
1964
1965
1966 l_gtlv_tbl(j).object_version_number := 1;
1967 l_gtlv_tbl(j).primary_yn := 'N';
1968 l_gtlv_tbl(j).dependent_sty_id := leaseop_dep_rec.id;
1969 l_gtlv_tbl(j).pricing_name := Get_Pricing_Name(leaseop_dep_rec.code);
1970 l_gtlv_tbl(j).gtt_id := p_gttv_rec.id;
1971 END LOOP; -- End for leaseop_rec
1972
1973 -- Add dependent stream type for a leasest/leasedf contract
1974
1975 ELSIF p_book_class IN ('LEASEDF', 'LEASEST') THEN
1976 FOR leasedf_st_dep_rec IN leasedf_st_dep_csr (p_gttv_rec.id)LOOP
1977 j := j +1;
1978 IF leasedf_st_dep_rec.code IN ('ADVANCED RENTALS',
1979 'PRESENT VALUE RENT',
1980 'EVERGREEN RENT',
1981 'STIP LOSS VALUE',
1982 'TERMINATION VALUE',
1983 'PASS THROUGH RENEWAL RENT',
1984 'PRE-TAX INCOME') THEN
1985 l_gtlv_tbl(j).primary_sty_id := l_rent_sty_id;
1986
1987 ELSIF leasedf_st_dep_rec.code IN ('PRESENT VALUE RESIDUAL',
1988 'GUARANTEED RESIDUAL INSURED',
1989 'GUARANTEED RESIDUAL THIRD PARTY',
1990 'PV GUARANTEE',
1991 'PV GUARANTEED RESIDUAL',
1992 'RESIDUAL VALUE INSURANCE PREMIUM',
1993 'PRESENT VALUE INSURED RESIDUAL',
1994 'PRESENT VALUE UNINSURED RESIDUAL',
1995 'PRESENT VALUE UNGUARANTEED RESIDUAL',
1996 'PV UNGUARANTEED RESIDUAL') THEN
1997 l_gtlv_tbl(j).primary_sty_id := l_rv_sty_id;
1998 END IF;
1999
2000 l_gtlv_tbl(j).object_version_number := 1;
2001 l_gtlv_tbl(j).primary_yn := 'N';
2002 l_gtlv_tbl(j).dependent_sty_id := leasedf_st_dep_rec.id;
2003 l_gtlv_tbl(j).pricing_name := Get_Pricing_Name(leasedf_st_dep_rec.code);
2004 l_gtlv_tbl(j).gtt_id := p_gttv_rec.id;
2005 END LOOP; -- End for leaseop_rec
2006
2007 -- Add dependent stream type for a loan contract
2008
2009 ELSIF p_book_class IN ('LOAN', 'LOAN-REVOLVING') THEN
2010 FOR loan_dep_rec IN loan_dep_csr (p_gttv_rec.id)LOOP
2011
2012 j := j +1;
2013 IF loan_dep_rec.code IN ('ADVANCED RENTALS',
2014 'PRINCIPAL CATCH UP',
2015 'INTEREST INCOME',
2016 'INTEREST PAYMENT',
2017 'UNSCHEDULED PRINCIPAL PAYMENT',
2018 'INTERIM INTEREST',
2019 'LOAN PAYMENT',
2020 'PRINCIPAL BALANCE',
2021 'PRINCIPAL PAYMENT') THEN
2022 l_gtlv_tbl(j).primary_sty_id := l_rent_sty_id;
2023
2024 ELSIF loan_dep_rec.code IN ('VARIABLE INCOME NON-ACCRUAL',
2025 'VARIABLE INCOME ACCRUAL') THEN
2026 l_gtlv_tbl(j).primary_sty_id := l_variable_sty_id;
2027 END IF;
2028 l_gtlv_tbl(j).object_version_number := 1;
2029 l_gtlv_tbl(j).primary_yn := 'N';
2030 l_gtlv_tbl(j).dependent_sty_id := loan_dep_rec.id;
2031 l_gtlv_tbl(j).pricing_name := Get_Pricing_Name(loan_dep_rec.code);
2032 l_gtlv_tbl(j).gtt_id := p_gttv_rec.id;
2033 END LOOP; -- End for leaseop_rec
2034
2035 END IF; -- End If for book class
2036
2037 x_gtlv_tbl := l_gtlv_tbl;
2038
2039 EXCEPTION
2040 WHEN OTHERS THEN
2041 Fnd_File.put_line(Fnd_File.LOG, SQLERRM);
2042
2043 END Add_Mandatory;
2044
2045
2046 ---------------------------------------------------------------------------
2047 -- PROCEDURE Add_Used_Stream_Types
2048 ---------------------------------------------------------------------------
2049 -- Start of comments
2050 --
2051 -- Procedure Name : Add_Used_Stream_Types
2052 -- Description : Procedure to identify the used stream types for a contract
2053 -- Business Rules :
2054 -- Parameters :
2055 -- Version : 1.0
2056 -- End of comments
2057 ---------------------------------------------------------------------------
2058
2059
2060 PROCEDURE Add_Used_Stream_Types (p_book_class IN VARCHAR2,
2061 p_tax_owner IN VARCHAR2,
2062 p_gttv_rec IN gttv_rec_type,
2063 x_gtlv_tbl OUT NOCOPY gtlv_tbl_type)
2064 IS
2065
2066 CURSOR strm_type_csr IS
2067 SELECT
2068 id,
2069 name,
2070 code,
2071 stream_type_subclass,
2072 stream_type_purpose,
2073 start_date,
2074 stream_type_class
2075 FROM
2076 okl_strm_type_v;
2077
2078 l_gtlv_tbl gtlv_tbl_type;
2079 l_rent_sty_id NUMBER;
2080 l_rv_sty_id NUMBER;
2081 l_variable_sty_id NUMBER;
2082 j NUMBER := 0;
2083
2084 l_sty_used VARCHAR2(1) := G_FALSE;
2085 l_primary_yn okl_st_gen_tmpt_lns.primary_yn%TYPE;
2086 l_primary_sty_id okl_st_gen_tmpt_lns.primary_sty_id%TYPE;
2087 l_dependent_sty_id okl_st_gen_tmpt_lns.dependent_sty_id%TYPE;
2088 l_pricing_name okl_st_gen_tmpt_lns.pricing_name%TYPE;
2089 l_dep_sty_tbl dep_sty_tbl;
2090
2091 BEGIN
2092
2093 FOR strm_type_rec IN strm_type_csr LOOP
2094
2095 l_sty_used := Check_If_Used(p_sty_id => strm_type_rec.id,
2096 p_book_class => p_book_class,
2097 p_tax_owner => p_tax_owner);
2098
2099 IF l_sty_used = G_TRUE THEN
2100
2101 j := j+1;
2102 -- Fnd_File.put_line(Fnd_File.LOG,'Populating the Stream Template Line - ' || strm_type_rec.id || ' from Check If Used' );
2103 l_gtlv_tbl(j).object_version_number := 1;
2104 l_gtlv_tbl(j).primary_yn := 'Y';
2105 l_gtlv_tbl(j).primary_sty_id := strm_type_rec.id;
2106 l_gtlv_tbl(j).dependent_sty_id := NULL;
2107 l_gtlv_tbl(j).gtt_id := p_gttv_rec.id;
2108
2109
2110 IF strm_type_rec.stream_type_purpose = 'FEE_PAYMENT' THEN
2111 l_gtlv_tbl(j).pricing_name := 'Periodic Income';
2112 ELSE
2113 l_gtlv_tbl(j).pricing_name := Get_Pricing_Name(strm_type_rec.code);
2114 END IF;
2115
2116 l_dep_sty_tbl.DELETE;
2117 Get_dependent_Sty(strm_type_rec.id, p_book_class, l_dep_sty_tbl);
2118
2119 IF l_dep_sty_tbl.COUNT > 0 THEN
2120 FOR i IN l_dep_sty_tbl.first..l_dep_sty_tbl.last LOOP
2121 j:= j+1;
2122 l_gtlv_tbl(j).object_version_number := 1;
2123 l_gtlv_tbl(j).primary_yn := 'N';
2124 l_gtlv_tbl(j).primary_sty_id := strm_type_rec.id;
2125 l_gtlv_tbl(j).dependent_sty_id := l_dep_sty_tbl(i).sty_id;
2126 l_gtlv_tbl(j).gtt_id := p_gttv_rec.id;
2127
2128 IF l_dep_sty_tbl(i).stream_type_purpose = 'SUBSIDY_INCOME' THEN
2129 l_gtlv_tbl(j).pricing_name := 'Single Subsidy Accrual';
2130 ELSE
2131 l_gtlv_tbl(j).pricing_name := Get_Pricing_Name(l_dep_sty_tbl(i).sty_code);
2132 END IF;
2133
2134 END LOOP;
2135 END IF;
2136
2137 END IF;
2138
2139 END LOOP; -- End for strm_type_csr
2140
2141 x_gtlv_tbl := l_gtlv_tbl;
2142
2143 EXCEPTION
2144 WHEN OTHERS THEN
2145 Fnd_File.put_line(Fnd_File.LOG, SQLERRM);
2146
2147 END Add_Used_Stream_Types;
2148
2149
2150 ---------------------------------------------------------------------------
2151 -- PROCEDURE Migrate_Streams_Process
2152 ---------------------------------------------------------------------------
2153 -- Start of comments
2154 --
2155 -- Procedure Name : Migrate_Streams_Process
2156 -- Description : Migrate Streams Process
2157 -- Business Rules :
2158 -- Parameters :
2159 -- Version : 1.0
2160 -- End of comments
2161 ---------------------------------------------------------------------------
2162
2163
2164 PROCEDURE Migrate_Streams_Process(p_stream_generator IN VARCHAR2)
2165 IS
2166
2167 l_gttv_rec gttv_rec_type;
2168 l_gtsv_rec gtsv_rec_type;
2169 l_gtlv_rec gtlv_rec_type;
2170 l_gtpv_rec gtpv_rec_type;
2171
2172 l_gttv_tbl gttv_tbl_type;
2173 l_gtsv_tbl gtsv_tbl_type;
2174 l_gtlv_tbl gtlv_tbl_type;
2175 l_gtpv_tbl gtpv_tbl_type;
2176
2177 x_gttv_rec gttv_rec_type;
2178 x_gtsv_rec gtsv_rec_type;
2179 x_gtlv_rec gtlv_rec_type;
2180 x_gtpv_rec gtpv_rec_type;
2181
2182 x_gttv_tbl gttv_tbl_type;
2183 x_gtsv_tbl gtsv_tbl_type;
2184 x_gtlv_tbl gtlv_tbl_type;
2185 x_gtpv_tbl gtpv_tbl_type;
2186
2187 l_error_msg_rec Okl_Accounting_Util.ERROR_MESSAGE_TYPE;
2188 l_error_msgs_tbl error_msgs_tbl_type;
2189
2190 l_gts_id OKL_ST_GEN_TMPT_SETS.id%TYPE;
2191 l_gts_name OKL_ST_GEN_TMPT_SETS.name%TYPE;
2192 l_gtt_id OKL_ST_GEN_TEMPLATES.id%TYPE;
2193 l_gtt_status OKL_ST_GEN_TEMPLATES.tmpt_status%TYPE;
2194 l_gtt_out_status OKL_ST_GEN_TEMPLATES.tmpt_status%TYPE;
2195 l_gts_found VARCHAR2(1);
2196
2197 l_api_version NUMBER DEFAULT 1.0;
2198 l_init_msg_list VARCHAR2(1) DEFAULT Okl_Api.g_false;
2199 x_return_status VARCHAR2(1) := Okl_Api.g_ret_sts_success;
2200 x_msg_count NUMBER;
2201 x_msg_data VARCHAR2(2000);
2202
2203 l_sty_migrated VARCHAR2(1) := G_FALSE;
2204 l_api_name CONSTANT VARCHAR2(40) := 'migrate_stream_process';
2205
2206 -- Cursor to select all existing products and its values
2207
2208 CURSOR bc_to_csr IS
2209 SELECT
2210 DISTINCT
2211 q1.value deal_type,
2212 q2.value tax_owner
2213 FROM
2214 okl_products_v p,
2215 okl_pdt_pqy_vals_uv q1,
2216 okl_pdt_pqy_vals_uv q2
2217 WHERE
2218 p.id = q1.pdt_id AND q1.name = 'LEASE' AND q1.value IS NOT NULL AND
2219 p.id = q2.pdt_id AND q2.name = 'TAXOWNER' AND q2.value IS NOT NULL
2220 UNION
2221 SELECT
2222 DISTINCT
2223 q1.value deal_type,
2224 'LESSEE' tax_owner
2225 FROM
2226 okl_products_v p,
2227 okl_pdt_pqy_vals_uv q1
2228 WHERE
2229 p.id = q1.pdt_id AND q1.name = 'INVESTOR' AND q1.value IS NOT NULL;
2230
2231
2232 -- Cursor to get all the stream templates for the unique combination
2233 -- of deal type and tax owner.
2234
2235 CURSOR st_tmpt_csr (l_name VARCHAR2) IS
2236 SELECT
2237 gts.id gts_id,
2238 gts.name,
2239 gtt.id gtt_id,
2240 gtt.tmpt_status
2241 FROM
2242 OKL_ST_GEN_TMPT_SETS gts,
2243 OKL_ST_GEN_TEMPLATES gtt
2244 WHERE
2245 gts.id = gtt.gts_id AND
2246 gts.name = l_name;
2247
2248 -- Cursor to get all the stream types avilable in the system
2249
2250 CURSOR strm_type_csr IS
2251 SELECT
2252 id,
2253 name,
2254 code,
2255 stream_type_subclass,
2256 stream_type_purpose,
2257 start_date,
2258 stream_type_class
2259 FROM
2260 okl_strm_type_v;
2261
2262 -- Cursor to find out if the stream types have been migrated or not.
2263
2264 CURSOR sty_mig_csr IS
2265 SELECT '1'
2266 FROM OKL_STRM_TYPE_V STY
2267 WHERE STY.SHORT_DESCRIPTION <> 'UPGRADED SUCCESSFULLY';
2268
2269 -- Cusrsor to get the distinct operating units in the system
2270
2271 CURSOR org_csr (l_deal_type IN VARCHAR2, l_tax_owner IN VARCHAR2) IS
2272 SELECT
2273 DISTINCT aes.org_id
2274 FROM
2275 okl_products_v p,
2276 okl_ae_tmpt_sets aes,
2277 okl_pdt_pqy_vals_uv q1,
2278 okl_pdt_pqy_vals_uv q2
2279 WHERE
2280 p.aes_id = aes.id AND
2281 p.id = q1.pdt_id AND q1.name = 'LEASE' AND q1.value = l_deal_type AND
2282 p.id = q2.pdt_id AND q2.name = 'TAXOWNER' AND q2.value = l_tax_owner
2283 UNION
2284 SELECT
2285 DISTINCT aes.org_id
2286 FROM
2287 okl_products_v p,
2288 okl_pdt_pqy_vals_uv q1,
2289 okl_ae_tmpt_sets aes
2290 WHERE
2291 p.aes_id = aes.id AND
2292 p.id = q1.pdt_id AND q1.name = 'INVESTOR' AND q1.value = l_deal_type;
2293
2294
2295 -- Cursor to fetch the organization namd for a org id.
2296
2297 CURSOR org_name_csr (l_org_id IN NUMBER) IS
2298 SELECT name
2299 FROM hr_operating_units
2300 WHERE organization_id = l_org_id;
2301
2302
2303 CURSOR gtt_start_date_csr IS
2304 SELECT MIN(aes.start_date)
2305 FROM okl_ae_tmpt_sets aes;
2306
2307 j NUMBER;
2308 l_new_gts_name VARCHAR2(100);
2309 l_org_name VARCHAR2(150);
2310
2311 BEGIN
2312 x_return_status := Okl_Api.START_ACTIVITY (
2313 l_api_name
2314 ,l_init_msg_list
2315 ,'_PVT'
2316 ,x_return_status);
2317
2318 -- Check if activity started successfully
2319 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2320 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Error at start activity' );
2321 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2322 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2323 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Error at start activity' );
2324 RAISE Okl_Api.G_EXCEPTION_ERROR;
2325 END IF;
2326
2327 -- Check if stream types are migrated
2328 OPEN sty_mig_csr;
2329 FETCH sty_mig_csr INTO l_sty_migrated;
2330 CLOSE sty_mig_csr;
2331
2332 IF l_sty_migrated IS NOT NULL
2333 THEN
2334 Fnd_File.put_line(Fnd_File.LOG,'Updating the Stream Types with stream type purposes based on its usage...');
2335 -- If stream types are not migrated, migrate the stream types.
2336 Migrate_Stream_Types(x_return_status);
2337 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR)
2338 THEN
2339 Fnd_File.PUT_LINE(Fnd_File.LOG, 'The following errors occured while upgrading the stream types.' );
2340 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2341 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2342 Fnd_File.PUT_LINE(Fnd_File.LOG, 'The following errors occured while upgrading the stream types.' );
2343 RAISE Okl_Api.G_EXCEPTION_ERROR;
2344 END IF;
2345 ELSE
2346 Fnd_File.put_line(Fnd_File.LOG,'Stream Types have been migrated... ');
2347 END IF;
2348 -- Loop through the unique combination of book class and tax owner.
2349 FOR bc_to_rec IN bc_to_csr
2350 LOOP
2351 -- Loop through operating unit.
2352 FOR org_rec IN org_csr (bc_to_rec.deal_type, bc_to_rec.tax_owner)
2353 LOOP
2354 mo_global.set_policy_context('S',org_rec.org_id); --dkagrawa changed for MOAC
2355 Fnd_File.put_line(Fnd_File.LOG,' ');
2356 -- Fnd_File.put_line(Fnd_File.LOG,'Creating Stream Templates for the combination of Deal Type - ' || bc_to_rec.deal_type || ', Tax Owner - ' || bc_to_rec.tax_owner || ', Org Id - ' || org_rec.org_id);
2357 FOR org_name_rec IN org_name_csr (org_rec.org_id) LOOP
2358 l_org_name := org_name_rec.name;
2359 END LOOP;
2360 Fnd_File.put_line(Fnd_File.LOG,'Operating Unit : ' || l_org_name);
2361 Fnd_File.put_line(Fnd_File.LOG,'Creating Stream Templates for the combination of Deal Type - ' || bc_to_rec.deal_type || ', Tax Owner - ' || bc_to_rec.tax_owner );
2362 -- Fnd_File.put_line(Fnd_File.LOG,'Setting Org id to ' || org_rec.org_id);
2363 -- Check if stream templates are available for for the combination of
2364 -- deal type and tax owner.
2365 IF bc_to_rec.deal_type = 'SALE'
2366 THEN
2367 l_new_gts_name := bc_to_rec.deal_type || '-'|| 'LESSEE';
2368 ELSE
2369 l_new_gts_name := bc_to_rec.deal_type || '-'|| bc_to_rec.tax_owner;
2370 END IF;
2371 OPEN st_tmpt_csr (l_new_gts_name);
2372 FETCH st_tmpt_csr INTO l_gts_id, l_gts_name, l_gtt_id, l_gtt_status;
2373 IF st_tmpt_csr%NOTFOUND THEN
2374 l_gts_found := 'N';
2375 ELSE
2376 l_gts_found := 'Y';
2377 END IF;
2378 CLOSE st_tmpt_csr;
2379 -- If stream template is found
2380 IF l_gts_found = 'Y'
2381 THEN
2382 Fnd_File.put_line(Fnd_File.LOG, l_gtt_status || ' Stream Template ' || l_gts_name || ' has been found for the combination of ' ||
2383 'Deal Type ' || bc_to_rec.deal_type || ', Tax Owner - ' || bc_to_rec.tax_owner || ', Org Id - ' || org_rec.org_id);
2384 -- If stream template is not active
2385 IF l_gtt_status <> 'ACTIVE'
2386 THEN
2387 -- validate stream template
2388 IF l_gtt_status <> 'COMPLETE'
2389 THEN
2390 Fnd_File.put_line(Fnd_File.LOG,'Validating the Stream Template - ' || l_gts_name );
2391 Okl_Strm_Gen_Template_Pub.validate_template(
2392 p_api_version => l_api_version,
2393 p_init_msg_list => l_init_msg_list,
2394 x_return_status => x_return_status,
2395 x_msg_count => x_msg_count,
2396 x_msg_data => x_msg_data,
2397 p_gtt_id => l_gtt_id,
2398 x_error_msgs_tbl => l_error_msgs_tbl,
2399 x_return_tmpt_status=> l_gtt_out_status,
2400 p_during_upd_flag => 'N' );
2401 IF (l_gtt_out_status = 'INCOMPLETE')
2402 THEN
2403 Fnd_File.PUT_LINE(Fnd_File.LOG, 'The following errors occured while validating the stream template - ' || l_gts_name );
2404 IF l_error_msgs_tbl.COUNT > 0
2405 THEN
2406 FOR i IN l_error_msgs_tbl.FIRST .. l_error_msgs_tbl.LAST
2407 LOOP
2408 Fnd_File.PUT_LINE(Fnd_File.LOG, l_error_msgs_tbl(i).Error_Message);
2409 END LOOP;
2410 END IF;
2411 END IF; -- (l_gtt_out_status = 'INCOMPLETE')
2412 END IF; -- IF l_gtt_status <> 'COMPLETE' THEN
2413 IF (l_gtt_out_status = 'COMPLETE') OR (l_gtt_status = 'COMPLETE')
2414 THEN
2415 -- activate stream template
2416 Fnd_File.put_line(Fnd_File.LOG,'Activating the Stream Template - ' || l_gts_name );
2417 Okl_Strm_Gen_Template_Pub.activate_template(
2418 p_api_version => l_api_version,
2419 p_init_msg_list => l_init_msg_list,
2420 x_return_status => x_return_status,
2421 x_msg_count => x_msg_count,
2422 x_msg_data => x_msg_data,
2423 p_gtt_id => l_gtt_id );
2424 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS)
2425 THEN
2426 Fnd_File.PUT_LINE(Fnd_File.LOG, 'The following errors occured while activating the template - ' || l_gts_name );
2427 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
2428 IF (l_error_msg_rec.COUNT > 0)
2429 THEN
2430 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
2431 LOOP
2432 IF l_error_msg_rec(m) IS NOT NULL
2433 THEN
2434 Fnd_File.put_line(Fnd_File.LOG, l_error_msg_rec(m));
2435 END IF;
2436 END LOOP;
2437 END IF;
2438 END IF;
2439 END IF; -- IF (l_gtt_out_status = 'COMPLETE') THEN
2440 END IF; -- l_gtt_status <> 'ACTIVE' THEN
2441 ELSE -- -- l_gts_exists = 'Y' THEN
2442 l_gtsv_rec := NULL;
2443 l_gttv_rec := NULL;
2444 l_gtlv_tbl.DELETE;
2445 -- Populate Stream Generation Template set details.
2446 l_gtsv_rec.object_version_number := 1;
2447 l_gtsv_rec.deal_type := bc_to_rec.deal_type;
2448 l_gtsv_rec.pricing_engine := UPPER(p_stream_generator);
2449 IF bc_to_rec.deal_type = 'SALE'
2450 THEN
2451 l_gtsv_rec.name := bc_to_rec.deal_type || '-' || 'LESSEE';
2452 l_gtsv_rec.description := 'Seeded Template for ' || bc_to_rec.deal_type || ' - ' || 'LESSEE';
2453 l_gtsv_rec.tax_owner := 'LESSEE';
2454 ELSE
2455 l_gtsv_rec.name := bc_to_rec.deal_type || '-' || bc_to_rec.tax_owner;
2456 l_gtsv_rec.description := 'Seeded Template for ' || bc_to_rec.deal_type || ' - ' || bc_to_rec.tax_owner;
2457 l_gtsv_rec.tax_owner := bc_to_rec.tax_owner;
2458 END IF;
2459 IF bc_to_rec.deal_type = 'SALE'
2460 THEN
2461 l_gtsv_rec.product_type := 'INVESTOR';
2462 ELSE
2463 l_gtsv_rec.product_type := 'FINANCIAL';
2464 END IF;
2465 -- Populate Stream Generation Template details.
2466 l_gttv_rec.object_version_number := 1;
2467 l_gttv_rec.version := '1.0';
2468 l_gttv_rec.tmpt_status := 'NEW';
2469 OPEN gtt_start_date_csr;
2470 FETCH gtt_start_date_csr INTO l_gttv_rec.start_date;
2471 CLOSE gtt_start_date_csr;
2472 -- Call the API to create stream template set, template and lines
2473 Fnd_File.put_line(Fnd_File.LOG,'Creating the Stream Template set - ' || l_gtsv_rec.name );
2474 create_strm_gen_template(
2475 p_api_version => l_api_version,
2476 p_init_msg_list => l_init_msg_list,
2477 x_return_status => x_return_status,
2478 x_msg_count => x_msg_count,
2479 x_msg_data => x_msg_data,
2480 p_gtsv_rec => l_gtsv_rec,
2481 p_gttv_rec => l_gttv_rec,
2482 p_gtpv_tbl => l_gtpv_tbl,
2483 p_gtlv_tbl => l_gtlv_tbl,
2484 x_gttv_rec => x_gttv_rec);
2485 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS)
2486 THEN
2487 Fnd_File.PUT_LINE(Fnd_File.LOG, 'The following errors occured while creating stream template ' || l_gtsv_rec.name );
2488 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
2489 IF (l_error_msg_rec.COUNT > 0)
2490 THEN
2491 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
2492 LOOP
2493 IF l_error_msg_rec(m) IS NOT NULL
2494 THEN
2495 Fnd_File.put_line(Fnd_File.LOG, l_error_msg_rec(m));
2496 END IF;
2497 END LOOP;
2498 END IF;
2499 END IF;
2500 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Adding mandatory primary and dependent stream types for the stream template ' || l_gtsv_rec.name );
2501 Add_Mandatory (p_book_class => bc_to_rec.deal_type,
2502 p_gttv_rec => x_gttv_rec,
2503 x_gtlv_tbl => l_gtlv_tbl);
2504 IF l_gtlv_tbl.COUNT > 0
2505 THEN
2506 -- Fnd_File.PUT_LINE(Fnd_File.LOG, 'Adding Template Lines for ' || bc_to_rec.deal_type || '-' || bc_to_rec.tax_owner );
2507 insert_template_lines(
2508 p_api_version => l_api_version,
2509 p_init_msg_list => l_init_msg_list,
2510 x_return_status => x_return_status,
2511 x_msg_count => x_msg_count,
2512 x_msg_data => x_msg_data,
2513 p_gtlv_tbl => l_gtlv_tbl);
2514 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS)
2515 THEN
2516 Fnd_File.PUT_LINE(Fnd_File.LOG, 'The following errors occured while adding mandatory template lines ' );
2517 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
2518 IF (l_error_msg_rec.COUNT > 0)
2519 THEN
2520 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST LOOP
2521 IF l_error_msg_rec(m) IS NOT NULL THEN
2522 Fnd_File.put_line(Fnd_File.LOG, l_error_msg_rec(m));
2523 END IF;
2524 END LOOP;
2525 END IF;
2526 END IF;
2527 END IF; -- IF l_gtlv_tbl.COUNT > 0 THEN
2528 IF bc_to_rec.deal_type <> 'SALE'
2529 THEN
2530 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Adding primary and dependent stream types for the stream template ' || l_gtsv_rec.name || ' based on usage. ');
2531 l_gtlv_tbl.DELETE;
2532 Add_Used_Stream_Types (
2533 p_book_class => bc_to_rec.deal_type,
2534 p_tax_owner => bc_to_rec.tax_owner,
2535 p_gttv_rec => x_gttv_rec,
2536 x_gtlv_tbl => l_gtlv_tbl);
2537 IF l_gtlv_tbl.COUNT > 0
2538 THEN
2539 insert_template_lines(
2540 p_api_version => l_api_version,
2541 p_init_msg_list => l_init_msg_list,
2542 x_return_status => x_return_status,
2543 x_msg_count => x_msg_count,
2544 x_msg_data => x_msg_data,
2545 p_gtlv_tbl => l_gtlv_tbl);
2546 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS)
2547 THEN
2548 Fnd_File.PUT_LINE(Fnd_File.LOG, 'The following errors occured while adding used template lines ' );
2549 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
2550 IF (l_error_msg_rec.COUNT > 0) THEN
2551 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST LOOP
2552 IF l_error_msg_rec(m) IS NOT NULL THEN
2553 Fnd_File.put_line(Fnd_File.LOG, l_error_msg_rec(m));
2554 END IF;
2555 END LOOP;
2556 END IF;
2557 END IF;
2558 END IF; -- IF l_gtlv_tbl.COUNT > 0 THEN
2559 END IF;
2560 -- validate stream template
2561 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Validating the template - ' || l_gtsv_rec.name );
2562 Okl_Strm_Gen_Template_Pub.validate_template(
2563 p_api_version => l_api_version,
2564 p_init_msg_list => l_init_msg_list,
2565 x_return_status => x_return_status,
2566 x_msg_count => x_msg_count,
2567 x_msg_data => x_msg_data,
2568 p_gtt_id => x_gttv_rec.id,
2569 x_error_msgs_tbl => l_error_msgs_tbl,
2570 x_return_tmpt_status=> l_gtt_out_status,
2571 p_during_upd_flag => 'N' );
2572 IF (l_gtt_out_status = 'INCOMPLETE')
2573 THEN
2574 Fnd_File.PUT_LINE(Fnd_File.LOG, 'The following errors occured while validating the stream template - ' || l_gtsv_rec.name );
2575 IF l_error_msgs_tbl.COUNT > 0
2576 THEN
2577 FOR i IN l_error_msgs_tbl.FIRST .. l_error_msgs_tbl.LAST LOOP
2578 Fnd_File.PUT_LINE(Fnd_File.LOG, l_error_msgs_tbl(i).Error_Message);
2579 END LOOP;
2580 END IF;
2581 ELSE -- (l_gtt_out_status = 'INCOMPLETE')
2582 -- activate stream template
2583 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Activating the template - ' || l_gtsv_rec.name );
2584 Okl_Strm_Gen_Template_Pub.activate_template(
2585 p_api_version => l_api_version,
2586 p_init_msg_list => l_init_msg_list,
2587 x_return_status => x_return_status,
2588 x_msg_count => x_msg_count,
2589 x_msg_data => x_msg_data,
2590 p_gtt_id => x_gttv_rec.id );
2591 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS)
2592 THEN
2593 Fnd_File.PUT_LINE(Fnd_File.LOG, 'The following errors occured while activating the template - ' || l_gtsv_rec.name );
2594 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
2595 IF (l_error_msg_rec.COUNT > 0)
2596 THEN
2597 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST LOOP
2598 IF l_error_msg_rec(m) IS NOT NULL THEN
2599 Fnd_File.put_line(Fnd_File.LOG, l_error_msg_rec(m));
2600 END IF;
2601 END LOOP;
2602 END IF;
2603 END IF;
2604 END IF;
2605 END IF; -- l_gts_exists = 'Y' THEN
2606 END LOOP; -- End of org csr
2607 END LOOP; -- End of pdt_csr
2608 Okl_Api.END_ACTIVITY (x_msg_count, x_msg_data );
2609 EXCEPTION
2610 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2611 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
2612 IF (l_error_msg_rec.COUNT > 0) THEN
2613 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST LOOP
2614 IF l_error_msg_rec(m) IS NOT NULL THEN
2615 Fnd_File.put_line(Fnd_File.LOG, l_error_msg_rec(m));
2616 END IF;
2617 END LOOP;
2618 END IF;
2619
2620 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2621 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
2622 IF (l_error_msg_rec.COUNT > 0) THEN
2623 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST LOOP
2624 IF l_error_msg_rec(m) IS NOT NULL THEN
2625 Fnd_File.put_line(Fnd_File.LOG, l_error_msg_rec(m));
2626 END IF;
2627 END LOOP;
2628 END IF;
2629
2630 WHEN OTHERS THEN
2631 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
2632 IF (l_error_msg_rec.COUNT > 0) THEN
2633 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST LOOP
2634 IF l_error_msg_rec(m) IS NOT NULL THEN
2635 Fnd_File.put_line(Fnd_File.LOG, l_error_msg_rec(m));
2636 END IF;
2637 END LOOP;
2638 END IF;
2639
2640 END Migrate_Streams_Process;
2641 ---------------------------------------------------------------------------
2642 -- PROCEDURE Migrate_Accounting_Templates
2643 ---------------------------------------------------------------------------
2644 -- Start of comments
2645 --
2646 -- Procedure Name : Migrate_Accounting_Templates
2647 -- Description : Migrate Accounting Templates
2648 -- Business Rules :
2649 -- Parameters :
2650 -- Version : 1.0
2651 -- End of comments
2652 ---------------------------------------------------------------------------
2653
2654 PROCEDURE Migrate_Accounting_Templates
2655 IS
2656 l_aesv_rec aesv_rec_type;
2657 l_avlv_rec avlv_rec_type;
2658 l_atlv_rec atlv_rec_type;
2659 l_pdtv_rec pdtv_rec_type;
2660
2661 x_aesv_rec aesv_rec_type;
2662 x_avlv_rec avlv_rec_type;
2663 x_atlv_rec atlv_rec_type;
2664 x_pdtv_rec pdtv_rec_type;
2665
2666 -- Cursor to select all existing org from template sets
2667
2668 CURSOR aes_org_csr IS
2669 SELECT DISTINCT org_id
2670 FROM okl_ae_tmpt_sets;
2671
2672
2673 -- Cursor to select all existing products and its values
2674 CURSOR pdt_csr IS
2675 SELECT
2676 p.id product_id,
2677 p.name product_name,
2678 p.aes_id aes_id,
2679 p.ptl_id,
2680 q1.value deal_type,
2681 q2.value tax_owner,
2682 aes.name aes_name
2683 FROM
2684 okl_products_v p,
2685 okl_ae_tmpt_sets_v aes,
2686 okl_pdt_pqy_vals_uv q1,
2687 okl_pdt_pqy_vals_uv q2
2688 WHERE
2689 p.aes_id = aes.id AND
2690 aes.gts_id IS NULL AND
2691 p.id = q1.pdt_id AND q1.name = 'LEASE' AND q1.value IS NOT NULL AND
2692 p.id = q2.pdt_id AND q2.name = 'TAXOWNER' AND q2.value IS NOT NULL
2693 UNION
2694 SELECT
2695 p.id product_id,
2696 p.name product_name,
2697 p.aes_id aes_id,
2698 p.ptl_id,
2699 q1.value deal_type,
2700 'LESSEE' tax_owner,
2701 aes.name aes_name
2702 FROM
2703 okl_products_v p,
2704 okl_ae_tmpt_sets_v aes,
2705 okl_pdt_pqy_vals_uv q1
2706 WHERE
2707 p.aes_id = aes.id AND
2708 aes.gts_id IS NULL AND
2709 p.id = q1.pdt_id AND q1.name = 'INVESTOR' AND q1.value IS NOT NULL
2710 ORDER BY aes_id, deal_type, tax_owner,product_id;
2711
2712
2713 -- Cursor to get all the stream templates for the unique combination
2714 -- of deal type and tax owner.
2715
2716 CURSOR st_tmpt_csr (p_deal_type VARCHAR2, p_tax_owner VARCHAR2, l_name VARCHAR2) IS
2717 SELECT gts.id, gts.name
2718 FROM OKL_ST_GEN_TMPT_SETS gts, OKL_ST_GEN_TEMPLATES gtt
2719 WHERE gts.id = gtt.gts_id AND
2720 gts.deal_type = p_deal_type
2721 AND gts.tax_owner = p_tax_owner AND
2722 gts.name = l_name AND
2723 gtt.tmpt_status = 'ACTIVE';
2724
2725 -- Cursor to check to see if the accounting template set is used by more than one product.
2726
2727
2728 -- Cursor to get aes record from database
2729
2730 CURSOR aesv_pk_csr (p_id IN NUMBER) IS
2731 SELECT
2732 id,
2733 object_version_number,
2734 name,
2735 description,
2736 version,
2737 start_date,
2738 end_date,
2739 org_id,
2740 created_by,
2741 creation_date,
2742 last_updated_by,
2743 last_update_date,
2744 last_update_login,
2745 gts_id
2746 FROM okl_ae_tmpt_sets_v
2747 WHERE okl_ae_tmpt_sets_v.id = p_id;
2748
2749 -- Cursor to get all the templates for a template set.
2750
2751 CURSOR avlv_pk_csr (p_aes_id IN NUMBER) IS
2752 SELECT
2753 id,
2754 object_version_number,
2755 try_id,
2756 aes_id,
2757 sty_id,
2758 fma_id,
2759 set_of_books_id,
2760 fac_code,
2761 syt_code,
2762 post_to_gl,
2763 advance_arrears,
2764 memo_yn,
2765 prior_year_yn,
2766 name,
2767 description,
2768 version,
2769 factoring_synd_flag,
2770 start_date,
2771 end_date,
2772 Accrual_Yn,
2773 attribute_category,
2774 attribute1,
2775 attribute2,
2776 attribute3,
2777 attribute4,
2778 attribute5,
2779 attribute6,
2780 attribute7,
2781 attribute8,
2782 attribute9,
2783 attribute10,
2784 attribute11,
2785 attribute12,
2786 attribute13,
2787 attribute14,
2788 attribute15,
2789 org_id,
2790 created_by,
2791 creation_date,
2792 last_updated_by,
2793 last_update_date,
2794 last_update_login,
2795 inv_code
2796 FROM OKL_AE_TEMPLATES
2797 WHERE OKL_AE_TEMPLATES.aes_id = p_aes_id;
2798
2799 -- Cursor to select all template lines for a template
2800
2801 CURSOR atlv_pk_csr (p_avl_id IN NUMBER) IS
2802 SELECT
2803 id,
2804 object_version_number,
2805 avl_id,
2806 crd_code,
2807 code_combination_id,
2808 ae_line_type,
2809 sequence_number,
2810 description,
2811 percentage,
2812 account_builder_yn,
2813 attribute_category,
2814 attribute1,
2815 attribute2,
2816 attribute3,
2817 attribute4,
2818 attribute5,
2819 attribute6,
2820 attribute7,
2821 attribute8,
2822 attribute9,
2823 attribute10,
2824 attribute11,
2825 attribute12,
2826 attribute13,
2827 attribute14,
2828 attribute15,
2829 org_id,
2830 created_by,
2831 creation_date,
2832 last_updated_by,
2833 last_update_date,
2834 last_update_login
2835 FROM OKL_AE_TMPT_LNES
2836 WHERE OKL_AE_TMPT_LNES.avl_id = p_avl_id;
2837
2838
2839 CURSOR org_name_csr (l_org_id IN NUMBER) IS
2840 SELECT name
2841 FROM hr_operating_units
2842 WHERE organization_id = l_org_id;
2843
2844 -- End : Cursors declaration
2845
2846 -- Start : All local variables declaration
2847
2848 l_api_version NUMBER DEFAULT 1.0;
2849 l_init_msg_list VARCHAR2(1) DEFAULT Okl_Api.g_false;
2850 x_return_status VARCHAR2(1) := Okl_Api.g_ret_sts_success;
2851 x_msg_count NUMBER;
2852 x_msg_data VARCHAR2(2000);
2853 l_org_name VARCHAR2(150);
2854
2855 l_product_name okl_products_v.name%TYPE;
2856 l_product_id okl_products_v.id%TYPE;
2857 l_ptl_id okl_products_v.ptl_id%TYPE;
2858 l_aes_id okl_ae_tmpt_sets.id%TYPE;
2859 l_deal_type okl_pdt_pqy_vals_uv.value%TYPE;
2860 l_tax_owner okl_pdt_pqy_vals_uv.value%TYPE;
2861
2862 l_new_aes_id okl_ae_tmpt_sets.id%TYPE;
2863 l_template_id okl_ae_templates.id%TYPE;
2864 l_new_template_id okl_ae_templates.id%TYPE;
2865
2866 l_gts_id OKL_ST_GEN_TMPT_SETS.id%TYPE;
2867 l_gts_name OKL_ST_GEN_TMPT_SETS.name%TYPE;
2868 l_old_aes_name okl_ae_tmpt_sets_v.name%TYPE;
2869
2870 l_row_found VARCHAR2(1000);
2871 l_template_set_found VARCHAR2(1);
2872 l_templates_found VARCHAR2(1);
2873 l_template_lines_found VARCHAR2(1);
2874 l_used_by_other_products VARCHAR2(1);
2875
2876 -- variables for utl_file
2877
2878 l_log_file VARCHAR2(2000);
2879 l_out_file VARCHAR2(2000);
2880 l_out_file_dir VARCHAR2(1000);
2881
2882 l_api_name VARCHAR2(100);
2883
2884
2885 l_error_msg_rec Okl_Accounting_Util.ERROR_MESSAGE_TYPE;
2886 l_prev_deal_type okl_pdt_pqy_vals_uv.value%TYPE;
2887 l_prev_aes_id NUMBER;
2888 -- Bug 4938066
2889 l_prev_tax_owner okl_pdt_pqy_vals_uv.value%TYPE;
2890 l_aes_deal_type okl_pdt_pqy_vals_uv.value%TYPE;
2891 l_aes_tax_owner okl_pdt_pqy_vals_uv.value%TYPE;
2892
2893
2894 BEGIN
2895 x_return_status := Okl_Api.START_ACTIVITY (
2896 l_api_name
2897 ,l_init_msg_list
2898 ,'_PVT'
2899 ,x_return_status);
2900 -- Check if activity started successfully
2901 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR)
2902 THEN
2903 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2904 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR)
2905 THEN
2906 RAISE Okl_Api.G_EXCEPTION_ERROR;
2907 END IF;
2908 FOR aes_org_rec IN aes_org_csr
2909 LOOP
2910 mo_global.set_policy_context('S',aes_org_rec.org_id); --dkagrawa changed for MOAC
2911 FOR org_name_rec IN org_name_csr (aes_org_rec.org_id)
2912 LOOP
2913 l_org_name := org_name_rec.name;
2914 END LOOP;
2915 Fnd_File.put_line(Fnd_File.LOG,' ');
2916 Fnd_File.put_line(Fnd_File.LOG,'Operating Unit : ' || l_org_name);
2917 Fnd_File.put_line(Fnd_File.LOG,'======================================');
2918 Fnd_File.put_line(Fnd_File.LOG,'Migrating Products and Accounting Templates ... ');
2919 -- Loop through the products in the system
2920 FOR pdt_rec IN pdt_csr
2921 LOOP
2922 l_product_id := pdt_rec.product_id;
2923 l_product_name := pdt_rec.product_name;
2924 l_aes_id := pdt_rec.aes_id;
2925 l_deal_type := pdt_rec.deal_type;
2926 l_tax_owner := pdt_rec.tax_owner;
2927 l_ptl_id := pdt_rec.ptl_id;
2928 Fnd_File.put_line(Fnd_File.LOG,' ');
2929 Fnd_File.put_line(Fnd_File.LOG,'Processing Product ' || l_product_name || ' ... ');
2930 Fnd_File.put_line(Fnd_File.LOG,'------------------------------------------------- ');
2931 -- Check if the stream template exists for a product.
2932 l_gts_id := NULL;
2933 l_gts_name := NULL;
2934 OPEN st_tmpt_csr(l_deal_type, l_tax_owner, l_deal_type || '-' || l_tax_owner);
2935 FETCH st_tmpt_csr INTO l_gts_id, l_gts_name;
2936 IF st_tmpt_csr%NOTFOUND
2937 THEN
2938 Fnd_File.put_line(Fnd_File.LOG,'Deal Type : ' || l_deal_type );
2939 Fnd_File.put_line(Fnd_File.LOG,'Tax Owner : ' || l_tax_owner );
2940 Fnd_File.put_line(Fnd_File.LOG,'Stream Template does not exist for this product ');
2941 ELSE
2942 Fnd_File.put_line(Fnd_File.LOG,'Deal Type : ' || l_deal_type );
2943 Fnd_File.put_line(Fnd_File.LOG,'Tax Owner : ' || l_tax_owner );
2944 Fnd_File.put_line(Fnd_File.LOG,'Stream Template : ' || l_gts_name );
2945 END IF;
2946 CLOSE st_tmpt_csr;
2947
2948 -- if the stream template exists for a product.
2949 IF l_gts_id IS NOT NULL
2950 THEN
2951
2952 -- Fnd_File.put_line(Fnd_File.LOG,'Stream Template for Deal Type ' || l_deal_type || ' and Tax Owner ' || l_tax_owner || ' is ' || TO_CHAR(l_gts_id));
2953 l_used_by_other_products := 'N';
2954 IF (l_prev_deal_type IS NULL AND l_prev_tax_owner IS NULL )
2955 AND l_prev_aes_id IS NULL
2956 THEN
2957 -- For the first product, we have to make l_used_by_other_products should be made to 'N'
2958 l_used_by_other_products := 'N';
2959 l_aes_deal_type := l_deal_type;
2960 l_aes_tax_owner := l_tax_owner;
2961 ELSIF ( l_prev_deal_type = l_deal_type AND l_prev_tax_owner = l_tax_owner)
2962 AND l_prev_aes_id = l_aes_id
2963 THEN
2964 -- If the current product has the same quality values and AES
2965 -- as the previous product ..
2966 -- then use the same AES as it is.
2967 IF (l_deal_type = l_aes_deal_type AND l_tax_owner = l_aes_tax_owner )
2968 THEN
2969 l_used_by_other_products := 'N';
2970 ELSE
2971 l_used_by_other_products := 'Y';
2972 END IF;
2973 ELSIF ( l_prev_deal_type <> l_deal_type OR l_prev_tax_owner <> l_tax_owner )
2974 AND l_prev_aes_id = l_aes_id
2975 THEN
2976 -- If the current product has the conflicting quality values and AES is the same
2977 -- as the previous product .. then copy the AES to P-AES
2978 l_used_by_other_products := 'Y';
2979 ELSIF l_prev_aes_id <> l_aes_id
2980 --AND l_prev_deal_type <> l_deal_type
2981 THEN
2982 -- Else, if we have control broken on the AES, then it means that we
2983 -- are actually reached the next set of products for which the AES is the same
2984 l_used_by_other_products := 'N';
2985 l_aes_deal_type := l_deal_type;
2986 l_aes_tax_owner := l_tax_owner;
2987 END IF;
2988 l_prev_deal_type := l_deal_type;
2989 l_prev_tax_owner := l_tax_owner;
2990 l_prev_aes_id := l_aes_id;
2991 -- Fnd_File.put_line(Fnd_File.LOG,'l_used_by_other_products -- ' || l_used_by_other_products);
2992 IF l_used_by_other_products = 'Y'
2993 THEN
2994 -- ============ Start : Create New Template Set for the product ====================
2995 l_template_set_found := 'Y';
2996 OPEN aesv_pk_csr (l_aes_id);
2997 FETCH aesv_pk_csr INTO
2998 l_aesv_rec.id,
2999 l_aesv_rec.object_version_number,
3000 l_aesv_rec.name,
3001 l_aesv_rec.description,
3002 l_aesv_rec.version,
3003 l_aesv_rec.start_date,
3004 l_aesv_rec.end_date,
3005 l_aesv_rec.org_id,
3006 l_aesv_rec.created_by,
3007 l_aesv_rec.creation_date,
3008 l_aesv_rec.last_updated_by,
3009 l_aesv_rec.last_update_date,
3010 l_aesv_rec.last_update_login,
3011 l_aesv_rec.gts_id;
3012 IF aesv_pk_csr%NOTFOUND
3013 THEN
3014 Fnd_File.put_line(Fnd_File.LOG,'Accounting Template set does not exist for product ' || l_product_name);
3015 l_template_set_found := 'N';
3016 END IF;
3017 CLOSE aesv_pk_csr;
3018 IF l_template_set_found = 'Y'
3019 THEN
3020 -- Set a new name for the new accounting template set.
3021 l_old_aes_name := l_aesv_rec.name;
3022 l_aesv_rec.id := Okl_Api.G_MISS_NUM;
3023 l_aesv_rec.name := l_product_name || '-' || l_aesv_rec.name;
3024 l_aesv_rec.gts_id := l_gts_id;
3025 -- Create new template set.
3026 Fnd_File.put_line(Fnd_File.LOG,'Creating Accounting Template set - ' || l_aesv_rec.name || ' by copying from - ' || l_old_aes_name);
3027 Okl_Process_Tmpt_Set_Pub.create_tmpt_set(
3028 p_api_version => l_api_version,
3029 p_init_msg_list => l_init_msg_list,
3030 x_return_status => x_return_status,
3031 x_msg_count => x_msg_count,
3032 x_msg_data => x_msg_data,
3033 p_aesv_rec => l_aesv_rec,
3034 x_aesv_rec => x_aesv_rec);
3035 -- Check if the creation is successful.
3036 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS)
3037 THEN
3038 Fnd_File.put_line(Fnd_File.LOG,'The following errors occured while Creating Accounting Template set - ' || l_aesv_rec.name );
3039 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
3040 IF (l_error_msg_rec.COUNT > 0)
3041 THEN
3042 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
3043 LOOP
3044 IF l_error_msg_rec(m) IS NOT NULL
3045 THEN
3046 Fnd_File.put_line(Fnd_File.LOG, l_error_msg_rec(m));
3047 END IF;
3048 END LOOP;
3049 END IF;
3050 ELSE
3051 -- Get the new accounting template set id.
3052 -- Fnd_File.put_line(Fnd_File.LOG,'Accounting Template set ' || l_aesv_rec.name || ' created ' );
3053 l_new_aes_id := x_aesv_rec.id;
3054 END IF;
3055 END IF; -- l_template_set_found = 'Y' THEN
3056 -- ============ End : Create New Template Set for the product ====================
3057 -- ============ Start : Copy Accounting Templates for the Set ====================
3058 l_templates_found := 'N';
3059 FOR avlv_pk_rec IN avlv_pk_csr (l_aes_id)
3060 LOOP
3061 l_templates_found := 'Y';
3062 l_template_id := avlv_pk_rec.id;
3063 l_avlv_rec.object_version_number := avlv_pk_rec.object_version_number;
3064 l_avlv_rec.try_id := avlv_pk_rec.try_id;
3065 l_avlv_rec.aes_id := l_new_aes_id;
3066 l_avlv_rec.sty_id := avlv_pk_rec.sty_id;
3067 l_avlv_rec.fma_id := avlv_pk_rec.fma_id;
3068 l_avlv_rec.set_of_books_id := avlv_pk_rec.set_of_books_id;
3069 l_avlv_rec.fac_code := avlv_pk_rec.fac_code;
3070 l_avlv_rec.syt_code := avlv_pk_rec.syt_code;
3071 l_avlv_rec.post_to_gl := avlv_pk_rec.post_to_gl;
3072 l_avlv_rec.advance_arrears := avlv_pk_rec.advance_arrears;
3073 l_avlv_rec.memo_yn := avlv_pk_rec.memo_yn;
3074 l_avlv_rec.prior_year_yn := avlv_pk_rec.prior_year_yn;
3075 l_avlv_rec.name := avlv_pk_rec.name;
3076 l_avlv_rec.description := avlv_pk_rec.description;
3077 l_avlv_rec.version := avlv_pk_rec.version;
3078 l_avlv_rec.factoring_synd_flag := avlv_pk_rec.factoring_synd_flag;
3079 l_avlv_rec.start_date := avlv_pk_rec.start_date;
3080 l_avlv_rec.end_date := avlv_pk_rec.end_date;
3081 l_avlv_rec.Accrual_Yn := avlv_pk_rec.Accrual_Yn;
3082 l_avlv_rec.attribute_category := avlv_pk_rec.attribute_category;
3083 l_avlv_rec.attribute1 := avlv_pk_rec.attribute1;
3084 l_avlv_rec.attribute2 := avlv_pk_rec.attribute2;
3085 l_avlv_rec.attribute3 := avlv_pk_rec.attribute3;
3086 l_avlv_rec.attribute4 := avlv_pk_rec.attribute4;
3087 l_avlv_rec.attribute5 := avlv_pk_rec.attribute5;
3088 l_avlv_rec.attribute6 := avlv_pk_rec.attribute6;
3089 l_avlv_rec.attribute7 := avlv_pk_rec.attribute7;
3090 l_avlv_rec.attribute8 := avlv_pk_rec.attribute8;
3091 l_avlv_rec.attribute9 := avlv_pk_rec.attribute9;
3092 l_avlv_rec.attribute10 := avlv_pk_rec.attribute10;
3093 l_avlv_rec.attribute11 := avlv_pk_rec.attribute11;
3094 l_avlv_rec.attribute12 := avlv_pk_rec.attribute12;
3095 l_avlv_rec.attribute13 := avlv_pk_rec.attribute13;
3096 l_avlv_rec.attribute14 := avlv_pk_rec.attribute14;
3097 l_avlv_rec.attribute15 := avlv_pk_rec.attribute15;
3098 l_avlv_rec.org_id := avlv_pk_rec.org_id;
3099 l_avlv_rec.created_by := avlv_pk_rec.created_by;
3100 l_avlv_rec.creation_date := avlv_pk_rec.creation_date;
3101 l_avlv_rec.last_updated_by := avlv_pk_rec.last_updated_by;
3102 l_avlv_rec.last_update_date := avlv_pk_rec.last_update_date;
3103 l_avlv_rec.last_update_login := avlv_pk_rec.last_update_login;
3104 l_avlv_rec.inv_code := avlv_pk_rec.inv_code;
3105 -- Create new template.
3106 Okl_Process_Tmpt_Set_Pub.create_template(
3107 p_api_version => l_api_version,
3108 p_init_msg_list => l_init_msg_list,
3109 x_return_status => x_return_status,
3110 x_msg_count => x_msg_count,
3111 x_msg_data => x_msg_data,
3112 p_avlv_rec => l_avlv_rec,
3113 x_avlv_rec => x_avlv_rec);
3114 -- Check if the creation is successful.
3115 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS)
3116 THEN
3117 Fnd_File.put_line(Fnd_File.LOG,'Accounting Template ' || l_avlv_rec.name ||
3118 ' could not be copied because of the following issues ');
3119 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
3120 IF (l_error_msg_rec.COUNT > 0)
3121 THEN
3122 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
3123 LOOP
3124 IF l_error_msg_rec(m) IS NOT NULL
3125 THEN
3126 Fnd_File.put_line(Fnd_File.LOG, l_error_msg_rec(m));
3127 END IF;
3128 END LOOP;
3129 END IF;
3130 ELSE
3131 -- Get the new accounting template set id.
3132 l_new_template_id := x_avlv_rec.id;
3133 END IF;
3134 -- ============ Start : Copy Accounting Template Lines for the template ============
3135 l_template_lines_found := 'N';
3136 FOR atlv_pk_rec IN atlv_pk_csr (l_template_id)
3137 LOOP
3138 l_template_lines_found := 'Y';
3139 l_atlv_rec.id := atlv_pk_rec.id;
3140 l_atlv_rec.object_version_number := atlv_pk_rec.object_version_number;
3141 l_atlv_rec.avl_id := l_new_template_id;
3142 l_atlv_rec.crd_code := atlv_pk_rec.crd_code;
3143 l_atlv_rec.code_combination_id := atlv_pk_rec.code_combination_id;
3144 l_atlv_rec.ae_line_type := atlv_pk_rec.ae_line_type;
3145 l_atlv_rec.sequence_number := atlv_pk_rec.sequence_number;
3146 l_atlv_rec.description := atlv_pk_rec.description;
3147 l_atlv_rec.percentage := atlv_pk_rec.percentage;
3148 l_atlv_rec.account_builder_yn := atlv_pk_rec.account_builder_yn;
3149 l_atlv_rec.attribute_category := atlv_pk_rec.attribute_category;
3150 l_atlv_rec.attribute1 := atlv_pk_rec.attribute1;
3151 l_atlv_rec.attribute2 := atlv_pk_rec.attribute2;
3152 l_atlv_rec.attribute3 := atlv_pk_rec.attribute3;
3153 l_atlv_rec.attribute4 := atlv_pk_rec.attribute4;
3154 l_atlv_rec.attribute5 := atlv_pk_rec.attribute5;
3155 l_atlv_rec.attribute6 := atlv_pk_rec.attribute6;
3156 l_atlv_rec.attribute7 := atlv_pk_rec.attribute7;
3157 l_atlv_rec.attribute8 := atlv_pk_rec.attribute8;
3158 l_atlv_rec.attribute9 := atlv_pk_rec.attribute9;
3159 l_atlv_rec.attribute10 := atlv_pk_rec.attribute10;
3160 l_atlv_rec.attribute11 := atlv_pk_rec.attribute11;
3161 l_atlv_rec.attribute12 := atlv_pk_rec.attribute12;
3162 l_atlv_rec.attribute13 := atlv_pk_rec.attribute13;
3163 l_atlv_rec.attribute14 := atlv_pk_rec.attribute14;
3164 l_atlv_rec.attribute15 := atlv_pk_rec.attribute15;
3165 l_atlv_rec.org_id := atlv_pk_rec.org_id;
3166 l_atlv_rec.created_by := atlv_pk_rec.created_by;
3167 l_atlv_rec.creation_date := atlv_pk_rec.creation_date;
3168 l_atlv_rec.last_updated_by := atlv_pk_rec.last_updated_by;
3169 l_atlv_rec.last_update_date := atlv_pk_rec.last_update_date;
3170 l_atlv_rec.last_update_login := atlv_pk_rec.last_update_login;
3171 -- Create new template.
3172 Okl_Process_Tmpt_Set_Pub.create_tmpt_lines(
3173 p_api_version => l_api_version,
3174 p_init_msg_list => l_init_msg_list,
3175 x_return_status => x_return_status,
3176 x_msg_count => x_msg_count,
3177 x_msg_data => x_msg_data,
3178 p_atlv_rec => l_atlv_rec,
3179 x_atlv_rec => x_atlv_rec );
3180 -- Check if the creation is successful.
3181 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS)
3182 THEN
3183 Fnd_File.put_line(Fnd_File.LOG,'Accounting Template Lines ' || l_atlv_rec.crd_code ||
3184 ' could not be copied because of the following issues ');
3185 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
3186 IF (l_error_msg_rec.COUNT > 0)
3187 THEN
3188 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
3189 LOOP
3190 IF l_error_msg_rec(m) IS NOT NULL THEN
3191 Fnd_File.put_line(Fnd_File.LOG, l_error_msg_rec(m));
3192 END IF;
3193 END LOOP;
3194 END IF;
3195 END IF;
3196 END LOOP; -- End Loop for atlv_pk_csr
3197 END LOOP; -- End Loop for avlv_pk_csr
3198 -- ============ End : Copy Accounting Templates for the Set ====================
3199 -- ============ Start : Update the product with new accounting template set ==============
3200 -- UPDATE PRODUCT WITH NEW AES_ID
3201 l_pdtv_rec.id := l_product_id;
3202 l_pdtv_rec.aes_id := l_new_aes_id;
3203 l_pdtv_rec.ptl_id := l_ptl_id;
3204 l_pdtv_rec.product_status_code := Okl_Setupproducts_Pvt.G_PDT_STS_INVALID;
3205 Fnd_File.put_line(Fnd_File.LOG,'Associating the product - ' || l_product_name || ' with the new Accounting Template set - ' || l_aesv_rec.name );
3206 Fnd_File.put_line(Fnd_File.LOG,'Invalidating the product - ' || l_product_name );
3207 Okl_Products_Pub.update_products(
3208 p_api_version => l_api_version,
3209 p_init_msg_list => l_init_msg_list,
3210 x_return_status => x_return_status,
3211 x_msg_count => x_msg_count,
3212 x_msg_data => x_msg_data,
3213 p_pdtv_rec => l_pdtv_rec,
3214 x_pdtv_rec => x_pdtv_rec );
3215 -- Check if the updation is successful.
3216 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS)
3217 THEN
3218 Fnd_File.put_line(Fnd_File.LOG,'The following errors occured while associating the product with new Accounting Template set ' );
3219 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
3220 IF (l_error_msg_rec.COUNT > 0)
3221 THEN
3222 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST LOOP
3223 IF l_error_msg_rec(m) IS NOT NULL THEN
3224 Fnd_File.put_line(Fnd_File.LOG, l_error_msg_rec(m));
3225 END IF;
3226 END LOOP;
3227 END IF;
3228 END IF;
3229 -- PRODUCT VALIDATIONS
3230 IF x_return_status = Okl_Api.G_RET_STS_SUCCESS
3231 THEN
3232 Fnd_File.put_line(Fnd_File.LOG,'Validating the product - ' || l_product_name );
3233 Okl_Setupproducts_Pvt.validate_product(
3234 p_api_version => l_api_version,
3235 p_init_msg_list => l_init_msg_list,
3236 x_return_status => x_return_status,
3237 x_msg_count => x_msg_count,
3238 x_msg_data => x_msg_data,
3239 p_pdtv_rec => l_pdtv_rec,
3240 x_pdtv_rec => x_pdtv_rec);
3241 -- Check if the creation is successful.
3242 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS)
3243 THEN
3244 Fnd_File.put_line(Fnd_File.LOG,'The following errors occured while validating the product - ' || l_product_name );
3245 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
3246 IF (l_error_msg_rec.COUNT > 0)
3247 THEN
3248 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST LOOP
3249 IF l_error_msg_rec(m) IS NOT NULL
3250 THEN
3251 Fnd_File.put_line(Fnd_File.LOG, l_error_msg_rec(m));
3252 END IF;
3253 END LOOP;
3254 END IF;
3255 END IF;
3256 IF x_pdtv_rec.PRODUCT_STATUS_CODE = 'PASSED'
3257 THEN
3258 Fnd_File.put_line(Fnd_File.LOG,'Approving the product - ' || l_product_name );
3259 Okl_Setupproducts_Pvt.update_product_status(
3260 p_api_version => l_api_version,
3261 p_init_msg_list => l_init_msg_list,
3262 x_return_status => x_return_status,
3263 x_msg_count => x_msg_count,
3264 x_msg_data => x_msg_data,
3265 p_pdt_status => Okl_Setupproducts_Pvt.G_PDT_STS_APPROVED,
3266 p_pdt_id => x_pdtv_rec.id);
3267 -- Check if the creation is successful.
3268 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS)
3269 THEN
3270 Fnd_File.put_line(Fnd_File.LOG,'The following errors occured while approving the product - ' || l_product_name );
3271 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
3272 IF (l_error_msg_rec.COUNT > 0)
3273 THEN
3274 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
3275 LOOP
3276 IF l_error_msg_rec(m) IS NOT NULL THEN
3277 Fnd_File.put_line(Fnd_File.LOG, l_error_msg_rec(m));
3278 END IF;
3279 END LOOP;
3280 END IF;
3281 END IF;
3282 END IF; -- IF x_pdtv_rec.PRODUCT_STATUS_CODE = 'PASSED' THEN
3283 END IF; -- x_return_status = OKL_API.G_RET_STS_SUCCESS THEN
3284 -- ============ End : Update the product with new accounting template set ==============
3285 ELSIF l_used_by_other_products = 'N' THEN -- l_used_by_other_products = 'Y' THEN
3286 -- ============ Start : Update Template Set with Stream Template ====================
3287 l_template_set_found := 'Y';
3288 OPEN aesv_pk_csr (l_aes_id);
3289 FETCH aesv_pk_csr INTO
3290 l_aesv_rec.id,
3291 l_aesv_rec.object_version_number,
3292 l_aesv_rec.name,
3293 l_aesv_rec.description,
3294 l_aesv_rec.version,
3295 l_aesv_rec.start_date,
3296 l_aesv_rec.end_date,
3297 l_aesv_rec.org_id,
3298 l_aesv_rec.created_by,
3299 l_aesv_rec.creation_date,
3300 l_aesv_rec.last_updated_by,
3301 l_aesv_rec.last_update_date,
3302 l_aesv_rec.last_update_login,
3303 l_aesv_rec.gts_id;
3304 IF aesv_pk_csr%NOTFOUND
3305 THEN
3306 Fnd_File.put_line(Fnd_File.LOG,'Accounting Template set does not exist for product ' || l_product_name);
3307 l_template_set_found := 'N';
3308 END IF;
3309 CLOSE aesv_pk_csr;
3310 IF l_template_set_found = 'Y'
3311 THEN
3312 IF l_aesv_rec.gts_id IS NULL
3313 THEN
3314 l_aesv_rec.id := l_aes_id;
3315 l_aesv_rec.gts_id := l_gts_id;
3316 -- Update Accounting Template set with the gts_id.
3317 Fnd_File.put_line(Fnd_File.LOG,'Updating the accounting template set - ' || l_aesv_rec.name || ' with stream template' );
3318 Okl_Process_Tmpt_Set_Pub.update_tmpt_set(
3319 p_api_version => l_api_version,
3320 p_init_msg_list => l_init_msg_list,
3321 x_return_status => x_return_status,
3322 x_msg_count => x_msg_count,
3323 x_msg_data => x_msg_data,
3324 p_aesv_rec => l_aesv_rec,
3325 x_aesv_rec => x_aesv_rec);
3326 -- Check if the update is successful.
3327 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS)
3328 THEN
3329 Fnd_File.put_line(Fnd_File.LOG,'The following errors occured while updating the accounting template set - ' || l_aesv_rec.name || ' with stream template' );
3330 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
3331 IF (l_error_msg_rec.COUNT > 0)
3332 THEN
3333 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
3334 LOOP
3335 IF l_error_msg_rec(m) IS NOT NULL
3336 THEN
3337 Fnd_File.put_line(Fnd_File.LOG, l_error_msg_rec(m));
3338 END IF;
3339 END LOOP;
3340 END IF;
3341 END IF;
3342 END IF; -- IF l_aesv_rec.gts_id IS NULL THEN
3343 -- INVALIDATE PRODUCT
3344 l_pdtv_rec.id := l_product_id;
3345 l_pdtv_rec.aes_id := l_aes_id;
3346 l_pdtv_rec.ptl_id := l_ptl_id;
3347 l_pdtv_rec.product_status_code := Okl_Setupproducts_Pvt.G_PDT_STS_INVALID;
3348 Fnd_File.put_line(Fnd_File.LOG,'Invalidating the product - ' || l_product_name );
3349 Okl_Products_Pub.update_products(
3350 p_api_version => l_api_version,
3351 p_init_msg_list => l_init_msg_list,
3352 x_return_status => x_return_status,
3353 x_msg_count => x_msg_count,
3354 x_msg_data => x_msg_data,
3355 p_pdtv_rec => l_pdtv_rec,
3356 x_pdtv_rec => x_pdtv_rec);
3357 -- Fnd_File.put_line(Fnd_File.LOG,'product status ' || x_pdtv_rec.product_status_code );
3358 -- Check if the updation is successful.
3359 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS)
3360 THEN
3361 Fnd_File.put_line(Fnd_File.LOG,'The following errors occured while invalidating the product - ' || l_product_name);
3362 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
3363 IF (l_error_msg_rec.COUNT > 0)
3364 THEN
3365 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
3366 LOOP
3367 IF l_error_msg_rec(m) IS NOT NULL
3368 THEN
3369 Fnd_File.put_line(Fnd_File.LOG, l_error_msg_rec(m));
3370 END IF;
3371 END LOOP;
3372 END IF;
3373 END IF;
3374 -- PRODUCT VALIDATIONS
3375 Fnd_File.put_line(Fnd_File.LOG,'Validating the product - ' || l_product_name );
3376 Okl_Setupproducts_Pvt.validate_product(
3377 p_api_version => l_api_version,
3378 p_init_msg_list => l_init_msg_list,
3379 x_return_status => x_return_status,
3380 x_msg_count => x_msg_count,
3381 x_msg_data => x_msg_data,
3382 p_pdtv_rec => l_pdtv_rec,
3383 x_pdtv_rec => x_pdtv_rec);
3384 -- Check if the creation is successful.
3385 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS)
3386 THEN
3387 Fnd_File.put_line(Fnd_File.LOG,'The following errors occured while validating the product - ' || l_product_name );
3388 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
3389 IF (l_error_msg_rec.COUNT > 0)
3390 THEN
3391 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
3392 LOOP
3393 IF l_error_msg_rec(m) IS NOT NULL
3394 THEN
3395 Fnd_File.put_line(Fnd_File.LOG, l_error_msg_rec(m));
3396 END IF;
3397 END LOOP;
3398 END IF;
3399 END IF;
3400 IF x_pdtv_rec.PRODUCT_STATUS_CODE = 'PASSED'
3401 THEN
3402 Fnd_File.put_line(Fnd_File.LOG,'Approving the product - ' || l_product_name );
3403 Okl_Setupproducts_Pvt.update_product_status(
3404 p_api_version => l_api_version,
3405 p_init_msg_list => l_init_msg_list,
3406 x_return_status => x_return_status,
3407 x_msg_count => x_msg_count,
3408 x_msg_data => x_msg_data,
3409 p_pdt_status => Okl_Setupproducts_Pvt.G_PDT_STS_APPROVED,
3410 p_pdt_id => x_pdtv_rec.id);
3411 -- Check if the creation is successful.
3412 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS)
3413 THEN
3414 Fnd_File.put_line(Fnd_File.LOG,'The following errors occured while approving the product - ' || l_product_name);
3415 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
3416 IF (l_error_msg_rec.COUNT > 0)
3417 THEN
3418 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
3419 LOOP
3420 IF l_error_msg_rec(m) IS NOT NULL
3421 THEN
3422 Fnd_File.put_line(Fnd_File.LOG, l_error_msg_rec(m));
3423 END IF;
3424 END LOOP;
3425 END IF;
3426 END IF;
3427 END IF; -- IF x_pdtv_rec.PRODUCT_STATUS_CODE = 'PASSED' THEN
3428 END IF; -- l_template_set_found = 'Y' THEN
3429 -- ============ End : Update Template Set with Stream Template ====================
3430 END IF; -- l_used_by_other_products = 'N' THEN
3431 -- if the stream template does not exist for a product.
3432 ELSE -- l_gts_id IS NULL THEN
3433 Fnd_File.put_line(Fnd_File.LOG,'Stream Template does not exist for Product ' || l_product_name );
3434 Fnd_File.put_line(Fnd_File.LOG,'Invalidating the product - ' || l_product_name );
3435 -- UPDATE THE STATUS OF THE PRODUCT TO INVALID
3436 Okl_Setupproducts_Pvt.update_product_status(
3437 p_api_version => l_api_version,
3438 p_init_msg_list => l_init_msg_list,
3439 x_return_status => x_return_status,
3440 x_msg_count => x_msg_count,
3441 x_msg_data => x_msg_data,
3442 p_pdt_status => Okl_Setupproducts_Pvt.G_PDT_STS_INVALID,
3443 p_pdt_id => l_product_id);
3444 -- Check if the creation is successful.
3445 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS)
3446 THEN
3447 Fnd_File.put_line(Fnd_File.LOG,'The following errors occured while invalidating the product - ' || l_product_name );
3448 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
3449 IF (l_error_msg_rec.COUNT > 0)
3450 THEN
3451 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
3452 LOOP
3453 IF l_error_msg_rec(m) IS NOT NULL
3454 THEN
3455 Fnd_File.put_line(Fnd_File.LOG, l_error_msg_rec(m));
3456 END IF;
3457 END LOOP;
3458 END IF;
3459 END IF;
3460 END IF; -- l_gts_id IS NOT NULL
3461 END LOOP; -- pdt_rec IN pdt_csr LOOP
3462 END LOOP; -- End for aes_org_csr
3463 Okl_Api.END_ACTIVITY (x_msg_count, x_msg_data );
3464 EXCEPTION
3465 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
3466 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
3467 IF (l_error_msg_rec.COUNT > 0) THEN
3468 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST LOOP
3469 IF l_error_msg_rec(m) IS NOT NULL THEN
3470 Fnd_File.put_line(Fnd_File.LOG, l_error_msg_rec(m));
3471 END IF;
3472 END LOOP;
3473 END IF;
3474
3475 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
3476 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
3477 IF (l_error_msg_rec.COUNT > 0) THEN
3478 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST LOOP
3479 IF l_error_msg_rec(m) IS NOT NULL THEN
3480 Fnd_File.put_line(Fnd_File.LOG, l_error_msg_rec(m));
3481 END IF;
3482 END LOOP;
3483 END IF;
3484
3485 WHEN OTHERS THEN
3486 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
3487 IF (l_error_msg_rec.COUNT > 0) THEN
3488 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST LOOP
3489 IF l_error_msg_rec(m) IS NOT NULL THEN
3490 Fnd_File.put_line(Fnd_File.LOG, l_error_msg_rec(m));
3491 END IF;
3492 END LOOP;
3493 END IF;
3494 END Migrate_Accounting_Templates;
3495 END OKL_STREAM_MIGRATION_PVT;