[Home] [Help]
PACKAGE BODY: APPS.OKL_SECURITIZATION_PVT
Source
1 PACKAGE BODY Okl_Securitization_Pvt AS
2 /* $Header: OKLRSZSB.pls 120.17 2008/06/16 07:17:14 rpillay noship $ */
3 ----------------------------------------------------------------------------
4 -- Global Message Constants
5 ----------------------------------------------------------------------------
6 G_POC_STS_NEW CONSTANT VARCHAR2(10) := Okl_Pool_Pvt.G_POC_STS_NEW;
7 G_POC_STS_ACTIVE CONSTANT VARCHAR2(10) := Okl_Pool_Pvt.G_POC_STS_ACTIVE;
8 G_POC_STS_INACTIVE CONSTANT VARCHAR2(10) := Okl_Pool_Pvt.G_POC_STS_INACTIVE;
9 G_FINAL_DATE CONSTANT DATE := Okl_Pool_Pvt.G_FINAL_DATE;
10
11 G_STY_INV_RENT_BUYBACK CONSTANT VARCHAR2(30) := 'INVESTOR_RENT_BUYBACK';
12 G_STY_INV_RESIDUAL_BUYBACK CONSTANT VARCHAR2(30) := 'INVESTOR_RESIDUAL_BUYBACK';
13 G_STY_SUBCLASS_RENT CONSTANT VARCHAR2(4) := 'RENT';
14 G_STY_SUBCLASS_RESIDUAL CONSTANT VARCHAR2(10) := 'RESIDUAL';
15 G_STM_SGN_CODE_MANUAL CONSTANT VARCHAR2(4) := 'MANL';
16 G_STM_SAY_CODE_CURR CONSTANT VARCHAR2(4) := 'CURR';
17 G_STM_ACTIVE_Y CONSTANT VARCHAR2(1) := 'Y';
18 G_STM_SOURCE_TABLE CONSTANT VARCHAR2(15) := 'OKL_K_HEADERS';
19
20 --ankushar Bug#6740000, Added new Stream type Subclass for Loan Contract
21 G_STY_SUBCLASS_LOAN_PAYMENT CONSTANT VARCHAR2(20) := 'LOAN_PAYMENT';
22 G_STY_INV_PRINCIPAL_BUYBACK CONSTANT VARCHAR2(30) := 'INVESTOR_PRINCIPAL_BUYBACK';
23 G_STY_INV_INTEREST_BUYBACK CONSTANT VARCHAR2(30) := 'INVESTOR_INTEREST_BUYBACK';
24 G_STY_INV_PPD_BUYBACK CONSTANT VARCHAR2(30) := 'INVESTOR_PAYDOWN_BUYBACK';
25
26 G_SECURITIZED_CODE_Y CONSTANT VARCHAR2(1) := 'Y';
27 G_SECURITIZED_CODE_N CONSTANT VARCHAR2(1) := 'N';
28 -- sosharma added codes for tranaction_status
29 G_POOL_TRX_STATUS_COMPLETE CONSTANT VARCHAR2(30) := 'COMPLETE';
30
31 ----------------------------------------------------------------------------
32 -- Procedures and Functions
33 ----------------------------------------------------------------------------
34
35 ----------------------------------------------------------------------------------
36 -- Start of comments
37 --
38 -- Procedure Name : is_khr_securitized
39 -- Description : Checks if a contract is securitized on the given date
40 -- Business Rules :
41 -- Parameters :
42 -- : return: OKL_API.G_TRUE: true, OKL_API.G_FALSE false,
43 -- OKL_API.G_RET_STS_ERROR, OKL_API.G_RET_STS_UNEXP_ERROR
44 -- Version : 1.0
45 -- End of comments
46 ----------------------------------------------------------------------------------
47 FUNCTION is_khr_securitized(
48 p_khr_id IN okc_k_headers_b.ID%TYPE
49 ,p_effective_date IN DATE
50 ,p_effective_date_operator IN VARCHAR2 DEFAULT G_GREATER_THAN_EQUAL_TO
51 ,p_stream_type_subclass IN okl_strm_type_b.stream_type_subclass%TYPE DEFAULT NULL
52 ) RETURN VARCHAR
53 IS
54 l_dummy VARCHAR2(1);
55 x_value VARCHAR2(1) := Okl_Api.G_FALSE;
56 l_row_found BOOLEAN := FALSE;
57 v_sql VARCHAR2(4000);
58
59 -- case 1
60 CURSOR c_khr_def(p_khr_id okc_k_headers_b.ID%TYPE, p_effective_date DATE) IS
61 SELECT '1'
62 FROM okl_pool_contents pocb,
63 okl_pools polb,
64 okc_k_headers_b chrb
65 WHERE pocb.pol_id = polb.id
66 AND polb.khr_id = chrb.id -- inv agreement
67 AND chrb.sts_code = G_STS_CODE_ACTIVE
68 AND pocb.khr_id = p_khr_id
69 AND TRUNC(NVL(pocb.streams_to_date,G_FINAL_DATE)) >= TRUNC(p_effective_date)
70 AND pocb.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
71 AND EXISTS (SELECT '1'
72 FROM okl_streams stmb,
73 okl_strm_type_b styb
74 WHERE stmb.sty_id = styb.id
75 AND stmb.khr_id = pocb.khr_id
76 AND stmb.id = pocb.stm_id
77 AND NVL(styb.stream_type_subclass,'x')
78 = NVL(p_stream_type_subclass, NVL(styb.stream_type_subclass,'x'))
79 );
80
81 -- case 2
82 CURSOR c_khr_gr(p_khr_id okc_k_headers_b.ID%TYPE, p_effective_date DATE) IS
83 SELECT '1'
84 FROM okl_pool_contents pocb,
85 okl_pools polb,
86 okc_k_headers_b chrb
87 WHERE pocb.pol_id = polb.id
88 AND polb.khr_id = chrb.id -- inv agreement
89 AND chrb.sts_code = G_STS_CODE_ACTIVE
90 AND pocb.khr_id = p_khr_id
91 AND TRUNC(NVL(pocb.streams_to_date,G_FINAL_DATE)) > TRUNC(p_effective_date)
92 AND pocb.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
93 AND EXISTS (SELECT '1'
94 FROM okl_streams stmb,
95 okl_strm_type_b styb
96 WHERE stmb.sty_id = styb.id
97 AND stmb.khr_id = pocb.khr_id
98 AND stmb.id = pocb.stm_id
99 AND NVL(styb.stream_type_subclass,'x')
100 = NVL(p_stream_type_subclass, NVL(styb.stream_type_subclass,'x'))
101 );
102
103
104 -- case 3
105 CURSOR c_khr_ls(p_khr_id okc_k_headers_b.ID%TYPE, p_effective_date DATE) IS
106 SELECT '1'
107 FROM okl_pool_contents pocb,
108 okl_pools polb,
109 okc_k_headers_b chrb
110 WHERE pocb.pol_id = polb.id
111 AND polb.khr_id = chrb.id -- inv agreement
112 AND chrb.sts_code = G_STS_CODE_ACTIVE
113 AND pocb.khr_id = p_khr_id
114 AND TRUNC(NVL(pocb.streams_to_date,G_FINAL_DATE)) < TRUNC(p_effective_date)
115 AND pocb.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
116 AND EXISTS (SELECT '1'
117 FROM okl_streams stmb,
118 okl_strm_type_b styb
119 WHERE stmb.sty_id = styb.id
120 AND stmb.khr_id = pocb.khr_id
121 AND stmb.id = pocb.stm_id
122 AND NVL(styb.stream_type_subclass,'x')
123 = NVL(p_stream_type_subclass, NVL(styb.stream_type_subclass,'x'))
124 );
125 -- case 4
126 CURSOR c_khr_eq(p_khr_id okc_k_headers_b.ID%TYPE, p_effective_date DATE) IS
127 SELECT '1'
128 FROM okl_pool_contents pocb,
129 okl_pools polb,
130 okc_k_headers_b chrb
131 WHERE pocb.pol_id = polb.id
132 AND polb.khr_id = chrb.id -- inv agreement
133 AND chrb.sts_code = G_STS_CODE_ACTIVE
134 AND pocb.khr_id = p_khr_id
135 AND TRUNC(NVL(pocb.streams_to_date,G_FINAL_DATE)) = TRUNC(p_effective_date)
136 AND pocb.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
137 AND EXISTS (SELECT '1'
138 FROM okl_streams stmb,
139 okl_strm_type_b styb
140 WHERE stmb.sty_id = styb.id
141 AND stmb.khr_id = pocb.khr_id
142 AND stmb.id = pocb.stm_id
143 AND NVL(styb.stream_type_subclass,'x')
144 = NVL(p_stream_type_subclass, NVL(styb.stream_type_subclass,'x'))
145 );
146 BEGIN
147
148 IF (p_effective_date_operator = G_GREATER_THAN) THEN
149
150 OPEN c_khr_gr(p_khr_id, p_effective_date);
151 FETCH c_khr_gr INTO l_dummy;
152 l_row_found := c_khr_gr%FOUND;
153 CLOSE c_khr_gr;
154 ELSIF (p_effective_date_operator = G_LESS_THAN) THEN
155
156 OPEN c_khr_ls(p_khr_id, p_effective_date);
157 FETCH c_khr_ls INTO l_dummy;
158 l_row_found := c_khr_ls%FOUND;
159 CLOSE c_khr_ls;
160 ELSIF (p_effective_date_operator = G_EQUAL_TO) THEN
161
162 OPEN c_khr_eq(p_khr_id, p_effective_date);
163 FETCH c_khr_eq INTO l_dummy;
164 l_row_found := c_khr_eq%FOUND;
165 CLOSE c_khr_eq;
166
167 ELSIF (p_effective_date_operator = G_GREATER_THAN_EQUAL_TO) THEN
168
169 OPEN c_khr_def(p_khr_id, p_effective_date);
170 FETCH c_khr_def INTO l_dummy;
171 l_row_found := c_khr_def%FOUND;
172 CLOSE c_khr_def;
173
174 END IF;
175
176
177 IF l_row_found THEN
178 x_value := Okl_Api.G_TRUE;
179 ELSE
180 x_value := Okl_Api.G_FALSE;
181 END IF;
182
183
184 RETURN x_value;
185 EXCEPTION
186 WHEN OTHERS THEN
187 --l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
188 Okl_Api.Set_Message(p_app_name => Okl_Api.G_APP_NAME,
189 p_msg_name => 'OKL_UNEXPECTED_ERROR',
190 p_token1 => 'OKL_SQLCODE',
191 p_token1_value => SQLCODE,
192 p_token2 => 'OKL_SQLERRM',
193 p_token2_value => SQLERRM);
194 RETURN 0;
195
196 END is_khr_securitized;
197
198 -----------------------------------------------------------------------
199 -- Start of comments
200 --
201 -- Procedure Name : check_khr_securitized
202 -- Description : Checks if a contract is securitized on the given date
203 -- Business Rules :
204 -- Parameters :
205 -- : x_value: OKL_API.G_TRUE: true, OKL_API.G_FALSE false,
206 -- Version : 1.0
207 -- End of comments
208 -----------------------------------------------------------------------
209 PROCEDURE check_khr_securitized(
210 p_api_version IN NUMBER
211 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
212 ,x_return_status OUT NOCOPY VARCHAR2
213 ,x_msg_count OUT NOCOPY NUMBER
214 ,x_msg_data OUT NOCOPY VARCHAR2
215 ,p_khr_id IN okc_k_headers_b.ID%TYPE
216 ,p_effective_date IN DATE
217 -- cklee 08-08-2003 11.5.10
218 ,p_effective_date_operator IN VARCHAR2 DEFAULT G_GREATER_THAN_EQUAL_TO
219 ,p_stream_type_subclass IN okl_strm_type_b.stream_type_subclass%TYPE DEFAULT NULL
220 -- cklee 08-08-2003 11.5.10
221 ,x_value OUT NOCOPY VARCHAR2
222 ,x_inv_agmt_chr_id_tbl OUT NOCOPY inv_agmt_chr_id_tbl_type
223 )
224 IS
225 l_api_name CONSTANT VARCHAR2(30) := 'check_khr_securitized_pvt';
226 l_api_version CONSTANT NUMBER := 1.0;
227 i NUMBER;
228 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
229
230 CURSOR c_inv_khr(p_khr_id okc_k_headers_b.id%TYPE) IS
231 SELECT ph.khr_id -- inv agreemnet id
232 FROM okl_pools ph
233 WHERE EXISTS (SELECT '1'
234 FROM okl_pool_contents pl
235 WHERE pl.pol_id = ph.id
236 AND pl.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
237 AND pl.khr_id = p_khr_id) -- lease contract id
238 ;
239
240 BEGIN
241 -- Set API savepoint
242 SAVEPOINT check_khr_securitized_pvt;
243
244 -- Check for call compatibility
245 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
246 p_api_version,
247 l_api_name,
248 G_PKG_NAME ))
249 THEN
250 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
251 END IF;
252
253 -- Initialize message list if requested
254 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
255 Fnd_Msg_Pub.initialize;
256 END IF;
257
258 -- Initialize API status to success
259 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
260
261
262 /*** Begin API body ****************************************************/
263 -- 1. get flag
264 x_value := is_khr_securitized
265 (p_khr_id => p_khr_id
266 ,p_effective_date => p_effective_date
267 -- cklee 08-08-2003 11.5.10
268 ,p_effective_date_operator => p_effective_date_operator
269 ,p_stream_type_subclass => p_stream_type_subclass);
270 -- cklee 08-08-2003 11.5.10
271
272 -- 2. get investor agreement id
273 OPEN c_inv_khr(p_khr_id);
274 i := 0;
275 LOOP
276
277 FETCH c_inv_khr INTO
278 x_inv_agmt_chr_id_tbl(i).khr_id;
279
280 EXIT WHEN c_inv_khr%NOTFOUND;
281
282 i := i+1;
283 END LOOP;
284 CLOSE c_inv_khr;
285
286 /*** End API body ******************************************************/
287
288 -- Get message count and if count is 1, get message info
289 Fnd_Msg_Pub.Count_And_Get
290 (p_count => x_msg_count,
291 p_data => x_msg_data);
292
293 EXCEPTION
294 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
295 ROLLBACK TO check_khr_securitized_pvt;
296 x_return_status := Okl_Api.G_RET_STS_ERROR;
297 Fnd_Msg_Pub.Count_And_Get
298 (p_count => x_msg_count,
299 p_data => x_msg_data);
300
301 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
302 ROLLBACK TO check_khr_securitized_pvt;
303 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
304 Fnd_Msg_Pub.Count_And_Get
305 (p_count => x_msg_count,
306 p_data => x_msg_data);
307
308 WHEN OTHERS THEN
309 ROLLBACK TO check_khr_securitized_pvt;
310 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
311 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
312 p_msg_name => G_UNEXPECTED_ERROR,
313 p_token1 => G_SQLCODE_TOKEN,
314 p_token1_value => SQLCODE,
315 p_token2 => G_SQLERRM_TOKEN,
316 p_token2_value => SQLERRM);
317 Fnd_Msg_Pub.Count_And_Get
318 (p_count => x_msg_count,
319 p_data => x_msg_data);
320
321 END check_khr_securitized;
322
323 ----------------------------------------------------------------------------------
324 -- Start of comments
325 --
326 -- Procedure Name : is_kle_securitized
327 -- Description : Checks if an Asset is securitized on the given date
328 -- Business Rules :
329 -- Parameters :
330 -- : return: OKL_API.G_TRUE: true, OKL_API.G_FALSE false,
331 -- OKL_API.G_RET_STS_ERROR, OKL_API.G_RET_STS_UNEXP_ERROR
332 -- Version : 1.0
333 -- End of comments
334 ----------------------------------------------------------------------------------
335 FUNCTION is_kle_securitized(
336 p_kle_id IN okc_k_lines_b.ID%TYPE
337 ,p_effective_date IN DATE
338 -- cklee 08-08-2003 11.5.10
339 ,p_effective_date_operator IN VARCHAR2 DEFAULT G_GREATER_THAN_EQUAL_TO
340 ,p_stream_type_subclass IN okl_strm_type_b.stream_type_subclass%TYPE DEFAULT NULL
341 -- cklee 08-08-2003 11.5.10
342 ) RETURN VARCHAR
343 IS
344 l_dummy VARCHAR2(1);
345 x_value VARCHAR2(1) := Okl_Api.G_FALSE;
346 l_row_found BOOLEAN := FALSE;
347
348 -- case 1
349 CURSOR c_kle_def(p_kle_id okc_k_lines_b.ID%TYPE, p_effective_date DATE) IS
350 SELECT '1'
351 FROM okl_pool_contents pocb,
352 okl_pools polb,
353 okc_k_headers_b chrb
354 WHERE pocb.pol_id = polb.id
355 AND polb.khr_id = chrb.id -- inv agreement
356 AND chrb.sts_code = G_STS_CODE_ACTIVE
357 AND pocb.kle_id = p_kle_id
358 AND TRUNC(NVL(pocb.streams_to_date,G_FINAL_DATE)) >= TRUNC(p_effective_date)
359 AND pocb.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
360 AND EXISTS (SELECT '1'
361 FROM okl_streams stmb,
362 okl_strm_type_b styb
363 WHERE stmb.sty_id = styb.id
364 AND stmb.khr_id = pocb.khr_id
365 AND stmb.kle_id = pocb.kle_id
366 AND stmb.id = pocb.stm_id
367 AND NVL(styb.stream_type_subclass,'x')
368 = NVL(p_stream_type_subclass, NVL(styb.stream_type_subclass,'x'))
369 );
370
371 -- case 2
372 CURSOR c_kle_gr(p_kle_id okc_k_lines_b.ID%TYPE, p_effective_date DATE) IS
373 SELECT '1'
374 FROM okl_pool_contents pocb,
375 okl_pools polb,
376 okc_k_headers_b chrb
377 WHERE pocb.pol_id = polb.id
378 AND polb.khr_id = chrb.id -- inv agreement
379 AND chrb.sts_code = G_STS_CODE_ACTIVE
380 AND pocb.kle_id = p_kle_id
381 AND TRUNC(NVL(pocb.streams_to_date,G_FINAL_DATE)) > TRUNC(p_effective_date)
382 AND pocb.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
383 AND EXISTS (SELECT '1'
384 FROM okl_streams stmb,
385 okl_strm_type_b styb
386 WHERE stmb.sty_id = styb.id
387 AND stmb.khr_id = pocb.khr_id
388 AND stmb.kle_id = pocb.kle_id
389 AND stmb.id = pocb.stm_id
390 AND NVL(styb.stream_type_subclass,'x')
391 = NVL(p_stream_type_subclass, NVL(styb.stream_type_subclass,'x'))
392 );
393
394 -- case 3
395 CURSOR c_kle_ls(p_kle_id okc_k_lines_b.ID%TYPE, p_effective_date DATE) IS
396 SELECT '1'
397 FROM okl_pool_contents pocb,
398 okl_pools polb,
399 okc_k_headers_b chrb
400 WHERE pocb.pol_id = polb.id
401 AND polb.khr_id = chrb.id -- inv agreement
402 AND chrb.sts_code = G_STS_CODE_ACTIVE
403 AND pocb.kle_id = p_kle_id
404 AND TRUNC(NVL(pocb.streams_to_date,G_FINAL_DATE)) < TRUNC(p_effective_date)
405 AND pocb.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
406 AND EXISTS (SELECT '1'
407 FROM okl_streams stmb,
408 okl_strm_type_b styb
409 WHERE stmb.sty_id = styb.id
410 AND stmb.khr_id = pocb.khr_id
411 AND stmb.kle_id = pocb.kle_id
412 AND stmb.id = pocb.stm_id
413 AND NVL(styb.stream_type_subclass,'x')
414 = NVL(p_stream_type_subclass, NVL(styb.stream_type_subclass,'x'))
415 );
416
417 -- case 4
418 CURSOR c_kle_eq(p_kle_id okc_k_lines_b.ID%TYPE, p_effective_date DATE) IS
419 SELECT '1'
420 FROM okl_pool_contents pocb,
421 okl_pools polb,
422 okc_k_headers_b chrb
423 WHERE pocb.pol_id = polb.id
424 AND polb.khr_id = chrb.id -- inv agreement
425 AND chrb.sts_code = G_STS_CODE_ACTIVE
426 AND pocb.kle_id = p_kle_id
427 AND TRUNC(NVL(pocb.streams_to_date,G_FINAL_DATE)) = TRUNC(p_effective_date)
428 AND pocb.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
429 AND EXISTS (SELECT '1'
430 FROM okl_streams stmb,
431 okl_strm_type_b styb
432 WHERE stmb.sty_id = styb.id
433 AND stmb.khr_id = pocb.khr_id
434 AND stmb.kle_id = pocb.kle_id
435 AND stmb.id = pocb.stm_id
436 AND NVL(styb.stream_type_subclass,'x')
437 = NVL(p_stream_type_subclass, NVL(styb.stream_type_subclass,'x'))
438 );
439 BEGIN
440
441 IF (p_effective_date_operator = G_GREATER_THAN) THEN
442
443 OPEN c_kle_gr(p_kle_id, p_effective_date);
444 FETCH c_kle_gr INTO l_dummy;
445 l_row_found := c_kle_gr%FOUND;
446 CLOSE c_kle_gr;
447 ELSIF (p_effective_date_operator = G_LESS_THAN) THEN
448
449 OPEN c_kle_ls(p_kle_id, p_effective_date);
450 FETCH c_kle_ls INTO l_dummy;
451 l_row_found := c_kle_ls%FOUND;
452 CLOSE c_kle_ls;
453 ELSIF (p_effective_date_operator = G_EQUAL_TO) THEN
454
455 OPEN c_kle_eq(p_kle_id, p_effective_date);
456 FETCH c_kle_eq INTO l_dummy;
457 l_row_found := c_kle_eq%FOUND;
458 CLOSE c_kle_eq;
459
460 ELSIF (p_effective_date_operator = G_GREATER_THAN_EQUAL_TO) THEN
461
462 OPEN c_kle_def(p_kle_id, p_effective_date);
463 FETCH c_kle_def INTO l_dummy;
464 l_row_found := c_kle_def%FOUND;
465 CLOSE c_kle_def;
466
467 END IF;
468
469 IF l_row_found THEN
470 x_value := Okl_Api.G_TRUE;
471 ELSE
472 x_value := Okl_Api.G_FALSE;
473 END IF;
474
475 RETURN x_value;
476 EXCEPTION
477 WHEN OTHERS THEN
478 --l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
479
480 Okl_Api.Set_Message(p_app_name => Okl_Api.G_APP_NAME,
481 p_msg_name => 'OKL_UNEXPECTED_ERROR',
482 p_token1 => 'OKL_SQLCODE',
483 p_token1_value => SQLCODE,
484 p_token2 => 'OKL_SQLERRM',
485 p_token2_value => SQLERRM);
486 RETURN 0;
487
488 END is_kle_securitized;
489
490
491 -----------------------------------------------------------------------
492 -- Start of comments
493 --
494 -- Procedure Name : check_kle_securitized
495 -- Description : Checks if an Asset is securitized on the given date
496 -- Business Rules :
497 -- Parameters :
498 -- : x_value: OKL_API.G_TRUE: true, OKL_API.G_FALSE false,
499 -- Version : 1.0
500 -- End of comments
501 -----------------------------------------------------------------------
502 PROCEDURE check_kle_securitized(
503 p_api_version IN NUMBER
504 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
505 ,x_return_status OUT NOCOPY VARCHAR2
506 ,x_msg_count OUT NOCOPY NUMBER
507 ,x_msg_data OUT NOCOPY VARCHAR2
508 ,p_kle_id IN okc_k_lines_b.ID%TYPE
509 ,p_effective_date IN DATE
510 -- cklee 08-08-2003 11.5.10
511 ,p_effective_date_operator IN VARCHAR2 DEFAULT G_GREATER_THAN_EQUAL_TO
512 ,p_stream_type_subclass IN okl_strm_type_b.stream_type_subclass%TYPE DEFAULT NULL
513 -- cklee 08-08-2003 11.5.10
514 ,x_value OUT NOCOPY VARCHAR2
515 ,x_inv_agmt_chr_id_tbl OUT NOCOPY inv_agmt_chr_id_tbl_type
516 )
517 IS
518 l_api_name CONSTANT VARCHAR2(30) := 'check_kle_securitized_pvt';
519 l_api_version CONSTANT NUMBER := 1.0;
520 i NUMBER;
521 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
522
523 CURSOR c_inv_khr(p_kle_id okc_k_lines_b.id%TYPE) IS
524 SELECT ph.khr_id -- inv agreemnet id
525 FROM okl_pools ph
526 WHERE EXISTS (SELECT '1'
527 FROM okl_pool_contents pl
528 WHERE pl.pol_id = ph.id
529 AND pl.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
530 AND pl.kle_id = p_kle_id) -- lease contract id
531 ;
532
533
534 BEGIN
535 -- Set API savepoint
536 SAVEPOINT check_kle_securitized_pvt;
537
538 -- Check for call compatibility
539 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
540 p_api_version,
541 l_api_name,
542 G_PKG_NAME ))
543 THEN
544 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
545 END IF;
546
547 -- Initialize message list if requested
548 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
549 Fnd_Msg_Pub.initialize;
550 END IF;
551
552 -- Initialize API status to success
553 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
554
555
556 /*** Begin API body ****************************************************/
557
558 -- 1. get amount
559 x_value := is_kle_securitized(
560 p_kle_id => p_kle_id
561 -- cklee 08-08-2003 11.5.10
562 ,p_effective_date_operator => p_effective_date_operator
563 ,p_stream_type_subclass => p_stream_type_subclass
564 -- cklee 08-08-2003 11.5.10
565 ,p_effective_date => p_effective_date);
566
567 -- 2. get investor agreement id
568 OPEN c_inv_khr(p_kle_id);
569 i := 0;
570 LOOP
571
572 FETCH c_inv_khr INTO
573 x_inv_agmt_chr_id_tbl(i).khr_id;
574
575 EXIT WHEN c_inv_khr%NOTFOUND;
576
577 i := i+1;
578 END LOOP;
579 CLOSE c_inv_khr;
580
581 /*** End API body ******************************************************/
582
583 -- Get message count and if count is 1, get message info
584 Fnd_Msg_Pub.Count_And_Get
585 (p_count => x_msg_count,
586 p_data => x_msg_data);
587
588 EXCEPTION
589 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
590 ROLLBACK TO check_kle_securitized_pvt;
591 x_return_status := Okl_Api.G_RET_STS_ERROR;
592 Fnd_Msg_Pub.Count_And_Get
593 (p_count => x_msg_count,
594 p_data => x_msg_data);
595
596 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
597 ROLLBACK TO check_kle_securitized_pvt;
598 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
599 Fnd_Msg_Pub.Count_And_Get
600 (p_count => x_msg_count,
601
602 p_data => x_msg_data);
603
604 WHEN OTHERS THEN
605 ROLLBACK TO check_kle_securitized_pvt;
606 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
607 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
608 p_msg_name => G_UNEXPECTED_ERROR,
609 p_token1 => G_SQLCODE_TOKEN,
610 p_token1_value => SQLCODE,
611
612
613
614
615
616 p_token2 => G_SQLERRM_TOKEN,
617 p_token2_value => SQLERRM);
618 Fnd_Msg_Pub.Count_And_Get
619 (p_count => x_msg_count,
620 p_data => x_msg_data);
621
622 END check_kle_securitized;
623
624
625
626 -----------------------------------------------------------------------
627 -- Start of comments
628 -- mvasudev, 10/03/2003
629 -- Procedure Name : check_sty_securitized
630 -- Description : Checks if a StreamType is securitized on the given date
631 -- Business Rules :
632 -- Parameters :
633 -- : x_value: OKL_API.G_TRUE: true, OKL_API.G_FALSE false,
634 -- Version : 1.0
635 -- End of comments
636 -----------------------------------------------------------------------
637 PROCEDURE check_sty_securitized(
638 p_api_version IN NUMBER
639 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
640 ,x_return_status OUT NOCOPY VARCHAR2
641 ,x_msg_count OUT NOCOPY NUMBER
642 ,x_msg_data OUT NOCOPY VARCHAR2
643 ,p_khr_id IN okc_k_headers_b.ID%TYPE
644 ,p_effective_date IN DATE
645 ,p_effective_date_operator IN VARCHAR2 DEFAULT G_GREATER_THAN_EQUAL_TO
646 ,p_sty_id IN okl_strm_type_b.id%TYPE
647 ,x_value OUT NOCOPY VARCHAR2
648 ,x_inv_agmt_chr_id OUT NOCOPY NUMBER
649 )
650 IS
651
652 -- Cursor to check if sty is securitized ">" effective_date
653 CURSOR l_okl_sty_grt_csr IS
654 SELECT DISTINCT polb.khr_id
655 FROM okl_pool_contents pocb,
656 okl_pools polb,
657 okc_k_headers_b chrb,
658 okl_strm_type_b styb
659 WHERE pocb.pol_id = polb.id
660 AND polb.khr_id = chrb.id -- inv agreement
661 AND chrb.sts_code = G_STS_CODE_ACTIVE
662 AND pocb.khr_id = p_khr_id
663 AND TRUNC(NVL(pocb.streams_to_date,G_FINAL_DATE)) > TRUNC(p_effective_date)
664 AND pocb.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
665 AND pocb.sty_id = styb.id;
666
667 -- Cursor to check if sty is securitized "<" effective_date
668 CURSOR l_okl_sty_les_csr IS
669 SELECT DISTINCT polb.khr_id
670 FROM okl_pool_contents pocb,
671 okl_pools polb,
672 okc_k_headers_b chrb,
673 okl_strm_type_b styb
674 WHERE pocb.pol_id = polb.id
675 AND polb.khr_id = chrb.id -- inv agreement
676 AND chrb.sts_code = G_STS_CODE_ACTIVE
677 AND pocb.khr_id = p_khr_id
678 AND TRUNC(NVL(pocb.streams_to_date,G_FINAL_DATE)) < TRUNC(p_effective_date)
679 AND pocb.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
680 AND pocb.sty_id = styb.id;
681
682 -- Cursor to check if sty is securitized "=" effective_date
683 CURSOR l_okl_sty_eql_csr IS
684 SELECT DISTINCT polb.khr_id
685 FROM okl_pool_contents pocb,
686 okl_pools polb,
687 okc_k_headers_b chrb,
688 okl_strm_type_b styb
689 WHERE pocb.pol_id = polb.id
690 AND polb.khr_id = chrb.id -- inv agreement
691 AND chrb.sts_code = G_STS_CODE_ACTIVE
692 AND pocb.khr_id = p_khr_id
693 AND TRUNC(NVL(pocb.streams_to_date,G_FINAL_DATE)) = TRUNC(p_effective_date)
694 AND pocb.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
695 AND pocb.sty_id = styb.id;
696
697 -- Cursor to check if sty is securitized ">=" effective_date
698 CURSOR l_okl_sty_geq_csr IS
699 SELECT DISTINCT polb.khr_id
700 FROM okl_pool_contents pocb,
701 okl_pools polb,
702 okc_k_headers_b chrb,
703 okl_strm_type_b styb
704 WHERE pocb.pol_id = polb.id
705 AND polb.khr_id = chrb.id -- inv agreement
706 AND chrb.sts_code = G_STS_CODE_ACTIVE
707 AND pocb.khr_id = p_khr_id
708 AND TRUNC(NVL(pocb.streams_to_date,G_FINAL_DATE)) >= TRUNC(p_effective_date)
709 AND pocb.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
710 AND pocb.sty_id = styb.id;
711
712 l_api_name CONSTANT VARCHAR2(30) := 'check_sty_securitized';
713 l_api_version CONSTANT NUMBER := 1.0;
714 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
715
716 l_value VARCHAR2(1) := Okl_Api.G_FALSE;
717
718 BEGIN
719
720 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
721 p_pkg_name => G_PKG_NAME,
722 p_init_msg_list => p_init_msg_list,
723 l_api_version => l_api_version,
724 p_api_version => p_api_version,
725 p_api_type => G_API_TYPE,
726 x_return_status => l_return_status);
727
728 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
729 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
730 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
731 RAISE G_EXCEPTION_ERROR;
732 END IF;
733
734 IF (p_effective_date_operator = G_GREATER_THAN) THEN
735 FOR l_okl_sty_grt_rec IN l_okl_sty_grt_csr
736 LOOP
737 l_value := Okl_Api.G_TRUE;
738 x_inv_agmt_chr_id := l_okl_sty_grt_rec.khr_id;
739 END LOOP;
740 ELSIF (p_effective_date_operator = G_LESS_THAN) THEN
741 FOR l_okl_sty_les_rec IN l_okl_sty_les_csr
742 LOOP
743 l_value := Okl_Api.G_TRUE;
744 x_inv_agmt_chr_id := l_okl_sty_les_rec.khr_id;
745 END LOOP;
746 ELSIF (p_effective_date_operator = G_EQUAL_TO) THEN
747 FOR l_okl_sty_eql_rec IN l_okl_sty_eql_csr
748 LOOP
749 l_value := Okl_Api.G_TRUE;
750 x_inv_agmt_chr_id := l_okl_sty_eql_rec.khr_id;
751 END LOOP;
752 ELSIF (p_effective_date_operator = G_GREATER_THAN_EQUAL_TO) THEN
753 FOR l_okl_sty_geq_rec IN l_okl_sty_geq_csr
754 LOOP
755 l_value := Okl_Api.G_TRUE;
756 x_inv_agmt_chr_id := l_okl_sty_geq_rec.khr_id;
757 END LOOP;
758 END IF; -- p_effective_date_operator
759
760 x_value := l_value;
761
762 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count
763 ,x_msg_data => x_msg_data);
764
765 x_return_status := l_return_status;
766
767 EXCEPTION
768 WHEN G_EXCEPTION_ERROR THEN
769
770 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
771 p_pkg_name => G_PKG_NAME,
772 p_exc_name => G_EXC_NAME_ERROR,
773 x_msg_count => x_msg_count,
774 x_msg_data => x_msg_data,
775 p_api_type => G_API_TYPE);
776
777 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
778
779 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
780 p_pkg_name => G_PKG_NAME,
781 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
782 x_msg_count => x_msg_count,
783 x_msg_data => x_msg_data,
784 p_api_type => G_API_TYPE);
785 WHEN OTHERS THEN
786
787 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
788 p_pkg_name => G_PKG_NAME,
789 p_exc_name => G_EXC_NAME_OTHERS,
790 x_msg_count => x_msg_count,
791 x_msg_data => x_msg_data,
792 p_api_type => G_API_TYPE);
793 END check_sty_securitized;
794
795 ----------------------------------------------------------------------------------
796 -- Start of comments
797 --
798 -- Procedure Name : is_stm_securitized
799 -- Description : Checks if any of the Streams Element under a streams header is securitized
800 -- Business Rules :
801 -- Parameters :
802 -- : return: OKL_API.G_TRUE: true, OKL_API.G_FALSE false,
803 -- OKL_API.G_RET_STS_ERROR, OKL_API.G_RET_STS_UNEXP_ERROR
804 -- Version : 1.0
805
806 -- End of comments
807 ----------------------------------------------------------------------------------
808 FUNCTION is_stm_securitized(
809 p_stm_id IN okl_streams.ID%TYPE
810 ,p_effective_date IN DATE
811 ) RETURN VARCHAR
812 IS
813 l_dummy VARCHAR2(1);
814 x_value VARCHAR2(1) := Okl_Api.G_FALSE;
815 l_row_found BOOLEAN := FALSE;
816
817 CURSOR c_stm(p_stm_id okl_streams.ID%TYPE, p_effective_date DATE) IS
818 SELECT '1'
819 FROM okl_streams strm
820 ,okl_pool_contents pl
821 ,okl_pools ph
822 ,okc_k_headers_b khr
823 WHERE -- mvasudev, stm_id changes
824 /*
825 strm.KHR_ID = pl.KHR_ID
826 AND strm.KLE_ID = pl.KLE_ID
827 AND strm.STY_ID = pl.STY_ID
828 */
829 pl.STM_ID = strm.id
830 -- end, mvasudev, stm_id changes
831 AND strm.say_code = 'CURR'
832 AND strm.active_yn = 'Y'
833 AND pl.pol_id = ph.id
834 AND ph.khr_id = khr.id -- inv agreement
835 AND khr.sts_code = G_STS_CODE_ACTIVE
836 AND strm.id = p_stm_id
837 --AND pl.streams_from_date <= p_effective_date
838 AND TRUNC(NVL(pl.streams_to_date,G_FINAL_DATE)) >= TRUNC(p_effective_date)
839 AND pl.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
840 ;
841
842
843 BEGIN
844
845 OPEN c_stm(p_stm_id, p_effective_date);
846 FETCH c_stm INTO l_dummy;
847 l_row_found := c_stm%FOUND;
848 CLOSE c_stm;
849
850 IF l_row_found THEN
851 x_value := Okl_Api.G_TRUE;
852 ELSE
853 x_value := Okl_Api.G_FALSE;
854 END IF;
855
856 RETURN x_value;
857 EXCEPTION
858 WHEN OTHERS THEN
859 --l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
860 Okl_Api.Set_Message(p_app_name => Okl_Api.G_APP_NAME,
861 p_msg_name => 'OKL_UNEXPECTED_ERROR',
862 p_token1 => 'OKL_SQLCODE',
863 p_token1_value => SQLCODE,
864 p_token2 => 'OKL_SQLERRM',
865 p_token2_value => SQLERRM);
866 RETURN 0;
867
868 END is_stm_securitized;
869
870 -----------------------------------------------------------------------
871 -- Start of comments
872 --
873 -- Procedure Name : check_stm_securitized
874 -- Description : Checks if any of the Streams Element under a streams header is securitized
875 -- Business Rules :
876 -- Parameters :
877 -- : x_value: OKL_API.G_TRUE: true, OKL_API.G_FALSE false,
878 -- Version : 1.0
879 -- End of comments
880 -----------------------------------------------------------------------
881 PROCEDURE check_stm_securitized(
882 p_api_version IN NUMBER
883 ,p_init_msg_list IN VARCHAR2
884 ,x_return_status OUT NOCOPY VARCHAR2
885 ,x_msg_count OUT NOCOPY NUMBER
886 ,x_msg_data OUT NOCOPY VARCHAR2
887 ,p_stm_id IN okl_streams.ID%TYPE
888 ,p_effective_date IN DATE
889 ,x_value OUT NOCOPY VARCHAR2
890 )
891 IS
892 l_api_name CONSTANT VARCHAR2(30) := 'check_stm_securitized_pvt';
893 l_api_version CONSTANT NUMBER := 1.0;
894 i NUMBER;
895 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
896
897 BEGIN
898 -- Set API savepoint
899 SAVEPOINT check_stm_securitized_pvt;
900
901 -- Check for call compatibility
902 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
903 p_api_version,
904 l_api_name,
905 G_PKG_NAME ))
906 THEN
907 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
908 END IF;
909
910 -- Initialize message list if requested
911 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
912 Fnd_Msg_Pub.initialize;
913 END IF;
914
915 -- Initialize API status to success
916 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
917
918
919 /*** Begin API body ****************************************************/
920
921 x_value := is_stm_securitized(p_stm_id => p_stm_id
922 ,p_effective_date => p_effective_date);
923
924 /*** End API body ******************************************************/
925
926 -- Get message count and if count is 1, get message info
927 Fnd_Msg_Pub.Count_And_Get
928 (p_count => x_msg_count,
929 p_data => x_msg_data);
930
931 EXCEPTION
932 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
933 ROLLBACK TO check_stm_securitized_pvt;
934 x_return_status := Okl_Api.G_RET_STS_ERROR;
935 Fnd_Msg_Pub.Count_And_Get
936 (p_count => x_msg_count,
937 p_data => x_msg_data);
938
939 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
940 ROLLBACK TO check_stm_securitized_pvt;
941 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
942 Fnd_Msg_Pub.Count_And_Get
943
944 (p_count => x_msg_count,
945 p_data => x_msg_data);
946
947 WHEN OTHERS THEN
948 ROLLBACK TO check_stm_securitized_pvt;
949 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
950 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
951 p_msg_name => G_UNEXPECTED_ERROR,
952 p_token1 => G_SQLCODE_TOKEN,
953 p_token1_value => SQLCODE,
954 p_token2 => G_SQLERRM_TOKEN,
955 p_token2_value => SQLERRM);
956 Fnd_Msg_Pub.Count_And_Get
957 (p_count => x_msg_count,
958 p_data => x_msg_data);
959 END check_stm_securitized;
960
961 ----------------------------------------------------------------------------------
962 -- Start of comments
963 --
964 -- Procedure Name : is_sel_securitized
965 -- Description : Checks if passed in entity is securitized
966 -- Business Rules :
967 -- Parameters :
968 -- : return: OKL_API.G_TRUE: true, OKL_API.G_FALSE false,
969 -- OKL_API.G_RET_STS_ERROR, OKL_API.G_RET_STS_UNEXP_ERROR
970 -- Version : 1.0
971 -- End of comments
972 ----------------------------------------------------------------------------------
973 FUNCTION is_sel_securitized(
974 p_sel_id IN okl_strm_elements.ID%TYPE
975 ,p_effective_date IN DATE
976 ) RETURN VARCHAR
977 IS
978 l_dummy VARCHAR2(1);
979 x_value VARCHAR2(1) := Okl_Api.G_FALSE;
980 l_row_found BOOLEAN := FALSE;
981
982 CURSOR c_sel(p_sel_id okl_strm_elements.ID%TYPE, p_effective_date DATE) IS
983 SELECT '1'
984 FROM okl_streams strm
985 ,okl_strm_elements ele
986 ,okl_pool_contents pl
987 ,okl_pools ph
988 ,okc_k_headers_b khr
989 WHERE strm.id = ele.stm_id
990 -- mvasudev, stm_id changes
991 /*
992 AND strm.KHR_ID = pl.KHR_ID
993 AND strm.KLE_ID = pl.KLE_ID
994 AND strm.STY_ID = pl.STY_ID
995 */
996 -- end, mvasudev, stm_id changes
997 AND pl.stm_id = strm.id
998 AND strm.say_code = 'CURR'
999 AND strm.active_yn = 'Y'
1000 AND pl.pol_id = ph.id
1001 AND ph.khr_id = khr.id -- inv agreement
1002 AND khr.sts_code = G_STS_CODE_ACTIVE
1003 AND ele.id = p_sel_id
1004 --AND pl.streams_from_date <= p_effective_date
1005 AND TRUNC(NVL(pl.streams_to_date,G_FINAL_DATE)) >= TRUNC(p_effective_date)
1006 AND pl.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
1007 ;
1008
1009
1010 BEGIN
1011
1012 OPEN c_sel(p_sel_id, p_effective_date);
1013 FETCH c_sel INTO l_dummy;
1014 l_row_found := c_sel%FOUND;
1015 CLOSE c_sel;
1016
1017 IF l_row_found THEN
1018 x_value := Okl_Api.G_TRUE;
1019 ELSE
1020 x_value := Okl_Api.G_FALSE;
1021 END IF;
1022
1023 RETURN x_value;
1024 EXCEPTION
1025 WHEN OTHERS THEN
1026 --l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1027 Okl_Api.Set_Message(p_app_name => Okl_Api.G_APP_NAME,
1028 p_msg_name => 'OKL_UNEXPECTED_ERROR',
1029 p_token1 => 'OKL_SQLCODE',
1030 p_token1_value => SQLCODE,
1031 p_token2 => 'OKL_SQLERRM',
1032 p_token2_value => SQLERRM);
1033 RETURN 0;
1034
1035 END is_sel_securitized;
1036
1037 -------------------------------------------------------------------------
1038 -- Start of comments
1039 --
1040 -- Procedure Name : check_sel_securitized
1041 -- Description : Checks if a Stream Element is securitized
1042 -- Business Rules :
1043 -- Parameters :
1044 -- : x_value: OKL_API.G_TRUE: true, OKL_API.G_FALSE false,
1045 -- Version : 1.0
1046 -- End of comments
1047 -----------------------------------------------------------------------
1048 PROCEDURE check_sel_securitized(
1049 p_api_version IN NUMBER
1050 ,p_init_msg_list IN VARCHAR2
1051 ,x_return_status OUT NOCOPY VARCHAR2
1052 ,x_msg_count OUT NOCOPY NUMBER
1053 ,x_msg_data OUT NOCOPY VARCHAR2
1054 ,p_sel_id IN okl_strm_elements.ID%TYPE
1055 ,p_effective_date IN DATE
1056 ,x_value OUT NOCOPY VARCHAR2
1057 )
1058 IS
1059 l_api_name CONSTANT VARCHAR2(30) := 'check_sel_securitized_pvt';
1060 l_api_version CONSTANT NUMBER := 1.0;
1061 i NUMBER;
1062 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1063
1064 BEGIN
1065 -- Set API savepoint
1066 SAVEPOINT check_sel_securitized_pvt;
1067
1068 -- Check for call compatibility
1069 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
1070 p_api_version,
1071 l_api_name,
1072 G_PKG_NAME ))
1073 THEN
1074 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1075 END IF;
1076
1077 -- Initialize message list if requested
1078 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
1079 Fnd_Msg_Pub.initialize;
1080 END IF;
1081
1082 -- Initialize API status to success
1083 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1084
1085
1086 /*** Begin API body ****************************************************/
1087
1088 x_value := is_sel_securitized(p_sel_id => p_sel_id
1089 ,p_effective_date => p_effective_date);
1090
1091 /*** End API body ******************************************************/
1092
1093 -- Get message count and if count is 1, get message info
1094
1095 Fnd_Msg_Pub.Count_And_Get
1096 (p_count => x_msg_count,
1097 p_data => x_msg_data);
1098
1099 EXCEPTION
1100 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1101
1102 ROLLBACK TO check_sel_securitized_pvt;
1103 x_return_status := Okl_Api.G_RET_STS_ERROR;
1104 Fnd_Msg_Pub.Count_And_Get
1105 (p_count => x_msg_count,
1106 p_data => x_msg_data);
1107
1108 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1109 ROLLBACK TO check_sel_securitized_pvt;
1110 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1111 Fnd_Msg_Pub.Count_And_Get
1112 (p_count => x_msg_count,
1113 p_data => x_msg_data);
1114
1115 WHEN OTHERS THEN
1116 ROLLBACK TO check_sel_securitized_pvt;
1117 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
1118 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
1119 p_msg_name => G_UNEXPECTED_ERROR,
1120 p_token1 => G_SQLCODE_TOKEN,
1121 p_token1_value => SQLCODE,
1122 p_token2 => G_SQLERRM_TOKEN,
1123 p_token2_value => SQLERRM);
1124 Fnd_Msg_Pub.Count_And_Get
1125 (p_count => x_msg_count,
1126 p_data => x_msg_data);
1127 END check_sel_securitized;
1128
1129 ----------------------------------------------------------------------------------
1130 -- Start of comments
1131 --
1132 -- Procedure Name : check_khr_ia_associated
1133 -- Description : Utility API for Accounting and rest of okl to check whether
1134 -- a contract is associated with investor agreement.
1135 -- Business Rules :
1136 -- Parameters :
1137 -- Version : 1.0
1138 -- End of comments
1139 ----------------------------------------------------------------------------------
1140 PROCEDURE check_khr_ia_associated(
1141 p_api_version IN NUMBER
1142 ,p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE
1143 ,x_return_status OUT NOCOPY VARCHAR2
1144 ,x_msg_count OUT NOCOPY NUMBER
1145 ,x_msg_data OUT NOCOPY VARCHAR2
1146 ,p_khr_id IN NUMBER
1147 ,p_scs_code IN okc_k_headers_b.scs_code%TYPE DEFAULT NULL
1148 ,p_trx_date IN DATE
1149 ,x_fact_synd_code OUT NOCOPY fnd_lookups.lookup_code%TYPE
1150 ,x_inv_acct_code OUT NOCOPY okc_rules_b.RULE_INFORMATION1%TYPE
1151 )
1152 IS
1153 l_api_name CONSTANT VARCHAR2(30) := 'check_khr_ia_associated';
1154 l_api_version CONSTANT NUMBER := 1.0;
1155 i NUMBER;
1156 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1157 l_trx_date DATE; -- cklee fixed bug: 7017824(R12)/OKL.H bug#6964174
1158
1159 CURSOR investor_code (p_ia_chr_id IN NUMBER) IS
1160 SELECT rule_information1
1161 FROM okc_rules_b
1162 WHERE dnz_chr_id = p_ia_chr_id
1163 AND RULE_INFORMATION_CATEGORY='LASEAC';
1164 l_investor_code okc_rules_b.rule_information1%TYPE;
1165
1166 CURSOR fact_synd_code IS
1167 SELECT lookup_code
1168 FROM fnd_lookups
1169 WHERE lookup_type = 'OKL_FACTORING_SYNDICATION'
1170 AND lookup_code = 'INVESTOR';
1171 l_fact_synd_code fnd_lookups.lookup_code%TYPE;
1172
1173 CURSOR ia_number (p_chr_id IN NUMBER, p_trx_date IN DATE)IS
1174 SELECT COUNT(khr_id)
1175 FROM okl_pools
1176 WHERE id IN
1177 (SELECT pol_id
1178 FROM okl_pool_contents
1179 WHERE khr_id = p_chr_id
1180 AND TRUNC(streams_from_date) <= TRUNC(p_trx_date) -- cklee 05/29/08
1181 AND (TRUNC(streams_to_date) >= TRUNC(p_trx_date) OR -- cklee 05/29/08
1182 streams_to_date IS NULL)
1183 AND STATUS_CODE ='ACTIVE' -- 5/29/08 cklee fixed bug: 6862849 (R12)/OKL.H # snandiko 6857723
1184 );
1185 l_count NUMBER;
1186
1187 CURSOR ia_id (p_chr_id IN NUMBER, p_trx_date IN DATE)IS
1188 SELECT khr_id
1189 FROM okl_pools
1190 WHERE id IN
1191 (SELECT pol_id
1192 FROM okl_pool_contents
1193 WHERE khr_id = p_chr_id
1194 AND TRUNC(streams_from_date) <= TRUNC(p_trx_date) -- cklee 05/29/08
1195 AND (TRUNC(streams_to_date) >= TRUNC(p_trx_date) OR -- cklee 05/29/08
1196 streams_to_date IS NULL)
1197 AND STATUS_CODE ='ACTIVE' -- 5/29/08 cklee fixed bug: 6862849 (R12)/OKL.H # snandiko 6857723
1198 );
1199 l_khr_id NUMBER;
1200 --start:| 05-29-08 cklee -- fixed bug: 6932520(R12)/OKL.H: bug#6869289 |
1201 -- shagarg bug 6869289 start
1202 -- cursor to get all pools to which contract is attached
1203 CURSOR csr_get_all_assoc_ia(p_chr_id IN NUMBER) IS
1204 SELECT ia.ID ia_id, ia.START_DATE ia_start_date,op.ID pool_id,opc.STY_CODE strm_type,
1205 opc.streams_from_date strm_from_date, opc.streams_to_date strm_to_date
1206 FROM okc_k_headers_b ia,okl_pools op, okl_pool_contents opc
1207 WHERE ia.ID = op.khr_id
1208 AND op.id = opc.pol_id
1209 AND opc.khr_id = p_chr_id
1210 AND opc.STATUS_CODE ='ACTIVE'
1211 AND ia.scs_code = 'INVESTOR'
1212 ORDER by strm_from_date , ia_start_date;
1213 -- shagarg bug 6869289 end
1214 --end:| 05-29-08 cklee -- fixed bug: 6932520(R12)/OKL.H: bug#6869289 |
1215
1216 CURSOR scs_code (p_chr_id IN NUMBER) IS
1217 SELECT scs_code
1218 FROM okc_k_headers_b
1219 WHERE id = p_chr_id;
1220 l_scs_code okc_k_headers_b.scs_code%TYPE;
1221
1222 BEGIN
1223 -- Set API savepoint
1224 SAVEPOINT check_khr_ia_associated_pvt;
1225
1226 -- Check for call compatibility
1227 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
1228 p_api_version,
1229 l_api_name,
1230 G_PKG_NAME ))
1231 THEN
1232 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1233 END IF;
1234
1235 -- Initialize message list if requested
1236 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
1237 Fnd_Msg_Pub.initialize;
1238 END IF;
1239
1240 -- Initialize API status to success
1241 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1242
1243 /*** Begin API body ****************************************************/
1244
1245 -- scs_code is INVESTOR, return null
1246 x_fact_synd_code := NULL;
1247 x_inv_acct_code := NULL;
1248
1249 IF (p_scs_code IS NULL) THEN
1250 OPEN scs_code (p_khr_id);
1251 FETCH scs_code INTO l_scs_code;
1252 CLOSE scs_code;
1253 ELSE
1254 l_scs_code := p_scs_code;
1255 END IF;
1256
1257 IF (l_scs_code = 'LEASE')THEN
1258 --start:| 05-29-08 cklee -- fixed bug: 6932520(R12)/OKL.H: bug#6869289 |
1259 -- shagarg commented for bug 6869289
1260 /*
1261 OPEN ia_number (p_khr_id, p_trx_date);
1262 FETCH ia_number INTO l_count;
1263 CLOSE ia_number;
1264
1265 IF (l_count > 0) THEN
1266 OPEN fact_synd_code;
1267 FETCH fact_synd_code INTO l_fact_synd_code;
1268 CLOSE fact_synd_code;
1269 IF (l_fact_synd_code IS NOT NULL) THEN
1270 x_fact_synd_code := l_fact_synd_code;
1271 ELSE
1272 RAISE G_EXCEPTION_ERROR;
1273 END IF;
1274 END IF;
1275
1276 IF (l_count = 1) THEN
1277 OPEN ia_id (p_khr_id, p_trx_date);
1278 FETCH ia_id INTO l_khr_id;
1279 CLOSE ia_id;
1280
1281 OPEN investor_code (l_khr_id);
1282 FETCH investor_code INTO l_investor_code;
1283 CLOSE investor_code;
1284 IF (l_investor_code IS NOT NULL) THEN
1285 x_inv_acct_code := l_investor_code;
1286 END IF;
1287 -- acct code not required field, return null if not present
1288 END IF;*/
1289 -- shagarg bug 6869289 start
1290 l_trx_date := TRUNC(p_trx_date); -- cklee fixed bug: 7017824(R12)/OKL.H bug#6964174-- added Bug# 6964174 to ensure time is not considered for accounting -- get all pools to which contract is securitized
1291 for l_all_assoc_ia_rec in csr_get_all_assoc_ia(p_khr_id)
1292 loop
1293 -- If the trans date falls b/w the pool contents dates, pick the IA associated with this pool
1294 -- to get special accounting code. If the trans date does not fall b/w pool contents dates, chk if it
1295 -- falls b/w IA start date and pool content strm element start dates,If so, use this Ia to get spcl accounting code
1296 -- as the trans is happening after the IA effective date. Else do regular accounting.
1297 IF(l_all_assoc_ia_rec.strm_from_date <= l_trx_date
1298 AND (l_all_assoc_ia_rec.strm_to_date >= l_trx_date OR l_all_assoc_ia_rec.strm_to_date IS NULL))
1299 --
1300 OR (l_all_assoc_ia_rec.ia_start_date <= l_trx_date AND l_trx_date <= l_all_assoc_ia_rec.strm_from_date )
1301 THEN
1302 OPEN fact_synd_code;
1303 FETCH fact_synd_code INTO l_fact_synd_code;
1304 CLOSE fact_synd_code;
1305 IF (l_fact_synd_code IS NOT NULL) THEN
1306 x_fact_synd_code := l_fact_synd_code;
1307 ELSE
1308 RAISE G_EXCEPTION_ERROR;
1309 END IF;
1310
1311 OPEN investor_code (l_all_assoc_ia_rec.ia_id);
1312 FETCH investor_code INTO l_investor_code;
1313 CLOSE investor_code;
1314 IF (l_investor_code IS NOT NULL) THEN
1315 x_inv_acct_code := l_investor_code;
1316 END IF;
1317 return;
1318 END IF;
1319 end loop;
1320 -- shagarg bug 6869289 end
1321 --end:| 05-29-08 cklee -- fixed bug: 6932520(R12)/OKL.H: bug#6869289 |
1322 END IF;
1323
1324 /*** End API body ******************************************************/
1325
1326 -- Get message count and if count is 1, get message info
1327
1328 Fnd_Msg_Pub.Count_And_Get
1329 (p_count => x_msg_count,
1330 p_data => x_msg_data);
1331
1332 EXCEPTION
1333 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1334 ROLLBACK TO check_khr_ia_associated_pvt;
1335 x_return_status := Okl_Api.G_RET_STS_ERROR;
1336 Fnd_Msg_Pub.Count_And_Get
1337 (p_count => x_msg_count,
1338 p_data => x_msg_data);
1339
1340 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1341 ROLLBACK TO check_khr_ia_associated_pvt;
1342 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1343 Fnd_Msg_Pub.Count_And_Get
1344 (p_count => x_msg_count,
1345 p_data => x_msg_data);
1346
1347 WHEN OTHERS THEN
1348 ROLLBACK TO check_khr_ia_associated_pvt;
1349 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
1350 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
1351 p_msg_name => G_UNEXPECTED_ERROR,
1352 p_token1 => G_SQLCODE_TOKEN,
1353 p_token1_value => SQLCODE,
1354 p_token2 => G_SQLERRM_TOKEN,
1355 p_token2_value => SQLERRM);
1356 Fnd_Msg_Pub.Count_And_Get
1357 (p_count => x_msg_count,
1358 p_data => x_msg_data);
1359 END check_khr_ia_associated;
1360
1361 ----------------------------------------------------------------------------------
1362 -- Start of comments
1363 --
1364 -- Procedure Name : buyback_asset
1365 -- Description : Automatically buy back stream elements based on passed in kle_id
1366 -- Business Rules :
1367 -- Parameters :
1368 -- Version : 1.0
1369 -- End of comments
1370 ----------------------------------------------------------------------------------
1371 PROCEDURE buyback_asset(
1372 p_api_version IN NUMBER
1373 ,p_init_msg_list IN VARCHAR2
1374 ,x_return_status OUT NOCOPY VARCHAR2
1375 ,x_msg_count OUT NOCOPY NUMBER
1376 ,x_msg_data OUT NOCOPY VARCHAR2
1377 ,p_kle_id IN OKC_K_LINES_B.ID%TYPE
1378 ,p_effective_date IN DATE
1379 )
1380 IS
1381 BEGIN
1382 NULL;
1383 -- mvasudev, Commented until future use discovered, 4/28/2003
1384 END BUYBACK_ASSET;
1385
1386 ----------------------------------------------------------------------------------
1387 -- Start of comments
1388 --
1389 -- Procedure Name : buyback_contract
1390 -- Description : Automatically buy back stream elements based on passed in khr_id
1391 -- Business Rules :
1392 -- Parameters :
1393 -- Version : 1.0
1394 -- End of comments
1395 ----------------------------------------------------------------------------------
1396 PROCEDURE buyback_contract(
1397 p_api_version IN NUMBER
1398 ,p_init_msg_list IN VARCHAR2
1399 ,x_return_status OUT NOCOPY VARCHAR2
1400 ,x_msg_count OUT NOCOPY NUMBER
1401 ,x_msg_data OUT NOCOPY VARCHAR2
1402 ,p_khr_id IN OKC_K_HEADERS_B.ID%TYPE
1403 ,p_effective_date IN DATE
1404 )
1405 IS
1406 BEGIN
1407 NULL;
1408 -- mvasudev, Commented until future use discovered, 9/17/2003
1409 END buyback_contract;
1410
1411
1412
1413
1414 -------------------------------------------------------------------------------------------------------
1415 -- Start of comments
1416 --
1417 -- Procedure Name : process_investor_rules
1418 -- Description : checks the Buyback rule at the Investor Agreement and performs Buyback if required
1419 -- Business Rules :
1420 -- Parameters :
1421 -- Version : 1.0
1422 -- End of comments
1423 ---------------------------------------------------------------------------------------------------------
1424 PROCEDURE process_khr_investor_rules(
1425 p_api_version IN NUMBER
1426 ,p_init_msg_list IN VARCHAR2
1427 ,x_return_status OUT NOCOPY VARCHAR2
1428 ,x_msg_count OUT NOCOPY NUMBER
1429 ,x_msg_data OUT NOCOPY VARCHAR2
1430 ,p_khr_id IN okc_k_headers_b.ID%TYPE
1431 ,p_effective_date IN DATE
1432 ,p_rgd_code IN VARCHAR2
1433 ,p_rdf_code IN VARCHAR2
1434 ,x_process_code OUT NOCOPY VARCHAR2
1435 ,x_inv_agmt_chr_id_tbl OUT NOCOPY inv_agmt_chr_id_tbl_type
1436 )
1437 IS
1438 l_api_name CONSTANT VARCHAR2(30) := 'process_khr_investor_rules';
1439 l_api_version CONSTANT NUMBER := 1.0;
1440 i NUMBER;
1441 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1442
1443 l_flag VARCHAR2(1) := Okl_Api.G_FALSE;
1444 l_action NUMBER;
1445
1446 CURSOR c_action(p_rgd_code okc_rule_groups_b.rgd_code%TYPE,
1447 p_rdf_code okc_rules_b.RULE_INFORMATION_CATEGORY%TYPE,
1448 p_khr_id okc_k_headers_b.id%TYPE) IS
1449 SELECT MIN(DECODE(rg.RULE_INFORMATION1, G_PROCESS_AUTO_BACK_BACK, G_PRIORITY_2,
1450 G_PROCESS_NOT_ALLOWED, G_PRIORITY_1,
1451 G_PRIORITY_2))
1452 FROM okc_rule_groups_b rgd,
1453 okc_rules_b rg
1454 WHERE rgd.id = rg.rgp_id
1455 AND rgd.rgd_code = p_rgd_code
1456 AND rg.RULE_INFORMATION_CATEGORY = p_rdf_code -- 'LASEPR'
1457 AND EXISTS -- investor agreement Ids
1458 (SELECT '1'
1459 FROM okl_pools ph
1460
1461 ,okl_pool_contents pl
1462 WHERE ph.id = pl.pol_id
1463 AND pl.khr_id = p_khr_id -- lease contract id
1464 AND pl.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
1465 AND ph.khr_id = rg.dnz_chr_id) -- investor agreement contarct id
1466 ;
1467
1468
1469 CURSOR c_inv_khr(p_rgd_code okc_rule_groups_b.rgd_code%TYPE,
1470 p_rdf_code okc_rules_b.RULE_INFORMATION_CATEGORY%TYPE,
1471 p_khr_id okc_k_headers_b.id%TYPE) IS
1472 SELECT rgd.dnz_chr_id,
1473 rg.RULE_INFORMATION1
1474 FROM okc_rule_groups_b rgd,
1475 okc_rules_b rg
1476 WHERE rgd.id = rg.rgp_id
1477 AND rgd.rgd_code = p_rgd_code
1478 AND rg.RULE_INFORMATION_CATEGORY = p_rdf_code -- 'LASEPR'
1479 AND EXISTS -- investor agreement Ids
1480 (SELECT '1'
1481 FROM okl_pools ph
1482 ,okl_pool_contents pl
1483 WHERE ph.id = pl.pol_id
1484 AND pl.khr_id = p_khr_id -- lease contract id
1485 AND pl.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
1486 AND ph.khr_id = rg.dnz_chr_id) -- investor agreement contarct id
1487 ;
1488
1489 BEGIN
1490 -- Set API savepoint
1491 SAVEPOINT process_khr_investor_rules;
1492
1493 -- Check for call compatibility
1494 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
1495 p_api_version,
1496 l_api_name,
1497 G_PKG_NAME ))
1498 THEN
1499 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1500 END IF;
1501
1502 -- Initialize message list if requested
1503 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
1504 Fnd_Msg_Pub.initialize;
1505 END IF;
1506
1507 -- Initialize API status to success
1508 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1509
1510
1511 /*** Begin API body ****************************************************/
1512 /*
1513 Algorithm:
1514
1515 - the calling process passes asset/contract id and the rule group code
1516 - identify the POOLS in which the given asset/contract is securitized - check for only active pools/agreements
1517 - for each pool get the corresponding investor agreement id
1518 - using the investor agreement id and rule group code, get the RULE value
1519 - apply the RULE hierarchy logic ( not allow > buy back)
1520 - execute the rule
1521 - return
1522 */
1523
1524 -- 1. get flag
1525 l_flag := is_khr_securitized(p_khr_id => p_khr_id
1526 ,p_effective_date => p_effective_date);
1527
1528 IF l_flag = Okl_Api.G_TRUE THEN
1529
1530 OPEN c_action(p_rgd_code
1531 ,NVL(p_rdf_code, G_PROCESS_RULE_CODE)
1532
1533 ,p_khr_id);
1534 FETCH c_action INTO l_action;
1535 CLOSE c_action;
1536
1537 IF l_action = G_PRIORITY_2 THEN
1538
1539 x_process_code := G_PROCESS_AUTO_BACK_BACK;
1540 -- auto buyback
1541 buyback_contract(
1542 p_api_version => p_api_version,
1543 p_init_msg_list => p_init_msg_list,
1544 x_return_status => x_return_status,
1545 x_msg_count => x_msg_count,
1546 x_msg_data => x_msg_data,
1547 p_khr_id => p_khr_id,
1548 p_effective_date => p_effective_date);
1549
1550 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1551 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1552 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1553 RAISE Okl_Api.G_EXCEPTION_ERROR;
1554 END IF;
1555 -- set message
1556
1557 ELSIF l_action = G_PRIORITY_1 THEN
1558 x_process_code := G_PROCESS_NOT_ALLOWED;
1559 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
1560 p_msg_name => 'OKL_LLA_BUYBACK_NOT_ALLOWED');
1561
1562 END IF;
1563
1564 -- 2. fill in information table
1565 OPEN c_inv_khr(p_rgd_code
1566 ,NVL(p_rdf_code, G_PROCESS_RULE_CODE)
1567 ,p_khr_id);
1568 i := 0;
1569 LOOP
1570
1571 FETCH c_inv_khr INTO
1572 x_inv_agmt_chr_id_tbl(i).khr_id,
1573 x_inv_agmt_chr_id_tbl(i).process_code;
1574
1575 EXIT WHEN c_inv_khr%NOTFOUND;
1576
1577 i := i+1;
1578 END LOOP;
1579 CLOSE c_inv_khr;
1580
1581 END IF;
1582 /*** End API body ******************************************************/
1583
1584 -- Get message count and if count is 1, get message info
1585
1586
1587 Fnd_Msg_Pub.Count_And_Get
1588 (p_count => x_msg_count,
1589 p_data => x_msg_data);
1590
1591 EXCEPTION
1592 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1593 ROLLBACK TO process_khr_investor_rules;
1594 x_return_status := Okl_Api.G_RET_STS_ERROR;
1595 Fnd_Msg_Pub.Count_And_Get
1596 (p_count => x_msg_count,
1597 p_data => x_msg_data);
1598
1599 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1600 ROLLBACK TO process_khr_investor_rules;
1601 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1602 Fnd_Msg_Pub.Count_And_Get
1603 (p_count => x_msg_count,
1604 p_data => x_msg_data);
1605
1606 WHEN OTHERS THEN
1607 ROLLBACK TO process_khr_investor_rules;
1608 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
1609 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
1610 p_msg_name => G_UNEXPECTED_ERROR,
1611 p_token1 => G_SQLCODE_TOKEN,
1612 p_token1_value => SQLCODE,
1613 p_token2 => G_SQLERRM_TOKEN,
1614 p_token2_value => SQLERRM);
1615 Fnd_Msg_Pub.Count_And_Get
1616 (p_count => x_msg_count,
1617 p_data => x_msg_data);
1618
1619 END process_khr_investor_rules;
1620
1621
1622
1623 -------------------------------------------------------------------------------------------------------
1624 -- Start of comments
1625 --
1626 -- Procedure Name : process_investor_rules
1627 -- Description : checks the Buyback rule at the Investor Agreement and performs Buyback if required
1628 -- Business Rules :
1629 -- Parameters :
1630 -- Version : 1.0
1631 -- End of comments
1632 ---------------------------------------------------------------------------------------------------------
1633 PROCEDURE process_kle_investor_rules(
1634 p_api_version IN NUMBER
1635 ,p_init_msg_list IN VARCHAR2
1636 ,x_return_status OUT NOCOPY VARCHAR2
1637 ,x_msg_count OUT NOCOPY NUMBER
1638 ,x_msg_data OUT NOCOPY VARCHAR2
1639 ,p_kle_id IN okc_k_lines_b.ID%TYPE
1640 ,p_effective_date IN DATE
1641 ,p_rgd_code IN VARCHAR2
1642 ,p_rdf_code IN VARCHAR2
1643 ,x_process_code OUT NOCOPY VARCHAR2
1644 ,x_inv_agmt_chr_id_tbl OUT NOCOPY inv_agmt_chr_id_tbl_type
1645 )
1646 IS
1647 l_api_name CONSTANT VARCHAR2(30) := 'process_kle_investor_rules';
1648 l_api_version CONSTANT NUMBER := 1.0;
1649 i NUMBER;
1650 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1651
1652 l_flag VARCHAR2(1) := Okl_Api.G_FALSE;
1653 l_action NUMBER;
1654
1655 CURSOR c_action(p_rgd_code okc_rule_groups_b.rgd_code%TYPE,
1656 p_rdf_code okc_rules_b.RULE_INFORMATION_CATEGORY%TYPE,
1657 p_kle_id okc_k_lines_b.id%TYPE) IS
1658 SELECT MIN(DECODE(rg.RULE_INFORMATION1, G_PROCESS_AUTO_BACK_BACK, G_PRIORITY_2,
1659 G_PROCESS_NOT_ALLOWED, G_PRIORITY_1,
1660 G_PRIORITY_2))
1661 FROM okc_rule_groups_b rgd,
1662 okc_rules_b rg
1663 WHERE rgd.id = rg.rgp_id
1664 AND rgd.rgd_code = p_rgd_code
1665 AND rg.RULE_INFORMATION_CATEGORY = p_rdf_code -- 'LASEPR'
1666 AND EXISTS -- investor agreement Ids
1667 (SELECT '1'
1668 FROM okl_pools ph
1669 ,okl_pool_contents pl
1670 WHERE ph.id = pl.pol_id
1671 AND pl.kle_id = p_kle_id -- lease contract top line Id
1672 AND pl.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
1673 AND ph.khr_id = rg.dnz_chr_id) -- investor agreement contarct id
1674 ;
1675
1676
1677 CURSOR c_inv_khr(p_rgd_code okc_rule_groups_b.rgd_code%TYPE,
1678 p_rdf_code okc_rules_b.RULE_INFORMATION_CATEGORY%TYPE,
1679
1680 p_kle_id okc_k_lines_b.id%TYPE) IS
1681 SELECT rgd.dnz_chr_id,
1682 rg.RULE_INFORMATION1
1683 FROM okc_rule_groups_b rgd,
1684 okc_rules_b rg
1685 WHERE rgd.id = rg.rgp_id
1686 AND rgd.rgd_code = p_rgd_code
1687 AND rg.RULE_INFORMATION_CATEGORY = p_rdf_code -- 'LASEPR'
1688
1689 AND EXISTS -- investor agreement Ids
1690 (SELECT '1'
1691 FROM okl_pools ph
1692 ,okl_pool_contents pl
1693 WHERE ph.id = pl.pol_id
1694 AND pl.kle_id = p_kle_id -- lease contract top line Id
1695 AND pl.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
1696 AND ph.khr_id = rg.dnz_chr_id) -- investor agreement contarct id
1697 ;
1698
1699 BEGIN
1700 -- Set API savepoint
1701 SAVEPOINT process_kle_investor_rules;
1702
1703 -- Check for call compatibility
1704 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
1705 p_api_version,
1706 l_api_name,
1707 G_PKG_NAME ))
1708 THEN
1709 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1710 END IF;
1711
1712 -- Initialize message list if requested
1713 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
1714 Fnd_Msg_Pub.initialize;
1715 END IF;
1716
1717
1718 -- Initialize API status to success
1719 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1720
1721
1722 /*** Begin API body ****************************************************/
1723 /*
1724 Algorithm:
1725
1726 - the calling process passes asset/contract id and the rule group code
1727 - identify the POOLS in which the given asset/contract is securitized - check for only active pools/agreements
1728 - for each pool get the corresponding investor agreement id
1729 - using the investor agreement id and rule group code, get the RULE value
1730 - apply the RULE hierarchy logic ( not allow > buy back)
1731 - execute the rule
1732 - return
1733 */
1734
1735 -- 1. get flag
1736 l_flag := is_kle_securitized(p_kle_id => p_kle_id
1737 ,p_effective_date => p_effective_date);
1738
1739 IF l_flag = Okl_Api.G_TRUE THEN
1740
1741 OPEN c_action(p_rgd_code
1742 ,NVL(p_rdf_code, G_PROCESS_RULE_CODE)
1743 ,p_kle_id);
1744 FETCH c_action INTO l_action;
1745 CLOSE c_action;
1746
1747 IF l_action = G_PRIORITY_2 THEN
1748
1749 x_process_code := G_PROCESS_AUTO_BACK_BACK;
1750 -- auto buyback
1751 buyback_asset(
1752 p_api_version => p_api_version,
1753 p_init_msg_list => p_init_msg_list,
1754 x_return_status => x_return_status,
1755 x_msg_count => x_msg_count,
1756 x_msg_data => x_msg_data,
1757 p_kle_id => p_kle_id,
1758 p_effective_date => p_effective_date);
1759
1760 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1761 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1762 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1763 RAISE Okl_Api.G_EXCEPTION_ERROR;
1764 END IF;
1765 -- set message
1766
1767 ELSIF l_action = G_PRIORITY_1 THEN
1768 x_process_code := G_PROCESS_NOT_ALLOWED;
1769 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
1770 p_msg_name => 'OKL_LLA_BUYBACK_NOT_ALLOWED');
1771
1772 END IF;
1773
1774 -- 2. fill in information table
1775 OPEN c_inv_khr(p_rgd_code
1776 ,NVL(p_rdf_code, G_PROCESS_RULE_CODE)
1777 ,p_kle_id);
1778 i := 0;
1779 LOOP
1780
1781 FETCH c_inv_khr INTO
1782 x_inv_agmt_chr_id_tbl(i).khr_id,
1783 x_inv_agmt_chr_id_tbl(i).process_code;
1784
1785 EXIT WHEN c_inv_khr%NOTFOUND;
1786
1787 i := i+1;
1788 END LOOP;
1789 CLOSE c_inv_khr;
1790
1791 END IF;
1792 /*** End API body ******************************************************/
1793
1794 -- Get message count and if count is 1, get message info
1795 Fnd_Msg_Pub.Count_And_Get
1796 (p_count => x_msg_count,
1797 p_data => x_msg_data);
1798
1799 EXCEPTION
1800 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1801 ROLLBACK TO process_kle_investor_rules;
1802 x_return_status := Okl_Api.G_RET_STS_ERROR;
1803 Fnd_Msg_Pub.Count_And_Get
1804 (p_count => x_msg_count,
1805 p_data => x_msg_data);
1806
1807 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1808 ROLLBACK TO process_kle_investor_rules;
1809 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1810 Fnd_Msg_Pub.Count_And_Get
1811 (p_count => x_msg_count,
1812 p_data => x_msg_data);
1813
1814 WHEN OTHERS THEN
1815 ROLLBACK TO process_kle_investor_rules;
1816 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
1817 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
1818 p_msg_name => G_UNEXPECTED_ERROR,
1819 p_token1 => G_SQLCODE_TOKEN,
1820 p_token1_value => SQLCODE,
1821 p_token2 => G_SQLERRM_TOKEN,
1822 p_token2_value => SQLERRM);
1823 Fnd_Msg_Pub.Count_And_Get
1824 (p_count => x_msg_count,
1825 p_data => x_msg_data);
1826
1827 END process_kle_investor_rules;
1828
1829 /*
1830 PROCEDURE buyback_streams(
1831 p_api_version IN NUMBER
1832 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1833 ,p_khr_id IN okc_k_headers_b.ID%TYPE
1834 ,p_pol_id IN okl_pools.ID%TYPE
1835 ,p_stream_type_subclass IN okl_strm_type_b.stream_type_subclass%TYPE
1836 ,x_return_status OUT NOCOPY VARCHAR2
1837 ,x_msg_count OUT NOCOPY NUMBER
1838 ,x_msg_data OUT NOCOPY VARCHAR2)
1839 IS
1840 -- Collect Pool Contents
1841 CURSOR l_okl_poc_csr
1842 IS
1843 SELECT pocb.id
1844 FROM okl_pool_contents pocb
1845 ,okl_strm_type_b styb
1846 WHERE pocb.khr_id = p_khr_id
1847 AND pocb.pol_id = p_pol_id
1848 AND pocb.sty_id = styb.id
1849 AND styb.stream_type_subclass = p_stream_type_subclass;
1850
1851 l_api_name CONSTANT VARCHAR2(30) := 'buyback_streams';
1852 l_api_version CONSTANT NUMBER := 1.0;
1853 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1854
1855 BEGIN
1856
1857 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
1858 p_pkg_name => G_PKG_NAME,
1859 p_init_msg_list => p_init_msg_list,
1860 l_api_version => l_api_version,
1861 p_api_version => p_api_version,
1862 p_api_type => G_API_TYPE,
1863 x_return_status => l_return_status);
1864
1865 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1866 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1867 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1868 RAISE G_EXCEPTION_ERROR;
1869 END IF;
1870
1871 FOR l_okl_poc_rec IN l_okl_poc_csr
1872 LOOP
1873 buyback_pool_content(p_api_version => p_api_version
1874 ,p_init_msg_list => p_init_msg_list
1875 ,x_return_status => l_return_status
1876 ,x_msg_count => x_msg_count
1877 ,x_msg_data => x_msg_data
1878 ,p_poc_id => l_okl_poc_rec.id
1879 ,p_effective_date => SYSDATE);
1880
1881 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1882 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1883 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1884 RAISE G_EXCEPTION_ERROR;
1885 END IF;
1886
1887 END LOOP; -- l_okl_poc_csr
1888
1889 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count
1890 ,x_msg_data => x_msg_data);
1891
1892 x_return_status := l_return_status;
1893
1894
1895 EXCEPTION
1896 WHEN G_EXCEPTION_ERROR THEN
1897
1898 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1899 p_pkg_name => G_PKG_NAME,
1900 p_exc_name => G_EXC_NAME_ERROR,
1901 x_msg_count => x_msg_count,
1902 x_msg_data => x_msg_data,
1903 p_api_type => G_API_TYPE);
1904
1905 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1906
1907 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1908 p_pkg_name => G_PKG_NAME,
1909 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
1910 x_msg_count => x_msg_count,
1911 x_msg_data => x_msg_data,
1912 p_api_type => G_API_TYPE);
1913 WHEN OTHERS THEN
1914
1915 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1916 p_pkg_name => G_PKG_NAME,
1917 p_exc_name => G_EXC_NAME_OTHERS,
1918 x_msg_count => x_msg_count,
1919 x_msg_data => x_msg_data,
1920 p_api_type => G_API_TYPE);
1921
1922 END buyback_streams;
1923
1924 ----------------------------------------------------------------------------------
1925 -- Start of comments
1926 --
1927 -- Procedure Name : calculate_buyback_content
1928 -- Description : Calculate BuyBack amount for a given Pool Content
1929 -- Business Rules :
1930 -- Parameters :
1931 -- Version : 1.0
1932 -- End of comments
1933 ----------------------------------------------------------------------------------
1934 PROCEDURE calculate_buyback_content(
1935 p_api_version IN NUMBER
1936 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1937 ,p_poc_id IN NUMBER
1938 ,x_buyback_amount OUT NOCOPY NUMBER
1939 ,x_return_status OUT NOCOPY VARCHAR2
1940 ,x_msg_count OUT NOCOPY NUMBER
1941 ,x_msg_data OUT NOCOPY VARCHAR2
1942 )
1943 IS
1944
1945 CURSOR l_okl_poc_formula_csr(p_poc_id IN NUMBER)
1946 IS
1947 SELECT polb.khr_id khr_id -- Investor Agreement
1948 ,rulb.rule_information1 formula -- BuyBack Formula
1949 ,pocb.khr_id dnz_chr_id -- Lease Contract
1950 -- ,pocb.pol_id pol_id -- Pool
1951 ,pocb.kle_id kle_id -- Asset
1952 ,pocb.sty_id sty_id -- Stream Type
1953 FROM okl_pool_contents pocb
1954 ,okl_pools polb
1955 ,okc_rules_b rulb
1956 ,okc_rule_groups_v rgpb
1957 WHERE pocb.id = p_poc_id
1958 AND pocb.pol_id = polb.id
1959 AND polb.khr_id = rulb.dnz_chr_id
1960 AND rgpb.rgd_code = 'LASEBB'
1961 AND rulb.rgp_id = rgpb.id
1962 AND rulb.rule_information_category = 'LASEFM';
1963
1964 lp_add_parameters okl_execute_formula_pub.ctxt_val_tbl_type;
1965
1966 l_api_name CONSTANT VARCHAR2(30) := 'calculate_buyback_content';
1967 l_api_version CONSTANT NUMBER := 1.0;
1968 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1969
1970 BEGIN
1971 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
1972 p_pkg_name => G_PKG_NAME,
1973 p_init_msg_list => p_init_msg_list,
1974 l_api_version => l_api_version,
1975 p_api_version => p_api_version,
1976 p_api_type => G_API_TYPE,
1977 x_return_status => l_return_status);
1978
1979 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1980 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1981 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1982 RAISE G_EXCEPTION_ERROR;
1983 END IF;
1984
1985 FOR l_okl_poc_formula_rec IN l_okl_poc_formula_csr(p_poc_id)
1986 LOOP
1987
1988 lp_add_parameters(1).NAME := 'p_khr_id';
1989 lp_add_parameters(1).VALUE := l_okl_poc_formula_rec.khr_id;
1990 lp_add_parameters(2).NAME := 'p_sty_id';
1991 lp_add_parameters(2).VALUE := l_okl_poc_formula_rec.sty_id;
1992
1993 OKL_EXECUTE_FORMULA_PUB.EXECUTE(p_api_version => p_api_version
1994 ,p_init_msg_list => p_init_msg_list
1995 ,x_return_status => l_return_status
1996 ,x_msg_count => x_msg_count
1997 ,x_msg_data => x_msg_data
1998 ,p_formula_name => l_okl_poc_formula_rec.formula
1999 ,p_contract_id => l_okl_poc_formula_rec.dnz_chr_id
2000 ,p_line_id => l_okl_poc_formula_rec.kle_id
2001 ,p_additional_parameters => lp_add_parameters
2002 ,x_value => x_buyback_amount);
2003
2004
2005 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2006 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2007 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2008 RAISE OKL_API.G_EXCEPTION_ERROR;
2009 END IF;
2010
2011 END LOOP; --l_okl_poc_formula_rec
2012
2013 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count
2014 ,x_msg_data => x_msg_data);
2015
2016 x_return_status := l_return_status;
2017
2018 EXCEPTION
2019 WHEN G_EXCEPTION_ERROR THEN
2020
2021 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2022 p_pkg_name => G_PKG_NAME,
2023 p_exc_name => G_EXC_NAME_ERROR,
2024 x_msg_count => x_msg_count,
2025 x_msg_data => x_msg_data,
2026 p_api_type => G_API_TYPE);
2027 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2028
2029 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2030 p_pkg_name => G_PKG_NAME,
2031 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
2032 x_msg_count => x_msg_count,
2033 x_msg_data => x_msg_data,
2034 p_api_type => G_API_TYPE);
2035 WHEN OTHERS THEN
2036
2037 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2038 p_pkg_name => G_PKG_NAME,
2039 p_exc_name => G_EXC_NAME_OTHERS,
2040 x_msg_count => x_msg_count,
2041 x_msg_data => x_msg_data,
2042 p_api_type => G_API_TYPE);
2043
2044
2045 END calculate_buyback_content;
2046 */
2047 PROCEDURE calculate_buyback_amount(
2048 p_api_version IN NUMBER
2049 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
2050 ,p_khr_id IN okc_k_headers_b.ID%TYPE
2051 ,p_pol_id IN okl_pools.ID%TYPE
2052 ,p_stream_type_subclass IN okl_strm_type_b.stream_type_subclass%TYPE
2053 ,x_buyback_amount OUT NOCOPY NUMBER
2054 ,x_return_status OUT NOCOPY VARCHAR2
2055 ,x_msg_count OUT NOCOPY NUMBER
2056 ,x_msg_data OUT NOCOPY VARCHAR2
2057 )
2058 IS
2059 -- Cursor to organize api calls by Pools
2060 -- also to make sure we have data
2061 CURSOR l_okl_pol_csr
2062 IS
2063 SELECT DISTINCT pocb.pol_id
2064 FROM okl_pool_contents pocb
2065 ,okl_strm_type_b styb
2066 WHERE pocb.khr_id = p_khr_id
2067 AND pocb.pol_id = p_pol_id
2068 AND pocb.sty_id = styb.id
2069 AND styb.stream_type_subclass = p_stream_type_subclass;
2070
2071 -- Cursor for Buyback Formula
2072 CURSOR l_okl_formula_csr
2073 IS
2074 SELECT rulb.rule_information1 formula
2075 FROM --okl_pool_contents pocb --changed by abhsaxen for Bug#6174484
2076 --,
2077 okl_pools polb
2078 ,okc_rules_b rulb
2079 ,okc_rule_groups_v rgpb
2080 WHERE polb.id = p_pol_id
2081 AND polb.khr_id = rulb.dnz_chr_id
2082 AND rgpb.rgd_code = 'LASEBB'
2083 AND rulb.rgp_id = rgpb.id
2084 AND rulb.rule_information_category = 'LASEFM';
2085
2086 -- Get all the Latest Pool Contents for this Pool
2087 CURSOR l_okl_poc_csr
2088 IS
2089 SELECT pocb.id poc_id
2090 ,polb.khr_id khr_id
2091 ,pocb.kle_id kle_id
2092 ,pocb.sty_id sty_id
2093 ,pocb.sty_code
2094 ,pocb.streams_from_date
2095 FROM okl_pool_contents pocb
2096 ,okl_strm_type_b styb
2097 ,okl_pools polb
2098 WHERE pocb.khr_id = p_khr_id
2099 AND pocb.pol_id = p_pol_id
2100 AND pocb.pol_id = polb.id
2101 AND pocb.status_code = G_POC_STS_ACTIVE
2102 AND pocb.sty_id = styb.id
2103 AND styb.stream_type_subclass = p_stream_type_subclass;
2104
2105 -- the revenue shares for the investor agreement
2106 CURSOR l_okl_rev_shares_csr(p_khr_id IN NUMBER,p_sty_id IN NUMBER)
2107 IS
2108 SELECT kleb.percent_stake percent_stake
2109 FROM okl_k_lines kleb
2110 ,okc_k_lines_b cles
2111 ,okc_line_styles_b lses
2112 ,okc_k_lines_b clet
2113 ,okc_line_styles_b lset
2114 WHERE clet.dnz_chr_id = p_khr_id
2115 AND clet.lse_id = lset.id
2116 AND lset.lty_code = 'INVESTMENT'
2117 AND cles.cle_id = clet.id
2118 AND cles.lse_id = lses.id
2119 AND lses.lty_code = 'REVENUE_SHARE'
2120 AND kleb.id = cles.id
2121 AND kleb.sty_id = p_sty_id;
2122
2123 l_api_name CONSTANT VARCHAR2(30) := 'calculate_buyback_amount';
2124 l_api_version CONSTANT NUMBER := 1.0;
2125 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2126
2127 lp_add_parameters Okl_Execute_Formula_Pub.ctxt_val_tbl_type;
2128 lx_buyback_amount NUMBER := 0;
2129 l_amount NUMBER := 0;
2130
2131 l_formula VARCHAR2(450);
2132
2133 BEGIN
2134
2135 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
2136 p_pkg_name => G_PKG_NAME,
2137 p_init_msg_list => p_init_msg_list,
2138 l_api_version => l_api_version,
2139 p_api_version => p_api_version,
2140 p_api_type => G_API_TYPE,
2141 x_return_status => l_return_status);
2142
2143 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2144 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2145 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2146 RAISE G_EXCEPTION_ERROR;
2147 END IF;
2148
2149 FOR l_okl_pol_rec IN l_okl_pol_csr
2150 LOOP
2151 FOR l_okl_formula_rec IN l_okl_formula_csr
2152 LOOP
2153 l_formula := l_okl_formula_rec.formula;
2154 END LOOP;
2155
2156 IF l_formula IS NOT NULL THEN
2157
2158 FOR l_okl_poc_rec IN l_okl_poc_csr
2159 LOOP
2160 lp_add_parameters(1).name := 'p_khr_id';
2161 lp_add_parameters(1).value := l_okl_poc_rec.khr_id;
2162 lp_add_parameters(2).name := 'p_sty_id';
2163 lp_add_parameters(2).value := l_okl_poc_rec.sty_id;
2164
2165 Okl_Execute_Formula_Pub.EXECUTE(p_api_version => p_api_version
2166 ,p_init_msg_list => p_init_msg_list
2167 ,x_return_status => l_return_status
2168 ,x_msg_count => x_msg_count
2169 ,x_msg_data => x_msg_data
2170 ,p_formula_name => l_formula
2171 ,p_contract_id => p_khr_id
2172 ,p_line_id => l_okl_poc_rec.kle_id
2173 ,p_additional_parameters => lp_add_parameters
2174 ,x_value => l_amount);
2175
2176 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2177 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2178 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2179 RAISE Okl_Api.G_EXCEPTION_ERROR;
2180 END IF;
2181
2182 -- calculate the revenue share for each sty
2183 FOR l_okl_rev_shares_rec IN l_okl_rev_shares_csr(l_okl_poc_rec.khr_id,l_okl_poc_rec.sty_id)
2184 LOOP
2185 l_amount := ( ((l_okl_rev_shares_rec.percent_stake) / 100) * l_amount);
2186 END LOOP; -- revenue shares csr
2187
2188 lx_buyback_amount := lx_buyback_amount + l_amount;
2189 END LOOP; -- l_okl_poc_formula_rec
2190
2191 END IF; -- l_formula
2192
2193
2194 END LOOP; -- l_okl_pol_csr
2195
2196 x_buyback_amount := lx_buyback_amount;
2197
2198 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count
2199 ,x_msg_data => x_msg_data);
2200
2201 x_return_status := l_return_status;
2202
2203
2204 EXCEPTION
2205 WHEN G_EXCEPTION_ERROR THEN
2206
2207 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2208 p_pkg_name => G_PKG_NAME,
2209 p_exc_name => G_EXC_NAME_ERROR,
2210 x_msg_count => x_msg_count,
2211 x_msg_data => x_msg_data,
2212 p_api_type => G_API_TYPE);
2213
2214 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2215
2216 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2217 p_pkg_name => G_PKG_NAME,
2218 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
2219 x_msg_count => x_msg_count,
2220 x_msg_data => x_msg_data,
2221 p_api_type => G_API_TYPE);
2222 WHEN OTHERS THEN
2223
2224 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2225 p_pkg_name => G_PKG_NAME,
2226 p_exc_name => G_EXC_NAME_OTHERS,
2227 x_msg_count => x_msg_count,
2228 x_msg_data => x_msg_data,
2229 p_api_type => G_API_TYPE);
2230
2231 END calculate_buyback_amount;
2232
2233 ----------------------------------------------------------------------------------
2234 -- Start of comments
2235 -- mvasudev
2236 -- Procedure Name : adjust_pool_contents
2237 -- Description : Utility Procedure to Adjust pool contents (DownStream Processing)
2238 --
2239 -- Business Rules :
2240 -- Parameters :
2241 -- Version : 1.0
2242 -- End of comments
2243 ----------------------------------------------------------------------------------
2244 PROCEDURE adjust_pool_contents(
2245 p_api_version IN NUMBER
2246 ,p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE
2247 ,x_return_status OUT NOCOPY VARCHAR2
2248 ,x_msg_count OUT NOCOPY NUMBER
2249 ,x_msg_data OUT NOCOPY VARCHAR2
2250 ,p_poxv_rec IN poxv_rec_type
2251 ,p_khr_id IN NUMBER DEFAULT NULL
2252 ,p_kle_id IN NUMBER DEFAULT NULL
2253 ,p_stream_type_subclass IN VARCHAR2 DEFAULT NULL
2254 ,p_streams_to_date IN DATE DEFAULT NULL
2255 )
2256 IS
2257
2258 -- Cursor to get all Pool Contents for this Lease Contract/Asset Line
2259 CURSOR l_okl_poc_csr(p_pol_id IN NUMBER)
2260 IS
2261 SELECT pocb.id
2262 ,pocb.kle_id
2263 ,pocb.sty_id
2264 ,pocb.sty_code
2265 ,pocb.streams_from_date
2266 ,pocb.streams_to_date
2267 ,styb.stream_type_subclass
2268 FROM okl_pool_contents pocb
2269 ,okl_strm_type_b styb
2270 WHERE pocb.pol_id = p_pol_id
2271 AND pocb.khr_id = p_khr_id
2272 AND pocb.kle_id = NVL(p_kle_id,pocb.kle_id)
2273 AND pocb.sty_id = styb.id
2274 AND styb.stream_type_subclass = NVL(p_stream_type_subclass,styb.stream_type_subclass)
2275 AND pocb.status_code = Okl_Pool_Pvt.G_POC_STS_ACTIVE
2276 AND pocb.transaction_number_out IS NULL;
2277
2278 -- Cursor to get Latest Streams for this Lease Contract/Asset Line
2279 CURSOR l_okl_stm_csr(p_khr_id IN NUMBER, p_kle_id IN NUMBER, p_sty_id IN NUMBER)
2280 IS
2281 SELECT id
2282 FROM okl_streams
2283 WHERE khr_id = p_khr_id
2284 AND kle_id = p_kle_id
2285 AND sty_id = p_sty_id
2286 AND say_code = 'CURR'
2287 AND active_yn = 'Y';
2288
2289 -- Cursor to get the Lease Contract End Date
2290 CURSOR l_okl_khr_csr
2291 IS
2292 SELECT end_date,
2293 sts_code --Bug 6594724
2294 FROM okc_k_headers_b
2295 WHERE id = p_khr_id;
2296
2297
2298 l_api_name CONSTANT VARCHAR2(30) := 'adjust_pool_contents';
2299 l_api_version CONSTANT NUMBER := 1.0;
2300 i NUMBER;
2301 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2302
2303 lp_poxv_rec poxv_rec_type;
2304 lx_poxv_rec poxv_rec_type;
2305 lp_pocv_rec pocv_rec_type;
2306 lx_pocv_rec pocv_rec_type;
2307
2308 lp_pocv_rec_cre pocv_rec_type;
2309 lp_pocv_rec_upd pocv_rec_type;
2310
2311 l_create BOOLEAN := FALSE;
2312
2313 l_rv_date_updated BOOLEAN := FALSE;
2314
2315 BEGIN
2316
2317 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
2318 p_pkg_name => G_PKG_NAME,
2319 p_init_msg_list => p_init_msg_list,
2320 l_api_version => l_api_version,
2321 p_api_version => p_api_version,
2322 p_api_type => G_API_TYPE,
2323 x_return_status => l_return_status);
2324 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2325 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2326 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2327 RAISE G_EXCEPTION_ERROR;
2328 END IF;
2329
2330 lp_poxv_rec := p_poxv_rec;
2331 Okl_Pool_Pvt.create_pool_transaction(p_api_version => p_api_version
2332 ,p_init_msg_list => p_init_msg_list
2333 ,x_return_status => l_return_status
2334 ,x_msg_count => x_msg_count
2335 ,x_msg_data => x_msg_data
2336 ,p_poxv_rec => lp_poxv_rec
2337 ,x_poxv_rec => lx_poxv_rec);
2338
2339 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2340 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2341 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2342 RAISE Okl_Api.G_EXCEPTION_ERROR;
2343 END IF;
2344
2345 -- Inactivate the corresponding pool contents with this transaction_number OUT
2346 FOR l_okl_poc_rec IN l_okl_poc_csr(p_poxv_rec.pol_id)
2347 LOOP
2348 lp_pocv_rec.id := l_okl_poc_rec.id;
2349 lp_pocv_rec.pol_id := p_poxv_rec.pol_id;
2350 lp_pocv_rec.date_inactive := lp_poxv_rec.date_effective;
2351 lp_pocv_rec.transaction_number_out := lx_poxv_rec.transaction_number;
2352 lp_pocv_rec.status_code := G_POC_STS_INACTIVE;
2353
2354 l_rv_date_updated := FALSE;
2355 IF (l_okl_poc_rec.stream_type_subclass = G_STY_SUBCLASS_RESIDUAL OR l_okl_poc_rec.stream_type_subclass = G_STY_SUBCLASS_LOAN_PAYMENT)
2356 THEN
2357 IF ( lp_poxv_rec.transaction_reason = G_TRX_REASON_ASSET_DISPOSAL
2358 OR lp_poxv_rec.transaction_reason = G_TRX_REASON_PURCHASE
2359 OR lp_poxv_rec.transaction_reason = G_TRX_REASON_REPURCHASE
2360 OR lp_poxv_rec.transaction_reason = G_TRX_REASON_SCRAP
2361 OR lp_poxv_rec.transaction_reason = G_TRX_REASON_REMARKET
2362 )
2363 THEN
2364 lp_pocv_rec.streams_to_date := p_streams_to_date; -- contract end date+1
2365 l_rv_date_updated := TRUE;
2366 ELSIF ( lp_poxv_rec.transaction_reason = G_TRX_REASON_EARLY_TERMINATION
2367 OR lp_poxv_rec.transaction_reason = G_TRX_REASON_ASSET_TERMINATION
2368 )
2369 THEN
2370 FOR l_okl_khr_rec IN l_okl_khr_csr
2371 LOOP
2372 lp_pocv_rec.streams_to_date := l_okl_khr_rec.end_date;
2373 END LOOP;
2374
2375 l_rv_date_updated := TRUE;
2376 END IF;
2377 END IF;
2378
2379 Okl_Pool_Pvt.update_pool_contents(p_api_version => p_api_version
2380 ,p_init_msg_list => p_init_msg_list
2381 ,x_return_status => l_return_status
2382 ,x_msg_count => x_msg_count
2383 ,x_msg_data => x_msg_data
2384 ,p_pocv_rec => lp_pocv_rec
2385 ,x_pocv_rec => lx_pocv_rec);
2386
2387
2388 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2389 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2390 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2391 RAISE Okl_Api.G_EXCEPTION_ERROR;
2392 END IF;
2393
2394 IF ( lp_poxv_rec.transaction_reason = G_TRX_REASON_CONTRACT_REBOOK
2395 OR lp_poxv_rec.transaction_reason = G_TRX_REASON_ASSET_SPLIT)
2396 THEN
2397 l_create := TRUE;
2398 ELSIF (lp_poxv_rec.transaction_reason = G_TRX_REASON_EARLY_TERMINATION
2399 OR lp_poxv_rec.transaction_reason = G_TRX_REASON_ASSET_TERMINATION)
2400 AND l_okl_poc_rec.streams_from_date <= lp_poxv_rec.date_effective
2401 THEN
2402 l_create := TRUE;
2403 ELSIF (lp_poxv_rec.transaction_reason = G_TRX_REASON_BUYBACK
2404 AND l_okl_poc_rec.streams_from_date <= SYSDATE )
2405 THEN
2406 l_create := TRUE;
2407 /*
2408 ankushar --Bug 6594724: Unable to terminate Investor Agreement with Residual Streams
2409 Start changes
2410 */
2411 IF (l_okl_poc_rec.stream_type_subclass = G_STY_SUBCLASS_RESIDUAL) THEN
2412 FOR l_okl_khr_rec IN l_okl_khr_csr
2413 LOOP
2414 IF (l_okl_khr_rec.sts_code IN ('TERMINATED','EXPIRED')) THEN
2415 l_create := FALSE;
2416 END IF;
2417 END LOOP;
2418 END IF;
2419 /*
2420 ankushar Bug 6594724
2421 End Changes
2422 */
2423
2424 END IF;
2425
2426 IF(l_create AND NOT l_rv_date_updated) THEN
2427 --create a new poc record with the above poc details
2428 lp_pocv_rec_cre.pol_id := p_poxv_rec.pol_id;
2429 lp_pocv_rec_cre.khr_id := p_khr_id;
2430 lp_pocv_rec_cre.kle_id := l_okl_poc_rec.kle_id;
2431 lp_pocv_rec_cre.sty_id := l_okl_poc_rec.sty_id;
2432 lp_pocv_rec_cre.sty_code := l_okl_poc_rec.sty_code;
2433 lp_pocv_rec_cre.streams_from_date := l_okl_poc_rec.streams_from_date;
2434
2435 -- residual poc-s , while created, always have null end date
2436 IF l_okl_poc_rec.stream_type_subclass = G_STY_SUBCLASS_RESIDUAL
2437 THEN
2438 lp_pocv_rec_cre.streams_to_date := NULL;
2439 ELSIF p_streams_to_date IS NOT NULL THEN
2440 lp_pocv_rec_cre.streams_to_date := p_streams_to_date;
2441 ELSE
2442 lp_pocv_rec_cre.streams_to_date := l_okl_poc_rec.streams_to_date;
2443 END IF;
2444
2445 lp_pocv_rec_cre.transaction_number_in := lx_poxv_rec.transaction_number;
2446 /* sosharma 26-11-2007
2447 Changes to stamp pox_id on okl_pool_contents
2448 Start Changes
2449 */
2450 lp_pocv_rec_cre.pox_id:= lx_poxv_rec.id;
2451 /* sosharma end changes*/
2452
2453 FOR l_okl_stm_rec IN l_okl_stm_csr(p_khr_id,l_okl_poc_rec.kle_id,l_okl_poc_rec.sty_id)
2454 LOOP
2455 -- there should exactly be one record
2456 lp_pocv_rec_cre.stm_id := l_okl_stm_rec.id;
2457 END LOOP; -- l_okl_stm_rec
2458
2459 lx_pocv_rec := NULL;
2460 Okl_Pool_Pvt.create_pool_contents(p_api_version => p_api_version
2461 ,p_init_msg_list => p_init_msg_list
2462 ,x_return_status => l_return_status
2463 ,x_msg_count => x_msg_count
2464 ,x_msg_data => x_msg_data
2465 ,p_pocv_rec => lp_pocv_rec_cre
2466 ,x_pocv_rec => lx_pocv_rec);
2467
2468 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2469 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2470 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2471 RAISE Okl_Api.G_EXCEPTION_ERROR;
2472 END IF;
2473
2474 -- Update Status => "Active"
2475 lp_pocv_rec_upd.id := lx_pocv_rec.id;
2476 lp_pocv_rec_upd.status_code := G_POC_STS_ACTIVE;
2477
2478 lx_pocv_rec := NULL;
2479
2480 Okl_Pool_Pvt.update_pool_contents(p_api_version => p_api_version
2481 ,p_init_msg_list => p_init_msg_list
2482 ,x_return_status => l_return_status
2483 ,x_msg_count => x_msg_count
2484 ,x_msg_data => x_msg_data
2485 ,p_pocv_rec => lp_pocv_rec_upd
2486 ,x_pocv_rec => lx_pocv_rec);
2487
2488 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2489 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2490 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2491 RAISE Okl_Api.G_EXCEPTION_ERROR;
2492 END IF;
2493
2494 END IF; -- l_create
2495
2496 END LOOP; -- l_okl_poc_rec
2497
2498 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count
2499 ,x_msg_data => x_msg_data);
2500
2501 x_return_status := l_return_status;
2502
2503 EXCEPTION
2504 WHEN G_EXCEPTION_ERROR THEN
2505 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2506 p_pkg_name => G_PKG_NAME,
2507 p_exc_name => G_EXC_NAME_ERROR,
2508 x_msg_count => x_msg_count,
2509 x_msg_data => x_msg_data,
2510 p_api_type => G_API_TYPE);
2511
2512 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2513 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2514 p_pkg_name => G_PKG_NAME,
2515 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
2516 x_msg_count => x_msg_count,
2517 x_msg_data => x_msg_data,
2518 p_api_type => G_API_TYPE);
2519 WHEN OTHERS THEN
2520 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2521 p_pkg_name => G_PKG_NAME,
2522 p_exc_name => G_EXC_NAME_OTHERS,
2523 x_msg_count => x_msg_count,
2524 x_msg_data => x_msg_data,
2525 p_api_type => G_API_TYPE);
2526 END adjust_pool_contents;
2527
2528 ----------------------------------------------------------------------------------
2529 -- Start of comments
2530 --
2531 -- Procedure Name : modify_pool_contents
2532 -- Description : Gateway API for DownStream Lease Processes to Modify Pool
2533 -- Contents upon some regular changes.
2534 -- Business Rules :
2535 -- Parameters :
2536 -- Version : 1.0
2537 -- End of comments
2538 ----------------------------------------------------------------------------------
2539 PROCEDURE modify_pool_contents(
2540 p_api_version IN NUMBER
2541 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
2542 ,p_transaction_reason IN VARCHAR2
2543 ,p_khr_id IN okc_k_headers_b.ID%TYPE
2544 ,p_kle_id IN OKC_K_LINES_B.ID%TYPE DEFAULT NULL
2545 ,p_stream_type_subclass IN okl_strm_type_b.stream_type_subclass%TYPE DEFAULT NULL
2546 ,p_transaction_date IN DATE
2547 ,p_effective_date IN DATE
2548 ,x_return_status OUT NOCOPY VARCHAR2
2549 ,x_msg_count OUT NOCOPY NUMBER
2550 ,x_msg_data OUT NOCOPY VARCHAR2
2551 )
2552 IS
2553
2554 -- Cursor to get all the Pools associated with this Lease Contract/ Asset Line
2555 CURSOR l_okl_pol_csr
2556 IS
2557 SELECT DISTINCT pol_id
2558 FROM okl_pool_contents pocb
2559 ,okl_strm_type_b styb
2560 WHERE pocb.khr_id = p_khr_id
2561 AND pocb.kle_id = NVL(p_kle_id,pocb.kle_id)
2562 AND pocb.sty_id = styb.id
2563 AND styb.stream_type_subclass = NVL(p_stream_type_subclass,styb.stream_type_subclass)
2564 AND pocb.status_code = Okl_Pool_Pvt.G_POC_STS_ACTIVE
2565 AND pocb.transaction_number_out IS NULL;
2566
2567
2568 -- Cursor to get the Lease Contract End Date
2569 CURSOR l_okl_khr_csr
2570 IS
2571 SELECT end_date
2572 FROM okc_k_headers_b
2573 WHERE id = p_khr_id;
2574
2575 -- begin ankushar 29-11-2006 Legal Entity Changes
2576 -- Cursor to fecth LE associated to the pool
2577 CURSOR l_okl_legal_entity_id_csr(p_pol_id NUMBER)
2578 IS
2579 SELECT legal_entity_id
2580 FROM okl_pools
2581 WHERE id = p_pol_id;
2582
2583 -- end ankushar Legal Entity changes
2584
2585 l_api_name CONSTANT VARCHAR2(30) := 'modify_pool_contents';
2586 l_api_version CONSTANT NUMBER := 1.0;
2587 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2588
2589
2590 lp_pocv_rec pocv_rec_type;
2591 lx_pocv_rec pocv_rec_type;
2592
2593 lp_pocv_rec_cre pocv_rec_type;
2594 lp_poxv_rec poxv_rec_type;
2595
2596 l_khr_end_date DATE;
2597
2598 -- begin ankushar 29-11-2006 Legal Entity Changes
2599 -- Attribute to store legal_entity_id
2600 lp_legal_entity_id NUMBER;
2601 -- end ankushar Legal Entity changes
2602
2603 BEGIN
2604
2605 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
2606 p_pkg_name => G_PKG_NAME,
2607 p_init_msg_list => p_init_msg_list,
2608 l_api_version => l_api_version,
2609 p_api_version => p_api_version,
2610 p_api_type => G_API_TYPE,
2611 x_return_status => l_return_status);
2612
2613 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2614 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2615 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2616 RAISE G_EXCEPTION_ERROR;
2617 END IF;
2618
2619 --Bug# 6788253: During split asset from UI for contracts in Booked status, need
2620 -- to modify pool contents for all active asset lines after stream
2621 -- regeneration.
2622 IF (p_transaction_reason IN (G_TRX_REASON_CONTRACT_REBOOK, G_TRX_REASON_ASSET_SPLIT))
2623 THEN
2624 FOR l_okl_pol_rec IN l_okl_pol_csr
2625 LOOP
2626
2627 -- begin ankushar 29-11-2006 Legal Entity Changes
2628 OPEN l_okl_legal_entity_id_csr(l_okl_pol_rec.pol_id);
2629 FETCH l_okl_legal_entity_id_csr INTO lp_legal_entity_id;
2630 CLOSE l_okl_legal_entity_id_csr;
2631 -- end ankushar Legal Entity changes
2632
2633 lp_poxv_rec.pol_id := l_okl_pol_rec.pol_id;
2634 lp_poxv_rec.transaction_date := p_transaction_date;
2635 lp_poxv_rec.transaction_type := G_TRX_TYPE_REPLACE;
2636 lp_poxv_rec.transaction_reason := p_transaction_reason;
2637 lp_poxv_rec.date_effective := p_effective_date;
2638
2639 --sosharma 04/12/2007 added to enable status on pool transaction
2640 lp_poxv_rec.transaction_status := G_POOL_TRX_STATUS_COMPLETE;
2641
2642 -- begin ankushar 29-11-2006 Legal Entity Changes
2643 -- legal_entity_id populated in the record
2644 lp_poxv_rec.legal_entity_id := lp_legal_entity_id;
2645 -- end ankushar Legal Entity changes
2646
2647 FOR l_okl_khr_rec IN l_okl_khr_csr
2648 LOOP
2649 --exactly one record
2650 adjust_pool_contents(p_api_version => p_api_version
2651 ,p_init_msg_list => p_init_msg_list
2652 ,x_return_status => l_return_status
2653 ,x_msg_count => x_msg_count
2654 ,x_msg_data => x_msg_data
2655 ,p_poxv_rec => lp_poxv_rec
2656 ,p_khr_id => p_khr_id
2657 ,p_kle_id => p_kle_id
2658 ,p_stream_type_subclass => p_stream_type_subclass
2659 -- mvasudev, 02/06/2004
2660 ,p_streams_to_date => l_okl_khr_rec.end_date+1);
2661
2662 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2663 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2664 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2665 RAISE Okl_Api.G_EXCEPTION_ERROR;
2666 END IF;
2667
2668 END LOOP; -- l_okl_khr_csr
2669
2670 END LOOP ; --l_okl_pol_csr
2671 ELSIF (p_transaction_reason = G_TRX_REASON_EARLY_TERMINATION)
2672 THEN
2673 FOR l_okl_pol_rec IN l_okl_pol_csr
2674 LOOP
2675
2676 -- begin ankushar 29-11-2006 Legal Entity Changes
2677 OPEN l_okl_legal_entity_id_csr(l_okl_pol_rec.pol_id);
2678 FETCH l_okl_legal_entity_id_csr INTO lp_legal_entity_id;
2679 CLOSE l_okl_legal_entity_id_csr;
2680 -- end ankushar Legal Entity changes
2681
2682 lp_poxv_rec.pol_id := l_okl_pol_rec.pol_id;
2683 lp_poxv_rec.transaction_date := p_transaction_date;
2684 lp_poxv_rec.transaction_type := G_TRX_TYPE_REPLACE;
2685 lp_poxv_rec.transaction_reason := p_transaction_reason;
2686 lp_poxv_rec.date_effective := p_effective_date;
2687 --sosharma 04/12/2007 added to enable status on pool transaction
2688 lp_poxv_rec.transaction_status := G_POOL_TRX_STATUS_COMPLETE;
2689
2690 -- begin ankushar 29-11-2006 Legal Entity Changes
2691 -- legal_entity_id populated in the record
2692 lp_poxv_rec.legal_entity_id := lp_legal_entity_id;
2693 -- end ankushar Legal Entity changes
2694
2695 adjust_pool_contents(p_api_version => p_api_version
2696 ,p_init_msg_list => p_init_msg_list
2697 ,x_return_status => l_return_status
2698 ,x_msg_count => x_msg_count
2699 ,x_msg_data => x_msg_data
2700 ,p_poxv_rec => lp_poxv_rec
2701 ,p_khr_id => p_khr_id
2702 ,p_kle_id => p_kle_id
2703 ,p_stream_type_subclass => p_stream_type_subclass
2704 ,p_streams_to_date => p_effective_date);
2705
2706 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2707 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2708 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2709 RAISE Okl_Api.G_EXCEPTION_ERROR;
2710 END IF;
2711
2712 END LOOP ; --l_okl_pol_csr
2713 ELSIF (p_transaction_reason = G_TRX_REASON_ASSET_TERMINATION) THEN
2714 IF(p_kle_id IS NOT NULL) THEN
2715 FOR l_okl_pol_rec IN l_okl_pol_csr
2716 LOOP
2717
2718 -- begin ankushar 29-11-2006 Legal Entity Changes
2719 OPEN l_okl_legal_entity_id_csr(l_okl_pol_rec.pol_id);
2720 FETCH l_okl_legal_entity_id_csr INTO lp_legal_entity_id;
2721 CLOSE l_okl_legal_entity_id_csr;
2722 -- end ankushar Legal Entity changes
2723
2724 lp_poxv_rec.pol_id := l_okl_pol_rec.pol_id;
2725 lp_poxv_rec.transaction_date := p_transaction_date;
2726 lp_poxv_rec.transaction_type := G_TRX_TYPE_REPLACE;
2727 lp_poxv_rec.transaction_reason := p_transaction_reason;
2728 lp_poxv_rec.date_effective := p_effective_date;
2729
2730 --sosharma 04/12/2007 added to enable status on pool transaction
2731 lp_poxv_rec.transaction_status := G_POOL_TRX_STATUS_COMPLETE;
2732 -- begin ankushar 29-11-2006 Legal Entity Changes
2733 -- legal_entity_id populated in the record
2734 lp_poxv_rec.legal_entity_id := lp_legal_entity_id;
2735 -- end ankushar Legal Entity changes
2736
2737 adjust_pool_contents(p_api_version => p_api_version
2738 ,p_init_msg_list => p_init_msg_list
2739 ,x_return_status => l_return_status
2740 ,x_msg_count => x_msg_count
2741 ,x_msg_data => x_msg_data
2742 ,p_poxv_rec => lp_poxv_rec
2743 ,p_khr_id => p_khr_id
2744 ,p_kle_id => p_kle_id
2745 ,p_stream_type_subclass => p_stream_type_subclass
2746 ,p_streams_to_date => p_effective_date);
2747
2748 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2749 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2750 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2751 RAISE Okl_Api.G_EXCEPTION_ERROR;
2752 END IF;
2753
2754 END LOOP ; --l_okl_pol_csr
2755 ELSE
2756 RAISE Okl_Api.G_EXCEPTION_ERROR;
2757 END IF;
2758 ELSIF(p_transaction_reason = G_TRX_REASON_ASSET_DISPOSAL OR
2759 p_transaction_reason = G_TRX_REASON_PURCHASE OR
2760 p_transaction_reason = G_TRX_REASON_REPURCHASE OR
2761 p_transaction_reason = G_TRX_REASON_SCRAP OR
2762 p_transaction_reason = G_TRX_REASON_REMARKET
2763 )
2764 THEN
2765 -- These are residual value streams
2766 --
2767 IF (p_transaction_reason = G_TRX_REASON_ASSET_DISPOSAL
2768 AND p_kle_id IS NULL) THEN
2769 RAISE Okl_Api.G_EXCEPTION_ERROR;
2770 END IF;
2771
2772 IF(p_effective_date IS NULL) THEN
2773 RAISE Okl_Api.G_EXCEPTION_ERROR;
2774 ELSE
2775 FOR l_okl_pol_rec IN l_okl_pol_csr
2776 LOOP
2777
2778 -- begin ankushar 29-11-2006 Legal Entity Changes
2779 OPEN l_okl_legal_entity_id_csr(l_okl_pol_rec.pol_id);
2780 FETCH l_okl_legal_entity_id_csr INTO lp_legal_entity_id;
2781 CLOSE l_okl_legal_entity_id_csr;
2782 -- end ankushar Legal Entity changes
2783
2784 lp_poxv_rec.pol_id := l_okl_pol_rec.pol_id;
2785 lp_poxv_rec.transaction_date := p_transaction_date;
2786 lp_poxv_rec.transaction_type := G_TRX_TYPE_REMOVAL;
2787 lp_poxv_rec.transaction_reason := p_transaction_reason;
2788 lp_poxv_rec.date_effective := p_effective_date;
2789 --sosharma 04/12/2007 added to enable status on pool transaction
2790 lp_poxv_rec.transaction_status := G_POOL_TRX_STATUS_COMPLETE;
2791 -- begin ankushar 29-11-2006 Legal Entity Changes
2792 -- legal_entity_id populated in the record
2793 lp_poxv_rec.legal_entity_id := lp_legal_entity_id;
2794 -- end ankushar Legal Entity changes
2795
2796 FOR l_okl_khr_rec IN l_okl_khr_csr
2797 LOOP
2798 adjust_pool_contents(p_api_version => p_api_version
2799 ,p_init_msg_list => p_init_msg_list
2800 ,x_return_status => l_return_status
2801 ,x_msg_count => x_msg_count
2802 ,x_msg_data => x_msg_data
2803 ,p_poxv_rec => lp_poxv_rec
2804 ,p_khr_id => p_khr_id
2805 ,p_kle_id => p_kle_id
2806 ,p_stream_type_subclass => p_stream_type_subclass
2807 -- mvasudev, 02/06/2004
2808 ,p_streams_to_date => l_okl_khr_rec.end_date+1);
2809
2810 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2811 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2812 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2813 RAISE Okl_Api.G_EXCEPTION_ERROR;
2814 END IF;
2815 END LOOP;
2816
2817 END LOOP ; --l_okl_pol_csr
2818
2819 END IF; -- p_effective_date
2820
2821 END IF; -- p_transaction_reason
2822
2823
2824 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count
2825 ,x_msg_data => x_msg_data);
2826
2827 x_return_status := l_return_status;
2828
2829
2830 EXCEPTION
2831 WHEN G_EXCEPTION_ERROR THEN
2832 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2833 p_pkg_name => G_PKG_NAME,
2834 p_exc_name => G_EXC_NAME_ERROR,
2835 x_msg_count => x_msg_count,
2836 x_msg_data => x_msg_data,
2837 p_api_type => G_API_TYPE);
2838
2839 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2840 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2841 p_pkg_name => G_PKG_NAME,
2842 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
2843 x_msg_count => x_msg_count,
2844 x_msg_data => x_msg_data,
2845 p_api_type => G_API_TYPE);
2846 WHEN OTHERS THEN
2847 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2848 p_pkg_name => G_PKG_NAME,
2849 p_exc_name => G_EXC_NAME_OTHERS,
2850 x_msg_count => x_msg_count,
2851 x_msg_data => x_msg_data,
2852 p_api_type => G_API_TYPE);
2853 END modify_pool_contents;
2854
2855 ----------------------------------------------------------------------------------
2856 -- Start of comments
2857 --
2858 -- Procedure Name : modify_pool_contents
2859 -- Description : Gateway API for DownStream Lease Processes to Modify Pool
2860 -- Contents upon Asset Split.
2861 -- Business Rules :
2862 -- Parameters :
2863 -- Version : 1.0
2864 -- End of comments
2865 ----------------------------------------------------------------------------------
2866 PROCEDURE modify_pool_contents(
2867 p_api_version IN NUMBER
2868 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
2869 ,p_transaction_reason IN VARCHAR2
2870 ,p_khr_id IN OKC_K_HEADERS_B.ID%TYPE
2871 ,p_kle_id IN OKC_K_LINES_B.ID%TYPE
2872 ,p_split_kle_ids IN cle_tbl_type
2873 ,p_transaction_date IN DATE
2874 ,p_effective_date IN DATE
2875 ,x_return_status OUT NOCOPY VARCHAR2
2876 ,x_msg_count OUT NOCOPY NUMBER
2877 ,x_msg_data OUT NOCOPY VARCHAR2
2878 )
2879 IS
2880
2881 -- Cursor to get all the Pools associated with this Lease Contract/ Asset Line
2882 CURSOR l_okl_pol_csr
2883 IS
2884 SELECT DISTINCT pol_id
2885 FROM okl_pool_contents
2886 WHERE khr_id = p_khr_id
2887 AND kle_id = p_kle_id
2888 AND status_code = Okl_Pool_Pvt.G_POC_STS_ACTIVE
2889 AND transaction_number_out IS NULL;
2890
2891 -- Cursor to get all the Pool Contents for this Asset Line
2892 CURSOR l_okl_poc_csr(p_pol_id IN NUMBER)
2893 IS
2894 SELECT id
2895 ,kle_id
2896 ,sty_id
2897 ,sty_code
2898 ,streams_from_date
2899 ,streams_to_date
2900 FROM okl_pool_contents
2901 WHERE pol_id = p_pol_id
2902 AND khr_id = p_khr_id
2903 AND kle_id = p_kle_id
2904 AND status_code = Okl_Pool_Pvt.G_POC_STS_ACTIVE
2905 AND transaction_number_out IS NULL;
2906
2907 -- Cursor to get the Latest Streams for this Asset Line
2908 CURSOR l_okl_stm_csr(p_khr_id IN NUMBER, p_kle_id IN NUMBER, p_sty_id IN NUMBER)
2909 IS
2910 SELECT id
2911 FROM okl_streams
2912 WHERE khr_id = p_khr_id
2913 AND kle_id = p_kle_id
2914 AND sty_id = p_sty_id
2915 AND say_code = 'CURR'
2916 AND active_yn = 'Y';
2917
2918 -- begin ankushar 29-11-2006 Legal Entity Changes
2919 -- Cursor to fecth LE associated to the pool
2920 CURSOR l_okl_legal_entity_id_csr(p_pol_id NUMBER)
2921 IS
2922 SELECT legal_entity_id
2923 FROM okl_pools
2924 WHERE id = p_pol_id;
2925 -- end ankushar Legal Entity Changes
2926
2927 l_api_name CONSTANT VARCHAR2(30) := 'modify_pool_contents';
2928 l_api_version CONSTANT NUMBER := 1.0;
2929 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2930
2931 -- begin ankushar 29-11-2006 Legal Entity Changes
2932 -- Attribute to store legal_entity_id
2933 lp_legal_entity_id NUMBER;
2934 -- end ankushar Legal Entity changes
2935
2936
2937 lp_pocv_rec pocv_rec_type;
2938 lx_pocv_rec pocv_rec_type;
2939 lp_poxv_rec poxv_rec_type;
2940 lx_poxv_rec poxv_rec_type;
2941
2942 lp_pocv_rec_cre pocv_rec_type;
2943 lp_pocv_rec_upd pocv_rec_type;
2944
2945
2946 BEGIN
2947
2948 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
2949 p_pkg_name => G_PKG_NAME,
2950 p_init_msg_list => p_init_msg_list,
2951 l_api_version => l_api_version,
2952 p_api_version => p_api_version,
2953 p_api_type => G_API_TYPE,
2954 x_return_status => l_return_status);
2955
2956 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2957 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2958 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2959 RAISE G_EXCEPTION_ERROR;
2960 END IF;
2961
2962 IF(p_transaction_reason = G_TRX_REASON_ASSET_SPLIT)
2963 THEN
2964 IF(p_kle_id IS NOT NULL AND p_split_kle_ids.COUNT>0 )
2965 THEN
2966 FOR l_okl_pol_rec IN l_okl_pol_csr
2967 LOOP
2968
2969 -- begin ankushar 29-11-2006 Legal Entity Changes
2970 OPEN l_okl_legal_entity_id_csr(l_okl_pol_rec.pol_id);
2971 FETCH l_okl_legal_entity_id_csr INTO lp_legal_entity_id;
2972 CLOSE l_okl_legal_entity_id_csr;
2973 -- end ankushar Legal Entity changes
2974
2975 lp_poxv_rec.pol_id := l_okl_pol_rec.pol_id;
2976 lp_poxv_rec.transaction_date := p_transaction_date;
2977 lp_poxv_rec.transaction_type := G_TRX_TYPE_REPLACE;
2978 lp_poxv_rec.transaction_reason := p_transaction_reason;
2979 --sosharma 04/12/2007 added to enable status on pool transaction
2980 lp_poxv_rec.transaction_status := G_POOL_TRX_STATUS_COMPLETE;
2981 -- begin ankushar 29-11-2006 Legal Entity Changes
2982 -- legal_entity_id populated in the record
2983 lp_poxv_rec.legal_entity_id := lp_legal_entity_id;
2984 -- end ankushar Legal Entity changes
2985
2986 Okl_Pool_Pvt.create_pool_transaction(p_api_version => p_api_version
2987 ,p_init_msg_list => p_init_msg_list
2988 ,x_return_status => l_return_status
2989 ,x_msg_count => x_msg_count
2990 ,x_msg_data => x_msg_data
2991 ,p_poxv_rec => lp_poxv_rec
2992 ,x_poxv_rec => lx_poxv_rec);
2993
2994 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2995 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2996 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2997 RAISE Okl_Api.G_EXCEPTION_ERROR;
2998 END IF;
2999
3000 FOR l_okl_poc_rec IN l_okl_poc_csr(l_okl_pol_rec.pol_id)
3001 LOOP
3002 -- Inactivate the corresponding pool contents with this transaction_number OUT
3003 lp_pocv_rec.id := l_okl_poc_rec.id;
3004 lp_pocv_rec.pol_id := l_okl_pol_rec.pol_id;
3005 lp_pocv_rec.transaction_number_out := lx_poxv_rec.transaction_number;
3006 lp_pocv_rec.status_code := G_POC_STS_INACTIVE;
3007 lp_pocv_rec.date_inactive := p_effective_date;
3008
3009 Okl_Pool_Pvt.update_pool_contents(p_api_version => p_api_version
3010 ,p_init_msg_list => p_init_msg_list
3011 ,x_return_status => l_return_status
3012 ,x_msg_count => x_msg_count
3013 ,x_msg_data => x_msg_data
3014 ,p_pocv_rec => lp_pocv_rec
3015 ,x_pocv_rec => lx_pocv_rec);
3016
3017 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3018 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3019 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3020 RAISE Okl_Api.G_EXCEPTION_ERROR;
3021 END IF;
3022
3023 FOR i IN 1..p_split_kle_ids.COUNT
3024 LOOP
3025 -- create a new poc record with the above poc details
3026 lp_pocv_rec_cre.pol_id := l_okl_pol_rec.pol_id;
3027 lp_pocv_rec_cre.khr_id := p_khr_id;
3028 lp_pocv_rec_cre.kle_id := p_split_kle_ids(i).cle_id;
3029 lp_pocv_rec_cre.sty_id := l_okl_poc_rec.sty_id;
3030 lp_pocv_rec_cre.sty_code := l_okl_poc_rec.sty_code;
3031
3032 FOR l_okl_stm_rec IN l_okl_stm_csr(p_khr_id,p_split_kle_ids(i).cle_id,l_okl_poc_rec.sty_id)
3033 LOOP
3034 lp_pocv_rec_cre.stm_id := l_okl_stm_rec.id;
3035 END LOOP; -- l_okl_stm_rec
3036
3037 lp_pocv_rec_cre.streams_from_date := l_okl_poc_rec.streams_from_date;
3038 lp_pocv_rec_cre.streams_to_date := l_okl_poc_rec.streams_to_date;
3039 lp_pocv_rec_cre.transaction_number_in := lx_poxv_rec.transaction_number;
3040
3041 /* sosharma 26-11-2007
3042 Changes to stamp pox_id on okl_pool_contents
3043 Start Changes
3044 */
3045 lp_pocv_rec_cre.pox_id:= lx_poxv_rec.id;
3046 /* sosharma end changes*/
3047
3048 Okl_Pool_Pvt.create_pool_contents(p_api_version => p_api_version
3049 ,p_init_msg_list => p_init_msg_list
3050 ,x_return_status => l_return_status
3051 ,x_msg_count => x_msg_count
3052 ,x_msg_data => x_msg_data
3053 ,p_pocv_rec => lp_pocv_rec_cre
3054 ,x_pocv_rec => lx_pocv_rec);
3055
3056 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3057 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3058 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3059 RAISE Okl_Api.G_EXCEPTION_ERROR;
3060 END IF;
3061
3062 -- Update Status => "Active"
3063 lp_pocv_rec_upd.id := lx_pocv_rec.id;
3064 lp_pocv_rec_upd.status_code := G_POC_STS_ACTIVE;
3065
3066 lx_pocv_rec := NULL;
3067
3068 Okl_Pool_Pvt.update_pool_contents(p_api_version => p_api_version
3069 ,p_init_msg_list => p_init_msg_list
3070 ,x_return_status => l_return_status
3071 ,x_msg_count => x_msg_count
3072 ,x_msg_data => x_msg_data
3073 ,p_pocv_rec => lp_pocv_rec_upd
3074 ,x_pocv_rec => lx_pocv_rec);
3075
3076 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3077 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3078 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3079 RAISE Okl_Api.G_EXCEPTION_ERROR;
3080 END IF;
3081
3082 END LOOP; -- p_split_kle_ids
3083
3084 END LOOP; -- l_okl_poc_csr
3085
3086 END LOOP ; -- l_okl_pol_csr
3087 ELSE
3088 RAISE G_EXCEPTION_ERROR;
3089 END IF;
3090 ELSE
3091 RAISE G_EXCEPTION_ERROR;
3092 END IF;
3093
3094 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count
3095 ,x_msg_data => x_msg_data);
3096
3097 x_return_status := l_return_status;
3098
3099
3100 EXCEPTION
3101 WHEN G_EXCEPTION_ERROR THEN
3102 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3103 p_pkg_name => G_PKG_NAME,
3104 p_exc_name => G_EXC_NAME_ERROR,
3105 x_msg_count => x_msg_count,
3106 x_msg_data => x_msg_data,
3107 p_api_type => G_API_TYPE);
3108
3109 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
3110 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3111 p_pkg_name => G_PKG_NAME,
3112 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
3113 x_msg_count => x_msg_count,
3114 x_msg_data => x_msg_data,
3115 p_api_type => G_API_TYPE);
3116 WHEN OTHERS THEN
3117 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3118 p_pkg_name => G_PKG_NAME,
3119 p_exc_name => G_EXC_NAME_OTHERS,
3120 x_msg_count => x_msg_count,
3121 x_msg_data => x_msg_data,
3122 p_api_type => G_API_TYPE);
3123 END modify_pool_contents;
3124
3125 ----------------------------------------------------------------------------------
3126 -- Start of comments
3127 -- mvasudev
3128 -- Procedure Name : create_inv_disb_streams
3129 -- Description : Utility Procedure to Create Investor Disbursement Streams.
3130 --
3131 -- Business Rules :
3132 -- Parameters :
3133 -- Version : 1.0
3134 -- End of comments
3135 ----------------------------------------------------------------------------------
3136 PROCEDURE create_inv_disb_streams(
3137 p_api_version IN NUMBER
3138 ,p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE
3139 ,x_return_status OUT NOCOPY VARCHAR2
3140 ,x_msg_count OUT NOCOPY NUMBER
3141 ,x_msg_data OUT NOCOPY VARCHAR2
3142 ,p_khr_id IN NUMBER
3143 ,p_source_id IN NUMBER
3144 ,p_stream_type_subclass IN VARCHAR2
3145 ,p_amount IN NUMBER
3146 ,p_stream_element_date IN DATE
3147 ,p_loan_sty_purpose IN VARCHAR2 DEFAULT NULL
3148 )
3149 IS
3150
3151 -- To get the Transaction Number for Streams
3152 CURSOR l_okl_seq_csr
3153 IS
3154 SELECT okl_sif_seq.NEXTVAL transaction_number
3155 FROM dual;
3156
3157 lp_stmv_rec Okl_Streams_Pub.stmv_rec_type;
3158 lx_stmv_rec Okl_Streams_Pub.stmv_rec_type;
3159 lp_selv_tbl Okl_Streams_Pub.selv_tbl_type;
3160 lx_selv_tbl Okl_Streams_Pub.selv_tbl_type;
3161
3162 l_api_name CONSTANT VARCHAR2(30) := 'create_inv_disb_streams';
3163 l_api_version CONSTANT NUMBER := 1.0;
3164 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3165
3166 l_sty_purpose VARCHAR2(150);
3167 l_sty_id NUMBER;
3168
3169 BEGIN
3170
3171 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
3172 p_pkg_name => G_PKG_NAME,
3173 p_init_msg_list => p_init_msg_list,
3174 l_api_version => l_api_version,
3175 p_api_version => p_api_version,
3176 p_api_type => G_API_TYPE,
3177 x_return_status => l_return_status);
3178 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
3179 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3180 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
3181 RAISE G_EXCEPTION_ERROR;
3182 END IF;
3183
3184 IF p_stream_type_subclass = G_STY_SUBCLASS_RENT THEN
3185 l_sty_purpose := G_STY_INV_RENT_BUYBACK;
3186 ELSIF p_stream_type_subclass = G_STY_SUBCLASS_RESIDUAL THEN
3187 l_sty_purpose := G_STY_INV_RESIDUAL_BUYBACK ;
3188 /* ankushar Bug#6740000 20-Jan-2008
3189 Added else clause for LOAN_PAYMENT subclass
3190 */
3191 ELSIF p_stream_type_subclass = G_STY_SUBCLASS_LOAN_PAYMENT THEN
3192 l_sty_purpose := p_loan_sty_purpose;
3193 END IF;
3194 -- End Changes ankushar Bug# 6740000
3195 -- Get the primary Stream Type
3196 Okl_Streams_Util.get_primary_stream_type(p_khr_id => p_source_id,
3197 p_primary_sty_purpose => l_sty_purpose,
3198 x_return_status => l_return_status,
3199 x_primary_sty_id => l_sty_id);
3200
3201 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
3202 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3203 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
3204 RAISE G_EXCEPTION_ERROR;
3205 END IF;
3206
3207 IF l_sty_id IS NOT NULL THEN
3208
3209 lp_stmv_rec.sty_id := l_sty_id;
3210 lp_stmv_rec.khr_id := p_khr_id;
3211 lp_stmv_rec.source_id := p_source_id;
3212 lp_stmv_rec.source_table := G_STM_SOURCE_TABLE;
3213 lp_stmv_rec.sgn_code := G_STM_SGN_CODE_MANUAL;
3214 lp_stmv_rec.say_code := G_STM_SAY_CODE_CURR;
3215 lp_stmv_rec.active_yn := G_STM_ACTIVE_Y;
3216 lp_stmv_rec.date_current := SYSDATE;
3217
3218 FOR l_okl_seq_rec IN l_okl_seq_csr
3219 LOOP
3220 lp_stmv_rec.transaction_number := l_okl_seq_rec.transaction_number;
3221 END LOOP;
3222
3223 lp_selv_tbl(1).amount := p_amount;
3224 lp_selv_tbl(1).stream_element_date := p_stream_element_date;
3225 lp_selv_tbl(1).se_line_number := 1;
3226
3227 Okl_Streams_Pub.create_streams(p_api_version => p_api_version
3228 ,p_init_msg_list => p_init_msg_list
3229 ,x_return_status => l_return_status
3230 ,x_msg_count => x_msg_count
3231 ,x_msg_data => x_msg_data
3232 ,p_stmv_rec => lp_stmv_rec
3233 ,p_selv_tbl => lp_selv_tbl
3234 ,x_stmv_rec => lx_stmv_rec
3235 ,x_selv_tbl => lx_selv_tbl);
3236
3237 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3238 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3239 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3240 RAISE Okl_Api.G_EXCEPTION_ERROR;
3241 END IF;
3242
3243 END IF; -- l_sty_id
3244
3245
3246 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count
3247 ,x_msg_data => x_msg_data);
3248
3249 x_return_status := l_return_status;
3250
3251 EXCEPTION
3252 WHEN G_EXCEPTION_ERROR THEN
3253 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3254 p_pkg_name => G_PKG_NAME,
3255 p_exc_name => G_EXC_NAME_ERROR,
3256 x_msg_count => x_msg_count,
3257 x_msg_data => x_msg_data,
3258 p_api_type => G_API_TYPE);
3259
3260 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
3261 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3262 p_pkg_name => G_PKG_NAME,
3263 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
3264 x_msg_count => x_msg_count,
3265 x_msg_data => x_msg_data,
3266 p_api_type => G_API_TYPE);
3267 WHEN OTHERS THEN
3268 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3269 p_pkg_name => G_PKG_NAME,
3270 p_exc_name => G_EXC_NAME_OTHERS,
3271 x_msg_count => x_msg_count,
3272 x_msg_data => x_msg_data,
3273 p_api_type => G_API_TYPE);
3274 END create_inv_disb_streams;
3275
3276
3277 PROCEDURE buyback_pool_contents(
3278 p_api_version IN NUMBER
3279 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
3280 ,p_khr_id IN okc_k_headers_b.ID%TYPE
3281 ,p_pol_id IN okl_pools.ID%TYPE
3282 ,p_stream_type_subclass IN okl_strm_type_b.stream_type_subclass%TYPE
3283 ,p_effective_date IN DATE
3284 ,x_return_status OUT NOCOPY VARCHAR2
3285 ,x_msg_count OUT NOCOPY NUMBER
3286 ,x_msg_data OUT NOCOPY VARCHAR2)
3287 IS
3288 -- Cursor to organize api calls by Pools
3289 -- also to make sure we have data
3290 CURSOR l_okl_pol_csr
3291 IS
3292 SELECT DISTINCT pocb.pol_id
3293 FROM okl_pool_contents pocb
3294 ,okl_strm_type_b styb
3295 WHERE pocb.khr_id = p_khr_id
3296 AND pocb.pol_id = p_pol_id
3297 AND pocb.sty_id = styb.id
3298 AND styb.stream_type_subclass = p_stream_type_subclass;
3299
3300 -- Cursor to get the Agreement id and Legal Entity Id
3301 CURSOR l_okl_agr_csr
3302 IS
3303 SELECT khr_id, legal_entity_id
3304 FROM okl_pools
3305 WHERE id = p_pol_id;
3306
3307 -- Cursor to check is Lease Contract
3308 -- is still securitized
3309 CURSOR l_okl_poc_khr_csr
3310 IS
3311 SELECT 1
3312 FROM okl_pool_contents pocb
3313 WHERE pocb.khr_id = p_khr_id
3314 AND pocb.status_code = 'ACTIVE';
3315
3316 -- cursor to get stream type purposes for a stream_type_subclass
3317 CURSOR get_sty_id_csr (p_sty_sub_classs VARCHAR2)
3318 IS
3319 SELECT id, stream_type_purpose
3320 FROM OKL_STRM_TYPE_B
3321 WHERE stream_type_subclass = p_sty_sub_classs;
3322
3323 lp_poxv_rec poxv_rec_type;
3324 lx_poxv_rec poxv_rec_type;
3325
3326 lp_chrv_rec Okl_Okc_Migration_Pvt.chrv_rec_type;
3327 lx_chrv_rec Okl_Okc_Migration_Pvt.chrv_rec_type;
3328 lp_khrv_rec Okl_Contract_Pub.khrv_rec_type;
3329 lx_khrv_rec Okl_Contract_Pub.khrv_rec_type;
3330
3331 l_api_name CONSTANT VARCHAR2(30) := 'buyback_streams';
3332 l_api_version CONSTANT NUMBER := 1.0;
3333 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3334
3335 lx_buyback_amount NUMBER;
3336 lp_effective_date DATE;
3337 lp_agreement_id NUMBER;
3338 --added by abhsaxen for legal Entiy Uptake
3339 lp_legal_entiy_id NUMBER;
3340 l_khr_active BOOLEAN := FALSE;
3341
3342 TYPE l_loan_sty_purpose_rec IS RECORD(
3343 l_loan_sty_purpose VARCHAR2(40));
3344
3345 TYPE l_loan_sty_purpose_tbl IS TABLE OF l_loan_sty_purpose_rec INDEX BY BINARY_INTEGER;
3346 l_loan_sty_purposes l_loan_sty_purpose_tbl;
3347
3348 BEGIN
3349
3350 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
3351 p_pkg_name => G_PKG_NAME,
3352 p_init_msg_list => p_init_msg_list,
3353 l_api_version => l_api_version,
3354 p_api_version => p_api_version,
3355 p_api_type => G_API_TYPE,
3356 x_return_status => l_return_status);
3357
3358 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
3359 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3360 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
3361 RAISE G_EXCEPTION_ERROR;
3362 END IF;
3363
3364 FOR l_okl_agr_rec IN l_okl_agr_csr
3365 LOOP
3366 lp_agreement_id := l_okl_agr_rec.khr_id;
3367 lp_legal_entiy_id := l_okl_agr_rec.legal_entity_id;
3368 END LOOP;
3369
3370 IF lp_agreement_id IS NULL THEN
3371 RAISE G_EXCEPTION_ERROR;
3372 END IF;
3373
3374 FOR l_okl_pol_rec IN l_okl_pol_csr
3375 LOOP
3376
3377 -- Calculate the BuyBack Amount
3378 calculate_buyback_amount(p_api_version => p_api_version
3379 ,p_init_msg_list => p_init_msg_list
3380 ,x_return_status => l_return_status
3381 ,x_msg_count => x_msg_count
3382 ,x_msg_data => x_msg_data
3383 ,p_khr_id => p_khr_id
3384 ,p_pol_id => p_pol_id
3385 ,p_stream_type_subclass => p_stream_type_subclass
3386 ,x_buyback_amount => lx_buyback_amount);
3387 -- Modify the pool contents as needed
3388 IF p_effective_date IS NOT NULL THEN
3389 lp_effective_date := p_effective_date;
3390 ELSE
3391 lp_effective_date := SYSDATE;
3392 END IF;
3393
3394 lp_poxv_rec.pol_id := p_pol_id;
3395 lp_poxv_rec.transaction_date := SYSDATE;
3396 lp_poxv_rec.transaction_type := G_TRX_TYPE_REMOVAL;
3397 lp_poxv_rec.transaction_reason := G_TRX_REASON_BUYBACK;
3398 lp_poxv_rec.date_effective := lp_effective_date;
3399 lp_poxv_rec.legal_entity_id := lp_legal_entiy_id;
3400 --sosharma 04/12/2007 added to enable status on pool transaction
3401 lp_poxv_rec.transaction_status := G_POOL_TRX_STATUS_COMPLETE;
3402
3403 adjust_pool_contents(p_api_version => p_api_version
3404 ,p_init_msg_list => p_init_msg_list
3405 ,x_return_status => l_return_status
3406 ,x_msg_count => x_msg_count
3407 ,x_msg_data => x_msg_data
3408 ,p_poxv_rec => lp_poxv_rec
3409 ,p_khr_id => p_khr_id
3410 ,p_stream_type_subclass => p_stream_type_subclass
3411 ,p_streams_to_date => lp_effective_date);
3412
3413 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3414 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3415 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3416 RAISE Okl_Api.G_EXCEPTION_ERROR;
3417 END IF;
3418
3419 IF p_stream_type_subclass = G_STY_SUBCLASS_LOAN_PAYMENT THEN
3420 l_loan_sty_purposes(1).l_loan_sty_purpose := G_STY_INV_PRINCIPAL_BUYBACK;
3421 l_loan_sty_purposes(2).l_loan_sty_purpose := G_STY_INV_INTEREST_BUYBACK;
3422 l_loan_sty_purposes(3).l_loan_sty_purpose := G_STY_INV_PPD_BUYBACK;
3423 FOR i IN 1 .. l_loan_sty_purposes.count
3424 LOOP
3425 --Create Investor Disbursment Streams for the Buyback amount
3426 create_inv_disb_streams(p_api_version => p_api_version
3427 ,p_init_msg_list => p_init_msg_list
3428 ,x_return_status => l_return_status
3429 ,x_msg_count => x_msg_count
3430 ,x_msg_data => x_msg_data
3431 ,p_khr_id => p_khr_id
3432 ,p_source_id => lp_agreement_id
3433 ,p_stream_type_subclass => p_stream_type_subclass
3434 ,p_amount => lx_buyback_amount
3435 ,p_stream_element_date => SYSDATE
3436 ,p_loan_sty_purpose => l_loan_sty_purposes(i).l_loan_sty_purpose);
3437 END LOOP;
3438 ELSE
3439 --Create Investor Disbursment Streams for the Buyback amount
3440 create_inv_disb_streams(p_api_version => p_api_version
3441 ,p_init_msg_list => p_init_msg_list
3442 ,x_return_status => l_return_status
3443 ,x_msg_count => x_msg_count
3444 ,x_msg_data => x_msg_data
3445 ,p_khr_id => p_khr_id
3446 ,p_source_id => lp_agreement_id
3447 ,p_stream_type_subclass => p_stream_type_subclass
3448 ,p_amount => lx_buyback_amount
3449 ,p_stream_element_date => SYSDATE
3450 ,p_loan_sty_purpose => NULL);
3451 END IF;
3452
3453 -- Cancel Accrual Streams for 'RENT' and 'LOAN_PAYMENT' subclass only
3454 IF p_stream_type_subclass = G_STY_SUBCLASS_RENT OR p_stream_type_subclass = G_STY_SUBCLASS_LOAN_PAYMENT THEN
3455 Okl_Accrual_Sec_Pvt.cancel_streams(p_api_version => p_api_version
3456 ,p_init_msg_list => p_init_msg_list
3457 ,x_return_status => l_return_status
3458 ,x_msg_count => x_msg_count
3459 ,x_msg_data => x_msg_data
3460 ,p_khr_id => p_khr_id
3461 ,p_cancel_date => lp_effective_date);
3462
3463
3464 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3465 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3466 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3467 RAISE Okl_Api.G_EXCEPTION_ERROR;
3468 END IF;
3469
3470 END IF; -- p_stream_type_subclass
3471
3472 -- Update contract header, if needed
3473 l_khr_active := FALSE;
3474 FOR l_okl_poc_khr_rec IN l_okl_poc_khr_csr
3475 LOOP
3476 l_khr_active := TRUE;
3477 END LOOP;
3478
3479 IF NOT l_khr_active THEN
3480
3481 lp_chrv_rec.id := p_khr_id;
3482 lp_khrv_rec.id := p_khr_id;
3483 lp_khrv_rec.securitized_code := G_SECURITIZED_CODE_N;
3484
3485 Okl_Contract_Pub.update_contract_header(
3486 p_api_version => p_api_version,
3487 p_init_msg_list => p_init_msg_list,
3488 x_return_status => l_return_status,
3489 x_msg_count => x_msg_count,
3490 x_msg_data => x_msg_data,
3491 p_chrv_rec => lp_chrv_rec,
3492 p_khrv_rec => lp_khrv_rec,
3493 x_chrv_rec => lx_chrv_rec,
3494 x_khrv_rec => lx_khrv_rec);
3495
3496 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3497 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3498 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3499 RAISE Okl_Api.G_EXCEPTION_ERROR;
3500 END IF;
3501
3502 END IF;
3503
3504
3505 END LOOP; -- l_okl_pol_csr
3506
3507
3508 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count
3509 ,x_msg_data => x_msg_data);
3510
3511 x_return_status := l_return_status;
3512
3513
3514 EXCEPTION
3515 WHEN G_EXCEPTION_ERROR THEN
3516
3517 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3518 p_pkg_name => G_PKG_NAME,
3519 p_exc_name => G_EXC_NAME_ERROR,
3520 x_msg_count => x_msg_count,
3521 x_msg_data => x_msg_data,
3522 p_api_type => G_API_TYPE);
3523
3524 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
3525
3526 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3527 p_pkg_name => G_PKG_NAME,
3528 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
3529 x_msg_count => x_msg_count,
3530 x_msg_data => x_msg_data,
3531 p_api_type => G_API_TYPE);
3532 WHEN OTHERS THEN
3533
3534 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3535 p_pkg_name => G_PKG_NAME,
3536 p_exc_name => G_EXC_NAME_OTHERS,
3537 x_msg_count => x_msg_count,
3538 x_msg_data => x_msg_data,
3539 p_api_type => G_API_TYPE);
3540
3541 END buyback_pool_contents;
3542
3543
3544 END Okl_Securitization_Pvt;