[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_SECURITIZATION_PVT
Source
1 PACKAGE BODY OKL_AM_SECURITIZATION_PVT AS
2 /* $Header: OKLRASZB.pls 120.17.12010000.2 2008/08/01 20:35:03 appldev ship $ */
3
4
5 -- rmunjulu EDAT
6 -- declare g_add_params as global variable, so that it can be passed to all formulae
7 g_add_params okl_execute_formula_pub.ctxt_val_tbl_type;
8 -- rmunjulu 4398936 Added the following global variable for bug
9 g_call_ad_flag boolean default false;
10
11 -- gboomina Bug 4775555 - Start
12 G_DATE_EFFECTIVE_FROM DATE;
13 G_PARTIAL_YN VARCHAR2(3);
14 -- cklee R12 bug7164915/okl.h Bug 7009075 - Added - Start
15 /*========================================================================
16 | PUBLIC PROCEDURE create_inv_khr_obligation
17 |
18 | DESCRIPTION
19 | Processes invester contract obligation for rent. This procedure was
20 | created from logic written in disburse_investor_rent to separate logic
21 | for creation of investor KHR obligation from investor disb adjustment.
22 |
23 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
24 | Called from PROCESS_SECURITIZED_STREAMS
25 |
26 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
27 |
28 | PARAMETERS
29 | p_api_version IN Standard in parameter
30 | p_init_msg_list IN Standard in parameter
31 | x_return_status OUT Standard out parameter
32 | x_msg_count OUT Standard out parameter
33 | x_msg_data OUT Standard out parameter
34 | p_asset_rec IN Record of asset for processing
35 |
36 | MODIFICATION HISTORY
37 | Date Author Description of Changes
38 | 22-MAY-2008 smadhava Created.
39 *=======================================================================*/
40 PROCEDURE create_inv_khr_obligation(
41 p_api_version IN NUMBER,
42 p_init_msg_list IN VARCHAR2,
43 x_return_status OUT NOCOPY VARCHAR2,
44 x_msg_count OUT NOCOPY NUMBER,
45 x_msg_data OUT NOCOPY VARCHAR2,
46 p_ia_id IN NUMBER,
47 p_effective_date IN DATE DEFAULT NULL,
48 p_transaction_date IN DATE DEFAULT NULL,
49 p_asset_rec IN qte_asset_type) IS
50
51 /*-----------------------------------------------------------------------+
52 | Cursor Declarations |
53 +-----------------------------------------------------------------------*/
54
55 CURSOR get_next_trx_val_csr IS
56 SELECT okl_sif_seq.nextval
57 FROM dual;
58
59 /*-----------------------------------------------------------------------+
60 | Local Variable Declarations and initializations |
61 +-----------------------------------------------------------------------*/
62 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
63
64 l_loop_counter NUMBER;
65 l_formula_amount NUMBER := 0;
66 l_formula_name CONSTANT VARCHAR2(40) := 'INVESTOR_RENT_DISBURSEMENT';
67 l_rent_sty CONSTANT VARCHAR2(50) := 'INVESTOR_CNTRCT_OBLIGATION_PAY'; -- SMODUGA 15-Oct-04 Bug 3925469
68 l_disbursement_amount NUMBER;
69
70 l_stmv_rec Okl_Stm_Pvt.stmv_rec_type;
71 l_selv_tbl Okl_Sel_Pvt.selv_tbl_type;
72 x_stmv_rec Okl_Stm_Pvt.stmv_rec_type;
73 x_selv_tbl Okl_Sel_Pvt.selv_tbl_type;
74
75 l_sty_id NUMBER;
76 l_trx_id NUMBER;
77 --06-Dec-2004 PAGARG Bug# 3948473 passing investor agreement id as part of
78 --additonal parameter to obtain formula value.
79 l_flag BOOLEAN;
80 BEGIN
81 --message logging
82 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
83 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE
84 ,'OKL_AM_SECURITIZATION_PVT.create_inv_khr_obligation'
85 ,'Begin (+)');
86 END IF;
87
88 --06-Dec-2004 PAGARG Bug# 3948473 passing investor agreement id as part of
89 --additonal parameter to obtain formula value.
90 l_flag := FALSE;
91 IF g_add_params.COUNT > 0
92 THEN
93 FOR l_loop_counter IN g_add_params.FIRST..g_add_params.LAST
94 LOOP
95 IF g_add_params(l_loop_counter).name = 'inv_agr_id'
96 THEN
97 l_flag := TRUE;
98 g_add_params(l_loop_counter).value := p_ia_id;
99 END IF;
100 END LOOP;
101 END IF;
102 IF l_flag = FALSE
103 THEN
104 l_loop_counter := NVL(g_add_params.LAST, 0) + 1;
105 g_add_params(l_loop_counter).name := 'inv_agr_id';
106 g_add_params(l_loop_counter).value := p_ia_id;
107 END IF;
108
109 -- smoduga +++++++++ User Defined Streams -- start ++++++++++++++++
110 OKL_STREAMS_UTIL.get_primary_stream_type(p_ia_id,
111 l_rent_sty,
112 l_return_status,
113 l_sty_id);
114
115 IF l_sty_id IS NULL OR l_sty_id = OKL_API.G_MISS_NUM THEN
116 --message logging
117 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
118 FND_LOG.STRING(FND_LOG.LEVEL_ERROR
119 ,'OKL_AM_SECURITIZATION_PVT.create_inv_khr_obligation'
120 ,'OKL_STREAMS_UTIL.get_primary_stream_type returned no values');
121 END IF;
122
123 -- smoduga +++++++++ User Defined Streams -- end ++++++++++++++++
124 OKL_API.set_message(p_app_name => G_APP_NAME,
125 p_msg_name => G_INVALID_VALUE1,
126 p_token1 => 'COL_NAME',
127 p_token1_value => 'STY_ID');
128 RAISE OKL_API.G_EXCEPTION_ERROR;
129 END IF;
130
131 IF p_asset_rec.p_amount IS NOT NULL THEN
132 --message logging
133 IF (FND_LOG.LEVEL_STATEMENT >=
134 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
135 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
136 ,'OKL_AM_SECURITIZATION_PVT.create_inv_khr_obligation'
137 ,'calling OKL_AM_UTIL_PVT.get_formula_value with'
138 ||' formula name '||l_formula_name);
139 END IF;
140
141 OKL_AM_UTIL_PVT.get_formula_value(
142 p_formula_name => l_formula_name
143 ,p_chr_id => p_asset_rec.p_khr_id
144 ,p_cle_id => p_asset_rec.p_kle_id
145 ,p_additional_parameters => g_add_params -- rmunjulu EDAT
146 ,x_formula_value => l_formula_amount
147 ,x_return_status => l_return_status);
148
149 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
150 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
151 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
152 RAISE OKL_API.G_EXCEPTION_ERROR;
153 END IF;
154 -- message logging
155 IF (FND_LOG.LEVEL_STATEMENT >=
156 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
157 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
158 ,'OKL_AM_SECURITIZATION_PVT.create_inv_khr_obligation'
159 ,'returning from OKL_AM_UTIL_PVT.get_formula_value'
160 ||', status is '
161 ||l_return_status
162 ||' and l_formula_amount is '
163 ||l_formula_amount);
164 END IF;
165
166 OPEN get_next_trx_val_csr;
167 FETCH get_next_trx_val_csr INTO l_trx_id;
168 CLOSE get_next_trx_val_csr;
169
170 l_disbursement_amount :=
171 (p_asset_rec.p_amount * l_formula_amount);
172 -- stream header parameters
173 l_stmv_rec.khr_id := p_asset_rec.p_khr_id;
174 l_stmv_rec.kle_id := p_asset_rec.p_kle_id;
175 l_stmv_rec.sty_id := l_sty_id;
176 l_stmv_rec.SGN_CODE := 'MANL';
177 l_stmv_rec.SAY_CODE := 'CURR';
178 l_stmv_rec.TRANSACTION_NUMBER := l_trx_id;
179 l_stmv_rec.ACTIVE_YN := 'Y';
180
181 -- rmunjulu 3910833 added code to set source_id and source_table
182 l_stmv_rec.source_id := p_ia_id;
183 l_stmv_rec.source_table := G_SOURCE_TABLE;
184
185 -- stream element parameters
186 l_selv_tbl(1).stream_element_date := p_transaction_date; -- rmunjulu EDAT
187 -- 04 Nov 2004 PAGARG Bug# 3954752
188 l_selv_tbl(1).amount := l_disbursement_amount;
189 l_selv_tbl(1).ACCRUED_YN := 'N';
190 l_selv_tbl(1).SE_LINE_NUMBER := 1;
191
192 --message logging
193 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
194 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
195 ,'OKL_AM_SECURITIZATION_PVT.create_inv_khr_obligation'
196 ,'calling OKL_STREAMS_PUB.create_streams ');
197 END IF;
198
199 OKL_STREAMS_PUB.create_streams(
200 p_api_version => p_api_version
201 ,p_init_msg_list => p_init_msg_list
202 ,x_return_status => l_return_status
203 ,x_msg_count => x_msg_count
204 ,x_msg_data => x_msg_data
205 ,p_stmv_rec => l_stmv_rec
206 ,p_selv_tbl => l_selv_tbl
207 ,x_stmv_rec => x_stmv_rec
208 ,x_selv_tbl => x_selv_tbl );
209
210 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
211 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
212 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
213 RAISE OKL_API.G_EXCEPTION_ERROR;
214 END IF;
215 --message logging
216 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
217 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
218 ,'OKL_AM_SECURITIZATION_PVT.create_inv_khr_obligation'
219 ,'returning from OKL_STREAMS_PUB.create_streams,'
220 ||' status is '||l_return_status);
221 END IF;
222 END IF;
223
224 --message logging
225 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
226 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE
227 ,'OKL_AM_SECURITIZATION_PVT.create_inv_khr_obligation'
228 ,'End (-)');
229 END IF;
230
231 x_return_status := l_return_status;
232
233 EXCEPTION
234 WHEN OKL_API.G_EXCEPTION_ERROR THEN
235
236
237 IF get_next_trx_val_csr%ISOPEN THEN
238 CLOSE get_next_trx_val_csr;
239 END IF;
240
241 --message logging
242 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
243 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
244 ,'OKL_AM_SECURITIZATION_PVT.create_inv_khr_obligation'
245 , 'Handled exception occured');
246 END IF;
247
248 x_return_status := OKL_API.HANDLE_EXCEPTIONS
249 (
250 'create_inv_khr_obligation',
251 G_PKG_NAME,
252 'OKC_API.G_RET_STS_ERROR',
253 x_msg_count,
254 x_msg_data,
255 '_PVT'
256 );
257
258 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
259
260
261 IF get_next_trx_val_csr%ISOPEN THEN
262 CLOSE get_next_trx_val_csr;
263 END IF;
264
265 --message logging
266 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
267 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
268 ,'OKL_AM_SECURITIZATION_PVT.create_inv_khr_obligation'
269 , 'Expected exception occured');
270 END IF;
271
272 x_return_status := OKL_API.HANDLE_EXCEPTIONS
273 (
274 'create_inv_khr_obligation',
275 G_PKG_NAME,
276 'OKC_API.G_RET_STS_UNEXP_ERROR',
277 x_msg_count,
278 x_msg_data,
279 '_PVT'
280 );
281
282 WHEN OTHERS THEN
283
284
285 IF get_next_trx_val_csr%ISOPEN THEN
286 CLOSE get_next_trx_val_csr;
287 END IF;
288
289 --message logging
290 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
291 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
292 ,'OKL_AM_SECURITIZATION_PVT.create_inv_khr_obligation'
293 , 'When others exception occured');
294 END IF;
295
296 OKL_API.set_message(p_app_name => g_app_name,
297 p_msg_name => g_unexpected_error,
298 p_token1 => g_sqlcode_token,
299 p_token1_value => sqlcode,
300 p_token2 => g_sqlerrm_token,
301 p_token2_value => sqlerrm);
302
303 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
304
305
306 END create_inv_khr_obligation;
307 -- cklee R12 bug7164915/okl.h Bug 7009075 - Added - End
308
309 /*========================================================================
310 | PROCEDURE do_disb_adjustments
311 |
312 | DESCRIPTION
313 | do investor disbursement adjustments during full termination
314 |
315 | MODIFICATION HISTORY
316 | 26-sep-05 rmunjulu INVESTOR_DISB_ADJUSTMENTS
317 *=======================================================================*/
318 PROCEDURE do_disb_adjustments (
319 p_api_version IN NUMBER,
320 p_init_msg_list IN VARCHAR2,
321 x_msg_count OUT NOCOPY NUMBER,
322 x_msg_data OUT NOCOPY VARCHAR2,
323 p_ia_id IN NUMBER,
324 p_khr_id IN NUMBER,
325 p_kle_id IN NUMBER,
326 p_partial_yn IN VARCHAR2,
327 p_quote_eff_date IN DATE,
328 x_return_status OUT NOCOPY VARCHAR2) IS
329
330 -- get already disbursed amounts over and above termination date
331 CURSOR get_disb_streams_csr(
332 p_ia_id IN NUMBER,
333 p_kle_id IN NUMBER,
334 p_quote_eff_date IN DATE,
335 p_invdisbas_sty_id IN NUMBER,
336 p_rbkadj_sty_id IN NUMBER) IS
337 SELECT
338 sum(ste.amount)
339 FROM
340 OKL_STRM_ELEMENTS STE,
341 OKL_STREAMS STM,
342 OKL_STRM_TYPE_V STY
343 WHERE ste.amount <> 0
344 AND stm.id = ste.stm_id
345 AND sty.id IN (p_invdisbas_sty_id) -- pick INVESTOR RENT DISB BASIS streams only
346 AND ste.date_billed IS NOT NULL -- already disbursed (fake and real both)
347 AND stm.active_yn = 'Y' -- always active streams only
348 AND stm.say_code = 'CURR' -- always current streams only
349 AND sty.id = stm.sty_id
350 AND stm.kle_id = p_kle_id -- for the terminated asset
351 AND ste.stream_element_date > p_quote_eff_date -- pick streams greater than termination date
352 AND stm.source_id = p_ia_id;
353
354 -- get disbusement amounts which are not yet disbursed but which will be
355 -- disbursed over and above termination date
356 CURSOR get_disb_streams_csr2(
357 p_ia_id IN NUMBER,
358 p_kle_id IN NUMBER,
359 p_quote_eff_date IN DATE,
360 p_invdisbas_sty_id IN NUMBER,
361 p_rbkadj_sty_id IN NUMBER) IS
362 SELECT
363 sum(ste.amount)
364 FROM
365 OKL_STRM_ELEMENTS STE,
366 OKL_STREAMS STM,
367 OKL_STRM_TYPE_V STY,
368 OKL_STRM_ELEMENTS BILLED_STE
369 WHERE ste.amount <> 0
370 AND stm.id = ste.stm_id
371 AND sty.id IN (p_invdisbas_sty_id) -- pick INVESTOR RENT DISB BASIS streams only
372 AND ste.date_billed IS NULL -- NOT YET disbursed (fake or real)
373 AND ste.sel_id = billed_ste.id -- original billing stream was billed
374 AND billed_ste.date_billed IS NOT NULL
375 AND stm.active_yn = 'Y' -- always active streams only
376 AND stm.say_code = 'CURR' -- always current streams only
377 AND sty.id = stm.sty_id
378 AND stm.kle_id = p_kle_id -- for the terminated asset
379 AND ste.stream_element_date > p_quote_eff_date -- pick streams greater than termination date
380 AND stm.source_id = p_ia_id ;
381
382 CURSOR get_next_trx_val_csr IS
383 SELECT okl_sif_seq.nextval
384 FROM dual;
385
386 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
387 l_invdisbas_sty_id NUMBER;
388 l_rbkadj_sty_id NUMBER;
389 l_adj_amount NUMBER := 0;
390 l_adj_amount2 NUMBER := 0;
391 l_trx_id NUMBER;
392 l_stmv_rec OKL_STM_PVT.stmv_rec_type;
393 l_selv_tbl OKL_SEL_PVT.selv_tbl_type;
394 x_stmv_rec OKL_STM_PVT.stmv_rec_type;
395 x_selv_tbl OKL_SEL_PVT.selv_tbl_type;
396 l_api_name VARCHAR2(30) := 'inv_disb';
397 l_api_version NUMBER := 1;
398 l_term_adj_amount NUMBER;
399
400 BEGIN
401
402 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
403 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
404 ,'OKL_AM_SECURITIZATION_PVT.do_disb_adjustments'
405 , 'START+');
406 END IF;
407
408 l_return_status := OKL_API.start_activity(
409 p_api_name => l_api_name,
410 p_pkg_name => G_PKG_NAME,
411 p_init_msg_list => p_init_msg_list,
412 l_api_version => l_api_version,
413 p_api_version => p_api_version,
414 p_api_type => G_API_TYPE,
415 x_return_status => l_return_status);
416
417 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
418 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
419 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
420 RAISE OKL_API.G_EXCEPTION_ERROR;
421 END IF;
422
423 -- get disbursement amounts only when FULL TERMINATION
424 IF p_partial_yn = 'N' THEN
425
426 -- get rebook disbursement stream purpose
427 OKL_STREAMS_UTIL.get_primary_stream_type(
428 p_khr_id => p_ia_id,
429 p_primary_sty_purpose => 'INVESTOR_DISB_ADJUSTMENT',
430 x_return_status => l_return_status,
431 x_primary_sty_id => l_rbkadj_sty_id);
432
433 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
434 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
435 ,'OKL_AM_SECURITIZATION_PVT.do_disb_adjustments'
436 , 'get_primary_stream_type INVESTOR_DISB_ADJUSTMENT returns '||l_return_status);
437 END IF;
438
439 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
440 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
444
441 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
442 RAISE OKL_API.G_EXCEPTION_ERROR;
443 END IF;
445 -- get investor rent disb basis stream purpose
446 OKL_STREAMS_UTIL.get_primary_stream_type(
447 p_khr_id => p_ia_id,
448 p_primary_sty_purpose => 'INVESTOR_RENT_DISB_BASIS',
449 x_return_status => l_return_status,
450 x_primary_sty_id => l_invdisbas_sty_id);
451
452 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
453 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
454 ,'OKL_AM_SECURITIZATION_PVT.do_disb_adjustments'
455 , 'get_primary_stream_type INVESTOR_RENT_DISB_BASIS returns '||l_return_status);
456 END IF;
457
458 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
459 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
460 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
461 RAISE OKL_API.G_EXCEPTION_ERROR;
462 END IF;
463
464 OPEN get_disb_streams_csr(
465 p_ia_id,
466 p_kle_id ,
467 p_quote_eff_date ,
468 l_invdisbas_sty_id ,
469 l_rbkadj_sty_id );
470 FETCH get_disb_streams_csr INTO l_adj_amount;
471 CLOSE get_disb_streams_csr;
472
473
474 OPEN get_disb_streams_csr2(
475 p_ia_id,
476 p_kle_id ,
477 p_quote_eff_date ,
478 l_invdisbas_sty_id ,
479 l_rbkadj_sty_id );
480 FETCH get_disb_streams_csr2 INTO l_adj_amount2;
481 CLOSE get_disb_streams_csr2;
482
483
484 l_term_adj_amount := nvl(l_adj_amount,0) + nvl(l_adj_amount2,0);
485
486 IF l_term_adj_amount <> 0 THEN
487
488 OPEN get_next_trx_val_csr;
489 FETCH get_next_trx_val_csr INTO l_trx_id;
490 CLOSE get_next_trx_val_csr;
491
492 -- stream header parameters
493 l_stmv_rec.khr_id := p_khr_id;
494 l_stmv_rec.kle_id := p_kle_id;
495 l_stmv_rec.sty_id := l_rbkadj_sty_id;
496 l_stmv_rec.SGN_CODE := 'MANL';
497 l_stmv_rec.SAY_CODE := 'CURR';
498 l_stmv_rec.transaction_number := l_trx_id;
499 l_stmv_rec.active_yn := 'Y';
500 l_stmv_rec.source_id := p_ia_id;
501 l_stmv_rec.source_table := G_SOURCE_TABLE;
502 l_stmv_rec.date_current := sysdate;
503
504 -- stream element parameters
505 l_selv_tbl(1).stream_element_date := sysdate; --***
506 l_selv_tbl(1).amount := l_term_adj_amount * -1; -- rmunjulu always negate the amount
507 l_selv_tbl(1).accrued_yn := 'N';
508 l_selv_tbl(1).se_line_number := 1;
509
510 OKL_STREAMS_PUB.create_streams(
511 p_api_version => p_api_version
512 ,p_init_msg_list => OKL_API.G_FALSE
513 ,x_return_status => l_return_status
514 ,x_msg_count => x_msg_count
515 ,x_msg_data => x_msg_data
516 ,p_stmv_rec => l_stmv_rec
517 ,p_selv_tbl => l_selv_tbl
518 ,x_stmv_rec => x_stmv_rec
519 ,x_selv_tbl => x_selv_tbl);
520
521 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
522 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
523 ,'OKL_AM_SECURITIZATION_PVT.do_disb_adjustments'
524 , 'create_streams returns '||l_return_status);
525 END IF;
526
527 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
528 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
529 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
530 RAISE OKL_API.G_EXCEPTION_ERROR;
531 END IF;
532
533 END IF;
534 END IF;
535
536 x_return_status := l_return_status;
537
538 OKL_API.end_activity(x_msg_count => x_msg_count
539 ,x_msg_data => x_msg_data);
540
541 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
542 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
543 ,'OKL_AM_SECURITIZATION_PVT.do_disb_adjustments'
544 , 'END-');
545 END IF;
546
547 EXCEPTION
548 WHEN OKC_API.G_EXCEPTION_ERROR THEN
549
550 IF get_disb_streams_csr%ISOPEN THEN
551 CLOSE get_disb_streams_csr;
552 END IF;
553
554 IF get_disb_streams_csr2%ISOPEN THEN
555 CLOSE get_disb_streams_csr2;
559 CLOSE get_next_trx_val_csr;
556 END IF;
557
558 IF get_next_trx_val_csr%ISOPEN THEN
560 END IF;
561
562 --message logging
563 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
564 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
565 ,'OKL_AM_SECURITIZATION_PVT.do_disb_adjustments'
566 , 'Handled exception occured');
567 END IF;
568 x_return_status := OKC_API.HANDLE_EXCEPTIONS
569 (
570 l_api_name,
571 G_PKG_NAME,
572 'OKC_API.G_RET_STS_ERROR',
573 x_msg_count,
574 x_msg_data,
575 '_PVT'
576 );
577
578 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
579
580 IF get_disb_streams_csr%ISOPEN THEN
581 CLOSE get_disb_streams_csr;
582 END IF;
583
584 IF get_disb_streams_csr2%ISOPEN THEN
585 CLOSE get_disb_streams_csr2;
586 END IF;
587
588 IF get_next_trx_val_csr%ISOPEN THEN
589 CLOSE get_next_trx_val_csr;
590 END IF;
591
592 --message logging
593 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
594 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
595 ,'OKL_AM_SECURITIZATION_PVT.do_disb_adjustments'
596 , 'Unexpected exception occured');
597 END IF;
598 x_return_status := OKC_API.HANDLE_EXCEPTIONS
599 (
600 l_api_name,
601 G_PKG_NAME,
602 'OKC_API.G_RET_STS_UNEXP_ERROR',
603 x_msg_count,
604 x_msg_data,
605 '_PVT'
606 );
607
608 WHEN OTHERS THEN
609
610 IF get_disb_streams_csr%ISOPEN THEN
611 CLOSE get_disb_streams_csr;
612 END IF;
613
614 IF get_disb_streams_csr2%ISOPEN THEN
615 CLOSE get_disb_streams_csr2;
616 END IF;
617
618 IF get_next_trx_val_csr%ISOPEN THEN
619 CLOSE get_next_trx_val_csr;
620 END IF;
621
622 --message logging
623 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
624 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
625 ,'OKL_AM_SECURITIZATION_PVT.do_disb_adjustments'
626 , 'When others exception occured');
627 END IF;
628 OKL_API.set_message(p_app_name => g_app_name,
629 p_msg_name => g_unexpected_error,
630 p_token1 => g_sqlcode_token,
631 p_token1_value => sqlcode,
632 p_token2 => g_sqlerrm_token,
633 p_token2_value => sqlerrm);
634
635 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
636
637 END;
638 -- gboomina Bug 4775555 - End
639
640 /* sosharma Start changes */
641
642 PROCEDURE do_disb_adjustments_loan (
643 p_api_version IN NUMBER,
644 p_init_msg_list IN VARCHAR2,
645 x_msg_count OUT NOCOPY NUMBER,
646 x_msg_data OUT NOCOPY VARCHAR2,
647 p_ia_id IN NUMBER,
648 p_khr_id IN NUMBER,
649 p_kle_id IN NUMBER,
650 p_partial_yn IN VARCHAR2,
651 p_quote_eff_date IN DATE,
652 x_return_status OUT NOCOPY VARCHAR2) IS
653
654 -- get already disbursed amounts over and above termination date
655 CURSOR get_disb_streams_csr(
656 p_ia_id IN NUMBER,
657 p_kle_id IN NUMBER,
658 p_quote_eff_date IN DATE,
659 p_invdisbas_sty_id IN NUMBER,
660 p_intdisbas_sty_id NUMBER,
661 p_rbkadj_sty_id IN NUMBER) IS
662 SELECT
663 sum(ste.amount)
664 FROM
665 OKL_STRM_ELEMENTS STE,
666 OKL_STREAMS STM,
667 OKL_STRM_TYPE_V STY
668 WHERE ste.amount <> 0
669 AND stm.id = ste.stm_id
670 AND sty.id IN (p_invdisbas_sty_id,p_intdisbas_sty_id) -- pick INVESTOR Principal,interest DISB BASIS streams only
671 AND ste.date_billed IS NOT NULL -- already disbursed (fake and real both)
672 AND stm.active_yn = 'Y' -- always active streams only
673 AND stm.say_code = 'CURR' -- always current streams only
674 AND sty.id = stm.sty_id
678
675 AND stm.kle_id = p_kle_id -- for the terminated asset
676 AND ste.stream_element_date > p_quote_eff_date -- pick streams greater than termination date
677 AND stm.source_id = p_ia_id;
679 -- get disbusement amounts which are not yet disbursed but which will be
680 -- disbursed over and above termination date
681 CURSOR get_disb_streams_csr2(
682 p_ia_id IN NUMBER,
683 p_kle_id IN NUMBER,
684 p_quote_eff_date IN DATE,
685 p_invdisbas_sty_id IN NUMBER,
686 p_intdisbas_sty_id IN NUMBER,
687 p_rbkadj_sty_id IN NUMBER) IS
688 SELECT
689 sum(ste.amount)
690 FROM
691 OKL_STRM_ELEMENTS STE,
692 OKL_STREAMS STM,
693 OKL_STRM_TYPE_V STY,
694 OKL_STRM_ELEMENTS BILLED_STE
695 WHERE ste.amount <> 0
696 AND stm.id = ste.stm_id
697 AND sty.id IN (p_invdisbas_sty_id,p_intdisbas_sty_id) -- pick INVESTOR Principal,interest DISB BASIS streams only
698 AND ste.date_billed IS NULL -- NOT YET disbursed (fake or real)
699 AND ste.sel_id = billed_ste.id -- original billing stream was billed
700 AND billed_ste.date_billed IS NOT NULL
701 AND stm.active_yn = 'Y' -- always active streams only
702 AND stm.say_code = 'CURR' -- always current streams only
703 AND sty.id = stm.sty_id
704 AND stm.kle_id = p_kle_id -- for the terminated asset
705 AND ste.stream_element_date > p_quote_eff_date -- pick streams greater than termination date
706 AND stm.source_id = p_ia_id ;
707
708 CURSOR get_next_trx_val_csr IS
709 SELECT okl_sif_seq.nextval
710 FROM dual;
711
712 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
713 l_invdisbas_sty_id NUMBER;
714 l_intdisbas_sty_id NUMBER;
715 l_rbkadj_sty_id NUMBER;
716 l_adj_amount NUMBER := 0;
717 l_adj_amount2 NUMBER := 0;
718 l_trx_id NUMBER;
719 l_stmv_rec OKL_STM_PVT.stmv_rec_type;
720 l_selv_tbl OKL_SEL_PVT.selv_tbl_type;
721 x_stmv_rec OKL_STM_PVT.stmv_rec_type;
722 x_selv_tbl OKL_SEL_PVT.selv_tbl_type;
723 l_api_name VARCHAR2(30) := 'inv_disb';
724 l_api_version NUMBER := 1;
725 l_term_adj_amount NUMBER;
726
727 BEGIN
728
729 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
730 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
731 ,'OKL_AM_SECURITIZATION_PVT.do_disb_adjustments_loan'
732 , 'START+');
733 END IF;
734
735 l_return_status := OKL_API.start_activity(
736 p_api_name => l_api_name,
737 p_pkg_name => G_PKG_NAME,
738 p_init_msg_list => p_init_msg_list,
739 l_api_version => l_api_version,
740 p_api_version => p_api_version,
741 p_api_type => G_API_TYPE,
742 x_return_status => l_return_status);
743
744 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
745 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
746 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
747 RAISE OKL_API.G_EXCEPTION_ERROR;
748 END IF;
749
750 -- get disbursement amounts only when FULL TERMINATION
751 IF p_partial_yn = 'N' THEN
752
753 -- get rebook disbursement stream purpose
754 OKL_STREAMS_UTIL.get_primary_stream_type(
755 p_khr_id => p_ia_id,
756 p_primary_sty_purpose => 'INVESTOR_DISB_ADJUSTMENT',
757 x_return_status => l_return_status,
758 x_primary_sty_id => l_rbkadj_sty_id);
759
760 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
761 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
762 ,'OKL_AM_SECURITIZATION_PVT.do_disb_adjustments_loan'
763 , 'get_primary_stream_type INVESTOR_DISB_ADJUSTMENT returns '||l_return_status);
764 END IF;
765
766 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
767 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
768 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
769 RAISE OKL_API.G_EXCEPTION_ERROR;
770 END IF;
771
772 -- get investor investor disb basis stream purpose
776 x_return_status => l_return_status,
773 OKL_STREAMS_UTIL.get_primary_stream_type(
774 p_khr_id => p_ia_id,
775 p_primary_sty_purpose => 'INVESTOR_PRINCIPAL_DISB_BASIS',
777 x_primary_sty_id => l_invdisbas_sty_id);
778
779 OKL_STREAMS_UTIL.get_primary_stream_type(
780 p_khr_id => p_ia_id,
781 p_primary_sty_purpose => 'INVESTOR_INTEREST_DISB_BASIS',
782 x_return_status => l_return_status,
783 x_primary_sty_id => l_intdisbas_sty_id);
784
785 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
786 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
787 ,'OKL_AM_SECURITIZATION_PVT.do_disb_adjustments_loan'
788 , 'get_primary_stream_type INVESTOR_RENT_DISB_BASIS returns '||l_return_status);
789 END IF;
790
791 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
792 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
793 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
794 RAISE OKL_API.G_EXCEPTION_ERROR;
795 END IF;
796
797 OPEN get_disb_streams_csr(
798 p_ia_id,
799 p_kle_id ,
800 p_quote_eff_date ,
801 l_invdisbas_sty_id ,
802 l_intdisbas_sty_id ,
803 l_rbkadj_sty_id );
804 FETCH get_disb_streams_csr INTO l_adj_amount;
805 CLOSE get_disb_streams_csr;
806
807
808 OPEN get_disb_streams_csr2(
809 p_ia_id,
810 p_kle_id ,
811 p_quote_eff_date ,
812 l_invdisbas_sty_id ,
813 l_intdisbas_sty_id ,
814 l_rbkadj_sty_id );
815 FETCH get_disb_streams_csr2 INTO l_adj_amount2;
816 CLOSE get_disb_streams_csr2;
817
818
819 l_term_adj_amount := nvl(l_adj_amount,0) + nvl(l_adj_amount2,0);
820
821 IF l_term_adj_amount <> 0 THEN
822
823 OPEN get_next_trx_val_csr;
824 FETCH get_next_trx_val_csr INTO l_trx_id;
825 CLOSE get_next_trx_val_csr;
826
827 -- stream header parameters
828 l_stmv_rec.khr_id := p_khr_id;
829 l_stmv_rec.kle_id := p_kle_id;
830 l_stmv_rec.sty_id := l_rbkadj_sty_id;
831 l_stmv_rec.SGN_CODE := 'MANL';
832 l_stmv_rec.SAY_CODE := 'CURR';
833 l_stmv_rec.transaction_number := l_trx_id;
834 l_stmv_rec.active_yn := 'Y';
835 l_stmv_rec.source_id := p_ia_id;
836 l_stmv_rec.source_table := G_SOURCE_TABLE;
837 l_stmv_rec.date_current := sysdate;
838
839 -- stream element parameters
840 l_selv_tbl(1).stream_element_date := sysdate; --***
841 l_selv_tbl(1).amount := l_term_adj_amount * -1; -- rmunjulu always negate the amount
842 l_selv_tbl(1).accrued_yn := 'N';
843 l_selv_tbl(1).se_line_number := 1;
844
845 OKL_STREAMS_PUB.create_streams(
846 p_api_version => p_api_version
847 ,p_init_msg_list => OKL_API.G_FALSE
848 ,x_return_status => l_return_status
849 ,x_msg_count => x_msg_count
850 ,x_msg_data => x_msg_data
851 ,p_stmv_rec => l_stmv_rec
852 ,p_selv_tbl => l_selv_tbl
853 ,x_stmv_rec => x_stmv_rec
854 ,x_selv_tbl => x_selv_tbl);
855
856 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
857 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
858 ,'OKL_AM_SECURITIZATION_PVT.do_disb_adjustments_loan'
859 , 'create_streams returns '||l_return_status);
860 END IF;
861
862 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
863 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
864 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
865 RAISE OKL_API.G_EXCEPTION_ERROR;
866 END IF;
867
868 END IF;
869 END IF;
870
871 x_return_status := l_return_status;
872
873 OKL_API.end_activity(x_msg_count => x_msg_count
874 ,x_msg_data => x_msg_data);
875
876 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
877 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
878 ,'OKL_AM_SECURITIZATION_PVT.do_disb_adjustments_loan'
882 EXCEPTION
879 , 'END-');
880 END IF;
881
883 WHEN OKC_API.G_EXCEPTION_ERROR THEN
884
885 IF get_disb_streams_csr%ISOPEN THEN
886 CLOSE get_disb_streams_csr;
887 END IF;
888
889 IF get_disb_streams_csr2%ISOPEN THEN
890 CLOSE get_disb_streams_csr2;
891 END IF;
892
893 IF get_next_trx_val_csr%ISOPEN THEN
894 CLOSE get_next_trx_val_csr;
895 END IF;
896
897 --message logging
898 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
899 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
900 ,'OKL_AM_SECURITIZATION_PVT.do_disb_adjustments_loan'
901 , 'Handled exception occured');
902 END IF;
903 x_return_status := OKC_API.HANDLE_EXCEPTIONS
904 (
905 l_api_name,
906 G_PKG_NAME,
907 'OKC_API.G_RET_STS_ERROR',
908 x_msg_count,
909 x_msg_data,
910 '_PVT'
911 );
912
913 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
914
915 IF get_disb_streams_csr%ISOPEN THEN
916 CLOSE get_disb_streams_csr;
917 END IF;
918
919 IF get_disb_streams_csr2%ISOPEN THEN
920 CLOSE get_disb_streams_csr2;
921 END IF;
922
923 IF get_next_trx_val_csr%ISOPEN THEN
924 CLOSE get_next_trx_val_csr;
925 END IF;
926
927 --message logging
928 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
929 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
930 ,'OKL_AM_SECURITIZATION_PVT.do_disb_adjustments_loan'
931 , 'Unexpected exception occured');
932 END IF;
933 x_return_status := OKC_API.HANDLE_EXCEPTIONS
934 (
935 l_api_name,
936 G_PKG_NAME,
937 'OKC_API.G_RET_STS_UNEXP_ERROR',
938 x_msg_count,
939 x_msg_data,
940 '_PVT'
941 );
942
943 WHEN OTHERS THEN
944
945 IF get_disb_streams_csr%ISOPEN THEN
946 CLOSE get_disb_streams_csr;
947 END IF;
948
949 IF get_disb_streams_csr2%ISOPEN THEN
950 CLOSE get_disb_streams_csr2;
951 END IF;
952
953 IF get_next_trx_val_csr%ISOPEN THEN
954 CLOSE get_next_trx_val_csr;
955 END IF;
956
957 --message logging
958 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
959 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
960 ,'OKL_AM_SECURITIZATION_PVT.do_disb_adjustments_loan'
961 , 'When others exception occured');
962 END IF;
963 OKL_API.set_message(p_app_name => g_app_name,
964 p_msg_name => g_unexpected_error,
965 p_token1 => g_sqlcode_token,
966 p_token1_value => sqlcode,
967 p_token2 => g_sqlerrm_token,
968 p_token2_value => sqlerrm);
969
970 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
971
972 END;
973 /* sosharma end changes*/
974
975 /*========================================================================
976 | PUBLIC PROCEDURE PROCESS_SECURITIZED_STREAMS
977 |
978 | DESCRIPTION
979 | Main procedure, determines if securitized items existif so,
980 | disbursements are created.
981 |
982 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
983 | Called externally from Termination Quote Acceptance workflow (OKLAMPPT).
984 | The associated workflow PACKAGE.procedure name is,
985 | OKL_AM_QUOTES_WF.chk_securitization.
986 |
987 | Called externally from Asset Dsiposition PACKAGE.procedure name is,
988 | OKL_AM_ASSET_DISPOSE_PVT.dispose_asset.
989 |
990 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
991 |
992 | PARAMETERS
993 | p_api_version IN Standard in parameter
994 | p_init_msg_list IN Standard in parameter
995 | x_return_status OUT Standard out parameter
996 | x_msg_count OUT Standard out parameter
997 | x_msg_data OUT Standard out parameter
998 | p_quote_id IN Termination Quote Identifier when called from
999 | termincation quote acceptance.
1000 | p_kle_id IN Asset Line identifier pased when called from
1001 | asset disposition.
1002 | p_khr_id IN Contract Header identifier passed when called
1003 | from asset disposition
1004 | p_sale_price IN Disposition Amount passed when called from
1005 | asset disposition.
1006 | p_call_origin IN Used internally to identify where the has been
1007 | made from.
1008 |
1009 | KNOWN ISSUES
1010 |
1011 | NOTES
1012 |
1016 | 05-Nov-2003 MDokal Changed x_return_status to l_return_status
1013 | MODIFICATION HISTORY
1014 | Date Author Description of Changes
1015 | 09-OCT-2003 MDokal Created.
1017 | 13-NOV-2003 MDokal Bug #3247596 Ensure l_asset_tbl.COUNT > 0.
1018 | 24-Sep-2004 rmunjulu 3910833 Added code to pass investor id to
1019 | disburse rent and disburse rv
1020 | 06-Oct-2004 rmunjulu EDAT Added Parameters to get transaction
1021 | date and effective date and do processing
1022 | based on those
1023 | 18-Oct-2004 rmunjulu EDAT Added code to pass quote_id to formula
1024 | 04-Nov-2004 PAGARG 3954752 changed to get kle_ids from AMCFIA if
1025 | contract_obligation not found
1026 | 24-Nov-2004 rmunjulu EDAT Modified to initialize and pass correct
1027 | dates to calling APIs
1028 | 11-Jan-2005 PAGARG 3948473 Set the flag if kle_id is null in
1029 | get_qte_asset_details_csr and check the flag
1030 | to populate asset table with AMCFIA quote line
1031 | 12-Jan-2005 PAGARG Bug 3954752 Set the flag and initialise the
1032 | counter also if kle_id is null in
1033 | get_qte_asset_details_csr. So that
1034 | l_asset_table is populated fresh.
1035 | 07-Dec-2005 gboomina Bug 4775555 - INVESTOR_DISB_ADJUSTSMENTS
1036 | 17-Jan-2008 sosharma Modifications to include fixed loans in
1037 | Investor Agreement
1038 *=======================================================================*/
1039 PROCEDURE process_securitized_streams(
1040 p_api_version IN NUMBER,
1041 p_init_msg_list IN VARCHAR2,
1042 x_return_status OUT NOCOPY VARCHAR2,
1043 x_msg_count OUT NOCOPY NUMBER,
1044 x_msg_data OUT NOCOPY VARCHAR2,
1045 p_quote_id IN NUMBER,
1046 p_kle_id IN NUMBER,
1047 p_khr_id IN NUMBER,
1048 p_sale_price IN NUMBER,
1049 p_effective_date IN DATE DEFAULT NULL, -- rmunjulu EDAT
1050 p_transaction_date IN DATE DEFAULT NULL, -- rmunjulu EDAT
1051 p_call_origin IN VARCHAR2) IS
1052
1053 /*-----------------------------------------------------------------------+
1054 | Cursor Declarations |
1055 +-----------------------------------------------------------------------*/
1056
1057 -- rmunjulu EDAT 24-Nov-04
1058 CURSOR get_khr_id_csr(c_quote_id NUMBER) IS
1059 SELECT KHR_ID,
1060 NVL(PARTIAL_YN, 'N') PARTIAL_YN,
1061 date_effective_from, -- rmunjulu EDAT 24-Nov-04
1062 date_accepted -- rmunjulu EDAT 24-Nov-04
1063 FROM OKL_TRX_QUOTES_V
1064 WHERE ID = c_quote_id;
1065
1066 CURSOR get_rv_khr_id_csr(c_kle_id NUMBER) IS
1067 SELECT CHR_ID
1068 FROM OKC_K_LINES_B
1069 WHERE ID = c_kle_id;
1070
1071 CURSOR get_qte_asset_details_csr(c_quote_id NUMBER) IS
1072 SELECT KLE_ID, -- STY_ID,
1073 SUM(AMOUNT) AMOUNT
1074 --, QLT_CODE
1075 FROM OKL_TXL_QUOTE_LINES_B
1076 WHERE QTE_ID = c_quote_id
1077 AND QLT_CODE IN ('AMBCOC','AMCTUR','AMCTOC') -- qualify as CO lines
1078 GROUP BY KLE_ID, STY_ID, QLT_CODE
1079 ORDER BY KLE_ID;
1080
1081 CURSOR get_rv_streams_csr(c_khr_id IN NUMBER,
1082 c_kle_id IN NUMBER) IS
1083 SELECT STM.id
1084 FROM OKL_STREAMS_V STM,
1085 OKL_STRM_TYPE_B STY
1086 WHERE STM.khr_id = c_khr_id
1087 AND STM.kle_id = c_kle_id
1088 AND STM.say_code = 'CURR'
1089 AND STM.STY_ID = STY.ID
1090 AND stream_type_subclass = 'RESIDUAL';
1091
1092 -- 04 Nov 2004 PAGARG Bug# 3954752
1093 -- get the quoted assets
1094 CURSOR get_qte_assets_csr(c_quote_id NUMBER) IS
1095 SELECT KLE_ID,
1096 0 AMOUNT
1097 FROM OKL_TXL_QUOTE_LINES_B
1098 WHERE QTE_ID = c_quote_id
1099 AND QLT_CODE IN ('AMCFIA');
1100
1101 -- Bug# 7009075 - Added - Start
1102 -- Cursor to fetch the latest of buy back dates on a contract
1103 CURSOR get_max_buy_back_date(p_khr_id OKC_K_HEADERS_B.ID%TYPE, p_sty_code OKL_POOL_CONTENTS.STY_CODE%TYPE) IS
1104 SELECT TRUNC(MAX(POX.DATE_EFFECTIVE))
1105 FROM OKL_POOL_CONTENTS POC,
1106 OKL_POOL_TRANSACTIONS POX
1107 WHERE POC.KHR_ID = p_khr_id
1108 AND POC.STY_CODE = p_sty_code
1109 AND POX.POL_ID = POC.POL_ID
1110 AND POX.TRANSACTION_NUMBER = POC.TRANSACTION_NUMBER_IN
1111 AND POX.TRANSACTION_REASON = 'BUY_BACK';
1112
1113 -- Cursor to fetch the active pool contents for a contract line. Cursor
1114 -- fetches the pool content that is latest first
1115 CURSOR get_active_poc(p_khr_id OKC_K_HEADERS_B.ID%TYPE
1116 , p_kle_id OKC_K_LINES_B.ID%TYPE
1117 , p_sty_code OKL_POOL_CONTENTS.STY_CODE%TYPE) IS
1118 SELECT POOL.KHR_ID IA_ID
1119 , POC.STREAMS_TO_DATE
1120 FROM OKL_POOL_CONTENTS POC
1121 , OKL_POOLS POOL
1122 WHERE POOL.ID = POC.POL_ID
1126 AND EXISTS (SELECT '1'
1123 AND POC.KHR_ID = p_khr_id
1124 AND POC.KLE_ID = p_kle_id
1125 AND POC.STATUS_CODE = 'ACTIVE'
1127 FROM OKL_STREAMS STMB,
1128 OKL_STRM_TYPE_B STYB
1129 WHERE stmb.sty_id = styb.id
1130 AND stmb.khr_id = POC.khr_id
1131 AND stmb.kle_id = POC.kle_id
1132 AND stmb.id = POC.stm_id
1133 AND styb.stream_type_subclass =p_sty_code
1134 )
1135 ORDER BY POC.STREAMS_TO_DATE DESC;
1136 -- Bug# 7009075 - Added - End
1137
1138 /* sosharma 17-jan-2008
1139 cursor to fetch the book classification of a contract
1140 Start changes
1141 */
1142 CURSOR get_bok_class_csr(p_khr_id NUMBER) IS
1143 SELECT deal_type
1144 FROM OKL_K_HEADERS khr
1145 WHERE khr.id = p_khr_id;
1146
1147 /* sosharma end changes*/
1148
1149
1150 /*-----------------------------------------------------------------------+
1151 | Local Variable Declarations and initializations |
1152 +-----------------------------------------------------------------------*/
1153
1154 l_api_version CONSTANT NUMBER := 1;
1155 l_api_name CONSTANT VARCHAR2(30) := 'proc_secure_streams';
1156 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1157
1158 l_disb_type VARCHAR2(20);
1159 l_kle_id NUMBER;
1160 l_khr_id NUMBER;
1161 l_loop_counter NUMBER := 0;
1162 l_is_securitized VARCHAR2(1) := OKC_API.G_FALSE;
1163
1164 lp_stmv_tbl OKL_STREAMS_PUB.stmv_tbl_type;
1165 lx_stmv_tbl OKL_STREAMS_PUB.stmv_tbl_type;
1166
1167 l_stm_id NUMBER;
1168 l_stm_id_not_found BOOLEAN := FALSE;
1169 l_asset_tbl asset_tbl_type;
1170
1171 l_inv_agmt_chr_id_tbl OKL_SECURITIZATION_PVT.inv_agmt_chr_id_tbl_type;
1172 l_transaction_reason VARCHAR2(50);
1173 l_partial VARCHAR2(1) := '0'; -- rmunjulu 4398936 initialized this variable
1174
1175 -- rmunjulu 3910833
1176 l_ia_id NUMBER;
1177
1178 -- rmunjulu EDAT
1179 l_sysdate DATE;
1180 l_effective_date DATE;
1181 l_transaction_date DATE;
1182
1183 -- 02 Nov 2004 PAGARG Bug# 3954752
1184 no_cob_exception EXCEPTION;
1185
1186 -- rmunjulu EDAT 24-Nov-04
1187 l_quote_accpt_date DATE;
1188 l_quote_eff_date DATE;
1189 l_effective_from_date DATE;
1190 l_acceptance_date DATE;
1191 l_sys_date DATE;
1192 -- PAGARG 3948473
1193 l_flag BOOLEAN;
1194
1195 --sosharma added to get book classification
1196 l_book_class VARCHAR2(50);
1197
1198 -- Bug# 7009075 - Added
1199 l_max_buy_back_date DATE;
1200 l_stream_to_date DATE;
1201 l_create_inv_khr_oblig BOOLEAN DEFAULT FALSE;
1202 l_temp_ia_id OKC_K_HEADERS_B.ID%TYPE;
1203
1204 BEGIN
1205
1206 --message logging
1207 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1208 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE
1209 ,'OKL_AM_SECURITIZATION_PVT.process_securitized_streams'
1210 ,'Begin (+)');
1211 END IF;
1212
1213 --message logging
1214 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1215 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1216 ,'OKL_AM_SECURITIZATION_PVT.process_securitized_streams'
1217 ,'calling Okl_Api.START_ACTIVITY');
1218 END IF;
1219
1220 -- Check API version, initialize message list and create savepoint.
1221 l_return_status := Okl_Api.START_ACTIVITY(
1222 p_api_name => l_api_name,
1223 p_pkg_name => G_PKG_NAME,
1224 p_init_msg_list => p_init_msg_list,
1225 l_api_version => l_api_version,
1226 p_api_version => p_api_version,
1227 p_api_type => G_API_TYPE,
1228 x_return_status => l_return_status);
1229
1230 --message logging
1231 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1232 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1233 ,'OKL_AM_SECURITIZATION_PVT.process_securitized_streams'
1234 ,'returning from Okl_Api.START_ACTIVITY, status is '
1235 ||l_return_status);
1236 END IF;
1237
1238 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1239 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1240 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1241 RAISE OKL_API.G_EXCEPTION_ERROR;
1242 END IF;
1243
1244 SELECT sysdate INTO l_sysdate FROM dual;
1245
1246 -- Check which parameters have been passed to determine
1247 -- the origin of the call
1248 -- rmunjulu 24-Nov-04 Changed OR to AND in the below IF
1249 IF (p_quote_id IS NOT NULL) AND (p_quote_id <> OKL_API.G_MISS_NUM) THEN
1250
1251 -- rmunjulu EDAT
1252 -- set the operands for formula engine with quote_id
1253 g_add_params(1).name := 'quote_id';
1254 g_add_params(1).value := to_char(p_quote_id);
1255
1256 l_disb_type := 'RENT';
1257
1261 IF get_khr_id_csr%notfound THEN
1258 -- rmunjulu bug 4398936 - START
1259 OPEN get_khr_id_csr(p_quote_id);
1260 FETCH get_khr_id_csr INTO l_khr_id, l_partial, l_effective_from_date, l_acceptance_date;
1262 CLOSE get_khr_id_csr;
1263 --message logging
1264 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1265 FND_LOG.STRING(FND_LOG.LEVEL_ERROR
1266 ,'OKL_AM_SECURITIZATION_PVT.process_securitized_streams'
1267 ,'cursor get_khr_id_csr returned no values');
1268 END IF;
1269
1270 OKL_API.set_message(p_app_name => G_APP_NAME,
1271 p_msg_name => G_INVALID_VALUE1,
1272 p_token1 => 'COL_NAME',
1273 p_token1_value => 'Quote Id');
1274 x_return_status := G_RET_STS_ERROR;
1275 RAISE OKL_API.G_EXCEPTION_ERROR;
1276 END IF;
1277 CLOSE get_khr_id_csr;
1278
1279 /* sosharma 17-01-2008
1280 modifications to include loans in Investor agreement
1281 Based on the deal type variable l_disb_type is set to loan
1282 Start Changes
1283 */
1284 OPEN get_bok_class_csr(l_khr_id);
1285 FETCH get_bok_class_csr INTO l_book_class;
1286 IF get_bok_class_csr%NOTFOUND THEN
1287 CLOSE get_bok_class_csr;
1288 x_return_status := G_RET_STS_ERROR;
1289 RAISE OKL_API.G_EXCEPTION_ERROR;
1290 END IF;
1291 CLOSE get_bok_class_csr;
1292
1293 IF l_book_class = 'LOAN' THEN
1294 l_disb_type := 'LOAN_PAYMENT';
1295 END IF;
1296
1297 /* sosharma end changes*/
1298 -- rmunjulu 4398936 - END
1299 --gboomina Bug 4775555 INVESTOR_DISB_ADJUSTMENTS - Start
1300 IF NVL(l_partial,'N') = 'Y' THEN
1301 -- need to check if no more assets
1302 l_partial := OKL_AM_LEASE_LOAN_TRMNT_PVT.check_true_partial_quote(
1303 p_quote_id => p_quote_id,
1304 p_contract_id => l_khr_id);
1305 END IF;
1306 G_PARTIAL_YN := l_partial; -- rmunjulu set partial yn global flag
1307 G_DATE_EFFECTIVE_FROM := l_effective_from_date; -- rmunjulu set effective from date global flag
1308 --gboomina Bug 4775555 - End
1309
1310 -- rmunjulu 24-Nov-04 Changed OR to AND in the below ELSIF
1311 ELSIF ((p_kle_id IS NOT NULL) AND (p_kle_id <> OKL_API.G_MISS_NUM)) AND
1312 ((p_sale_price IS NOT NULL) AND (p_sale_price <> OKL_API.G_MISS_NUM)) THEN
1313
1314 l_disb_type := 'RESIDUAL';
1315
1316 ELSE
1317
1318 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1319 FND_LOG.STRING(FND_LOG.LEVEL_ERROR
1320 ,'OKL_AM_SECURITIZATION_PVT.process_securitized_streams'
1321 ,'parameter p_quote_id is null or (p_kle_id and p_sale_price)'
1322 ||' are null');
1323 END IF;
1324
1325 OKL_API.set_message(p_app_name => G_APP_NAME,
1326 p_msg_name => G_REQUIRED_VALUE);
1327 x_return_status := G_RET_STS_ERROR;
1328 RAISE OKL_API.G_EXCEPTION_ERROR;
1329 END IF;
1330
1331
1332 -- check if securitized items exist for the underlying contract
1333
1334 IF l_disb_type = 'RENT' OR l_disb_type = 'LOAN_PAYMENT' THEN
1335
1336 -- rmunjulu 4398936 start -- commented out
1337 /*
1338 OPEN get_khr_id_csr(p_quote_id);
1339 FETCH get_khr_id_csr INTO l_khr_id, l_partial, l_effective_from_date, l_acceptance_date;
1340 IF get_khr_id_csr%notfound THEN
1341 CLOSE get_khr_id_csr;
1342 --message logging
1343 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1344 FND_LOG.STRING(FND_LOG.LEVEL_ERROR
1345 ,'OKL_AM_SECURITIZATION_PVT.process_securitized_streams'
1346 ,'cursor get_khr_id_csr returned no values');
1347 END IF;
1348
1349 OKL_API.set_message(p_app_name => G_APP_NAME,
1350 p_msg_name => G_INVALID_VALUE1,
1351 p_token1 => 'COL_NAME',
1352 p_token1_value => 'Quote Id');
1353 x_return_status := G_RET_STS_ERROR;
1354 RAISE OKL_API.G_EXCEPTION_ERROR;
1355 END IF;
1356 CLOSE get_khr_id_csr;
1357 */
1358 -- rmunjulu 4398936 end
1359
1360 -- rmunjulu EDAT 24-Nov-04
1361 l_quote_accpt_date := l_acceptance_date;
1362 l_quote_eff_date := l_effective_from_date;
1363
1364 ELSIF l_disb_type = 'RESIDUAL' THEN
1365 OPEN get_rv_khr_id_csr(p_kle_id);
1366 FETCH get_rv_khr_id_csr INTO l_khr_id;
1367 IF get_rv_khr_id_csr%notfound THEN
1368 CLOSE get_rv_khr_id_csr;
1369 --message logging
1370 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1371 FND_LOG.STRING(FND_LOG.LEVEL_ERROR
1372 ,'OKL_AM_SECURITIZATION_PVT.process_securitized_streams'
1373 ,'cursor get_rv_khr_id_csr returned no values');
1374 END IF;
1375
1379 p_token1_value => 'Asset Id');
1376 OKL_API.set_message(p_app_name => G_APP_NAME,
1377 p_msg_name => G_INVALID_VALUE1,
1378 p_token1 => 'COL_NAME',
1380 x_return_status := G_RET_STS_ERROR;
1381 RAISE OKL_API.G_EXCEPTION_ERROR;
1382 END IF;
1383 CLOSE get_rv_khr_id_csr;
1384
1385
1386 -- rmunjulu EDAT 24-Nov-04
1387 -- If quote exists then effective date is quote effective date else sysdate
1388 IF nvl(okl_am_lease_loan_trmnt_pvt.g_quote_exists,'N') = 'Y' THEN
1389
1390 l_quote_accpt_date := okl_am_lease_loan_trmnt_pvt.g_quote_accept_date;
1391 l_quote_eff_date := okl_am_lease_loan_trmnt_pvt.g_quote_eff_from_date;
1392
1393 ELSE
1394
1395 l_quote_accpt_date := l_sysdate; -- rmunjulu 4398936 changed to l_sysdate
1396 l_quote_eff_date := l_sysdate; -- rmunjulu 4398936 changed to l_sysdate
1397
1398 END IF;
1399 END IF;
1400
1401 IF (l_khr_id IS NOT NULL) THEN
1402 --message logging
1403 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1404 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1405 ,'OKL_AM_SECURITIZATION_PVT.process_securitized_streams'
1406 ,'calling OKL_SECURITIZATION_PVT.check_khr_securitized');
1407 END IF;
1408
1409 -- check if Contract is securitized
1410 OKL_SECURITIZATION_PVT.check_khr_securitized(
1411 p_api_version => p_api_version
1412 ,p_init_msg_list => p_init_msg_list
1413 ,x_return_status => l_return_status
1414 ,x_msg_count => x_msg_count
1415 ,x_msg_data => x_msg_data
1416 ,p_khr_id => l_khr_id
1417 ,p_effective_date => l_quote_eff_date -- rmunjulu EDAT 24-Nov-04
1418 ,p_stream_type_subclass => l_disb_type
1419 ,x_value => l_is_securitized
1420 ,x_inv_agmt_chr_id_tbl => l_inv_agmt_chr_id_tbl );
1421
1422 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1423 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1424 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1425 RAISE OKL_API.G_EXCEPTION_ERROR;
1426 END IF;
1427
1428 -- rmunjulu 3910833
1429 -- get the investor agreement for this contract's RENT or RESIDUAL streams
1430 IF l_inv_agmt_chr_id_tbl.COUNT > 0 THEN
1431
1432 l_ia_id := l_inv_agmt_chr_id_tbl(l_inv_agmt_chr_id_tbl.FIRST).khr_id;
1433
1434 END IF;
1435
1436 --message logging
1437 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1438 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1439 ,'OKL_AM_SECURITIZATION_PVT.process_securitized_streams'
1440 ,'returning from OKL_SECURITIZATION_PVT.check_khr_securitized'
1441 ||', status is '||l_return_status);
1442 END IF;
1443 ELSE
1444 l_is_securitized := OKC_API.G_FALSE; -- not securitized
1445 END IF;
1446
1447 IF l_partial = 'Y' THEN
1448 -- modifiy call origin
1449 l_transaction_reason :=
1450 OKL_SECURITIZATION_PVT.G_TRX_REASON_ASSET_TERMINATION;
1451 ELSE
1452 l_transaction_reason := p_call_origin;
1453 END IF;
1454
1455 --- rmunjulu 4398936 Added the following condition.
1456 g_call_ad_flag := FALSE;
1457 IF (l_partial ='N' OR l_partial = '0') THEN
1458 g_call_ad_flag := TRUE;
1459 END IF;
1460
1461 -- Bug# 7009075 - Moved - Start
1462 -- For RENT, we need to see if contract has ever been securitized -check not to be specific to
1463 -- a date. Because Investor Contract Obligation streams are to be created only if the
1464 -- contract still has securitized streams on an investor pool. This means after all buy back
1465 -- transactions, if there is an investor pool securitizing the contract, then we allow
1466 -- creation of Investor Contract Obligation streams. Hence for RENT alone we donot directly
1467 -- check using 'l_is_securitized' flag
1468 IF l_inv_agmt_chr_id_tbl.COUNT > 0 THEN
1469 IF l_disb_type = 'RENT' THEN
1470 -- Find asset details from the quote id supplied and store in a table
1471 -- PAGARG 3948473
1472 l_flag := FALSE;
1473 -- PAGARG 3954752
1474 l_loop_counter := 0;
1475 FOR l_qte_asset_details_rec IN get_qte_asset_details_csr(p_quote_id) LOOP
1476
1477 l_loop_counter := l_loop_counter+1;
1478
1479 IF l_qte_asset_details_rec.kle_id IS NOT NULL THEN
1480 --store the kle_id details
1481 l_asset_tbl(l_loop_counter).p_kle_id
1482 := l_qte_asset_details_rec.kle_id;
1483 -- PAGARG 3948473
1484 ELSE
1485 l_flag := TRUE;
1486 END IF;
1487
1488 l_asset_tbl(l_loop_counter).p_khr_id := l_khr_id;
1489 l_asset_tbl(l_loop_counter).p_amount
1490 := l_qte_asset_details_rec.amount;
1494 -- no contract obligation found, get kle_id from AMCFIA quote lines
1491 END LOOP; -- get_qte_asset_details_csr
1492
1493 -- 04 Nov 2004 PAGARG Bug# 3954752
1495 -- amount will be 0
1496 -- PAGARG 3948473
1497 IF l_asset_tbl.count = 0 OR l_flag = TRUE THEN
1498 -- PAGARG 3954752
1499 l_loop_counter := 0;
1500 l_asset_tbl.DELETE;
1501
1502 -- get all quote assets, amount will be zero in this case
1503 FOR get_qte_assets_rec IN get_qte_assets_csr (p_quote_id) LOOP
1504
1505 l_loop_counter := l_loop_counter+1;
1506
1507 IF get_qte_assets_rec.kle_id IS NOT NULL THEN
1508 --store the kle_id details
1509 l_asset_tbl(l_loop_counter).p_kle_id
1510 := get_qte_assets_rec.kle_id;
1511 END IF;
1512
1513 l_asset_tbl(l_loop_counter).p_khr_id := l_khr_id;
1514 l_asset_tbl(l_loop_counter).p_amount
1515 := get_qte_assets_rec.amount;
1516 END LOOP;
1517 END IF;
1518
1519 -- Bug# 7009075 - Added - Start
1520 l_max_buy_back_date := null;
1521 -- Check if contract was bought back atleast once and get latest of the buy back dates
1522 OPEN get_max_buy_back_date(l_khr_id, 'RENT');
1523 FETCH get_max_buy_back_date INTO l_max_buy_back_date;
1524 CLOSE get_max_buy_back_date;
1525 -- Bug# 7009075 - Added - End
1526
1527 --mdokal Bug #3247596
1528 IF l_asset_tbl.COUNT > 0 THEN
1529 FOR l_loop_counter IN l_asset_tbl.FIRST..l_asset_tbl.LAST
1530 LOOP
1531 IF l_asset_tbl(l_loop_counter).p_amount IS NOT NULL THEN
1532
1533 l_create_inv_khr_oblig := FALSE;
1534 l_stream_to_date := null;
1535 -- Check if contract ever was bought back
1536 IF l_max_buy_back_date IS NULL THEN
1537 -- get Active pool content stream to date for this asset
1538 OPEN get_active_poc(l_khr_id, l_asset_tbl(l_loop_counter).p_kle_id, 'RENT');
1539 FETCH get_active_poc INTO l_temp_ia_id, l_stream_to_date;
1540 -- if contract has active RENT pool contents for this asset,
1541 -- create investor contract obligation streams
1542 IF get_active_poc%FOUND THEN
1543 l_create_inv_khr_oblig := TRUE;
1544 END IF;
1545 CLOSE get_active_poc;
1546 ELSE -- implies contract was bought back atleast once
1547 -- get the latest Active pool content stream to date for this asset
1548 OPEN get_active_poc(l_khr_id, l_asset_tbl(l_loop_counter).p_kle_id, 'RENT');
1549 FETCH get_active_poc INTO l_temp_ia_id, l_stream_to_date;
1550 CLOSE get_active_poc;
1551 -- if there are active RENT pool contents beyond the last buy back date,
1552 -- create investor contract obligation streams
1553 IF l_max_buy_back_date < NVL(TRUNC(l_stream_to_date),l_max_buy_back_date) THEN
1554 l_create_inv_khr_oblig := TRUE;
1555 END IF;
1556 END IF; -- end of l_max_buy_back_date NULL check
1557
1558 -- Create investor contract obligation streams
1559 IF l_create_inv_khr_oblig THEN
1560 create_inv_khr_obligation(
1561 p_api_version => p_api_version
1562 ,p_init_msg_list => p_init_msg_list
1563 ,x_return_status => l_return_status
1564 ,x_msg_count => x_msg_count
1565 ,x_msg_data => x_msg_data
1566 ,p_ia_id => l_temp_ia_id -- rmunjulu 3910833
1567 ,p_effective_date => l_quote_eff_date -- rmunjulu EDAT 24-Nov-04
1568 ,p_transaction_date => l_quote_accpt_date -- rmunjulu EDAT 24-Nov-04
1569 ,p_asset_rec => l_asset_tbl(l_loop_counter));
1570 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1571 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1572 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1573 RAISE OKL_API.G_EXCEPTION_ERROR;
1574 END IF;
1575 END IF;
1576
1577 -- Do disbursement adjustments if contract is securitized as of termination date
1578 IF l_is_securitized = OKC_API.G_TRUE THEN
1579 do_disb_adjustments (
1580 p_api_version => p_api_version,
1581 p_init_msg_list => OKL_API.G_FALSE,
1582 x_msg_count => x_msg_count,
1583 x_msg_data => x_msg_data,
1584 p_ia_id => l_ia_id,
1585 p_khr_id => l_asset_tbl(l_loop_counter).p_khr_id,
1586 p_kle_id => l_asset_tbl(l_loop_counter).p_kle_id,
1587 p_partial_yn => G_PARTIAL_YN,
1588 p_quote_eff_date => G_DATE_EFFECTIVE_FROM,
1589 x_return_status => l_return_status);
1590
1591 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1595 END IF;
1592 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1593 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1594 RAISE OKL_API.G_EXCEPTION_ERROR;
1596 END IF; -- end of l_is_securitized check
1597 END IF; -- end of check for amount not null
1598 END LOOP; -- end of for loop over l_asset_tbl
1599 END IF; -- end of check for l_asset_tbl.COUNT > 0
1600 END IF;
1601 END IF; -- end of check for l_inv_agmt_chr_id_tbl.COUNT > 0
1602 -- Bug# 7009075 - Moved - End
1603
1604 IF l_is_securitized = OKC_API.G_TRUE THEN -- this contract is securitized.
1605
1606 IF l_disb_type = 'RESIDUAL' THEN
1607
1608 -- Disburse Investor Residual Stream
1609 disburse_investor_rv( p_api_version => p_api_version
1610 ,p_init_msg_list => p_init_msg_list
1611 ,x_return_status => l_return_status
1612 ,x_msg_count => x_msg_count
1613 ,x_msg_data => x_msg_data
1614 ,p_khr_id => l_khr_id
1615 ,p_kle_id => p_kle_id
1616 ,p_ia_id => l_ia_id -- rmunjulu 3910833
1617 ,p_effective_date => l_quote_eff_date -- rmunjulu EDAT 24-Nov-04
1618 ,p_transaction_date => l_quote_accpt_date -- rmunjulu EDAT 24-Nov-04
1619 ,p_sale_price => p_sale_price);
1620
1621 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1622 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1623 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1624 RAISE OKL_API.G_EXCEPTION_ERROR;
1625 END IF;
1626
1627 -- Historize Residual Value Stream
1628 -- set the tbl type for streams pub
1629 l_loop_counter := 1;
1630
1631 FOR rv_streams_rec IN get_rv_streams_csr(p_khr_id, p_kle_id) LOOP
1632
1633 lp_stmv_tbl(l_loop_counter).khr_id := p_khr_id;
1634 lp_stmv_tbl(l_loop_counter).khr_id := p_khr_id;
1635 lp_stmv_tbl(l_loop_counter).active_yn := 'N';
1636 lp_stmv_tbl(l_loop_counter).id := rv_streams_rec.id;
1637 lp_stmv_tbl(l_loop_counter).say_code := 'HIST';
1638 lp_stmv_tbl(l_loop_counter).date_history := SYSDATE;
1639
1640 l_loop_counter := l_loop_counter + 1;
1641
1642 END LOOP; -- get_rv_streams_csr
1643 --message logging
1644 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1645 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1646 ,'OKL_AM_SECURITIZATION_PVT.process_securitized_streams'
1647 ,'calling OKL_STREAMS_PUB.update_streams');
1648 END IF;
1649
1650 -- historize rv streams
1651 OKL_STREAMS_PUB.update_streams(
1652 p_api_version => p_api_version,
1653 p_init_msg_list => OKC_API.G_FALSE,
1654 x_return_status => l_return_status,
1655 x_msg_count => x_msg_count,
1656 x_msg_data => x_msg_data,
1657 p_stmv_tbl => lp_stmv_tbl,
1658 x_stmv_tbl => lx_stmv_tbl);
1659
1660 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1661 OKL_API.set_message( p_app_name => G_APP_NAME,
1662 p_msg_name => 'OKL_AM_ERR_UPD_STREAMS');
1663 END IF;
1664 --message logging
1665 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1666 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1667 ,'OKL_AM_SECURITIZATION_PVT.process_securitized_streams'
1668 ,'returning from OKL_STREAMS_PUB.update_streams'
1669 ||', status is '||l_return_status);
1670 END IF;
1671
1672 /* sosharma 17-01-2008
1673 modifications to include loans in Investor agreement
1674 Start Changes
1675 */
1676
1677 ELSIF l_disb_type = 'LOAN_PAYMENT' THEN
1678
1679 -- Find asset details from the quote id supplied and store in a table
1680 l_flag := FALSE;
1681 l_loop_counter := 0;
1682 FOR l_qte_asset_details_rec IN get_qte_asset_details_csr(p_quote_id) LOOP
1683
1684 l_loop_counter := l_loop_counter+1;
1685
1686 IF l_qte_asset_details_rec.kle_id IS NOT NULL THEN
1687 --store the kle_id details
1688 l_asset_tbl(l_loop_counter).p_kle_id
1689 := l_qte_asset_details_rec.kle_id;
1690 ELSE
1691 l_flag := TRUE;
1692 END IF;
1693
1694 l_asset_tbl(l_loop_counter).p_khr_id := l_khr_id;
1695 l_asset_tbl(l_loop_counter).p_amount
1696 := l_qte_asset_details_rec.amount;
1697 END LOOP; -- get_qte_asset_details_csr
1698
1702 l_loop_counter := 0;
1699 -- no contract obligation found, get kle_id from AMCFIA quote lines
1700 -- amount will be 0
1701 IF l_asset_tbl.count = 0 OR l_flag = TRUE THEN
1703 l_asset_tbl.DELETE;
1704
1705 -- get all quote assets, amount will be zero in this case
1706 FOR get_qte_assets_rec IN get_qte_assets_csr (p_quote_id) LOOP
1707
1708 l_loop_counter := l_loop_counter+1;
1709
1710 IF get_qte_assets_rec.kle_id IS NOT NULL THEN
1711 --store the kle_id details
1712 l_asset_tbl(l_loop_counter).p_kle_id
1713 := get_qte_assets_rec.kle_id;
1714 END IF;
1715
1716 l_asset_tbl(l_loop_counter).p_khr_id := l_khr_id;
1717 l_asset_tbl(l_loop_counter).p_amount
1718 := get_qte_assets_rec.amount;
1719 END LOOP;
1720 END IF;
1721
1722 IF l_asset_tbl.COUNT > 0 THEN
1723 -- Disburse Investor Loan Payment Stream
1724 disburse_investor_loan_payment( p_api_version => p_api_version
1725 ,p_init_msg_list => p_init_msg_list
1726 ,x_return_status => l_return_status
1727 ,x_msg_count => x_msg_count
1728 ,x_msg_data => x_msg_data
1729 ,p_ia_id => l_ia_id
1730 ,p_effective_date => l_quote_eff_date
1731 ,p_transaction_date => l_quote_accpt_date
1732 ,p_asset_tbl => l_asset_tbl);
1733
1734 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1735 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1736 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1737 RAISE OKL_API.G_EXCEPTION_ERROR;
1738 END IF;
1739 END IF;
1740
1741 -- END IF;
1742
1743 END IF;
1744
1745 --mdokal Bug #3247596
1746 IF l_disb_type = 'RESIDUAL' OR l_asset_tbl.COUNT > 0 THEN
1747
1748 -- Create Pool Transaction Record
1749 create_pool_transaction(
1750 p_api_version => p_api_version
1751 ,p_init_msg_list => p_init_msg_list
1752 ,x_return_status => l_return_status
1753 ,x_msg_count => x_msg_count
1754 ,x_msg_data => x_msg_data
1755 ,p_asset_tbl => l_asset_tbl
1756 ,p_transaction_reason => l_transaction_reason
1757 ,p_khr_id => l_khr_id
1758 ,p_kle_id => p_kle_id
1759 ,p_effective_date => l_quote_eff_date -- rmunjulu EDAT 24-Nov-04
1760 ,p_transaction_date => l_quote_accpt_date -- rmunjulu EDAT 24-Nov-04
1761 ,p_disb_type => l_disb_type);
1762
1763 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1764 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1765 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1766 RAISE OKL_API.G_EXCEPTION_ERROR;
1767 END IF;
1768 END IF; -- mdokal Bug #3247596 End
1769 END IF;
1770
1771 --message logging
1772 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1773 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1774 ,'OKL_AM_SECURITIZATION_PVT.process_securitized_streams'
1775 ,'calling Okl_Api.END_ACTIVITY');
1776 END IF;
1777
1778 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count
1779 ,x_msg_data => x_msg_data);
1780
1781 x_return_status := l_return_status;
1782 --message logging
1783 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1784 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1785 ,'OKL_AM_SECURITIZATION_PVT.process_securitized_streams'
1786 ,' returning from Okl_Api.END_ACTIVITY, status is ||'
1787 ||x_return_status);
1788 END IF;
1789 --message logging
1790 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1791 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE
1792 ,'OKL_AM_SECURITIZATION_PVT.process_securitized_streams'
1793 , 'End (-)');
1794 END IF;
1795
1796 EXCEPTION
1797 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1798
1799 IF get_khr_id_csr%ISOPEN THEN
1800 CLOSE get_khr_id_csr;
1801 END IF;
1802
1803 IF get_rv_khr_id_csr%ISOPEN THEN
1804 CLOSE get_rv_khr_id_csr;
1805 END IF;
1806
1807 IF get_qte_asset_details_csr%ISOPEN THEN
1808 CLOSE get_qte_asset_details_csr;
1809 END IF;
1810
1811 IF get_rv_streams_csr%ISOPEN THEN
1812 CLOSE get_rv_streams_csr;
1813 END IF;
1814
1815 --message logging
1816 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1817 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
1818 ,'OKL_AM_SECURITIZATION_PVT.process_securitized_streams'
1819 , 'Handled exception occured');
1823 l_api_name,
1820 END IF;
1821 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1822 (
1824 G_PKG_NAME,
1825 'OKC_API.G_RET_STS_ERROR',
1826 x_msg_count,
1827 x_msg_data,
1828 '_PVT'
1829 );
1830
1831 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1832
1833 IF get_khr_id_csr%ISOPEN THEN
1834 CLOSE get_khr_id_csr;
1835 END IF;
1836
1837 IF get_rv_khr_id_csr%ISOPEN THEN
1838 CLOSE get_rv_khr_id_csr;
1839 END IF;
1840
1841 IF get_qte_asset_details_csr%ISOPEN THEN
1842 CLOSE get_qte_asset_details_csr;
1843 END IF;
1844
1845 IF get_rv_streams_csr%ISOPEN THEN
1846 CLOSE get_rv_streams_csr;
1847 END IF;
1848
1849 --message logging
1850 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1851 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
1852 ,'OKL_AM_SECURITIZATION_PVT.process_securitized_streams'
1853 , 'Unexpected exception occured');
1854 END IF;
1855 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1856 (
1857 l_api_name,
1858 G_PKG_NAME,
1859 'OKC_API.G_RET_STS_UNEXP_ERROR',
1860 x_msg_count,
1861 x_msg_data,
1862 '_PVT'
1863 );
1864
1865 WHEN OTHERS THEN
1866
1867
1868 IF get_khr_id_csr%ISOPEN THEN
1869 CLOSE get_khr_id_csr;
1870 END IF;
1871
1872 IF get_rv_khr_id_csr%ISOPEN THEN
1873 CLOSE get_rv_khr_id_csr;
1874 END IF;
1875
1876 IF get_qte_asset_details_csr%ISOPEN THEN
1877 CLOSE get_qte_asset_details_csr;
1878 END IF;
1879
1880 IF get_rv_streams_csr%ISOPEN THEN
1881 CLOSE get_rv_streams_csr;
1882 END IF;
1883
1884 --message logging
1885 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1886 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
1887 ,'OKL_AM_SECURITIZATION_PVT.process_securitized_streams'
1888 , 'When others exception occured');
1889 END IF;
1890 OKL_API.set_message(p_app_name => g_app_name,
1891 p_msg_name => g_unexpected_error,
1892 p_token1 => g_sqlcode_token,
1893 p_token1_value => sqlcode,
1894 p_token2 => g_sqlerrm_token,
1895 p_token2_value => sqlerrm);
1896
1897 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1898
1899 END process_securitized_streams;
1900
1901 /*========================================================================
1902 | PUBLIC PROCEDURE DISBURSE_INVESTOR_RENT
1903 |
1904 | DESCRIPTION
1905 | Processes invester disbursement for rent.
1906 |
1907 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1908 | Called from PROCESS_SECURITIZED_STREAMS
1909 |
1910 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1911 |
1912 | PARAMETERS
1913 | p_api_version IN Standard in parameter
1914 | p_init_msg_list IN Standard in parameter
1915 | x_return_status OUT Standard out parameter
1916 | x_msg_count OUT Standard out parameter
1917 | x_msg_data OUT Standard out parameter
1918 | p_asset_tbl IN Table of asset(s) records for processing
1919 |
1920 | KNOWN ISSUES
1921 |
1922 | NOTES
1923 |
1924 | MODIFICATION HISTORY
1925 | Date Author Description of Changes
1926 | 09-OCT-2003 MDokal Created.
1927 | 03-Nov-2003 MDokal Changed INVESTOR RENT PAYABLE to
1928 | INVESTOR CONTRACT OBLIGATION PAYABLE
1929 | 24-Sep-2004 rmunjulu 3910833 Added code to get ia_id and
1930 | set for disbursement stream
1931 | 06-Oct-2004 rmunjulu EDAT Added Parameters to get transaction
1932 | date and effective date and do processing
1933 | based on those
1934 | 06-Dec-2004 PAGARG Pass investor agreement id to obtain
1935 | formula value.
1936 | 07-Dec-2005 gboomina Bug 4775555 INVESTOR_DISB_ADJUSTMENTS
1937 *=======================================================================*/
1938 PROCEDURE disburse_investor_rent(
1939 p_api_version IN NUMBER,
1940 p_init_msg_list IN VARCHAR2,
1941 x_return_status OUT NOCOPY VARCHAR2,
1942 x_msg_count OUT NOCOPY NUMBER,
1943 x_msg_data OUT NOCOPY VARCHAR2,
1944 p_ia_id IN NUMBER, -- rmunjulu 3910833
1945 p_effective_date IN DATE DEFAULT NULL, -- rmunjulu EDAT
1946 p_transaction_date IN DATE DEFAULT NULL, -- rmunjulu EDAT
1947 p_asset_tbl IN asset_tbl_type) IS
1948
1949 /*-----------------------------------------------------------------------+
1950 | Cursor Declarations |
1954 SELECT okl_sif_seq.nextval
1951 +-----------------------------------------------------------------------*/
1952
1953 CURSOR get_next_trx_val_csr IS
1955 FROM dual;
1956
1957 /*-----------------------------------------------------------------------+
1958 | Local Variable Declarations and initializations |
1959 +-----------------------------------------------------------------------*/
1960
1961 l_loop_counter NUMBER;
1962
1963 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1964
1965 l_formula_amount NUMBER := 0;
1966 l_formula_name CONSTANT VARCHAR2(40) := 'INVESTOR_RENT_DISBURSEMENT';
1967 l_rent_sty CONSTANT VARCHAR2(50) := 'INVESTOR_CNTRCT_OBLIGATION_PAY'; -- SMODUGA 15-Oct-04 Bug 3925469
1968 l_disbursement_amount NUMBER;
1969
1970 l_stmv_rec Okl_Stm_Pvt.stmv_rec_type;
1971 l_selv_tbl Okl_Sel_Pvt.selv_tbl_type;
1972 x_stmv_rec Okl_Stm_Pvt.stmv_rec_type;
1973 x_selv_tbl Okl_Sel_Pvt.selv_tbl_type;
1974
1975 l_sty_id NUMBER;
1976 l_trx_id NUMBER;
1977 --06-Dec-2004 PAGARG Bug# 3948473 passing investor agreement id as part of
1978 --additonal parameter to obtain formula value.
1979 l_flag BOOLEAN;
1980 BEGIN
1981 --message logging
1982 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1983 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE
1984 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_rent'
1985 ,'Begin (+)');
1986 END IF;
1987
1988 --06-Dec-2004 PAGARG Bug# 3948473 passing investor agreement id as part of
1989 --additonal parameter to obtain formula value.
1990 l_flag := FALSE;
1991 IF g_add_params.COUNT > 0
1992 THEN
1993 FOR l_loop_counter IN g_add_params.FIRST..g_add_params.LAST
1994 LOOP
1995 IF g_add_params(l_loop_counter).name = 'inv_agr_id'
1996 THEN
1997 l_flag := TRUE;
1998 g_add_params(l_loop_counter).value := p_ia_id;
1999 END IF;
2000 END LOOP;
2001 END IF;
2002 IF l_flag = FALSE
2003 THEN
2004 l_loop_counter := NVL(g_add_params.LAST, 0) + 1;
2005 g_add_params(l_loop_counter).name := 'inv_agr_id';
2006 g_add_params(l_loop_counter).value := p_ia_id;
2007 END IF;
2008
2009 IF p_asset_tbl.COUNT > 0 THEN
2010
2011 -- smoduga +++++++++ User Defined Streams -- start ++++++++++++++++
2012 OKL_STREAMS_UTIL.get_primary_stream_type(p_ia_id,
2013 l_rent_sty,
2014 l_return_status,
2015 l_sty_id);
2016
2017 IF l_sty_id IS NULL OR l_sty_id = OKL_API.G_MISS_NUM THEN
2018 --message logging
2019 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2020 FND_LOG.STRING(FND_LOG.LEVEL_ERROR
2021 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_rent'
2022 ,'OKL_STREAMS_UTIL.get_primary_stream_type returned no values');
2023 END IF;
2024
2025 -- smoduga +++++++++ User Defined Streams -- end ++++++++++++++++
2026 OKL_API.set_message(p_app_name => G_APP_NAME,
2027 p_msg_name => G_INVALID_VALUE1,
2028 p_token1 => 'COL_NAME',
2029 p_token1_value => 'STY_ID');
2030 END IF;
2031 END IF;
2032
2033 FOR l_loop_counter IN p_asset_tbl.FIRST..p_asset_tbl.LAST
2034 LOOP
2035 IF p_asset_tbl(l_loop_counter).p_amount IS NOT NULL
2036 THEN
2037 --message logging
2038 IF (FND_LOG.LEVEL_STATEMENT >=
2039 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2040 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2041 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_rent'
2042 ,'calling OKL_AM_UTIL_PVT.get_formula_value with'
2043 ||' formula name '||l_formula_name);
2044 END IF;
2045
2046 OKL_AM_UTIL_PVT.get_formula_value(
2047 p_formula_name => l_formula_name
2048 ,p_chr_id => p_asset_tbl(l_loop_counter).p_khr_id
2049 ,p_cle_id => p_asset_tbl(l_loop_counter).p_kle_id
2050 ,p_additional_parameters => g_add_params -- rmunjulu EDAT
2051 ,x_formula_value => l_formula_amount
2052 ,x_return_status => l_return_status);
2053
2054 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2055 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2056 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2057 RAISE OKL_API.G_EXCEPTION_ERROR;
2058 END IF;
2059 --message logging
2060 IF (FND_LOG.LEVEL_STATEMENT >=
2061 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2062 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2063 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_rent'
2064 ,'returning from OKL_AM_UTIL_PVT.get_formula_value'
2065 ||', status is '
2066 ||l_return_status
2070
2067 ||' and l_formula_amount is '
2068 ||l_formula_amount);
2069 END IF;
2071 OPEN get_next_trx_val_csr;
2072 FETCH get_next_trx_val_csr INTO l_trx_id;
2073 CLOSE get_next_trx_val_csr;
2074
2075 l_disbursement_amount :=
2076 (p_asset_tbl(l_loop_counter).p_amount * l_formula_amount);
2077 -- stream header parameters
2078 l_stmv_rec.khr_id := p_asset_tbl(l_loop_counter).p_khr_id;
2079 l_stmv_rec.kle_id := p_asset_tbl(l_loop_counter).p_kle_id;
2080 l_stmv_rec.sty_id := l_sty_id;
2081 l_stmv_rec.SGN_CODE := 'MANL';
2082 l_stmv_rec.SAY_CODE := 'CURR';
2083 l_stmv_rec.TRANSACTION_NUMBER := l_trx_id;
2084 l_stmv_rec.ACTIVE_YN := 'Y';
2085
2086 -- rmunjulu 3910833 added code to set source_id and source_table
2087 l_stmv_rec.source_id := p_ia_id;
2088 l_stmv_rec.source_table := G_SOURCE_TABLE;
2089 l_stmv_rec.date_current := SYSDATE;
2090
2091 -- stream element parameters
2092 l_selv_tbl(1).stream_element_date := p_transaction_date; -- rmunjulu EDAT
2093 -- 04 Nov 2004 PAGARG Bug# 3954752
2094 l_selv_tbl(1).amount := l_disbursement_amount;
2095 l_selv_tbl(1).ACCRUED_YN := 'N';
2096 l_selv_tbl(1).SE_LINE_NUMBER := 1;
2097
2098 -- create disbursement record
2099
2100 --message logging
2101 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2102 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2103 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_rent'
2104 ,'calling OKL_STREAMS_PUB.create_streams ');
2105 END IF;
2106
2107 OKL_STREAMS_PUB.create_streams(
2108 p_api_version => p_api_version
2109 ,p_init_msg_list => p_init_msg_list
2110 ,x_return_status => l_return_status
2111 ,x_msg_count => x_msg_count
2112 ,x_msg_data => x_msg_data
2113 ,p_stmv_rec => l_stmv_rec
2114 ,p_selv_tbl => l_selv_tbl
2115 ,x_stmv_rec => x_stmv_rec
2116 ,x_selv_tbl => x_selv_tbl );
2117
2118 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2119 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2120 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2121 RAISE OKL_API.G_EXCEPTION_ERROR;
2122 END IF;
2123 --message logging
2124 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2125 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2126 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_rent'
2127 ,'returning from OKL_STREAMS_PUB.create_streams,'
2128 ||' status is '||l_return_status);
2129 END IF;
2130 -- gboomina Bug 4775555 - Start
2131 -- INVESTOR_DISB_ADJUSTMENTS
2132 do_disb_adjustments (
2133 p_api_version => p_api_version,
2134 p_init_msg_list => OKL_API.G_FALSE,
2135 x_msg_count => x_msg_count,
2136 x_msg_data => x_msg_data,
2137 p_ia_id => p_ia_id,
2138 p_khr_id => p_asset_tbl(l_loop_counter).p_khr_id,
2139 p_kle_id => p_asset_tbl(l_loop_counter).p_kle_id,
2140 p_partial_yn => G_PARTIAL_YN,
2141 p_quote_eff_date => G_DATE_EFFECTIVE_FROM,
2142 x_return_status => l_return_status);
2143
2144 --message logging
2145 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2146 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2147 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_rent'
2148 ,'returning from do_disb_adjustments,'
2149 ||' status is '||l_return_status);
2150 END IF;
2151
2152 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2153 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2154 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2155 RAISE OKL_API.G_EXCEPTION_ERROR;
2156 END IF;
2157 -- gboomina Bug 4775555 - End
2158 END IF;
2159 END LOOP; -- p_asset_tbl
2160 --message logging
2161 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2162 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE
2163 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_rent'
2164 ,'End (-)');
2165 END IF;
2166
2167 x_return_status := l_return_status;
2168
2169 EXCEPTION
2170 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2171
2172
2173 IF get_next_trx_val_csr%ISOPEN THEN
2177 --message logging
2174 CLOSE get_next_trx_val_csr;
2175 END IF;
2176
2178 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2179 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
2180 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_rent'
2181 , 'Handled exception occured');
2182 END IF;
2183
2184 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2185 (
2186 'proc_secure_streams',
2187 G_PKG_NAME,
2188 'OKC_API.G_RET_STS_ERROR',
2189 x_msg_count,
2190 x_msg_data,
2191 '_PVT'
2192 );
2193
2194 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2195
2196
2197 IF get_next_trx_val_csr%ISOPEN THEN
2198 CLOSE get_next_trx_val_csr;
2199 END IF;
2200
2201 --message logging
2202 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2203 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
2204 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_rent'
2205 , 'Expected exception occured');
2206 END IF;
2207
2208 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2209 (
2210 'proc_secure_streams',
2211 G_PKG_NAME,
2212 'OKC_API.G_RET_STS_UNEXP_ERROR',
2213 x_msg_count,
2214 x_msg_data,
2215 '_PVT'
2216 );
2217
2218 WHEN OTHERS THEN
2219
2220
2221 IF get_next_trx_val_csr%ISOPEN THEN
2222 CLOSE get_next_trx_val_csr;
2223 END IF;
2224
2225 --message logging
2226 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2227 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
2228 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_rent'
2229 , 'When others exception occured');
2230 END IF;
2231
2232 OKL_API.set_message(p_app_name => g_app_name,
2233 p_msg_name => g_unexpected_error,
2234 p_token1 => g_sqlcode_token,
2235 p_token1_value => sqlcode,
2236 p_token2 => g_sqlerrm_token,
2237 p_token2_value => sqlerrm);
2238
2239 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2240
2241
2242 END disburse_investor_rent;
2243
2244
2245 /*========================================================================
2246 | PUBLIC PROCEDURE DISBURSE_INVESTOR_LOAN_PAYMENT
2247 |
2248 | DESCRIPTION
2249 | Processes invester disbursement for loan payment.
2250 |
2251 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2252 | Called from PROCESS_SECURITIZED_STREAMS
2253 |
2254 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2255 |
2256 | PARAMETERS
2257 | p_api_version IN Standard in parameter
2258 | p_init_msg_list IN Standard in parameter
2259 | x_return_status OUT Standard out parameter
2260 | x_msg_count OUT Standard out parameter
2261 | x_msg_data OUT Standard out parameter
2262 | p_asset_tbl IN Table of asset(s) records for processing
2263 |
2264 | KNOWN ISSUES
2265 |
2266 | NOTES
2267 |
2268 | MODIFICATION HISTORY
2269 | Date Author Description of Changes
2270 | 17-Jan-2008 sosharma Created.
2271 |
2272 *=======================================================================*/
2273 PROCEDURE disburse_investor_loan_payment(
2274 p_api_version IN NUMBER,
2275 p_init_msg_list IN VARCHAR2,
2276 x_return_status OUT NOCOPY VARCHAR2,
2277 x_msg_count OUT NOCOPY NUMBER,
2278 x_msg_data OUT NOCOPY VARCHAR2,
2279 p_ia_id IN NUMBER, -- rmunjulu 3910833
2280 p_effective_date IN DATE DEFAULT NULL, -- rmunjulu EDAT
2281 p_transaction_date IN DATE DEFAULT NULL, -- rmunjulu EDAT
2282 p_asset_tbl IN asset_tbl_type) IS
2283
2284 /*-----------------------------------------------------------------------+
2285 | Cursor Declarations |
2286 +-----------------------------------------------------------------------*/
2287
2288 CURSOR get_next_trx_val_csr IS
2289 SELECT okl_sif_seq.nextval
2290 FROM dual;
2291
2292 /*-----------------------------------------------------------------------+
2293 | Local Variable Declarations and initializations |
2294 +-----------------------------------------------------------------------*/
2295
2296 l_loop_counter NUMBER;
2297
2298 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
2299
2300 l_formula_amount NUMBER := 0;
2301 l_formula_name CONSTANT VARCHAR2(40) := 'INVESTOR_LOAN_DISBURSEMENT';
2302 l_rent_sty CONSTANT VARCHAR2(50) := 'INVESTOR_CNTRCT_OBLIGATION_PAY'; -- SMODUGA 15-Oct-04 Bug 3925469
2303 l_disbursement_amount NUMBER;
2304
2305 l_stmv_rec Okl_Stm_Pvt.stmv_rec_type;
2306 l_selv_tbl Okl_Sel_Pvt.selv_tbl_type;
2307 x_stmv_rec Okl_Stm_Pvt.stmv_rec_type;
2308 x_selv_tbl Okl_Sel_Pvt.selv_tbl_type;
2309
2310 l_sty_id NUMBER;
2311 l_trx_id NUMBER;
2315 BEGIN
2312 --06-Dec-2004 PAGARG Bug# 3948473 passing investor agreement id as part of
2313 --additonal parameter to obtain formula value.
2314 l_flag BOOLEAN;
2316 --message logging
2317 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2318 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE
2319 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_loan_payment'
2320 ,'Begin (+)');
2321 END IF;
2322
2323 --06-Dec-2004 PAGARG Bug# 3948473 passing investor agreement id as part of
2324 --additonal parameter to obtain formula value.
2325 l_flag := FALSE;
2326 IF g_add_params.COUNT > 0
2327 THEN
2328 FOR l_loop_counter IN g_add_params.FIRST..g_add_params.LAST
2329 LOOP
2330 IF g_add_params(l_loop_counter).name = 'inv_agr_id'
2331 THEN
2332 l_flag := TRUE;
2333 g_add_params(l_loop_counter).value := p_ia_id;
2334 END IF;
2335 END LOOP;
2336 END IF;
2337 IF l_flag = FALSE
2338 THEN
2339 l_loop_counter := NVL(g_add_params.LAST, 0) + 1;
2340 g_add_params(l_loop_counter).name := 'inv_agr_id';
2341 g_add_params(l_loop_counter).value := p_ia_id;
2342 END IF;
2343
2344 IF p_asset_tbl.COUNT > 0 THEN
2345
2346 -- smoduga +++++++++ User Defined Streams -- start ++++++++++++++++
2347 OKL_STREAMS_UTIL.get_primary_stream_type(p_ia_id,
2348 l_rent_sty,
2349 l_return_status,
2350 l_sty_id);
2351
2352 IF l_sty_id IS NULL OR l_sty_id = OKL_API.G_MISS_NUM THEN
2353 --message logging
2354 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2355 FND_LOG.STRING(FND_LOG.LEVEL_ERROR
2356 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_loan_payment'
2357 ,'OKL_STREAMS_UTIL.get_primary_stream_type returned no values');
2358 END IF;
2359
2360 -- smoduga +++++++++ User Defined Streams -- end ++++++++++++++++
2361 OKL_API.set_message(p_app_name => G_APP_NAME,
2362 p_msg_name => G_INVALID_VALUE1,
2363 p_token1 => 'COL_NAME',
2364 p_token1_value => 'STY_ID');
2365 END IF;
2366 END IF;
2367
2368 FOR l_loop_counter IN p_asset_tbl.FIRST..p_asset_tbl.LAST
2369 LOOP
2370 IF p_asset_tbl(l_loop_counter).p_amount IS NOT NULL
2371 THEN
2372 --message logging
2373 IF (FND_LOG.LEVEL_STATEMENT >=
2374 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2375 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2376 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_loan_payment'
2377 ,'calling OKL_AM_UTIL_PVT.get_formula_value with'
2378 ||' formula name '||l_formula_name);
2379 END IF;
2380
2381 OKL_AM_UTIL_PVT.get_formula_value(
2382 p_formula_name => l_formula_name
2383 ,p_chr_id => p_asset_tbl(l_loop_counter).p_khr_id
2384 ,p_cle_id => p_asset_tbl(l_loop_counter).p_kle_id
2385 ,p_additional_parameters => g_add_params -- rmunjulu EDAT
2386 ,x_formula_value => l_formula_amount
2387 ,x_return_status => l_return_status);
2388
2389 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2390 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2391 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2392 RAISE OKL_API.G_EXCEPTION_ERROR;
2393 END IF;
2394 --message logging
2395 IF (FND_LOG.LEVEL_STATEMENT >=
2396 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2397 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2398 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_loan_payment'
2399 ,'returning from OKL_AM_UTIL_PVT.get_formula_value'
2400 ||', status is '
2401 ||l_return_status
2402 ||' and l_formula_amount is '
2403 ||l_formula_amount);
2404 END IF;
2405
2406 OPEN get_next_trx_val_csr;
2407 FETCH get_next_trx_val_csr INTO l_trx_id;
2408 CLOSE get_next_trx_val_csr;
2409
2410 l_disbursement_amount :=
2411 (p_asset_tbl(l_loop_counter).p_amount * l_formula_amount);
2412 -- stream header parameters
2413 l_stmv_rec.khr_id := p_asset_tbl(l_loop_counter).p_khr_id;
2414 l_stmv_rec.kle_id := p_asset_tbl(l_loop_counter).p_kle_id;
2415 l_stmv_rec.sty_id := l_sty_id;
2416 l_stmv_rec.SGN_CODE := 'MANL';
2417 l_stmv_rec.SAY_CODE := 'CURR';
2418 l_stmv_rec.TRANSACTION_NUMBER := l_trx_id;
2419 l_stmv_rec.ACTIVE_YN := 'Y';
2420
2421 -- rmunjulu 3910833 added code to set source_id and source_table
2422 l_stmv_rec.source_id := p_ia_id;
2423 l_stmv_rec.source_table := G_SOURCE_TABLE;
2424 l_stmv_rec.date_current := SYSDATE;
2425
2429 l_selv_tbl(1).amount := l_disbursement_amount;
2426 -- stream element parameters
2427 l_selv_tbl(1).stream_element_date := p_transaction_date; -- rmunjulu EDAT
2428 -- 04 Nov 2004 PAGARG Bug# 3954752
2430 l_selv_tbl(1).ACCRUED_YN := 'N';
2431 l_selv_tbl(1).SE_LINE_NUMBER := 1;
2432
2433 -- create disbursement record
2434
2435 --message logging
2436 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2437 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2438 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_loan_payment'
2439 ,'calling OKL_STREAMS_PUB.create_streams ');
2440 END IF;
2441
2442 OKL_STREAMS_PUB.create_streams(
2443 p_api_version => p_api_version
2444 ,p_init_msg_list => p_init_msg_list
2445 ,x_return_status => l_return_status
2446 ,x_msg_count => x_msg_count
2447 ,x_msg_data => x_msg_data
2448 ,p_stmv_rec => l_stmv_rec
2449 ,p_selv_tbl => l_selv_tbl
2450 ,x_stmv_rec => x_stmv_rec
2451 ,x_selv_tbl => x_selv_tbl );
2452
2453 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2454 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2455 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2456 RAISE OKL_API.G_EXCEPTION_ERROR;
2457 END IF;
2458 --message logging
2459 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2460 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2461 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_loan_payment'
2462 ,'returning from OKL_STREAMS_PUB.create_streams,'
2463 ||' status is '||l_return_status);
2464 END IF;
2465 -- gboomina Bug 4775555 - Start
2466 -- INVESTOR_DISB_ADJUSTMENTS
2467 do_disb_adjustments_loan (
2468 p_api_version => p_api_version,
2469 p_init_msg_list => OKL_API.G_FALSE,
2470 x_msg_count => x_msg_count,
2471 x_msg_data => x_msg_data,
2472 p_ia_id => p_ia_id,
2473 p_khr_id => p_asset_tbl(l_loop_counter).p_khr_id,
2474 p_kle_id => p_asset_tbl(l_loop_counter).p_kle_id,
2475 p_partial_yn => G_PARTIAL_YN,
2476 p_quote_eff_date => G_DATE_EFFECTIVE_FROM,
2477 x_return_status => l_return_status);
2478
2479 --message logging
2480 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2481 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2482 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_loan_payment'
2483 ,'returning from do_disb_adjustments,'
2484 ||' status is '||l_return_status);
2485 END IF;
2486
2487 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2488 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2489 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2490 RAISE OKL_API.G_EXCEPTION_ERROR;
2491 END IF;
2492 -- gboomina Bug 4775555 - End
2493 END IF;
2494 END LOOP; -- p_asset_tbl
2495 --message logging
2496 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2497 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE
2498 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_loan_payment'
2499 ,'End (-)');
2500 END IF;
2501
2502 x_return_status := l_return_status;
2503
2504 EXCEPTION
2505 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2506
2507
2508 IF get_next_trx_val_csr%ISOPEN THEN
2509 CLOSE get_next_trx_val_csr;
2510 END IF;
2511
2512 --message logging
2513 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2514 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
2515 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_loan_payment'
2516 , 'Handled exception occured');
2517 END IF;
2518
2519 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2520 (
2521 'proc_secure_streams',
2522 G_PKG_NAME,
2523 'OKC_API.G_RET_STS_ERROR',
2524 x_msg_count,
2525 x_msg_data,
2526 '_PVT'
2527 );
2528
2529 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2530
2531
2532 IF get_next_trx_val_csr%ISOPEN THEN
2533 CLOSE get_next_trx_val_csr;
2534 END IF;
2535
2536 --message logging
2537 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2538 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
2542
2539 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_loan_payment'
2540 , 'Expected exception occured');
2541 END IF;
2543 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2544 (
2545 'proc_secure_streams',
2546 G_PKG_NAME,
2547 'OKC_API.G_RET_STS_UNEXP_ERROR',
2548 x_msg_count,
2549 x_msg_data,
2550 '_PVT'
2551 );
2552
2553 WHEN OTHERS THEN
2554
2555
2556 IF get_next_trx_val_csr%ISOPEN THEN
2557 CLOSE get_next_trx_val_csr;
2558 END IF;
2559
2560 --message logging
2561 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2562 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
2563 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_loan_payment'
2564 , 'When others exception occured');
2565 END IF;
2566
2567 OKL_API.set_message(p_app_name => g_app_name,
2568 p_msg_name => g_unexpected_error,
2569 p_token1 => g_sqlcode_token,
2570 p_token1_value => sqlcode,
2571 p_token2 => g_sqlerrm_token,
2572 p_token2_value => sqlerrm);
2573
2574 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2575
2576
2577 END disburse_investor_loan_payment;
2578
2579 /*========================================================================
2580 | PUBLIC PROCEDURE DISBURSE_INVESTOR_RV
2581 |
2582 | DESCRIPTION
2583 | Processes invester disbursement for residual value.
2584 |
2585 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2586 | Called from PROCESS_SECURITIZED_STREAMS
2587 |
2588 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2589 |
2590 | PARAMETERS
2591 | p_api_version IN Standard in parameter
2592 | p_init_msg_list IN Standard in parameter
2593 | x_return_status OUT Standard out parameter
2594 | x_msg_count OUT Standard out parameter
2595 | x_msg_data OUT Standard out parameter
2596 | p_kle_id IN Asset Line identifier
2597 | p_khr_id IN Contract Header identifier
2598 | p_sale_price IN Disposition Amount
2599 |
2600 | KNOWN ISSUES
2601 |
2602 | NOTES
2603 |
2604 | MODIFICATION HISTORY
2605 | Date Author Description of Changes
2606 | 09-OCT-2003 MDokal Created.
2607 | DD-MON-YYYY Name Bug #####, modified amount ..
2608 | 24-Sep-2004 rmunjulu 3910833 Added code to get ia_id and
2609 | set for disbursement stream
2610 | 06-Oct-2004 rmunjulu EDAT Added Parameters to get transaction
2611 | date and effective date and do processing
2612 | based on those
2613 |
2614 *=======================================================================*/
2615 PROCEDURE disburse_investor_rv(
2616 p_api_version IN NUMBER,
2617 p_init_msg_list IN VARCHAR2,
2618 x_return_status OUT NOCOPY VARCHAR2,
2619 x_msg_count OUT NOCOPY NUMBER,
2620 x_msg_data OUT NOCOPY VARCHAR2,
2621 p_khr_id IN NUMBER,
2622 p_kle_id IN NUMBER,
2623 p_ia_id IN NUMBER, -- rmunjulu 3910833
2624 p_effective_date IN DATE DEFAULT NULL, -- rmunjulu EDAT
2625 p_transaction_date IN DATE DEFAULT NULL, -- rmunjulu EDAT
2626 p_sale_price IN NUMBER) IS
2627
2628 /*-----------------------------------------------------------------------+
2629 | Cursor Declarations |
2630 +-----------------------------------------------------------------------*/
2631
2632
2633 CURSOR get_next_trx_val_csr IS
2634 SELECT okl_sif_seq.nextval
2635 FROM dual;
2636
2637 /*-----------------------------------------------------------------------+
2638 | Local Variable Declarations and initializations |
2639 +-----------------------------------------------------------------------*/
2640
2641 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
2642
2643 l_formula_amount NUMBER;
2644 l_formula_name CONSTANT VARCHAR2(40) := 'INVESTOR_RV_DISBURSEMENT';
2645 l_rent_sty CONSTANT VARCHAR2(30) := 'INVESTOR_RESIDUAL_PAY';-- SMODUGA 15-Oct-04 Bug 3925469
2646
2647 l_disbursement_amount NUMBER;
2648
2649 l_stmv_rec Okl_Stm_Pvt.stmv_rec_type;
2650 l_selv_tbl Okl_Sel_Pvt.selv_tbl_type;
2651 x_stmv_rec Okl_Stm_Pvt.stmv_rec_type;
2652 x_selv_tbl Okl_Sel_Pvt.selv_tbl_type;
2653
2654 l_trx_id NUMBER;
2655
2656 BEGIN
2657 --message logging
2658 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2659 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE
2660 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_rv'
2661 ,'Begin (+)');
2662 END IF;
2663 -- kle_id and sale_amount are used to determine the what and how much to
2664 -- disburse.
2665
2666 IF (p_kle_id IS NOT NULL) AND (p_sale_price IS NOT NULL) THEN
2667 --message logging
2671 ,'calling OKL_AM_UTIL_PVT.get_formula_value with formula name '
2668 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2669 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2670 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_rv'
2672 ||l_formula_name);
2673 END IF;
2674
2675 OKL_AM_UTIL_PVT.get_formula_value(
2676 p_formula_name => l_formula_name,
2677 p_chr_id => p_khr_id,
2678 p_cle_id => p_kle_id,
2679 p_additional_parameters => g_add_params, -- rmunjulu EDAT
2680 x_formula_value => l_formula_amount,
2681 x_return_status => l_return_status);
2682
2683 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2684 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2685 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2686 RAISE OKL_API.G_EXCEPTION_ERROR;
2687 END IF;
2688 --message logging
2689 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2690 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2691 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_rv'
2692 ,'returning from OKL_AM_UTIL_PVT.get_formula_value, status is '
2693 ||l_return_status
2694 ||' and l_formula_amount is '
2695 ||l_formula_amount);
2696 END IF;
2697
2698 l_disbursement_amount := (p_sale_price * l_formula_amount);
2699
2700
2701 -- smoduga +++++++++ User Defined Streams -- start ++++++++++++++++
2702 OKL_STREAMS_UTIL.get_primary_stream_type(p_ia_id,
2703 l_rent_sty,
2704 l_return_status,
2705 l_stmv_rec.sty_id);
2706 IF l_stmv_rec.sty_id IS NULL OR l_stmv_rec.sty_id = OKL_API.G_MISS_NUM THEN
2707
2708 --message logging
2709 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2710 FND_LOG.STRING(FND_LOG.LEVEL_ERROR
2711 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_rv'
2712 ,' OKL_STREAMS_UTIL.get_primary_stream_type returned no values');
2713 END IF;
2714 -- smoduga +++++++++ User Defined Streams -- end ++++++++++++++++
2715
2716 OKL_API.set_message(p_app_name => G_APP_NAME,
2717 p_msg_name => G_INVALID_VALUE1,
2718 p_token1 => 'COL_NAME',
2719 p_token1_value => 'STY.CODE');
2720 END IF;
2721
2722 OPEN get_next_trx_val_csr;
2723 FETCH get_next_trx_val_csr INTO l_trx_id;
2724 CLOSE get_next_trx_val_csr;
2725
2726 -- stream header parameters
2727 l_stmv_rec.khr_id := p_khr_id;
2728 l_stmv_rec.kle_id := p_kle_id;
2729 l_stmv_rec.SGN_CODE := 'MANL';
2730 l_stmv_rec.SAY_CODE := 'CURR';
2731 l_stmv_rec.TRANSACTION_NUMBER := l_trx_id;
2732 l_stmv_rec.ACTIVE_YN := 'Y';
2733 l_stmv_rec.DATE_CURRENT := SYSDATE;
2734
2735 -- rmunjulu 3910833 added code to set source_id and source_table
2736 l_stmv_rec.source_id := p_ia_id;
2737 l_stmv_rec.source_table := G_SOURCE_TABLE;
2738
2739 -- stream element parameters
2740 l_selv_tbl(1).stream_element_date := p_transaction_date; -- rmunjulu EDAT
2741 l_selv_tbl(1).amount := l_disbursement_amount;
2742 l_selv_tbl(1).ACCRUED_YN := 'N';
2743 l_selv_tbl(1).SE_LINE_NUMBER := 1;
2744
2745 --message logging
2746 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2747 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2748 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_rv'
2749 ,'calling OKL_STREAMS_PUB.create_streams ');
2750 END IF;
2751
2752 -- create disbursement record
2753 OKL_STREAMS_PUB.create_streams(
2754 p_api_version => p_api_version
2755 ,p_init_msg_list => p_init_msg_list
2756 ,x_return_status => l_return_status
2757 ,x_msg_count => x_msg_count
2758 ,x_msg_data => x_msg_data
2759 ,p_stmv_rec => l_stmv_rec
2760 ,p_selv_tbl => l_selv_tbl
2761 ,x_stmv_rec => x_stmv_rec
2762 ,x_selv_tbl => x_selv_tbl );
2763
2764 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2765 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2766 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2767 RAISE OKL_API.G_EXCEPTION_ERROR;
2768 END IF;
2769 --message logging
2770 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2771 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2772 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_rv'
2773 ,'returning from OKL_STREAMS_PUB.create_streams, status is '
2774 ||l_return_status);
2775 END IF;
2776 END IF;
2777 --message logging
2778 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2779 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE
2780 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_rv'
2781 ,'End (-)');
2782 END IF;
2783
2784 x_return_status := l_return_status;
2785
2786 EXCEPTION
2787 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2788
2789
2790 IF get_next_trx_val_csr%ISOPEN THEN
2791 CLOSE get_next_trx_val_csr;
2792 END IF;
2793
2794 --message logging
2795 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2796 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
2797 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_rv'
2798 , 'Handled exception occured');
2799 END IF;
2800
2801 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2802 (
2803 'proc_secure_streams',
2804 G_PKG_NAME,
2805 'OKC_API.G_RET_STS_ERROR',
2806 x_msg_count,
2807 x_msg_data,
2808 '_PVT'
2809 );
2810
2811 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2812
2813
2814 IF get_next_trx_val_csr%ISOPEN THEN
2815 CLOSE get_next_trx_val_csr;
2816 END IF;
2817
2818 --message logging
2819 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2820 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
2821 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_rv'
2822 , 'Unexpected exception occured');
2823 END IF;
2824
2825 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2826 (
2827 'proc_secure_streams',
2828 G_PKG_NAME,
2829 'OKC_API.G_RET_STS_UNEXP_ERROR',
2830 x_msg_count,
2831 x_msg_data,
2832 '_PVT'
2833 );
2834
2835 WHEN OTHERS THEN
2836
2837
2838 IF get_next_trx_val_csr%ISOPEN THEN
2839 CLOSE get_next_trx_val_csr;
2840 END IF;
2841
2842 --message logging
2843 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2844 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
2845 ,'OKL_AM_SECURITIZATION_PVT.disburse_investor_rv'
2846 , 'When others exception occured');
2847 END IF;
2848
2849 OKL_API.set_message(p_app_name => g_app_name,
2850 p_msg_name => g_unexpected_error,
2851 p_token1 => g_sqlcode_token,
2852 p_token1_value => sqlcode,
2853 p_token2 => g_sqlerrm_token,
2854 p_token2_value => sqlerrm);
2855
2856 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2857
2858 END disburse_investor_rv;
2859
2860 /*========================================================================
2861 | PUBLIC PROCEDURE CREATE_POOL_TRANSACTION
2862 |
2863 | DESCRIPTION
2864 | Create the pool transaction and makes pool modifications
2865 |
2866 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2867 | Called from PROCESS_SECURITIZED_STREAMS
2868 |
2869 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2870 |
2871 | PARAMETERS
2872 | p_api_version IN Standard in parameter
2873 | p_init_msg_list IN Standard in parameter
2874 | x_return_status OUT Standard out parameter
2875 | x_msg_count OUT Standard out parameter
2876 | x_msg_data OUT Standard out parameter
2877 | p_asset_tbl IN Contains a list of assets for pool transactions
2878 | p_transaction_reason IN Reason required for creating pool transaction
2879 | p_kle_id IN Asset for pool transaction
2883 | KNOWN ISSUES
2880 | p_khr_id IN Contract for pool transaction
2881 | p_disb_type IN Identifies the subclass, RESIDUAL or RENT
2882 |
2884 |
2885 | NOTES
2886 |
2887 | MODIFICATION HISTORY
2888 | Date Author Description of Changes
2889 | 09-OCT-2003 MDokal Created.
2890 | DD-MON-YYYY Name Bug #####, modified amount ..
2891 | 23-Sep-2004 rmunjulu EDAT Added Parameters to get transaction
2892 | date and effective date and do processing
2893 | based on those
2894 |
2895 *=======================================================================*/
2896 PROCEDURE create_pool_transaction(
2897 p_api_version IN NUMBER,
2898 p_init_msg_list IN VARCHAR2,
2899 x_return_status OUT NOCOPY VARCHAR2,
2900 x_msg_count OUT NOCOPY NUMBER,
2901 x_msg_data OUT NOCOPY VARCHAR2,
2902 p_asset_tbl IN asset_tbl_type,
2903 p_transaction_reason IN VARCHAR2,
2904 p_khr_id IN NUMBER,
2905 p_kle_id IN NUMBER,
2906 p_effective_date IN DATE DEFAULT NULL, -- rmunjulu EDAT
2907 p_transaction_date IN DATE DEFAULT NULL, -- rmunjulu EDAT
2908 p_disb_type IN VARCHAR2) IS
2909
2910 /*-----------------------------------------------------------------------+
2911 | Local Variable Declarations and initializations |
2912 +-----------------------------------------------------------------------*/
2913
2914 l_asset_table asset_tbl_type;
2915 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
2916
2917 l_loop_counter NUMBER := 1;
2918
2919 BEGIN
2920 --message logging
2921 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2922 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE
2923 ,'OKL_AM_SECURITIZATION_PVT.create_pool_transaction'
2924 ,'Begin (+)');
2925 END IF;
2926
2927 IF p_disb_type = 'RESIDUAL' THEN -- RV pool transaction
2928
2929 l_asset_table(1).p_khr_id := p_khr_id;
2930 l_asset_table(1).p_kle_id := p_kle_id;
2931 ELSE
2932 l_asset_table := p_asset_tbl; -- Rent pool transaction
2933 END IF;
2934
2935 IF p_transaction_reason =
2936 OKL_SECURITIZATION_PVT.G_TRX_REASON_EARLY_TERMINATION THEN
2937 --message logging
2938 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2939 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2940 ,'OKL_AM_SECURITIZATION_PVT.create_pool_transaction'
2941 ,'calling OKL_SECURITIZATION_PVT.MODIFY_POOL_CONTENTS'
2942 ||' with transaction reason '
2943 ||p_transaction_reason);
2944 END IF;
2945 --start: | 05-29-08 cklee -- fixed bug: 7017824(R12)/OKL.H: bug#6964174 |
2946 -- Commenting for bug 6964174
2947 -- Inactivation of pool contents during early full termination is deferred to
2948 -- the end of termination transaction. This was done to ensure that pool contents are active
2949 -- until all accounting transactions like termination billing amounts, termination accounting
2950 -- transactions are complete and have used special accounting.
2951 -- Instead of this place, pool contents are inactivated at the end of
2952 -- OKL_AM_LEASE_TRMNT_PVT.lease_termination API
2953 /*
2954 OKL_SECURITIZATION_PVT.MODIFY_POOL_CONTENTS
2955 ( p_api_version => p_api_version
2956 ,p_init_msg_list => p_init_msg_list
2957 ,p_transaction_reason => p_transaction_reason
2958 ,p_khr_id => l_asset_table(l_loop_counter).p_khr_id
2959 ,p_stream_type_subclass => p_disb_type
2960 ,p_transaction_date => p_transaction_date -- rmunjulu EDAT
2961 ,p_effective_date => p_effective_date -- rmunjulu EDAT
2962 ,x_return_status => l_return_status
2963 ,x_msg_count => x_msg_count
2964 ,x_msg_data => x_msg_data );
2965
2966 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2967 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2968 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2969 RAISE OKL_API.G_EXCEPTION_ERROR;
2970 END IF;
2971 --message logging
2972 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2973 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2974 ,'OKL_AM_SECURITIZATION_PVT.create_pool_transaction'
2975 ,'returning from '
2976 ||'OKL_SECURITIZATION_PVT.MODIFY_POOL_CONTENTS, status is '
2977 ||l_return_status);
2978 END IF;*/-- end of commenting for bug 6964174
2979 --end: | 05-29-08 cklee -- fixed bug: 7017824(R12)/OKL.H: bug#6964174 |
2980
2981 -- rmunjulu 4398936 removed the following else and added elseif
2982 --ELSE -- handle partial termination transactions and residual
2983 -- transactions by asset
2984
2985 -- rmunjulu 4398936 Added this elseif
2986 ELSIF p_transaction_reason =
2987 OKL_SECURITIZATION_PVT.G_TRX_REASON_ASSET_DISPOSAL THEN
2988 IF g_call_ad_flag THEN
2989
2990 FOR l_loop_counter IN l_asset_table.FIRST..l_asset_table.LAST LOOP
2991 IF l_asset_table(l_loop_counter).p_khr_id IS NOT NULL THEN
2992 --message logging
2993 IF (FND_LOG.LEVEL_STATEMENT >=
2994 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2995 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2996 ,'OKL_AM_SECURITIZATION_PVT.create_pool_transaction'
2997 ,'calling OKL_SECURITIZATION_PVT.MODIFY_POOL_'
2998 ||'CONTENTS with transaction reason '
2999 ||p_transaction_reason);
3000 END IF;
3001
3002 OKL_SECURITIZATION_PVT.MODIFY_POOL_CONTENTS
3003 ( p_api_version => p_api_version
3004 ,p_init_msg_list => p_init_msg_list
3005 ,p_transaction_reason => p_transaction_reason
3006 ,p_khr_id => l_asset_table(l_loop_counter).p_khr_id
3007 ,p_kle_id => l_asset_table(l_loop_counter).p_kle_id
3008 ,p_stream_type_subclass => p_disb_type
3009 ,p_transaction_date => p_transaction_date -- rmunjulu EDAT
3010 ,p_effective_date => p_effective_date -- rmunjulu EDAT
3011 ,x_return_status => l_return_status
3012 ,x_msg_count => x_msg_count
3013 ,x_msg_data => x_msg_data );
3014
3015 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
3016 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3017 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
3018 RAISE OKL_API.G_EXCEPTION_ERROR;
3019 END IF;
3020 --message logging
3021 IF (FND_LOG.LEVEL_STATEMENT >=
3022 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3023 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
3024 ,'OKL_AM_SECURITIZATION_PVT.create_pool_transaction'
3025 ,'returning from OKL_SECURITIZATION_PVT.'
3026 ||'MODIFY_POOL_CONTENTS, status is '
3027 ||l_return_status);
3028 END IF;
3029 END IF;
3030 END LOOP; -- l_asset_table
3031 END IF; -- gboomina 07-Dec-05 - Added End if
3032 END IF;
3033 --message logging
3034 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3035 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE
3036 ,'OKL_AM_SECURITIZATION_PVT.create_pool_transaction'
3037 ,'End (-)');
3038 END IF;
3039
3040 x_return_status := l_return_status;
3041
3042 EXCEPTION
3043 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3044
3045 --message logging
3046 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3047 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
3048 ,'OKL_AM_SECURITIZATION_PVT.create_pool_transaction'
3049 , 'Handled exception occured');
3050 END IF;
3051
3052 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3053 (
3054 'proc_secure_streams',
3055 G_PKG_NAME,
3056 'OKC_API.G_RET_STS_ERROR',
3057 x_msg_count,
3058 x_msg_data,
3059 '_PVT'
3060 );
3061
3062 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3063
3064 --message logging
3065 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3066 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
3067 ,'OKL_AM_SECURITIZATION_PVT.create_pool_transaction'
3068 , 'Unexpected exception occured');
3069 END IF;
3070
3071 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3072 (
3073 'proc_secure_streams',
3074 G_PKG_NAME,
3075 'OKC_API.G_RET_STS_UNEXP_ERROR',
3076 x_msg_count,
3077 x_msg_data,
3078 '_PVT'
3079 );
3080
3081 WHEN OTHERS THEN
3082
3083 --message logging
3084 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3085 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
3086 ,'OKL_AM_SECURITIZATION_PVT.create_pool_transaction'
3087 , 'When others exception occured');
3088 END IF;
3089 OKL_API.set_message(p_app_name => g_app_name,
3090 p_msg_name => g_unexpected_error,
3091 p_token1 => g_sqlcode_token,
3092 p_token1_value => sqlcode,
3093 p_token2 => g_sqlerrm_token,
3094 p_token2_value => sqlerrm);
3095
3096 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3097
3098 END create_pool_transaction;
3099
3100
3101 END OKL_AM_SECURITIZATION_PVT;