[Home] [Help]
PACKAGE BODY: APPS.OKL_DAILY_INTEREST_CALC_PVT
Source
1 PACKAGE BODY OKL_DAILY_INTEREST_CALC_PVT AS
2 /* $Header: OKLRDICB.pls 120.23.12010000.4 2008/09/08 09:51:45 rpillay ship $ */
3 ------------------------------------------------------------------------------
4 -- Global Variables
5 ------------------------------------------------------------------------------
6 --G_DEBUG CONSTANT NUMBER := 1;
7 G_DEBUG CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
8 G_INIT_NUMBER CONSTANT NUMBER := -9999;
9 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
10
11 --Bug# 7277007
12 TYPE rpt_summary_rec_type IS RECORD (
13 total_receipt_amt_success NUMBER
14 ,total_receipt_amt_error NUMBER);
15
16 TYPE rpt_summary_tbl_type IS TABLE OF rpt_summary_rec_type INDEX BY VARCHAR2(15);
17
18 g_rpt_summary_tbl rpt_summary_tbl_type;
19 g_rpt_summary_tbl_counter okc_k_headers_b.currency_code%TYPE;
20
21 TYPE error_msg_tbl_type is TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
22
23 TYPE rpt_error_rec_type IS RECORD (
24 contract_number okc_k_headers_b.contract_number%TYPE
25 ,product_name okl_products.name%TYPE
26 ,interest_calc_basis fnd_lookup_values.lookup_code%TYPE
27 ,receipt_date DATE
28 ,receipt_amt NUMBER
29 ,error_msg_tbl error_msg_tbl_type);
30
31 TYPE rpt_error_tbl_type IS TABLE OF rpt_error_rec_type INDEX BY BINARY_INTEGER;
32 TYPE rpt_error_curr_tbl_type IS TABLE OF rpt_error_tbl_type INDEX BY VARCHAR2(15);
33
34 g_rpt_error_curr_tbl rpt_error_curr_tbl_type;
35 g_rpt_error_curr_tbl_ctr okc_k_headers_b.currency_code%TYPE;
36 g_rpt_error_tbl_counter NUMBER := 0;
37
38 TYPE rpt_success_rec_type IS RECORD (
39 contract_number okc_k_headers_b.contract_number%TYPE
40 ,principal_balance NUMBER
41 ,receipt_amt NUMBER
42 ,receipt_date DATE
43 ,int_start_date DATE
44 ,int_end_date DATE
45 ,daily_int_amt NUMBER
46 ,daily_prin_amt NUMBER
47 ,int_till_date_amt NUMBER
48 ,prin_till_date_amt NUMBER
49 ,daily_int_adj_amt NUMBER
50 ,daily_prin_adj_amt NUMBER);
51
52 TYPE rpt_success_tbl_type IS TABLE OF rpt_success_rec_type INDEX BY BINARY_INTEGER;
53 TYPE rpt_success_curr_tbl_type IS TABLE OF rpt_success_tbl_type INDEX BY VARCHAR2(15);
54
55 g_rpt_success_curr_tbl rpt_success_curr_tbl_type;
56 g_rpt_success_curr_tbl_ctr okc_k_headers_b.currency_code%TYPE;
57 g_rpt_success_tbl_counter NUMBER := 0;
58 --Bug# 7277007
59
60 ---------------------------------------------------------------------------
61 -- Procedures and Functions
62 ---------------------------------------------------------------------------
63
64 PROCEDURE print_line (p_message IN VARCHAR2) IS
65 BEGIN
66 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, p_message);
67 EXCEPTION
68 WHEN OTHERS THEN
69 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '** EXCEPTION IN print_line: '||SQLERRM);
70 END print_line;
71
72 ---------------------------------------------------------------------------
73
74 --Bug# 7277007
75 ------------------------------------------------------------------------------
76 -- PROCEDURE print_output
77 --
78 -- This procedure prints output report for Daily Interest Calculation
79 -- concurrent program
80 --
81 -- Calls:
82 -- Called By:
83 ------------------------------------------------------------------------------
84 PROCEDURE print_output(p_contract_number IN VARCHAR2) IS
85
86 l_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
87 l_print_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
88 l_print_contract_number1 OKC_K_HEADERS_B.contract_number%TYPE;
89 l_print_contract_number2 OKC_K_HEADERS_B.contract_number%TYPE;
90 l_org_id OKC_K_HEADERS_B.authoring_org_id%TYPE;
91 l_counter NUMBER;
92
93 CURSOR l_org_name_csr(p_org_id IN NUMBER) IS
94 SELECT name
95 FROM hr_all_organization_units
96 WHERE organization_id = p_org_id;
97
98 l_org_name_rec l_org_name_csr%ROWTYPE;
99 l_total_receipt_amt_error NUMBER;
100 l_total_receipt_amt_success NUMBER;
101
102 l_currency_code OKC_K_HEADERS_B.currency_code%TYPE;
103 BEGIN
104
105 l_org_id := MO_GLOBAL.get_current_org_id;
106 IF (l_org_id IS NOT NULL) THEN
107 OPEN l_org_name_csr(p_org_id => l_org_id);
108 FETCH l_org_name_csr INTO l_org_name_rec;
109 CLOSE l_org_name_csr;
110 END IF;
111
112 print_line('Daily Interest Calculation');
113 print_line('****************************************************************************************************');
114 print_line('Program Run Date: '||trunc(sysdate));
115 print_line('Operating Unit: '||l_org_name_rec.name);
116 print_line('Contract Number: '||p_contract_number);
117 print_line('****************************************************************************************************');
118 print_line(' ');
119 print_line(' ');
120 print_line('====================================================================================================');
121 print_line('Summary');
122 print_line(' ');
123 print_line(' _____________________________________________________');
124 print_line('| Receipt Application | Currency | Value |');
125 print_line('|_____________________________________________________|');
126
127 IF (g_rpt_summary_tbl.COUNT > 0) THEN
128 g_rpt_summary_tbl_counter := g_rpt_summary_tbl.FIRST;
129 LOOP
130
131 IF (NVL(g_rpt_summary_tbl(g_rpt_summary_tbl_counter).total_receipt_amt_success,0) <> 0) THEN
132
133 print_line('| ' || RPAD('Processed',21,' ')|| '|' ||
134 ' '|| RPAD(g_rpt_summary_tbl_counter,9,' ') || '|' ||
135 LPAD(OKL_ACCOUNTING_UTIL.format_amount(NVL(g_rpt_summary_tbl(g_rpt_summary_tbl_counter).total_receipt_amt_success,0),g_rpt_summary_tbl_counter),18,' ') || ' |');
136 END IF;
137
138 IF (NVL(g_rpt_summary_tbl(g_rpt_summary_tbl_counter).total_receipt_amt_error,0) <> 0) THEN
139
140 print_line('| ' || RPAD('Rejected',21,' ')|| '|' ||
141 ' '|| RPAD(g_rpt_summary_tbl_counter,9,' ') || '|' ||
142 LPAD(OKL_ACCOUNTING_UTIL.format_amount(NVL(g_rpt_summary_tbl(g_rpt_summary_tbl_counter).total_receipt_amt_error,0),g_rpt_summary_tbl_counter),18,' ') || ' |');
143 END IF;
144
145 print_line('|_____________________________________________________|');
146
147 EXIT WHEN g_rpt_summary_tbl_counter = g_rpt_summary_tbl.LAST;
148 g_rpt_summary_tbl_counter := g_rpt_summary_tbl.next(g_rpt_summary_tbl_counter);
149 END LOOP;
150 ELSE
151 print_line('|_____________________________________________________|');
152 END IF;
153 print_line(' ');
154 print_line(' ');
155 print_line('====================================================================================================');
156
157 IF (g_rpt_error_curr_tbl.COUNT > 0) THEN
158 print_line('Rejected Receipt Applications');
159 print_line('____________________________________________________________________________________________________');
160
161 g_rpt_error_curr_tbl_ctr := g_rpt_error_curr_tbl.FIRST;
162 LOOP
163
164 l_total_receipt_amt_error := 0;
165 FOR i IN g_rpt_error_curr_tbl(g_rpt_error_curr_tbl_ctr).FIRST..g_rpt_error_curr_tbl(g_rpt_error_curr_tbl_ctr).LAST LOOP
166
167 l_total_receipt_amt_error := l_total_receipt_amt_error + NVL(g_rpt_error_curr_tbl(g_rpt_error_curr_tbl_ctr)(i).receipt_amt,0);
168
169 print_line(' ');
170 print_line(RPAD('Contract Number: '||g_rpt_error_curr_tbl(g_rpt_error_curr_tbl_ctr)(i).contract_number,77,' ')||
171 'Product: '||g_rpt_error_curr_tbl(g_rpt_error_curr_tbl_ctr)(i).product_name);
172
173 IF LENGTH(g_rpt_error_curr_tbl(g_rpt_error_curr_tbl_ctr)(i).contract_number) > 60 THEN
174 print_line(LPAD(SUBSTR(g_rpt_error_curr_tbl(g_rpt_error_curr_tbl_ctr)(i).contract_number,61),' ',17));
175 END IF;
176
177 print_line(RPAD('Receipt Date: '||TO_CHAR(g_rpt_error_curr_tbl(g_rpt_error_curr_tbl_ctr)(i).receipt_date,'DD-MON-RRRR'),77,' ')||
178 'Total Receipt Amount: '||OKL_ACCOUNTING_UTIL.format_amount(NVL(g_rpt_error_curr_tbl(g_rpt_error_curr_tbl_ctr)(i).receipt_amt,0),g_rpt_error_curr_tbl_ctr));
179
180 print_line(RPAD('Interest Calculation Basis: '||g_rpt_error_curr_tbl(g_rpt_error_curr_tbl_ctr)(i).interest_calc_basis,77,' ')||
181 'Currency: '||g_rpt_error_curr_tbl_ctr);
182 print_line(' ');
183 print_line('Error Description: ');
184
185 IF (g_rpt_error_curr_tbl(g_rpt_error_curr_tbl_ctr)(i).error_msg_tbl.COUNT) > 0 THEN
186 FOR j IN g_rpt_error_curr_tbl(g_rpt_error_curr_tbl_ctr)(i).error_msg_tbl.FIRST..g_rpt_error_curr_tbl(g_rpt_error_curr_tbl_ctr)(i).error_msg_tbl.LAST LOOP
187
188 IF (g_rpt_error_curr_tbl(g_rpt_error_curr_tbl_ctr)(i).error_msg_tbl(j) IS NOT NULL) THEN
189 l_counter := 1;
190 WHILE l_counter <= LENGTH(g_rpt_error_curr_tbl(g_rpt_error_curr_tbl_ctr)(i).error_msg_tbl(j))
191 LOOP
192 print_line(SUBSTR(g_rpt_error_curr_tbl(g_rpt_error_curr_tbl_ctr)(i).error_msg_tbl(j),l_counter,100));
193 l_counter := l_counter + 100;
194 END LOOP;
195 END IF;
196 END LOOP;
197 END IF;
198
199 print_line('____________________________________________________________________________________________________');
200
201 END LOOP;
202
203 print_line(' ');
204 print_line('Total Amount Rejected '||g_rpt_error_curr_tbl_ctr||': '||OKL_ACCOUNTING_UTIL.format_amount(l_total_receipt_amt_error,g_rpt_error_curr_tbl_ctr));
205 print_line(' ');
206 print_line('____________________________________________________________________________________________________');
207
208 EXIT WHEN g_rpt_error_curr_tbl_ctr = g_rpt_error_curr_tbl.LAST;
209 g_rpt_error_curr_tbl_ctr := g_rpt_error_curr_tbl.next(g_rpt_error_curr_tbl_ctr);
210 END LOOP;
211
212 print_line(' ');
213 print_line(' ');
214 print_line('====================================================================================================');
215 END IF;
216
217 IF (g_rpt_success_curr_tbl.COUNT > 0) THEN
218 print_line('Processed Receipt Applications');
219 print_line(' ');
220 print_line(' '|| RPAD('_',257,'_'));
221
222 print_line('|' || RPAD('Contract Number',40,' ') ||
223 ' |' || RPAD('Currency',15,' ') ||
224 ' |' || LPAD('Principal Balance',18,' ') ||
225 ' |' || LPAD('Receipt Amount',18,' ') ||
226 ' |' || RPAD('Receipt',11,' ') ||
227 ' |' || RPAD('Interest',11,' ') ||
228 ' |' || RPAD('Interest',11,' ') ||
229 ' |' || LPAD('Calculated Daily',18,' ') ||
230 ' |' || LPAD('Calculated',18,' ') ||
231 ' |' || LPAD('Paid Interest',18,' ') ||
232 ' |' || LPAD('Paid Principal',18,' ') ||
233 ' |' || LPAD('Interest',18,' ') ||
234 ' |' || LPAD('Principal',18,' ') ||
235 ' |');
236
237 print_line('|' || RPAD(' ',40,' ') ||
238 ' |' || RPAD(' ',15,' ') ||
239 ' |' || LPAD(' ',18,' ') ||
240 ' |' || LPAD(' ',18,' ') ||
241 ' |' || RPAD('Date',11,' ') ||
242 ' |' || RPAD('Start Date',11,' ') ||
243 ' |' || RPAD('End Date',11,' ') ||
244 ' |' || LPAD('Interest',18,' ') ||
245 ' |' || LPAD('Principal',18,' ') ||
246 ' |' || LPAD(' ',18,' ') ||
247 ' |' || LPAD(' ',18,' ') ||
248 ' |' || LPAD('Adjustment',18,' ') ||
249 ' |' || LPAD('Adjustment',18,' ') ||
250 ' |');
251
252 print_line('|' || RPAD('_',257,'_') || '|');
253
254 g_rpt_success_curr_tbl_ctr := g_rpt_success_curr_tbl.FIRST;
255 LOOP
256
257 l_total_receipt_amt_success := 0;
258 FOR i IN g_rpt_success_curr_tbl(g_rpt_success_curr_tbl_ctr).FIRST..g_rpt_success_curr_tbl(g_rpt_success_curr_tbl_ctr).LAST LOOP
259
260 l_total_receipt_amt_success := l_total_receipt_amt_success + NVL(g_rpt_success_curr_tbl(g_rpt_success_curr_tbl_ctr)(i).receipt_amt,0);
261
262 IF l_contract_number IS NULL THEN
263 l_contract_number := g_rpt_success_curr_tbl(g_rpt_success_curr_tbl_ctr)(i).contract_number;
264 l_print_contract_number := g_rpt_success_curr_tbl(g_rpt_success_curr_tbl_ctr)(i).contract_number;
265 l_currency_code := g_rpt_success_curr_tbl_ctr;
266
267 ELSIF l_contract_number <> g_rpt_success_curr_tbl(g_rpt_success_curr_tbl_ctr)(i).contract_number THEN
268 print_line('|' || RPAD('_',257,'_') || '|');
269 l_contract_number := g_rpt_success_curr_tbl(g_rpt_success_curr_tbl_ctr)(i).contract_number;
270 l_print_contract_number := g_rpt_success_curr_tbl(g_rpt_success_curr_tbl_ctr)(i).contract_number;
271 l_currency_code := g_rpt_success_curr_tbl_ctr;
272 ELSE
273 l_print_contract_number := ' ';
274 l_currency_code := ' ';
275 END IF;
276
277 l_print_contract_number1 := NULL;
278 l_print_contract_number2 := NULL;
279 IF LENGTH(l_print_contract_number) > 40 THEN
280 l_print_contract_number1 := SUBSTR(l_print_contract_number,41,40);
281 l_print_contract_number2 := SUBSTR(l_print_contract_number,81,40);
282 END IF;
283
284 print_line('|' || RPAD(l_print_contract_number,40,' ') ||
285 ' |' || RPAD(l_currency_code,15,' ') ||
286 ' |' || LPAD(OKL_ACCOUNTING_UTIL.format_amount(NVL(g_rpt_success_curr_tbl(g_rpt_success_curr_tbl_ctr)(i).principal_balance,0),g_rpt_success_curr_tbl_ctr),18,' ') ||
287 ' |' || LPAD(OKL_ACCOUNTING_UTIL.format_amount(NVL(g_rpt_success_curr_tbl(g_rpt_success_curr_tbl_ctr)(i).receipt_amt,0),g_rpt_success_curr_tbl_ctr),18,' ') ||
288 ' |' || RPAD(TO_CHAR(g_rpt_success_curr_tbl(g_rpt_success_curr_tbl_ctr)(i).receipt_date,'DD-MON-RRRR'),11,' ') ||
289 ' |' || RPAD(TO_CHAR(g_rpt_success_curr_tbl(g_rpt_success_curr_tbl_ctr)(i).int_start_date,'DD-MON-RRRR'),11,' ') ||
290 ' |' || RPAD(TO_CHAR(g_rpt_success_curr_tbl(g_rpt_success_curr_tbl_ctr)(i).int_end_date,'DD-MON-RRRR'),11,' ') ||
291 ' |' || LPAD(OKL_ACCOUNTING_UTIL.format_amount(NVL(g_rpt_success_curr_tbl(g_rpt_success_curr_tbl_ctr)(i).daily_int_amt,0),g_rpt_success_curr_tbl_ctr),18,' ') ||
292 ' |' || LPAD(OKL_ACCOUNTING_UTIL.format_amount(NVL(g_rpt_success_curr_tbl(g_rpt_success_curr_tbl_ctr)(i).daily_prin_amt,0),g_rpt_success_curr_tbl_ctr),18,' ') ||
293 ' |' || LPAD(OKL_ACCOUNTING_UTIL.format_amount(NVL(g_rpt_success_curr_tbl(g_rpt_success_curr_tbl_ctr)(i).int_till_date_amt,0),g_rpt_success_curr_tbl_ctr),18,' ') ||
294 ' |' || LPAD(OKL_ACCOUNTING_UTIL.format_amount(NVL(g_rpt_success_curr_tbl(g_rpt_success_curr_tbl_ctr)(i).prin_till_date_amt,0),g_rpt_success_curr_tbl_ctr),18,' ') ||
295 ' |' || LPAD(OKL_ACCOUNTING_UTIL.format_amount(NVL(g_rpt_success_curr_tbl(g_rpt_success_curr_tbl_ctr)(i).daily_int_adj_amt,0),g_rpt_success_curr_tbl_ctr),18,' ') ||
296 ' |' || LPAD(OKL_ACCOUNTING_UTIL.format_amount(NVL(g_rpt_success_curr_tbl(g_rpt_success_curr_tbl_ctr)(i).daily_prin_adj_amt,0),g_rpt_success_curr_tbl_ctr),18,' ') ||
297 ' |');
298
299 IF l_print_contract_number1 IS NOT NULL THEN
300 print_line('|' || RPAD(l_print_contract_number1,40,' ') ||
301 ' |' || RPAD(' ',15,' ') ||
302 ' |' || LPAD(' ',18,' ') ||
303 ' |' || LPAD(' ',18,' ') ||
304 ' |' || RPAD(' ',11,' ') ||
305 ' |' || RPAD(' ',11,' ') ||
306 ' |' || RPAD(' ',11,' ') ||
307 ' |' || LPAD(' ',18,' ') ||
308 ' |' || LPAD(' ',18,' ') ||
309 ' |' || LPAD(' ',18,' ') ||
310 ' |' || LPAD(' ',18,' ') ||
311 ' |' || LPAD(' ',18,' ') ||
312 ' |' || LPAD(' ',18,' ') ||
313 ' |');
314 END IF;
315
316 IF l_print_contract_number2 IS NOT NULL THEN
317 print_line('|' || RPAD(l_print_contract_number2,40,' ') ||
318 ' |' || RPAD(' ',15,' ') ||
319 ' |' || LPAD(' ',18,' ') ||
320 ' |' || LPAD(' ',18,' ') ||
321 ' |' || RPAD(' ',11,' ') ||
322 ' |' || RPAD(' ',11,' ') ||
323 ' |' || RPAD(' ',11,' ') ||
324 ' |' || LPAD(' ',18,' ') ||
325 ' |' || LPAD(' ',18,' ') ||
326 ' |' || LPAD(' ',18,' ') ||
327 ' |' || LPAD(' ',18,' ') ||
328 ' |' || LPAD(' ',18,' ') ||
329 ' |' || LPAD(' ',18,' ') ||
330 ' |');
331 END IF;
332
333 IF (i = g_rpt_success_curr_tbl(g_rpt_success_curr_tbl_ctr).LAST) THEN
334 print_line('|' || RPAD('_',257,'_') || '|');
335 END IF;
336
337 END LOOP;
338
339 print_line('|' || LPAD('Total',77,' ') ||
340 ' |' || LPAD(OKL_ACCOUNTING_UTIL.format_amount(l_total_receipt_amt_success,g_rpt_success_curr_tbl_ctr),18,' ') ||
341 ' |' || RPAD(' ',157,' ') ||
342 ' |');
343
344 IF (g_rpt_success_curr_tbl_ctr = g_rpt_success_curr_tbl.LAST) THEN
345 print_line('|' || RPAD('_',257,'_') || '|');
346 EXIT;
347 END IF;
348
349 g_rpt_success_curr_tbl_ctr := g_rpt_success_curr_tbl.next(g_rpt_success_curr_tbl_ctr);
350 END LOOP;
351
352 print_line(' ');
353 print_line(' ');
354 print_line('====================================================================================================');
355 END IF;
356
357 EXCEPTION
358 WHEN OTHERS THEN
359 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '** EXCEPTION IN print_output: '||SQLERRM);
360 END print_output;
361
362
363 ------------------------------------------------------------------------------
364 -- PROCEDURE debug_message
365 --
366 -- This procedure prints debug message depending on DEBUG flag
367 --
368 -- Calls:
369 -- Called By:
370 ------------------------------------------------------------------------------
371
372 PROCEDURE print_debug (p_message IN VARCHAR2) IS
373 BEGIN
374 -- IF ( G_DEBUG = 'Y' ) THEN
375 FND_FILE.PUT_LINE (FND_FILE.LOG, p_message);
376 OKL_DEBUG_PUB.logmessage(p_message, 25);
377 --dbms_output.put_line (p_message);
378 -- END IF;
379 EXCEPTION
380 WHEN OTHERS THEN
381 FND_FILE.PUT_LINE (FND_FILE.LOG, '** EXCEPTION IN print_line: '||SQLERRM);
382 END print_debug;
383
384 ------------------------------------------------------------------------------
385
386 PROCEDURE receipt_date_range(
387 p_api_version IN NUMBER,
388 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
389 x_return_status OUT NOCOPY VARCHAR2,
390 x_msg_count OUT NOCOPY NUMBER,
391 x_msg_data OUT NOCOPY VARCHAR2,
392 p_contract_id IN NUMBER,
393 p_start_date IN DATE,
394 p_due_date IN DATE,
395 x_principal_balance OUT NOCOPY NUMBER,
396 x_receipt_tbl OUT NOCOPY receipt_tbl_type) IS
397
398 l_api_name CONSTANT VARCHAR2(30) := 'RECEIPT_DATE_RANGE';
399 l_api_version CONSTANT NUMBER := 1.0;
400 l_principal_basis OKL_K_RATE_PARAMS.principal_basis_code%TYPE;
401 l_effective_date DATE := SYSDATE;
402 l_principal_balance_tbl principal_balance_tbl_typ ;
403 l_contract_start_date DATE;
404 l_start_date DATE;
405 l_counter NUMBER := 0;
406 l_receipt_tbl receipt_tbl_type;
407 l_deal_type OKL_K_HEADERS_FULL_V.deal_type%TYPE;
408 l_stream_element_date DATE;
409 l_principal_balance NUMBER;
410
411 l_receipt_tbl_temp receipt_tbl_type;
412 l_counter_temp NUMBER := 0;
413 l_temp_receipt_date DATE := NULL;
414
415 Cursor contract_csr (p_contract_id NUMBER) IS
416 SELECT start_date, deal_type
417 FROM okl_k_headers_full_v
418 WHERE id = p_contract_id;
419
420 -- Begin - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
421 --Bug# 6742532: Modified cursor to fetch correct receipt amount
422 -- for contracts with multiple asset lines
423
424 --Bug# 6965021: Modified cursor to fetch correct receipt amount
425 -- for cases where invoices contain lines
426 -- from multiple contracts
427
428 CURSOR receipt_details_loan_csr (p_contract_id NUMBER,
429 p_start_date DATE,
430 p_due_date DATE) IS
431 SELECT cra.receipt_date receipt_date
432 ,SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) receipt_amount --4884843, 4872370
433 FROM okl_txd_ar_ln_dtls_b tld,
434 ra_customer_trx_lines_all ractrl,
435 okl_txl_ar_inv_lns_b til,
436 okl_trx_ar_invoices_b tai,
437 ar_payment_schedules_all aps,
438 ar_receivable_applications_all raa,
439 ar_cash_receipts_all cra,
440 okl_strm_type_b sty_ln_pmt,
441 ar_distributions_all ad
442 WHERE tai.trx_status_code = 'PROCESSED'
443 AND tai.khr_id = p_contract_id
444 AND tld.khr_id = p_contract_id
445 AND ractrl.customer_trx_id = aps.customer_trx_id
446 AND raa.applied_customer_trx_id = aps.customer_trx_id
447 AND aps.class = 'INV'
448 AND raa.application_type IN ('CASH','CM')
449 AND raa.status = 'APP'
450 AND raa.display = 'Y'
451 AND cra.receipt_date <= NVL(p_due_date, cra.receipt_date)
452 AND raa.cash_receipt_id = cra.cash_receipt_id
453 AND tld.sty_id = sty_ln_pmt.id
454 AND sty_ln_pmt.stream_type_purpose IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT', 'UNSCHEDULED_LOAN_PAYMENT', 'AMBCOC' )
455 AND to_char(tld.id) = ractrl.interface_line_attribute14
456 AND tld.til_id_details = til.id
457 AND til.tai_id = tai.id
458 AND raa.receivable_application_id = ad.source_id
459 AND ad.source_table = 'RA'
460 AND ad.ref_customer_trx_Line_Id = ractrl.customer_trx_line_id
461 GROUP BY receipt_date
462 UNION ALL
463 SELECT cra.receipt_date receipt_date
464 ,SUM(raa.line_applied) receipt_amount --4884843, 4872370
465 FROM okl_txd_ar_ln_dtls_b tld,
466 ra_customer_trx_lines_all ractrl,
467 okl_txl_ar_inv_lns_b til,
468 okl_trx_ar_invoices_b tai,
469 ar_payment_schedules_all aps,
470 ar_receivable_applications_all raa,
471 ar_cash_receipts_all cra,
472 okl_strm_type_b sty_ln_pmt
473 WHERE tai.trx_status_code = 'PROCESSED'
474 AND tai.khr_id = p_contract_id
475 AND tld.khr_id = p_contract_id
476 AND ractrl.customer_trx_id = aps.customer_trx_id
477 AND raa.applied_customer_trx_id = aps.customer_trx_id
478 AND aps.class = 'INV'
479 AND raa.application_type IN ('CASH','CM')
480 AND raa.status = 'APP'
481 AND raa.display = 'Y'
482 AND cra.receipt_date <= NVL(p_due_date, cra.receipt_date)
483 AND raa.cash_receipt_id = cra.cash_receipt_id
484 AND tld.sty_id = sty_ln_pmt.id
485 AND sty_ln_pmt.stream_type_purpose IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT','UNSCHEDULED_LOAN_PAYMENT', 'AMBCOC')
486 AND to_char(tld.id) = ractrl.interface_line_attribute14
487 AND tld.til_id_details = til.id
488 AND til.tai_id = tai.id
489 AND EXISTS (SELECT 1
490 FROM ar_distributions_all ad
491 WHERE raa.receivable_application_id = ad.source_id
492 AND ad.source_table = 'RA'
493 AND ad.source_type = 'REC'
494 AND ad.ref_customer_trx_Line_Id IS NULL)
495 GROUP BY receipt_date
496 ORDER BY receipt_date asc;
497
498 Cursor receipt_details_rloan_csr (p_contract_id NUMBER,
499 p_start_date DATE,
500 p_due_date DATE) IS
501 SELECT cra.receipt_date receipt_date
502 ,SUM(line_applied) receipt_amount --4884843, 4872370
503 FROM okl_txd_ar_ln_dtls_b tld,
504 ra_customer_trx_lines_all ractrl,
505 okl_txl_ar_inv_lns_b til,
506 okl_trx_ar_invoices_b tai,
507 ar_payment_schedules_all aps,
508 ar_receivable_applications_all raa,
509 ar_cash_receipts_all cra,
510 okl_strm_type_b sty
511 WHERE tai.trx_status_code = 'PROCESSED'
512 AND tai.khr_id = p_contract_id
513 AND tld.khr_id = p_contract_id
514 AND ractrl.customer_trx_id = aps.customer_trx_id
515 AND raa.applied_customer_trx_id = aps.customer_trx_id
516 AND aps.class = 'INV'
517 AND raa.application_type IN ('CASH','CM')
518 AND raa.status = 'APP'
519 AND raa.display = 'Y'
520 AND cra.receipt_date <= NVL(p_due_date, cra.receipt_date)
521 AND raa.cash_receipt_id = cra.cash_receipt_id
522 AND tld.sty_id = sty.id
523 AND sty.stream_type_purpose in ('UNSCHEDULED_LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT')
524 AND to_char(tld.id) = ractrl.interface_line_attribute14
525 AND tld.til_id_details = til.id
526 AND til.tai_id = tai.id
527 GROUP BY receipt_date
528 ORDER BY receipt_date asc;
529 -- End - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
530
531 BEGIN
532 x_return_status := OKL_API.G_RET_STS_SUCCESS;
533 l_start_date := p_start_date;
534
535 OPEN contract_csr (p_contract_id);
536 FETCH contract_csr INTO l_contract_start_date, l_deal_type;
537 IF (contract_csr%NOTFOUND) THEN
538 CLOSE contract_csr;
539 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
540 END IF;
541 CLOSE contract_csr;
542
543 l_counter := 0;
544 IF (l_deal_type = 'LOAN') THEN
545 -- Derive Principal Balance
546 Okl_Execute_Formula_Pub.EXECUTE(p_api_version => 1.0,
547 p_init_msg_list => OKL_API.G_TRUE,
548 x_return_status => x_return_status,
549 x_msg_count => x_msg_count,
550 x_msg_data => x_msg_data,
551 p_formula_name => 'CONTRACT_FINANCED_AMOUNT',
552 p_contract_id => p_contract_id,
553 p_line_id => NULL,
554 x_value => l_principal_balance
555 );
556 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
557 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
558 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
559 RAISE Okl_Api.G_EXCEPTION_ERROR;
560 END IF;
561
562 FOR current_receipt in receipt_details_loan_csr (p_contract_id, l_start_date, p_due_date)
563 LOOP
564 l_counter := l_counter + 1;
565 l_receipt_tbl(l_counter).khr_id := p_contract_id;
566 l_receipt_tbl(l_counter).receipt_date := current_receipt.receipt_date;
567 l_receipt_tbl(l_counter).receipt_amount := current_receipt.receipt_amount;
568 END LOOP;
569
570 --x_receipt_tbl := l_receipt_tbl;
571 -- 4957212
572 x_principal_balance := l_principal_balance;
573
574 ELSIF (l_deal_type = 'LOAN-REVOLVING') THEN
575 FOR current_receipt in receipt_details_rloan_csr (p_contract_id, l_start_date, p_due_date)
576 LOOP
577 l_counter := l_counter + 1;
578 l_receipt_tbl(l_counter).khr_id := p_contract_id;
579 l_receipt_tbl(l_counter).receipt_date := current_receipt.receipt_date;
580 l_receipt_tbl(l_counter).receipt_amount := current_receipt.receipt_amount;
581 END LOOP;
582
583 --x_receipt_tbl := l_receipt_tbl;
584 -- 4957212
585 x_principal_balance := NULL;
586 END IF;
587
588 FOR l_counter IN 1..l_receipt_tbl.count LOOP
589 --receipt date less than khr start date
590 IF (l_receipt_tbl(l_counter).receipt_date < l_contract_start_date) THEN
591 IF (l_temp_receipt_date IS NOT NULL
592 AND l_receipt_tbl_temp(l_counter_temp).receipt_date = l_receipt_tbl(l_counter).receipt_date) THEN
593 --if a record exist in l_receipt_tbl_temp and the current record has the same date as the current
594 --record in l_receipt_tbl then add the receipt amount in l_receipt_tbl to the amount in l_receipt_tbl_temp
595 l_receipt_tbl_temp(l_counter_temp).receipt_amount := l_receipt_tbl_temp(l_counter_temp).receipt_amount +
596 l_receipt_tbl(l_counter).receipt_amount;
597
598 ELSE
599 --create a new rec in l_receipt_tbl_temp and set the receipt date to the khr start date
600 l_counter_temp := l_counter_temp + 1;
601 l_receipt_tbl_temp(l_counter_temp).khr_id := l_receipt_tbl(l_counter).khr_id ;
602 l_receipt_tbl_temp(l_counter_temp).receipt_date := l_contract_start_date ;
603 l_receipt_tbl_temp(l_counter_temp).receipt_amount := l_receipt_tbl(l_counter).receipt_amount ;
604 l_temp_receipt_date := l_receipt_tbl_temp(l_counter_temp).receipt_date;
605 END IF;
606 ELSE
607 --transfer the from l_receipt_tbl to l_receipt_tbl_temp
608 l_counter_temp := l_counter_temp + 1;
609 l_receipt_tbl_temp(l_counter_temp).khr_id := l_receipt_tbl(l_counter).khr_id;
610 l_receipt_tbl_temp(l_counter_temp).receipt_date := l_receipt_tbl(l_counter).receipt_date;
611 l_receipt_tbl_temp(l_counter_temp).receipt_amount := l_receipt_tbl(l_counter).receipt_amount ;
612 l_temp_receipt_date := l_receipt_tbl_temp(l_counter_temp).receipt_date;
613 END IF;
614 END LOOP;
615
616 x_receipt_tbl := l_receipt_tbl_temp;
617 -- 4957212
618 --x_principal_balance := NULL;
619
620 EXCEPTION
621
622 WHEN OTHERS THEN
623 Okl_Api.SET_MESSAGE(
624 p_app_name => G_APP_NAME,
625 p_msg_name => G_UNEXPECTED_ERROR,
626 p_token1 => G_SQLCODE_TOKEN,
627 p_token1_value => SQLCODE,
628 p_token2 => G_SQLERRM_TOKEN,
629 p_token2_value => SQLERRM);
630
631 END receipt_date_range;
632
633 ---------------------------------------------------------------------------
634
635 PROCEDURE daily_interest(p_api_version IN NUMBER
636 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
637 ,x_return_status OUT NOCOPY VARCHAR2
638 ,x_msg_count OUT NOCOPY NUMBER
639 ,x_msg_data OUT NOCOPY VARCHAR2
640 ,p_khr_id IN NUMBER DEFAULT NULL) IS
641
642 ------------------------------------------------------------
643 -- Declare Variables required by APIs
644 ------------------------------------------------------------
645 l_api_version CONSTANT NUMBER := 1;
646 l_api_name CONSTANT VARCHAR2(30) := 'DAILY_INTEREST';
647 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
648
649 lx_principal_balance NUMBER := 0;
650 lx_receipt_tbl receipt_tbl_type;
651 l_receipt_tbl_row NUMBER;
652 l_prev_receipt_tbl_row NUMBER;
653 l_khr_start_date DATE;
654 l_range_start_date DATE;
655 l_range_end_date DATE;
656 l_error_flag BOOLEAN := FALSE;
657 l_start_bal NUMBER := 0;
658 l_cal_int_amt NUMBER := 0;
659 l_daily_int_amt NUMBER := 0;
660 l_daily_prin_amt NUMBER := 0;
661 l_prev_daily_int_amt NUMBER := 0;
662 l_prev_daily_prin_amt NUMBER := 0;
663 l_payment_amount NUMBER := 0;
664 l_principal_paid NUMBER := 0;
665 l_excess_principal NUMBER := 0;
666 l_excess_principal_paid NUMBER := 0;
667 l_invoice_id NUMBER;
668
669 --Bug# 7277007
670 l_daily_int_calc_amt NUMBER;
671 l_daily_prin_calc_amt NUMBER;
672 lx_msg_index_out NUMBER;
673 lx_msg_data VARCHAR2(2000);
674 l_receipt_amt_success NUMBER;
675 l_receipt_amt_error NUMBER;
676 --Bug# 7277007
677
678 --------------------------------------------------------------------
679 --Declare Cursors
680 --------------------------------------------------------------------
681 --get contract details
682 Cursor c_khr_csr(cp_khr_id IN NUMBER) IS select khr.id khr_id
683 , khr.contract_number
684 , khr.start_date
685 , khr.deal_type
686 , khr.currency_code
687 , ppm.revenue_recognition_method
688 , ppm.interest_calculation_basis
689 , ppm.name product_name
690 from okl_k_headers_full_v khr
691 ,okl_product_parameters_v ppm
692 where khr.pdt_id = ppm.id
693 and ppm.revenue_recognition_method = 'ACTUAL'
694 and khr.id = NVL(cp_khr_id, khr.id)
695 order by khr.contract_number;
696
697 --get principal paid as of a given date
698 Cursor c_principal_paid_csr(cp_khr_id IN NUMBER, cp_from_date IN DATE) IS select nvl(sum(sel.amount), 0) principal_paid
699 from okl_streams_v stm
700 , okl_strm_type_v sty
701 , okl_strm_elements_v sel
702 where stm.khr_id = cp_khr_id
703 and stm.sty_id = sty.id
704 and sty.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
705 and stm.id = sel.stm_id
706 and sel.stream_element_date <= trunc(cp_from_date);
707
708 --get excess principal paid
709 Cursor c_excess_principal_paid_csr(cp_khr_id IN NUMBER) IS select nvl(sum(sel.amount), 0) excess_principal_paid
710 from okl_streams_v stm
711 , okl_strm_type_v sty
712 , okl_strm_elements_v sel
713 where stm.khr_id = cp_khr_id
714 and stm.sty_id = sty.id
715 and sty.stream_type_purpose = 'EXCESS_LOAN_PAYMENT_PAID'
716 and stm.id = sel.stm_id;
717
718 --get asset termination value between range dates
719 Cursor c_asset_term_val_csr(cp_khr_id IN NUMBER, cp_from_date IN DATE, cp_to_date IN DATE) IS
720 select trunc(cbl.termination_date) term_date
721 , nvl(sum(cbl.termination_value_amt), 0) term_value
722 from okl_contract_balances cbl
723 where cbl.khr_id = cp_khr_id
724 and cbl.termination_date between cp_from_date and cp_to_date
725 group by trunc(cbl.termination_date);
726
727 --get payments for revolving loans for receipt date range
728 -- sjalasut, modified the cursor to include okl_txl_ap_inv_lns_all_b and
729 -- okl_cnsld_ap_invs_all. khr_id now will be stored at the internal transaction
730 -- line table and consolidated invoice id is also stored at the internal
731 -- transaction lines table (okl_txl_ap_inv_lns_all_b)
732 -- changes made as part of OKLR12B disbursements project
733 Cursor c_borrower_payment_csr(cp_khr_id IN NUMBER, cp_from_date IN DATE, cp_to_date IN DATE) IS
734 select iph.check_date borrower_payment_date
735 , sum(iph.amount) borrower_payment
736 from ap_invoices_all ap_inv
737 , okl_trx_ap_invoices_v okl_inv
738 , ap_invoice_payment_history_v iph
739 , okl_txl_ap_inv_lns_all_b okl_inv_ln
740 , okl_cnsld_Ap_invs_all okl_cnsld
741 , fnd_application fnd_app
742 where okl_inv.id = okl_inv_ln.tap_id
743 and okl_inv_ln.khr_id = cp_khr_id
744 and ap_inv.application_id = fnd_app.application_id
745 and fnd_app.application_short_name = 'OKL'
746 and okl_inv_ln.cnsld_ap_inv_id = okl_cnsld.cnsld_ap_inv_id
747 and okl_cnsld.cnsld_ap_inv_id = to_number(ap_inv.reference_key1)
748 and ap_inv.product_table = 'OKL_CNSLD_AP_INVS_ALL'
749 and okl_inv.funding_type_code = 'BORROWER_PAYMENT'
750 and ap_inv.invoice_id = iph.invoice_id
751 and iph.check_date BETWEEN cp_from_date AND NVL(cp_to_date, iph.check_date)
752 group by iph.check_date;
753
754 --get sum of payments upto a date
755 -- sjalasut, modified the cursor to include okl_txl_ap_inv_lns_all_b and
756 -- okl_cnsld_ap_invs_all. khr_id now will be stored at the internal transaction
757 -- line table and consolidated invoice id is also stored at the internal
758 -- transaction lines table (okl_txl_ap_inv_lns_all_b)
759 -- changes made as part of OKLR12B disbursements project
760 Cursor c_payment_amount_csr(cp_khr_id IN NUMBER, cp_from_date IN DATE) IS
761 select sum(iph.amount) payment_amount
762 from ap_invoices_all ap_inv
763 , okl_trx_ap_invoices_v okl_inv
764 , ap_invoice_payment_history_v iph
765 , okl_txl_ap_inv_lns_all_b okl_inv_ln
766 , okl_cnsld_Ap_invs_all okl_cnsld
767 , fnd_application fnd_app
768 where okl_inv.id = okl_inv_ln.tap_id
769 and okl_inv_ln.khr_id = cp_khr_id
770 and ap_inv.application_id = fnd_app.application_id
771 and fnd_app.application_short_name = 'OKL'
772 and okl_inv_ln.cnsld_ap_inv_id = okl_cnsld.cnsld_ap_inv_id
773 and okl_cnsld.cnsld_ap_inv_id = to_number(ap_inv.reference_key1)
774 and ap_inv.product_table = 'OKL_CNSLD_AP_INVS_ALL'
775 and ap_inv.invoice_num = okl_inv.vendor_invoice_number
776 and okl_inv.funding_type_code = 'BORROWER_PAYMENT'
777 and ap_inv.invoice_id = iph.invoice_id
778 and iph.check_date <= cp_from_date;
779
780 --get existing daily interest streams
781 Cursor c_daily_int_stm_csr(cp_khr_id IN NUMBER, cp_sty_purpose IN VARCHAR2, cp_receipt_date IN DATE) IS
782 select sum(sel.amount) exist_amount
783 from okl_streams_v stm
784 , okl_strm_type_v sty
785 , okl_strm_elements_v sel
786 where stm.khr_id = cp_khr_id
787 and stm.sty_id = sty.id
788 and sty.stream_type_purpose = cp_sty_purpose
789 and stm.id = sel.stm_id
790 and sel.stream_element_date = trunc(cp_receipt_date);
791
792 /*outputs the contents of tables passed to it*/
793 PROCEDURE print_table_content(p_receipt_tbl IN receipt_tbl_type) IS
794 l_rcpt_tbl_row NUMBER := 0;
795 l_out_str varchar2(2000);
796 BEGIN
797
798 print_debug('*****************************************');
799 print_debug('****START CONTENTS OF P_RECEIPT_TBL****');
800 l_rcpt_tbl_row := p_receipt_tbl.first;
801 WHILE l_rcpt_tbl_row IS NOT NULL
802 LOOP
803 print_debug(' khr_id : ' || p_receipt_tbl(l_rcpt_tbl_row).khr_id);
804 print_debug(' kle_id : ' || p_receipt_tbl(l_rcpt_tbl_row).kle_id);
805 print_debug(' receipt_date : ' || p_receipt_tbl(l_rcpt_tbl_row).receipt_date);
806 print_debug(' receipt_amount : ' || p_receipt_tbl(l_rcpt_tbl_row).receipt_amount);
807 print_debug(' principal_pmt_rcpt_amt : ' || p_receipt_tbl(l_rcpt_tbl_row).principal_pmt_rcpt_amt);
808 print_debug(' loan_pmt_rcpt_amt : ' || p_receipt_tbl(l_rcpt_tbl_row).loan_pmt_rcpt_amt);
809
810 l_rcpt_tbl_row := p_receipt_tbl.next(l_rcpt_tbl_row);
811 END LOOP;
812 print_debug('*****END CONTENTS OF P_RECEIPT_TBL*****');
813 print_debug('*****************************************');
814
815
816 EXCEPTION
817 WHEN OTHERS THEN
818 print_debug('SQLCODE : ' || SQLCODE || ' SQLERRM : ' || SQLERRM);
819 END print_table_content;
820
821 BEGIN
822
823 FOR cur_khr IN c_khr_csr(p_khr_id) LOOP
824 l_error_flag := FALSE;
825 l_receipt_amt_success := 0;
826 l_receipt_amt_error := 0;
827
828 print_debug('====================================================================');
829 print_debug('Start - Daily Interest Processing for contract number => ' || cur_khr.contract_number);
830 print_debug(' Contract start date => ' || cur_khr.start_date);
831 print_debug(' Deal type => ' || cur_khr.deal_type);
832 print_debug(' Interest calculation basis => ' || cur_khr.interest_calculation_basis);
833 print_debug(' Revenue recognition method => ' || cur_khr.revenue_recognition_method);
834 receipt_date_range(
835 p_api_version => l_api_version,
836 p_init_msg_list => p_init_msg_list,
837 x_return_status => l_return_status,
838 x_msg_count => x_msg_count,
839 x_msg_data => x_msg_data,
840 p_contract_id => cur_khr.khr_id,
841 p_start_date => cur_khr.start_date,
842 p_due_date => NULL,
843 x_principal_balance => lx_principal_balance,
844 x_receipt_tbl => lx_receipt_tbl);
845
846 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
847 l_error_flag := TRUE;
848 print_debug(' Call to receipt_date_range failed.');
849 END IF;
850 --print_debug(' Starting principal balance : ' || lx_principal_balance);
851 print_table_content(lx_receipt_tbl);
852
853 l_receipt_tbl_row:= lx_receipt_tbl.first;
854 l_khr_start_date := cur_khr.start_date;
855 l_range_start_date := cur_khr.start_date;
856 WHILE (l_receipt_tbl_row IS NOT NULL) LOOP
857 IF (NOT(l_error_flag)) THEN
858 IF (lx_receipt_tbl(l_receipt_tbl_row).receipt_date = l_khr_start_date) THEN
859 l_prev_daily_prin_amt := 0;
860 l_daily_prin_amt := lx_receipt_tbl(l_receipt_tbl_row).receipt_amount;
861
862 FOR cur_daily_int_stm IN c_daily_int_stm_csr(cur_khr.khr_id, 'DAILY_INTEREST_PRINCIPAL', lx_receipt_tbl(l_receipt_tbl_row).receipt_date) LOOP
863 l_prev_daily_prin_amt := NVL(cur_daily_int_stm.exist_amount, 0);
864 END LOOP;
865 --print_debug(' Value of l_prev_daily_prin_amt: ' || l_prev_daily_prin_amt);
866
867 --adjust against existing streams
868 l_daily_prin_amt := NVL(l_daily_prin_amt, 0) - NVL(l_prev_daily_prin_amt, 0);
869 --print_debug(' After adjustment value of l_daily_prin_amt: ' || l_daily_prin_amt);
870
871 --create a principal accrual strm for entire rcpt amount
872 IF (l_daily_prin_amt <> 0) THEN
873 print_debug(' Creating stream for DAILY_INTEREST_PRINCIPAL.');
874 OKL_VARIABLE_INTEREST_PVT.Create_Daily_Interest_Streams (
875 p_api_version => l_api_version,
876 p_init_msg_list => p_init_msg_list,
877 x_return_status => l_return_status,
878 x_msg_count => x_msg_count,
879 x_msg_data => x_msg_data,
880 p_contract_id => cur_khr.khr_id,
881 p_amount => l_daily_prin_amt,
882 p_due_date => lx_receipt_tbl(l_receipt_tbl_row).receipt_date,
883 p_stream_type_purpose => 'DAILY_INTEREST_PRINCIPAL',
884 p_create_invoice_flag => OKL_API.G_FALSE,
885 p_process_flag => 'DAILY_INTEREST',
886 p_currency_code => cur_khr.currency_code);
887
888 print_debug('Status of creating stream: ' || l_return_status);
889 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
890 l_error_flag := TRUE;
891 print_debug(' Error: creating stream for DAILY_INTEREST_PRINCIPAL.');
892 ELSE
893 print_debug(' Success: creating stream for DAILY_INTEREST_PRINCIPAL.');
894 END IF;
895 END IF;
896
897 --Bug# 7277007
898 IF (NOT(l_error_flag)) THEN
899
900 l_receipt_amt_success := l_receipt_amt_success + lx_receipt_tbl(l_receipt_tbl_row).receipt_amount;
901
902 g_rpt_success_tbl_counter := 1;
903 IF g_rpt_success_curr_tbl.EXISTS(cur_khr.currency_code) THEN
904 g_rpt_success_tbl_counter := g_rpt_success_curr_tbl(cur_khr.currency_code).LAST + 1;
905 END IF;
906
907 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).contract_number := cur_khr.contract_number;
908 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).principal_balance := lx_principal_balance;
909 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).receipt_amt := lx_receipt_tbl(l_receipt_tbl_row).receipt_amount;
910 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).receipt_date := lx_receipt_tbl(l_receipt_tbl_row).receipt_date;
911 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).int_start_date := l_khr_start_date;
912 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).int_end_date := l_khr_start_date;
913 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).daily_int_amt := 0;
914 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).daily_prin_amt := lx_receipt_tbl(l_receipt_tbl_row).receipt_amount;
915 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).int_till_date_amt := 0;
916 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).prin_till_date_amt := NVL(l_prev_daily_prin_amt, 0);
917 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).daily_int_adj_amt := 0;
918 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).daily_prin_adj_amt := l_daily_prin_amt;
919 ELSE
920
921 l_receipt_amt_error := l_receipt_amt_error + lx_receipt_tbl(l_receipt_tbl_row).receipt_amount;
922
923 g_rpt_error_tbl_counter := 1;
924 IF g_rpt_error_curr_tbl.EXISTS(cur_khr.currency_code) THEN
925 g_rpt_error_tbl_counter := g_rpt_error_curr_tbl(cur_khr.currency_code).LAST + 1;
926 END IF;
927
928 g_rpt_error_curr_tbl(cur_khr.currency_code)(g_rpt_error_tbl_counter).contract_number := cur_khr.contract_number;
929 g_rpt_error_curr_tbl(cur_khr.currency_code)(g_rpt_error_tbl_counter).product_name := cur_khr.product_name;
930 g_rpt_error_curr_tbl(cur_khr.currency_code)(g_rpt_error_tbl_counter).interest_calc_basis := cur_khr.interest_calculation_basis;
931 g_rpt_error_curr_tbl(cur_khr.currency_code)(g_rpt_error_tbl_counter).receipt_amt := lx_receipt_tbl(l_receipt_tbl_row).receipt_amount;
932 g_rpt_error_curr_tbl(cur_khr.currency_code)(g_rpt_error_tbl_counter).receipt_date := lx_receipt_tbl(l_receipt_tbl_row).receipt_date;
933
934 IF (x_msg_count >= 1) THEN
935 FOR i in 1..x_msg_count LOOP
936 fnd_msg_pub.get (p_msg_index => i,
937 p_encoded => 'F',
938 p_data => lx_msg_data,
939 p_msg_index_out => lx_msg_index_out);
940
941 g_rpt_error_curr_tbl(cur_khr.currency_code)(g_rpt_error_tbl_counter).error_msg_tbl(i) := SUBSTR(lx_msg_data,1,2000);
942
943 END LOOP;
944 END IF;
945 --Bug# 7277007
946
947 END IF;
948
949 ELSE
950 --receipt date not equal khr start date
951 --obtain interest due
952 l_range_end_date := lx_receipt_tbl(l_receipt_tbl_row).receipt_date - 1;
953 --print_debug(' Value of l_range_start_date: ' || l_range_start_date);
954 --print_debug(' Value of l_range_end_date: ' || l_range_end_date);
955 l_cal_int_amt := 0;
956 l_start_bal := 0;
957
958 IF (cur_khr.deal_type = 'LOAN') THEN
959 --get principal balance as of range start date
960 FOR cur_principal_paid IN c_principal_paid_csr(cur_khr.khr_id, l_range_start_date) LOOP
961 l_start_bal := NVL(lx_principal_balance, 0) - NVL(cur_principal_paid.principal_paid, 0);
962 END LOOP; --c_principal_amt_csr
963 --print_debug(' Value of l_start_bal: ' || l_start_bal);
964
965 --get asset termination date and termination value
966 FOR cur_asset_term_val IN c_asset_term_val_csr(cur_khr.khr_id, l_range_start_date, l_range_end_date) LOOP
967 l_cal_int_amt := l_cal_int_amt + OKL_VARIABLE_INTEREST_PVT.calculate_interest (
968 p_api_version => l_api_version,
969 p_init_msg_list => p_init_msg_list,
970 x_return_status => l_return_status,
971 x_msg_count => x_msg_count,
972 x_msg_data => x_msg_data,
973 p_contract_id => cur_khr.khr_id,
974 p_from_date => l_range_start_date,
975 p_to_date => cur_asset_term_val.term_date -1,
976 p_principal_amount => l_start_bal,
977 p_currency_code => cur_khr.currency_code);
978
979 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
980 l_error_flag := TRUE;
981 END IF;
982 --print_debug(' Value of l_cal_int_amt: ' || l_cal_int_amt);
983
984 --print_debug('Asset term value: ' || cur_asset_term_val.term_value);
985 --print_debug('Asset term date: ' || cur_asset_term_val.term_date);
986 l_range_start_date := cur_asset_term_val.term_date;
987 l_start_bal := NVL(l_start_bal, 0) - NVL(cur_asset_term_val.term_value, 0);
988 --print_debug(' Value of l_start_bal: ' || l_start_bal);
989 END LOOP; --c_asset_term_val_csr
990
991 l_cal_int_amt := l_cal_int_amt + OKL_VARIABLE_INTEREST_PVT.calculate_interest (
992 p_api_version => l_api_version,
993 p_init_msg_list => p_init_msg_list,
994 x_return_status => l_return_status,
995 x_msg_count => x_msg_count,
996 x_msg_data => x_msg_data,
997 p_contract_id => cur_khr.khr_id,
998 p_from_date => l_range_start_date,
999 p_to_date => l_range_end_date,
1000 p_principal_amount => l_start_bal,
1001 p_currency_code => cur_khr.currency_code);
1002
1003 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1004 l_error_flag := TRUE;
1005 END IF;
1006 --print_debug(' Value of l_cal_int_amt: ' || l_cal_int_amt);
1007 ELSE --deal type = Rev Loan
1008 FOR cur_borrower_payment IN c_borrower_payment_csr(cur_khr.khr_id, l_range_start_date, l_range_end_date) LOOP
1009 --get payments maid upto the range start date
1010 FOR cur_payment_amount IN c_payment_amount_csr(cur_khr.khr_id, l_range_start_date) LOOP
1011 l_payment_amount := NVL(cur_payment_amount.payment_amount, 0);
1012 END LOOP;
1013 --print_debug(' Value of l_payment_amount: ' || l_payment_amount);
1014
1015 --get principal paid
1016 FOR cur_principal_paid IN c_principal_paid_csr(cur_khr.khr_id, l_range_start_date) LOOP
1017 l_principal_paid := NVL(cur_principal_paid.principal_paid, 0);
1018 END LOOP;
1019 --print_debug(' Value of l_principal_paid: ' || l_principal_paid);
1020
1021 l_start_bal := NVL(l_payment_amount, 0) - NVL(l_principal_paid, 0);
1022 --print_debug(' Value of l_start_bal: ' || l_start_bal);
1023
1024 l_cal_int_amt := l_cal_int_amt + OKL_VARIABLE_INTEREST_PVT.calculate_interest (
1025 p_api_version => l_api_version,
1026 p_init_msg_list => p_init_msg_list,
1027 x_return_status => l_return_status,
1028 x_msg_count => x_msg_count,
1029 x_msg_data => x_msg_data,
1030 p_contract_id => cur_khr.khr_id,
1031 p_from_date => l_range_start_date,
1032 p_to_date => cur_borrower_payment.borrower_payment_date -1,
1033 p_principal_amount => l_start_bal,
1034 p_currency_code => cur_khr.currency_code);
1035
1036 --print_debug(' Value of l_cal_int_amt: ' || l_cal_int_amt);
1037 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1038 l_error_flag := TRUE;
1039 END IF;
1040
1041 l_range_start_date := cur_borrower_payment.borrower_payment_date;
1042 END LOOP;
1043
1044 --get payments maid upto the range start date
1045 FOR cur_payment_amount IN c_payment_amount_csr(cur_khr.khr_id, l_range_start_date) LOOP
1046 l_payment_amount := NVL(cur_payment_amount.payment_amount, 0);
1047 END LOOP;
1048 --print_debug(' Value of l_payment_amount: ' || l_payment_amount);
1049
1050 --get principal paid
1051 FOR cur_principal_paid IN c_principal_paid_csr(cur_khr.khr_id, l_range_start_date) LOOP
1052 l_principal_paid := NVL(cur_principal_paid.principal_paid, 0);
1053 END LOOP;
1054 --print_debug(' Value of l_principal_paid: ' || l_principal_paid);
1055
1056 l_start_bal := NVL(l_payment_amount, 0) - NVL(l_principal_paid, 0);
1057 --print_debug(' Value of l_start_bal: ' || l_start_bal);
1058
1059 l_cal_int_amt := l_cal_int_amt + OKL_VARIABLE_INTEREST_PVT.calculate_interest (
1060 p_api_version => l_api_version,
1061 p_init_msg_list => p_init_msg_list,
1062 x_return_status => l_return_status,
1063 x_msg_count => x_msg_count,
1064 x_msg_data => x_msg_data,
1065 p_contract_id => cur_khr.khr_id,
1066 p_from_date => l_range_start_date,
1067 p_to_date => l_range_end_date,
1068 p_principal_amount => l_start_bal,
1069 p_currency_code => cur_khr.currency_code);
1070
1071 --print_debug(' Value of l_cal_int_amt: ' || l_cal_int_amt);
1072
1073 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1074 l_error_flag := TRUE;
1075 END IF;
1076 END IF;
1077
1078 --split into daily interest and principal
1079 l_daily_int_amt := 0;
1080 l_daily_prin_amt := 0;
1081
1082 --Bug# 7277007
1083 l_daily_int_calc_amt := 0;
1084 l_daily_prin_calc_amt := 0;
1085
1086 IF (lx_receipt_tbl(l_receipt_tbl_row).receipt_amount > l_cal_int_amt) THEN
1087 l_daily_int_amt := NVL(l_cal_int_amt, 0);
1088 l_daily_prin_amt := NVL(lx_receipt_tbl(l_receipt_tbl_row).receipt_amount, 0) - NVL(l_daily_int_amt, 0);
1089 ELSE
1090 l_daily_int_amt := NVL(lx_receipt_tbl(l_receipt_tbl_row).receipt_amount, 0);
1091 END IF;
1092 --print_debug(' Value of l_daily_int_amt: ' || l_daily_int_amt);
1093 --print_debug(' Value of l_daily_prin_amt: ' || l_daily_prin_amt);
1094
1095 --Bug# 7277007
1096 l_daily_int_calc_amt := l_daily_int_amt;
1097 l_daily_prin_calc_amt := l_daily_prin_amt;
1098
1099 --check for existing daily interest streams
1100 l_prev_daily_int_amt := 0;
1101 l_prev_daily_prin_amt := 0;
1102 FOR cur_daily_int_stm IN c_daily_int_stm_csr(cur_khr.khr_id, 'DAILY_INTEREST_INTEREST', lx_receipt_tbl(l_receipt_tbl_row).receipt_date) LOOP
1103 l_prev_daily_int_amt := NVL(cur_daily_int_stm.exist_amount, 0);
1104 END LOOP;
1105
1106 FOR cur_daily_int_stm IN c_daily_int_stm_csr(cur_khr.khr_id, 'DAILY_INTEREST_PRINCIPAL', lx_receipt_tbl(l_receipt_tbl_row).receipt_date) LOOP
1107 l_prev_daily_prin_amt := NVL(cur_daily_int_stm.exist_amount, 0);
1108 END LOOP;
1109 --print_debug(' Value of l_prev_daily_int_amt: ' || l_prev_daily_int_amt);
1110 --print_debug(' Value of l_prev_daily_prin_amt: ' || l_prev_daily_prin_amt);
1111
1112 --adjust against existing streams
1113 l_daily_int_amt := NVL(l_daily_int_amt, 0) - NVL(l_prev_daily_int_amt, 0);
1114 l_daily_prin_amt := NVL(l_daily_prin_amt, 0) - NVL(l_prev_daily_prin_amt, 0);
1115 --print_debug(' After adjustment value of l_daily_int_amt: ' || l_daily_int_amt);
1116 --print_debug(' After adjustment value of l_daily_prin_amt: ' || l_daily_prin_amt);
1117
1118 --create daily interest streams
1119 IF (l_daily_int_amt <> 0) AND (NOT(l_error_flag)) THEN
1120 print_debug(' Creating stream for DAILY_INTEREST_INTEREST.');
1121
1122 OKL_VARIABLE_INTEREST_PVT.Create_Daily_Interest_Streams (
1123 p_api_version => l_api_version,
1124 p_init_msg_list => p_init_msg_list,
1125 x_return_status => l_return_status,
1126 x_msg_count => x_msg_count,
1127 x_msg_data => x_msg_data,
1128 p_contract_id => cur_khr.khr_id,
1129 p_amount => l_daily_int_amt,
1130 p_due_date => lx_receipt_tbl(l_receipt_tbl_row).receipt_date,
1131 p_stream_type_purpose => 'DAILY_INTEREST_INTEREST',
1132 p_create_invoice_flag => OKL_API.G_FALSE,
1133 p_process_flag => 'DAILY_INTEREST',
1134 p_currency_code => cur_khr.currency_code);
1135
1136 print_debug('Status of creating stream: ' || l_return_status);
1137
1138 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1139 l_error_flag := TRUE;
1140 print_debug(' Error: creating stream for DAILY_INTEREST_INTEREST.');
1141 ELSE
1142 print_debug(' Success: creating stream for DAILY_INTEREST_INTEREST.');
1143 END IF;
1144 END IF;
1145
1146 IF (l_daily_prin_amt <> 0) AND (NOT(l_error_flag)) THEN
1147
1148 --check for excess payments
1149 l_excess_principal := 0;
1150 IF (cur_khr.deal_type = 'LOAN') THEN
1151 --check for excess loan payment for LOAN
1152 FOR cur_principal_paid IN c_principal_paid_csr(cur_khr.khr_id, lx_receipt_tbl(l_receipt_tbl_row).receipt_date) LOOP
1153 l_excess_principal := (NVL(cur_principal_paid.principal_paid, 0) + NVL(l_daily_prin_amt, 0)) - NVL(lx_principal_balance, 0);
1154 END LOOP;
1155
1156 ELSE
1157 --check for excess loan payment for REV-LOAN
1158 FOR cur_payment_amount IN c_payment_amount_csr(cur_khr.khr_id, lx_receipt_tbl(l_receipt_tbl_row).receipt_date) LOOP
1159 FOR cur_principal_paid IN c_principal_paid_csr(cur_khr.khr_id, lx_receipt_tbl(l_receipt_tbl_row).receipt_date) LOOP
1160 l_excess_principal := (NVL(cur_principal_paid.principal_paid, 0) + NVL(l_daily_prin_amt, 0)) - NVL(cur_payment_amount.payment_amount, 0);
1161 END LOOP;
1162 END LOOP;
1163 END IF;
1164
1165 IF (l_excess_principal <= 0) AND (NOT(l_error_flag)) THEN
1166 l_excess_principal := 0;
1167 ELSE
1168 --principal is in excess
1169 --print_debug(' Value of l_excess_principal: ' || l_excess_principal);
1170 l_daily_prin_amt := NVL(l_daily_prin_amt, 0) - NVL(l_excess_principal, 0);
1171 END IF;
1172
1173 print_debug(' Creating stream for DAILY_INTEREST_PRINCIPAL.');
1174
1175 OKL_VARIABLE_INTEREST_PVT.Create_Daily_Interest_Streams (
1176 p_api_version => l_api_version,
1177 p_init_msg_list => p_init_msg_list,
1178 x_return_status => l_return_status,
1179 x_msg_count => x_msg_count,
1180 x_msg_data => x_msg_data,
1181 p_contract_id => cur_khr.khr_id,
1182 p_amount => l_daily_prin_amt,
1183 p_due_date => lx_receipt_tbl(l_receipt_tbl_row).receipt_date,
1184 p_stream_type_purpose => 'DAILY_INTEREST_PRINCIPAL',
1185 p_create_invoice_flag => OKL_API.G_FALSE,
1186 p_process_flag => 'DAILY_INTEREST',
1187 p_currency_code => cur_khr.currency_code);
1188
1189 print_debug('Status of creating stream: ' || l_return_status);
1190
1191 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1192 l_error_flag := TRUE;
1193 print_debug(' Error: creating stream for DAILY_INTEREST_PRINCIPAL.');
1194 ELSE
1195 print_debug(' Success: creating stream for DAILY_INTEREST_PRINCIPAL.');
1196 END IF;
1197
1198 --check for excess principal paid
1199 l_excess_principal_paid := 0;
1200 FOR cur_excess_principal_paid IN c_excess_principal_paid_csr(cur_khr.khr_id) LOOP
1201 /*l_excess_principal_paid := NVL(cur_excess_principal_paid.excess_principal_paid, 0);
1202 print_debug(' Value of l_excess_principal_paid: ' || l_excess_principal_paid);
1203 l_excess_principal := NVL(l_excess_principal, 0) - NVL(l_excess_principal_paid, 0);*/
1204 null;
1205 END LOOP;
1206
1207 IF (l_excess_principal <> 0) AND (NOT(l_error_flag)) THEN
1208
1209 --create stream for excess loan payment
1210 print_debug(' Creating stream for EXCESS_LOAN_PAYMENT_PAID.');
1211
1212 OKL_VARIABLE_INTEREST_PVT.Create_Daily_Interest_Streams (
1213 p_api_version => l_api_version,
1214 p_init_msg_list => p_init_msg_list,
1215 x_return_status => l_return_status,
1216 x_msg_count => x_msg_count,
1217 x_msg_data => x_msg_data,
1218 p_contract_id => cur_khr.khr_id,
1219 p_amount => l_excess_principal,
1220 p_due_date => lx_receipt_tbl(l_receipt_tbl_row).receipt_date,
1221 p_stream_type_purpose => 'EXCESS_LOAN_PAYMENT_PAID',
1222 p_create_invoice_flag => OKL_API.G_FALSE,
1223 p_process_flag => 'DAILY_INTEREST',
1224 p_currency_code => cur_khr.currency_code);
1225
1226 print_debug('Status of creating stream: ' || l_return_status);
1227
1228 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1229 l_error_flag := TRUE;
1230 print_debug(' Error: creating stream for EXCESS_LOAN_PAYMENT_PAID.');
1231 ELSE
1232 print_debug(' Success: creating stream for EXCESS_LOAN_PAYMENT_PAID.');
1233 END IF;
1234 END IF;
1235 END IF;
1236
1237 --Bug# 7277007
1238 IF (NOT(l_error_flag)) THEN
1239
1240 l_receipt_amt_success := l_receipt_amt_success + lx_receipt_tbl(l_receipt_tbl_row).receipt_amount;
1241
1242 g_rpt_success_tbl_counter := 1;
1243 IF g_rpt_success_curr_tbl.EXISTS(cur_khr.currency_code) THEN
1244 g_rpt_success_tbl_counter := g_rpt_success_curr_tbl(cur_khr.currency_code).LAST + 1;
1245 END IF;
1246
1247 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).contract_number := cur_khr.contract_number;
1248 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).principal_balance := l_start_bal;
1249 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).receipt_amt := lx_receipt_tbl(l_receipt_tbl_row).receipt_amount;
1250 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).receipt_date := lx_receipt_tbl(l_receipt_tbl_row).receipt_date;
1251 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).int_start_date := l_range_start_date;
1252 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).int_end_date := l_range_end_date;
1253 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).daily_int_amt := l_daily_int_calc_amt;
1254 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).daily_prin_amt := l_daily_prin_calc_amt;
1255 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).int_till_date_amt := l_prev_daily_int_amt;
1256 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).prin_till_date_amt := l_prev_daily_prin_amt;
1257 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).daily_int_adj_amt := l_daily_int_amt;
1258 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).daily_prin_adj_amt := l_daily_prin_amt;
1259 ELSE
1260
1261 l_receipt_amt_error := l_receipt_amt_error + lx_receipt_tbl(l_receipt_tbl_row).receipt_amount;
1262
1263 g_rpt_error_tbl_counter := 1;
1264 IF g_rpt_error_curr_tbl.EXISTS(cur_khr.currency_code) THEN
1265 g_rpt_error_tbl_counter := g_rpt_error_curr_tbl(cur_khr.currency_code).LAST + 1;
1266 END IF;
1267
1268 g_rpt_error_curr_tbl(cur_khr.currency_code)(g_rpt_error_tbl_counter).contract_number := cur_khr.contract_number;
1269 g_rpt_error_curr_tbl(cur_khr.currency_code)(g_rpt_error_tbl_counter).product_name := cur_khr.product_name;
1270 g_rpt_error_curr_tbl(cur_khr.currency_code)(g_rpt_error_tbl_counter).interest_calc_basis := cur_khr.interest_calculation_basis;
1271 g_rpt_error_curr_tbl(cur_khr.currency_code)(g_rpt_error_tbl_counter).receipt_amt := lx_receipt_tbl(l_receipt_tbl_row).receipt_amount;
1272 g_rpt_error_curr_tbl(cur_khr.currency_code)(g_rpt_error_tbl_counter).receipt_date := lx_receipt_tbl(l_receipt_tbl_row).receipt_date;
1273
1274 IF (x_msg_count >= 1) THEN
1275 FOR i in 1..x_msg_count LOOP
1276 fnd_msg_pub.get (p_msg_index => i,
1277 p_encoded => 'F',
1278 p_data => lx_msg_data,
1279 p_msg_index_out => lx_msg_index_out);
1280
1281 g_rpt_error_curr_tbl(cur_khr.currency_code)(g_rpt_error_tbl_counter).error_msg_tbl(i) := SUBSTR(lx_msg_data,1,2000);
1282
1283 END LOOP;
1284 END IF;
1285 END IF;
1286 --Bug# 7277007
1287
1288 END IF;
1289 END IF;
1290
1291 --if rcpt unapp results in a zero DI-Principal strm elem, then the int calculation
1292 --must start from the previous start date
1293 IF ((l_daily_prin_amt + l_prev_daily_prin_amt) <> 0) THEN
1294 l_range_start_date := lx_receipt_tbl(l_receipt_tbl_row).receipt_date;
1295 END IF;
1296 l_receipt_tbl_row:= lx_receipt_tbl.next(l_receipt_tbl_row);
1297 END LOOP; --lx_receipt_tbl
1298
1299 --Bug# 7277007
1300 IF g_rpt_summary_tbl.EXISTS(cur_khr.currency_code)
1301 THEN
1302 g_rpt_summary_tbl(cur_khr.currency_code).total_receipt_amt_success :=
1303 g_rpt_summary_tbl(cur_khr.currency_code).total_receipt_amt_success + l_receipt_amt_success;
1304
1305 g_rpt_summary_tbl(cur_khr.currency_code).total_receipt_amt_error :=
1306 g_rpt_summary_tbl(cur_khr.currency_code).total_receipt_amt_error + l_receipt_amt_error;
1307
1308 ELSE
1309 g_rpt_summary_tbl(cur_khr.currency_code).total_receipt_amt_success := l_receipt_amt_success;
1310 g_rpt_summary_tbl(cur_khr.currency_code).total_receipt_amt_error := l_receipt_amt_error;
1311
1312 END IF;
1313 --Bug# 7277007
1314
1315 IF (l_error_flag) THEN
1316 print_debug('ERROR - Daily Interest Processing for contract number => ' || cur_khr.contract_number);
1317 END IF;
1318 print_debug('====================================================================');
1319 print_debug('End - Daily Interest Processing for contract number => ' || cur_khr.contract_number);
1320 END LOOP; --c_khr_csr
1321
1322 EXCEPTION
1323 ------------------------------------------------------------
1324 -- Exception handling
1325 ------------------------------------------------------------
1326
1327 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1328
1329 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1330 p_api_name => l_api_name,
1331 p_pkg_name => G_PKG_NAME,
1332 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1333 x_msg_count => x_msg_count,
1334 x_msg_data => x_msg_data,
1335 p_api_type => '_PVT');
1336
1337 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1338
1339 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1340 p_api_name => l_api_name,
1341 p_pkg_name => G_PKG_NAME,
1342 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1343 x_msg_count => x_msg_count,
1344 x_msg_data => x_msg_data,
1345 p_api_type => '_PVT');
1346
1347 WHEN OTHERS THEN
1348
1349 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1350 p_api_name => l_api_name,
1351 p_pkg_name => G_PKG_NAME,
1352 p_exc_name => 'OTHERS',
1353 x_msg_count => x_msg_count,
1354 x_msg_data => x_msg_data,
1355 p_api_type => '_PVT');
1356
1357 END daily_interest;
1358
1359 --dkagrawa created for concurrent program OKL Daily Interest Calculation
1360 PROCEDURE calculate_daily_interest(
1361 errbuf OUT NOCOPY VARCHAR2,
1362 retcode OUT NOCOPY NUMBER,
1363 p_contract_number IN VARCHAR2
1364 ) IS
1365 l_api_version CONSTANT NUMBER DEFAULT 1.0;
1366
1367 lx_msg_count NUMBER;
1368 lx_msg_data VARCHAR2(450);
1369 l_msg_index_out NUMBER;
1370 lx_return_status VARCHAR(1);
1371 l_khr_id NUMBER DEFAULT NULL;
1372 l_contract_number okc_k_headers_b.contract_number%type;
1373 --dkagrawa changed the cursor to use new view okl_prod_qlty_val_uv for product quality value instead of product_parameter_v
1374 CURSOR check_contract_csr(cp_contract_number IN VARCHAR2) IS
1375 SELECT khr.id khr_id
1376 ,khr.contract_number
1377 ,khr.authoring_org_id
1378 FROM okl_k_headers_full_v khr,
1379 okl_prod_qlty_val_uv ppm,
1380 okc_statuses_b ste
1381 WHERE khr.contract_number = NVL(cp_contract_number, khr.contract_number)
1382 AND khr.pdt_id = ppm.pdt_id
1383 AND ppm.quality_name = 'REVENUE_RECOGNITION_METHOD'
1384 AND ppm.quality_val = 'ACTUAL'
1385 AND khr.sts_code = ste.code
1386 AND ste.ste_code in ('ACTIVE', 'TERMINATED');
1387
1388 BEGIN
1389
1390 --set variable rate global variables so that interest calculation
1391 --and interest rate parameter updates works correctly
1392 OKL_VARIABLE_INTEREST_PVT.G_CALC_METHOD_CODE := 'DAILY_INTEREST';
1393 OKL_VARIABLE_INTEREST_PVT.G_INTEREST_CALCULATION_BASIS := 'DAILY_INTEREST';
1394 OKL_VARIABLE_INTEREST_PVT.G_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1395
1396 OPEN check_contract_csr(p_contract_number);
1397 LOOP
1398 FETCH check_contract_csr INTO l_khr_id, l_contract_number, OKL_VARIABLE_INTEREST_PVT.G_AUTHORING_ORG_ID;
1399
1400 IF (check_contract_csr%NOTFOUND) THEN
1401 CLOSE check_contract_csr;
1402 EXIT;
1403 END IF;
1404
1405 daily_interest(
1406 p_api_version => l_api_version,
1407 p_init_msg_list => FND_API.G_FALSE,
1408 x_return_status => lx_return_status,
1409 x_msg_count => lx_msg_count,
1410 x_msg_data => lx_msg_data,
1411 p_khr_id => l_khr_id
1412 );
1413 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1414 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1415 ELSIF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
1416 RAISE OKL_API.G_EXCEPTION_ERROR;
1417 END IF;
1418
1419 END LOOP;
1420
1421 OKL_VARIABLE_INTEREST_PVT.G_CALC_METHOD_CODE := NULL;
1422 OKL_VARIABLE_INTEREST_PVT.G_INTEREST_CALCULATION_BASIS := NULL;
1423 OKL_VARIABLE_INTEREST_PVT.G_REQUEST_ID := NULL;
1424
1425 errbuf := lx_msg_data;
1426
1427 --Bug# 7277007
1428 print_output(p_contract_number => p_contract_number);
1429
1430 retcode := 0;
1431 EXCEPTION
1432 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1433 retcode := 2;
1434 IF check_contract_csr%ISOPEN THEN
1435 CLOSE check_contract_csr;
1436 END IF;
1437 lx_return_status := Okl_Api.HANDLE_EXCEPTIONS(G_APP_NAME,
1438 G_PKG_NAME,
1439 'Okl_Api.G_RET_STS_ERROR',
1440 lx_msg_count,
1441 lx_msg_data,
1442 '_PVT');
1443 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1444 retcode := 2;
1445 IF check_contract_csr%ISOPEN THEN
1446 CLOSE check_contract_csr;
1447 END IF;
1448 lx_return_status := OKL_API.HANDLE_EXCEPTIONS(
1449 p_api_name => G_APP_NAME,
1450 p_pkg_name => G_PKG_NAME,
1451 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1452 x_msg_count => lx_msg_count,
1453 x_msg_data => lx_msg_data,
1454 p_api_type => '_PVT');
1455 WHEN OTHERS THEN
1456 retcode := 2;
1457 errbuf := SQLERRM;
1458 IF check_contract_csr%ISOPEN THEN
1459 CLOSE check_contract_csr;
1460 END IF;
1461 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Error '||SQLCODE||': '||SQLERRM);
1462 END calculate_daily_interest;
1463
1464 END OKL_DAILY_INTEREST_CALC_PVT;