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