eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.OKL_VAR_INT_CATCHUP_PVT

Source


1 PACKAGE BODY OKL_VAR_INT_CATCHUP_PVT AS
2 /* $Header: OKLRVCCB.pls 120.0.12020000.2 2012/11/23 14:06:57 rpillay noship $ */
3 
4   --Bug# 14165508
5   TYPE payment_rec_type IS RECORD (
6     period_start_date   DATE
7    ,period_end_date     DATE
8    ,period_or_stub_flag VARCHAR2(1)
9    ,conversion_factor   NUMBER);
10 
11   --Bug# 14165508
12   TYPE payment_tbl_type IS TABLE OF payment_rec_type INDEX BY BINARY_INTEGER;
13 
14   --Bug# 14165508
15   FUNCTION get_conversion_factor(p_khr_id     IN NUMBER,
16                                  p_start_date IN DATE,
17                                  p_due_date   IN DATE) RETURN payment_tbl_type IS
18 
19     CURSOR l_contract_csr(p_contract_id NUMBER) IS
20     SELECT (NVL(term_duration,0) * 30) term_days
21     FROM okl_k_headers
22     WHERE id = p_contract_id;
23 
24     CURSOR c_khr_params(cp_khr_id IN NUMBER) IS
25     select khr.start_date
26     , NVL(rpm.catchup_start_date, khr.start_date) catchup_start_date
27     , NVL(rpm.catchup_frequency_code, 'MONTHLY') catchup_frequency_code
28     , khr.end_date end_date
29     from okc_k_headers_b khr
30     , okl_k_rate_params rpm
31     where khr.id = rpm.khr_id
32     and khr.id = cp_khr_id;
33 
34     l_chr_start_date DATE;
35     l_chr_end_date DATE;
36     l_catchup_start_date DATE;
37     l_catchup_frequency VARCHAR2(50);
38 
39     l_return_status      VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
40     l_adder_months       NUMBER := 0;
41 
42     l_chr_term_days      NUMBER := 0;
43     l_chr_period_days    NUMBER := 0;
44     l_chr_stub_days      NUMBER := 0;
45     l_actual_stub_days   NUMBER := 0;
46     l_stub_conversion_factor NUMBER := 0;
47 
48     x_payment_tbl        payment_tbl_type;
49     i                    NUMBER;
50     j                    NUMBER;
51     l_start_date         DATE;
52     l_end_date           DATE;
53 
54   BEGIN
55 
56     OPEN l_contract_csr(p_khr_id);
57     FETCH l_contract_csr INTO l_chr_term_days;
58     CLOSE l_contract_csr;
59 
60     OKL_VARIABLE_INTEREST_PVT.print_debug('l_chr_term_days: '|| l_chr_term_days);
61 
62     FOR cur_khr_params IN c_khr_params(p_khr_id) LOOP
63       l_chr_start_date := cur_khr_params.start_date;
64       l_catchup_start_date := cur_khr_params.catchup_start_date;
65       l_catchup_frequency := cur_khr_params.catchup_frequency_code;
66       l_chr_end_date := cur_khr_params.end_date;
67       EXIT;
68     END LOOP;
69 
70     OKL_VARIABLE_INTEREST_PVT.print_debug('l_chr_start_date: '|| l_chr_start_date);
71     OKL_VARIABLE_INTEREST_PVT.print_debug('l_chr_end_date: '|| l_chr_end_date);
72     OKL_VARIABLE_INTEREST_PVT.print_debug('l_catchup_start_date: '|| l_catchup_start_date);
73     OKL_VARIABLE_INTEREST_PVT.print_debug('l_catchup_frequency: '|| l_catchup_frequency);
74 
75     IF(UPPER(l_catchup_frequency) = 'ANNUAL') THEN
76       l_adder_months := 12;
77     ELSIF(UPPER(l_catchup_frequency) = 'SEMI_ANNUAL') THEN
78       l_adder_months := 6;
79     ELSIF(UPPER(l_catchup_frequency) = 'QUARTERLY') THEN
80       l_adder_months := 3;
81     ELSIF(UPPER(l_catchup_frequency) = 'MONTHLY') THEN
82       l_adder_months := 1;
83     END IF;
84 
85     i := 0;
86     l_chr_period_days := 0;
87     l_chr_stub_days := 0;
88     l_actual_stub_days := 0;
89 
90     IF (l_catchup_start_date > l_chr_start_date) THEN
91       l_actual_stub_days := l_catchup_start_date - l_chr_start_date;
92 
93       OKL_VARIABLE_INTEREST_PVT.print_debug('l_actual_stub_days start of term: '|| l_actual_stub_days);
94 
95       IF (p_start_date = l_chr_start_date) THEN
96 
97         i := i + 1;
98         x_payment_tbl(i).period_start_date := l_chr_start_date;
99         x_payment_tbl(i).period_end_date := l_catchup_start_date - 1;
100         x_payment_tbl(i).period_or_stub_flag := 'S';
101 
102         OKL_VARIABLE_INTEREST_PVT.print_debug('i : '|| i);
103         OKL_VARIABLE_INTEREST_PVT.print_debug('x_payment_tbl(i).period_start_date : '|| x_payment_tbl(i).period_start_date);
104         OKL_VARIABLE_INTEREST_PVT.print_debug('x_payment_tbl(i).period_end_date : '|| x_payment_tbl(i).period_end_date);
105         OKL_VARIABLE_INTEREST_PVT.print_debug('x_payment_tbl(i).period_or_stub_flag : '|| x_payment_tbl(i).period_or_stub_flag);
106         OKL_VARIABLE_INTEREST_PVT.print_debug('x_payment_tbl(i).conversion_factor : '|| x_payment_tbl(i).conversion_factor);
107       END IF;
108     END IF;
109 
110     l_start_date := l_catchup_start_date;
111     j := 0;
112     LOOP
113       j := j + 1;
114 
115       OKL_STREAM_GENERATOR_PVT.get_sel_date(p_start_date         => l_catchup_start_date,
116                                             p_advance_or_arrears => 'ARREARS',
117                                             p_periods_after      => j,
118                                             p_months_per_period  => l_adder_months,
119                                             x_date               => l_end_date,
120                                             x_return_status      => l_return_status,
121                                             p_recurrence_date    => l_catchup_start_date,
122                                             p_arrears_pay_dates_option => 'LAST_DAY_OF_PERIOD');
123 
124       IF(l_return_status <> OKL_API.G_RET_STS_SUCCESS)THEN
125         RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
126       END IF;
127 
128       OKL_VARIABLE_INTEREST_PVT.print_debug('l_start_date: '|| l_start_date);
129       OKL_VARIABLE_INTEREST_PVT.print_debug('l_end_date: '|| l_end_date);
130 
131       EXIT WHEN l_end_date > l_chr_end_date;
132 
133       IF(l_end_date < p_start_date) THEN
134         NULL;
135 
136       ELSIF (l_start_date <= p_due_date) THEN
137 
138         i := i + 1;
139         x_payment_tbl(i).period_start_date := l_start_date;
140         x_payment_tbl(i).period_end_date := l_end_date;
141         x_payment_tbl(i).period_or_stub_flag := 'P';
142         x_payment_tbl(i).conversion_factor := (30 * l_adder_months)/(l_end_date - l_start_date + 1);
143 
144         OKL_VARIABLE_INTEREST_PVT.print_debug('i : '|| i);
145         OKL_VARIABLE_INTEREST_PVT.print_debug('x_payment_tbl(i).period_start_date : '|| x_payment_tbl(i).period_start_date);
146         OKL_VARIABLE_INTEREST_PVT.print_debug('x_payment_tbl(i).period_end_date : '|| x_payment_tbl(i).period_end_date);
147         OKL_VARIABLE_INTEREST_PVT.print_debug('x_payment_tbl(i).period_or_stub_flag : '|| x_payment_tbl(i).period_or_stub_flag);
148         OKL_VARIABLE_INTEREST_PVT.print_debug('x_payment_tbl(i).conversion_factor : '|| x_payment_tbl(i).conversion_factor);
149 
150       END IF;
151 
152       l_chr_period_days := l_chr_period_days +  (30 * l_adder_months);
153       l_start_date := l_end_date + 1;
154 
155     END LOOP;
156 
157     OKL_VARIABLE_INTEREST_PVT.print_debug('l_chr_period_days: '|| l_chr_period_days);
158 
159     IF (l_start_date <= l_chr_end_date) THEN
160       l_actual_stub_days := l_actual_stub_days + (l_chr_end_date - l_start_date + 1);
161 
162       OKL_VARIABLE_INTEREST_PVT.print_debug('l_actual_stub_days: '|| l_actual_stub_days);
163 
164       IF (p_due_date >= l_start_date) THEN
165         i := i + 1;
166         x_payment_tbl(i).period_start_date := l_start_date;
167         x_payment_tbl(i).period_end_date := l_chr_end_date;
168         x_payment_tbl(i).period_or_stub_flag := 'S';
169 
170         OKL_VARIABLE_INTEREST_PVT.print_debug('i : '|| i);
171         OKL_VARIABLE_INTEREST_PVT.print_debug('x_payment_tbl(i).period_start_date : '|| x_payment_tbl(i).period_start_date);
172         OKL_VARIABLE_INTEREST_PVT.print_debug('x_payment_tbl(i).period_end_date : '|| x_payment_tbl(i).period_end_date);
173         OKL_VARIABLE_INTEREST_PVT.print_debug('x_payment_tbl(i).period_or_stub_flag : '|| x_payment_tbl(i).period_or_stub_flag);
174         OKL_VARIABLE_INTEREST_PVT.print_debug('x_payment_tbl(i).conversion_factor : '|| x_payment_tbl(i).conversion_factor);
175       END IF;
176     END IF;
177 
178     l_chr_stub_days := l_chr_term_days - l_chr_period_days;
179 
180     OKL_VARIABLE_INTEREST_PVT.print_debug('l_chr_stub_days: '|| l_chr_stub_days);
181     OKL_VARIABLE_INTEREST_PVT.print_debug('l_actual_stub_days: '|| l_actual_stub_days);
182 
183     IF (l_actual_stub_days <> 0 AND  l_chr_stub_days <> 0) THEN
184       l_stub_conversion_factor := l_chr_stub_days/l_actual_stub_days;
185 
186       OKL_VARIABLE_INTEREST_PVT.print_debug('l_stub_conversion_factor : '|| l_stub_conversion_factor);
187 
188       IF (x_payment_tbl.COUNT) > 0 THEN
189         FOR i IN  x_payment_tbl.FIRST..x_payment_tbl.LAST LOOP
190           IF (x_payment_tbl(i).period_or_stub_flag = 'S') THEN
191             x_payment_tbl(i).conversion_factor := l_stub_conversion_factor;
192           END IF;
193         END LOOP;
194       END IF;
195 
196     END IF;
197 
198     RETURN x_payment_tbl;
199   END get_conversion_factor;
200   --Bug# 14165508
201 
202   FUNCTION get_next_catchup_date(p_khr_id IN NUMBER) RETURN DATE IS
203 
204     CURSOR c_khr_params(cp_khr_id IN NUMBER) IS
205     select khr.date_last_interim_interest_cal date_last_interim_interest_cal
206     , NVL(rpm.catchup_start_date, khr.start_date) catchup_start_date
207     , NVL(rpm.catchup_frequency_code, 'MONTHLY') catchup_frequency_code
208     , NVL(khr.date_terminated, khr.end_date) end_date
209     from okl_k_headers_full_v khr
210     , okl_k_rate_params rpm
211     where khr.id = rpm.khr_id
212     and khr.id = cp_khr_id;
213 
214     l_next_catchup_date DATE;
215     l_mnth_adder NUMBER := 0;
216     l_last_int_cal_date DATE;
217     l_catchup_start_date DATE;
218     l_catchup_frequency VARCHAR2(50);
219     l_end_date DATE;
220 
221     --Bug# 13001695
222     i NUMBER;
223     l_return_status VARCHAR2(1);
224 
225   BEGIN
226     FOR cur_khr_params IN c_khr_params(p_khr_id) LOOP
227       l_last_int_cal_date := cur_khr_params.date_last_interim_interest_cal;
228       l_catchup_start_date := cur_khr_params.catchup_start_date;
229       l_catchup_frequency := cur_khr_params.catchup_frequency_code;
230       l_end_date := cur_khr_params.end_date;
231       EXIT;
232     END LOOP;
233 
234     if(UPPER(l_catchup_frequency) = 'ANNUAL') then
235       l_mnth_adder := 12;
236     elsif(UPPER(l_catchup_frequency) = 'SEMI_ANNUAL') then
237       l_mnth_adder := 6;
238     elsif(UPPER(l_catchup_frequency) = 'QUARTERLY') then
239       l_mnth_adder := 3;
240     elsif(UPPER(l_catchup_frequency) = 'MONTHLY') then
241       l_mnth_adder := 1;
242     else
243       return null;
244     end if;
245 
246     --Bug# 13001695
247     i := 0;
248     loop
249 
250         i := i + 1;
251         OKL_STREAM_GENERATOR_PVT.get_sel_date(p_start_date         => l_catchup_start_date,
252                                               p_advance_or_arrears => 'ARREARS',
253                                               p_periods_after      => i,
254                                               p_months_per_period  => l_mnth_adder,
255                                               x_date               => l_next_catchup_date,
256                                               x_return_status      => l_return_status,
257                                               p_recurrence_date    => l_catchup_start_date);
258 
259       exit when l_next_catchup_date > NVL(l_last_int_cal_date, l_next_catchup_date - 1);
260     end loop;
261 
262     /*l_next_catchup_date := l_catchup_start_date;
263     loop
264       exit when l_next_catchup_date > NVL(l_last_int_cal_date, l_next_catchup_date - 1);
265       --select add_months(l_next_date, l_mnth_adder) INTO l_next_date from dual;
266       l_next_catchup_date := add_months(l_next_catchup_date, l_mnth_adder);
267     end loop;*/
268 
269     --if next catchup date exceeds the contract end date
270     if (l_next_catchup_date > l_end_date) then
271       l_next_catchup_date := l_end_date;
272     end if;
273 
274     return l_next_catchup_date;
275   EXCEPTION
276    	WHEN OTHERS THEN
277     return l_next_catchup_date;
278   END get_next_catchup_date;
279 
280   ------------------------------------------------------------------------------
281 
282     -- Start of Comments
283     -- Created By:       Ramesh Seela
284     -- Procedure Name    variable_interest_catchup
285     -- Description:      This procedure is called by Variable Interest Calculation for Loans / Revolving Loans
286     --                   Inputs :
287     --                   Output :
288     -- Dependencies:
289     -- Parameters:
290     -- Version:          1.0
291     -- End of Comments
292 
293   ------------------------------------------------------------------------------
294 
295   PROCEDURE variable_interest_catchup(
296             p_api_version     IN  NUMBER,
297             p_init_msg_list   IN  VARCHAR2,
298             x_return_status   OUT NOCOPY VARCHAR2,
299             x_msg_count       OUT NOCOPY NUMBER,
300             x_msg_data        OUT NOCOPY VARCHAR2,
301             p_contract_id     IN  NUMBER,
302             p_principal_basis IN  VARCHAR2,
303             p_rev_rec_method  IN  VARCHAR2,
304             p_deal_type       IN  VARCHAR2,
305             p_currency_code   IN  VARCHAR2,
306             p_start_date      IN  DATE,
307             p_due_date        IN  DATE) IS
308 
309     l_api_version             CONSTANT NUMBER := 1.0;
310     l_api_name	              CONSTANT VARCHAR2(30) := 'VARIABLE_INTEREST_CATCHUP';
311     l_return_status	          VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
312     l_msg_index_out           NUMBER;
313     l_interest_calculated     NUMBER := 0;
314     l_total_interest_billed   NUMBER := 0;
315     l_principal_adjusted      NUMBER := 0;
316 --    l_catchup_settlement_code OKL_K_RATE_PARAMS.catchup_settlement_code%TYPE;
317     l_invoice_amt             NUMBER := 0;
318     l_stream_type_purpose     OKL_STRM_TYPE_V.stream_type_purpose%TYPE;
319     l_invoice_id              NUMBER;
320     l_principal_balance_tbl   OKL_VARIABLE_INTEREST_PVT.principal_balance_tbl_typ;
321     l_total_asset_val         NUMBER := 0;
322     l_principal_balance       NUMBER := 0;
323     l_asset_line_tbl          okl_kle_pvt.kle_tbl_type;
324     l_line_index              NUMBER := 0;
325     l_index                   NUMBER := 0;
326     l_asset_line_tbl_count    NUMBER := 0;
327     l_prorated_invoice_amt    NUMBER := 0;
328     i_vir_tbl                 OKL_VARIABLE_INTEREST_PVT.vir_tbl_type;
329     r_vir_tbl                 OKL_VARIABLE_INTEREST_PVT.vir_tbl_type;
330     l_stream_element_id       OKL_STRM_ELEMENTS_V.id%TYPE;
331     var_int_catchup_failed    EXCEPTION;
332 
333     --Bug# 13001695: Added parameter cp_stream_type_purpose
334     -- bug 13877914 - excluding financed fee interest
335     --Bug# 14127126
336     -- The interest_payment_amount_csr will fetch financial asset line level INTEREST_PAYMENT stream amounts and
337     -- contract header level INTEREST_CATCHUP stream amounts
338     -- Should not fetch Financed Fee, Rollover Fee or Linked Fee-Asset line level INTEREST_PAYMENT stream amounts
339     -- Bug# 13447258: Support Asset Level calculations
340     CURSOR interest_payment_amount_csr (cp_khr_id NUMBER, cp_start_date DATE, cp_due_date DATE, cp_stream_type_purpose VARCHAR2, cp_kle_id NUMBER) IS
341         SELECT nvl(SUM(nvl(amount, 0)),0) pmt_amt
342         FROM
343               okl_strm_elements sel
344              ,okl_streams str
345              ,okl_strm_type_b sty
346             WHERE  sel.stm_id = str.id
347               AND  str.khr_id = cp_khr_id
348               AND  str.say_code = 'CURR'
349               AND  str.active_yn = 'Y'
350               --Bug# 12903938
351               AND  sel.stream_element_date BETWEEN cp_start_date AND cp_due_date
352               AND  str.sty_id = sty.id
353               AND  sty.stream_type_purpose = cp_stream_type_purpose
354               AND  str.kle_id = cp_kle_id;
355 
356     --Bug# 13001695: Added parameter cp_stream_type_purpose
357     --Bug# 14127126
358     -- The int_pay_amt_end_date_csr will fetch financial asset line level INTEREST_PAYMENT stream amounts and
359     -- contract header level INTEREST_CATCHUP stream amounts
360     -- Should not fetch Financed Fee, Rollover Fee or Linked Fee-Asset line level INTEREST_PAYMENT stream amounts
361     -- Bug# 13447258: Support Asset Level calculations
362     CURSOR int_pay_amt_end_date_csr (cp_khr_id NUMBER, cp_start_date DATE, cp_stream_type_purpose VARCHAR2, cp_kle_id NUMBER) IS
363        SELECT nvl(SUM(nvl(sel_prin_pmt.amount, 0)),0) pmt_amt
364        FROM  okl_strm_type_b sty_prin_pmt,
365              okl_streams stm_prin_pmt,
366              okl_strm_elements sel_prin_pmt
367        WHERE stm_prin_pmt.khr_id = cp_khr_id
368        AND   stm_prin_pmt.id = sel_prin_pmt.stm_id
369        AND   sel_prin_pmt.stream_element_date >= cp_start_date
370        AND   stm_prin_pmt.sty_id = sty_prin_pmt.id
371        AND   stm_prin_pmt.active_yn = 'Y'
372        AND   stm_prin_pmt.say_code = 'CURR'
373        AND   sty_prin_pmt.stream_type_purpose = cp_stream_type_purpose
374        AND   stm_prin_pmt.kle_id = cp_kle_id;
375 
376     CURSOR principal_adjustment_csr (cp_khr_id NUMBER, cp_start_date DATE, cp_due_date DATE, cp_kle_id NUMBER) IS
377        SELECT nvl(SUM(nvl(sel_prin_pmt.amount, 0)),0) pmt_amt
378        FROM  okl_strm_type_v sty_prin_pmt,
379              okl_streams_v stm_prin_pmt,
380              okl_strm_elements_v sel_prin_pmt
381        WHERE stm_prin_pmt.khr_id = cp_khr_id
382        AND   stm_prin_pmt.id = sel_prin_pmt.stm_id
383        AND   sel_prin_pmt.stream_element_date BETWEEN cp_start_date AND cp_due_date
384        AND   stm_prin_pmt.sty_id = sty_prin_pmt.id
385        AND   stm_prin_pmt.active_yn = 'Y'
386        AND   stm_prin_pmt.say_code = 'CURR'
387        AND   sty_prin_pmt.stream_type_purpose = 'PRINCIPAL_CATCHUP'
388        AND   stm_prin_pmt.kle_id = cp_kle_id;
389 
390      --Bug# 12974796
391      CURSOR contract_csr(p_khr_id IN NUMBER) IS
392      SELECT contract_number
393      FROM okc_k_headers_b
394      WHERE id = p_khr_id;
395 
396      l_contract_rec  contract_csr%ROWTYPE;
397      l_counter       NUMBER;
398      --Bug# 12974796
399 
400      --Bug# 13001695
401      l_arrears_pay_dates_option  okl_st_gen_tmpt_sets_all.isg_arrears_pay_dates_option%type;
402      l_invoice_date DATE;
403      l_interest_billed NUMBER;
404      l_catchup_interest_billed NUMBER;
405      l_stream_element_start_date DATE;
406      l_stream_element_end_date DATE;
407 
408      -- Bug# 13447258: Support Asset Level calculations
409      CURSOR l_assets_csr(p_contract_id NUMBER) IS
410      SELECT cle.id
411        FROM okc_k_lines_b cle,
412             okc_statuses_b sts
413       WHERE cle.lse_id = OKL_VARIABLE_INTEREST_PVT.G_FIN_LINE_LTY_ID
414         AND cle.dnz_chr_id = p_contract_id
415         AND cle.chr_id = p_contract_id
416         AND cle.sts_code = sts.code
417         AND sts.ste_code NOT IN ('TERMINATED', 'CANCELLED');
418 
419      l_cle_id NUMBER;
420 
421      --Bug# 14165508
422      l_payment_tbl         payment_tbl_type;
423      l_interest            NUMBER;
424 
425   BEGIN
426     ------------------------------------------------------------
427     -- Start processing
428     ------------------------------------------------------------
429 
430     x_return_status := OKL_API.G_RET_STS_SUCCESS;
431 
432     OKL_VARIABLE_INTEREST_PVT.print_debug('Executing procedure VARIABLE_INTEREST_CATCHUP using following parameters : ');
433     OKL_VARIABLE_INTEREST_PVT.print_debug('contract ID:                 '|| p_contract_id);
434     OKL_VARIABLE_INTEREST_PVT.print_Debug('Principal Basis:             '|| p_principal_basis);
435     OKL_VARIABLE_INTEREST_PVT.print_debug('Revenue Recognition Method:  '|| p_rev_rec_method);
436     OKL_VARIABLE_INTEREST_PVT.print_debug('Deal Type:                   '|| p_deal_type);
437     OKL_VARIABLE_INTEREST_PVT.Print_debug('Currency Code:               '|| p_currency_code);
438     OKL_VARIABLE_INTEREST_PVT.print_debug('Start Date:                  '|| p_start_date);
439     OKL_VARIABLE_INTEREST_PVT.print_debug('Due Date:                    '|| p_due_date);
440 
441     --Bug# 13447258: Variable Rate processing is not allowed for Catchup-Cleanup Loan contracts if
442     --               contract streams have not been upgraded to asset level
443     OKL_VARIABLE_INT_UTIL_PVT.check_vr_asset_level_upgrade(
444       p_khr_id            => p_contract_id,
445       x_return_status     => x_return_status);
446 
447     IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
448       RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
449     ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
450       RAISE var_int_catchup_failed;
451     END IF;
452 
453     OKL_VARIABLE_INTEREST_PVT.g_vpb_tbl.delete;
454     OKL_VARIABLE_INTEREST_PVT.g_vpb_tbl_counter := 0;
455 
456     OKL_VARIABLE_INTEREST_PVT.populate_txns(
457       p_api_version => p_api_version,
458       p_init_msg_list  => p_init_msg_list,
459       p_khr_id  => p_contract_id,
460       p_from_date => p_start_date,
461       p_to_date  => p_due_date,
462       x_return_status   => x_return_status,
463       x_msg_count       => x_msg_count,
464       x_msg_data        => x_msg_data);
465 
466     IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
467       OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to POPULATE_TXNS');
468       RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
469     ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
470       OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to POPULATE_TXNS');
471       RAISE var_int_catchup_failed;
472     END IF;
473 
474     --Bug# 13001695
475     OKL_ISG_UTILS_PVT.get_arrears_pay_dates_option(
476       p_khr_id                   => p_contract_id,
477       x_arrears_pay_dates_option => l_arrears_pay_dates_option,
478       x_return_status            => x_return_status);
479 
480     IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
481       RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
482     ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
483       RAISE OKL_API.G_EXCEPTION_ERROR;
484     END IF;
485 
486     --Bug# 14165508
487     IF OKL_VARIABLE_INTEREST_PVT.G_DAYS_IN_A_MONTH_CODE = '30' THEN
488 
489       l_payment_tbl := get_conversion_factor(p_khr_id     => p_contract_id,
490                                              p_start_date => p_start_date,
491                                              p_due_date   => p_due_date);
492     END IF;
493     --Bug# 14165508
494 
495     -- Bug# 13447258: Support Asset Level calculations
496     FOR l_assets_rec IN l_assets_csr(p_contract_id)
497     LOOP
498 
499       l_cle_id := l_assets_rec.id;
500       OKL_VARIABLE_INTEREST_PVT.g_vir_tbl.delete;
501       OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter := 0;
502 
503       OKL_VARIABLE_INTEREST_PVT.print_debug('Calculating Interest for Line ID : ' || l_cle_id);
504 
505       --Bug# 14165508
506       IF OKL_VARIABLE_INTEREST_PVT.G_DAYS_IN_A_MONTH_CODE = '30' THEN
507 
508         l_interest_calculated := 0;
509         OKL_VARIABLE_INTEREST_PVT.print_debug('l_payment_tbl COUNT '||l_payment_tbl.COUNT);
510         IF (l_payment_tbl.COUNT > 0) THEN
511           FOR i IN l_payment_tbl.FIRST..l_payment_tbl.LAST LOOP
512 
513             l_interest :=  OKL_VARIABLE_INTEREST_PVT.calculate_total_interest_due(
514                                         p_api_version       => 1.0,
515                                         p_init_msg_list     => OKL_API.G_FALSE,
516                                         x_return_status     => x_return_status,
517                                         x_msg_count         => x_msg_count,
518                                         x_msg_data          => x_msg_data,
519                                         p_contract_id       => p_contract_id,
520                                         p_currency_code     => p_currency_code,
521                                         p_start_date        => l_payment_tbl(i).period_start_date,
522                                         p_due_date          => l_payment_tbl(i).period_end_date,
523                                         p_line_id           => l_cle_id,
524                                         p_conversion_factor => l_payment_tbl(i).conversion_factor);
525             IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
526               OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to CALCULATE_TOTAL_INTEREST_DUE');
527               RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
528             ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
529               OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to CALCULATE_TOTAL_INTEREST_DUE');
530               RAISE var_int_catchup_failed;
531             END IF;
532 
533             l_interest_calculated := l_interest_calculated + NVL(l_interest,0);
534           END LOOP;
535         END IF;
536       ELSE
537 
538         l_interest_calculated :=  OKL_VARIABLE_INTEREST_PVT.calculate_total_interest_due(
539                                         p_api_version     => 1.0,
540                                         p_init_msg_list   => OKL_API.G_FALSE,
541                                         x_return_status   => x_return_status,
542                                         x_msg_count       => x_msg_count,
543                                         x_msg_data        => x_msg_data,
544                                         p_contract_id     => p_contract_id,
545                                         p_currency_code   => p_currency_code,
546                                         p_start_date      => p_start_date,
547                                         p_due_date        => p_due_date,
548                                         p_line_id         => l_cle_id);
549         IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
550           OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to CALCULATE_TOTAL_INTEREST_DUE');
551           RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
552         ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
553           OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to CALCULATE_TOTAL_INTEREST_DUE');
554           RAISE var_int_catchup_failed;
555         END IF;
556 
557       END IF;
558       --Bug# 14165508
559 
560       OKL_VARIABLE_INTEREST_PVT.print_debug('Interest Calculated : ' || l_interest_calculated);
561       l_total_interest_billed      := 0;
562 
563       IF (p_deal_type = 'LOAN') THEN
564          IF (p_principal_basis = 'ACTUAL') THEN
565             IF (p_rev_rec_method = 'STREAMS') THEN
566 
567              --fix for bug 5072399
568              IF (p_due_date = OKL_VARIABLE_INTEREST_PVT.G_CONTRACT_END_DATE) THEN
569 
570                --Bug# 13001695: Effective Date of Interest Catchup should be last day of previous period
571                --              regardless of SGT Arrears option
572 
573                l_stream_element_start_date := p_start_date;
574 
575                l_interest_billed := 0;
576                OPEN  int_pay_amt_end_date_csr (p_contract_id, p_start_date, 'INTEREST_PAYMENT',l_cle_id);
577                FETCH int_pay_amt_end_date_csr INTO l_interest_billed;
578                IF (int_pay_amt_end_date_csr % NOTFOUND) THEN
579                   l_interest_billed := 0;
580                END IF;
581                CLOSE int_pay_amt_end_date_csr;
582 
583                OKL_VARIABLE_INTEREST_PVT.print_debug('Interest Payment Billed : ' || l_interest_billed);
584 
585                IF (l_arrears_pay_dates_option = 'FIRST_DAY_OF_NEXT_PERIOD') THEN
586                  l_stream_element_start_date := p_start_date + 1;
587                END IF;
588                l_catchup_interest_billed := 0;
589                OPEN  int_pay_amt_end_date_csr (p_contract_id, l_stream_element_start_date, 'INTEREST_CATCHUP',l_cle_id);
590                FETCH int_pay_amt_end_date_csr INTO l_catchup_interest_billed;
591                IF (int_pay_amt_end_date_csr % NOTFOUND) THEN
592                   l_catchup_interest_billed := 0;
593                END IF;
594                CLOSE int_pay_amt_end_date_csr;
595 
596                OKL_VARIABLE_INTEREST_PVT.print_debug('Interest Catchup Billed : ' || l_catchup_interest_billed);
597 
598                l_total_interest_billed :=  l_interest_billed + l_catchup_interest_billed;
599 
600                --Bug# 13001695
601 
602              ELSE
603 
604                --Bug# 13001695: Effective Date of Interest Catchup should be last day of previous period
605                --              regardless of SGT Arrears option
606 
607                l_stream_element_start_date := p_start_date;
608                l_stream_element_end_date := p_due_date;
609 
610                l_interest_billed := 0;
611                OPEN  interest_payment_amount_csr (p_contract_id, p_start_date, p_due_date, 'INTEREST_PAYMENT',l_cle_id);
612                FETCH interest_payment_amount_csr INTO l_interest_billed;
613                IF (interest_payment_amount_csr % NOTFOUND) THEN
614                   l_interest_billed := 0;
615                END IF;
616                CLOSE interest_payment_amount_csr;
617 
618                OKL_VARIABLE_INTEREST_PVT.print_debug('Interest Payment Billed : ' || l_interest_billed);
619 
620                IF (l_arrears_pay_dates_option = 'FIRST_DAY_OF_NEXT_PERIOD') THEN
621                  l_stream_element_start_date := p_start_date + 1;
622                  l_stream_element_end_date := p_due_date + 1;
623                END IF;
624                l_catchup_interest_billed := 0;
625                OPEN  interest_payment_amount_csr (p_contract_id, l_stream_element_start_date, l_stream_element_end_date, 'INTEREST_CATCHUP',l_cle_id);
626                FETCH interest_payment_amount_csr INTO l_catchup_interest_billed;
627                IF (interest_payment_amount_csr % NOTFOUND) THEN
628                   l_catchup_interest_billed := 0;
629                END IF;
630                CLOSE interest_payment_amount_csr;
631 
632                OKL_VARIABLE_INTEREST_PVT.print_debug('Interest Catchup Billed : ' || l_catchup_interest_billed);
633 
634                l_total_interest_billed :=  l_interest_billed + l_catchup_interest_billed;
635 
636                --Bug# 13001695
637 
638              END IF;
639 
640              --Bug# 13043342: Reduce Principal Adjustment to calculate Net Interest Billed
641 
642              OKL_VARIABLE_INTEREST_PVT.print_debug('Interest Billed before considering Principal Catchup: ' || l_total_interest_billed);
643 
644              OPEN  principal_adjustment_csr (p_contract_id, p_start_date, p_due_date,l_cle_id);
645              FETCH principal_adjustment_csr INTO l_principal_adjusted;
646              IF (principal_adjustment_csr % NOTFOUND) THEN
647                l_principal_adjusted := 0;
648              END IF;
649              CLOSE principal_adjustment_csr;
650 
651              OKL_VARIABLE_INTEREST_PVT.print_debug('Principal Catchup: ' || l_principal_adjusted);
652 
653              IF (l_principal_adjusted > 0) THEN
654                l_total_interest_billed := l_total_interest_billed - l_principal_adjusted;
655              END IF;
656              --Bug# 13043342
657 
658              OKL_VARIABLE_INTEREST_PVT.print_debug('Net Interest Billed : ' || l_total_interest_billed);
659              l_invoice_amt := OKL_ACCOUNTING_UTIL.round_amount(l_interest_calculated - l_total_interest_billed, p_currency_code);
660 
661              OKL_VARIABLE_INTEREST_PVT.Print_debug ('Invoice Amount : '|| l_invoice_amt);
662 
663              --Bug# 12974796
664              OPEN contract_csr(p_khr_id =>  p_contract_id);
665              FETCH contract_csr INTO l_contract_rec;
666              CLOSE contract_csr;
667 
668              --Bug# 12974796
669              l_invoice_date := p_due_date;
670              IF (l_arrears_pay_dates_option = 'FIRST_DAY_OF_NEXT_PERIOD') THEN
671                l_invoice_date := p_due_date + 1;
672              END IF;
673              --Bug# 1301695
674 
675              IF (l_invoice_amt > 0) THEN
676 
677                 l_stream_type_purpose := 'INTEREST_CATCHUP';
678                 OKL_VR_DOCUMENT_PVT.Create_Stream_Invoice (
679                        p_api_version         => 1.0,
680                        p_init_msg_list       => p_init_msg_list,
681                        x_return_status       => x_return_status,
682                        x_msg_count           => x_msg_count,
683                        x_msg_data            => x_msg_data,
684                        p_contract_id         => p_contract_id,
685                        p_line_id             => l_cle_id,
686                        p_amount              => l_invoice_amt,
687                        --Bug# 13001695
688                        p_due_date            => l_invoice_date,
689                        p_stream_type_purpose => l_stream_type_purpose,
690                        p_create_invoice_flag => OKL_API.G_TRUE,
691                        p_parent_strm_element_id => NULL,
692                        x_invoice_id          => l_invoice_id,
693                        x_stream_element_id   => l_stream_element_id);
694                 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
695                   OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to CREATE_STREAM_INVOICE');
696                   RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
697                 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
698                   OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to CREATE_STREAM_INVOICE');
699                   RAISE var_int_catchup_failed;
700                 END IF;
701 
702                 i_vir_tbl := OKL_VARIABLE_INTEREST_PVT.g_vir_tbl;
703 
704                 OKL_VARIABLE_INTEREST_PVT.upd_vir_params_with_invoice (
705                     p_api_version   => 1.0,
706                     p_init_msg_list => OKL_API.G_TRUE,
707                     x_return_status => x_return_status,
708                     x_msg_count     => x_msg_count,
709                     x_msg_data      => x_msg_data,
710                     p_source_id     => l_invoice_id,
711                     p_catchup_amt   => l_invoice_amt,
712                     p_catchup_settlement_code => 'INTEREST_CATCHUP',
713                     p_vir_tbl      => i_vir_tbl,
714                     x_vir_tbl      => r_vir_tbl);
715                 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
716                   OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to UPD_VIR_PARAMS_WITH_INVOICE');
717                   RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
718                 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
719                   OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to UPD_VIR_PARAMS_WITH_INVOICE');
720                   RAISE var_int_catchup_failed;
721                 END IF;
722 
723                 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl := r_vir_tbl;
724 
725                 OKL_VR_DATA_HANDLER_PVT.insert_vir_params(
726                          p_api_version    => 1.0,
727                          p_init_msg_list  => OKL_API.G_TRUE,
728                          x_return_status  => x_return_status,
729                          x_msg_count      => x_msg_count,
730                          x_msg_data       => x_msg_data,
731                          p_vir_tbl        => OKL_VARIABLE_INTEREST_PVT.g_vir_tbl);
732                 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
733                   OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to POPULATE_VIR_PARAMS');
734                   RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
735                 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
736                   OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to POPULATE_VIR_PARAMS');
737                   RAISE var_int_catchup_failed;
738                 END IF;
739 
740              ELSIF (l_invoice_amt < 0) THEN
741 
742                 OKL_VARIABLE_INTEREST_PVT.print_debug (' catchup settlement code : '|| OKL_VARIABLE_INTEREST_PVT.G_CATCHUP_SETTLEMENT_CODE);
743 
744                 IF (OKL_VARIABLE_INTEREST_PVT.G_CATCHUP_SETTLEMENT_CODE = 'CREDIT') THEN
745 
746                    l_stream_type_purpose := 'INTEREST_CATCHUP';
747                    OKL_VR_DOCUMENT_PVT.Create_Stream_Invoice (
748                           p_api_version            => 1.0,
749                           p_init_msg_list          => p_init_msg_list,
750                           x_return_status          => x_return_status,
751                           x_msg_count              => x_msg_count,
752                           x_msg_data               => x_msg_data,
753                           p_contract_id            => p_contract_id,
754                           p_line_id                => l_cle_id,
755                           p_amount                 => l_invoice_amt,
756                           --Bug# 13001695
757                           p_due_date               => l_invoice_date,
758                           p_stream_type_purpose    => l_stream_type_purpose,
759                           p_create_invoice_flag    => OKL_API.G_TRUE,
760                           p_parent_strm_element_id => NULL,
761                           x_invoice_id             => l_invoice_id,
762                           x_stream_element_id      => l_stream_element_id);
763 
764                    IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
765                      OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to CREATE_STREAM_INVOICE');
766                      RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
767                    ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
768                      OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to CREATE_STREAM_INVOICE');
769                      RAISE var_int_catchup_failed;
770                    END IF;
771 
772                    i_vir_tbl := OKL_VARIABLE_INTEREST_PVT.g_vir_tbl;
773 
774                    OKL_VARIABLE_INTEREST_PVT.upd_vir_params_with_invoice (
775                        p_api_version   => 1.0,
776                        p_init_msg_list => OKL_API.G_TRUE,
777                        x_return_status => x_return_status,
778                        x_msg_count     => x_msg_count,
779                        x_msg_data      => x_msg_data,
780                        p_source_id     => l_invoice_id,
781                        p_catchup_amt   => l_invoice_amt,
782                        p_catchup_settlement_code => OKL_VARIABLE_INTEREST_PVT.G_CATCHUP_SETTLEMENT_CODE,
783                        p_vir_tbl       => i_vir_tbl,
784                        x_vir_tbl       => r_vir_tbl);
785                    IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
786                      OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to UPD_VIR_PARAMS_WITH_INVOICE');
787                      RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
788                    ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
789                      OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to UPD_VIR_PARAMS_WITH_INVOICE');
790                      RAISE var_int_catchup_failed;
791                    END IF;
792 
793                    OKL_VARIABLE_INTEREST_PVT.g_vir_tbl := r_vir_tbl;
794 
795                    OKL_VR_DATA_HANDLER_PVT.insert_vir_params(
796                             p_api_version    => 1.0,
797                             p_init_msg_list  => OKL_API.G_TRUE,
798                             x_return_status  => x_return_status,
799                             x_msg_count      => x_msg_count,
800                             x_msg_data       => x_msg_data,
801                             p_vir_tbl        => OKL_VARIABLE_INTEREST_PVT.g_vir_tbl);
802                    IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
803                      OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to POPULATE_VIR_PARAMS');
804                      RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
805                    ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
806                      OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to POPULATE_VIR_PARAMS');
807                      RAISE var_int_catchup_failed;
808                    END IF;
809 
810                 ELSIF (OKL_VARIABLE_INTEREST_PVT.G_CATCHUP_SETTLEMENT_CODE = 'ADJUST') THEN
811 
812                    l_invoice_amt          := -1 * l_invoice_amt;
813 
814                    OKL_VARIABLE_INTEREST_PVT.print_debug('Net Invoice amount after adjustment : '|| l_invoice_amt);
815 
816                    OKL_VARIABLE_INTEREST_PVT.print_debug('Creating PRINCIPAL_CATCHUP adjustment streams for asset id : ' || l_cle_id || ' for Amount :' || l_invoice_amt);
817                    l_stream_type_purpose := 'PRINCIPAL_CATCHUP';
818                    OKL_VR_DOCUMENT_PVT.Create_Stream_Invoice (
819                                 p_api_version            => 1.0,
820                                 p_init_msg_list          => p_init_msg_list,
821                                 x_return_status          => x_return_status,
822                                 x_msg_count              => x_msg_count,
823                                 x_msg_data               => x_msg_data,
824                                 p_contract_id            => p_contract_id,
825                                 p_line_id                => l_cle_id,
826                                 p_amount                 => l_invoice_amt,
827                                 p_due_date               => p_due_date,
828                                 p_stream_type_purpose    => l_stream_type_purpose,
829                                 p_create_invoice_flag    => OKL_API.G_FALSE,
830                                 p_process_flag           => 'PRINCIPAL_CATCHUP',
831                                 p_parent_strm_element_id => NULL,
832                                 x_invoice_id             => l_invoice_id,
833                                 x_stream_element_id      => l_stream_element_id);
834 
835                    IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
836                      OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to CREATE_STREAM_INVOICE');
837                      RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
838                    ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
839                      OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to CREATE_STREAM_INVOICE');
840                      RAISE var_int_catchup_failed;
841                    END IF;
842 
843                    OKL_VARIABLE_INTEREST_PVT.print_debug('Successfully created PRINCIPAL_CATCHUP adjustment streams for asset id : ' || l_cle_id || ' for Amount :' || l_invoice_amt);
844 
845                    i_vir_tbl := OKL_VARIABLE_INTEREST_PVT.g_vir_tbl;
846 
847                    OKL_VARIABLE_INTEREST_PVT.upd_vir_params_with_invoice (
848                           p_api_version   => 1.0,
849                           p_init_msg_list => OKL_API.G_TRUE,
850                           x_return_status => x_return_status,
851                           x_msg_count     => x_msg_count,
852                           x_msg_data      => x_msg_data,
853                           p_source_id     => l_stream_element_id,
854                           p_catchup_amt   => (-1 * l_invoice_amt),
855                           p_catchup_settlement_code => OKL_VARIABLE_INTEREST_PVT.G_CATCHUP_SETTLEMENT_CODE,
856                           p_vir_tbl       => i_vir_tbl,
857                           x_vir_tbl       => r_vir_tbl,
858                           --Bug# 12974796
859                           p_source_table  => 'OKL_STRM_ELEMENTS_V');
860                       IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
861                         OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to UPD_VIR_PARAMS_WITH_INVOICE');
862                          RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
863                       ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
864                         OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to UPD_VIR_PARAMS_WITH_INVOICE');
865                         RAISE var_int_catchup_failed;
866                       END IF;
867 
868                       OKL_VARIABLE_INTEREST_PVT.g_vir_tbl := r_vir_tbl;
869 
870                       OKL_VR_DATA_HANDLER_PVT.insert_vir_params(
871                                p_api_version    => 1.0,
872                                p_init_msg_list  => OKL_API.G_TRUE,
873                                x_return_status  => x_return_status,
874                                x_msg_count      => x_msg_count,
875                                x_msg_data       => x_msg_data,
876                                p_vir_tbl        => OKL_VARIABLE_INTEREST_PVT.g_vir_tbl);
877                       IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
878                         OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to POPULATE_VIR_PARAMS');
879                         RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
880                       ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
881                         OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to POPULATE_VIR_PARAMS');
882                         RAISE var_int_catchup_failed;
883                       END IF;
884                 --Bug# 12974796
885                 ELSIF (OKL_VARIABLE_INTEREST_PVT.G_CATCHUP_SETTLEMENT_CODE = 'NOT_ADJUST') THEN
886 
887                   i_vir_tbl := OKL_VARIABLE_INTEREST_PVT.g_vir_tbl;
888 
889                   OKL_VARIABLE_INTEREST_PVT.upd_vir_params_with_invoice (
890                     p_api_version   => 1.0,
891                     p_init_msg_list => OKL_API.G_TRUE,
892                     x_return_status => x_return_status,
893                     x_msg_count     => x_msg_count,
894                     x_msg_data      => x_msg_data,
895                     p_source_id     => NULL,
896                     p_catchup_amt   => l_invoice_amt,
897                     p_catchup_settlement_code => OKL_VARIABLE_INTEREST_PVT.G_CATCHUP_SETTLEMENT_CODE,
898                     p_vir_tbl       => i_vir_tbl,
899                     x_vir_tbl       => r_vir_tbl,
900                     --Bug# 12974796
901                     p_source_table  => 'OKL_VAR_INT_PARAMS');
902                   IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
903                     OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to UPD_VIR_PARAMS_WITH_INVOICE');
904                     RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
905                   ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
906                     OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to UPD_VIR_PARAMS_WITH_INVOICE');
907                     RAISE var_int_catchup_failed;
908                   END IF;
909 
910                   OKL_VARIABLE_INTEREST_PVT.g_vir_tbl := r_vir_tbl;
911 
912                   OKL_VR_DATA_HANDLER_PVT.insert_vir_params(
913                     p_api_version    => 1.0,
914                     p_init_msg_list  => OKL_API.G_TRUE,
915                     x_return_status  => x_return_status,
916                     x_msg_count      => x_msg_count,
917                     x_msg_data       => x_msg_data,
918                     p_vir_tbl        => OKL_VARIABLE_INTEREST_PVT.g_vir_tbl);
919                   IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
920                     OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to POPULATE_VIR_PARAMS');
921                     RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
922                   ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
923                     OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to POPULATE_VIR_PARAMS');
924                     RAISE var_int_catchup_failed;
925                   END IF;
926                 END IF;
927                 --Bug# 12974796
928              END IF;
929           END IF;
930         END IF;
931       END IF;
932     END LOOP;
933     -- Bug# 13447258: Support Asset Level calculations
934 
935     OKL_VR_DATA_HANDLER_PVT.insert_principal_bal_txn(
936              p_api_version    => p_api_version,
937              p_init_msg_list  => p_init_msg_list,
938              x_return_status  => x_return_status,
939              x_msg_count      => x_msg_count,
940              x_msg_data       => x_msg_data,
941              p_vpb_tbl        => OKL_VARIABLE_INTEREST_PVT.g_vpb_tbl);
942 
943     IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
944       OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to POPULATE_PRINCIPAL_BAL_TXN');
945       RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
946     ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
947       OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to POPULATE_PRINCIPAL_BAL_TXN');
948       RAISE var_int_catchup_failed;
949     END IF;
950 
951     OKL_VARIABLE_INTEREST_PVT.g_vpb_tbl.delete;
952     OKL_VARIABLE_INTEREST_PVT.g_vpb_tbl_counter := 0;
953 
954   EXCEPTION
955     WHEN var_int_catchup_failed THEN
956       OKL_VARIABLE_INTEREST_PVT.print_error_message ('Exception var_int_catchup_failed raised in procedure VARIABLE_INTEREST_CATCHUP');
957       x_return_status := OKL_API.G_RET_STS_ERROR;
958     WHEN OTHERS  THEN
959       OKL_VARIABLE_INTEREST_PVT.print_error_message ('Exception raised in procedure VARIABLE_INTEREST_CATCHUP');
960       Okl_Api.SET_MESSAGE(
961               p_app_name     => G_APP_NAME,
962               p_msg_name     => G_UNEXPECTED_ERROR,
963               p_token1       => G_SQLCODE_TOKEN,
964               p_token1_value => SQLCODE,
965               p_token2       => G_SQLERRM_TOKEN,
966               p_token2_value => SQLERRM);
967       x_return_status := OKL_API.G_RET_STS_ERROR;
968   END variable_interest_catchup;
969 
970 -----------------------------------------------------------------
971   PROCEDURE process_interest_catchup(
972         p_api_version            IN  NUMBER,
973         p_init_msg_list          IN  VARCHAR2,
974         x_return_status          OUT NOCOPY VARCHAR2,
975         x_msg_count              OUT NOCOPY NUMBER,
976         x_msg_data               OUT NOCOPY VARCHAR2,
977         p_contract_id            IN  NUMBER,
978         p_to_date                IN  DATE,
979         p_last_interest_cal_date IN  DATE,
980         p_print_lead_days        IN  NUMBER)
981 
982     IS
983     ------------------------------------------------------------
984     -- Declare variables required by APIs
985     ------------------------------------------------------------
986 
987     l_api_version                CONSTANT NUMBER := 1.0;
988     l_api_name                   CONSTANT VARCHAR2(30) := 'PROCESS_INTEREST_CATCHUP';
989 
990     l_last_interest_cal_date     DATE;
991     l_end_of_process             BOOLEAN := FALSE;
992     l_catchup_date               DATE;
993     l_int_cal_start_date    DATE;
994     l_period_start_date          DATE;
995     l_calculate_from_khr_start   VARCHAR2(10) := 'Y';
996 
997     process_int_catchup_failed   EXCEPTION;
998 
999   BEGIN
1000 
1001      l_end_of_process := FALSE;
1002      l_last_interest_cal_date := p_last_interest_cal_date;
1003      l_catchup_date := get_next_catchup_date(p_contract_id);
1004      l_calculate_from_khr_start := 'Y';
1005 
1006      OKL_VARIABLE_INTEREST_PVT.Print_Debug ('Principal Basis : '|| OKL_VARIABLE_INTEREST_PVT.G_PRINCIPAL_BASIS_CODE);
1007      OKL_VARIABLE_INTEREST_PVT.Print_Debug ('Catchup Date : '|| l_catchup_date);
1008 
1009      LOOP
1010        IF (l_catchup_date > NVL(l_last_interest_cal_date, l_catchup_date - 1) AND
1011          l_catchup_date  <= (trunc(sysdate)+ p_print_lead_days) AND
1012          --Bug# 13366221
1013          --l_catchup_date <= NVL(p_to_date, l_catchup_date)) THEN
1014          l_catchup_date <= NVL(p_to_date, l_catchup_date) + NVL(p_print_lead_days,0)) THEN
1015          l_end_of_process := FALSE;
1016        ELSE
1017          l_end_of_process := TRUE;
1018        END IF;
1019 
1020        EXIT WHEN l_end_of_process = TRUE;
1021 
1022        l_period_start_date := NVL(l_last_interest_cal_date + 1, OKL_VARIABLE_INTEREST_PVT.G_CONTRACT_START_DATE);
1023 
1024        l_calculate_from_khr_start := OKL_VARIABLE_INTEREST_PVT.calculate_from_khr_start_date(p_khr_id => p_contract_id,
1025                                                                                              p_from_date => l_period_start_date);
1026 
1027     	 IF (l_calculate_from_khr_start = 'Y') THEN
1028          l_int_cal_start_date := OKL_VARIABLE_INTEREST_PVT.G_CONTRACT_START_DATE;
1029          OKL_VARIABLE_INTEREST_PVT.print_debug('Catchup Cleanup interest calculation will start from contract start date : ' || l_int_cal_start_date);
1030        ELSE
1031          l_int_cal_start_date := l_period_start_date;
1032          OKL_VARIABLE_INTEREST_PVT.print_debug('Catchup Cleanup interest calculation will start from catchup period start date : ' || l_int_cal_start_date);
1033        END IF;
1034 
1035        VARIABLE_INTEREST_CATCHUP(
1036          p_api_version     => p_api_version,
1037          p_init_msg_list   => Okl_Api.G_TRUE,
1038          x_return_status   => x_return_status,
1039          x_msg_count       => x_msg_count,
1040          x_msg_data        => x_msg_data,
1041          p_contract_id     => p_contract_id,
1042          p_principal_basis => OKL_VARIABLE_INTEREST_PVT.G_PRINCIPAL_BASIS_CODE,
1043          p_rev_rec_method  => OKL_VARIABLE_INTEREST_PVT.G_REVENUE_RECOGNITION_METHOD,
1044          p_deal_type       => OKL_VARIABLE_INTEREST_PVT.G_DEAL_TYPE,
1045          p_currency_code   => OKL_VARIABLE_INTEREST_PVT.G_CURRENCY_CODE,
1046          p_start_date      => l_int_cal_start_date,
1047          p_due_date        => l_catchup_date);
1048 
1049          IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1050            OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to VARIABLE_INTEREST_CATCHUP');
1051            RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1052          ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1053             OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to VARIABLE_INTEREST_CATCHUP');
1054             RAISE process_int_catchup_failed;
1055          END IF;
1056 
1057          OKL_VARIABLE_INTEREST_PVT.print_debug ('Updating contract - id : '|| p_contract_id || 'with interest calculation date : '|| l_catchup_date);
1058 
1059          OKL_VR_DATA_HANDLER_PVT.UPDATE_LAST_INT_CAL_DATE(
1060                     p_api_version     => p_api_version,
1061                     p_init_msg_list   => Okl_Api.G_TRUE,
1062                     x_return_status   => x_return_status,
1063                     x_msg_count       => x_msg_count,
1064                     x_msg_data        => x_msg_data,
1065                     p_contract_id     => p_contract_id,
1066                     p_last_interest_cal_date => l_catchup_date);
1067          IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1068             RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1069          ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1070             RAISE OKL_API.G_EXCEPTION_ERROR;
1071          END IF;
1072 
1073          l_last_interest_cal_date := l_catchup_date;
1074 
1075          COMMIT;
1076 
1077          l_catchup_date := get_next_catchup_date(p_contract_id);
1078 
1079      END LOOP;
1080 
1081    EXCEPTION
1082 
1083      WHEN process_int_catchup_failed THEN
1084        OKL_VARIABLE_INTEREST_PVT.print_error_message ('Exception process_int_catchup_failed raised in procedure PROCESS_INTEREST_CATCHUP');
1085        x_return_status := OKL_API.G_RET_STS_ERROR;
1086      WHEN OTHERS THEN
1087        OKL_VARIABLE_INTEREST_PVT.print_error_message ('Exception raised in procedure PROCESS_INTEREST_CATCHUP');
1088        Okl_Api.SET_MESSAGE(
1089                          p_app_name     => G_APP_NAME,
1090                          p_msg_name     => G_UNEXPECTED_ERROR,
1091                          p_token1       => G_SQLCODE_TOKEN,
1092                          p_token1_value => SQLCODE,
1093                          p_token2       => G_SQLERRM_TOKEN,
1094                          p_token2_value => SQLERRM);
1095       x_return_status := OKL_API.G_RET_STS_ERROR;
1096    END process_interest_catchup;
1097 END;