[Home] [Help]
PACKAGE BODY: APPS.OKL_POOL_PVT
Source
1 PACKAGE BODY Okl_Pool_Pvt AS
2 /* $Header: OKLRSZPB.pls 120.30 2008/02/14 05:12:07 ankushar noship $ */
3 ----------------------------------------------------------------------------
4 -- Global Message Constants
5 ----------------------------------------------------------------------------
6 G_STS_CODE VARCHAR2(10) := 'NEW';
7
8 G_REJECT_DUP_POCS CONSTANT VARCHAR2(2) := '01';
9 G_REJECT_VARIABLE_INTEREST CONSTANT VARCHAR2(2) := '02';
10 G_REJECT_REV_KHR CONSTANT VARCHAR2(2) := '03';
11 G_REJECT_SPLIT_ASSET CONSTANT VARCHAR2(2) := '04';
12 G_REJECT_DELINQ_KHR CONSTANT VARCHAR2(2) := '05';
13 G_REJECT_ASSET_TERMINATED CONSTANT VARCHAR2(2) := '06';
14 G_REJECT_LEGAL_ENTITY_MISMATCH CONSTANT VARCHAR2(2) := '07';
15
16 G_POOL_ADD_TBL_HDR CONSTANT VARCHAR2(30) := 'OKL_POOL_ADD_TBL_HDR';
17 G_POOL_ADD_NEW_TBL_HDR CONSTANT VARCHAR2(25) := 'OKL_POOL_ADD_NEW_TBL_HDR';
18 G_ROW_NUMBER CONSTANT VARCHAR2(14) := 'OKL_ROW_NUMBER';
19 G_CONTRACT_NUMBER CONSTANT VARCHAR2(25) := 'OKL_GLP_RPT_CTR_NUM_TITLE';
20 G_ASSET_NUMBER CONSTANT VARCHAR2(16) := 'OKL_ASSET_NUMBER';
21 G_LESSEE CONSTANT VARCHAR2(10) := 'OKL_LESSEE';
22 G_STREAM_TYPE_SUBCLASS CONSTANT VARCHAR2(25) := 'OKL_STREAM_TYPE_SUBCLASS';
23 G_REJECT_REASON_CODE CONSTANT VARCHAR2(25) := 'OKL_REJECT_REASON_CODE';
24 G_REJECT_REASON_CODES CONSTANT VARCHAR2(25) := 'OKL_REJECT_REASON_CODES';
25 -- sosharma added codes for adjustment
26 G_POOL_TRX_ADD CONSTANT VARCHAR2(30) := 'ADD';
27 G_POOL_TRX_REASON_ADJUST CONSTANT VARCHAR2(30) := 'ADJUSTMENTS';
28 -- sosharma added codes for tranaction_status
29 G_POOL_TRX_STATUS_NEW CONSTANT VARCHAR2(30) := 'NEW';
30 G_POOL_TRX_STATUS_APPREJ CONSTANT VARCHAR2(30) := 'APPROVAL_REJECTED';
31 G_POOL_TRX_STATUS_APPROVED CONSTANT VARCHAR2(30) := 'APPROVED';
32 G_POOL_TRX_STATUS_INCOMPLETE CONSTANT VARCHAR2(30) := 'INCOMPLETE ';
33 G_POOL_TRX_STATUS_COMPLETE CONSTANT VARCHAR2(30) := 'COMPLETE ';
34
35 TYPE msg_rec_type IS RECORD ( msg VARCHAR2(150) );
36 TYPE msg_tbl_type IS TABLE OF msg_rec_type INDEX BY BINARY_INTEGER;
37
38 --Added by kthiruva on 21-Nov -2007 to determine the Pool Status
39 --Bug 6640050 - Start of Changes
40 CURSOR pool_status_csr(p_pol_id IN NUMBER)
41 IS
42 SELECT status_code
43 FROM okl_pools
44 WHERE id = p_pol_id;
45 --Bug 6640050 - End of Changes
46
47 -- Cursor for getting the status of the open transaction
48 CURSOR l_trans_status_csr(p_pol_id IN NUMBER)
49 IS
50 SELECT transaction_status,id FROM OKL_POOL_TRANSACTIONS pools
51 where pools.transaction_status <> G_POOL_TRX_STATUS_COMPLETE
52 and pools.transaction_type='ADD' and pools.transaction_reason='ADJUSTMENTS'
53 and pools.pol_id=p_pol_id;
54
55
56
57 ----------------------------------------------------------------------------
58 -- Procedures and Functions
59 ----------------------------------------------------------------------------
60 ----------------------------------------------------------------------------------
61 -- Start of comments
62 --
63 -- Procedure Name : update_pool_status
64 -- Description : updates a pool header, and contents' status. This utility IS
65 -- used for sync 'ACTIVE', and 'EXPIRED' status only
66 -- Business Rules :
67 -- Parameters :
68 -- Version : 1.0
69 -- End of comments
70 ----------------------------------------------------------------------------------
71 PROCEDURE update_pool_status(
72 p_api_version IN NUMBER
73 ,p_init_msg_list IN VARCHAR2
74 ,x_return_status OUT NOCOPY VARCHAR2
75 ,x_msg_count OUT NOCOPY NUMBER
76 ,x_msg_data OUT NOCOPY VARCHAR2
77 ,p_pool_status IN okl_pools.status_code%TYPE
78 ,p_pol_id IN okl_pools.id%TYPE)
79 IS
80 l_api_name CONSTANT VARCHAR2(30) := 'update_pool_status';
81 l_api_version CONSTANT NUMBER := 1.0;
82 i NUMBER;
83 l_pol_id OKL_POOLS.ID%TYPE;
84 l_currency_code OKL_POOLS.CURRENCY_CODE%TYPE;
85 l_org_id OKL_POOLS.ORG_ID%TYPE;
86 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
87
88 lp_polv_rec polv_rec_type;
89 lx_polv_rec polv_rec_type;
90 l_poc_id OKL_POOL_CONTENTS.ID%TYPE;
91
92 lp_pocv_rec pocv_rec_type;
93 lx_pocv_rec pocv_rec_type;
94
95
96 CURSOR c_poc IS
97 SELECT poc.id
98 FROM okl_pool_contents poc
99 WHERE poc.pol_id = p_pol_id
100 ;
101
102 BEGIN
103 -- Set API savepoint
104 SAVEPOINT update_pool_status_PVT;
105
106 -- Check for call compatibility
107 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
108 p_api_version,
109 l_api_name,
110 G_PKG_NAME ))
111 THEN
112 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
113 END IF;
114
115 -- Initialize message list if requested
116 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
117 Fnd_Msg_Pub.initialize;
118 END IF;
119
120 -- Initialize API status to success
121 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
122
123
124 /*** Begin API body ****************************************************/
125 ----------------------------------------------------------------------------
126 --1. update pool status
127 ----------------------------------------------------------------------------
128 --DBMS_OUTPUT.PUT_LINE('1. update pool header status');
129 --DBMS_OUTPUT.PUT_LINE('OKL_POOL_PVT.update_pool start');
130
131 lp_polv_rec.ID := p_pol_id;
132 lp_polv_rec.status_code := p_pool_status;
133
134 Okl_Pool_Pvt.update_pool(
135 p_api_version => p_api_version,
136 p_init_msg_list => p_init_msg_list,
137 x_return_status => x_return_status,
138 x_msg_count => x_msg_count,
139 x_msg_data => x_msg_data,
140 p_polv_rec => lp_polv_rec,
141 x_polv_rec => lx_polv_rec);
142
143 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
144 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
145 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
146 RAISE Okl_Api.G_EXCEPTION_ERROR;
147 END IF;
148
149 ----------------------------------------------------------------------------
150 --2. update pool contents status
151 ----------------------------------------------------------------------------
152 --DBMS_OUTPUT.PUT_LINE('2. update pool contents status');
153 --DBMS_OUTPUT.PUT_LINE('OKL_POOL_PVT.update_pool_contents start');
154
155 OPEN c_poc;
156 LOOP
157 FETCH c_poc INTO
158 l_poc_id;
159 EXIT WHEN c_poc%NOTFOUND;
160
161 lp_pocv_rec.id := l_poc_id;
162 lp_pocv_rec.status_code := p_pool_status;
163
164 Okl_Pool_Pvt.update_pool_contents(
165 p_api_version => p_api_version,
166 p_init_msg_list => p_init_msg_list,
167 x_return_status => x_return_status,
168 x_msg_count => x_msg_count,
169 x_msg_data => x_msg_data,
170 p_pocv_rec => lp_pocv_rec,
171 x_pocv_rec => lx_pocv_rec);
172
173 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
174 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
175 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
176 RAISE Okl_Api.G_EXCEPTION_ERROR;
177 END IF;
178
179 END LOOP;
180 CLOSE c_poc;
181
182 --DBMS_OUTPUT.PUT_LINE('OKL_POOL_PVT.update_pool_status end');
183
184 /*** End API body ******************************************************/
185
186 -- Get message count and if count is 1, get message info
187 Fnd_Msg_Pub.Count_And_Get
188 (p_count => x_msg_count,
189 p_data => x_msg_data);
190
191 EXCEPTION
192 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
193 ROLLBACK TO update_pool_status_PVT;
194 x_return_status := Okl_Api.G_RET_STS_ERROR;
195 Fnd_Msg_Pub.Count_And_Get
196 (p_count => x_msg_count,
197 p_data => x_msg_data);
198
199 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
200 ROLLBACK TO update_pool_status_PVT;
201 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
202 Fnd_Msg_Pub.Count_And_Get
203 (p_count => x_msg_count,
204 p_data => x_msg_data);
205
206 WHEN OTHERS THEN
207 ROLLBACK TO update_pool_status_PVT;
208 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
209 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
210 p_msg_name => G_UNEXPECTED_ERROR,
211 p_token1 => G_SQLCODE_TOKEN,
212 p_token1_value => SQLCODE,
213 p_token2 => G_SQLERRM_TOKEN,
214 p_token2_value => SQLERRM);
215 Fnd_Msg_Pub.Count_And_Get
216 (p_count => x_msg_count,
217 p_data => x_msg_data);
218
219 END update_pool_status;
220
221 ----------------------------------------------------------------------------------
222 -- Start of comments
223 --
224 -- Procedure Name : create_pool
225 -- Description : wrapper api for create pool
226 -- Business Rules :
227 -- Parameters :
228 -- Version : 1.0
229 -- End of comments
230 ----------------------------------------------------------------------------------
231 PROCEDURE create_pool(
232 p_api_version IN NUMBER
233 ,p_init_msg_list IN VARCHAR2
234 ,x_return_status OUT NOCOPY VARCHAR2
235 ,x_msg_count OUT NOCOPY NUMBER
236 ,x_msg_data OUT NOCOPY VARCHAR2
237 ,p_polv_rec IN polv_rec_type
238 ,x_polv_rec OUT NOCOPY polv_rec_type
239 ) IS
240 l_api_name CONSTANT VARCHAR2(30) := 'create_pool_pvt';
241 l_api_version CONSTANT NUMBER := 1.0;
242 i NUMBER;
243 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
244
245
246 l_polv_rec polv_rec_type := p_polv_rec;
247 -- x_polv_rec polv_rec_type;
248
249 BEGIN
250 -- Set API savepoint
251 SAVEPOINT create_pool_PVT;
252
253 -- Check for call compatibility
254 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
255 p_api_version,
256 l_api_name,
257 G_PKG_NAME ))
258 THEN
259 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
260 END IF;
261
262 -- Initialize message list if requested
263 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
264 Fnd_Msg_Pub.initialize;
265 END IF;
266
267 -- Initialize API status to success
268 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
269
270
271 /*** Begin API body ****************************************************/
272
273 l_polv_rec.DATE_CREATED := SYSDATE;
274 l_polv_rec.DATE_LAST_UPDATED := SYSDATE;
275 l_polv_rec.STATUS_CODE := G_STS_CODE; -- default to 'NEW'
276
277 Okl_Pol_Pvt.insert_row(
278 p_api_version => p_api_version,
279 p_init_msg_list => p_init_msg_list,
280 x_return_status => x_return_status,
281 x_msg_count => x_msg_count,
282 x_msg_data => x_msg_data,
283 p_polv_rec => l_polv_rec,
284 x_polv_rec => x_polv_rec);
285
286 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
287 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
288 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
289 RAISE Okl_Api.G_EXCEPTION_ERROR;
290 END IF;
291
292 /*** End API body ******************************************************/
293
294 -- Get message count and if count is 1, get message info
295 Fnd_Msg_Pub.Count_And_Get
296 (p_count => x_msg_count,
297 p_data => x_msg_data);
298
299 EXCEPTION
300 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
301 ROLLBACK TO create_pool_PVT;
302 x_return_status := Okl_Api.G_RET_STS_ERROR;
303 Fnd_Msg_Pub.Count_And_Get
304 (p_count => x_msg_count,
305 p_data => x_msg_data);
306
307 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
308 ROLLBACK TO create_pool_PVT;
309 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
310 Fnd_Msg_Pub.Count_And_Get
311 (p_count => x_msg_count,
312 p_data => x_msg_data);
313
314 WHEN OTHERS THEN
315 ROLLBACK TO create_pool_PVT;
316 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
317
318
319
320
321
322
323 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
324 p_msg_name => G_UNEXPECTED_ERROR,
325 p_token1 => G_SQLCODE_TOKEN,
326 p_token1_value => SQLCODE,
327 p_token2 => G_SQLERRM_TOKEN,
328 p_token2_value => SQLERRM);
329 Fnd_Msg_Pub.Count_And_Get
330 (p_count => x_msg_count,
331 p_data => x_msg_data);
332
333 END create_pool;
334 ----------------------------------------------------------------------------------
335 -- Start of comments
336 --
337 -- Procedure Name : update_pool
338 -- Description : wrapper api for update pool
339 -- Business Rules :
340 -- Parameters :
341 -- Version : 1.0
342 -- End of comments
343 ----------------------------------------------------------------------------------
344 PROCEDURE update_pool(
345 p_api_version IN NUMBER
346 ,p_init_msg_list IN VARCHAR2
347 ,x_return_status OUT NOCOPY VARCHAR2
348 ,x_msg_count OUT NOCOPY NUMBER
349 ,x_msg_data OUT NOCOPY VARCHAR2
350 ,p_polv_rec IN polv_rec_type
351 ,x_polv_rec OUT NOCOPY polv_rec_type
352 ) IS
353 l_api_name CONSTANT VARCHAR2(30) := 'update_pool_pvt';
354 l_api_version CONSTANT NUMBER := 1.0;
355 i NUMBER;
356 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
357
358 l_polv_rec polv_rec_type := p_polv_rec;
359 -- x_polv_rec polv_rec_type;
360
361 BEGIN
362 -- Set API savepoint
363 SAVEPOINT update_pool_PVT;
364
365 -- Check for call compatibility
366 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
367 p_api_version,
368 l_api_name,
369 G_PKG_NAME ))
370 THEN
371 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
372 END IF;
373
374 -- Initialize message list if requested
375 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
376 Fnd_Msg_Pub.initialize;
377 END IF;
378
379 -- Initialize API status to success
380 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
381
382
383 /*** Begin API body ****************************************************/
384
385 l_polv_rec.DATE_LAST_UPDATED := SYSDATE;
386 -- 12/30/02 fixed
387 -- l_polv_rec.TOTAL_RECEIVABLE_AMOUNT := get_tot_receivable_amt(p_pol_id => l_polv_rec.ID);
388 -- l_polv_rec.TOTAL_PRINCIPAL_AMOUNT := get_tot_principal_amt(p_pol_id => l_polv_rec.ID);
389 -- l_polv_rec.DATE_TOTAL_PRINCIPAL_CALC := SYSDATE;
390
391
392 -- fmiao 12/09/05 fix OBJECT_VERSION_NUMBER
393
394 IF (l_polv_rec.object_version_number IS NULL) THEN
395 l_polv_rec.object_version_number :=Okl_Api.G_MISS_NUM;
396 END IF;
397
398 IF (l_polv_rec.date_created IS NULL) THEN
399 l_polv_rec.date_created :=Okl_Api.G_MISS_DATE;
400 END IF;
401
402 IF (l_polv_rec.created_by IS NULL) THEN
403 l_polv_rec.created_by :=Okl_Api.G_MISS_NUM;
404 END IF;
405 IF (l_polv_rec.creation_date IS NULL) THEN
406 l_polv_rec.creation_date :=Okl_Api.G_MISS_DATE;
407 END IF;
408 IF (l_polv_rec.last_updated_by IS NULL) THEN
409 l_polv_rec.last_updated_by :=Okl_Api.G_MISS_NUM;
410 END IF;
411 IF (l_polv_rec.last_update_date IS NULL) THEN
412 l_polv_rec.last_update_date :=Okl_Api.G_MISS_DATE;
413 END IF;
414 IF (l_polv_rec.last_update_login IS NULL) THEN
415 l_polv_rec.last_update_login :=Okl_Api.G_MISS_NUM;
416 END IF;
417 -- end fmiao 12/09/05 fix OBJECT_VERSION_NUMBER
418
419 Okl_Pol_Pvt.update_row(
420 p_api_version => p_api_version,
421 p_init_msg_list => p_init_msg_list,
422 x_return_status => x_return_status,
423 x_msg_count => x_msg_count,
424 x_msg_data => x_msg_data,
425 p_polv_rec => l_polv_rec,
426 x_polv_rec => x_polv_rec);
427
428 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
429 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
430 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
431 RAISE Okl_Api.G_EXCEPTION_ERROR;
432 END IF;
433
434 /*** End API body ******************************************************/
435
436 -- Get message count and if count is 1, get message info
437 Fnd_Msg_Pub.Count_And_Get
438 (p_count => x_msg_count,
439 p_data => x_msg_data);
440
441 EXCEPTION
442 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
443 ROLLBACK TO update_pool_PVT;
444 x_return_status := Okl_Api.G_RET_STS_ERROR;
445 Fnd_Msg_Pub.Count_And_Get
446 (p_count => x_msg_count,
447 p_data => x_msg_data);
448
449 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
450 ROLLBACK TO update_pool_PVT;
451 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
452 Fnd_Msg_Pub.Count_And_Get
453 (p_count => x_msg_count,
454 p_data => x_msg_data);
455
456
457
458
459
460 WHEN OTHERS THEN
461 ROLLBACK TO update_pool_PVT;
462 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
463 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
464 p_msg_name => G_UNEXPECTED_ERROR,
465 p_token1 => G_SQLCODE_TOKEN,
466 p_token1_value => SQLCODE,
467 p_token2 => G_SQLERRM_TOKEN,
468 p_token2_value => SQLERRM);
469 Fnd_Msg_Pub.Count_And_Get
470 (p_count => x_msg_count,
471 p_data => x_msg_data);
472 END update_pool;
473 ----------------------------------------------------------------------------------
474 -- Start of comments
475 --
476 -- Procedure Name : delete_pool
477 -- Description : wrapper api for delete pool
478 -- Business Rules :
479 -- Parameters :
480 -- Version : 1.0
481 -- End of comments
482 ----------------------------------------------------------------------------------
483 PROCEDURE delete_pool(
484 p_api_version IN NUMBER
485 ,p_init_msg_list IN VARCHAR2
486 ,x_return_status OUT NOCOPY VARCHAR2
487 ,x_msg_count OUT NOCOPY NUMBER
488 ,x_msg_data OUT NOCOPY VARCHAR2
489 ,p_polv_rec IN polv_rec_type
490 ) IS
491 l_api_name CONSTANT VARCHAR2(30) := 'delete_pool_pvt';
492 l_api_version CONSTANT NUMBER := 1.0;
493 i NUMBER;
494 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
495
496 l_polv_rec polv_rec_type := p_polv_rec;
497 -- x_polv_rec polv_rec_type;
498
499 BEGIN
500 -- Set API savepoint
501 SAVEPOINT delete_pool_PVT;
502
503 -- Check for call compatibility
504 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
505 p_api_version,
506 l_api_name,
507 G_PKG_NAME ))
508 THEN
509 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
510 END IF;
511
512 -- Initialize message list if requested
513 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
514 Fnd_Msg_Pub.initialize;
515 END IF;
516
517 -- Initialize API status to success
518 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
519
520
521 /*** Begin API body ****************************************************/
522
523 Okl_Pol_Pvt.delete_row(
524 p_api_version => p_api_version,
525 p_init_msg_list => p_init_msg_list,
526 x_return_status => x_return_status,
527 x_msg_count => x_msg_count,
528 x_msg_data => x_msg_data,
529 p_polv_rec => l_polv_rec);
530
531 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
532 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
533 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
534 RAISE Okl_Api.G_EXCEPTION_ERROR;
535 END IF;
536
537 /*** End API body ******************************************************/
538
539 -- Get message count and if count is 1, get message info
540 Fnd_Msg_Pub.Count_And_Get
541 (p_count => x_msg_count,
542 p_data => x_msg_data);
543
544 EXCEPTION
545 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
546 ROLLBACK TO delete_pool_PVT;
547 x_return_status := Okl_Api.G_RET_STS_ERROR;
548 Fnd_Msg_Pub.Count_And_Get
549 (p_count => x_msg_count,
550 p_data => x_msg_data);
551
552 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
553 ROLLBACK TO delete_pool_PVT;
554 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
555 Fnd_Msg_Pub.Count_And_Get
556 (p_count => x_msg_count,
557 p_data => x_msg_data);
558
559 WHEN OTHERS THEN
560 ROLLBACK TO delete_pool_PVT;
561 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
562 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
563 p_msg_name => G_UNEXPECTED_ERROR,
564 p_token1 => G_SQLCODE_TOKEN,
565 p_token1_value => SQLCODE,
566 p_token2 => G_SQLERRM_TOKEN,
567 p_token2_value => SQLERRM);
568 Fnd_Msg_Pub.Count_And_Get
569 (p_count => x_msg_count,
570 p_data => x_msg_data);
571 END delete_pool;
572 ----------------------------------------------------------------------------------
573 -- Start of comments
574 --
575 -- Procedure Name : create_pool_contents
576 -- Description : wrapper api for create pool contents
577 -- Business Rules :
578 -- Parameters :
579 -- Version : 1.0
580 -- End of comments
581 ----------------------------------------------------------------------------------
582 PROCEDURE create_pool_contents(
583 p_api_version IN NUMBER
584 ,p_init_msg_list IN VARCHAR2
585 ,x_return_status OUT NOCOPY VARCHAR2
586 ,x_msg_count OUT NOCOPY NUMBER
587 ,x_msg_data OUT NOCOPY VARCHAR2
588 ,p_pocv_rec IN pocv_rec_type
589 ,x_pocv_rec OUT NOCOPY pocv_rec_type
590 ) IS
591 l_api_name CONSTANT VARCHAR2(30) := 'create_pool_contents_pvt';
592 l_api_version CONSTANT NUMBER := 1.0;
593 i NUMBER;
594 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
595
596 l_pocv_rec pocv_rec_type := p_pocv_rec;
597 -- x_pocv_rec pocv_rec_type;
598 --Added by kthiruva on 21-Nov-2007 for Bug 6640050
599 l_status_code okl_pools.status_Code%TYPE;
600
601 lp_polv_rec polv_rec_type;
602 lx_polv_rec polv_rec_type;
603
604 BEGIN
605 -- Set API savepoint
606 SAVEPOINT create_pool_contents_PVT;
607
608 -- Check for call compatibility
609 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
610 p_api_version,
611 l_api_name,
612 G_PKG_NAME ))
613 THEN
614 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
615 END IF;
616
617 -- Initialize message list if requested
618 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
619 Fnd_Msg_Pub.initialize;
620 END IF;
621
622 -- Initialize API status to success
623 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
624 FOR pool_status_rec IN pool_status_csr(p_pocv_rec.pol_id)
625 LOOP
626 l_status_code := pool_status_rec.status_code;
627 END LOOP;
628
629
630 /*** Begin API body ****************************************************/
631
632 --Modified by kthiruva on 21-Nov-2007
633 --If pool is active, new contents are created in status 'PENDING'
634 -- Bug 6640050 - start of changes
635 IF l_status_code = G_POL_STS_ACTIVE
636 THEN
637 l_pocv_rec.STATUS_CODE := G_POC_STS_PENDING;
638 ELSE
639 l_pocv_rec.STATUS_CODE := G_POC_STS_NEW; -- default to NEW status_code. cklee 04/14/03
640 END IF;
641 -- Bug 6640050 - end of changes
642
643 Okl_Poc_Pvt.insert_row(
644 p_api_version => p_api_version,
645 p_init_msg_list => p_init_msg_list,
646 x_return_status => x_return_status,
647 x_msg_count => x_msg_count,
648 x_msg_data => x_msg_data,
649 p_pocv_rec => l_pocv_rec,
650 x_pocv_rec => x_pocv_rec);
651
652 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
653 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
654 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
655 RAISE Okl_Api.G_EXCEPTION_ERROR;
656 END IF;
657
658 lp_polv_rec.ID := p_pocv_rec.POL_ID;
659
660 Okl_Pool_Pvt.update_pool(
661 p_api_version => p_api_version,
662 p_init_msg_list => p_init_msg_list,
663 x_return_status => x_return_status,
664 x_msg_count => x_msg_count,
665 x_msg_data => x_msg_data,
666 p_polv_rec => lp_polv_rec,
667 x_polv_rec => lx_polv_rec);
668
669 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
670 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
671 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
672
673 RAISE Okl_Api.G_EXCEPTION_ERROR;
674 END IF;
675
676 /*** End API body ******************************************************/
677
678 -- Get message count and if count is 1, get message info
679 Fnd_Msg_Pub.Count_And_Get
680 (p_count => x_msg_count,
681 p_data => x_msg_data);
682
683 EXCEPTION
684 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
685 ROLLBACK TO create_pool_contents_PVT;
686 x_return_status := Okl_Api.G_RET_STS_ERROR;
687 Fnd_Msg_Pub.Count_And_Get
688 (p_count => x_msg_count,
689 p_data => x_msg_data);
690
691 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
692 ROLLBACK TO create_pool_contents_pvt;
693 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
694 Fnd_Msg_Pub.Count_And_Get
695 (p_count => x_msg_count,
696 p_data => x_msg_data);
697
698 WHEN OTHERS THEN
699 ROLLBACK TO create_pool_contents_pvt;
700 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
701 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
702 p_msg_name => G_UNEXPECTED_ERROR,
703 p_token1 => G_SQLCODE_TOKEN,
704 p_token1_value => SQLCODE,
705 p_token2 => G_SQLERRM_TOKEN,
706 p_token2_value => SQLERRM);
707 Fnd_Msg_Pub.Count_And_Get
708 (p_count => x_msg_count,
709 p_data => x_msg_data);
710
711 END create_pool_contents;
712 ----------------------------------------------------------------------------------
713 -- Start of comments
714 --
715 -- Procedure Name : create_pool_contents
716 -- Description : wrapper api for create pool contents
717 -- Business Rules :
718 -- Parameters :
719 -- Version : 1.0
720 -- End of comments
721 ----------------------------------------------------------------------------------
722 PROCEDURE create_pool_contents(
723 p_api_version IN NUMBER
724 ,p_init_msg_list IN VARCHAR2
725 ,x_return_status OUT NOCOPY VARCHAR2
726 ,x_msg_count OUT NOCOPY NUMBER
727 ,x_msg_data OUT NOCOPY VARCHAR2
728 ,p_pocv_tbl IN pocv_tbl_type
729 ,x_pocv_tbl OUT NOCOPY pocv_tbl_type
730 ) IS
731 l_api_name CONSTANT VARCHAR2(30) := 'create_pool_contents_pvt';
732 l_api_version CONSTANT NUMBER := 1.0;
733 i NUMBER;
734 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
735
736 l_pocv_tbl pocv_tbl_type := p_pocv_tbl;
737 -- x_pocv_tbl pocv_tbl_type;
738 lp_polv_rec polv_rec_type;
739 lx_polv_rec polv_rec_type;
740
741 BEGIN
742 -- Set API savepoint
743 SAVEPOINT create_pool_contents_PVT2;
744
745 -- Check for call compatibility
746 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
747 p_api_version,
748 l_api_name,
749 G_PKG_NAME ))
750 THEN
751 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
752 END IF;
753
754 -- Initialize message list if requested
755 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
756 Fnd_Msg_Pub.initialize;
757 END IF;
758
759 -- Initialize API status to success
760 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
761
762
763 /*** Begin API body ****************************************************/
764
765 IF (l_pocv_tbl.COUNT > 0) THEN
766 i := l_pocv_tbl.FIRST;
767 LOOP
768
769 Okl_Pool_Pvt.create_pool_contents(
770 p_api_version => p_api_version,
771 p_init_msg_list => p_init_msg_list,
772 x_return_status => x_return_status,
773 x_msg_count => x_msg_count,
774 x_msg_data => x_msg_data,
775 p_pocv_rec => l_pocv_tbl(i),
776 x_pocv_rec => x_pocv_tbl(i));
777
778 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
779 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
780 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
781 RAISE Okl_Api.G_EXCEPTION_ERROR;
782 END IF;
783
784 EXIT WHEN (i = l_pocv_tbl.LAST);
785 i := l_pocv_tbl.NEXT(i);
786 END LOOP;
787 END IF;
788
789 /*** End API body ******************************************************/
790
791 -- Get message count and if count is 1, get message info
792 Fnd_Msg_Pub.Count_And_Get
793 (p_count => x_msg_count,
794 p_data => x_msg_data);
795
796 EXCEPTION
797 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
798 ROLLBACK TO create_pool_contents_PVT2;
799 x_return_status := Okl_Api.G_RET_STS_ERROR;
800 Fnd_Msg_Pub.Count_And_Get
801 (p_count => x_msg_count,
802 p_data => x_msg_data);
803
804 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
805 ROLLBACK TO create_pool_contents_pvt2;
806 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
807 Fnd_Msg_Pub.Count_And_Get
808 (p_count => x_msg_count,
809 p_data => x_msg_data);
810
811 WHEN OTHERS THEN
812 ROLLBACK TO create_pool_contents_pvt2;
813 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
814 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
815 p_msg_name => G_UNEXPECTED_ERROR,
816 p_token1 => G_SQLCODE_TOKEN,
817 p_token1_value => SQLCODE,
818 p_token2 => G_SQLERRM_TOKEN,
819 p_token2_value => SQLERRM);
820 Fnd_Msg_Pub.Count_And_Get
821 (p_count => x_msg_count,
822 p_data => x_msg_data);
823
824 END create_pool_contents;
825
826 ----------------------------------------------------------------------------------
827 -- Start of comments
828 --
829 -- Procedure Name : update_pool_contents
830 -- Description : wrapper api for update pool contents
831 -- Business Rules :
832 -- Parameters :
833 -- Version : 1.0
834 -- End of comments
835 ----------------------------------------------------------------------------------
836 PROCEDURE update_pool_contents(
837 p_api_version IN NUMBER
838 ,p_init_msg_list IN VARCHAR2
839 ,x_return_status OUT NOCOPY VARCHAR2
840 ,x_msg_count OUT NOCOPY NUMBER
841 ,x_msg_data OUT NOCOPY VARCHAR2
842 ,p_pocv_rec IN pocv_rec_type
843 ,x_pocv_rec OUT NOCOPY pocv_rec_type
844 ) IS
845 l_api_name CONSTANT VARCHAR2(30) := 'update_pool_contents_pvt';
846 l_api_version CONSTANT NUMBER := 1.0;
847 i NUMBER;
848 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
849
850 l_pocv_rec pocv_rec_type := p_pocv_rec;
851 -- x_pocv_rec pocv_rec_type;
852 lp_polv_rec polv_rec_type;
853 lx_polv_rec polv_rec_type;
854
855 CURSOR c_pol(p_id okl_pool_contents.id%TYPE) IS
856 SELECT pol_id
857 FROM okl_pool_contents
858 WHERE id = p_id
859 ;
860
861 BEGIN
862 -- Set API savepoint
863 SAVEPOINT update_pool_contents_pvt;
864
865 -- Check for call compatibility
866 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
867 p_api_version,
868 l_api_name,
869 G_PKG_NAME ))
870 THEN
871 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
872 END IF;
873
874 -- Initialize message list if requested
875 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
876 Fnd_Msg_Pub.initialize;
877 END IF;
878
879 -- Initialize API status to success
880 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
881
882
883 /*** Begin API body ****************************************************/
884
885 -- 12/30/02 fixed
886 OPEN c_pol(l_pocv_rec.ID);
887 FETCH c_pol INTO lp_polv_rec.ID;
888 CLOSE c_pol;
889
890 Okl_Poc_Pvt.update_row(
891 p_api_version => p_api_version,
892 p_init_msg_list => p_init_msg_list,
893 x_return_status => x_return_status,
894 x_msg_count => x_msg_count,
895 x_msg_data => x_msg_data,
896 p_pocv_rec => l_pocv_rec,
897 x_pocv_rec => x_pocv_rec);
898
899 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
900 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
901 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
902 RAISE Okl_Api.G_EXCEPTION_ERROR;
903 END IF;
904
905 -- lp_polv_rec.ID := p_pocv_rec.POL_ID;
906
907 Okl_Pool_Pvt.update_pool(
908 p_api_version => p_api_version,
909 p_init_msg_list => p_init_msg_list,
910 x_return_status => x_return_status,
911 x_msg_count => x_msg_count,
912 x_msg_data => x_msg_data,
913 p_polv_rec => lp_polv_rec,
914 x_polv_rec => lx_polv_rec);
915
916 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
917 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
918 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
919 RAISE Okl_Api.G_EXCEPTION_ERROR;
920 END IF;
921
922 /*** End API body ******************************************************/
923
924 -- Get message count and if count is 1, get message info
925 Fnd_Msg_Pub.Count_And_Get
926 (p_count => x_msg_count,
927 p_data => x_msg_data);
928
929
930 EXCEPTION
931 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
932 ROLLBACK TO update_pool_contents_PVT;
933
934
935
936
937 x_return_status := Okl_Api.G_RET_STS_ERROR;
938 Fnd_Msg_Pub.Count_And_Get
939 (p_count => x_msg_count,
940 p_data => x_msg_data);
941
942 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
943 ROLLBACK TO update_pool_contents_pvt;
944 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
945 Fnd_Msg_Pub.Count_And_Get
946 (p_count => x_msg_count,
947 p_data => x_msg_data);
948
949 WHEN OTHERS THEN
950 ROLLBACK TO update_pool_contents_pvt;
951 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
952 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
953 p_msg_name => G_UNEXPECTED_ERROR,
954 p_token1 => G_SQLCODE_TOKEN,
955 p_token1_value => SQLCODE,
956 p_token2 => G_SQLERRM_TOKEN,
957 p_token2_value => SQLERRM);
958 Fnd_Msg_Pub.Count_And_Get
959 (p_count => x_msg_count,
960 p_data => x_msg_data);
961
962 END update_pool_contents;
963 ----------------------------------------------------------------------------------
964 -- Start of comments
965 --
966 -- Procedure Name : update_pool_contents
967 -- Description : wrapper api for update pool contents
968 -- Business Rules :
969 -- Parameters :
970 -- Version : 1.0
971 -- End of comments
972 ----------------------------------------------------------------------------------
973 PROCEDURE update_pool_contents(
974 p_api_version IN NUMBER
975 ,p_init_msg_list IN VARCHAR2
976 ,x_return_status OUT NOCOPY VARCHAR2
977 ,x_msg_count OUT NOCOPY NUMBER
978 ,x_msg_data OUT NOCOPY VARCHAR2
979 ,p_pocv_tbl IN pocv_tbl_type
980 ,x_pocv_tbl OUT NOCOPY pocv_tbl_type
981 ) IS
982 l_api_name CONSTANT VARCHAR2(30) := 'update_pool_contents_pvt';
983 l_api_version CONSTANT NUMBER := 1.0;
984 i NUMBER;
985 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
986
987 l_pocv_tbl pocv_tbl_type := p_pocv_tbl;
988 -- x_pocv_tbl pocv_tbl_type;
989 lp_polv_rec polv_rec_type;
990 lx_polv_rec polv_rec_type;
991
992 BEGIN
993 -- Set API savepoint
994 SAVEPOINT update_pool_contents_PVT2;
995
996 -- Check for call compatibility
997 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
998 p_api_version,
999 l_api_name,
1000 G_PKG_NAME ))
1001 THEN
1002 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1003 END IF;
1004
1005 -- Initialize message list if requested
1006 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
1007 Fnd_Msg_Pub.initialize;
1008 END IF;
1009
1010 -- Initialize API status to success
1011 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1012
1013
1014 /*** Begin API body ****************************************************/
1015
1016 IF (l_pocv_tbl.COUNT > 0) THEN
1017 i := l_pocv_tbl.FIRST;
1018 LOOP
1019
1020 Okl_Pool_Pvt.update_pool_contents(
1021 p_api_version => p_api_version,
1022 p_init_msg_list => p_init_msg_list,
1023 x_return_status => x_return_status,
1024 x_msg_count => x_msg_count,
1025 x_msg_data => x_msg_data,
1026 p_pocv_rec => l_pocv_tbl(i),
1027 x_pocv_rec => x_pocv_tbl(i));
1028
1029 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1030 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1031 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1032 RAISE Okl_Api.G_EXCEPTION_ERROR;
1033 END IF;
1034
1035 EXIT WHEN (i = l_pocv_tbl.LAST);
1036 i := l_pocv_tbl.NEXT(i);
1037 END LOOP;
1038 END IF;
1039
1040 /*** End API body ******************************************************/
1041
1042 -- Get message count and if count is 1, get message info
1043 Fnd_Msg_Pub.Count_And_Get
1044 (p_count => x_msg_count,
1045 p_data => x_msg_data);
1046
1047 EXCEPTION
1048 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1049 ROLLBACK TO update_pool_contents_PVT2;
1050 x_return_status := Okl_Api.G_RET_STS_ERROR;
1051 Fnd_Msg_Pub.Count_And_Get
1052 (p_count => x_msg_count,
1053 p_data => x_msg_data);
1054
1055 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1056 ROLLBACK TO update_pool_contents_pvt2;
1057 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1058 Fnd_Msg_Pub.Count_And_Get
1059 (p_count => x_msg_count,
1060 p_data => x_msg_data);
1061
1062 WHEN OTHERS THEN
1063 ROLLBACK TO update_pool_contents_pvt2;
1064 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
1065 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
1066 p_msg_name => G_UNEXPECTED_ERROR,
1067 p_token1 => G_SQLCODE_TOKEN,
1068 p_token1_value => SQLCODE,
1069 p_token2 => G_SQLERRM_TOKEN,
1070 p_token2_value => SQLERRM);
1071 Fnd_Msg_Pub.Count_And_Get
1072 (p_count => x_msg_count,
1073 p_data => x_msg_data);
1074
1075 END update_pool_contents;
1076
1077 ----------------------------------------------------------------------------------
1078 -- Start of comments
1079 --
1080 -- Procedure Name : delete_pool_contents
1081 -- Description : wrapper api for delele pool contents
1082 -- Business Rules :
1083 -- Parameters :
1084 -- Version : 1.0
1085 -- End of comments
1086 ----------------------------------------------------------------------------------
1087 PROCEDURE delete_pool_contents(
1088 p_api_version IN NUMBER
1089 ,p_init_msg_list IN VARCHAR2
1090 ,x_return_status OUT NOCOPY VARCHAR2
1091 ,x_msg_count OUT NOCOPY NUMBER
1092 ,x_msg_data OUT NOCOPY VARCHAR2
1093 ,p_pocv_rec IN pocv_rec_type
1094 ) IS
1095 l_api_name CONSTANT VARCHAR2(30) := 'delete_pool_contents_pvt';
1096 l_api_version CONSTANT NUMBER := 1.0;
1097 i NUMBER;
1098 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1099
1100 l_pocv_rec pocv_rec_type := p_pocv_rec;
1101 -- x_pocv_rec pocv_rec_type;
1102
1103 lp_polv_rec polv_rec_type;
1104 lx_polv_rec polv_rec_type;
1105
1106 CURSOR c_pol(p_id okl_pool_contents.id%TYPE) IS
1107 SELECT pol_id
1108 FROM okl_pool_contents
1109 WHERE id = p_id
1110 ;
1111
1112 BEGIN
1113 -- Set API savepoint
1114 SAVEPOINT delete_pool_contents_pvt;
1115
1116 -- Check for call compatibility
1117 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
1118 p_api_version,
1119 l_api_name,
1120 G_PKG_NAME ))
1121 THEN
1122 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1123 END IF;
1124
1125 -- Initialize message list if requested
1126 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
1127 Fnd_Msg_Pub.initialize;
1128 END IF;
1129
1130 -- Initialize API status to success
1131 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1132
1133
1134 /*** Begin API body ****************************************************/
1135
1136 OPEN c_pol(l_pocv_rec.ID);
1137 FETCH c_pol INTO lp_polv_rec.ID;
1138 CLOSE c_pol;
1139
1140 Okl_Poc_Pvt.delete_row(
1141 p_api_version => p_api_version,
1142 p_init_msg_list => p_init_msg_list,
1143 x_return_status => x_return_status,
1144 x_msg_count => x_msg_count,
1145 x_msg_data => x_msg_data,
1146 p_pocv_rec => l_pocv_rec);
1147
1148 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1149 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1150 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1151 RAISE Okl_Api.G_EXCEPTION_ERROR;
1152 END IF;
1153
1154 -- lp_polv_rec.ID := p_pocv_rec.POL_ID;
1155
1156 Okl_Pool_Pvt.update_pool(
1157 p_api_version => p_api_version,
1158 p_init_msg_list => p_init_msg_list,
1159 x_return_status => x_return_status,
1160 x_msg_count => x_msg_count,
1161 x_msg_data => x_msg_data,
1162 p_polv_rec => lp_polv_rec,
1163 x_polv_rec => lx_polv_rec);
1164
1165 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1166 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1167 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1168 RAISE Okl_Api.G_EXCEPTION_ERROR;
1169 END IF;
1170
1171 /*** End API body ******************************************************/
1172
1173 -- Get message count and if count is 1, get message info
1174 Fnd_Msg_Pub.Count_And_Get
1175 (p_count => x_msg_count,
1176 p_data => x_msg_data);
1177
1178 EXCEPTION
1179 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1180 ROLLBACK TO delete_pool_contents_PVT;
1181 x_return_status := Okl_Api.G_RET_STS_ERROR;
1182 Fnd_Msg_Pub.Count_And_Get
1183 (p_count => x_msg_count,
1184 p_data => x_msg_data);
1185
1186 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1187 ROLLBACK TO delete_pool_contents_pvt;
1188 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1189 Fnd_Msg_Pub.Count_And_Get
1190 (p_count => x_msg_count,
1191 p_data => x_msg_data);
1192
1193 WHEN OTHERS THEN
1194 ROLLBACK TO delete_pool_contents_pvt;
1195 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
1196 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
1197 p_msg_name => G_UNEXPECTED_ERROR,
1198 p_token1 => G_SQLCODE_TOKEN,
1199 p_token1_value => SQLCODE,
1200 p_token2 => G_SQLERRM_TOKEN,
1201 p_token2_value => SQLERRM);
1202 Fnd_Msg_Pub.Count_And_Get
1203 (p_count => x_msg_count,
1204 p_data => x_msg_data);
1205
1206 END delete_pool_contents;
1207
1208 ----------------------------------------------------------------------------------
1209 -- Start of comments
1210 --
1211 -- Procedure Name : delete_pool_contents
1212 -- Description : wrapper api for delele pool contents
1213 -- Business Rules :
1214 -- Parameters :
1215 -- Version : 1.0
1216 -- End of comments
1217 ----------------------------------------------------------------------------------
1218
1219 PROCEDURE delete_pool_contents(
1220 p_api_version IN NUMBER
1221 ,p_init_msg_list IN VARCHAR2
1222
1223 ,x_return_status OUT NOCOPY VARCHAR2
1224 ,x_msg_count OUT NOCOPY NUMBER
1225 ,x_msg_data OUT NOCOPY VARCHAR2
1226 ,p_pocv_tbl IN pocv_tbl_type
1227 ) IS
1228 l_api_name CONSTANT VARCHAR2(30) := 'delete_pool_contents_pvt';
1229 l_api_version CONSTANT NUMBER := 1.0;
1230 i NUMBER;
1231 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1232
1233 l_pocv_tbl pocv_tbl_type := p_pocv_tbl;
1234 -- x_pocv_tbl pocv_tbl_type;
1235 lp_polv_rec polv_rec_type;
1236 lx_polv_rec polv_rec_type;
1237
1238 BEGIN
1239 -- Set API savepoint
1240 SAVEPOINT delete_pool_contents_pvt2;
1241
1242 -- Check for call compatibility
1243 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
1244 p_api_version,
1245 l_api_name,
1246 G_PKG_NAME ))
1247 THEN
1248 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1249 END IF;
1250
1251 -- Initialize message list if requested
1252 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
1253 Fnd_Msg_Pub.initialize;
1254 END IF;
1255
1256 -- Initialize API status to success
1257 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1258
1259
1260 /*** Begin API body ****************************************************/
1261
1262 IF (l_pocv_tbl.COUNT > 0) THEN
1263 i := l_pocv_tbl.FIRST;
1264 LOOP
1265
1266 Okl_Pool_Pvt.delete_pool_contents(
1267 p_api_version => p_api_version,
1268 p_init_msg_list => p_init_msg_list,
1269 x_return_status => x_return_status,
1270 x_msg_count => x_msg_count,
1271
1272 x_msg_data => x_msg_data,
1273 p_pocv_rec => l_pocv_tbl(i));
1274
1275 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1276 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1277 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1278 RAISE Okl_Api.G_EXCEPTION_ERROR;
1279 END IF;
1280
1281 EXIT WHEN (i = l_pocv_tbl.LAST);
1282 i := l_pocv_tbl.NEXT(i);
1283 END LOOP;
1284
1285 END IF;
1286
1287 /*** End API body ******************************************************/
1288
1289 -- Get message count and if count is 1, get message info
1290 Fnd_Msg_Pub.Count_And_Get
1291 (p_count => x_msg_count,
1292 p_data => x_msg_data);
1293
1294 EXCEPTION
1295 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1296 ROLLBACK TO delete_pool_contents_PVT2;
1297 x_return_status := Okl_Api.G_RET_STS_ERROR;
1298 Fnd_Msg_Pub.Count_And_Get
1299 (p_count => x_msg_count,
1300 p_data => x_msg_data);
1301
1302 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1303 ROLLBACK TO delete_pool_contents_pvt2;
1304 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1305 Fnd_Msg_Pub.Count_And_Get
1306 (p_count => x_msg_count,
1307 p_data => x_msg_data);
1308
1309 WHEN OTHERS THEN
1310 ROLLBACK TO delete_pool_contents_pvt2;
1311 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
1312 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
1313 p_msg_name => G_UNEXPECTED_ERROR,
1314 p_token1 => G_SQLCODE_TOKEN,
1315 p_token1_value => SQLCODE,
1316 p_token2 => G_SQLERRM_TOKEN,
1317 p_token2_value => SQLERRM);
1318 Fnd_Msg_Pub.Count_And_Get
1319 (p_count => x_msg_count,
1320 p_data => x_msg_data);
1321
1322 END delete_pool_contents;
1323
1324 ----------------------------------------------------------------------------------
1325 -- Start of comments
1326 --
1327 -- Procedure Name : create_pool_transaction
1328 -- Description : wrapper api for create pool transaction
1329 -- Business Rules :
1330 -- Parameters :
1331 -- Version : 1.0
1332 -- End of comments
1333 ----------------------------------------------------------------------------------
1334 PROCEDURE create_pool_transaction(
1335 p_api_version IN NUMBER
1336 ,p_init_msg_list IN VARCHAR2
1337 ,x_return_status OUT NOCOPY VARCHAR2
1338 ,x_msg_count OUT NOCOPY NUMBER
1339 ,x_msg_data OUT NOCOPY VARCHAR2
1340 ,p_poxv_rec IN poxv_rec_type
1341 ,x_poxv_rec OUT NOCOPY poxv_rec_type
1342 )
1343 IS
1344 l_api_name CONSTANT VARCHAR2(30) := 'create_pool_transaction_pvt';
1345 l_api_version CONSTANT NUMBER := 1.0;
1346 i NUMBER;
1347 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1348
1349 l_poxv_rec poxv_rec_type := p_poxv_rec;
1350 -- x_poxv_rec poxv_rec_type;
1351
1352 BEGIN
1353 -- Set API savepoint
1354 SAVEPOINT create_pool_transaction_pvt;
1355
1356 -- Check for call compatibility
1357 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
1358 p_api_version,
1359 l_api_name,
1360 G_PKG_NAME ))
1361 THEN
1362
1363 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1364 END IF;
1365
1366 -- Initialize message list if requested
1367 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
1368 Fnd_Msg_Pub.initialize;
1369 END IF;
1370
1371 -- Initialize API status to success
1372 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1373
1374
1375 /*** Begin API body ****************************************************/
1376
1377 Okl_Pox_Pvt.insert_row(
1378 p_api_version => p_api_version,
1379 p_init_msg_list => p_init_msg_list,
1380 x_return_status => x_return_status,
1381 x_msg_count => x_msg_count,
1382 x_msg_data => x_msg_data,
1383 p_poxv_rec => l_poxv_rec,
1384 x_poxv_rec => x_poxv_rec);
1385
1386 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1387 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1388 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1389 RAISE Okl_Api.G_EXCEPTION_ERROR;
1390 END IF;
1391
1392 /*** End API body ******************************************************/
1393
1394 -- Get message count and if count is 1, get message info
1395 Fnd_Msg_Pub.Count_And_Get
1396 (p_count => x_msg_count,
1397 p_data => x_msg_data);
1398
1399 EXCEPTION
1400 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1401 ROLLBACK TO create_pool_transaction_PVT;
1402 x_return_status := Okl_Api.G_RET_STS_ERROR;
1403 Fnd_Msg_Pub.Count_And_Get
1404 (p_count => x_msg_count,
1405 p_data => x_msg_data);
1406
1407 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1408 ROLLBACK TO create_pool_transaction_pvt;
1409 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1410 Fnd_Msg_Pub.Count_And_Get
1411 (p_count => x_msg_count,
1412 p_data => x_msg_data);
1413
1414 WHEN OTHERS THEN
1415 ROLLBACK TO create_pool_transaction_pvt;
1416 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
1417 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
1418 p_msg_name => G_UNEXPECTED_ERROR,
1419 p_token1 => G_SQLCODE_TOKEN,
1420 p_token1_value => SQLCODE,
1421 p_token2 => G_SQLERRM_TOKEN,
1422 p_token2_value => SQLERRM);
1423 Fnd_Msg_Pub.Count_And_Get
1424 (p_count => x_msg_count,
1425 p_data => x_msg_data);
1426 END create_pool_transaction;
1427
1428 ----------------------------------------------------------------------------------
1429 -- Start of comments
1430 --
1431 -- Procedure Name : update_pool_transaction
1432 -- Description : wrapper api for update pool transaction
1433 -- Business Rules :
1434 -- Parameters :
1435 -- Version : 1.0
1436 -- End of comments
1437 ----------------------------------------------------------------------------------
1438 PROCEDURE update_pool_transaction(
1439 p_api_version IN NUMBER
1440 ,p_init_msg_list IN VARCHAR2
1441 ,x_return_status OUT NOCOPY VARCHAR2
1442 ,x_msg_count OUT NOCOPY NUMBER
1443 ,x_msg_data OUT NOCOPY VARCHAR2
1444 ,p_poxv_rec IN poxv_rec_type
1445 ,x_poxv_rec OUT NOCOPY poxv_rec_type
1446 )
1447 IS
1448 l_api_name CONSTANT VARCHAR2(30) := 'update_pool_transaction_pvt';
1449 l_api_version CONSTANT NUMBER := 1.0;
1450 i NUMBER;
1451 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1452
1453 l_poxv_rec poxv_rec_type := p_poxv_rec;
1454 -- x_poxv_rec poxv_rec_type;
1455
1456 BEGIN
1457 -- Set API savepoint
1458 SAVEPOINT update_pool_transaction_pvt;
1459
1460 -- Check for call compatibility
1461 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
1462 p_api_version,
1463 l_api_name,
1464 G_PKG_NAME ))
1465 THEN
1466 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1467 END IF;
1468
1469 -- Initialize message list if requested
1470 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
1471 Fnd_Msg_Pub.initialize;
1472 END IF;
1473
1474 -- Initialize API status to success
1475 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1476
1477
1478 /*** Begin API body ****************************************************/
1479
1480 Okl_Pox_Pvt.update_row(
1481 p_api_version => p_api_version,
1482 p_init_msg_list => p_init_msg_list,
1483 x_return_status => x_return_status,
1484 x_msg_count => x_msg_count,
1485 x_msg_data => x_msg_data,
1486 p_poxv_rec => l_poxv_rec,
1487 x_poxv_rec => x_poxv_rec);
1488
1489 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1490 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1491
1492 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1493 RAISE Okl_Api.G_EXCEPTION_ERROR;
1494 END IF;
1495
1496 /*** End API body ******************************************************/
1497
1498
1499 -- Get message count and if count is 1, get message info
1500 Fnd_Msg_Pub.Count_And_Get
1501 (p_count => x_msg_count,
1502 p_data => x_msg_data);
1503
1504 EXCEPTION
1505 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1506 ROLLBACK TO update_pool_transaction_PVT;
1507 x_return_status := Okl_Api.G_RET_STS_ERROR;
1508 Fnd_Msg_Pub.Count_And_Get
1509 (p_count => x_msg_count,
1510 p_data => x_msg_data);
1511
1512 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1513 ROLLBACK TO update_pool_transaction_pvt;
1514 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1515 Fnd_Msg_Pub.Count_And_Get
1516 (p_count => x_msg_count,
1517 p_data => x_msg_data);
1518
1519 WHEN OTHERS THEN
1520 ROLLBACK TO update_pool_transaction_pvt;
1521 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
1522 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
1523 p_msg_name => G_UNEXPECTED_ERROR,
1524 p_token1 => G_SQLCODE_TOKEN,
1525 p_token1_value => SQLCODE,
1526 p_token2 => G_SQLERRM_TOKEN,
1527 p_token2_value => SQLERRM);
1528 Fnd_Msg_Pub.Count_And_Get
1529 (p_count => x_msg_count,
1530 p_data => x_msg_data);
1531
1532 END update_pool_transaction;
1533
1534
1535 ----------------------------------------------------------------------------------
1536 -- Start of comments
1537 --
1538 -- Procedure Name : delete_pool_transaction
1539 -- Description : wrapper api for delete pool transaction
1540 -- Business Rules :
1541 -- Parameters :
1542 -- Version : 1.0
1543 -- End of comments
1544 ----------------------------------------------------------------------------------
1545 PROCEDURE delete_pool_transaction(
1546 p_api_version IN NUMBER
1547 ,p_init_msg_list IN VARCHAR2
1548 ,x_return_status OUT NOCOPY VARCHAR2
1549 ,x_msg_count OUT NOCOPY NUMBER
1550 ,x_msg_data OUT NOCOPY VARCHAR2
1551 ,p_poxv_rec IN poxv_rec_type
1552 )
1553 IS
1554 l_api_name CONSTANT VARCHAR2(30) := 'delete_pool_transaction_pvt';
1555 l_api_version CONSTANT NUMBER := 1.0;
1556 i NUMBER;
1557 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1558
1559 l_poxv_rec poxv_rec_type := p_poxv_rec;
1560 -- x_poxv_rec poxv_rec_type;
1561
1562 BEGIN
1563 -- Set API savepoint
1564 SAVEPOINT delete_pool_transaction_pvt2;
1565
1566
1567 -- Check for call compatibility
1568 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
1569 p_api_version,
1570 l_api_name,
1571 G_PKG_NAME ))
1572 THEN
1573 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1574 END IF;
1575
1576 -- Initialize message list if requested
1577 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
1578 Fnd_Msg_Pub.initialize;
1579 END IF;
1580
1581 -- Initialize API status to success
1582 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1583
1584
1585 /*** Begin API body ****************************************************/
1586
1587 Okl_Pox_Pvt.delete_row(
1588 p_api_version => p_api_version,
1589 p_init_msg_list => p_init_msg_list,
1590 x_return_status => x_return_status,
1591 x_msg_count => x_msg_count,
1592
1593 x_msg_data => x_msg_data,
1594 p_poxv_rec => l_poxv_rec);
1595
1596 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1597 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1598 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1599 RAISE Okl_Api.G_EXCEPTION_ERROR;
1600 END IF;
1601
1602 /*** End API body ******************************************************/
1603
1604 -- Get message count and if count is 1, get message info
1605 Fnd_Msg_Pub.Count_And_Get
1606 (p_count => x_msg_count,
1607 p_data => x_msg_data);
1608
1609 EXCEPTION
1610 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1611 ROLLBACK TO delete_pool_transaction_PVT2;
1612 x_return_status := Okl_Api.G_RET_STS_ERROR;
1613 Fnd_Msg_Pub.Count_And_Get
1614 (p_count => x_msg_count,
1615 p_data => x_msg_data);
1616
1617 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1618 ROLLBACK TO delete_pool_transaction_pvt2;
1619 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1620 Fnd_Msg_Pub.Count_And_Get
1621 (p_count => x_msg_count,
1622 p_data => x_msg_data);
1623
1624 WHEN OTHERS THEN
1625 ROLLBACK TO delete_pool_transaction_pvt2;
1626 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
1627 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
1628 p_msg_name => G_UNEXPECTED_ERROR,
1629 p_token1 => G_SQLCODE_TOKEN,
1630 p_token1_value => SQLCODE,
1631 p_token2 => G_SQLERRM_TOKEN,
1632 p_token2_value => SQLERRM);
1633 Fnd_Msg_Pub.Count_And_Get
1634 (p_count => x_msg_count,
1635 p_data => x_msg_data);
1636
1637 END delete_pool_transaction;
1638
1639 ----------------------------------------------------------------------------------
1640 -- Start of comments
1641 --
1642 -- Procedure Name : get_pool_stream_amout
1643 -- Description : get stream elements amount from pool contents
1644 -- Business Rules : This amount filter by the from date and to date of pool contents
1645 -- Parameters :
1646 -- Version : 1.0
1647 -- End of comments
1648 ----------------------------------------------------------------------------------
1649 FUNCTION get_pool_stream_amout(
1650 p_poc_id IN okl_pool_contents.id%TYPE
1651 ) RETURN NUMBER
1652 IS
1653 l_amount NUMBER;
1654
1655 CURSOR c (p_poc_id NUMBER )
1656 IS
1657 SELECT
1658 NVL(SUM(NVL(ele.AMOUNT,0)),0) STREAM_AMOUNT
1659 -- SUM(ele.AMOUNT) STREAM_AMOUNT --fixed cklee 06/05/2003
1660 --for streams
1661 FROM
1662 okl_streams strm
1663 ,okl_strm_elements ele
1664 ,okl_pool_contents cnt
1665 WHERE strm.id = ele.stm_id
1666 AND cnt.ID = p_poc_id
1667 -- mvasudev, 08/11/2003 , Restoring stm_id changes
1668 --AND strm.KHR_ID = cnt.KHR_ID
1669 --AND strm.KLE_ID = cnt.KLE_ID
1670 --AND strm.STY_ID = cnt.STY_ID
1671 AND strm.ID = cnt.STM_ID
1672 AND strm.say_code = 'CURR'
1673 AND strm.active_yn = 'Y'
1674 AND cnt.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
1675 AND ele.STREAM_ELEMENT_DATE
1676 BETWEEN cnt.STREAMS_FROM_DATE AND NVL(cnt.STREAMS_TO_DATE,G_FINAL_DATE)
1677 ;
1678
1679 BEGIN
1680
1681 OPEN c (p_poc_id);
1682 FETCH c INTO l_amount;
1683 CLOSE c;
1684
1685 RETURN l_amount;
1686 EXCEPTION
1687 WHEN OTHERS THEN
1688 --l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1689 Okl_Api.Set_Message(p_app_name => Okl_Api.G_APP_NAME,
1690 p_msg_name => 'OKL_UNEXPECTED_ERROR',
1691 p_token1 => 'OKL_SQLCODE',
1692 p_token1_value => SQLCODE,
1693 p_token2 => 'OKL_SQLERRM',
1694 p_token2_value => SQLERRM);
1695 RETURN NULL;
1696
1697 END get_pool_stream_amout;
1698
1699 ----------------------------------------------------------------------------------
1700 -- Start of comments
1701 --
1702 -- Procedure Name : get_tot_receivable_amt
1703 -- Description : get stream elements amount from pool contents by okl_pools.id
1704 -- Business Rules :
1705 -- Parameters :
1706 -- Version : 1.0
1707 -- End of comments
1708 ----------------------------------------------------------------------------------
1709 FUNCTION get_tot_receivable_amt(
1710 p_pol_id IN okl_pools.id%TYPE
1711
1712 ) RETURN NUMBER
1713 IS
1714 l_amount NUMBER;
1715 l_tot_amount NUMBER := 0;
1716 l_poc_id okl_pool_contents.id%TYPE;
1717 i NUMBER := 0;
1718
1719 CURSOR c_poc (p_pol_id NUMBER)
1720 IS
1721 SELECT poc.id
1722 FROM okl_pool_contents poc
1723 WHERE poc.pol_id = p_pol_id
1724 AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
1725 ;
1726
1727 BEGIN
1728
1729 OPEN c_poc (p_pol_id);
1730 LOOP
1731
1732 FETCH c_poc INTO l_poc_id;
1733 EXIT WHEN c_poc%NOTFOUND;
1734
1735 l_amount := get_pool_stream_amout(l_poc_id);
1736 l_tot_amount := l_tot_amount + l_amount;
1737 i := i+1;
1738
1739 END LOOP;
1740 CLOSE c_poc;
1741
1742 IF (i = 0) THEN
1743 l_tot_amount := NULL;
1744 END IF;
1745
1746 RETURN l_tot_amount;
1747
1748 EXCEPTION
1749 WHEN OTHERS THEN
1750 --l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1751 Okl_Api.Set_Message(p_app_name => Okl_Api.G_APP_NAME,
1752 p_msg_name => 'OKL_UNEXPECTED_ERROR',
1753 p_token1 => 'OKL_SQLCODE',
1754 p_token1_value => SQLCODE,
1755 p_token2 => 'OKL_SQLERRM',
1756 p_token2_value => SQLERRM);
1757 RETURN NULL;
1758
1759 END get_tot_receivable_amt;
1760
1761 /* ankushar - Bug 6658065
1762 Prcedure to return Value Of Streams for Pending Pool Transactions
1763 start changes
1764 */
1765 ----------------------------------------------------------------------------------
1766 -- Start of comments
1767 --
1768 -- Procedure Name : get_tot_recv_amt_for_pend
1769 -- Description : get stream elements amount from pool contents by okl_pools.id
1770 -- Business Rules :
1771 -- Parameters :
1772 -- Version : 1.0
1773 -- End of comments
1774 ----------------------------------------------------------------------------------
1775 FUNCTION get_tot_recv_amt_for_pend(
1776 p_pol_id IN okl_pools.id%TYPE
1777
1778 ) RETURN NUMBER
1779 IS
1780 l_amount NUMBER;
1781 l_tot_amount NUMBER := 0;
1782 l_poc_id okl_pool_contents.id%TYPE;
1783 i NUMBER := 0;
1784
1785 --Begin - Changes for bug#6658065 by VARANGAN on 30/11/2007
1786 CURSOR c_poc (p_pol_id NUMBER)
1787 IS
1788 SELECT poc.id
1789 FROM okl_pool_contents poc
1790 WHERE poc.pol_id = p_pol_id
1791 AND poc.status_code IN (G_POC_STS_PENDING); -- Getting only pending POC - for Bug 6691554
1792 --Getting only pending POC stream amounts
1793 CURSOR c_strm_amount ( p_poc_id NUMBER)
1794 IS
1795 SELECT NVL(SUM(NVL(ele.AMOUNT,0)),0) STREAM_AMOUNT
1796 FROM okl_streams strm
1797 ,okl_strm_elements ele
1798 ,okl_pool_contents cnt
1799 WHERE strm.id = ele.stm_id
1800 AND cnt.ID = p_poc_id
1801 AND strm.ID = cnt.STM_ID
1802 AND strm.say_code = 'CURR'
1803 AND strm.active_yn = 'Y'
1804 AND cnt.status_code IN (G_POC_STS_PENDING)
1805 AND ele.STREAM_ELEMENT_DATE
1806 BETWEEN cnt.STREAMS_FROM_DATE AND NVL(cnt.STREAMS_TO_DATE,G_FINAL_DATE);
1807
1808 l_allowed_status Varchar2(100);
1809 l_status_code okl_pools.status_Code%TYPE;
1810 --End - Changes for bug#6658065 by VARANGAN on 30/11/2007
1811 BEGIN
1812 -- Begin - Changes for Bug#6658065
1813 --(1) Check the pool status
1814 FOR pool_status_rec IN pool_status_csr(p_pol_id)
1815 LOOP
1816 l_status_code := pool_status_rec.status_code;
1817 END LOOP;
1818 --(2) If the Status is 'Active', then only 'Pending' status pool contents created for adjustment should be processed
1819 IF l_status_code = G_POL_STS_ACTIVE THEN
1820 OPEN c_poc (p_pol_id);
1821 LOOP
1822 FETCH c_poc INTO l_poc_id;
1823 EXIT WHEN c_poc%NOTFOUND;
1824 OPEN c_strm_amount (l_poc_id) ;
1825 FETCH c_strm_amount INTO l_amount;
1826 CLOSE c_strm_amount;
1827 l_tot_amount := l_tot_amount + l_amount;
1828 i := i+1;
1829 END LOOP;
1830 CLOSE c_poc;
1831 END IF;
1832
1833 IF (i = 0) THEN
1834 l_tot_amount := NULL;
1835 END IF;
1836
1837 RETURN l_tot_amount;
1838
1839 EXCEPTION
1840 WHEN OTHERS THEN
1841 --l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1842 Okl_Api.Set_Message(p_app_name => Okl_Api.G_APP_NAME,
1843 p_msg_name => 'OKL_UNEXPECTED_ERROR',
1844 p_token1 => 'OKL_SQLCODE',
1845 p_token1_value => SQLCODE,
1846 p_token2 => 'OKL_SQLERRM',
1847 p_token2_value => SQLERRM);
1848 RETURN NULL;
1849
1850 END get_tot_recv_amt_for_pend;
1851
1852 ----------------------------------------------------------------------------------
1853 -- Start of comments
1854 --
1855
1856 -- Procedure Name : get_tot_recei_amt_pend
1857 -- Description : wrapper api for get_tot_receivable_amt_pend
1858 -- Business Rules :
1859 -- Parameters :
1860 -- Version : 1.0
1861 -- End of comments
1862 ----------------------------------------------------------------------------------
1863 PROCEDURE get_tot_recei_amt_pend(
1864 p_api_version IN NUMBER
1865 ,p_init_msg_list IN VARCHAR2
1866 ,x_return_status OUT NOCOPY VARCHAR2
1867 ,x_msg_count OUT NOCOPY NUMBER
1868 ,x_msg_data OUT NOCOPY VARCHAR2
1869 ,x_value OUT NOCOPY NUMBER
1870 ,p_pol_id IN okl_pools.id%TYPE
1871
1872 )
1873 IS
1874 l_api_name CONSTANT VARCHAR2(30) := 'get_tot_receivable_amt_pvt2';
1875 l_api_version CONSTANT NUMBER := 1.0;
1876
1877 i NUMBER;
1878 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1879 l_amount NUMBER;
1880
1881 BEGIN
1882 -- Set API savepoint
1883 SAVEPOINT get_tot_recv_amt_pend_pvt2;
1884
1885 -- Check for call compatibility
1886 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
1887 p_api_version,
1888 l_api_name,
1889 G_PKG_NAME ))
1890 THEN
1891 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1892 END IF;
1893
1894 -- Initialize message list if requested
1895 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
1896 Fnd_Msg_Pub.initialize;
1897 END IF;
1898
1899 -- Initialize API status to success
1900 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1901
1902
1903 /*** Begin API body ****************************************************/
1904
1905 x_value := get_tot_recv_amt_for_pend(p_pol_id =>p_pol_id);
1906
1907 /*** End API body ******************************************************/
1908
1909 -- Get message count and if count is 1, get message info
1910 Fnd_Msg_Pub.Count_And_Get
1911 (p_count => x_msg_count,
1912
1913 p_data => x_msg_data);
1914
1915 EXCEPTION
1916 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1917 ROLLBACK TO get_tot_recv_amt_pend_pvt2;
1918 x_return_status := Okl_Api.G_RET_STS_ERROR;
1919 Fnd_Msg_Pub.Count_And_Get
1920 (p_count => x_msg_count,
1921 p_data => x_msg_data);
1922
1923 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1924 ROLLBACK TO get_tot_recv_amt_pend_pvt2;
1925 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1926 Fnd_Msg_Pub.Count_And_Get
1927 (p_count => x_msg_count,
1928 p_data => x_msg_data);
1929
1930 WHEN OTHERS THEN
1931 ROLLBACK TO get_tot_recv_amt_pend_pvt2;
1932 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
1933 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
1934 p_msg_name => G_UNEXPECTED_ERROR,
1935 p_token1 => G_SQLCODE_TOKEN,
1936 p_token1_value => SQLCODE,
1937 p_token2 => G_SQLERRM_TOKEN,
1938 p_token2_value => SQLERRM);
1939 Fnd_Msg_Pub.Count_And_Get
1940 (p_count => x_msg_count,
1941 p_data => x_msg_data);
1942
1943 END get_tot_recei_amt_pend;
1944
1945 /* ankushar - Bug 6658065
1946 end changes
1947 */
1948 ----------------------------------------------------------------------------------
1949 -- Start of comments
1950 --
1951
1952 -- Procedure Name : get_tot_recei_amt
1953 -- Description : wrapper api for get_tot_receivable_amt
1954 -- Business Rules :
1955 -- Parameters :
1956 -- Version : 1.0
1957 -- End of comments
1958 ----------------------------------------------------------------------------------
1959 PROCEDURE get_tot_recei_amt(
1960 p_api_version IN NUMBER
1961 ,p_init_msg_list IN VARCHAR2
1962 ,x_return_status OUT NOCOPY VARCHAR2
1963 ,x_msg_count OUT NOCOPY NUMBER
1964 ,x_msg_data OUT NOCOPY VARCHAR2
1965 ,x_value OUT NOCOPY NUMBER
1966 ,p_pol_id IN okl_pools.id%TYPE
1967
1968 )
1969 IS
1970 l_api_name CONSTANT VARCHAR2(30) := 'get_tot_receivable_amt_pvt2';
1971 l_api_version CONSTANT NUMBER := 1.0;
1972
1973 i NUMBER;
1974 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1975 l_amount NUMBER;
1976
1977 BEGIN
1978 -- Set API savepoint
1979 SAVEPOINT get_tot_receivable_amt_pvt2;
1980
1981 -- Check for call compatibility
1982 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
1983 p_api_version,
1984 l_api_name,
1985 G_PKG_NAME ))
1986 THEN
1987 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1988 END IF;
1989
1990 -- Initialize message list if requested
1991 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
1992 Fnd_Msg_Pub.initialize;
1993 END IF;
1994
1995 -- Initialize API status to success
1996 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1997
1998
1999 /*** Begin API body ****************************************************/
2000
2001 x_value := get_tot_receivable_amt(p_pol_id =>p_pol_id);
2002
2003 /*** End API body ******************************************************/
2004
2005 -- Get message count and if count is 1, get message info
2006 Fnd_Msg_Pub.Count_And_Get
2007 (p_count => x_msg_count,
2008
2009 p_data => x_msg_data);
2010
2011 EXCEPTION
2012 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2013 ROLLBACK TO get_tot_receivable_amt_pvt2;
2014 x_return_status := Okl_Api.G_RET_STS_ERROR;
2015 Fnd_Msg_Pub.Count_And_Get
2016 (p_count => x_msg_count,
2017 p_data => x_msg_data);
2018
2019 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2020 ROLLBACK TO get_tot_receivable_amt_pvt2;
2021 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
2022 Fnd_Msg_Pub.Count_And_Get
2023 (p_count => x_msg_count,
2024 p_data => x_msg_data);
2025
2026 WHEN OTHERS THEN
2027 ROLLBACK TO get_tot_receivable_amt_pvt2;
2028 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
2029 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
2030 p_msg_name => G_UNEXPECTED_ERROR,
2031 p_token1 => G_SQLCODE_TOKEN,
2032 p_token1_value => SQLCODE,
2033 p_token2 => G_SQLERRM_TOKEN,
2034 p_token2_value => SQLERRM);
2035 Fnd_Msg_Pub.Count_And_Get
2036 (p_count => x_msg_count,
2037 p_data => x_msg_data);
2038
2039 END get_tot_recei_amt;
2040
2041 ----------------------------------------------------------------------------------
2042 -- Start of comments
2043 --
2044 -- Procedure Name : get_tot_receivable_amt
2045 -- Description : wrapper api for get_tot_receivable_amt by investor agreement ID
2046 -- Business Rules :
2047 -- Parameters :
2048 -- Version : 1.0
2049 -- End of comments
2050 ----------------------------------------------------------------------------------
2051 PROCEDURE get_tot_receivable_amt(
2052 p_api_version IN NUMBER
2053 ,p_init_msg_list IN VARCHAR2
2054 ,x_return_status OUT NOCOPY VARCHAR2
2055 ,x_msg_count OUT NOCOPY NUMBER
2056 ,x_msg_data OUT NOCOPY VARCHAR2
2057 ,x_value OUT NOCOPY NUMBER
2058 ,p_khr_id IN okc_k_headers_b.id%TYPE
2059 )
2060 IS
2061 l_api_name CONSTANT VARCHAR2(30) := 'get_tot_receivable_amt_pvt2';
2062 l_api_version CONSTANT NUMBER := 1.0;
2063 i NUMBER;
2064 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2065 l_pol_id okl_pools.id%TYPE;
2066
2067 CURSOR c_khr(p_khr_id okc_k_headers_b.id%TYPE) IS
2068 SELECT ph.id
2069 FROM okl_pools ph
2070 WHERE ph.khr_id = p_khr_id
2071 ;
2072
2073 BEGIN
2074 -- Set API savepoint
2075 SAVEPOINT get_tot_receivable_amt_pvt2;
2076
2077 -- Check for call compatibility
2078 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
2079 p_api_version,
2080 l_api_name,
2081 G_PKG_NAME ))
2082 THEN
2083 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2084 END IF;
2085
2086 -- Initialize message list if requested
2087 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
2088 Fnd_Msg_Pub.initialize;
2089
2090 END IF;
2091
2092
2093
2094 -- Initialize API status to success
2095 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
2096
2097
2098 /*** Begin API body ****************************************************/
2099
2100 OPEN c_khr (p_khr_id);
2101 FETCH c_khr INTO l_pol_id;
2102 CLOSE c_khr;
2103
2104 x_value := get_tot_receivable_amt(p_pol_id =>l_pol_id);
2105
2106 /*** End API body ******************************************************/
2107
2108 -- Get message count and if count is 1, get message info
2109 Fnd_Msg_Pub.Count_And_Get
2110 (p_count => x_msg_count,
2111 p_data => x_msg_data);
2112
2113 EXCEPTION
2114 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2115 ROLLBACK TO get_tot_receivable_amt_pvt2;
2116 x_return_status := Okl_Api.G_RET_STS_ERROR;
2117 Fnd_Msg_Pub.Count_And_Get
2118
2119
2120 (p_count => x_msg_count,
2121 p_data => x_msg_data);
2122
2123 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2124 ROLLBACK TO get_tot_receivable_amt_pvt2;
2125 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
2126 Fnd_Msg_Pub.Count_And_Get
2127 (p_count => x_msg_count,
2128 p_data => x_msg_data);
2129
2130 WHEN OTHERS THEN
2131 ROLLBACK TO get_tot_receivable_amt_pvt2;
2132 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
2133 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
2134 p_msg_name => G_UNEXPECTED_ERROR,
2135 p_token1 => G_SQLCODE_TOKEN,
2136 p_token1_value => SQLCODE,
2137 p_token2 => G_SQLERRM_TOKEN,
2138 p_token2_value => SQLERRM);
2139 Fnd_Msg_Pub.Count_And_Get
2140 (p_count => x_msg_count,
2141 p_data => x_msg_data);
2142
2143 END get_tot_receivable_amt;
2144 ----------------------------------------------------------------------------------
2145 -- Start of comments
2146 --
2147 -- Procedure Name : get_tot_principal_amt
2148 -- Description : get asset principal amount from pool contents by okl_pools.id
2149 -- Business Rules :
2150 -- Parameters :
2151
2152 -- Version : 1.0
2153 -- End of comments
2154 ----------------------------------------------------------------------------------
2155 FUNCTION get_tot_principal_amt(
2156 p_pol_id IN okl_pools.id%TYPE
2157 ) RETURN NUMBER
2158 IS
2159 l_amount NUMBER := 0;
2160 l_tot_amount NUMBER := 0;
2161
2162 l_khr_id okl_pool_contents.khr_id%TYPE;
2163 l_kle_id okl_pool_contents.kle_id%TYPE := NULL;
2164 l_start_date DATE;
2165 l_end_date DATE;
2166
2167 l_api_version NUMBER := 1;
2168 l_init_msg_list VARCHAR2(100) := Okc_Api.G_FALSE;
2169 x_return_status VARCHAR2(100);
2170 x_msg_count NUMBER;
2171 x_msg_data VARCHAR2(1999);
2172 x_value NUMBER := 0;
2173 l_ctxt_val_tbl Okl_Execute_Formula_Pvt.ctxt_val_tbl_type;
2174 l_deal_type okl_k_headers.deal_type%TYPE;
2175 l_contract_number okc_k_headers_b.contract_number%TYPE;
2176
2177 l_formula_name VARCHAR2(100);
2178 /*
2179 CURSOR c_poc (p_pol_id NUMBER)
2180 IS
2181 SELECT DISTINCT poc.khr_id,
2182 poc.kle_id,
2183 DECODE(khr.deal_type, G_DEAL_TYPE_LEASEDF, G_NET_INVESTMENT_DF
2184
2185 , G_DEAL_TYPE_LEASEST, G_NET_INVESTMENT_DF
2186 , G_DEAL_TYPE_LOAN, G_NET_INVESTMENT_LOAN
2187 , G_DEAL_TYPE_LEASEOP, G_NET_INVESTMENT_OP
2188 , G_NET_INVESTMENT_OTHERS)
2189 FROM okl_pool_contents poc,
2190 okc_k_headers_b CHR,
2191 okl_k_headers khr
2192 WHERE poc.khr_id = CHR.id
2193 AND khr.id = CHR.id
2194 AND poc.pol_id = p_pol_id
2195 ;
2196 */
2197 CURSOR c_poc (p_pol_id NUMBER)
2198 IS
2199 SELECT khr.id,
2200 DECODE(khr.deal_type, G_DEAL_TYPE_LEASEDF, G_NET_INVESTMENT_DF
2201 , G_DEAL_TYPE_LEASEST, G_NET_INVESTMENT_DF
2202 , G_DEAL_TYPE_LOAN, G_NET_INVESTMENT_LOAN
2203 , G_DEAL_TYPE_LEASEOP, G_NET_INVESTMENT_OP
2204 , G_NET_INVESTMENT_OTHERS)
2205 FROM okl_k_headers khr
2206 WHERE EXISTS (SELECT '1'
2207 FROM okl_pool_contents poc
2208 WHERE khr.id = poc.khr_id
2209 AND poc.pol_id = p_pol_id
2210 AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE))
2211 ;
2212
2213
2214 CURSOR c_chr (p_chr_id NUMBER)
2215 IS
2216 SELECT CHR.contract_number,
2217 khr.deal_type
2218 FROM okc_k_headers_b CHR,
2219 okl_k_headers khr
2220 WHERE CHR.id = khr.id
2221 AND CHR.id = p_chr_id
2222 ;
2223
2224 BEGIN
2225
2226 OPEN c_poc (p_pol_id);
2227 LOOP
2228
2229 FETCH c_poc INTO l_khr_id,
2230 -- l_kle_id,
2231 l_formula_name;
2232
2233 EXIT WHEN c_poc%NOTFOUND;
2234
2235 IF (l_formula_name = G_NET_INVESTMENT_OTHERS) THEN
2236 OPEN c_chr (l_khr_id);
2237 FETCH c_chr INTO l_contract_number,
2238 l_deal_type;
2239 CLOSE c_chr;
2240
2241 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
2242 p_msg_name => 'OKL_INVALID_DEAL_TYPE',
2243 p_token1 => 'CONTRACT_NUM',
2244 p_token1_value => l_contract_number,
2245 p_token2 => 'DEAL_TYPE',
2246 p_token2_value => l_deal_type);
2247
2248
2249
2250 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2251 END IF;
2252
2253 -- DBMS_OUTPUT.PUT_LINE('l_khr_id = ' || l_khr_id);
2254 -- DBMS_OUTPUT.PUT_LINE('l_kle_id = ' || l_kle_id);
2255
2256 Okl_Execute_Formula_Pub.EXECUTE(
2257 p_api_version => l_api_version,
2258 p_init_msg_list => l_init_msg_list,
2259 x_return_status => x_return_status,
2260 x_msg_count => x_msg_count,
2261 x_msg_data => x_msg_data,
2262 p_formula_name => l_formula_name,
2263 p_contract_id => l_khr_id,
2264 p_line_id => l_kle_id,
2265 x_value => x_value);
2266
2267 -- DBMS_OUTPUT.PUT_LINE('x_value = ' || x_value);
2268
2269 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2270 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2271 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2272 RAISE Okl_Api.G_EXCEPTION_ERROR;
2273 END IF;
2274
2275 l_tot_amount := l_tot_amount + NVL(x_value,0);
2276
2277
2278 END LOOP;
2279 CLOSE c_poc;
2280
2281 RETURN l_tot_amount;
2282 EXCEPTION
2283 WHEN OTHERS THEN
2284 --l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2285 Okl_Api.Set_Message(p_app_name => Okl_Api.G_APP_NAME,
2286 p_msg_name => 'OKL_UNEXPECTED_ERROR',
2287 p_token1 => 'OKL_SQLCODE',
2288 p_token1_value => SQLCODE,
2289 p_token2 => 'OKL_SQLERRM',
2290 p_token2_value => SQLERRM);
2291 RETURN NULL;
2292
2293 END get_tot_principal_amt;
2294
2295 ----------------------------------------------------------------------------------
2296 -- Start of comments
2297 --
2298 -- Procedure Name : recal_tot_princ_amt
2299 -- Description : wrapper api for get_tot_principal_amt
2300
2301 -- Business Rules :
2302 -- Parameters :
2303 -- Version : 1.0
2304 -- End of comments
2305 ----------------------------------------------------------------------------------
2306 PROCEDURE recal_tot_princ_amt(
2307 p_api_version IN NUMBER
2308 ,p_init_msg_list IN VARCHAR2
2309 ,x_return_status OUT NOCOPY VARCHAR2
2310 ,x_msg_count OUT NOCOPY NUMBER
2311 ,x_msg_data OUT NOCOPY VARCHAR2
2312 ,x_value OUT NOCOPY NUMBER
2313 ,p_pol_id IN okl_pools.id%TYPE
2314 )
2315 IS
2316 l_api_name CONSTANT VARCHAR2(30) := 'recal_tot_princ_amt_pvt';
2317 l_api_version CONSTANT NUMBER := 1.0;
2318 i NUMBER;
2319 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2320 l_amount NUMBER;
2321
2322 lp_polv_rec polv_rec_type;
2323 lx_polv_rec polv_rec_type;
2324
2325 BEGIN
2326 -- Set API savepoint
2327 SAVEPOINT recal_tot_princ_amt_pvt;
2328
2329 -- Check for call compatibility
2330 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
2331 p_api_version,
2332 l_api_name,
2333 G_PKG_NAME ))
2334 THEN
2335 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2336 END IF;
2337
2338 -- Initialize message list if requested
2339 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
2340 Fnd_Msg_Pub.initialize;
2341 END IF;
2342
2343 -- Initialize API status to success
2344 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
2345
2346
2347 /*** Begin API body ****************************************************/
2348
2349 x_value := get_tot_principal_amt(p_pol_id =>p_pol_id);
2350
2351 IF x_value IS NULL THEN
2352 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
2353 p_msg_name => 'OKL_RECAL_PRINC_AMT_ERR');
2354 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2355 END IF;
2356
2357 lp_polv_rec.ID := p_pol_id;
2358 lp_polv_rec.TOTAL_PRINCIPAL_AMOUNT := x_value;
2359 lp_polv_rec.DATE_TOTAL_PRINCIPAL_CALC := SYSDATE;
2360
2361 Okl_Pool_Pvt.update_pool(
2362 p_api_version => p_api_version,
2363 p_init_msg_list => p_init_msg_list,
2364 x_return_status => x_return_status,
2365 x_msg_count => x_msg_count,
2366 x_msg_data => x_msg_data,
2367 p_polv_rec => lp_polv_rec,
2368 x_polv_rec => lx_polv_rec);
2369
2370
2371 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2372 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2373 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2374 RAISE Okl_Api.G_EXCEPTION_ERROR;
2375 END IF;
2376
2377 /*** End API body ******************************************************/
2378
2379 -- Get message count and if count is 1, get message info
2380 Fnd_Msg_Pub.Count_And_Get
2381 (p_count => x_msg_count,
2382 p_data => x_msg_data);
2383
2384 EXCEPTION
2385 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2386 ROLLBACK TO recal_tot_princ_amt_pvt;
2387 x_return_status := Okl_Api.G_RET_STS_ERROR;
2388 Fnd_Msg_Pub.Count_And_Get
2389 (p_count => x_msg_count,
2390 p_data => x_msg_data);
2391
2392 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2393 ROLLBACK TO recal_tot_princ_amt_pvt;
2394
2395 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
2396 Fnd_Msg_Pub.Count_And_Get
2397
2398 (p_count => x_msg_count,
2399 p_data => x_msg_data);
2400
2401 WHEN OTHERS THEN
2402 ROLLBACK TO recal_tot_princ_amt_pvt;
2403 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
2404 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
2405 p_msg_name => G_UNEXPECTED_ERROR,
2406 p_token1 => G_SQLCODE_TOKEN,
2407 p_token1_value => SQLCODE,
2408 p_token2 => G_SQLERRM_TOKEN,
2409 p_token2_value => SQLERRM);
2410 Fnd_Msg_Pub.Count_And_Get
2411 (p_count => x_msg_count,
2412 p_data => x_msg_data);
2413 END recal_tot_princ_amt;
2414
2415 ----------------------------------------------------------------------------------
2416 -- Start of comments
2417 --
2418 -- Procedure Name : recal_tot_principal_amt
2419 -- Description : wrapper api for get_tot_principal_amt by investor agreement ID
2420 -- Business Rules :
2421 -- Parameters :
2422 -- Version : 1.0
2423 -- End of comments
2424 ----------------------------------------------------------------------------------
2425 PROCEDURE recal_tot_principal_amt(
2426 p_api_version IN NUMBER
2427 ,p_init_msg_list IN VARCHAR2
2428 ,x_return_status OUT NOCOPY VARCHAR2
2429 ,x_msg_count OUT NOCOPY NUMBER
2430 ,x_msg_data OUT NOCOPY VARCHAR2
2431 ,x_value OUT NOCOPY NUMBER
2432 ,p_khr_id IN okc_k_headers_b.id%TYPE
2433 )
2434 IS
2435 l_api_name CONSTANT VARCHAR2(30) := 'recal_tot_principal_amt_pvt';
2436 l_api_version CONSTANT NUMBER := 1.0;
2437 i NUMBER;
2438 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2439 l_pol_id okl_pools.id%TYPE;
2440
2441 lp_polv_rec polv_rec_type;
2442 lx_polv_rec polv_rec_type;
2443
2444 CURSOR c_khr(p_khr_id okc_k_headers_b.id%TYPE) IS
2445 SELECT ph.id
2446 FROM okl_pools ph
2447 WHERE ph.khr_id = p_khr_id
2448 ;
2449
2450 BEGIN
2451 -- Set API savepoint
2452 SAVEPOINT recal_tot_principal_amt_pvt;
2453
2454 -- Check for call compatibility
2455 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
2456 p_api_version,
2457 l_api_name,
2458 G_PKG_NAME ))
2459 THEN
2460 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2461 END IF;
2462
2463 -- Initialize message list if requested
2464 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
2465 Fnd_Msg_Pub.initialize;
2466 END IF;
2467
2468 -- Initialize API status to success
2469 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
2470
2471
2472 /*** Begin API body ****************************************************/
2473
2474 OPEN c_khr (p_khr_id);
2475 FETCH c_khr INTO l_pol_id;
2476 CLOSE c_khr;
2477
2478 x_value := get_tot_principal_amt(p_pol_id =>l_pol_id);
2479
2480 IF x_value IS NULL THEN
2481 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
2482 p_msg_name => 'OKL_RECAL_PRINC_AMT_ERR');
2483 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2484 END IF;
2485
2486 lp_polv_rec.ID := l_pol_id;
2487 lp_polv_rec.TOTAL_PRINCIPAL_AMOUNT := x_value;
2488 lp_polv_rec.DATE_TOTAL_PRINCIPAL_CALC := SYSDATE;
2489
2490 Okl_Pool_Pvt.update_pool(
2491 p_api_version => p_api_version,
2492 p_init_msg_list => p_init_msg_list,
2493 x_return_status => x_return_status,
2494 x_msg_count => x_msg_count,
2495 x_msg_data => x_msg_data,
2496 p_polv_rec => lp_polv_rec,
2497 x_polv_rec => lx_polv_rec);
2498
2499 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2500 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2501 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2502 RAISE Okl_Api.G_EXCEPTION_ERROR;
2503 END IF;
2504
2505 /*** End API body ******************************************************/
2506
2507 -- Get message count and if count is 1, get message info
2508 Fnd_Msg_Pub.Count_And_Get
2509 (p_count => x_msg_count,
2510 p_data => x_msg_data);
2511
2512 EXCEPTION
2513 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2514
2515 ROLLBACK TO recal_tot_principal_amt_pvt;
2516 x_return_status := Okl_Api.G_RET_STS_ERROR;
2517 Fnd_Msg_Pub.Count_And_Get
2518 (p_count => x_msg_count,
2519 p_data => x_msg_data);
2520
2521 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2522 ROLLBACK TO recal_tot_principal_amt_pvt;
2523 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
2524 Fnd_Msg_Pub.Count_And_Get
2525 (p_count => x_msg_count,
2526 p_data => x_msg_data);
2527
2528 WHEN OTHERS THEN
2529 ROLLBACK TO recal_tot_principal_amt_pvt;
2530 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
2531 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
2532 p_msg_name => G_UNEXPECTED_ERROR,
2533 p_token1 => G_SQLCODE_TOKEN,
2534 p_token1_value => SQLCODE,
2535 p_token2 => G_SQLERRM_TOKEN,
2536 p_token2_value => SQLERRM);
2537 Fnd_Msg_Pub.Count_And_Get
2538 (p_count => x_msg_count,
2539 p_data => x_msg_data);
2540
2541 END recal_tot_principal_amt;
2542
2543 ----------------------------------------------------------------------------------
2544 -- Start of comments
2545 --
2546 -- Procedure Name : add_pool_contents
2547 -- Description : creates pool contents based on passed in search criteria
2548 -- Business Rules :
2549 -- Parameters :
2550 -- Version : 1.0
2551 -- End of comments
2552 ----------------------------------------------------------------------------------
2553 -- Create by Search Criteria: Query Streams from contracts + Create
2554 /*
2555 PROCEDURE add_pool_contents(
2556 p_api_version IN NUMBER
2557 ,p_init_msg_list IN VARCHAR2
2558 ,x_return_status OUT NOCOPY VARCHAR2
2559 ,x_msg_count OUT NOCOPY NUMBER
2560 ,x_msg_data OUT NOCOPY VARCHAR2
2561 ,x_row_count OUT NOCOPY NUMBER
2562 ,p_currency_code IN VARCHAR2
2563 ,p_pol_id IN NUMBER
2564 ,p_multi_org IN VARCHAR2
2565 ,p_cust_object1_id1 IN NUMBER
2566 ,p_sic_code IN VARCHAR2
2567 ,p_khr_id IN NUMBER
2568 ,p_pre_tax_yield_from IN NUMBER
2569 ,p_pre_tax_yield_to IN NUMBER
2570 ,p_book_classification IN VARCHAR2
2571 ,p_tax_owner IN VARCHAR2
2572 ,p_pdt_id IN NUMBER
2573 ,p_start_date_from IN DATE
2574 ,p_start_date_to IN DATE
2575 ,p_end_date_from IN DATE
2576 ,p_end_date_to IN DATE
2577 ,p_asset_id IN NUMBER
2578 ,p_item_id1 IN NUMBER
2579 ,p_model_number IN VARCHAR2
2580 ,p_manufacturer_name IN VARCHAR2
2581 ,p_vendor_id1 IN NUMBER
2582 ,p_oec_from IN NUMBER
2583 ,p_oec_to IN NUMBER
2584 ,p_residual_percentage IN NUMBER
2585 ,p_sty_id1 IN NUMBER
2586 ,p_sty_id2 IN NUMBER
2587 -- start added by cklee 08/06/03
2588 ,p_stream_type_subclass IN VARCHAR2
2589 -- end added by cklee 08/06/03
2590 ,p_stream_element_from_date IN DATE
2591 ,p_stream_element_to_date IN DATE
2592 ,p_stream_element_payment_freq IN VARCHAR2)
2593 IS
2594 l_api_name CONSTANT VARCHAR2(30) := 'add_pool_contents_pvt';
2595 l_api_version CONSTANT NUMBER := 1.0;
2596 i NUMBER;
2597 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2598
2599 l_pocv_rec pocv_rec_type;
2600 x_pocv_rec pocv_rec_type;
2601
2602 -------------------------------------------
2603 -- search w/o payment frequency
2604 -------------------------------------------
2605
2606 CURSOR c_pool IS
2607 SELECT
2608 pol.dnz_chr_id khr_id
2609 ,pol.kle_id
2610 ,pol.sty_id
2611 ,pol.stream_type_code sty_code
2612 ,MIN(pol.stream_element_due_date) streams_from_date
2613 ,DECODE(pol.stream_type_subclass, 'RESIDUAL', NULL, pol.end_date) streams_to_date
2614 -- mvasudev, stm_id changes
2615 ,pol.stm_id
2616 FROM okl_pool_srch_v pol
2617 WHERE
2618 -- pre-req
2619 pol.currency_code = p_currency_code
2620 AND pol.sts_code = 'BOOKED'
2621 AND pol.assignable_yn = 'Y'
2622 AND pol.stream_element_date_billed IS NULL
2623 AND NVL(pol.cust_object1_id1,G_DEFAULT_NUM) = NVL(p_cust_object1_id1, NVL(pol.cust_object1_id1,G_DEFAULT_NUM))
2624 AND NVL(pol.sic_code,G_DEFAULT_CHAR) = NVL(p_sic_code, NVL(pol.sic_code,G_DEFAULT_CHAR))
2625 AND NVL(pol.dnz_chr_id,G_DEFAULT_NUM) = NVL(p_khr_id, NVL(pol.dnz_chr_id,G_DEFAULT_NUM))
2626 AND NVL(pol.pre_tax_yield,G_DEFAULT_NUM) BETWEEN NVL(p_pre_tax_yield_from, NVL(pol.pre_tax_yield,G_DEFAULT_NUM))
2627 AND NVL(p_pre_tax_yield_to, NVL(pol.pre_tax_yield,G_DEFAULT_NUM))
2628 --and pol.contract_number
2629 AND NVL(pol.book_classification,G_DEFAULT_CHAR) = NVL(p_book_classification, NVL(pol.book_classification,G_DEFAULT_CHAR))
2630 AND NVL(pol.pdt_id,G_DEFAULT_NUM) = NVL(p_pdt_id, NVL(pol.pdt_id,G_DEFAULT_NUM))
2631 AND NVL(pol.start_date, G_DEFAULT_DATE)
2632 BETWEEN NVL(p_start_date_from, NVL(pol.start_date, G_DEFAULT_DATE))
2633 AND NVL(p_start_date_to, NVL(pol.start_date, G_DEFAULT_DATE))
2634 AND NVL(pol.end_date,G_DEFAULT_DATE)
2635 BETWEEN NVL(p_end_date_from, NVL(pol.end_date,G_DEFAULT_DATE))
2636 AND NVL(p_end_date_to, NVL(pol.end_date,G_DEFAULT_DATE))
2637 AND NVL(pol.tax_owner,G_DEFAULT_CHAR) = NVL(p_tax_owner, NVL(pol.tax_owner,G_DEFAULT_CHAR))
2638
2639 AND NVL(pol.stream_element_due_date, G_DEFAULT_DATE)
2640 BETWEEN NVL(p_stream_element_from_date,
2641 NVL(pol.stream_element_due_date, G_DEFAULT_DATE))
2642 AND NVL(p_stream_element_to_date,NVL(pol.stream_element_due_date, G_DEFAULT_DATE))
2643 -- pre-req condition
2644 -- start for 11.5.10 by cklee 08/06/03 stream_type_subclass ER
2645 AND pol.stream_type_subclass IN ('RENT', 'RESIDUAL')
2646 AND NVL(pol.stream_type_subclass,G_DEFAULT_CHAR) = NVL(p_stream_type_subclass, NVL(pol.stream_type_subclass,G_DEFAULT_CHAR))
2647 -- end for 11.5.10 by cklee 08/06/03 stream_type_subclass ER
2648
2649 AND pol.stream_say_code = 'CURR'
2650 AND pol.stream_active_yn = 'Y'
2651 AND pol.stream_element_due_date > SYSDATE
2652 -- multi-org
2653 -- non existing check
2654 -- start for 11.5.10 by cklee 08/06/03 stream_type_subclass ER
2655 AND NOT EXISTS -- okl_pool_contents
2656 (SELECT '1'
2657 FROM okl_pool_contents pol_cnts,
2658 okl_strm_type_b styb
2659 WHERE pol_cnts.pol_id = pol_id
2660 -- mvasudev, stm_id changes
2661 -- AND pol_cnts.sty_id = styb.id
2662 -- AND pol.dnz_chr_id = pol_cnts.khr_id
2663 AND pol.stm_id = pol_cnts.stm_id
2664 AND styb.stream_type_subclass = pol.stream_type_subclass
2665 AND pol_cnts.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
2666 )
2667 -- end for 11.5.10 by cklee 08/06/03 stream_type_subclass ER
2668 AND NOT EXISTS -- variable interest rate
2669 (SELECT '1'
2670
2671 FROM okc_rule_groups_b rgp
2672 ,okc_rules_b rg
2673 WHERE rgp.id = rg.rgp_id
2674 AND rgp.rgd_code = 'LAIIND'
2675 AND rg.rule_information_category= 'LAINTP'
2676 AND rg.rule_information1 = 'Y'
2677 AND rgp.dnz_chr_id = pol.dnz_chr_id)
2678 AND NOT EXISTS -- revision contract: rebook, split contract, reverse
2679 (SELECT '1'
2680 FROM okl_trx_contracts trxc
2681 WHERE trxc.tcn_type IN ('TRBK','SPLC','RVS')
2682 AND trxc.tsu_code NOT IN ('PROCESSED', 'ERROR','CANCELED') -- condition changes 01/13/2003 cklee
2683 AND trxc.khr_id = pol.dnz_chr_id)
2684 AND NOT EXISTS -- split assets, split assets components
2685 (SELECT '1'
2686 FROM okl_txd_assets_v tdas,
2687 okl_txl_assets_b tal,
2688 okc_k_lines_b cle
2689 WHERE cle.id = tal.kle_id
2690 AND tal.id = tdas.tal_id
2691 AND tal.tal_type = 'ALI'
2692 -- link from okl_pool_srch_v pol
2693 AND cle.cle_id = pol.kle_id -- top line id
2694 AND tal.dnz_khr_id = pol.dnz_chr_id
2695 -- link from okl_pool_srch_v pol
2696 AND EXISTS (SELECT '1'
2697 FROM okl_trx_assets tas
2698 WHERE tas.id = tal.tas_id
2699 AND tas.tas_type = 'ALI'
2700 AND tas.tsu_code NOT IN ('PROCESSED','CANCELED')))--cklee 02/24/03
2701 AND NOT EXISTS -- contract is under deliquent status
2702 (SELECT '1'
2703 FROM iex_case_objects ico,
2704 iex_delinquencies_all del
2705 WHERE ico. cas_id = del.case_id
2706 AND del.status ='DELINQUENT'
2707 AND ico.object_id = pol.dnz_chr_id)
2708 AND NOT EXISTS -- contract line has been terminated
2709 (SELECT '1'
2710 FROM okc_k_lines_b cle,
2711 okc_statuses_b sts
2712 WHERE sts.code = cle.sts_code
2713 AND sts.ste_code IN ('HOLD','EXPIRED','TERMINATED','CANCELLED')
2714 AND cle.id = pol.kle_id)
2715 GROUP BY
2716 pol.dnz_chr_id
2717 ,pol.kle_id
2718 ,pol.sty_id
2719 ,pol.stream_type_code
2720 ,pol.stream_type_subclass
2721 ,pol.end_date
2722 -- mvasudev, stm_id changes
2723 ,pol.stm_id
2724 ;
2725
2726 -------------------------------------------
2727 -- search with payment frequency
2728 -------------------------------------------
2729
2730 CURSOR c_pool_payfreq IS
2731 SELECT
2732 pol.dnz_chr_id khr_id
2733 ,pol.kle_id
2734 ,pol.sty_id
2735 ,pol.stream_type_code sty_code
2736 ,MIN(pol.stream_element_due_date) streams_from_date
2737 ,DECODE(pol.stream_type_subclass, 'RESIDUAL', NULL, pol.end_date) streams_to_date
2738 -- mvasudev, stm_id changes
2739 ,pol.stm_id
2740 FROM okl_pool_srch_payfreq_v pol
2741 WHERE
2742 -- pre-req
2743 pol.currency_code = p_currency_code
2744 AND pol.sts_code = 'BOOKED'
2745 AND pol.assignable_yn = 'Y'
2746 AND pol.stream_element_date_billed IS NULL
2747 --
2748 AND NVL(pol.cust_object1_id1,G_DEFAULT_NUM) = NVL(p_cust_object1_id1, NVL(pol.cust_object1_id1,G_DEFAULT_NUM))
2749 --and pol.lessee
2750 AND NVL(pol.sic_code,G_DEFAULT_CHAR) = NVL(p_sic_code, NVL(pol.sic_code,G_DEFAULT_CHAR))
2751 AND NVL(pol.dnz_chr_id,G_DEFAULT_NUM) = NVL(p_khr_id, NVL(pol.dnz_chr_id,G_DEFAULT_NUM))
2752 AND NVL(pol.pre_tax_yield,G_DEFAULT_NUM) BETWEEN NVL(p_pre_tax_yield_from, NVL(pol.pre_tax_yield,G_DEFAULT_NUM))
2753 AND NVL(p_pre_tax_yield_to, NVL(pol.pre_tax_yield,G_DEFAULT_NUM))
2754 --and pol.contract_number
2755 AND NVL(pol.book_classification,G_DEFAULT_CHAR) = NVL(p_book_classification, NVL(pol.book_classification,G_DEFAULT_CHAR))
2756 AND NVL(pol.pdt_id,G_DEFAULT_NUM) = NVL(p_pdt_id, NVL(pol.pdt_id,G_DEFAULT_NUM))
2757 AND NVL(pol.start_date, G_DEFAULT_DATE)
2758 BETWEEN NVL(p_start_date_from, NVL(pol.start_date, G_DEFAULT_DATE))
2759 AND NVL(p_start_date_to, NVL(pol.start_date, G_DEFAULT_DATE))
2760 AND NVL(pol.end_date,G_DEFAULT_DATE)
2761 BETWEEN NVL(p_end_date_from, NVL(pol.end_date,G_DEFAULT_DATE))
2762 AND NVL(p_end_date_to, NVL(pol.end_date,G_DEFAULT_DATE))
2763 AND NVL(pol.tax_owner,G_DEFAULT_CHAR) = NVL(p_tax_owner, NVL(pol.tax_owner,G_DEFAULT_CHAR))
2764 AND NVL(pol.stream_element_due_date, G_DEFAULT_DATE)
2765 BETWEEN NVL(p_stream_element_from_date,
2766 NVL(pol.stream_element_due_date, G_DEFAULT_DATE))
2767 AND NVL(p_stream_element_to_date,
2768 NVL(pol.stream_element_due_date, G_DEFAULT_DATE))
2769 -- cklee 02/21/2003 bug fixed
2770 AND NVL(pol.PAYMENT_FREQ,G_DEFAULT_CHAR) = NVL(p_STREAM_ELEMENT_PAYMENT_FREQ, NVL(pol.PAYMENT_FREQ,G_DEFAULT_CHAR))
2771 -- pre-req condition
2772 -- start for 11.5.10 by cklee 08/06/03 stream_type_subclass ER
2773 AND pol.stream_type_subclass IN ('RENT', 'RESIDUAL')
2774 AND NVL(pol.stream_type_subclass,G_DEFAULT_CHAR) = NVL(p_stream_type_subclass, NVL(pol.stream_type_subclass,G_DEFAULT_CHAR))
2775 -- end for 11.5.10 by cklee 08/06/03 stream_type_subclass ER
2776
2777 AND pol.stream_say_code = 'CURR'
2778 AND pol.stream_active_yn = 'Y'
2779 AND pol.stream_element_due_date > SYSDATE
2780 -- multi-org
2781 -- non existing check
2782 -- start for 11.5.10 by cklee 08/06/03 stream_type_subclass ER
2783 AND NOT EXISTS -- okl_pool_contents
2784 (SELECT '1'
2785 FROM okl_pool_contents pol_cnts,
2786 okl_strm_type_b styb
2787 WHERE pol_cnts.pol_id = pol_id
2788 -- mvasudev, stm_id changes
2789 --AND pol_cnts.sty_id = styb.id
2790 --AND pol.dnz_chr_id = pol_cnts.khr_id
2791 AND pol.stm_id = pol_cnts.stm_id
2792 AND styb.stream_type_subclass = pol.stream_type_subclass
2793 AND pol_cnts.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
2794 )
2795 -- end for 11.5.10 by cklee 08/06/03 stream_type_subclass ER
2796 AND NOT EXISTS -- variable interest rate
2797 (SELECT '1'
2798 FROM okc_rule_groups_b rgp
2799 ,okc_rules_b rg
2800 WHERE rgp.id = rg.rgp_id
2801 AND rgp.rgd_code = 'LAIIND'
2802 AND rg.rule_information_category= 'LAINTP'
2803 AND rg.rule_information1 = 'Y'
2804 AND rgp.dnz_chr_id = pol.dnz_chr_id)
2805 AND NOT EXISTS -- revision contract: rebook, split contract, reverse
2806 (SELECT '1'
2807 FROM okl_trx_contracts
2808 WHERE tcn_type IN ('TRBK','SPLC','RVS')
2809 AND tsu_code NOT IN ('PROCESSED', 'ERROR','CANCELED') -- condition changes 01/13/2003 cklee
2810 AND khr_id = pol.dnz_chr_id)
2811 AND NOT EXISTS -- split assets, split assets components
2812 (SELECT '1'
2813 FROM okl_txd_assets_v tdas,
2814 okl_txl_assets_b tal,
2815 okc_k_lines_b cle
2816 WHERE cle.id = tal.kle_id
2817 AND tal.id = tdas.tal_id
2818 AND tal.tal_type = 'ALI'
2819 -- link from okl_pool_srch_v pol
2820 AND cle.cle_id = pol.kle_id -- top line id
2821 AND tal.dnz_khr_id = pol.dnz_chr_id
2822 -- link from okl_pool_srch_v pol
2823 AND EXISTS (SELECT '1'
2824 FROM okl_trx_assets tas
2825 WHERE tas.id = tal.tas_id
2826 AND tas.tas_type = 'ALI'
2827 AND tas.tsu_code NOT IN ('PROCESSED','CANCELED'))) -- cklee 02/24/03
2828 AND NOT EXISTS -- contract is under deliquent status
2829 (SELECT '1'
2830 FROM iex_case_objects ico,
2831 iex_delinquencies_all del
2832 WHERE ico. cas_id = del.case_id
2833 AND del.status ='DELINQUENT'
2834 AND ico.object_id = pol.dnz_chr_id)
2835 AND NOT EXISTS -- contract line has been terminated
2836 (SELECT '1'
2837 FROM okc_k_lines_b cle,
2838 okc_statuses_b sts
2839 WHERE sts.code = cle.sts_code
2840 AND sts.ste_code IN ('HOLD','EXPIRED','TERMINATED','CANCELLED')
2841 AND cle.id = pol.kle_id)
2842 GROUP BY
2843 pol.dnz_chr_id
2844 ,pol.kle_id
2845 ,pol.sty_id
2846 ,pol.stream_type_code
2847 ,pol.stream_type_subclass
2848 ,pol.end_date
2849 -- mvasudev, stm_id changes
2850 ,pol.stm_id
2851 ;
2852
2853 BEGIN
2854 -- Set API savepoint
2855 SAVEPOINT add_pool_contents_PVT;
2856
2857 -- Check for call compatibility
2858 IF (NOT FND_API.Compatible_API_Call (l_api_version,
2859 p_api_version,
2860 l_api_name,
2861 G_PKG_NAME ))
2862 THEN
2863 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2864 END IF;
2865
2866 -- Initialize message list if requested
2867 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
2868 FND_MSG_PUB.initialize;
2869 END IF;
2870
2871 -- Initialize API status to success
2872 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2873
2874
2875 --/*** Begin API body ****************************************************
2876 ---------------------------------------------------------
2877 -- 1. validate date format
2878 -- 2. validate number
2879 ---------------------------------------------------------
2880
2881 -- DBMS_OUTPUT.PUT_LINE('START add_pool_contents poc');
2882
2883 l_pocv_rec.POL_ID := p_POL_ID;
2884 i := 0;
2885
2886 -- fill in l_polv_rec
2887 IF (p_STREAM_ELEMENT_PAYMENT_FREQ IS NOT NULL) THEN
2888
2889 -- DBMS_OUTPUT.PUT_LINE('IF (p_STREAM_ELEMENT_PAYMENT_FREQ IS NOT NULL) THEN');
2890
2891
2892 OPEN c_pool_payfreq;
2893 LOOP
2894 -- DBMS_OUTPUT.PUT_LINE('inside LOOP IF (p_STREAM_ELEMENT_PAYMENT_FREQ IS NOT NULL) THEN');
2895
2896
2897 FETCH c_pool_payfreq INTO
2898 l_pocv_rec.KHR_ID
2899 ,l_pocv_rec.KLE_ID
2900 ,l_pocv_rec.STY_ID
2901 ,l_pocv_rec.STY_CODE
2902 ,l_pocv_rec.STREAMS_FROM_DATE
2903 ,l_pocv_rec.STREAMS_TO_DATE
2904 -- mvasudev, stm_id changes
2905 ,l_pocv_rec.STM_ID;
2906
2907 EXIT WHEN c_pool_payfreq%NOTFOUND;
2908
2909 Okl_Pool_Pvt.create_pool_contents(
2910 p_api_version => p_api_version,
2911 p_init_msg_list => p_init_msg_list,
2912 x_return_status => x_return_status,
2913 x_msg_count => x_msg_count,
2914 x_msg_data => x_msg_data,
2915 p_pocv_rec => l_pocv_rec,
2916 x_pocv_rec => x_pocv_rec);
2917
2918 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2919 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2920 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2921 RAISE OKL_API.G_EXCEPTION_ERROR;
2922 END IF;
2923
2924 i := i+1;
2925 END LOOP;
2926 CLOSE c_pool_payfreq;
2927
2928
2929 ELSE -- to avoid outer join
2930 -- DBMS_OUTPUT.PUT_LINE('ELSE IF (p_STREAM_ELEMENT_PAYMENT_FREQ IS NOT NULL) THEN');
2931
2932 OPEN c_pool;
2933 LOOP
2934 -- DBMS_OUTPUT.PUT_LINE('inside LOOP ELSE IF (p_STREAM_ELEMENT_PAYMENT_FREQ IS NOT NULL) THEN');
2935
2936 FETCH c_pool INTO
2937 l_pocv_rec.KHR_ID
2938 ,l_pocv_rec.KLE_ID
2939 ,l_pocv_rec.STY_ID
2940 ,l_pocv_rec.STY_CODE
2941 ,l_pocv_rec.STREAMS_FROM_DATE
2942 ,l_pocv_rec.STREAMS_TO_DATE
2943 -- mvasudev, stm_id changes
2944 ,l_pocv_rec.STM_ID;
2945
2946
2947 EXIT WHEN c_pool%NOTFOUND;
2948
2949 Okl_Pool_Pvt.create_pool_contents(
2950 p_api_version => p_api_version,
2951 p_init_msg_list => p_init_msg_list,
2952 x_return_status => x_return_status,
2953 x_msg_count => x_msg_count,
2954 x_msg_data => x_msg_data,
2955 p_pocv_rec => l_pocv_rec,
2956 x_pocv_rec => x_pocv_rec);
2957
2958 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2959 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2960 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2961 RAISE OKL_API.G_EXCEPTION_ERROR;
2962 END IF;
2963
2964 i := i+1;
2965 END LOOP;
2966 CLOSE c_pool;
2967
2968 END IF;
2969 x_row_count := i;
2970 -- DBMS_OUTPUT.PUT_LINE('END add_pool_contents poc');
2971
2972
2973 --/*** End API body ******************************************************
2974
2975 -- Get message count and if count is 1, get message info
2976 FND_MSG_PUB.Count_And_Get
2977 (p_count => x_msg_count,
2978 p_data => x_msg_data);
2979
2980 EXCEPTION
2981 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2982 ROLLBACK TO add_pool_contents_pvt;
2983 x_row_count := 0;
2984
2985
2986 x_return_status := OKL_API.G_RET_STS_ERROR;
2987 FND_MSG_PUB.Count_And_Get
2988 (p_count => x_msg_count,
2989 p_data => x_msg_data);
2990
2991 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2992 ROLLBACK TO add_pool_contents_pvt;
2993 x_row_count := 0;
2994
2995 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2996 FND_MSG_PUB.Count_And_Get
2997 (p_count => x_msg_count,
2998 p_data => x_msg_data);
2999
3000 WHEN OTHERS THEN
3001 ROLLBACK TO add_pool_contents_pvt;
3002 x_row_count := 0;
3003
3004 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3005 OKL_API.Set_Message(p_app_name => G_APP_NAME,
3006 p_msg_name => G_UNEXPECTED_ERROR,
3007 p_token1 => G_SQLCODE_TOKEN,
3008 p_token1_value => SQLCODE,
3009 p_token2 => G_SQLERRM_TOKEN,
3010 p_token2_value => SQLERRM);
3011 FND_MSG_PUB.Count_And_Get
3012 (p_count => x_msg_count,
3013 p_data => x_msg_data);
3014
3015 END add_pool_contents;
3016 */
3017
3018 PROCEDURE add_pool_contents(
3019 p_api_version IN NUMBER
3020 ,p_init_msg_list IN VARCHAR2
3021 ,x_return_status OUT NOCOPY VARCHAR2
3022 ,x_msg_count OUT NOCOPY NUMBER
3023 ,x_msg_data OUT NOCOPY VARCHAR2
3024 ,x_row_count OUT NOCOPY NUMBER
3025 ,p_currency_code IN VARCHAR2
3026 ,p_pol_id IN NUMBER
3027 ,p_multi_org IN VARCHAR2
3028 ,p_cust_object1_id1 IN NUMBER
3029 ,p_sic_code IN VARCHAR2
3030 ,p_khr_id IN NUMBER
3031 ,p_pre_tax_yield_from IN NUMBER
3032 ,p_pre_tax_yield_to IN NUMBER
3033 ,p_book_classification IN VARCHAR2
3034 ,p_tax_owner IN VARCHAR2
3035 ,p_pdt_id IN NUMBER
3036 ,p_start_date_from IN DATE
3037 ,p_start_date_to IN DATE
3038 ,p_end_date_from IN DATE
3039 ,p_end_date_to IN DATE
3040 ,p_asset_id IN NUMBER
3041 ,p_item_id1 IN NUMBER
3042 ,p_model_number IN VARCHAR2
3043 ,p_manufacturer_name IN VARCHAR2
3044 ,p_vendor_id1 IN NUMBER
3045 ,p_oec_from IN NUMBER
3046 ,p_oec_to IN NUMBER
3047 ,p_residual_percentage IN NUMBER
3048 ,p_sty_id1 IN NUMBER
3049 ,p_sty_id2 IN NUMBER
3050 -- start added by cklee 08/06/03
3051 ,p_stream_type_subclass IN VARCHAR2
3052 -- end added by cklee 08/06/03
3053 ,p_stream_element_from_date IN DATE
3054 ,p_stream_element_to_date IN DATE
3055 ,p_stream_element_payment_freq IN VARCHAR2
3056 /* ankushar 26-JUL-2007 Bug#6000531 start changes*/
3057 ,p_log_message IN VARCHAR2 DEFAULT 'Y'
3058 /* ankushar end changes 26-Jul-2007*/
3059 ,p_cust_crd_clf_code IN VARCHAR2 DEFAULT NULL)
3060
3061 IS
3062
3063 -------------------------------------------
3064 -- search w/o payment frequency
3065 -------------------------------------------
3066
3067 -- Cursor to collect all pocs that satisfy the criteria
3068 CURSOR l_okl_pocs_csr IS
3069 SELECT
3070 pol.dnz_chr_id khr_id
3071 ,pol.kle_id
3072 ,pol.sty_id
3073 ,pol.stream_type_code sty_code
3074 ,MIN(pol.stream_element_due_date) streams_from_date
3075 -- mvasudev, 02/06/2004
3076 ,DECODE(pol.stream_type_subclass, 'RESIDUAL', NULL, pol.end_date+1) streams_to_date
3077 ,pol.stm_id
3078 -- extra
3079 ,pol.stream_type_subclass
3080 ,pol.contract_number
3081 ,pol.lessee
3082 ,lkup.meaning sty_subclass_meaning
3083 ,pol.asset_number
3084 ,hcp.credit_classification
3085 FROM okl_pool_srch_v pol
3086 ,fnd_lookups lkup
3087 ,hz_customer_profiles hcp
3088 WHERE
3089 -- pre-req
3090 pol.currency_code = p_currency_code
3091 AND pol.sts_code IN ('BOOKED','EVERGREEN')
3092 AND pol.assignable_yn = 'Y'
3093 AND pol.stream_element_date_billed IS NULL
3094 -- to fetch stream_type_subclass name
3095 AND lkup.lookup_type = 'OKL_STREAM_TYPE_SUBCLASS'
3096 AND lkup.lookup_code = pol.stream_type_subclass
3097 -- customer
3098 AND NVL(pol.cust_object1_id1,G_DEFAULT_NUM) = NVL(p_cust_object1_id1, NVL(pol.cust_object1_id1,G_DEFAULT_NUM))
3099 AND NVL(pol.sic_code,G_DEFAULT_CHAR) = NVL(p_sic_code, NVL(pol.sic_code,G_DEFAULT_CHAR))
3100 -- contract number
3101 AND NVL(pol.dnz_chr_id,G_DEFAULT_NUM) = NVL(p_khr_id, NVL(pol.dnz_chr_id,G_DEFAULT_NUM))
3102 AND NVL(pol.pre_tax_yield,G_DEFAULT_NUM) BETWEEN NVL(p_pre_tax_yield_from, NVL(pol.pre_tax_yield,G_DEFAULT_NUM))
3103 AND NVL(p_pre_tax_yield_to, NVL(pol.pre_tax_yield,G_DEFAULT_NUM))
3104 AND NVL(pol.book_classification,G_DEFAULT_CHAR) = NVL(p_book_classification, NVL(pol.book_classification,G_DEFAULT_CHAR))
3105 AND NVL(pol.pdt_id,G_DEFAULT_NUM) = NVL(p_pdt_id, NVL(pol.pdt_id,G_DEFAULT_NUM))
3106 AND NVL(pol.start_date, G_DEFAULT_DATE)
3107 BETWEEN NVL(p_start_date_from, NVL(pol.start_date, G_DEFAULT_DATE))
3108 AND NVL(p_start_date_to, NVL(pol.start_date, G_DEFAULT_DATE))
3109 AND NVL(pol.end_date,G_DEFAULT_DATE)
3110 BETWEEN NVL(p_end_date_from, NVL(pol.end_date,G_DEFAULT_DATE))
3111 AND NVL(p_end_date_to, NVL(pol.end_date,G_DEFAULT_DATE))
3112 AND NVL(pol.tax_owner,G_DEFAULT_CHAR) = NVL(p_tax_owner, NVL(pol.tax_owner,G_DEFAULT_CHAR))
3113 -- streams
3114 AND NVL(pol.stream_element_due_date, G_DEFAULT_DATE)
3115 BETWEEN NVL(p_stream_element_from_date,
3116 NVL(pol.stream_element_due_date, G_DEFAULT_DATE))
3117 AND NVL(p_stream_element_to_date,NVL(pol.stream_element_due_date, G_DEFAULT_DATE))
3118 --Bug 674000 ssdeshpa start
3119 AND pol.stream_type_subclass IN ('RENT', 'RESIDUAL', 'LOAN_PAYMENT')
3120 --Bug 674000 ssdeshpa end
3121 AND NVL(pol.stream_type_subclass,G_DEFAULT_CHAR) = NVL(p_stream_type_subclass, NVL(pol.stream_type_subclass,G_DEFAULT_CHAR))
3122 AND pol.stream_say_code = 'CURR'
3123 AND pol.stream_active_yn = 'Y'
3124 -- mvasudev, 02/06/2004
3125 --AND pol.stream_element_due_date > SYSDATE
3126 -- multi-org
3127 --Bug # 6691554 Changes for Cust Credit Classification Lov Start
3128 AND pol.cust_object1_id1 = hcp.party_id(+)
3129 AND hcp.cust_account_id(+) = -1
3130 AND hcp.site_use_id(+) IS NULL
3131 AND NVL(hcp.credit_classification(+),G_DEFAULT_CHAR) = NVL(p_cust_crd_clf_code, NVL(hcp.credit_classification(+),G_DEFAULT_CHAR))
3132 --Bug # 6691554 Changes for Cust Credit Classification Lov End
3133 GROUP BY
3134 pol.dnz_chr_id
3135 ,pol.kle_id
3136 ,pol.sty_id
3137 ,pol.stream_type_code
3138 ,pol.stream_type_subclass
3139 ,pol.end_date
3140 -- mvasudev, stm_id changes
3141 ,pol.stm_id
3142 ,pol.contract_number
3143 ,pol.lessee
3144 ,lkup.meaning
3145 ,pol.asset_number
3146 ,hcp.credit_classification;
3147
3148 -- Cursor to discard pocs that already exist
3149 /* ankushar 03-JAN-2008
3150 Bug#6726555 Modified cursor to look at the stream element level to achive partially bought back
3151 contracts to be associcated to another Pool.
3152 start changes
3153 */
3154 CURSOR l_okl_dup_pocs_csr(p_stream_type_subclass IN VARCHAR2,p_stm_id IN NUMBER)
3155 IS
3156 SELECT '1'
3157 FROM okl_pool_contents pol_cnts,
3158 okl_strm_type_b styb,
3159 okl_strm_elements sel
3160 WHERE pol_cnts.pol_id = pol_id
3161 AND pol_cnts.stm_id = p_stm_id
3162 AND styb.stream_type_subclass = p_stream_type_subclass
3163 AND pol_cnts.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE,G_POC_STS_PENDING)
3164 AND sel.stm_id = pol_cnts.stm_id
3165 AND sel.date_billed IS NULL
3166 GROUP BY sel.stm_id
3167 HAVING MAX(sel.STREAM_ELEMENT_DATE) <= MAX(nvl(pol_cnts.STREAMS_TO_DATE,pol_cnts.STREAMS_FROM_DATE));
3168 /* ankushar 03-JAN-2008 Bug# 6726555
3169 end Changes
3170 */
3171
3172 /*Added by kthiruva to check whether the contract streams were bought back */
3173 CURSOR l_buyback_yes_csr(p_stm_id IN NUMBER)
3174 IS
3175 SELECT '1'
3176 FROM okl_pool_contents pol_cnts
3177 WHERE pol_cnts.pol_id = pol_id
3178 AND pol_cnts.stm_id = p_stm_id
3179 AND pol_cnts.status_code = 'INACTIVE';
3180
3181 /*Added by ankushar to fetch the maximum stream to_date to pick up the remaining streams after this date */
3182 CURSOR l_max_to_date_csr(p_stm_id IN NUMBER)
3183 IS
3184 SELECT MAX(nvl(pol_cnts.STREAMS_TO_DATE,pol_cnts.STREAMS_FROM_DATE)) eff_from_date
3185 FROM okl_pool_contents pol_cnts
3186 WHERE pol_cnts.pol_id = pol_id
3187 AND pol_cnts.stm_id = p_stm_id
3188 AND pol_cnts.status_code = 'ACTIVE';
3189
3190 l_buyback_yn BOOLEAN := false;
3191 l_eff_from_date DATE;
3192 -- Variable Interest Rate
3193 -- fmiao bug 5160080--
3194 /*
3195 CURSOR l_okl_poc_vari_csr(p_dnz_chr_id IN NUMBER)
3196 IS
3197 SELECT '1'
3198 FROM okc_rule_groups_b rgp
3199 ,okc_rules_b rg
3200 WHERE rgp.id = rg.rgp_id
3201 AND rgp.rgd_code = 'LAIIND'
3202 AND rg.rule_information_category= 'LAINTP'
3203 AND rg.rule_information1 = 'Y'
3204 AND rgp.dnz_chr_id = p_dnz_chr_id;
3205 */
3206 SUBTYPE pdt_parameters_rec_type IS Okl_Setupproducts_Pvt.pdt_parameters_rec_type;
3207 l_pdt_parameters_rec pdt_parameters_rec_type;
3208 --fmiao 5160080 end-- fmiao bug 5160080 end --
3209
3210 -- revision contract: rebook, split contract, reverse
3211 CURSOR l_okl_poc_rev_csr(p_dnz_chr_id IN NUMBER)
3212 IS
3213 SELECT '1'
3214 FROM okl_trx_contracts trxc
3215 WHERE trxc.tcn_type IN ('TRBK','SPLC','RVS')
3216 AND trxc.tsu_code NOT IN ('PROCESSED', 'ERROR','CANCELED')
3217 AND trxc.khr_id = p_dnz_chr_id;
3218
3219 -- split assets, split assets components
3220 CURSOR l_okl_poc_splits_csr(p_dnz_chr_id IN NUMBER,p_kle_id IN NUMBER)
3221 IS
3222 SELECT '1'
3223 FROM okl_txd_assets_v tdas,
3224 okl_txl_assets_b tal,
3225 okc_k_lines_b cle
3226 WHERE cle.id = tal.kle_id
3227 AND tal.id = tdas.tal_id
3228 AND tal.tal_type = 'ALI'
3229 -- link from okl_pool_srch_v pol
3230 AND cle.cle_id = p_kle_id -- top line id
3231 AND tal.dnz_khr_id = p_dnz_chr_id
3232 -- link from okl_pool_srch_v pol
3233 AND EXISTS (SELECT '1'
3234 FROM okl_trx_assets tas
3235 WHERE tas.id = tal.tas_id
3236 AND tas.tas_type = 'ALI'
3237 AND tas.tsu_code NOT IN ('PROCESSED','CANCELED')
3238 );
3239
3240 -- contract is under deliquent status
3241 CURSOR l_okl_poc_delinq_csr(p_dnz_chr_id IN NUMBER)
3242 IS
3243 SELECT '1'
3244 FROM iex_case_objects ico,
3245 iex_delinquencies_all del
3246 WHERE ico. cas_id = del.case_id
3247 AND del.status ='DELINQUENT'
3248 AND ico.object_id = p_dnz_chr_id;
3249
3250 -- contract line has been terminated
3251 CURSOR l_okl_poc_kle_csr(p_kle_id IN NUMBER)
3252 IS
3253 SELECT '1'
3254 FROM okc_k_lines_b cle,
3255 okc_statuses_b sts
3256 WHERE sts.code = cle.sts_code
3257 AND sts.ste_code IN ('HOLD','EXPIRED','TERMINATED','CANCELLED')
3258 AND cle.id = p_kle_id;
3259
3260 -- cursor to discard Legal Entity Mismatch
3261
3262 --if Legal Entity Id for Pool and Contract is not same
3263 -- then raise an error
3264 CURSOR l_okl_reject_le_csr(p_khr_id IN NUMBER)
3265 IS
3266 SELECT '1'
3267 FROM okl_k_headers khr,
3268 okl_pools pol
3269 WHERE pol.legal_entity_id <> khr.legal_entity_id
3270 AND pol.id =p_pol_id
3271 AND khr.id = p_khr_id;
3272
3273 CURSOR l_okl_reject_codes_csr
3274 IS
3275 SELECT lookup_code,
3276 meaning
3277 FROM fnd_lookups
3278 WHERE LOOKUP_TYPE LIKE 'OKL_POOL_REJECT_REASON'
3279 ORDER BY LOOKUP_CODE;
3280
3281 /* sosharma 21-nov-2007
3282 R12 Bug 6640050
3283 Cursor to check whether new transaction needs to be created for adjustments
3284 Start Changes
3285 */
3286 CURSOR l_trans_exists_csr(p_pol_id IN NUMBER)
3287 IS
3288 SELECT id pox_id,transaction_number FROM OKL_POOL_TRANSACTIONS pools
3289 where pools.transaction_status in (G_POOL_TRX_STATUS_INCOMPLETE,G_POOL_TRX_STATUS_NEW,G_POOL_TRX_STATUS_APPREJ)
3290 and pools.transaction_type='ADD' and pools.transaction_reason='ADJUSTMENTS'
3291 and pools.pol_id=p_pol_id;
3292
3293 -- Cursor to get the Legal Entity Id
3294 CURSOR l_okl_agrle_csr(p_pol_id IN NUMBER)
3295 IS
3296 SELECT legal_entity_id
3297 FROM okl_pools
3298 WHERE id = p_pol_id;
3299
3300
3301 /* sosharma end changes*/
3302
3303 l_api_name CONSTANT VARCHAR2(30) := 'add_pool_contents_pvt';
3304 l_api_version CONSTANT NUMBER := 1.0;
3305
3306 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3307
3308 l_pocv_rec pocv_rec_type;
3309 x_pocv_rec pocv_rec_type;
3310 --Added by kthiruva on 21-Nov-2007 for Bug 6640050
3311 l_pocv_tbl pocv_tbl_type;
3312 l_status_code okl_pools.status_code%TYPE;
3313 l_poc_count NUMBER := 0;
3314
3315 -- l_discarded BOOLEAN := FALSE;
3316 l_discard_count NUMBER := 0;
3317 l_add_count NUMBER := 0;
3318 l_reject_code VARCHAR2(5) ;
3319
3320 -- copied from okl_poolconc_pvt (need to modify this later to directly refer to that api)
3321 l_row_num_len NUMBER := 6;
3322 l_contract_num_len NUMBER := 30;
3323 l_asset_num_len NUMBER := 15;
3324 l_lessee_len NUMBER := 40;
3325 l_sty_subclass_len NUMBER := 25;
3326 l_reject_code_len NUMBER := 20;
3327 l_filler VARCHAR2(5) := RPAD(' ',5,' ');
3328
3329 l_adds_msg_tbl msg_tbl_type;
3330 l_rejects_msg_tbl msg_tbl_type;
3331 i NUMBER;
3332 --sosharma added
3333 l_pox_id NUMBER;
3334 l_transaction_number NUMBER;
3335 l_legal_entity_id NUMBER;
3336 l_transaction_status VARCHAR2(30);
3337 l_trx_id NUMBER;
3338 l_trans_found BOOLEAN := TRUE;
3339 lp_poxv_rec poxv_rec_type;
3340 lx_poxv_rec poxv_rec_type;
3341 x_pocv_tbl pocv_tbl_type;
3342
3343
3344 BEGIN
3345 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
3346 p_pkg_name => G_PKG_NAME,
3347 p_init_msg_list => p_init_msg_list,
3348 l_api_version => l_api_version,
3349 p_api_version => p_api_version,
3350 p_api_type => G_API_TYPE,
3351 x_return_status => l_return_status);
3352
3353 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
3354 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3355 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
3356 RAISE G_EXCEPTION_ERROR;
3357 END IF;
3358
3359
3360 -- Initialize API status to success
3361 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
3362
3363 l_pocv_rec.pol_id := p_pol_id;
3364 --Added by kthiruva on 21-Nov -2007 to fetch the Pool Status
3365 --Bug 6640050 - Start of Changes
3366 FOR pool_status_rec IN pool_status_csr(p_pol_id)
3367 LOOP
3368 l_status_code := pool_status_rec.status_code;
3369 END LOOP;
3370 --Bug 6640050 - End of Changes
3371
3372 FOR l_okl_poc_rec IN l_okl_pocs_csr
3373 LOOP
3374 l_reject_code := NULL;
3375
3376 -- Any poc that is caught in the following cursors
3377 -- needs to be discarded
3378
3379
3380 -- duplicate pocs
3381 FOR l_okl_dup_pocs_rec IN l_okl_dup_pocs_csr(l_okl_poc_rec.stream_type_subclass,l_okl_poc_rec.stm_id)
3382 LOOP
3383 l_discard_count := l_discard_count + 1;
3384 l_reject_code := G_REJECT_DUP_POCS;
3385 EXIT WHEN l_reject_code IS NOT NULL;
3386 END LOOP;
3387
3388
3389 IF l_reject_code IS NULL THEN
3390 /* Added by kthiruva to check if a contract stream has been bought back */
3391 FOR l_buyback_yes_rec IN l_buyback_yes_csr(l_okl_poc_rec.stm_id)
3392 LOOP
3393 /* ankushar 21-JAN-2008
3394 Bug#6740000 Modified cursor to populate the effective from date as the max date of the Active Streams.
3395 start changes
3396 */
3397 FOR l_max_to_date_rec IN l_max_to_date_csr(l_okl_poc_rec.stm_id)
3398 LOOP
3399 l_eff_from_date := l_max_to_date_rec.eff_from_date;
3400 END LOOP;
3401 /* ankushar 21-JAN-2008
3402 Bug#6740000 end changes
3403 */
3404 l_buyback_yn := true;
3405 END LOOP;
3406 -- variable interest
3407 -- fmiao bug 5160080
3408 --FOR l_okl_poc_vari_rec IN l_okl_poc_vari_csr(l_okl_poc_rec.khr_id)
3409 --LOOP
3410 Okl_K_Rate_Params_Pvt.GET_PRODUCT(
3411 p_api_version => p_api_version,
3412 p_init_msg_list => p_init_msg_list,
3413 x_return_status => x_return_status,
3414 x_msg_count => x_msg_count,
3415 x_msg_data => x_msg_data,
3416 p_khr_id => l_okl_poc_rec.khr_id,
3417 x_pdt_parameter_rec => l_pdt_parameters_rec);
3418
3419 IF l_pdt_parameters_rec.interest_calculation_basis <> 'FIXED' THEN
3420 l_discard_count := l_discard_count + 1;
3421 l_reject_code := G_REJECT_VARIABLE_INTEREST;
3422 ELSE
3423 IF l_pdt_parameters_rec.revenue_recognition_method = 'ACTUAL' THEN
3424 l_discard_count := l_discard_count + 1;
3425 l_reject_code := G_REJECT_VARIABLE_INTEREST;
3426 END IF;
3427 END IF;
3428 --EXIT WHEN l_reject_code IS NOT NULL;
3429 --END LOOP;
3430 -- fmiao bug 5160080 end
3431 END IF;
3432
3433 IF l_reject_code IS NULL THEN
3434 -- revision contract
3435 FOR l_okl_poc_rev_rec IN l_okl_poc_rev_csr(l_okl_poc_rec.khr_id)
3436 LOOP
3437 l_discard_count := l_discard_count + 1;
3438 l_reject_code := G_REJECT_REV_KHR;
3439 EXIT WHEN l_reject_code IS NOT NULL;
3440 END LOOP;
3441 END IF;
3442
3443 IF l_reject_code IS NULL THEN
3444 -- split asset components
3445 FOR l_okl_poc_splits_rec IN l_okl_poc_splits_csr(l_okl_poc_rec.khr_id,l_okl_poc_rec.kle_id)
3446 LOOP
3447 l_discard_count := l_discard_count + 1;
3448 l_reject_code := G_REJECT_SPLIT_ASSET;
3449 EXIT WHEN l_reject_code IS NOT NULL;
3450 END LOOP;
3451 END IF;
3452
3453 IF l_reject_code IS NULL THEN
3454 -- delinquent contract
3455 FOR l_okl_poc_delinq_rec IN l_okl_poc_delinq_csr(l_okl_poc_rec.khr_id)
3456 LOOP
3457 l_discard_count := l_discard_count + 1;
3458 l_reject_code := G_REJECT_DELINQ_KHR;
3459 EXIT WHEN l_reject_code IS NOT NULL;
3460 END LOOP;
3461 END IF;
3462
3463 IF l_reject_code IS NULL THEN
3464 -- terminated assets
3465 FOR l_okl_poc_kle_rec IN l_okl_poc_kle_csr(l_okl_poc_rec.kle_id)
3466 LOOP
3467 l_discard_count := l_discard_count + 1;
3468 l_reject_code := G_REJECT_ASSET_TERMINATED;
3469 EXIT WHEN l_reject_code IS NOT NULL;
3470 END LOOP;
3471 END IF;
3472
3473 IF l_reject_code IS NULL THEN
3474 -- check for Legal Entity
3475 FOR l_okl_poc_le_rec IN l_okl_reject_le_csr(l_okl_poc_rec.khr_id)
3476 LOOP
3477 l_discard_count := l_discard_count + 1;
3478 l_reject_code := G_REJECT_LEGAL_ENTITY_MISMATCH;
3479 EXIT WHEN l_reject_code IS NOT NULL;
3480 END LOOP;
3481 END IF;
3482
3483 IF l_reject_code IS NOT NULL THEN
3484 -- write it to the report
3485
3486 IF Fnd_Global.CONC_REQUEST_ID <> -1 THEN
3487
3488 l_rejects_msg_tbl(l_discard_count).msg := RPAD(l_discard_count,l_row_num_len)
3489 || RPAD(l_okl_poc_rec.contract_number ,l_contract_num_len)
3490 || RPAD(l_okl_poc_rec.asset_number ,l_asset_num_len)
3491 || RPAD(l_okl_poc_rec.lessee ,l_lessee_len)
3492 || RPAD(l_okl_poc_rec.sty_subclass_meaning ,l_sty_subclass_len)
3493 || RPAD(l_reject_code ,l_reject_code_len);
3494 END IF;
3495
3496 ELSE
3497
3498 l_add_count := l_add_count+1;
3499 IF l_status_code = 'NEW' THEN
3500
3501 l_pocv_rec.khr_id := l_okl_poc_rec.khr_id;
3502 l_pocv_rec.kle_id := l_okl_poc_rec.kle_id;
3503 l_pocv_rec.sty_id := l_okl_poc_rec.sty_id;
3504 l_pocv_rec.sty_code := l_okl_poc_rec.sty_code;
3505 /*Modified by kthiruva to set the streams start date to effective from date of the stream has been bought back*/
3506 IF (l_buyback_yn) AND (l_okl_poc_rec.streams_from_date < (l_eff_from_date +1)) THEN
3507 l_pocv_rec.streams_from_date := l_eff_from_date + 1;
3508 ELSE
3509 l_pocv_rec.streams_from_date := l_okl_poc_rec.streams_from_date;
3510 END IF;
3511 l_pocv_rec.streams_to_date := l_okl_poc_rec.streams_to_date;
3512 l_pocv_rec.stm_id := l_okl_poc_rec.stm_id;
3513
3514 Okl_Pool_Pvt.create_pool_contents(
3515 p_api_version => p_api_version,
3516 p_init_msg_list => p_init_msg_list,
3517 x_return_status => x_return_status,
3518 x_msg_count => x_msg_count,
3519 x_msg_data => x_msg_data,
3520 p_pocv_rec => l_pocv_rec,
3521 x_pocv_rec => x_pocv_rec);
3522
3523
3524 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3525 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3526 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3527 RAISE Okl_Api.G_EXCEPTION_ERROR;
3528 END IF;
3529
3530 ELSE
3531 /* sosharma 21-Nov-2007
3532 R12 Bug 6640050
3533 Code to create a table for pool content records before making the create call
3534 Start Changes
3535 */
3536 l_pocv_tbl(l_poc_count).pol_id := p_pol_id;
3537 l_pocv_tbl(l_poc_count).khr_id := l_okl_poc_rec.khr_id;
3538 l_pocv_tbl(l_poc_count).kle_id := l_okl_poc_rec.kle_id;
3539 l_pocv_tbl(l_poc_count).sty_id := l_okl_poc_rec.sty_id;
3540 l_pocv_tbl(l_poc_count).sty_code := l_okl_poc_rec.sty_code;
3541 /*Modified by kthiruva to set the streams start date to effective
3542 * from date of the stream has been bought back*/
3543 IF (l_buyback_yn) AND (l_okl_poc_rec.streams_from_date <(l_eff_from_date +1)) THEN
3544 l_pocv_tbl(l_poc_count).streams_from_date := l_eff_from_date + 1;
3545
3546 ELSE
3547 l_pocv_tbl(l_poc_count).streams_from_date := l_okl_poc_rec.streams_from_date;
3548 END IF; l_pocv_tbl(l_poc_count).streams_to_date := l_okl_poc_rec.streams_to_date;
3549 l_pocv_tbl(l_poc_count).stm_id := l_okl_poc_rec.stm_id;
3550 -- Status code to be set as PENDING
3551 -- l_pocv_tbl(l_poc_count).status_code := G_POC_STS_PENDING;
3552 l_poc_count := l_poc_count + 1;
3553 END IF;
3554 /* sosharma End changes*/
3555
3556
3557
3558 /* ankushar 26-JUL-2007
3559 Bug#6000531
3560 start changes
3561 */
3562 IF p_log_message ='Y' THEN
3563 /* ankushar end changes 26-Jul-2007*/
3564 -- populate the log table only if the p_log_message is 'Y'
3565 IF Fnd_Global.CONC_REQUEST_ID <> -1 THEN
3566 l_adds_msg_tbl(l_add_count).msg := RPAD(l_add_count,l_row_num_len)
3567 || RPAD(l_okl_poc_rec.contract_number ,l_contract_num_len)
3568 || RPAD(l_okl_poc_rec.asset_number ,l_asset_num_len)
3569 || RPAD(l_okl_poc_rec.lessee ,l_lessee_len)
3570 || RPAD(l_okl_poc_rec.sty_subclass_meaning ,l_sty_subclass_len);
3571 END IF;
3572
3573 END IF;
3574
3575 END IF;
3576
3577 END LOOP; -- l_okl_poc_csr
3578
3579 /* sosharma 21-Nov-2007
3580 R12 Bug 6640050
3581 Create pool contents and create transaction calls
3582 Start Changes
3583 */
3584 IF l_pocv_tbl.COUNT > 0 THEN
3585 OPEN l_trans_exists_csr(p_pol_id);
3586 FETCH l_trans_exists_csr INTO l_pox_id,l_transaction_number;
3587 l_trans_found := l_trans_exists_csr%FOUND;
3588 CLOSE l_trans_exists_csr;
3589 IF l_trans_found THEN
3590 FOR i IN l_pocv_tbl.FIRST..l_pocv_tbl.LAST LOOP
3591 l_pocv_tbl(i).pox_id:= l_pox_id;
3592 l_pocv_tbl(i).transaction_number_in:=l_transaction_number;
3593 END LOOP;
3594 ELSE
3595
3596 -- get the legal entity id to create transactions
3597 OPEN l_okl_agrle_csr(p_pol_id);
3598 FETCH l_okl_agrle_csr into l_legal_entity_id;
3599 CLOSE l_okl_agrle_csr;
3600
3601 -- populate pool transaction rec
3602 lp_poxv_rec.POL_ID := p_pol_id;
3603 lp_poxv_rec.TRANSACTION_DATE := SYSDATE;
3604 lp_poxv_rec.TRANSACTION_TYPE := G_POOL_TRX_ADD;
3605 lp_poxv_rec.TRANSACTION_REASON := G_POOL_TRX_REASON_ADJUST;
3606 lp_poxv_rec.CURRENCY_CODE := p_currency_code;
3607 lp_poxv_rec.LEGAL_ENTITY_ID := l_legal_entity_id;
3608 --sosharma 03/12/2007 added to enable status on pool transaction
3609 lp_poxv_rec.TRANSACTION_STATUS := G_POOL_TRX_STATUS_NEW;
3610
3611 -- create ADD transaction for Adjustment
3612 Okl_Pool_Pvt.create_pool_transaction(p_api_version => p_api_version
3613 ,p_init_msg_list => p_init_msg_list
3614 ,x_return_status => l_return_status
3615 ,x_msg_count => x_msg_count
3616 ,x_msg_data => x_msg_data
3617 ,p_poxv_rec => lp_poxv_rec
3618 ,x_poxv_rec => lx_poxv_rec);
3619
3620 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3621 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3622 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3623 RAISE Okl_Api.G_EXCEPTION_ERROR;
3624 END IF;
3625 -- Assign the Transaction Id to pool contents
3626 FOR i IN l_pocv_tbl.FIRST..l_pocv_tbl.LAST LOOP
3627 l_pocv_tbl(i).pox_id:= lx_poxv_rec.id;
3628 l_pocv_tbl(i).transaction_number_in:= lx_poxv_rec.transaction_number;
3629 END LOOP;
3630
3631 END IF;
3632 --- create pool contents for Adjustment ADD transaction
3633 Okl_Pool_Pvt.create_pool_contents(
3634 p_api_version => p_api_version,
3635 p_init_msg_list => p_init_msg_list,
3636 x_return_status => x_return_status,
3637 x_msg_count => x_msg_count,
3638 x_msg_data => x_msg_data,
3639 p_pocv_tbl => l_pocv_tbl,
3640 x_pocv_tbl => x_pocv_tbl);
3641
3642
3643 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3644 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3645 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3646 RAISE Okl_Api.G_EXCEPTION_ERROR;
3647 END IF;
3648
3649 -- get existing the transaction status
3650 OPEN l_trans_status_csr(p_pol_id);
3651 FETCH l_trans_status_csr INTO l_transaction_status,l_trx_id;
3652 CLOSE l_trans_status_csr;
3653
3654 IF l_transaction_status = G_POOL_TRX_STATUS_APPREJ THEN
3655 lp_poxv_rec.TRANSACTION_STATUS := G_POOL_TRX_STATUS_INCOMPLETE;
3656 lp_poxv_rec.POL_ID := p_pol_id;
3657 lp_poxv_rec.ID := l_trx_id;
3658
3659 -- create ADD transaction for Adjustment
3660 Okl_Pool_Pvt.update_pool_transaction(p_api_version => p_api_version
3661 ,p_init_msg_list => p_init_msg_list
3662 ,x_return_status => l_return_status
3663 ,x_msg_count => x_msg_count
3664 ,x_msg_data => x_msg_data
3665 ,p_poxv_rec => lp_poxv_rec
3666 ,x_poxv_rec => lx_poxv_rec);
3667
3668 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3669 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3670 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3671 RAISE Okl_Api.G_EXCEPTION_ERROR;
3672 END IF;
3673 END IF;
3674
3675 END IF;
3676
3677
3678 /* sosharma end changes*/
3679
3680 /* ankushar 26-JUL-2007
3681 Bug#6000531
3682 start changes
3683 */
3684 IF p_log_message ='Y' THEN
3685 /* ankushar end changes 26-Jul-2007*/
3686 /*** REJECTS ***/
3687 -- note preceding table header
3688 Fnd_File.Put_Line(Fnd_File.output,' ');
3689 Fnd_File.Put_Line(Fnd_File.output,Fnd_Message.get_string(g_app_name,g_pool_add_tbl_hdr));
3690
3691 -- table header
3692 Fnd_File.Put_Line(Fnd_File.output,RPAD('-',l_row_num_len-1,'-') || ' '
3693 || RPAD('-',l_contract_num_len-1,'-') || ' '
3694 || RPAD('-',l_asset_num_len-1,'-') || ' '
3695 || RPAD('-',l_lessee_len-1,'-') || ' '
3696 || RPAD('-',l_sty_subclass_len-1,'-') || ' '
3697 || RPAD('-',l_reject_code_len-1,'-'));
3698
3699 Fnd_File.Put_Line(Fnd_File.output,RPAD(Fnd_Message.get_string(g_app_name,g_row_number),l_row_num_len-1) || ' '
3700 || RPAD(Fnd_Message.get_string(g_app_name,g_contract_number),l_contract_num_len-1) || ' '
3701 || RPAD(Fnd_Message.get_string(g_app_name,g_asset_number),l_asset_num_len-1) || ' '
3702 || RPAD(Fnd_Message.get_string(g_app_name,g_lessee),l_lessee_len-1) || ' '
3703 || RPAD(Fnd_Message.get_string(g_app_name,g_stream_type_subclass),l_sty_subclass_len-1) || ' '
3704 || RPAD(Fnd_Message.get_string(g_app_name,g_reject_reason_code),l_reject_code_len-1));
3705
3706 Fnd_File.Put_Line(Fnd_File.output,RPAD('-',l_row_num_len-1,'-') || ' '
3707 || RPAD('-',l_contract_num_len-1,'-') || ' '
3708 || RPAD('-',l_asset_num_len-1,'-') || ' '
3709 || RPAD('-',l_lessee_len-1,'-') || ' '
3710 || RPAD('-',l_sty_subclass_len-1,'-') || ' '
3711 || RPAD('-',l_reject_code_len-1,'-'));
3712
3713 FOR i IN 1..l_rejects_msg_tbl.COUNT
3714 LOOP
3715 Fnd_File.Put_Line(Fnd_File.OUTPUT, l_rejects_msg_tbl(i).msg);
3716 END LOOP;
3717
3718 Fnd_File.Put_Line(Fnd_File.OUTPUT,' ');
3719 Fnd_File.Put_Line(Fnd_File.OUTPUT,Fnd_Message.GET_STRING(G_APP_NAME,G_REJECT_REASON_CODES));
3720
3721 -- Listing Reason Code Meaning-s
3722 FOR l_okl_reject_codes_rec IN l_okl_reject_codes_csr
3723 LOOP
3724 Fnd_File.Put_Line(Fnd_File.OUTPUT,l_filler || l_okl_reject_codes_rec.lookup_code
3725 || ' => '
3726 || l_okl_reject_codes_rec.meaning);
3727 END LOOP;
3728
3729 /*** ADDS ***/
3730
3731 -- note preceding table header
3732 Fnd_File.Put_Line(Fnd_File.output,' ');
3733 Fnd_File.Put_Line(Fnd_File.output,' ');
3734 Fnd_File.Put_Line(Fnd_File.output,' ');
3735 Fnd_File.Put_Line(Fnd_File.output,' ');
3736 Fnd_File.Put_Line(Fnd_File.output,Fnd_Message.get_string(g_app_name,g_pool_add_new_tbl_hdr));
3737
3738 -- table header
3739 Fnd_File.Put_Line(Fnd_File.output,RPAD('-',l_row_num_len-1,'-') || ' '
3740 || RPAD('-',l_contract_num_len-1,'-') || ' '
3741 || RPAD('-',l_asset_num_len-1,'-') || ' '
3742 || RPAD('-',l_lessee_len-1,'-') || ' '
3743 || RPAD('-',l_sty_subclass_len-1,'-'));
3744
3745 Fnd_File.Put_Line(Fnd_File.output,RPAD(Fnd_Message.get_string(g_app_name,g_row_number),l_row_num_len-1) || ' '
3746 || RPAD(Fnd_Message.get_string(g_app_name,g_contract_number),l_contract_num_len-1) || ' '
3747 || RPAD(Fnd_Message.get_string(g_app_name,g_asset_number),l_asset_num_len-1) || ' '
3748 || RPAD(Fnd_Message.get_string(g_app_name,g_lessee),l_lessee_len-1) || ' '
3749 || RPAD(Fnd_Message.get_string(g_app_name,g_stream_type_subclass),l_sty_subclass_len-1));
3750
3751 Fnd_File.Put_Line(Fnd_File.output,RPAD('-',l_row_num_len-1,'-') || ' '
3752 || RPAD('-',l_contract_num_len-1,'-') || ' '
3753 || RPAD('-',l_asset_num_len-1,'-') || ' '
3754 || RPAD('-',l_lessee_len-1,'-') || ' '
3755 || RPAD('-',l_sty_subclass_len-1,'-'));
3756
3757 FOR i IN 1..l_adds_msg_tbl.COUNT
3758 LOOP
3759 Fnd_File.Put_Line(Fnd_File.OUTPUT, l_adds_msg_tbl(i).msg);
3760 END LOOP;
3761
3762 END IF;
3763
3764 x_row_count := l_add_count;
3765
3766 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count
3767 ,x_msg_data => x_msg_data);
3768
3769 x_return_status := l_return_status;
3770
3771
3772 EXCEPTION
3773 WHEN G_EXCEPTION_ERROR THEN
3774
3775 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3776 p_pkg_name => G_PKG_NAME,
3777 p_exc_name => G_EXC_NAME_ERROR,
3778 x_msg_count => x_msg_count,
3779 x_msg_data => x_msg_data,
3780 p_api_type => G_API_TYPE);
3781 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
3782
3783 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3784 p_pkg_name => G_PKG_NAME,
3785 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
3786 x_msg_count => x_msg_count,
3787 x_msg_data => x_msg_data,
3788 p_api_type => G_API_TYPE);
3789 WHEN OTHERS THEN
3790
3791 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3792 p_pkg_name => G_PKG_NAME,
3793 p_exc_name => G_EXC_NAME_OTHERS,
3794 x_msg_count => x_msg_count,
3795 x_msg_data => x_msg_data,
3796 p_api_type => G_API_TYPE);
3797
3798
3799 END add_pool_contents;
3800
3801 ----------------------------------------------------------------------------------
3802 -- Start of comments
3803 --
3804 -- Procedure Name : cleanup_pool_contents
3805 -- Description : removes pool contents based on passed in search criteria
3806 -- Business Rules :
3807 -- Parameters :
3808 -- Version : 1.0
3809 -- End of comments
3810 ----------------------------------------------------------------------------------
3811 -- Create by Search Criteria: Query Streams from contracts + Create
3812
3813 PROCEDURE cleanup_pool_contents(
3814 p_api_version IN NUMBER
3815 ,p_init_msg_list IN VARCHAR2
3816 ,x_return_status OUT NOCOPY VARCHAR2
3817 ,x_msg_count OUT NOCOPY NUMBER
3818 ,x_msg_data OUT NOCOPY VARCHAR2
3819 ,p_currency_code IN VARCHAR2
3820 ,p_pol_id IN NUMBER
3821 ,p_multi_org IN VARCHAR2
3822 ,p_cust_object1_id1 IN NUMBER
3823 ,p_sic_code IN VARCHAR2
3824 ,p_dnz_chr_id IN NUMBER
3825 ,p_pre_tax_yield_from IN NUMBER
3826 ,p_pre_tax_yield_to IN NUMBER
3827 ,p_book_classification IN VARCHAR2
3828 ,p_tax_owner IN VARCHAR2
3829 ,p_pdt_id IN NUMBER
3830 ,p_start_from_date IN DATE
3831 ,p_start_to_date IN DATE
3832 ,p_end_from_date IN DATE
3833 ,p_end_to_date IN DATE
3834 ,p_asset_id IN NUMBER
3835 ,p_item_id1 IN NUMBER
3836 ,p_model_number IN VARCHAR2
3837 ,p_manufacturer_name IN VARCHAR2
3838 ,p_vendor_id1 IN NUMBER
3839 ,p_oec_from IN NUMBER
3840 ,p_oec_to IN NUMBER
3841 ,p_residual_percentage IN NUMBER
3842 ,p_sty_id IN NUMBER
3843 -- mvasudev, 11.5.10
3844 ,p_stream_type_subclass IN VARCHAR2
3845 -- end, mvasudev, 11.5.10
3846 ,p_streams_from_date IN DATE
3847 ,p_streams_to_date IN DATE
3848 ,p_action_code IN VARCHAR2
3849 ,x_poc_uv_tbl OUT NOCOPY poc_uv_tbl_type
3850 ,p_cust_crd_clf_code IN VARCHAR2 DEFAULT NULL)
3851 IS
3852 l_api_name CONSTANT VARCHAR2(30) := 'cleanup_pool_contents';
3853 l_api_version CONSTANT NUMBER := 1.0;
3854 i NUMBER;
3855 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3856
3857 l_pocv_rec pocv_rec_type;
3858 lp_poxv_rec poxv_rec_type;
3859 lx_poxv_rec poxv_rec_type;
3860
3861 l_transaction_status VARCHAR2(30);
3862 l_trx_id NUMBER;
3863
3864 CURSOR l_okl_pool_cleanup_csr(p_allowed_sts Varchar2) IS
3865 SELECT pocv.poc_id
3866 ,pocv.contract_number
3867 ,pocv.asset_number
3868 ,pocv.lessee
3869 ,pocv.stream_type_name
3870 ,pocv.sty_subclass_code
3871 ,pocv.sty_subclass
3872 -- mvasudev, 09/28/2004, Bug#3909240
3873 ,pocv.stream_type_purpose
3874 ,hcp.credit_classification
3875 FROM okl_pool_contents_uv pocv
3876 ,hz_customer_profiles hcp
3877 -- pool_number
3878 WHERE pocv.pol_id = p_pol_id
3879 -- customer
3880 AND NVL(pocv.cust_object1_id1,G_DEFAULT_NUM) = NVL(p_cust_object1_id1, NVL(pocv.cust_object1_id1,G_DEFAULT_NUM))
3881 AND NVL(pocv.sic_code,G_DEFAULT_CHAR) = NVL(p_sic_code, NVL(pocv.sic_code,G_DEFAULT_CHAR))
3882 -- contract
3883 AND NVL(pocv.dnz_chr_id,G_DEFAULT_NUM) = NVL(p_dnz_chr_id, NVL(pocv.dnz_chr_id,G_DEFAULT_NUM))
3884 AND NVL(pocv.pre_tax_yield,G_DEFAULT_NUM) BETWEEN NVL(p_pre_tax_yield_from, NVL(pocv.pre_tax_yield,G_DEFAULT_NUM))
3885 AND NVL(p_pre_tax_yield_to, NVL(pocv.pre_tax_yield,G_DEFAULT_NUM))
3886 AND NVL(pocv.book_classification,G_DEFAULT_CHAR) = NVL(p_book_classification, NVL(pocv.book_classification,G_DEFAULT_CHAR))
3887 AND NVL(pocv.pdt_id,G_DEFAULT_NUM) = NVL(p_pdt_id, NVL(pocv.pdt_id,G_DEFAULT_NUM))
3888 AND NVL(pocv.start_date, G_DEFAULT_DATE)
3889 BETWEEN NVL(p_start_from_date, NVL(pocv.start_date, G_DEFAULT_DATE))
3890 AND NVL(p_start_to_date, NVL(pocv.start_date, G_DEFAULT_DATE))
3891 AND NVL(pocv.end_date,G_FINAL_DATE)
3892 BETWEEN NVL(p_end_from_date, NVL(pocv.end_date,G_FINAL_DATE))
3893 AND NVL(p_end_to_date, NVL(pocv.end_date,G_FINAL_DATE))
3894 AND NVL(pocv.tax_owner,G_DEFAULT_CHAR) = NVL(p_tax_owner, NVL(pocv.tax_owner,G_DEFAULT_CHAR))
3895 -- asset
3896 /* cklee, 04/23/2003
3897 AND NVL(pocv.asset_id,G_DEFAULT_NUM) = NVL(p_asset_id, NVL(pocv.asset_id,G_DEFAULT_NUM))
3898 AND NVL(UPPER(pocv.model_number),G_DEFAULT_CHAR) LIKE NVL(UPPER(p_model_number),NVL(UPPER(pocv.model_number),G_DEFAULT_CHAR))
3899 AND NVL(UPPER(pocv.manufacturer_name),G_DEFAULT_CHAR) LIKE NVL(UPPER(p_manufacturer_name),NVL(UPPER(pocv.manufacturer_name),G_DEFAULT_CHAR))
3900 AND NVL(pocv.item_id1,G_DEFAULT_NUM) = NVL(p_item_id1, NVL(pocv.item_id1,G_DEFAULT_NUM))
3901 AND NVL(pocv.vendor_id1,G_DEFAULT_NUM) = NVL(p_vendor_id1, NVL(pocv.vendor_id1,G_DEFAULT_NUM))
3902 AND NVL(pocv.oec,G_DEFAULT_NUM) BETWEEN NVL(p_oec_from, NVL(pocv.oec,G_DEFAULT_NUM))
3903 AND NVL(p_oec_to, NVL(pocv.oec,G_DEFAULT_NUM))
3904 AND NVL(pocv.residual_percentage,G_DEFAULT_NUM) = NVL(p_residual_percentage, NVL(pocv.residual_percentage,G_DEFAULT_NUM))
3905 */
3906 -- streams
3907 -- mvasudev, 11.5.10
3908 --AND NVL(pocv.sty_id,G_DEFAULT_NUM) = NVL(p_sty_id, NVL(pocv.sty_id,G_DEFAULT_NUM))
3909 AND NVL(pocv.sty_subclass_code,G_DEFAULT_CHAR) = NVL(p_stream_type_subclass, NVL(pocv.sty_subclass_code,G_DEFAULT_CHAR))
3910 -- end, mvasudev, 11.5.10
3911 AND NVL(pocv.streams_from_date, G_DEFAULT_DATE)
3912 BETWEEN NVL(p_streams_from_date, NVL(pocv.streams_from_date, G_DEFAULT_DATE))
3913 AND NVL(p_streams_to_date, NVL(pocv.streams_to_date, G_FINAL_DATE))
3914 -- cklee 04/10/2003
3915 AND EXISTS (SELECT '1'
3916 FROM okl_pool_contents pc
3917 WHERE pc.id = pocv.POC_ID
3918 AND pc.status_code IN (p_allowed_sts))
3919 -- cklee 04/10/2003
3920 --Bug # 6691554 Changes for Cust Credit Classification Lov Start
3921 AND pocv.cust_object1_id1 = hcp.party_id(+)
3922 AND hcp.cust_account_id(+) = -1
3923 AND hcp.site_use_id(+) IS NULL
3924 AND NVL(hcp.credit_classification(+),G_DEFAULT_CHAR) = NVL(p_cust_crd_clf_code, NVL(hcp.credit_classification(+),G_DEFAULT_CHAR))
3925 --Bug # 6691554 Changes for Cust Credit Classification Lov End
3926 ORDER BY pocv.contract_number
3927 ,pocv.asset_number
3928 ,pocv.stream_type_name;
3929
3930
3931 lp_pocv_tbl pocv_tbl_type;
3932
3933 l_row_count NUMBER;
3934 l_pool_amount NUMBER;
3935 --Begin - Changes for Bug 6640050 by varangan on 29-Nov-2007
3936 CURSOR c_strm_amount ( p_poc_id NUMBER)
3937 IS
3938 SELECT NVL(SUM(NVL(ele.AMOUNT,0)),0) STREAM_AMOUNT
3939 FROM okl_streams strm
3940 ,okl_strm_elements ele
3941 ,okl_pool_contents cnt
3942 WHERE strm.id = ele.stm_id
3943 AND cnt.ID = p_poc_id
3944 AND strm.ID = cnt.STM_ID
3945 AND strm.say_code = 'CURR'
3946 AND strm.active_yn = 'Y'
3947 AND cnt.status_code IN (G_POC_STS_PENDING)
3948 AND ele.STREAM_ELEMENT_DATE
3949 BETWEEN cnt.STREAMS_FROM_DATE AND NVL(cnt.STREAMS_TO_DATE,G_FINAL_DATE);
3950
3951 l_allowed_status Varchar2(100);
3952 l_status_code okl_pools.status_Code%TYPE;
3953 --End - Changes for Bug 6640050 by varangan on 29-Nov-2007
3954
3955 BEGIN
3956 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
3957 p_pkg_name => G_PKG_NAME,
3958 p_init_msg_list => p_init_msg_list,
3959 l_api_version => l_api_version,
3960 p_api_version => p_api_version,
3961 p_api_type => G_API_TYPE,
3962
3963 x_return_status => l_return_status);
3964 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
3965 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3966 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
3967 RAISE G_EXCEPTION_ERROR;
3968 END IF;
3969
3970
3971 l_row_count := 0;
3972 -- Begin - Changes for Bug#6658065
3973 --(1) Check the pool status for clean up action
3974 FOR pool_status_rec IN pool_status_csr(p_pol_id)
3975 LOOP
3976 l_status_code := pool_status_rec.status_code;
3977 END LOOP;
3978 --(2) If the Status is 'Active', then only 'Pending' status pool contents created for adjustment should be removed
3979 -- else, existing flow should be followed
3980 IF l_status_code = G_POL_STS_ACTIVE THEN
3981 l_allowed_status:= G_POC_STS_PENDING;
3982 ELSE
3983 l_allowed_status:=G_POC_STS_NEW; -- Fetch only 'New' status POCs for Bug 6691554
3984 END IF;
3985 --(3) Query the pool contents as per the status check
3986 FOR l_okl_pool_cleanup_rec IN l_okl_pool_cleanup_csr(l_allowed_status)
3987 LOOP
3988 l_row_count := l_row_count + 1;
3989 lp_pocv_tbl(l_row_count).id := l_okl_pool_cleanup_rec.poc_id;
3990 -- Get Pool Stream Amount to Display
3991 IF l_status_code= G_POL_STS_ACTIVE THEN -- Get the pending contents stream amount
3992 OPEN c_strm_amount (l_okl_pool_cleanup_rec.poc_id) ;
3993 FETCH c_strm_amount INTO l_pool_amount;
3994 CLOSE c_strm_amount;
3995 ELSE -- follow the existing process
3996 l_pool_amount := Okl_Pool_Pvt.get_pool_stream_amout(l_okl_pool_cleanup_rec.poc_id);
3997 END IF;
3998
3999 x_poc_uv_tbl(l_row_count).poc_id := l_okl_pool_cleanup_rec.poc_id;
4000 x_poc_uv_tbl(l_row_count).contract_number := l_okl_pool_cleanup_rec.contract_number;
4001 x_poc_uv_tbl(l_row_count).asset_number := l_okl_pool_cleanup_rec.asset_number;
4002 x_poc_uv_tbl(l_row_count).lessee := l_okl_pool_cleanup_rec.lessee;
4003 x_poc_uv_tbl(l_row_count).stream_type_name := l_okl_pool_cleanup_rec.stream_type_name;
4004
4005 x_poc_uv_tbl(l_row_count).sty_subclass_code := l_okl_pool_cleanup_rec.sty_subclass_code;
4006 x_poc_uv_tbl(l_row_count).sty_subclass := l_okl_pool_cleanup_rec.sty_subclass;
4007
4008 x_poc_uv_tbl(l_row_count).pool_amount := l_pool_amount;
4009 -- mvasudev, 09/28/2004, Bug#3909240
4010 x_poc_uv_tbl(l_row_count).stream_type_purpose := l_okl_pool_cleanup_rec.stream_type_purpose;
4011
4012 END LOOP;
4013 -- End - Changes for Bug#6658065
4014
4015 IF p_action_code = Okl_Pool_Pvt.G_ACTION_REMOVE THEN
4016 Okl_Pool_Pvt.delete_pool_contents(p_api_version => p_api_version
4017 ,p_init_msg_list => p_init_msg_list
4018 ,x_return_status => l_return_status
4019 ,x_msg_count => x_msg_count
4020 ,x_msg_data => x_msg_data
4021 ,p_pocv_tbl => lp_pocv_tbl);
4022
4023 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
4024 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4025 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
4026 RAISE G_EXCEPTION_ERROR;
4027 END IF;
4028 END IF;
4029
4030 -- get existing the transaction status
4031 OPEN l_trans_status_csr(p_pol_id);
4032 FETCH l_trans_status_csr INTO l_transaction_status,l_trx_id;
4033 CLOSE l_trans_status_csr;
4034
4035 IF l_transaction_status = G_POOL_TRX_STATUS_APPREJ THEN
4036 lp_poxv_rec.TRANSACTION_STATUS := G_POOL_TRX_STATUS_INCOMPLETE;
4037 lp_poxv_rec.POL_ID := p_pol_id;
4038 lp_poxv_rec.ID := l_trx_id;
4039
4040 -- create ADD transaction for Adjustment
4041 Okl_Pool_Pvt.update_pool_transaction(p_api_version => p_api_version
4042 ,p_init_msg_list => p_init_msg_list
4043 ,x_return_status => l_return_status
4044 ,x_msg_count => x_msg_count
4045 ,x_msg_data => x_msg_data
4046 ,p_poxv_rec => lp_poxv_rec
4047 ,x_poxv_rec => lx_poxv_rec);
4048
4049 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4050 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4051 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4052 RAISE Okl_Api.G_EXCEPTION_ERROR;
4053 END IF;
4054 END IF;
4055
4056 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count
4057 ,x_msg_data => x_msg_data);
4058
4059 x_return_status := l_return_status;
4060
4061 EXCEPTION
4062 WHEN G_EXCEPTION_ERROR THEN
4063
4064 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
4065 p_pkg_name => G_PKG_NAME,
4066 p_exc_name => G_EXC_NAME_ERROR,
4067 x_msg_count => x_msg_count,
4068 x_msg_data => x_msg_data,
4069 p_api_type => G_API_TYPE);
4070 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
4071
4072 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
4073 p_pkg_name => G_PKG_NAME,
4074 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
4075 x_msg_count => x_msg_count,
4076 x_msg_data => x_msg_data,
4077 p_api_type => G_API_TYPE);
4078 WHEN OTHERS THEN
4079
4080 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
4081 p_pkg_name => G_PKG_NAME,
4082 p_exc_name => G_EXC_NAME_OTHERS,
4083 x_msg_count => x_msg_count,
4084 x_msg_data => x_msg_data,
4085 p_api_type => G_API_TYPE);
4086 END cleanup_pool_contents;
4087
4088 ----------------------------------------------------------------------------------
4089 -- Start of comments
4090 --
4091 -- Procedure Name : reconcile_contents
4092 -- Description : reconcile pool contents
4093 -- Business Rules :
4094 -- Parameters :
4095 -- Version : 1.0
4096 -- End of comments
4097 ----------------------------------------------------------------------------------
4098 PROCEDURE reconcile_contents(p_api_version IN NUMBER
4099 ,p_init_msg_list IN VARCHAR2
4100 ,p_pol_id IN NUMBER
4101 ,p_mode IN VARCHAR2 DEFAULT NULL
4102 ,x_return_status OUT NOCOPY VARCHAR2
4103 ,x_msg_count OUT NOCOPY NUMBER
4104 ,x_msg_data OUT NOCOPY VARCHAR2
4105 ,x_reconciled OUT NOCOPY VARCHAR2)
4106 IS
4107 --fmiao 21-OCT-2005 bug 4775555 --
4108 CURSOR evg_rent_strms_csr (p_pol_id IN NUMBER)
4109 IS
4110 -- to remove all the rent strms if the contract turns to EVERGREEN--
4111 SELECT poc.id
4112 FROM okl_pool_contents poc,
4113 okl_pools pol,
4114 okl_strm_type_b sty,
4115 okc_k_headers_b CHR
4116 WHERE pol.id = p_pol_id
4117 AND pol.id = poc.pol_id
4118 AND poc.sty_id = sty.id
4119 AND sty.STREAM_TYPE_SUBCLASS ='RENT'
4120 AND poc.KHR_ID = CHR.id
4121 AND CHR.sts_code ='EVERGREEN'
4122 AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE) ;
4123 --fmiao 21-OCT-2005 bug 4775555 --
4124
4125 CURSOR l_okl_invalid_khr_csr(p_pol_id IN NUMBER)
4126 IS
4127 -- to remove all contents pointing to invalid contracts
4128 SELECT poc.id
4129 FROM okl_pool_contents poc,
4130 okc_k_headers_b chrb,
4131 okl_k_headers khrb
4132 WHERE poc.pol_id = p_pol_id
4133 AND poc.khr_id = chrb.id
4134 AND poc.khr_id = khrb.id
4135 -- cklee 04/10/2003 never reconcile historical data
4136 AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
4137 -- cklee 04/10/2003 never reconcile historical data
4138 AND (chrb.sts_code NOT IN ('BOOKED','EVERGREEN') OR khrb.assignable_yn <> 'Y');
4139
4140 CURSOR l_okl_delinq_khr_csr(p_pol_id IN NUMBER)
4141 IS
4142 -- to remove all contents pointing to delinquent contracts
4143 SELECT poc.id
4144 FROM okl_pool_contents poc,
4145 iex_case_objects ico,
4146 iex_delinquencies_all del
4147 WHERE poc.pol_id = p_pol_id
4148 AND poc.khr_id = ico.object_id
4149 AND ico. cas_id = del.case_id
4150 -- cklee 04/10/2003 never reconcile historical data
4151 AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
4152 -- cklee 04/10/2003 never reconcile historical data
4153 AND del.status = 'DELINQUENT';
4154
4155 CURSOR l_okl_rev_khr_csr(p_pol_id IN NUMBER)
4156 IS
4157 -- and to remove all contents pointing to contracts under modification
4158 SELECT poc.id
4159 FROM okl_pool_contents poc,
4160 okc_k_headers_b chrb
4161 WHERE poc.pol_id = p_pol_id
4162 AND poc.khr_id = chrb.id
4163 -- cklee 04/10/2003 never reconcile historical data
4164 AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
4165 -- cklee 04/10/2003 never reconcile historical data
4166 -- AND chrb.sts_code = 'BOOKED'
4167 AND EXISTS -- revision contract: rebook, split contract, reverse
4168 (SELECT '1'
4169 FROM okl_trx_contracts trxb
4170 WHERE trxb.tcn_type IN ('TRBK','SPLC','RVS')
4171 -- AND trxb.tsu_code = 'ENTERED'
4172 AND trxb.tsu_code NOT IN ('PROCESSED', 'ERROR','CANCELED') -- condition changes 01/13/2003 cklee
4173 AND trxb.khr_id = poc.khr_id
4174 )
4175 AND EXISTS -- split assets, split assets components
4176 (SELECT '1'
4177 FROM okl_txd_assets_v tdas,
4178 okl_txl_assets_b talb,
4179 okc_k_lines_b cleb
4180 WHERE cleb.id = talb.kle_id
4181 AND talb.ID = tdas.TAL_ID
4182 AND talb.TAL_TYPE = 'ALI'
4183 AND cleb.cle_id = poc.kle_id -- top line id
4184 AND talb.dnz_khr_id = poc.khr_id
4185 AND EXISTS (SELECT '1'
4186 FROM okl_trx_assets tas
4187 WHERE tas.id = talb.tas_id
4188 AND tas.tas_type = 'ALI'
4189 AND tas.tsu_code = 'PROCESSED')
4190 );
4191
4192 CURSOR l_okl_invalid_assets_csr(p_pol_id IN NUMBER)
4193 IS
4194 -- and to remove all contents pointing to lease contracts
4195 -- that have atleast one invalid asset
4196 SELECT poc.id
4197 FROM okl_pool_contents poc
4198 WHERE poc.khr_id
4199 IN
4200 (SELECT poc.khr_id
4201 FROM okl_pool_contents poc,
4202 okc_k_lines_b cleb,
4203 okc_statuses_b stsb
4204 WHERE poc.pol_id = p_pol_id
4205 AND poc.kle_id = cleb.id
4206 AND cleb.sts_code = stsb.code
4207 -- cklee 04/10/2003 never reconcile historical data
4208 AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
4209 -- cklee 04/10/2003 never reconcile historical data
4210 AND stsb.ste_code IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
4211 );
4212
4213 -- v115.37 Fix
4214 CURSOR l_okl_invalid_streams_csr(p_pol_id IN NUMBER)
4215 IS
4216 -- to remove all contents pointing to inactive streams
4217 --- or assets that do not have streams
4218 /*
4219 SELECT poc.id
4220 FROM okl_pool_contents poc,
4221 okl_streams stmb
4222 WHERE poc.pol_id = p_pol_id
4223 AND poc.kle_id = stmb.kle_id
4224 AND poc.sty_id = stmb.sty_id
4225 AND (
4226 -- if the streams are not active
4227 stmb.active_yn <> 'Y'
4228 --if stream elements do not exist
4229
4230 OR NOT EXISTS
4231 (
4232 SELECT '1'
4233 FROM okl_strm_elements selb
4234 WHERE selb.stm_id = stmb.id
4235 )
4236 );
4237 */
4238 SELECT poc.id
4239 FROM okl_pool_contents poc
4240 WHERE poc.pol_id = p_pol_id
4241 AND NOT EXISTS
4242 ( SELECT '1'
4243 FROM OKL_POOL_STREAMS_UV pols
4244 WHERE poc.stm_id = pols.stm_id
4245 AND pols.stream_say_code = 'CURR'
4246 AND pols.stream_active_yn = 'Y'
4247 -- mvasudev, 02/06/2004
4248 --AND pols.stream_element_due_date > SYSDATE
4249 )
4250 -- cklee 04/10/2003 never reconcile historical data
4251 AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
4252 ;
4253 -- cklee 04/10/2003 never reconcile historical data
4254
4255
4256 CURSOR l_okl_update_khr_dates_csr(p_pol_id IN NUMBER)
4257 IS
4258 SELECT poc.id, chrb.end_date
4259 FROM okl_pool_contents poc,
4260 okc_k_headers_b chrb
4261 WHERE poc.pol_id = p_pol_id
4262 AND poc.khr_id = chrb.id
4263 -- cklee 04/10/2003 never reconcile historical data
4264 AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
4265 -- cklee 04/10/2003 never reconcile historical data
4266 -- mvasudev,02/06/2004
4267 AND poc.streams_to_date <> chrb.end_date+1;
4268
4269 CURSOR l_okl_update_dates_csr(p_pol_id IN NUMBER)
4270 IS
4271 SELECT poc.id, poc.stm_id
4272 FROM okl_pool_contents poc
4273 ,okl_streams stmb
4274 WHERE poc.pol_id = p_pol_id
4275 AND poc.stm_id = stmb.id
4276 AND stmb.say_code = 'CURR'
4277 AND stmb.active_yn = 'Y'
4278 -- cklee 04/10/2003 never reconcile historical data
4279 AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
4280 -- cklee 04/10/2003 never reconcile historical data
4281 AND TRUNC(poc.streams_from_date) <> ( SELECT TRUNC(MIN(selb.stream_element_date))
4282 FROM okl_strm_elements selb
4283 WHERE selb.stm_id = stmb.id
4284 AND selb.date_billed IS NULL
4285 -- mvasudev, 02/06/2004
4286 --AND selb.stream_element_date > SYSDATE
4287 );
4288
4289 /* sosharma 26-Dec-2007
4290 New cursors to reconcile Transient pool contents
4291 Start Changes
4292 */
4293 CURSOR evg_rent_strms_pend_csr (p_pol_id IN NUMBER)
4294 IS
4295 -- to remove all the rent strms if the contract turns to EVERGREEN--
4296 SELECT poc.id
4297 FROM okl_pool_contents poc,
4298 okl_pools pol,
4299 okl_strm_type_b sty,
4300 okc_k_headers_b CHR
4301 WHERE pol.id = p_pol_id
4302 AND pol.id = poc.pol_id
4303 AND poc.sty_id = sty.id
4304 AND sty.STREAM_TYPE_SUBCLASS ='RENT'
4305 AND poc.KHR_ID = CHR.id
4306 AND CHR.sts_code ='EVERGREEN'
4307 AND poc.status_code = G_POC_STS_PENDING ;
4308 --fmiao 21-OCT-2005 bug 4775555 --
4309
4310 CURSOR l_okl_invalid_khr_pend_csr(p_pol_id IN NUMBER)
4311 IS
4312 -- to remove all contents pointing to invalid contracts
4313 SELECT poc.id
4314 FROM okl_pool_contents poc,
4315 okc_k_headers_b chrb,
4316 okl_k_headers khrb
4317 WHERE poc.pol_id = p_pol_id
4318 AND poc.khr_id = chrb.id
4319 AND poc.khr_id = khrb.id
4320 -- cklee 04/10/2003 never reconcile historical data
4321 AND poc.status_code IN (G_POC_STS_PENDING)
4322 -- cklee 04/10/2003 never reconcile historical data
4323 AND (chrb.sts_code NOT IN ('BOOKED','EVERGREEN') OR khrb.assignable_yn <> 'Y');
4324
4325 CURSOR l_okl_delinq_khr_pend_csr(p_pol_id IN NUMBER)
4326 IS
4327 -- to remove all contents pointing to delinquent contracts
4328 SELECT poc.id
4329 FROM okl_pool_contents poc,
4330 iex_case_objects ico,
4331 iex_delinquencies_all del
4332 WHERE poc.pol_id = p_pol_id
4333 AND poc.khr_id = ico.object_id
4334 AND ico. cas_id = del.case_id
4335 -- cklee 04/10/2003 never reconcile historical data
4336 AND poc.status_code IN (G_POC_STS_PENDING)
4337 -- cklee 04/10/2003 never reconcile historical data
4338 AND del.status = 'DELINQUENT';
4339
4340 CURSOR l_okl_rev_khr_pend_csr(p_pol_id IN NUMBER)
4341 IS
4342 -- and to remove all contents pointing to contracts under modification
4343 SELECT poc.id
4344 FROM okl_pool_contents poc,
4345 okc_k_headers_b chrb
4346 WHERE poc.pol_id = p_pol_id
4347 AND poc.khr_id = chrb.id
4348 -- cklee 04/10/2003 never reconcile historical data
4349 AND poc.status_code IN (G_POC_STS_PENDING)
4350 -- cklee 04/10/2003 never reconcile historical data
4351 -- AND chrb.sts_code = 'BOOKED'
4352 AND EXISTS -- revision contract: rebook, split contract, reverse
4353 (SELECT '1'
4354 FROM okl_trx_contracts trxb
4355 WHERE trxb.tcn_type IN ('TRBK','SPLC','RVS')
4356 -- AND trxb.tsu_code = 'ENTERED'
4357 AND trxb.tsu_code NOT IN ('PROCESSED', 'ERROR','CANCELED') -- condition changes 01/13/2003 cklee
4358 AND trxb.khr_id = poc.khr_id
4359 )
4360 AND EXISTS -- split assets, split assets components
4361 (SELECT '1'
4362 FROM okl_txd_assets_v tdas,
4363 okl_txl_assets_b talb,
4364 okc_k_lines_b cleb
4365 WHERE cleb.id = talb.kle_id
4366 AND talb.ID = tdas.TAL_ID
4367 AND talb.TAL_TYPE = 'ALI'
4368 AND cleb.cle_id = poc.kle_id -- top line id
4369 AND talb.dnz_khr_id = poc.khr_id
4370 AND EXISTS (SELECT '1'
4371 FROM okl_trx_assets tas
4372 WHERE tas.id = talb.tas_id
4373 AND tas.tas_type = 'ALI'
4374 AND tas.tsu_code = 'PROCESSED')
4375 );
4376
4377 CURSOR l_okl_invalid_assets_pend_csr(p_pol_id IN NUMBER)
4378 IS
4379 -- and to remove all contents pointing to lease contracts
4380 -- that have atleast one invalid asset
4381 SELECT poc.id
4382 FROM okl_pool_contents poc
4383 WHERE poc.khr_id
4384 IN
4385 (SELECT poc.khr_id
4386 FROM okl_pool_contents poc,
4387 okc_k_lines_b cleb,
4388 okc_statuses_b stsb
4389 WHERE poc.pol_id = p_pol_id
4390 AND poc.kle_id = cleb.id
4391 AND cleb.sts_code = stsb.code
4392 -- cklee 04/10/2003 never reconcile historical data
4393 AND poc.status_code IN (G_POC_STS_PENDING)
4394 -- cklee 04/10/2003 never reconcile historical data
4395 AND stsb.ste_code IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
4396 );
4397
4398 -- v115.37 Fix
4399 CURSOR l_okl_invalid_streams_pend_csr(p_pol_id IN NUMBER)
4400 IS
4401 -- to remove all contents pointing to inactive streams
4402 --- or assets that do not have streams
4403 SELECT poc.id
4404 FROM okl_pool_contents poc
4405 WHERE poc.pol_id = p_pol_id
4406 AND NOT EXISTS
4407 ( SELECT '1'
4408 FROM OKL_POOL_STREAMS_UV pols
4409 WHERE poc.stm_id = pols.stm_id
4410 AND pols.stream_say_code = 'CURR'
4411 AND pols.stream_active_yn = 'Y'
4412 -- mvasudev, 02/06/2004
4413 --AND pols.stream_element_due_date > SYSDATE
4414 )
4415 -- cklee 04/10/2003 never reconcile historical data
4416 AND poc.status_code IN (G_POC_STS_PENDING)
4417 ;
4418 -- cklee 04/10/2003 never reconcile historical data
4419
4420
4421 CURSOR l_okl_update_khr_dts_pend_csr(p_pol_id IN NUMBER)
4422 IS
4423 SELECT poc.id, chrb.end_date
4424 FROM okl_pool_contents poc,
4425 okc_k_headers_b chrb
4426 WHERE poc.pol_id = p_pol_id
4427 AND poc.khr_id = chrb.id
4428 -- cklee 04/10/2003 never reconcile historical data
4429 AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
4430 -- cklee 04/10/2003 never reconcile historical data
4431 -- mvasudev,02/06/2004
4432 AND poc.streams_to_date <> chrb.end_date+1;
4433
4434 CURSOR l_okl_update_dates_pend_csr(p_pol_id IN NUMBER)
4435 IS
4436 SELECT poc.id, poc.stm_id
4437 FROM okl_pool_contents poc
4438 ,okl_streams stmb
4439 WHERE poc.pol_id = p_pol_id
4440 AND poc.stm_id = stmb.id
4441 AND stmb.say_code = 'CURR'
4442 AND stmb.active_yn = 'Y'
4443 -- cklee 04/10/2003 never reconcile historical data
4444 AND poc.status_code IN (G_POC_STS_PENDING)
4445 -- cklee 04/10/2003 never reconcile historical data
4446 AND TRUNC(poc.streams_from_date) <> ( SELECT TRUNC(MIN(selb.stream_element_date))
4447 FROM okl_strm_elements selb
4448 WHERE selb.stm_id = stmb.id
4449 AND selb.date_billed IS NULL
4450 -- mvasudev, 02/06/2004
4451 --AND selb.stream_element_date > SYSDATE
4452 );
4453 /* End changes */
4454
4455
4456 CURSOR l_okl_valid_dates_csr(p_stm_id IN NUMBER)
4457 IS
4458 SELECT MIN(selb.stream_element_date)
4459 FROM okl_strm_elements selb
4460 ,okl_streams stmb
4461 WHERE selb.stm_id = stmb.id
4462 AND selb.date_billed IS NULL
4463 -- mvasudev, 02/06/2004
4464 --AND selb.stream_element_date > SYSDATE
4465 AND stmb.id = p_stm_id
4466 AND stmb.say_code = 'CURR'
4467 AND stmb.active_yn = 'Y';
4468
4469 l_api_name CONSTANT VARCHAR2(30) := 'reconcile_contents';
4470 l_api_version CONSTANT NUMBER := 1.0;
4471 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
4472 lp_pocv_tbl pocv_tbl_type;
4473 lx_pocv_tbl pocv_tbl_type;
4474 lp_polv_rec polv_rec_type;
4475 lx_polv_rec polv_rec_type;
4476
4477 i NUMBER := 1;
4478 l_date DATE;
4479
4480 BEGIN
4481
4482 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
4483 p_pkg_name => G_PKG_NAME,
4484 p_init_msg_list => p_init_msg_list,
4485 l_api_version => l_api_version,
4486 p_api_version => p_api_version,
4487 p_api_type => G_API_TYPE,
4488 x_return_status => l_return_status);
4489 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
4490 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4491 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
4492 RAISE G_EXCEPTION_ERROR;
4493 END IF;
4494
4495 x_reconciled := Okl_Api.G_FALSE;
4496
4497
4498 /*sosharma 26-Dec-2007
4499 Bifurcating further processing based on the value of p_mode
4500 Start Changes
4501 */
4502 IF p_mode IS NULL THEN
4503 --fmiao 21-OCT-2005 bug 4775555 --
4504 i := 1;
4505 FOR evg_rent_strms_rec IN evg_rent_strms_csr(p_pol_id)
4506 LOOP
4507 lp_pocv_tbl(i).id := evg_rent_strms_rec.id;
4508 lp_pocv_tbl(i).pol_id := p_pol_id;
4509 i := i + 1;
4510 END LOOP;
4511
4512 IF lp_pocv_tbl.COUNT > 0 THEN
4513
4514 -- Remove rent streams for EVERGREEN contract from pool--
4515 Okl_Pool_Pvt.delete_pool_contents(
4516 p_api_version => p_api_version,
4517 p_init_msg_list => p_init_msg_list,
4518 x_return_status => l_return_status,
4519 x_msg_count => x_msg_count,
4520 x_msg_data => x_msg_data,
4521 p_pocv_tbl => lp_pocv_tbl);
4522
4523 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4524 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4525 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4526 RAISE Okl_Api.G_EXCEPTION_ERROR;
4527 END IF;
4528
4529 x_reconciled := Okl_Api.G_TRUE;
4530 END IF;
4531 lp_pocv_tbl.DELETE; -- clear
4532 --fmiao 21-OCT-2005 bug 4775555 --
4533
4534 i := 1; -- initialize
4535 FOR l_okl_invalid_khr IN l_okl_invalid_khr_csr(p_pol_id)
4536 LOOP
4537 lp_pocv_tbl(i).id := l_okl_invalid_khr.id;
4538 lp_pocv_tbl(i).pol_id := p_pol_id;
4539 i := i + 1;
4540 END LOOP;
4541
4542 IF lp_pocv_tbl.COUNT > 0 THEN
4543
4544 -- Remove the invalid khrs
4545 Okl_Pool_Pvt.delete_pool_contents(
4546 p_api_version => p_api_version,
4547 p_init_msg_list => p_init_msg_list,
4548 x_return_status => l_return_status,
4549 x_msg_count => x_msg_count,
4550 x_msg_data => x_msg_data,
4551 p_pocv_tbl => lp_pocv_tbl);
4552
4553 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4554 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4555 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4556 RAISE Okl_Api.G_EXCEPTION_ERROR;
4557 END IF;
4558
4559 x_reconciled := Okl_Api.G_TRUE;
4560 END IF;
4561
4562
4563 lp_pocv_tbl.DELETE; -- clear
4564 i := 1; -- initialize
4565 FOR l_okl_delinq_khr IN l_okl_delinq_khr_csr(p_pol_id)
4566 LOOP
4567 lp_pocv_tbl(i).id := l_okl_delinq_khr.id;
4568 lp_pocv_tbl(i).pol_id := p_pol_id;
4569 i := i + 1;
4570 END LOOP;
4571
4572 IF lp_pocv_tbl.COUNT > 0 THEN
4573
4574 -- Remove the delinquent khrs
4575 Okl_Pool_Pvt.delete_pool_contents(
4576 p_api_version => p_api_version,
4577 p_init_msg_list => p_init_msg_list,
4578 x_return_status => l_return_status,
4579 x_msg_count => x_msg_count,
4580 x_msg_data => x_msg_data,
4581 p_pocv_tbl => lp_pocv_tbl);
4582
4583 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4584 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4585 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4586 RAISE Okl_Api.G_EXCEPTION_ERROR;
4587 END IF;
4588
4589 x_reconciled := Okl_Api.G_TRUE;
4590 END IF;
4591
4592
4593
4594
4595
4596 lp_pocv_tbl.DELETE; -- clear
4597
4598 i := 1; -- initialize
4599 FOR l_okl_rev_khr IN l_okl_rev_khr_csr(p_pol_id)
4600 LOOP
4601 lp_pocv_tbl(i).id := l_okl_rev_khr.id;
4602 lp_pocv_tbl(i).pol_id := p_pol_id;
4603 i := i + 1;
4604 END LOOP;
4605
4606 IF lp_pocv_tbl.COUNT > 0 THEN
4607
4608 -- Remove the rev khr rows
4609 Okl_Pool_Pvt.delete_pool_contents(
4610 p_api_version => p_api_version,
4611 p_init_msg_list => p_init_msg_list,
4612 x_return_status => l_return_status,
4613 x_msg_count => x_msg_count,
4614 x_msg_data => x_msg_data,
4615 p_pocv_tbl => lp_pocv_tbl);
4616
4617 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4618 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4619 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4620 RAISE Okl_Api.G_EXCEPTION_ERROR;
4621 END IF;
4622
4623 x_reconciled := Okl_Api.G_TRUE;
4624 END IF;
4625
4626
4627 lp_pocv_tbl.DELETE; -- clear
4628
4629 i := 1; -- initialize
4630 FOR l_okl_invalid_assets IN l_okl_invalid_assets_csr(p_pol_id)
4631 LOOP
4632 lp_pocv_tbl(i).id := l_okl_invalid_assets.id;
4633 lp_pocv_tbl(i).pol_id := p_pol_id;
4634 i := i + 1;
4635 END LOOP;
4636
4637 IF lp_pocv_tbl.COUNT > 0 THEN
4638
4639 -- Remove the rows of invalid_assets
4640 Okl_Pool_Pvt.delete_pool_contents(
4641 p_api_version => p_api_version,
4642 p_init_msg_list => p_init_msg_list,
4643 x_return_status => l_return_status,
4644 x_msg_count => x_msg_count,
4645 x_msg_data => x_msg_data,
4646 p_pocv_tbl => lp_pocv_tbl);
4647
4648 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4649 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4650 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4651 RAISE Okl_Api.G_EXCEPTION_ERROR;
4652 END IF;
4653
4654 x_reconciled := Okl_Api.G_TRUE;
4655 END IF;
4656
4657 lp_pocv_tbl.DELETE; -- clear
4658
4659 i := 1; -- initialize
4660 FOR l_okl_invalid_streams IN l_okl_invalid_streams_csr(p_pol_id)
4661 LOOP
4662 lp_pocv_tbl(i).id := l_okl_invalid_streams.id;
4663 lp_pocv_tbl(i).pol_id := p_pol_id;
4664 i := i + 1;
4665 END LOOP;
4666
4667 IF lp_pocv_tbl.COUNT > 0 THEN
4668
4669 -- Remove the rows of invalid_streams
4670 Okl_Pool_Pvt.delete_pool_contents(
4671 p_api_version => p_api_version,
4672 p_init_msg_list => p_init_msg_list,
4673 x_return_status => l_return_status,
4674 x_msg_count => x_msg_count,
4675 x_msg_data => x_msg_data,
4676 p_pocv_tbl => lp_pocv_tbl);
4677
4678 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4679 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4680 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4681 RAISE Okl_Api.G_EXCEPTION_ERROR;
4682 END IF;
4683 x_reconciled := Okl_Api.G_TRUE;
4684 END IF;
4685
4686 lp_pocv_tbl.DELETE; -- clear the contents to get updatable rows
4687
4688 -- Collect all the records that need to be updated for Contract dates
4689 i := 1;
4690 FOR l_okl_update_khr_dates IN l_okl_update_khr_dates_csr(p_pol_id)
4691 LOOP
4692
4693 lp_pocv_tbl(i).id := l_okl_update_khr_dates.id;
4694
4695 lp_pocv_tbl(i).pol_id := p_pol_id;
4696 -- mvasudev, 02/06/2004
4697 lp_pocv_tbl(i).streams_to_date := l_okl_update_khr_dates.end_date+1;
4698 i := i + 1;
4699 END LOOP;
4700
4701 IF lp_pocv_tbl.COUNT > 0 THEN
4702 -- Update the rows with correct stream dates
4703 Okl_Pool_Pvt.update_pool_contents(
4704 p_api_version => p_api_version,
4705 p_init_msg_list => p_init_msg_list,
4706 x_return_status => l_return_status,
4707 x_msg_count => x_msg_count,
4708 x_msg_data => x_msg_data,
4709 p_pocv_tbl => lp_pocv_tbl,
4710 x_pocv_tbl => lx_pocv_tbl);
4711
4712 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4713 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4714 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4715 RAISE Okl_Api.G_EXCEPTION_ERROR;
4716 END IF;
4717
4718 x_reconciled := Okl_Api.G_TRUE;
4719 END IF;
4720
4721 lp_pocv_tbl.DELETE; -- clear the contents to get new updatable rows
4722
4723 -- Collect all the records that need to be updated for Stream dates
4724 i := 1;
4725 FOR l_okl_update_dates IN l_okl_update_dates_csr(p_pol_id)
4726 LOOP
4727
4728 l_date := NULL;
4729 OPEN l_okl_valid_dates_csr(l_okl_update_dates.stm_id);
4730 FETCH l_okl_valid_dates_csr INTO l_date;
4731 CLOSE l_okl_valid_dates_csr;
4732
4733 lp_pocv_tbl(i).id := l_okl_update_dates.id;
4734 lp_pocv_tbl(i).pol_id := p_pol_id;
4735 lp_pocv_tbl(i).streams_from_date := l_date;
4736
4737 i := i + 1;
4738 END LOOP;
4739 IF lp_pocv_tbl.COUNT > 0 THEN
4740 -- Update the rows with correct stream dates
4741 Okl_Pool_Pvt.update_pool_contents(
4742 p_api_version => p_api_version,
4743 p_init_msg_list => p_init_msg_list,
4744 x_return_status => l_return_status,
4745 x_msg_count => x_msg_count,
4746 x_msg_data => x_msg_data,
4747 p_pocv_tbl => lp_pocv_tbl,
4748 x_pocv_tbl => lx_pocv_tbl);
4749
4750 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4751 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4752 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4753 RAISE Okl_Api.G_EXCEPTION_ERROR;
4754 END IF;
4755
4756 x_reconciled := Okl_Api.G_TRUE;
4757 END IF;
4758
4759 ELSE
4760
4761 i := 1;
4762 FOR evg_rent_strms_rec IN evg_rent_strms_pend_csr(p_pol_id)
4763 LOOP
4764 lp_pocv_tbl(i).id := evg_rent_strms_rec.id;
4765 lp_pocv_tbl(i).pol_id := p_pol_id;
4766 i := i + 1;
4767 END LOOP;
4768
4769 IF lp_pocv_tbl.COUNT > 0 THEN
4770
4771 -- Remove rent streams for EVERGREEN contract from pool--
4772 Okl_Pool_Pvt.delete_pool_contents(
4773 p_api_version => p_api_version,
4774 p_init_msg_list => p_init_msg_list,
4775 x_return_status => l_return_status,
4776 x_msg_count => x_msg_count,
4777 x_msg_data => x_msg_data,
4778 p_pocv_tbl => lp_pocv_tbl);
4779
4780 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4781 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4782 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4783 RAISE Okl_Api.G_EXCEPTION_ERROR;
4784 END IF;
4785
4786 x_reconciled := Okl_Api.G_TRUE;
4787 END IF;
4788 lp_pocv_tbl.DELETE; -- clear
4789 --fmiao 21-OCT-2005 bug 4775555 --
4790
4791 i := 1; -- initialize
4792 FOR l_okl_invalid_khr IN l_okl_invalid_khr_pend_csr(p_pol_id)
4793 LOOP
4794 lp_pocv_tbl(i).id := l_okl_invalid_khr.id;
4795 lp_pocv_tbl(i).pol_id := p_pol_id;
4796 i := i + 1;
4797 END LOOP;
4798
4799 IF lp_pocv_tbl.COUNT > 0 THEN
4800
4801 -- Remove the invalid khrs
4802 Okl_Pool_Pvt.delete_pool_contents(
4803 p_api_version => p_api_version,
4804 p_init_msg_list => p_init_msg_list,
4805 x_return_status => l_return_status,
4806 x_msg_count => x_msg_count,
4807 x_msg_data => x_msg_data,
4808 p_pocv_tbl => lp_pocv_tbl);
4809
4810 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4811 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4812 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4813 RAISE Okl_Api.G_EXCEPTION_ERROR;
4814 END IF;
4815
4816 x_reconciled := Okl_Api.G_TRUE;
4817 END IF;
4818
4819
4820 lp_pocv_tbl.DELETE; -- clear
4821 i := 1; -- initialize
4822 FOR l_okl_delinq_khr IN l_okl_delinq_khr_pend_csr(p_pol_id)
4823 LOOP
4824 lp_pocv_tbl(i).id := l_okl_delinq_khr.id;
4825 lp_pocv_tbl(i).pol_id := p_pol_id;
4826 i := i + 1;
4827 END LOOP;
4828
4829 IF lp_pocv_tbl.COUNT > 0 THEN
4830
4831 -- Remove the delinquent khrs
4832 Okl_Pool_Pvt.delete_pool_contents(
4833 p_api_version => p_api_version,
4834 p_init_msg_list => p_init_msg_list,
4835 x_return_status => l_return_status,
4836 x_msg_count => x_msg_count,
4837 x_msg_data => x_msg_data,
4838 p_pocv_tbl => lp_pocv_tbl);
4839
4840 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4841 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4842 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4843 RAISE Okl_Api.G_EXCEPTION_ERROR;
4844 END IF;
4845
4846 x_reconciled := Okl_Api.G_TRUE;
4847 END IF;
4848
4849
4850
4851
4852
4853 lp_pocv_tbl.DELETE; -- clear
4854
4855 i := 1; -- initialize
4856 FOR l_okl_rev_khr IN l_okl_rev_khr_pend_csr(p_pol_id)
4857 LOOP
4858 lp_pocv_tbl(i).id := l_okl_rev_khr.id;
4859 lp_pocv_tbl(i).pol_id := p_pol_id;
4860 i := i + 1;
4861 END LOOP;
4862
4863 IF lp_pocv_tbl.COUNT > 0 THEN
4864
4865 -- Remove the rev khr rows
4866 Okl_Pool_Pvt.delete_pool_contents(
4867 p_api_version => p_api_version,
4868 p_init_msg_list => p_init_msg_list,
4869 x_return_status => l_return_status,
4870 x_msg_count => x_msg_count,
4871 x_msg_data => x_msg_data,
4872 p_pocv_tbl => lp_pocv_tbl);
4873
4874 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4875 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4876 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4877 RAISE Okl_Api.G_EXCEPTION_ERROR;
4878 END IF;
4879
4880 x_reconciled := Okl_Api.G_TRUE;
4881 END IF;
4882
4883
4884 lp_pocv_tbl.DELETE; -- clear
4885
4886 i := 1; -- initialize
4887 FOR l_okl_invalid_assets IN l_okl_invalid_assets_pend_csr(p_pol_id)
4888 LOOP
4889 lp_pocv_tbl(i).id := l_okl_invalid_assets.id;
4890 lp_pocv_tbl(i).pol_id := p_pol_id;
4891 i := i + 1;
4892 END LOOP;
4893
4894 IF lp_pocv_tbl.COUNT > 0 THEN
4895
4896 -- Remove the rows of invalid_assets
4897 Okl_Pool_Pvt.delete_pool_contents(
4898 p_api_version => p_api_version,
4899 p_init_msg_list => p_init_msg_list,
4900 x_return_status => l_return_status,
4901 x_msg_count => x_msg_count,
4902 x_msg_data => x_msg_data,
4903 p_pocv_tbl => lp_pocv_tbl);
4904
4905 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4906 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4907 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4908 RAISE Okl_Api.G_EXCEPTION_ERROR;
4909 END IF;
4910
4911 x_reconciled := Okl_Api.G_TRUE;
4912 END IF;
4913
4914 lp_pocv_tbl.DELETE; -- clear
4915
4916 i := 1; -- initialize
4917 FOR l_okl_invalid_streams IN l_okl_invalid_streams_pend_csr(p_pol_id)
4918 LOOP
4919 lp_pocv_tbl(i).id := l_okl_invalid_streams.id;
4920 lp_pocv_tbl(i).pol_id := p_pol_id;
4921 i := i + 1;
4922 END LOOP;
4923
4924 IF lp_pocv_tbl.COUNT > 0 THEN
4925
4926 -- Remove the rows of invalid_streams
4927 Okl_Pool_Pvt.delete_pool_contents(
4928 p_api_version => p_api_version,
4929 p_init_msg_list => p_init_msg_list,
4930 x_return_status => l_return_status,
4931 x_msg_count => x_msg_count,
4932 x_msg_data => x_msg_data,
4933 p_pocv_tbl => lp_pocv_tbl);
4934
4935 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4936 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4937 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4938 RAISE Okl_Api.G_EXCEPTION_ERROR;
4939 END IF;
4940 x_reconciled := Okl_Api.G_TRUE;
4941 END IF;
4942
4943 lp_pocv_tbl.DELETE; -- clear the contents to get updatable rows
4944
4945 -- Collect all the records that need to be updated for Contract dates
4946 i := 1;
4947 FOR l_okl_update_khr_dates IN l_okl_update_khr_dts_pend_csr(p_pol_id)
4948 LOOP
4949
4950 lp_pocv_tbl(i).id := l_okl_update_khr_dates.id;
4951
4952 lp_pocv_tbl(i).pol_id := p_pol_id;
4953 -- mvasudev, 02/06/2004
4954 lp_pocv_tbl(i).streams_to_date := l_okl_update_khr_dates.end_date+1;
4955 i := i + 1;
4956 END LOOP;
4957
4958 IF lp_pocv_tbl.COUNT > 0 THEN
4959 -- Update the rows with correct stream dates
4960 Okl_Pool_Pvt.update_pool_contents(
4961 p_api_version => p_api_version,
4962 p_init_msg_list => p_init_msg_list,
4963 x_return_status => l_return_status,
4964 x_msg_count => x_msg_count,
4965 x_msg_data => x_msg_data,
4966 p_pocv_tbl => lp_pocv_tbl,
4967 x_pocv_tbl => lx_pocv_tbl);
4968
4969 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4970 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4971 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4972 RAISE Okl_Api.G_EXCEPTION_ERROR;
4973 END IF;
4974
4975 x_reconciled := Okl_Api.G_TRUE;
4976 END IF;
4977
4978 lp_pocv_tbl.DELETE; -- clear the contents to get new updatable rows
4979
4980 -- Collect all the records that need to be updated for Stream dates
4981 i := 1;
4982 FOR l_okl_update_dates IN l_okl_update_dates_pend_csr(p_pol_id)
4983 LOOP
4984
4985 l_date := NULL;
4986 OPEN l_okl_valid_dates_csr(l_okl_update_dates.stm_id);
4987 FETCH l_okl_valid_dates_csr INTO l_date;
4988 CLOSE l_okl_valid_dates_csr;
4989
4990 lp_pocv_tbl(i).id := l_okl_update_dates.id;
4991 lp_pocv_tbl(i).pol_id := p_pol_id;
4992 lp_pocv_tbl(i).streams_from_date := l_date;
4993
4994 i := i + 1;
4995 END LOOP;
4996 IF lp_pocv_tbl.COUNT > 0 THEN
4997 -- Update the rows with correct stream dates
4998 Okl_Pool_Pvt.update_pool_contents(
4999 p_api_version => p_api_version,
5000 p_init_msg_list => p_init_msg_list,
5001 x_return_status => l_return_status,
5002 x_msg_count => x_msg_count,
5003 x_msg_data => x_msg_data,
5004 p_pocv_tbl => lp_pocv_tbl,
5005 x_pocv_tbl => lx_pocv_tbl);
5006
5007 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
5008 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
5009 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
5010 RAISE Okl_Api.G_EXCEPTION_ERROR;
5011 END IF;
5012
5013 x_reconciled := Okl_Api.G_TRUE;
5014 END IF;
5015 END IF;
5016 /*sosharma end changes*/
5017
5018 -- update date_last_reconciled
5019 lp_polv_rec.id := p_pol_id;
5020 lp_polv_rec.date_last_reconciled := SYSDATE;
5021 Okl_Pol_Pvt.update_row(
5022 p_api_version => p_api_version,
5023 p_init_msg_list => p_init_msg_list,
5024 x_return_status => l_return_status,
5025 x_msg_count => x_msg_count,
5026 x_msg_data => x_msg_data,
5027 p_polv_rec => lp_polv_rec,
5028 x_polv_rec => lx_polv_rec);
5029
5030 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
5031 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
5032
5033 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
5034 RAISE Okl_Api.G_EXCEPTION_ERROR;
5035 END IF;
5036
5037 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
5038 x_msg_data => x_msg_data);
5039
5040 x_return_status := l_return_status;
5041
5042 EXCEPTION
5043 WHEN G_EXCEPTION_ERROR THEN
5044
5045 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
5046 p_pkg_name => G_PKG_NAME,
5047 p_exc_name => G_EXC_NAME_ERROR,
5048 x_msg_count => x_msg_count,
5049 x_msg_data => x_msg_data,
5050 p_api_type => G_API_TYPE);
5051 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
5052
5053 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
5054 p_pkg_name => G_PKG_NAME,
5055 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
5056 x_msg_count => x_msg_count,
5057 x_msg_data => x_msg_data,
5058 p_api_type => G_API_TYPE);
5059 WHEN OTHERS THEN
5060
5061 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
5062 p_pkg_name => G_PKG_NAME,
5063 p_exc_name => G_EXC_NAME_OTHERS,
5064 x_msg_count => x_msg_count,
5065 x_msg_data => x_msg_data,
5066 p_api_type => G_API_TYPE);
5067
5068 END reconcile_contents;
5069
5070 ----------------------------------------------------------------------------------
5071 -- Start of comments
5072 --
5073
5074 -- Procedure Name : update_pool_status_active
5075 -- Description : updates a pool header, and contents' status.
5076 -- Business Rules :
5077 -- Parameters :
5078 -- Version : 1.0
5079 -- End of comments
5080 ----------------------------------------------------------------------------------
5081 PROCEDURE update_pool_status_active(
5082 p_api_version IN NUMBER
5083 ,p_init_msg_list IN VARCHAR2
5084 ,x_return_status OUT NOCOPY VARCHAR2
5085 ,x_msg_count OUT NOCOPY NUMBER
5086 ,x_msg_data OUT NOCOPY VARCHAR2
5087 ,p_pol_id IN okl_pools.id%TYPE)
5088 IS
5089
5090
5091 l_api_name CONSTANT VARCHAR2(30) := 'update_pool_status_active';
5092 l_api_version CONSTANT NUMBER := 1.0;
5093
5094 BEGIN
5095 -- Set API savepoint
5096 SAVEPOINT update_pool_status_active_PVT;
5097
5098 -- Check for call compatibility
5099 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
5100 p_api_version,
5101 l_api_name,
5102 G_PKG_NAME ))
5103 THEN
5104 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
5105 END IF;
5106
5107 -- Initialize message list if requested
5108 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
5109 Fnd_Msg_Pub.initialize;
5110 END IF;
5111
5112 -- Initialize API status to success
5113 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
5114
5115
5116 /*** Begin API body ****************************************************/
5117
5118 update_pool_status(
5119 p_api_version => p_api_version,
5120 p_init_msg_list => p_init_msg_list,
5121 x_return_status => x_return_status,
5122 x_msg_count => x_msg_count,
5123 x_msg_data => x_msg_data,
5124 p_pool_status => G_POL_STS_ACTIVE,
5125 p_pol_id => p_pol_id);
5126
5127 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
5128 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
5129 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
5130 RAISE Okl_Api.G_EXCEPTION_ERROR;
5131 END IF;
5132
5133 /*** End API body ******************************************************/
5134
5135 -- Get message count and if count is 1, get message info
5136 Fnd_Msg_Pub.Count_And_Get
5137 (p_count => x_msg_count,
5138 p_data => x_msg_data);
5139
5140 EXCEPTION
5141 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
5142 ROLLBACK TO update_pool_status_active_PVT;
5143 x_return_status := Okl_Api.G_RET_STS_ERROR;
5144 Fnd_Msg_Pub.Count_And_Get
5145 (p_count => x_msg_count,
5146 p_data => x_msg_data);
5147
5148 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
5149
5150 ROLLBACK TO update_pool_status_active_PVT;
5151 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
5152 Fnd_Msg_Pub.Count_And_Get
5153 (p_count => x_msg_count,
5154 p_data => x_msg_data);
5155
5156 WHEN OTHERS THEN
5157 ROLLBACK TO update_pool_status_active_PVT;
5158 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
5159 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
5160 p_msg_name => G_UNEXPECTED_ERROR,
5161 p_token1 => G_SQLCODE_TOKEN,
5162 p_token1_value => SQLCODE,
5163 p_token2 => G_SQLERRM_TOKEN,
5164 p_token2_value => SQLERRM);
5165 Fnd_Msg_Pub.Count_And_Get
5166 (p_count => x_msg_count,
5167 p_data => x_msg_data);
5168
5169 END update_pool_status_active;
5170
5171
5172 ----------------------------------------------------------------------------------
5173 -- Start of comments
5174 --
5175 -- Procedure Name : update_pool_status_expired
5176 -- Description : updates a pool header, and contents' status.
5177 -- Business Rules :
5178 -- Parameters :
5179 -- Version : 1.0
5180 -- End of comments
5181 ----------------------------------------------------------------------------------
5182 PROCEDURE update_pool_status_expired(
5183 p_api_version IN NUMBER
5184 ,p_init_msg_list IN VARCHAR2
5185 ,x_return_status OUT NOCOPY VARCHAR2
5186 ,x_msg_count OUT NOCOPY NUMBER
5187 ,x_msg_data OUT NOCOPY VARCHAR2
5188 ,p_pol_id IN okl_pools.id%TYPE)
5189 IS
5190 l_api_name CONSTANT VARCHAR2(30) := 'update_pool_status_expired';
5191 l_api_version CONSTANT NUMBER := 1.0;
5192
5193 BEGIN
5194 -- Set API savepoint
5195 SAVEPOINT update_pool_status_expired_PVT;
5196
5197 -- Check for call compatibility
5198 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
5199 p_api_version,
5200
5201 l_api_name,
5202 G_PKG_NAME ))
5203 THEN
5204
5205 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
5206 END IF;
5207
5208 -- Initialize message list if requested
5209 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
5210 Fnd_Msg_Pub.initialize;
5211 END IF;
5212
5213 -- Initialize API status to success
5214 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
5215
5216
5217 /*** Begin API body ****************************************************/
5218
5219 update_pool_status(
5220 p_api_version => p_api_version,
5221 p_init_msg_list => p_init_msg_list,
5222 x_return_status => x_return_status,
5223 x_msg_count => x_msg_count,
5224 x_msg_data => x_msg_data,
5225 p_pool_status => G_POL_STS_EXPIRED,
5226 p_pol_id => p_pol_id);
5227
5228 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
5229 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
5230 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
5231 RAISE Okl_Api.G_EXCEPTION_ERROR;
5232 END IF;
5233
5234 /*** End API body ******************************************************/
5235
5236 -- Get message count and if count is 1, get message info
5237 Fnd_Msg_Pub.Count_And_Get
5238 (p_count => x_msg_count,
5239 p_data => x_msg_data);
5240
5241 EXCEPTION
5242 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
5243 ROLLBACK TO update_pool_status_expired_PVT;
5244 x_return_status := Okl_Api.G_RET_STS_ERROR;
5245 Fnd_Msg_Pub.Count_And_Get
5246 (p_count => x_msg_count,
5247 p_data => x_msg_data);
5248
5249 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
5250 ROLLBACK TO update_pool_status_expired_PVT;
5251 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
5252 Fnd_Msg_Pub.Count_And_Get
5253 (p_count => x_msg_count,
5254 p_data => x_msg_data);
5255
5256 WHEN OTHERS THEN
5257 ROLLBACK TO update_pool_status_expired_PVT;
5258 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
5259 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
5260 p_msg_name => G_UNEXPECTED_ERROR,
5261 p_token1 => G_SQLCODE_TOKEN,
5262 p_token1_value => SQLCODE,
5263
5264 p_token2 => G_SQLERRM_TOKEN,
5265 p_token2_value => SQLERRM);
5266 Fnd_Msg_Pub.Count_And_Get
5267 (p_count => x_msg_count,
5268 p_data => x_msg_data);
5269
5270 END update_pool_status_expired;
5271
5272 ----------------------------------------------------------------------------------
5273 -- Start of comments
5274 -- mvasudev
5275 -- Procedure Name : get_total_stream_amount
5276 -- Description : Gets the Total Stream Amount for a given POC using the stm_id
5277 -- regardless of its status
5278 -- Business Rules :
5279 -- Parameters :
5280 -- Version : 1.0
5281 -- End of comments
5282 ----------------------------------------------------------------------------------
5283 PROCEDURE get_total_stream_amount(
5284 p_api_version IN NUMBER
5285 ,p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE
5286 ,x_return_status OUT NOCOPY VARCHAR2
5287 ,x_msg_count OUT NOCOPY NUMBER
5288 ,x_msg_data OUT NOCOPY VARCHAR2
5289 ,p_poc_id IN okl_pool_contents.id%TYPE
5290 ,p_stm_id IN okl_streams.id%TYPE
5291 ,x_amount OUT NOCOPY NUMBER
5292 )
5293 IS
5294
5295 l_api_name CONSTANT VARCHAR2(30) := 'get_total_stream_amount';
5296 l_api_version CONSTANT NUMBER := 1.0;
5297 i NUMBER;
5298 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
5299
5300 CURSOR l_okl_poc_stm_csr
5301 IS
5302 SELECT NVL(SUM(NVL(selb.amount,0)),0) amount
5303 FROM okl_streams stmb
5304 ,okl_strm_elements selb
5305 ,okl_pool_contents pocb
5306 ,okl_strm_type_v styv
5307 ,okc_k_headers_b chrb
5308 WHERE pocb.stm_id = stmb.id
5309 AND stmb.id = selb.stm_id
5310 AND pocb.id = p_poc_id
5311 -- Bug#3520846,mvasudev, 3/22/2004
5312 AND pocb.status_code = 'ACTIVE'
5313 AND selb.date_billed IS NULL
5314 /*
5315 ankushar --Bug 6594724: Unable to terminate Investor Agreement with Residual Streams
5316 Start changes
5317 */
5318 AND stmb.sty_id = styv.id
5319 AND pocb.khr_id = chrb.id
5320 AND ( selb.stream_element_date > SYSDATE OR
5321 ( styv.stream_type_subclass = 'RESIDUAL'
5322 and chrb.STS_CODE IN ('TERMINATED','EXPIRED')
5323 )
5324 )
5325 /* ankushar Bug 6594724
5326 End Changes
5327 */
5328
5329 -- end, mvasudev
5330 AND selb.stream_element_date
5331 BETWEEN pocb.streams_from_date AND NVL(pocb.streams_to_date,G_FINAL_DATE)
5332 ;
5333
5334 CURSOR l_okl_poc_csr
5335 IS
5336 SELECT NVL(SUM(NVL(selb.amount,0)),0) amount
5337 FROM okl_streams stmb
5338 ,okl_strm_elements selb
5339 ,okl_pool_contents pocb
5340 WHERE pocb.stm_id = stmb.id
5341 AND stmb.id = selb.stm_id
5342 AND pocb.id = p_poc_id
5343 AND selb.stream_element_date
5344 BETWEEN pocb.streams_from_date AND NVL(pocb.streams_to_date,G_FINAL_DATE)
5345 ;
5346
5347 BEGIN
5348
5349 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
5350 p_pkg_name => G_PKG_NAME,
5351 p_init_msg_list => p_init_msg_list,
5352 l_api_version => l_api_version,
5353 p_api_version => p_api_version,
5354 p_api_type => G_API_TYPE,
5355 x_return_status => l_return_status);
5356 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
5357 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
5358 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
5359 RAISE G_EXCEPTION_ERROR;
5360 END IF;
5361
5362 x_amount := 0;
5363
5364 -- mvasudev, 04/01/2004
5365 IF p_stm_id IS NOT NULL THEN
5366 FOR l_okl_poc_stm_rec IN l_okl_poc_stm_csr
5367 LOOP
5368 x_amount := x_amount + l_okl_poc_stm_rec.amount;
5369 END LOOP;
5370 ELSE
5371 FOR l_okl_poc_rec IN l_okl_poc_csr
5372 LOOP
5373 x_amount := x_amount + l_okl_poc_rec.amount;
5374 END LOOP;
5375 END IF;
5376
5377 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count
5378 ,x_msg_data => x_msg_data);
5379
5380 x_return_status := l_return_status;
5381
5382 EXCEPTION
5383 WHEN G_EXCEPTION_ERROR THEN
5384
5385 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
5386 p_pkg_name => G_PKG_NAME,
5387 p_exc_name => G_EXC_NAME_ERROR,
5388 x_msg_count => x_msg_count,
5389 x_msg_data => x_msg_data,
5390 p_api_type => G_API_TYPE);
5391 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
5392
5393 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
5394 p_pkg_name => G_PKG_NAME,
5395 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
5396 x_msg_count => x_msg_count,
5397 x_msg_data => x_msg_data,
5398 p_api_type => G_API_TYPE);
5399 WHEN OTHERS THEN
5400
5401 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
5402 p_pkg_name => G_PKG_NAME,
5403 p_exc_name => G_EXC_NAME_OTHERS,
5404 x_msg_count => x_msg_count,
5405 x_msg_data => x_msg_data,
5406 p_api_type => G_API_TYPE);
5407
5408 END get_total_stream_amount;
5409
5410 /* ankushar 26-JUL-2007
5411 Bug#6000531 To publish OKL_POOL_PUB added a new api validate_pool
5412 start changes
5413 */
5414 PROCEDURE validate_pool(
5415 p_api_version IN NUMBER
5416 ,p_init_msg_list IN VARCHAR2
5417 ,p_api_name IN VARCHAR2
5418 ,p_polv_rec IN polv_rec_type
5419 ,p_action IN VARCHAR2
5420 ,x_return_status OUT NOCOPY VARCHAR2
5421 ,x_msg_count OUT NOCOPY NUMBER
5422 ,x_msg_data OUT NOCOPY VARCHAR2
5423
5424 ) IS
5425 l_api_name CONSTANT VARCHAR2(30) := 'validate_pool';
5426 l_api_version CONSTANT NUMBER := 1.0;
5427 i NUMBER;
5428 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
5429 l_action VARCHAR2(40) := p_api_name;
5430 l_contract_number OKL_K_HEADERS_FULL_V.CONTRACT_NUMBER%type;
5431 l_polv_rec polv_rec_type := p_polv_rec;
5432
5433 CURSOR l_okl_pol_status_csr(p_pol_id IN NUMBER)
5434 IS
5435 SELECT status_code
5436 FROM okl_pools
5437 WHERE id = p_pol_id;
5438
5439 -- Cursor For OKL_CURRENCIES;
5440 CURSOR okl_fnd_curr_csr (p_code IN OKL_POOLS.currency_code%TYPE) IS
5441 SELECT '1'
5442 FROM FND_CURRENCIES_VL
5443 WHERE FND_CURRENCIES_VL.currency_code = currency_code;
5444
5445 l_dummy VARCHAR2(1) := '?';
5446 l_row_not_found BOOLEAN := FALSE;
5447 BEGIN
5448
5449 -- Initialize API status to success
5450 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
5451
5452 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
5453 p_pkg_name => G_PKG_NAME,
5454 p_init_msg_list => p_init_msg_list,
5455 l_api_version => l_api_version,
5456 p_api_version => p_api_version,
5457 p_api_type => G_API_TYPE,
5458 x_return_status => l_return_status);
5459 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
5460 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5461 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
5462 RAISE OKL_API.G_EXCEPTION_ERROR;
5463 END IF;
5464
5465 -- Initialize message list if requested
5466 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
5467 Fnd_Msg_Pub.initialize;
5468 END IF;
5469
5470 --update validation for pool id when the pool is in active or new status
5471 IF l_action = 'update_pool' THEN
5472 IF ((p_polv_rec.id is null) OR (p_polv_rec.id=OKL_API.G_MISS_NUM )) THEN
5473 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
5474 RAISE OKL_API.G_EXCEPTION_ERROR;
5475 END IF;
5476 END IF;
5477
5478 --following actions are permitted only when pool status is NEW
5479 IF l_action in ('add_pool_contents','cleanup_pool_contents') THEN
5480 IF ((p_polv_rec.id is null) OR (p_polv_rec.id=OKL_API.G_MISS_NUM )) THEN
5481 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
5482 RAISE OKL_API.G_EXCEPTION_ERROR;
5483 ELSE
5484 FOR l_okl_pol_status_rec IN l_okl_pol_status_csr(p_polv_rec.id)
5485 LOOP
5486
5487 IF l_okl_pol_status_rec.status_code <> Okl_Pool_Pvt.G_POL_STS_NEW THEN
5488 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME, p_msg_name => 'OKL_POOL_NO_MODIFY');
5489 RAISE OKL_API.G_EXCEPTION_ERROR;
5490 END IF;
5491 END LOOP;
5492 End if;
5493 END IF;
5494
5495 IF l_action = 'add_pool_contents' THEN
5496 -- validte whether the currency code entered is correct
5497 -- currency code must be entered by user while adding
5498 -- the pool contents
5499
5500 IF (p_polv_rec.currency_code = OKL_API.G_MISS_CHAR OR
5501 p_polv_rec.currency_code IS NULL)
5502 THEN
5503 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
5504 p_msg_name => 'OKL_REQUIRED_VALUE',
5505 p_token1 => 'COL_NAME',
5506 p_token1_value => 'currency_code');
5507 RAISE G_EXCEPTION_ERROR;
5508 END IF;
5509
5510 OPEN okl_fnd_curr_csr(p_polv_rec.currency_code);
5511
5512 FETCH okl_fnd_curr_csr INTO l_dummy;
5513 l_row_not_found := okl_fnd_curr_csr%NOTFOUND;
5514 CLOSE okl_fnd_curr_csr;
5515
5516 IF l_row_not_found THEN
5517 OKL_API.set_message(G_APP_NAME,
5518 OKL_API.G_INVALID_VALUE,
5519 OKL_API.G_COL_NAME_TOKEN,
5520 'currency_code');
5521 RAISE G_EXCEPTION_ERROR;
5522 END IF;
5523
5524 END IF; --l_action
5525
5526 -- Get message count and if count is 1, get message info
5527 Fnd_Msg_Pub.Count_And_Get
5528 (p_count => x_msg_count,
5529 p_data => x_msg_data);
5530
5531 EXCEPTION
5532 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
5533 x_return_status := Okl_Api.G_RET_STS_ERROR;
5534 Fnd_Msg_Pub.Count_And_Get
5535 (p_count => x_msg_count,
5536 p_data => x_msg_data);
5537
5538 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
5539 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
5540 Fnd_Msg_Pub.Count_And_Get
5541 (p_count => x_msg_count,
5542 p_data => x_msg_data);
5543
5544 WHEN OTHERS THEN
5545 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
5546
5547 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
5548 p_msg_name => G_UNEXPECTED_ERROR,
5549 p_token1 => G_SQLCODE_TOKEN,
5550 p_token1_value => SQLCODE,
5551 p_token2 => G_SQLERRM_TOKEN,
5552 p_token2_value => SQLERRM);
5553 Fnd_Msg_Pub.Count_And_Get
5554 (p_count => x_msg_count,
5555 p_data => x_msg_data);
5556
5557 END validate_pool;
5558 /* ankushar end changes 26-Jul-2007*/
5559
5560 END Okl_Pool_Pvt;