[Home] [Help]
Skip to content
PACKAGE BODY: APPS.OKL_DAILY_INTEREST_CALC_PVT
Source
1 PACKAGE BODY OKL_DAILY_INTEREST_CALC_PVT AS
2 /* $Header: OKLRDICB.pls 120.25.12020000.3 2012/09/27 10:34:02 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
429 --Bug# 14179413: Include Credit Memos and Adjustments when calculating
430 -- Actual Principal Balance
431 CURSOR receipt_details_loan_csr (p_contract_id NUMBER,
432 p_start_date DATE,
433 p_due_date DATE) IS
434 SELECT cra.receipt_date receipt_date
435 ,SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) receipt_amount --4884843, 4872370
436 FROM okl_txd_ar_ln_dtls_b tld,
437 ra_customer_trx_lines_all ractrl,
438 okl_txl_ar_inv_lns_b til,
439 okl_trx_ar_invoices_b tai,
440 ar_payment_schedules_all aps,
441 ar_receivable_applications_all raa,
442 ar_cash_receipts_all cra,
443 okl_strm_type_b sty_ln_pmt,
444 ar_distributions_all ad
445 WHERE tai.trx_status_code = 'PROCESSED'
446 AND tai.khr_id = p_contract_id
447 AND tld.khr_id = p_contract_id
448 AND ractrl.customer_trx_id = aps.customer_trx_id
449 AND raa.applied_customer_trx_id = aps.customer_trx_id
450 AND aps.class = 'INV'
451 AND raa.application_type = 'CASH'
452 AND raa.status = 'APP'
453 AND raa.display = 'Y'
454 AND cra.receipt_date <= NVL(p_due_date, cra.receipt_date)
455 AND raa.cash_receipt_id = cra.cash_receipt_id
456 AND tld.sty_id = sty_ln_pmt.id
457 AND sty_ln_pmt.stream_type_purpose IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT', 'UNSCHEDULED_LOAN_PAYMENT', 'AMBCOC' )
458 AND to_char(tld.id) = ractrl.interface_line_attribute14
459 AND tld.til_id_details = til.id
460 AND til.tai_id = tai.id
461 AND raa.receivable_application_id = ad.source_id
462 AND ad.source_table = 'RA'
463 AND ad.ref_customer_trx_Line_Id = ractrl.customer_trx_line_id
464 GROUP BY receipt_date
465 UNION ALL
466 SELECT cra.receipt_date receipt_date
467 ,SUM(raa.line_applied) receipt_amount --4884843, 4872370
468 FROM okl_txd_ar_ln_dtls_b tld,
469 ra_customer_trx_lines_all ractrl,
470 okl_txl_ar_inv_lns_b til,
471 okl_trx_ar_invoices_b tai,
472 ar_payment_schedules_all aps,
473 ar_receivable_applications_all raa,
474 ar_cash_receipts_all cra,
475 okl_strm_type_b sty_ln_pmt
476 WHERE tai.trx_status_code = 'PROCESSED'
477 AND tai.khr_id = p_contract_id
478 AND tld.khr_id = p_contract_id
479 AND ractrl.customer_trx_id = aps.customer_trx_id
480 AND raa.applied_customer_trx_id = aps.customer_trx_id
481 AND aps.class = 'INV'
482 AND raa.application_type = 'CASH'
483 AND raa.status = 'APP'
484 AND raa.display = 'Y'
485 AND cra.receipt_date <= NVL(p_due_date, cra.receipt_date)
486 AND raa.cash_receipt_id = cra.cash_receipt_id
487 AND tld.sty_id = sty_ln_pmt.id
488 AND sty_ln_pmt.stream_type_purpose IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT','UNSCHEDULED_LOAN_PAYMENT', 'AMBCOC')
489 AND to_char(tld.id) = ractrl.interface_line_attribute14
490 AND tld.til_id_details = til.id
491 AND til.tai_id = tai.id
492 AND EXISTS (SELECT 1
493 FROM ar_distributions_all ad
494 WHERE raa.receivable_application_id = ad.source_id
495 AND ad.source_table = 'RA'
496 AND ad.source_type = 'REC'
497 AND ad.ref_customer_trx_Line_Id IS NULL)
498 GROUP BY receipt_date
499 UNION ALL
500 SELECT cm.trx_date receipt_date
501 ,SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) receipt_amount
502 FROM okl_txd_ar_ln_dtls_b tld,
503 ra_customer_trx_lines_all ractrl,
504 okl_txl_ar_inv_lns_b til,
505 okl_trx_ar_invoices_b tai,
506 ar_payment_schedules_all aps,
507 ar_receivable_applications_all raa,
508 ra_customer_trx_all cm,
509 okl_strm_type_b sty_ln_pmt,
510 ar_distributions_all ad
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 = 'CM'
518 AND raa.status = 'APP'
519 AND raa.display = 'Y'
520 AND cm.trx_date <= NVL(p_due_date, cm.trx_date)
521 AND raa.customer_trx_id = cm.customer_trx_id
522 AND tld.sty_id = sty_ln_pmt.id
523 AND sty_ln_pmt.stream_type_purpose IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT', 'UNSCHEDULED_LOAN_PAYMENT', 'AMBCOC' )
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 AND raa.receivable_application_id = ad.source_id
528 AND ad.source_table = 'RA'
529 AND ad.ref_customer_trx_Line_Id = ractrl.customer_trx_line_id
530 GROUP BY cm.trx_date
531 UNION ALL
532 SELECT cm.trx_date receipt_date
533 ,SUM(raa.line_applied) receipt_amount
534 FROM okl_txd_ar_ln_dtls_b tld,
535 ra_customer_trx_lines_all ractrl,
536 okl_txl_ar_inv_lns_b til,
537 okl_trx_ar_invoices_b tai,
538 ar_payment_schedules_all aps,
539 ar_receivable_applications_all raa,
540 ra_customer_trx_all cm,
541 okl_strm_type_b sty_ln_pmt
542 WHERE tai.trx_status_code = 'PROCESSED'
543 AND tai.khr_id = p_contract_id
544 AND tld.khr_id = p_contract_id
545 AND ractrl.customer_trx_id = aps.customer_trx_id
546 AND raa.applied_customer_trx_id = aps.customer_trx_id
547 AND aps.class = 'INV'
548 AND raa.application_type = 'CM'
549 AND raa.status = 'APP'
550 AND raa.display = 'Y'
551 AND cm.trx_date <= NVL(p_due_date, cm.trx_date)
552 AND raa.customer_trx_id = cm.customer_trx_id
553 AND tld.sty_id = sty_ln_pmt.id
554 AND sty_ln_pmt.stream_type_purpose IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT','UNSCHEDULED_LOAN_PAYMENT', 'AMBCOC')
555 AND to_char(tld.id) = ractrl.interface_line_attribute14
556 AND tld.til_id_details = til.id
557 AND til.tai_id = tai.id
558 AND EXISTS (SELECT 1
559 FROM ar_distributions_all ad
560 WHERE raa.receivable_application_id = ad.source_id
561 AND ad.source_table = 'RA'
562 AND ad.source_type = 'REC'
563 AND ad.ref_customer_trx_Line_Id IS NULL)
564 GROUP BY cm.trx_date
565 UNION ALL
566 SELECT adj.apply_date receipt_date
567 ,SUM(nvl(ad.amount_dr,0))- SUM(nvl(ad.amount_cr,0)) receipt_amount
568 FROM okl_txd_ar_ln_dtls_b tld,
569 ra_customer_trx_lines_all ractrl,
570 okl_txl_ar_inv_lns_b til,
571 okl_trx_ar_invoices_b tai,
572 ar_payment_schedules_all aps,
573 ar_adjustments_all adj,
574 okl_strm_type_b sty,
575 ar_distributions_all ad
576 WHERE tai.trx_status_code = 'PROCESSED'
577 AND tai.khr_id = p_contract_id
578 AND tld.khr_id = p_contract_id
579 AND ractrl.customer_trx_id = aps.customer_trx_id
580 AND adj.customer_trx_id = aps.customer_trx_id
581 AND aps.class = 'INV'
582 AND adj.status = 'A'
583 AND adj.apply_date <= NVL(p_due_date, adj.apply_date)
584 AND tld.sty_id = sty.id
585 AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT')
586 AND to_char(tld.id) = ractrl.interface_line_attribute14
587 AND tld.til_id_details = til.id
588 AND til.tai_id = tai.id
589 AND adj.adjustment_id = ad.source_id
590 AND ad.source_table = 'ADJ'
591 AND ad.ref_customer_trx_Line_Id = ractrl.customer_trx_line_id
592 GROUP BY adj.apply_date
593 ORDER BY receipt_date asc;
594
595 --Bug# 14179413: Include Credit Memos and Adjustments when calculating
596 -- Actual Principal Balance
597 Cursor receipt_details_rloan_csr (p_contract_id NUMBER,
598 p_start_date DATE,
599 p_due_date DATE) IS
600 SELECT cra.receipt_date receipt_date
601 ,SUM(line_applied) receipt_amount --4884843, 4872370
602 FROM okl_txd_ar_ln_dtls_b tld,
603 ra_customer_trx_lines_all ractrl,
604 okl_txl_ar_inv_lns_b til,
605 okl_trx_ar_invoices_b tai,
606 ar_payment_schedules_all aps,
607 ar_receivable_applications_all raa,
608 ar_cash_receipts_all cra,
609 okl_strm_type_b sty
610 WHERE tai.trx_status_code = 'PROCESSED'
611 AND tai.khr_id = p_contract_id
612 AND tld.khr_id = p_contract_id
613 AND ractrl.customer_trx_id = aps.customer_trx_id
614 AND raa.applied_customer_trx_id = aps.customer_trx_id
615 AND aps.class = 'INV'
616 AND raa.application_type = 'CASH'
617 AND raa.status = 'APP'
618 AND raa.display = 'Y'
619 AND cra.receipt_date <= NVL(p_due_date, cra.receipt_date)
620 AND raa.cash_receipt_id = cra.cash_receipt_id
621 AND tld.sty_id = sty.id
622 AND sty.stream_type_purpose in ('UNSCHEDULED_LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT')
623 AND to_char(tld.id) = ractrl.interface_line_attribute14
624 AND tld.til_id_details = til.id
625 AND til.tai_id = tai.id
626 GROUP BY receipt_date
627 UNION ALL
628 SELECT cm.trx_date receipt_date
629 ,SUM(line_applied) receipt_amount
630 FROM okl_txd_ar_ln_dtls_b tld,
631 ra_customer_trx_lines_all ractrl,
632 okl_txl_ar_inv_lns_b til,
633 okl_trx_ar_invoices_b tai,
634 ar_payment_schedules_all aps,
635 ar_receivable_applications_all raa,
636 ra_customer_trx_all cm,
637 okl_strm_type_b sty
638 WHERE tai.trx_status_code = 'PROCESSED'
639 AND tai.khr_id = p_contract_id
640 AND tld.khr_id = p_contract_id
641 AND ractrl.customer_trx_id = aps.customer_trx_id
642 AND raa.applied_customer_trx_id = aps.customer_trx_id
643 AND aps.class = 'INV'
644 AND raa.application_type = 'CM'
645 AND raa.status = 'APP'
646 AND raa.display = 'Y'
647 AND cm.trx_date <= NVL(p_due_date, cm.trx_date)
648 AND raa.customer_trx_id = cm.customer_trx_id
649 AND tld.sty_id = sty.id
650 AND sty.stream_type_purpose in ('UNSCHEDULED_LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT')
651 AND to_char(tld.id) = ractrl.interface_line_attribute14
652 AND tld.til_id_details = til.id
653 AND til.tai_id = tai.id
654 GROUP BY cm.trx_date
655 UNION ALL
656 SELECT adj.apply_date receipt_date
657 ,SUM(-1 * NVL(adj.amount,0)) receipt_amount
658 FROM okl_txd_ar_ln_dtls_b tld,
659 ra_customer_trx_lines_all ractrl,
660 okl_txl_ar_inv_lns_b til,
661 okl_trx_ar_invoices_b tai,
662 ar_payment_schedules_all aps,
663 ar_adjustments_all adj,
664 okl_strm_type_b sty
665 WHERE tai.trx_status_code = 'PROCESSED'
666 AND tai.khr_id = p_contract_id
667 AND tld.khr_id = p_contract_id
668 AND ractrl.customer_trx_id = aps.customer_trx_id
669 AND adj.customer_trx_id = aps.customer_trx_id
670 AND aps.class = 'INV'
671 AND adj.status = 'A'
672 AND adj.apply_date <= NVL(p_due_date, adj.apply_date)
673 AND tld.sty_id = sty.id
674 AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT')
675 AND to_char(tld.id) = ractrl.interface_line_attribute14
676 AND tld.til_id_details = til.id
677 AND til.tai_id = tai.id
678 GROUP BY adj.apply_date
679 ORDER BY receipt_date asc;
680 -- End - Billing Inline changes - Bug#5898792 - varangan - 23/2/2007
681
682 BEGIN
683 x_return_status := OKL_API.G_RET_STS_SUCCESS;
684 l_start_date := p_start_date;
685
686 OPEN contract_csr (p_contract_id);
687 FETCH contract_csr INTO l_contract_start_date, l_deal_type;
688 IF (contract_csr%NOTFOUND) THEN
689 CLOSE contract_csr;
690 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
691 END IF;
692 CLOSE contract_csr;
693
694 l_counter := 0;
695 IF (l_deal_type = 'LOAN') THEN
696 -- Derive Principal Balance
697 Okl_Execute_Formula_Pub.EXECUTE(p_api_version => 1.0,
698 p_init_msg_list => OKL_API.G_TRUE,
699 x_return_status => x_return_status,
700 x_msg_count => x_msg_count,
701 x_msg_data => x_msg_data,
702 p_formula_name => 'CONTRACT_FINANCED_AMOUNT',
703 p_contract_id => p_contract_id,
704 p_line_id => NULL,
705 x_value => l_principal_balance
706 );
707 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
708 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
709 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
710 RAISE Okl_Api.G_EXCEPTION_ERROR;
711 END IF;
712
713 FOR current_receipt in receipt_details_loan_csr (p_contract_id, l_start_date, p_due_date)
714 LOOP
715 l_counter := l_counter + 1;
716 l_receipt_tbl(l_counter).khr_id := p_contract_id;
717 l_receipt_tbl(l_counter).receipt_date := current_receipt.receipt_date;
718 l_receipt_tbl(l_counter).receipt_amount := current_receipt.receipt_amount;
719 END LOOP;
720
721 --x_receipt_tbl := l_receipt_tbl;
722 -- 4957212
723 x_principal_balance := l_principal_balance;
724
725 ELSIF (l_deal_type = 'LOAN-REVOLVING') THEN
726 FOR current_receipt in receipt_details_rloan_csr (p_contract_id, l_start_date, p_due_date)
727 LOOP
728 l_counter := l_counter + 1;
729 l_receipt_tbl(l_counter).khr_id := p_contract_id;
730 l_receipt_tbl(l_counter).receipt_date := current_receipt.receipt_date;
731 l_receipt_tbl(l_counter).receipt_amount := current_receipt.receipt_amount;
732 END LOOP;
733
734 --x_receipt_tbl := l_receipt_tbl;
735 -- 4957212
736 x_principal_balance := NULL;
737 END IF;
738
739 FOR l_counter IN 1..l_receipt_tbl.count LOOP
740 --receipt date less than khr start date
741 IF (l_receipt_tbl(l_counter).receipt_date < l_contract_start_date) THEN
742 IF (l_temp_receipt_date IS NOT NULL
743 AND l_receipt_tbl_temp(l_counter_temp).receipt_date = l_receipt_tbl(l_counter).receipt_date) THEN
744 --if a record exist in l_receipt_tbl_temp and the current record has the same date as the current
745 --record in l_receipt_tbl then add the receipt amount in l_receipt_tbl to the amount in l_receipt_tbl_temp
746 l_receipt_tbl_temp(l_counter_temp).receipt_amount := l_receipt_tbl_temp(l_counter_temp).receipt_amount +
747 l_receipt_tbl(l_counter).receipt_amount;
748
749 ELSE
750 --create a new rec in l_receipt_tbl_temp and set the receipt date to the khr start date
751 l_counter_temp := l_counter_temp + 1;
752 l_receipt_tbl_temp(l_counter_temp).khr_id := l_receipt_tbl(l_counter).khr_id ;
753 l_receipt_tbl_temp(l_counter_temp).receipt_date := l_contract_start_date ;
754 l_receipt_tbl_temp(l_counter_temp).receipt_amount := l_receipt_tbl(l_counter).receipt_amount ;
755 l_temp_receipt_date := l_receipt_tbl_temp(l_counter_temp).receipt_date;
756 END IF;
757 ELSE
758 --transfer the from l_receipt_tbl to l_receipt_tbl_temp
759 l_counter_temp := l_counter_temp + 1;
760 l_receipt_tbl_temp(l_counter_temp).khr_id := l_receipt_tbl(l_counter).khr_id;
761 l_receipt_tbl_temp(l_counter_temp).receipt_date := l_receipt_tbl(l_counter).receipt_date;
762 l_receipt_tbl_temp(l_counter_temp).receipt_amount := l_receipt_tbl(l_counter).receipt_amount ;
763 l_temp_receipt_date := l_receipt_tbl_temp(l_counter_temp).receipt_date;
764 END IF;
765 END LOOP;
766
767 x_receipt_tbl := l_receipt_tbl_temp;
768 -- 4957212
769 --x_principal_balance := NULL;
770
771 EXCEPTION
772
773 WHEN OTHERS THEN
774 Okl_Api.SET_MESSAGE(
775 p_app_name => G_APP_NAME,
776 p_msg_name => G_UNEXPECTED_ERROR,
777 p_token1 => G_SQLCODE_TOKEN,
778 p_token1_value => SQLCODE,
779 p_token2 => G_SQLERRM_TOKEN,
780 p_token2_value => SQLERRM);
781
782 END receipt_date_range;
783
784 ---------------------------------------------------------------------------
785
786 PROCEDURE daily_interest(p_api_version IN NUMBER
787 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
788 ,x_return_status OUT NOCOPY VARCHAR2
789 ,x_msg_count OUT NOCOPY NUMBER
790 ,x_msg_data OUT NOCOPY VARCHAR2
791 ,p_khr_id IN NUMBER DEFAULT NULL) IS
792
793 ------------------------------------------------------------
794 -- Declare Variables required by APIs
795 ------------------------------------------------------------
796 l_api_version CONSTANT NUMBER := 1;
797 l_api_name CONSTANT VARCHAR2(30) := 'DAILY_INTEREST';
798 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
799
800 lx_principal_balance NUMBER := 0;
801 lx_receipt_tbl receipt_tbl_type;
802 l_receipt_tbl_row NUMBER;
803 l_prev_receipt_tbl_row NUMBER;
804 l_khr_start_date DATE;
805 l_range_start_date DATE;
806 l_range_end_date DATE;
807 l_error_flag BOOLEAN := FALSE;
808 l_start_bal NUMBER := 0;
809 l_cal_int_amt NUMBER := 0;
810 l_daily_int_amt NUMBER := 0;
811 l_daily_prin_amt NUMBER := 0;
812 l_prev_daily_int_amt NUMBER := 0;
813 l_prev_daily_prin_amt NUMBER := 0;
814 l_payment_amount NUMBER := 0;
815 l_principal_paid NUMBER := 0;
816 l_excess_principal NUMBER := 0;
817 l_excess_principal_paid NUMBER := 0;
818 l_invoice_id NUMBER;
819
820 --Bug# 7277007
821 l_daily_int_calc_amt NUMBER;
822 l_daily_prin_calc_amt NUMBER;
823 lx_msg_index_out NUMBER;
824 lx_msg_data VARCHAR2(2000);
825 l_receipt_amt_success NUMBER;
826 l_receipt_amt_error NUMBER;
827 --Bug# 7277007
828
829 --------------------------------------------------------------------
830 --Declare Cursors
831 --------------------------------------------------------------------
832 --get contract details
833 Cursor c_khr_csr(cp_khr_id IN NUMBER) IS select khr.id khr_id
834 , khr.contract_number
835 , khr.start_date
836 , khr.deal_type
837 , khr.currency_code
838 , ppm.revenue_recognition_method
839 , ppm.interest_calculation_basis
840 , ppm.name product_name
841 from okl_k_headers_full_v khr
842 ,okl_product_parameters_v ppm
843 where khr.pdt_id = ppm.id
844 and ppm.revenue_recognition_method = 'ACTUAL'
845 and khr.id = NVL(cp_khr_id, khr.id)
846 order by khr.contract_number;
847
848 --get principal paid as of a given date
849 Cursor c_principal_paid_csr(cp_khr_id IN NUMBER, cp_from_date IN DATE) IS select nvl(sum(sel.amount), 0) principal_paid
850 from okl_streams_v stm
851 , okl_strm_type_v sty
852 , okl_strm_elements_v sel
853 where stm.khr_id = cp_khr_id
854 and stm.sty_id = sty.id
855 and sty.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
856 and stm.id = sel.stm_id
857 and sel.stream_element_date <= trunc(cp_from_date);
858
859 --get excess principal paid
860 Cursor c_excess_principal_paid_csr(cp_khr_id IN NUMBER) IS select nvl(sum(sel.amount), 0) excess_principal_paid
861 from okl_streams_v stm
862 , okl_strm_type_v sty
863 , okl_strm_elements_v sel
864 where stm.khr_id = cp_khr_id
865 and stm.sty_id = sty.id
866 and sty.stream_type_purpose = 'EXCESS_LOAN_PAYMENT_PAID'
867 and stm.id = sel.stm_id;
868
869 --get asset termination value between range dates
870 Cursor c_asset_term_val_csr(cp_khr_id IN NUMBER, cp_from_date IN DATE, cp_to_date IN DATE) IS
871 select trunc(cbl.termination_date) term_date
872 , nvl(sum(cbl.termination_value_amt), 0) term_value
873 from okl_contract_balances cbl
874 where cbl.khr_id = cp_khr_id
875 and cbl.termination_date between cp_from_date and cp_to_date
876 group by trunc(cbl.termination_date);
877
878 --get payments for revolving loans for receipt date range
879 -- sjalasut, modified the cursor to include okl_txl_ap_inv_lns_all_b and
880 -- okl_cnsld_ap_invs_all. khr_id now will be stored at the internal transaction
881 -- line table and consolidated invoice id is also stored at the internal
882 -- transaction lines table (okl_txl_ap_inv_lns_all_b)
883 -- changes made as part of OKLR12B disbursements project
884 Cursor c_borrower_payment_csr(cp_khr_id IN NUMBER, cp_from_date IN DATE, cp_to_date IN DATE) IS
885 select iph.check_date borrower_payment_date
886 , sum(iph.amount) borrower_payment
887 from ap_invoices_all ap_inv
888 , okl_trx_ap_invoices_v okl_inv
889 , ap_invoice_payment_history_v iph
890 , okl_txl_ap_inv_lns_all_b okl_inv_ln
891 , okl_cnsld_Ap_invs_all okl_cnsld
892 , fnd_application fnd_app
893 where okl_inv.id = okl_inv_ln.tap_id
894 and okl_inv_ln.khr_id = cp_khr_id
895 and ap_inv.application_id = fnd_app.application_id
896 and fnd_app.application_short_name = 'OKL'
897 and okl_inv_ln.cnsld_ap_inv_id = okl_cnsld.cnsld_ap_inv_id
898 and okl_cnsld.cnsld_ap_inv_id = to_number(ap_inv.reference_key1)
899 and ap_inv.product_table = 'OKL_CNSLD_AP_INVS_ALL'
900 and okl_inv.funding_type_code = 'BORROWER_PAYMENT'
901 and ap_inv.invoice_id = iph.invoice_id
902 and iph.check_date BETWEEN cp_from_date AND NVL(cp_to_date, iph.check_date)
903 group by iph.check_date;
904
905 --get sum of payments upto a date
906 -- sjalasut, modified the cursor to include okl_txl_ap_inv_lns_all_b and
907 -- okl_cnsld_ap_invs_all. khr_id now will be stored at the internal transaction
908 -- line table and consolidated invoice id is also stored at the internal
909 -- transaction lines table (okl_txl_ap_inv_lns_all_b)
910 -- changes made as part of OKLR12B disbursements project
911 Cursor c_payment_amount_csr(cp_khr_id IN NUMBER, cp_from_date IN DATE) IS
912 select sum(iph.amount) payment_amount
913 from ap_invoices_all ap_inv
914 , okl_trx_ap_invoices_v okl_inv
915 , ap_invoice_payment_history_v iph
916 , okl_txl_ap_inv_lns_all_b okl_inv_ln
917 , okl_cnsld_Ap_invs_all okl_cnsld
918 , fnd_application fnd_app
919 where okl_inv.id = okl_inv_ln.tap_id
920 and okl_inv_ln.khr_id = cp_khr_id
921 and ap_inv.application_id = fnd_app.application_id
922 and fnd_app.application_short_name = 'OKL'
923 and okl_inv_ln.cnsld_ap_inv_id = okl_cnsld.cnsld_ap_inv_id
924 and okl_cnsld.cnsld_ap_inv_id = to_number(ap_inv.reference_key1)
925 and ap_inv.product_table = 'OKL_CNSLD_AP_INVS_ALL'
926 and ap_inv.invoice_num = okl_inv.vendor_invoice_number
927 and okl_inv.funding_type_code = 'BORROWER_PAYMENT'
928 and ap_inv.invoice_id = iph.invoice_id
929 and iph.check_date <= cp_from_date;
930
931 --get existing daily interest streams
932 Cursor c_daily_int_stm_csr(cp_khr_id IN NUMBER, cp_sty_purpose IN VARCHAR2, cp_receipt_date IN DATE) IS
933 select sum(sel.amount) exist_amount
934 from okl_streams_v stm
935 , okl_strm_type_v sty
936 , okl_strm_elements_v sel
937 where stm.khr_id = cp_khr_id
938 and stm.sty_id = sty.id
939 and sty.stream_type_purpose = cp_sty_purpose
940 and stm.id = sel.stm_id
941 and sel.stream_element_date = trunc(cp_receipt_date);
942
943 /*outputs the contents of tables passed to it*/
944 PROCEDURE print_table_content(p_receipt_tbl IN receipt_tbl_type) IS
945 l_rcpt_tbl_row NUMBER := 0;
946 l_out_str varchar2(2000);
947 BEGIN
948
949 print_debug('*****************************************');
950 print_debug('****START CONTENTS OF P_RECEIPT_TBL****');
951 l_rcpt_tbl_row := p_receipt_tbl.first;
952 WHILE l_rcpt_tbl_row IS NOT NULL
953 LOOP
954 print_debug(' khr_id : ' || p_receipt_tbl(l_rcpt_tbl_row).khr_id);
955 print_debug(' kle_id : ' || p_receipt_tbl(l_rcpt_tbl_row).kle_id);
956 print_debug(' receipt_date : ' || p_receipt_tbl(l_rcpt_tbl_row).receipt_date);
957 print_debug(' receipt_amount : ' || p_receipt_tbl(l_rcpt_tbl_row).receipt_amount);
958 print_debug(' principal_pmt_rcpt_amt : ' || p_receipt_tbl(l_rcpt_tbl_row).principal_pmt_rcpt_amt);
959 print_debug(' loan_pmt_rcpt_amt : ' || p_receipt_tbl(l_rcpt_tbl_row).loan_pmt_rcpt_amt);
960
961 l_rcpt_tbl_row := p_receipt_tbl.next(l_rcpt_tbl_row);
962 END LOOP;
963 print_debug('*****END CONTENTS OF P_RECEIPT_TBL*****');
964 print_debug('*****************************************');
965
966
967 EXCEPTION
968 WHEN OTHERS THEN
969 print_debug('SQLCODE : ' || SQLCODE || ' SQLERRM : ' || SQLERRM);
970 END print_table_content;
971
972 BEGIN
973
974 FOR cur_khr IN c_khr_csr(p_khr_id) LOOP
975 l_error_flag := FALSE;
976 l_receipt_amt_success := 0;
977 l_receipt_amt_error := 0;
978
979 print_debug('====================================================================');
980 print_debug('Start - Daily Interest Processing for contract number => ' || cur_khr.contract_number);
981 print_debug(' Contract start date => ' || cur_khr.start_date);
982 print_debug(' Deal type => ' || cur_khr.deal_type);
983 print_debug(' Interest calculation basis => ' || cur_khr.interest_calculation_basis);
984 print_debug(' Revenue recognition method => ' || cur_khr.revenue_recognition_method);
985 receipt_date_range(
986 p_api_version => l_api_version,
987 p_init_msg_list => p_init_msg_list,
988 x_return_status => l_return_status,
989 x_msg_count => x_msg_count,
990 x_msg_data => x_msg_data,
991 p_contract_id => cur_khr.khr_id,
992 p_start_date => cur_khr.start_date,
993 p_due_date => NULL,
994 x_principal_balance => lx_principal_balance,
995 x_receipt_tbl => lx_receipt_tbl);
996
997 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
998 l_error_flag := TRUE;
999 print_debug(' Call to receipt_date_range failed.');
1000 END IF;
1001 --print_debug(' Starting principal balance : ' || lx_principal_balance);
1002 print_table_content(lx_receipt_tbl);
1003
1004 l_receipt_tbl_row:= lx_receipt_tbl.first;
1005 l_khr_start_date := cur_khr.start_date;
1006 l_range_start_date := cur_khr.start_date;
1007 WHILE (l_receipt_tbl_row IS NOT NULL) LOOP
1008 IF (NOT(l_error_flag)) THEN
1009 IF (lx_receipt_tbl(l_receipt_tbl_row).receipt_date = l_khr_start_date) THEN
1010 l_prev_daily_prin_amt := 0;
1011 l_daily_prin_amt := lx_receipt_tbl(l_receipt_tbl_row).receipt_amount;
1012
1013 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
1014 l_prev_daily_prin_amt := NVL(cur_daily_int_stm.exist_amount, 0);
1015 END LOOP;
1016 --print_debug(' Value of l_prev_daily_prin_amt: ' || l_prev_daily_prin_amt);
1017
1018 --adjust against existing streams
1019 l_daily_prin_amt := NVL(l_daily_prin_amt, 0) - NVL(l_prev_daily_prin_amt, 0);
1020 --print_debug(' After adjustment value of l_daily_prin_amt: ' || l_daily_prin_amt);
1021
1022 --create a principal accrual strm for entire rcpt amount
1023 IF (l_daily_prin_amt <> 0) THEN
1024 print_debug(' Creating stream for DAILY_INTEREST_PRINCIPAL.');
1025 OKL_VARIABLE_INTEREST_PVT.Create_Daily_Interest_Streams (
1026 p_api_version => l_api_version,
1027 p_init_msg_list => p_init_msg_list,
1028 x_return_status => l_return_status,
1029 x_msg_count => x_msg_count,
1030 x_msg_data => x_msg_data,
1031 p_contract_id => cur_khr.khr_id,
1032 p_amount => l_daily_prin_amt,
1033 p_due_date => lx_receipt_tbl(l_receipt_tbl_row).receipt_date,
1034 p_stream_type_purpose => 'DAILY_INTEREST_PRINCIPAL',
1035 p_create_invoice_flag => OKL_API.G_FALSE,
1036 p_process_flag => 'DAILY_INTEREST',
1037 p_currency_code => cur_khr.currency_code);
1038
1039 print_debug('Status of creating stream: ' || l_return_status);
1040 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1041 l_error_flag := TRUE;
1042 print_debug(' Error: creating stream for DAILY_INTEREST_PRINCIPAL.');
1043 ELSE
1044 print_debug(' Success: creating stream for DAILY_INTEREST_PRINCIPAL.');
1045 END IF;
1046 END IF;
1047
1048 --Bug# 7277007
1049 IF (NOT(l_error_flag)) THEN
1050
1051 l_receipt_amt_success := l_receipt_amt_success + lx_receipt_tbl(l_receipt_tbl_row).receipt_amount;
1052
1053 g_rpt_success_tbl_counter := 1;
1054 IF g_rpt_success_curr_tbl.EXISTS(cur_khr.currency_code) THEN
1055 g_rpt_success_tbl_counter := g_rpt_success_curr_tbl(cur_khr.currency_code).LAST + 1;
1056 END IF;
1057
1058 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).contract_number := cur_khr.contract_number;
1059 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).principal_balance := lx_principal_balance;
1060 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;
1061 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;
1062 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).int_start_date := l_khr_start_date;
1063 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).int_end_date := l_khr_start_date;
1064 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).daily_int_amt := 0;
1065 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;
1066 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).int_till_date_amt := 0;
1067 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);
1068 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).daily_int_adj_amt := 0;
1069 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).daily_prin_adj_amt := l_daily_prin_amt;
1070 ELSE
1071
1072 l_receipt_amt_error := l_receipt_amt_error + lx_receipt_tbl(l_receipt_tbl_row).receipt_amount;
1073
1074 g_rpt_error_tbl_counter := 1;
1075 IF g_rpt_error_curr_tbl.EXISTS(cur_khr.currency_code) THEN
1076 g_rpt_error_tbl_counter := g_rpt_error_curr_tbl(cur_khr.currency_code).LAST + 1;
1077 END IF;
1078
1079 g_rpt_error_curr_tbl(cur_khr.currency_code)(g_rpt_error_tbl_counter).contract_number := cur_khr.contract_number;
1080 g_rpt_error_curr_tbl(cur_khr.currency_code)(g_rpt_error_tbl_counter).product_name := cur_khr.product_name;
1081 g_rpt_error_curr_tbl(cur_khr.currency_code)(g_rpt_error_tbl_counter).interest_calc_basis := cur_khr.interest_calculation_basis;
1082 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;
1083 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;
1084
1085 IF (x_msg_count >= 1) THEN
1086 FOR i in 1..x_msg_count LOOP
1087 fnd_msg_pub.get (p_msg_index => i,
1088 p_encoded => 'F',
1089 p_data => lx_msg_data,
1090 p_msg_index_out => lx_msg_index_out);
1091
1092 g_rpt_error_curr_tbl(cur_khr.currency_code)(g_rpt_error_tbl_counter).error_msg_tbl(i) := SUBSTR(lx_msg_data,1,2000);
1093
1094 END LOOP;
1095 END IF;
1096 --Bug# 7277007
1097
1098 END IF;
1099
1100 ELSE
1101 --receipt date not equal khr start date
1102 --obtain interest due
1103 l_range_end_date := lx_receipt_tbl(l_receipt_tbl_row).receipt_date - 1;
1104 --print_debug(' Value of l_range_start_date: ' || l_range_start_date);
1105 --print_debug(' Value of l_range_end_date: ' || l_range_end_date);
1106 l_cal_int_amt := 0;
1107 l_start_bal := 0;
1108
1109 IF (cur_khr.deal_type = 'LOAN') THEN
1110 --get principal balance as of range start date
1111 FOR cur_principal_paid IN c_principal_paid_csr(cur_khr.khr_id, l_range_start_date) LOOP
1112 l_start_bal := NVL(lx_principal_balance, 0) - NVL(cur_principal_paid.principal_paid, 0);
1113 END LOOP; --c_principal_amt_csr
1114 --print_debug(' Value of l_start_bal: ' || l_start_bal);
1115
1116 --get asset termination date and termination value
1117 FOR cur_asset_term_val IN c_asset_term_val_csr(cur_khr.khr_id, l_range_start_date, l_range_end_date) LOOP
1118 l_cal_int_amt := l_cal_int_amt + OKL_VARIABLE_INTEREST_PVT.calculate_interest (
1119 p_api_version => l_api_version,
1120 p_init_msg_list => p_init_msg_list,
1121 x_return_status => l_return_status,
1122 x_msg_count => x_msg_count,
1123 x_msg_data => x_msg_data,
1124 p_contract_id => cur_khr.khr_id,
1125 p_from_date => l_range_start_date,
1126 p_to_date => cur_asset_term_val.term_date -1,
1127 p_principal_amount => l_start_bal,
1128 p_currency_code => cur_khr.currency_code);
1129
1130 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1131 l_error_flag := TRUE;
1132 END IF;
1133 --print_debug(' Value of l_cal_int_amt: ' || l_cal_int_amt);
1134
1135 --print_debug('Asset term value: ' || cur_asset_term_val.term_value);
1136 --print_debug('Asset term date: ' || cur_asset_term_val.term_date);
1137 l_range_start_date := cur_asset_term_val.term_date;
1138 l_start_bal := NVL(l_start_bal, 0) - NVL(cur_asset_term_val.term_value, 0);
1139 --print_debug(' Value of l_start_bal: ' || l_start_bal);
1140 END LOOP; --c_asset_term_val_csr
1141
1142 l_cal_int_amt := l_cal_int_amt + OKL_VARIABLE_INTEREST_PVT.calculate_interest (
1143 p_api_version => l_api_version,
1144 p_init_msg_list => p_init_msg_list,
1145 x_return_status => l_return_status,
1146 x_msg_count => x_msg_count,
1147 x_msg_data => x_msg_data,
1148 p_contract_id => cur_khr.khr_id,
1149 p_from_date => l_range_start_date,
1150 p_to_date => l_range_end_date,
1151 p_principal_amount => l_start_bal,
1152 p_currency_code => cur_khr.currency_code);
1153
1154 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1155 l_error_flag := TRUE;
1156 END IF;
1157 --print_debug(' Value of l_cal_int_amt: ' || l_cal_int_amt);
1158 ELSE --deal type = Rev Loan
1159 FOR cur_borrower_payment IN c_borrower_payment_csr(cur_khr.khr_id, l_range_start_date, l_range_end_date) LOOP
1160 --get payments maid upto the range start date
1161 FOR cur_payment_amount IN c_payment_amount_csr(cur_khr.khr_id, l_range_start_date) LOOP
1162 l_payment_amount := NVL(cur_payment_amount.payment_amount, 0);
1163 END LOOP;
1164 --print_debug(' Value of l_payment_amount: ' || l_payment_amount);
1165
1166 --get principal paid
1167 FOR cur_principal_paid IN c_principal_paid_csr(cur_khr.khr_id, l_range_start_date) LOOP
1168 l_principal_paid := NVL(cur_principal_paid.principal_paid, 0);
1169 END LOOP;
1170 --print_debug(' Value of l_principal_paid: ' || l_principal_paid);
1171
1172 l_start_bal := NVL(l_payment_amount, 0) - NVL(l_principal_paid, 0);
1173 --print_debug(' Value of l_start_bal: ' || l_start_bal);
1174
1175 l_cal_int_amt := l_cal_int_amt + OKL_VARIABLE_INTEREST_PVT.calculate_interest (
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_from_date => l_range_start_date,
1183 p_to_date => cur_borrower_payment.borrower_payment_date -1,
1184 p_principal_amount => l_start_bal,
1185 p_currency_code => cur_khr.currency_code);
1186
1187 --print_debug(' Value of l_cal_int_amt: ' || l_cal_int_amt);
1188 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1189 l_error_flag := TRUE;
1190 END IF;
1191
1192 l_range_start_date := cur_borrower_payment.borrower_payment_date;
1193 END LOOP;
1194
1195 --get payments maid upto the range start date
1196 FOR cur_payment_amount IN c_payment_amount_csr(cur_khr.khr_id, l_range_start_date) LOOP
1197 l_payment_amount := NVL(cur_payment_amount.payment_amount, 0);
1198 END LOOP;
1199 --print_debug(' Value of l_payment_amount: ' || l_payment_amount);
1200
1201 --get principal paid
1202 FOR cur_principal_paid IN c_principal_paid_csr(cur_khr.khr_id, l_range_start_date) LOOP
1203 l_principal_paid := NVL(cur_principal_paid.principal_paid, 0);
1204 END LOOP;
1205 --print_debug(' Value of l_principal_paid: ' || l_principal_paid);
1206
1207 l_start_bal := NVL(l_payment_amount, 0) - NVL(l_principal_paid, 0);
1208 --print_debug(' Value of l_start_bal: ' || l_start_bal);
1209
1210 l_cal_int_amt := l_cal_int_amt + OKL_VARIABLE_INTEREST_PVT.calculate_interest (
1211 p_api_version => l_api_version,
1212 p_init_msg_list => p_init_msg_list,
1213 x_return_status => l_return_status,
1214 x_msg_count => x_msg_count,
1215 x_msg_data => x_msg_data,
1216 p_contract_id => cur_khr.khr_id,
1217 p_from_date => l_range_start_date,
1218 p_to_date => l_range_end_date,
1219 p_principal_amount => l_start_bal,
1220 p_currency_code => cur_khr.currency_code);
1221
1222 --print_debug(' Value of l_cal_int_amt: ' || l_cal_int_amt);
1223
1224 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1225 l_error_flag := TRUE;
1226 END IF;
1227 END IF;
1228
1229 --split into daily interest and principal
1230 l_daily_int_amt := 0;
1231 l_daily_prin_amt := 0;
1232
1233 --Bug# 7277007
1234 l_daily_int_calc_amt := 0;
1235 l_daily_prin_calc_amt := 0;
1236
1237 IF (lx_receipt_tbl(l_receipt_tbl_row).receipt_amount > l_cal_int_amt) THEN
1238 l_daily_int_amt := NVL(l_cal_int_amt, 0);
1239 l_daily_prin_amt := NVL(lx_receipt_tbl(l_receipt_tbl_row).receipt_amount, 0) - NVL(l_daily_int_amt, 0);
1240 ELSE
1241 l_daily_int_amt := NVL(lx_receipt_tbl(l_receipt_tbl_row).receipt_amount, 0);
1242 END IF;
1243 --print_debug(' Value of l_daily_int_amt: ' || l_daily_int_amt);
1244 --print_debug(' Value of l_daily_prin_amt: ' || l_daily_prin_amt);
1245
1246 --Bug# 7277007
1247 l_daily_int_calc_amt := l_daily_int_amt;
1248 l_daily_prin_calc_amt := l_daily_prin_amt;
1249
1250 --check for existing daily interest streams
1251 l_prev_daily_int_amt := 0;
1252 l_prev_daily_prin_amt := 0;
1253 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
1254 l_prev_daily_int_amt := NVL(cur_daily_int_stm.exist_amount, 0);
1255 END LOOP;
1256
1257 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
1258 l_prev_daily_prin_amt := NVL(cur_daily_int_stm.exist_amount, 0);
1259 END LOOP;
1260 --print_debug(' Value of l_prev_daily_int_amt: ' || l_prev_daily_int_amt);
1261 --print_debug(' Value of l_prev_daily_prin_amt: ' || l_prev_daily_prin_amt);
1262
1263 --adjust against existing streams
1264 l_daily_int_amt := NVL(l_daily_int_amt, 0) - NVL(l_prev_daily_int_amt, 0);
1265 l_daily_prin_amt := NVL(l_daily_prin_amt, 0) - NVL(l_prev_daily_prin_amt, 0);
1266 --print_debug(' After adjustment value of l_daily_int_amt: ' || l_daily_int_amt);
1267 --print_debug(' After adjustment value of l_daily_prin_amt: ' || l_daily_prin_amt);
1268
1269 --create daily interest streams
1270 IF (l_daily_int_amt <> 0) AND (NOT(l_error_flag)) THEN
1271 print_debug(' Creating stream for DAILY_INTEREST_INTEREST.');
1272
1273 OKL_VARIABLE_INTEREST_PVT.Create_Daily_Interest_Streams (
1274 p_api_version => l_api_version,
1275 p_init_msg_list => p_init_msg_list,
1276 x_return_status => l_return_status,
1277 x_msg_count => x_msg_count,
1278 x_msg_data => x_msg_data,
1279 p_contract_id => cur_khr.khr_id,
1280 p_amount => l_daily_int_amt,
1281 p_due_date => lx_receipt_tbl(l_receipt_tbl_row).receipt_date,
1282 p_stream_type_purpose => 'DAILY_INTEREST_INTEREST',
1283 p_create_invoice_flag => OKL_API.G_FALSE,
1284 p_process_flag => 'DAILY_INTEREST',
1285 p_currency_code => cur_khr.currency_code);
1286
1287 print_debug('Status of creating stream: ' || l_return_status);
1288
1289 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1290 l_error_flag := TRUE;
1291 print_debug(' Error: creating stream for DAILY_INTEREST_INTEREST.');
1292 ELSE
1293 print_debug(' Success: creating stream for DAILY_INTEREST_INTEREST.');
1294 END IF;
1295 END IF;
1296
1297 IF (l_daily_prin_amt <> 0) AND (NOT(l_error_flag)) THEN
1298
1299 --check for excess payments
1300 l_excess_principal := 0;
1301 IF (cur_khr.deal_type = 'LOAN') THEN
1302 --check for excess loan payment for LOAN
1303 FOR cur_principal_paid IN c_principal_paid_csr(cur_khr.khr_id, lx_receipt_tbl(l_receipt_tbl_row).receipt_date) LOOP
1304 l_excess_principal := (NVL(cur_principal_paid.principal_paid, 0) + NVL(l_daily_prin_amt, 0)) - NVL(lx_principal_balance, 0);
1305 END LOOP;
1306
1307 ELSE
1308 --check for excess loan payment for REV-LOAN
1309 FOR cur_payment_amount IN c_payment_amount_csr(cur_khr.khr_id, lx_receipt_tbl(l_receipt_tbl_row).receipt_date) LOOP
1310 FOR cur_principal_paid IN c_principal_paid_csr(cur_khr.khr_id, lx_receipt_tbl(l_receipt_tbl_row).receipt_date) LOOP
1311 l_excess_principal := (NVL(cur_principal_paid.principal_paid, 0) + NVL(l_daily_prin_amt, 0)) - NVL(cur_payment_amount.payment_amount, 0);
1312 END LOOP;
1313 END LOOP;
1314 END IF;
1315
1316 IF (l_excess_principal <= 0) AND (NOT(l_error_flag)) THEN
1317 l_excess_principal := 0;
1318 ELSE
1319 --principal is in excess
1320 --print_debug(' Value of l_excess_principal: ' || l_excess_principal);
1321 l_daily_prin_amt := NVL(l_daily_prin_amt, 0) - NVL(l_excess_principal, 0);
1322 END IF;
1323
1324 print_debug(' Creating stream for DAILY_INTEREST_PRINCIPAL.');
1325
1326 OKL_VARIABLE_INTEREST_PVT.Create_Daily_Interest_Streams (
1327 p_api_version => l_api_version,
1328 p_init_msg_list => p_init_msg_list,
1329 x_return_status => l_return_status,
1330 x_msg_count => x_msg_count,
1331 x_msg_data => x_msg_data,
1332 p_contract_id => cur_khr.khr_id,
1333 p_amount => l_daily_prin_amt,
1334 p_due_date => lx_receipt_tbl(l_receipt_tbl_row).receipt_date,
1335 p_stream_type_purpose => 'DAILY_INTEREST_PRINCIPAL',
1336 p_create_invoice_flag => OKL_API.G_FALSE,
1337 p_process_flag => 'DAILY_INTEREST',
1338 p_currency_code => cur_khr.currency_code);
1339
1340 print_debug('Status of creating stream: ' || l_return_status);
1341
1342 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1343 l_error_flag := TRUE;
1344 print_debug(' Error: creating stream for DAILY_INTEREST_PRINCIPAL.');
1345 ELSE
1346 print_debug(' Success: creating stream for DAILY_INTEREST_PRINCIPAL.');
1347 END IF;
1348
1349 --check for excess principal paid
1350 l_excess_principal_paid := 0;
1351 FOR cur_excess_principal_paid IN c_excess_principal_paid_csr(cur_khr.khr_id) LOOP
1352 /*l_excess_principal_paid := NVL(cur_excess_principal_paid.excess_principal_paid, 0);
1353 print_debug(' Value of l_excess_principal_paid: ' || l_excess_principal_paid);
1354 l_excess_principal := NVL(l_excess_principal, 0) - NVL(l_excess_principal_paid, 0);*/
1355 null;
1356 END LOOP;
1357
1358 IF (l_excess_principal <> 0) AND (NOT(l_error_flag)) THEN
1359
1360 --create stream for excess loan payment
1361 print_debug(' Creating stream for EXCESS_LOAN_PAYMENT_PAID.');
1362
1363 OKL_VARIABLE_INTEREST_PVT.Create_Daily_Interest_Streams (
1364 p_api_version => l_api_version,
1365 p_init_msg_list => p_init_msg_list,
1366 x_return_status => l_return_status,
1367 x_msg_count => x_msg_count,
1368 x_msg_data => x_msg_data,
1369 p_contract_id => cur_khr.khr_id,
1370 p_amount => l_excess_principal,
1371 p_due_date => lx_receipt_tbl(l_receipt_tbl_row).receipt_date,
1372 p_stream_type_purpose => 'EXCESS_LOAN_PAYMENT_PAID',
1373 p_create_invoice_flag => OKL_API.G_FALSE,
1374 p_process_flag => 'DAILY_INTEREST',
1375 p_currency_code => cur_khr.currency_code);
1376
1377 print_debug('Status of creating stream: ' || l_return_status);
1378
1379 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1380 l_error_flag := TRUE;
1381 print_debug(' Error: creating stream for EXCESS_LOAN_PAYMENT_PAID.');
1382 ELSE
1383 print_debug(' Success: creating stream for EXCESS_LOAN_PAYMENT_PAID.');
1384 END IF;
1385 END IF;
1386 END IF;
1387
1388 --Bug# 7277007
1389 IF (NOT(l_error_flag)) THEN
1390
1391 l_receipt_amt_success := l_receipt_amt_success + lx_receipt_tbl(l_receipt_tbl_row).receipt_amount;
1392
1393 g_rpt_success_tbl_counter := 1;
1394 IF g_rpt_success_curr_tbl.EXISTS(cur_khr.currency_code) THEN
1395 g_rpt_success_tbl_counter := g_rpt_success_curr_tbl(cur_khr.currency_code).LAST + 1;
1396 END IF;
1397
1398 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).contract_number := cur_khr.contract_number;
1399 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).principal_balance := l_start_bal;
1400 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;
1401 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;
1402 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).int_start_date := l_range_start_date;
1403 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).int_end_date := l_range_end_date;
1404 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).daily_int_amt := l_daily_int_calc_amt;
1405 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).daily_prin_amt := l_daily_prin_calc_amt;
1406 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).int_till_date_amt := l_prev_daily_int_amt;
1407 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).prin_till_date_amt := l_prev_daily_prin_amt;
1408 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).daily_int_adj_amt := l_daily_int_amt;
1409 g_rpt_success_curr_tbl(cur_khr.currency_code)(g_rpt_success_tbl_counter).daily_prin_adj_amt := l_daily_prin_amt;
1410 ELSE
1411
1412 l_receipt_amt_error := l_receipt_amt_error + lx_receipt_tbl(l_receipt_tbl_row).receipt_amount;
1413
1414 g_rpt_error_tbl_counter := 1;
1415 IF g_rpt_error_curr_tbl.EXISTS(cur_khr.currency_code) THEN
1416 g_rpt_error_tbl_counter := g_rpt_error_curr_tbl(cur_khr.currency_code).LAST + 1;
1417 END IF;
1418
1419 g_rpt_error_curr_tbl(cur_khr.currency_code)(g_rpt_error_tbl_counter).contract_number := cur_khr.contract_number;
1420 g_rpt_error_curr_tbl(cur_khr.currency_code)(g_rpt_error_tbl_counter).product_name := cur_khr.product_name;
1421 g_rpt_error_curr_tbl(cur_khr.currency_code)(g_rpt_error_tbl_counter).interest_calc_basis := cur_khr.interest_calculation_basis;
1422 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;
1423 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;
1424
1425 IF (x_msg_count >= 1) THEN
1426 FOR i in 1..x_msg_count LOOP
1427 fnd_msg_pub.get (p_msg_index => i,
1428 p_encoded => 'F',
1429 p_data => lx_msg_data,
1430 p_msg_index_out => lx_msg_index_out);
1431
1432 g_rpt_error_curr_tbl(cur_khr.currency_code)(g_rpt_error_tbl_counter).error_msg_tbl(i) := SUBSTR(lx_msg_data,1,2000);
1433
1434 END LOOP;
1435 END IF;
1436 END IF;
1437 --Bug# 7277007
1438
1439 END IF;
1440 END IF;
1441
1442 --if rcpt unapp results in a zero DI-Principal strm elem, then the int calculation
1443 --must start from the previous start date
1444 IF ((l_daily_prin_amt + l_prev_daily_prin_amt) <> 0) THEN
1445 l_range_start_date := lx_receipt_tbl(l_receipt_tbl_row).receipt_date;
1446 END IF;
1447 l_receipt_tbl_row:= lx_receipt_tbl.next(l_receipt_tbl_row);
1448 END LOOP; --lx_receipt_tbl
1449
1450 --Bug# 7277007
1451 IF g_rpt_summary_tbl.EXISTS(cur_khr.currency_code)
1452 THEN
1453 g_rpt_summary_tbl(cur_khr.currency_code).total_receipt_amt_success :=
1454 g_rpt_summary_tbl(cur_khr.currency_code).total_receipt_amt_success + l_receipt_amt_success;
1455
1456 g_rpt_summary_tbl(cur_khr.currency_code).total_receipt_amt_error :=
1457 g_rpt_summary_tbl(cur_khr.currency_code).total_receipt_amt_error + l_receipt_amt_error;
1458
1459 ELSE
1460 g_rpt_summary_tbl(cur_khr.currency_code).total_receipt_amt_success := l_receipt_amt_success;
1461 g_rpt_summary_tbl(cur_khr.currency_code).total_receipt_amt_error := l_receipt_amt_error;
1462
1463 END IF;
1464 --Bug# 7277007
1465
1466 IF (l_error_flag) THEN
1467 print_debug('ERROR - Daily Interest Processing for contract number => ' || cur_khr.contract_number);
1468 END IF;
1469 print_debug('====================================================================');
1470 print_debug('End - Daily Interest Processing for contract number => ' || cur_khr.contract_number);
1471 END LOOP; --c_khr_csr
1472
1473 EXCEPTION
1474 ------------------------------------------------------------
1475 -- Exception handling
1476 ------------------------------------------------------------
1477
1478 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1479
1480 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1481 p_api_name => l_api_name,
1482 p_pkg_name => G_PKG_NAME,
1483 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1484 x_msg_count => x_msg_count,
1485 x_msg_data => x_msg_data,
1486 p_api_type => '_PVT');
1487
1488 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1489
1490 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1491 p_api_name => l_api_name,
1492 p_pkg_name => G_PKG_NAME,
1493 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1494 x_msg_count => x_msg_count,
1495 x_msg_data => x_msg_data,
1496 p_api_type => '_PVT');
1497
1498 WHEN OTHERS THEN
1499
1500 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1501 p_api_name => l_api_name,
1502 p_pkg_name => G_PKG_NAME,
1503 p_exc_name => 'OTHERS',
1504 x_msg_count => x_msg_count,
1505 x_msg_data => x_msg_data,
1506 p_api_type => '_PVT');
1507
1508 END daily_interest;
1509
1510 --dkagrawa created for concurrent program OKL Daily Interest Calculation
1511 PROCEDURE calculate_daily_interest(
1512 errbuf OUT NOCOPY VARCHAR2,
1513 retcode OUT NOCOPY NUMBER,
1514 p_contract_number IN VARCHAR2
1515 ) IS
1516 l_api_version CONSTANT NUMBER DEFAULT 1.0;
1517
1518 lx_msg_count NUMBER;
1519 lx_msg_data VARCHAR2(450);
1520 l_msg_index_out NUMBER;
1521 lx_return_status VARCHAR(1);
1522 l_khr_id NUMBER DEFAULT NULL;
1523 l_contract_number okc_k_headers_b.contract_number%type;
1524 --dkagrawa changed the cursor to use new view okl_prod_qlty_val_uv for product quality value instead of product_parameter_v
1525 CURSOR check_contract_csr(cp_contract_number IN VARCHAR2) IS
1526 SELECT khr.id khr_id
1527 ,khr.contract_number
1528 ,khr.authoring_org_id
1529 FROM okl_k_headers_full_v khr,
1530 okl_prod_qlty_val_uv ppm,
1531 okc_statuses_b ste
1532 WHERE khr.contract_number = NVL(cp_contract_number, khr.contract_number)
1533 AND khr.pdt_id = ppm.pdt_id
1534 AND ppm.quality_name = 'REVENUE_RECOGNITION_METHOD'
1535 AND ppm.quality_val = 'ACTUAL'
1536 AND khr.sts_code = ste.code
1537 AND ste.ste_code in ('ACTIVE', 'TERMINATED');
1538
1539 BEGIN
1540
1541 --set variable rate global variables so that interest calculation
1542 --and interest rate parameter updates works correctly
1543 OKL_VARIABLE_INTEREST_PVT.G_CALC_METHOD_CODE := 'DAILY_INTEREST';
1544 OKL_VARIABLE_INTEREST_PVT.G_INTEREST_CALCULATION_BASIS := 'DAILY_INTEREST';
1545 OKL_VARIABLE_INTEREST_PVT.G_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1546
1547 OPEN check_contract_csr(p_contract_number);
1548 LOOP
1549 FETCH check_contract_csr INTO l_khr_id, l_contract_number, OKL_VARIABLE_INTEREST_PVT.G_AUTHORING_ORG_ID;
1550
1551 IF (check_contract_csr%NOTFOUND) THEN
1552 CLOSE check_contract_csr;
1553 EXIT;
1554 END IF;
1555
1556 daily_interest(
1557 p_api_version => l_api_version,
1558 p_init_msg_list => FND_API.G_FALSE,
1559 x_return_status => lx_return_status,
1560 x_msg_count => lx_msg_count,
1561 x_msg_data => lx_msg_data,
1562 p_khr_id => l_khr_id
1563 );
1564 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1565 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1566 ELSIF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
1567 RAISE OKL_API.G_EXCEPTION_ERROR;
1568 END IF;
1569
1570 END LOOP;
1571
1572 OKL_VARIABLE_INTEREST_PVT.G_CALC_METHOD_CODE := NULL;
1573 OKL_VARIABLE_INTEREST_PVT.G_INTEREST_CALCULATION_BASIS := NULL;
1574 OKL_VARIABLE_INTEREST_PVT.G_REQUEST_ID := NULL;
1575
1576 errbuf := lx_msg_data;
1577
1578 --Bug# 7277007
1579 print_output(p_contract_number => p_contract_number);
1580
1581 retcode := 0;
1582 EXCEPTION
1583 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1584 retcode := 2;
1585 IF check_contract_csr%ISOPEN THEN
1586 CLOSE check_contract_csr;
1587 END IF;
1588 lx_return_status := Okl_Api.HANDLE_EXCEPTIONS(G_APP_NAME,
1589 G_PKG_NAME,
1590 'Okl_Api.G_RET_STS_ERROR',
1591 lx_msg_count,
1592 lx_msg_data,
1593 '_PVT');
1594 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1595 retcode := 2;
1596 IF check_contract_csr%ISOPEN THEN
1597 CLOSE check_contract_csr;
1598 END IF;
1599 lx_return_status := OKL_API.HANDLE_EXCEPTIONS(
1600 p_api_name => G_APP_NAME,
1601 p_pkg_name => G_PKG_NAME,
1602 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1603 x_msg_count => lx_msg_count,
1604 x_msg_data => lx_msg_data,
1605 p_api_type => '_PVT');
1606 WHEN OTHERS THEN
1607 retcode := 2;
1608 errbuf := SQLERRM;
1609 IF check_contract_csr%ISOPEN THEN
1610 CLOSE check_contract_csr;
1611 END IF;
1612 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Error '||SQLCODE||': '||SQLERRM);
1613 END calculate_daily_interest;
1614
1615 END OKL_DAILY_INTEREST_CALC_PVT;