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