[Home] [Help]
PACKAGE BODY: APPS.OKL_VARIABLE_INT_UTIL_PVT
Source
1 PACKAGE BODY OKL_VARIABLE_INT_UTIL_PVT AS
2 /* $Header: OKLRVIUB.pls 120.31.12020000.4 2012/08/30 11:18:03 rpillay ship $ */
3 ------------------------------------------------------------------------------
4 -- Start of Comments
5 -- Created By: dkagrawa
6 -- Function Name: get_interest_billed
7 -- Description: This Function is called to get interest billed for a date range
8 -- Inputs :
9 -- Output : Interest Billed
10 -- Dependencies:
11 -- Parameters: Contract id, Start Date, End Date
12 -- Version: 1.0
13 -- End of Comments
14 ------------------------------------------------------------------------------
15
16 FUNCTION get_interest_billed(
17 x_return_status OUT NOCOPY VARCHAR2,
18 p_khr_id IN NUMBER,
19 p_kle_id IN NUMBER DEFAULT NULL,
20 p_from_date IN DATE,
21 p_to_date IN DATE DEFAULT SYSDATE) RETURN NUMBER IS
22
23 l_debug_enabled VARCHAR2(1);
24 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_INTEREST_BILLED';
25 x_msg_count NUMBER;
26 x_msg_data VARCHAR2(2000);
27
28 l_interest_billed NUMBER;
29
30 --Bug# 13447258: Added parameter cp_kle_id
31 --Bug# 14549777: Added where condition to return contract level interest for upgraded contracts
32 -- Begin - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
33 CURSOR l_interest_billed_csr(cp_khr_id IN NUMBER, cp_kle_id IN NUMBER, cp_from_date IN DATE, cp_to_date IN DATE) IS
34 -- Begin bug 6456733
35 -- dcshanmu bug 6734738 start
36 SELECT NVL(SUM(AMOUNT),0) interest_billed_amount
37 FROM okl_bpd_ar_inv_lines_v lpt,
38 okl_strm_type_b sty,
39 ar_payment_schedules_all aps,
40 okl_k_headers_full_v khr
41 WHERE lpt.contract_id = cp_khr_id
42 AND (cp_kle_id IS NULL OR (cp_kle_id IS NOT NULL AND lpt.contract_line_id = cp_kle_id))
43 AND lpt.contract_number = khr.contract_number
44 AND lpt.sty_id = sty.id
45 AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
46 AND sty.stream_type_purpose IN ('INTEREST_PAYMENT', 'VARIABLE_INTEREST','INTEREST_CATCHUP')
47 AND TRUNC(aps.trx_date) BETWEEN TRUNC(NVL(cp_from_date, khr.start_date))
48 AND TRUNC(NVL(cp_to_date,SYSDATE));
49 --dcshanmu bug 6734738 end
50 -- End bug 6456733
51
52 BEGIN
53 l_debug_enabled := okl_debug_pub.check_log_enabled;
54 IF(NVL(l_debug_enabled,'N')='Y') THEN
55 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_interest_billed');
56 END IF;
57
58 l_interest_billed := 0;
59 x_return_status := OKL_API.G_RET_STS_SUCCESS;
60 IF ( p_khr_id IS NULL ) THEN
61 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
62 END IF;
63
64 --Bug# 13447258: Added parameter p_kle_id
65 OPEN l_interest_billed_csr(p_khr_id, p_kle_id, p_from_date, p_to_date);
66 FETCH l_interest_billed_csr INTO l_interest_billed;
67 CLOSE l_interest_billed_csr;
68
69 IF(NVL(l_debug_enabled,'N')='Y') THEN
70 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_interest_billed');
71 END IF;
72
73 RETURN l_interest_billed;
74 EXCEPTION
75 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
76 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
77 RETURN l_interest_billed;
78 WHEN OTHERS THEN
79 IF l_interest_billed_csr%ISOPEN THEN
80 CLOSE l_interest_billed_csr;
81 END IF;
82 OKL_API.SET_MESSAGE(
83 p_app_name => G_APP_NAME,
84 p_msg_name => G_UNEXPECTED_ERROR,
85 p_token1 => G_SQLCODE_TOKEN,
86 p_token1_value => SQLCODE,
87 p_token2 => G_SQLERRM_TOKEN,
88 p_token2_value => SQLERRM);
89 RETURN l_interest_billed;
90 END get_interest_billed;
91
92 ------------------------------------------------------------------------------
93 -- Start of Comments
94 -- Created By: dkagrawa
95 -- Function Name: get_interest_paid
96 -- Description: This Function is called to get interest paid for a date range
97 -- Inputs :
98 -- Output : Interest Paid
99 -- Dependencies:
100 -- Parameters: Contract id, Start Date, End Date
101 -- Version: 1.0
102 -- End of Comments
103 ------------------------------------------------------------------------------
104
105 FUNCTION get_interest_paid(
106 x_return_status OUT NOCOPY VARCHAR2,
107 p_khr_id IN NUMBER,
108 p_kle_id IN NUMBER DEFAULT NULL,
109 p_from_date IN DATE,
110 p_to_date IN DATE DEFAULT SYSDATE) RETURN NUMBER IS
111
112 l_debug_enabled VARCHAR2(1);
113 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_INTEREST_PAID';
114 x_msg_count NUMBER;
115 x_msg_data VARCHAR2(2000);
116
117 l_interest_paid NUMBER;
118
119 --Bug# 13447258: Added parameter cp_kle_id
120 --Bug# 14549777: Added where condition to return contract level interest for upgraded contracts
121 CURSOR l_interest_paid_csr(cp_khr_id IN NUMBER, cp_kle_id IN NUMBER, cp_from_date IN DATE, cp_to_date IN DATE) IS
122 --Begin bug# 13893142
123 SELECT NVL(SUM(interest_paid_amount),0) interest_paid_amount
124 FROM
125 (
126 SELECT NVL(SUM(AMOUNT),0)- NVL(SUM(LINE_AMOUNT_REMAINING_WOTAX),0) interest_paid_amount
127 FROM okl_bpd_ar_inv_lines_v lpt1,
128 okl_strm_type_b sty,
129 ar_payment_schedules_all aps,
130 okc_k_headers_b khr
131 WHERE contract_id=cp_khr_id
132 AND lpt1.contract_id = khr.id
133 AND lpt1.sty_id = sty.id
134 AND (cp_kle_id IS NULL OR (cp_kle_id IS NOT NULL AND lpt1.contract_line_id = cp_kle_id))
135 AND lpt1.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
136 AND sty.stream_type_purpose IN ('INTEREST_PAYMENT', 'VARIABLE_INTEREST','INTEREST_CATCHUP')
137 AND TRUNC(aps.trx_date)
138 BETWEEN TRUNC(NVL(cp_from_date, khr.start_date)) AND TRUNC(NVL(cp_to_date, SYSDATE))
139 UNION ALL
140 SELECT NVL(SUM(sel.amount),0) interest_paid_amount
141 FROM okl_strm_type_v sty,
142 okl_streams_v stm,
143 okl_strm_elements sel,
144 okc_k_headers_b khr
145 WHERE stm.khr_id = cp_khr_id
146 AND stm.kle_id = NVL(cp_kle_id, stm.kle_id)
147 AND stm.khr_id = khr.id
148 AND stm.sty_id = sty.id
149 AND sty.stream_type_purpose = 'DAILY_INTEREST_INTEREST'
150 AND stm.id = sel.stm_id
151 AND TRUNC(sel.stream_element_date)
152 BETWEEN TRUNC(NVL(cp_from_date, khr.start_date)) AND TRUNC(NVL(cp_to_date, SYSDATE))
153 );
154 --End bug# 13893142
155
156 BEGIN
157 l_debug_enabled := okl_debug_pub.check_log_enabled;
158 IF(NVL(l_debug_enabled,'N')='Y') THEN
159 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_interest_paid');
160 END IF;
161
162 l_interest_paid := 0;
163 x_return_status := OKL_API.G_RET_STS_SUCCESS;
164 IF ( p_khr_id IS NULL ) THEN
165 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
166 END IF;
167
168 --Bug# 13447258: Added parameter p_kle_id
169 OPEN l_interest_paid_csr(p_khr_id, p_kle_id, p_from_date, p_to_date);
170 FETCH l_interest_paid_csr INTO l_interest_paid;
171 CLOSE l_interest_paid_csr;
172
173 IF(NVL(l_debug_enabled,'N')='Y') THEN
174 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_interest_paid');
175 END IF;
176
177 RETURN l_interest_paid;
178 EXCEPTION
179 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
180 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
181 RETURN l_interest_paid;
182 WHEN OTHERS THEN
183 IF l_interest_paid_csr%ISOPEN THEN
184 CLOSE l_interest_paid_csr;
185 END IF;
186 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
187 OKL_API.SET_MESSAGE(
188 p_app_name => G_APP_NAME,
189 p_msg_name => G_UNEXPECTED_ERROR,
190 p_token1 => G_SQLCODE_TOKEN,
191 p_token1_value => SQLCODE,
192 p_token2 => G_SQLERRM_TOKEN,
193 p_token2_value => SQLERRM);
194 RETURN l_interest_paid;
195 END get_interest_paid;
196
197 ------------------------------------------------------------------------------
198 -- Start of Comments
199 -- Created By: dkagrawa
200 -- Function Name: get_interest_due
201 -- Description: This Function is called to get interest due for a date range
202 -- Inputs :
203 -- Output : Interest Due
204 -- Dependencies:
205 -- Parameters: Contract id, Effective Date
206 -- Version: 1.0
207 -- End of Comments
208 ------------------------------------------------------------------------------
209
210 FUNCTION get_interest_due(
211 x_return_status OUT NOCOPY VARCHAR2,
212 p_khr_id IN NUMBER,
213 p_to_date IN DATE DEFAULT SYSDATE) RETURN NUMBER IS
214
215 l_api_version CONSTANT NUMBER := 1.0;
216 x_msg_count NUMBER;
217 x_msg_data VARCHAR2(2000);
218 l_debug_enabled VARCHAR2(1);
219 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_INTEREST_DUE';
220
221 l_principal_basis okl_k_rate_params.principal_basis_code%TYPE;
222 l_start_date DATE;
223 l_end_date DATE;
224 l_to_date DATE;
225 l_due_date DATE;
226 l_next_period_start_date DATE;
227 l_next_period_end_date DATE;
228 l_interest_amt NUMBER;
229 l_interest_due NUMBER;
230 l_int_calc_basis OKL_PRODUCT_PARAMETERS_V.interest_calculation_basis%TYPE;
231 l_rev_rec_mthd OKL_PRODUCT_PARAMETERS_V.revenue_recognition_method%TYPE;
232
233 CURSOR l_principal_basis_csr(cp_khr_id IN NUMBER) IS
234 SELECT principal_basis_code
235 FROM okl_k_rate_params
236 WHERE khr_id = cp_khr_id
237 AND parameter_type_code = 'ACTUAL'
238 AND TRUNC(SYSDATE) BETWEEN effective_from_date AND NVL(effective_to_date, TRUNC(SYSDATE));
239
240 CURSOR l_contract_info_csr(cp_khr_id IN NUMBER) IS
241 SELECT chr.start_date,
242 chr.currency_code,
243 chr.end_date
244 FROM OKC_K_HEADERS_B chr,
245 OKL_K_HEADERS khr
246 WHERE chr.id = khr.id
247 AND khr.id = cp_khr_id;
248
249 CURSOR l_int_calc_basis_csr(cp_khr_id IN NUMBER) IS
250 SELECT ppm.interest_calculation_basis
251 ,ppm.revenue_recognition_method
252 FROM okl_k_headers khr,
253 okl_product_parameters_v ppm
254 WHERE khr.id = cp_khr_id
255 AND khr.pdt_id = ppm.id;
256
257 CURSOR l_interest_due_csr (cp_khr_id NUMBER,
258 p_due_date DATE) IS
259 SELECT NVL(SUM(amount),0)
260 FROM okl_strm_elements sel,
261 okl_streams str,
262 okl_strm_type_v sty
263 WHERE sel.stm_id = str.id
264 AND str.khr_id = cp_khr_id
265 AND str.say_code = 'CURR'
266 AND str.active_yn = 'Y'
267 AND sel.stream_element_date <= p_due_date
268 AND str.sty_id = sty.id
269 AND sty.stream_type_purpose = 'INTEREST_PAYMENT';
270
271 l_contract_info_rec l_contract_info_csr%ROWTYPE;
272
273 BEGIN
274 l_debug_enabled := okl_debug_pub.check_log_enabled;
275 IF(NVL(l_debug_enabled,'N')='Y') THEN
276 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_interest_due');
277 END IF;
278
279 l_interest_amt := 0;
280 l_interest_due := 0;
281 x_return_status := OKL_API.G_RET_STS_SUCCESS;
282 IF ( p_khr_id IS NULL ) THEN
283 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
284 END IF;
285
286 OPEN l_principal_basis_csr(p_khr_id);
287 FETCH l_principal_basis_csr INTO l_principal_basis;
288 CLOSE l_principal_basis_csr;
289
290 --change for bug fix 4905791
291 IF (l_principal_basis IS NULL) THEN
292 --this is set to SCHEDULED so that the code works for Fixed Loans as well
293 l_principal_basis := 'SCHEDULED';
294 END IF;
295
296 OPEN l_contract_info_csr(p_khr_id);
297 FETCH l_contract_info_csr INTO l_contract_info_rec;
298 CLOSE l_contract_info_csr;
299
300 l_start_date := l_contract_info_rec.start_date;
301 l_end_date := l_contract_info_rec.end_date;
302
303 IF (l_end_date <= p_to_date) THEN
304 l_to_date := l_end_date;
305 ELSE
306 l_to_date := p_to_date;
307 END IF;
308
309 OPEN l_int_calc_basis_csr(p_khr_id);
310 FETCH l_int_calc_basis_csr INTO l_int_calc_basis, l_rev_rec_mthd;
311 CLOSE l_int_calc_basis_csr;
312
313
314 --change for bug fix 4905791
315 IF (l_rev_rec_mthd = 'ACTUAL') THEN
316 l_interest_due := OKL_VARIABLE_INTEREST_PVT.calculate_total_interest_due(
317 p_api_version => l_api_version,
318 p_init_msg_list => OKL_API.G_FALSE,
319 x_return_status => x_return_status,
320 x_msg_count => x_msg_count,
321 x_msg_data => x_msg_data,
322 p_contract_id => p_khr_id,
323 p_currency_code => l_contract_info_rec.currency_code,
324 p_start_date => l_start_date,
325 p_due_date => l_to_date,
326 p_principal_basis => 'ACTUAL');
327
328 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
329 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
330 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
331 RAISE OKL_API.G_EXCEPTION_ERROR;
332 END IF;
333 ELSE
334
335 IF l_principal_basis = 'ACTUAL' THEN
336 l_interest_due := OKL_VARIABLE_INTEREST_PVT.calculate_total_interest_due(
337 p_api_version => l_api_version,
338 p_init_msg_list => OKL_API.G_FALSE,
339 x_return_status => x_return_status,
340 x_msg_count => x_msg_count,
341 x_msg_data => x_msg_data,
342 p_contract_id => p_khr_id,
343 p_currency_code => l_contract_info_rec.currency_code,
344 p_start_date => l_start_date,
345 p_due_date => l_to_date,
346 p_principal_basis => 'ACTUAL');
347 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
348 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
349 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
350 RAISE OKL_API.G_EXCEPTION_ERROR;
351 END IF;
352 ELSIF l_principal_basis = 'SCHEDULED' THEN
353 /*OPEN l_int_calc_basis_csr(p_khr_id);
354 FETCH l_int_calc_basis_csr INTO l_int_calc_basis;
355 CLOSE l_int_calc_basis_csr;*/
356
357 --change for bug fix 4905791
358 IF (l_int_calc_basis IN ('REAMORT', 'FIXED')) THEN
359 OPEN l_interest_due_csr(p_khr_id, l_to_date);
360 FETCH l_interest_due_csr INTO l_interest_due;
361 CLOSE l_interest_due_csr;
362 ELSIF (l_int_calc_basis = 'FLOAT') THEN
363 LOOP
364 OKL_STREAM_GENERATOR_PVT.get_next_billing_date(
365 p_api_version => l_api_version,
366 p_init_msg_list => OKL_API.G_FALSE,
367 p_khr_id => p_khr_id,
368 p_billing_date => l_start_date,
369 x_next_due_date => l_due_date,
370 x_next_period_start_date => l_next_period_start_date,
371 x_next_period_end_date => l_next_period_end_date,
372 x_return_status => x_return_status,
373 x_msg_count => x_msg_count,
374 x_msg_data => x_msg_data);
375 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
376 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
377 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
378 RAISE OKL_API.G_EXCEPTION_ERROR;
379 END IF;
380 --dkagrawa changed condition from > to >= for bug 4742907
381 --dkagrawa handled nvl for fully billed contract bug#6660659
382 IF nvl(l_due_date,l_to_date) >= l_to_date THEN
383 l_due_date := l_to_date;
384 END IF;
385 l_interest_amt := OKL_VARIABLE_INTEREST_PVT.calculate_total_interest_due(
386 p_api_version => l_api_version,
387 p_init_msg_list => OKL_API.G_FALSE,
388 x_return_status => x_return_status,
389 x_msg_count => x_msg_count,
390 x_msg_data => x_msg_data,
391 p_contract_id => p_khr_id,
392 p_currency_code => l_contract_info_rec.currency_code,
393 p_start_date => l_next_period_start_date,
394 p_due_date => l_due_date,
395 p_principal_basis => 'SCHEDULED');
396
397 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
398 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
399 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
400 RAISE OKL_API.G_EXCEPTION_ERROR;
401 END IF;
402
403 l_interest_due := l_interest_due + l_interest_amt;
404 IF l_due_date >= l_to_date THEN
405 EXIT;
406 END IF;
407 l_start_date := l_due_date;
408 END LOOP;
409 END IF;
410 END IF;
411 END IF;
412 IF(NVL(l_debug_enabled,'N')='Y') THEN
413 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_interest_due');
414 END IF;
415
416 RETURN l_interest_due;
417 EXCEPTION
418 WHEN OKL_API.G_EXCEPTION_ERROR THEN
419 x_return_status := OKL_API.G_RET_STS_ERROR;
420 RETURN l_interest_due;
421 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
422 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
423 RETURN l_interest_due;
424 WHEN OTHERS THEN
425 IF l_principal_basis_csr%ISOPEN THEN
426 CLOSE l_principal_basis_csr;
427 END IF;
428 IF l_contract_info_csr%ISOPEN THEN
429 CLOSE l_contract_info_csr;
430 END IF;
431 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
432 OKL_API.SET_MESSAGE(
433 p_app_name => G_APP_NAME,
434 p_msg_name => G_UNEXPECTED_ERROR,
435 p_token1 => G_SQLCODE_TOKEN,
436 p_token1_value => SQLCODE,
437 p_token2 => G_SQLERRM_TOKEN,
438 p_token2_value => SQLERRM);
439 RETURN l_interest_due;
440 END get_interest_due;
441
442 ------------------------------------------------------------------------------
443 -- Start of Comments
444 -- Created By: dkagrawa
445 -- Function Name: get_principal_bal
446 -- Description: This Function is called to get principal balance on a
447 -- contract for a loan as of a given date
448 -- Inputs :
449 -- Output : Principal Balance
450 -- Dependencies:
451 -- Parameters: Contract id, Effective Date
452 -- Version: 1.0
453 -- History : sechawla 02-may-08 6939451 Set the contract id and deal type
454 -- when default proncipal basis is used.
455 -- End of Comments
456 -----------------------------------------------------------------------------
457
458 FUNCTION get_principal_bal(
459 x_return_status OUT NOCOPY VARCHAR2,
460 p_khr_id IN NUMBER,
461 p_kle_id IN NUMBER,
462 p_date IN DATE) RETURN NUMBER IS
463
464 l_api_version CONSTANT NUMBER := 1.0;
465 x_msg_count NUMBER;
466 x_msg_data VARCHAR2(2000);
467 l_debug_enabled VARCHAR2(1);
468 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_PRINCIPAL_BAL';
469
470 l_start_date DATE;
471 l_principal_basis okl_k_rate_params.principal_basis_code%TYPE;
472 l_principal_balance_tbl okl_variable_interest_pvt.principal_balance_tbl_typ;
473 l_principal_bal NUMBER;
474 l_stream_element_date DATE;
475
476 CURSOR l_principal_basis_csr(cp_khr_id IN NUMBER) IS
477 SELECT chr.start_date, rpm.principal_basis_code
478 FROM okc_k_headers_b chr,
479 okl_k_headers khr,
480 okl_k_rate_params rpm
481 WHERE chr.id = khr.id
482 AND rpm.khr_id = khr.id
483 AND rpm.parameter_type_code = 'ACTUAL'
484 AND TRUNC(SYSDATE) BETWEEN rpm.effective_from_date AND NVL(rpm.effective_to_date, TRUNC(SYSDATE))
485 AND khr.id = cp_khr_id;
486
487 Cursor sch_asset_prin_bal_date_csr (p_contract_id NUMBER,
488 p_line_id NUMBER,
489 p_due_date DATE) IS
490
491 SELECT MAX(sel.stream_element_date)
492 FROM
493 okl_strm_elements sel
494 ,okl_streams str
495 ,okl_strm_type_v sty
496 WHERE sel.stm_id = str.id
497 AND str.khr_id = p_contract_id
498 AND str.kle_id = p_line_id
499 AND str.say_code = 'CURR'
500 AND str.active_yn = 'Y'
501 AND sel.stream_element_date <= p_due_date
502 AND str.sty_id = sty.id
503 AND sty.stream_type_purpose = 'PRINCIPAL_BALANCE';
504
505 Cursor sch_ctr_prin_bal_date_csr (p_contract_id NUMBER,
506 p_due_date DATE) IS
507 SELECT MAX(sel.stream_element_date)
508 FROM
509 okl_strm_elements sel
510 ,okl_streams str
511 ,okl_strm_type_v sty
512 WHERE sel.stm_id = str.id
513 AND str.khr_id = p_contract_id
514 AND str.say_code = 'CURR'
515 AND str.active_yn = 'Y'
516 AND sel.stream_element_date <= p_due_date
517 AND str.sty_id = sty.id
518 AND sty.stream_type_purpose = 'PRINCIPAL_BALANCE';
519
520 --change for bug fix 4905791
521 CURSOR l_int_calc_basis_csr(cp_khr_id IN NUMBER) IS
522 SELECT ppm.interest_calculation_basis
523 ,ppm.revenue_recognition_method
524 FROM okl_k_headers khr,
525 okl_product_parameters_v ppm
526 WHERE khr.id = cp_khr_id
527 AND khr.pdt_id = ppm.id;
528
529 -- sechawla 02-may-08 6939451 Addec this cursor
530 Cursor contract_csr (p_contract_id NUMBER) IS
531 SELECT deal_type
532 FROM okl_k_headers
533 WHERE id = p_contract_id;
534
535 l_int_calc_basis OKL_PRODUCT_PARAMETERS_V.interest_calculation_basis%TYPE;
536 l_rev_rec_mthd OKL_PRODUCT_PARAMETERS_V.revenue_recognition_method%TYPE;
537
538 BEGIN
539 l_debug_enabled := okl_debug_pub.check_log_enabled;
540 IF(NVL(l_debug_enabled,'N')='Y') THEN
541 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_principal_bal');
542 END IF;
543
544 l_principal_bal := 0;
545 x_return_status := OKL_API.G_RET_STS_SUCCESS;
546 IF ( p_khr_id IS NULL ) THEN
547 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
548 END IF;
549
550 OPEN l_principal_basis_csr(p_khr_id);
551 FETCH l_principal_basis_csr INTO l_start_date,l_principal_basis;
552 CLOSE l_principal_basis_csr;
553
554 --change for bug fix 4905791
555 IF (l_principal_basis IS NULL) THEN
556 --this is set to SCHEDULED so that the code works for Fixed Loans as well
557 l_principal_basis := 'SCHEDULED';
558
559 -- sechawla 02-may-08 6939451 : When interest rate parametrs are not defined on the contract,
560 -- default principal basis 'SCHEDULED' is used, as per the existing assignment above
561 -- But in OKL_VARIABLE_INTEREST_PVT, the code tries to fetch principal basis again from the
562 -- interst rate paramaters on the contract, and fails when not found. This check is done based upon the
563 -- value of OKL_VARIABLE_INTEREST_PVT.G_CONTRACT_ID. If this global is not set, validation is done, but
564 -- If it is set, validation is by passed. Since in this case, validation is not needed, setting the
565 -- following 2 globals here, so OKL_VARIABLE_INTEREST_PVT can proceed with the default principal basis.
566 OKL_VARIABLE_INTEREST_PVT.G_CONTRACT_ID := p_khr_id;
567 OPEN contract_csr (p_khr_id);
568 FETCH contract_csr INTO OKL_VARIABLE_INTEREST_PVT.G_DEAL_TYPE;
569 CLOSE contract_csr;
570 -- sechawla 02-may-08 6939451 : end
571
572 END IF;
573
574
575 --change for bug fix 4905791
576 OPEN l_int_calc_basis_csr(p_khr_id);
577 FETCH l_int_calc_basis_csr INTO l_int_calc_basis, l_rev_rec_mthd;
578 CLOSE l_int_calc_basis_csr;
579
580 --change for bug fix 4905791
581 IF (l_principal_basis = 'ACTUAL' OR l_rev_rec_mthd = 'ACTUAL') THEN
582 OKL_VARIABLE_INTEREST_PVT.prin_date_range_var_rate_ctr (
583 p_api_version => l_api_version,
584 p_init_msg_list => OKL_API.G_FALSE,
585 x_return_status => x_return_status,
586 x_msg_count => x_msg_count,
587 x_msg_data => x_msg_data,
588 p_contract_id => p_khr_id,
589 p_line_id => p_kle_id,
590 p_start_date => l_start_date,
591 p_due_date => p_date,
592 p_principal_basis => 'ACTUAL',
593 x_principal_balance_tbl => l_principal_balance_tbl);
594
595 ELSIF l_principal_basis = 'SCHEDULED' THEN
596 IF (p_kle_id IS NOT NULL) THEN
597 OPEN sch_asset_prin_bal_date_csr(p_khr_id, p_kle_id,p_date);
598 FETCH sch_asset_prin_bal_date_csr INTO l_stream_element_date;
599 CLOSE sch_asset_prin_bal_date_csr;
600 ELSE
601 OPEN sch_ctr_prin_bal_date_csr(p_khr_id, p_date);
602 FETCH sch_ctr_prin_bal_date_csr INTO l_stream_element_date;
603 CLOSE sch_ctr_prin_bal_date_csr;
604 END IF;
605
606 IF (l_stream_element_date IS NULL) THEN
607 RETURN 0;
608 END IF;
609
610 OKL_VARIABLE_INTEREST_PVT.prin_date_range_var_rate_ctr (
611 p_api_version => l_api_version,
612 p_init_msg_list => OKL_API.G_FALSE,
613 x_return_status => x_return_status,
614 x_msg_count => x_msg_count,
615 x_msg_data => x_msg_data,
616 p_contract_id => p_khr_id,
617 p_line_id => p_kle_id,
618 p_start_date => l_stream_element_date,
619 p_due_date => l_stream_element_date,
620 p_principal_basis => 'SCHEDULED',
621 x_principal_balance_tbl => l_principal_balance_tbl);
622 END IF;
623
624 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
625 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
626 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
627 RAISE OKL_API.G_EXCEPTION_ERROR;
628 END IF;
629
630 IF l_principal_balance_tbl.COUNT > 0 THEN
631 l_principal_bal := l_principal_balance_tbl(l_principal_balance_tbl.COUNT).principal_balance;
632 END IF;
633
634 IF(NVL(l_debug_enabled,'N')='Y') THEN
635 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_principal_bal');
636 END IF;
637
638 RETURN l_principal_bal;
639 EXCEPTION
640 WHEN OKL_API.G_EXCEPTION_ERROR THEN
641
642 -- sechawla 02-may-08 6939451
643 IF contract_csr%ISOPEN THEN
644 CLOSE contract_csr;
645 END IF;
646
647 x_return_status := OKL_API.G_RET_STS_ERROR;
648 RETURN l_principal_bal;
649 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
650
651 -- sechawla 02-may-08 6939451
652 IF contract_csr%ISOPEN THEN
653 CLOSE contract_csr;
654 END IF;
655
656 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
657 RETURN l_principal_bal;
658 WHEN OTHERS THEN
659
660 -- sechawla 02-may-08 6939451
661 IF contract_csr%ISOPEN THEN
662 CLOSE contract_csr;
663 END IF;
664
665 IF l_principal_basis_csr%ISOPEN THEN
666 CLOSE l_principal_basis_csr;
667 END IF;
668 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
669 OKL_API.SET_MESSAGE(
670 p_app_name => G_APP_NAME,
671 p_msg_name => G_UNEXPECTED_ERROR,
672 p_token1 => G_SQLCODE_TOKEN,
673 p_token1_value => SQLCODE,
674 p_token2 => G_SQLERRM_TOKEN,
675 p_token2_value => SQLERRM);
676 RETURN l_principal_bal;
677 END get_principal_bal;
678
679 ------------------------------------------------------------------------------
680 -- Start of Comments
681 -- Created By: dkagrawa
682 -- Function Name: get_principal_paid
683 -- Description: This Function is called to get principal paid for a
684 -- date range for revolving loan
685 -- Inputs :
686 -- Output : Principal Paid
687 -- Dependencies:
688 -- Parameters: Contract id, Asset Line id, From Date, To Date
689 -- Version: 1.0
690 -- End of Comments
691 -----------------------------------------------------------------------------
692
693 FUNCTION get_principal_paid(
694 x_return_status OUT NOCOPY VARCHAR2,
695 p_khr_id IN NUMBER,
696 p_kle_id IN NUMBER,
697 p_from_date IN DATE,
698 p_to_date IN DATE DEFAULT SYSDATE) RETURN NUMBER IS
699
700 l_debug_enabled VARCHAR2(1);
701 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_PRINCIPAL_PAID';
702 x_msg_count NUMBER;
703 x_msg_data VARCHAR2(2000);
704
705 l_principal_paid NUMBER;
706
707 -- Begin bug# 13893142
708 CURSOR l_principal_paid_csr(cp_khr_id IN NUMBER, cp_from_date IN DATE, cp_to_date IN DATE) IS
709 SELECT SUM(principal_paid_amount) principal_paid_amount
710 FROM
711 (
712 SELECT SUM(AMOUNT)- SUM(LINE_AMOUNT_REMAINING_WOTAX) principal_paid_amount
713 FROM okl_bpd_ar_inv_lines_v lpt1,
714 okl_strm_type_b sty,
715 ar_payment_schedules_all aps,
716 okc_k_headers_b khr
717 WHERE contract_id=cp_khr_id
718 AND lpt1.contract_id = khr.id
719 AND lpt1.sty_id = sty.id
720 AND lpt1.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
721 AND sty.stream_type_purpose IN
722 ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT','PRINCIPAL_CATCHUP')
723 AND TRUNC(aps.trx_date)
724 BETWEEN TRUNC(NVL(cp_from_date, khr.start_date)) AND TRUNC(NVL(cp_to_date, SYSDATE))
725 UNION ALL
726 SELECT NVL(SUM(sel.amount),0) principal_paid_amount
727 FROM okl_strm_type_v sty,
728 okl_streams_v stm,
729 okl_strm_elements sel,
730 okc_k_headers_b khr
731 WHERE stm.khr_id = cp_khr_id
732 AND stm.kle_id = NVL(null, stm.kle_id)
733 AND stm.khr_id = khr.id
734 AND stm.sty_id = sty.id
735 AND sty.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
736 AND stm.id = sel.stm_id
737 AND TRUNC(sel.stream_element_date)
738 BETWEEN TRUNC(NVL(cp_from_date, khr.start_date)) AND TRUNC(NVL(cp_to_date, SYSDATE))
739 );
740 -- End Bug# 13893142
741
742 -- Begin - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
743 -- Begin bug 6456733
744 -- CURSOR l_principal_paid_csr(cp_khr_id IN NUMBER, cp_kle_id IN NUMBER, cp_from_date IN DATE, cp_to_date IN DATE) IS
745
746 -- Start of code commented as part of Bug# 13893142
747 /* CURSOR l_principal_paid_csr(cp_khr_id IN NUMBER, cp_from_date IN DATE, cp_to_date IN DATE) IS
748 SELECT SUM(principal_paid_amount) principal_paid_amount
749 FROM
750 (
751 SELECT SUM(AMOUNT)- SUM(AMOUNT_LINE_ITEMS_REMAINING) principal_paid_amount
752 FROM okl_bpd_ar_inv_lines_v lpt1
753 where contract_id=cp_khr_id
754 and RECEIVABLES_INVOICE_ID in
755 (
756 SELECT RECEIVABLES_INVOICE_ID
757 FROM
758 okl_bpd_ar_inv_lines_v lpt,
759 okl_strm_type_b sty,
760 ar_payment_schedules_all aps,
761 okl_k_headers_full_v khr
762 WHERE
763 lpt.contract_id = lpt1.contract_id
764 AND lpt.contract_number = khr.contract_number
765 AND lpt.sty_id = sty.id
766 AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
767 AND sty.stream_type_purpose IN
768 ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT','PRINCIPAL_CATCHUP')
769 AND TRUNC(aps.trx_date)
770 BETWEEN TRUNC(NVL(cp_from_date, khr.start_date)) AND TRUNC(NVL(cp_to_date, SYSDATE))
771 )
772 group by RECEIVABLES_INVOICE_ID
773 having SUM(AMOUNT_LINE_ITEMS_REMAINING)< SUM(AMOUNT)
774 UNION ALL
775 SELECT NVL(SUM(sel.amount),0) principal_paid_amount
776 FROM okl_strm_type_v sty,
777 okl_streams_v stm,
778 okl_strm_elements sel,
779 okc_k_headers_b khr
780 WHERE stm.khr_id = cp_khr_id
781 AND stm.kle_id = NVL(null, stm.kle_id)
782 AND stm.khr_id = khr.id
783 AND stm.sty_id = sty.id
784 AND sty.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
785 AND stm.id = sel.stm_id
786 AND TRUNC(sel.stream_element_date)
787 BETWEEN TRUNC(NVL(cp_from_date, khr.start_date)) AND TRUNC(NVL(cp_to_date, SYSDATE))
788 ); */
789 -- End of code commented as part of Bug# 13893142
790
791
792 -- End bug 6456733
793 --SELECT SUM(principal_paid_amount) FROM
794 --(
795 -- SELECT NVL(SUM(app.amount_applied),0) principal_paid_amount
796 --SELECT NVL(SUM(app.line_applied),0) principal_paid_amount --End bug# 5767426
797 --FROM ar_receivable_applications_all app,
798 --ar_cash_receipts_all cra,
799 --ar_payment_schedules_all sch,
800 --okl_bpd_tld_ar_lines_v tld,
801 --okl_strm_type_v sty,
802 --okl_k_headers_full_v khr
803 --WHERE TRUNC(cra.receipt_date) BETWEEN TRUNC(NVL(cp_from_date, cra.receipt_date)) AND TRUNC(NVL(cp_to_date, SYSDATE))
804 --AND app.cash_receipt_id = cra.cash_receipt_id
805 --AND app.status = 'APP'
806 --AND app.applied_payment_schedule_id = sch.payment_schedule_id
807 --AND sch.class = 'INV'
808 --AND sch.customer_trx_id = tld.customer_trx_id
809 --AND tld.khr_id = cp_khr_id
810 --AND tld.kle_id = NVL(cp_kle_id, tld.kle_id)
811 --AND tld.khr_id = khr.id
812 --AND tld.sty_id = sty.id
813 --AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT', 'PRINCIPAL_CATCHUP')
814 --UNION
815 --SELECT NVL(SUM(sel.amount),0) principal_paid_amount
816 --FROM okl_strm_type_v sty,
817 --okl_streams_v stm,
818 --okl_strm_elements sel,
819 --okc_k_headers_b khr
820 --WHERE stm.khr_id = cp_khr_id
821 --AND stm.kle_id = NVL(cp_kle_id, stm.kle_id)
822 --AND stm.khr_id = khr.id
823 --AND stm.sty_id = sty.id
824 --AND sty.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
825 --AND stm.id = sel.stm_id
826 --AND TRUNC(sel.stream_element_date) BETWEEN TRUNC(NVL(cp_from_date, khr.start_date)) AND TRUNC(NVL(cp_to_date, SYSDATE)));
827 -- End - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
828
829
830 BEGIN
831 l_debug_enabled := okl_debug_pub.check_log_enabled;
832 IF(NVL(l_debug_enabled,'N')='Y') THEN
833 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_principal_paid');
834 END IF;
835
836 l_principal_paid := 0;
837 x_return_status := OKL_API.G_RET_STS_SUCCESS;
838 IF ( p_khr_id IS NULL ) THEN
839 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
840 END IF;
841 -- Begin bug 6456733
842 --OPEN l_principal_paid_csr(p_khr_id, p_kle_id, p_from_date, p_to_date);
843 OPEN l_principal_paid_csr(p_khr_id, p_from_date, p_to_date);
844 -- End bug 6456733
845 FETCH l_principal_paid_csr INTO l_principal_paid;
846 CLOSE l_principal_paid_csr;
847 IF(NVL(l_debug_enabled,'N')='Y') THEN
848 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_principal_paid');
849 END IF;
850
851 RETURN l_principal_paid;
852 EXCEPTION
853 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
854 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
855 RETURN l_principal_paid;
856 WHEN OTHERS THEN
857 IF l_principal_paid_csr%ISOPEN THEN
858 CLOSE l_principal_paid_csr;
859 END IF;
860 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
861 OKL_API.SET_MESSAGE(
862 p_app_name => G_APP_NAME,
863 p_msg_name => G_UNEXPECTED_ERROR,
864 p_token1 => G_SQLCODE_TOKEN,
865 p_token1_value => SQLCODE,
866 p_token2 => G_SQLERRM_TOKEN,
867 p_token2_value => SQLERRM);
868 RETURN l_principal_paid;
869 END get_principal_paid;
870
871 ------------------------------------------------------------------------------
872 -- Start of Comments
873 -- Created By: dkagrawa
874 -- Function Name:
875 -- Description: This Function is called to get an indicator Y/N if
876 -- the interest rate has changed
877 -- Inputs :
878 -- Output : interest rate change falg
879 -- Dependencies:
880 -- Parameters: Contract id
881 -- Version: 1.0
882 -- End of Comments
883 -----------------------------------------------------------------------------
884
885 FUNCTION get_interest_rate_change_flag(
886 x_return_status OUT NOCOPY VARCHAR2,
887 p_khr_id IN NUMBER) RETURN VARCHAR2 IS
888
889 l_debug_enabled VARCHAR2(1);
890 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_INTEREST_RATE_CHANGE_FLAG';
891 x_msg_count NUMBER;
892 x_msg_data VARCHAR2(2000);
893
894 l_interest_rate_change_flag VARCHAR2(1);
895 l_effective_int_rate NUMBER;
896
897 CURSOR l_var_int_params_csr(cp_khr_id IN NUMBER) IS
898 SELECT interest_calc_end_date, interest_rate
899 FROM okl_var_int_params
900 WHERE khr_id = cp_khr_id
901 ORDER BY interest_calc_end_date DESC;
902
903 l_var_int_params_rec l_var_int_params_csr%ROWTYPE;
904 BEGIN
905 l_debug_enabled := okl_debug_pub.check_log_enabled;
906 IF(NVL(l_debug_enabled,'N')='Y') THEN
907 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_interest_rate_change_flag');
908 END IF;
909
910 l_interest_rate_change_flag := 'N';
911 IF ( p_khr_id IS NULL ) THEN
912 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
913 END IF;
914
915 OPEN l_var_int_params_csr(p_khr_id);
916 FETCH l_var_int_params_csr INTO l_var_int_params_rec;
917 CLOSE l_var_int_params_csr;
918 l_effective_int_rate := get_effective_int_rate(
919 x_return_status => x_return_status,
920 p_khr_id => p_khr_id,
921 p_effective_date => l_var_int_params_rec.interest_calc_end_date + 1);
922
923 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
924 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
925 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
926 RAISE OKL_API.G_EXCEPTION_ERROR;
927 END IF;
928 IF l_effective_int_rate <> l_var_int_params_rec.interest_rate THEN
929 l_interest_rate_change_flag := 'Y';
930 END IF;
931
932 IF(NVL(l_debug_enabled,'N')='Y') THEN
933 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_interest_rate_change_flag');
934 END IF;
935
936 RETURN l_interest_rate_change_flag;
937 EXCEPTION
938 WHEN OKL_API.G_EXCEPTION_ERROR THEN
939 x_return_status := OKL_API.G_RET_STS_ERROR;
940 RETURN l_interest_rate_change_flag;
941 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
942 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
943 RETURN l_interest_rate_change_flag;
944 WHEN OTHERS THEN
945 IF l_var_int_params_csr%ISOPEN THEN
946 CLOSE l_var_int_params_csr;
947 END IF;
948 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
949 OKL_API.SET_MESSAGE(
950 p_app_name => G_APP_NAME,
951 p_msg_name => G_UNEXPECTED_ERROR,
952 p_token1 => G_SQLCODE_TOKEN,
953 p_token1_value => SQLCODE,
954 p_token2 => G_SQLERRM_TOKEN,
955 p_token2_value => SQLERRM);
956 RETURN l_interest_rate_change_flag;
957 END get_interest_rate_change_flag;
958
959 ------------------------------------------------------------------------------
960 -- Start of Comments
961 -- Created By: dkagrawa
962 -- Function Name: get_effective_int_rate
963 -- Description: This Function is called to get effective interest rate
964 -- as of a given date
965 -- Inputs :
966 -- Output : Effective interest rate
967 -- Dependencies:
968 -- Parameters: Contract id, Effective Date
969 -- Version: 1.0
970 -- End of Comments
971 -----------------------------------------------------------------------------
972
973 FUNCTION get_effective_int_rate(
974 x_return_status OUT NOCOPY VARCHAR2,
975 p_khr_id IN NUMBER,
976 p_effective_date IN DATE) RETURN NUMBER IS
977 --dkagrawa changed cursor to use view OKL_PROD_QLTY_VAL_UV than okl_product_parameters_v
978 CURSOR l_calc_basis_csr(cp_khr_id IN NUMBER) IS
979 SELECT ppm.quality_val interest_calculation_basis
980 , end_date
981 FROM okl_k_headers_full_v khr,
982 okl_prod_qlty_val_uv ppm
983 WHERE khr.pdt_id = ppm.pdt_id
984 AND ppm.quality_name = 'INTEREST_CALCULATION_BASIS'
985 AND khr.id = cp_khr_id;
986
987 l_api_version CONSTANT NUMBER := 1.0;
988 x_msg_count NUMBER;
989 x_msg_data VARCHAR2(2000);
990 l_debug_enabled VARCHAR2(1);
991 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_EFFECTIVE_INT_RATE';
992
993 l_interest_rate_tbl okl_variable_interest_pvt.interest_rate_tbl_type;
994 l_effective_int_rate NUMBER;
995 l_process_flag okl_product_parameters_v.interest_calculation_basis%TYPE;
996 l_end_date okl_k_headers_full_v.end_date%TYPE;
997 l_effective_date DATE := NULL;
998
999 BEGIN
1000 l_debug_enabled := okl_debug_pub.check_log_enabled;
1001 IF(NVL(l_debug_enabled,'N')='Y') THEN
1002 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_effective_int_rate');
1003 END IF;
1004
1005 l_effective_int_rate := 0;
1006 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1007 IF ( p_khr_id IS NULL ) THEN
1008 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1009 END IF;
1010
1011 OPEN l_calc_basis_csr(p_khr_id);
1012 FETCH l_calc_basis_csr INTO l_process_flag, l_end_date;
1013 CLOSE l_calc_basis_csr;
1014
1015 l_effective_date := p_effective_date;
1016 IF (l_effective_date > l_end_date) THEN
1017 l_effective_date := l_end_date;
1018 END IF;
1019
1020 OKL_VARIABLE_INTEREST_PVT.interest_date_range (
1021 p_api_version => l_api_version,
1022 p_init_msg_list => OKL_API.G_FALSE,
1023 x_return_status => x_return_status,
1024 x_msg_count => x_msg_count,
1025 x_msg_data => x_msg_data,
1026 p_contract_id => p_khr_id,
1027 p_start_date => l_effective_date,
1028 p_end_date => l_effective_date,
1029 p_process_flag => l_process_flag,
1030 x_interest_rate_tbl => l_interest_rate_tbl);
1031
1032 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
1033 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1034 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
1035 RAISE OKL_API.G_EXCEPTION_ERROR;
1036 END IF;
1037
1038 IF l_interest_rate_tbl.COUNT > 0 THEN
1039 l_effective_int_rate := l_interest_rate_tbl(l_interest_rate_tbl.COUNT).rate;
1040 END IF;
1041
1042 IF(NVL(l_debug_enabled,'N')='Y') THEN
1043 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_effective_int_rate');
1044 END IF;
1045
1046 RETURN l_effective_int_rate;
1047 EXCEPTION
1048 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1049 x_return_status := OKL_API.G_RET_STS_ERROR;
1050 RETURN l_effective_int_rate;
1051 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1052 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1053 RETURN l_effective_int_rate;
1054 WHEN OTHERS THEN
1055 IF l_calc_basis_csr%ISOPEN THEN
1056 CLOSE l_calc_basis_csr;
1057 END IF;
1058 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1059 OKL_API.SET_MESSAGE(
1060 p_app_name => G_APP_NAME,
1061 p_msg_name => G_UNEXPECTED_ERROR,
1062 p_token1 => G_SQLCODE_TOKEN,
1063 p_token1_value => SQLCODE,
1064 p_token2 => G_SQLERRM_TOKEN,
1065 p_token2_value => SQLERRM);
1066 RETURN l_effective_int_rate;
1067 END get_effective_int_rate;
1068
1069 ------------------------------------------------------------------------------
1070 -- Start of Comments
1071 -- Created By: dkagrawa
1072 -- Function Name: get_interest_due_unbilled
1073 -- Description: This Function is called to get Interest due but not billed
1074 -- as of a given date for a Loan
1075 -- Inputs :
1076 -- Output : Unbilled Interest due
1077 -- Dependencies:
1078 -- Parameters: Contract id, Effective Date
1079 -- Version: 1.0
1080 -- End of Comments
1081 -----------------------------------------------------------------------------
1082
1083 FUNCTION get_interest_due_unbilled(
1084 x_return_status OUT NOCOPY VARCHAR2,
1085 p_khr_id IN NUMBER,
1086 p_effective_date IN DATE) RETURN NUMBER IS
1087
1088 l_debug_enabled VARCHAR2(1);
1089 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_INTEREST_DUE_UNBILLED';
1090 x_msg_count NUMBER;
1091 x_msg_data VARCHAR2(2000);
1092
1093 l_interest_billed NUMBER;
1094 l_interest_due NUMBER;
1095 l_interest_due_unbilled NUMBER;
1096 l_start_date DATE;
1097
1098 CURSOR l_start_date_csr(cp_khr_id IN NUMBER) IS
1099 SELECT chr.start_date
1100 FROM OKC_K_HEADERS_B chr,
1101 OKL_K_HEADERS khr
1102 WHERE chr.id = khr.id
1103 AND khr.id = cp_khr_id;
1104 BEGIN
1105 l_debug_enabled := okl_debug_pub.check_log_enabled;
1106 IF(NVL(l_debug_enabled,'N')='Y') THEN
1107 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_interest_due_unbilled');
1108 END IF;
1109
1110 l_interest_billed := 0;
1111 l_interest_due := 0;
1112 l_interest_due_unbilled := 0;
1113 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1114 IF ( p_khr_id IS NULL ) THEN
1115 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1116 END IF;
1117
1118 OPEN l_start_date_csr(p_khr_id);
1119 FETCH l_start_date_csr INTO l_start_date;
1120 CLOSE l_start_date_csr;
1121 l_interest_due := get_interest_due(
1122 x_return_status => x_return_status,
1123 p_khr_id => p_khr_id,
1124 p_to_date => p_effective_date);
1125 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1126 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1127 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1128 RAISE OKL_API.G_EXCEPTION_ERROR;
1129 END IF;
1130 l_interest_billed := get_interest_billed(
1131 x_return_status => x_return_status,
1132 p_khr_id => p_khr_id,
1133 p_from_date => l_start_date,
1134 p_to_date => p_effective_date);
1135 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1136 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1137 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1138 RAISE OKL_API.G_EXCEPTION_ERROR;
1139 END IF;
1140 l_interest_due_unbilled := l_interest_due - l_interest_billed;
1141
1142 IF(NVL(l_debug_enabled,'N')='Y') THEN
1143 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_interest_due_unbilled');
1144 END IF;
1145
1146 RETURN l_interest_due_unbilled;
1147 EXCEPTION
1148 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1149 x_return_status := OKL_API.G_RET_STS_ERROR;
1150 RETURN l_interest_due_unbilled;
1151 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1152 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1153 RETURN l_interest_due_unbilled;
1154 WHEN OTHERS THEN
1155 IF l_start_date_csr%ISOPEN THEN
1156 CLOSE l_start_date_csr;
1157 END IF;
1158 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1159 OKL_API.SET_MESSAGE(
1160 p_app_name => G_APP_NAME,
1161 p_msg_name => G_UNEXPECTED_ERROR,
1162 p_token1 => G_SQLCODE_TOKEN,
1163 p_token1_value => SQLCODE,
1164 p_token2 => G_SQLERRM_TOKEN,
1165 p_token2_value => SQLERRM);
1166 RETURN l_interest_due_unbilled;
1167 END get_interest_due_unbilled;
1168
1169 ------------------------------------------------------------------------------
1170 -- Start of Comments
1171 -- Created By: dkagrawa
1172 -- Function Name: get_principal_billed
1173 -- Description: This Function is called to get Principal Billed for a loan contract
1174 -- as of a given date range
1175 -- Inputs :
1176 -- Output : Principal billed
1177 -- Dependencies:
1178 -- Parameters: Contract id, Asset Line id, From Date, To Date
1179 -- Version: 1.0
1180 -- End of Comments
1181 -----------------------------------------------------------------------------
1182
1183 FUNCTION get_principal_billed(
1184 x_return_status OUT NOCOPY VARCHAR2,
1185 p_khr_id IN NUMBER,
1186 p_kle_id IN NUMBER,
1187 p_from_date IN DATE,
1188 p_to_date IN DATE DEFAULT SYSDATE) RETURN NUMBER IS
1189
1190 l_debug_enabled VARCHAR2(1);
1191 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_PRINCIPAL_BILLED';
1192 x_msg_count NUMBER;
1193 x_msg_data VARCHAR2(2000);
1194
1195 l_principal_billed NUMBER;
1196
1197 -- Begin - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
1198 -- Begin bug 6456733
1199 --CURSOR l_principal_billed_csr(cp_khr_id IN NUMBER, cp_kle_id IN NUMBER, cp_from_date IN DATE, cp_to_date IN DATE) IS
1200 CURSOR l_principal_billed_csr(cp_khr_id IN NUMBER, cp_from_date IN DATE, cp_to_date IN DATE) IS
1201 -- dcshanmu bug 6734738 start
1202 --SELECT NVL(SUM(AMOUNT),0) principal_billed_amount
1203 --FROM okl_bpd_ar_inv_lines_v lpt1
1204 --where contract_id=cp_khr_id
1205 --and RECEIVABLES_INVOICE_ID in
1206 --(
1207 SELECT NVL(SUM(AMOUNT),0) principal_billed_amount --RECEIVABLES_INVOICE_ID
1208 -- dcshanmu bug 6734738 end
1209 FROM okl_bpd_ar_inv_lines_v lpt,
1210 okl_strm_type_b sty,
1211 ar_payment_schedules_all aps,
1212 okl_k_headers_full_v khr
1213 WHERE
1214 -- dcshanmu bug 6734738 start
1215 lpt.contract_id = cp_khr_id
1216 -- dcshanmu bug 6734738 end
1217 AND lpt.contract_number = khr.contract_number
1218 AND lpt.sty_id = sty.id
1219 AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
1220 AND sty.stream_type_purpose IN
1221 ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT','PRINCIPAL_CATCHUP')
1222 AND TRUNC(aps.trx_date) BETWEEN TRUNC(NVL(cp_from_date, khr.start_date))
1223 AND TRUNC(NVL(cp_to_date, SYSDATE))
1224 -- dcshanmu bug 6734738 start
1225 --)
1226 ;
1227 -- dcshanmu bug 6734738 end
1228 -- End bug 6456733
1229
1230 -- SELECT NVL(SUM(aps.amount_due_original), 0) principal_billed_amount
1231 --SELECT NVL(sum(aps.amount_line_items_original), 0) principal_billed_amount --End bug#5767426
1232 --FROM okl_bpd_tld_ar_lines_v tld,
1233 --okl_strm_type_b sty,
1234 --ar_payment_schedules_all aps,
1235 --okl_k_headers_full_v khr
1236 --WHERE tld.khr_id = cp_khr_id
1237 --AND tld.kle_id = NVL(cp_kle_id, tld.kle_id)
1238 --AND tld.khr_id = khr.id
1239 --AND tld.sty_id = sty.id
1240 --AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT','PRINCIPAL_CATCHUP')
1241 --AND tld.customer_trx_id = aps.customer_trx_id
1242 --AND TRUNC(aps.trx_date) BETWEEN TRUNC(NVL(cp_from_date, khr.start_date)) AND TRUNC(NVL(cp_to_date, SYSDATE));
1243 ---- End - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
1244
1245 BEGIN
1246 l_debug_enabled := okl_debug_pub.check_log_enabled;
1247 IF(NVL(l_debug_enabled,'N')='Y') THEN
1248 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_principal_billed');
1249 END IF;
1250
1251 l_principal_billed := 0;
1252 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1253 IF ( p_khr_id IS NULL ) THEN
1254 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1255 END IF;
1256
1257 -- Begin bug 6456733
1258 --OPEN l_principal_billed_csr(p_khr_id, p_kle_id, p_from_date, p_to_date);
1259 OPEN l_principal_billed_csr(p_khr_id, p_from_date, p_to_date);
1260 -- End bug 6456733
1261 FETCH l_principal_billed_csr INTO l_principal_billed;
1262 CLOSE l_principal_billed_csr;
1263
1264 IF(NVL(l_debug_enabled,'N')='Y') THEN
1265 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_principal_billed');
1266 END IF;
1267
1268 RETURN l_principal_billed;
1269 EXCEPTION
1270 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1271 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1272 RETURN l_principal_billed;
1273 WHEN OTHERS THEN
1274 IF l_principal_billed_csr%ISOPEN THEN
1275 CLOSE l_principal_billed_csr;
1276 END IF;
1277 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1278 OKL_API.SET_MESSAGE(
1279 p_app_name => G_APP_NAME,
1280 p_msg_name => G_UNEXPECTED_ERROR,
1281 p_token1 => G_SQLCODE_TOKEN,
1282 p_token1_value => SQLCODE,
1283 p_token2 => G_SQLERRM_TOKEN,
1284 p_token2_value => SQLERRM);
1285 RETURN l_principal_billed;
1286 END get_principal_billed;
1287
1288 ------------------------------------------------------------------------------
1289 -- Start of Comments
1290 -- Created By: dkagrawa
1291 -- Function Name: get_float_factor_billed
1292 -- Description: This Function is called to get Float Factor Billing Amount
1293 -- for a float factor contract as of a given date
1294 -- Inputs :
1295 -- Output : Float Factor Billed
1296 -- Dependencies:
1297 -- Parameters: Contract id, Effective Date
1298 -- Version: 1.0
1299 -- End of Comments
1300 -----------------------------------------------------------------------------
1301
1302 FUNCTION get_float_factor_billed(
1303 x_return_status OUT NOCOPY VARCHAR2,
1304 p_khr_id IN NUMBER,
1305 p_effective_date IN DATE) RETURN NUMBER IS
1306
1307 l_debug_enabled VARCHAR2(1);
1308 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_FLOAT_FACTOR_BILLED';
1309 x_msg_count NUMBER;
1310 x_msg_data VARCHAR2(2000);
1311
1312 l_float_factor_billed NUMBER;
1313
1314 -- Begin - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
1315 CURSOR l_float_billed_csr(cp_khr_id IN NUMBER, cp_effective_date IN DATE) IS
1316 -- Begin bug 6456733
1317 SELECT NVL(SUM(AMOUNT_DUE_ORIGINAL),0) interest_billed_amount
1318 FROM okl_bpd_ar_inv_lines_v lpt1
1319 where contract_id=cp_khr_id
1320 and RECEIVABLES_INVOICE_ID in
1321 (
1322 SELECT lpt.RECEIVABLES_INVOICE_ID
1323 FROM okl_bpd_ar_inv_lines_v lpt,
1324 okl_strm_type_b sty,
1325 ar_payment_schedules_all aps,
1326 okl_k_headers_full_v khr
1327 WHERE lpt.contract_id = lpt1.contract_id
1328 AND lpt.contract_number = khr.contract_number
1329 AND lpt.sty_id = sty.id
1330 AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
1331 AND sty.stream_type_purpose = 'FLOAT_FACTOR_ADJUSTMENT'
1332 AND TRUNC(aps.trx_date) <= cp_effective_date
1333 );
1334
1335 --SELECT NVL(SUM(aps.amount_due_original), 0) interest_billed_amount
1336 --FROM okl_bpd_tld_ar_lines_v tld,
1337 --okl_strm_type_b sty,
1338 --ar_payment_schedules_all aps,
1339 --okc_k_headers_b khr
1340 --WHERE tld.khr_id = cp_khr_id
1341 --AND tld.khr_id = khr.id
1342 --AND tld.sty_id = sty.id
1343 --AND sty.stream_type_purpose = 'FLOAT_FACTOR_ADJUSTMENT'
1344 --AND tld.customer_trx_id = aps.customer_trx_id
1345 --AND TRUNC(aps.trx_date) <= cp_effective_date;
1346
1347 -- End bug 6456733
1348 -- End - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
1349 BEGIN
1350 l_debug_enabled := okl_debug_pub.check_log_enabled;
1351 IF(NVL(l_debug_enabled,'N')='Y') THEN
1352 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_float_factor_billed');
1353 END IF;
1354
1355 l_float_factor_billed := 0;
1356 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1357 IF ( p_khr_id IS NULL ) THEN
1358 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1359 END IF;
1360
1361 OPEN l_float_billed_csr(p_khr_id, p_effective_date);
1362 FETCH l_float_billed_csr INTO l_float_factor_billed;
1363 CLOSE l_float_billed_csr;
1364
1365 IF(NVL(l_debug_enabled,'N')='Y') THEN
1366 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_float_factor_billed');
1367 END IF;
1368
1369 RETURN l_float_factor_billed;
1370 EXCEPTION
1371 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1372 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1373 RETURN l_float_factor_billed;
1374 WHEN OTHERS THEN
1375 IF l_float_billed_csr%ISOPEN THEN
1376 CLOSE l_float_billed_csr;
1377 END IF;
1378 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1379 OKL_API.SET_MESSAGE(
1380 p_app_name => G_APP_NAME,
1381 p_msg_name => G_UNEXPECTED_ERROR,
1382 p_token1 => G_SQLCODE_TOKEN,
1383 p_token1_value => SQLCODE,
1384 p_token2 => G_SQLERRM_TOKEN,
1385 p_token2_value => SQLERRM);
1386 RETURN l_float_factor_billed;
1387 END get_float_factor_billed;
1388
1389 ------------------------------------------------------------------------------
1390 -- Start of Comments
1391 -- Created By: dkagrawa
1392 -- Function Name: get_loan_payment_billed
1393 -- Description: Loan Payment Billed for a loan contract with
1394 -- a revenue recognition method of Actual
1395 -- Inputs :
1396 -- Output : Loan payment billed
1397 -- Dependencies:
1398 -- Parameters: Contract id, Effective Date
1399 -- Version: 1.0
1400 -- End of Comments
1401 -----------------------------------------------------------------------------
1402
1403 FUNCTION get_loan_payment_billed(
1404 x_return_status OUT NOCOPY VARCHAR2,
1405 p_khr_id IN NUMBER,
1406 p_effective_date IN DATE) RETURN NUMBER IS
1407
1408 l_debug_enabled VARCHAR2(1);
1409 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_LOAN_PAYMENT_BILLED';
1410 x_msg_count NUMBER;
1411 x_msg_data VARCHAR2(2000);
1412
1413 l_loan_payment_billed NUMBER;
1414
1415 -- Begin - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
1416 CURSOR l_loan_payment_billed_csr(cp_khr_id IN NUMBER, cp_effective_date IN DATE) IS
1417 --Begin bug 6456733
1418 SELECT NVL(SUM(lpt1.AMOUNT_DUE_ORIGINAL),0) loan_billed_amount
1419 FROM okl_bpd_ar_inv_lines_v lpt1
1420 where contract_id=cp_khr_id
1421 and RECEIVABLES_INVOICE_ID in
1422 (
1423 SELECT lpt.RECEIVABLES_INVOICE_ID
1424 FROM okl_bpd_ar_inv_lines_v lpt,
1425 okl_strm_type_b sty,
1426 ar_payment_schedules_all aps,
1427 okl_k_headers_full_v khr
1428 WHERE lpt.contract_id = lpt1.contract_id
1429 AND lpt.contract_number = khr.contract_number
1430 AND lpt.sty_id = sty.id
1431 AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
1432 AND sty.stream_type_purpose IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT', 'UNSCHEDULED_LOAN_PAYMENT')
1433 AND TRUNC(aps.trx_date) <= cp_effective_date
1434 );
1435
1436 --SELECT NVL(SUM(aps.amount_due_original), 0) loan_billed_amount
1437 --FROM okl_bpd_tld_ar_lines_v tld,
1438 --okl_strm_type_b sty,
1439 --ar_payment_schedules_all aps,
1440 --okl_k_headers_full_v khr
1441 --WHERE tld.khr_id = cp_khr_id
1442 --AND tld.khr_id = khr.id
1443 --AND tld.sty_id = sty.id
1444 --AND sty.stream_type_purpose IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT', 'UNSCHEDULED_LOAN_PAYMENT')
1445 --AND tld.customer_trx_id = aps.customer_trx_id
1446 --AND TRUNC(aps.trx_date) <= cp_effective_date;
1447
1448 --End bug 6456733
1449 -- End - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
1450
1451 BEGIN
1452 l_debug_enabled := okl_debug_pub.check_log_enabled;
1453 IF(NVL(l_debug_enabled,'N')='Y') THEN
1454 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_loan_payment_billed');
1455 END IF;
1456
1457 l_loan_payment_billed := 0;
1458 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1459 IF ( p_khr_id IS NULL ) THEN
1460 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1461 END IF;
1462
1463 OPEN l_loan_payment_billed_csr(p_khr_id, p_effective_date);
1464 FETCH l_loan_payment_billed_csr INTO l_loan_payment_billed;
1465 CLOSE l_loan_payment_billed_csr;
1466
1467 IF(NVL(l_debug_enabled,'N')='Y') THEN
1468 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_loan_payment_billed');
1469 END IF;
1470
1471 RETURN l_loan_payment_billed;
1472 EXCEPTION
1473 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1474 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1475 RETURN l_loan_payment_billed;
1476 WHEN OTHERS THEN
1477 IF l_loan_payment_billed_csr%ISOPEN THEN
1478 CLOSE l_loan_payment_billed_csr;
1479 END IF;
1480 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1481 OKL_API.SET_MESSAGE(
1482 p_app_name => G_APP_NAME,
1483 p_msg_name => G_UNEXPECTED_ERROR,
1484 p_token1 => G_SQLCODE_TOKEN,
1485 p_token1_value => SQLCODE,
1486 p_token2 => G_SQLERRM_TOKEN,
1487 p_token2_value => SQLERRM);
1488 RETURN l_loan_payment_billed;
1489 END get_loan_payment_billed;
1490
1491 ------------------------------------------------------------------------------
1492 -- Start of Comments
1493 -- Created By: dkagrawa
1494 -- Function Name: get_loan_payment_paid
1495 -- Description: Loan Payment Received for a loan contract with
1496 -- a revenue recognition method of Actual
1497 -- Inputs :
1498 -- Output : Loan payment Paid
1499 -- Dependencies:
1500 -- Parameters: Contract id, Effective Date
1501 -- Version: 1.0
1502 -- End of Comments
1503 -----------------------------------------------------------------------------
1504
1505 FUNCTION get_loan_payment_paid(
1506 x_return_status OUT NOCOPY VARCHAR2,
1507 p_khr_id IN NUMBER,
1508 p_effective_date IN DATE) RETURN NUMBER IS
1509
1510 l_debug_enabled VARCHAR2(1);
1511 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_LOAN_PAYMENT_PAID';
1512 x_msg_count NUMBER;
1513 x_msg_data VARCHAR2(2000);
1514
1515 l_loan_payment_paid NUMBER;
1516
1517 -- Begin - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
1518 CURSOR l_laon_payment_csr(cp_khr_id IN NUMBER, cp_effective_date IN DATE) IS
1519 -- Begin Bug 6456733
1520 SELECT
1521 (NVL(SUM(AMOUNT_DUE_ORIGINAL),0)- NVL(SUM(AMOUNT_LINE_ITEMS_REMAINING),0)) loan_paid_amount
1522 FROM okl_bpd_ar_inv_lines_v lpt1
1523 where contract_id=cp_khr_id
1524 and RECEIVABLES_INVOICE_ID in
1525 (
1526 SELECT lpt.RECEIVABLES_INVOICE_ID
1527 FROM okl_bpd_ar_inv_lines_v lpt,
1528 okl_strm_type_b sty,
1529 ar_payment_schedules_all aps,
1530 okl_k_headers_full_v khr
1531 WHERE lpt.contract_id = lpt1.contract_id
1532 AND lpt.contract_number = khr.contract_number
1533 AND lpt.sty_id = sty.id
1534 AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
1535 AND sty.stream_type_purpose IN
1536 ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT', 'UNSCHEDULED_LOAN_PAYMENT')
1537 AND TRUNC(aps.trx_date) <= cp_effective_date
1538 );
1539
1540 --SELECT NVL(SUM(app.amount_applied),0) loan_paid_amount
1541 --FROM ar_receivable_applications_all app,
1542 --ar_payment_schedules_all sch,
1543 --okl_bpd_tld_ar_lines_v tld,
1544 --okl_strm_type_v sty,
1545 --okl_k_headers_full_v khr
1546 --WHERE TRUNC(app.apply_date) <= cp_effective_date
1547 --AND app.status = 'APP'
1548 --AND app.applied_payment_schedule_id = sch.payment_schedule_id
1549 --AND sch.class = 'INV'
1550 --AND sch.customer_trx_id = tld.customer_trx_id
1551 --AND tld.khr_id = cp_khr_id
1552 --AND tld.khr_id = khr.id
1553 --AND tld.sty_id = sty.id
1554 --AND sty.stream_type_purpose IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT', 'UNSCHEDULED_LOAN_PAYMENT');
1555
1556 -- End bug 6456733
1557
1558 -- End - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
1559
1560 BEGIN
1561 l_debug_enabled := okl_debug_pub.check_log_enabled;
1562 IF(NVL(l_debug_enabled,'N')='Y') THEN
1563 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_loan_payment_paid');
1564 END IF;
1565
1566 l_loan_payment_paid := 0;
1567 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1568 IF ( p_khr_id IS NULL ) THEN
1569 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1570 END IF;
1571
1572 OPEN l_laon_payment_csr(p_khr_id, p_effective_date);
1573 FETCH l_laon_payment_csr INTO l_loan_payment_paid;
1574 CLOSE l_laon_payment_csr;
1575
1576 IF(NVL(l_debug_enabled,'N')='Y') THEN
1577 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_loan_payment_paid');
1578 END IF;
1579
1580 RETURN l_loan_payment_paid;
1581 EXCEPTION
1582 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1583 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1584 RETURN l_loan_payment_paid;
1585 WHEN OTHERS THEN
1586 IF l_laon_payment_csr%ISOPEN THEN
1587 CLOSE l_laon_payment_csr;
1588 END IF;
1589 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1590 OKL_API.SET_MESSAGE(
1591 p_app_name => G_APP_NAME,
1592 p_msg_name => G_UNEXPECTED_ERROR,
1593 p_token1 => G_SQLCODE_TOKEN,
1594 p_token1_value => SQLCODE,
1595 p_token2 => G_SQLERRM_TOKEN,
1596 p_token2_value => SQLERRM);
1597 RETURN l_loan_payment_paid;
1598 END get_loan_payment_paid;
1599
1600 ------------------------------------------------------------------------------
1601 -- Start of Comments
1602 -- Created By: dkagrawa
1603 -- Function Name: get_excess_loan_payment
1604 -- Description: Excess Loan Payment Received for a loan contract with
1605 -- a revenue recognition method of Actual
1606 -- Inputs :
1607 -- Output : Loan payment Paid
1608 -- Dependencies:
1609 -- Parameters: Contract id
1610 -- Version: 1.0
1611 -- End of Comments
1612 -----------------------------------------------------------------------------
1613
1614 FUNCTION get_excess_loan_payment(
1615 x_return_status OUT NOCOPY VARCHAR2,
1616 p_khr_id IN NUMBER) RETURN NUMBER IS
1617
1618 l_debug_enabled VARCHAR2(1);
1619 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_EXCESS_LOAN_PAYMENT';
1620 x_msg_count NUMBER;
1621 x_msg_data VARCHAR2(2000);
1622
1623 l_excess_loan_payment NUMBER;
1624
1625 CURSOR l_laon_payment_csr(cp_khr_id IN NUMBER) IS
1626 SELECT NVL(SUM(sel.amount), 0) loan_excess_amount
1627 FROM okl_streams_v stm,
1628 okl_strm_type_v sty,
1629 okl_strm_elements_v sel
1630 WHERE stm.khr_id = cp_khr_id
1631 AND stm.id = sel.stm_id
1632 AND stm.sty_id = sty.id
1633 AND sty.stream_type_purpose = 'EXCESS_LOAN_PAYMENT_PAID';
1634
1635 BEGIN
1636 l_debug_enabled := okl_debug_pub.check_log_enabled;
1637 IF(NVL(l_debug_enabled,'N')='Y') THEN
1638 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_excess_loan_payment');
1639 END IF;
1640
1641 l_excess_loan_payment := 0;
1642 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1643 IF ( p_khr_id IS NULL ) THEN
1644 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1645 END IF;
1646
1647 OPEN l_laon_payment_csr(p_khr_id);
1648 FETCH l_laon_payment_csr INTO l_excess_loan_payment;
1649 CLOSE l_laon_payment_csr;
1650
1651 IF(NVL(l_debug_enabled,'N')='Y') THEN
1652 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_excess_loan_payment');
1653 END IF;
1654
1655 RETURN l_excess_loan_payment;
1656 EXCEPTION
1657 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1658 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1659 RETURN l_excess_loan_payment;
1660 WHEN OTHERS THEN
1661 IF l_laon_payment_csr%ISOPEN THEN
1662 CLOSE l_laon_payment_csr;
1663 END IF;
1664 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1665 OKL_API.SET_MESSAGE(
1666 p_app_name => G_APP_NAME,
1667 p_msg_name => G_UNEXPECTED_ERROR,
1668 p_token1 => G_SQLCODE_TOKEN,
1669 p_token1_value => SQLCODE,
1670 p_token2 => G_SQLERRM_TOKEN,
1671 p_token2_value => SQLERRM);
1672 RETURN l_excess_loan_payment;
1673 END get_excess_loan_payment;
1674
1675 ------------------------------------------------------------------------------
1676 -- Start of Comments
1677 -- Created By: dkagrawa
1678 -- Function Name: get_last_interim_int_calc_date
1679 -- Description: Returns the date last interim interest calculated
1680 -- for variable rate contract
1681 -- Inputs :
1682 -- Output : last interim interest calculated Date
1683 -- Dependencies:
1684 -- Parameters: Contract id
1685 -- Version: 1.0
1686 -- End of Comments
1687 -----------------------------------------------------------------------------
1688
1689 FUNCTION get_last_interim_int_calc_date(
1690 x_return_status OUT NOCOPY VARCHAR2,
1691 p_khr_id IN NUMBER) RETURN DATE IS
1692
1693 l_debug_enabled VARCHAR2(1);
1694 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_LAST_INTERIM_INT_CALC_DATE';
1695 x_msg_count NUMBER;
1696 x_msg_data VARCHAR2(2000);
1697 l_last_interest_date DATE;
1698
1699 CURSOR l_interest_calc_date_csr (cp_khr_id IN NUMBER) IS
1700 SELECT date_last_interim_interest_cal
1701 FROM okl_k_headers
1702 WHERE id = cp_khr_id;
1703 BEGIN
1704 l_debug_enabled := okl_debug_pub.check_log_enabled;
1705 IF(NVL(l_debug_enabled,'N')='Y') THEN
1706 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_last_interim_int_calc_date');
1707 END IF;
1708
1709 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1710 IF ( p_khr_id IS NULL ) THEN
1711 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1712 END IF;
1713
1714 OPEN l_interest_calc_date_csr(p_khr_id);
1715 FETCH l_interest_calc_date_csr INTO l_last_interest_date;
1716 CLOSE l_interest_calc_date_csr;
1717 IF(NVL(l_debug_enabled,'N')='Y') THEN
1718 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_last_interim_int_calc_date');
1719 END IF;
1720 RETURN l_last_interest_date;
1721 EXCEPTION
1722 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1723 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1724 RETURN l_last_interest_date;
1725 WHEN OTHERS THEN
1726 IF l_interest_calc_date_csr%ISOPEN THEN
1727 CLOSE l_interest_calc_date_csr;
1728 END IF;
1729 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1730 OKL_API.SET_MESSAGE(
1731 p_app_name => G_APP_NAME,
1732 p_msg_name => G_UNEXPECTED_ERROR,
1733 p_token1 => G_SQLCODE_TOKEN,
1734 p_token1_value => SQLCODE,
1735 p_token2 => G_SQLERRM_TOKEN,
1736 p_token2_value => SQLERRM);
1737 RETURN l_last_interest_date;
1738
1739 END get_last_interim_int_calc_date;
1740
1741 ------------------------------------------------------------------------------
1742 -- Start of Comments
1743 -- Created By: dkagrawa
1744 -- Function Name: get_last_sch_int_calc_date
1745 -- Description: Returns the last scheduled interest calculation date prior
1746 -- to the Termination Date
1747 -- Inputs :
1748 -- Output : last scheduled interest calculated Date
1749 -- Dependencies:
1750 -- Parameters: Contract id, Effective Date
1751 -- Version: 1.0
1752 -- End of Comments
1753 -----------------------------------------------------------------------------
1754
1755 FUNCTION get_last_sch_int_calc_date(
1756 x_return_status OUT NOCOPY VARCHAR2,
1757 p_khr_id IN NUMBER,
1758 p_effective_date IN DATE) RETURN DATE IS
1759
1760 l_debug_enabled VARCHAR2(1);
1761 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_LAST_SCH_INT_CALC_DATE';
1762 x_msg_count NUMBER;
1763 x_msg_data VARCHAR2(2000);
1764 x_no_data_found BOOLEAN;
1765 l_api_version CONSTANT NUMBER := 1.0;
1766
1767 l_last_interest_date DATE;
1768 l_pdtv_rec OKL_PRODUCTS_PUB.pdtv_rec_type;
1769 x_pdt_parameter_rec OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
1770 l_pay_freq NUMBER;
1771 l_start_date DATE;
1772 l_end_date DATE;
1773 l_date_terminated DATE;
1774 l_due_date DATE;
1775 l_next_period_start_date DATE;
1776 l_next_period_end_date DATE;
1777
1778 CURSOR l_pdt_csr(cp_khr_id IN NUMBER) IS
1779 SELECT pdt_id
1780 FROM okl_k_headers
1781 WHERE id = cp_khr_id;
1782
1783 CURSOR l_payment_freq_csr(cp_khr_id IN NUMBER) IS
1784 SELECT DECODE(sll.object1_id1,'M',1,'Q',3,'S',6,'A',12) pay_freq
1785 FROM okc_rules_b sll,
1786 okc_rules_b slh,
1787 okl_strm_type_v styp,
1788 okc_rule_groups_b rgp
1789 WHERE TO_NUMBER(sll.object2_id1) = slh.id
1790 AND sll.rule_information_category = 'LASLL'
1791 AND sll.dnz_chr_id = rgp.dnz_chr_id
1792 AND sll.rgp_id = rgp.id
1793 AND slh.rule_information_category = 'LASLH'
1794 AND slh.dnz_chr_id = rgp.dnz_chr_id
1795 AND slh.rgp_id = rgp.id
1796 AND slh.object1_id1 = styp.id
1797 AND styp.stream_type_purpose = 'RENT'
1798 AND rgp.rgd_code = 'LALEVL'
1799 AND rgp.dnz_chr_id = cp_khr_id
1800 AND ROWNUM < 2;
1801
1802 CURSOR l_date_csr(cp_khr_id IN NUMBER) IS
1803 SELECT chr.start_date,
1804 chr.end_date
1805 FROM OKC_K_HEADERS_B chr,
1806 OKL_K_HEADERS khr
1807 WHERE chr.id = khr.id
1808 AND khr.id = cp_khr_id;
1809
1810 CURSOR l_stream_csr(cp_khr_id IN NUMBER, cp_term_date IN DATE) IS
1811 SELECT max(sel.stream_element_date)
1812 FROM okl_streams_v stm,
1813 okl_strm_type_v sty,
1814 okl_strm_elements_v sel
1815 WHERE stm.khr_id = cp_khr_id
1816 AND stm.id = sel.stm_id
1817 AND stm.sty_id = sty.id
1818 AND sty.stream_type_purpose = 'RENT'
1819 AND sel.stream_element_date <= cp_term_date;
1820
1821 CURSOR l_catchup_csr (cp_khr_id IN NUMBER) IS
1822 SELECT catchup_start_date,DECODE(catchup_frequency_code,'MONTHLY',1,'QUARTERLY',3,'SEMI_ANNUAL',6,'ANNUAL',12) pay_freq
1823 FROM okl_k_rate_params
1824 WHERE khr_id = cp_khr_id
1825 AND parameter_type_code = 'ACTUAL';
1826
1827 --Bug# 14049528
1828 CURSOR l_varint_sll_csr( khrid NUMBER ) IS
1829 SELECT NVL(rul2.rule_information10,'N') arrears_yn
1830 FROM okc_rule_groups_b rgp,
1831 okc_rules_b rul1,
1832 okc_rules_b rul2,
1833 okl_strm_type_b sty
1834 WHERE rul2.dnz_chr_id = rgp.dnz_chr_id
1835 AND rul2.rule_information_category = 'LASLL'
1836 AND rul2.rgp_id = rgp.id
1837 AND rgp.cle_id IS NULL
1838 AND rgp.chr_id = khrid
1839 AND rgp.dnz_chr_id = khrid
1840 AND rgp.rgd_code = 'LALEVL'
1841 AND rul1.dnz_chr_id = rgp.dnz_chr_id
1842 AND rul1.rule_information_category = 'LASLH'
1843 AND rul1.rgp_id = rgp.id
1844 AND TO_NUMBER(rul2.object2_id1) = rul1.id
1845 AND TO_NUMBER(rul1.object1_id1) = sty.id
1846 AND sty.stream_type_purpose = 'VARIABLE_INTEREST_SCHEDULE'
1847 ORDER BY FND_DATE.canonical_to_date(rul2.rule_information2);
1848
1849 l_arrears_pay_dates_option VARCHAR2(50);
1850 l_arrears_yn VARCHAR2(50);
1851 l_interest_calc_date DATE;
1852 l_next_catchup_date DATE;
1853 l_previous_period_end_date DATE;
1854 i NUMBER;
1855
1856 l_contract_start_date DATE;
1857 --Bug# 14049528
1858
1859 BEGIN
1860 l_debug_enabled := okl_debug_pub.check_log_enabled;
1861 IF(NVL(l_debug_enabled,'N')='Y') THEN
1862 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_last_sch_int_calc_date');
1863 END IF;
1864 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1865 IF ( p_khr_id IS NULL ) THEN
1866 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1867 END IF;
1868
1869 OPEN l_pdt_csr(p_khr_id);
1870 FETCH l_pdt_csr INTO l_pdtv_rec.id;
1871 CLOSE l_pdt_csr;
1872
1873 OKL_SETUPPRODUCTS_PVT.getpdt_parameters(
1874 p_api_version => l_api_version,
1875 p_init_msg_list => OKL_API.G_FALSE,
1876 x_return_status => x_return_status,
1877 x_no_data_found => x_no_data_found,
1878 x_msg_count => x_msg_count,
1879 x_msg_data => x_msg_data,
1880 p_pdtv_rec => l_pdtv_rec,
1881 p_pdt_parameter_rec => x_pdt_parameter_rec );
1882 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1883 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1884 ELSIF ( x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1885 RAISE OKL_API.G_EXCEPTION_ERROR;
1886 END IF;
1887
1888 --Bug# 14049528
1889 OPEN l_date_csr(p_khr_id);
1890 FETCH l_date_csr INTO l_contract_start_date,l_end_date;
1891 CLOSE l_date_csr;
1892
1893 l_start_date := l_contract_start_date;
1894
1895 l_date_terminated := p_effective_date;
1896 IF (l_date_terminated IS NULL) THEN
1897 l_date_terminated := l_end_date;
1898 END IF;
1899
1900 IF x_pdt_parameter_rec.interest_calculation_basis = 'REAMORT' THEN
1901 IF (l_date_terminated > l_end_date) THEN
1902 l_date_terminated := l_end_date;
1903 END IF;
1904
1905 --Bug# 14049528
1906 LOOP
1907 l_last_interest_date := l_start_date;
1908 l_start_date := OKL_VARIABLE_INTEREST_PVT.get_next_period_start_date(p_khr_id => p_khr_id,
1909 p_start_date => l_start_date);
1910
1911 -- Below check is not needed since we need to use Invoice Date and not Due Date for comparison
1912 -- with Termination Date
1913 -- l_previous_period_end_date := l_start_date - 1;
1914
1915 IF (l_last_interest_date = l_start_date OR l_start_date > l_date_terminated) THEN
1916 EXIT;
1917 END IF;
1918
1919 END LOOP;
1920 --Bug# 14049528
1921
1922 ELSIF x_pdt_parameter_rec.interest_calculation_basis = 'FLOAT_FACTORS' THEN
1923
1924 --Bug# 14049528
1925 IF (l_date_terminated > l_end_date) THEN
1926 l_date_terminated := l_end_date;
1927 END IF;
1928
1929 l_date_terminated := l_date_terminated + 1;
1930 --Bug# 14049528
1931
1932 OPEN l_stream_csr(p_khr_id,l_date_terminated);
1933 FETCH l_stream_csr INTO l_last_interest_date;
1934 CLOSE l_stream_csr;
1935
1936 IF (l_last_interest_date IS NULL) THEN
1937 l_last_interest_date := l_start_date;
1938 END IF;
1939
1940 ELSIF x_pdt_parameter_rec.interest_calculation_basis = 'FLOAT' THEN
1941
1942 --Bug# 14049528
1943 OPEN l_varint_sll_csr(p_khr_id);
1944 FETCH l_varint_sll_csr into l_arrears_yn;
1945 CLOSE l_varint_sll_csr;
1946
1947 IF l_arrears_yn = 'Y' THEN
1948 OKL_ISG_UTILS_PVT.get_arrears_pay_dates_option(
1949 p_khr_id => p_khr_id,
1950 x_arrears_pay_dates_option => l_arrears_pay_dates_option,
1951 x_return_status => x_return_status);
1952
1953 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1954 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1955 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1956 RAISE OKL_API.G_EXCEPTION_ERROR;
1957 END IF;
1958 END IF;
1959 --Bug# 14049528
1960
1961 IF (l_date_terminated > l_end_date) THEN
1962 l_date_terminated := l_end_date;
1963 END IF;
1964
1965 LOOP
1966 OKL_STREAM_GENERATOR_PVT.get_next_billing_date(
1967 p_api_version => l_api_version,
1968 p_init_msg_list => OKL_API.G_FALSE,
1969 p_khr_id => p_khr_id,
1970 p_billing_date => l_start_date,
1971 x_next_due_date => l_due_date,
1972 x_next_period_start_date => l_next_period_start_date,
1973 x_next_period_end_date => l_next_period_end_date,
1974 x_return_status => x_return_status,
1975 x_msg_count => x_msg_count,
1976 x_msg_data => x_msg_data);
1977 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
1978 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1979 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
1980 RAISE OKL_API.G_EXCEPTION_ERROR;
1981 END IF;
1982
1983 --Bug# 14049528
1984 l_interest_calc_date := l_due_date;
1985 -- Below check is not needed since we need to use Invoice Date and not Due Date for comparison
1986 -- with Termination Date
1987 /*
1988 IF l_interest_calc_date IS NOT NULL THEN
1989 IF (l_arrears_yn = 'Y' AND l_arrears_pay_dates_option = 'FIRST_DAY_OF_NEXT_PERIOD') THEN
1990 l_interest_calc_date := l_interest_calc_date - 1;
1991 END IF;
1992 END IF;
1993 */
1994
1995 IF (l_interest_calc_date IS NULL OR l_interest_calc_date > l_date_terminated) THEN
1996 l_last_interest_date := l_start_date;
1997
1998 IF (l_arrears_yn = 'Y' AND l_arrears_pay_dates_option = 'FIRST_DAY_OF_NEXT_PERIOD') THEN
1999 l_last_interest_date := l_last_interest_date - 1;
2000 END IF;
2001
2002 IF (l_last_interest_date < l_contract_start_date) THEN
2003 l_last_interest_date := l_contract_start_date;
2004 END IF;
2005
2006 EXIT;
2007 END IF;
2008 --Bug# 14049528
2009 l_start_date := l_due_date;
2010 END LOOP;
2011 ELSIF x_pdt_parameter_rec.interest_calculation_basis = 'CATCHUP/CLEANUP' THEN
2012
2013 --Bug# 14049528
2014 IF (l_date_terminated >= l_end_date) THEN
2015 l_date_terminated := l_end_date;
2016 END IF;
2017
2018 OPEN l_catchup_csr(p_khr_id);
2019 FETCH l_catchup_csr INTO l_start_date,l_pay_freq;
2020 CLOSE l_catchup_csr;
2021
2022 i := 0;
2023 l_next_catchup_date := l_start_date;
2024
2025 IF (l_date_terminated < l_next_catchup_date) THEN
2026 l_last_interest_date := l_contract_start_date;
2027 ELSE
2028 LOOP
2029 l_last_interest_date := l_next_catchup_date;
2030 i := i + 1;
2031 OKL_STREAM_GENERATOR_PVT.get_sel_date(p_start_date => l_start_date,
2032 p_advance_or_arrears => 'ARREARS',
2033 p_periods_after => i,
2034 p_months_per_period => l_pay_freq,
2035 x_date => l_next_catchup_date,
2036 x_return_status => x_return_status,
2037 p_recurrence_date => l_start_date);
2038
2039 EXIT WHEN l_next_catchup_date > l_date_terminated;
2040 END LOOP;
2041
2042 IF (l_last_interest_date = l_start_date) THEN
2043 l_last_interest_date := l_contract_start_date;
2044 END IF;
2045
2046 END IF;
2047 --Bug# 14049528
2048
2049 END IF;
2050
2051 IF(NVL(l_debug_enabled,'N')='Y') THEN
2052 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_last_sch_int_calc_date');
2053 END IF;
2054
2055 RETURN l_last_interest_date;
2056
2057 EXCEPTION
2058 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2059 x_return_status := OKL_API.G_RET_STS_ERROR;
2060 RETURN l_last_interest_date;
2061 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2062 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2063 RETURN l_last_interest_date;
2064 WHEN OTHERS THEN
2065 IF l_pdt_csr%ISOPEN THEN
2066 CLOSE l_pdt_csr;
2067 END IF;
2068 IF l_payment_freq_csr%ISOPEN THEN
2069 CLOSE l_payment_freq_csr;
2070 END IF;
2071 IF l_date_csr%ISOPEN THEN
2072 CLOSE l_date_csr;
2073 END IF;
2074 IF l_stream_csr%ISOPEN THEN
2075 CLOSE l_stream_csr;
2076 END IF;
2077 IF l_catchup_csr%ISOPEN THEN
2078 CLOSE l_catchup_csr;
2079 END IF;
2080 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2081 OKL_API.SET_MESSAGE(
2082 p_app_name => G_APP_NAME,
2083 p_msg_name => G_UNEXPECTED_ERROR,
2084 p_token1 => G_SQLCODE_TOKEN,
2085 p_token1_value => SQLCODE,
2086 p_token2 => G_SQLERRM_TOKEN,
2087 p_token2_value => SQLERRM);
2088 RETURN l_last_interest_date;
2089 END get_last_sch_int_calc_date;
2090
2091 ------------------------------------------------------------------------------
2092 -- Start of Comments
2093 -- Created By: dkagrawa
2094 -- Procedure Name: get_open_invoices
2095 -- Description: Derive a list, consisting of number, Invoice date, Remaining amount
2096 -- for open invoices for a Loan contract
2097 -- Inputs :
2098 -- Output : Invoice information table
2099 -- Dependencies:
2100 -- Parameters: Contract id
2101 -- Version: 1.0
2102 -- End of Comments
2103 -----------------------------------------------------------------------------
2104 PROCEDURE get_open_invoices(
2105 x_return_status OUT NOCOPY VARCHAR2,
2106 p_khr_id IN NUMBER,
2107 x_invoice_tbl OUT NOCOPY invoice_info_tbl_type) IS
2108
2109 l_debug_enabled VARCHAR2(1);
2110 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_OPEN_INVOICES';
2111 x_msg_count NUMBER;
2112 x_msg_data VARCHAR2(2000);
2113
2114 --Begin - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
2115 CURSOR l_invoice_info_csr (cp_khr_id IN NUMBER) IS
2116 -- Begin bug 6456733
2117 SELECT
2118 lpt.amount_due_remaining,
2119 lpt.due_date INVOICE_DATE,
2120 lpt.TXD_ID LSM_ID,
2121 lpt.TXD_ID tld_id,
2122 aps.customer_trx_id receivables_invoice_id
2123 FROM
2124 okl_bpd_ar_inv_lines_v lpt,
2125 okl_strm_type_b sty,
2126 ar_payment_schedules_all aps,
2127 okl_k_headers_full_v khr
2128 WHERE
2129 lpt.CONTRACT_ID = cp_khr_id
2130 AND lpt.contract_number = khr.contract_number
2131 AND lpt.sty_id = sty.id
2132 AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
2133 AND aps.status = 'OP'
2134 AND aps.class = 'INV'
2135 AND sty.stream_type_purpose
2136 IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT', 'UNSCHEDULED_LOAN_PAYMENT');
2137
2138
2139 --SELECT sch.amount_due_remaining,
2140 --TLD.INVOICE_DATE INVOICE_DATE,
2141 --TLD.TLD_ID LSM_ID,
2142 --tld.TLD_id tld_id,
2143 -- lsm.receivables_invoice_id
2144 --sch.customer_trx_id receivables_invoice_id
2145 --FROM okl_bpd_tld_ar_lines_v tld,
2146 --ar_payment_schedules_all sch,
2147 --okl_strm_type_b sty
2148 --WHERE sch.customer_trx_id = tld.customer_trx_id
2149 --AND sch.status = 'OP'
2150 --AND sch.class = 'INV'
2151 --AND tld.khr_id = cp_khr_id
2152 --AND tld.sty_id = sty.id
2153 --AND sty.stream_type_purpose IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT', 'UNSCHEDULED_LOAN_PAYMENT');
2154
2155 -- End bug 6456733
2156
2157 -- End - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
2158
2159 BEGIN
2160 l_debug_enabled := okl_debug_pub.check_log_enabled;
2161 IF(NVL(l_debug_enabled,'N')='Y') THEN
2162 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_open_invoices');
2163 END IF;
2164 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2165 IF ( p_khr_id IS NULL ) THEN
2166 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2167 END IF;
2168
2169 OPEN l_invoice_info_csr(p_khr_id);
2170 FETCH l_invoice_info_csr BULK COLLECT INTO x_invoice_tbl;
2171 CLOSE l_invoice_info_csr;
2172 IF(NVL(l_debug_enabled,'N')='Y') THEN
2173 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_open_invoices');
2174 END IF;
2175
2176 EXCEPTION
2177 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2178 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2179 WHEN OTHERS THEN
2180 IF l_invoice_info_csr%ISOPEN THEN
2181 CLOSE l_invoice_info_csr;
2182 END IF;
2183 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2184 OKL_API.SET_MESSAGE(
2185 p_app_name => G_APP_NAME,
2186 p_msg_name => G_UNEXPECTED_ERROR,
2187 p_token1 => G_SQLCODE_TOKEN,
2188 p_token1_value => SQLCODE,
2189 p_token2 => G_SQLERRM_TOKEN,
2190 p_token2_value => SQLERRM);
2191 END get_open_invoices;
2192
2193
2194
2195 --Added by bkatraga for bug 11924386
2196 PROCEDURE check_principal_stream(
2197 p_khr_id IN NUMBER,
2198 x_return_status OUT NOCOPY VARCHAR2,
2199 x_prin_stream_flag OUT NOCOPY NUMBER) IS
2200
2201 l_prin_payment_exists VARCHAR2(1) := 'N';
2202 l_prin_balance_exists VARCHAR2(1) := 'N';
2203 l_fee_exists VARCHAR2(1) := 'N';
2204
2205 CURSOR l_asset_lines_csr(chrid OKL_K_HEADERS.KHR_ID%TYPE) IS
2206 SELECT kle.id
2207 FROM OKL_K_LINES_FULL_V kle,
2208 OKC_LINE_STYLES_B ls,
2209 OKC_STATUSES_B sts
2210 WHERE kle.lse_id = ls.id
2211 AND ls.lty_code = 'FREE_FORM1'
2212 AND kle.dnz_chr_id = chrid
2213 AND sts.code = kle.sts_code
2214 AND sts.ste_code not in ( 'HOLD', 'EXPIRED', 'CANCELLED');
2215
2216 CURSOR l_check_fee_csr(chrid OKL_K_HEADERS.KHR_ID%TYPE) IS
2217 SELECT 'Y'
2218 FROM OKL_K_LINES_FULL_V kle,
2219 OKC_LINE_STYLES_B ls,
2220 OKC_STATUSES_B sts
2221 WHERE kle.lse_id = ls.id
2222 AND ls.lty_code = 'FEE'
2223 AND kle.fee_type IN('FINANCED','ROLLOVER')
2224 AND kle.dnz_chr_id = chrid
2225 AND sts.code = kle.sts_code
2226 AND sts.ste_code not in ( 'HOLD', 'EXPIRED', 'CANCELLED');
2227
2228 CURSOR check_prin_payment_csr (p_contract_id NUMBER,
2229 p_line_id NUMBER) IS
2230 SELECT 'Y'
2231 FROM okl_strm_elements sel
2232 ,okl_streams str
2233 ,okl_strm_type_v sty
2234 WHERE sel.stm_id = str.id
2235 AND str.khr_id = p_contract_id
2236 AND str.kle_id = p_line_id
2237 AND str.say_code = 'CURR'
2238 AND str.active_yn = 'Y'
2239 AND str.sty_id = sty.id
2240 AND sty.stream_type_purpose = 'PRINCIPAL_PAYMENT';
2241
2242 CURSOR check_prin_balance_csr (p_contract_id NUMBER,
2243 p_line_id NUMBER) IS
2244 SELECT 'Y'
2245 FROM okl_strm_elements sel
2246 ,okl_streams str
2247 ,okl_strm_type_v sty
2248 WHERE sel.stm_id = str.id
2249 AND str.khr_id = p_contract_id
2250 AND str.kle_id = p_line_id
2251 AND str.say_code = 'CURR'
2252 AND str.active_yn = 'Y'
2253 AND str.sty_id = sty.id
2254 AND sty.stream_type_purpose = 'PRINCIPAL_BALANCE';
2255
2256 BEGIN
2257 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2258 x_prin_stream_flag := 0;
2259
2260 OPEN l_check_fee_csr(p_khr_id);
2261 FETCH l_check_fee_csr INTO l_fee_exists;
2262 CLOSE l_check_fee_csr;
2263
2264 IF(l_fee_exists = 'Y') THEN
2265 x_prin_stream_flag := 1;
2266 ELSE
2267 FOR l_lne IN l_asset_lines_csr(p_khr_id)
2268 LOOP
2269 l_prin_payment_exists := 'N';
2270 l_prin_balance_exists := 'N';
2271
2272 OPEN check_prin_payment_csr(p_khr_id, l_lne.id);
2273 FETCH check_prin_payment_csr INTO l_prin_payment_exists;
2274 CLOSE check_prin_payment_csr;
2275
2276 OPEN check_prin_balance_csr(p_khr_id, l_lne.id);
2277 FETCH check_prin_balance_csr INTO l_prin_balance_exists;
2278 CLOSE check_prin_balance_csr;
2279
2280 IF(l_prin_payment_exists = 'Y' AND l_prin_balance_exists = 'Y') THEN
2281 x_prin_stream_flag := 1;
2282 EXIT;
2283 END IF;
2284 END LOOP;
2285 END IF;
2286
2287 EXCEPTION
2288 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2289 x_return_status := OKL_API.G_RET_STS_ERROR;
2290 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2291 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2292 WHEN OTHERS THEN
2293 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2294 OKL_API.SET_MESSAGE(
2295 p_app_name => G_APP_NAME,
2296 p_msg_name => G_UNEXPECTED_ERROR,
2297 p_token1 => G_SQLCODE_TOKEN,
2298 p_token1_value => SQLCODE,
2299 p_token2 => G_SQLERRM_TOKEN,
2300 p_token2_value => SQLERRM);
2301 END check_principal_stream;
2302 --end bkatraga for bug 11924386
2303
2304 --Bug# 13146882
2305 PROCEDURE check_accounting_setups(
2306 p_chr_id IN NUMBER,
2307 p_transaction_type IN VARCHAR2,
2308 p_transaction_date IN DATE,
2309 x_return_status OUT NOCOPY VARCHAR2) IS
2310
2311
2312 CURSOR Transaction_Type_csr (p_transaction_type IN okl_trx_types_v.name%TYPE ) IS
2313 SELECT id trx_try_id
2314 FROM okl_trx_types_tl
2315 WHERE name = p_transaction_type
2316 AND language = 'US';
2317
2318 CURSOR chr_csr(p_chr_id NUMBER) IS
2319 SELECT khr.pdt_id,
2320 pdt.name pdt_name,
2321 chr.scs_code
2322 FROM okl_k_headers khr,
2323 okl_products pdt,
2324 okc_k_headers_b chr
2325 WHERE khr.id = p_chr_id
2326 AND chr.id = khr.id
2327 AND khr.pdt_id = pdt.id;
2328
2329 CURSOR trx_type_name_csr (p_trx_type_id OKL_TRX_TYPES_TL.ID%TYPE) IS
2330 SELECT name
2331 FROM okl_trx_types_tl try
2332 WHERE id = p_trx_type_id
2333 AND try.language = USERENV('LANG');
2334
2335 --Cursor to get the Account Derivation Option
2336 CURSOR get_acct_derivation_csr
2337 IS
2338 SELECT ACCOUNT_DERIVATION
2339 FROM okl_sys_acct_opts;
2340
2341 CURSOR atl_csr(p_avl_id NUMBER) IS
2342 SELECT ID
2343 ,CODE_COMBINATION_ID
2344 ,AE_LINE_TYPE
2345 ,CRD_CODE
2346 ,ACCOUNT_BUILDER_YN
2347 ,PERCENTAGE
2348 FROM OKL_AE_TMPT_LNES
2349 WHERE avl_id = p_avl_id;
2350
2351 l_atl_rec atl_csr%ROWTYPE;
2352 l_account_derivation OKL_SYS_ACCT_OPTS.ACCOUNT_DERIVATION%TYPE;
2353 l_rowcount NUMBER := 0;
2354
2355 l_chr_rec chr_csr%ROWTYPE;
2356 l_Trx_Type_rec Transaction_Type_csr%ROWTYPE;
2357 l_trx_type_name_rec trx_type_name_csr%ROWTYPE;
2358 l_tmpl_identify_rec OKL_ACCOUNT_DIST_PVT.TMPL_IDENTIFY_REC_TYPE;
2359 l_template_tbl OKL_ACCOUNT_DIST_PVT.AVLV_TBL_TYPE;
2360 l_tmpl_rec OKL_ACCOUNT_DIST_PVT.AVLV_REC_TYPE;
2361
2362 l_fact_synd_code FND_LOOKUPS.Lookup_code%TYPE;
2363 l_inv_acct_code OKC_RULES_B.Rule_Information1%TYPE;
2364
2365 l_api_version CONSTANT NUMBER := 1.0;
2366 x_msg_count NUMBER;
2367 x_msg_data VARCHAR2(2000);
2368
2369
2370 BEGIN
2371 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2372
2373 -- Check Transaction_Type
2374 OPEN Transaction_Type_csr(p_transaction_type);
2375 FETCH Transaction_Type_csr INTO l_Trx_Type_rec;
2376 IF Transaction_Type_csr%NOTFOUND THEN
2377 Okl_Api.SET_MESSAGE(G_APP_NAME, OKL_API.G_INVALID_VALUE, OKL_API.G_COL_NAME_TOKEN, p_transaction_type);
2378 CLOSE Transaction_Type_csr;
2379 RAISE Okl_Api.G_EXCEPTION_ERROR;
2380 END IF;
2381 CLOSE Transaction_Type_csr;
2382
2383 OPEN chr_csr(p_chr_id);
2384 FETCH chr_csr INTO l_chr_rec;
2385 CLOSE chr_csr;
2386
2387 l_tmpl_identify_rec.transaction_type_id := l_Trx_Type_rec.trx_try_id;
2388 l_tmpl_identify_rec.product_id := l_chr_rec.pdt_id;
2389 l_tmpl_identify_rec.memo_yn := OKL_API.G_MISS_CHAR;
2390
2391 OKL_SECURITIZATION_PVT.Check_Khr_ia_associated(
2392 p_api_version => l_api_version,
2393 p_init_msg_list => OKL_API.G_FALSE,
2394 x_return_status => x_return_status,
2395 x_msg_count => x_msg_count,
2396 x_msg_data => x_msg_data,
2397 p_khr_id => p_chr_id,
2398 p_scs_code => l_chr_rec.scs_code,
2399 p_trx_date => p_transaction_date,
2400 x_fact_synd_code => l_fact_synd_code,
2401 x_inv_acct_code => l_inv_acct_code
2402 );
2403
2404 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2405 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2406 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2407 RAISE OKL_API.G_EXCEPTION_ERROR;
2408 END IF;
2409
2410 l_tmpl_identify_rec.factoring_synd_flag := l_fact_synd_code;
2411 l_tmpl_identify_rec.investor_code := l_inv_acct_code;
2412
2413 OKL_ACCOUNT_DIST_PVT.GET_TEMPLATE_INFO(p_api_version => l_api_version,
2414 p_init_msg_list => OKL_API.G_FALSE,
2415 x_return_status => x_return_status,
2416 x_msg_count => x_msg_count,
2417 x_msg_data => x_msg_data,
2418 p_tmpl_identify_rec => l_tmpl_identify_rec,
2419 x_template_tbl => l_template_tbl,
2420 p_validity_date => p_transaction_date);
2421
2422 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2423 RAISE OKL_API.G_EXCEPTION_ERROR;
2424 END IF;
2425
2426 -- Raise an error if template is not found.
2427 IF (l_template_tbl.COUNT = 0) THEN
2428
2429 OPEN trx_type_name_csr (l_Trx_Type_rec.trx_try_id);
2430 FETCH trx_type_name_csr INTO l_trx_type_name_rec;
2431 CLOSE trx_type_name_csr;
2432
2433 OKL_API.SET_MESSAGE(p_app_name => g_app_name
2434 ,p_msg_name => 'OKL_TMPL_NOT_FOUND'
2435 ,p_token1 => 'PRODUCT'
2436 ,p_token1_value => l_chr_rec.pdt_name
2437 ,p_token2 => 'TRANSACTION_TYPE'
2438 ,p_token2_value => l_trx_type_name_rec.name
2439 ,p_token3 => 'STREAM_TYPE'
2440 ,p_token3_value => ' '
2441 ,p_token4 => 'ACCOUNTING_DATE'
2442 ,p_token4_value => p_transaction_date);
2443
2444 RAISE OKL_API.G_EXCEPTION_ERROR;
2445
2446 ELSE
2447
2448 OPEN get_acct_derivation_csr;
2449 FETCH get_acct_derivation_csr INTO l_account_derivation;
2450 CLOSE get_acct_derivation_csr;
2451
2452 FOR j IN 1..l_template_tbl.COUNT
2453 LOOP
2454 l_tmpl_rec := l_template_tbl(j);
2455
2456 IF (l_tmpl_rec.FMA_ID IS NULL) OR (l_tmpl_rec.FMA_ID = OKL_API.G_MISS_NUM) THEN
2457 -- If the formula is not found associated with template
2458 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2459 p_msg_name => 'OKL_FMA_NOT_PRESENT',
2460 p_token1 => 'TEMPLATE_NAME',
2461 p_token1_value => l_tmpl_rec.NAME);
2462 RAISE OKL_API.G_EXCEPTION_ERROR;
2463 END IF;
2464
2465 IF (l_account_derivation = 'ATS') THEN
2466
2467 OPEN atl_csr(l_tmpl_rec.ID);
2468 LOOP
2469 FETCH atl_csr INTO l_atl_rec;
2470 l_rowcount := atl_csr%ROWCOUNT;
2471 EXIT WHEN atl_csr%NOTFOUND;
2472 END LOOP;
2473 CLOSE atl_csr;
2474
2475 IF (l_rowcount < 2) THEN
2476 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2477 p_msg_name => 'OKL_LT2_LINE_IN_TMPL',
2478 p_token1 => 'TEMPLATE',
2479 p_token1_value => l_tmpl_rec.NAME,
2480 p_token2 => 'COUNT',
2481 p_token2_value => l_rowcount);
2482 x_return_status := OKL_API.G_RET_STS_ERROR;
2483 RAISE OKL_API.G_EXCEPTION_ERROR;
2484 END IF;
2485 END IF;
2486 END LOOP;
2487
2488 END IF;
2489
2490 EXCEPTION
2491 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2492 x_return_status := OKL_API.G_RET_STS_ERROR;
2493 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2494 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2495 WHEN OTHERS THEN
2496 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2497 OKL_API.SET_MESSAGE(
2498 p_app_name => G_APP_NAME,
2499 p_msg_name => G_UNEXPECTED_ERROR,
2500 p_token1 => G_SQLCODE_TOKEN,
2501 p_token1_value => SQLCODE,
2502 p_token2 => G_SQLERRM_TOKEN,
2503 p_token2_value => SQLERRM);
2504 END check_accounting_setups;
2505 --Bug# 13146882
2506
2507 --Bug# 13447258
2508 PROCEDURE check_vr_asset_level_upgrade(
2509 p_khr_id IN NUMBER,
2510 x_return_status OUT NOCOPY VARCHAR2) IS
2511
2512 l_chr_streams_exist VARCHAR2(1) := 'N';
2513 l_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
2514
2515 CURSOR check_chr_streams_csr (p_khr_id IN NUMBER) IS
2516 SELECT 'Y',
2517 chr.contract_number
2518 FROM okl_strm_elements sel
2519 ,okl_streams str
2520 ,okl_strm_type_b sty
2521 ,okc_k_headers_b chr
2522 WHERE chr.id = p_khr_id
2523 AND sel.stm_id = str.id
2524 AND str.khr_id = chr.id
2525 AND str.kle_id IS NULL
2526 AND str.say_code = 'CURR'
2527 AND str.active_yn = 'Y'
2528 AND str.sty_id = sty.id
2529 AND sty.stream_type_purpose in ('VARIABLE_INTEREST', 'VARIABLE_LOAN_PAYMENT', 'INTEREST_CATCHUP');
2530
2531 BEGIN
2532 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2533
2534 OPEN check_chr_streams_csr(p_khr_id);
2535 FETCH check_chr_streams_csr INTO l_chr_streams_exist, l_contract_number;
2536 CLOSE check_chr_streams_csr;
2537
2538 IF (l_chr_streams_exist = 'Y') THEN
2539 -- If contract level variable interest streams exist
2540 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2541 p_msg_name => 'OKL_VR_ASSET_NOT_UPGRADE',
2542 p_token1 => 'CONTRACT_NUMBER',
2543 p_token1_value => l_contract_number);
2544 RAISE OKL_API.G_EXCEPTION_ERROR;
2545 END IF;
2546
2547 EXCEPTION
2548 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2549 x_return_status := OKL_API.G_RET_STS_ERROR;
2550 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2551 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2552 WHEN OTHERS THEN
2553 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2554 OKL_API.SET_MESSAGE(
2555 p_app_name => G_APP_NAME,
2556 p_msg_name => G_UNEXPECTED_ERROR,
2557 p_token1 => G_SQLCODE_TOKEN,
2558 p_token1_value => SQLCODE,
2559 p_token2 => G_SQLERRM_TOKEN,
2560 p_token2_value => SQLERRM);
2561 END check_vr_asset_level_upgrade;
2562 --Bug# 13447258
2563
2564 ------------------------------------------------------------------------------
2565
2566 END OKL_VARIABLE_INT_UTIL_PVT;