[Home] [Help]
PACKAGE BODY: APPS.OKL_INTEREST_CALC_PVT
Source
1 PACKAGE BODY OKL_INTEREST_CALC_PVT AS
2 /* $Header: OKLRITUB.pls 120.28 2011/02/23 10:08:39 rpillay noship $ */
3 -- Start of wraper code generated automatically by Debug code generator
4 L_MODULE VARCHAR2(40) := 'LEASE.ACCOUNTING.INTEREST';
5 L_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 L_LEVEL_PROCEDURE NUMBER;
7 IS_DEBUG_PROCEDURE_ON BOOLEAN;
8 -- End of wraper code generated automatically by Debug code generator
9
10
11 PROCEDURE CREATE_TRX_ACCT(p_khr_id IN NUMBER,
12 p_product_id IN NUMBER,
13 p_amount IN NUMBER,
14 p_calc_date IN DATE,
15 x_source_id OUT NOCOPY NUMBER,
16 x_return_status OUT NOCOPY VARCHAR2)
17
18
19 IS
20 -- Code can be commented for billing transaction- HKPATEL
21 /*
22 l_tcnv_rec_in OKL_TRX_CONTRACTS_PUB.TCNV_REC_TYPE;
23 l_tcnv_rec_out OKL_TRX_CONTRACTS_PUB.TCNV_REC_TYPE;
24 l_tclv_tbl_in OKL_TRX_CONTRACTS_PUB.TCLV_TBL_TYPE;
25 l_tclv_tbl_out OKL_TRX_CONTRACTS_PUB.TCLV_TBL_TYPE;
26 */
27 -- Commented code for billing transaction ends here- HKPATEL
28
29 l_khrv_rec_in OKL_CONTRACT_PUB.khrv_rec_type;
30 l_khrv_rec_out OKL_CONTRACT_PUB.khrv_rec_type;
31 l_chrv_rec_in okl_okc_migration_pvt.chrv_rec_type;
32 l_chrv_rec_out okl_okc_migration_pvt.chrv_rec_type;
33 l_error_msg_rec OKL_ACCOUNTING_UTIL.Error_message_Type;
34
35 l_tmpl_identify_rec OKL_ACCOUNT_DIST_PUB.TMPL_IDENTIFY_REC_TYPE;
36 l_dist_info_rec OKL_ACCOUNT_DIST_PUB.DIST_INFO_REC_TYPE;
37 l_ctxt_val_tbl OKL_ACCOUNT_DIST_PUB.CTXT_VAL_TBL_TYPE;
38 l_acc_gen_tbl OKL_ACCOUNT_DIST_PUB.ACC_GEN_PRIMARY_KEY;
39 l_avlv_tbl OKL_TMPT_SET_PUB.avlv_tbl_type;
40 l_amount_tbl OKL_ACCOUNT_DIST_PUB.amount_tbl_type;
41
42 l_try_id NUMBER;
43 l_sty_id NUMBER;
44 l_product_id NUMBER;
45 l_amount NUMBER := 0;
46
47 l_init_msg_list VARCHAR2(1) := OKL_API.G_FALSE;
48 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
49 l_msg_count NUMBER;
50 l_msg_data VARCHAR2(2000);
51
52 -- Code added by HKPATEL for billing transaction
53 l_api_version CONSTANT NUMBER := 1;
54 l_line_number CONSTANT NUMBER := 1;
55 l_int_desc VARCHAR2(4000) := 'Interest Calculation';
56
57 --Bug# 10628703
58 i_tilv_rec okl_til_pvt.tilv_rec_type;
59 i_tilv_tbl okl_til_pvt.tilv_tbl_type;
60 lx_tilv_tbl okl_til_pvt.tilv_tbl_type;
61
62 i_taiv_rec okl_tai_pvt.taiv_rec_type;
63 lx_taiv_rec okl_tai_pvt.taiv_rec_type;
64
65 i_tldv_rec okl_tld_pvt.tldv_rec_type;
66 i_tldv_tbl okl_tld_pvt.tldv_tbl_type;
67 lx_tldv_tbl okl_tld_pvt.tldv_tbl_type;
68 --Bug# 10628703
69
70 -- Added code by HKPATEL ends here
71
72 -- Added by Santonyr on 27-Nov-2002 for Multi-Currency Changes
73 l_functional_currency GL_LEDGERS_PUBLIC_V.currency_code%TYPE;
74 l_currency_code okl_trx_contracts.currency_code%TYPE;
75 l_currency_conversion_type okl_trx_contracts.currency_conversion_type%TYPE;
76 l_currency_conversion_rate okl_trx_contracts.currency_conversion_rate%TYPE;
77 l_currency_conversion_date okl_trx_contracts.currency_conversion_date%TYPE;
78
79 --Bug 4622198
80 l_fact_sync_code VARCHAR2(2000);
81 l_inv_acct_code VARCHAR2(2000);
82 l_scs_code VARCHAR2(2000);
83
84 -- Derived the currency conversion factors from Contracts table
85
86 CURSOR curr_csr (l_khr_id NUMBER) IS
87 select CHRB.CURRENCY_CODE,
88 KHR.CURRENCY_CONVERSION_TYPE,
89 KHR.CURRENCY_CONVERSION_RATE,
90 KHR.CURRENCY_CONVERSION_DATE
91 from OKC_K_HEADERS_B CHRB,OKL_K_HEADERS KHR
92 WHERE KHR.ID = CHRB.ID
93 AND CHRB.ID = l_khr_id;
94
95 CURSOR try_csr IS
96 SELECT id
97 FROM OKL_TRX_TYPES_TL
98 WHERE name = 'Billing'
99 AND language = 'US';
100
101 -- cursor to get scs_code
102 CURSOR scs_code_csr IS
103 SELECT scs_code
104 FROM OKL_K_HEADERS_FULL_V
105 WHERE id = p_khr_id;
106
107
108 --commented as a part of user defined streams change - kmotepal
109 -- Bug 3940088
110
111 /* CURSOR sty_csr IS
112 SELECT ID
113 FROM OKL_STRM_TYPE_TL
114 WHERE name = 'INTERIM INTEREST' ; */
115
116
117 l_org_id NUMBER;
118
119
120 Cursor sales_csr(v_khr_id NUMBER) IS
121 SELECT ct.object1_id1 id
122 from okc_contacts ct,
123 okc_contact_sources csrc,
124 okc_k_party_roles_b pty,
125 okc_k_headers_b chr
126 where ct.cpl_id = pty.id
127 and ct.cro_code = csrc.cro_code
128 and ct.jtot_object1_code = csrc.jtot_object_code
129 and ct.dnz_chr_id = chr.id
130 and pty.rle_code = csrc.rle_code
131 and csrc.cro_code = 'SALESPERSON'
132 and csrc.rle_code = 'LESSOR'
133 and csrc.buy_or_sell = chr.buy_or_sell
134 and pty.dnz_chr_id = chr.id
135 and pty.chr_id = chr.id
136 and chr.id = v_khr_id;
137
138 l_sales_rep OKC_CONTACTS.object1_id1%TYPE;
139
140 CURSOR trx_csr IS
141 SELECT cust_trx_type_id
142 FROM ra_cust_trx_types
143 WHERE name = 'Invoice-OKL';
144
145 l_trx_type NUMBER;
146
147 Cursor Billto_csr(v_khr_id NUMBER) IS
148 SELECT object1_id1 cust_acct_site_id
149 FROM okc_rules_b rul
150 WHERE rul.rule_information_category = 'BTO'
151 and exists (select '1'
152 from okc_rule_groups_b rgp
153 where rgp.id = rul.rgp_id
154 and rgp.rgd_code = 'LABILL'
155 and rgp.chr_id = rul.dnz_chr_id
156 and rgp.chr_id = v_khr_id );
157
158 l_ar_site_use OKC_RULES_B.object1_id1%TYPE;
159 -- Added by dpsingh for LE Uptake
160 -- cursor to get the contract number
161 CURSOR contract_num_csr (p_ctr_id NUMBER) IS
162 SELECT contract_number
163 FROM okc_k_headers_b
164 WHERE id = p_ctr_id;
165
166 l_legal_entity_id Number;
167 l_cntrct_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
168
169 BEGIN
170
171 OPEN try_csr;
172 FETCH try_csr INTO l_try_id;
173 CLOSE try_csr;
174
175 --kmotepal calling a util to get primary stream id as a part of user defined streams change
176 -- Bug 3940088
177
178 Okl_Streams_Util.get_primary_stream_type(p_khr_id,'PREFUNDING_INTEREST_PAYMENT',l_return_status,l_sty_id);
179
180 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
181 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
182 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
183 RAISE OKL_API.G_EXCEPTION_ERROR;
184 END IF;
185
186 -- Fetch the functional currency
187 l_functional_currency := OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE;
188
189 -- Added by Santonyr on 22-Nov-2002. Multi-Currency Changes
190 -- Fetch the currency conversion factors from Contracts table
191
192 FOR curr_rec IN curr_csr(p_khr_id) LOOP
193 l_currency_code := curr_rec.currency_code;
194 l_currency_conversion_type := curr_rec.currency_conversion_type;
195 l_currency_conversion_rate := curr_rec.currency_conversion_rate;
196 l_currency_conversion_date := curr_rec.currency_conversion_date;
197 END LOOP;
198
199 -- Fetch the currency conversion factors from GL_DAILY_RATES if the
200 -- conversion type is not 'USER'.
201
202 IF UPPER(l_currency_conversion_type) <> 'USER' THEN
203 l_currency_conversion_date := SYSDATE;
204 l_currency_conversion_rate := okl_accounting_util.get_curr_con_rate
205 (p_from_curr_code => l_currency_code,
206 p_to_curr_code => l_functional_currency,
207 p_con_date => l_currency_conversion_date,
208 p_con_type => l_currency_conversion_type);
209
210 END IF; -- End IF for (UPPER(l_currency_conversion_type) <> 'USER')
211
212 l_amount := OKL_ACCOUNTING_UTIL.CROSS_CURRENCY_ROUND_AMOUNT
213 (p_amount => p_amount,
214 p_currency_code => l_currency_code);
215
216 -- Code can be commented to create billing transaction - HKPATEL
217
218 -- Code can be commented till here - HKPATEL
219
220 -- Code needed for billing transaction- HKPATEL
221 ----------------------------------------------------------------------------------
222 -- Preparing Invoice Header.
223 ----------------------------------------------------------------------------------
224 --Added by dpsingh for LE Uptake
225 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(p_khr_id) ;
226 IF l_legal_entity_id IS NOT NULL THEN
227 i_taiv_rec.legal_entity_id := l_legal_entity_id;
228 ELSE
229 OPEN contract_num_csr(p_khr_id);
230 FETCH contract_num_csr INTO l_cntrct_number;
231 CLOSE contract_num_csr;
232 Okl_Api.set_message(p_app_name => g_app_name,
233 p_msg_name => 'OKL_LE_NOT_EXIST_CNTRCT',
234 p_token1 => 'CONTRACT_NUMBER',
235 p_token1_value => l_cntrct_number);
236 RAISE OKL_API.G_EXCEPTION_ERROR;
237 END IF;
238
239 i_taiv_rec.try_id := l_try_id;
240 i_taiv_rec.khr_id := p_khr_id;
241 i_taiv_rec.date_entered := SYSDATE;
242 i_taiv_rec.date_invoiced := SYSDATE;
243 i_taiv_rec.description := l_int_desc;
244 i_taiv_rec.amount := l_amount;
245 i_taiv_rec.trx_status_code := 'SUBMITTED';
246 -- Populate the currency conversion factors
247 i_taiv_rec.currency_code := l_currency_code;
248 i_taiv_rec.currency_conversion_type := l_currency_conversion_type;
249 i_taiv_rec.currency_conversion_rate := l_currency_conversion_rate;
250 i_taiv_rec.currency_conversion_date := l_currency_conversion_date;
251 --Bug# 10628703
252 i_taiv_rec.okl_source_billing_trx := 'PREFUNDING_INTEREST';
253
254 i_tilv_rec.line_number := l_line_number;
255 i_tilv_rec.description := l_int_desc;
256 i_tilv_rec.amount := l_amount;
257 i_tilv_rec.sty_id := l_sty_id;
258 i_tilv_rec.inv_receiv_line_code := 'LINE';
259
260 i_tilv_tbl(1) := i_tilv_rec;
261
262 -- Start of wraper code generated automatically by Debug code generator for okl_trx_ar_invoices_pub.insert_trx_ar_invoices
263 IF(L_DEBUG_ENABLED='Y') THEN
264 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
265 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
266 END IF;
267 IF(IS_DEBUG_PROCEDURE_ON) THEN
268 BEGIN
269 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRITUB.pls call OKL_INTERNAL_BILLING_PVT.create_billing_trx ');
270 END;
271 END IF;
272
273 --Bug# 10628703
274 -- R12 B Billing Architecture
275 -- call central Billing API to create transaction and accounting distributions
276 OKL_INTERNAL_BILLING_PVT.create_billing_trx
277 ( p_api_version => l_api_version
278 ,p_init_msg_list => l_init_msg_list
279 ,x_return_status => l_return_status
280 ,x_msg_count => l_msg_count
281 ,x_msg_data => l_msg_data
282 ,p_taiv_rec => i_taiv_rec
283 ,p_tilv_tbl => i_tilv_tbl
284 ,p_tldv_tbl => i_tldv_tbl
285 ,x_taiv_rec => lx_taiv_rec
286 ,x_tilv_tbl => lx_tilv_tbl
287 ,x_tldv_tbl => lx_tldv_tbl);
288
289 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
290 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
291 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
292 RAISE OKL_API.G_EXCEPTION_ERROR;
293 END IF;
294
295 IF(IS_DEBUG_PROCEDURE_ON) THEN
296 BEGIN
297 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRITUB.pls call OKL_INTERNAL_BILLING_PVT.create_billing_trx ');
298 END;
299 END IF;
300
301 -- Code needed for billing transaction ends here - HKPATEL
302
303
304 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
305
306 l_khrv_rec_in.ID := p_khr_id;
307 l_khrv_rec_in.DATE_LAST_INTERIM_INTEREST_CAL := p_calc_date;
308 l_chrv_rec_in.ID := p_khr_id;
309
310 -- Start of wraper code generated automatically by Debug code generator for OKL_CONTRACT_PUB.update_contract_header
311 IF(IS_DEBUG_PROCEDURE_ON) THEN
312 BEGIN
313 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRITUB.pls call OKL_CONTRACT_PUB.update_contract_header ');
314 END;
315 END IF;
316 OKL_CONTRACT_PUB.update_contract_header(p_api_version => 1.0,
317 p_init_msg_list => l_init_msg_list,
318 x_return_status => l_return_status,
319 x_msg_count => l_msg_count,
320 x_msg_data => l_msg_data,
321 p_restricted_update => OKL_API.G_TRUE,
322 p_chrv_rec => l_chrv_rec_in,
323 p_khrv_rec => l_khrv_rec_in,
324 p_edit_mode => 'N',
325 x_chrv_rec => l_chrv_rec_out,
326 x_khrv_rec => l_khrv_rec_out);
327
328
329 IF(IS_DEBUG_PROCEDURE_ON) THEN
330 BEGIN
331 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRITUB.pls call OKL_CONTRACT_PUB.update_contract_header ');
332 END;
333 END IF;
334 -- End of wraper code generated automatically by Debug code generator for OKL_CONTRACT_PUB.update_contract_header
335
336
337 END IF;
338
339 x_return_status := l_return_status;
340
341 -- Code for billing transaction - HKPATEL
342 --Bug# 10628703
343 x_source_id := lx_tilv_tbl(1).id;
344 -- Code for billing transaction ends here
345
346
347 END CREATE_TRX_ACCT;
348
349
350
351 PROCEDURE CALC_INTEREST(p_contract_number IN VARCHAR2,
352 p_start_date IN DATE,
353 p_end_date IN DATE,
354 p_mode IN VARCHAR2,
355 x_amount OUT NOCOPY NUMBER,
356 x_return_status OUT NOCOPY VARCHAR2)
357
358 IS
359
360 -- Cursor to select the interest Name
361 --Bug# 10628703
362 -- Modified cursor to fetch Index Name from Prefunding Terms
363 -- and Conditions
364 CURSOR indx_csr(p_chr_id IN NUMBER) IS
365 SELECT idx.name
366 FROM OKC_RULES_B rule, OKL_INDICES idx, OKC_RULE_GROUPS_B rgp
367 WHERE rule.rule_information_category = 'LAIVAR'
368 AND rule.dnz_chr_id = p_chr_id
369 AND rule.rule_information2 = idx.id
370 AND rule.rgp_id = rgp.id
371 AND rgp.rgd_code = 'LAPREF'
372 AND rgp.chr_id = p_chr_id;
373
374 -- Changed the select statementby Santonyr on 04-Aug-2004 to improve the performance
375 -- sjalasut, modified the cursor to refer consolidated invoice id from okl_cnsld_ap_invs_all
376 -- added okl_txl_ap_inv_lns_all_b in the from clause as khr_id now moves to the
377 -- transaction line level.
378 -- changes made as part of OKLR12B disbursements project.
379 CURSOR change_csr(v_khr_id NUMBER,
380 v_start_date DATE,
381 v_end_date DATE,
382 v_name OKL_INDICES.NAME%TYPE) IS
383 SELECT ac.check_date change_date
384 FROM OKL_TRX_AP_INVOICES_B oklinv,
385 okl_txl_ap_inv_lns_all_b tpl,
386 okl_cnsld_ap_invs_all okl_cnsld,
387 AP_INVOICES inv,
388 AP_INVOICE_PAYMENTS pay,
389 AP_CHECKS AC,
390 fnd_application fnd_app
391 WHERE oklinv.id = tpl.tap_id
392 AND tpl.khr_id = v_khr_id
393 AND tpl.cnsld_ap_inv_id = okl_cnsld.cnsld_ap_inv_id
394 AND inv.application_id = fnd_app.application_id
395 AND fnd_app.application_short_name = 'OKL'
396 AND inv.product_table = 'OKL_CNSLD_AP_INVS_ALL'
397 AND okl_cnsld.cnsld_ap_inv_id = to_number(inv.reference_key1)
398 AND oklinv.funding_type_code = 'PREFUNDING'
399 --Bug# 10628703
400 --AND oklinv.vendor_invoice_number = inv.invoice_num -- sjalasut, is this required now?
401 AND inv.invoice_id = pay.invoice_id
402 AND pay.check_id = ac.check_id
403 AND ac.check_date >= v_start_date
404 AND ac.check_date <= v_end_date
405 UNION
406 SELECT oiv.datetime_valid change_date
407 FROM OKL_INDEX_VALUES oiv,
408 OKL_INDICES oi
409 WHERE oiv.idx_id = oi.id
410 AND oi.name = v_name
411 AND oiv.datetime_valid >= v_start_date
412 AND oiv.datetime_valid <= v_end_date;
413
414 -- This Cursor will Return Principal Funding Amount on any Particular Day
415
416 -- Changed the select statement by Santonyr on 04-Aug-2004 to improve the performance
417 -- sjalasut, modified the below cursor to have included okl_txl_ap_inv_lns_all_b
418 -- so that khr_id now refers from this table and also introduced the new
419 -- okl consolidation table okl_cnsld_ap_invs_all to join the consolidated invoices
420 -- from the transaction lines table.
421 CURSOR princ_csr(v_date IN DATE, v_khr_id IN NUMBER) IS
422 SELECT SUM(pay.amount)
423 FROM OKL_TRX_AP_INVOICES_B oklinv,
424 okl_txl_ap_inv_lns_all_b okl_inv_ln,
425 okl_cnsld_ap_invs_all okl_cnsld,
426 AP_INVOICES inv,
427 AP_INVOICE_PAYMENTS pay,
428 AP_CHECKS AC,
429 fnd_application fnd_app
430 WHERE oklinv.id = okl_inv_ln.tap_id
431 AND okl_inv_ln.khr_id = v_khr_id
432 AND okl_inv_ln.cnsld_ap_inv_id = okl_cnsld.cnsld_ap_inv_id
433 AND inv.application_id = fnd_app.application_id
434 AND fnd_app.application_short_name = 'OKL'
435 AND inv.product_table = 'OKL_CNSLD_AP_INVS_ALL'
436 AND okl_cnsld.cnsld_ap_inv_id = to_number(inv.reference_key1)
437 AND oklinv.funding_type_code = 'PREFUNDING'
438 --Bug# 10628703
439 --AND oklinv.vendor_invoice_number = inv.invoice_num -- sjalasut, is this required now?
440 AND inv.invoice_id = pay.invoice_id
441 AND pay.check_id = ac.check_id
442 AND ac.check_date <= v_date;
443
444
445 -- This cursor will return the rate of interest as on a particular Day.
446 CURSOR int_max_csr(v_name VARCHAR2,
447 v_interest_date DATE) IS
448 SELECT idv.value
449 FROM OKL_INDEX_VALUES idv,
450 OKL_INDICES idx
451 WHERE idx.ID = idv.IDX_ID
452 AND idx.NAME = v_name
453 AND idv.datetime_valid = (SELECT MAX(idv.datetime_valid)
454 FROM OKL_INDEX_VALUES idv ,
455 OKL_INDICES idx
456 WHERE idx.id = idv.idx_id
457 AND idx.name = v_name
458 AND idv.datetime_valid <= v_interest_date);
459
460 -- This cursor will return the Contract ID from the given contract number
461 CURSOR cont_csr(v_contract_number VARCHAR2) IS
462 SELECT okch.ID
463 FROM OKL_K_HEADERS oklh,
464 OKC_K_HEADERS_B okch
465 WHERE oklh.id = okch.id
466 AND okch.contract_number = v_contract_number
467 AND okch.scs_code = 'LEASE';
468
469 CURSOR time1_csr IS
470 SELECT quantity
471 FROM okc_timeunit_v
472 WHERE uom_code = 'YR'
473 AND tce_code= 'DAY';
474
475 CURSOR num_days_csr(p_date DATE) IS
476 SELECT add_months(trunc(p_date,'year'),12) - trunc(p_date,'year')
477 FROM dual;
478
479 l_name OKL_INDICES.name%TYPE;
480 l_prev_start_date DATE;
481 l_temp_interest NUMBER := 0;
482 l_total_interest NUMBER := 0;
483 l_principal_amount NUMBER := 0;
484 l_interest_rate NUMBER := 0;
485 l_khr_id NUMBER := 0;
486 l_no_of_days_in_year VARCHAR2(450);
487 -- gboomina Bug 4900213 - Added - Start
488 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
489 -- though l_no_of_days_in_month is not used, need to pass this to OKL_PRICING_UTILS_PVT.get_day_convention method.
490 l_no_of_days_in_month VARCHAR2(450);
491 -- gboomina Bug 4900213 - Added - End
492
493 FUNCTION DO_CALC(p_principal_amount IN NUMBER,
494 p_start_date IN DATE,
495 p_end_date IN DATE,
496 p_rate IN NUMBER)
497
498 RETURN NUMBER IS
499 l_no_of_days NUMBER := 0;
500 l_interest NUMBER := 0;
501
502 BEGIN
503
504 l_no_of_days := trunc(p_end_date) - trunc(p_start_date);
505
506 IF (l_no_of_days_in_year = 'ACTUAL') THEN
507 OPEN num_days_csr(p_start_date);
508 FETCH num_days_csr INTO l_no_of_days_in_year;
509 CLOSE num_days_csr;
510 END IF;
511
512 l_interest := (p_principal_amount * p_rate * (l_no_of_days + 1) ) /
513 (to_number(l_no_of_days_in_year) * 100);
514 RETURN (l_interest);
515
516 EXCEPTION
517
518 WHEN OTHERS then RETURN 0;
519
520 END DO_CALC;
521
522 BEGIN
523
524 -- gboomina bug 4900213 - Start
525 -- moved cont_csr here
526 OPEN cont_csr(p_contract_number);
527 FETCH cont_csr INTO l_khr_id;
528 IF (cont_csr%NOTFOUND) THEN
529 -- Abort the process.
530 IF (p_mode = 'ONLINE') THEN
531 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
532 p_msg_name => 'OKL_CNTR_NO_INVALID',
533 p_token1 => 'CONTRACT_NUMBER',
534 p_token1_value => p_contract_number);
535 ELSE
536 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Contract Number ' || p_contract_number ||
537 ' is not Valid');
538
539 END IF;
540
541 CLOSE cont_csr;
542 RAISE OKL_API.G_EXCEPTION_ERROR;
543 END IF;
544 CLOSE cont_csr;
545
546 -- Find out number of days in Year
547 -- Now OKL_PRICING_UTILS_PVT.get_day_convention method is used to get no of days in a year.
548
549 OKL_PRICING_UTILS_PVT.get_day_convention(p_id => l_khr_id,
550 p_source => 'ISG', -- simply passing ISG
551 x_days_in_month => l_no_of_days_in_month,
552 x_days_in_year => l_no_of_days_in_year,
553 x_return_status => l_return_status);
554
555 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
556 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
557 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
558 RAISE OKL_API.G_EXCEPTION_ERROR;
559 END IF;
560
561 IF l_no_of_days_in_year IS NULL THEN
562 IF (p_mode = 'ONLINE') THEN
563 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
564 p_msg_name => 'OKL_DAYS_IN_YEAR_NOT_FOUND');
565 ELSE
566 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error : Could not get Number of Days in Year');
567 END IF;
568 RAISE OKL_API.G_EXCEPTION_ERROR;
569 END IF;
570 -- gboomina bug 4900213 - End
571
572 -- Find out the interest Name.
573 --Bug# 10628703: Added p_chr_id parameter
574 OPEN indx_csr(p_chr_id => l_khr_id);
575 FETCH indx_csr INTO l_name;
576 IF (indx_csr%NOTFOUND) THEN
577 -- Cannot Continue. Must Stop
578 IF (p_mode = 'ONLINE') THEN
579 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
580 p_msg_name => 'OKL_INTR_NAME_NOT_FOUND');
581 ELSE
582 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error : Could not get Interest Rate '
583 || 'Name in the Rules');
584
585 END IF;
586 CLOSE indx_csr;
587 RAISE OKL_API.G_EXCEPTION_ERROR;
588 END IF;
589 CLOSE indx_csr;
590
591 l_prev_start_date := p_start_date;
592
593 FOR change_rec IN change_csr(l_khr_id,
594 p_start_date,
595 p_end_date,
596 l_name)
597 LOOP
598 OPEN int_max_csr(l_name, change_rec.change_date - 1);
599 FETCH int_max_csr INTO l_interest_rate;
600 IF (int_max_csr%NOTFOUND) THEN
601 IF (p_mode = 'ONLINE') THEN
602 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
603 p_msg_name => 'OKL_INTR_RATE_NOT_FOUND',
604 p_token1 => 'INTR_DATE',
605 p_token1_value => to_char(change_rec.change_date - 1,'DD-MM-YY'));
606 ELSE
607 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Interest Rate is not available for ' ||
608 to_char(change_rec.change_date-1,'DD-MON-YY'));
609 END IF;
610
611 CLOSE int_max_csr;
612 RAISE OKL_API.G_EXCEPTION_ERROR;
613
614 END IF;
615
616 CLOSE int_max_csr;
617 OPEN princ_csr(change_rec.change_date - 1, l_khr_id);
618 FETCH princ_csr INTO l_principal_amount;
619 CLOSE princ_csr;
620
621 l_temp_interest := DO_CALC(l_principal_amount
622 ,l_prev_start_date
623 ,(change_rec.change_date - 1)
624 ,l_interest_rate);
625 l_total_interest := nvl(l_total_interest,0) + nvl(l_temp_interest,0);
626 l_prev_start_date := change_rec.change_date;
627
628 END LOOP;
629
630 OPEN int_max_csr(l_name, p_end_date);
631 FETCH int_max_csr INTO l_interest_rate;
632 IF (int_max_csr%NOTFOUND) THEN
633 IF (p_mode = 'ONLINE') THEN
634 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
635 p_msg_name => 'OKL_INTR_RATE_NOT_FOUND',
636 p_token1 => 'INTR_DATE',
637 p_token1_value => to_char(p_end_date,'DD-MM-YY'));
638 ELSE
639 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Interest Rate is not available for ' ||
640 to_char(p_end_date,'DD-MON-YY'));
641 END IF;
642
643 CLOSE int_max_csr;
644 RAISE OKL_API.G_EXCEPTION_ERROR;
645 END IF;
646
647 CLOSE int_max_csr;
648
649 OPEN princ_csr(p_end_date, l_khr_id);
650 FETCH princ_csr INTO l_principal_amount;
651 CLOSE princ_csr;
652 l_temp_interest := DO_CALC(l_principal_amount
653 ,l_prev_start_date
654 ,p_end_date
655 ,l_interest_rate);
656
657 l_total_interest := nvl(l_total_interest,0) + nvl(l_temp_interest,0);
658
659 x_amount := l_total_interest;
660 x_return_status := OKL_API.G_RET_STS_SUCCESS;
661
662 EXCEPTION
663 WHEN OKL_API.G_EXCEPTION_ERROR THEN
664 x_return_status := OKL_API.G_RET_STS_ERROR;
665 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
666 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
667 WHEN OTHERS THEN
668 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
669
670 END CALC_INTEREST;
671
672
673
674 PROCEDURE CALC_INTEREST_PERD(p_errbuf OUT NOCOPY VARCHAR2,
675 p_retcode OUT NOCOPY NUMBER,
676 p_calc_upto IN VARCHAR2)
677 IS
678 l_api_name CONSTANT VARCHAR2(40) := 'CALC_INTEREST_PERD';
679 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
680 l_init_msg_list VARCHAR2(1);
681 l_msg_data VARCHAR2(2000);
682 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
683
684 l_api_version CONSTANT NUMBER := 1.0;
685 p_api_version CONSTANT NUMBER := 1.0;
686 l_row_count NUMBER;
687 l_set_of_books_id NUMBER := 0;
688 l_org_id NUMBER;
689 i NUMBER := 0;
690 l_amount NUMBER := 0;
691 l_msg_count NUMBER;
692 l_source_id NUMBER;
693 l_count NUMBER := 0;
694
695 l_start_date DATE;
696 l_end_date DATE;
697 l_period_end_date DATE;
698 l_last_calc_date DATE;
699 l_int_calc_upto DATE;
700 l_contract_start_date DATE;
701 l_period_name VARCHAR2(10);
702
703 l_tcnv_rec_in OKL_TRX_CONTRACTS_PUB.tcnv_rec_type;
704 l_tclv_tbl_in OKL_TRX_CONTRACTS_PUB.tclv_tbl_type;
705 l_tcnv_rec_out OKL_TRX_CONTRACTS_PUB.tcnv_rec_type;
706 l_tclv_tbl_out OKL_TRX_CONTRACTS_PUB.tclv_tbl_type;
707 l_description OKL_TRX_CONTRACTS.DESCRIPTION%TYPE;
708 l_khrv_tbl_in OKL_CONTRACT_PUB.KHRV_TBL_TYPE;
709 l_khrv_tbl_out OKL_CONTRACT_PUB.KHRV_TBL_TYPE;
710
711
712 l_error_msg_rec OKL_ACCOUNTING_UTIL.Error_message_Type;
713
714
715 -- Cursor for getting all the eligible contracts (which have capitalization flag = 'N') and
716 -- which are in certain status
717 -- Bug 4704664. SGIYER. Adding org striping criteria.
718 CURSOR cont_head_csr IS
719 SELECT okch.contract_number contract_number,
720 oklh.DATE_LAST_INTERIM_INTEREST_CAL last_calc_date,
721 oklh.id khr_id,
722 oklh.pdt_id pdt_id,
723 okch.start_date start_date
724 FROM OKL_K_HEADERS oklh,
725 OKC_K_HEADERS_B okch,
726 OKC_RULES_B rule
727 WHERE okch.sts_code IN ('ENTERED', 'COMPLETE','PASSED','INCOMPLETE','PENDING_APPROVAL',
728 'APPROVED')
729 AND oklh.id = okch.id
730 AND rule.DNZ_CHR_ID = oklh.id
731 AND rule.rule_information_category = 'LACPLN'
732 AND rule.rule_information1 = 'N';
733
734
735
736 -- Cursor for selecting the earliest available funding date if the last calculation
737 -- date field in the contract header is NULL
738
739 -- Changed by Santonyr on 12 Mar 2004
740 -- Changed the invoice_number to vendor_invoice_number in the where condition
741 -- Changed the select statement by Santonyr on 04-Aug-2004 to improve the performance
742 -- sjalasut, modified the below cursor to have included okl_txl_ap_inv_lns_all_b
743 -- so that khr_id now refers from this table and also introduced the new
744 -- okl consolidation table okl_cnsld_ap_invs_all to join the consolidated invoices
745 -- from the transaction lines table.
746 CURSOR fund_csr(v_khr_id NUMBER) IS
747 SELECT MIN(ac.check_date)
748 FROM OKL_TRX_AP_INVOICES_B oklinv,
749 okl_txl_ap_inv_lns_all_b okl_inv_ln,
750 okl_cnsld_ap_invs_all okl_cnsld,
751 AP_INVOICES inv,
752 AP_INVOICE_PAYMENTS pay,
753 AP_CHECKS AC,
754 fnd_application fnd_app
755 WHERE oklinv.id = okl_inv_ln.tap_id
756 AND okl_inv_ln.khr_id = v_khr_id
757 AND okl_inv_ln.cnsld_ap_inv_id = okl_cnsld.cnsld_ap_inv_id
758 AND inv.application_id = fnd_app.application_id
759 AND fnd_app.application_short_name = 'OKL'
760 AND inv.product_table = 'OKL_CNSLD_AP_INVS_ALL'
761 AND okl_cnsld.cnsld_ap_inv_id = to_number(inv.reference_key1)
762 AND oklinv.funding_type_code = 'PREFUNDING'
763 --Bug# 10628703
764 --AND oklinv.vendor_invoice_number = inv.invoice_num -- sjalasut, is this required now?
765 AND oklinv.ipvs_id = inv.vendor_site_id
766 AND inv.invoice_id = pay.invoice_id
767 AND pay.check_id = ac.check_id;
768
769 lv_msg_count NUMBER := 0;
770 lv_msg_text VARCHAR2(2000) := NULL;
771
772
773 BEGIN
774
775 -- kmotepal bug # 4035770 User entered Interest Calculation Upto date will be taken as end date
776 -- and will be used for further calculations
777
778 l_int_calc_upto := FND_DATE.CANONICAL_TO_DATE(p_calc_upto);
779
780 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Given Interest Calculation Upto Date : ' || l_int_calc_upto);
781 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Starting the Interest Calculation Process');
782
783 OKL_ACCOUNTING_UTIL.get_period_info(p_date => l_int_calc_upto,
784 p_period_name => l_period_name,
785 p_start_date => l_start_date,
786 p_end_date => l_period_end_date);
787
788 -- We should not take the end date returned from the above call. Hence we are not using
789 -- the variable l_period_end_date anywhere as Interest Calculation Upto is the correct end date
790
791 IF (l_start_date IS NULL) THEN
792
793 FND_FILE.PUT_LINE(FND_FILE.LOG, 'The Period ' || l_period_name || ' is Invalid');
794 RAISE OKL_API.G_EXCEPTION_ERROR;
795
796 END IF;
797
798 FOR cont_head_rec IN cont_head_csr
799
800 LOOP
801
802 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calculating Interest for Contract Number: ' ||
803 cont_head_rec.contract_number);
804 l_last_calc_date := cont_head_rec.last_calc_date;
805 l_contract_start_date := cont_head_rec.start_date;
806 l_end_date := l_int_calc_upto;
807
808 IF (l_last_calc_date IS NULL) THEN
809 OPEN fund_csr(cont_head_rec.khr_id);
810 FETCH fund_csr INTO l_last_calc_date;
811 CLOSE fund_csr;
812 END IF;
813
814 -- If l_last_calc_date is NULL then it means that no funding is available
815 -- for the contract
816
817 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Last calculation Date was ' || l_last_calc_Date);
818
819 -- If the Interest Calculation Upto is given as more than the contract start date
820 -- then Interest should be calculated one day prior to the start date
821
822 IF l_contract_start_date < l_end_date THEN
823 l_end_date := l_contract_start_date - 1;
824 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Interest will be calculated only upto contract start date for this contract : '||
825 cont_head_rec.contract_number);
826 END IF;
827
828
829 IF (l_last_calc_date IS NOT NULL) AND (l_last_calc_date < l_end_date) THEN
830
831 CALC_INTEREST(p_contract_number => cont_head_rec.contract_number,
832 p_start_date => l_last_calc_date + 1,
833 p_end_date => l_end_date,
834 p_mode => 'BATCH',
835 x_amount => l_amount,
836 x_return_status => l_return_status);
837
838 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total amount calculated was ' || l_amount);
839 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Interest Calculation was made upto ' || l_end_date);
840
841 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
842
843 SAVEPOINT CREATE_TRX_ACCT;
844
845 CREATE_TRX_ACCT(p_khr_id => cont_head_rec.khr_id,
846 p_product_id => cont_head_rec.pdt_id,
847 p_amount => l_amount,
848 p_calc_date => l_end_date,
849 x_source_id => l_source_id,
850 x_return_status => l_return_status);
851
852
853 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
854 l_count := l_count + 1;
855 COMMIT WORK;
856 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Interest Successfully Calculated. ' ||
857 'Total Amount was ' || OKL_ACCOUNTING_UTIL.ROUND_AMOUNT(p_amount => l_amount,
858 p_currency_code => OKL_ACCOUNTING_UTIL.get_func_curr_code));
859 ELSE
860 ROLLBACK TO CREATE_TRX_ACCT;
861 FND_FILE.PUT_LINE(FND_FILE.LOG, 'There was a problem calculating interest for ' ||
862 'Contract Number ' || cont_head_rec.contract_number);
863
864 -- Commented out code to fix bug 3695764
865
866 /*
867 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
868 IF (l_error_msg_rec.COUNT > 0) THEN
869 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
870 LOOP
871 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
872 END LOOP;
873 END IF;
874 */
875
876
877 -- Added by Santonyr on 13-Aug-2004 to fix bug 3695764
878 -- Get the last message from the message stack and display in the log file.
879
880 Fnd_Msg_Pub.get
881 (p_msg_index => Fnd_Msg_Pub.count_msg,
882 p_encoded => Fnd_Api.g_false,
883 p_data => lv_msg_text,
884 p_msg_index_out => lv_msg_count);
885
886 Fnd_File.PUT_LINE(Fnd_File.LOG, lv_msg_text);
887
888 END IF;
889
890 ELSE
891
892 FND_FILE.PUT_LINE(FND_FILE.LOG, 'There was a problem calculating interest for ' ||
893 ' Contract Number ' || cont_head_rec.contract_number);
894 FND_FILE.PUT_LINE(FND_FILE.LOG, 'In the else part ' || l_return_status);
895
896 END IF;
897
898 ELSE
899
900 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Funding not Available');
901
902 END IF;
903
904 END LOOP;
905
906 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
907 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
908 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Interest was successfully calculated for ' ||
909 l_count || ' contracts');
910 FND_FILE.PUT_LINE(FND_FILE.LOG, '***Successful End of Interest Calculation Process***');
911
912 EXCEPTION
913
914 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
915
916 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Interest Calculation Process Aborted');
917 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
918 IF (l_error_msg_rec.COUNT > 0) THEN
919 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
920 LOOP
921 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
922 END LOOP;
923 END IF;
924
925 WHEN OTHERS THEN
926
927 p_errbuf := SQLERRM;
928 p_retcode := 2;
929
930 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Interest Calculation Process Aborted');
931 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
932 IF (l_error_msg_rec.COUNT > 0) THEN
933 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
934 LOOP
935 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
936 END LOOP;
937 END IF;
938
939 END CALC_INTEREST_PERD;
940
941
942
943
944 PROCEDURE CALC_INTEREST_ACTIVATE(p_api_version IN NUMBER,
945 p_init_msg_list IN VARCHAR2,
946 x_return_status OUT NOCOPY VARCHAR2,
947 x_msg_count OUT NOCOPY NUMBER,
948 x_msg_data OUT NOCOPY VARCHAR2,
949 p_contract_number IN VARCHAR2,
950 p_activation_date IN DATE,
951 x_amount OUT NOCOPY NUMBER,
952 x_source_id OUT NOCOPY NUMBER)
953
954 IS
955
956 l_api_name CONSTANT VARCHAR2(40) := 'CALC_INTEREST_ACTIVATE';
957 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
958 l_captl_flag VARCHAR2(1);
959 l_init_msg_list VARCHAR2(1);
960 l_msg_data VARCHAR2(2000);
961
962 l_api_version CONSTANT NUMBER := 1.0;
963 l_khr_id NUMBER;
964 l_amount NUMBER;
965 l_row_count NUMBER;
966 l_msg_count NUMBER;
967 l_product_id NUMBER;
968 l_source_id NUMBER;
969 l_total_oec NUMBER := 0;
970 i NUMBER := 0;
971
972 l_last_calc_date DATE;
973
974 l_description OKL_TRX_CONTRACTS.DESCRIPTION%TYPE;
975 l_khrv_rec_in OKL_CONTRACT_PUB.khrv_rec_type;
976 l_khrv_rec_out OKL_CONTRACT_PUB.khrv_rec_type;
977 l_chrv_rec_in okl_okc_migration_pvt.chrv_rec_type;
978 l_chrv_rec_out okl_okc_migration_pvt.chrv_rec_type;
979
980 l_klev_tbl_in OKL_CONTRACT_PUB.klev_tbl_type;
981 l_klev_tbl_out OKL_CONTRACT_PUB.klev_tbl_type;
982
983 l_clev_tbl_in OKL_OKC_MIGRATION_PVT.clev_tbl_type;
984 l_clev_tbl_out OKL_OKC_MIGRATION_PVT.clev_tbl_type;
985
986 l_tcnv_rec_in OKL_TRX_CONTRACTS_PUB.tcnv_rec_type;
987 l_tclv_tbl_in OKL_TRX_CONTRACTS_PUB.tclv_tbl_type;
988 l_tcnv_rec_out OKL_TRX_CONTRACTS_PUB.tcnv_rec_type;
989 l_tclv_tbl_out OKL_TRX_CONTRACTS_PUB.tclv_tbl_type;
990
991 -- Cursor for getting the last calculation date
992
993 CURSOR cont_head_csr IS
994 SELECT oklh.DATE_LAST_INTERIM_INTEREST_CAL,
995 oklh.PDT_ID,
996 oklh.ID
997 FROM OKL_K_HEADERS oklh,
998 OKC_K_HEADERS_B okch
999 WHERE oklh.id = okch.id
1000 AND okch.contract_number = p_contract_number
1001 AND okch.scs_code = 'LEASE';
1002
1003
1004 -- Cursor for getting the earliest funding date
1005
1006 -- Changed the select statement by Santonyr on 04-Aug-2004 to improve the performance
1007
1008 CURSOR fund_csr(v_khr_id NUMBER) IS
1009 SELECT MIN(ac.check_date)
1010 FROM OKL_TRX_AP_INVOICES_B oklinv,
1011 okl_txl_ap_inv_lns_all_b okl_inv_ln,
1012 okl_cnsld_ap_invs_all okl_cnsld,
1013 AP_INVOICES inv,
1014 AP_INVOICE_PAYMENTS pay,
1015 AP_CHECKS AC,
1016 fnd_application fnd_app
1017 WHERE oklinv.id = okl_inv_ln.tap_id
1018 AND okl_inv_ln.khr_id = v_khr_id
1019 AND okl_inv_ln.cnsld_ap_inv_id = okl_cnsld.cnsld_ap_inv_id
1020 AND inv.application_id = fnd_app.application_id
1021 AND fnd_app.application_short_name = 'OKL'
1022 AND inv.product_table = 'OKL_CNSLD_AP_INVS_ALL'
1023 AND okl_cnsld.cnsld_ap_inv_id = to_number(inv.reference_key1)
1024 AND oklinv.funding_type_code = 'PREFUNDING'
1025 --Bug# 10628703
1026 --AND oklinv.vendor_invoice_number = inv.invoice_num -- sjalasut, is this required now?
1027 AND oklinv.ipvs_id = inv.vendor_site_id
1028 AND inv.invoice_id = pay.invoice_id
1029 AND pay.check_id = ac.check_id;
1030
1031
1032 -- Cursor for getting the capitalization flag information
1033 CURSOR captl_csr(v_khr_id NUMBER) IS
1034 SELECT rule_information1
1035 FROM OKC_RULES_B
1036 WHERE DNZ_CHR_ID = v_khr_id
1037 AND rule_information_category = 'LACPLN';
1038
1039 -- Cursor for getting OEC and then updating the CAPITALIZED_INTEREST field
1040
1041 CURSOR oec_csr(v_contract_number VARCHAR2) IS
1042 SELECT kln.ID ID,
1043 kln.OEC OEC,
1044 --Added by kthiruva on 20-Feb-2006 for Bug 4899328
1045 kln.CAPITAL_AMOUNT CAPITAL_AMOUNT,
1046 kln.CAPITALIZED_INTEREST CAPITALIZED_INTEREST
1047 FROM okl_k_lines kln,
1048 okc_k_lines_b cln,
1049 okc_k_headers_b chr,
1050 okc_line_styles_b cls
1051 WHERE chr.contract_number = v_contract_number AND
1052 chr.scs_code = 'LEASE' AND
1053 chr.ID = cln.chr_id AND
1054 kln.ID = cln.ID AND
1055 cln.lse_id = cls.ID AND
1056 cls.lty_code = 'FREE_FORM1' AND
1057 cln.STS_CODE <> 'ABANDONED';
1058
1059 --Added by kthiruva on 20-Feb-2006
1060 --Bug 4899328 - Start of Changes
1061 l_old_capitalized_interest NUMBER;
1062 --Bug 4899328 - End of Changes
1063
1064 BEGIN
1065
1066 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1067 G_PKG_NAME,
1068 p_init_msg_list,
1069 l_api_version,
1070 p_api_version,
1071 '_PVT',
1072 x_return_status);
1073
1074 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1075 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1076 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1077 RAISE OKL_API.G_EXCEPTION_ERROR;
1078 END IF;
1079
1080
1081 -- First Get the last calculation Date
1082
1083 OPEN cont_head_csr;
1084 FETCH cont_head_csr INTO l_last_calc_date,
1085 l_product_id,
1086 l_khr_id;
1087 IF (cont_head_csr%NOTFOUND) THEN
1088 OKL_API.set_message('OKC', G_INVALID_VALUE, G_COL_NAME_TOKEN,'CONTRACT_NUMBER');
1089 CLOSE cont_head_csr;
1090 RAISE OKL_API.G_EXCEPTION_ERROR;
1091 END IF;
1092
1093 CLOSE cont_head_csr;
1094
1095
1096 -- IF last calculation date is null then it means that interest has never been calculated.
1097 -- So we should take the earliest funding date and start calculating the interest
1098 -- from that date.
1099
1100 IF (l_last_calc_date IS NULL) THEN
1101 OPEN fund_csr(l_khr_id);
1102 FETCH fund_csr INTO l_last_calc_date;
1103 CLOSE fund_csr;
1104 END IF;
1105
1106 -- IF even the funding table does not have any record then it means that interest should be Zero.
1107 -- Call the CALC_INTEREST only if the last calculation date is NOT NULL
1108
1109 IF (l_last_calc_date IS NOT NULL) AND (l_last_calc_date < p_activation_date) THEN
1110
1111 CALC_INTEREST(p_contract_number => p_contract_number,
1112 p_start_date => l_last_calc_date,
1113 p_end_date => p_activation_date - 1,
1114 p_mode => 'ONLINE',
1115 x_amount => l_amount,
1116 x_return_status => l_return_status);
1117
1118 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1119 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1120 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1121 RAISE OKL_API.G_EXCEPTION_ERROR;
1122 END IF;
1123 ELSE
1124 l_amount := 0;
1125
1126 END IF;
1127
1128 -- If capitalization flag is Set for the contract then just return the Amount
1129 -- If capitalization flag is not set then we have to create a transaction record
1130
1131 OPEN captl_csr(l_khr_id);
1132 FETCH captl_csr INTO l_captl_flag;
1133 IF (captl_csr%NOTFOUND) THEN
1134 /*
1135 Changed by HKPATEL for bug 3589126
1136 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1137 p_msg_name => 'OKL_CAPTL_INFO_NOT_FOUND',
1138 p_token1 => 'CONTRACT_NUMBER',
1139 p_token1_value => p_contract_number);
1140 */
1141 l_captl_flag := 'N';
1142 --CLOSE captl_csr;
1143
1144 --RAISE OKL_API.G_EXCEPTION_ERROR;
1145 END IF;
1146
1147 CLOSE captl_csr;
1148
1149 IF (l_captl_flag = 'N') THEN
1150
1151 IF (l_amount > 0) THEN
1152
1153 CREATE_TRX_ACCT(p_khr_id => l_khr_id,
1154 p_product_id => l_product_id,
1155 p_amount => l_amount,
1156 p_calc_date => p_activation_date,
1157 x_source_id => l_source_id,
1158 x_return_status => l_return_status);
1159
1160 END IF;
1161
1162
1163 ELSE
1164
1165 i := 0;
1166
1167 FOR oec_rec IN oec_csr(p_contract_number)
1168 LOOP
1169 i := i + 1;
1170 l_clev_tbl_in(i).ID := oec_rec.ID;
1171 l_klev_tbl_in(i).ID := oec_rec.ID;
1172 l_klev_tbl_in(i).OEC := oec_rec.OEC;
1173 l_total_oec := l_total_oec + NVL(oec_rec.OEC,0);
1174 --Added by kthiruva on 20-Feb-2006
1175 --Bug 4899328 - Start of Changes
1176 l_klev_tbl_in(i).CAPITALIZED_INTEREST := oec_rec.CAPITALIZED_INTEREST;
1177 l_klev_tbl_in(i).CAPITAL_AMOUNT := oec_rec.CAPITAL_AMOUNT;
1178 --Bug 4899328 - End of Changes
1179
1180 END LOOP;
1181
1182 FOR i IN 1..l_klev_tbl_in.COUNT
1183 LOOP
1184 --Modified by kthiruva on 20-Feb-2006
1185 --Bug 4899328 - Start of Changes
1186 l_old_capitalized_interest := nvl(l_klev_tbl_in(i).CAPITALIZED_INTEREST,0);
1187 l_klev_tbl_in(i).CAPITALIZED_INTEREST := (l_klev_tbl_in(i).OEC / l_total_oec) * l_amount;
1188 l_klev_tbl_in(i).CAPITAL_AMOUNT := l_klev_tbl_in(i).CAPITAL_AMOUNT - l_old_capitalized_interest + l_klev_tbl_in(i).CAPITALIZED_INTEREST;
1189 --Bug 4899328 - End of Changes
1190 END LOOP;
1191
1192 OKL_CONTRACT_PUB.update_contract_line
1193 (p_api_version => p_api_version,
1194 p_init_msg_list => p_init_msg_list,
1195 x_return_status => l_return_status,
1196 x_msg_count => x_msg_count,
1197 x_msg_data => x_msg_data,
1198 p_clev_tbl => l_clev_tbl_in,
1199 p_klev_tbl => l_klev_tbl_in,
1200 p_edit_mode => 'N',
1201 x_clev_tbl => l_clev_tbl_out,
1202 x_klev_tbl => l_klev_tbl_out);
1203
1204 END IF;
1205
1206 x_amount := l_amount;
1207 x_source_id := l_source_id;
1208 x_return_status := l_return_status;
1209
1210 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1211
1212 EXCEPTION
1213
1214 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1215 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1216 (
1217 l_api_name,
1218 G_PKG_NAME,
1219 'OKL_API.G_RET_STS_ERROR',
1220 x_msg_count,
1221 x_msg_data,
1222 '_PVT'
1223 );
1224 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1225 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1226 (
1227 l_api_name,
1228 G_PKG_NAME,
1229 'OKL_API.G_RET_STS_UNEXP_ERROR',
1230 x_msg_count,
1231 x_msg_data,
1232 '_PVT'
1233 );
1234 WHEN OTHERS THEN
1235 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1236 (
1237 l_api_name,
1238 G_PKG_NAME,
1239 'OTHERS',
1240 x_msg_count,
1241 x_msg_data,
1242 '_PVT'
1243 );
1244
1245
1246 END CALC_INTEREST_ACTIVATE;
1247
1248
1249
1250 FUNCTION SUBMIT_CALCULATE_INTEREST(p_api_version IN NUMBER,
1251 p_init_msg_list IN VARCHAR2,
1252 x_return_status OUT NOCOPY VARCHAR2,
1253 x_msg_count OUT NOCOPY NUMBER,
1254 x_msg_data OUT NOCOPY VARCHAR2,
1255 p_period_name IN VARCHAR2 )
1256
1257 RETURN NUMBER IS
1258
1259 l_api_version CONSTANT NUMBER := 1.0;
1260 l_api_name CONSTANT VARCHAR2(30) := 'SUBMIT_CALCULATE_INTEREST';
1261 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1262 l_init_msg_list VARCHAR2(20) DEFAULT Okl_Api.G_FALSE;
1263 l_msg_count NUMBER;
1264 l_msg_data VARCHAR2(2000);
1265 x_request_id NUMBER;
1266
1267 BEGIN
1268
1269 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1270
1271 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1272 G_PKG_NAME,
1273 p_init_msg_list,
1274 l_api_version,
1275 p_api_version,
1276 '_PVT',
1277 x_return_status);
1278 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1279 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1280 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1281 RAISE OKL_API.G_EXCEPTION_ERROR;
1282 END IF;
1283
1284
1285 -- check for period name before submitting the request.
1286 IF (p_period_name IS NULL) OR (p_period_name = Okl_Api.G_MISS_CHAR) THEN
1287 OKL_API.set_message('OKC', G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Period Name');
1288 RAISE Okl_Api.G_EXCEPTION_ERROR;
1289 END IF;
1290
1291 -- Submit Concurrent Program Request for interest calculation
1292
1293 x_request_id := FND_REQUEST.SUBMIT_REQUEST
1294 (application => 'OKL',
1295 program => 'OKLINTCALC',
1296 description => 'Interest Calculation',
1297 argument1 => p_period_name);
1298
1299 IF (x_request_id = 0) THEN
1300
1301 OKL_API.set_message(p_app_name => 'OFA',
1302 p_msg_name => 'FA_DEPRN_TAX_ERROR',
1303 p_token1 => 'REQUEST_ID',
1304 p_token1_value => x_request_id);
1305 RAISE OKL_API.G_EXCEPTION_ERROR;
1306
1307 END IF;
1308
1309 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1310
1311 RETURN x_request_id;
1312
1313 EXCEPTION
1314 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1315 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1316 (
1317 l_api_name,
1318 G_PKG_NAME,
1319 'OKL_API.G_RET_STS_ERROR',
1320 x_msg_count,
1321 x_msg_data,
1322 '_PVT'
1323 );
1324 RETURN x_request_id;
1325 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1326 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1327 (
1328 l_api_name,
1329 G_PKG_NAME,
1330 'OKL_API.G_RET_STS_UNEXP_ERROR',
1331 x_msg_count,
1332 x_msg_data,
1333 '_PVT'
1334 );
1335 RETURN x_request_id;
1336 WHEN OTHERS THEN
1337 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1338 (
1339 l_api_name,
1340 G_PKG_NAME,
1341 'OTHERS',
1342 x_msg_count,
1343 x_msg_data,
1344 '_PVT'
1345 );
1346 RETURN x_request_id;
1347
1348 END SUBMIT_CALCULATE_INTEREST;
1349
1350
1351 END OKL_INTEREST_CALC_PVT;