[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.29 2008/05/02 20:07:55 sechawla noship $ */
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_from_date IN DATE,
20 p_to_date IN DATE DEFAULT SYSDATE) RETURN NUMBER IS
21
22 l_debug_enabled VARCHAR2(1);
23 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_INTEREST_BILLED';
24 x_msg_count NUMBER;
25 x_msg_data VARCHAR2(2000);
26
27 l_interest_billed NUMBER;
28
29 -- Begin - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
30 CURSOR l_interest_billed_csr(cp_khr_id IN NUMBER, cp_from_date IN DATE, cp_to_date IN DATE) IS
31 -- Begin bug 6456733
32 -- dcshanmu bug 6734738 start
33 --SELECT NVL(SUM(AMOUNT),0) interest_billed_amount
34 --FROM okl_bpd_ar_inv_lines_v lpt1
35 --where contract_id=cp_khr_id
36 --and RECEIVABLES_INVOICE_ID in
37 --(
38 SELECT NVL(SUM(AMOUNT),0) interest_billed_amount -- lpt.RECEIVABLES_INVOICE_ID
39 --dcshanmu bug 6734738 end
40 FROM okl_bpd_ar_inv_lines_v lpt,
41 okl_strm_type_b sty,
42 ar_payment_schedules_all aps,
43 okl_k_headers_full_v khr
44 --dcshanmu bug 6734738 start
45 WHERE lpt.contract_id = cp_khr_id
46 --dcshanmu bug 6734738 end
47 AND lpt.contract_number = khr.contract_number
48 AND lpt.sty_id = sty.id
49 AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
50 AND sty.stream_type_purpose
51 IN ('INTEREST_PAYMENT', 'VARIABLE_INTEREST','INTEREST_CATCHUP')
52 AND TRUNC(aps.trx_date) BETWEEN TRUNC(NVL(cp_from_date, khr.start_date))
53 AND TRUNC(NVL(cp_to_date,SYSDATE))
54 --dcshanmu bug 6734738 start
55 --)
56 ;
57 --dcshanmu bug 6734738 end
58 -- End bug 6456733
59
60
61 -- SELECT SUM(interest_billed_amount) interest_billed_amount FROM
62 -- (
63 -- SELECT NVL(SUM(aps.amount_due_original), 0) interest_billed_amount
64 -- SELECT NVL(SUM(aps.amount_line_items_original),0) interest_billed_amount --End bug# 5767426
65 -- FROM okl_bpd_tld_ar_lines_v tld,
66 -- okl_strm_type_b sty,
67 -- ar_payment_schedules_all aps,
68 -- okl_k_headers_full_v khr
69 -- WHERE tld.khr_id = cp_khr_id
70 -- AND tld.khr_id = khr.id
71 -- AND tld.sty_id = sty.id
72 -- AND sty.stream_type_purpose IN ('INTEREST_PAYMENT', 'VARIABLE_INTEREST','INTEREST_CATCHUP')
73 -- AND tld.customer_trx_id = aps.customer_trx_id
74 -- AND TRUNC(aps.trx_date) BETWEEN TRUNC(NVL(cp_from_date, khr.start_date))
75 -- AND TRUNC(NVL(cp_to_date, SYSDATE)) );
76 -- End - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
77
78
79 -- 4933500
80 /*
81 UNION
82 SELECT sum(aps.amount_due_original) interest_billed_amount
83 FROM okl_cnsld_ar_strms_b lsm,
84 okl_strm_type_v sty,
85 okl_strm_elements sel,
86 ar_payment_schedules_all aps,
87 okl_k_headers_full_v khr
88 WHERE lsm.khr_id = cp_khr_id
89 AND lsm.khr_id = khr.id
90 AND lsm.sty_id = sty.id
91 AND sty.stream_type_purpose = 'VARIABLE_LOAN_PAYMENT'
92 AND lsm.sel_id = sel.id
93 AND sel.sel_id IS NULL
94 AND lsm.receivables_invoice_id = aps.customer_trx_id
95 AND TRUNC(aps.trx_date) BETWEEN TRUNC(NVL(cp_from_date, khr.start_date)) AND TRUNC(NVL(cp_to_date, SYSDATE)));
96 */
97 BEGIN
98 l_debug_enabled := okl_debug_pub.check_log_enabled;
99 IF(NVL(l_debug_enabled,'N')='Y') THEN
100 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_interest_billed');
101 END IF;
102
103 l_interest_billed := 0;
104 x_return_status := OKL_API.G_RET_STS_SUCCESS;
105 IF ( p_khr_id IS NULL ) THEN
106 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
107 END IF;
108
109 OPEN l_interest_billed_csr(p_khr_id, p_from_date, p_to_date);
110 FETCH l_interest_billed_csr INTO l_interest_billed;
111 CLOSE l_interest_billed_csr;
112
113 IF(NVL(l_debug_enabled,'N')='Y') THEN
114 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_interest_billed');
115 END IF;
116
117 RETURN l_interest_billed;
118 EXCEPTION
119 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
120 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
121 RETURN l_interest_billed;
122 WHEN OTHERS THEN
123 IF l_interest_billed_csr%ISOPEN THEN
124 CLOSE l_interest_billed_csr;
125 END IF;
126 OKL_API.SET_MESSAGE(
127 p_app_name => G_APP_NAME,
128 p_msg_name => G_UNEXPECTED_ERROR,
129 p_token1 => G_SQLCODE_TOKEN,
130 p_token1_value => SQLCODE,
131 p_token2 => G_SQLERRM_TOKEN,
132 p_token2_value => SQLERRM);
133 RETURN l_interest_billed;
134 END get_interest_billed;
135
136 ------------------------------------------------------------------------------
137 -- Start of Comments
138 -- Created By: dkagrawa
139 -- Function Name: get_interest_paid
140 -- Description: This Function is called to get interest paid for a date range
141 -- Inputs :
142 -- Output : Interest Paid
143 -- Dependencies:
144 -- Parameters: Contract id, Start Date, End Date
145 -- Version: 1.0
146 -- End of Comments
147 ------------------------------------------------------------------------------
148
149 FUNCTION get_interest_paid(
150 x_return_status OUT NOCOPY VARCHAR2,
151 p_khr_id IN NUMBER,
152 p_from_date IN DATE,
153 p_to_date IN DATE DEFAULT SYSDATE) RETURN NUMBER IS
154
155 l_debug_enabled VARCHAR2(1);
156 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_INTEREST_PAID';
157 x_msg_count NUMBER;
158 x_msg_data VARCHAR2(2000);
159
160 l_interest_paid NUMBER;
161
162 --Begin - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
163
164 CURSOR l_interest_paid_csr(cp_khr_id IN NUMBER, cp_from_date IN DATE, cp_to_date IN DATE) IS
165 -- Begin bug 6456733
166 SELECT NVL(SUM(interest_paid_amount),0) interest_paid_amount
167 FROM
168 (
169 SELECT NVL(SUM(AMOUNT),0)- NVL(SUM(AMOUNT_LINE_ITEMS_REMAINING),0) interest_paid_amount
170 FROM okl_bpd_ar_inv_lines_v lpt1
171 where contract_id=cp_khr_id
172 and RECEIVABLES_INVOICE_ID in
173 (
174 SELECT RECEIVABLES_INVOICE_ID
175 FROM
176 okl_bpd_ar_inv_lines_v lpt,
177 okl_strm_type_b sty,
178 ar_payment_schedules_all aps,
179 okl_k_headers_full_v khr
180 WHERE
181 lpt.contract_id = lpt1.contract_id
182 AND lpt.contract_number = khr.contract_number
183 AND lpt.sty_id = sty.id
184 AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
185 AND sty.stream_type_purpose IN
186 ('INTEREST_PAYMENT', 'VARIABLE_INTEREST','INTEREST_CATCHUP')
187 AND TRUNC(aps.trx_date)
188 BETWEEN TRUNC(NVL(cp_from_date, khr.start_date)) AND TRUNC(NVL(cp_to_date, SYSDATE))
189 )
190 group by RECEIVABLES_INVOICE_ID
191 having NVL(SUM(AMOUNT),0) < NVL(SUM(AMOUNT_LINE_ITEMS_REMAINING),0)
192 UNION ALL
193 SELECT NVL(SUM(sel.amount),0) interest_paid_amount
194 FROM okl_strm_type_v sty,
195 okl_streams_v stm,
196 okl_strm_elements sel,
197 okc_k_headers_b khr
198 WHERE stm.khr_id = cp_khr_id
199 AND stm.kle_id = NVL(null, stm.kle_id)
200 AND stm.khr_id = khr.id
201 AND stm.sty_id = sty.id
202 AND sty.stream_type_purpose = 'DAILY_INTEREST_INTEREST'
203 AND stm.id = sel.stm_id
204 AND TRUNC(sel.stream_element_date)
205 BETWEEN TRUNC(NVL(cp_from_date, khr.start_date)) AND TRUNC(NVL(cp_to_date, SYSDATE))
206 );
207
208 --SELECT SUM(interest_paid_amount) FROM
209 --(SELECT NVL(SUM(app.line_applied),0) interest_paid_amount
210 --FROM ar_receivable_applications_all app,
211 --ar_cash_receipts_all cra,
212 --ar_payment_schedules_all sch,
213 --okl_bpd_tld_ar_lines_v tld,
214 --okl_strm_type_v sty,
215 --okl_k_headers_full_v khr
216 --WHERE TRUNC(cra.receipt_date) BETWEEN TRUNC(NVL(cp_from_date, cra.receipt_date)) AND TRUNC(NVL(cp_to_date, SYSDATE))
217 --AND app.status = 'APP'
218 --AND app.applied_payment_schedule_id = sch.payment_schedule_id
219 --AND app.cash_receipt_id = cra.cash_receipt_id
220 --AND sch.class = 'INV'
221 --AND sch.customer_trx_id = tld.customer_trx_id
222 --AND tld.khr_id = cp_khr_id
223 --AND tld.khr_id = khr.id
224 --AND tld.sty_id = sty.id
225 --AND sty.stream_type_purpose IN ('INTEREST_PAYMENT', 'VARIABLE_INTEREST','INTEREST_CATCHUP')
226 --UNION
227 ----fix for bug # 4746404
228 --SELECT NVL(SUM(sel.amount),0) interest_paid_amount
229 --FROM okl_strm_type_v sty,
230 --okl_streams_v stm,
231 --okl_strm_elements sel,
232 --okc_k_headers_b khr
233 --WHERE stm.khr_id = cp_khr_id
234 --AND stm.khr_id = khr.id
235 --AND stm.sty_id = sty.id
236 --AND sty.stream_type_purpose = 'DAILY_INTEREST_INTEREST'
237 --AND stm.id = sel.stm_id
238 --AND TRUNC(sel.stream_element_date) BETWEEN TRUNC(NVL(cp_from_date, khr.start_date)) AND TRUNC(NVL(cp_to_date, SYSDATE)));
239 -- End - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
240
241 -- End bug 6456733
242
243 BEGIN
244 l_debug_enabled := okl_debug_pub.check_log_enabled;
245 IF(NVL(l_debug_enabled,'N')='Y') THEN
246 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_interest_paid');
247 END IF;
248
249 l_interest_paid := 0;
250 x_return_status := OKL_API.G_RET_STS_SUCCESS;
251 IF ( p_khr_id IS NULL ) THEN
252 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
253 END IF;
254
255 OPEN l_interest_paid_csr(p_khr_id, p_from_date, p_to_date);
256 FETCH l_interest_paid_csr INTO l_interest_paid;
257 CLOSE l_interest_paid_csr;
258
259 IF(NVL(l_debug_enabled,'N')='Y') THEN
260 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_interest_paid');
261 END IF;
262
263 RETURN l_interest_paid;
264 EXCEPTION
265 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
266 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
267 RETURN l_interest_paid;
268 WHEN OTHERS THEN
269 IF l_interest_paid_csr%ISOPEN THEN
270 CLOSE l_interest_paid_csr;
271 END IF;
272 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
273 OKL_API.SET_MESSAGE(
274 p_app_name => G_APP_NAME,
275 p_msg_name => G_UNEXPECTED_ERROR,
276 p_token1 => G_SQLCODE_TOKEN,
277 p_token1_value => SQLCODE,
278 p_token2 => G_SQLERRM_TOKEN,
279 p_token2_value => SQLERRM);
280 RETURN l_interest_paid;
281 END get_interest_paid;
282
283 ------------------------------------------------------------------------------
284 -- Start of Comments
285 -- Created By: dkagrawa
286 -- Function Name: get_interest_due
287 -- Description: This Function is called to get interest due for a date range
288 -- Inputs :
289 -- Output : Interest Due
290 -- Dependencies:
291 -- Parameters: Contract id, Effective Date
292 -- Version: 1.0
293 -- End of Comments
294 ------------------------------------------------------------------------------
295
296 FUNCTION get_interest_due(
297 x_return_status OUT NOCOPY VARCHAR2,
298 p_khr_id IN NUMBER,
299 p_to_date IN DATE DEFAULT SYSDATE) RETURN NUMBER IS
300
301 l_api_version CONSTANT NUMBER := 1.0;
302 x_msg_count NUMBER;
303 x_msg_data VARCHAR2(2000);
304 l_debug_enabled VARCHAR2(1);
305 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_INTEREST_DUE';
306
307 l_principal_basis okl_k_rate_params.principal_basis_code%TYPE;
308 l_start_date DATE;
309 l_end_date DATE;
310 l_to_date DATE;
311 l_due_date DATE;
312 l_next_period_start_date DATE;
313 l_next_period_end_date DATE;
314 l_interest_amt NUMBER;
315 l_interest_due NUMBER;
316 l_int_calc_basis OKL_PRODUCT_PARAMETERS_V.interest_calculation_basis%TYPE;
317 l_rev_rec_mthd OKL_PRODUCT_PARAMETERS_V.revenue_recognition_method%TYPE;
318
319 CURSOR l_principal_basis_csr(cp_khr_id IN NUMBER) IS
320 SELECT principal_basis_code
321 FROM okl_k_rate_params
322 WHERE khr_id = cp_khr_id
323 AND parameter_type_code = 'ACTUAL'
324 AND TRUNC(SYSDATE) BETWEEN effective_from_date AND NVL(effective_to_date, TRUNC(SYSDATE));
325
326 CURSOR l_contract_info_csr(cp_khr_id IN NUMBER) IS
327 SELECT chr.start_date,
328 chr.currency_code,
329 chr.end_date
330 FROM OKC_K_HEADERS_B chr,
331 OKL_K_HEADERS khr
332 WHERE chr.id = khr.id
333 AND khr.id = cp_khr_id;
334
335 CURSOR l_int_calc_basis_csr(cp_khr_id IN NUMBER) IS
336 SELECT ppm.interest_calculation_basis
337 ,ppm.revenue_recognition_method
338 FROM okl_k_headers khr,
339 okl_product_parameters_v ppm
340 WHERE khr.id = cp_khr_id
341 AND khr.pdt_id = ppm.id;
342
343 CURSOR l_interest_due_csr (cp_khr_id NUMBER,
344 p_due_date DATE) IS
345 SELECT NVL(SUM(amount),0)
346 FROM okl_strm_elements sel,
347 okl_streams str,
348 okl_strm_type_v sty
349 WHERE sel.stm_id = str.id
350 AND str.khr_id = cp_khr_id
351 AND str.say_code = 'CURR'
352 AND str.active_yn = 'Y'
353 AND sel.stream_element_date <= p_due_date
354 AND str.sty_id = sty.id
355 AND sty.stream_type_purpose = 'INTEREST_PAYMENT';
356
357 l_contract_info_rec l_contract_info_csr%ROWTYPE;
358
359 BEGIN
360 l_debug_enabled := okl_debug_pub.check_log_enabled;
361 IF(NVL(l_debug_enabled,'N')='Y') THEN
362 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_interest_due');
363 END IF;
364
365 l_interest_amt := 0;
366 l_interest_due := 0;
367 x_return_status := OKL_API.G_RET_STS_SUCCESS;
368 IF ( p_khr_id IS NULL ) THEN
369 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
370 END IF;
371
372 OPEN l_principal_basis_csr(p_khr_id);
373 FETCH l_principal_basis_csr INTO l_principal_basis;
374 CLOSE l_principal_basis_csr;
375
376 --change for bug fix 4905791
377 IF (l_principal_basis IS NULL) THEN
378 --this is set to SCHEDULED so that the code works for Fixed Loans as well
379 l_principal_basis := 'SCHEDULED';
380 END IF;
381
382 OPEN l_contract_info_csr(p_khr_id);
383 FETCH l_contract_info_csr INTO l_contract_info_rec;
384 CLOSE l_contract_info_csr;
385
386 l_start_date := l_contract_info_rec.start_date;
387 l_end_date := l_contract_info_rec.end_date;
388
389 IF (l_end_date <= p_to_date) THEN
390 l_to_date := l_end_date;
391 ELSE
392 l_to_date := p_to_date;
393 END IF;
394
395 OPEN l_int_calc_basis_csr(p_khr_id);
396 FETCH l_int_calc_basis_csr INTO l_int_calc_basis, l_rev_rec_mthd;
397 CLOSE l_int_calc_basis_csr;
398
399
400 --change for bug fix 4905791
401 IF (l_rev_rec_mthd = 'ACTUAL') THEN
402 l_interest_due := OKL_VARIABLE_INTEREST_PVT.calculate_total_interest_due(
403 p_api_version => l_api_version,
404 p_init_msg_list => OKL_API.G_FALSE,
405 x_return_status => x_return_status,
406 x_msg_count => x_msg_count,
407 x_msg_data => x_msg_data,
408 p_contract_id => p_khr_id,
409 p_currency_code => l_contract_info_rec.currency_code,
410 p_start_date => l_start_date,
411 p_due_date => l_to_date,
412 p_principal_basis => 'ACTUAL');
413
414 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
415 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
416 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
417 RAISE OKL_API.G_EXCEPTION_ERROR;
418 END IF;
419 ELSE
420
421 IF l_principal_basis = 'ACTUAL' THEN
422 l_interest_due := OKL_VARIABLE_INTEREST_PVT.calculate_total_interest_due(
423 p_api_version => l_api_version,
424 p_init_msg_list => OKL_API.G_FALSE,
425 x_return_status => x_return_status,
426 x_msg_count => x_msg_count,
427 x_msg_data => x_msg_data,
428 p_contract_id => p_khr_id,
429 p_currency_code => l_contract_info_rec.currency_code,
430 p_start_date => l_start_date,
431 p_due_date => l_to_date,
432 p_principal_basis => 'ACTUAL');
433 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
434 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
435 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
436 RAISE OKL_API.G_EXCEPTION_ERROR;
437 END IF;
438 ELSIF l_principal_basis = 'SCHEDULED' THEN
439 /*OPEN l_int_calc_basis_csr(p_khr_id);
440 FETCH l_int_calc_basis_csr INTO l_int_calc_basis;
441 CLOSE l_int_calc_basis_csr;*/
442
443 --change for bug fix 4905791
444 IF (l_int_calc_basis IN ('REAMORT', 'FIXED')) THEN
445 OPEN l_interest_due_csr(p_khr_id, l_to_date);
446 FETCH l_interest_due_csr INTO l_interest_due;
447 CLOSE l_interest_due_csr;
448 ELSIF (l_int_calc_basis = 'FLOAT') THEN
449 LOOP
450 OKL_STREAM_GENERATOR_PVT.get_next_billing_date(
451 p_api_version => l_api_version,
452 p_init_msg_list => OKL_API.G_FALSE,
453 p_khr_id => p_khr_id,
454 p_billing_date => l_start_date,
455 x_next_due_date => l_due_date,
456 x_next_period_start_date => l_next_period_start_date,
457 x_next_period_end_date => l_next_period_end_date,
458 x_return_status => x_return_status,
459 x_msg_count => x_msg_count,
460 x_msg_data => x_msg_data);
461 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
462 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
463 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
464 RAISE OKL_API.G_EXCEPTION_ERROR;
465 END IF;
466 --dkagrawa changed condition from > to >= for bug 4742907
467 --dkagrawa handled nvl for fully billed contract bug#6660659
468 IF nvl(l_due_date,l_to_date) >= l_to_date THEN
469 l_due_date := l_to_date;
470 END IF;
471 l_interest_amt := OKL_VARIABLE_INTEREST_PVT.calculate_total_interest_due(
472 p_api_version => l_api_version,
473 p_init_msg_list => OKL_API.G_FALSE,
474 x_return_status => x_return_status,
475 x_msg_count => x_msg_count,
476 x_msg_data => x_msg_data,
477 p_contract_id => p_khr_id,
478 p_currency_code => l_contract_info_rec.currency_code,
479 p_start_date => l_next_period_start_date,
480 p_due_date => l_due_date,
481 p_principal_basis => 'SCHEDULED');
482
483 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
484 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
485 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
486 RAISE OKL_API.G_EXCEPTION_ERROR;
487 END IF;
488
489 l_interest_due := l_interest_due + l_interest_amt;
490 IF l_due_date >= l_to_date THEN
491 EXIT;
492 END IF;
493 l_start_date := l_due_date;
494 END LOOP;
495 END IF;
496 END IF;
497 END IF;
498 IF(NVL(l_debug_enabled,'N')='Y') THEN
499 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_interest_due');
500 END IF;
501
502 RETURN l_interest_due;
503 EXCEPTION
504 WHEN OKL_API.G_EXCEPTION_ERROR THEN
505 x_return_status := OKL_API.G_RET_STS_ERROR;
506 RETURN l_interest_due;
507 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
508 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
509 RETURN l_interest_due;
510 WHEN OTHERS THEN
511 IF l_principal_basis_csr%ISOPEN THEN
512 CLOSE l_principal_basis_csr;
513 END IF;
514 IF l_contract_info_csr%ISOPEN THEN
515 CLOSE l_contract_info_csr;
516 END IF;
517 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
518 OKL_API.SET_MESSAGE(
519 p_app_name => G_APP_NAME,
520 p_msg_name => G_UNEXPECTED_ERROR,
521 p_token1 => G_SQLCODE_TOKEN,
522 p_token1_value => SQLCODE,
523 p_token2 => G_SQLERRM_TOKEN,
524 p_token2_value => SQLERRM);
525 RETURN l_interest_due;
526 END get_interest_due;
527
528 ------------------------------------------------------------------------------
529 -- Start of Comments
530 -- Created By: dkagrawa
531 -- Function Name: get_principal_bal
532 -- Description: This Function is called to get principal balance on a
533 -- contract for a loan as of a given date
534 -- Inputs :
535 -- Output : Principal Balance
536 -- Dependencies:
537 -- Parameters: Contract id, Effective Date
538 -- Version: 1.0
539 -- History : sechawla 02-may-08 6939451 Set the contract id and deal type
540 -- when default proncipal basis is used.
541 -- End of Comments
542 -----------------------------------------------------------------------------
543
544 FUNCTION get_principal_bal(
545 x_return_status OUT NOCOPY VARCHAR2,
546 p_khr_id IN NUMBER,
547 p_kle_id IN NUMBER,
548 p_date IN DATE) RETURN NUMBER IS
549
550 l_api_version CONSTANT NUMBER := 1.0;
551 x_msg_count NUMBER;
552 x_msg_data VARCHAR2(2000);
553 l_debug_enabled VARCHAR2(1);
554 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_PRINCIPAL_BAL';
555
556 l_start_date DATE;
557 l_principal_basis okl_k_rate_params.principal_basis_code%TYPE;
558 l_principal_balance_tbl okl_variable_interest_pvt.principal_balance_tbl_typ;
559 l_principal_bal NUMBER;
560 l_stream_element_date DATE;
561
562 CURSOR l_principal_basis_csr(cp_khr_id IN NUMBER) IS
563 SELECT chr.start_date, rpm.principal_basis_code
564 FROM okc_k_headers_b chr,
565 okl_k_headers khr,
566 okl_k_rate_params rpm
567 WHERE chr.id = khr.id
568 AND rpm.khr_id = khr.id
569 AND rpm.parameter_type_code = 'ACTUAL'
570 AND TRUNC(SYSDATE) BETWEEN rpm.effective_from_date AND NVL(rpm.effective_to_date, TRUNC(SYSDATE))
571 AND khr.id = cp_khr_id;
572
573 Cursor sch_asset_prin_bal_date_csr (p_contract_id NUMBER,
574 p_line_id NUMBER,
575 p_due_date DATE) IS
576
577 SELECT MAX(sel.stream_element_date)
578 FROM
579 okl_strm_elements sel
580 ,okl_streams str
581 ,okl_strm_type_v sty
582 WHERE sel.stm_id = str.id
583 AND str.khr_id = p_contract_id
584 AND str.kle_id = p_line_id
585 AND str.say_code = 'CURR'
586 AND str.active_yn = 'Y'
587 AND sel.stream_element_date <= p_due_date
588 AND str.sty_id = sty.id
589 AND sty.stream_type_purpose = 'PRINCIPAL_BALANCE';
590
591 Cursor sch_ctr_prin_bal_date_csr (p_contract_id NUMBER,
592 p_due_date DATE) IS
593 SELECT MAX(sel.stream_element_date)
594 FROM
595 okl_strm_elements sel
596 ,okl_streams str
597 ,okl_strm_type_v sty
598 WHERE sel.stm_id = str.id
599 AND str.khr_id = p_contract_id
600 AND str.say_code = 'CURR'
601 AND str.active_yn = 'Y'
602 AND sel.stream_element_date <= p_due_date
603 AND str.sty_id = sty.id
604 AND sty.stream_type_purpose = 'PRINCIPAL_BALANCE';
605
606 --change for bug fix 4905791
607 CURSOR l_int_calc_basis_csr(cp_khr_id IN NUMBER) IS
608 SELECT ppm.interest_calculation_basis
609 ,ppm.revenue_recognition_method
610 FROM okl_k_headers khr,
611 okl_product_parameters_v ppm
612 WHERE khr.id = cp_khr_id
613 AND khr.pdt_id = ppm.id;
614
615 -- sechawla 02-may-08 6939451 Addec this cursor
616 Cursor contract_csr (p_contract_id NUMBER) IS
617 SELECT deal_type
618 FROM okl_k_headers
619 WHERE id = p_contract_id;
620
621 l_int_calc_basis OKL_PRODUCT_PARAMETERS_V.interest_calculation_basis%TYPE;
622 l_rev_rec_mthd OKL_PRODUCT_PARAMETERS_V.revenue_recognition_method%TYPE;
623
624 BEGIN
625 l_debug_enabled := okl_debug_pub.check_log_enabled;
626 IF(NVL(l_debug_enabled,'N')='Y') THEN
627 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_principal_bal');
628 END IF;
629
630 l_principal_bal := 0;
631 x_return_status := OKL_API.G_RET_STS_SUCCESS;
632 IF ( p_khr_id IS NULL ) THEN
633 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
634 END IF;
635
636 OPEN l_principal_basis_csr(p_khr_id);
637 FETCH l_principal_basis_csr INTO l_start_date,l_principal_basis;
638 CLOSE l_principal_basis_csr;
639
640 --change for bug fix 4905791
641 IF (l_principal_basis IS NULL) THEN
642 --this is set to SCHEDULED so that the code works for Fixed Loans as well
643 l_principal_basis := 'SCHEDULED';
644
645 -- sechawla 02-may-08 6939451 : When interest rate parametrs are not defined on the contract,
646 -- default principal basis 'SCHEDULED' is used, as per the existing assignment above
647 -- But in OKL_VARIABLE_INTEREST_PVT, the code tries to fetch principal basis again from the
648 -- interst rate paramaters on the contract, and fails when not found. This check is done based upon the
649 -- value of OKL_VARIABLE_INTEREST_PVT.G_CONTRACT_ID. If this global is not set, validation is done, but
650 -- If it is set, validation is by passed. Since in this case, validation is not needed, setting the
651 -- following 2 globals here, so OKL_VARIABLE_INTEREST_PVT can proceed with the default principal basis.
652 OKL_VARIABLE_INTEREST_PVT.G_CONTRACT_ID := p_khr_id;
653 OPEN contract_csr (p_khr_id);
654 FETCH contract_csr INTO OKL_VARIABLE_INTEREST_PVT.G_DEAL_TYPE;
655 CLOSE contract_csr;
656 -- sechawla 02-may-08 6939451 : end
657
658 END IF;
659
660
661 --change for bug fix 4905791
662 OPEN l_int_calc_basis_csr(p_khr_id);
663 FETCH l_int_calc_basis_csr INTO l_int_calc_basis, l_rev_rec_mthd;
664 CLOSE l_int_calc_basis_csr;
665
666 --change for bug fix 4905791
667 IF (l_principal_basis = 'ACTUAL' OR l_rev_rec_mthd = 'ACTUAL') THEN
668 OKL_VARIABLE_INTEREST_PVT.prin_date_range_var_rate_ctr (
669 p_api_version => l_api_version,
670 p_init_msg_list => OKL_API.G_FALSE,
671 x_return_status => x_return_status,
672 x_msg_count => x_msg_count,
673 x_msg_data => x_msg_data,
674 p_contract_id => p_khr_id,
675 p_line_id => p_kle_id,
676 p_start_date => l_start_date,
677 p_due_date => p_date,
678 p_principal_basis => 'ACTUAL',
679 x_principal_balance_tbl => l_principal_balance_tbl);
680
681 ELSIF l_principal_basis = 'SCHEDULED' THEN
682 IF (p_kle_id IS NOT NULL) THEN
683 OPEN sch_asset_prin_bal_date_csr(p_khr_id, p_kle_id,p_date);
684 FETCH sch_asset_prin_bal_date_csr INTO l_stream_element_date;
685 CLOSE sch_asset_prin_bal_date_csr;
686 ELSE
687 OPEN sch_ctr_prin_bal_date_csr(p_khr_id, p_date);
688 FETCH sch_ctr_prin_bal_date_csr INTO l_stream_element_date;
689 CLOSE sch_ctr_prin_bal_date_csr;
690 END IF;
691
692 IF (l_stream_element_date IS NULL) THEN
693 RETURN 0;
694 END IF;
695
696 OKL_VARIABLE_INTEREST_PVT.prin_date_range_var_rate_ctr (
697 p_api_version => l_api_version,
698 p_init_msg_list => OKL_API.G_FALSE,
699 x_return_status => x_return_status,
700 x_msg_count => x_msg_count,
701 x_msg_data => x_msg_data,
702 p_contract_id => p_khr_id,
703 p_line_id => p_kle_id,
704 p_start_date => l_stream_element_date,
705 p_due_date => l_stream_element_date,
706 p_principal_basis => 'SCHEDULED',
707 x_principal_balance_tbl => l_principal_balance_tbl);
708 END IF;
709
710 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
711 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
712 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
713 RAISE OKL_API.G_EXCEPTION_ERROR;
714 END IF;
715
716 IF l_principal_balance_tbl.COUNT > 0 THEN
717 l_principal_bal := l_principal_balance_tbl(l_principal_balance_tbl.COUNT).principal_balance;
718 END IF;
719
720 IF(NVL(l_debug_enabled,'N')='Y') THEN
721 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_principal_bal');
722 END IF;
723
724 RETURN l_principal_bal;
725 EXCEPTION
726 WHEN OKL_API.G_EXCEPTION_ERROR THEN
727
728 -- sechawla 02-may-08 6939451
729 IF contract_csr%ISOPEN THEN
730 CLOSE contract_csr;
731 END IF;
732
733 x_return_status := OKL_API.G_RET_STS_ERROR;
734 RETURN l_principal_bal;
735 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
736
737 -- sechawla 02-may-08 6939451
738 IF contract_csr%ISOPEN THEN
739 CLOSE contract_csr;
740 END IF;
741
742 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
743 RETURN l_principal_bal;
744 WHEN OTHERS THEN
745
746 -- sechawla 02-may-08 6939451
747 IF contract_csr%ISOPEN THEN
748 CLOSE contract_csr;
749 END IF;
750
751 IF l_principal_basis_csr%ISOPEN THEN
752 CLOSE l_principal_basis_csr;
753 END IF;
754 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
755 OKL_API.SET_MESSAGE(
756 p_app_name => G_APP_NAME,
757 p_msg_name => G_UNEXPECTED_ERROR,
758 p_token1 => G_SQLCODE_TOKEN,
759 p_token1_value => SQLCODE,
760 p_token2 => G_SQLERRM_TOKEN,
761 p_token2_value => SQLERRM);
762 RETURN l_principal_bal;
763 END get_principal_bal;
764
765 ------------------------------------------------------------------------------
766 -- Start of Comments
767 -- Created By: dkagrawa
768 -- Function Name: get_principal_paid
769 -- Description: This Function is called to get principal paid for a
770 -- date range for revolving loan
771 -- Inputs :
772 -- Output : Principal Paid
773 -- Dependencies:
774 -- Parameters: Contract id, Asset Line id, From Date, To Date
775 -- Version: 1.0
776 -- End of Comments
777 -----------------------------------------------------------------------------
778
779 FUNCTION get_principal_paid(
780 x_return_status OUT NOCOPY VARCHAR2,
781 p_khr_id IN NUMBER,
782 p_kle_id IN NUMBER,
783 p_from_date IN DATE,
784 p_to_date IN DATE DEFAULT SYSDATE) RETURN NUMBER IS
785
786 l_debug_enabled VARCHAR2(1);
787 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_PRINCIPAL_PAID';
788 x_msg_count NUMBER;
789 x_msg_data VARCHAR2(2000);
790
791 l_principal_paid NUMBER;
792
793 -- Begin - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
794 -- Begin bug 6456733
795 -- 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
796 CURSOR l_principal_paid_csr(cp_khr_id IN NUMBER, cp_from_date IN DATE, cp_to_date IN DATE) IS
797 SELECT SUM(principal_paid_amount) principal_paid_amount
798 FROM
799 (
800 SELECT SUM(AMOUNT)- SUM(AMOUNT_LINE_ITEMS_REMAINING) principal_paid_amount
801 FROM okl_bpd_ar_inv_lines_v lpt1
802 where contract_id=cp_khr_id
803 and RECEIVABLES_INVOICE_ID in
804 (
805 SELECT RECEIVABLES_INVOICE_ID
806 FROM
807 okl_bpd_ar_inv_lines_v lpt,
808 okl_strm_type_b sty,
809 ar_payment_schedules_all aps,
810 okl_k_headers_full_v khr
811 WHERE
812 lpt.contract_id = lpt1.contract_id
813 AND lpt.contract_number = khr.contract_number
814 AND lpt.sty_id = sty.id
815 AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
816 AND sty.stream_type_purpose IN
817 ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT','PRINCIPAL_CATCHUP')
818 AND TRUNC(aps.trx_date)
819 BETWEEN TRUNC(NVL(cp_from_date, khr.start_date)) AND TRUNC(NVL(cp_to_date, SYSDATE))
820 )
821 group by RECEIVABLES_INVOICE_ID
822 having SUM(AMOUNT_LINE_ITEMS_REMAINING)< SUM(AMOUNT)
823 UNION ALL
824 SELECT NVL(SUM(sel.amount),0) principal_paid_amount
825 FROM okl_strm_type_v sty,
826 okl_streams_v stm,
827 okl_strm_elements sel,
828 okc_k_headers_b khr
829 WHERE stm.khr_id = cp_khr_id
830 AND stm.kle_id = NVL(null, stm.kle_id)
831 AND stm.khr_id = khr.id
832 AND stm.sty_id = sty.id
833 AND sty.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
834 AND stm.id = sel.stm_id
835 AND TRUNC(sel.stream_element_date)
836 BETWEEN TRUNC(NVL(cp_from_date, khr.start_date)) AND TRUNC(NVL(cp_to_date, SYSDATE))
837 );
838
839 -- End bug 6456733
840 --SELECT SUM(principal_paid_amount) FROM
841 --(
842 -- SELECT NVL(SUM(app.amount_applied),0) principal_paid_amount
843 --SELECT NVL(SUM(app.line_applied),0) principal_paid_amount --End bug# 5767426
844 --FROM ar_receivable_applications_all app,
845 --ar_cash_receipts_all cra,
846 --ar_payment_schedules_all sch,
847 --okl_bpd_tld_ar_lines_v tld,
848 --okl_strm_type_v sty,
849 --okl_k_headers_full_v khr
850 --WHERE TRUNC(cra.receipt_date) BETWEEN TRUNC(NVL(cp_from_date, cra.receipt_date)) AND TRUNC(NVL(cp_to_date, SYSDATE))
851 --AND app.cash_receipt_id = cra.cash_receipt_id
852 --AND app.status = 'APP'
853 --AND app.applied_payment_schedule_id = sch.payment_schedule_id
854 --AND sch.class = 'INV'
855 --AND sch.customer_trx_id = tld.customer_trx_id
856 --AND tld.khr_id = cp_khr_id
857 --AND tld.kle_id = NVL(cp_kle_id, tld.kle_id)
858 --AND tld.khr_id = khr.id
859 --AND tld.sty_id = sty.id
860 --AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT', 'PRINCIPAL_CATCHUP')
861 --UNION
862 --SELECT NVL(SUM(sel.amount),0) principal_paid_amount
863 --FROM okl_strm_type_v sty,
864 --okl_streams_v stm,
865 --okl_strm_elements sel,
866 --okc_k_headers_b khr
867 --WHERE stm.khr_id = cp_khr_id
868 --AND stm.kle_id = NVL(cp_kle_id, stm.kle_id)
869 --AND stm.khr_id = khr.id
870 --AND stm.sty_id = sty.id
871 --AND sty.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
872 --AND stm.id = sel.stm_id
873 --AND TRUNC(sel.stream_element_date) BETWEEN TRUNC(NVL(cp_from_date, khr.start_date)) AND TRUNC(NVL(cp_to_date, SYSDATE)));
874 -- End - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
875
876
877 BEGIN
878 l_debug_enabled := okl_debug_pub.check_log_enabled;
879 IF(NVL(l_debug_enabled,'N')='Y') THEN
880 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_principal_paid');
881 END IF;
882
883 l_principal_paid := 0;
884 x_return_status := OKL_API.G_RET_STS_SUCCESS;
885 IF ( p_khr_id IS NULL ) THEN
886 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
887 END IF;
888 -- Begin bug 6456733
889 --OPEN l_principal_paid_csr(p_khr_id, p_kle_id, p_from_date, p_to_date);
890 OPEN l_principal_paid_csr(p_khr_id, p_from_date, p_to_date);
891 -- End bug 6456733
892 FETCH l_principal_paid_csr INTO l_principal_paid;
893 CLOSE l_principal_paid_csr;
894 IF(NVL(l_debug_enabled,'N')='Y') THEN
895 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_principal_paid');
896 END IF;
897
898 RETURN l_principal_paid;
899 EXCEPTION
900 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
901 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
902 RETURN l_principal_paid;
903 WHEN OTHERS THEN
904 IF l_principal_paid_csr%ISOPEN THEN
905 CLOSE l_principal_paid_csr;
906 END IF;
907 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
908 OKL_API.SET_MESSAGE(
909 p_app_name => G_APP_NAME,
910 p_msg_name => G_UNEXPECTED_ERROR,
911 p_token1 => G_SQLCODE_TOKEN,
912 p_token1_value => SQLCODE,
913 p_token2 => G_SQLERRM_TOKEN,
914 p_token2_value => SQLERRM);
915 RETURN l_principal_paid;
916 END get_principal_paid;
917
918 ------------------------------------------------------------------------------
919 -- Start of Comments
920 -- Created By: dkagrawa
921 -- Function Name:
922 -- Description: This Function is called to get an indicator Y/N if
923 -- the interest rate has changed
924 -- Inputs :
925 -- Output : interest rate change falg
926 -- Dependencies:
927 -- Parameters: Contract id
928 -- Version: 1.0
929 -- End of Comments
930 -----------------------------------------------------------------------------
931
932 FUNCTION get_interest_rate_change_flag(
933 x_return_status OUT NOCOPY VARCHAR2,
934 p_khr_id IN NUMBER) RETURN VARCHAR2 IS
935
936 l_debug_enabled VARCHAR2(1);
937 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_INTEREST_RATE_CHANGE_FLAG';
938 x_msg_count NUMBER;
939 x_msg_data VARCHAR2(2000);
940
941 l_interest_rate_change_flag VARCHAR2(1);
942 l_effective_int_rate NUMBER;
943
944 CURSOR l_var_int_params_csr(cp_khr_id IN NUMBER) IS
945 SELECT interest_calc_end_date, interest_rate
946 FROM okl_var_int_params
947 WHERE khr_id = cp_khr_id
948 ORDER BY interest_calc_end_date DESC;
949
950 l_var_int_params_rec l_var_int_params_csr%ROWTYPE;
951 BEGIN
952 l_debug_enabled := okl_debug_pub.check_log_enabled;
953 IF(NVL(l_debug_enabled,'N')='Y') THEN
954 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_interest_rate_change_flag');
955 END IF;
956
957 l_interest_rate_change_flag := 'N';
958 IF ( p_khr_id IS NULL ) THEN
959 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
960 END IF;
961
962 OPEN l_var_int_params_csr(p_khr_id);
963 FETCH l_var_int_params_csr INTO l_var_int_params_rec;
964 CLOSE l_var_int_params_csr;
965 l_effective_int_rate := get_effective_int_rate(
966 x_return_status => x_return_status,
967 p_khr_id => p_khr_id,
968 p_effective_date => l_var_int_params_rec.interest_calc_end_date + 1);
969
970 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
971 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
972 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
973 RAISE OKL_API.G_EXCEPTION_ERROR;
974 END IF;
975 IF l_effective_int_rate <> l_var_int_params_rec.interest_rate THEN
976 l_interest_rate_change_flag := 'Y';
977 END IF;
978
979 IF(NVL(l_debug_enabled,'N')='Y') THEN
980 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_interest_rate_change_flag');
981 END IF;
982
983 RETURN l_interest_rate_change_flag;
984 EXCEPTION
985 WHEN OKL_API.G_EXCEPTION_ERROR THEN
986 x_return_status := OKL_API.G_RET_STS_ERROR;
987 RETURN l_interest_rate_change_flag;
988 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
989 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
990 RETURN l_interest_rate_change_flag;
991 WHEN OTHERS THEN
992 IF l_var_int_params_csr%ISOPEN THEN
993 CLOSE l_var_int_params_csr;
994 END IF;
995 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
996 OKL_API.SET_MESSAGE(
997 p_app_name => G_APP_NAME,
998 p_msg_name => G_UNEXPECTED_ERROR,
999 p_token1 => G_SQLCODE_TOKEN,
1000 p_token1_value => SQLCODE,
1001 p_token2 => G_SQLERRM_TOKEN,
1002 p_token2_value => SQLERRM);
1003 RETURN l_interest_rate_change_flag;
1004 END get_interest_rate_change_flag;
1005
1006 ------------------------------------------------------------------------------
1007 -- Start of Comments
1008 -- Created By: dkagrawa
1009 -- Function Name: get_effective_int_rate
1010 -- Description: This Function is called to get effective interest rate
1011 -- as of a given date
1012 -- Inputs :
1013 -- Output : Effective interest rate
1014 -- Dependencies:
1015 -- Parameters: Contract id, Effective Date
1016 -- Version: 1.0
1017 -- End of Comments
1018 -----------------------------------------------------------------------------
1019
1020 FUNCTION get_effective_int_rate(
1021 x_return_status OUT NOCOPY VARCHAR2,
1022 p_khr_id IN NUMBER,
1023 p_effective_date IN DATE) RETURN NUMBER IS
1024 --dkagrawa changed cursor to use view OKL_PROD_QLTY_VAL_UV than okl_product_parameters_v
1025 CURSOR l_calc_basis_csr(cp_khr_id IN NUMBER) IS
1026 SELECT ppm.quality_val interest_calculation_basis
1027 , end_date
1028 FROM okl_k_headers_full_v khr,
1029 okl_prod_qlty_val_uv ppm
1030 WHERE khr.pdt_id = ppm.pdt_id
1031 AND ppm.quality_name = 'INTEREST_CALCULATION_BASIS'
1032 AND khr.id = cp_khr_id;
1033
1034 l_api_version CONSTANT NUMBER := 1.0;
1035 x_msg_count NUMBER;
1036 x_msg_data VARCHAR2(2000);
1037 l_debug_enabled VARCHAR2(1);
1038 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_EFFECTIVE_INT_RATE';
1039
1040 l_interest_rate_tbl okl_variable_interest_pvt.interest_rate_tbl_type;
1041 l_effective_int_rate NUMBER;
1042 l_process_flag okl_product_parameters_v.interest_calculation_basis%TYPE;
1043 l_end_date okl_k_headers_full_v.end_date%TYPE;
1044 l_effective_date DATE := NULL;
1045
1046 BEGIN
1047 l_debug_enabled := okl_debug_pub.check_log_enabled;
1048 IF(NVL(l_debug_enabled,'N')='Y') THEN
1049 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_effective_int_rate');
1050 END IF;
1051
1052 l_effective_int_rate := 0;
1053 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1054 IF ( p_khr_id IS NULL ) THEN
1055 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1056 END IF;
1057
1058 OPEN l_calc_basis_csr(p_khr_id);
1059 FETCH l_calc_basis_csr INTO l_process_flag, l_end_date;
1060 CLOSE l_calc_basis_csr;
1061
1062 l_effective_date := p_effective_date;
1063 IF (l_effective_date > l_end_date) THEN
1064 l_effective_date := l_end_date;
1065 END IF;
1066
1067 OKL_VARIABLE_INTEREST_PVT.interest_date_range (
1068 p_api_version => l_api_version,
1069 p_init_msg_list => OKL_API.G_FALSE,
1070 x_return_status => x_return_status,
1071 x_msg_count => x_msg_count,
1072 x_msg_data => x_msg_data,
1073 p_contract_id => p_khr_id,
1074 p_start_date => l_effective_date,
1075 p_end_date => l_effective_date,
1076 p_process_flag => l_process_flag,
1077 x_interest_rate_tbl => l_interest_rate_tbl);
1078
1079 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
1080 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1081 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
1082 RAISE OKL_API.G_EXCEPTION_ERROR;
1083 END IF;
1084
1085 IF l_interest_rate_tbl.COUNT > 0 THEN
1086 l_effective_int_rate := l_interest_rate_tbl(l_interest_rate_tbl.COUNT).rate;
1087 END IF;
1088
1089 IF(NVL(l_debug_enabled,'N')='Y') THEN
1090 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_effective_int_rate');
1091 END IF;
1092
1093 RETURN l_effective_int_rate;
1094 EXCEPTION
1095 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1096 x_return_status := OKL_API.G_RET_STS_ERROR;
1097 RETURN l_effective_int_rate;
1098 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1099 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1100 RETURN l_effective_int_rate;
1101 WHEN OTHERS THEN
1102 IF l_calc_basis_csr%ISOPEN THEN
1103 CLOSE l_calc_basis_csr;
1104 END IF;
1105 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1106 OKL_API.SET_MESSAGE(
1107 p_app_name => G_APP_NAME,
1108 p_msg_name => G_UNEXPECTED_ERROR,
1109 p_token1 => G_SQLCODE_TOKEN,
1110 p_token1_value => SQLCODE,
1111 p_token2 => G_SQLERRM_TOKEN,
1112 p_token2_value => SQLERRM);
1113 RETURN l_effective_int_rate;
1114 END get_effective_int_rate;
1115
1116 ------------------------------------------------------------------------------
1117 -- Start of Comments
1118 -- Created By: dkagrawa
1119 -- Function Name: get_interest_due_unbilled
1120 -- Description: This Function is called to get Interest due but not billed
1121 -- as of a given date for a Loan
1122 -- Inputs :
1123 -- Output : Unbilled Interest due
1124 -- Dependencies:
1125 -- Parameters: Contract id, Effective Date
1126 -- Version: 1.0
1127 -- End of Comments
1128 -----------------------------------------------------------------------------
1129
1130 FUNCTION get_interest_due_unbilled(
1131 x_return_status OUT NOCOPY VARCHAR2,
1132 p_khr_id IN NUMBER,
1133 p_effective_date IN DATE) RETURN NUMBER IS
1134
1135 l_debug_enabled VARCHAR2(1);
1136 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_INTEREST_DUE_UNBILLED';
1137 x_msg_count NUMBER;
1138 x_msg_data VARCHAR2(2000);
1139
1140 l_interest_billed NUMBER;
1141 l_interest_due NUMBER;
1142 l_interest_due_unbilled NUMBER;
1143 l_start_date DATE;
1144
1145 CURSOR l_start_date_csr(cp_khr_id IN NUMBER) IS
1146 SELECT chr.start_date
1147 FROM OKC_K_HEADERS_B chr,
1148 OKL_K_HEADERS khr
1149 WHERE chr.id = khr.id
1150 AND khr.id = cp_khr_id;
1151 BEGIN
1152 l_debug_enabled := okl_debug_pub.check_log_enabled;
1153 IF(NVL(l_debug_enabled,'N')='Y') THEN
1154 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_interest_due_unbilled');
1155 END IF;
1156
1157 l_interest_billed := 0;
1158 l_interest_due := 0;
1159 l_interest_due_unbilled := 0;
1160 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1161 IF ( p_khr_id IS NULL ) THEN
1162 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1163 END IF;
1164
1165 OPEN l_start_date_csr(p_khr_id);
1166 FETCH l_start_date_csr INTO l_start_date;
1167 CLOSE l_start_date_csr;
1168 l_interest_due := get_interest_due(
1169 x_return_status => x_return_status,
1170 p_khr_id => p_khr_id,
1171 p_to_date => p_effective_date);
1172 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1173 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1174 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1175 RAISE OKL_API.G_EXCEPTION_ERROR;
1176 END IF;
1177 l_interest_billed := get_interest_billed(
1178 x_return_status => x_return_status,
1179 p_khr_id => p_khr_id,
1180 p_from_date => l_start_date,
1181 p_to_date => p_effective_date);
1182 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1183 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1184 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1185 RAISE OKL_API.G_EXCEPTION_ERROR;
1186 END IF;
1187 l_interest_due_unbilled := l_interest_due - l_interest_billed;
1188
1189 IF(NVL(l_debug_enabled,'N')='Y') THEN
1190 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_interest_due_unbilled');
1191 END IF;
1192
1193 RETURN l_interest_due_unbilled;
1194 EXCEPTION
1195 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1196 x_return_status := OKL_API.G_RET_STS_ERROR;
1197 RETURN l_interest_due_unbilled;
1198 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1199 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1200 RETURN l_interest_due_unbilled;
1201 WHEN OTHERS THEN
1202 IF l_start_date_csr%ISOPEN THEN
1203 CLOSE l_start_date_csr;
1204 END IF;
1205 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1206 OKL_API.SET_MESSAGE(
1207 p_app_name => G_APP_NAME,
1208 p_msg_name => G_UNEXPECTED_ERROR,
1209 p_token1 => G_SQLCODE_TOKEN,
1210 p_token1_value => SQLCODE,
1211 p_token2 => G_SQLERRM_TOKEN,
1212 p_token2_value => SQLERRM);
1213 RETURN l_interest_due_unbilled;
1214 END get_interest_due_unbilled;
1215
1216 ------------------------------------------------------------------------------
1217 -- Start of Comments
1218 -- Created By: dkagrawa
1219 -- Function Name: get_principal_billed
1220 -- Description: This Function is called to get Principal Billed for a loan contract
1221 -- as of a given date range
1222 -- Inputs :
1223 -- Output : Principal billed
1224 -- Dependencies:
1225 -- Parameters: Contract id, Asset Line id, From Date, To Date
1226 -- Version: 1.0
1227 -- End of Comments
1228 -----------------------------------------------------------------------------
1229
1230 FUNCTION get_principal_billed(
1231 x_return_status OUT NOCOPY VARCHAR2,
1232 p_khr_id IN NUMBER,
1233 p_kle_id IN NUMBER,
1234 p_from_date IN DATE,
1235 p_to_date IN DATE DEFAULT SYSDATE) RETURN NUMBER IS
1236
1237 l_debug_enabled VARCHAR2(1);
1238 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_PRINCIPAL_BILLED';
1239 x_msg_count NUMBER;
1240 x_msg_data VARCHAR2(2000);
1241
1242 l_principal_billed NUMBER;
1243
1244 -- Begin - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
1245 -- Begin bug 6456733
1246 --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
1247 CURSOR l_principal_billed_csr(cp_khr_id IN NUMBER, cp_from_date IN DATE, cp_to_date IN DATE) IS
1248 -- dcshanmu bug 6734738 start
1249 --SELECT NVL(SUM(AMOUNT),0) principal_billed_amount
1250 --FROM okl_bpd_ar_inv_lines_v lpt1
1251 --where contract_id=cp_khr_id
1252 --and RECEIVABLES_INVOICE_ID in
1253 --(
1254 SELECT NVL(SUM(AMOUNT),0) principal_billed_amount --RECEIVABLES_INVOICE_ID
1255 -- dcshanmu bug 6734738 end
1256 FROM okl_bpd_ar_inv_lines_v lpt,
1257 okl_strm_type_b sty,
1258 ar_payment_schedules_all aps,
1259 okl_k_headers_full_v khr
1260 WHERE
1261 -- dcshanmu bug 6734738 start
1262 lpt.contract_id = cp_khr_id
1263 -- dcshanmu bug 6734738 end
1264 AND lpt.contract_number = khr.contract_number
1265 AND lpt.sty_id = sty.id
1266 AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
1267 AND sty.stream_type_purpose IN
1268 ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT','PRINCIPAL_CATCHUP')
1269 AND TRUNC(aps.trx_date) BETWEEN TRUNC(NVL(cp_from_date, khr.start_date))
1270 AND TRUNC(NVL(cp_to_date, SYSDATE))
1271 -- dcshanmu bug 6734738 start
1272 --)
1273 ;
1274 -- dcshanmu bug 6734738 end
1275 -- End bug 6456733
1276
1277 -- SELECT NVL(SUM(aps.amount_due_original), 0) principal_billed_amount
1278 --SELECT NVL(sum(aps.amount_line_items_original), 0) principal_billed_amount --End bug#5767426
1279 --FROM okl_bpd_tld_ar_lines_v tld,
1280 --okl_strm_type_b sty,
1281 --ar_payment_schedules_all aps,
1282 --okl_k_headers_full_v khr
1283 --WHERE tld.khr_id = cp_khr_id
1284 --AND tld.kle_id = NVL(cp_kle_id, tld.kle_id)
1285 --AND tld.khr_id = khr.id
1286 --AND tld.sty_id = sty.id
1287 --AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT','PRINCIPAL_CATCHUP')
1288 --AND tld.customer_trx_id = aps.customer_trx_id
1289 --AND TRUNC(aps.trx_date) BETWEEN TRUNC(NVL(cp_from_date, khr.start_date)) AND TRUNC(NVL(cp_to_date, SYSDATE));
1290 ---- End - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
1291
1292 BEGIN
1293 l_debug_enabled := okl_debug_pub.check_log_enabled;
1294 IF(NVL(l_debug_enabled,'N')='Y') THEN
1295 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_principal_billed');
1296 END IF;
1297
1298 l_principal_billed := 0;
1299 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1300 IF ( p_khr_id IS NULL ) THEN
1301 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1302 END IF;
1303
1304 -- Begin bug 6456733
1305 --OPEN l_principal_billed_csr(p_khr_id, p_kle_id, p_from_date, p_to_date);
1306 OPEN l_principal_billed_csr(p_khr_id, p_from_date, p_to_date);
1307 -- End bug 6456733
1308 FETCH l_principal_billed_csr INTO l_principal_billed;
1309 CLOSE l_principal_billed_csr;
1310
1311 IF(NVL(l_debug_enabled,'N')='Y') THEN
1312 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_principal_billed');
1313 END IF;
1314
1315 RETURN l_principal_billed;
1316 EXCEPTION
1317 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1318 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1319 RETURN l_principal_billed;
1320 WHEN OTHERS THEN
1321 IF l_principal_billed_csr%ISOPEN THEN
1322 CLOSE l_principal_billed_csr;
1323 END IF;
1324 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1325 OKL_API.SET_MESSAGE(
1326 p_app_name => G_APP_NAME,
1327 p_msg_name => G_UNEXPECTED_ERROR,
1328 p_token1 => G_SQLCODE_TOKEN,
1329 p_token1_value => SQLCODE,
1330 p_token2 => G_SQLERRM_TOKEN,
1331 p_token2_value => SQLERRM);
1332 RETURN l_principal_billed;
1333 END get_principal_billed;
1334
1335 ------------------------------------------------------------------------------
1336 -- Start of Comments
1337 -- Created By: dkagrawa
1338 -- Function Name: get_float_factor_billed
1339 -- Description: This Function is called to get Float Factor Billing Amount
1340 -- for a float factor contract as of a given date
1341 -- Inputs :
1342 -- Output : Float Factor Billed
1343 -- Dependencies:
1344 -- Parameters: Contract id, Effective Date
1345 -- Version: 1.0
1346 -- End of Comments
1347 -----------------------------------------------------------------------------
1348
1349 FUNCTION get_float_factor_billed(
1350 x_return_status OUT NOCOPY VARCHAR2,
1351 p_khr_id IN NUMBER,
1352 p_effective_date IN DATE) RETURN NUMBER IS
1353
1354 l_debug_enabled VARCHAR2(1);
1355 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_FLOAT_FACTOR_BILLED';
1356 x_msg_count NUMBER;
1357 x_msg_data VARCHAR2(2000);
1358
1359 l_float_factor_billed NUMBER;
1360
1361 -- Begin - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
1362 CURSOR l_float_billed_csr(cp_khr_id IN NUMBER, cp_effective_date IN DATE) IS
1363 -- Begin bug 6456733
1364 SELECT NVL(SUM(AMOUNT_DUE_ORIGINAL),0) interest_billed_amount
1365 FROM okl_bpd_ar_inv_lines_v lpt1
1366 where contract_id=cp_khr_id
1367 and RECEIVABLES_INVOICE_ID in
1368 (
1369 SELECT lpt.RECEIVABLES_INVOICE_ID
1370 FROM okl_bpd_ar_inv_lines_v lpt,
1371 okl_strm_type_b sty,
1372 ar_payment_schedules_all aps,
1373 okl_k_headers_full_v khr
1374 WHERE lpt.contract_id = lpt1.contract_id
1375 AND lpt.contract_number = khr.contract_number
1376 AND lpt.sty_id = sty.id
1377 AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
1378 AND sty.stream_type_purpose = 'FLOAT_FACTOR_ADJUSTMENT'
1379 AND TRUNC(aps.trx_date) <= cp_effective_date
1380 );
1381
1382 --SELECT NVL(SUM(aps.amount_due_original), 0) interest_billed_amount
1383 --FROM okl_bpd_tld_ar_lines_v tld,
1384 --okl_strm_type_b sty,
1385 --ar_payment_schedules_all aps,
1386 --okc_k_headers_b khr
1387 --WHERE tld.khr_id = cp_khr_id
1388 --AND tld.khr_id = khr.id
1389 --AND tld.sty_id = sty.id
1390 --AND sty.stream_type_purpose = 'FLOAT_FACTOR_ADJUSTMENT'
1391 --AND tld.customer_trx_id = aps.customer_trx_id
1392 --AND TRUNC(aps.trx_date) <= cp_effective_date;
1393
1394 -- End bug 6456733
1395 -- End - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
1396 BEGIN
1397 l_debug_enabled := okl_debug_pub.check_log_enabled;
1398 IF(NVL(l_debug_enabled,'N')='Y') THEN
1399 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_float_factor_billed');
1400 END IF;
1401
1402 l_float_factor_billed := 0;
1403 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1404 IF ( p_khr_id IS NULL ) THEN
1405 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1406 END IF;
1407
1408 OPEN l_float_billed_csr(p_khr_id, p_effective_date);
1409 FETCH l_float_billed_csr INTO l_float_factor_billed;
1410 CLOSE l_float_billed_csr;
1411
1412 IF(NVL(l_debug_enabled,'N')='Y') THEN
1413 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_float_factor_billed');
1414 END IF;
1415
1416 RETURN l_float_factor_billed;
1417 EXCEPTION
1418 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1419 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1420 RETURN l_float_factor_billed;
1421 WHEN OTHERS THEN
1422 IF l_float_billed_csr%ISOPEN THEN
1423 CLOSE l_float_billed_csr;
1424 END IF;
1425 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1426 OKL_API.SET_MESSAGE(
1427 p_app_name => G_APP_NAME,
1428 p_msg_name => G_UNEXPECTED_ERROR,
1429 p_token1 => G_SQLCODE_TOKEN,
1430 p_token1_value => SQLCODE,
1431 p_token2 => G_SQLERRM_TOKEN,
1432 p_token2_value => SQLERRM);
1433 RETURN l_float_factor_billed;
1434 END get_float_factor_billed;
1435
1436 ------------------------------------------------------------------------------
1437 -- Start of Comments
1438 -- Created By: dkagrawa
1439 -- Function Name: get_loan_payment_billed
1440 -- Description: Loan Payment Billed for a loan contract with
1441 -- a revenue recognition method of Actual
1442 -- Inputs :
1443 -- Output : Loan payment billed
1444 -- Dependencies:
1445 -- Parameters: Contract id, Effective Date
1446 -- Version: 1.0
1447 -- End of Comments
1448 -----------------------------------------------------------------------------
1449
1450 FUNCTION get_loan_payment_billed(
1451 x_return_status OUT NOCOPY VARCHAR2,
1452 p_khr_id IN NUMBER,
1453 p_effective_date IN DATE) RETURN NUMBER IS
1454
1455 l_debug_enabled VARCHAR2(1);
1456 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_LOAN_PAYMENT_BILLED';
1457 x_msg_count NUMBER;
1458 x_msg_data VARCHAR2(2000);
1459
1460 l_loan_payment_billed NUMBER;
1461
1462 -- Begin - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
1463 CURSOR l_loan_payment_billed_csr(cp_khr_id IN NUMBER, cp_effective_date IN DATE) IS
1464 --Begin bug 6456733
1465 SELECT NVL(SUM(lpt1.AMOUNT_DUE_ORIGINAL),0) loan_billed_amount
1466 FROM okl_bpd_ar_inv_lines_v lpt1
1467 where contract_id=cp_khr_id
1468 and RECEIVABLES_INVOICE_ID in
1469 (
1470 SELECT lpt.RECEIVABLES_INVOICE_ID
1471 FROM okl_bpd_ar_inv_lines_v lpt,
1472 okl_strm_type_b sty,
1473 ar_payment_schedules_all aps,
1474 okl_k_headers_full_v khr
1475 WHERE lpt.contract_id = lpt1.contract_id
1476 AND lpt.contract_number = khr.contract_number
1477 AND lpt.sty_id = sty.id
1478 AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
1479 AND sty.stream_type_purpose IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT', 'UNSCHEDULED_LOAN_PAYMENT')
1480 AND TRUNC(aps.trx_date) <= cp_effective_date
1481 );
1482
1483 --SELECT NVL(SUM(aps.amount_due_original), 0) loan_billed_amount
1484 --FROM okl_bpd_tld_ar_lines_v tld,
1485 --okl_strm_type_b sty,
1486 --ar_payment_schedules_all aps,
1487 --okl_k_headers_full_v khr
1488 --WHERE tld.khr_id = cp_khr_id
1489 --AND tld.khr_id = khr.id
1490 --AND tld.sty_id = sty.id
1491 --AND sty.stream_type_purpose IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT', 'UNSCHEDULED_LOAN_PAYMENT')
1492 --AND tld.customer_trx_id = aps.customer_trx_id
1493 --AND TRUNC(aps.trx_date) <= cp_effective_date;
1494
1495 --End bug 6456733
1496 -- End - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
1497
1498 BEGIN
1499 l_debug_enabled := okl_debug_pub.check_log_enabled;
1500 IF(NVL(l_debug_enabled,'N')='Y') THEN
1501 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_loan_payment_billed');
1502 END IF;
1503
1504 l_loan_payment_billed := 0;
1505 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1506 IF ( p_khr_id IS NULL ) THEN
1507 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1508 END IF;
1509
1510 OPEN l_loan_payment_billed_csr(p_khr_id, p_effective_date);
1511 FETCH l_loan_payment_billed_csr INTO l_loan_payment_billed;
1512 CLOSE l_loan_payment_billed_csr;
1513
1514 IF(NVL(l_debug_enabled,'N')='Y') THEN
1515 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_loan_payment_billed');
1516 END IF;
1517
1518 RETURN l_loan_payment_billed;
1519 EXCEPTION
1520 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1521 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1522 RETURN l_loan_payment_billed;
1523 WHEN OTHERS THEN
1524 IF l_loan_payment_billed_csr%ISOPEN THEN
1525 CLOSE l_loan_payment_billed_csr;
1526 END IF;
1527 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1528 OKL_API.SET_MESSAGE(
1529 p_app_name => G_APP_NAME,
1530 p_msg_name => G_UNEXPECTED_ERROR,
1531 p_token1 => G_SQLCODE_TOKEN,
1532 p_token1_value => SQLCODE,
1533 p_token2 => G_SQLERRM_TOKEN,
1534 p_token2_value => SQLERRM);
1535 RETURN l_loan_payment_billed;
1536 END get_loan_payment_billed;
1537
1538 ------------------------------------------------------------------------------
1539 -- Start of Comments
1540 -- Created By: dkagrawa
1541 -- Function Name: get_loan_payment_paid
1542 -- Description: Loan Payment Received for a loan contract with
1543 -- a revenue recognition method of Actual
1544 -- Inputs :
1545 -- Output : Loan payment Paid
1546 -- Dependencies:
1547 -- Parameters: Contract id, Effective Date
1548 -- Version: 1.0
1549 -- End of Comments
1550 -----------------------------------------------------------------------------
1551
1552 FUNCTION get_loan_payment_paid(
1553 x_return_status OUT NOCOPY VARCHAR2,
1554 p_khr_id IN NUMBER,
1555 p_effective_date IN DATE) RETURN NUMBER IS
1556
1557 l_debug_enabled VARCHAR2(1);
1558 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_LOAN_PAYMENT_PAID';
1559 x_msg_count NUMBER;
1560 x_msg_data VARCHAR2(2000);
1561
1562 l_loan_payment_paid NUMBER;
1563
1564 -- Begin - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
1565 CURSOR l_laon_payment_csr(cp_khr_id IN NUMBER, cp_effective_date IN DATE) IS
1566 -- Begin Bug 6456733
1567 SELECT
1568 (NVL(SUM(AMOUNT_DUE_ORIGINAL),0)- NVL(SUM(AMOUNT_LINE_ITEMS_REMAINING),0)) loan_paid_amount
1569 FROM okl_bpd_ar_inv_lines_v lpt1
1570 where contract_id=cp_khr_id
1571 and RECEIVABLES_INVOICE_ID in
1572 (
1573 SELECT lpt.RECEIVABLES_INVOICE_ID
1574 FROM okl_bpd_ar_inv_lines_v lpt,
1575 okl_strm_type_b sty,
1576 ar_payment_schedules_all aps,
1577 okl_k_headers_full_v khr
1578 WHERE lpt.contract_id = lpt1.contract_id
1579 AND lpt.contract_number = khr.contract_number
1580 AND lpt.sty_id = sty.id
1581 AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
1582 AND sty.stream_type_purpose IN
1583 ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT', 'UNSCHEDULED_LOAN_PAYMENT')
1584 AND TRUNC(aps.trx_date) <= cp_effective_date
1585 );
1586
1587 --SELECT NVL(SUM(app.amount_applied),0) loan_paid_amount
1588 --FROM ar_receivable_applications_all app,
1589 --ar_payment_schedules_all sch,
1590 --okl_bpd_tld_ar_lines_v tld,
1591 --okl_strm_type_v sty,
1592 --okl_k_headers_full_v khr
1593 --WHERE TRUNC(app.apply_date) <= cp_effective_date
1594 --AND app.status = 'APP'
1595 --AND app.applied_payment_schedule_id = sch.payment_schedule_id
1596 --AND sch.class = 'INV'
1597 --AND sch.customer_trx_id = tld.customer_trx_id
1598 --AND tld.khr_id = cp_khr_id
1599 --AND tld.khr_id = khr.id
1600 --AND tld.sty_id = sty.id
1601 --AND sty.stream_type_purpose IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT', 'UNSCHEDULED_LOAN_PAYMENT');
1602
1603 -- End bug 6456733
1604
1605 -- End - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
1606
1607 BEGIN
1608 l_debug_enabled := okl_debug_pub.check_log_enabled;
1609 IF(NVL(l_debug_enabled,'N')='Y') THEN
1610 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_loan_payment_paid');
1611 END IF;
1612
1613 l_loan_payment_paid := 0;
1614 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1615 IF ( p_khr_id IS NULL ) THEN
1616 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1617 END IF;
1618
1619 OPEN l_laon_payment_csr(p_khr_id, p_effective_date);
1620 FETCH l_laon_payment_csr INTO l_loan_payment_paid;
1621 CLOSE l_laon_payment_csr;
1622
1623 IF(NVL(l_debug_enabled,'N')='Y') THEN
1624 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_loan_payment_paid');
1625 END IF;
1626
1627 RETURN l_loan_payment_paid;
1628 EXCEPTION
1629 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1630 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1631 RETURN l_loan_payment_paid;
1632 WHEN OTHERS THEN
1633 IF l_laon_payment_csr%ISOPEN THEN
1634 CLOSE l_laon_payment_csr;
1635 END IF;
1636 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1637 OKL_API.SET_MESSAGE(
1638 p_app_name => G_APP_NAME,
1639 p_msg_name => G_UNEXPECTED_ERROR,
1640 p_token1 => G_SQLCODE_TOKEN,
1641 p_token1_value => SQLCODE,
1642 p_token2 => G_SQLERRM_TOKEN,
1643 p_token2_value => SQLERRM);
1644 RETURN l_loan_payment_paid;
1645 END get_loan_payment_paid;
1646
1647 ------------------------------------------------------------------------------
1648 -- Start of Comments
1649 -- Created By: dkagrawa
1650 -- Function Name: get_excess_loan_payment
1651 -- Description: Excess Loan Payment Received for a loan contract with
1652 -- a revenue recognition method of Actual
1653 -- Inputs :
1654 -- Output : Loan payment Paid
1655 -- Dependencies:
1656 -- Parameters: Contract id
1657 -- Version: 1.0
1658 -- End of Comments
1659 -----------------------------------------------------------------------------
1660
1661 FUNCTION get_excess_loan_payment(
1662 x_return_status OUT NOCOPY VARCHAR2,
1663 p_khr_id IN NUMBER) RETURN NUMBER IS
1664
1665 l_debug_enabled VARCHAR2(1);
1666 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_EXCESS_LOAN_PAYMENT';
1667 x_msg_count NUMBER;
1668 x_msg_data VARCHAR2(2000);
1669
1670 l_excess_loan_payment NUMBER;
1671
1672 CURSOR l_laon_payment_csr(cp_khr_id IN NUMBER) IS
1673 SELECT NVL(SUM(sel.amount), 0) loan_excess_amount
1674 FROM okl_streams_v stm,
1675 okl_strm_type_v sty,
1676 okl_strm_elements_v sel
1677 WHERE stm.khr_id = cp_khr_id
1678 AND stm.id = sel.stm_id
1679 AND stm.sty_id = sty.id
1680 AND sty.stream_type_purpose = 'EXCESS_LOAN_PAYMENT_PAID';
1681
1682 BEGIN
1683 l_debug_enabled := okl_debug_pub.check_log_enabled;
1684 IF(NVL(l_debug_enabled,'N')='Y') THEN
1685 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_excess_loan_payment');
1686 END IF;
1687
1688 l_excess_loan_payment := 0;
1689 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1690 IF ( p_khr_id IS NULL ) THEN
1691 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1692 END IF;
1693
1694 OPEN l_laon_payment_csr(p_khr_id);
1695 FETCH l_laon_payment_csr INTO l_excess_loan_payment;
1696 CLOSE l_laon_payment_csr;
1697
1698 IF(NVL(l_debug_enabled,'N')='Y') THEN
1699 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_excess_loan_payment');
1700 END IF;
1701
1702 RETURN l_excess_loan_payment;
1703 EXCEPTION
1704 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1705 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1706 RETURN l_excess_loan_payment;
1707 WHEN OTHERS THEN
1708 IF l_laon_payment_csr%ISOPEN THEN
1709 CLOSE l_laon_payment_csr;
1710 END IF;
1711 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1712 OKL_API.SET_MESSAGE(
1713 p_app_name => G_APP_NAME,
1714 p_msg_name => G_UNEXPECTED_ERROR,
1715 p_token1 => G_SQLCODE_TOKEN,
1716 p_token1_value => SQLCODE,
1717 p_token2 => G_SQLERRM_TOKEN,
1718 p_token2_value => SQLERRM);
1719 RETURN l_excess_loan_payment;
1720 END get_excess_loan_payment;
1721
1722 ------------------------------------------------------------------------------
1723 -- Start of Comments
1724 -- Created By: dkagrawa
1725 -- Function Name: get_last_interim_int_calc_date
1726 -- Description: Returns the date last interim interest calculated
1727 -- for variable rate contract
1728 -- Inputs :
1729 -- Output : last interim interest calculated Date
1730 -- Dependencies:
1731 -- Parameters: Contract id
1732 -- Version: 1.0
1733 -- End of Comments
1734 -----------------------------------------------------------------------------
1735
1736 FUNCTION get_last_interim_int_calc_date(
1737 x_return_status OUT NOCOPY VARCHAR2,
1738 p_khr_id IN NUMBER) RETURN DATE IS
1739
1740 l_debug_enabled VARCHAR2(1);
1741 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_LAST_INTERIM_INT_CALC_DATE';
1742 x_msg_count NUMBER;
1743 x_msg_data VARCHAR2(2000);
1744 l_last_interest_date DATE;
1745
1746 CURSOR l_interest_calc_date_csr (cp_khr_id IN NUMBER) IS
1747 SELECT date_last_interim_interest_cal
1748 FROM okl_k_headers
1749 WHERE id = cp_khr_id;
1750 BEGIN
1751 l_debug_enabled := okl_debug_pub.check_log_enabled;
1752 IF(NVL(l_debug_enabled,'N')='Y') THEN
1753 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_last_interim_int_calc_date');
1754 END IF;
1755
1756 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1757 IF ( p_khr_id IS NULL ) THEN
1758 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1759 END IF;
1760
1761 OPEN l_interest_calc_date_csr(p_khr_id);
1762 FETCH l_interest_calc_date_csr INTO l_last_interest_date;
1763 CLOSE l_interest_calc_date_csr;
1764 IF(NVL(l_debug_enabled,'N')='Y') THEN
1765 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_last_interim_int_calc_date');
1766 END IF;
1767 RETURN l_last_interest_date;
1768 EXCEPTION
1769 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1770 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1771 RETURN l_last_interest_date;
1772 WHEN OTHERS THEN
1773 IF l_interest_calc_date_csr%ISOPEN THEN
1774 CLOSE l_interest_calc_date_csr;
1775 END IF;
1776 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1777 OKL_API.SET_MESSAGE(
1778 p_app_name => G_APP_NAME,
1779 p_msg_name => G_UNEXPECTED_ERROR,
1780 p_token1 => G_SQLCODE_TOKEN,
1781 p_token1_value => SQLCODE,
1782 p_token2 => G_SQLERRM_TOKEN,
1783 p_token2_value => SQLERRM);
1784 RETURN l_last_interest_date;
1785
1786 END get_last_interim_int_calc_date;
1787
1788 ------------------------------------------------------------------------------
1789 -- Start of Comments
1790 -- Created By: dkagrawa
1791 -- Function Name: get_last_sch_int_calc_date
1792 -- Description: Returns the last scheduled interest calculation date prior
1793 -- to the Termination Date
1794 -- Inputs :
1795 -- Output : last scheduled interest calculated Date
1796 -- Dependencies:
1797 -- Parameters: Contract id, Effective Date
1798 -- Version: 1.0
1799 -- End of Comments
1800 -----------------------------------------------------------------------------
1801
1802 FUNCTION get_last_sch_int_calc_date(
1803 x_return_status OUT NOCOPY VARCHAR2,
1804 p_khr_id IN NUMBER,
1805 p_effective_date IN DATE) RETURN DATE IS
1806
1807 l_debug_enabled VARCHAR2(1);
1808 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_LAST_SCH_INT_CALC_DATE';
1809 x_msg_count NUMBER;
1810 x_msg_data VARCHAR2(2000);
1811 x_no_data_found BOOLEAN;
1812 l_api_version CONSTANT NUMBER := 1.0;
1813
1814 l_last_interest_date DATE;
1815 l_pdtv_rec OKL_PRODUCTS_PUB.pdtv_rec_type;
1816 x_pdt_parameter_rec OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
1817 l_pay_freq NUMBER;
1818 l_start_date DATE;
1819 l_end_date DATE;
1820 l_date_terminated DATE;
1821 l_due_date DATE;
1822 l_next_period_start_date DATE;
1823 l_next_period_end_date DATE;
1824
1825 CURSOR l_pdt_csr(cp_khr_id IN NUMBER) IS
1826 SELECT pdt_id
1827 FROM okl_k_headers
1828 WHERE id = cp_khr_id;
1829
1830 CURSOR l_payment_freq_csr(cp_khr_id IN NUMBER) IS
1831 SELECT DECODE(sll.object1_id1,'M',1,'Q',3,'S',6,'A',12) pay_freq
1832 FROM okc_rules_b sll,
1833 okc_rules_b slh,
1834 okl_strm_type_v styp,
1835 okc_rule_groups_b rgp
1836 WHERE TO_NUMBER(sll.object2_id1) = slh.id
1837 AND sll.rule_information_category = 'LASLL'
1838 AND sll.dnz_chr_id = rgp.dnz_chr_id
1839 AND sll.rgp_id = rgp.id
1840 AND slh.rule_information_category = 'LASLH'
1841 AND slh.dnz_chr_id = rgp.dnz_chr_id
1842 AND slh.rgp_id = rgp.id
1843 AND slh.object1_id1 = styp.id
1844 AND styp.stream_type_purpose = 'RENT'
1845 AND rgp.rgd_code = 'LALEVL'
1846 AND rgp.dnz_chr_id = cp_khr_id
1847 AND ROWNUM < 2;
1848
1849 CURSOR l_date_csr(cp_khr_id IN NUMBER) IS
1850 SELECT chr.start_date,
1851 chr.end_date
1852 FROM OKC_K_HEADERS_B chr,
1853 OKL_K_HEADERS khr
1854 WHERE chr.id = khr.id
1855 AND khr.id = cp_khr_id;
1856
1857 CURSOR l_stream_csr(cp_khr_id IN NUMBER, cp_term_date IN DATE) IS
1858 SELECT max(sel.stream_element_date)
1859 FROM okl_streams_v stm,
1860 okl_strm_type_v sty,
1861 okl_strm_elements_v sel
1862 WHERE stm.khr_id = cp_khr_id
1863 AND stm.id = sel.stm_id
1864 AND stm.sty_id = sty.id
1865 AND sty.stream_type_purpose = 'RENT'
1866 AND sel.stream_element_date <= cp_term_date;
1867
1868 CURSOR l_catchup_csr (cp_khr_id IN NUMBER) IS
1869 SELECT catchup_start_date,DECODE(catchup_frequency_code,'MONTHLY',1,'QUARTERLY',3,'SEMI_ANNUAL',6,'ANNUAL',12) pay_freq
1870 FROM okl_k_rate_params
1871 WHERE khr_id = cp_khr_id;
1872
1873 BEGIN
1874 l_debug_enabled := okl_debug_pub.check_log_enabled;
1875 IF(NVL(l_debug_enabled,'N')='Y') THEN
1876 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_last_sch_int_calc_date');
1877 END IF;
1878 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1879 IF ( p_khr_id IS NULL ) THEN
1880 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1881 END IF;
1882
1883 OPEN l_pdt_csr(p_khr_id);
1884 FETCH l_pdt_csr INTO l_pdtv_rec.id;
1885 CLOSE l_pdt_csr;
1886
1887 OKL_SETUPPRODUCTS_PVT.getpdt_parameters(
1888 p_api_version => l_api_version,
1889 p_init_msg_list => OKL_API.G_FALSE,
1890 x_return_status => x_return_status,
1891 x_no_data_found => x_no_data_found,
1892 x_msg_count => x_msg_count,
1893 x_msg_data => x_msg_data,
1894 p_pdtv_rec => l_pdtv_rec,
1895 p_pdt_parameter_rec => x_pdt_parameter_rec );
1896 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1897 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1898 ELSIF ( x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1899 RAISE OKL_API.G_EXCEPTION_ERROR;
1900 END IF;
1901 OPEN l_date_csr(p_khr_id);
1902 FETCH l_date_csr INTO l_start_date,l_end_date;
1903 CLOSE l_date_csr;
1904
1905 l_date_terminated := p_effective_date;
1906 IF (l_date_terminated IS NULL) THEN
1907 l_date_terminated := l_end_date;
1908 END IF;
1909
1910 IF x_pdt_parameter_rec.interest_calculation_basis = 'REAMORT' THEN
1911 IF (l_date_terminated > l_end_date) THEN
1912 l_date_terminated := l_end_date;
1913 END IF;
1914
1915 OPEN l_payment_freq_csr(p_khr_id);
1916 FETCH l_payment_freq_csr INTO l_pay_freq;
1917 CLOSE l_payment_freq_csr;
1918 LOOP
1919 l_last_interest_date := l_start_date;
1920 l_start_date := add_months(l_start_date,l_pay_freq);
1921 EXIT WHEN(l_start_date > l_date_terminated);
1922 END LOOP;
1923 ELSIF x_pdt_parameter_rec.interest_calculation_basis = 'FLOAT_FACTORS' THEN
1924 OPEN l_stream_csr(p_khr_id,l_date_terminated);
1925 FETCH l_stream_csr INTO l_last_interest_date;
1926 CLOSE l_stream_csr;
1927 ELSIF x_pdt_parameter_rec.interest_calculation_basis = 'FLOAT' THEN
1928 IF (l_date_terminated > l_end_date) THEN
1929 l_date_terminated := l_end_date;
1930 END IF;
1931
1932 LOOP
1933 OKL_STREAM_GENERATOR_PVT.get_next_billing_date(
1934 p_api_version => l_api_version,
1935 p_init_msg_list => OKL_API.G_FALSE,
1936 p_khr_id => p_khr_id,
1937 p_billing_date => l_start_date,
1938 x_next_due_date => l_due_date,
1939 x_next_period_start_date => l_next_period_start_date,
1940 x_next_period_end_date => l_next_period_end_date,
1941 x_return_status => x_return_status,
1942 x_msg_count => x_msg_count,
1943 x_msg_data => x_msg_data);
1944 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
1945 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1946 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
1947 RAISE OKL_API.G_EXCEPTION_ERROR;
1948 END IF;
1949
1950 IF (l_due_date > l_date_terminated OR l_due_date IS NULL) THEN
1951 l_last_interest_date := l_start_date;
1952 EXIT;
1953 END IF;
1954 l_start_date := l_due_date;
1955 END LOOP;
1956 ELSIF x_pdt_parameter_rec.interest_calculation_basis = 'CATCHUP/CLEANUP' THEN
1957 IF (l_date_terminated >= l_end_date) THEN
1958 l_last_interest_date := l_end_date;
1959 ELSE
1960 OPEN l_catchup_csr(p_khr_id);
1961 FETCH l_catchup_csr INTO l_start_date,l_pay_freq;
1962 CLOSE l_catchup_csr;
1963 LOOP
1964 l_last_interest_date := l_start_date;
1965 l_start_date := add_months(l_start_date,l_pay_freq);
1966 EXIT WHEN(l_start_date > l_date_terminated);
1967 END LOOP;
1968
1969 IF (l_last_interest_date >= l_end_date) THEN
1970 l_last_interest_date := l_end_date;
1971 END IF;
1972 END IF;
1973 END IF;
1974
1975 IF(NVL(l_debug_enabled,'N')='Y') THEN
1976 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_last_sch_int_calc_date');
1977 END IF;
1978
1979 RETURN l_last_interest_date;
1980
1981 EXCEPTION
1982 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1983 x_return_status := OKL_API.G_RET_STS_ERROR;
1984 RETURN l_last_interest_date;
1985 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1986 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1987 RETURN l_last_interest_date;
1988 WHEN OTHERS THEN
1989 IF l_pdt_csr%ISOPEN THEN
1990 CLOSE l_pdt_csr;
1991 END IF;
1992 IF l_payment_freq_csr%ISOPEN THEN
1993 CLOSE l_payment_freq_csr;
1994 END IF;
1995 IF l_date_csr%ISOPEN THEN
1996 CLOSE l_date_csr;
1997 END IF;
1998 IF l_stream_csr%ISOPEN THEN
1999 CLOSE l_stream_csr;
2000 END IF;
2001 IF l_catchup_csr%ISOPEN THEN
2002 CLOSE l_catchup_csr;
2003 END IF;
2004 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2005 OKL_API.SET_MESSAGE(
2006 p_app_name => G_APP_NAME,
2007 p_msg_name => G_UNEXPECTED_ERROR,
2008 p_token1 => G_SQLCODE_TOKEN,
2009 p_token1_value => SQLCODE,
2010 p_token2 => G_SQLERRM_TOKEN,
2011 p_token2_value => SQLERRM);
2012 RETURN l_last_interest_date;
2013 END get_last_sch_int_calc_date;
2014
2015 ------------------------------------------------------------------------------
2016 -- Start of Comments
2017 -- Created By: dkagrawa
2018 -- Procedure Name: get_open_invoices
2019 -- Description: Derive a list, consisting of number, Invoice date, Remaining amount
2020 -- for open invoices for a Loan contract
2021 -- Inputs :
2022 -- Output : Invoice information table
2023 -- Dependencies:
2024 -- Parameters: Contract id
2025 -- Version: 1.0
2026 -- End of Comments
2027 -----------------------------------------------------------------------------
2028 PROCEDURE get_open_invoices(
2029 x_return_status OUT NOCOPY VARCHAR2,
2030 p_khr_id IN NUMBER,
2031 x_invoice_tbl OUT NOCOPY invoice_info_tbl_type) IS
2032
2033 l_debug_enabled VARCHAR2(1);
2034 l_module CONSTANT fnd_log_messages.MODULE%TYPE := 'okl.plsql.OKL_VARIABLE_INT_UTIL_PVT.GET_OPEN_INVOICES';
2035 x_msg_count NUMBER;
2036 x_msg_data VARCHAR2(2000);
2037
2038 --Begin - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
2039 CURSOR l_invoice_info_csr (cp_khr_id IN NUMBER) IS
2040 -- Begin bug 6456733
2041 SELECT
2042 lpt.amount_due_remaining,
2043 lpt.due_date INVOICE_DATE,
2044 lpt.TXD_ID LSM_ID,
2045 lpt.TXD_ID tld_id,
2046 aps.customer_trx_id receivables_invoice_id
2047 FROM
2048 okl_bpd_ar_inv_lines_v lpt,
2049 okl_strm_type_b sty,
2050 ar_payment_schedules_all aps,
2051 okl_k_headers_full_v khr
2052 WHERE
2053 lpt.CONTRACT_ID = cp_khr_id
2054 AND lpt.contract_number = khr.contract_number
2055 AND lpt.sty_id = sty.id
2056 AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
2057 AND aps.status = 'OP'
2058 AND aps.class = 'INV'
2059 AND sty.stream_type_purpose
2060 IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT', 'UNSCHEDULED_LOAN_PAYMENT');
2061
2062
2063 --SELECT sch.amount_due_remaining,
2064 --TLD.INVOICE_DATE INVOICE_DATE,
2065 --TLD.TLD_ID LSM_ID,
2066 --tld.TLD_id tld_id,
2067 -- lsm.receivables_invoice_id
2068 --sch.customer_trx_id receivables_invoice_id
2069 --FROM okl_bpd_tld_ar_lines_v tld,
2070 --ar_payment_schedules_all sch,
2071 --okl_strm_type_b sty
2072 --WHERE sch.customer_trx_id = tld.customer_trx_id
2073 --AND sch.status = 'OP'
2074 --AND sch.class = 'INV'
2075 --AND tld.khr_id = cp_khr_id
2076 --AND tld.sty_id = sty.id
2077 --AND sty.stream_type_purpose IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT', 'UNSCHEDULED_LOAN_PAYMENT');
2078
2079 -- End bug 6456733
2080
2081 -- End - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
2082
2083 BEGIN
2084 l_debug_enabled := okl_debug_pub.check_log_enabled;
2085 IF(NVL(l_debug_enabled,'N')='Y') THEN
2086 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVIUB.pls call get_open_invoices');
2087 END IF;
2088 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2089 IF ( p_khr_id IS NULL ) THEN
2090 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2091 END IF;
2092
2093 OPEN l_invoice_info_csr(p_khr_id);
2094 FETCH l_invoice_info_csr BULK COLLECT INTO x_invoice_tbl;
2095 CLOSE l_invoice_info_csr;
2096 IF(NVL(l_debug_enabled,'N')='Y') THEN
2097 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVIUB.pls call get_open_invoices');
2098 END IF;
2099
2100 EXCEPTION
2101 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2102 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2103 WHEN OTHERS THEN
2104 IF l_invoice_info_csr%ISOPEN THEN
2105 CLOSE l_invoice_info_csr;
2106 END IF;
2107 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2108 OKL_API.SET_MESSAGE(
2109 p_app_name => G_APP_NAME,
2110 p_msg_name => G_UNEXPECTED_ERROR,
2111 p_token1 => G_SQLCODE_TOKEN,
2112 p_token1_value => SQLCODE,
2113 p_token2 => G_SQLERRM_TOKEN,
2114 p_token2_value => SQLERRM);
2115 END get_open_invoices;
2116
2117 ------------------------------------------------------------------------------
2118
2119 END OKL_VARIABLE_INT_UTIL_PVT;