1 PACKAGE BODY OKL_TRANSFER_ASSUMPTION_PVT AS
2 /* $Header: OKLRTNAB.pls 120.1 2005/10/30 03:17:30 appldev noship $ */
3 ----------------------------------------------------------------------------
4 -- Data Structures
5 ----------------------------------------------------------------------------
6 subtype khrv_rec_type is OKL_KHR_pvt.khrv_rec_type;
7 subtype chrv_rec_type is okl_okc_migration_pvt.chrv_rec_type;
8
9 ----------------------------------------------------------------------------
10 -- Global Message Constants
11 ----------------------------------------------------------------------------
12 G_CONTRACT_FINANCED_AMOUNT constant varchar2(40) :='CONTRACT_FINANCED_AMOUNT';
13 G_LINE_FINANCED_AMOUNT constant varchar2(40) :='LINE_FINANCED_AMOUNT';
14 ----------------------------------------------------------------------------
15 -- Procedures and Functions
16 ----------------------------------------------------------------------------
17
18 ----------------------------------------------------------------------------------
19 -- Start of comments
20 --
21 -- Procedure Name : update_tna_creditline
22 -- Description : Calculate total Transfers and Assumption amount based on
23 -- pass in Contarct and correlated credit line ID and update
24 -- credit line tna amount
25 -- Business Rules :
26 -- Parameters :
27 -- Version : 1.0
28 -- End of comments
29 ----------------------------------------------------------------------------------
30 PROCEDURE update_tna_creditline(
31 p_api_version IN NUMBER
32 ,p_init_msg_list IN VARCHAR2
33 ,x_return_status OUT NOCOPY VARCHAR2
34 ,x_msg_count OUT NOCOPY NUMBER
35 ,x_msg_data OUT NOCOPY VARCHAR2
36 ,p_chr_id IN okc_k_headers_b.id%type -- contract ID
37 ,p_credit_line_id IN okc_k_headers_b.id%type -- credit line ID
38 ,p_formula_name IN VARCHAR2
39 ,p_credit_flag IN BOOLEAN default false
40 )
41 is
42 l_api_name CONSTANT VARCHAR2(30) := 'update_tna_creditline';
43 l_api_version CONSTANT NUMBER := 1.0;
44 i NUMBER;
45 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
46 l_src_chr_not_found boolean := false;
47 l_credit_tna NUMBER;
48 l_orig_credit_tna NUMBER;
49
50 l_chrv_rec chrv_rec_type;
51 l_khrv_rec khrv_rec_type;
52 x_chrv_rec chrv_rec_type;
53 x_khrv_rec khrv_rec_type;
54
55
56 cursor c_credit_tna (p_chr_id okc_k_headers_b.id%TYPE)
57 is
58 select NVL(khr.TOT_CL_NET_TRANSFER_AMT,0)
59 from okl_k_headers khr
60 where khr.id = p_chr_id
61 ;
62
63
64 begin
65 -- Set API savepoint
66 SAVEPOINT update_tna_creditline;
67
68 -- Check for call compatibility
69 IF (NOT FND_API.Compatible_API_Call (l_api_version,
70 p_api_version,
71 l_api_name,
72 G_PKG_NAME ))
73 THEN
74 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
75 END IF;
76
77 -- Initialize message list if requested
78 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
79 FND_MSG_PUB.initialize;
80 END IF;
81
82 -- Initialize API status to success
83 x_return_status := OKL_API.G_RET_STS_SUCCESS;
84
85
86 /*** Begin API body ****************************************************/
87
88 OKL_EXECUTE_FORMULA_PUB.execute(
89 p_api_version => l_api_version,
90 p_init_msg_list => p_init_msg_list,
91 x_return_status => x_return_status,
92 x_msg_count => x_msg_count,
93 x_msg_data => x_msg_data,
94 p_formula_name => p_formula_name,
95 p_contract_id => p_chr_id,
96 x_value => l_credit_tna);
97
98 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
99 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
100 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
101 RAISE OKL_API.G_EXCEPTION_ERROR;
102 END IF;
103
104 -- get credit line tna amount
105 open c_credit_tna(p_credit_line_id);
106 fetch c_credit_tna into l_orig_credit_tna;
107 close c_credit_tna;
108
109 -- set credit line record's value
110 l_chrv_rec.id := p_credit_line_id;
111 l_khrv_rec.id := p_credit_line_id;
112
113 -- change to negative sign if credit flag is true
114 if (p_credit_flag) then
115 l_credit_tna := -l_credit_tna;
116 end if;
117
118 l_khrv_rec.TOT_CL_NET_TRANSFER_AMT := l_orig_credit_tna + l_credit_tna;
119
120 okl_contract_pvt.update_contract_header(
121 p_api_version => l_api_version,
122 p_init_msg_list => p_init_msg_list,
123 x_return_status => x_return_status,
124 x_msg_count => x_msg_count,
125 x_msg_data => x_msg_data,
126 p_restricted_update => 'F',
127 p_chrv_rec => l_chrv_rec,
128 p_khrv_rec => l_khrv_rec,
129 x_chrv_rec => x_chrv_rec,
130 x_khrv_rec => x_khrv_rec);
131
132 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
133 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
134 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
135 RAISE OKL_API.G_EXCEPTION_ERROR;
136 END IF;
137
138 /*** End API body ******************************************************/
139
140 -- Get message count and if count is 1, get message info
141 FND_MSG_PUB.Count_And_Get
142 (p_count => x_msg_count,
143 p_data => x_msg_data);
144
145 EXCEPTION
146 WHEN OKL_API.G_EXCEPTION_ERROR THEN
147 ROLLBACK TO update_tna_creditline;
148 x_return_status := OKL_API.G_RET_STS_ERROR;
149 FND_MSG_PUB.Count_And_Get
150 (p_count => x_msg_count,
151 p_data => x_msg_data);
152
153 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
154 ROLLBACK TO update_tna_creditline;
155 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
156 FND_MSG_PUB.Count_And_Get
157 (p_count => x_msg_count,
158 p_data => x_msg_data);
159
160 WHEN OTHERS THEN
161 ROLLBACK TO update_tna_creditline;
162 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
163 OKL_API.Set_Message(p_app_name => G_APP_NAME,
164 p_msg_name => G_UNEXPECTED_ERROR,
165 p_token1 => G_SQLCODE_TOKEN,
166 p_token1_value => SQLCODE,
167 p_token2 => G_SQLERRM_TOKEN,
168 p_token2_value => SQLERRM);
169 FND_MSG_PUB.Count_And_Get
170 (p_count => x_msg_count,
171 p_data => x_msg_data);
172
173 end update_tna_creditline;
174
175
176 ----------------------------------------------------------------------------------
177 -- Start of comments
178 --
179 -- Procedure Name : update_full_tna_creditline
180 -- Description : Calculate total Transfers and Assumption amount based on
181 -- pass in contract ID and update correlated credit lines' tna amount
182 -- Business Rules :
183 -- Parameters :
184 -- Version : 1.0
185 -- End of comments
186 ----------------------------------------------------------------------------------
187 PROCEDURE update_full_tna_creditline(
188 p_api_version IN NUMBER
189 ,p_init_msg_list IN VARCHAR2
190 ,x_return_status OUT NOCOPY VARCHAR2
191 ,x_msg_count OUT NOCOPY NUMBER
192 ,x_msg_data OUT NOCOPY VARCHAR2
193 ,p_chr_id IN okc_k_headers_b.id%type
194 )
195 is
196 l_api_name CONSTANT VARCHAR2(30) := 'update_full_tna_creditline';
197 l_api_version CONSTANT NUMBER := 1.0;
198 i NUMBER;
199 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
200 l_src_credit_id NUMBER;
201 l_dst_credit_id NUMBER;
202 l_src_chr_id NUMBER;
203 l_src_chr_not_found boolean := false;
204
205 cursor c_src_chr (p_chr_id okc_k_headers_b.id%TYPE)
206 is
207 select chr.ORIG_SYSTEM_ID1
208 from okc_k_headers_b chr
209 where chr.id = p_chr_id
210 ;
211
212 begin
213 -- Set API savepoint
214 SAVEPOINT update_full_tna_creditline;
215
216 -- Check for call compatibility
217 IF (NOT FND_API.Compatible_API_Call (l_api_version,
218 p_api_version,
219 l_api_name,
220 G_PKG_NAME ))
221 THEN
222 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
223 END IF;
224
225 -- Initialize message list if requested
226 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
227 FND_MSG_PUB.initialize;
228 END IF;
229
230 -- Initialize API status to success
231 x_return_status := OKL_API.G_RET_STS_SUCCESS;
232
233
234 /*** Begin API body ****************************************************/
235 /*
236 -> contract T and A
237 5.1 get credit line ID for destination contract if any (ignore if credit line not found?)
238 5.2 get source contract's ID via okc_k_headers_b.ORIG_SYSTEM_ID1 (raise system error if not found)
239 5.3 get credit line ID for source contract if any (ignore if credit line not found?)
240
241 5.4
242 IF source contract credit line exists THEN
243 get total T and A for source contract via formula CONTRACT_FINANCED_AMOUNT
244 Update (add this amount as positive to OKL_K_HEADERS.TOT_CL_NET_TRANSFER_AMT)
245 T and A for source contract's credit line
246 END IF;
247
248 5.5
249 IF destination contract credit line exists THEN
250 get total T and A for destination contract via formula CONTRACT_FINANCED_AMOUNT
251 Update (add this amount as negative to OKL_K_HEADERS.TOT_CL_NET_TRANSFER_AMT)
252 T and A for destination contract's credit line
253 END IF;
254
255 */
256
257 -- get credit line ID for destination contract if any (ignore if credit line not found?)
258 l_dst_credit_id := OKL_CREDIT_PUB.get_creditline_by_chrid(p_chr_id);
259
260 -- get source contract's ID via okc_k_headers_b.ORIG_SYSTEM_ID1
261 open c_src_chr(p_chr_id);
262 fetch c_src_chr into l_src_chr_id;
263 l_src_chr_not_found := c_src_chr%notfound;
264 close c_src_chr;
265
266 -- raise system error if data not found
267 if l_src_chr_not_found then
268 -- add new message
269 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
270 end if;
271
272 -- get credit line ID for source contract if any (ignore if credit line not found?)
273 l_src_credit_id := OKL_CREDIT_PUB.get_creditline_by_chrid(l_src_chr_id);
274
275 -- IF source contract credit line exists THEN
276 -- get total T for source contract via formula CONTRACT_FINANCED_AMOUNT
277 -- Update (add this amount as positive to OKL_K_HEADERS.TOT_CL_NET_TRANSFER_AMT) T for source contract's credit line
278 -- END IF;
279 if l_src_credit_id is not null then
280
281 update_tna_creditline(
282 p_api_version => l_api_version,
283 p_init_msg_list => p_init_msg_list,
284 x_return_status => x_return_status,
285 x_msg_count => x_msg_count,
286 x_msg_data => x_msg_data,
287 p_chr_id => p_chr_id,--l_src_chr_id, based on destination contract financed amount
288 p_credit_line_id => l_src_credit_id,
289 p_formula_name => G_CONTRACT_FINANCED_AMOUNT,
290 p_credit_flag => false);
291
292 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
293 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
294 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
295 RAISE OKL_API.G_EXCEPTION_ERROR;
296 END IF;
297
298 end if;
299
300 -- IF destination contract credit line exists THEN
301 -- get total T for destination contract via formula CONTRACT_FINANCED_AMOUNT
302 -- Update (add this amount as negative to OKL_K_HEADERS.TOT_CL_NET_TRANSFER_AMT) T for destination contract's credit line
303 -- END IF;
304 if l_dst_credit_id is not null then
305
306 update_tna_creditline(
307 p_api_version => l_api_version,
308 p_init_msg_list => p_init_msg_list,
309 x_return_status => x_return_status,
310 x_msg_count => x_msg_count,
311 x_msg_data => x_msg_data,
312 p_chr_id => p_chr_id,
313 p_credit_line_id => l_dst_credit_id,
314 p_formula_name => G_CONTRACT_FINANCED_AMOUNT,
315 p_credit_flag => true);
316
317 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
318 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
319 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
320 RAISE OKL_API.G_EXCEPTION_ERROR;
321 END IF;
322
323 end if;
324
325
326 /*** End API body ******************************************************/
327
328 -- Get message count and if count is 1, get message info
329 FND_MSG_PUB.Count_And_Get
330 (p_count => x_msg_count,
331 p_data => x_msg_data);
332
333 EXCEPTION
334 WHEN OKL_API.G_EXCEPTION_ERROR THEN
335 ROLLBACK TO update_full_tna_creditline;
336 x_return_status := OKL_API.G_RET_STS_ERROR;
337 FND_MSG_PUB.Count_And_Get
338 (p_count => x_msg_count,
339 p_data => x_msg_data);
340
341 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
342 ROLLBACK TO update_full_tna_creditline;
343 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
344 FND_MSG_PUB.Count_And_Get
345 (p_count => x_msg_count,
346 p_data => x_msg_data);
347
348 WHEN OTHERS THEN
349 ROLLBACK TO update_full_tna_creditline;
350 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
351 OKL_API.Set_Message(p_app_name => G_APP_NAME,
352 p_msg_name => G_UNEXPECTED_ERROR,
353 p_token1 => G_SQLCODE_TOKEN,
354 p_token1_value => SQLCODE,
355 p_token2 => G_SQLERRM_TOKEN,
356 p_token2_value => SQLERRM);
357 FND_MSG_PUB.Count_And_Get
358 (p_count => x_msg_count,
359 p_data => x_msg_data);
360
361 end update_full_tna_creditline;
362
363
364 ----------------------------------------------------------------------------------
365 -- Start of comments
366 --
367 -- Procedure Name : update_partial_tna_creditline
368 -- Description : Calculate Transfers and Assumption amount for for the
369 -- source and destination contract's asset and update correlated
370 -- credit line's tna amount
371 -- Business Rules :
372 -- Parameters :
373 -- Version : 1.0
374 -- End of comments
375 ----------------------------------------------------------------------------------
376 PROCEDURE update_partial_tna_creditline(
377 p_api_version IN NUMBER
378 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
379 ,x_return_status OUT NOCOPY VARCHAR2
380 ,x_msg_count OUT NOCOPY NUMBER
381 ,x_msg_data OUT NOCOPY VARCHAR2
382 ,p_chr_id IN okc_k_headers_b.id%type
383 )
384 is
385 l_api_name CONSTANT VARCHAR2(30) := 'update_partial_tna_creditline';
386 l_api_version CONSTANT NUMBER := 1.0;
387 i NUMBER;
388 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
389 l_src_credit_id NUMBER;
390 l_dst_credit_id NUMBER;
391 l_src_chr_id NUMBER;
392 l_src_chr_not_found boolean := false;
393 l_src_credit_tna NUMBER;
394 l_dst_credit_tna NUMBER;
395 l_src_orig_credit_tna NUMBER;
396 l_dst_orig_credit_tna NUMBER;
397
398 l_chrv_rec chrv_rec_type;
399 l_khrv_rec khrv_rec_type;
400 x_chrv_rec chrv_rec_type;
401 x_khrv_rec khrv_rec_type;
402
403
404 cursor c_credit_tna (p_chr_id okc_k_headers_b.id%TYPE)
405 is
406 select NVL(khr.TOT_CL_NET_TRANSFER_AMT,0)
407 from okl_k_headers khr
408 where khr.id = p_chr_id
409 ;
410
411 -- source contarct and it's top line ids
412 cursor c_src_chr (p_chr_id okc_k_headers_b.id%TYPE)
413 is
414 select src_cle.dnz_chr_id,
415 src_cle.id cle_id
416 from okc_k_lines_b src_cle,
417 okc_k_lines_b cle,
418 okc_line_styles_b lse
419 where src_cle.id = cle.ORIG_SYSTEM_ID1
420 and lse.id = cle.lse_id
421 and lse.lty_code = 'FREE_FORM1'
422 and cle.dnz_chr_id = p_chr_id -- destination contract ID
423 ;
424
425 begin
426 -- Set API savepoint
427 SAVEPOINT update_partial_tna_creditline;
428
429 -- Check for call compatibility
430 IF (NOT FND_API.Compatible_API_Call (l_api_version,
431 p_api_version,
432 l_api_name,
433 G_PKG_NAME ))
434 THEN
435 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
436 END IF;
437
438 -- Initialize message list if requested
439 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
440 FND_MSG_PUB.initialize;
441 END IF;
442
443 -- Initialize API status to success
444 x_return_status := OKL_API.G_RET_STS_SUCCESS;
445
446
447 /*** Begin API body ****************************************************/
448 /*
449 -> contract T and A
450 -- only one destination credit line
451 5.1 get credit line ID for destination contract if any (raise error if credit line not found?)
452
453 5.2
454 <<loop of destination contract asset lines>>
455 Loop
456
457 -- Source contract T and A
458 -- May refer from multiple contracts if it's partial transfer
459 5.2.1 get credit line ID for source contract if any (raise error if credit line not found?)
460 IF credit line found THEN
461 5.2.1.1 get source contract's asset top line via okc_k_lines_b.ORIG_SYSTEM_ID1
462 5.2.1.2 get total T and A for source contract asset via formula LINE_CAP_AMOUNT
463 5.2.1.3 Update (add this amount as positive to OKL_K_HEADERS.TOT_CL_NET_TRANSFER_AMT) T and A for source contract's credit line
464 END IF;
465
466 end loop;
467
468 IF destination credit line found THEN
469 5.3.1 get total T and A for destination contract asset via formula LINE_CAP_AMOUNT
470 5.3.2 Update (add this amount as negative to OKL_K_HEADERS.TOT_CL_NET_TRANSFER_AMT) T and A for destination contract's credit line
471 END IF;
472
473 */
474
475 -- only one destination credit line
476 -- get credit line ID for destination contract if any (ignore error if credit line not found?)
477 l_dst_credit_id := OKL_CREDIT_PUB.get_creditline_by_chrid(p_chr_id);
478
479 -- <<loop of destination contract asset lines>>
480 -- Loop
481 For r_src_chr in c_src_chr(p_chr_id) loop
482 -- Source contract T and A
483 -- May refer from multiple contracts if it's partial transfer
484 -- get credit line ID for source contract if any (ignore if credit line not found?)
485 l_src_credit_id := OKL_CREDIT_PUB.get_creditline_by_chrid(r_src_chr.dnz_chr_id);
486
487 -- IF credit line found THEN
488 -- get source contract's asset top line via okc_k_lines_b.ORIG_SYSTEM_ID1
489 -- get total T and A for source contract asset via formula LINE_CAP_AMOUNT
490 -- Update (add this amount as positive to OKL_K_HEADERS.TOT_CL_NET_TRANSFER_AMT) T and A for source contract's credit line
491 -- END IF;
492 if l_src_credit_id is not null then
493
494 update_tna_creditline(
495 p_api_version => l_api_version,
496 p_init_msg_list => p_init_msg_list,
497 x_return_status => x_return_status,
498 x_msg_count => x_msg_count,
499 x_msg_data => x_msg_data,
500 p_chr_id => r_src_chr.dnz_chr_id,
501 p_credit_line_id => l_src_credit_id,
502 p_formula_name => G_LINE_FINANCED_AMOUNT,
503 p_credit_flag => false);
504
505 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
506 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
507 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
508 RAISE OKL_API.G_EXCEPTION_ERROR;
509 END IF;
510
511 end if;
512
513 end loop;
514 -- end loop;
515
516 -- IF destination credit line found THEN
517 -- get total T and A for destination contract asset via formula CONTRACT_CAP_AMOUNT
518 -- Update (add this amount as negative to OKL_K_HEADERS.TOT_CL_NET_TRANSFER_AMT) T and A for destination contract's credit line
519 -- END IF;
520 if l_dst_credit_id is not null then
521
522 update_tna_creditline(
523 p_api_version => l_api_version,
524 p_init_msg_list => p_init_msg_list,
525 x_return_status => x_return_status,
526 x_msg_count => x_msg_count,
527 x_msg_data => x_msg_data,
528 p_chr_id => p_chr_id,
529 p_credit_line_id => l_dst_credit_id,
530 p_formula_name => G_CONTRACT_FINANCED_AMOUNT,
531 p_credit_flag => true);
532
533 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
534 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
535 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
536 RAISE OKL_API.G_EXCEPTION_ERROR;
537 END IF;
538
539 end if;
540
541 /*** End API body ******************************************************/
542
543 -- Get message count and if count is 1, get message info
544 FND_MSG_PUB.Count_And_Get
545 (p_count => x_msg_count,
546 p_data => x_msg_data);
547
548 EXCEPTION
549 WHEN OKL_API.G_EXCEPTION_ERROR THEN
550 ROLLBACK TO update_partial_tna_creditline;
551 x_return_status := OKL_API.G_RET_STS_ERROR;
552 FND_MSG_PUB.Count_And_Get
553 (p_count => x_msg_count,
554 p_data => x_msg_data);
555
556 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
557 ROLLBACK TO update_partial_tna_creditline;
558 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
559 FND_MSG_PUB.Count_And_Get
560 (p_count => x_msg_count,
561 p_data => x_msg_data);
562
563 WHEN OTHERS THEN
564 ROLLBACK TO update_partial_tna_creditline;
565 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
566 OKL_API.Set_Message(p_app_name => G_APP_NAME,
567 p_msg_name => G_UNEXPECTED_ERROR,
568 p_token1 => G_SQLCODE_TOKEN,
569 p_token1_value => SQLCODE,
570 p_token2 => G_SQLERRM_TOKEN,
571 p_token2_value => SQLERRM);
572 FND_MSG_PUB.Count_And_Get
573 (p_count => x_msg_count,
574 p_data => x_msg_data);
575
576 end update_partial_tna_creditline;
577
578
579 END OKL_TRANSFER_ASSUMPTION_PVT;
[Home] [Help]