[Home] [Help]
PACKAGE BODY: APPS.OKL_LOAN_AMORT_SCHEDULE_PVT
Source
1 PACKAGE BODY OKL_LOAN_AMORT_SCHEDULE_PVT AS
2 /* $Header: OKLRLASB.pls 120.8 2008/02/20 22:06:11 sechawla noship $ */
3 ------------------------------------------------------------------------------
4 -- Global Variables
5 ------------------------------------------------------------------------------
6 G_BULK_SIZE NUMBER := 10000;
7 G_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_esg_transport_pvt';
8 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
9 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
10 ------------------------------------------------------------------------------
11 -- Record Type
12 ------------------------------------------------------------------------------
13
14 TYPE periods_rec_type IS RECORD (
15 start_date DATE,
16 end_date DATE
17 );
18
19 TYPE periods_tbl_type is table of periods_rec_type INDEX BY BINARY_INTEGER;
20
21 TYPE pymt_sched_rec_type IS RECORD (
22 start_date DATE,
23 stub_days NUMBER,
24 periods NUMBER,
25 frequency NUMBER,
26 arrears_yn VARCHAR2(1)
27 );
28
29 TYPE pymt_sched_tbl_type is table of pymt_sched_rec_type INDEX BY BINARY_INTEGER;
30
31 TYPE receipts_rec_type IS RECORD (
32 receipt_date DATE,
33 principal NUMBER,
34 interest NUMBER
35 );
36
37 TYPE receipts_tbl_type is table of receipts_rec_type INDEX BY BINARY_INTEGER;
38
39 ---------------------------------------------------------------------------
40 -- Procedures and Functions
41 ---------------------------------------------------------------------------
42
43 -- Start of comments
44 --
45 -- API name : get_pymt_sched_periods
46 -- Pre-reqs : None
47 -- Function : This procedure fetches the start and end dates
48 -- for all periods/stubs in the input contract's
49 -- payment schedule
50 --
51 -- Parameters :
52 -- IN : p_api_version - Standard input parameter
53 -- p_init_msg_list - Standard input parameter
54 -- p_chr_id - Contract Id
55 -- Version : 1.0
56 -- History : rpillay created.
57 -- End of comments
58
59 PROCEDURE get_pymt_sched_periods
60 (p_api_version IN NUMBER,
61 p_init_msg_list IN VARCHAR2,
62 x_return_status OUT NOCOPY VARCHAR2,
63 x_msg_count OUT NOCOPY NUMBER,
64 x_msg_data OUT NOCOPY VARCHAR2,
65 p_chr_id IN NUMBER,
66 x_arrears_yn OUT NOCOPY VARCHAR2,
67 x_periods_tbl OUT NOCOPY periods_tbl_type) IS
68
69 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
70 l_api_name CONSTANT VARCHAR2(30) := 'GET_PYMT_SCHED_PERIODS';
71 l_api_version CONSTANT NUMBER := 1.0;
72
73 CURSOR fin_cle_csr(p_chr_id IN NUMBER) IS
74 SELECT cleb_fin.id
75 FROM okc_k_lines_b cleb_fin,
76 okc_statuses_b sts
77 WHERE cleb_fin.dnz_chr_id = p_chr_id
78 AND cleb_fin.chr_id = p_chr_id
79 AND cleb_fin.lse_id = 33
80 AND cleb_fin.sts_code = sts.code
81 AND sts.ste_code <> 'CANCELLED';
82
83 fin_cle_rec fin_cle_csr%ROWTYPE;
84
85 CURSOR pymt_sched_csr(p_chr_id IN NUMBER,
86 p_cle_id IN NUMBER) IS
87 SELECT FND_DATE.canonical_to_date(sll.rule_information2) start_date,
88 TO_NUMBER(sll.rule_information7) stub_days,
89 TO_NUMBER(sll.rule_information3) periods,
90 DECODE(sll.object1_id1, 'M',1,'Q',3,'S',6,'A',12) frequency,
91 sll.rule_information10 arrears_yn
92 FROM okc_rules_b sll,
93 okc_rules_b slh,
94 okc_rule_groups_b rgp,
95 okl_strm_type_b sty
96 WHERE rgp.dnz_chr_id = p_chr_id
97 AND rgp.cle_id = p_cle_id
98 AND rgp.rgd_code = 'LALEVL'
99 AND slh.rgp_id = rgp.id
100 AND slh.rule_information_category = 'LASLH'
101 AND sll.object2_id1 = slh.id
102 AND sll.rule_information_category = 'LASLL'
103 AND sll.rgp_id = rgp.id
104 AND slh.object1_id1 = sty.id
105 AND sty.stream_type_purpose IN ('RENT','PRINCIPAL_PAYMENT')
106 ORDER BY start_date;
107
108 l_temp_pymt_sched_tbl pymt_sched_tbl_type;
109 l_pymt_sched_tbl pymt_sched_tbl_type;
110 l_periods_tbl periods_tbl_type;
111 l_temp_counter NUMBER;
112
113 l_counter NUMBER;
114 l_start_day NUMBER;
115 BEGIN
116
117 x_return_status := OKL_API.G_RET_STS_SUCCESS;
118
119 OPEN fin_cle_csr(p_chr_id => p_chr_id);
120 FETCH fin_cle_csr INTO fin_cle_rec;
121 CLOSE fin_cle_csr;
122
123 l_temp_counter := 0;
124 OPEN pymt_sched_csr(p_chr_id => p_chr_id,
125 p_cle_id => fin_cle_rec.id);
126 LOOP
127 FETCH pymt_sched_csr BULK COLLECT INTO l_temp_pymt_sched_tbl LIMIT G_BULK_SIZE;
128 EXIT WHEN l_temp_pymt_sched_tbl.COUNT = 0;
129
130 FOR i IN l_temp_pymt_sched_tbl.FIRST..l_temp_pymt_sched_tbl.LAST
131 LOOP
132 l_temp_counter := l_temp_counter + 1;
133 l_pymt_sched_tbl(l_temp_counter).start_date := l_temp_pymt_sched_tbl(i).start_date;
134 l_pymt_sched_tbl(l_temp_counter).stub_days := l_temp_pymt_sched_tbl(i).stub_days;
135 l_pymt_sched_tbl(l_temp_counter).periods := l_temp_pymt_sched_tbl(i).periods;
136 l_pymt_sched_tbl(l_temp_counter).frequency := l_temp_pymt_sched_tbl(i).frequency;
137 l_pymt_sched_tbl(l_temp_counter).arrears_yn := l_temp_pymt_sched_tbl(i).arrears_yn;
138
139 END LOOP;
140 END LOOP;
141 CLOSE pymt_sched_csr;
142
143 IF l_pymt_sched_tbl.COUNT > 0 THEN
144
145 l_counter := 1;
146 FOR i IN l_pymt_sched_tbl.FIRST..l_pymt_sched_tbl.LAST
147 LOOP
148
149 l_periods_tbl(l_counter).start_date := l_pymt_sched_tbl(i).start_date;
150
151 IF l_pymt_sched_tbl(i).stub_days IS NOT NULL THEN
152
153 l_periods_tbl(l_counter).end_date := l_periods_tbl(l_counter).start_date + l_pymt_sched_tbl(i).stub_days - 1;
154
155 l_counter := l_counter + 1;
156
157 ELSIF l_pymt_sched_tbl(i).periods IS NOT NULL THEN
158
159 FOR j IN 1..l_pymt_sched_tbl(i).periods
160 LOOP
161
162 l_start_day := TO_CHAR(l_periods_tbl(l_counter).start_date,'DD');
163 l_periods_tbl(l_counter).end_date :=
164 OKL_LLA_UTIL_PVT.calculate_end_date(p_start_date => l_periods_tbl(l_counter).start_date,
165 p_months => l_pymt_sched_tbl(i).frequency,
166 p_start_day => l_start_day,
167 p_contract_end_date => NULL);
168 l_counter := l_counter + 1;
169
170 IF (j < l_pymt_sched_tbl(i).periods) THEN
171 l_periods_tbl(l_counter).start_date := l_periods_tbl(l_counter - 1).end_date + 1;
172 END IF;
173 END LOOP;
174
175 END IF;
176
177 END LOOP;
178
179 x_arrears_yn := l_pymt_sched_tbl(l_pymt_sched_tbl.FIRST).arrears_yn;
180 END IF;
181
182 x_periods_tbl := l_periods_tbl;
183
184 EXCEPTION
185 WHEN OKL_API.G_EXCEPTION_ERROR Then
186 x_return_status := OKL_API.G_RET_STS_ERROR;
187
188 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
189 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
190
191 WHEN OTHERS THEN
192 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
193
194 END get_pymt_sched_periods;
195
196
197 -- Start of comments
198 --
199 -- API name : load_ln_streams_dtl
200 -- Pre-reqs : None
201 -- Function : This procedure loads the Amortization Schedule - Detail report - Detail report- both past and projected,
202 -- based on the billed and unbilled stream elements for the input contract,
203 -- as of the date on which Amortization schedule is requested
204 -- This schedule applies to Loans with Interest Calculation Basis = FIXED' or 'REAMORT' and
205 -- Revenue Recognition - STREAMS
206 --
207 --
208 -- Parameters :
209 -- IN : p_api_version - Standard input parameter
210 -- p_init_msg_list - Standard input parameter
211 -- p_chr_id - Contract ID
212 -- Version : 1.0
213 -- History : sechawla created.
214 -- sechawla 19-feb-08 6831074 :changed payment type G_PPD to G_BILLED/G_PROJECTED
215 -- End of comments
216
217 PROCEDURE load_ln_streams_dtl(
218 p_api_version IN NUMBER,
219 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
220 x_return_status OUT NOCOPY VARCHAR2,
221 x_msg_count OUT NOCOPY NUMBER,
222 x_msg_data OUT NOCOPY VARCHAR2,
223 p_chr_id IN NUMBER,
224 x_amort_sched_tbl OUT NOCOPY amort_sched_tbl_type) IS
225
226 l_api_name CONSTANT VARCHAR2(30) := 'load_ln_streams_dtl';
227 l_api_version CONSTANT NUMBER := 1.0;
228
229
230
231 l_amort_sched_tbl amort_sched_tbl_type;
232 indx NUMBER;
233 l_princ_bal NUMBER;
234 k NUMBER;
235 --l_last_billing_date DATE;
236
237 --This cursor fetches the total billed 'PRINCIPAL_PAYMENT' and 'INTEREST_PAYMENT'
238 --streams for the contract. Amounts are derived at the contract level i.e streams are
239 --summedup for all the assets and teh total amounts are returned as the header level amounts
240 CURSOR c_amort_sch_dtl_csr(cp_khr_id in number) is
241 SELECT bill_date
242 ,SUM(principal) principal
243 ,SUM(interest) interest
244 , 0 princ_pay_down,
245 payment_type
246 FROM
247 (
248 SELECT sel.stream_element_date bill_date
249 ,sel.amount principal
250 ,0 interest
251 ,decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
252 FROM okl_strm_elements sel,
253 okl_streams stm,
254 okl_strm_type_b sty
255 WHERE stm.khr_id = cp_khr_id
256 AND sty.id = stm.sty_id
257 AND sty.stream_type_purpose = 'PRINCIPAL_PAYMENT'
258 AND sel.stm_id = stm.id
259 -- AND sel.date_billed IS NOT NULL --Billing is done in OKL
260 AND stm.SAY_CODE = 'CURR'
261 AND stm.active_yn = 'Y'
262 UNION ALL
263 SELECT sel.stream_element_date bill_date
264 ,0 principal
265 ,sel.amount interest
266 ,decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
267 FROM okl_strm_elements sel,
268 okl_streams stm,
269 okl_strm_type_b sty
270 WHERE stm.khr_id = cp_khr_id
271 AND sty.id = stm.sty_id
272 AND sty.stream_type_purpose = 'INTEREST_PAYMENT'
273 AND sel.stm_id = stm.id
274 -- AND sel.date_billed IS NOT NULL --Billing is done in OKL
275 AND stm.SAY_CODE = 'CURR'
276 AND stm.active_yn = 'Y'
277 )
278 GROUP BY bill_date,payment_type
279 UNION ALL
280 SELECT bill_date,
281 0 principal,
282 0 interest,
283 SUM(princ_pay_down) princ_pay_down,
284 payment_type
285 FROM
286 (
287 SELECT sel.stream_element_date bill_date,
288 0 principal,
289 0 interest,
290 sel.amount princ_pay_down,
291 decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
292 FROM okl_strm_elements sel,
293 okl_streams stm,
294 okl_strm_type_b sty
295 WHERE stm.khr_id = cp_khr_id
296 AND sty.id = stm.sty_id
297 AND sty.stream_type_purpose = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
298 AND sel.stm_id = stm.id
299 AND stm.SAY_CODE = 'CURR'
300 AND stm.active_yn = 'Y'
301 )
302 -- principal paydown rebooks the contract and all streams are regenerated
303 -- Loan Payment Paydown is not permitted with Rev Rec = 'STREAMS'
304 GROUP BY bill_date, payment_type
305 ORDER BY bill_date;
306
307 TYPE temp_tbl_type IS TABLE OF c_amort_sch_dtl_csr%ROWTYPE INDEX BY BINARY_INTEGER;
308 l_temp_tbl temp_tbl_type;
309
310 BEGIN
311
312 x_return_status := OKL_API.G_RET_STS_SUCCESS;
313 -- Call start_activity to create savepoint, check compatibility
314 -- and initialize message list
315 x_return_status := OKL_API.START_ACTIVITY (
316 l_api_name
317 ,p_init_msg_list
318 ,'_PVT'
319 ,x_return_status);
320 -- Check if activity started successfully
321 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
322 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
323 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
324 RAISE OKL_API.G_EXCEPTION_ERROR;
325 END IF;
326
327 --Derive starting Principal Balance
328 Okl_Execute_Formula_Pub.EXECUTE(p_api_version => p_api_version,
329 p_init_msg_list => p_init_msg_list,
330 x_return_status => x_return_status,
331 x_msg_count => x_msg_count,
332 x_msg_data => x_msg_data,
333 p_formula_name => 'CONTRACT_FINANCED_AMOUNT',
334 p_contract_id => p_chr_id,
335 p_line_id => NULL,
336 x_value => l_princ_bal
337 );
338 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
339 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
340 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
341 RAISE OKL_API.G_EXCEPTION_ERROR;
342 END IF;
343
344 indx := 1;
345 l_amort_sched_tbl(indx).principal_balance := l_princ_bal;
346 OPEN c_amort_sch_dtl_csr(p_chr_id);
347 LOOP
348 l_temp_tbl.DELETE;
349 FETCH c_amort_sch_dtl_csr BULK COLLECT INTO l_temp_tbl LIMIT G_BULK_SIZE;
350 IF l_temp_tbl.COUNT > 0 THEN
351 FOR k IN l_temp_tbl.FIRST..l_temp_tbl.LAST LOOP
352 indx := indx + 1;
353
354 l_amort_sched_tbl(indx).start_date := l_temp_tbl(k).bill_date;
355 l_amort_sched_tbl(indx).loan_payment := l_temp_tbl(k).principal + l_temp_tbl(k).princ_pay_down + l_temp_tbl(k).interest;
356 l_amort_sched_tbl(indx).principal := l_temp_tbl(k).principal + l_temp_tbl(k).princ_pay_down;
357 l_amort_sched_tbl(indx).interest := l_temp_tbl(k).interest;
358 l_amort_sched_tbl(indx).principal_balance := l_amort_sched_tbl(indx - 1).principal_balance - l_temp_tbl(k).principal - l_temp_tbl(k).princ_pay_down;
359 l_amort_sched_tbl(indx).payment_type := l_temp_tbl(k).payment_type;
360
361 END LOOP;
362 END IF;
363 EXIT WHEN c_amort_sch_dtl_csr%NOTFOUND;
364 END LOOP;
365 CLOSE c_amort_sch_dtl_csr;
366
367 x_amort_sched_tbl := l_amort_sched_tbl;
368 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data);
369
370 EXCEPTION
371
372 ------------------------------------------------------------
373 -- Exception handling
374 ------------------------------------------------------------
375
376 WHEN OKL_API.G_EXCEPTION_ERROR THEN
377 IF c_amort_sch_dtl_csr%ISOPEN THEN
378 CLOSE c_amort_sch_dtl_csr;
379 END IF;
380
381
382 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
383 p_api_name => l_api_name,
384 p_pkg_name => G_PKG_NAME,
385 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
386 x_msg_count => x_msg_count,
387 x_msg_data => x_msg_data,
388 p_api_type => '_PVT');
389
390 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
391
392 IF c_amort_sch_dtl_csr%ISOPEN THEN
393 CLOSE c_amort_sch_dtl_csr;
394 END IF;
395
396
397 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
398 p_api_name => l_api_name,
399 p_pkg_name => G_PKG_NAME,
400 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
401 x_msg_count => x_msg_count,
402 x_msg_data => x_msg_data,
403 p_api_type => '_PVT');
404
405 WHEN OTHERS THEN
406 IF c_amort_sch_dtl_csr%ISOPEN THEN
407 CLOSE c_amort_sch_dtl_csr;
408 END IF;
409
410
411 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
412 p_api_name => l_api_name,
413 p_pkg_name => G_PKG_NAME,
414 p_exc_name => 'OTHERS',
415 x_msg_count => x_msg_count,
416 x_msg_data => x_msg_data,
417 p_api_type => '_PVT');
418
419 END load_ln_streams_dtl;
420
421
422
423 -- This function matches the input date with period start/end dates
424 -- and returns the start/end date. If input date matches with a start date,
425 -- the corresponding end date is returned. If input date matches with an end date,
426 -- the corresponding start date is returned.
427 FUNCTION get_period_start_end_date
428 (p_periods_tbl IN periods_tbl_type,
429 p_period_date IN DATE) RETURN DATE IS
430
431 k NUMBER;
432 BEGIN
433 IF p_periods_tbl.COUNT > 0 THEN
434 FOR k IN p_periods_tbl.FIRST..p_periods_tbl.LAST LOOP
435 IF p_periods_tbl(k).end_date = p_period_date THEN
436 RETURN p_periods_tbl(k).start_date;
437 ELSIF p_periods_tbl(k).start_date = p_period_date THEN
438 RETURN p_periods_tbl(k).end_date;
439 END IF;
440 END LOOP;
441 -- Control will come here when payments are in Arrears and stream element date is the first day of the
442 -- next period, and the next period falls outside the payment periods. For e.g if K term is
443 -- 01-jan-07 to 31-Dec-07, the last stream element date will be 01-jan-08, which is outside the
444 -- contract payment periods. In this case, amort schedule period end date will be same as the amort schedule period start date
445 RETURN p_period_date;
446 END IF;
447
448 RETURN NULL;
449
450 EXCEPTION
451 WHEN OTHERS THEN
452 RETURN NULL;
453 END;
454 -- Start of comments
455 --
456 -- API name : load_ln_streams_summ
457 -- Pre-reqs : None
458 -- Function : This procedure loads the Amortization Schedule - Summary report- both past and projected,
459 -- based on the billed and unbilled stream elements for the input contract,
460 -- as of the date on which Amortization schedule is requested
461 -- This schedule applies to Loans with Interest Calculation Basis = FIXED' or 'REAMORT' and
462 -- Revenue Recognition - STREAMS
463 --
464 --
465 -- Parameters :
466 -- IN : p_api_version - Standard input parameter
467 -- p_init_msg_list - Standard input parameter
468 -- p_chr_id - Contract ID
469 -- Version : 1.0
470 -- History : sechawla created.
471 -- End of comments
472
473 PROCEDURE load_ln_streams_summ(
474 p_api_version IN NUMBER,
475 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
476 x_return_status OUT NOCOPY VARCHAR2,
477 x_msg_count OUT NOCOPY NUMBER,
478 x_msg_data OUT NOCOPY VARCHAR2,
479 p_chr_id IN NUMBER,
480 x_amort_sched_tbl OUT NOCOPY amort_sched_tbl_type) IS
481
482 l_api_name CONSTANT VARCHAR2(30) := 'load_ln_streams_summ';
483 l_api_version CONSTANT NUMBER := 1.0;
484
485
486
487 l_amort_sched_tbl amort_sched_tbl_type;
488 indx NUMBER;
489 l_princ_bal NUMBER;
490 k NUMBER;
491
492
493 --This cursor fetches the total billed 'PRINCIPAL_PAYMENT' and 'INTEREST_PAYMENT'
494 --streams for the contract. Amounts are derived at the contract level i.e streams are
495 --summedup for all the assets and teh total amounts are returned as the header level amounts
496 CURSOR c_amort_sch_summ_csr(cp_khr_id IN NUMBER) IS
497 SELECT bill_date
498 ,(SUM(principal) + SUM(interest)) total_amount
499 ,SUM(principal) principal
500 ,SUM(interest) interest
501 FROM
502 (
503 SELECT sel.stream_element_date bill_date
504 ,sel.amount principal
505 ,0 interest
506 FROM okl_strm_elements sel,
507 okl_streams stm,
508 okl_strm_type_b sty
509 WHERE stm.khr_id = cp_khr_id
510 AND sty.id = stm.sty_id
511 AND sty.stream_type_purpose = 'PRINCIPAL_PAYMENT'
512 AND sel.stm_id = stm.id
513 -- AND sel.date_billed IS NOT NULL --Billing is done in OKL
514 AND stm.SAY_CODE = 'CURR'
515 AND stm.active_yn = 'Y'
516 UNION ALL
517 SELECT sel.stream_element_date bill_date
518 ,0 principal
519 ,sel.amount interest
520 FROM okl_strm_elements sel,
521 okl_streams stm,
522 okl_strm_type_b sty
523 WHERE stm.khr_id = cp_khr_id
524 AND sty.id = stm.sty_id
525 AND sty.stream_type_purpose = 'INTEREST_PAYMENT'
526 AND sel.stm_id = stm.id
527 -- AND sel.date_billed IS NOT NULL --Billing is done in OKL
528 AND stm.SAY_CODE = 'CURR'
529 AND stm.active_yn = 'Y'
530
531 )
532 GROUP BY bill_date
533 ORDER BY bill_date;
534
535 --This cursor selects the past principal paydown streams for a contract.
536 CURSOR c_amort_sch_ppd_csr(cp_khr_id IN NUMBER) IS
537 SELECT sel.stream_element_date bill_date
538 ,sum(sel.amount) ppd_amount
539 FROM okl_strm_elements sel,
540 okl_streams stm,
541 okl_strm_type_b sty
542 WHERE stm.khr_id = cp_khr_id
543 AND sty.id = stm.sty_id
544 AND sty.stream_type_purpose = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
545 AND sel.stm_id = stm.id
546 -- AND sel.date_billed IS NOT NULL --Billing is done in OKL
547 AND stm.SAY_CODE = 'CURR'
548 AND stm.active_yn = 'Y'
549 GROUP BY sel.stream_element_date
550 ORDER BY bill_date;
551
552
553 TYPE temp_tbl_type IS TABLE OF c_amort_sch_summ_csr%ROWTYPE INDEX BY BINARY_INTEGER;
554 l_temp_tbl temp_tbl_type;
555
556
557 l_periods_tbl periods_tbl_type;
558 l_arrears_yn VARCHAR2(1);
559
560 l_summ_total_amount NUMBER;
561 l_summ_principal NUMBER;
562
563 l_period_date DATE;
564 BEGIN
565
566 x_return_status := OKL_API.G_RET_STS_SUCCESS;
567 -- Call start_activity to create savepoint, check compatibility
568 -- and initialize message list
569 x_return_status := OKL_API.START_ACTIVITY (
570 l_api_name
571 ,p_init_msg_list
572 ,'_PVT'
573 ,x_return_status);
574 -- Check if activity started successfully
575 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
576 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
577 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
578 RAISE OKL_API.G_EXCEPTION_ERROR;
579 END IF;
580
581
582
583 --Derive Payment Schedule Periods
584 get_pymt_sched_periods(p_api_version => p_api_version,
585 p_init_msg_list => p_init_msg_list,
586 x_return_status => x_return_status,
587 x_msg_count => x_msg_count,
588 x_msg_data => x_msg_data,
589 p_chr_id => p_chr_id,
590 x_arrears_yn => l_arrears_yn,
591 x_periods_tbl => l_periods_tbl
592 );
593
594 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
595 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
596 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
597 RAISE Okl_Api.G_EXCEPTION_ERROR;
598 END IF;
599
600 --Derive starting Principal Balance
601 Okl_Execute_Formula_Pub.EXECUTE(p_api_version => p_api_version,
602 p_init_msg_list => p_init_msg_list,
603 x_return_status => x_return_status,
604 x_msg_count => x_msg_count,
605 x_msg_data => x_msg_data,
606 p_formula_name => 'CONTRACT_FINANCED_AMOUNT',
607 p_contract_id => p_chr_id,
608 p_line_id => NULL,
609 x_value => l_princ_bal
610 );
611 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
612 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
613 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
614 RAISE OKL_API.G_EXCEPTION_ERROR;
615 END IF;
616
617 indx := 1;
618 l_amort_sched_tbl(indx).principal_balance := l_princ_bal;
619
620 OPEN c_amort_sch_summ_csr(p_chr_id);
621 LOOP
622 l_temp_tbl.DELETE;
623 FETCH c_amort_sch_summ_csr BULK COLLECT INTO l_temp_tbl LIMIT G_BULK_SIZE;
624 IF l_temp_tbl.COUNT > 0 THEN
625 FOR k IN l_temp_tbl.FIRST..l_temp_tbl.LAST LOOP
626 indx := indx + 1;
627
628 IF l_arrears_yn = 'N' THEN -- Payments in Advance
629 l_amort_sched_tbl(indx).start_date := l_temp_tbl(k).bill_date;
630 l_amort_sched_tbl(indx).end_date := get_period_start_end_date(l_periods_tbl, l_amort_sched_tbl(indx).start_date);
631 ELSE --Payment in Arrears, stream element date will either be the last day of the period, or first day of the next period
632 l_period_date := get_period_start_end_date(l_periods_tbl,l_temp_tbl(k).bill_date); --l_temp_tbl(k).bill_date is the stream element date
633 IF l_period_date < l_temp_tbl(k).bill_date THEN
634 --stream element date (l_temp_tbl(k).bill_date) is the last day of the period
635 l_amort_sched_tbl(indx).start_date :=l_period_date;
636 l_amort_sched_tbl(indx).end_date := l_temp_tbl(k).bill_date;
637 ELSIF l_period_date > l_temp_tbl(k).bill_date THEN
638 --stream element date (l_temp_tbl(k).bill_date) is the first day of the next period
639 l_amort_sched_tbl(indx).start_date := l_temp_tbl(k).bill_date;
640 l_amort_sched_tbl(indx).end_date := l_period_date;
641 ELSE
642 -- l_period_date = l_temp_tbl(k).bill_date
643 -- stream element date falls outside the payment period
644 l_amort_sched_tbl(indx).start_date := l_temp_tbl(k).bill_date;
645 l_amort_sched_tbl(indx).end_date := l_temp_tbl(k).bill_date;
646 END IF;
647 END IF;
648 l_summ_total_amount := 0;
649 l_summ_principal := 0;
650
651 l_summ_total_amount := l_summ_total_amount + l_temp_tbl(k).total_amount;
652 l_summ_principal := l_summ_principal + l_temp_tbl(k).principal;
653
654 FOR c_amort_sch_ppd_rec IN c_amort_sch_ppd_csr(p_chr_id) LOOP
655 IF c_amort_sch_ppd_rec.bill_date BETWEEN l_amort_sched_tbl(indx).start_date AND l_amort_sched_tbl(indx).end_date THEN
656 l_summ_principal := l_summ_principal + c_amort_sch_ppd_rec.ppd_amount;
657 l_summ_total_amount := l_summ_total_amount + c_amort_sch_ppd_rec.ppd_amount;
658 END IF;
659 END LOOP;
660
661 l_amort_sched_tbl(indx).loan_payment := l_summ_total_amount ;
662 l_amort_sched_tbl(indx).principal := l_summ_principal;
663 l_amort_sched_tbl(indx).interest := l_temp_tbl(k).interest;
664 l_amort_sched_tbl(indx).principal_balance := l_amort_sched_tbl(indx - 1).principal_balance - l_amort_sched_tbl(indx).principal;
665 -- l_amort_sched_tbl(indx).payment_type := G_BILLED;
666
667 END LOOP;
668 END IF;
669 EXIT WHEN c_amort_sch_summ_csr%NOTFOUND;
670 END LOOP;
671 CLOSE c_amort_sch_summ_csr;
672 x_amort_sched_tbl := l_amort_sched_tbl;
673 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data);
674
675 EXCEPTION
676
677 ------------------------------------------------------------
678 -- Exception handling
679 ------------------------------------------------------------
680
681 WHEN OKL_API.G_EXCEPTION_ERROR THEN
682 IF c_amort_sch_summ_csr%ISOPEN THEN
683 CLOSE c_amort_sch_summ_csr;
684 END IF;
685
686 IF c_amort_sch_ppd_csr%ISOPEN THEN
687 CLOSE c_amort_sch_ppd_csr;
688 END IF;
689
690 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
691 p_api_name => l_api_name,
692 p_pkg_name => G_PKG_NAME,
693 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
694 x_msg_count => x_msg_count,
695 x_msg_data => x_msg_data,
696 p_api_type => '_PVT');
697
698 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
699
700 IF c_amort_sch_summ_csr%ISOPEN THEN
701 CLOSE c_amort_sch_summ_csr;
702 END IF;
703
704 IF c_amort_sch_ppd_csr%ISOPEN THEN
705 CLOSE c_amort_sch_ppd_csr;
706 END IF;
707
708 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
709 p_api_name => l_api_name,
710 p_pkg_name => G_PKG_NAME,
711 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
712 x_msg_count => x_msg_count,
713 x_msg_data => x_msg_data,
714 p_api_type => '_PVT');
715
716 WHEN OTHERS THEN
717 IF c_amort_sch_summ_csr%ISOPEN THEN
718 CLOSE c_amort_sch_summ_csr;
719 END IF;
720
721 IF c_amort_sch_ppd_csr%ISOPEN THEN
722 CLOSE c_amort_sch_ppd_csr;
723 END IF;
724
725 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
726 p_api_name => l_api_name,
727 p_pkg_name => G_PKG_NAME,
728 p_exc_name => 'OTHERS',
729 x_msg_count => x_msg_count,
730 x_msg_data => x_msg_data,
731 p_api_type => '_PVT');
732
733 END load_ln_streams_summ;
734
735
736 -- Start of comments
737 --
738 -- API name : load_ln_actual_dtl
739 -- Pre-reqs : None
740 -- Function : This procedure loads the Amortization Schedule - Detail report
741 -- based on all receipts that have been processed by the Daily
742 -- Interest Program and projected payments for the remaining
743 -- loan term for the input contract. This schedule applies
744 -- to Loans with Revenue Recognition - ACTUAL
745 --
746 -- Parameters :
747 -- IN : p_api_version - Standard input parameter
748 -- p_init_msg_list - Standard input parameter
749 -- p_chr_id - Contract ID
750 -- Version : 1.0
751 -- History : rpillay created.
752 -- End of comments
753
754 PROCEDURE load_ln_actual_dtl(
755 p_api_version IN NUMBER,
756 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
757 x_return_status OUT NOCOPY VARCHAR2,
758 x_msg_count OUT NOCOPY NUMBER,
759 x_msg_data OUT NOCOPY VARCHAR2,
760 p_chr_id IN NUMBER,
761 x_proj_interest_rate OUT NOCOPY NUMBER,
762 x_amort_sched_tbl OUT NOCOPY amort_sched_tbl_type) IS
763
764 l_api_name CONSTANT VARCHAR2(30) := 'LOAD_LN_ACTUAL_DTL';
765 l_api_version CONSTANT NUMBER := 1.0;
766
767 CURSOR amort_sched_dtl_csr(p_chr_id IN NUMBER) IS
768 SELECT receipt_date,
769 SUM(principal) principal,
770 SUM(interest) interest
771 FROM
772 (
773 SELECT sel_dii.stream_element_date receipt_date,
774 0 principal,
775 sel_dii.amount interest,
776 sel_dii.request_id
777 FROM okl_strm_elements sel_dii,
778 okl_streams stm_dii,
779 okl_strm_type_b sty_dii
780 WHERE stm_dii.khr_id = p_chr_id
781 AND sty_dii.id = stm_dii.sty_id
782 AND sty_dii.stream_type_purpose = 'DAILY_INTEREST_INTEREST'
783 AND sel_dii.stm_id = stm_dii.id
784 AND stm_dii.say_code = 'CURR'
785 AND stm_dii.active_yn = 'Y'
786 UNION ALL
787 SELECT sel_dip.stream_element_date receipt_date,
788 sel_dip.amount principal,
789 0 interest,
790 sel_dip.request_id
791 FROM okl_strm_elements sel_dip,
792 okl_streams stm_dip,
793 okl_strm_type_b sty_dip
794 WHERE stm_dip.khr_id = p_chr_id
795 AND sty_dip.id = stm_dip.sty_id
796 AND sty_dip.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
797 AND sel_dip.stm_id = stm_dip.id
798 AND stm_dip.say_code = 'CURR'
799 AND stm_dip.active_yn = 'Y'
800 )
801 GROUP BY receipt_date,request_id
802 ORDER BY receipt_date;
803
804 l_temp_tbl receipts_tbl_type;
805
806 l_principal_balance NUMBER;
807 l_amort_sched_tbl amort_sched_tbl_type;
808 l_counter NUMBER;
809
810 l_schedule_tbl OKL_PRICING_PVT.schedule_table_type;
811 l_last_int_calc_date DATE;
812 l_proj_interest_rate NUMBER;
813
814 BEGIN
815
816 x_return_status := OKL_API.G_RET_STS_SUCCESS;
817 -- Call start_activity to create savepoint, check compatibility
818 -- and initialize message list
819 x_return_status := OKL_API.START_ACTIVITY (
820 l_api_name
821 ,p_init_msg_list
822 ,'_PVT'
823 ,x_return_status);
824 -- Check if activity started successfully
825 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
826 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
827 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
828 RAISE OKL_API.G_EXCEPTION_ERROR;
829 END IF;
830
831 --Derive Principal Balance
832 Okl_Execute_Formula_Pub.EXECUTE(p_api_version => p_api_version,
833 p_init_msg_list => p_init_msg_list,
834 x_return_status => x_return_status,
835 x_msg_count => x_msg_count,
836 x_msg_data => x_msg_data,
837 p_formula_name => 'CONTRACT_FINANCED_AMOUNT',
838 p_contract_id => p_chr_id,
839 p_line_id => NULL,
840 x_value => l_principal_balance
841 );
842 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
843 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
844 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
845 RAISE OKL_API.G_EXCEPTION_ERROR;
846 END IF;
847
848 l_counter := 1;
849 l_amort_sched_tbl(l_counter).principal_balance := l_principal_balance;
850
851 OPEN amort_sched_dtl_csr(p_chr_id => p_chr_id);
852 LOOP
853 FETCH amort_sched_dtl_csr BULK COLLECT INTO l_temp_tbl LIMIT G_BULK_SIZE;
854 EXIT WHEN l_temp_tbl.COUNT = 0;
855
856 FOR i IN l_temp_tbl.FIRST..l_temp_tbl.LAST
857 LOOP
858 l_counter := l_counter + 1;
859 l_amort_sched_tbl(l_counter).start_date := l_temp_tbl(i).receipt_date;
860 l_amort_sched_tbl(l_counter).principal := l_temp_tbl(i).principal;
861 l_amort_sched_tbl(l_counter).interest := l_temp_tbl(i).interest;
862 l_amort_sched_tbl(l_counter).loan_payment := l_temp_tbl(i).principal + l_temp_tbl(i).interest;
863 l_amort_sched_tbl(l_counter).principal_balance := l_amort_sched_tbl(l_counter - 1).principal_balance - l_temp_tbl(i).principal;
864 l_amort_sched_tbl(l_counter).payment_type := G_RECEIVED;
865 END LOOP;
866 END LOOP;
867 CLOSE amort_sched_dtl_csr;
868
869 IF (l_amort_sched_tbl(l_counter).principal_balance > 0) THEN
870
871 l_last_int_calc_date := OKL_VARIABLE_INTEREST_PVT.get_last_int_calc_date(p_khr_id => p_chr_id);
872
873 --Generate Projected Schedule
874 OKL_PRICING_PVT.generate_loan_schedules
875 (p_khr_id => p_chr_id,
876 p_investment => l_amort_sched_tbl(l_counter).principal_balance,
877 p_start_date => l_last_int_calc_date + 1,
878 x_interest_rate => l_proj_interest_rate,
879 x_schedule_table => l_schedule_tbl,
880 x_return_status => x_return_status);
881
882 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
883 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
884 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
885 RAISE OKL_API.G_EXCEPTION_ERROR;
886 END IF;
887
888 IF (l_schedule_tbl.COUNT > 0) THEN
889
890 FOR i IN l_schedule_tbl.FIRST..l_schedule_tbl.LAST
891 LOOP
892 l_counter := l_counter + 1;
893 l_amort_sched_tbl(l_counter).start_date := l_schedule_tbl(i).schedule_date;
894 l_amort_sched_tbl(l_counter).principal := l_schedule_tbl(i).schedule_principal;
895 l_amort_sched_tbl(l_counter).interest := l_schedule_tbl(i).schedule_interest;
896 l_amort_sched_tbl(l_counter).loan_payment := l_schedule_tbl(i).schedule_principal + l_schedule_tbl(i).schedule_interest;
897 l_amort_sched_tbl(l_counter).principal_balance := l_amort_sched_tbl(l_counter - 1).principal_balance - l_schedule_tbl(i).schedule_principal;
898 l_amort_sched_tbl(l_counter).payment_type := G_PROJECTED;
899 END LOOP;
900
901 END IF;
902
903 END IF;
904
905 x_amort_sched_tbl := l_amort_sched_tbl;
906 x_proj_interest_rate := l_proj_interest_rate;
907
908 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data);
909
910 EXCEPTION
911
912 ------------------------------------------------------------
913 -- Exception handling
914 ------------------------------------------------------------
915
916 WHEN OKL_API.G_EXCEPTION_ERROR THEN
917
918 IF amort_sched_dtl_csr%ISOPEN THEN
919 CLOSE amort_sched_dtl_csr;
920 END IF;
921
922 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
923 p_api_name => l_api_name,
924 p_pkg_name => G_PKG_NAME,
925 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
926 x_msg_count => x_msg_count,
927 x_msg_data => x_msg_data,
928 p_api_type => '_PVT');
929
930 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
931
932 IF amort_sched_dtl_csr%ISOPEN THEN
933 CLOSE amort_sched_dtl_csr;
934 END IF;
935
936 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
937 p_api_name => l_api_name,
938 p_pkg_name => G_PKG_NAME,
939 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
940 x_msg_count => x_msg_count,
941 x_msg_data => x_msg_data,
942 p_api_type => '_PVT');
943
944 WHEN OTHERS THEN
945
946 IF amort_sched_dtl_csr%ISOPEN THEN
947 CLOSE amort_sched_dtl_csr;
948 END IF;
949
950 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
951 p_api_name => l_api_name,
952 p_pkg_name => G_PKG_NAME,
953 p_exc_name => 'OTHERS',
954 x_msg_count => x_msg_count,
955 x_msg_data => x_msg_data,
956 p_api_type => '_PVT');
957
958 END load_ln_actual_dtl;
959
960 -- Start of comments
961 -- API name : load_ln_actual_summ
962 -- Pre-reqs : None
963 -- Function : This procedure loads the Amortization Schedule - Summary report
964 -- based on all receipts that have been processed by the Daily
965 -- Interest Program and projected payments for the remaining
966 -- loan term for the input contract. This schedule applies
967 -- to Loans with Revenue Recognition - ACTUAL
968 --
969 -- Parameters :
970 -- IN : p_api_version - Standard input parameter
971 -- p_init_msg_list - Standard input parameter
972 -- p_chr_id - Contract ID
973 -- Version : 1.0
974 -- History : rpillay created.
975 -- End of comments
976
977 PROCEDURE load_ln_actual_summ(
978 p_api_version IN NUMBER,
979 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
980 x_return_status OUT NOCOPY VARCHAR2,
981 x_msg_count OUT NOCOPY NUMBER,
982 x_msg_data OUT NOCOPY VARCHAR2,
983 p_chr_id IN NUMBER,
984 x_proj_interest_rate OUT NOCOPY NUMBER,
985 x_amort_sched_tbl OUT NOCOPY amort_sched_tbl_type) IS
986
987 l_api_name CONSTANT VARCHAR2(30) := 'LOAD_LN_ACTUAL_SUMM';
988 l_api_version CONSTANT NUMBER := 1.0;
989
990 CURSOR amort_sched_dtl_csr(p_chr_id IN NUMBER) IS
991 SELECT receipt_date,
992 SUM(principal) principal,
993 SUM(interest) interest
994 FROM
995 (
996 SELECT sel_dii.stream_element_date receipt_date,
997 0 principal,
998 sel_dii.amount interest
999 FROM okl_strm_elements sel_dii,
1000 okl_streams stm_dii,
1001 okl_strm_type_b sty_dii
1002 WHERE stm_dii.khr_id = p_chr_id
1003 AND sty_dii.id = stm_dii.sty_id
1004 AND sty_dii.stream_type_purpose = 'DAILY_INTEREST_INTEREST'
1005 AND sel_dii.stm_id = stm_dii.id
1006 AND stm_dii.say_code = 'CURR'
1007 AND stm_dii.active_yn = 'Y'
1008 UNION ALL
1009 SELECT sel_dip.stream_element_date receipt_date,
1010 sel_dip.amount principal,
1011 0 interest
1012 FROM okl_strm_elements sel_dip,
1013 okl_streams stm_dip,
1014 okl_strm_type_b sty_dip
1015 WHERE stm_dip.khr_id = p_chr_id
1016 AND sty_dip.id = stm_dip.sty_id
1017 AND sty_dip.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
1018 AND sel_dip.stm_id = stm_dip.id
1019 AND stm_dip.say_code = 'CURR'
1020 AND stm_dip.active_yn = 'Y'
1021 )
1022 GROUP BY receipt_date
1023 ORDER BY receipt_date;
1024
1025 l_temp_tbl receipts_tbl_type;
1026 l_temp1_tbl receipts_tbl_type;
1027 l_temp_counter NUMBER;
1028
1029 l_principal_balance NUMBER;
1030 l_amort_sched_tbl amort_sched_tbl_type;
1031 l_counter NUMBER;
1032 i NUMBER;
1033 l_max_receipt_date DATE;
1034
1035 l_periods_tbl periods_tbl_type;
1036 l_arrears_yn VARCHAR2(1);
1037
1038 l_schedule_tbl OKL_PRICING_PVT.schedule_table_type;
1039 l_min_schedule_date DATE;
1040 l_last_int_calc_date DATE;
1041 l_proj_interest_rate NUMBER;
1042
1043 BEGIN
1044
1045 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1046 -- Call start_activity to create savepoint, check compatibility
1047 -- and initialize message list
1048 x_return_status := OKL_API.START_ACTIVITY (
1049 l_api_name
1050 ,p_init_msg_list
1051 ,'_PVT'
1052 ,x_return_status);
1053 -- Check if activity started successfully
1054 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1055 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1056 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1057 RAISE OKL_API.G_EXCEPTION_ERROR;
1058 END IF;
1059
1060 --Derive Principal Balance
1061 Okl_Execute_Formula_Pub.EXECUTE(p_api_version => p_api_version,
1062 p_init_msg_list => p_init_msg_list,
1063 x_return_status => x_return_status,
1064 x_msg_count => x_msg_count,
1065 x_msg_data => x_msg_data,
1066 p_formula_name => 'CONTRACT_FINANCED_AMOUNT',
1067 p_contract_id => p_chr_id,
1068 p_line_id => NULL,
1069 x_value => l_principal_balance
1070 );
1071 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1072 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1073 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1074 RAISE OKL_API.G_EXCEPTION_ERROR;
1075 END IF;
1076
1077 l_temp_counter := 0;
1078 OPEN amort_sched_dtl_csr(p_chr_id => p_chr_id);
1079 LOOP
1080 FETCH amort_sched_dtl_csr BULK COLLECT INTO l_temp_tbl LIMIT G_BULK_SIZE;
1081 EXIT WHEN l_temp_tbl.COUNT = 0;
1082
1083 FOR i IN l_temp_tbl.FIRST..l_temp_tbl.LAST
1084 LOOP
1085 l_temp_counter := l_temp_counter + 1;
1086 l_temp1_tbl(l_temp_counter).receipt_date := l_temp_tbl(i).receipt_date;
1087 l_temp1_tbl(l_temp_counter).principal := l_temp_tbl(i).principal;
1088 l_temp1_tbl(l_temp_counter).interest := l_temp_tbl(i).interest;
1089 END LOOP;
1090 END LOOP;
1091 CLOSE amort_sched_dtl_csr;
1092
1093 --Derive Payment Schedule Periods
1094 get_pymt_sched_periods(p_api_version => p_api_version,
1095 p_init_msg_list => p_init_msg_list,
1096 x_return_status => x_return_status,
1097 x_msg_count => x_msg_count,
1098 x_msg_data => x_msg_data,
1099 p_chr_id => p_chr_id,
1100 x_arrears_yn => l_arrears_yn,
1101 x_periods_tbl => l_periods_tbl
1102 );
1103
1104 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1105 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1106 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1107 RAISE OKL_API.G_EXCEPTION_ERROR;
1108 END IF;
1109
1110 l_counter := 1;
1111 l_amort_sched_tbl(l_counter).principal_balance := l_principal_balance;
1112
1113 IF (l_temp1_tbl.COUNT > 0 AND l_periods_tbl.COUNT > 0) THEN
1114
1115 i := l_temp1_tbl.FIRST;
1116 l_max_receipt_date := l_temp1_tbl(l_temp1_tbl.LAST).receipt_date;
1117
1118 FOR j IN l_periods_tbl.FIRST..l_periods_tbl.LAST
1119 LOOP
1120
1121 EXIT WHEN l_periods_tbl(j).start_date > l_max_receipt_date;
1122
1123 l_counter := l_counter + 1;
1124 l_amort_sched_tbl(l_counter).start_date := l_periods_tbl(j).start_date;
1125 l_amort_sched_tbl(l_counter).end_date := l_periods_tbl(j).end_date;
1126 l_amort_sched_tbl(l_counter).principal := 0;
1127 l_amort_sched_tbl(l_counter).interest := 0;
1128 l_amort_sched_tbl(l_counter).loan_payment := 0;
1129 l_amort_sched_tbl(l_counter).principal_balance := l_amort_sched_tbl(l_counter - 1).principal_balance;
1130
1131 WHILE ((i <= l_temp1_tbl.COUNT) AND
1132 (l_temp1_tbl(i).receipt_date BETWEEN l_periods_tbl(j).start_date AND l_periods_tbl(j).end_date))
1133 LOOP
1134 l_amort_sched_tbl(l_counter).principal := l_amort_sched_tbl(l_counter).principal + l_temp1_tbl(i).principal;
1135 l_amort_sched_tbl(l_counter).interest := l_amort_sched_tbl(l_counter).interest + l_temp1_tbl(i).interest;
1136 l_amort_sched_tbl(l_counter).loan_payment := l_amort_sched_tbl(l_counter).loan_payment + l_temp1_tbl(i).principal + l_temp1_tbl(i).interest;
1137 l_amort_sched_tbl(l_counter).principal_balance := l_amort_sched_tbl(l_counter).principal_balance - l_temp1_tbl(i).principal;
1138 i := i + 1;
1139 END LOOP;
1140
1141 END LOOP;
1142
1143 -- Handle receipts that fall outside the payment schedule
1144 IF (i <= l_temp1_tbl.COUNT) THEN
1145
1146 l_counter := l_counter + 1;
1147 l_amort_sched_tbl(l_counter).start_date := l_periods_tbl(l_periods_tbl.LAST).end_date + 1;
1148 l_amort_sched_tbl(l_counter).principal := 0;
1149 l_amort_sched_tbl(l_counter).interest := 0;
1150 l_amort_sched_tbl(l_counter).loan_payment := 0;
1151 l_amort_sched_tbl(l_counter).principal_balance := l_amort_sched_tbl(l_counter - 1).principal_balance;
1152
1153 WHILE (i <= l_temp1_tbl.COUNT)
1154 LOOP
1155 l_amort_sched_tbl(l_counter).end_date := l_temp1_tbl(i).receipt_date;
1156 l_amort_sched_tbl(l_counter).principal := l_amort_sched_tbl(l_counter).principal + l_temp1_tbl(i).principal;
1157 l_amort_sched_tbl(l_counter).interest := l_amort_sched_tbl(l_counter).interest + l_temp1_tbl(i).interest;
1158 l_amort_sched_tbl(l_counter).loan_payment := l_amort_sched_tbl(l_counter).loan_payment + l_temp1_tbl(i).principal + l_temp1_tbl(i).interest;
1159 l_amort_sched_tbl(l_counter).principal_balance := l_amort_sched_tbl(l_counter).principal_balance - l_temp1_tbl(i).principal;
1160 i := i + 1;
1161 END LOOP;
1162 END IF;
1163
1164 END IF;
1165
1166 IF (l_amort_sched_tbl(l_counter).principal_balance > 0) THEN
1167
1168 l_last_int_calc_date := OKL_VARIABLE_INTEREST_PVT.get_last_int_calc_date(p_khr_id => p_chr_id);
1169
1170 --Generate Projected Schedule
1171 OKL_PRICING_PVT.generate_loan_schedules
1172 (p_khr_id => p_chr_id,
1173 p_investment => l_amort_sched_tbl(l_counter).principal_balance,
1174 p_start_date => l_last_int_calc_date + 1,
1175 x_interest_rate => l_proj_interest_rate,
1176 x_schedule_table => l_schedule_tbl,
1177 x_return_status => x_return_status);
1178
1179 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1180 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1181 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1182 RAISE OKL_API.G_EXCEPTION_ERROR;
1183 END IF;
1184
1185 IF (l_schedule_tbl.COUNT > 0 AND l_periods_tbl.COUNT > 0) THEN
1186
1187 i := l_schedule_tbl.FIRST;
1188 l_min_schedule_date := l_schedule_tbl(i).schedule_date;
1189
1190 FOR j IN l_periods_tbl.FIRST..l_periods_tbl.LAST
1191 LOOP
1192
1193 IF (l_periods_tbl(j).end_date < l_min_schedule_date) OR (i > l_schedule_tbl.COUNT) THEN
1194 NULL;
1195 ELSE
1196
1197 IF (l_counter > 1) AND (l_amort_sched_tbl(l_counter).start_date = l_periods_tbl(j).start_date) THEN
1198 NULL;
1199 ELSE
1200 l_counter := l_counter + 1;
1201 l_amort_sched_tbl(l_counter).start_date := l_periods_tbl(j).start_date;
1202 l_amort_sched_tbl(l_counter).end_date := l_periods_tbl(j).end_date;
1203 l_amort_sched_tbl(l_counter).principal := 0;
1204 l_amort_sched_tbl(l_counter).interest := 0;
1205 l_amort_sched_tbl(l_counter).loan_payment := 0;
1206 l_amort_sched_tbl(l_counter).principal_balance := l_amort_sched_tbl(l_counter - 1).principal_balance;
1207 END IF;
1208
1209 WHILE ((i <= l_schedule_tbl.COUNT) AND
1210 (l_schedule_tbl(i).schedule_date BETWEEN l_periods_tbl(j).start_date AND l_periods_tbl(j).end_date))
1211 LOOP
1212 l_amort_sched_tbl(l_counter).principal := l_amort_sched_tbl(l_counter).principal + l_schedule_tbl(i).schedule_principal;
1213 l_amort_sched_tbl(l_counter).interest := l_amort_sched_tbl(l_counter).interest + l_schedule_tbl(i).schedule_interest;
1214 l_amort_sched_tbl(l_counter).loan_payment := l_amort_sched_tbl(l_counter).loan_payment + l_schedule_tbl(i).schedule_principal + l_schedule_tbl(i).schedule_interest;
1215 l_amort_sched_tbl(l_counter).principal_balance := l_amort_sched_tbl(l_counter).principal_balance - l_schedule_tbl(i).schedule_principal;
1216 i := i + 1;
1217 END LOOP;
1218 END IF;
1219
1220 END LOOP;
1221
1222 -- Handle projected payments that fall outside the payment schedule
1223 IF (i <= l_schedule_tbl.COUNT) THEN
1224
1225 IF (l_counter > 1) AND (l_amort_sched_tbl(l_counter).start_date = (l_periods_tbl(l_periods_tbl.LAST).end_date + 1)) THEN
1226 NULL;
1227 ELSE
1228 l_counter := l_counter + 1;
1229 l_amort_sched_tbl(l_counter).start_date := l_periods_tbl(l_periods_tbl.LAST).end_date + 1;
1230 l_amort_sched_tbl(l_counter).principal := 0;
1231 l_amort_sched_tbl(l_counter).interest := 0;
1232 l_amort_sched_tbl(l_counter).loan_payment := 0;
1233 l_amort_sched_tbl(l_counter).principal_balance := l_amort_sched_tbl(l_counter - 1).principal_balance;
1234 END IF;
1235
1236 WHILE (i <= l_schedule_tbl.COUNT)
1237 LOOP
1238 l_amort_sched_tbl(l_counter).principal := l_amort_sched_tbl(l_counter).principal + l_schedule_tbl(i).schedule_principal;
1239 l_amort_sched_tbl(l_counter).interest := l_amort_sched_tbl(l_counter).interest + l_schedule_tbl(i).schedule_interest;
1240 l_amort_sched_tbl(l_counter).loan_payment := l_amort_sched_tbl(l_counter).loan_payment + l_schedule_tbl(i).schedule_principal + l_schedule_tbl(i).schedule_interest;
1241 l_amort_sched_tbl(l_counter).principal_balance := l_amort_sched_tbl(l_counter).principal_balance - l_schedule_tbl(i).schedule_principal;
1242 l_amort_sched_tbl(l_counter).end_date := l_schedule_tbl(i).schedule_date;
1243 i := i + 1;
1244 END LOOP;
1245
1246 END IF;
1247
1248 END IF;
1249
1250 END IF;
1251
1252 x_amort_sched_tbl := l_amort_sched_tbl;
1253 x_proj_interest_rate := l_proj_interest_rate;
1254
1255 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data);
1256
1257 EXCEPTION
1258
1259 ------------------------------------------------------------
1260 -- Exception handling
1261 ------------------------------------------------------------
1262
1263 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1264
1265 IF amort_sched_dtl_csr%ISOPEN THEN
1266 CLOSE amort_sched_dtl_csr;
1267 END IF;
1268
1269 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1270 p_api_name => l_api_name,
1271 p_pkg_name => G_PKG_NAME,
1272 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1273 x_msg_count => x_msg_count,
1274 x_msg_data => x_msg_data,
1275 p_api_type => '_PVT');
1276
1277 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1278
1279 IF amort_sched_dtl_csr%ISOPEN THEN
1280 CLOSE amort_sched_dtl_csr;
1281 END IF;
1282
1283 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1284 p_api_name => l_api_name,
1285 p_pkg_name => G_PKG_NAME,
1286 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1287 x_msg_count => x_msg_count,
1288 x_msg_data => x_msg_data,
1289 p_api_type => '_PVT');
1290
1291 WHEN OTHERS THEN
1292
1293 IF amort_sched_dtl_csr%ISOPEN THEN
1294 CLOSE amort_sched_dtl_csr;
1295 END IF;
1296
1297 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1298 p_api_name => l_api_name,
1299 p_pkg_name => G_PKG_NAME,
1300 p_exc_name => 'OTHERS',
1301 x_msg_count => x_msg_count,
1302 x_msg_data => x_msg_data,
1303 p_api_type => '_PVT');
1304
1305 END load_ln_actual_summ;
1306
1307 -- Start of comments
1308 --
1309 -- API name : load_ln_float_eb_dtl
1310 -- Pre-reqs : None
1311 -- Function : This procedure loads the Amortization Schedule - Detail report
1312 -- based on the billed and unbilled stream elements for the input contract,
1313 -- as of the date on which Amortization schedule is requested.
1314 -- This schedule applies to Loans with Interest Calculation Basis - FLOAT and
1315 -- Revenue Recognition - ESTIMATED_AND_BILLED
1316 --
1317 -- Parameters :
1318 -- IN : p_api_version - Standard input parameter
1319 -- p_init_msg_list - Standard input parameter
1320 -- p_chr_id - Contract ID
1321 -- Version : 1.0
1322 -- History : rpillay created.
1323 -- rpillay 20-Feb-08 Bug# 6831074 :Changed payment type G_PPD to G_BILLED/G_PROJECTED
1324 -- End of comments
1325
1326 PROCEDURE load_ln_float_eb_dtl(
1327 p_api_version IN NUMBER,
1328 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1329 x_return_status OUT NOCOPY VARCHAR2,
1330 x_msg_count OUT NOCOPY NUMBER,
1331 x_msg_data OUT NOCOPY VARCHAR2,
1332 p_chr_id IN NUMBER,
1333 x_amort_sched_tbl OUT NOCOPY amort_sched_tbl_type) IS
1334
1335 l_api_name CONSTANT VARCHAR2(30) := 'LOAD_LN_FLOAT_EB_DTL';
1336 l_api_version CONSTANT NUMBER := 1.0;
1337
1338 CURSOR last_bill_date_csr(p_chr_id IN NUMBER) IS
1339 SELECT MAX(sel.stream_element_date)
1340 FROM okl_streams stm,
1341 okl_strm_elements sel,
1342 okl_strm_type_b sty
1343 WHERE stm.khr_id = p_chr_id
1344 AND sty.id = stm.sty_id
1345 AND sty.stream_type_purpose = 'PRINCIPAL_PAYMENT'
1346 AND sel.stm_id = stm.id
1347 AND stm.say_code = 'CURR'
1348 AND stm.active_yn = 'Y'
1349 AND sel.date_billed IS NOT NULL;
1350
1351 l_last_bill_date OKL_STRM_ELEMENTS.stream_element_date%TYPE;
1352
1353 CURSOR chr_csr(p_chr_id IN NUMBER) IS
1354 SELECT chr.start_date
1355 FROM okc_k_headers_b chr
1356 WHERE chr.id = p_chr_id;
1357
1358 CURSOR amort_sched_dtl_csr(p_chr_id IN NUMBER,
1359 p_last_bill_date IN DATE) IS
1360 SELECT bill_date,
1361 SUM(principal) principal,
1362 SUM(interest) interest,
1363 0 princ_pay_down,
1364 payment_type
1365 FROM
1366 (
1367 SELECT sel.stream_element_date bill_date,
1368 0 principal,
1369 sel.amount interest,
1370 G_BILLED payment_type
1371 FROM okl_strm_elements sel,
1372 okl_streams stm,
1373 okl_strm_type_b sty
1374 WHERE stm.khr_id = p_chr_id
1375 AND sty.id = stm.sty_id
1376 AND sty.stream_type_purpose = 'VARIABLE_INTEREST'
1377 AND sel.stm_id = stm.id
1378 AND stm.say_code = 'CURR'
1379 AND stm.active_yn = 'Y'
1380 AND sel.date_billed IS NOT NULL
1381 UNION ALL
1382 SELECT sel.stream_element_date bill_date,
1383 sel.amount principal,
1384 0 interest,
1385 decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
1386 FROM okl_strm_elements sel,
1387 okl_streams stm,
1388 okl_strm_type_b sty
1389 WHERE stm.khr_id = p_chr_id
1390 AND sty.id = stm.sty_id
1391 AND sty.stream_type_purpose = 'PRINCIPAL_PAYMENT'
1392 AND sel.stm_id = stm.id
1393 AND stm.say_code = 'CURR'
1394 AND stm.active_yn = 'Y'
1395 UNION ALL
1396 SELECT sel.stream_element_date bill_date,
1397 0 principal,
1398 sel.amount interest,
1399 G_PROJECTED payment_type
1400 FROM okl_strm_elements sel,
1401 okl_streams stm,
1402 okl_strm_type_b sty
1403 WHERE stm.khr_id = p_chr_id
1404 AND sty.id = stm.sty_id
1405 AND sty.stream_type_purpose = 'INTEREST_PAYMENT'
1406 AND sel.stm_id = stm.id
1407 AND stm.say_code = 'CURR'
1408 AND stm.active_yn = 'Y'
1409 AND sel.date_billed IS NULL
1410 AND sel.stream_element_date > p_last_bill_date
1411 )
1412 GROUP BY bill_date,payment_type
1413 UNION ALL
1414 SELECT bill_date,
1415 0 principal,
1416 0 interest,
1417 SUM(princ_pay_down) princ_pay_down,
1418 payment_type
1419 FROM
1420 (
1421 SELECT sel.stream_element_date bill_date,
1422 0 principal,
1423 0 interest,
1424 sel.amount princ_pay_down,
1425 decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
1426 FROM okl_strm_elements sel,
1427 okl_streams stm,
1428 okl_strm_type_b sty
1429 WHERE stm.khr_id = p_chr_id
1430 AND sty.id = stm.sty_id
1431 AND sty.stream_type_purpose = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
1432 AND sel.stm_id = stm.id
1433 AND stm.say_code = 'CURR'
1434 AND stm.active_yn = 'Y'
1435 )
1436 GROUP BY bill_date,payment_type
1437 ORDER BY bill_date;
1438
1439 TYPE temp_tbl_type IS TABLE OF amort_sched_dtl_csr%ROWTYPE INDEX BY BINARY_INTEGER;
1440 l_temp_tbl temp_tbl_type;
1441
1442 l_principal_balance NUMBER;
1443 l_amort_sched_tbl amort_sched_tbl_type;
1444 l_counter NUMBER;
1445
1446 BEGIN
1447
1448 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1449 -- Call start_activity to create savepoint, check compatibility
1450 -- and initialize message list
1451 x_return_status := OKL_API.START_ACTIVITY (
1452 l_api_name
1453 ,p_init_msg_list
1454 ,'_PVT'
1455 ,x_return_status);
1456 -- Check if activity started successfully
1457 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1458 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1459 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1460 RAISE OKL_API.G_EXCEPTION_ERROR;
1461 END IF;
1462
1463 --Derive Principal Balance
1464 Okl_Execute_Formula_Pub.EXECUTE(p_api_version => p_api_version,
1465 p_init_msg_list => p_init_msg_list,
1466 x_return_status => x_return_status,
1467 x_msg_count => x_msg_count,
1468 x_msg_data => x_msg_data,
1469 p_formula_name => 'CONTRACT_FINANCED_AMOUNT',
1470 p_contract_id => p_chr_id,
1471 p_line_id => NULL,
1472 x_value => l_principal_balance
1473 );
1474 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1475 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1476 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1477 RAISE OKL_API.G_EXCEPTION_ERROR;
1478 END IF;
1479
1480 OPEN last_bill_date_csr(p_chr_id => p_chr_id);
1481 FETCH last_bill_date_csr INTO l_last_bill_date;
1482 CLOSE last_bill_date_csr;
1483
1484 IF l_last_bill_date IS NULL THEN
1485 OPEN chr_csr(p_chr_id => p_chr_id);
1486 FETCH chr_csr INTO l_last_bill_date;
1487 CLOSE chr_csr;
1488 END IF;
1489
1490 l_counter := 1;
1491 l_amort_sched_tbl(l_counter).principal_balance := l_principal_balance;
1492
1493 OPEN amort_sched_dtl_csr(p_chr_id => p_chr_id,
1494 p_last_bill_date => l_last_bill_date);
1495 LOOP
1496 FETCH amort_sched_dtl_csr BULK COLLECT INTO l_temp_tbl LIMIT G_BULK_SIZE;
1497 EXIT WHEN l_temp_tbl.COUNT = 0;
1498
1499 FOR i IN l_temp_tbl.FIRST..l_temp_tbl.LAST
1500 LOOP
1501 l_counter := l_counter + 1;
1502 l_amort_sched_tbl(l_counter).start_date := l_temp_tbl(i).bill_date;
1503 l_amort_sched_tbl(l_counter).principal := l_temp_tbl(i).principal + l_temp_tbl(i).princ_pay_down;
1504 l_amort_sched_tbl(l_counter).interest := l_temp_tbl(i).interest;
1505 l_amort_sched_tbl(l_counter).loan_payment := l_temp_tbl(i).principal + l_temp_tbl(i).princ_pay_down + l_temp_tbl(i).interest;
1506 l_amort_sched_tbl(l_counter).principal_balance := l_amort_sched_tbl(l_counter - 1).principal_balance - l_temp_tbl(i).principal - l_temp_tbl(i).princ_pay_down;
1507 l_amort_sched_tbl(l_counter).payment_type := l_temp_tbl(i).payment_type;
1508 END LOOP;
1509 END LOOP;
1510 CLOSE amort_sched_dtl_csr;
1511
1512 x_amort_sched_tbl := l_amort_sched_tbl;
1513
1514 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data);
1515
1516 EXCEPTION
1517
1518 ------------------------------------------------------------
1519 -- Exception handling
1520 ------------------------------------------------------------
1521
1522 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1523
1524 IF amort_sched_dtl_csr%ISOPEN THEN
1525 CLOSE amort_sched_dtl_csr;
1526 END IF;
1527
1528 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1529 p_api_name => l_api_name,
1530 p_pkg_name => G_PKG_NAME,
1531 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1532 x_msg_count => x_msg_count,
1533 x_msg_data => x_msg_data,
1534 p_api_type => '_PVT');
1535
1536 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1537
1538 IF amort_sched_dtl_csr%ISOPEN THEN
1539 CLOSE amort_sched_dtl_csr;
1540 END IF;
1541
1542 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1543 p_api_name => l_api_name,
1544 p_pkg_name => G_PKG_NAME,
1545 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1546 x_msg_count => x_msg_count,
1547 x_msg_data => x_msg_data,
1548 p_api_type => '_PVT');
1549
1550 WHEN OTHERS THEN
1551
1552 IF amort_sched_dtl_csr%ISOPEN THEN
1553 CLOSE amort_sched_dtl_csr;
1554 END IF;
1555
1556 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1557 p_api_name => l_api_name,
1558 p_pkg_name => G_PKG_NAME,
1559 p_exc_name => 'OTHERS',
1560 x_msg_count => x_msg_count,
1561 x_msg_data => x_msg_data,
1562 p_api_type => '_PVT');
1563
1564 END load_ln_float_eb_dtl;
1565
1566 -- Start of comments
1567 --
1568 -- API name : load_ln_float_eb_summ
1569 -- Pre-reqs : None
1570 -- Function : This procedure loads the Amortization Schedule - Summary report
1571 -- based on the billed and unbilled stream elements for the input contract,
1572 -- as of the date on which Amortization schedule is requested.
1573 -- This schedule applies to Loans with Interest Calculation Basis - FLOAT and
1574 -- Revenue Recognition - ESTIMATED_AND_BILLED
1575 --
1576 -- Parameters :
1577 -- IN : p_api_version - Standard input parameter
1578 -- p_init_msg_list - Standard input parameter
1579 -- p_chr_id - Contract ID
1580 -- Version : 1.0
1581 -- History : rpillay created.
1582 -- End of comments
1583
1584 PROCEDURE load_ln_float_eb_summ(
1585 p_api_version IN NUMBER,
1586 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1587 x_return_status OUT NOCOPY VARCHAR2,
1588 x_msg_count OUT NOCOPY NUMBER,
1589 x_msg_data OUT NOCOPY VARCHAR2,
1590 p_chr_id IN NUMBER,
1591 x_amort_sched_tbl OUT NOCOPY amort_sched_tbl_type) IS
1592
1593 l_api_name CONSTANT VARCHAR2(30) := 'LOAD_LN_FLOAT_EB_SUMM';
1594 l_api_version CONSTANT NUMBER := 1.0;
1595
1596 CURSOR last_bill_date_csr(p_chr_id IN NUMBER) IS
1597 SELECT MAX(sel.stream_element_date)
1598 FROM okl_streams stm,
1599 okl_strm_elements sel,
1600 okl_strm_type_b sty
1601 WHERE stm.khr_id = p_chr_id
1602 AND sty.id = stm.sty_id
1603 AND sty.stream_type_purpose = 'PRINCIPAL_PAYMENT'
1604 AND sel.stm_id = stm.id
1605 AND stm.say_code = 'CURR'
1606 AND stm.active_yn = 'Y'
1607 AND sel.date_billed IS NOT NULL;
1608
1609 l_last_bill_date OKL_STRM_ELEMENTS.stream_element_date%TYPE;
1610
1611 CURSOR chr_csr(p_chr_id IN NUMBER) IS
1612 SELECT chr.start_date
1613 FROM okc_k_headers_b chr
1614 WHERE chr.id = p_chr_id;
1615
1616 CURSOR amort_sched_dtl_csr(p_chr_id IN NUMBER,
1617 p_last_bill_date IN DATE) IS
1618 SELECT bill_date,
1619 SUM(principal) principal,
1620 SUM(interest) interest
1621 FROM
1622 (
1623 SELECT sel.stream_element_date bill_date,
1624 0 principal,
1625 sel.amount interest
1626 FROM okl_strm_elements sel,
1627 okl_streams stm,
1628 okl_strm_type_b sty
1629 WHERE stm.khr_id = p_chr_id
1630 AND sty.id = stm.sty_id
1631 AND sty.stream_type_purpose = 'VARIABLE_INTEREST'
1632 AND sel.stm_id = stm.id
1633 AND stm.say_code = 'CURR'
1634 AND stm.active_yn = 'Y'
1635 AND sel.date_billed IS NOT NULL
1636 UNION ALL
1637 SELECT sel.stream_element_date bill_date,
1638 0 principal,
1639 sel.amount interest
1640 FROM okl_strm_elements sel,
1641 okl_streams stm,
1642 okl_strm_type_b sty
1643 WHERE stm.khr_id = p_chr_id
1644 AND sty.id = stm.sty_id
1645 AND sty.stream_type_purpose = 'INTEREST_PAYMENT'
1646 AND sel.stm_id = stm.id
1647 AND stm.say_code = 'CURR'
1648 AND stm.active_yn = 'Y'
1649 AND sel.date_billed IS NULL
1650 AND sel.stream_element_date > p_last_bill_date
1651 UNION ALL
1652 SELECT sel.stream_element_date bill_date,
1653 sel.amount principal,
1654 0 interest
1655 FROM okl_strm_elements sel,
1656 okl_streams stm,
1657 okl_strm_type_b sty
1658 WHERE stm.khr_id = p_chr_id
1659 AND sty.id = stm.sty_id
1660 AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT','UNSCHEDULED_PRINCIPAL_PAYMENT')
1661 AND sel.stm_id = stm.id
1662 AND stm.say_code = 'CURR'
1663 AND stm.active_yn = 'Y'
1664 )
1665 GROUP BY bill_date
1666 ORDER BY bill_date;
1667
1668 TYPE temp_tbl_type IS TABLE OF amort_sched_dtl_csr%ROWTYPE INDEX BY BINARY_INTEGER;
1669 l_temp_tbl temp_tbl_type;
1670 l_temp1_tbl temp_tbl_type;
1671 l_temp_counter NUMBER;
1672
1673 l_principal_balance NUMBER;
1674 l_amort_sched_tbl amort_sched_tbl_type;
1675 l_counter NUMBER;
1676 i NUMBER;
1677 l_max_bill_date DATE;
1678
1679 l_periods_tbl periods_tbl_type;
1680 l_arrears_yn VARCHAR2(1);
1681 BEGIN
1682
1683 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1684 -- Call start_activity to create savepoint, check compatibility
1685 -- and initialize message list
1686 x_return_status := OKL_API.START_ACTIVITY (
1687 l_api_name
1688 ,p_init_msg_list
1689 ,'_PVT'
1690 ,x_return_status);
1691 -- Check if activity started successfully
1692 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1693 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1694 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1695 RAISE OKL_API.G_EXCEPTION_ERROR;
1696 END IF;
1697
1698 --Derive Principal Balance
1699 Okl_Execute_Formula_Pub.EXECUTE(p_api_version => p_api_version,
1700 p_init_msg_list => p_init_msg_list,
1701 x_return_status => x_return_status,
1702 x_msg_count => x_msg_count,
1703 x_msg_data => x_msg_data,
1704 p_formula_name => 'CONTRACT_FINANCED_AMOUNT',
1705 p_contract_id => p_chr_id,
1706 p_line_id => NULL,
1707 x_value => l_principal_balance
1708 );
1709 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1710 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1711 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1712 RAISE OKL_API.G_EXCEPTION_ERROR;
1713 END IF;
1714
1715 OPEN last_bill_date_csr(p_chr_id => p_chr_id);
1716 FETCH last_bill_date_csr INTO l_last_bill_date;
1717 CLOSE last_bill_date_csr;
1718
1719 IF l_last_bill_date IS NULL THEN
1720 OPEN chr_csr(p_chr_id => p_chr_id);
1721 FETCH chr_csr INTO l_last_bill_date;
1722 CLOSE chr_csr;
1723 END IF;
1724
1725 l_temp_counter := 0;
1726 OPEN amort_sched_dtl_csr(p_chr_id => p_chr_id,
1727 p_last_bill_date => l_last_bill_date);
1728 LOOP
1729 FETCH amort_sched_dtl_csr BULK COLLECT INTO l_temp_tbl LIMIT G_BULK_SIZE;
1730 EXIT WHEN l_temp_tbl.COUNT = 0;
1731
1732 FOR i IN l_temp_tbl.FIRST..l_temp_tbl.LAST
1733 LOOP
1734 l_temp_counter := l_temp_counter + 1;
1735 l_temp1_tbl(l_temp_counter).bill_date := l_temp_tbl(i).bill_date;
1736 l_temp1_tbl(l_temp_counter).principal := l_temp_tbl(i).principal;
1737 l_temp1_tbl(l_temp_counter).interest := l_temp_tbl(i).interest;
1738 END LOOP;
1739 END LOOP;
1740 CLOSE amort_sched_dtl_csr;
1741
1742 --Derive Payment Schedule Periods
1743 get_pymt_sched_periods(p_api_version => p_api_version,
1744 p_init_msg_list => p_init_msg_list,
1745 x_return_status => x_return_status,
1746 x_msg_count => x_msg_count,
1747 x_msg_data => x_msg_data,
1748 p_chr_id => p_chr_id,
1749 x_arrears_yn => l_arrears_yn,
1750 x_periods_tbl => l_periods_tbl
1751 );
1752
1753 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1754 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1755 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1756 RAISE OKL_API.G_EXCEPTION_ERROR;
1757 END IF;
1758
1759 l_counter := 1;
1760 l_amort_sched_tbl(l_counter).principal_balance := l_principal_balance;
1761
1762 IF (l_temp1_tbl.COUNT > 0 AND l_periods_tbl.COUNT > 0) THEN
1763
1764 i := l_temp1_tbl.FIRST;
1765 l_max_bill_date := l_temp1_tbl(l_temp1_tbl.LAST).bill_date;
1766
1767 FOR j IN l_periods_tbl.FIRST..l_periods_tbl.LAST
1768 LOOP
1769
1770 EXIT WHEN l_periods_tbl(j).start_date > l_max_bill_date;
1771
1772 l_counter := l_counter + 1;
1773 l_amort_sched_tbl(l_counter).start_date := l_periods_tbl(j).start_date;
1774 l_amort_sched_tbl(l_counter).end_date := l_periods_tbl(j).end_date;
1775 l_amort_sched_tbl(l_counter).principal := 0;
1776 l_amort_sched_tbl(l_counter).interest := 0;
1777 l_amort_sched_tbl(l_counter).loan_payment := 0;
1778 l_amort_sched_tbl(l_counter).principal_balance := l_amort_sched_tbl(l_counter - 1).principal_balance;
1779
1780 WHILE ((i <= l_temp1_tbl.COUNT) AND
1781 (l_temp1_tbl(i).bill_date BETWEEN l_periods_tbl(j).start_date AND l_periods_tbl(j).end_date))
1782 LOOP
1783 l_amort_sched_tbl(l_counter).principal := l_amort_sched_tbl(l_counter).principal + l_temp1_tbl(i).principal;
1784 l_amort_sched_tbl(l_counter).interest := l_amort_sched_tbl(l_counter).interest + l_temp1_tbl(i).interest;
1785 l_amort_sched_tbl(l_counter).loan_payment := l_amort_sched_tbl(l_counter).loan_payment + l_temp1_tbl(i).principal + l_temp1_tbl(i).interest;
1786 l_amort_sched_tbl(l_counter).principal_balance := l_amort_sched_tbl(l_counter).principal_balance - l_temp1_tbl(i).principal;
1787 i := i + 1;
1788 END LOOP;
1789
1790 END LOOP;
1791
1792 -- Handle payments that fall outside the payment schedule
1793 IF (i <= l_temp1_tbl.COUNT) THEN
1794
1795 l_counter := l_counter + 1;
1796 l_amort_sched_tbl(l_counter).start_date := l_periods_tbl(l_periods_tbl.LAST).end_date + 1;
1797 l_amort_sched_tbl(l_counter).principal := 0;
1798 l_amort_sched_tbl(l_counter).interest := 0;
1799 l_amort_sched_tbl(l_counter).loan_payment := 0;
1800 l_amort_sched_tbl(l_counter).principal_balance := l_amort_sched_tbl(l_counter - 1).principal_balance;
1801
1802 WHILE (i <= l_temp1_tbl.COUNT)
1803 LOOP
1804 l_amort_sched_tbl(l_counter).end_date := l_temp1_tbl(i).bill_date;
1805 l_amort_sched_tbl(l_counter).principal := l_amort_sched_tbl(l_counter).principal + l_temp1_tbl(i).principal;
1806 l_amort_sched_tbl(l_counter).interest := l_amort_sched_tbl(l_counter).interest + l_temp1_tbl(i).interest;
1807 l_amort_sched_tbl(l_counter).loan_payment := l_amort_sched_tbl(l_counter).loan_payment + l_temp1_tbl(i).principal + l_temp1_tbl(i).interest;
1808 l_amort_sched_tbl(l_counter).principal_balance := l_amort_sched_tbl(l_counter).principal_balance - l_temp1_tbl(i).principal;
1809 i := i + 1;
1810 END LOOP;
1811 END IF;
1812
1813 END IF;
1814
1815 x_amort_sched_tbl := l_amort_sched_tbl;
1816
1817 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data);
1818
1819 EXCEPTION
1820
1821 ------------------------------------------------------------
1822 -- Exception handling
1823 ------------------------------------------------------------
1824
1825 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1826
1827 IF amort_sched_dtl_csr%ISOPEN THEN
1828 CLOSE amort_sched_dtl_csr;
1829 END IF;
1830
1831 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1832 p_api_name => l_api_name,
1833 p_pkg_name => G_PKG_NAME,
1834 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1835 x_msg_count => x_msg_count,
1836 x_msg_data => x_msg_data,
1837 p_api_type => '_PVT');
1838
1839 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1840
1841 IF amort_sched_dtl_csr%ISOPEN THEN
1842 CLOSE amort_sched_dtl_csr;
1843 END IF;
1844
1845 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1846 p_api_name => l_api_name,
1847 p_pkg_name => G_PKG_NAME,
1848 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1849 x_msg_count => x_msg_count,
1850 x_msg_data => x_msg_data,
1851 p_api_type => '_PVT');
1852
1853 WHEN OTHERS THEN
1854
1855 IF amort_sched_dtl_csr%ISOPEN THEN
1856 CLOSE amort_sched_dtl_csr;
1857 END IF;
1858
1859 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1860 p_api_name => l_api_name,
1861 p_pkg_name => G_PKG_NAME,
1862 p_exc_name => 'OTHERS',
1863 x_msg_count => x_msg_count,
1864 x_msg_data => x_msg_data,
1865 p_api_type => '_PVT');
1866
1867 END load_ln_float_eb_summ;
1868
1869 -- Start of comments
1870 --
1871 -- API name : load_ln_cc_strm_dtl
1872 -- Pre-reqs : None
1873 -- Function : This procedure loads the Amortization Schedule - Detail report
1874 -- based on the billed and unbilled stream elements for the input contract,
1875 -- as of the date on which Amortization schedule is requested.
1876 -- This schedule applies to Loans with Interest Calculation Basis -
1877 -- CATCHUP/CLEANUP and Revenue Recognition - STREAMS
1878 --
1879 -- Parameters :
1880 -- IN : p_api_version - Standard input parameter
1881 -- p_init_msg_list - Standard input parameter
1882 -- p_chr_id - Contract ID
1883 -- Version : 1.0
1884 -- History : rpillay created.
1885 -- rpillay 20-Feb-08 Bug# 6831074 :Changed payment type G_PPD to G_BILLED/G_PROJECTED
1886 --
1887 -- End of comments
1888
1889 PROCEDURE load_ln_cc_strm_dtl(
1890 p_api_version IN NUMBER,
1891 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1892 x_return_status OUT NOCOPY VARCHAR2,
1893 x_msg_count OUT NOCOPY NUMBER,
1894 x_msg_data OUT NOCOPY VARCHAR2,
1895 p_chr_id IN NUMBER,
1896 x_amort_sched_tbl OUT NOCOPY amort_sched_tbl_type) IS
1897
1898 l_api_name CONSTANT VARCHAR2(30) := 'LOAD_LN_CC_STRM_DTL';
1899 l_api_version CONSTANT NUMBER := 1.0;
1900
1901 CURSOR amort_sched_dtl_csr(p_chr_id IN NUMBER) IS
1902 SELECT bill_date,
1903 SUM(principal) principal,
1904 SUM(interest) interest,
1905 0 princ_pay_down,
1906 payment_type
1907 FROM
1908 (
1909 SELECT sel.stream_element_date bill_date,
1910 0 principal,
1911 sel.amount interest,
1912 decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
1913 FROM okl_strm_elements sel,
1914 okl_streams stm,
1915 okl_strm_type_b sty
1916 WHERE stm.khr_id = p_chr_id
1917 AND sty.id = stm.sty_id
1918 AND sty.stream_type_purpose = 'INTEREST_PAYMENT'
1919 AND sel.stm_id = stm.id
1920 AND stm.say_code = 'CURR'
1921 AND stm.active_yn = 'Y'
1922 UNION ALL
1923 SELECT sel.stream_element_date bill_date,
1924 sel.amount principal,
1925 0 interest,
1926 decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
1927 FROM okl_strm_elements sel,
1928 okl_streams stm,
1929 okl_strm_type_b sty
1930 WHERE stm.khr_id = p_chr_id
1931 AND sty.id = stm.sty_id
1932 AND sty.stream_type_purpose = 'PRINCIPAL_PAYMENT'
1933 AND sel.stm_id = stm.id
1934 AND stm.say_code = 'CURR'
1935 AND stm.active_yn = 'Y'
1936 )
1937 GROUP BY bill_date,payment_type
1938 UNION ALL
1939 SELECT sel.stream_element_date bill_date,
1940 SUM(sel.amount) principal,
1941 0 interest,
1942 0 princ_pay_down,
1943 G_BILLED payment_type
1944 FROM okl_strm_elements sel,
1945 okl_streams stm,
1946 okl_strm_type_b sty
1947 WHERE stm.khr_id = p_chr_id
1948 AND sty.id = stm.sty_id
1949 AND sty.stream_type_purpose = 'PRINCIPAL_CATCHUP'
1950 AND sel.stm_id = stm.id
1951 AND stm.say_code = 'CURR'
1952 AND stm.active_yn = 'Y'
1953 AND sel.date_billed IS NOT NULL
1954 GROUP BY sel.stream_element_date
1955 UNION ALL
1956 SELECT sel.stream_element_date bill_date,
1957 0 principal,
1958 SUM(sel.amount) interest,
1959 0 princ_pay_down,
1960 G_BILLED payment_type
1961 FROM okl_strm_elements sel,
1962 okl_streams stm,
1963 okl_strm_type_b sty
1964 WHERE stm.khr_id = p_chr_id
1965 AND sty.id = stm.sty_id
1966 AND sty.stream_type_purpose = 'INTEREST_CATCHUP'
1967 AND sel.stm_id = stm.id
1968 AND stm.say_code = 'CURR'
1969 AND stm.active_yn = 'Y'
1970 AND sel.date_billed IS NOT NULL
1971 GROUP BY sel.stream_element_date
1972 UNION ALL
1973 SELECT bill_date,
1974 0 principal,
1975 0 interest,
1976 SUM(princ_pay_down) princ_pay_down,
1977 payment_type
1978 FROM
1979 (
1980 SELECT sel.stream_element_date bill_date,
1981 0 principal,
1982 0 interest,
1983 sel.amount princ_pay_down,
1984 decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
1985 FROM okl_strm_elements sel,
1986 okl_streams stm,
1987 okl_strm_type_b sty
1988 WHERE stm.khr_id = p_chr_id
1989 AND sty.id = stm.sty_id
1990 AND sty.stream_type_purpose = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
1991 AND sel.stm_id = stm.id
1992 AND stm.say_code = 'CURR'
1993 AND stm.active_yn = 'Y'
1994 )
1995 GROUP BY bill_date,payment_type
1996 ORDER BY bill_date;
1997
1998 TYPE temp_tbl_type IS TABLE OF amort_sched_dtl_csr%ROWTYPE INDEX BY BINARY_INTEGER;
1999 l_temp_tbl temp_tbl_type;
2000
2001 l_principal_balance NUMBER;
2002 l_amort_sched_tbl amort_sched_tbl_type;
2003 l_counter NUMBER;
2004
2005 l_temp_counter NUMBER;
2006 l_temp1_tbl temp_tbl_type;
2007 l_max_bill_counter NUMBER;
2008
2009 BEGIN
2010
2011 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2012 -- Call start_activity to create savepoint, check compatibility
2013 -- and initialize message list
2014 x_return_status := OKL_API.START_ACTIVITY (
2015 l_api_name
2016 ,p_init_msg_list
2017 ,'_PVT'
2018 ,x_return_status);
2019 -- Check if activity started successfully
2020 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2021 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2022 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2023 RAISE OKL_API.G_EXCEPTION_ERROR;
2024 END IF;
2025
2026 --Derive Principal Balance
2027 Okl_Execute_Formula_Pub.EXECUTE(p_api_version => p_api_version,
2028 p_init_msg_list => p_init_msg_list,
2029 x_return_status => x_return_status,
2030 x_msg_count => x_msg_count,
2031 x_msg_data => x_msg_data,
2032 p_formula_name => 'CONTRACT_FINANCED_AMOUNT',
2033 p_contract_id => p_chr_id,
2034 p_line_id => NULL,
2035 x_value => l_principal_balance
2036 );
2037 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2038 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2039 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2040 RAISE OKL_API.G_EXCEPTION_ERROR;
2041 END IF;
2042
2043 l_max_bill_counter := 0;
2044 l_temp_counter := 0;
2045 OPEN amort_sched_dtl_csr(p_chr_id => p_chr_id);
2046 LOOP
2047 FETCH amort_sched_dtl_csr BULK COLLECT INTO l_temp_tbl LIMIT G_BULK_SIZE;
2048 EXIT WHEN l_temp_tbl.COUNT = 0;
2049
2050 FOR i IN l_temp_tbl.FIRST..l_temp_tbl.LAST
2051 LOOP
2052 l_temp_counter := l_temp_counter + 1;
2053 l_temp1_tbl(l_temp_counter).bill_date := l_temp_tbl(i).bill_date;
2054 l_temp1_tbl(l_temp_counter).principal := l_temp_tbl(i).principal;
2055 l_temp1_tbl(l_temp_counter).interest := l_temp_tbl(i).interest;
2056 l_temp1_tbl(l_temp_counter).princ_pay_down := l_temp_tbl(i).princ_pay_down;
2057 l_temp1_tbl(l_temp_counter).payment_type := l_temp_tbl(i).payment_type;
2058
2059 IF (l_temp1_tbl(l_temp_counter).payment_type = G_BILLED) THEN
2060 l_max_bill_counter := l_temp_counter;
2061 END IF;
2062 END LOOP;
2063 END LOOP;
2064 CLOSE amort_sched_dtl_csr;
2065
2066 l_counter := 1;
2067 l_amort_sched_tbl(l_counter).principal_balance := l_principal_balance;
2068
2069 IF (l_temp1_tbl.COUNT > 0) THEN
2070 FOR i IN l_temp1_tbl.FIRST..l_temp1_tbl.LAST
2071 LOOP
2072 IF (l_temp1_tbl(i).payment_type = G_BILLED OR l_amort_sched_tbl(l_counter).principal_balance > 0) THEN
2073 l_counter := l_counter + 1;
2074 l_amort_sched_tbl(l_counter).start_date := l_temp1_tbl(i).bill_date;
2075 l_amort_sched_tbl(l_counter).principal := l_temp1_tbl(i).principal + l_temp1_tbl(i).princ_pay_down;
2076 l_amort_sched_tbl(l_counter).interest := l_temp1_tbl(i).interest;
2077 l_amort_sched_tbl(l_counter).loan_payment := l_temp1_tbl(i).principal + l_temp1_tbl(i).princ_pay_down + l_temp1_tbl(i).interest;
2078 l_amort_sched_tbl(l_counter).principal_balance := l_amort_sched_tbl(l_counter - 1).principal_balance - l_temp1_tbl(i).principal - l_temp1_tbl(i).princ_pay_down;
2079 l_amort_sched_tbl(l_counter).payment_type := l_temp1_tbl(i).payment_type;
2080
2081 IF (l_amort_sched_tbl(l_counter).principal_balance < 0 AND l_amort_sched_tbl(l_counter).payment_type = G_PROJECTED) THEN
2082 l_amort_sched_tbl(l_counter).principal := l_amort_sched_tbl(l_counter).principal + l_amort_sched_tbl(l_counter).principal_balance;
2083 l_amort_sched_tbl(l_counter).loan_payment := l_amort_sched_tbl(l_counter).principal + l_amort_sched_tbl(l_counter).interest;
2084 l_amort_sched_tbl(l_counter).principal_balance := 0;
2085
2086 EXIT WHEN (i > l_max_bill_counter);
2087 END IF;
2088 END IF;
2089 END LOOP;
2090 END IF;
2091
2092 x_amort_sched_tbl := l_amort_sched_tbl;
2093
2094 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data);
2095
2096 EXCEPTION
2097
2098 ------------------------------------------------------------
2099 -- Exception handling
2100 ------------------------------------------------------------
2101
2102 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2103
2104 IF amort_sched_dtl_csr%ISOPEN THEN
2105 CLOSE amort_sched_dtl_csr;
2106 END IF;
2107
2108 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2109 p_api_name => l_api_name,
2110 p_pkg_name => G_PKG_NAME,
2111 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2112 x_msg_count => x_msg_count,
2113 x_msg_data => x_msg_data,
2114 p_api_type => '_PVT');
2115
2116 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2117
2118 IF amort_sched_dtl_csr%ISOPEN THEN
2119 CLOSE amort_sched_dtl_csr;
2120 END IF;
2121
2122 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2123 p_api_name => l_api_name,
2124 p_pkg_name => G_PKG_NAME,
2125 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2126 x_msg_count => x_msg_count,
2127 x_msg_data => x_msg_data,
2128 p_api_type => '_PVT');
2129
2130 WHEN OTHERS THEN
2131
2132 IF amort_sched_dtl_csr%ISOPEN THEN
2133 CLOSE amort_sched_dtl_csr;
2134 END IF;
2135
2136 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2137 p_api_name => l_api_name,
2138 p_pkg_name => G_PKG_NAME,
2139 p_exc_name => 'OTHERS',
2140 x_msg_count => x_msg_count,
2141 x_msg_data => x_msg_data,
2142 p_api_type => '_PVT');
2143
2144 END load_ln_cc_strm_dtl;
2145
2146 -- Start of comments
2147 --
2148 -- API name : load_ln_cc_strm_summ
2149 -- Pre-reqs : None
2150 -- Function : This procedure loads the Amortization Schedule - Summary report
2151 -- based on the billed and unbilled stream elements for the input contract,
2152 -- as of the date on which Amortization schedule is requested.
2153 -- This schedule applies to Loans with Interest Calculation Basis -
2154 -- CATCHUP/CLEANUP and Revenue Recognition - STREAMS
2155 --
2156 -- Parameters :
2157 -- IN : p_api_version - Standard input parameter
2158 -- p_init_msg_list - Standard input parameter
2159 -- p_chr_id - Contract ID
2160 -- Version : 1.0
2161 -- History : rpillay created.
2162 -- End of comments
2163
2164 PROCEDURE load_ln_cc_strm_summ(
2165 p_api_version IN NUMBER,
2166 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2167 x_return_status OUT NOCOPY VARCHAR2,
2168 x_msg_count OUT NOCOPY NUMBER,
2169 x_msg_data OUT NOCOPY VARCHAR2,
2170 p_chr_id IN NUMBER,
2171 x_amort_sched_tbl OUT NOCOPY amort_sched_tbl_type) IS
2172
2173 l_api_name CONSTANT VARCHAR2(30) := 'LOAD_LN_CC_STRM_SUMM';
2174 l_api_version CONSTANT NUMBER := 1.0;
2175
2176 CURSOR amort_sched_dtl_csr(p_chr_id IN NUMBER) IS
2177 SELECT bill_date,
2178 SUM(principal) principal,
2179 SUM(interest) interest,
2180 payment_type
2181 FROM
2182 (
2183 SELECT sel.stream_element_date bill_date,
2184 0 principal,
2185 sel.amount interest,
2186 DECODE(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
2187 FROM okl_strm_elements sel,
2188 okl_streams stm,
2189 okl_strm_type_b sty
2190 WHERE stm.khr_id = p_chr_id
2191 AND sty.id = stm.sty_id
2192 AND sty.stream_type_purpose IN ('INTEREST_PAYMENT','INTEREST_CATCHUP')
2193 AND sel.stm_id = stm.id
2194 AND stm.say_code = 'CURR'
2195 AND stm.active_yn = 'Y'
2196 UNION ALL
2197 SELECT sel.stream_element_date bill_date,
2198 sel.amount principal,
2199 0 interest,
2200 DECODE(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
2201 FROM okl_strm_elements sel,
2202 okl_streams stm,
2203 okl_strm_type_b sty
2204 WHERE stm.khr_id = p_chr_id
2205 AND sty.id = stm.sty_id
2206 AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT','PRINCIPAL_CATCHUP','UNSCHEDULED_PRINCIPAL_PAYMENT')
2207 AND sel.stm_id = stm.id
2208 AND stm.say_code = 'CURR'
2209 AND stm.active_yn = 'Y'
2210 )
2211 GROUP BY bill_date,payment_type
2212 ORDER BY bill_date;
2213
2214 TYPE temp_tbl_type IS TABLE OF amort_sched_dtl_csr%ROWTYPE INDEX BY BINARY_INTEGER;
2215 l_temp_tbl temp_tbl_type;
2216 l_temp1_tbl temp_tbl_type;
2217 l_temp_counter NUMBER;
2218
2219 l_principal_balance NUMBER;
2220 l_amort_sched_tbl amort_sched_tbl_type;
2221 l_counter NUMBER;
2222 i NUMBER;
2223 l_max_bill_date DATE;
2224
2225 l_periods_tbl periods_tbl_type;
2226 l_arrears_yn VARCHAR2(1);
2227 l_max_bill_counter NUMBER;
2228
2229 BEGIN
2230
2231 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2232 -- Call start_activity to create savepoint, check compatibility
2233 -- and initialize message list
2234 x_return_status := OKL_API.START_ACTIVITY (
2235 l_api_name
2236 ,p_init_msg_list
2237 ,'_PVT'
2238 ,x_return_status);
2239 -- Check if activity started successfully
2240 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2241 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2242 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2243 RAISE OKL_API.G_EXCEPTION_ERROR;
2244 END IF;
2245
2246 --Derive Principal Balance
2247 Okl_Execute_Formula_Pub.EXECUTE(p_api_version => p_api_version,
2248 p_init_msg_list => p_init_msg_list,
2249 x_return_status => x_return_status,
2250 x_msg_count => x_msg_count,
2251 x_msg_data => x_msg_data,
2252 p_formula_name => 'CONTRACT_FINANCED_AMOUNT',
2253 p_contract_id => p_chr_id,
2254 p_line_id => NULL,
2255 x_value => l_principal_balance
2256 );
2257 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2258 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2259 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2260 RAISE OKL_API.G_EXCEPTION_ERROR;
2261 END IF;
2262
2263 l_temp_counter := 0;
2264 OPEN amort_sched_dtl_csr(p_chr_id => p_chr_id);
2265 LOOP
2266 FETCH amort_sched_dtl_csr BULK COLLECT INTO l_temp_tbl LIMIT G_BULK_SIZE;
2267 EXIT WHEN l_temp_tbl.COUNT = 0;
2268
2269 FOR i IN l_temp_tbl.FIRST..l_temp_tbl.LAST
2270 LOOP
2271 l_temp_counter := l_temp_counter + 1;
2272 l_temp1_tbl(l_temp_counter).bill_date := l_temp_tbl(i).bill_date;
2273 l_temp1_tbl(l_temp_counter).principal := l_temp_tbl(i).principal;
2274 l_temp1_tbl(l_temp_counter).interest := l_temp_tbl(i).interest;
2275 l_temp1_tbl(l_temp_counter).payment_type := l_temp_tbl(i).payment_type;
2276
2277 IF (l_temp1_tbl(l_temp_counter).payment_type = G_BILLED) THEN
2278 l_max_bill_counter := l_temp_counter;
2279 END IF;
2280
2281 END LOOP;
2282 END LOOP;
2283 CLOSE amort_sched_dtl_csr;
2284
2285 --Derive Payment Schedule Periods
2286 get_pymt_sched_periods(p_api_version => p_api_version,
2287 p_init_msg_list => p_init_msg_list,
2288 x_return_status => x_return_status,
2289 x_msg_count => x_msg_count,
2290 x_msg_data => x_msg_data,
2291 p_chr_id => p_chr_id,
2292 x_arrears_yn => l_arrears_yn,
2293 x_periods_tbl => l_periods_tbl
2294 );
2295
2296 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2297 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2298 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2299 RAISE OKL_API.G_EXCEPTION_ERROR;
2300 END IF;
2301
2302 l_counter := 1;
2303 l_amort_sched_tbl(l_counter).principal_balance := l_principal_balance;
2304
2305 IF (l_temp1_tbl.COUNT > 0 AND l_periods_tbl.COUNT > 0) THEN
2306
2307 i := l_temp1_tbl.FIRST;
2308 l_max_bill_date := l_temp1_tbl(l_temp1_tbl.LAST).bill_date;
2309
2310 FOR j IN l_periods_tbl.FIRST..l_periods_tbl.LAST
2311 LOOP
2312
2313 EXIT WHEN (l_periods_tbl(j).start_date > l_max_bill_date) OR
2314 (l_amort_sched_tbl(l_counter).principal_balance <= 0 AND i > l_max_bill_counter);
2315
2316 l_counter := l_counter + 1;
2317 l_amort_sched_tbl(l_counter).start_date := l_periods_tbl(j).start_date;
2318 l_amort_sched_tbl(l_counter).end_date := l_periods_tbl(j).end_date;
2319 l_amort_sched_tbl(l_counter).principal := 0;
2320 l_amort_sched_tbl(l_counter).interest := 0;
2321 l_amort_sched_tbl(l_counter).loan_payment := 0;
2322 l_amort_sched_tbl(l_counter).principal_balance := l_amort_sched_tbl(l_counter - 1).principal_balance;
2323
2324 WHILE ((i <= l_temp1_tbl.COUNT) AND
2325 (l_temp1_tbl(i).bill_date BETWEEN l_periods_tbl(j).start_date AND l_periods_tbl(j).end_date))
2326 LOOP
2327
2328 IF (l_temp1_tbl(i).payment_type = G_BILLED OR l_amort_sched_tbl(l_counter).principal_balance > 0) THEN
2329
2330 l_amort_sched_tbl(l_counter).principal := l_amort_sched_tbl(l_counter).principal + l_temp1_tbl(i).principal;
2331 l_amort_sched_tbl(l_counter).interest := l_amort_sched_tbl(l_counter).interest + l_temp1_tbl(i).interest;
2332 l_amort_sched_tbl(l_counter).loan_payment := l_amort_sched_tbl(l_counter).loan_payment + l_temp1_tbl(i).principal + l_temp1_tbl(i).interest;
2333 l_amort_sched_tbl(l_counter).principal_balance := l_amort_sched_tbl(l_counter).principal_balance - l_temp1_tbl(i).principal;
2334
2335 IF (l_amort_sched_tbl(l_counter).principal_balance < 0 AND l_temp1_tbl(i).payment_type = G_PROJECTED) THEN
2336 l_amort_sched_tbl(l_counter).principal := l_amort_sched_tbl(l_counter).principal + l_amort_sched_tbl(l_counter).principal_balance;
2337 l_amort_sched_tbl(l_counter).loan_payment := l_amort_sched_tbl(l_counter).principal + l_amort_sched_tbl(l_counter).interest;
2338 l_amort_sched_tbl(l_counter).principal_balance := 0;
2339 END IF;
2340 END IF;
2341 i := i + 1;
2342 END LOOP;
2343
2344 END LOOP;
2345
2346 -- Handle payments that fall outside the payment schedule
2347 IF (i <= l_temp1_tbl.COUNT) AND (l_temp1_tbl(i).bill_date > l_periods_tbl(l_periods_tbl.LAST).end_date) THEN
2348
2349 IF (l_amort_sched_tbl(l_counter).principal_balance <= 0 AND i > l_max_bill_counter) THEN
2350 NULL;
2351 ELSE
2352
2353 l_counter := l_counter + 1;
2354 l_amort_sched_tbl(l_counter).start_date := l_periods_tbl(l_periods_tbl.LAST).end_date + 1;
2355 l_amort_sched_tbl(l_counter).principal := 0;
2356 l_amort_sched_tbl(l_counter).interest := 0;
2357 l_amort_sched_tbl(l_counter).loan_payment := 0;
2358 l_amort_sched_tbl(l_counter).principal_balance := l_amort_sched_tbl(l_counter - 1).principal_balance;
2359
2360 WHILE (i <= l_temp1_tbl.COUNT)
2361 LOOP
2362 IF (l_temp1_tbl(i).payment_type = G_BILLED OR l_amort_sched_tbl(l_counter).principal_balance > 0) THEN
2363 l_amort_sched_tbl(l_counter).end_date := l_temp1_tbl(i).bill_date;
2364 l_amort_sched_tbl(l_counter).principal := l_amort_sched_tbl(l_counter).principal + l_temp1_tbl(i).principal;
2365 l_amort_sched_tbl(l_counter).interest := l_amort_sched_tbl(l_counter).interest + l_temp1_tbl(i).interest;
2366 l_amort_sched_tbl(l_counter).loan_payment := l_amort_sched_tbl(l_counter).loan_payment + l_temp1_tbl(i).principal + l_temp1_tbl(i).interest;
2367 l_amort_sched_tbl(l_counter).principal_balance := l_amort_sched_tbl(l_counter).principal_balance - l_temp1_tbl(i).principal;
2368
2369 IF (l_amort_sched_tbl(l_counter).principal_balance < 0 AND l_temp1_tbl(i).payment_type = G_PROJECTED) THEN
2370 l_amort_sched_tbl(l_counter).principal := l_amort_sched_tbl(l_counter).principal + l_amort_sched_tbl(l_counter).principal_balance;
2371 l_amort_sched_tbl(l_counter).loan_payment := l_amort_sched_tbl(l_counter).principal + l_amort_sched_tbl(l_counter).interest;
2372 l_amort_sched_tbl(l_counter).principal_balance := 0;
2373 END IF;
2374 END IF;
2375 i := i + 1;
2376 END LOOP;
2377
2378 END IF;
2379 END IF;
2380
2381 END IF;
2382
2383 x_amort_sched_tbl := l_amort_sched_tbl;
2384
2385 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data);
2386
2387 EXCEPTION
2388
2389 ------------------------------------------------------------
2390 -- Exception handling
2391 ------------------------------------------------------------
2392
2393 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2394
2395 IF amort_sched_dtl_csr%ISOPEN THEN
2396 CLOSE amort_sched_dtl_csr;
2397 END IF;
2398
2399 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2400 p_api_name => l_api_name,
2401 p_pkg_name => G_PKG_NAME,
2402 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2403 x_msg_count => x_msg_count,
2404 x_msg_data => x_msg_data,
2405 p_api_type => '_PVT');
2406
2407 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2408
2409 IF amort_sched_dtl_csr%ISOPEN THEN
2410 CLOSE amort_sched_dtl_csr;
2411 END IF;
2412
2413 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2414 p_api_name => l_api_name,
2415 p_pkg_name => G_PKG_NAME,
2416 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2417 x_msg_count => x_msg_count,
2418 x_msg_data => x_msg_data,
2419 p_api_type => '_PVT');
2420
2421 WHEN OTHERS THEN
2422
2423 IF amort_sched_dtl_csr%ISOPEN THEN
2424 CLOSE amort_sched_dtl_csr;
2425 END IF;
2426
2427 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2428 p_api_name => l_api_name,
2429 p_pkg_name => G_PKG_NAME,
2430 p_exc_name => 'OTHERS',
2431 x_msg_count => x_msg_count,
2432 x_msg_data => x_msg_data,
2433 p_api_type => '_PVT');
2434
2435 END load_ln_cc_strm_summ;
2436
2437 PROCEDURE check_payment_schedule
2438 (p_api_version IN NUMBER,
2439 p_init_msg_list IN VARCHAR2,
2440 x_return_status OUT NOCOPY VARCHAR2,
2441 x_msg_count OUT NOCOPY NUMBER,
2442 x_msg_data OUT NOCOPY VARCHAR2,
2443 p_chr_id IN NUMBER,
2444 x_schedule_match_yn OUT NOCOPY VARCHAR2) IS
2445
2446 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2447 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_PAYMENT_SCHEDULE';
2448 l_api_version CONSTANT NUMBER := 1.0;
2449
2450 TYPE l_payment_details_rec IS RECORD (
2451 start_date DATE := NULL,
2452 number_of_periods NUMBER := NULL,
2453 stub_days NUMBER := NULL,
2454 stub_amount NUMBER := NULL,
2455 advance_or_arrears VARCHAR2(1) := NULL
2456 );
2457
2458 TYPE l_payment_details_tbl IS TABLE OF l_payment_details_rec INDEX BY BINARY_INTEGER;
2459
2460 TYPE l_tbl_rec IS RECORD (
2461 kle_id NUMBER,
2462 l_payment_details l_payment_details_tbl
2463 );
2464
2465 TYPE l_tbl_type IS TABLE OF l_tbl_rec INDEX BY BINARY_INTEGER;
2466
2467 l_pmnt_tab l_tbl_type;
2468 l_pmnt_tab_counter NUMBER;
2469 l_payment_details_counter NUMBER;
2470
2471 CURSOR l_payment_lines_csr IS
2472 SELECT
2473 rgpb.cle_id kle_id,
2474 rulb2.rule_information2 start_date,
2475 rulb2.rule_information3 level_periods,
2476 rulb2.rule_information7 stub_days,
2477 rulb2.rule_information8 stub_amount,
2478 rulb2.rule_information10 arrear_yn
2479 FROM okc_k_lines_b cleb,
2480 okc_rule_groups_b rgpb,
2481 okc_rules_b rulb,
2482 okc_rules_b rulb2,
2483 okl_strm_type_b styb,
2484 okc_statuses_b sts
2485 WHERE rgpb.chr_id IS NULL
2486 AND rgpb.dnz_chr_id = cleb.dnz_chr_id
2487 AND rgpb.cle_id = cleb.id
2488 AND cleb.dnz_chr_id = p_chr_id
2489 AND sts.code = cleb.sts_code
2490 AND sts.ste_code <> 'CANCELLED'
2491 AND rgpb.rgd_code = 'LALEVL'
2492 AND rulb.rgp_id = rgpb.id
2493 AND rulb.rule_information_category = 'LASLH'
2494 AND TO_CHAR(styb.id) = rulb.object1_id1
2495 AND rulb2.object2_id1 = TO_CHAR(rulb.id)
2496 AND rulb2.rgp_id = rgpb.id
2497 AND rulb2.rule_information_category = 'LASLL'
2498 AND styb.stream_type_purpose IN ('RENT','PRINCIPAL_PAYMENT')
2499 ORDER BY kle_id, start_date, level_periods;
2500
2501 TYPE payment_rec_type IS RECORD (
2502 kle_id NUMBER,
2503 start_date VARCHAR2(450),
2504 number_of_periods VARCHAR2(450),
2505 stub_days VARCHAR2(450),
2506 stub_amount VARCHAR2(450),
2507 advance_or_arrears VARCHAR2(450)
2508 );
2509
2510 TYPE payment_table IS TABLE OF payment_rec_type INDEX BY BINARY_INTEGER;
2511
2512 l_payment_table payment_table;
2513 l_payment_table2 payment_table;
2514 l_payment_table_counter NUMBER;
2515 l_limit NUMBER := 10000;
2516 l_prev_kle_id NUMBER;
2517 l_num_schedules NUMBER;
2518 schedule_mismatch EXCEPTION;
2519 l_start_date DATE;
2520 l_number_of_periods NUMBER;
2521 l_stub_days NUMBER;
2522 l_advance_or_arrears VARCHAR2(30);
2523
2524 PROCEDURE print_tab(p_table IN l_tbl_type) IS
2525 BEGIN
2526 NULL;
2527 FOR i IN p_table.first..p_table.last
2528 LOOP
2529 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2530 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i='||i || '->kle_id='|| p_table(i).kle_id);
2531 END IF;
2532 FOR j IN p_table(i).l_payment_details.first..p_table(i).l_payment_details.last
2533 LOOP
2534 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2535 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-->j=' || j );
2536 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'------>start_date=' || p_table(i).l_payment_details(j).start_date);
2537 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'------>number_of_periods=' || p_table(i).l_payment_details(j).number_of_periods);
2538 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'------>stub_days=' || p_table(i).l_payment_details(j).stub_days);
2539 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'------>stub_amount=' || p_table(i).l_payment_details(j).stub_amount);
2540 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'------>advance_or_arrears=' || p_table(i).l_payment_details(j).advance_or_arrears);
2541 END IF;
2542 END LOOP;
2543 END LOOP;
2544 END;
2545
2546 PROCEDURE print_payment_orig_table(p_table IN payment_table) IS
2547 BEGIN
2548 NULL;
2549 IF (p_table.COUNT > 0) THEN
2550 FOR i IN p_table.first..p_table.last
2551 LOOP
2552 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2553 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i='||i || '->kle_id='|| p_table(i).kle_id);
2554 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'start_date=' || p_table(i).start_date);
2555 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'number_of_periods=' || p_table(i).number_of_periods);
2556 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'stub_days=' || p_table(i).stub_days);
2557 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'stub_amount=' || p_table(i).stub_amount);
2558 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'advance_or_arrears=' || p_table(i).advance_or_arrears);
2559 END IF;
2560 END LOOP;
2561 END IF;
2562 END;
2563
2564 BEGIN
2565 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
2566 x_schedule_match_yn := 'N';
2567
2568 l_payment_table_counter := 1;
2569 OPEN l_payment_lines_csr;
2570 LOOP
2571 FETCH l_payment_lines_csr BULK COLLECT INTO l_payment_table2 LIMIT l_limit;
2572 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2573 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_payment_table2.count=' || l_payment_table2.COUNT);
2574 END IF;
2575 IF (l_payment_table2.COUNT > 0) THEN
2576 FOR i IN l_payment_table2.FIRST..l_payment_table2.LAST
2577 LOOP
2578 l_payment_table(l_payment_table_counter) := l_payment_table2(i);
2579 l_payment_table_counter := l_payment_table_counter + 1;
2580 END LOOP;
2581 ELSE
2582 EXIT;
2583 END IF;
2584 END LOOP;
2585 CLOSE l_payment_lines_csr;
2586 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2587 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_payment_table_counter=' || l_payment_table_counter);
2588 print_payment_orig_table(l_payment_table);
2589 END IF;
2590
2591 IF (l_payment_table.COUNT > 0) THEN
2592 -- Prepare formatted table from l_payment_table
2593 l_pmnt_tab_counter := 0;
2594 l_payment_details_counter := 1;
2595 l_prev_kle_id := 0;
2596
2597 FOR i IN l_payment_table.first..l_payment_table.last
2598 LOOP
2599 IF (l_prev_kle_id <> l_payment_table(i).kle_id) THEN
2600 l_pmnt_tab_counter := l_pmnt_tab_counter + 1;
2601 l_payment_details_counter := 1;
2602 l_pmnt_tab(l_pmnt_tab_counter).kle_id := l_payment_table(i).kle_id;
2603
2604 l_pmnt_tab(l_pmnt_tab_counter).l_payment_details(l_payment_details_counter).start_date := Fnd_Date.canonical_to_date(l_payment_table(i).start_date);
2605 l_pmnt_tab(l_pmnt_tab_counter).l_payment_details(l_payment_details_counter).number_of_periods := l_payment_table(i).number_of_periods;
2606 l_pmnt_tab(l_pmnt_tab_counter).l_payment_details(l_payment_details_counter).stub_days := l_payment_table(i).stub_days;
2607 l_pmnt_tab(l_pmnt_tab_counter).l_payment_details(l_payment_details_counter).stub_amount := l_payment_table(i).stub_amount;
2608 l_pmnt_tab(l_pmnt_tab_counter).l_payment_details(l_payment_details_counter).advance_or_arrears := l_payment_table(i).advance_or_arrears;
2609
2610 l_prev_kle_id := l_payment_table(i).kle_id;
2611 l_payment_details_counter := l_payment_details_counter + 1;
2612 ELSE
2613 l_pmnt_tab(l_pmnt_tab_counter).l_payment_details(l_payment_details_counter).start_date := Fnd_Date.canonical_to_date(l_payment_table(i).start_date);
2614 l_pmnt_tab(l_pmnt_tab_counter).l_payment_details(l_payment_details_counter).number_of_periods := l_payment_table(i).number_of_periods;
2615 l_pmnt_tab(l_pmnt_tab_counter).l_payment_details(l_payment_details_counter).stub_days := l_payment_table(i).stub_days;
2616 l_pmnt_tab(l_pmnt_tab_counter).l_payment_details(l_payment_details_counter).stub_amount := l_payment_table(i).stub_amount;
2617 l_pmnt_tab(l_pmnt_tab_counter).l_payment_details(l_payment_details_counter).advance_or_arrears := l_payment_table(i).advance_or_arrears;
2618
2619 l_prev_kle_id := l_payment_table(i).kle_id;
2620 l_payment_details_counter := l_payment_details_counter + 1;
2621 END IF;
2622 END LOOP;
2623
2624 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2625 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Now Printing l_pmnt_tab...');
2626 print_tab(l_pmnt_tab);
2627 END IF;
2628
2629 -- Check only if there are at least two asset lines
2630 IF (l_pmnt_tab.COUNT > 1) THEN
2631
2632 l_num_schedules := l_pmnt_tab(1).l_payment_details.COUNT;
2633 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2634 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_num_schedules=' || l_num_schedules);
2635 END IF;
2636 FOR i IN 2..l_pmnt_tab.LAST
2637 LOOP
2638 IF (l_num_schedules <> l_pmnt_tab(i).l_payment_details.COUNT) THEN
2639 RAISE schedule_mismatch;
2640 END IF;
2641 END LOOP;
2642
2643
2644 FOR j IN l_pmnt_tab(1).l_payment_details.first..l_pmnt_tab(1).l_payment_details.last
2645 LOOP
2646 l_start_date := l_pmnt_tab(1).l_payment_details(j).start_date;
2647 l_number_of_periods := l_pmnt_tab(1).l_payment_details(j).number_of_periods;
2648 l_stub_days := l_pmnt_tab(1).l_payment_details(j).stub_days;
2649 l_advance_or_arrears := l_pmnt_tab(1).l_payment_details(j).advance_or_arrears;
2650 FOR i IN 2..l_pmnt_tab.LAST
2651 LOOP
2652 IF (Fnd_Date.canonical_to_date(l_start_date) <> Fnd_Date.canonical_to_date(l_pmnt_tab(i).l_payment_details(j).start_date)) OR
2653 (NVL(l_number_of_periods,0) <> NVL(l_pmnt_tab(i).l_payment_details(j).number_of_periods,0)) OR
2654 (NVL(l_stub_days,0) <> NVL(l_pmnt_tab(i).l_payment_details(j).stub_days,0)) OR
2655 (NVL(l_advance_or_arrears,'N') <> NVL(l_pmnt_tab(i).l_payment_details(j).advance_or_arrears,'N')) THEN
2656
2657 RAISE schedule_mismatch;
2658 END IF;
2659 END LOOP; -- i
2660 END LOOP; -- j
2661 END IF;
2662
2663 x_schedule_match_yn := 'Y';
2664 END IF;
2665
2666 EXCEPTION
2667 WHEN schedule_mismatch THEN
2668 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2669 x_schedule_match_yn := 'N';
2670
2671 WHEN OTHERS THEN
2672 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2673 x_schedule_match_yn := 'N';
2674
2675 END check_payment_schedule;
2676
2677 PROCEDURE load_loan_amort_schedule(
2678 p_api_version IN NUMBER,
2679 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2680 x_return_status OUT NOCOPY VARCHAR2,
2681 x_msg_count OUT NOCOPY NUMBER,
2682 x_msg_data OUT NOCOPY VARCHAR2,
2683 p_chr_id IN NUMBER,
2684 p_report_type IN VARCHAR2,
2685 x_proj_interest_rate OUT NOCOPY NUMBER,
2686 x_amort_sched_tbl OUT NOCOPY amort_sched_tbl_type) IS
2687
2688 l_api_name CONSTANT VARCHAR2(30) := 'LOAD_LOAN_AMORT_SCHEDULE';
2689 l_api_version CONSTANT NUMBER := 1.0;
2690
2691 CURSOR chr_product_csr(p_chr_id IN NUMBER) IS
2692 SELECT ppm_rrm.quality_val rev_rec_method,
2693 ppm_icb.quality_val int_calc_basis,
2694 khr.implicit_interest_rate,
2695 chrb.start_date
2696 FROM okl_k_headers khr,
2697 okl_prod_qlty_val_uv ppm_rrm,
2698 okl_prod_qlty_val_uv ppm_icb,
2699 okc_k_headers_b chrb
2700 WHERE chrb.id = p_chr_id
2701 AND khr.id = chrb.id
2702 AND ppm_rrm.pdt_id = khr.pdt_id
2703 AND ppm_rrm.quality_name = 'REVENUE_RECOGNITION_METHOD'
2704 AND ppm_icb.pdt_id = khr.pdt_id
2705 AND ppm_icb.quality_name = 'INTEREST_CALCULATION_BASIS';
2706
2707 chr_product_rec chr_product_csr%ROWTYPE;
2708
2709 x_last_billing_date DATE;
2710 l_schedule_match_yn VARCHAR2(1);
2711 l_principal_balance NUMBER;
2712 l_proj_interest_rate NUMBER;
2713
2714 BEGIN
2715
2716 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2717 -- Call start_activity to create savepoint, check compatibility
2718 -- and initialize message list
2719 x_return_status := OKL_API.START_ACTIVITY (
2720 l_api_name
2721 ,p_init_msg_list
2722 ,'_PVT'
2723 ,x_return_status);
2724 -- Check if activity started successfully
2725 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2726 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2727 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2728 RAISE OKL_API.G_EXCEPTION_ERROR;
2729 END IF;
2730
2731 -- Summary Report: Check whether all assets have same Payment Schedule
2732 IF p_report_type = G_REPORT_TYPE_SUMMARY THEN
2733
2734 check_payment_schedule(
2735 p_api_version => p_api_version,
2736 p_init_msg_list => p_init_msg_list,
2737 x_return_status => x_return_status,
2738 x_msg_count => x_msg_count,
2739 x_msg_data => x_msg_data,
2740 p_chr_id => p_chr_id,
2741 x_schedule_match_yn => l_schedule_match_yn);
2742
2743 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2744 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2745 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2746 RAISE OKL_API.G_EXCEPTION_ERROR;
2747 END IF;
2748
2749 END IF;
2750
2751 IF (p_report_type = G_REPORT_TYPE_DETAIL OR
2752 p_report_type = G_REPORT_TYPE_SUMMARY AND l_schedule_match_yn = 'Y') THEN
2753
2754 OPEN chr_product_csr(p_chr_id => p_chr_id);
2755 FETCH chr_product_csr INTO chr_product_rec;
2756 CLOSE chr_product_csr;
2757
2758 IF chr_product_rec.int_calc_basis IN (G_ICB_FIXED,G_ICB_REAMORT) AND chr_product_rec.rev_rec_method = G_RRM_STREAMS THEN
2759 IF p_report_type = G_REPORT_TYPE_SUMMARY THEN
2760 -- Fetch Past and Projected Schedule - Summary
2761 load_ln_streams_summ(
2762 p_api_version => p_api_version,
2763 p_init_msg_list => p_init_msg_list,
2764 x_return_status => x_return_status,
2765 x_msg_count => x_msg_count,
2766 x_msg_data => x_msg_data,
2767 p_chr_id => p_chr_id,
2768 x_amort_sched_tbl => x_amort_sched_tbl);
2769
2770 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2771 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2772 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2773 RAISE OKL_API.G_EXCEPTION_ERROR;
2774 END IF;
2775
2776
2777 ELSIF p_report_type = G_REPORT_TYPE_DETAIL THEN
2778 -- Fetch Past and Projected Schedule - Detail
2779 load_ln_streams_dtl(
2780 p_api_version => p_api_version,
2781 p_init_msg_list => p_init_msg_list,
2782 x_return_status => x_return_status,
2783 x_msg_count => x_msg_count,
2784 x_msg_data => x_msg_data,
2785 p_chr_id => p_chr_id,
2786 x_amort_sched_tbl => x_amort_sched_tbl);
2787
2788 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2789 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2790 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2791 RAISE OKL_API.G_EXCEPTION_ERROR;
2792 END IF;
2793
2794
2795 END IF;
2796 x_proj_interest_rate := chr_product_rec.implicit_interest_rate;
2797
2798 ELSIF chr_product_rec.rev_rec_method = G_RRM_ACTUAL THEN
2799
2800 IF p_report_type = G_REPORT_TYPE_SUMMARY THEN
2801
2802 -- Fetch Past and Projected Schedule - Summary
2803 load_ln_actual_summ(
2804 p_api_version => p_api_version,
2805 p_init_msg_list => p_init_msg_list,
2806 x_return_status => x_return_status,
2807 x_msg_count => x_msg_count,
2808 x_msg_data => x_msg_data,
2809 p_chr_id => p_chr_id,
2810 x_proj_interest_rate => l_proj_interest_rate,
2811 x_amort_sched_tbl => x_amort_sched_tbl);
2812
2813 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2814 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2815 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2816 RAISE OKL_API.G_EXCEPTION_ERROR;
2817 END IF;
2818
2819 ELSIF p_report_type = G_REPORT_TYPE_DETAIL THEN
2820
2821 -- Fetch Past and Projected Schedule - Detail
2822 load_ln_actual_dtl(
2823 p_api_version => p_api_version,
2824 p_init_msg_list => p_init_msg_list,
2825 x_return_status => x_return_status,
2826 x_msg_count => x_msg_count,
2827 x_msg_data => x_msg_data,
2828 p_chr_id => p_chr_id,
2829 x_proj_interest_rate => l_proj_interest_rate,
2830 x_amort_sched_tbl => x_amort_sched_tbl);
2831
2832 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2833 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2834 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2835 RAISE OKL_API.G_EXCEPTION_ERROR;
2836 END IF;
2837
2838 END IF;
2839 x_proj_interest_rate :=l_proj_interest_rate;
2840
2841 ELSIF chr_product_rec.int_calc_basis = G_ICB_FLOAT AND chr_product_rec.rev_rec_method = G_RRM_ESTIMATED_AND_BILLED THEN
2842
2843 IF p_report_type = G_REPORT_TYPE_SUMMARY THEN
2844
2845 -- Fetch Past and Projected Schedule - Summary
2846 load_ln_float_eb_summ(
2847 p_api_version => p_api_version,
2848 p_init_msg_list => p_init_msg_list,
2849 x_return_status => x_return_status,
2850 x_msg_count => x_msg_count,
2851 x_msg_data => x_msg_data,
2852 p_chr_id => p_chr_id,
2853 x_amort_sched_tbl => x_amort_sched_tbl);
2854
2855 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2856 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2857 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2858 RAISE OKL_API.G_EXCEPTION_ERROR;
2859 END IF;
2860
2861 ELSIF p_report_type = G_REPORT_TYPE_DETAIL THEN
2862
2863 -- Fetch Past and Projected Schedule - Detail
2864 load_ln_float_eb_dtl(
2865 p_api_version => p_api_version,
2866 p_init_msg_list => p_init_msg_list,
2867 x_return_status => x_return_status,
2868 x_msg_count => x_msg_count,
2869 x_msg_data => x_msg_data,
2870 p_chr_id => p_chr_id,
2871 x_amort_sched_tbl => x_amort_sched_tbl);
2872
2873 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2874 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2875 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2876 RAISE OKL_API.G_EXCEPTION_ERROR;
2877 END IF;
2878
2879 END IF;
2880 x_proj_interest_rate := chr_product_rec.implicit_interest_rate;
2881
2882 ELSIF chr_product_rec.int_calc_basis = G_ICB_CATCHUP_CLEANUP AND chr_product_rec.rev_rec_method = G_RRM_STREAMS THEN
2883
2884 IF p_report_type = G_REPORT_TYPE_SUMMARY THEN
2885
2886 -- Fetch Past and Projected Schedule - Summary
2887 load_ln_cc_strm_summ(
2888 p_api_version => p_api_version,
2889 p_init_msg_list => p_init_msg_list,
2890 x_return_status => x_return_status,
2891 x_msg_count => x_msg_count,
2892 x_msg_data => x_msg_data,
2893 p_chr_id => p_chr_id,
2894 x_amort_sched_tbl => x_amort_sched_tbl);
2895
2896 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2897 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2898 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2899 RAISE OKL_API.G_EXCEPTION_ERROR;
2900 END IF;
2901
2902 ELSIF p_report_type = G_REPORT_TYPE_DETAIL THEN
2903
2904 -- Fetch Past and Projected Schedule - Detail
2905 load_ln_cc_strm_dtl(
2906 p_api_version => p_api_version,
2907 p_init_msg_list => p_init_msg_list,
2908 x_return_status => x_return_status,
2909 x_msg_count => x_msg_count,
2910 x_msg_data => x_msg_data,
2911 p_chr_id => p_chr_id,
2912 x_amort_sched_tbl => x_amort_sched_tbl);
2913
2914 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2915 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2916 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2917 RAISE OKL_API.G_EXCEPTION_ERROR;
2918 END IF;
2919
2920 END IF;
2921 x_proj_interest_rate := chr_product_rec.implicit_interest_rate;
2922
2923 END IF;
2924
2925 --Bug# 6831074: Assign contract start date to the opening principal
2926 -- balance row in both summary and detail schedule
2927 IF (x_amort_sched_tbl.COUNT > 0) THEN
2928 IF (p_report_type = G_REPORT_TYPE_DETAIL) THEN
2929
2930 x_amort_sched_tbl(x_amort_sched_tbl.FIRST).start_date := chr_product_rec.start_date;
2931
2932 ELSIF (p_report_type = G_REPORT_TYPE_SUMMARY) THEN
2933
2934 x_amort_sched_tbl(x_amort_sched_tbl.FIRST).start_date := chr_product_rec.start_date;
2935 x_amort_sched_tbl(x_amort_sched_tbl.FIRST).end_date := chr_product_rec.start_date;
2936
2937 END IF;
2938 END IF;
2939
2940 END IF;
2941 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data);
2942
2943 EXCEPTION
2944
2945 ------------------------------------------------------------
2946 -- Exception handling
2947 ------------------------------------------------------------
2948
2949 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2950
2951 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2952 p_api_name => l_api_name,
2953 p_pkg_name => G_PKG_NAME,
2954 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2955 x_msg_count => x_msg_count,
2956 x_msg_data => x_msg_data,
2957 p_api_type => '_PVT');
2958
2959 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2960
2961 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2962 p_api_name => l_api_name,
2963 p_pkg_name => G_PKG_NAME,
2964 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2965 x_msg_count => x_msg_count,
2966 x_msg_data => x_msg_data,
2967 p_api_type => '_PVT');
2968
2969 WHEN OTHERS THEN
2970
2971 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2972 p_api_name => l_api_name,
2973 p_pkg_name => G_PKG_NAME,
2974 p_exc_name => 'OTHERS',
2975 x_msg_count => x_msg_count,
2976 x_msg_data => x_msg_data,
2977 p_api_type => '_PVT');
2978
2979 END load_loan_amort_schedule;
2980
2981 END OKL_LOAN_AMORT_SCHEDULE_PVT;