[Home] [Help]
PACKAGE BODY: APPS.IEX_CASE_INFO_PUB
Source
1 PACKAGE BODY IEX_CASE_INFO_PUB AS
2 /* $Header: iexcsinb.pls 120.3 2004/12/15 15:07:30 jsanju ship $ */
3
4 ---------------------------------------------------------------------
5 -- PROCEDURE get_total_rcvble_for_case
6 ---------------------------------------------------------------------
7 PG_DEBUG NUMBER ;
8
9
10 FUNCTION get_Amount_Overdue (p_case_id IN NUMBER) return NUMBER
11
12 IS
13 l_Amount_Overdue NUMBER := 0;
14 l_total_overdue NUMBER := 0;
15 l_contract_id NUMBER;
16 l_return_status VARCHAR2(5);
17 j NUMBER := 0;
18
19 Type refCur is Ref Cursor;
20 object_cur refCur;
21 vPLSQL varchar2(500);
22
23 BEGIN
24
25 if PG_DEBUG < 10 then
26 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
27 IEX_DEBUG_PUB.LogMessage('IEX_CASE_INFO_PUB.get_amount_overdue Start');
28 END IF;
29 end if;
30
31 vPLSQL := 'SELECT OBJECT_ID ' ||
32 'FROM IEX_CASE_OBJECTS ' ||
33 ' WHERE CAS_ID = ' || to_char(p_case_id) ||
34 ' AND OBJECT_CODE = ''CONTRACTS''' ||
35 ' AND ACTIVE_FLAG = ''Y''';
36 open object_cur for vPLSQL;
37 LOOP
38 j := j + 1;
39 fetch object_cur into l_contract_id;
40 exit when object_cur%NOTFOUND;
41 l_Amount_Overdue := 0;
42 l_return_status := OKL_CONTRACT_INFO.get_amount_past_due(l_contract_id, l_Amount_Overdue);
43 l_total_overdue := l_total_overdue + nvl(l_Amount_Overdue,0);
44
45 end loop;
46 close object_cur;
47
48 if PG_DEBUG < 10 then
49 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
50 IEX_DEBUG_PUB.LogMessage('Found ' || j || ' objects for case ' || p_case_id);
51 IEX_DEBUG_PUB.LogMessage('IEX_CASE_INFO_PUB.get_amount_overdue END');
52 END IF;
53 end if;
54
55 return nvl(l_total_overdue,0);
56
57 END;
58
59 PROCEDURE get_total_rcvble_for_case (
60 p_case_id IN NUMBER,
61 x_total_amt OUT NOCOPY NUMBER,
62 x_return_status OUT NOCOPY VARCHAR2)
63 IS
64 l_api_name VARCHAR2(30);
65 l_msg_count NUMBER;
66 l_msg_data VARCHAR2(2000);
67 l_contract_id NUMBER;
68
69 l_api_version NUMBER ;
70 l_total_amt NUMBER ;
71 l_rcvble_amt NUMBER ;
72
73 CURSOR c_case(l_case_id NUMBER) IS
74 SELECT OBJECT_ID
75 FROM IEX_CASE_OBJECTS
76 WHERE CAS_ID = l_case_id
77 AND OBJECT_CODE = 'CONTRACTS'
78 AND ACTIVE_FLAG = 'Y';
79
80 BEGIN
81
82 -- Initialize API return status to SUCCESS
83 x_return_status := FND_API.G_RET_STS_SUCCESS;
84
85 l_api_version := 2.0;
86 l_total_amt := 0;
87 l_rcvble_amt := 0;
88 l_api_name := 'get_total_rcvble_for_case';
89 if PG_DEBUG < 10 then
90 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
91 IEX_DEBUG_PUB.LogMessage('PUB:'||G_PKG_NAME||'.'||l_api_name||' Start');
92 END IF;
93 end if;
94
95 OPEN C_CASE(p_case_id);
96 LOOP
97 FETCH C_CASE INTO l_contract_id;
98 EXIT WHEN NOT C_CASE%FOUND;
99
100 BEGIN
101 l_rcvble_amt := 0;
102 x_return_status := OKL_CONTRACT_INFO.get_outstanding_rcvble(l_contract_id, l_rcvble_amt);
103 exception when others then
104 null;
105 end;
106
107 if PG_DEBUG < 10 then
108 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
109 IEX_DEBUG_PUB.LogMessage('ContractID: ' || l_contract_id || ' Recvble Amt: ' || l_rcvble_amt);
110 END IF;
111 end if;
112
113 l_total_amt := l_total_amt + nvl(l_rcvble_amt,0);
114
115 END LOOP;
116 CLOSE C_CASE;
117
118 x_total_amt := NVL(l_total_amt, 0);
119
120 if PG_DEBUG < 10 then
121 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
122 IEX_DEBUG_PUB.LogMessage('Total Case Overdue Amount $'||x_total_amt);
123 END IF;
124 end if;
125
126 EXCEPTION
127 WHEN FND_API.G_EXC_ERROR THEN
128 x_return_status := FND_API.G_RET_STS_ERROR;
129 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
130 if PG_DEBUG < 10 then
131 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
132 IEX_DEBUG_PUB.LogMessage('Exec Error ' || sqlerrm);
133 END IF;
134 end if;
135
136 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
138 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
139 if PG_DEBUG < 10 then
140 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
141 IEX_DEBUG_PUB.LogMessage('Unexec Error ' || sqlerrm);
142 END IF;
143 end if;
144
145 WHEN OTHERS THEN
146 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
147 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
148 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
149 END IF;
150 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
151 if PG_DEBUG < 10 then
152 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
153 IEX_DEBUG_PUB.LogMessage('Other Error ' || sqlerrm);
154 END IF;
155 end if;
156
157 END get_total_rcvble_for_case;
158
159
160
161
162 FUNCTION get_total_rcvble_for_case_fn (p_case_id IN NUMBER) return NUMBER
163 IS
164 l_amount Number ;
165 l_return_status varchar2(300) ;
166 Begin
167 get_total_rcvble_for_case(p_case_id, l_amount, l_return_status) ;
168 return l_amount ;
169 EXCEPTION
170 WHEN OTHERS then
171 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
172 IEX_DEBUG_PUB.LogMessage('Exec Error - get_total_rcvble_for_case_fn ' || SQLCODE || sqlerrm);
173 END IF;
174 End ;
175
176
177 ---------------------------------------------------------------------
178 -- PROCEDURE get total net book value for a case
179 ---------------------------------------------------------------------
180 PROCEDURE get_total_net_book_value (
181 p_case_id IN NUMBER,
182 x_total_amt OUT NOCOPY NUMBER,
183 x_return_status OUT NOCOPY VARCHAR2)
184 IS
185 l_api_version NUMBER ;
186 l_api_name VARCHAR2(30);
187 l_msg_count NUMBER;
188 l_msg_data VARCHAR2(2000);
189 l_contract_id NUMBER;
190
191 l_total_amt NUMBER(15,2);
192 l_nbook_amt NUMBER ;
193
194 CURSOR c_case(l_case_id NUMBER) IS
195 SELECT OBJECT_ID
196 FROM IEX_CASE_OBJECTS
197 WHERE CAS_ID = l_case_id
198 AND OBJECT_CODE = 'CONTRACTS'
199 AND ACTIVE_FLAG = 'Y';
200
201 BEGIN
202
203 -- Initialize API return status to SUCCESS
204 x_return_status := FND_API.G_RET_STS_SUCCESS;
205
206 l_total_amt := 0;
207 l_api_name := 'get_total_net_book_value';
208 l_api_version := 2.0;
209 l_nbook_amt := 0;
210 if PG_DEBUG < 10 then
211 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
212 IEX_DEBUG_PUB.LogMessage('PUB:'||G_PKG_NAME||'.'||l_api_name||' Start');
213 END IF;
214 end if;
215
216 OPEN C_CASE(p_case_id);
217 LOOP
218 FETCH C_CASE INTO l_contract_id;
219 EXIT WHEN NOT C_CASE%FOUND;
220
221 l_nbook_amt := 0;
222 x_return_status := OKL_CONTRACT_INFO.get_net_book_value(l_contract_id, l_nbook_amt);
223 if PG_DEBUG < 10 then
224 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
225 IEX_DEBUG_PUB.LogMessage('ContractID: ' || l_contract_id || ' Net Book: ' || l_nbook_amt);
226 END IF;
227 end if;
228
229 IF (x_return_status = fnd_api.G_RET_STS_UNEXP_ERROR) THEN
230 RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
231 ELSIF (x_return_status = fnd_api.G_RET_STS_ERROR) THEN
232 RAISE fnd_api.G_EXC_ERROR;
233 END IF;
234 l_total_amt := l_total_amt + l_nbook_amt;
235
236 END LOOP;
237 CLOSE C_CASE;
238
239 x_total_amt := NVL(l_total_amt, 0);
240
241 if PG_DEBUG < 10 then
242 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
243 IEX_DEBUG_PUB.LogMessage('--------->Total Case Net Book Amount $'||x_total_amt);
244 END IF;
245 end if;
246
247 EXCEPTION
248 WHEN FND_API.G_EXC_ERROR THEN
249 x_return_status := FND_API.G_RET_STS_ERROR;
250 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
251 if PG_DEBUG < 10 then
252 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
253 IEX_DEBUG_PUB.LogMessage('Exec Error ' || sqlerrm);
254 END IF;
255 end if;
256
257 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
259 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
260 if PG_DEBUG < 10 then
261 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
262 IEX_DEBUG_PUB.LogMessage('Unexec Error ' || sqlerrm);
263 END IF;
264 end if;
265
266 WHEN OTHERS THEN
267 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
268 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
269 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
270 END IF;
271 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
272 if PG_DEBUG < 10 then
273 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
274 IEX_DEBUG_PUB.LogMessage('Other Error ' || sqlerrm);
275 END IF;
276 end if;
277
278 END get_total_net_book_value;
279
280 ---------------------------------------------------------------------
281 -- PROCEDURE get total contract Original Equipment Cost for a case
282 ---------------------------------------------------------------------
283 PROCEDURE get_contract_oec (
284 p_case_id IN NUMBER,
285 x_total_amt OUT NOCOPY NUMBER,
286 x_return_status OUT NOCOPY VARCHAR2)
287 IS
288 l_msg_count NUMBER;
289 l_msg_data VARCHAR2(2000);
290 l_contract_id NUMBER;
291
292 l_total_amt NUMBER ;
293 l_oec_amt NUMBER ;
294 l_api_version NUMBER ;
295 l_api_name VARCHAR2(30);
296
297 CURSOR c_case(l_case_id NUMBER) IS
298 SELECT OBJECT_ID
299 FROM IEX_CASE_OBJECTS
300 WHERE CAS_ID = l_case_id
301 AND OBJECT_CODE = 'CONTRACTS'
302 AND ACTIVE_FLAG = 'Y';
303
304 BEGIN
305
306 -- Initialize API return status to SUCCESS
307 x_return_status := FND_API.G_RET_STS_SUCCESS;
308
309 l_total_amt := 0;
310 l_oec_amt := 0;
311 l_api_version := 2.0;
312 l_api_name := 'get_contract_oec';
313
314 if PG_DEBUG < 10 then
315 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
316 IEX_DEBUG_PUB.LogMessage('PUB:'||G_PKG_NAME||'.'||l_api_name||' Start');
317 END IF;
318 end if;
319
320 OPEN C_CASE(p_case_id);
321 LOOP
322 FETCH C_CASE INTO l_contract_id;
323 EXIT WHEN NOT C_CASE%FOUND;
324
325 --jsanju comment for bug #-2605083
326 -- raverma 04252003 uncomment for bug#2924455
327 --( l_oec_amt is intialized to zero)
328 l_oec_amt := 0;
329 l_oec_amt := OKL_SEEDED_FUNCTIONS_PVT.contract_oec(p_chr_id => l_contract_id,
330 p_line_id => null);
331 if PG_DEBUG < 10 then
332 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
333 IEX_DEBUG_PUB.LogMessage('ContractID: ' || l_contract_id || ' OEC Amt: ' || l_oec_amt);
334 END IF;
335 end if;
336
337 l_total_amt := l_total_amt + l_oec_amt;
338
339 END LOOP;
340 CLOSE C_CASE;
341
342 x_total_amt := NVL(l_total_amt, 0);
343
344 if PG_DEBUG < 10 then
345 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
346 IEX_DEBUG_PUB.LogMessage('Total Contract oec $'||x_total_amt);
347 END IF;
348 end if;
349
350 EXCEPTION
351 WHEN FND_API.G_EXC_ERROR THEN
352 x_return_status := FND_API.G_RET_STS_ERROR;
353 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
354 if PG_DEBUG < 10 then
355 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
356 IEX_DEBUG_PUB.LogMessage('Exec Error ' || sqlerrm);
357 END IF;
358 end if;
359
360 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
362 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
363 if PG_DEBUG < 10 then
364 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
365 IEX_DEBUG_PUB.LogMessage('Unexec Error ' || sqlerrm);
366 END IF;
367 end if;
368
369 WHEN OTHERS THEN
370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
371 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
372 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
373 END IF;
374 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
375 if PG_DEBUG < 10 then
376 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
377 IEX_DEBUG_PUB.LogMessage('Other Error ' || sqlerrm);
378 END IF;
379 end if;
380
381 END get_contract_oec;
382
383 ---------------------------------------------------------------------
384 -- PROCEDURE get_contract_tradein
385 ---------------------------------------------------------------------
386 PROCEDURE get_contract_tradein(
387 p_case_id IN NUMBER,
388 x_total_amt OUT NOCOPY NUMBER,
389 x_return_status OUT NOCOPY VARCHAR2)
390 IS
391 l_msg_count NUMBER;
392 l_msg_data VARCHAR2(2000);
393 l_contract_id NUMBER;
394
395 l_total_amt NUMBER;
396 l_tradein_amt NUMBER;
397 l_api_version NUMBER;
398 l_api_name VARCHAR2(30);
399
400 CURSOR c_case(l_case_id NUMBER) IS
401 SELECT OBJECT_ID
402 FROM IEX_CASE_OBJECTS
403 WHERE CAS_ID = l_case_id
404 AND OBJECT_CODE = 'CONTRACTS'
405 AND ACTIVE_FLAG = 'Y';
406
407 BEGIN
408
409 -- Initialize API return status to SUCCESS
410 x_return_status := FND_API.G_RET_STS_SUCCESS;
411
412 l_total_amt := 0;
413 l_tradein_amt := 0;
414 l_api_version := 2.0;
415 l_api_name := 'get_contract_tradein';
416 if PG_DEBUG < 10 then
417 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
418 IEX_DEBUG_PUB.LogMessage('PUB:'||G_PKG_NAME||'.'||l_api_name||' Start');
419 END IF;
420 end if;
421
422 OPEN C_CASE(p_case_id);
423 LOOP
424 FETCH C_CASE INTO l_contract_id;
425 EXIT WHEN NOT C_CASE%FOUND;
426
427
428 /* -jsanju comment for bug #-2605083
429 ( l_tradein_amt is intialized to zero)
430 */
431 -- raverma 04252003 uncomment #2924455
432 l_tradein_amt := 0;
433
434 l_tradein_amt := OKL_SEEDED_FUNCTIONS_PVT.contract_tradein(p_chr_id => l_contract_id,
435 p_line_id => null);
436 if PG_DEBUG < 10 then
437 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
438 IEX_DEBUG_PUB.LogMessage('ContractID: ' || l_contract_id || ' TradeinAmt: ' || l_tradein_amt);
439 END IF;
440 end if;
441
442 l_total_amt := l_total_amt + l_tradein_amt;
443
444 END LOOP;
445 CLOSE C_CASE;
446
447 x_total_amt := NVL(l_total_amt, 0);
448
449 if PG_DEBUG < 10 then
450 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
451 IEX_DEBUG_PUB.LogMessage( debug_msg => 'Total Contract Tradein $'||x_total_amt);
452 END IF;
453 end if;
454
455 EXCEPTION
456 WHEN FND_API.G_EXC_ERROR THEN
457 x_return_status := FND_API.G_RET_STS_ERROR;
458 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
459 if PG_DEBUG < 10 then
460 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
461 IEX_DEBUG_PUB.LogMessage('Exec Error ' || sqlerrm);
462 END IF;
463 end if;
464
465 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
466 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
467 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
468 if PG_DEBUG < 10 then
469 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
470 IEX_DEBUG_PUB.LogMessage('Unexec Error ' || sqlerrm);
471 END IF;
472 end if;
473
474 WHEN OTHERS THEN
475 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
476 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
477 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
478 END IF;
479 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
480 if PG_DEBUG < 10 then
481 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
482 IEX_DEBUG_PUB.LogMessage('Other Error ' || sqlerrm);
483 END IF;
484 end if;
485
486 END get_contract_tradein;
487
488 ---------------------------------------------------------------------
489 -- PROCEDURE get_contract_capital_reduction
490 ---------------------------------------------------------------------
491 PROCEDURE get_contract_capital_reduction(
492 p_case_id IN NUMBER,
493 x_total_amt OUT NOCOPY NUMBER,
494 x_return_status OUT NOCOPY VARCHAR2)
495 IS
496 l_msg_count NUMBER;
497 l_msg_data VARCHAR2(2000);
498 l_contract_id NUMBER;
499
500 l_total_amt NUMBER;
501 l_capred_amt NUMBER;
502 l_api_version NUMBER;
503 l_api_name VARCHAR2(30);
504
505 CURSOR c_case(l_case_id NUMBER) IS
506 SELECT OBJECT_ID
507 FROM IEX_CASE_OBJECTS
508 WHERE CAS_ID = l_case_id
509 AND OBJECT_CODE = 'CONTRACTS'
510 AND ACTIVE_FLAG = 'Y';
511
512 BEGIN
513
514 -- Initialize API return status to SUCCESS
515 x_return_status := FND_API.G_RET_STS_SUCCESS;
516
517 l_total_amt := 0;
518 l_capred_amt := 0;
519 l_api_version := 2.0;
520 l_api_name := 'get_contract_capital_reduction';
521 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
522 IEX_DEBUG_PUB.LogMessage('PUB:'||G_PKG_NAME||'.'||l_api_name||' Start');
523 END IF;
524
525 OPEN C_CASE(p_case_id);
526 LOOP
527 FETCH C_CASE INTO l_contract_id;
528 EXIT WHEN NOT C_CASE%FOUND;
529
530 -- -jsanju comment for bug #-2605083
531 -- ( l_capred_amt is intialized to zero)
532 -- raverma 04252003 uncomment #2924455
533
534 l_capred_amt := 0;
535 l_capred_amt := OKL_SEEDED_FUNCTIONS_PVT.contract_capital_reduction(p_chr_id => l_contract_id,
536 p_line_id => null);
537 if PG_DEBUG < 10 then
538 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
539 IEX_DEBUG_PUB.LogMessage('ContractID: ' || l_contract_id || ' CapRed_Amt: ' || l_capred_amt);
540 END IF;
541 end if;
542
543 l_total_amt := l_total_amt + l_capred_amt;
544
545 END LOOP;
546 CLOSE C_CASE;
547
548 x_total_amt := NVL(l_total_amt, 0);
549
550 if PG_DEBUG < 10 then
551 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
552 IEX_DEBUG_PUB.LogMessage('Total Capital Reduction $'||x_total_amt);
553 END IF;
554 end if;
555
556 EXCEPTION
557 WHEN FND_API.G_EXC_ERROR THEN
558 x_return_status := FND_API.G_RET_STS_ERROR;
559 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
560 if PG_DEBUG < 10 then
561 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
562 IEX_DEBUG_PUB.LogMessage('Exec Error ' || sqlerrm);
563 END IF;
564 end if;
565
566 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
567 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
568 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
569 if PG_DEBUG < 10 then
570 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
571 IEX_DEBUG_PUB.LogMessage('Unexec Error ' || sqlerrm);
572 END IF;
573 end if;
574
575 WHEN OTHERS THEN
576 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
577 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
578 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
579 END IF;
580 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
581 if PG_DEBUG < 10 then
582 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
583 IEX_DEBUG_PUB.LogMessage('Other Error ' || sqlerrm);
584 END IF;
585 end if;
586 END get_contract_capital_reduction;
587
588 ---------------------------------------------------------------------
589 -- PROCEDURE get_contract_fees_capitalized
590 ---------------------------------------------------------------------
591 PROCEDURE get_contract_fees_capitalized(
592 p_case_id IN NUMBER,
593 x_total_amt OUT NOCOPY NUMBER,
594 x_return_status OUT NOCOPY VARCHAR2)
595 IS
596 l_msg_count NUMBER;
597 l_msg_data VARCHAR2(2000);
598 l_contract_id NUMBER;
599
600 l_total_amt NUMBER;
601 l_feecap_amt NUMBER;
602 l_api_version NUMBER;
603 l_api_name VARCHAR2(30);
604
605 CURSOR c_case(l_case_id NUMBER) IS
606 SELECT OBJECT_ID
607 FROM IEX_CASE_OBJECTS
608 WHERE CAS_ID = l_case_id
609 AND OBJECT_CODE = 'CONTRACTS'
610 AND ACTIVE_FLAG = 'Y';
611
612 BEGIN
613
614 -- Initialize API return status to SUCCESS
615 x_return_status := FND_API.G_RET_STS_SUCCESS;
616
617 l_total_amt := 0;
618 l_feecap_amt := 0;
619 l_api_version := 2.0;
620 l_api_name := 'get_contract_fees_capitalized';
621 if PG_DEBUG < 10 then
622 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
623 IEX_DEBUG_PUB.LogMessage('PUB:'||G_PKG_NAME||'.'||l_api_name||' Start');
624 END IF;
625 end if;
626
627 OPEN C_CASE(p_case_id);
628 LOOP
629 FETCH C_CASE INTO l_contract_id;
630 EXIT WHEN NOT C_CASE%FOUND;
631
632 -- -jsanju comment for bug #-2605083
633 -- ( l_feecap_amt is intialized to zero)
634 -- raverma 04252003 uncomment #2924455
635
636 l_feecap_amt := 0;
637 l_feecap_amt := OKL_SEEDED_FUNCTIONS_PVT.contract_fees_capitalized(p_chr_id => l_contract_id,
638 p_line_id => null);
639 if PG_DEBUG < 10 then
640 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
641 IEX_DEBUG_PUB.LogMessage('ContractID: ' || l_contract_id || ' FeeCap_Amt: ' || l_feecap_amt);
642 END IF;
643 end if;
644
645 l_total_amt := l_total_amt + l_feecap_amt;
646
647 END LOOP;
648 CLOSE C_CASE;
649
650 x_total_amt := NVL(l_total_amt, 0);
651
652 if PG_DEBUG < 10 then
653 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
654 IEX_DEBUG_PUB.LogMessage( debug_msg => 'Contract Fees Capitalized $'||x_total_amt);
655 END IF;
656 end if;
657
658 EXCEPTION
659 WHEN FND_API.G_EXC_ERROR THEN
660 x_return_status := FND_API.G_RET_STS_ERROR;
661 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
662 if PG_DEBUG < 10 then
663 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
664 IEX_DEBUG_PUB.LogMessage('Exec Error ' || sqlerrm);
665 END IF;
666 end if;
667
668 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
669 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
670 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
671 if PG_DEBUG < 10 then
672 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
673 IEX_DEBUG_PUB.LogMessage('Unexec Error ' || sqlerrm);
674 END IF;
675 end if;
676
677 WHEN OTHERS THEN
678 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
679 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
680 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
681 END IF;
682 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
683 if PG_DEBUG < 10 then
684 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
685 IEX_DEBUG_PUB.LogMessage('Other Error ' || sqlerrm);
686 END IF;
687 end if;
688 END get_contract_fees_capitalized;
689
690
691 ---------------------------------------------------------------------
692 -- PROCEDURE get_total_capital_amount
693 ---------------------------------------------------------------------
694 PROCEDURE get_total_capital_amount(
695 p_case_id IN NUMBER,
696 x_total_amt OUT NOCOPY NUMBER,
697 x_return_status OUT NOCOPY VARCHAR2)
698 IS
699 l_msg_count NUMBER;
700 l_msg_data VARCHAR2(2000);
701 l_contract_id NUMBER;
702
703 l_total_cap_amt NUMBER;
704 l_oec_amt NUMBER;
705 l_total_oec_amt NUMBER;
706 l_trade_amt NUMBER;
707 l_total_trade_amt NUMBER;
708 l_capred_amt NUMBER;
709 l_total_capred_amt NUMBER;
710 l_feecap_amt NUMBER;
711 l_total_feecap_amt NUMBER;
712 l_api_version NUMBER;
713 l_api_name VARCHAR2(30);
714
715 CURSOR c_case_id (l_case_id NUMBER) IS
716 select nvl(sum(okl.oec) ,0)
717 from okc_k_lines_v okc
718 ,okl_k_lines okl
719 ,okc_line_styles_v lse
720 ,iex_case_objects icas
721 where okc.id=okl.id
722 and okc.lse_id = lse.id
723 and lse.lty_code='FREE_FORM1'
724 and okc.chr_id =icas.object_id
725 and icas.cas_id =p_case_id;
726
727 BEGIN
728
729 -- Initialize API return status to SUCCESS
730 x_return_status := FND_API.G_RET_STS_SUCCESS;
731
732 l_total_cap_amt := 0;
733 l_oec_amt := 0;
734 l_total_oec_amt := 0;
735 l_trade_amt := 0;
736 l_total_trade_amt := 0;
737 l_capred_amt := 0;
738 l_total_capred_amt := 0;
739 l_feecap_amt := 0;
740 l_total_feecap_amt := 0;
741 l_api_version := 2.0;
742 l_api_name := 'get_total_capital_amount';
743 if PG_DEBUG < 10 then
744 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
745 IEX_DEBUG_PUB.LogMessage('PUB:'||G_PKG_NAME||'.'||l_api_name||' Start');
746 END IF;
747 end if;
748 OPEN c_Case_id (p_case_id);
749 FETCH c_Case_id INTO l_total_cap_amt ;
750 CLOSE c_Case_id;
751
752 x_total_amt := NVL(l_total_cap_amt, 0);
753
754 if PG_DEBUG < 10 then
755 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
756 IEX_DEBUG_PUB.LogMessage('Total Capital Amount $'||x_total_amt);
757 END IF;
758 end if;
759
760 EXCEPTION
761 WHEN FND_API.G_EXC_ERROR THEN
762 x_return_status := FND_API.G_RET_STS_ERROR;
763 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
764 if PG_DEBUG < 10 then
765 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
766 IEX_DEBUG_PUB.LogMessage('Exec Error ' || sqlerrm);
767 END IF;
768 end if;
769
770 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
771 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
772 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
773 if PG_DEBUG < 10 then
774 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
775 IEX_DEBUG_PUB.LogMessage('Unexec Error ' || sqlerrm);
776 END IF;
777 end if;
778
779 WHEN OTHERS THEN
780 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
781 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
782 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
783 END IF;
784 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
785 if PG_DEBUG < 10 then
786 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
787 IEX_DEBUG_PUB.LogMessage('Other Error ' || sqlerrm);
788 END IF;
789 end if;
790 END get_total_capital_amount;
791
792 BEGIN
793
794 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
795
796
797 END IEX_CASE_INFO_PUB;