[Home] [Help]
PACKAGE BODY: APPS.OKL_POOL_PVT
Source
1 PACKAGE BODY Okl_Pool_Pvt AS
2 /* $Header: OKLRSZPB.pls 120.30.12020000.2 2012/08/07 13:36:26 rpillay ship $ */
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 ,okl_strm_type_b strm -- added for bug 13997160 :vsgandhi
3089 WHERE
3090 -- pre-req
3091 pol.currency_code = p_currency_code
3092 AND pol.sts_code IN ('BOOKED','EVERGREEN')
3093 AND pol.assignable_yn = 'Y'
3094 AND pol.stream_element_date_billed IS NULL
3095 AND pol.stream_element_amount <> 0 -- vsgandhi : Bug 13997160
3096 -- to fetch stream_type_subclass name
3097 AND lkup.lookup_type = 'OKL_STREAM_TYPE_SUBCLASS'
3098 AND lkup.lookup_code = pol.stream_type_subclass
3099 -- customer
3100 AND NVL(pol.cust_object1_id1,G_DEFAULT_NUM) = NVL(p_cust_object1_id1, NVL(pol.cust_object1_id1,G_DEFAULT_NUM))
3101 AND NVL(pol.sic_code,G_DEFAULT_CHAR) = NVL(p_sic_code, NVL(pol.sic_code,G_DEFAULT_CHAR))
3102 -- contract number
3103 AND NVL(pol.dnz_chr_id,G_DEFAULT_NUM) = NVL(p_khr_id, NVL(pol.dnz_chr_id,G_DEFAULT_NUM))
3104 AND NVL(pol.pre_tax_yield,G_DEFAULT_NUM) BETWEEN NVL(p_pre_tax_yield_from, NVL(pol.pre_tax_yield,G_DEFAULT_NUM))
3105 AND NVL(p_pre_tax_yield_to, NVL(pol.pre_tax_yield,G_DEFAULT_NUM))
3106 AND NVL(pol.book_classification,G_DEFAULT_CHAR) = NVL(p_book_classification, NVL(pol.book_classification,G_DEFAULT_CHAR))
3107 AND NVL(pol.pdt_id,G_DEFAULT_NUM) = NVL(p_pdt_id, NVL(pol.pdt_id,G_DEFAULT_NUM))
3108 AND NVL(pol.start_date, G_DEFAULT_DATE)
3109 BETWEEN NVL(p_start_date_from, NVL(pol.start_date, G_DEFAULT_DATE))
3110 AND NVL(p_start_date_to, NVL(pol.start_date, G_DEFAULT_DATE))
3111 AND NVL(pol.end_date,G_DEFAULT_DATE)
3112 BETWEEN NVL(p_end_date_from, NVL(pol.end_date,G_DEFAULT_DATE))
3113 AND NVL(p_end_date_to, NVL(pol.end_date,G_DEFAULT_DATE))
3114 AND NVL(pol.tax_owner,G_DEFAULT_CHAR) = NVL(p_tax_owner, NVL(pol.tax_owner,G_DEFAULT_CHAR))
3115 -- streams
3116 AND NVL(pol.stream_element_due_date, G_DEFAULT_DATE)
3117 BETWEEN NVL(p_stream_element_from_date,
3118 NVL(pol.stream_element_due_date, G_DEFAULT_DATE))
3119 AND NVL(p_stream_element_to_date,NVL(pol.stream_element_due_date, G_DEFAULT_DATE))
3120 --Bug 674000 ssdeshpa start
3121 AND pol.stream_type_subclass IN ('RENT', 'RESIDUAL', 'LOAN_PAYMENT')
3122 --Bug 674000 ssdeshpa end
3123 AND NVL(pol.stream_type_subclass,G_DEFAULT_CHAR) = NVL(p_stream_type_subclass, NVL(pol.stream_type_subclass,G_DEFAULT_CHAR))
3124 AND pol.stream_say_code = 'CURR'
3125 AND pol.stream_active_yn = 'Y'
3126 -- mvasudev, 02/06/2004
3127 --AND pol.stream_element_due_date > SYSDATE
3128 -- multi-org
3129 --Bug # 6691554 Changes for Cust Credit Classification Lov Start
3130 AND pol.cust_object1_id1 = hcp.party_id(+)
3131 AND hcp.cust_account_id(+) = -1
3132 AND hcp.site_use_id(+) IS NULL
3133 AND NVL(hcp.credit_classification(+),G_DEFAULT_CHAR) = NVL(p_cust_crd_clf_code, NVL(hcp.credit_classification(+),G_DEFAULT_CHAR))
3134 --Bug # 6691554 Changes for Cust Credit Classification Lov End
3135 --Bug # 13997160 only billable streams can be added to a pool : vsgandhi
3136 AND strm.id = pol.sty_id
3137 AND strm.billable_yn = 'Y'
3138 --Bug # 13997160 : vsgandhi
3139 GROUP BY
3140 pol.dnz_chr_id
3141 ,pol.kle_id
3142 ,pol.sty_id
3143 ,pol.stream_type_code
3144 ,pol.stream_type_subclass
3145 ,pol.end_date
3146 -- mvasudev, stm_id changes
3147 ,pol.stm_id
3148 ,pol.contract_number
3149 ,pol.lessee
3150 ,lkup.meaning
3151 ,pol.asset_number
3152 ,hcp.credit_classification;
3153
3154 -- Cursor to discard pocs that already exist
3155 /* ankushar 03-JAN-2008
3156 Bug#6726555 Modified cursor to look at the stream element level to achive partially bought back
3157 contracts to be associcated to another Pool.
3158 start changes
3159 */
3160 CURSOR l_okl_dup_pocs_csr(p_stream_type_subclass IN VARCHAR2,p_stm_id IN NUMBER)
3161 IS
3162 SELECT '1'
3163 FROM okl_pool_contents pol_cnts,
3164 okl_strm_type_b styb,
3165 okl_strm_elements sel
3166 WHERE pol_cnts.pol_id = pol_id
3167 AND pol_cnts.stm_id = p_stm_id
3168 AND styb.stream_type_subclass = p_stream_type_subclass
3169 AND pol_cnts.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE,G_POC_STS_PENDING)
3170 AND sel.stm_id = pol_cnts.stm_id
3171 AND sel.date_billed IS NULL
3172 GROUP BY sel.stm_id
3173 HAVING MAX(sel.STREAM_ELEMENT_DATE) <= MAX(nvl(pol_cnts.STREAMS_TO_DATE,pol_cnts.STREAMS_FROM_DATE));
3174 /* ankushar 03-JAN-2008 Bug# 6726555
3175 end Changes
3176 */
3177
3178 /*Added by kthiruva to check whether the contract streams were bought back */
3179 CURSOR l_buyback_yes_csr(p_stm_id IN NUMBER)
3180 IS
3181 SELECT '1'
3182 FROM okl_pool_contents pol_cnts
3183 WHERE pol_cnts.pol_id = pol_id
3184 AND pol_cnts.stm_id = p_stm_id
3185 AND pol_cnts.status_code = 'INACTIVE';
3186
3187 /*Added by ankushar to fetch the maximum stream to_date to pick up the remaining streams after this date */
3188 CURSOR l_max_to_date_csr(p_stm_id IN NUMBER)
3189 IS
3190 SELECT MAX(nvl(pol_cnts.STREAMS_TO_DATE,pol_cnts.STREAMS_FROM_DATE)) eff_from_date
3191 FROM okl_pool_contents pol_cnts
3192 WHERE pol_cnts.pol_id = pol_id
3193 AND pol_cnts.stm_id = p_stm_id
3194 AND pol_cnts.status_code = 'ACTIVE';
3195
3196 l_buyback_yn BOOLEAN := false;
3197 l_eff_from_date DATE;
3198 -- Variable Interest Rate
3199 -- fmiao bug 5160080--
3200 /*
3201 CURSOR l_okl_poc_vari_csr(p_dnz_chr_id IN NUMBER)
3202 IS
3203 SELECT '1'
3204 FROM okc_rule_groups_b rgp
3205 ,okc_rules_b rg
3206 WHERE rgp.id = rg.rgp_id
3207 AND rgp.rgd_code = 'LAIIND'
3208 AND rg.rule_information_category= 'LAINTP'
3209 AND rg.rule_information1 = 'Y'
3210 AND rgp.dnz_chr_id = p_dnz_chr_id;
3211 */
3212 SUBTYPE pdt_parameters_rec_type IS Okl_Setupproducts_Pvt.pdt_parameters_rec_type;
3213 l_pdt_parameters_rec pdt_parameters_rec_type;
3214 --fmiao 5160080 end-- fmiao bug 5160080 end --
3215
3216 -- revision contract: rebook, split contract, reverse
3217 CURSOR l_okl_poc_rev_csr(p_dnz_chr_id IN NUMBER)
3218 IS
3219 SELECT '1'
3220 FROM okl_trx_contracts trxc
3221 WHERE trxc.tcn_type IN ('TRBK','SPLC','RVS')
3222 AND trxc.tsu_code NOT IN ('PROCESSED', 'ERROR','CANCELED')
3223 AND trxc.khr_id = p_dnz_chr_id;
3224
3225 -- split assets, split assets components
3226 CURSOR l_okl_poc_splits_csr(p_dnz_chr_id IN NUMBER,p_kle_id IN NUMBER)
3227 IS
3228 SELECT '1'
3229 FROM okl_txd_assets_v tdas,
3230 okl_txl_assets_b tal,
3231 okc_k_lines_b cle
3232 WHERE cle.id = tal.kle_id
3233 AND tal.id = tdas.tal_id
3234 AND tal.tal_type = 'ALI'
3235 -- link from okl_pool_srch_v pol
3236 AND cle.cle_id = p_kle_id -- top line id
3237 AND tal.dnz_khr_id = p_dnz_chr_id
3238 -- link from okl_pool_srch_v pol
3239 AND EXISTS (SELECT '1'
3240 FROM okl_trx_assets tas
3241 WHERE tas.id = tal.tas_id
3242 AND tas.tas_type = 'ALI'
3243 AND tas.tsu_code NOT IN ('PROCESSED','CANCELED')
3244 );
3245
3246 -- contract is under deliquent status
3247 CURSOR l_okl_poc_delinq_csr(p_dnz_chr_id IN NUMBER)
3248 IS
3249 SELECT '1'
3250 FROM iex_case_objects ico,
3251 iex_delinquencies_all del
3252 WHERE ico. cas_id = del.case_id
3253 AND del.status ='DELINQUENT'
3254 AND ico.object_id = p_dnz_chr_id;
3255
3256 -- contract line has been terminated
3257 CURSOR l_okl_poc_kle_csr(p_kle_id IN NUMBER)
3258 IS
3259 SELECT '1'
3260 FROM okc_k_lines_b cle,
3261 okc_statuses_b sts
3262 WHERE sts.code = cle.sts_code
3263 AND sts.ste_code IN ('HOLD','EXPIRED','TERMINATED','CANCELLED')
3264 AND cle.id = p_kle_id;
3265
3266 -- cursor to discard Legal Entity Mismatch
3267
3268 --if Legal Entity Id for Pool and Contract is not same
3269 -- then raise an error
3270 CURSOR l_okl_reject_le_csr(p_khr_id IN NUMBER)
3271 IS
3272 SELECT '1'
3273 FROM okl_k_headers khr,
3274 okl_pools pol
3275 WHERE pol.legal_entity_id <> khr.legal_entity_id
3276 AND pol.id =p_pol_id
3277 AND khr.id = p_khr_id;
3278
3279 CURSOR l_okl_reject_codes_csr
3280 IS
3281 SELECT lookup_code,
3282 meaning
3283 FROM fnd_lookups
3284 WHERE LOOKUP_TYPE LIKE 'OKL_POOL_REJECT_REASON'
3285 ORDER BY LOOKUP_CODE;
3286
3287 /* sosharma 21-nov-2007
3288 R12 Bug 6640050
3289 Cursor to check whether new transaction needs to be created for adjustments
3290 Start Changes
3291 */
3292 CURSOR l_trans_exists_csr(p_pol_id IN NUMBER)
3293 IS
3294 SELECT id pox_id,transaction_number FROM OKL_POOL_TRANSACTIONS pools
3295 where pools.transaction_status in (G_POOL_TRX_STATUS_INCOMPLETE,G_POOL_TRX_STATUS_NEW,G_POOL_TRX_STATUS_APPREJ)
3296 and pools.transaction_type='ADD' and pools.transaction_reason='ADJUSTMENTS'
3297 and pools.pol_id=p_pol_id;
3298
3299 -- Cursor to get the Legal Entity Id
3300 CURSOR l_okl_agrle_csr(p_pol_id IN NUMBER)
3301 IS
3302 SELECT legal_entity_id
3303 FROM okl_pools
3304 WHERE id = p_pol_id;
3305
3306
3307 /* sosharma end changes*/
3308
3309 l_api_name CONSTANT VARCHAR2(30) := 'add_pool_contents_pvt';
3310 l_api_version CONSTANT NUMBER := 1.0;
3311
3312 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3313
3314 l_pocv_rec pocv_rec_type;
3315 x_pocv_rec pocv_rec_type;
3316 --Added by kthiruva on 21-Nov-2007 for Bug 6640050
3317 l_pocv_tbl pocv_tbl_type;
3318 l_status_code okl_pools.status_code%TYPE;
3319 l_poc_count NUMBER := 0;
3320
3321 -- l_discarded BOOLEAN := FALSE;
3322 l_discard_count NUMBER := 0;
3323 l_add_count NUMBER := 0;
3324 l_reject_code VARCHAR2(5) ;
3325
3326 -- copied from okl_poolconc_pvt (need to modify this later to directly refer to that api)
3327 l_row_num_len NUMBER := 6;
3328 l_contract_num_len NUMBER := 30;
3329 l_asset_num_len NUMBER := 15;
3330 l_lessee_len NUMBER := 40;
3331 l_sty_subclass_len NUMBER := 25;
3332 l_reject_code_len NUMBER := 20;
3333 l_filler VARCHAR2(5) := RPAD(' ',5,' ');
3334
3335 l_adds_msg_tbl msg_tbl_type;
3336 l_rejects_msg_tbl msg_tbl_type;
3337 i NUMBER;
3338 --sosharma added
3339 l_pox_id NUMBER;
3340 l_transaction_number NUMBER;
3341 l_legal_entity_id NUMBER;
3342 l_transaction_status VARCHAR2(30);
3343 l_trx_id NUMBER;
3344 l_trans_found BOOLEAN := TRUE;
3345 lp_poxv_rec poxv_rec_type;
3346 lx_poxv_rec poxv_rec_type;
3347 x_pocv_tbl pocv_tbl_type;
3348
3349
3350 BEGIN
3351 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
3352 p_pkg_name => G_PKG_NAME,
3353 p_init_msg_list => p_init_msg_list,
3354 l_api_version => l_api_version,
3355 p_api_version => p_api_version,
3356 p_api_type => G_API_TYPE,
3357 x_return_status => l_return_status);
3358
3359 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
3360 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3361 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
3362 RAISE G_EXCEPTION_ERROR;
3363 END IF;
3364
3365
3366 -- Initialize API status to success
3367 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
3368
3369 l_pocv_rec.pol_id := p_pol_id;
3370 --Added by kthiruva on 21-Nov -2007 to fetch the Pool Status
3371 --Bug 6640050 - Start of Changes
3372 FOR pool_status_rec IN pool_status_csr(p_pol_id)
3373 LOOP
3374 l_status_code := pool_status_rec.status_code;
3375 END LOOP;
3376 --Bug 6640050 - End of Changes
3377
3378 FOR l_okl_poc_rec IN l_okl_pocs_csr
3379 LOOP
3380 l_reject_code := NULL;
3381
3382 -- Any poc that is caught in the following cursors
3383 -- needs to be discarded
3384
3385
3386 -- duplicate pocs
3387 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)
3388 LOOP
3389 l_discard_count := l_discard_count + 1;
3390 l_reject_code := G_REJECT_DUP_POCS;
3391 EXIT WHEN l_reject_code IS NOT NULL;
3392 END LOOP;
3393
3394
3395 IF l_reject_code IS NULL THEN
3396 /* Added by kthiruva to check if a contract stream has been bought back */
3397 FOR l_buyback_yes_rec IN l_buyback_yes_csr(l_okl_poc_rec.stm_id)
3398 LOOP
3399 /* ankushar 21-JAN-2008
3400 Bug#6740000 Modified cursor to populate the effective from date as the max date of the Active Streams.
3401 start changes
3402 */
3403 FOR l_max_to_date_rec IN l_max_to_date_csr(l_okl_poc_rec.stm_id)
3404 LOOP
3405 l_eff_from_date := l_max_to_date_rec.eff_from_date;
3406 END LOOP;
3407 /* ankushar 21-JAN-2008
3408 Bug#6740000 end changes
3409 */
3410 l_buyback_yn := true;
3411 END LOOP;
3412 -- variable interest
3413 -- fmiao bug 5160080
3414 --FOR l_okl_poc_vari_rec IN l_okl_poc_vari_csr(l_okl_poc_rec.khr_id)
3415 --LOOP
3416 Okl_K_Rate_Params_Pvt.GET_PRODUCT(
3417 p_api_version => p_api_version,
3418 p_init_msg_list => p_init_msg_list,
3419 x_return_status => x_return_status,
3420 x_msg_count => x_msg_count,
3421 x_msg_data => x_msg_data,
3422 p_khr_id => l_okl_poc_rec.khr_id,
3423 x_pdt_parameter_rec => l_pdt_parameters_rec);
3424
3425 IF l_pdt_parameters_rec.interest_calculation_basis <> 'FIXED' THEN
3426 l_discard_count := l_discard_count + 1;
3427 l_reject_code := G_REJECT_VARIABLE_INTEREST;
3428 ELSE
3429 IF l_pdt_parameters_rec.revenue_recognition_method = 'ACTUAL' THEN
3430 l_discard_count := l_discard_count + 1;
3431 l_reject_code := G_REJECT_VARIABLE_INTEREST;
3432 END IF;
3433 END IF;
3434 --EXIT WHEN l_reject_code IS NOT NULL;
3435 --END LOOP;
3436 -- fmiao bug 5160080 end
3437 END IF;
3438
3439 IF l_reject_code IS NULL THEN
3440 -- revision contract
3441 FOR l_okl_poc_rev_rec IN l_okl_poc_rev_csr(l_okl_poc_rec.khr_id)
3442 LOOP
3443 l_discard_count := l_discard_count + 1;
3444 l_reject_code := G_REJECT_REV_KHR;
3445 EXIT WHEN l_reject_code IS NOT NULL;
3446 END LOOP;
3447 END IF;
3448
3449 IF l_reject_code IS NULL THEN
3450 -- split asset components
3451 FOR l_okl_poc_splits_rec IN l_okl_poc_splits_csr(l_okl_poc_rec.khr_id,l_okl_poc_rec.kle_id)
3452 LOOP
3453 l_discard_count := l_discard_count + 1;
3454 l_reject_code := G_REJECT_SPLIT_ASSET;
3455 EXIT WHEN l_reject_code IS NOT NULL;
3456 END LOOP;
3457 END IF;
3458
3459 IF l_reject_code IS NULL THEN
3460 -- delinquent contract
3461 FOR l_okl_poc_delinq_rec IN l_okl_poc_delinq_csr(l_okl_poc_rec.khr_id)
3462 LOOP
3463 l_discard_count := l_discard_count + 1;
3464 l_reject_code := G_REJECT_DELINQ_KHR;
3465 EXIT WHEN l_reject_code IS NOT NULL;
3466 END LOOP;
3467 END IF;
3468
3469 IF l_reject_code IS NULL THEN
3470 -- terminated assets
3471 FOR l_okl_poc_kle_rec IN l_okl_poc_kle_csr(l_okl_poc_rec.kle_id)
3472 LOOP
3473 l_discard_count := l_discard_count + 1;
3474 l_reject_code := G_REJECT_ASSET_TERMINATED;
3475 EXIT WHEN l_reject_code IS NOT NULL;
3476 END LOOP;
3477 END IF;
3478
3479 IF l_reject_code IS NULL THEN
3480 -- check for Legal Entity
3481 FOR l_okl_poc_le_rec IN l_okl_reject_le_csr(l_okl_poc_rec.khr_id)
3482 LOOP
3483 l_discard_count := l_discard_count + 1;
3484 l_reject_code := G_REJECT_LEGAL_ENTITY_MISMATCH;
3485 EXIT WHEN l_reject_code IS NOT NULL;
3486 END LOOP;
3487 END IF;
3488
3489 IF l_reject_code IS NOT NULL THEN
3490 -- write it to the report
3491
3492 IF Fnd_Global.CONC_REQUEST_ID <> -1 THEN
3493
3494 l_rejects_msg_tbl(l_discard_count).msg := RPAD(l_discard_count,l_row_num_len)
3495 || RPAD(l_okl_poc_rec.contract_number ,l_contract_num_len)
3496 || RPAD(l_okl_poc_rec.asset_number ,l_asset_num_len)
3497 || RPAD(l_okl_poc_rec.lessee ,l_lessee_len)
3498 || RPAD(l_okl_poc_rec.sty_subclass_meaning ,l_sty_subclass_len)
3499 || RPAD(l_reject_code ,l_reject_code_len);
3500 END IF;
3501
3502 ELSE
3503
3504 l_add_count := l_add_count+1;
3505 IF l_status_code = 'NEW' THEN
3506
3507 l_pocv_rec.khr_id := l_okl_poc_rec.khr_id;
3508 l_pocv_rec.kle_id := l_okl_poc_rec.kle_id;
3509 l_pocv_rec.sty_id := l_okl_poc_rec.sty_id;
3510 l_pocv_rec.sty_code := l_okl_poc_rec.sty_code;
3511 /*Modified by kthiruva to set the streams start date to effective from date of the stream has been bought back*/
3512 IF (l_buyback_yn) AND (l_okl_poc_rec.streams_from_date < (l_eff_from_date +1)) THEN
3513 l_pocv_rec.streams_from_date := l_eff_from_date + 1;
3514 ELSE
3515 l_pocv_rec.streams_from_date := l_okl_poc_rec.streams_from_date;
3516 END IF;
3517 l_pocv_rec.streams_to_date := l_okl_poc_rec.streams_to_date;
3518 l_pocv_rec.stm_id := l_okl_poc_rec.stm_id;
3519
3520 Okl_Pool_Pvt.create_pool_contents(
3521 p_api_version => p_api_version,
3522 p_init_msg_list => p_init_msg_list,
3523 x_return_status => x_return_status,
3524 x_msg_count => x_msg_count,
3525 x_msg_data => x_msg_data,
3526 p_pocv_rec => l_pocv_rec,
3527 x_pocv_rec => x_pocv_rec);
3528
3529
3530 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3531 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3532 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3533 RAISE Okl_Api.G_EXCEPTION_ERROR;
3534 END IF;
3535
3536 ELSE
3537 /* sosharma 21-Nov-2007
3538 R12 Bug 6640050
3539 Code to create a table for pool content records before making the create call
3540 Start Changes
3541 */
3542 l_pocv_tbl(l_poc_count).pol_id := p_pol_id;
3543 l_pocv_tbl(l_poc_count).khr_id := l_okl_poc_rec.khr_id;
3544 l_pocv_tbl(l_poc_count).kle_id := l_okl_poc_rec.kle_id;
3545 l_pocv_tbl(l_poc_count).sty_id := l_okl_poc_rec.sty_id;
3546 l_pocv_tbl(l_poc_count).sty_code := l_okl_poc_rec.sty_code;
3547 /*Modified by kthiruva to set the streams start date to effective
3548 * from date of the stream has been bought back*/
3549 IF (l_buyback_yn) AND (l_okl_poc_rec.streams_from_date <(l_eff_from_date +1)) THEN
3550 l_pocv_tbl(l_poc_count).streams_from_date := l_eff_from_date + 1;
3551
3552 ELSE
3553 l_pocv_tbl(l_poc_count).streams_from_date := l_okl_poc_rec.streams_from_date;
3554 END IF; l_pocv_tbl(l_poc_count).streams_to_date := l_okl_poc_rec.streams_to_date;
3555 l_pocv_tbl(l_poc_count).stm_id := l_okl_poc_rec.stm_id;
3556 -- Status code to be set as PENDING
3557 -- l_pocv_tbl(l_poc_count).status_code := G_POC_STS_PENDING;
3558 l_poc_count := l_poc_count + 1;
3559 END IF;
3560 /* sosharma End changes*/
3561
3562
3563
3564 /* ankushar 26-JUL-2007
3565 Bug#6000531
3566 start changes
3567 */
3568 IF p_log_message ='Y' THEN
3569 /* ankushar end changes 26-Jul-2007*/
3570 -- populate the log table only if the p_log_message is 'Y'
3571 IF Fnd_Global.CONC_REQUEST_ID <> -1 THEN
3572 l_adds_msg_tbl(l_add_count).msg := RPAD(l_add_count,l_row_num_len)
3573 || RPAD(l_okl_poc_rec.contract_number ,l_contract_num_len)
3574 || RPAD(l_okl_poc_rec.asset_number ,l_asset_num_len)
3575 || RPAD(l_okl_poc_rec.lessee ,l_lessee_len)
3576 || RPAD(l_okl_poc_rec.sty_subclass_meaning ,l_sty_subclass_len);
3577 END IF;
3578
3579 END IF;
3580
3581 END IF;
3582
3583 END LOOP; -- l_okl_poc_csr
3584
3585 /* sosharma 21-Nov-2007
3586 R12 Bug 6640050
3587 Create pool contents and create transaction calls
3588 Start Changes
3589 */
3590 IF l_pocv_tbl.COUNT > 0 THEN
3591 OPEN l_trans_exists_csr(p_pol_id);
3592 FETCH l_trans_exists_csr INTO l_pox_id,l_transaction_number;
3593 l_trans_found := l_trans_exists_csr%FOUND;
3594 CLOSE l_trans_exists_csr;
3595 IF l_trans_found THEN
3596 FOR i IN l_pocv_tbl.FIRST..l_pocv_tbl.LAST LOOP
3597 l_pocv_tbl(i).pox_id:= l_pox_id;
3598 l_pocv_tbl(i).transaction_number_in:=l_transaction_number;
3599 END LOOP;
3600 ELSE
3601
3602 -- get the legal entity id to create transactions
3603 OPEN l_okl_agrle_csr(p_pol_id);
3604 FETCH l_okl_agrle_csr into l_legal_entity_id;
3605 CLOSE l_okl_agrle_csr;
3606
3607 -- populate pool transaction rec
3608 lp_poxv_rec.POL_ID := p_pol_id;
3609 lp_poxv_rec.TRANSACTION_DATE := SYSDATE;
3610 lp_poxv_rec.TRANSACTION_TYPE := G_POOL_TRX_ADD;
3611 lp_poxv_rec.TRANSACTION_REASON := G_POOL_TRX_REASON_ADJUST;
3612 lp_poxv_rec.CURRENCY_CODE := p_currency_code;
3613 lp_poxv_rec.LEGAL_ENTITY_ID := l_legal_entity_id;
3614 --sosharma 03/12/2007 added to enable status on pool transaction
3615 lp_poxv_rec.TRANSACTION_STATUS := G_POOL_TRX_STATUS_NEW;
3616
3617 -- create ADD transaction for Adjustment
3618 Okl_Pool_Pvt.create_pool_transaction(p_api_version => p_api_version
3619 ,p_init_msg_list => p_init_msg_list
3620 ,x_return_status => l_return_status
3621 ,x_msg_count => x_msg_count
3622 ,x_msg_data => x_msg_data
3623 ,p_poxv_rec => lp_poxv_rec
3624 ,x_poxv_rec => lx_poxv_rec);
3625
3626 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3627 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3628 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3629 RAISE Okl_Api.G_EXCEPTION_ERROR;
3630 END IF;
3631 -- Assign the Transaction Id to pool contents
3632 FOR i IN l_pocv_tbl.FIRST..l_pocv_tbl.LAST LOOP
3633 l_pocv_tbl(i).pox_id:= lx_poxv_rec.id;
3634 l_pocv_tbl(i).transaction_number_in:= lx_poxv_rec.transaction_number;
3635 END LOOP;
3636
3637 END IF;
3638 --- create pool contents for Adjustment ADD transaction
3639 Okl_Pool_Pvt.create_pool_contents(
3640 p_api_version => p_api_version,
3641 p_init_msg_list => p_init_msg_list,
3642 x_return_status => x_return_status,
3643 x_msg_count => x_msg_count,
3644 x_msg_data => x_msg_data,
3645 p_pocv_tbl => l_pocv_tbl,
3646 x_pocv_tbl => x_pocv_tbl);
3647
3648
3649 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3650 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3651 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3652 RAISE Okl_Api.G_EXCEPTION_ERROR;
3653 END IF;
3654
3655 -- get existing the transaction status
3656 OPEN l_trans_status_csr(p_pol_id);
3657 FETCH l_trans_status_csr INTO l_transaction_status,l_trx_id;
3658 CLOSE l_trans_status_csr;
3659
3660 IF l_transaction_status = G_POOL_TRX_STATUS_APPREJ THEN
3661 lp_poxv_rec.TRANSACTION_STATUS := G_POOL_TRX_STATUS_INCOMPLETE;
3662 lp_poxv_rec.POL_ID := p_pol_id;
3663 lp_poxv_rec.ID := l_trx_id;
3664
3665 -- create ADD transaction for Adjustment
3666 Okl_Pool_Pvt.update_pool_transaction(p_api_version => p_api_version
3667 ,p_init_msg_list => p_init_msg_list
3668 ,x_return_status => l_return_status
3669 ,x_msg_count => x_msg_count
3670 ,x_msg_data => x_msg_data
3671 ,p_poxv_rec => lp_poxv_rec
3672 ,x_poxv_rec => lx_poxv_rec);
3673
3674 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3675 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3676 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3677 RAISE Okl_Api.G_EXCEPTION_ERROR;
3678 END IF;
3679 END IF;
3680
3681 END IF;
3682
3683
3684 /* sosharma end changes*/
3685
3686 /* ankushar 26-JUL-2007
3687 Bug#6000531
3688 start changes
3689 */
3690 IF p_log_message ='Y' THEN
3691 /* ankushar end changes 26-Jul-2007*/
3692 /*** REJECTS ***/
3693 -- note preceding table header
3694 Fnd_File.Put_Line(Fnd_File.output,' ');
3695 Fnd_File.Put_Line(Fnd_File.output,Fnd_Message.get_string(g_app_name,g_pool_add_tbl_hdr));
3696
3697 -- table header
3698 Fnd_File.Put_Line(Fnd_File.output,RPAD('-',l_row_num_len-1,'-') || ' '
3699 || RPAD('-',l_contract_num_len-1,'-') || ' '
3700 || RPAD('-',l_asset_num_len-1,'-') || ' '
3701 || RPAD('-',l_lessee_len-1,'-') || ' '
3702 || RPAD('-',l_sty_subclass_len-1,'-') || ' '
3703 || RPAD('-',l_reject_code_len-1,'-'));
3704
3705 Fnd_File.Put_Line(Fnd_File.output,RPAD(Fnd_Message.get_string(g_app_name,g_row_number),l_row_num_len-1) || ' '
3706 || RPAD(Fnd_Message.get_string(g_app_name,g_contract_number),l_contract_num_len-1) || ' '
3707 || RPAD(Fnd_Message.get_string(g_app_name,g_asset_number),l_asset_num_len-1) || ' '
3708 || RPAD(Fnd_Message.get_string(g_app_name,g_lessee),l_lessee_len-1) || ' '
3709 || RPAD(Fnd_Message.get_string(g_app_name,g_stream_type_subclass),l_sty_subclass_len-1) || ' '
3710 || RPAD(Fnd_Message.get_string(g_app_name,g_reject_reason_code),l_reject_code_len-1));
3711
3712 Fnd_File.Put_Line(Fnd_File.output,RPAD('-',l_row_num_len-1,'-') || ' '
3713 || RPAD('-',l_contract_num_len-1,'-') || ' '
3714 || RPAD('-',l_asset_num_len-1,'-') || ' '
3715 || RPAD('-',l_lessee_len-1,'-') || ' '
3716 || RPAD('-',l_sty_subclass_len-1,'-') || ' '
3717 || RPAD('-',l_reject_code_len-1,'-'));
3718
3719 FOR i IN 1..l_rejects_msg_tbl.COUNT
3720 LOOP
3721 Fnd_File.Put_Line(Fnd_File.OUTPUT, l_rejects_msg_tbl(i).msg);
3722 END LOOP;
3723
3724 Fnd_File.Put_Line(Fnd_File.OUTPUT,' ');
3725 Fnd_File.Put_Line(Fnd_File.OUTPUT,Fnd_Message.GET_STRING(G_APP_NAME,G_REJECT_REASON_CODES));
3726
3727 -- Listing Reason Code Meaning-s
3728 FOR l_okl_reject_codes_rec IN l_okl_reject_codes_csr
3729 LOOP
3730 Fnd_File.Put_Line(Fnd_File.OUTPUT,l_filler || l_okl_reject_codes_rec.lookup_code
3731 || ' => '
3732 || l_okl_reject_codes_rec.meaning);
3733 END LOOP;
3734
3735 /*** ADDS ***/
3736
3737 -- note preceding table header
3738 Fnd_File.Put_Line(Fnd_File.output,' ');
3739 Fnd_File.Put_Line(Fnd_File.output,' ');
3740 Fnd_File.Put_Line(Fnd_File.output,' ');
3741 Fnd_File.Put_Line(Fnd_File.output,' ');
3742 Fnd_File.Put_Line(Fnd_File.output,Fnd_Message.get_string(g_app_name,g_pool_add_new_tbl_hdr));
3743
3744 -- table header
3745 Fnd_File.Put_Line(Fnd_File.output,RPAD('-',l_row_num_len-1,'-') || ' '
3746 || RPAD('-',l_contract_num_len-1,'-') || ' '
3747 || RPAD('-',l_asset_num_len-1,'-') || ' '
3748 || RPAD('-',l_lessee_len-1,'-') || ' '
3749 || RPAD('-',l_sty_subclass_len-1,'-'));
3750
3751 Fnd_File.Put_Line(Fnd_File.output,RPAD(Fnd_Message.get_string(g_app_name,g_row_number),l_row_num_len-1) || ' '
3752 || RPAD(Fnd_Message.get_string(g_app_name,g_contract_number),l_contract_num_len-1) || ' '
3753 || RPAD(Fnd_Message.get_string(g_app_name,g_asset_number),l_asset_num_len-1) || ' '
3754 || RPAD(Fnd_Message.get_string(g_app_name,g_lessee),l_lessee_len-1) || ' '
3755 || RPAD(Fnd_Message.get_string(g_app_name,g_stream_type_subclass),l_sty_subclass_len-1));
3756
3757 Fnd_File.Put_Line(Fnd_File.output,RPAD('-',l_row_num_len-1,'-') || ' '
3758 || RPAD('-',l_contract_num_len-1,'-') || ' '
3759 || RPAD('-',l_asset_num_len-1,'-') || ' '
3760 || RPAD('-',l_lessee_len-1,'-') || ' '
3761 || RPAD('-',l_sty_subclass_len-1,'-'));
3762
3763 FOR i IN 1..l_adds_msg_tbl.COUNT
3764 LOOP
3765 Fnd_File.Put_Line(Fnd_File.OUTPUT, l_adds_msg_tbl(i).msg);
3766 END LOOP;
3767
3768 END IF;
3769
3770 x_row_count := l_add_count;
3771
3772 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count
3773 ,x_msg_data => x_msg_data);
3774
3775 x_return_status := l_return_status;
3776
3777
3778 EXCEPTION
3779 WHEN G_EXCEPTION_ERROR THEN
3780
3781 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3782 p_pkg_name => G_PKG_NAME,
3783 p_exc_name => G_EXC_NAME_ERROR,
3784 x_msg_count => x_msg_count,
3785 x_msg_data => x_msg_data,
3786 p_api_type => G_API_TYPE);
3787 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
3788
3789 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3790 p_pkg_name => G_PKG_NAME,
3791 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
3792 x_msg_count => x_msg_count,
3793 x_msg_data => x_msg_data,
3794 p_api_type => G_API_TYPE);
3795 WHEN OTHERS THEN
3796
3797 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3798 p_pkg_name => G_PKG_NAME,
3799 p_exc_name => G_EXC_NAME_OTHERS,
3800 x_msg_count => x_msg_count,
3801 x_msg_data => x_msg_data,
3802 p_api_type => G_API_TYPE);
3803
3804
3805 END add_pool_contents;
3806
3807 ----------------------------------------------------------------------------------
3808 -- Start of comments
3809 --
3810 -- Procedure Name : cleanup_pool_contents
3811 -- Description : removes pool contents based on passed in search criteria
3812 -- Business Rules :
3813 -- Parameters :
3814 -- Version : 1.0
3815 -- End of comments
3816 ----------------------------------------------------------------------------------
3817 -- Create by Search Criteria: Query Streams from contracts + Create
3818
3819 PROCEDURE cleanup_pool_contents(
3820 p_api_version IN NUMBER
3821 ,p_init_msg_list IN VARCHAR2
3822 ,x_return_status OUT NOCOPY VARCHAR2
3823 ,x_msg_count OUT NOCOPY NUMBER
3824 ,x_msg_data OUT NOCOPY VARCHAR2
3825 ,p_currency_code IN VARCHAR2
3826 ,p_pol_id IN NUMBER
3827 ,p_multi_org IN VARCHAR2
3828 ,p_cust_object1_id1 IN NUMBER
3829 ,p_sic_code IN VARCHAR2
3830 ,p_dnz_chr_id IN NUMBER
3831 ,p_pre_tax_yield_from IN NUMBER
3832 ,p_pre_tax_yield_to IN NUMBER
3833 ,p_book_classification IN VARCHAR2
3834 ,p_tax_owner IN VARCHAR2
3835 ,p_pdt_id IN NUMBER
3836 ,p_start_from_date IN DATE
3837 ,p_start_to_date IN DATE
3838 ,p_end_from_date IN DATE
3839 ,p_end_to_date IN DATE
3840 ,p_asset_id IN NUMBER
3841 ,p_item_id1 IN NUMBER
3842 ,p_model_number IN VARCHAR2
3843 ,p_manufacturer_name IN VARCHAR2
3844 ,p_vendor_id1 IN NUMBER
3845 ,p_oec_from IN NUMBER
3846 ,p_oec_to IN NUMBER
3847 ,p_residual_percentage IN NUMBER
3848 ,p_sty_id IN NUMBER
3849 -- mvasudev, 11.5.10
3850 ,p_stream_type_subclass IN VARCHAR2
3851 -- end, mvasudev, 11.5.10
3852 ,p_streams_from_date IN DATE
3853 ,p_streams_to_date IN DATE
3854 ,p_action_code IN VARCHAR2
3855 ,x_poc_uv_tbl OUT NOCOPY poc_uv_tbl_type
3856 ,p_cust_crd_clf_code IN VARCHAR2 DEFAULT NULL)
3857 IS
3858 l_api_name CONSTANT VARCHAR2(30) := 'cleanup_pool_contents';
3859 l_api_version CONSTANT NUMBER := 1.0;
3860 i NUMBER;
3861 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3862
3863 l_pocv_rec pocv_rec_type;
3864 lp_poxv_rec poxv_rec_type;
3865 lx_poxv_rec poxv_rec_type;
3866
3867 l_transaction_status VARCHAR2(30);
3868 l_trx_id NUMBER;
3869
3870 CURSOR l_okl_pool_cleanup_csr(p_allowed_sts Varchar2) IS
3871 SELECT pocv.poc_id
3872 ,pocv.contract_number
3873 ,pocv.asset_number
3874 ,pocv.lessee
3875 ,pocv.stream_type_name
3876 ,pocv.sty_subclass_code
3877 ,pocv.sty_subclass
3878 -- mvasudev, 09/28/2004, Bug#3909240
3879 ,pocv.stream_type_purpose
3880 ,hcp.credit_classification
3881 FROM okl_pool_contents_uv pocv
3882 ,hz_customer_profiles hcp
3883 -- pool_number
3884 WHERE pocv.pol_id = p_pol_id
3885 -- customer
3886 AND NVL(pocv.cust_object1_id1,G_DEFAULT_NUM) = NVL(p_cust_object1_id1, NVL(pocv.cust_object1_id1,G_DEFAULT_NUM))
3887 AND NVL(pocv.sic_code,G_DEFAULT_CHAR) = NVL(p_sic_code, NVL(pocv.sic_code,G_DEFAULT_CHAR))
3888 -- contract
3889 AND NVL(pocv.dnz_chr_id,G_DEFAULT_NUM) = NVL(p_dnz_chr_id, NVL(pocv.dnz_chr_id,G_DEFAULT_NUM))
3890 AND NVL(pocv.pre_tax_yield,G_DEFAULT_NUM) BETWEEN NVL(p_pre_tax_yield_from, NVL(pocv.pre_tax_yield,G_DEFAULT_NUM))
3891 AND NVL(p_pre_tax_yield_to, NVL(pocv.pre_tax_yield,G_DEFAULT_NUM))
3892 AND NVL(pocv.book_classification,G_DEFAULT_CHAR) = NVL(p_book_classification, NVL(pocv.book_classification,G_DEFAULT_CHAR))
3893 AND NVL(pocv.pdt_id,G_DEFAULT_NUM) = NVL(p_pdt_id, NVL(pocv.pdt_id,G_DEFAULT_NUM))
3894 AND NVL(pocv.start_date, G_DEFAULT_DATE)
3895 BETWEEN NVL(p_start_from_date, NVL(pocv.start_date, G_DEFAULT_DATE))
3896 AND NVL(p_start_to_date, NVL(pocv.start_date, G_DEFAULT_DATE))
3897 AND NVL(pocv.end_date,G_FINAL_DATE)
3898 BETWEEN NVL(p_end_from_date, NVL(pocv.end_date,G_FINAL_DATE))
3899 AND NVL(p_end_to_date, NVL(pocv.end_date,G_FINAL_DATE))
3900 AND NVL(pocv.tax_owner,G_DEFAULT_CHAR) = NVL(p_tax_owner, NVL(pocv.tax_owner,G_DEFAULT_CHAR))
3901 -- asset
3902 /* cklee, 04/23/2003
3903 AND NVL(pocv.asset_id,G_DEFAULT_NUM) = NVL(p_asset_id, NVL(pocv.asset_id,G_DEFAULT_NUM))
3904 AND NVL(UPPER(pocv.model_number),G_DEFAULT_CHAR) LIKE NVL(UPPER(p_model_number),NVL(UPPER(pocv.model_number),G_DEFAULT_CHAR))
3905 AND NVL(UPPER(pocv.manufacturer_name),G_DEFAULT_CHAR) LIKE NVL(UPPER(p_manufacturer_name),NVL(UPPER(pocv.manufacturer_name),G_DEFAULT_CHAR))
3906 AND NVL(pocv.item_id1,G_DEFAULT_NUM) = NVL(p_item_id1, NVL(pocv.item_id1,G_DEFAULT_NUM))
3907 AND NVL(pocv.vendor_id1,G_DEFAULT_NUM) = NVL(p_vendor_id1, NVL(pocv.vendor_id1,G_DEFAULT_NUM))
3908 AND NVL(pocv.oec,G_DEFAULT_NUM) BETWEEN NVL(p_oec_from, NVL(pocv.oec,G_DEFAULT_NUM))
3909 AND NVL(p_oec_to, NVL(pocv.oec,G_DEFAULT_NUM))
3910 AND NVL(pocv.residual_percentage,G_DEFAULT_NUM) = NVL(p_residual_percentage, NVL(pocv.residual_percentage,G_DEFAULT_NUM))
3911 */
3912 -- streams
3913 -- mvasudev, 11.5.10
3914 --AND NVL(pocv.sty_id,G_DEFAULT_NUM) = NVL(p_sty_id, NVL(pocv.sty_id,G_DEFAULT_NUM))
3915 AND NVL(pocv.sty_subclass_code,G_DEFAULT_CHAR) = NVL(p_stream_type_subclass, NVL(pocv.sty_subclass_code,G_DEFAULT_CHAR))
3916 -- end, mvasudev, 11.5.10
3917 AND NVL(pocv.streams_from_date, G_DEFAULT_DATE)
3918 BETWEEN NVL(p_streams_from_date, NVL(pocv.streams_from_date, G_DEFAULT_DATE))
3919 AND NVL(p_streams_to_date, NVL(pocv.streams_to_date, G_FINAL_DATE))
3920 -- cklee 04/10/2003
3921 AND EXISTS (SELECT '1'
3922 FROM okl_pool_contents pc
3923 WHERE pc.id = pocv.POC_ID
3924 AND pc.status_code IN (p_allowed_sts))
3925 -- cklee 04/10/2003
3926 --Bug # 6691554 Changes for Cust Credit Classification Lov Start
3927 AND pocv.cust_object1_id1 = hcp.party_id(+)
3928 AND hcp.cust_account_id(+) = -1
3929 AND hcp.site_use_id(+) IS NULL
3930 AND NVL(hcp.credit_classification(+),G_DEFAULT_CHAR) = NVL(p_cust_crd_clf_code, NVL(hcp.credit_classification(+),G_DEFAULT_CHAR))
3931 --Bug # 6691554 Changes for Cust Credit Classification Lov End
3932 ORDER BY pocv.contract_number
3933 ,pocv.asset_number
3934 ,pocv.stream_type_name;
3935
3936
3937 lp_pocv_tbl pocv_tbl_type;
3938
3939 l_row_count NUMBER;
3940 l_pool_amount NUMBER;
3941 --Begin - Changes for Bug 6640050 by varangan on 29-Nov-2007
3942 CURSOR c_strm_amount ( p_poc_id NUMBER)
3943 IS
3944 SELECT NVL(SUM(NVL(ele.AMOUNT,0)),0) STREAM_AMOUNT
3945 FROM okl_streams strm
3946 ,okl_strm_elements ele
3947 ,okl_pool_contents cnt
3948 WHERE strm.id = ele.stm_id
3949 AND cnt.ID = p_poc_id
3950 AND strm.ID = cnt.STM_ID
3951 AND strm.say_code = 'CURR'
3952 AND strm.active_yn = 'Y'
3953 AND cnt.status_code IN (G_POC_STS_PENDING)
3954 AND ele.STREAM_ELEMENT_DATE
3955 BETWEEN cnt.STREAMS_FROM_DATE AND NVL(cnt.STREAMS_TO_DATE,G_FINAL_DATE);
3956
3957 l_allowed_status Varchar2(100);
3958 l_status_code okl_pools.status_Code%TYPE;
3959 --End - Changes for Bug 6640050 by varangan on 29-Nov-2007
3960
3961 BEGIN
3962 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
3963 p_pkg_name => G_PKG_NAME,
3964 p_init_msg_list => p_init_msg_list,
3965 l_api_version => l_api_version,
3966 p_api_version => p_api_version,
3967 p_api_type => G_API_TYPE,
3968
3969 x_return_status => l_return_status);
3970 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
3971 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3972 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
3973 RAISE G_EXCEPTION_ERROR;
3974 END IF;
3975
3976
3977 l_row_count := 0;
3978 -- Begin - Changes for Bug#6658065
3979 --(1) Check the pool status for clean up action
3980 FOR pool_status_rec IN pool_status_csr(p_pol_id)
3981 LOOP
3982 l_status_code := pool_status_rec.status_code;
3983 END LOOP;
3984 --(2) If the Status is 'Active', then only 'Pending' status pool contents created for adjustment should be removed
3985 -- else, existing flow should be followed
3986 IF l_status_code = G_POL_STS_ACTIVE THEN
3987 l_allowed_status:= G_POC_STS_PENDING;
3988 ELSE
3989 l_allowed_status:=G_POC_STS_NEW; -- Fetch only 'New' status POCs for Bug 6691554
3990 END IF;
3991 --(3) Query the pool contents as per the status check
3992 FOR l_okl_pool_cleanup_rec IN l_okl_pool_cleanup_csr(l_allowed_status)
3993 LOOP
3994 l_row_count := l_row_count + 1;
3995 lp_pocv_tbl(l_row_count).id := l_okl_pool_cleanup_rec.poc_id;
3996 -- Get Pool Stream Amount to Display
3997 IF l_status_code= G_POL_STS_ACTIVE THEN -- Get the pending contents stream amount
3998 OPEN c_strm_amount (l_okl_pool_cleanup_rec.poc_id) ;
3999 FETCH c_strm_amount INTO l_pool_amount;
4000 CLOSE c_strm_amount;
4001 ELSE -- follow the existing process
4002 l_pool_amount := Okl_Pool_Pvt.get_pool_stream_amout(l_okl_pool_cleanup_rec.poc_id);
4003 END IF;
4004
4005 x_poc_uv_tbl(l_row_count).poc_id := l_okl_pool_cleanup_rec.poc_id;
4006 x_poc_uv_tbl(l_row_count).contract_number := l_okl_pool_cleanup_rec.contract_number;
4007 x_poc_uv_tbl(l_row_count).asset_number := l_okl_pool_cleanup_rec.asset_number;
4008 x_poc_uv_tbl(l_row_count).lessee := l_okl_pool_cleanup_rec.lessee;
4009 x_poc_uv_tbl(l_row_count).stream_type_name := l_okl_pool_cleanup_rec.stream_type_name;
4010
4011 x_poc_uv_tbl(l_row_count).sty_subclass_code := l_okl_pool_cleanup_rec.sty_subclass_code;
4012 x_poc_uv_tbl(l_row_count).sty_subclass := l_okl_pool_cleanup_rec.sty_subclass;
4013
4014 x_poc_uv_tbl(l_row_count).pool_amount := l_pool_amount;
4015 -- mvasudev, 09/28/2004, Bug#3909240
4016 x_poc_uv_tbl(l_row_count).stream_type_purpose := l_okl_pool_cleanup_rec.stream_type_purpose;
4017
4018 END LOOP;
4019 -- End - Changes for Bug#6658065
4020
4021 IF p_action_code = Okl_Pool_Pvt.G_ACTION_REMOVE THEN
4022 Okl_Pool_Pvt.delete_pool_contents(p_api_version => p_api_version
4023 ,p_init_msg_list => p_init_msg_list
4024 ,x_return_status => l_return_status
4025 ,x_msg_count => x_msg_count
4026 ,x_msg_data => x_msg_data
4027 ,p_pocv_tbl => lp_pocv_tbl);
4028
4029 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
4030 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4031 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
4032 RAISE G_EXCEPTION_ERROR;
4033 END IF;
4034 END IF;
4035
4036 -- get existing the transaction status
4037 OPEN l_trans_status_csr(p_pol_id);
4038 FETCH l_trans_status_csr INTO l_transaction_status,l_trx_id;
4039 CLOSE l_trans_status_csr;
4040
4041 IF l_transaction_status = G_POOL_TRX_STATUS_APPREJ THEN
4042 lp_poxv_rec.TRANSACTION_STATUS := G_POOL_TRX_STATUS_INCOMPLETE;
4043 lp_poxv_rec.POL_ID := p_pol_id;
4044 lp_poxv_rec.ID := l_trx_id;
4045
4046 -- create ADD transaction for Adjustment
4047 Okl_Pool_Pvt.update_pool_transaction(p_api_version => p_api_version
4048 ,p_init_msg_list => p_init_msg_list
4049 ,x_return_status => l_return_status
4050 ,x_msg_count => x_msg_count
4051 ,x_msg_data => x_msg_data
4052 ,p_poxv_rec => lp_poxv_rec
4053 ,x_poxv_rec => lx_poxv_rec);
4054
4055 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4056 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4057 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4058 RAISE Okl_Api.G_EXCEPTION_ERROR;
4059 END IF;
4060 END IF;
4061
4062 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count
4063 ,x_msg_data => x_msg_data);
4064
4065 x_return_status := l_return_status;
4066
4067 EXCEPTION
4068 WHEN G_EXCEPTION_ERROR THEN
4069
4070 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
4071 p_pkg_name => G_PKG_NAME,
4072 p_exc_name => G_EXC_NAME_ERROR,
4073 x_msg_count => x_msg_count,
4074 x_msg_data => x_msg_data,
4075 p_api_type => G_API_TYPE);
4076 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
4077
4078 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
4079 p_pkg_name => G_PKG_NAME,
4080 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
4081 x_msg_count => x_msg_count,
4082 x_msg_data => x_msg_data,
4083 p_api_type => G_API_TYPE);
4084 WHEN OTHERS THEN
4085
4086 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
4087 p_pkg_name => G_PKG_NAME,
4088 p_exc_name => G_EXC_NAME_OTHERS,
4089 x_msg_count => x_msg_count,
4090 x_msg_data => x_msg_data,
4091 p_api_type => G_API_TYPE);
4092 END cleanup_pool_contents;
4093
4094 ----------------------------------------------------------------------------------
4095 -- Start of comments
4096 --
4097 -- Procedure Name : reconcile_contents
4098 -- Description : reconcile pool contents
4099 -- Business Rules :
4100 -- Parameters :
4101 -- Version : 1.0
4102 -- End of comments
4103 ----------------------------------------------------------------------------------
4104 PROCEDURE reconcile_contents(p_api_version IN NUMBER
4105 ,p_init_msg_list IN VARCHAR2
4106 ,p_pol_id IN NUMBER
4107 ,p_mode IN VARCHAR2 DEFAULT NULL
4108 ,x_return_status OUT NOCOPY VARCHAR2
4109 ,x_msg_count OUT NOCOPY NUMBER
4110 ,x_msg_data OUT NOCOPY VARCHAR2
4111 ,x_reconciled OUT NOCOPY VARCHAR2)
4112 IS
4113 --fmiao 21-OCT-2005 bug 4775555 --
4114 CURSOR evg_rent_strms_csr (p_pol_id IN NUMBER)
4115 IS
4116 -- to remove all the rent strms if the contract turns to EVERGREEN--
4117 SELECT poc.id
4118 FROM okl_pool_contents poc,
4119 okl_pools pol,
4120 okl_strm_type_b sty,
4121 okc_k_headers_b CHR
4122 WHERE pol.id = p_pol_id
4123 AND pol.id = poc.pol_id
4124 AND poc.sty_id = sty.id
4125 AND sty.STREAM_TYPE_SUBCLASS ='RENT'
4126 AND poc.KHR_ID = CHR.id
4127 AND CHR.sts_code ='EVERGREEN'
4128 AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE) ;
4129 --fmiao 21-OCT-2005 bug 4775555 --
4130
4131 CURSOR l_okl_invalid_khr_csr(p_pol_id IN NUMBER)
4132 IS
4133 -- to remove all contents pointing to invalid contracts
4134 SELECT poc.id
4135 FROM okl_pool_contents poc,
4136 okc_k_headers_b chrb,
4137 okl_k_headers khrb
4138 WHERE poc.pol_id = p_pol_id
4139 AND poc.khr_id = chrb.id
4140 AND poc.khr_id = khrb.id
4141 -- cklee 04/10/2003 never reconcile historical data
4142 AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
4143 -- cklee 04/10/2003 never reconcile historical data
4144 AND (chrb.sts_code NOT IN ('BOOKED','EVERGREEN') OR khrb.assignable_yn <> 'Y');
4145
4146 CURSOR l_okl_delinq_khr_csr(p_pol_id IN NUMBER)
4147 IS
4148 -- to remove all contents pointing to delinquent contracts
4149 SELECT poc.id
4150 FROM okl_pool_contents poc,
4151 iex_case_objects ico,
4152 iex_delinquencies_all del
4153 WHERE poc.pol_id = p_pol_id
4154 AND poc.khr_id = ico.object_id
4155 AND ico. cas_id = del.case_id
4156 -- cklee 04/10/2003 never reconcile historical data
4157 AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
4158 -- cklee 04/10/2003 never reconcile historical data
4159 AND del.status = 'DELINQUENT';
4160
4161 CURSOR l_okl_rev_khr_csr(p_pol_id IN NUMBER)
4162 IS
4163 -- and to remove all contents pointing to contracts under modification
4164 SELECT poc.id
4165 FROM okl_pool_contents poc,
4166 okc_k_headers_b chrb
4167 WHERE poc.pol_id = p_pol_id
4168 AND poc.khr_id = chrb.id
4169 -- cklee 04/10/2003 never reconcile historical data
4170 AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
4171 -- cklee 04/10/2003 never reconcile historical data
4172 -- AND chrb.sts_code = 'BOOKED'
4173 AND EXISTS -- revision contract: rebook, split contract, reverse
4174 (SELECT '1'
4175 FROM okl_trx_contracts trxb
4176 WHERE trxb.tcn_type IN ('TRBK','SPLC','RVS')
4177 -- AND trxb.tsu_code = 'ENTERED'
4178 AND trxb.tsu_code NOT IN ('PROCESSED', 'ERROR','CANCELED') -- condition changes 01/13/2003 cklee
4179 AND trxb.khr_id = poc.khr_id
4180 )
4181 AND EXISTS -- split assets, split assets components
4182 (SELECT '1'
4183 FROM okl_txd_assets_v tdas,
4184 okl_txl_assets_b talb,
4185 okc_k_lines_b cleb
4186 WHERE cleb.id = talb.kle_id
4187 AND talb.ID = tdas.TAL_ID
4188 AND talb.TAL_TYPE = 'ALI'
4189 AND cleb.cle_id = poc.kle_id -- top line id
4190 AND talb.dnz_khr_id = poc.khr_id
4191 AND EXISTS (SELECT '1'
4192 FROM okl_trx_assets tas
4193 WHERE tas.id = talb.tas_id
4194 AND tas.tas_type = 'ALI'
4195 AND tas.tsu_code = 'PROCESSED')
4196 );
4197
4198 CURSOR l_okl_invalid_assets_csr(p_pol_id IN NUMBER)
4199 IS
4200 -- and to remove all contents pointing to lease contracts
4201 -- that have atleast one invalid asset
4202 SELECT poc.id
4203 FROM okl_pool_contents poc
4204 WHERE poc.khr_id
4205 IN
4206 (SELECT poc.khr_id
4207 FROM okl_pool_contents poc,
4208 okc_k_lines_b cleb,
4209 okc_statuses_b stsb
4210 WHERE poc.pol_id = p_pol_id
4211 AND poc.kle_id = cleb.id
4212 AND cleb.sts_code = stsb.code
4213 -- cklee 04/10/2003 never reconcile historical data
4214 AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
4215 -- cklee 04/10/2003 never reconcile historical data
4216 AND stsb.ste_code IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
4217 );
4218
4219 -- v115.37 Fix
4220 CURSOR l_okl_invalid_streams_csr(p_pol_id IN NUMBER)
4221 IS
4222 -- to remove all contents pointing to inactive streams
4223 --- or assets that do not have streams
4224 /*
4225 SELECT poc.id
4226 FROM okl_pool_contents poc,
4227 okl_streams stmb
4228 WHERE poc.pol_id = p_pol_id
4229 AND poc.kle_id = stmb.kle_id
4230 AND poc.sty_id = stmb.sty_id
4231 AND (
4232 -- if the streams are not active
4233 stmb.active_yn <> 'Y'
4234 --if stream elements do not exist
4235
4236 OR NOT EXISTS
4237 (
4238 SELECT '1'
4239 FROM okl_strm_elements selb
4240 WHERE selb.stm_id = stmb.id
4241 )
4242 );
4243 */
4244 SELECT poc.id
4245 FROM okl_pool_contents poc
4246 WHERE poc.pol_id = p_pol_id
4247 AND NOT EXISTS
4248 ( SELECT '1'
4249 FROM OKL_POOL_STREAMS_UV pols
4250 WHERE poc.stm_id = pols.stm_id
4251 AND pols.stream_say_code = 'CURR'
4252 AND pols.stream_active_yn = 'Y'
4253 -- mvasudev, 02/06/2004
4254 --AND pols.stream_element_due_date > SYSDATE
4255 )
4256 -- cklee 04/10/2003 never reconcile historical data
4257 AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
4258 ;
4259 -- cklee 04/10/2003 never reconcile historical data
4260
4261
4262 CURSOR l_okl_update_khr_dates_csr(p_pol_id IN NUMBER)
4263 IS
4264 SELECT poc.id, chrb.end_date
4265 FROM okl_pool_contents poc,
4266 okc_k_headers_b chrb
4267 WHERE poc.pol_id = p_pol_id
4268 AND poc.khr_id = chrb.id
4269 -- cklee 04/10/2003 never reconcile historical data
4270 AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
4271 -- cklee 04/10/2003 never reconcile historical data
4272 -- mvasudev,02/06/2004
4273 AND poc.streams_to_date <> chrb.end_date+1;
4274
4275 CURSOR l_okl_update_dates_csr(p_pol_id IN NUMBER)
4276 IS
4277 SELECT poc.id, poc.stm_id
4278 FROM okl_pool_contents poc
4279 ,okl_streams stmb
4280 WHERE poc.pol_id = p_pol_id
4281 AND poc.stm_id = stmb.id
4282 AND stmb.say_code = 'CURR'
4283 AND stmb.active_yn = 'Y'
4284 -- cklee 04/10/2003 never reconcile historical data
4285 AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
4286 -- cklee 04/10/2003 never reconcile historical data
4287 AND TRUNC(poc.streams_from_date) <> ( SELECT TRUNC(MIN(selb.stream_element_date))
4288 FROM okl_strm_elements selb
4289 WHERE selb.stm_id = stmb.id
4290 AND selb.date_billed IS NULL
4291 AND selb.amount <> 0 -- vsgandhi : Bug 13997160
4292 -- mvasudev, 02/06/2004
4293 --AND selb.stream_element_date > SYSDATE
4294 );
4295
4296 /* sosharma 26-Dec-2007
4297 New cursors to reconcile Transient pool contents
4298 Start Changes
4299 */
4300 CURSOR evg_rent_strms_pend_csr (p_pol_id IN NUMBER)
4301 IS
4302 -- to remove all the rent strms if the contract turns to EVERGREEN--
4303 SELECT poc.id
4304 FROM okl_pool_contents poc,
4305 okl_pools pol,
4306 okl_strm_type_b sty,
4307 okc_k_headers_b CHR
4308 WHERE pol.id = p_pol_id
4309 AND pol.id = poc.pol_id
4310 AND poc.sty_id = sty.id
4311 AND sty.STREAM_TYPE_SUBCLASS ='RENT'
4312 AND poc.KHR_ID = CHR.id
4313 AND CHR.sts_code ='EVERGREEN'
4314 AND poc.status_code = G_POC_STS_PENDING ;
4315 --fmiao 21-OCT-2005 bug 4775555 --
4316
4317 CURSOR l_okl_invalid_khr_pend_csr(p_pol_id IN NUMBER)
4318 IS
4319 -- to remove all contents pointing to invalid contracts
4320 SELECT poc.id
4321 FROM okl_pool_contents poc,
4322 okc_k_headers_b chrb,
4323 okl_k_headers khrb
4324 WHERE poc.pol_id = p_pol_id
4325 AND poc.khr_id = chrb.id
4326 AND poc.khr_id = khrb.id
4327 -- cklee 04/10/2003 never reconcile historical data
4328 AND poc.status_code IN (G_POC_STS_PENDING)
4329 -- cklee 04/10/2003 never reconcile historical data
4330 AND (chrb.sts_code NOT IN ('BOOKED','EVERGREEN') OR khrb.assignable_yn <> 'Y');
4331
4332 CURSOR l_okl_delinq_khr_pend_csr(p_pol_id IN NUMBER)
4333 IS
4334 -- to remove all contents pointing to delinquent contracts
4335 SELECT poc.id
4336 FROM okl_pool_contents poc,
4337 iex_case_objects ico,
4338 iex_delinquencies_all del
4339 WHERE poc.pol_id = p_pol_id
4340 AND poc.khr_id = ico.object_id
4341 AND ico. cas_id = del.case_id
4342 -- cklee 04/10/2003 never reconcile historical data
4343 AND poc.status_code IN (G_POC_STS_PENDING)
4344 -- cklee 04/10/2003 never reconcile historical data
4345 AND del.status = 'DELINQUENT';
4346
4347 CURSOR l_okl_rev_khr_pend_csr(p_pol_id IN NUMBER)
4348 IS
4349 -- and to remove all contents pointing to contracts under modification
4350 SELECT poc.id
4351 FROM okl_pool_contents poc,
4352 okc_k_headers_b chrb
4353 WHERE poc.pol_id = p_pol_id
4354 AND poc.khr_id = chrb.id
4355 -- cklee 04/10/2003 never reconcile historical data
4356 AND poc.status_code IN (G_POC_STS_PENDING)
4357 -- cklee 04/10/2003 never reconcile historical data
4358 -- AND chrb.sts_code = 'BOOKED'
4359 AND EXISTS -- revision contract: rebook, split contract, reverse
4360 (SELECT '1'
4361 FROM okl_trx_contracts trxb
4362 WHERE trxb.tcn_type IN ('TRBK','SPLC','RVS')
4363 -- AND trxb.tsu_code = 'ENTERED'
4364 AND trxb.tsu_code NOT IN ('PROCESSED', 'ERROR','CANCELED') -- condition changes 01/13/2003 cklee
4365 AND trxb.khr_id = poc.khr_id
4366 )
4367 AND EXISTS -- split assets, split assets components
4368 (SELECT '1'
4369 FROM okl_txd_assets_v tdas,
4370 okl_txl_assets_b talb,
4371 okc_k_lines_b cleb
4372 WHERE cleb.id = talb.kle_id
4373 AND talb.ID = tdas.TAL_ID
4374 AND talb.TAL_TYPE = 'ALI'
4375 AND cleb.cle_id = poc.kle_id -- top line id
4376 AND talb.dnz_khr_id = poc.khr_id
4377 AND EXISTS (SELECT '1'
4378 FROM okl_trx_assets tas
4379 WHERE tas.id = talb.tas_id
4380 AND tas.tas_type = 'ALI'
4381 AND tas.tsu_code = 'PROCESSED')
4382 );
4383
4384 CURSOR l_okl_invalid_assets_pend_csr(p_pol_id IN NUMBER)
4385 IS
4386 -- and to remove all contents pointing to lease contracts
4387 -- that have atleast one invalid asset
4388 SELECT poc.id
4389 FROM okl_pool_contents poc
4390 WHERE poc.khr_id
4391 IN
4392 (SELECT poc.khr_id
4393 FROM okl_pool_contents poc,
4394 okc_k_lines_b cleb,
4395 okc_statuses_b stsb
4396 WHERE poc.pol_id = p_pol_id
4397 AND poc.kle_id = cleb.id
4398 AND cleb.sts_code = stsb.code
4399 -- cklee 04/10/2003 never reconcile historical data
4400 AND poc.status_code IN (G_POC_STS_PENDING)
4401 -- cklee 04/10/2003 never reconcile historical data
4402 AND stsb.ste_code IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
4403 );
4404
4405 -- v115.37 Fix
4406 CURSOR l_okl_invalid_streams_pend_csr(p_pol_id IN NUMBER)
4407 IS
4408 -- to remove all contents pointing to inactive streams
4409 --- or assets that do not have streams
4410 SELECT poc.id
4411 FROM okl_pool_contents poc
4412 WHERE poc.pol_id = p_pol_id
4413 AND NOT EXISTS
4414 ( SELECT '1'
4415 FROM OKL_POOL_STREAMS_UV pols
4416 WHERE poc.stm_id = pols.stm_id
4417 AND pols.stream_say_code = 'CURR'
4418 AND pols.stream_active_yn = 'Y'
4419 -- mvasudev, 02/06/2004
4420 --AND pols.stream_element_due_date > SYSDATE
4421 )
4422 -- cklee 04/10/2003 never reconcile historical data
4423 AND poc.status_code IN (G_POC_STS_PENDING)
4424 ;
4425 -- cklee 04/10/2003 never reconcile historical data
4426
4427
4428 CURSOR l_okl_update_khr_dts_pend_csr(p_pol_id IN NUMBER)
4429 IS
4430 SELECT poc.id, chrb.end_date
4431 FROM okl_pool_contents poc,
4432 okc_k_headers_b chrb
4433 WHERE poc.pol_id = p_pol_id
4434 AND poc.khr_id = chrb.id
4435 -- cklee 04/10/2003 never reconcile historical data
4436 AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
4437 -- cklee 04/10/2003 never reconcile historical data
4438 -- mvasudev,02/06/2004
4439 AND poc.streams_to_date <> chrb.end_date+1;
4440
4441 CURSOR l_okl_update_dates_pend_csr(p_pol_id IN NUMBER)
4442 IS
4443 SELECT poc.id, poc.stm_id
4444 FROM okl_pool_contents poc
4445 ,okl_streams stmb
4446 WHERE poc.pol_id = p_pol_id
4447 AND poc.stm_id = stmb.id
4448 AND stmb.say_code = 'CURR'
4449 AND stmb.active_yn = 'Y'
4450 -- cklee 04/10/2003 never reconcile historical data
4451 AND poc.status_code IN (G_POC_STS_PENDING)
4452 -- cklee 04/10/2003 never reconcile historical data
4453 AND TRUNC(poc.streams_from_date) <> ( SELECT TRUNC(MIN(selb.stream_element_date))
4454 FROM okl_strm_elements selb
4455 WHERE selb.stm_id = stmb.id
4456 AND selb.date_billed IS NULL
4457 AND selb.amount <> 0 -- vsgandhi : Bug 13997160
4458 -- mvasudev, 02/06/2004
4459 --AND selb.stream_element_date > SYSDATE
4460 );
4461 /* End changes */
4462
4463
4464 CURSOR l_okl_valid_dates_csr(p_stm_id IN NUMBER)
4465 IS
4466 SELECT MIN(selb.stream_element_date)
4467 FROM okl_strm_elements selb
4468 ,okl_streams stmb
4469 WHERE selb.stm_id = stmb.id
4470 AND selb.date_billed IS NULL
4471 AND selb.amount <> 0 -- vsgandhi : Bug 13997160
4472 -- mvasudev, 02/06/2004
4473 --AND selb.stream_element_date > SYSDATE
4474 AND stmb.id = p_stm_id
4475 AND stmb.say_code = 'CURR'
4476 AND stmb.active_yn = 'Y';
4477
4478 l_api_name CONSTANT VARCHAR2(30) := 'reconcile_contents';
4479 l_api_version CONSTANT NUMBER := 1.0;
4480 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
4481 lp_pocv_tbl pocv_tbl_type;
4482 lx_pocv_tbl pocv_tbl_type;
4483 lp_polv_rec polv_rec_type;
4484 lx_polv_rec polv_rec_type;
4485
4486 i NUMBER := 1;
4487 l_date DATE;
4488
4489 BEGIN
4490
4491 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
4492 p_pkg_name => G_PKG_NAME,
4493 p_init_msg_list => p_init_msg_list,
4494 l_api_version => l_api_version,
4495 p_api_version => p_api_version,
4496 p_api_type => G_API_TYPE,
4497 x_return_status => l_return_status);
4498 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
4499 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4500 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
4501 RAISE G_EXCEPTION_ERROR;
4502 END IF;
4503
4504 x_reconciled := Okl_Api.G_FALSE;
4505
4506
4507 /*sosharma 26-Dec-2007
4508 Bifurcating further processing based on the value of p_mode
4509 Start Changes
4510 */
4511 IF p_mode IS NULL THEN
4512 --fmiao 21-OCT-2005 bug 4775555 --
4513 i := 1;
4514 FOR evg_rent_strms_rec IN evg_rent_strms_csr(p_pol_id)
4515 LOOP
4516 lp_pocv_tbl(i).id := evg_rent_strms_rec.id;
4517 lp_pocv_tbl(i).pol_id := p_pol_id;
4518 i := i + 1;
4519 END LOOP;
4520
4521 IF lp_pocv_tbl.COUNT > 0 THEN
4522
4523 -- Remove rent streams for EVERGREEN contract from pool--
4524 Okl_Pool_Pvt.delete_pool_contents(
4525 p_api_version => p_api_version,
4526 p_init_msg_list => p_init_msg_list,
4527 x_return_status => l_return_status,
4528 x_msg_count => x_msg_count,
4529 x_msg_data => x_msg_data,
4530 p_pocv_tbl => lp_pocv_tbl);
4531
4532 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4533 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4534 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4535 RAISE Okl_Api.G_EXCEPTION_ERROR;
4536 END IF;
4537
4538 x_reconciled := Okl_Api.G_TRUE;
4539 END IF;
4540 lp_pocv_tbl.DELETE; -- clear
4541 --fmiao 21-OCT-2005 bug 4775555 --
4542
4543 i := 1; -- initialize
4544 FOR l_okl_invalid_khr IN l_okl_invalid_khr_csr(p_pol_id)
4545 LOOP
4546 lp_pocv_tbl(i).id := l_okl_invalid_khr.id;
4547 lp_pocv_tbl(i).pol_id := p_pol_id;
4548 i := i + 1;
4549 END LOOP;
4550
4551 IF lp_pocv_tbl.COUNT > 0 THEN
4552
4553 -- Remove the invalid khrs
4554 Okl_Pool_Pvt.delete_pool_contents(
4555 p_api_version => p_api_version,
4556 p_init_msg_list => p_init_msg_list,
4557 x_return_status => l_return_status,
4558 x_msg_count => x_msg_count,
4559 x_msg_data => x_msg_data,
4560 p_pocv_tbl => lp_pocv_tbl);
4561
4562 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4563 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4564 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4565 RAISE Okl_Api.G_EXCEPTION_ERROR;
4566 END IF;
4567
4568 x_reconciled := Okl_Api.G_TRUE;
4569 END IF;
4570
4571
4572 lp_pocv_tbl.DELETE; -- clear
4573 i := 1; -- initialize
4574 FOR l_okl_delinq_khr IN l_okl_delinq_khr_csr(p_pol_id)
4575 LOOP
4576 lp_pocv_tbl(i).id := l_okl_delinq_khr.id;
4577 lp_pocv_tbl(i).pol_id := p_pol_id;
4578 i := i + 1;
4579 END LOOP;
4580
4581 IF lp_pocv_tbl.COUNT > 0 THEN
4582
4583 -- Remove the delinquent khrs
4584 Okl_Pool_Pvt.delete_pool_contents(
4585 p_api_version => p_api_version,
4586 p_init_msg_list => p_init_msg_list,
4587 x_return_status => l_return_status,
4588 x_msg_count => x_msg_count,
4589 x_msg_data => x_msg_data,
4590 p_pocv_tbl => lp_pocv_tbl);
4591
4592 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4593 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4594 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4595 RAISE Okl_Api.G_EXCEPTION_ERROR;
4596 END IF;
4597
4598 x_reconciled := Okl_Api.G_TRUE;
4599 END IF;
4600
4601
4602
4603
4604
4605 lp_pocv_tbl.DELETE; -- clear
4606
4607 i := 1; -- initialize
4608 FOR l_okl_rev_khr IN l_okl_rev_khr_csr(p_pol_id)
4609 LOOP
4610 lp_pocv_tbl(i).id := l_okl_rev_khr.id;
4611 lp_pocv_tbl(i).pol_id := p_pol_id;
4612 i := i + 1;
4613 END LOOP;
4614
4615 IF lp_pocv_tbl.COUNT > 0 THEN
4616
4617 -- Remove the rev khr rows
4618 Okl_Pool_Pvt.delete_pool_contents(
4619 p_api_version => p_api_version,
4620 p_init_msg_list => p_init_msg_list,
4621 x_return_status => l_return_status,
4622 x_msg_count => x_msg_count,
4623 x_msg_data => x_msg_data,
4624 p_pocv_tbl => lp_pocv_tbl);
4625
4626 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4627 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4628 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4629 RAISE Okl_Api.G_EXCEPTION_ERROR;
4630 END IF;
4631
4632 x_reconciled := Okl_Api.G_TRUE;
4633 END IF;
4634
4635
4636 lp_pocv_tbl.DELETE; -- clear
4637
4638 i := 1; -- initialize
4639 FOR l_okl_invalid_assets IN l_okl_invalid_assets_csr(p_pol_id)
4640 LOOP
4641 lp_pocv_tbl(i).id := l_okl_invalid_assets.id;
4642 lp_pocv_tbl(i).pol_id := p_pol_id;
4643 i := i + 1;
4644 END LOOP;
4645
4646 IF lp_pocv_tbl.COUNT > 0 THEN
4647
4648 -- Remove the rows of invalid_assets
4649 Okl_Pool_Pvt.delete_pool_contents(
4650 p_api_version => p_api_version,
4651 p_init_msg_list => p_init_msg_list,
4652 x_return_status => l_return_status,
4653 x_msg_count => x_msg_count,
4654 x_msg_data => x_msg_data,
4655 p_pocv_tbl => lp_pocv_tbl);
4656
4657 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4658 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4659 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4660 RAISE Okl_Api.G_EXCEPTION_ERROR;
4661 END IF;
4662
4663 x_reconciled := Okl_Api.G_TRUE;
4664 END IF;
4665
4666 lp_pocv_tbl.DELETE; -- clear
4667
4668 i := 1; -- initialize
4669 FOR l_okl_invalid_streams IN l_okl_invalid_streams_csr(p_pol_id)
4670 LOOP
4671 lp_pocv_tbl(i).id := l_okl_invalid_streams.id;
4672 lp_pocv_tbl(i).pol_id := p_pol_id;
4673 i := i + 1;
4674 END LOOP;
4675
4676 IF lp_pocv_tbl.COUNT > 0 THEN
4677
4678 -- Remove the rows of invalid_streams
4679 Okl_Pool_Pvt.delete_pool_contents(
4680 p_api_version => p_api_version,
4681 p_init_msg_list => p_init_msg_list,
4682 x_return_status => l_return_status,
4683 x_msg_count => x_msg_count,
4684 x_msg_data => x_msg_data,
4685 p_pocv_tbl => lp_pocv_tbl);
4686
4687 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4688 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4689 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4690 RAISE Okl_Api.G_EXCEPTION_ERROR;
4691 END IF;
4692 x_reconciled := Okl_Api.G_TRUE;
4693 END IF;
4694
4695 lp_pocv_tbl.DELETE; -- clear the contents to get updatable rows
4696
4697 -- Collect all the records that need to be updated for Contract dates
4698 i := 1;
4699 FOR l_okl_update_khr_dates IN l_okl_update_khr_dates_csr(p_pol_id)
4700 LOOP
4701
4702 lp_pocv_tbl(i).id := l_okl_update_khr_dates.id;
4703
4704 lp_pocv_tbl(i).pol_id := p_pol_id;
4705 -- mvasudev, 02/06/2004
4706 lp_pocv_tbl(i).streams_to_date := l_okl_update_khr_dates.end_date+1;
4707 i := i + 1;
4708 END LOOP;
4709
4710 IF lp_pocv_tbl.COUNT > 0 THEN
4711 -- Update the rows with correct stream dates
4712 Okl_Pool_Pvt.update_pool_contents(
4713 p_api_version => p_api_version,
4714 p_init_msg_list => p_init_msg_list,
4715 x_return_status => l_return_status,
4716 x_msg_count => x_msg_count,
4717 x_msg_data => x_msg_data,
4718 p_pocv_tbl => lp_pocv_tbl,
4719 x_pocv_tbl => lx_pocv_tbl);
4720
4721 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4722 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4723 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4724 RAISE Okl_Api.G_EXCEPTION_ERROR;
4725 END IF;
4726
4727 x_reconciled := Okl_Api.G_TRUE;
4728 END IF;
4729
4730 lp_pocv_tbl.DELETE; -- clear the contents to get new updatable rows
4731
4732 -- Collect all the records that need to be updated for Stream dates
4733 i := 1;
4734 FOR l_okl_update_dates IN l_okl_update_dates_csr(p_pol_id)
4735 LOOP
4736
4737 l_date := NULL;
4738 OPEN l_okl_valid_dates_csr(l_okl_update_dates.stm_id);
4739 FETCH l_okl_valid_dates_csr INTO l_date;
4740 CLOSE l_okl_valid_dates_csr;
4741
4742 lp_pocv_tbl(i).id := l_okl_update_dates.id;
4743 lp_pocv_tbl(i).pol_id := p_pol_id;
4744 lp_pocv_tbl(i).streams_from_date := l_date;
4745
4746 i := i + 1;
4747 END LOOP;
4748 IF lp_pocv_tbl.COUNT > 0 THEN
4749 -- Update the rows with correct stream dates
4750 Okl_Pool_Pvt.update_pool_contents(
4751 p_api_version => p_api_version,
4752 p_init_msg_list => p_init_msg_list,
4753 x_return_status => l_return_status,
4754 x_msg_count => x_msg_count,
4755 x_msg_data => x_msg_data,
4756 p_pocv_tbl => lp_pocv_tbl,
4757 x_pocv_tbl => lx_pocv_tbl);
4758
4759 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4760 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4761 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4762 RAISE Okl_Api.G_EXCEPTION_ERROR;
4763 END IF;
4764
4765 x_reconciled := Okl_Api.G_TRUE;
4766 END IF;
4767
4768 ELSE
4769
4770 i := 1;
4771 FOR evg_rent_strms_rec IN evg_rent_strms_pend_csr(p_pol_id)
4772 LOOP
4773 lp_pocv_tbl(i).id := evg_rent_strms_rec.id;
4774 lp_pocv_tbl(i).pol_id := p_pol_id;
4775 i := i + 1;
4776 END LOOP;
4777
4778 IF lp_pocv_tbl.COUNT > 0 THEN
4779
4780 -- Remove rent streams for EVERGREEN contract from pool--
4781 Okl_Pool_Pvt.delete_pool_contents(
4782 p_api_version => p_api_version,
4783 p_init_msg_list => p_init_msg_list,
4784 x_return_status => l_return_status,
4785 x_msg_count => x_msg_count,
4786 x_msg_data => x_msg_data,
4787 p_pocv_tbl => lp_pocv_tbl);
4788
4789 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4790 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4791 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4792 RAISE Okl_Api.G_EXCEPTION_ERROR;
4793 END IF;
4794
4795 x_reconciled := Okl_Api.G_TRUE;
4796 END IF;
4797 lp_pocv_tbl.DELETE; -- clear
4798 --fmiao 21-OCT-2005 bug 4775555 --
4799
4800 i := 1; -- initialize
4801 FOR l_okl_invalid_khr IN l_okl_invalid_khr_pend_csr(p_pol_id)
4802 LOOP
4803 lp_pocv_tbl(i).id := l_okl_invalid_khr.id;
4804 lp_pocv_tbl(i).pol_id := p_pol_id;
4805 i := i + 1;
4806 END LOOP;
4807
4808 IF lp_pocv_tbl.COUNT > 0 THEN
4809
4810 -- Remove the invalid khrs
4811 Okl_Pool_Pvt.delete_pool_contents(
4812 p_api_version => p_api_version,
4813 p_init_msg_list => p_init_msg_list,
4814 x_return_status => l_return_status,
4815 x_msg_count => x_msg_count,
4816 x_msg_data => x_msg_data,
4817 p_pocv_tbl => lp_pocv_tbl);
4818
4819 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4820 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4821 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4822 RAISE Okl_Api.G_EXCEPTION_ERROR;
4823 END IF;
4824
4825 x_reconciled := Okl_Api.G_TRUE;
4826 END IF;
4827
4828
4829 lp_pocv_tbl.DELETE; -- clear
4830 i := 1; -- initialize
4831 FOR l_okl_delinq_khr IN l_okl_delinq_khr_pend_csr(p_pol_id)
4832 LOOP
4833 lp_pocv_tbl(i).id := l_okl_delinq_khr.id;
4834 lp_pocv_tbl(i).pol_id := p_pol_id;
4835 i := i + 1;
4836 END LOOP;
4837
4838 IF lp_pocv_tbl.COUNT > 0 THEN
4839
4840 -- Remove the delinquent khrs
4841 Okl_Pool_Pvt.delete_pool_contents(
4842 p_api_version => p_api_version,
4843 p_init_msg_list => p_init_msg_list,
4844 x_return_status => l_return_status,
4845 x_msg_count => x_msg_count,
4846 x_msg_data => x_msg_data,
4847 p_pocv_tbl => lp_pocv_tbl);
4848
4849 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4850 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4851 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4852 RAISE Okl_Api.G_EXCEPTION_ERROR;
4853 END IF;
4854
4855 x_reconciled := Okl_Api.G_TRUE;
4856 END IF;
4857
4858
4859
4860
4861
4862 lp_pocv_tbl.DELETE; -- clear
4863
4864 i := 1; -- initialize
4865 FOR l_okl_rev_khr IN l_okl_rev_khr_pend_csr(p_pol_id)
4866 LOOP
4867 lp_pocv_tbl(i).id := l_okl_rev_khr.id;
4868 lp_pocv_tbl(i).pol_id := p_pol_id;
4869 i := i + 1;
4870 END LOOP;
4871
4872 IF lp_pocv_tbl.COUNT > 0 THEN
4873
4874 -- Remove the rev khr rows
4875 Okl_Pool_Pvt.delete_pool_contents(
4876 p_api_version => p_api_version,
4877 p_init_msg_list => p_init_msg_list,
4878 x_return_status => l_return_status,
4879 x_msg_count => x_msg_count,
4880 x_msg_data => x_msg_data,
4881 p_pocv_tbl => lp_pocv_tbl);
4882
4883 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4884 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4885 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4886 RAISE Okl_Api.G_EXCEPTION_ERROR;
4887 END IF;
4888
4889 x_reconciled := Okl_Api.G_TRUE;
4890 END IF;
4891
4892
4893 lp_pocv_tbl.DELETE; -- clear
4894
4895 i := 1; -- initialize
4896 FOR l_okl_invalid_assets IN l_okl_invalid_assets_pend_csr(p_pol_id)
4897 LOOP
4898 lp_pocv_tbl(i).id := l_okl_invalid_assets.id;
4899 lp_pocv_tbl(i).pol_id := p_pol_id;
4900 i := i + 1;
4901 END LOOP;
4902
4903 IF lp_pocv_tbl.COUNT > 0 THEN
4904
4905 -- Remove the rows of invalid_assets
4906 Okl_Pool_Pvt.delete_pool_contents(
4907 p_api_version => p_api_version,
4908 p_init_msg_list => p_init_msg_list,
4909 x_return_status => l_return_status,
4910 x_msg_count => x_msg_count,
4911 x_msg_data => x_msg_data,
4912 p_pocv_tbl => lp_pocv_tbl);
4913
4914 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4915 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4916 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4917 RAISE Okl_Api.G_EXCEPTION_ERROR;
4918 END IF;
4919
4920 x_reconciled := Okl_Api.G_TRUE;
4921 END IF;
4922
4923 lp_pocv_tbl.DELETE; -- clear
4924
4925 i := 1; -- initialize
4926 FOR l_okl_invalid_streams IN l_okl_invalid_streams_pend_csr(p_pol_id)
4927 LOOP
4928 lp_pocv_tbl(i).id := l_okl_invalid_streams.id;
4929 lp_pocv_tbl(i).pol_id := p_pol_id;
4930 i := i + 1;
4931 END LOOP;
4932
4933 IF lp_pocv_tbl.COUNT > 0 THEN
4934
4935 -- Remove the rows of invalid_streams
4936 Okl_Pool_Pvt.delete_pool_contents(
4937 p_api_version => p_api_version,
4938 p_init_msg_list => p_init_msg_list,
4939 x_return_status => l_return_status,
4940 x_msg_count => x_msg_count,
4941 x_msg_data => x_msg_data,
4942 p_pocv_tbl => lp_pocv_tbl);
4943
4944 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4945 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4946 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4947 RAISE Okl_Api.G_EXCEPTION_ERROR;
4948 END IF;
4949 x_reconciled := Okl_Api.G_TRUE;
4950 END IF;
4951
4952 lp_pocv_tbl.DELETE; -- clear the contents to get updatable rows
4953
4954 -- Collect all the records that need to be updated for Contract dates
4955 i := 1;
4956 FOR l_okl_update_khr_dates IN l_okl_update_khr_dts_pend_csr(p_pol_id)
4957 LOOP
4958
4959 lp_pocv_tbl(i).id := l_okl_update_khr_dates.id;
4960
4961 lp_pocv_tbl(i).pol_id := p_pol_id;
4962 -- mvasudev, 02/06/2004
4963 lp_pocv_tbl(i).streams_to_date := l_okl_update_khr_dates.end_date+1;
4964 i := i + 1;
4965 END LOOP;
4966
4967 IF lp_pocv_tbl.COUNT > 0 THEN
4968 -- Update the rows with correct stream dates
4969 Okl_Pool_Pvt.update_pool_contents(
4970 p_api_version => p_api_version,
4971 p_init_msg_list => p_init_msg_list,
4972 x_return_status => l_return_status,
4973 x_msg_count => x_msg_count,
4974 x_msg_data => x_msg_data,
4975 p_pocv_tbl => lp_pocv_tbl,
4976 x_pocv_tbl => lx_pocv_tbl);
4977
4978 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4979 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4980 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4981 RAISE Okl_Api.G_EXCEPTION_ERROR;
4982 END IF;
4983
4984 x_reconciled := Okl_Api.G_TRUE;
4985 END IF;
4986
4987 lp_pocv_tbl.DELETE; -- clear the contents to get new updatable rows
4988
4989 -- Collect all the records that need to be updated for Stream dates
4990 i := 1;
4991 FOR l_okl_update_dates IN l_okl_update_dates_pend_csr(p_pol_id)
4992 LOOP
4993
4994 l_date := NULL;
4995 OPEN l_okl_valid_dates_csr(l_okl_update_dates.stm_id);
4996 FETCH l_okl_valid_dates_csr INTO l_date;
4997 CLOSE l_okl_valid_dates_csr;
4998
4999 lp_pocv_tbl(i).id := l_okl_update_dates.id;
5000 lp_pocv_tbl(i).pol_id := p_pol_id;
5001 lp_pocv_tbl(i).streams_from_date := l_date;
5002
5003 i := i + 1;
5004 END LOOP;
5005 IF lp_pocv_tbl.COUNT > 0 THEN
5006 -- Update the rows with correct stream dates
5007 Okl_Pool_Pvt.update_pool_contents(
5008 p_api_version => p_api_version,
5009 p_init_msg_list => p_init_msg_list,
5010 x_return_status => l_return_status,
5011 x_msg_count => x_msg_count,
5012 x_msg_data => x_msg_data,
5013 p_pocv_tbl => lp_pocv_tbl,
5014 x_pocv_tbl => lx_pocv_tbl);
5015
5016 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
5017 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
5018 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
5019 RAISE Okl_Api.G_EXCEPTION_ERROR;
5020 END IF;
5021
5022 x_reconciled := Okl_Api.G_TRUE;
5023 END IF;
5024 END IF;
5025 /*sosharma end changes*/
5026
5027 -- update date_last_reconciled
5028 lp_polv_rec.id := p_pol_id;
5029 lp_polv_rec.date_last_reconciled := SYSDATE;
5030 Okl_Pol_Pvt.update_row(
5031 p_api_version => p_api_version,
5032 p_init_msg_list => p_init_msg_list,
5033 x_return_status => l_return_status,
5034 x_msg_count => x_msg_count,
5035 x_msg_data => x_msg_data,
5036 p_polv_rec => lp_polv_rec,
5037 x_polv_rec => lx_polv_rec);
5038
5039 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
5040 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
5041
5042 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
5043 RAISE Okl_Api.G_EXCEPTION_ERROR;
5044 END IF;
5045
5046 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
5047 x_msg_data => x_msg_data);
5048
5049 x_return_status := l_return_status;
5050
5051 EXCEPTION
5052 WHEN G_EXCEPTION_ERROR THEN
5053
5054 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
5055 p_pkg_name => G_PKG_NAME,
5056 p_exc_name => G_EXC_NAME_ERROR,
5057 x_msg_count => x_msg_count,
5058 x_msg_data => x_msg_data,
5059 p_api_type => G_API_TYPE);
5060 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
5061
5062 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
5063 p_pkg_name => G_PKG_NAME,
5064 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
5065 x_msg_count => x_msg_count,
5066 x_msg_data => x_msg_data,
5067 p_api_type => G_API_TYPE);
5068 WHEN OTHERS THEN
5069
5070 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
5071 p_pkg_name => G_PKG_NAME,
5072 p_exc_name => G_EXC_NAME_OTHERS,
5073 x_msg_count => x_msg_count,
5074 x_msg_data => x_msg_data,
5075 p_api_type => G_API_TYPE);
5076
5077 END reconcile_contents;
5078
5079 ----------------------------------------------------------------------------------
5080 -- Start of comments
5081 --
5082
5083 -- Procedure Name : update_pool_status_active
5084 -- Description : updates a pool header, and contents' status.
5085 -- Business Rules :
5086 -- Parameters :
5087 -- Version : 1.0
5088 -- End of comments
5089 ----------------------------------------------------------------------------------
5090 PROCEDURE update_pool_status_active(
5091 p_api_version IN NUMBER
5092 ,p_init_msg_list IN VARCHAR2
5093 ,x_return_status OUT NOCOPY VARCHAR2
5094 ,x_msg_count OUT NOCOPY NUMBER
5095 ,x_msg_data OUT NOCOPY VARCHAR2
5096 ,p_pol_id IN okl_pools.id%TYPE)
5097 IS
5098
5099
5100 l_api_name CONSTANT VARCHAR2(30) := 'update_pool_status_active';
5101 l_api_version CONSTANT NUMBER := 1.0;
5102
5103 BEGIN
5104 -- Set API savepoint
5105 SAVEPOINT update_pool_status_active_PVT;
5106
5107 -- Check for call compatibility
5108 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
5109 p_api_version,
5110 l_api_name,
5111 G_PKG_NAME ))
5112 THEN
5113 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
5114 END IF;
5115
5116 -- Initialize message list if requested
5117 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
5118 Fnd_Msg_Pub.initialize;
5119 END IF;
5120
5121 -- Initialize API status to success
5122 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
5123
5124
5125 /*** Begin API body ****************************************************/
5126
5127 update_pool_status(
5128 p_api_version => p_api_version,
5129 p_init_msg_list => p_init_msg_list,
5130 x_return_status => x_return_status,
5131 x_msg_count => x_msg_count,
5132 x_msg_data => x_msg_data,
5133 p_pool_status => G_POL_STS_ACTIVE,
5134 p_pol_id => p_pol_id);
5135
5136 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
5137 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
5138 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
5139 RAISE Okl_Api.G_EXCEPTION_ERROR;
5140 END IF;
5141
5142 /*** End API body ******************************************************/
5143
5144 -- Get message count and if count is 1, get message info
5145 Fnd_Msg_Pub.Count_And_Get
5146 (p_count => x_msg_count,
5147 p_data => x_msg_data);
5148
5149 EXCEPTION
5150 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
5151 ROLLBACK TO update_pool_status_active_PVT;
5152 x_return_status := Okl_Api.G_RET_STS_ERROR;
5153 Fnd_Msg_Pub.Count_And_Get
5154 (p_count => x_msg_count,
5155 p_data => x_msg_data);
5156
5157 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
5158
5159 ROLLBACK TO update_pool_status_active_PVT;
5160 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
5161 Fnd_Msg_Pub.Count_And_Get
5162 (p_count => x_msg_count,
5163 p_data => x_msg_data);
5164
5165 WHEN OTHERS THEN
5166 ROLLBACK TO update_pool_status_active_PVT;
5167 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
5168 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
5169 p_msg_name => G_UNEXPECTED_ERROR,
5170 p_token1 => G_SQLCODE_TOKEN,
5171 p_token1_value => SQLCODE,
5172 p_token2 => G_SQLERRM_TOKEN,
5173 p_token2_value => SQLERRM);
5174 Fnd_Msg_Pub.Count_And_Get
5175 (p_count => x_msg_count,
5176 p_data => x_msg_data);
5177
5178 END update_pool_status_active;
5179
5180
5181 ----------------------------------------------------------------------------------
5182 -- Start of comments
5183 --
5184 -- Procedure Name : update_pool_status_expired
5185 -- Description : updates a pool header, and contents' status.
5186 -- Business Rules :
5187 -- Parameters :
5188 -- Version : 1.0
5189 -- End of comments
5190 ----------------------------------------------------------------------------------
5191 PROCEDURE update_pool_status_expired(
5192 p_api_version IN NUMBER
5193 ,p_init_msg_list IN VARCHAR2
5194 ,x_return_status OUT NOCOPY VARCHAR2
5195 ,x_msg_count OUT NOCOPY NUMBER
5196 ,x_msg_data OUT NOCOPY VARCHAR2
5197 ,p_pol_id IN okl_pools.id%TYPE)
5198 IS
5199 l_api_name CONSTANT VARCHAR2(30) := 'update_pool_status_expired';
5200 l_api_version CONSTANT NUMBER := 1.0;
5201
5202 BEGIN
5203 -- Set API savepoint
5204 SAVEPOINT update_pool_status_expired_PVT;
5205
5206 -- Check for call compatibility
5207 IF (NOT Fnd_Api.Compatible_API_Call (l_api_version,
5208 p_api_version,
5209
5210 l_api_name,
5211 G_PKG_NAME ))
5212 THEN
5213
5214 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
5215 END IF;
5216
5217 -- Initialize message list if requested
5218 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
5219 Fnd_Msg_Pub.initialize;
5220 END IF;
5221
5222 -- Initialize API status to success
5223 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
5224
5225
5226 /*** Begin API body ****************************************************/
5227
5228 update_pool_status(
5229 p_api_version => p_api_version,
5230 p_init_msg_list => p_init_msg_list,
5231 x_return_status => x_return_status,
5232 x_msg_count => x_msg_count,
5233 x_msg_data => x_msg_data,
5234 p_pool_status => G_POL_STS_EXPIRED,
5235 p_pol_id => p_pol_id);
5236
5237 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
5238 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
5239 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
5240 RAISE Okl_Api.G_EXCEPTION_ERROR;
5241 END IF;
5242
5243 /*** End API body ******************************************************/
5244
5245 -- Get message count and if count is 1, get message info
5246 Fnd_Msg_Pub.Count_And_Get
5247 (p_count => x_msg_count,
5248 p_data => x_msg_data);
5249
5250 EXCEPTION
5251 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
5252 ROLLBACK TO update_pool_status_expired_PVT;
5253 x_return_status := Okl_Api.G_RET_STS_ERROR;
5254 Fnd_Msg_Pub.Count_And_Get
5255 (p_count => x_msg_count,
5256 p_data => x_msg_data);
5257
5258 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
5259 ROLLBACK TO update_pool_status_expired_PVT;
5260 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
5261 Fnd_Msg_Pub.Count_And_Get
5262 (p_count => x_msg_count,
5263 p_data => x_msg_data);
5264
5265 WHEN OTHERS THEN
5266 ROLLBACK TO update_pool_status_expired_PVT;
5267 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
5268 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
5269 p_msg_name => G_UNEXPECTED_ERROR,
5270 p_token1 => G_SQLCODE_TOKEN,
5271 p_token1_value => SQLCODE,
5272
5273 p_token2 => G_SQLERRM_TOKEN,
5274 p_token2_value => SQLERRM);
5275 Fnd_Msg_Pub.Count_And_Get
5276 (p_count => x_msg_count,
5277 p_data => x_msg_data);
5278
5279 END update_pool_status_expired;
5280
5281 ----------------------------------------------------------------------------------
5282 -- Start of comments
5283 -- mvasudev
5284 -- Procedure Name : get_total_stream_amount
5285 -- Description : Gets the Total Stream Amount for a given POC using the stm_id
5286 -- regardless of its status
5287 -- Business Rules :
5288 -- Parameters :
5289 -- Version : 1.0
5290 -- End of comments
5291 ----------------------------------------------------------------------------------
5292 PROCEDURE get_total_stream_amount(
5293 p_api_version IN NUMBER
5294 ,p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE
5295 ,x_return_status OUT NOCOPY VARCHAR2
5296 ,x_msg_count OUT NOCOPY NUMBER
5297 ,x_msg_data OUT NOCOPY VARCHAR2
5298 ,p_poc_id IN okl_pool_contents.id%TYPE
5299 ,p_stm_id IN okl_streams.id%TYPE
5300 ,x_amount OUT NOCOPY NUMBER
5301 )
5302 IS
5303
5304 l_api_name CONSTANT VARCHAR2(30) := 'get_total_stream_amount';
5305 l_api_version CONSTANT NUMBER := 1.0;
5306 i NUMBER;
5307 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
5308
5309 CURSOR l_okl_poc_stm_csr
5310 IS
5311 SELECT NVL(SUM(NVL(selb.amount,0)),0) amount
5312 FROM okl_streams stmb
5313 ,okl_strm_elements selb
5314 ,okl_pool_contents pocb
5315 ,okl_strm_type_v styv
5316 ,okc_k_headers_b chrb
5317 WHERE pocb.stm_id = stmb.id
5318 AND stmb.id = selb.stm_id
5319 AND pocb.id = p_poc_id
5320 -- Bug#3520846,mvasudev, 3/22/2004
5321 AND pocb.status_code = 'ACTIVE'
5322 AND selb.date_billed IS NULL
5323 /*
5324 ankushar --Bug 6594724: Unable to terminate Investor Agreement with Residual Streams
5325 Start changes
5326 */
5327 AND stmb.sty_id = styv.id
5328 AND pocb.khr_id = chrb.id
5329 AND ( selb.stream_element_date > SYSDATE OR
5330 ( styv.stream_type_subclass = 'RESIDUAL'
5331 and chrb.STS_CODE IN ('TERMINATED','EXPIRED')
5332 )
5333 )
5334 /* ankushar Bug 6594724
5335 End Changes
5336 */
5337
5338 -- end, mvasudev
5339 AND selb.stream_element_date
5340 BETWEEN pocb.streams_from_date AND NVL(pocb.streams_to_date,G_FINAL_DATE)
5341 ;
5342
5343 CURSOR l_okl_poc_csr
5344 IS
5345 SELECT NVL(SUM(NVL(selb.amount,0)),0) amount
5346 FROM okl_streams stmb
5347 ,okl_strm_elements selb
5348 ,okl_pool_contents pocb
5349 WHERE pocb.stm_id = stmb.id
5350 AND stmb.id = selb.stm_id
5351 AND pocb.id = p_poc_id
5352 AND selb.stream_element_date
5353 BETWEEN pocb.streams_from_date AND NVL(pocb.streams_to_date,G_FINAL_DATE)
5354 ;
5355
5356 BEGIN
5357
5358 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
5359 p_pkg_name => G_PKG_NAME,
5360 p_init_msg_list => p_init_msg_list,
5361 l_api_version => l_api_version,
5362 p_api_version => p_api_version,
5363 p_api_type => G_API_TYPE,
5364 x_return_status => l_return_status);
5365 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
5366 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
5367 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
5368 RAISE G_EXCEPTION_ERROR;
5369 END IF;
5370
5371 x_amount := 0;
5372
5373 -- mvasudev, 04/01/2004
5374 IF p_stm_id IS NOT NULL THEN
5375 FOR l_okl_poc_stm_rec IN l_okl_poc_stm_csr
5376 LOOP
5377 x_amount := x_amount + l_okl_poc_stm_rec.amount;
5378 END LOOP;
5379 ELSE
5380 FOR l_okl_poc_rec IN l_okl_poc_csr
5381 LOOP
5382 x_amount := x_amount + l_okl_poc_rec.amount;
5383 END LOOP;
5384 END IF;
5385
5386 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count
5387 ,x_msg_data => x_msg_data);
5388
5389 x_return_status := l_return_status;
5390
5391 EXCEPTION
5392 WHEN G_EXCEPTION_ERROR THEN
5393
5394 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
5395 p_pkg_name => G_PKG_NAME,
5396 p_exc_name => G_EXC_NAME_ERROR,
5397 x_msg_count => x_msg_count,
5398 x_msg_data => x_msg_data,
5399 p_api_type => G_API_TYPE);
5400 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
5401
5402 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
5403 p_pkg_name => G_PKG_NAME,
5404 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
5405 x_msg_count => x_msg_count,
5406 x_msg_data => x_msg_data,
5407 p_api_type => G_API_TYPE);
5408 WHEN OTHERS THEN
5409
5410 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
5411 p_pkg_name => G_PKG_NAME,
5412 p_exc_name => G_EXC_NAME_OTHERS,
5413 x_msg_count => x_msg_count,
5414 x_msg_data => x_msg_data,
5415 p_api_type => G_API_TYPE);
5416
5417 END get_total_stream_amount;
5418
5419 /* ankushar 26-JUL-2007
5420 Bug#6000531 To publish OKL_POOL_PUB added a new api validate_pool
5421 start changes
5422 */
5423 PROCEDURE validate_pool(
5424 p_api_version IN NUMBER
5425 ,p_init_msg_list IN VARCHAR2
5426 ,p_api_name IN VARCHAR2
5427 ,p_polv_rec IN polv_rec_type
5428 ,p_action IN VARCHAR2
5429 ,x_return_status OUT NOCOPY VARCHAR2
5430 ,x_msg_count OUT NOCOPY NUMBER
5431 ,x_msg_data OUT NOCOPY VARCHAR2
5432
5433 ) IS
5434 l_api_name CONSTANT VARCHAR2(30) := 'validate_pool';
5435 l_api_version CONSTANT NUMBER := 1.0;
5436 i NUMBER;
5437 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
5438 l_action VARCHAR2(40) := p_api_name;
5439 l_contract_number OKL_K_HEADERS_FULL_V.CONTRACT_NUMBER%type;
5440 l_polv_rec polv_rec_type := p_polv_rec;
5441
5442 CURSOR l_okl_pol_status_csr(p_pol_id IN NUMBER)
5443 IS
5444 SELECT status_code
5445 FROM okl_pools
5446 WHERE id = p_pol_id;
5447
5448 -- Cursor For OKL_CURRENCIES;
5449 CURSOR okl_fnd_curr_csr (p_code IN OKL_POOLS.currency_code%TYPE) IS
5450 SELECT '1'
5451 FROM FND_CURRENCIES_VL
5452 WHERE FND_CURRENCIES_VL.currency_code = currency_code;
5453
5454 l_dummy VARCHAR2(1) := '?';
5455 l_row_not_found BOOLEAN := FALSE;
5456 BEGIN
5457
5458 -- Initialize API status to success
5459 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
5460
5461 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
5462 p_pkg_name => G_PKG_NAME,
5463 p_init_msg_list => p_init_msg_list,
5464 l_api_version => l_api_version,
5465 p_api_version => p_api_version,
5466 p_api_type => G_API_TYPE,
5467 x_return_status => l_return_status);
5468 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
5469 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5470 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
5471 RAISE OKL_API.G_EXCEPTION_ERROR;
5472 END IF;
5473
5474 -- Initialize message list if requested
5475 IF (Fnd_Api.to_Boolean(p_init_msg_list)) THEN
5476 Fnd_Msg_Pub.initialize;
5477 END IF;
5478
5479 --update validation for pool id when the pool is in active or new status
5480 IF l_action = 'update_pool' THEN
5481 IF ((p_polv_rec.id is null) OR (p_polv_rec.id=OKL_API.G_MISS_NUM )) THEN
5482 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
5483 RAISE OKL_API.G_EXCEPTION_ERROR;
5484 END IF;
5485 END IF;
5486
5487 --following actions are permitted only when pool status is NEW
5488 IF l_action in ('add_pool_contents','cleanup_pool_contents') THEN
5489 IF ((p_polv_rec.id is null) OR (p_polv_rec.id=OKL_API.G_MISS_NUM )) THEN
5490 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
5491 RAISE OKL_API.G_EXCEPTION_ERROR;
5492 ELSE
5493 FOR l_okl_pol_status_rec IN l_okl_pol_status_csr(p_polv_rec.id)
5494 LOOP
5495
5496 IF l_okl_pol_status_rec.status_code <> Okl_Pool_Pvt.G_POL_STS_NEW THEN
5497 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME, p_msg_name => 'OKL_POOL_NO_MODIFY');
5498 RAISE OKL_API.G_EXCEPTION_ERROR;
5499 END IF;
5500 END LOOP;
5501 End if;
5502 END IF;
5503
5504 IF l_action = 'add_pool_contents' THEN
5505 -- validte whether the currency code entered is correct
5506 -- currency code must be entered by user while adding
5507 -- the pool contents
5508
5509 IF (p_polv_rec.currency_code = OKL_API.G_MISS_CHAR OR
5510 p_polv_rec.currency_code IS NULL)
5511 THEN
5512 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
5513 p_msg_name => 'OKL_REQUIRED_VALUE',
5514 p_token1 => 'COL_NAME',
5515 p_token1_value => 'currency_code');
5516 RAISE G_EXCEPTION_ERROR;
5517 END IF;
5518
5519 OPEN okl_fnd_curr_csr(p_polv_rec.currency_code);
5520
5521 FETCH okl_fnd_curr_csr INTO l_dummy;
5522 l_row_not_found := okl_fnd_curr_csr%NOTFOUND;
5523 CLOSE okl_fnd_curr_csr;
5524
5525 IF l_row_not_found THEN
5526 OKL_API.set_message(G_APP_NAME,
5527 OKL_API.G_INVALID_VALUE,
5528 OKL_API.G_COL_NAME_TOKEN,
5529 'currency_code');
5530 RAISE G_EXCEPTION_ERROR;
5531 END IF;
5532
5533 END IF; --l_action
5534
5535 -- Get message count and if count is 1, get message info
5536 Fnd_Msg_Pub.Count_And_Get
5537 (p_count => x_msg_count,
5538 p_data => x_msg_data);
5539
5540 EXCEPTION
5541 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
5542 x_return_status := Okl_Api.G_RET_STS_ERROR;
5543 Fnd_Msg_Pub.Count_And_Get
5544 (p_count => x_msg_count,
5545 p_data => x_msg_data);
5546
5547 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
5548 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
5549 Fnd_Msg_Pub.Count_And_Get
5550 (p_count => x_msg_count,
5551 p_data => x_msg_data);
5552
5553 WHEN OTHERS THEN
5554 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR ;
5555
5556 Okl_Api.Set_Message(p_app_name => G_APP_NAME,
5557 p_msg_name => G_UNEXPECTED_ERROR,
5558 p_token1 => G_SQLCODE_TOKEN,
5559 p_token1_value => SQLCODE,
5560 p_token2 => G_SQLERRM_TOKEN,
5561 p_token2_value => SQLERRM);
5562 Fnd_Msg_Pub.Count_And_Get
5563 (p_count => x_msg_count,
5564 p_data => x_msg_data);
5565
5566 END validate_pool;
5567 /* ankushar end changes 26-Jul-2007*/
5568
5569 END Okl_Pool_Pvt;