[Home] [Help]
PACKAGE BODY: APPS.CE_INTEREST_CALC
Source
1 package body CE_INTEREST_CALC as
2 /* $Header: ceintcab.pls 120.12.12010000.2 2008/08/10 14:27:39 csutaria ship $ */
3
4 l_DEBUG varchar2(1) := NVL(FND_PROFILE.value('CE_DEBUG'), 'N');
5 -- l_DEBUG varchar2(1) := 'Y';
6
7
8 -- cursor not used
9 CURSOR cashpool_accts_cur(p_cashpool_id number ) IS
10 select
11 ACCOUNT_ID
12 from CE_CASHPOOL_SUB_ACCTS SA
13 where
14 SA.TYPE IN ('CONC', 'ACCT', 'NEST')
15 AND SA.CASHPOOL_ID = p_cashpool_id
16 order by ACCOUNT_ID;
17
18 CURSOR calc_detail_cur(p_from_date date,
19 p_to_date date,
20 p_bank_account_id number,
21 p_interest_schedule_id number,
22 p_interest_acct_type varchar2,
23 p_cashpool_id number
24 ) IS
25 select
26 FROM_DATE
27 , TO_DATE
28 , VALUE_DATED_BALANCE
29 , nvl(INTEREST_RATE,0)
30 , INTEREST_CALC_DETAIL_ID
31 from CE_INT_CALC_DETAILS_GT
32 where INTEREST_SCHEDULE_ID = p_interest_schedule_id
33 --and BANK_ACCOUNT_ID = p_bank_account_id
34 and INTEREST_ACCT_TYPE = p_interest_acct_type
35 and CASHPOOL_ID = p_cashpool_id
36 UNION ALL
37 select
38 FROM_DATE
39 , TO_DATE
40 , VALUE_DATED_BALANCE
41 , nvl(INTEREST_RATE,0)
42 , INTEREST_CALC_DETAIL_ID
43 from CE_INT_CALC_DETAILS_GT
44 where INTEREST_SCHEDULE_ID = p_interest_schedule_id
45 and BANK_ACCOUNT_ID = p_bank_account_id
46 and INTEREST_ACCT_TYPE = p_interest_acct_type
47 and CASHPOOL_ID is null
48 order by from_date;
49
50 CURSOR end_date_cur(p_from_date date,
51 p_to_date date,
52 p_bank_account_id number,
53 p_interest_schedule_id number,
54 p_interest_acct_type varchar2,
55 p_cashpool_id number
56 ) IS
57 select from_date
58 , rownum
59 , INTEREST_CALC_DETAIL_ID
60 from CE_INT_CALC_DETAILS_GT
61 where
62 INTEREST_SCHEDULE_ID = p_interest_schedule_id
63 --and BANK_ACCOUNT_ID = p_bank_account_id
64 and INTEREST_ACCT_TYPE = p_interest_acct_type
65 and CASHPOOL_ID = p_cashpool_id
66 UNION ALL
67 select from_date
68 , rownum
69 , INTEREST_CALC_DETAIL_ID
70 from CE_INT_CALC_DETAILS_GT
71 where
72 INTEREST_SCHEDULE_ID = p_interest_schedule_id
73 and BANK_ACCOUNT_ID = p_bank_account_id
74 and INTEREST_ACCT_TYPE = p_interest_acct_type
75 and CASHPOOL_ID is null
76 order by from_date desc
77 ;
78
79 CURSOR xtr_cur(p_from_date date,
80 p_to_date date,
81 p_bank_account_id number,
82 p_interest_schedule_id number,
83 p_interest_acct_type varchar2
84 ) IS
85 select from_date
86 , rownum
87 , INTEREST_CALC_DETAIL_ID
88 from CE_INT_CALC_DETAILS_GT
89 where
90 INTEREST_SCHEDULE_ID = p_interest_schedule_id
91 and BANK_ACCOUNT_ID = p_bank_account_id
92 and INTEREST_ACCT_TYPE = p_interest_acct_type
93 and CASHPOOL_ID is null
94 order by from_date desc
95 ;
96
97 CURSOR balance_info(p_from_date date,
98 p_to_date date,
99 p_bank_account_id number,
100 p_interest_schedule_id number
101 ) IS
102
103 select
104 BAB.BANK_ACCOUNT_ID
105 , max(BAB.BALANCE_DATE) BALANCE_DATE_FROM
106 , BAB.VALUE_DATED_BALANCE
107 , IBR.BALANCE_RANGE_ID
108 , (select distinct ir.interest_rate
109 from CE_INTEREST_RATES IR
110 where ir.balance_range_id = IBR.BALANCE_RANGE_ID
111 and ir.effective_date =
112 (select max(ir2.effective_date)
113 from CE_INTEREST_RATES IR2
114 where ir2.effective_date <= p_from_date --'31-DEC-2003' --max(BAB.BALANCE_DATE)
115 and ir2.balance_range_id = ir.BALANCE_RANGE_ID
116 )
117 ) INTEREST_RATES
118 , 'Y' FIRST_ROW
119 , rownum
120 from
121 CE_BANK_ACCT_BALANCES BAB
122 --, CE_BANK_ACCOUNTS BA
123 , CE_INTEREST_SCHEDULES cIS
124 , CE_INTEREST_BAL_RANGES IBR
125 WHERE
126 cIS.INTEREST_SCHEDULE_ID = IBR.INTEREST_SCHEDULE_ID
127 and cIS.INTEREST_SCHEDULE_ID = p_interest_schedule_id --10002
128 --AND cIS.INTEREST_SCHEDULE_ID = ba.INTEREST_SCHEDULE_ID
129 --and BA.BANK_ACCOUNT_ID = BAB.BANK_ACCOUNT_ID
130 AND BAB.BALANCE_DATE <= p_from_date --'31-DEC-2003'
131 and BAB.VALUE_DATED_BALANCE >= nvl(IBR.FROM_BALANCE_AMOUNT, BAB.VALUE_DATED_BALANCE )
132 and BAB.VALUE_DATED_BALANCE <= nvl(IBR.TO_BALANCE_AMOUNT, BAB.VALUE_DATED_BALANCE )
133 AND BAB.BANK_ACCOUNT_ID = p_bank_account_id --10001
134 group by
135 BAB.BANK_ACCOUNT_ID
136 , BAB.BALANCE_DATE
137 , BAB.VALUE_DATED_BALANCE
138 , IBR.BALANCE_RANGE_ID
139 , rownum
140 having max(BAB.BALANCE_DATE) = (select max(bab2.balance_date)
141 from CE_BANK_ACCT_BALANCES BAB2
142 where BAb2.BANK_ACCOUNT_ID = BAB.BANK_ACCOUNT_ID
143 and BAB2.BALANCE_DATE <= p_from_date) --'01-May-2004')
144 UNION ALL
145 select
146 BAB.BANK_ACCOUNT_ID
147 , BAB.BALANCE_DATE BALANCE_DATE_FROM
148 , BAB.VALUE_DATED_BALANCE
149 , IBR.BALANCE_RANGE_ID
150 , (select distinct ir.interest_rate
151 from CE_INTEREST_RATES IR
152 where ir.balance_range_id = IBR.BALANCE_RANGE_ID
153 and ir.effective_date =
154 (select max(ir2.effective_date)
155 from CE_INTEREST_RATES IR2
156 where ir2.effective_date <= BAB.BALANCE_DATE
157 and ir2.balance_range_id = ir.BALANCE_RANGE_ID
158 )
159 ) INTEREST_RATES
160 , 'N' FIRST_ROW
161 , rownum
162 from
163 CE_BANK_ACCT_BALANCES BAB
164 --, CE_BANK_ACCOUNTS BA
165 , CE_INTEREST_SCHEDULES cIS
166 , CE_INTEREST_BAL_RANGES IBR
167 WHERE
168 cIS.INTEREST_SCHEDULE_ID = IBR.INTEREST_SCHEDULE_ID
169 and cIS.INTEREST_SCHEDULE_ID = p_interest_schedule_id --10002
170 --AND cIS.INTEREST_SCHEDULE_ID = ba.INTEREST_SCHEDULE_ID
171 --and BA.BANK_ACCOUNT_ID = BAB.BANK_ACCOUNT_ID
172 AND BAB.BALANCE_DATE >= p_from_date --'31-DEC-2003'
173 AND BAB.BALANCE_DATE <= p_to_date --'31-JAN-2004'
174 AND BAB.BALANCE_DATE <> p_from_date --'31-DEC-2003'
175 and BAB.VALUE_DATED_BALANCE >= nvl(IBR.FROM_BALANCE_AMOUNT , BAB.VALUE_DATED_BALANCE )
176 and BAB.VALUE_DATED_BALANCE <= nvl(IBR.TO_BALANCE_AMOUNT, BAB.VALUE_DATED_BALANCE )
177 AND BAB.BANK_ACCOUNT_ID = p_bank_account_id --10001
178 order by BALANCE_DATE_FROM
179 ;
180
181
182
183 CURSOR interest_info(p_from_date date,
184 p_to_date date,
185 p_bank_account_id number,
186 p_interest_schedule_id number,
187 p_interest_acct_type varchar2) IS
188 select distinct
189 tmp2.BANK_ACCOUNT_ID
190 , IR.EFFECTIVE_DATE
191 , tmp2.VALUE_DATED_BALANCE
192 /* , (select distinct tmp.VALUE_DATED_BALANCE
193 from CE_INT_CALC_DETAILS_GT tmp
194 where tmp.bank_account_id = bab.bank_account_id
195 and tmp.balance_range_id = ir.balance_range_id
196 and tmp.INTEREST_SCHEDULE_ID = cis.interest_schedule_id
197 and tmp.interest_acct_type = p_interest_acct_type
198 and tmp.from_date =
199 (select max(tmp2.from_date)
200 from CE_INT_CALC_DETAILS_GT tmp2
201 where tmp2.from_date < ir.effective_date -- tmp.from_date
202 and tmp.bank_account_id = tmp2.bank_account_id
203 and tmp2.interest_acct_type = p_interest_acct_type
204 )
205 ) VALUE_DATED_BALANCE */
206 , IR.BALANCE_RANGE_ID
207 , IR.INTEREST_RATE
208 from
209 --CE_BANK_ACCT_BALANCES BAB
210 CE_INT_CALC_DETAILS_GT tmp2 --, CE_BANK_ACCOUNTS BA
211 , CE_INTEREST_SCHEDULES CIS
212 , CE_INTEREST_BAL_RANGES IBR
213 , CE_INTEREST_RATES IR
214 WHERE
215 CIS.INTEREST_SCHEDULE_ID = IBR.INTEREST_SCHEDULE_ID
216 AND IBR.BALANCE_RANGE_ID = IR.BALANCE_RANGE_ID
217 AND IR.EFFECTIVE_DATE >= p_from_date --'31-DEC-2003'
218 AND IR.EFFECTIVE_DATE <= p_to_date -- '31-JAN-2004'
219 and tmp2.VALUE_DATED_BALANCE >= nvl(IBR.FROM_BALANCE_AMOUNT , tmp2.VALUE_DATED_BALANCE ) --y_int_calc_balance1
220 and tmp2.VALUE_DATED_BALANCE <= nvl(IBR.TO_BALANCE_AMOUNT, tmp2.VALUE_DATED_BALANCE ) --y_int_calc_balance1
221 --and BAB.VALUE_DATED_BALANCE >= nvl(IBR.FROM_BALANCE_AMOUNT , BAB.VALUE_DATED_BALANCE ) --y_int_calc_balance1
222 --and BAB.VALUE_DATED_BALANCE <= nvl(IBR.TO_BALANCE_AMOUNT, BAB.VALUE_DATED_BALANCE ) --y_int_calc_balance1
223 --and p_int_calc_balance >= nvl(IBR.FROM_BALANCE_AMOUNT , p_int_calc_balance )
224 --and p_int_calc_balance <= nvl(IBR.TO_BALANCE_AMOUNT,p_int_calc_balance )
225 --AND BA.BANK_ACCOUNT_ID = BAB.BANK_ACCOUNT_ID
226 --AND cIS.INTEREST_SCHEDULE_ID = BA.INTEREST_SCHEDULE_ID
227 AND cIS.INTEREST_SCHEDULE_ID = p_interest_schedule_id --10002
228 --AND BAB.BALANCE_DATE >= p_from_date --'31-DEC-2003'
229 --AND BAB.BALANCE_DATE <= p_to_date --'31-JAN-2004'
230 --AND BAB.BANK_ACCOUNT_ID = p_bank_account_id --10001
231 AND tmp2.from_DATE >= p_from_date --'31-DEC-2003'
232 AND nvl(tmp2.to_DATE, p_to_date) <= p_to_date --'31-JAN-2004'
233 AND tmp2.BANK_ACCOUNT_ID = p_bank_account_id --10001
234 AND IR.BALANCE_RANGE_ID = tmp2.BALANCE_RANGE_ID
235 and tmp2.interest_acct_type = p_interest_acct_type
236 and cIS.INTEREST_SCHEDULE_ID = tmp2.INTEREST_SCHEDULE_ID
237 and IR.EFFECTIVE_DATE > tmp2.from_date
238 and tmp2.from_date = (select max(tmp.from_date)
239 from CE_INT_CALC_DETAILS_GT tmp
240 where tmp.from_date < ir.effective_date
241 and tmp.bank_account_id = tmp2.bank_account_id
242 and tmp.interest_acct_type = p_interest_acct_type
243 )
244 and not exists (select tmp.from_date from CE_INT_CALC_DETAILS_GT tmp
245 where tmp.from_date = IR.EFFECTIVE_DATE
246 and tmp.bank_account_id = tmp2.BANK_ACCOUNT_ID
247 and tmp.from_date>=p_from_date --'31-DEC-2003'
248 and tmp.from_date<=p_to_date --'31-JAN-2004'
249 and tmp.INTEREST_SCHEDULE_ID = p_interest_schedule_id --10002
250 and tmp.interest_acct_type = p_interest_acct_type
251 )
252 and exists (select tmp.balance_range_id from CE_INT_CALC_DETAILS_GT tmp
253 where tmp.balance_range_id = IR.BALANCE_RANGE_ID
254 and tmp.bank_account_id = tmp2.BANK_ACCOUNT_ID
255 and tmp.from_date>=p_from_date --'31-DEC-2003'
256 and tmp.from_date<=p_to_date --'31-JAN-2004'
257 and tmp.INTEREST_SCHEDULE_ID = p_interest_schedule_id --10002
258 and tmp.interest_acct_type = p_interest_acct_type
259 )
260 ;
261
262
263 CURSOR missing_interest_info(p_from_date date, p_to_date date,
264 p_bank_account_id number, p_interest_schedule_id number ) IS
265 select ir.interest_rate,
266 tmp.from_date,
267 tmp.INTEREST_CALC_DETAIL_ID
268 from
269 CE_INTEREST_BAL_RANGES IBR
270 , ce_interest_rates ir
271 , CE_INT_CALC_DETAILS_GT tmp
272 where
273 ibr.balance_range_id = ir.balance_range_id
274 and nvl(ibr.TO_BALANCE_AMOUNT, tmp.VALUE_DATED_BALANCE) <= tmp.VALUE_DATED_BALANCE
275 and tmp.interest_rate is null
276 and tmp.interest_schedule_id = p_interest_schedule_id --10741
277 and tmp.bank_account_id = p_bank_account_id --16000
278 and tmp.interest_schedule_id = ibr.interest_schedule_id
279 and ir.effective_date <= tmp.from_date
280 and ir.effective_date = (select max(ir.effective_date)
281 from
282 CE_INTEREST_BAL_RANGES IBR
283 , ce_interest_rates ir
284 , CE_INT_CALC_DETAILS_GT tmp2
285 where
286 ibr.balance_range_id = ir.balance_range_id
287 and nvl(ibr.TO_BALANCE_AMOUNT, tmp2.VALUE_DATED_BALANCE) <= tmp.VALUE_DATED_BALANCE
288 and tmp2.interest_rate is null
289 and tmp2.interest_schedule_id = 10741
290 and tmp2.bank_account_id = 16000
291 and tmp2.interest_schedule_id = ibr.interest_schedule_id
292 and ir.effective_date <= tmp2.from_date
293 and tmp2.from_date = tmp.from_date
294 )
295 ;
296
297 --CURSOR DR_RANGE(p_from_date date, p_to_date date,
298 CURSOR DR_RANGE(p_balance_date_from date, p_balance_date_to date,
299 p_bank_account_id number, p_interest_schedule_id number,
300 l_amount number ) IS
301 select
302 nvl(FROM_BALANCE_AMOUNT, l_amount),
303 nvl(TO_BALANCE_AMOUNT, 0),
304 ir.interest_rate
305 from ce_interest_bal_ranges ibr
306 , ce_interest_rates ir
307 where ibr.INTEREST_SCHEDULE_ID = p_interest_schedule_id
308 and ibr.TO_BALANCE_AMOUNT >= l_amount
309 and ibr.TO_BALANCE_AMOUNT <= 0
310 and ibr.balance_range_id = ir.balance_range_id
311 and ir.effective_date=
312 (select max(ir2.effective_date)
313 from CE_INTEREST_RATES IR2
314 where ir2.effective_date <= p_balance_date_from --'31-DEC-2003' --max(BAB.BALANCE_DATE)
315 and ir2.balance_range_id = ir.BALANCE_RANGE_ID)
316 ;
317
318 /* select MIN_AMT,MAX_AMT,nvl(INTEREST_RATE,0)
319 from XTR_INTEREST_RATE_RANGES
320 where REF_CODE = l_acct
321 and PARTY_CODE = l_bank_code
322 -- and PARTY_CODE = l_setoff_party
323 and CURRENCY = L_CURRENCY
324 and MAX_AMT >= l_amount
325 and MIN_AMT <0
326 and EFFECTIVE_FROM_DATE =(select max(EFFECTIVE_FROM_DATE)
327 from XTR_INTEREST_RATE_RANGES
328 where REF_CODE = l_acct
329 and PARTY_CODE = l_bank_code
330 --and PARTY_CODE = l_setoff_party
331 and CURRENCY = L_CURRENCY
332 and MAX_AMT >= l_amount
333 and MIN_AMT <0
334 and EFFECTIVE_FROM_DATE<= L_BALANCE_DATE)
335 order by MAX_AMT desc;
336 */
337 --
338
339 --CURSOR CR_RANGE(p_from_date date, p_to_date date,
340 CURSOR CR_RANGE(p_balance_date_from date, p_balance_date_to date,
341 p_bank_account_id number, p_interest_schedule_id number,
342 l_amount number ) IS
343 select
344 nvl(FROM_BALANCE_AMOUNT, -1),
345 nvl(TO_BALANCE_AMOUNT, l_amount),
346 /* (select distinct ir.interest_rate
347 from CE_INTEREST_RATES IR
348 where ir.balance_range_id = IBR.BALANCE_RANGE_ID
349 and ir.effective_date =
350 (select max(ir2.effective_date)
351 from CE_INTEREST_RATES IR2
352 where ir2.effective_date <= p_balance_date_from --'31-DEC-2003' --max(BAB.BALANCE_DATE)
353 and ir2.balance_range_id = ir.BALANCE_RANGE_ID
354 )
355 ) INTEREST_RATES*/
356 ir.interest_rate
357 from ce_interest_bal_ranges ibr
358 , ce_interest_rates ir
359 where ibr.INTEREST_SCHEDULE_ID = p_interest_schedule_id
360 and ibr.FROM_BALANCE_AMOUNT <= l_amount
361 and ibr.FROM_BALANCE_AMOUNT >= 0
362 and ibr.balance_range_id = ir.balance_range_id
363 and ir.effective_date=
364 (select max(ir2.effective_date)
365 from CE_INTEREST_RATES IR2
366 where ir2.effective_date <= p_balance_date_from --'31-DEC-2003' --max(BAB.BALANCE_DATE)
367 and ir2.balance_range_id = ir.BALANCE_RANGE_ID)
368 ;
369
370 /* select MIN_AMT,MAX_AMT,nvl(INTEREST_RATE,0)
371 from XTR_INTEREST_RATE_RANGES
372 where REF_CODE = l_acct
373 and PARTY_CODE = l_bank_code
374 -- and PARTY_CODE = l_setoff_party
375 and CURRENCY = L_CURRENCY
376 and MIN_AMT <= l_amount
377 and MAX_AMT >= 0
378 and EFFECTIVE_FROM_DATE =(select max(EFFECTIVE_FROM_DATE)
379 from XTR_INTEREST_RATE_RANGES
380 where REF_CODE = l_acct
381 and PARTY_CODE = l_bank_code
382 -- and PARTY_CODE = l_setoff_party
383 and CURRENCY = L_CURRENCY
384 and MIN_AMT <= l_amount
385 and MAX_AMT >= 0
386 and EFFECTIVE_FROM_DATE<= L_BALANCE_DATE)
387 order by MIN_AMT desc;
388 */
389
390 /* =====================================================================
391 | Pool cursor |
392 |
393 --------------------------------------------------------------------- */
394 CURSOR range_and_rate_cur(p_from_date date,
395 p_to_date date,
396 p_bank_account_id number,
397 p_interest_schedule_id number,
398 p_interest_acct_type varchar2,
399 p_cashpool_id number
400 ) IS
401 select
402 tmp.INTEREST_CALC_DETAIL_ID
403 --, tmp.VALUE_DATED_BALANCE
404 , IBR.BALANCE_RANGE_ID
405 --, IR.INTEREST_RATE
406 , (select distinct ir.interest_rate
407 from CE_INTEREST_RATES IR
408 where ir.balance_range_id = IBR.BALANCE_RANGE_ID
409 and ir.effective_date =
410 (select max(ir2.effective_date)
411 from CE_INTEREST_RATES IR2
412 where ir2.effective_date <= tmp.from_date --p_from_date --max(BAB.BALANCE_DATE)
413 and ir2.balance_range_id = ir.BALANCE_RANGE_ID
414 )
415 ) INTEREST_RATES
416 from CE_INT_CALC_DETAILS_GT tmp
417 , CE_INTEREST_BAL_RANGES IBR
418 -- , CE_INTEREST_RATES IR
419 where tmp.INTEREST_SCHEDULE_ID = p_interest_schedule_id
420 and tmp.INTEREST_SCHEDULE_ID = IBR.INTEREST_SCHEDULE_ID
421 -- AND IBR.BALANCE_RANGE_ID = IR.BALANCE_RANGE_ID
422 and tmp.VALUE_DATED_BALANCE >= nvl(IBR.FROM_BALANCE_AMOUNT , tmp.VALUE_DATED_BALANCE )
423 and tmp.VALUE_DATED_BALANCE <= nvl(IBR.TO_BALANCE_AMOUNT, tmp.VALUE_DATED_BALANCE )
424 -- and IR.EFFECTIVE_DATE >= p_from_date --'31-DEC-2003'
425 -- AND IR.EFFECTIVE_DATE <= p_to_date -- '31-JAN-2004'
426 and tmp.BANK_ACCOUNT_ID = p_bank_account_id
427 and tmp.INTEREST_ACCT_TYPE = p_interest_acct_type
428 and tmp.cashpool_id = p_cashpool_id
429 order by tmp.from_date;
430
431 CURSOR balance_pool_info(p_from_date date,
432 p_to_date date,
433 p_bank_account_id number,
434 p_interest_schedule_id number,
435 p_interest_acct_type varchar2,
436 p_cashpool_id number
437 ) IS
438 select
439 BAB.BANK_ACCOUNT_ID
440 , max(BAB.BALANCE_DATE) BALANCE_DATE_FROM
441 , ce_bal_util.get_pool_balance(p_cashpool_id, p_from_date) VALUE_DATED_BALANCE --BAB.VALUE_DATED_BALANCE
442 , NULL BALANCE_RANGE_ID --IBR.BALANCE_RANGE_ID
443 , NULL INTEREST_RATES
444 /*, (select distinct ir.interest_rate
445 from CE_INTEREST_RATES IR
446 where ir.balance_range_id = IBR.BALANCE_RANGE_ID
447 and ir.effective_date =
448 (select max(ir2.effective_date)
449 from CE_INTEREST_RATES IR2
450 where ir2.effective_date <= p_from_date --'31-DEC-2003' --max(BAB.BALANCE_DATE)
451 and ir2.balance_range_id = ir.BALANCE_RANGE_ID
452 )
453 ) INTEREST_RATES*/
454 , 'Y' FIRST_ROW
455 , rownum
456 from
457 CE_BANK_ACCT_BALANCES BAB
458 --, CE_BANK_ACCOUNTS BA
459 , CE_INTEREST_SCHEDULES cIS
460 --, CE_INTEREST_BAL_RANGES IBR
461 WHERE
462 --cIS.INTEREST_SCHEDULE_ID = IBR.INTEREST_SCHEDULE_ID
463 cIS.INTEREST_SCHEDULE_ID = p_interest_schedule_id --10002
464 --AND cIS.INTEREST_SCHEDULE_ID = ba.INTEREST_SCHEDULE_ID
465 --and BA.BANK_ACCOUNT_ID = BAB.BANK_ACCOUNT_ID
466 AND BAB.BALANCE_DATE <= p_from_date --'31-DEC-2003'
467 --and BAB.VALUE_DATED_BALANCE >= nvl(IBR.FROM_BALANCE_AMOUNT, BAB.VALUE_DATED_BALANCE )
468 --and BAB.VALUE_DATED_BALANCE <= nvl(IBR.TO_BALANCE_AMOUNT, BAB.VALUE_DATED_BALANCE )
469 AND BAB.BANK_ACCOUNT_ID = p_bank_account_id --10001
470 and not exists (select tmp.from_date from CE_INT_CALC_DETAILS_GT tmp
471 where tmp.from_date = p_from_date --max(BAB.BALANCE_DATE)
472 --and tmp.bank_account_id = BAB.BANK_ACCOUNT_ID
473 and tmp.from_date>=p_from_date --'31-DEC-2003'
474 and tmp.from_date<=p_to_date --'31-JAN-2004'
475 and tmp.INTEREST_SCHEDULE_ID = p_interest_schedule_id --10002
476 and tmp.interest_acct_type = p_interest_acct_type
477 and tmp.cashpool_id = p_cashpool_id
478 )
479 group by
480 BAB.BANK_ACCOUNT_ID
481 , BAB.BALANCE_DATE
482 , BAB.VALUE_DATED_BALANCE
483 --, IBR.BALANCE_RANGE_ID
484 , rownum
485 having max(BAB.BALANCE_DATE) = (select max(bab2.balance_date)
486 from CE_BANK_ACCT_BALANCES BAB2
487 where BAb2.BANK_ACCOUNT_ID = BAB.BANK_ACCOUNT_ID
488 and BAB2.BALANCE_DATE <= p_from_date) --'01-May-2004')
489 UNION ALL
490 select
491 BAB.BANK_ACCOUNT_ID
492 , BAB.BALANCE_DATE --BALANCE_DATE_FROM
493 , ce_bal_util.get_pool_balance(p_cashpool_id, BAB.BALANCE_DATE) --VALUE_DATED_BALANCE --BAB.VALUE_DATED_BALANCE
494 , NULL BALANCE_RANGE_ID --IBR.BALANCE_RANGE_ID
495 , NULL INTEREST_RATES
496 /* , (select distinct ir.interest_rate
497 from CE_INTEREST_RATES IR
498 where ir.balance_range_id = IBR.BALANCE_RANGE_ID
499 and ir.effective_date =
500 (select max(ir2.effective_date)
501 from CE_INTEREST_RATES IR2
502 where ir2.effective_date <= BAB.BALANCE_DATE
503 and ir2.balance_range_id = ir.BALANCE_RANGE_ID
504 )
505 ) INTEREST_RATES*/
506 , 'N' FIRST_ROW
507 , rownum
508 from
509 CE_BANK_ACCT_BALANCES BAB
510 --, CE_BANK_ACCOUNTS BA
511 , CE_INTEREST_SCHEDULES cIS
512 --, CE_INTEREST_BAL_RANGES IBR
513 WHERE
514 --cIS.INTEREST_SCHEDULE_ID = IBR.INTEREST_SCHEDULE_ID
515 cIS.INTEREST_SCHEDULE_ID = p_interest_schedule_id --10002
516 --AND cIS.INTEREST_SCHEDULE_ID = ba.INTEREST_SCHEDULE_ID
517 --and BA.BANK_ACCOUNT_ID = BAB.BANK_ACCOUNT_ID
518 AND BAB.BALANCE_DATE >= p_from_date --'31-DEC-2003'
519 AND BAB.BALANCE_DATE <= p_to_date --'31-JAN-2004'
520 AND BAB.BALANCE_DATE <> p_from_date --'31-DEC-2003'
521 --and BAB.VALUE_DATED_BALANCE >= nvl(IBR.FROM_BALANCE_AMOUNT , BAB.VALUE_DATED_BALANCE )
522 --and BAB.VALUE_DATED_BALANCE <= nvl(IBR.TO_BALANCE_AMOUNT, BAB.VALUE_DATED_BALANCE )
523 AND BAB.BANK_ACCOUNT_ID = p_bank_account_id --10001
524 and not exists (select tmp.from_date from CE_INT_CALC_DETAILS_GT tmp
525 where tmp.from_date = BAB.BALANCE_DATE
526 --and tmp.bank_account_id = BAB.BANK_ACCOUNT_ID
527 and tmp.from_date>=p_from_date --'31-DEC-2003'
528 and tmp.from_date<=p_to_date --'31-JAN-2004'
529 and tmp.INTEREST_SCHEDULE_ID = p_interest_schedule_id --10002
530 and tmp.interest_acct_type = p_interest_acct_type
531 and tmp.cashpool_id = p_cashpool_id
532 )
533 order by BALANCE_DATE_FROM
534 ;
535
536
537 CURSOR interest_pool_info(p_from_date date,
538 p_to_date date,
539 p_bank_account_id number,
540 p_interest_schedule_id number,
541 p_interest_acct_type varchar2,
542 p_cashpool_id number) IS
543 select distinct
544 tmpx.BANK_ACCOUNT_ID
545 , IR.EFFECTIVE_DATE
546 , tmpx.VALUE_DATED_BALANCE
547 /* , (select distinct tmp.VALUE_DATED_BALANCE
548 from CE_INT_CALC_DETAILS_GT tmp
549 where tmp.bank_account_id = tmpx.bank_account_id
550 and tmp.balance_range_id = ir.balance_range_id
551 and tmp.INTEREST_SCHEDULE_ID = cis.interest_schedule_id
552 and tmp.interest_acct_type = p_interest_acct_type
553 and tmp.cashpool_id = p_cashpool_id
554 and tmp.from_date =
555 (select max(tmp2.from_date)
556 from CE_INT_CALC_DETAILS_GT tmp2
557 where tmp2.from_date < ir.effective_date -- tmp.from_date
558 and tmp.bank_account_id = tmp2.bank_account_id
559 and tmp2.interest_acct_type = p_interest_acct_type
560 and tmp2.cashpool_id = p_cashpool_id
561 )
562 ) VALUE_DATED_BALANCE*/
563 , IR.BALANCE_RANGE_ID
564 , IR.INTEREST_RATE
565 from
566 -- CE_BANK_ACCT_BALANCES BAB
567 --, CE_BANK_ACCOUNTS BA
568 CE_INTEREST_SCHEDULES cIS
569 , CE_INTEREST_BAL_RANGES IBR
570 , CE_INTEREST_RATES IR
571 , CE_INT_CALC_DETAILS_GT tmpx
572 WHERE
573 cIS.INTEREST_SCHEDULE_ID = IBR.INTEREST_SCHEDULE_ID
574 AND IBR.BALANCE_RANGE_ID = IR.BALANCE_RANGE_ID
575 and IR.EFFECTIVE_DATE >= p_from_date --'31-DEC-2003'
576 AND IR.EFFECTIVE_DATE <= p_to_date -- '31-JAN-2004'
577 and tmpx.VALUE_DATED_BALANCE >= nvl(IBR.FROM_BALANCE_AMOUNT , tmpx.VALUE_DATED_BALANCE ) --y_int_calc_balance1
578 and tmpx.VALUE_DATED_BALANCE <= nvl(IBR.TO_BALANCE_AMOUNT, tmpx.VALUE_DATED_BALANCE ) --y_int_calc_balance1
579 --and BAB.VALUE_DATED_BALANCE >= nvl(IBR.FROM_BALANCE_AMOUNT , BAB.VALUE_DATED_BALANCE ) --y_int_calc_balance1
580 --and BAB.VALUE_DATED_BALANCE <= nvl(IBR.TO_BALANCE_AMOUNT, BAB.VALUE_DATED_BALANCE ) --y_int_calc_balance1
581 --and p_int_calc_balance >= nvl(IBR.FROM_BALANCE_AMOUNT , p_int_calc_balance )
582 --and p_int_calc_balance <= nvl(IBR.TO_BALANCE_AMOUNT,p_int_calc_balance )
583 --AND BA.BANK_ACCOUNT_ID = BAB.BANK_ACCOUNT_ID
584 --AND cIS.INTEREST_SCHEDULE_ID = BA.INTEREST_SCHEDULE_ID
585 AND cIS.INTEREST_SCHEDULE_ID = p_interest_schedule_id --10002
586 --AND BAB.BALANCE_DATE >= p_from_date --'31-DEC-2003'
587 --AND BAB.BALANCE_DATE <= p_to_date --'31-JAN-2004'
588 --AND BAB.BANK_ACCOUNT_ID = p_bank_account_id --10001
589 AND tmpx.BANK_ACCOUNT_ID = p_bank_account_id --10001
590 AND tmpx.from_DATE >= p_from_date --'31-DEC-2003'
591 AND nvl(tmpx.to_DATE, p_to_date) <= p_to_date --'31-JAN-2004'
592 AND IR.BALANCE_RANGE_ID = tmpx.BALANCE_RANGE_ID
593 and tmpx.interest_acct_type = p_interest_acct_type
594 and CIS.INTEREST_SCHEDULE_ID = tmpx.INTEREST_SCHEDULE_ID
595 and IR.EFFECTIVE_DATE > tmpx.from_date
596 and tmpx.from_date = (select max(tmp.from_date)
597 from CE_INT_CALC_DETAILS_GT tmp
598 where tmp.from_date < ir.effective_date
599 and tmp.bank_account_id = tmpx.bank_account_id
600 and tmp.interest_acct_type = p_interest_acct_type
601 )
602 and not exists (select tmp.from_date from CE_INT_CALC_DETAILS_GT tmp
603 where tmp.from_date = IR.EFFECTIVE_DATE
604 and tmp.bank_account_id = tmpx.BANK_ACCOUNT_ID
605 --and tmp.bank_account_id = BAB.BANK_ACCOUNT_ID
606 and tmp.from_date>=p_from_date --'31-DEC-2003'
607 and tmp.from_date<=p_to_date --'31-JAN-2004'
608 and tmp.INTEREST_SCHEDULE_ID = p_interest_schedule_id --10002
609 and tmp.interest_acct_type = p_interest_acct_type
610 )
611 and exists (select tmp.balance_range_id from CE_INT_CALC_DETAILS_GT tmp
612 where tmp.balance_range_id = IR.BALANCE_RANGE_ID
613 and tmp.bank_account_id = tmpx.BANK_ACCOUNT_ID
614 --and tmp.bank_account_id = BAB.BANK_ACCOUNT_ID
615 and tmp.from_date>=p_from_date --'31-DEC-2003'
616 and tmp.from_date<=p_to_date --'31-JAN-2004'
617 and tmp.INTEREST_SCHEDULE_ID = p_interest_schedule_id --10002
618 and tmp.interest_acct_type = p_interest_acct_type
619 )
620 ;
621
622
623 /* --------------------------------------------------------------------
624 | PRIVATE Function |
625 | ROUNDUP |
626 | |
627 | CALLED BY |
628 | calculate_interest |
629 | |
630 | DESCRIPTION |
631 | Function uses for rounding up an amount |
632 | |
633 | RETURN |
634 | NUMBER |
635 --------------------------------------------------------------------- */
636 FUNCTION ROUNDUP(p_amount NUMBER,
637 p_round_factor NUMBER) RETURN NUMBER IS
638
639 l_amount number;
640 l_rounded_amount number;
641
642 BEGIN
643
644 l_amount := abs(p_amount);
645
646 l_rounded_amount := Ceil(l_amount*Power(10,p_round_factor))/Power(10,p_round_factor);
647
648 if p_amount < 0 then
649 l_rounded_amount := (-1)*l_rounded_amount;
650 end if;
651
652 return(l_rounded_amount);
653
654 END ROUNDUP;
655
656 /* --------------------------------------------------------------------
657 | PRIVATE Function |
658 | IsLeapYear |
659 | |
660 | CALLED BY |
661 | calculate_interest |
662 | |
663 | DESCRIPTION |
664 | Function uses to check if the year is in a leap year |
665 | - use to determine how many days are in that year |
666 | RETURN |
667 | Boolean |
668 --------------------------------------------------------------------- */
669 Function IsLeapYear(dDate number) RETURN Boolean IS
670 IsLeapYear varchar2(10);
671
672 BEGIN
673
674 select decode( mod(dDate, 4), 0,
675 decode( mod(dDate, 400), 0, 'TRUE',
676 decode( mod(dDate, 100), 0, 'FALSE', 'TRUE')
677 ), 'FALSE'
678 )
679 into IsLeapYear
680 from dual;
681
682 IF (IsLeapYear = 'FALSE') THEN
683 return(FALSE);
684 ELSE
685 return(TRUE);
686
687 END IF;
688
689 End IsLeapYear;
690
691 /*=======================================================================+
692 | PUBLIC FUNCTION get_interest_rate |
693 | |
694 | DESCRIPTION |
695 | Get interest rate |
696 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
697 | |
698 | ARGUMENTS |
699 | IN: |
700 | p_bank_account_id, p_balance_date, P_balance_amount |
701 | OUT: |
702 | P_INTEREST_RATE |
703 +=======================================================================*/
704 FUNCTION get_interest_rate( p_bank_account_id IN NUMBER,
705 p_balance_date IN DATE,
706 p_balance_amount IN NUMBER,
707 p_interest_rate IN OUT NOCOPY NUMBER ) RETURN NUMBER IS
708
709 BEGIN
710 IF l_DEBUG in ('Y', 'C') THEN
711 cep_standard.debug('>> CE_INTEREST_CALC.get_interest_rate');
712 END IF;
713
714 BEGIN
715 IF (p_balance_amount > 0) THEN
716 select ir.interest_rate
717 into p_interest_rate
718 from
719 CE_BANK_ACCOUNTS BA
720 , CE_INTEREST_SCHEDULES cIS
721 , CE_INTEREST_BAL_RANGES IBR
722 , CE_INTEREST_RATES IR
723 WHERE
724 BA.BANK_ACCOUNT_ID = p_bank_account_id --10001
725 AND cIS.INTEREST_SCHEDULE_ID = BA.INTEREST_SCHEDULE_ID
726 AND cIS.INTEREST_SCHEDULE_ID = IBR.INTEREST_SCHEDULE_ID
727 and IBR.FROM_BALANCE_AMOUNT <= p_balance_amount
728 and IBR.FROM_BALANCE_AMOUNT > 0
729 and nvl(IBR.TO_BALANCE_AMOUNT, p_balance_amount ) >= p_balance_amount
730 and ir.balance_range_id = IBR.BALANCE_RANGE_ID
731 and ir.effective_date =
732 (select max(ir2.effective_date)
733 from CE_INTEREST_RATES IR2
734 where ir2.effective_date <= p_balance_date --'31-DEC-2003'
735 and ir2.balance_range_id = ir.BALANCE_RANGE_ID
736 ) ;
737 ELSE
738 select ir.interest_rate
739 into p_interest_rate
740 from
741 CE_BANK_ACCOUNTS BA
742 , CE_INTEREST_SCHEDULES cIS
743 , CE_INTEREST_BAL_RANGES IBR
744 , CE_INTEREST_RATES IR
745 WHERE
746 BA.BANK_ACCOUNT_ID = p_bank_account_id --10001
747 AND cIS.INTEREST_SCHEDULE_ID = BA.INTEREST_SCHEDULE_ID
748 AND cIS.INTEREST_SCHEDULE_ID = IBR.INTEREST_SCHEDULE_ID
749 and nvl(IBR.FROM_BALANCE_AMOUNT, p_balance_amount ) <= p_balance_amount
750 and IBR.TO_BALANCE_AMOUNT >= p_balance_amount
751 and IBR.TO_BALANCE_AMOUNT <= 0
752 and ir.balance_range_id = IBR.BALANCE_RANGE_ID
753 and ir.effective_date =
754 (select max(ir2.effective_date)
755 from CE_INTEREST_RATES IR2
756 where ir2.effective_date <= p_balance_date --'31-DEC-2003'
757 and ir2.balance_range_id = ir.BALANCE_RANGE_ID
758 ) ;
759 END IF;
760 EXCEPTION
761 WHEN no_data_found THEN
762 p_interest_rate := NULL;
763 WHEN TOO_MANY_ROWS THEN
764 p_interest_rate := NULL;
765
766 END;
767 IF l_DEBUG in ('Y', 'C') THEN
768 cep_standard.debug('CE_INTEREST_CALC.get_interest_rate p_interest_rate '||p_interest_rate);
769 END IF;
770
771 RETURN p_interest_rate;
772
773 IF l_DEBUG in ('Y', 'C') THEN
774 cep_standard.debug('<< CE_INTEREST_CALC.get_interest_rate ');
775 END IF;
776
777 EXCEPTION
778 WHEN OTHERS THEN
779 cep_standard.debug('EXCEPTION: get_interest_rate');
780 RAISE;
781 END get_interest_rate;
782
783 /* --------------------------------------------------------------------
784 | PRIVATE PROCEDURE |
785 | delete_schedule_account |
786 | |
787 | CALLED BY |
788 | int_cal_detail_main |
789 | |
790 | DESCRIPTION |
791 | Delete accounts from |
792 | CE_INT_CALC_DETAILS_GT |
793 --------------------------------------------------------------------- */
794 PROCEDURE delete_schedule_account( p_interest_schedule_id number,
795 p_bank_account_id number,
796 p_interest_acct_type varchar2,
797 p_cashpool_id number
798 ) IS
799 x_balance_date_from DATE;
800 x_balance_date_to DATE;
801 x_bank_account_id NUMBER;
802 x_int_calc_balance NUMBER;
803 x_balance_range_id NUMBER;
804 x_days NUMBER;
805 x_first_row varchar2(1);
806 x_rownum NUMBER;
807 x_interest_rate NUMBER;
808
809 y_balance_date_from DATE;
810 y_balance_date_to DATE;
811 y_bank_account_id NUMBER;
812 y_int_calc_balance NUMBER;
813 y_balance_range_id NUMBER;
814 y_days NUMBER;
815 y_rownum NUMBER;
816 BEGIN
817 IF l_DEBUG in ('Y', 'C') THEN
818 cep_standard.debug('>> CE_INTEREST_CALC.delete_schedule_account');
819 END IF;
820
821 -- bug 5493399
822 --IF (p_interest_acct_type = 'BANK_ACCOUNT') THEN
823 IF (p_interest_acct_type in ('BANK_ACCOUNT', 'TREASURY')) THEN
824 IF l_DEBUG in ('Y', 'C') THEN
825 cep_standard.debug('delete p_interest_acct_type BANK_ACCOUNT');
826 END IF;
827 DELETE CE_INT_CALC_DETAILS_GT
828 WHERE INTEREST_SCHEDULE_ID = p_interest_schedule_id
829 and BANK_ACCOUNT_ID = p_bank_account_id
830 AND INTEREST_ACCT_TYPE = p_interest_acct_type;
831 ELSIF (p_interest_acct_type = 'NOTIONAL') THEN
832 IF l_DEBUG in ('Y', 'C') THEN
833 cep_standard.debug('delete p_interest_acct_type NOTIONAL');
834 END IF;
835 DELETE CE_INT_CALC_DETAILS_GT
836 WHERE INTEREST_SCHEDULE_ID = p_interest_schedule_id
837 --and BANK_ACCOUNT_ID = p_bank_account_id
838 AND CASHPOOL_ID = p_cashpool_id
839 AND INTEREST_ACCT_TYPE = p_interest_acct_type;
840 END IF;
841
842
843 IF l_DEBUG in ('Y', 'C') THEN
844 cep_standard.debug('<< CE_INTEREST_CALC.delete_schedule_account');
845 END IF;
846
847 EXCEPTION
848 when others then
849 IF l_DEBUG in ('Y', 'C') THEN
850 cep_standard.debug('EXCEPTION: CE_INTEREST_CALC.delete_schedule_account');
851 END IF;
852 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
853 FND_MESSAGE.Set_Token('PROCEDURE', 'CE_INTEREST_CALC.delete_schedule_account');
854 fnd_msg_pub.add;
855 END delete_schedule_account;
856
857 /* --------------------------------------------------------------------
858 | PRIVATE PROCEDURE |
859 | get_balance_info |
860 | |
861 | CALLED BY |
862 | int_cal_detail_main |
863 | |
864 | DESCRIPTION |
865 | Get Interest Calculated balances information and insert in |
866 | CE_INT_CALC_DETAILS_GT |
867 --------------------------------------------------------------------- */
868 PROCEDURE get_balance_info( p_from_date date,
869 p_to_date date,
870 p_interest_schedule_id number,
871 p_bank_account_id number,
872 p_interest_acct_type varchar2,
873 p_cashpool_id number,
874 p_row_count OUT NOCOPY NUMBER) IS
875 x_balance_date_from DATE;
876 x_balance_date_to DATE;
877 x_bank_account_id NUMBER;
878 x_int_calc_balance NUMBER;
879 x_balance_range_id NUMBER;
880 x_days NUMBER;
881 x_first_row varchar2(1);
882 x_rownum NUMBER;
883 x_interest_rate NUMBER;
884
885 y_balance_date_from DATE;
886 y_balance_date_to DATE;
887 y_bank_account_id NUMBER;
888 y_int_calc_balance NUMBER;
889 y_balance_range_id NUMBER;
890 y_days NUMBER;
891 y_rownum NUMBER;
892 y_first_row varchar2(1);
893 y_record_from varchar2(15);
894 y_row_count NUMBER;
895 y_interest_rate NUMBER;
896
897
898 BEGIN
899 IF l_DEBUG in ('Y', 'C') THEN
900 cep_standard.debug('>> CE_INTEREST_CALC.get_balance_info');
901 END IF;
902
903 OPEN balance_info (p_from_date, p_to_date,p_bank_account_id, p_interest_schedule_id );
904 LOOP
905 FETCH balance_info INTO x_bank_account_id ,
906 x_balance_date_from,
907 x_int_calc_balance ,
908 x_balance_range_id,
909 x_interest_rate,
910 x_first_row,
911 x_rownum;
912
913
914 EXIT WHEN balance_info%NOTFOUND OR balance_info%NOTFOUND IS NULL;
915
916 y_bank_account_id := x_bank_account_id;
917 y_balance_date_from := x_balance_date_from;
918 y_balance_range_id := x_balance_range_id;
919 y_int_calc_balance := x_int_calc_balance;
920 y_interest_rate := x_interest_rate;
921 y_rownum := x_rownum;
922 y_first_row := x_first_row;
923 y_record_from := 'BALANCE';
924
925
926 IF (x_first_row = 'Y') THEN
927 y_balance_date_from := p_from_date;
928 END IF;
929
930
931 IF l_DEBUG in ('Y', 'C') THEN
932 cep_standard.debug('y_bank_account_id = '|| y_bank_account_id);
933 cep_standard.debug('y_balance_date_from = '|| y_balance_date_from);
934 cep_standard.debug('y_int_calc_balance = '|| y_int_calc_balance);
935 cep_standard.debug('y_interest_rate = '|| y_interest_rate);
936 cep_standard.debug('y_first_row = '|| y_first_row);
937 cep_standard.debug('y_record_from = '|| y_record_from);
938 END IF;
939 insert into CE_INT_CALC_DETAILS_GT
940 (INTEREST_CALC_DETAIL_ID,
941 INTEREST_SCHEDULE_ID, BANK_ACCOUNT_ID, FROM_DATE, TO_DATE,
942 VALUE_DATED_BALANCE, NUMBER_OF_DAYS , INTEREST_AMOUNT,INTEREST_RATE,
943 BALANCE_RANGE_ID, RECORD_FROM,
944 INTEREST_ACCT_TYPE, CASHPOOL_ID,
945 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
946 LAST_UPDATE_LOGIN)
947 values
948 ( CE_INT_CALC_DETAILS_GT_S.nextval,
949 p_interest_schedule_id, p_bank_account_id, y_balance_date_from, null,
950 y_int_calc_balance, null, null, y_interest_rate,
951 y_balance_range_id, y_record_from,
952 p_interest_acct_type, p_cashpool_id,
953 sysdate, -1, sysdate, -1, null);
954
955 IF l_DEBUG in ('Y', 'C') THEN
956 cep_standard.debug('insert into CE_INT_CALC_DETAILS_GT completed ');
957 END IF;
958 END LOOP; --balance_info
959 p_row_count := balance_info%ROWCOUNT;
960 IF l_DEBUG in ('Y', 'C') THEN
961 cep_standard.debug('p_row_count = '|| p_row_count );
962 END IF;
963
964 CLOSE balance_info;
965
966
967 IF l_DEBUG in ('Y', 'C') THEN
968 cep_standard.debug('<< CE_INTEREST_CALC.get_balance_info');
969 END IF;
970
971 EXCEPTION
972 when others then
973 IF l_DEBUG in ('Y', 'C') THEN
974 cep_standard.debug('EXCEPTION: CE_INTEREST_CALC.get_balance_info');
975 END IF;
976 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
977 FND_MESSAGE.Set_Token('PROCEDURE', 'CE_INTEREST_CALC.get_balance_info');
978 fnd_msg_pub.add;
979 END get_balance_info;
980 /* --------------------------------------------------------------------
981 | PRIVATE PROCEDURE |
982 | get_balance_pool_info |
983 | |
984 | CALLED BY |
985 | int_cal_detail_main |
986 | |
987 | DESCRIPTION |
988 | Get Interest Calculated balances information and insert in |
989 | CE_INT_CALC_DETAILS_GT |
990 --------------------------------------------------------------------- */
991 PROCEDURE get_balance_pool_info( p_from_date date,
992 p_to_date date,
993 p_interest_schedule_id number,
994 p_bank_account_id number,
995 p_interest_acct_type varchar2,
996 p_cashpool_id number,
997 p_row_count OUT NOCOPY NUMBER) IS
998 x_balance_date_from DATE;
999 x_balance_date_to DATE;
1000 x_bank_account_id NUMBER;
1001 x_int_calc_balance NUMBER;
1002 x_balance_range_id NUMBER;
1003 x_days NUMBER;
1004 x_first_row varchar2(1);
1005 x_rownum NUMBER;
1006 x_interest_rate NUMBER;
1007
1008 y_balance_date_from DATE;
1009 y_balance_date_to DATE;
1010 y_bank_account_id NUMBER;
1011 y_int_calc_balance NUMBER;
1012 y_balance_range_id NUMBER;
1013 y_days NUMBER;
1014 y_rownum NUMBER;
1015 y_first_row varchar2(1);
1016 y_record_from varchar2(15);
1017 y_row_count NUMBER;
1018 y_interest_rate NUMBER;
1019
1020
1021 BEGIN
1022 IF l_DEBUG in ('Y', 'C') THEN
1023 cep_standard.debug('>> CE_INTEREST_CALC.get_balance_pool_info');
1024 END IF;
1025
1026 OPEN balance_pool_info (p_from_date, p_to_date,p_bank_account_id, p_interest_schedule_id,
1027 p_interest_acct_type, p_cashpool_id );
1028 LOOP
1029 FETCH balance_pool_info INTO x_bank_account_id ,
1030 x_balance_date_from,
1031 x_int_calc_balance ,
1032 x_balance_range_id,
1033 x_interest_rate,
1034 x_first_row,
1035 x_rownum;
1036
1037
1038 EXIT WHEN balance_pool_info%NOTFOUND OR balance_pool_info%NOTFOUND IS NULL;
1039
1040 y_bank_account_id := x_bank_account_id;
1041 y_balance_date_from := x_balance_date_from;
1042 y_balance_range_id := x_balance_range_id;
1043 y_int_calc_balance := x_int_calc_balance;
1044 y_interest_rate := x_interest_rate;
1045 y_rownum := x_rownum;
1046 y_first_row := x_first_row;
1047 y_record_from := 'BALANCE';
1048
1049
1050 IF (x_first_row = 'Y') THEN
1051 y_balance_date_from := p_from_date;
1052 END IF;
1053
1054
1055 IF l_DEBUG in ('Y', 'C') THEN
1056 cep_standard.debug('y_bank_account_id = '|| y_bank_account_id);
1057 cep_standard.debug('y_balance_date_from = '|| y_balance_date_from);
1058 cep_standard.debug('y_int_calc_balance = '|| y_int_calc_balance);
1059 cep_standard.debug('y_interest_rate = '|| y_interest_rate);
1060 cep_standard.debug('y_first_row = '|| y_first_row);
1061 cep_standard.debug('y_record_from = '|| y_record_from);
1062 END IF;
1063 insert into CE_INT_CALC_DETAILS_GT
1064 (INTEREST_CALC_DETAIL_ID,
1065 INTEREST_SCHEDULE_ID, BANK_ACCOUNT_ID, FROM_DATE, TO_DATE,
1066 VALUE_DATED_BALANCE, NUMBER_OF_DAYS , INTEREST_AMOUNT,INTEREST_RATE,
1067 BALANCE_RANGE_ID, RECORD_FROM,
1068 INTEREST_ACCT_TYPE, CASHPOOL_ID,
1069 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
1070 LAST_UPDATE_LOGIN)
1071 values
1072 ( CE_INT_CALC_DETAILS_GT_S.nextval,
1073 p_interest_schedule_id, p_bank_account_id, y_balance_date_from, null,
1074 y_int_calc_balance, null, null, y_interest_rate,
1075 y_balance_range_id, y_record_from,
1076 p_interest_acct_type, p_cashpool_id,
1077 sysdate, -1, sysdate, -1, null);
1078
1079 IF l_DEBUG in ('Y', 'C') THEN
1080 cep_standard.debug('insert into CE_INT_CALC_DETAILS_GT completed ');
1081 END IF;
1082 END LOOP; --balance_pool_info
1083 p_row_count := balance_pool_info%ROWCOUNT;
1084 IF l_DEBUG in ('Y', 'C') THEN
1085 cep_standard.debug('p_row_count = '|| p_row_count );
1086 END IF;
1087
1088 CLOSE balance_pool_info;
1089
1090
1091 IF l_DEBUG in ('Y', 'C') THEN
1092 cep_standard.debug('<< CE_INTEREST_CALC.get_balance_pool_info');
1093 END IF;
1094
1095 EXCEPTION
1096 when others then
1097 IF l_DEBUG in ('Y', 'C') THEN
1098 cep_standard.debug('EXCEPTION: CE_INTEREST_CALC.get_balance_pool_info');
1099 END IF;
1100 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
1101 FND_MESSAGE.Set_Token('PROCEDURE', 'CE_INTEREST_CALC.get_balance_pool_info');
1102 fnd_msg_pub.add;
1103 END get_balance_pool_info;
1104
1105 /* --------------------------------------------------------------------
1106 | PRIVATE PROCEDURE |
1107 | get_interest_info |
1108 | |
1109 | CALLED BY |
1110 | int_cal_detail_main |
1111 | |
1112 | DESCRIPTION |
1113 | Some Interest Calculated balances that are returned from |
1114 | get_balance_info might have interest rate changes within the |
1115 | same balance. This procedure is used to handle the additional |
1116 | break down of the from/to date range with same balance, but |
1117 | difference interest rate. |
1118 | |
1119 | |
1120 --------------------------------------------------------------------- */
1121 PROCEDURE get_interest_info( p_from_date date,
1122 p_to_date date,
1123 p_interest_schedule_id number,
1124 p_bank_account_id number,
1125 p_interest_acct_type varchar2,
1126 p_cashpool_id number,
1127 p_row_count OUT NOCOPY NUMBER) IS
1128 x_balance_date_from DATE;
1129 x_balance_date_to DATE;
1130 x_bank_account_id NUMBER;
1131 x_int_calc_balance NUMBER;
1132 x_balance_range_id NUMBER;
1133 x_days NUMBER;
1134 x_first_row varchar2(1);
1135 x_rownum NUMBER;
1136 x_interest_rate NUMBER;
1137
1138 y_balance_date_from DATE;
1139 y_balance_date_to DATE;
1140 y_bank_account_id NUMBER;
1141 y_int_calc_balance NUMBER;
1142 y_balance_range_id NUMBER;
1143 y_days NUMBER;
1144 y_rownum NUMBER;
1145 y_first_row varchar2(1);
1146 y_record_from varchar2(15);
1147 y_row_count NUMBER;
1148 y_interest_rate NUMBER;
1149
1150 BEGIN
1151 IF l_DEBUG in ('Y', 'C') THEN
1152 cep_standard.debug('>> CE_INTEREST_CALC.get_interest_info');
1153 END IF;
1154
1155 OPEN interest_info (p_from_date, p_to_date, p_bank_account_id, p_interest_schedule_id, p_interest_acct_type );
1156 LOOP
1157 FETCH interest_info INTO x_bank_account_id ,
1158 x_balance_date_from,
1159 x_int_calc_balance ,
1160 x_balance_range_id,
1161 x_interest_rate;
1162
1163
1164
1165 EXIT WHEN interest_info%NOTFOUND OR interest_info%NOTFOUND IS NULL;
1166
1167 y_bank_account_id := x_bank_account_id;
1168 y_balance_date_from := x_balance_date_from;
1169 y_balance_range_id := x_balance_range_id;
1170 y_int_calc_balance := x_int_calc_balance;
1171 y_interest_rate := x_interest_rate;
1172
1173 --y_rownum := x_rownum;
1174 --y_first_row := x_first_row;
1175 y_record_from := 'INTEREST';
1176
1177 IF (x_first_row = 'Y') THEN
1178 y_balance_date_from := p_from_date;
1179 END IF;
1180
1181
1182 IF l_DEBUG in ('Y', 'C') THEN
1183 cep_standard.debug('y_bank_account_id = '|| y_bank_account_id);
1184 cep_standard.debug('y_balance_date_from = '|| y_balance_date_from);
1185 cep_standard.debug('y_int_calc_balance = '|| y_int_calc_balance);
1186 cep_standard.debug('y_balance_range_id = '|| y_balance_range_id);
1187 cep_standard.debug('y_interest_rate = '|| y_interest_rate );
1188
1189 END IF;
1190 insert into CE_INT_CALC_DETAILS_GT
1191 (INTEREST_CALC_DETAIL_ID,
1192 INTEREST_SCHEDULE_ID, BANK_ACCOUNT_ID, FROM_DATE, TO_DATE,
1193 VALUE_DATED_BALANCE, NUMBER_OF_DAYS , INTEREST_AMOUNT,INTEREST_RATE,
1194 BALANCE_RANGE_ID, RECORD_FROM,
1195 INTEREST_ACCT_TYPE, CASHPOOL_ID,
1196 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
1197 LAST_UPDATE_LOGIN)
1198 values
1199 ( CE_INT_CALC_DETAILS_GT_S.nextval,
1200 p_interest_schedule_id, p_bank_account_id, y_balance_date_from, null,
1201 y_int_calc_balance, null, null, y_interest_rate,
1202 y_balance_range_id, y_record_from,
1203 p_interest_acct_type, p_cashpool_id,
1204 sysdate, -1, sysdate, -1, null);
1205
1206 END LOOP; --interest_info
1207 p_row_count := interest_info%ROWCOUNT;
1208 IF l_DEBUG in ('Y', 'C') THEN
1209 cep_standard.debug('p_row_count = '|| p_row_count );
1210 END IF;
1211
1212 CLOSE interest_info;
1213
1214
1215 IF l_DEBUG in ('Y', 'C') THEN
1216 cep_standard.debug('<< CE_INTEREST_CALC.get_interest_info');
1217 END IF;
1218
1219 EXCEPTION
1220 when others then
1221 IF l_DEBUG in ('Y', 'C') THEN
1222 cep_standard.debug('EXCEPTION: CE_INTEREST_CALC.get_interest_info');
1223 END IF;
1224 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
1225 FND_MESSAGE.Set_Token('PROCEDURE', 'CE_INTEREST_CALC.get_interest_info');
1226 fnd_msg_pub.add;
1227
1228 END get_interest_info;
1229
1230 /* --------------------------------------------------------------------
1231 | PRIVATE PROCEDURE |
1232 | get_interest_pool_info |
1233 | |
1234 | CALLED BY |
1235 | int_cal_detail_main |
1236 | |
1237 | DESCRIPTION |
1238 | Some Interest Calculated balances that are returned from |
1239 | get_balance_info might have interest rate changes within the |
1240 | same balance. This procedure is used to handle the additional |
1241 | break down of the from/to date range with same balance, but |
1242 | difference interest rate. |
1243 | |
1244 | |
1245 --------------------------------------------------------------------- */
1246 PROCEDURE get_interest_pool_info( p_from_date date,
1247 p_to_date date,
1248 p_interest_schedule_id number,
1249 p_bank_account_id number,
1250 p_interest_acct_type varchar2,
1251 p_cashpool_id number,
1252 p_row_count OUT NOCOPY NUMBER) IS
1253 x_balance_date_from DATE;
1254 x_balance_date_to DATE;
1255 x_bank_account_id NUMBER;
1256 x_int_calc_balance NUMBER;
1257 x_balance_range_id NUMBER;
1258 x_days NUMBER;
1259 x_first_row varchar2(1);
1260 x_rownum NUMBER;
1261 x_interest_rate NUMBER;
1262
1263 y_balance_date_from DATE;
1264 y_balance_date_to DATE;
1265 y_bank_account_id NUMBER;
1266 y_int_calc_balance NUMBER;
1267 y_balance_range_id NUMBER;
1268 y_days NUMBER;
1269 y_rownum NUMBER;
1270 y_first_row varchar2(1);
1271 y_record_from varchar2(15);
1272 y_row_count NUMBER;
1273 y_interest_rate NUMBER;
1274
1275 BEGIN
1276 IF l_DEBUG in ('Y', 'C') THEN
1277 cep_standard.debug('>> CE_INTEREST_CALC.get_interest_pool_info');
1278 END IF;
1279
1280 OPEN interest_pool_info (p_from_date, p_to_date, p_bank_account_id, p_interest_schedule_id,
1281 p_interest_acct_type, p_cashpool_id );
1282 LOOP
1283 FETCH interest_pool_info INTO x_bank_account_id ,
1284 x_balance_date_from,
1285 x_int_calc_balance ,
1286 x_balance_range_id,
1287 x_interest_rate;
1288
1289
1290
1291 EXIT WHEN interest_pool_info%NOTFOUND OR interest_pool_info%NOTFOUND IS NULL;
1292
1293 y_bank_account_id := x_bank_account_id;
1294 y_balance_date_from := x_balance_date_from;
1295 y_balance_range_id := x_balance_range_id;
1296 y_int_calc_balance := x_int_calc_balance;
1297 y_interest_rate := x_interest_rate;
1298
1299 --y_rownum := x_rownum;
1300 --y_first_row := x_first_row;
1301 y_record_from := 'INTEREST';
1302
1303 IF (x_first_row = 'Y') THEN
1304 y_balance_date_from := p_from_date;
1305 END IF;
1306
1307
1308 IF l_DEBUG in ('Y', 'C') THEN
1309 cep_standard.debug('y_bank_account_id = '|| y_bank_account_id);
1310 cep_standard.debug('y_balance_date_from = '|| y_balance_date_from);
1311 cep_standard.debug('y_int_calc_balance = '|| y_int_calc_balance);
1312 cep_standard.debug('y_balance_range_id = '|| y_balance_range_id);
1313 cep_standard.debug('y_interest_rate = '|| y_interest_rate );
1314
1315 END IF;
1316 insert into CE_INT_CALC_DETAILS_GT
1317 (INTEREST_CALC_DETAIL_ID,
1318 INTEREST_SCHEDULE_ID, BANK_ACCOUNT_ID, FROM_DATE, TO_DATE,
1319 VALUE_DATED_BALANCE, NUMBER_OF_DAYS , INTEREST_AMOUNT,INTEREST_RATE,
1320 BALANCE_RANGE_ID, RECORD_FROM,
1321 INTEREST_ACCT_TYPE, CASHPOOL_ID,
1322 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
1323 LAST_UPDATE_LOGIN)
1324 values
1325 ( CE_INT_CALC_DETAILS_GT_S.nextval,
1326 p_interest_schedule_id, p_bank_account_id, y_balance_date_from, null,
1327 y_int_calc_balance, null, null, y_interest_rate,
1328 y_balance_range_id, y_record_from,
1329 p_interest_acct_type, p_cashpool_id,
1330 sysdate, -1, sysdate, -1, null);
1331
1332 END LOOP; --interest_pool_info
1333 p_row_count := interest_pool_info%ROWCOUNT;
1334 IF l_DEBUG in ('Y', 'C') THEN
1335 cep_standard.debug('p_row_count = '|| p_row_count );
1336 END IF;
1337
1338 CLOSE interest_pool_info;
1339
1340
1341 IF l_DEBUG in ('Y', 'C') THEN
1342 cep_standard.debug('<< CE_INTEREST_CALC.get_interest_pool_info');
1343 END IF;
1344
1345 EXCEPTION
1346 when others then
1347 IF l_DEBUG in ('Y', 'C') THEN
1348 cep_standard.debug('EXCEPTION: CE_INTEREST_CALC.get_interest_pool_info');
1349 END IF;
1350 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
1351 FND_MESSAGE.Set_Token('PROCEDURE', 'CE_INTEREST_CALC.get_interest_pool_info');
1352 fnd_msg_pub.add;
1353
1354 END get_interest_pool_info;
1355
1356
1357 PROCEDURE get_missing_interest_info( p_from_date date,
1358 p_to_date date,
1359 p_interest_schedule_id number,
1360 p_bank_account_id number,
1361 p_row_count OUT NOCOPY NUMBER) IS
1362 x_balance_date_from DATE;
1363 x_balance_date_to DATE;
1364 x_bank_account_id NUMBER;
1365 x_int_calc_balance NUMBER;
1366 x_balance_range_id NUMBER;
1367 x_days NUMBER;
1368 x_first_row varchar2(1);
1369 x_rownum NUMBER;
1370 x_interest_rate NUMBER;
1371
1372 y_balance_date_from DATE;
1373 y_balance_date_to DATE;
1374 y_bank_account_id NUMBER;
1375 y_int_calc_balance NUMBER;
1376 y_balance_range_id NUMBER;
1377 y_days NUMBER;
1378 y_rownum NUMBER;
1379 y_first_row varchar2(1);
1380 y_record_from varchar2(15);
1381 y_row_count NUMBER;
1382 y_interest_rate NUMBER;
1383 x_interest_calc_detail_id number;
1384 y_interest_calc_detail_id number;
1385
1386 BEGIN
1387 IF l_DEBUG in ('Y', 'C') THEN
1388 cep_standard.debug('>> CE_INTEREST_CALC.get_interest_info');
1389 END IF;
1390
1391 OPEN missing_interest_info (p_from_date, p_to_date,p_bank_account_id, p_interest_schedule_id );
1392 LOOP
1393 FETCH missing_interest_info INTO x_interest_rate,
1394 x_balance_date_from,
1395 x_interest_calc_detail_id;
1396
1397 EXIT WHEN missing_interest_info%NOTFOUND OR missing_interest_info%NOTFOUND IS NULL;
1398
1399 y_balance_date_from := x_balance_date_from;
1400 y_interest_rate := x_interest_rate;
1401 y_interest_calc_detail_id := x_interest_calc_detail_id;
1402
1403 --y_rownum := x_rownum;
1404 --y_first_row := x_first_row;
1405
1406 IF l_DEBUG in ('Y', 'C') THEN
1407 cep_standard.debug('y_bank_account_id = '|| y_bank_account_id);
1408 cep_standard.debug('y_balance_date_from = '|| y_balance_date_from);
1409 cep_standard.debug('y_int_calc_balance = '|| y_int_calc_balance);
1410 cep_standard.debug('y_interest_rate = '|| y_interest_rate );
1411 cep_standard.debug('y_interest_calc_detail_id = '|| y_interest_calc_detail_id);
1412
1413 END IF;
1414
1415 update CE_INT_CALC_DETAILS_GT
1416 set INTEREST_RATE = y_interest_rate,
1417 LAST_UPDATE_DATE = sysdate
1418 where from_date = y_balance_date_from
1419 and bank_account_id = p_bank_account_id
1420 and INTEREST_SCHEDULE_ID = p_interest_schedule_id
1421 and interest_calc_detail_id = y_interest_calc_detail_id;
1422
1423
1424 END LOOP; --missing_interest_info
1425
1426 CLOSE missing_interest_info;
1427
1428
1429 IF l_DEBUG in ('Y', 'C') THEN
1430 cep_standard.debug('<< CE_INTEREST_CALC.get_missing_interest_info');
1431 END IF;
1432
1433 EXCEPTION
1434 when others then
1435 IF l_DEBUG in ('Y', 'C') THEN
1436 cep_standard.debug('EXCEPTION: CE_INTEREST_CALC.get_missing_interest_info');
1437 END IF;
1438 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
1439 FND_MESSAGE.Set_Token('PROCEDURE', 'CE_INTEREST_CALC.get_missing_interest_info');
1440 fnd_msg_pub.add;
1441
1442 END get_missing_interest_info;
1443
1444 /* --------------------------------------------------------------------
1445 | PRIVATE PROCEDURE |
1446 | set_range_and_rate
1447 | |
1448 | CALLED BY |
1449 | int_cal_detail_main |
1450 | |
1451 | DESCRIPTION |
1452 | Use to set the balance range id and interest rate
1453 | on CE_INT_CALC_DETAILS_GT for cashpool accounts only |
1454 | |
1455 --------------------------------------------------------------------- */
1456 PROCEDURE set_range_and_rate(p_from_date date,
1457 p_to_date date,
1458 p_interest_schedule_id number,
1459 p_bank_account_id number,
1460 p_interest_acct_type varchar2,
1461 p_cashpool_id number
1462 ) IS
1463 x_balance_date_from DATE;
1464 x_balance_date_to DATE;
1465 x_bank_account_id NUMBER;
1466 x_days NUMBER;
1467
1468 x_rownum NUMBER;
1469 x_previous_date_from DATE := null;
1470
1471 y_balance_date_from DATE;
1472 y_end_date DATE := null;
1473 y_rownum NUMBER;
1474 x_balance_range_id NUMBER;
1475 x_interest_rate NUMBER;
1476 y_balance_range_id NUMBER;
1477 y_interest_rate NUMBER;
1478 x_interest_calc_detail_id number;
1479 y_interest_calc_detail_id number;
1480
1481 i number := 0;
1482
1483 BEGIN
1484 IF l_DEBUG in ('Y', 'C') THEN
1485 cep_standard.debug('>> CE_INTEREST_CALC.range_and_rate_cur');
1486 END IF;
1487
1488 OPEN range_and_rate_cur (p_from_date, p_to_date,p_bank_account_id, p_interest_schedule_id, p_interest_acct_type,p_cashpool_id );
1489 LOOP
1490 FETCH range_and_rate_cur INTO x_interest_calc_detail_id,
1491 x_balance_range_id,
1492 x_interest_rate;
1493
1494 EXIT WHEN range_and_rate_cur%NOTFOUND OR range_and_rate_cur%NOTFOUND IS NULL;
1495
1496 y_balance_range_id := x_balance_range_id;
1497 y_interest_rate := x_interest_rate;
1498 y_interest_calc_detail_id := x_interest_calc_detail_id ;
1499
1500 IF l_DEBUG in ('Y', 'C') THEN
1501 cep_standard.debug('y_balance_range_id = '|| y_balance_range_id );
1502 cep_standard.debug('y_interest_rate = '|| y_interest_rate);
1503 cep_standard.debug('y_interest_calc_detail_id = '|| y_interest_calc_detail_id);
1504 END IF;
1505
1506 update CE_INT_CALC_DETAILS_GT
1507 set BALANCE_RANGE_ID = y_balance_range_id,
1508 INTEREST_RATE = y_interest_rate
1509 where
1510 bank_account_id = p_bank_account_id
1511 and INTEREST_ACCT_TYPE = p_interest_acct_type
1512 and cashpool_id = p_cashpool_id
1513 and interest_calc_detail_id = y_interest_calc_detail_id;
1514
1515 END LOOP; --range_and_rate_cur
1516
1517 CLOSE range_and_rate_cur;
1518
1519
1520 IF l_DEBUG in ('Y', 'C') THEN
1521 cep_standard.debug('<< CE_INTEREST_CALC.set_range_and_rate');
1522 END IF;
1523
1524 EXCEPTION
1525 when others then
1526 IF l_DEBUG in ('Y', 'C') THEN
1527 cep_standard.debug('EXCEPTION: CE_INTEREST_CALC.set_range_and_rate');
1528 END IF;
1529 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
1530 FND_MESSAGE.Set_Token('PROCEDURE', 'CE_INTEREST_CALC.set_range_and_rate');
1531 fnd_msg_pub.add;
1532
1533 END set_range_and_rate;
1534
1535 /* --------------------------------------------------------------------
1536 | PRIVATE PROCEDURE |
1537 | set_end_date |
1538 | |
1539 | CALLED BY |
1540 | int_cal_detail_main |
1541 | |
1542 | DESCRIPTION |
1543 | Use to set the TO_DATE on CE_INT_CALC_DETAILS_GT |
1544 | |
1545 --------------------------------------------------------------------- */
1546 PROCEDURE set_end_date( p_from_date date,
1547 p_to_date date,
1548 p_interest_schedule_id number,
1549 p_bank_account_id number,
1550 p_interest_acct_type varchar2,
1551 p_cashpool_id number
1552 ) IS
1553 x_balance_date_from DATE;
1554 x_balance_date_to DATE;
1555 x_bank_account_id NUMBER;
1556 x_days NUMBER;
1557
1558 x_rownum NUMBER;
1559 x_previous_date_from DATE := null;
1560
1561 y_balance_date_from DATE;
1562 y_end_date DATE := null;
1563 y_rownum NUMBER;
1564 x_interest_calc_detail_id number;
1565 y_interest_calc_detail_id number;
1566
1567 i number := 0;
1568 p_row_count NUMBER;
1569
1570 BEGIN
1571 IF l_DEBUG in ('Y', 'C') THEN
1572 cep_standard.debug('>> CE_INTEREST_CALC.set_end_date');
1573 END IF;
1574
1575 OPEN end_date_cur (p_from_date, p_to_date,p_bank_account_id, p_interest_schedule_id,
1576 p_interest_acct_type, p_cashpool_id );
1577 LOOP
1578 FETCH end_date_cur INTO x_balance_date_from,
1579 x_rownum,
1580 x_interest_calc_detail_id;
1581
1582 EXIT WHEN end_date_cur%NOTFOUND OR end_date_cur%NOTFOUND IS NULL;
1583
1584 y_balance_date_from := x_balance_date_from;
1585 y_rownum := x_rownum;
1586 y_interest_calc_detail_id := x_interest_calc_detail_id ;
1587
1588 i :=i + 1;
1589 IF (i = 1) THEN
1590 y_end_date := p_to_date;
1591
1592 ELSE
1593 -- bug 5393669/5479708
1594 --y_end_date := (x_previous_date_from - 1);
1595 y_end_date := x_previous_date_from;
1596
1597 END IF;
1598
1599
1600 IF l_DEBUG in ('Y', 'C') THEN
1601 cep_standard.debug('y_balance_date_from = '|| y_balance_date_from);
1602 cep_standard.debug('y_end_date = '|| y_end_date);
1603 cep_standard.debug('y_interest_calc_detail_id = '|| y_interest_calc_detail_id);
1604 cep_standard.debug(' i = '|| i);
1605
1606 END IF;
1607
1608 update CE_INT_CALC_DETAILS_GT
1609 set to_date = y_end_date,
1610 LAST_UPDATE_DATE = sysdate
1611 where from_date = x_balance_date_from
1612 --and bank_account_id = p_bank_account_id
1613 and INTEREST_SCHEDULE_ID = p_interest_schedule_id
1614 and interest_calc_detail_id = y_interest_calc_detail_id;
1615
1616 x_previous_date_from := x_balance_date_from;
1617
1618 IF l_DEBUG in ('Y', 'C') THEN
1619 cep_standard.debug('x_previous_date_from = '|| x_previous_date_from);
1620
1621 END IF;
1622
1623 END LOOP; --end_date_cur
1624
1625 CLOSE end_date_cur;
1626
1627
1628 IF l_DEBUG in ('Y', 'C') THEN
1629 cep_standard.debug('<< CE_INTEREST_CALC.set_end_date');
1630 END IF;
1631
1632 EXCEPTION
1633 when others then
1634 IF l_DEBUG in ('Y', 'C') THEN
1635 cep_standard.debug('EXCEPTION: CE_INTEREST_CALC.set_end_date');
1636 END IF;
1637 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
1638 FND_MESSAGE.Set_Token('PROCEDURE', 'CE_INTEREST_CALC.set_end_date');
1639 fnd_msg_pub.add;
1640
1641 END set_end_date;
1642
1643
1644 /* --------------------------------------------------------------------
1645 | PRIVATE PROCEDURE |
1646 | set_int_rate |
1647 | |
1648 | CALLED BY |
1649 | int_cal_xtr |
1650 | |
1651 | DESCRIPTION |
1652 | Use to set the INTEREST_RATE on CE_INT_CALC_DETAILS_GT |
1653 | |
1654 --------------------------------------------------------------------- */
1655 PROCEDURE set_int_rate( p_from_date date,
1656 p_to_date date,
1657 p_interest_schedule_id number,
1658 p_bank_account_id number,
1659 p_interest_acct_type varchar2,
1660 p_interest_rate number
1661 ) IS
1662 x_balance_date_from DATE;
1663 x_balance_date_to DATE;
1664 x_bank_account_id NUMBER;
1665 x_days NUMBER;
1666
1667 x_rownum NUMBER;
1668 x_previous_date_from DATE := null;
1669
1670 y_balance_date_from DATE;
1671 y_end_date DATE := null;
1672 y_rownum NUMBER;
1673 x_interest_calc_detail_id number;
1674 y_interest_calc_detail_id number;
1675
1676 i number := 0;
1677
1678 BEGIN
1679 IF l_DEBUG in ('Y', 'C') THEN
1680 cep_standard.debug('>> CE_INTEREST_CALC.set_int_rate');
1681 END IF;
1682
1683 OPEN xtr_cur (p_from_date, p_to_date,p_bank_account_id, p_interest_schedule_id,
1684 p_interest_acct_type);
1685 LOOP
1686 FETCH xtr_cur INTO x_balance_date_from,
1687 x_rownum,
1688 x_interest_calc_detail_id;
1689
1690 EXIT WHEN xtr_cur%NOTFOUND OR xtr_cur%NOTFOUND IS NULL;
1691
1692 y_balance_date_from := x_balance_date_from;
1693 y_rownum := x_rownum;
1694 y_interest_calc_detail_id := x_interest_calc_detail_id ;
1695
1696 IF l_DEBUG in ('Y', 'C') THEN
1697 cep_standard.debug('y_interest_calc_detail_id = '|| y_interest_calc_detail_id);
1698 END IF;
1699
1700 update CE_INT_CALC_DETAILS_GT
1701 set INTEREST_RATE = p_interest_rate
1702 where interest_calc_detail_id = y_interest_calc_detail_id;
1703
1704
1705 END LOOP; --xtr_cur
1706
1707 CLOSE xtr_cur;
1708
1709
1710 IF l_DEBUG in ('Y', 'C') THEN
1711 cep_standard.debug('<< CE_INTEREST_CALC.set_int_rate');
1712 END IF;
1713
1714 EXCEPTION
1715 when others then
1716 IF l_DEBUG in ('Y', 'C') THEN
1717 cep_standard.debug('EXCEPTION: CE_INTEREST_CALC.set_int_rate');
1718 END IF;
1719 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
1720 FND_MESSAGE.Set_Token('PROCEDURE', 'CE_INTEREST_CALC.set_int_rate');
1721 fnd_msg_pub.add;
1722
1723 END set_int_rate;
1724
1725 /* --------------------------------------------------------------------
1726 | PRIVATE PROCEDURE |
1727 | calculate_interest
1728 | |
1729 | CALLED BY |
1730 | int_cal_detail_main
1731 | int_cal_xtr |
1732 | |
1733 | CALLS |
1734 | |
1735 | DESCRIPTION |
1736 | Handles Interest Calculation |
1737 | |
1738 --------------------------------------------------------------------- */
1739
1740 PROCEDURE calculate_interest( p_from_date date,
1741 p_to_date date,
1742 p_interest_schedule_id number,
1743 p_bank_account_id number,
1744 p_interest_acct_type varchar2,
1745 p_cashpool_id number
1746 ) IS
1747 x_balance_date_from DATE;
1748 x_balance_date_to DATE;
1749 x_min_balance_date DATE;
1750 x_max_balance_date DATE;
1751
1752 x_days NUMBER;
1753 x_days_from NUMBER;
1754 x_days_to NUMBER;
1755 x_days_over_year NUMBER;
1756 x_days_over_year_from NUMBER;
1757 x_days_over_year_to NUMBER;
1758
1759 x_basis varchar2(30);
1760 x_interest_includes varchar2(30);
1761 x_interest_rounding varchar2(30);
1762 x_day_count_basis varchar2(30);
1763
1764 x_int_calc_balance NUMBER;
1765 x_add_days NUMBER;
1766 x_days_in_yr NUMBER;
1767 x_days_in_yr_from NUMBER;
1768 x_days_in_yr_to NUMBER;
1769 x_interest_amount NUMBER;
1770 x_interest_amount_round NUMBER;
1771 x_interest_rate NUMBER;
1772 x_new_interest_rate number;
1773 x_currency_code varchar2(15);
1774 x_from_year number;
1775 x_to_year number;
1776 x_from_year_leap BOOLEAN;
1777 x_to_year_leap BOOLEAN;
1778 x_num_of_yrs number;
1779 X_ADD_NUM_OF_YRS number;
1780 x_interest_calc_detail_id number;
1781
1782 l_count NUMBER;
1783 l_diff NUMBER;
1784 l_amount NUMBER;
1785 l_balance NUMBER;
1786 l_min NUMBER;
1787 l_max NUMBER;
1788 l_rate NUMBER;
1789 l_wavg NUMBER;
1790 l_int_rate NUMBER;
1791 x_add_min_pre_amt NUMBER;
1792
1793 precision NUMBER default NULL;
1794 ext_precision NUMBER default NULL;
1795 min_acct_unit NUMBER default NULL;
1796
1797 BEGIN
1798 IF l_DEBUG in ('Y', 'C') THEN
1799 cep_standard.debug('>> CE_INTEREST_CALC.calculate_interest');
1800 cep_standard.debug('p_from_date='||p_from_date||', p_to_date='||p_to_date);
1801 END IF;
1802
1803 select DAY_COUNT_BASIS,
1804 INTEREST_INCLUDES,
1805 INTEREST_ROUNDING,
1806 BASIS,
1807 CURRENCY_CODE
1808 INTO
1809 x_day_count_basis ,
1810 x_interest_includes ,
1811 x_interest_rounding,
1812 x_basis,
1813 x_currency_code
1814 from CE_INTEREST_SCHEDULES
1815 WHERE INTEREST_SCHEDULE_ID = p_interest_schedule_id;
1816
1817 FND_CURRENCY.get_info(x_currency_code,
1818 precision,
1819 ext_precision,
1820 min_acct_unit);
1821
1822 -- bug 5393669/5479708
1823 select min(BALANCE_DATE) , max(BALANCE_DATE)
1824 INTO x_min_balance_date , x_max_balance_date
1825 from ce_bank_acct_balances
1826 where bank_account_id = p_bank_account_id;
1827
1828 SELECT MIN(FROM_BALANCE_AMOUNT)
1829 INTO x_add_min_pre_amt
1830 FROM ce_interest_bal_ranges
1831 WHERE INTEREST_SCHEDULE_ID = p_interest_schedule_id
1832 AND FROM_BALANCE_AMOUNT > 0;
1833
1834 IF l_DEBUG in ('Y', 'C') THEN
1835 cep_standard.debug('x_basis = '|| x_basis);
1836 cep_standard.debug('x_interest_includes = '|| x_interest_includes );
1837 cep_standard.debug('x_interest_rounding = '|| x_interest_rounding );
1838 cep_standard.debug('x_day_count_basis = '|| x_day_count_basis );
1839 cep_standard.debug('precision = '|| precision);
1840 cep_standard.debug('ext_precision = '|| ext_precision);
1841 cep_standard.debug('min_acct_unit = '|| min_acct_unit);
1842
1843 cep_standard.debug('x_add_min_pre_amt = '|| x_add_min_pre_amt);
1844
1845 cep_standard.debug('x_min_balance_date = '|| x_min_balance_date);
1846 cep_standard.debug('x_max_balance_date = '|| x_max_balance_date);
1847
1848 END IF;
1849
1850 /*
1851 IF (nvl(x_interest_includes,'F') in ('F', 'L')) THEN
1852 x_add_days := 1;
1853 ELSE
1854 x_add_days := 2;
1855 END IF;
1856 */
1857
1858 IF (nvl(x_day_count_basis,'ACTUAL365') = 'ACTUAL365') THEN
1859 x_days_in_yr := 365;
1860 ELSIF (x_day_count_basis = 'ACTUAL360') THEN
1861 x_days_in_yr := 360;
1862 ELSIF (x_day_count_basis = 'ACTUAL/ACTUAL') THEN
1863 x_days_in_yr := null;
1864 END IF;
1865
1866 -----------------------------------------------------------------------------
1867
1868 -- Bug 6825932 start
1869 if calc_detail_cur%isopen then
1870 close calc_detail_cur ;
1871 end if;
1872 -- Bug 6825932 end
1873 OPEN calc_detail_cur (p_from_date, p_to_date,p_bank_account_id, p_interest_schedule_id, p_interest_acct_type, p_cashpool_id );
1874 LOOP
1875 FETCH calc_detail_cur INTO x_balance_date_from,
1876 x_balance_date_to ,
1877 x_int_calc_balance ,
1878 x_interest_rate,
1879 x_interest_calc_detail_id ;
1880
1881
1882 EXIT WHEN calc_detail_cur%NOTFOUND OR calc_detail_cur%NOTFOUND IS NULL;
1883
1884 -- bug 5393669/5479708
1885 /* IF (nvl(x_interest_includes,'F') = 'F') THEN
1886 IF ( p_from_date = p_to_date) THEN
1887 x_add_days := 1;
1888 ELSIF (p_to_date=x_balance_date_to) THEN
1889 x_add_days := 0;
1890 ELSE
1891 x_add_days := 1;
1892 END IF;
1893 ELSIF (nvl(x_interest_includes,'F') = 'L') THEN
1894 IF ( p_from_date = p_to_date) THEN
1895 x_add_days := 1;
1896 ELSIF (p_from_date=x_balance_date_from) THEN
1897 x_add_days := 0;
1898 ELSIF (p_to_date=x_balance_date_to) THEN
1899 x_add_days := 1;
1900 ELSE
1901 x_add_days := 1;
1902 END IF;
1903 ELSIF (nvl(x_interest_includes,'F') = 'B') THEN
1904 IF ( p_from_date = p_to_date) THEN
1905 x_add_days := 1;
1906 ELSIF (p_from_date=x_balance_date_from) THEN
1907 x_add_days := 1;
1908 ELSIF (p_to_date=x_balance_date_to) THEN
1909 x_add_days := 1;
1910 ELSE
1911 x_add_days := 1;
1912 END IF;
1913 END IF;
1914 */
1915
1916 IF (nvl(x_interest_includes,'F') in ('L', 'F')) THEN
1917 x_add_days := 0;
1918 ELSIF (nvl(x_interest_includes,'F') = 'B') THEN
1919 IF ( p_from_date = p_to_date) THEN
1920 x_add_days := 0;
1921 --ELSIF ((p_from_date=x_balance_date_from) and
1922 -- (x_balance_date_from =x_min_balance_date)) THEN
1923 ELSIF (x_balance_date_from =x_min_balance_date) THEN
1924 x_add_days := 1;
1925 ELSE
1926 x_add_days := 0;
1927 END IF;
1928 END IF;
1929
1930 IF (x_day_count_basis = 'ACTUAL/ACTUAL') THEN
1931 -- find days in yr
1932 x_days_in_yr := null;
1933 x_from_year := to_char(x_balance_date_from, 'yyyy');
1934 x_to_year := to_char(x_balance_date_to, 'yyyy');
1935
1936 IF (x_from_year = x_to_year) THEN
1937 x_from_year_leap := IsLeapYear(to_char(x_balance_date_from, 'yyyy'));
1938 IF (x_from_year_leap) THEN
1939 x_days_in_yr := 366;
1940 ELSE
1941 x_days_in_yr := 365;
1942 END IF;
1943
1944 x_days := (x_balance_date_to - x_balance_date_from + x_add_days );
1945 x_days_over_year := x_days/nvl(x_days_in_yr,1);
1946
1947 ELSE
1948 x_from_year_leap := IsLeapYear(to_char(x_balance_date_from, 'yyyy'));
1949 x_to_year_leap := IsLeapYear(to_char(x_balance_date_to, 'yyyy'));
1950 x_add_num_of_yrs := (x_to_year - x_from_year - 1 );
1951
1952 IF (x_from_year_leap) THEN
1953 x_days_in_yr_from := 366;
1954 ELSE
1955 x_days_in_yr_from := 365;
1956 END IF;
1957
1958 IF (x_to_year_leap) THEN
1959 x_days_in_yr_to := 366;
1960 ELSE
1961 x_days_in_yr_to := 365;
1962 END IF;
1963
1964 -- Bug 6825932 Start
1965 x_days_from := to_date('31-Dec-'||x_from_year) - to_date(x_balance_date_from) ;
1966 x_days_to := to_date('01-Jan'||x_to_year) - to_date(x_balance_date_to) ;
1967 -- Bug 6825932 end
1968 --x_days_over_year := x_days/x_days_in_yr_from;
1969 x_days_over_year_from := x_days_from/x_days_in_yr_from;
1970 x_days_over_year_to := x_days_to/x_days_in_yr_to;
1971 x_days_over_year := x_days_over_year_from + x_days_over_year_to + x_add_num_of_yrs ;
1972
1973 END IF; --(x_from_year = x_to_year)
1974 ELSE -- not 'ACTUAL/ACTUAL'
1975 x_days := (x_balance_date_to - x_balance_date_from + x_add_days );
1976 x_days_over_year := x_days/nvl(x_days_in_yr,1) ;
1977
1978 END IF; --(x_day_count_basis = 'ACTUAL/ACTUAL')
1979
1980 IF l_DEBUG in ('Y', 'C') THEN
1981 cep_standard.debug('---------- new balance range ------------');
1982 cep_standard.debug('x_balance_date_from = '|| x_balance_date_from);
1983 cep_standard.debug('x_balance_date_to = '|| x_balance_date_to);
1984 cep_standard.debug('x_int_calc_balance = '|| x_int_calc_balance );
1985 cep_standard.debug('x_interest_rate = '|| x_interest_rate );
1986 cep_standard.debug('x_add_days = '|| x_add_days);
1987 cep_standard.debug('x_days_over_year = '|| x_days_over_year);
1988 cep_standard.debug('x_days = '|| x_days);
1989 END IF;
1990 --------------------------------------------------------------------------------------------
1991 --x_days := (x_balance_date_to - x_balance_date_from + x_add_days );
1992
1993 -- calculate the interest amount
1994 IF (nvl(x_basis, 'FLAT') = 'FLAT') THEN
1995 --x_interest_amount := (x_int_calc_balance * x_interest_rate/100 * 1/nvl(x_days_in_yr,1) * x_days);
1996 x_interest_amount := (x_int_calc_balance * x_interest_rate/100 * x_days_over_year);
1997 ELSE -- STEP
1998
1999 l_amount := x_int_calc_balance; --l_balance;
2000 IF l_DEBUG in ('Y', 'C') THEN
2001 cep_standard.debug('l_amount = '|| l_amount);
2002 END IF;
2003
2004 if l_amount <= 0 then
2005
2006 IF l_DEBUG in ('Y', 'C') THEN
2007 cep_standard.debug('open DR_RANGE');
2008 END IF;
2009
2010 --open DR_RANGE(p_from_date , p_to_date ,
2011 open DR_RANGE(x_balance_date_from, x_balance_date_to ,
2012 p_bank_account_id, p_interest_schedule_id, l_amount);
2013 l_wavg := 0;
2014 l_count := 0;
2015 LOOP
2016 fetch DR_RANGE INTO l_min,l_max,l_rate;
2017 EXIT WHEN DR_RANGE%NOTFOUND;
2018
2019 IF l_DEBUG in ('Y', 'C') THEN
2020 cep_standard.debug('-------------');
2021 cep_standard.debug('l_min = '|| l_min );
2022 cep_standard.debug('l_max = '|| l_max );
2023 cep_standard.debug('l_rate = '|| l_rate );
2024 END IF;
2025
2026
2027 if l_max > 0 then
2028 l_max := 0;
2029 end if;
2030 if l_min < l_amount then
2031 l_min := l_amount;
2032 end if;
2033
2034 IF l_DEBUG in ('Y', 'C') THEN
2035 cep_standard.debug('new l_min = '|| l_min );
2036 cep_standard.debug('new l_max = '|| l_max );
2037
2038 END IF;
2039
2040 l_diff := (l_amount - l_max) - (l_amount - l_min);
2041
2042 IF l_DEBUG in ('Y', 'C') THEN
2043 cep_standard.debug('l_diff = '|| l_diff );
2044 cep_standard.debug('current l_wavg = '|| l_wavg );
2045 cep_standard.debug('current l_count = '|| l_count );
2046 END IF;
2047
2048 l_wavg := l_wavg + (l_diff * l_rate);
2049 l_count := l_count + 1;
2050
2051 IF l_DEBUG in ('Y', 'C') THEN
2052 cep_standard.debug('l_min = '|| l_min );
2053 cep_standard.debug('l_max = '|| l_max );
2054 cep_standard.debug('l_rate = '|| l_rate );
2055 cep_standard.debug('l_diff = '|| l_diff );
2056 cep_standard.debug('l_wavg = '|| l_wavg );
2057 END IF;
2058
2059 END LOOP;
2060 close DR_RANGE;
2061 --if nvl(l_balance,0) <>0 then
2062 -- l_int_rate := round(l_wavg /l_balance,5);
2063
2064 if nvl(x_int_calc_balance,0) <> 0 then
2065 l_int_rate := round(l_wavg /x_int_calc_balance,5);
2066 end if;
2067
2068 IF l_DEBUG in ('Y', 'C') THEN
2069 cep_standard.debug('-------------');
2070 cep_standard.debug('l_int_rate = '|| l_int_rate);
2071 END IF;
2072
2073 else
2074 IF l_DEBUG in ('Y', 'C') THEN
2075 cep_standard.debug('open CR_RANGE');
2076 END IF;
2077
2078 --open CR_RANGE(p_from_date , p_to_date ,
2079 open CR_RANGE(x_balance_date_from, x_balance_date_to ,
2080 p_bank_account_id, p_interest_schedule_id, l_amount);
2081 l_wavg := 0;
2082 l_count := 0;
2083 LOOP
2084 fetch CR_RANGE INTO l_min,l_max,l_rate;
2085 EXIT WHEN CR_RANGE%NOTFOUND;
2086
2087 IF l_DEBUG in ('Y', 'C') THEN
2088 cep_standard.debug('-------------');
2089 cep_standard.debug('l_min = '|| l_min );
2090 cep_standard.debug('l_max = '|| l_max );
2091 cep_standard.debug('l_rate = '|| l_rate );
2092 END IF;
2093
2094 if l_min < 0 then
2095 l_min := 0;
2096 end if;
2097 if l_max > l_amount then
2098 l_max := l_amount;
2099 end if;
2100
2101 IF l_DEBUG in ('Y', 'C') THEN
2102 cep_standard.debug('new l_min = '|| l_min );
2103 cep_standard.debug('new l_max = '|| l_max );
2104
2105 END IF;
2106
2107 l_diff := (((l_amount - l_min) - (l_amount - l_max)) + x_add_min_pre_amt);
2108
2109 IF l_DEBUG in ('Y', 'C') THEN
2110 cep_standard.debug('l_diff = '|| l_diff );
2111 cep_standard.debug('current l_wavg = '|| l_wavg );
2112 cep_standard.debug('current l_count = '|| l_count );
2113 END IF;
2114
2115 l_wavg := l_wavg + (l_diff * l_rate);
2116 l_count := l_count + 1;
2117
2118 IF l_DEBUG in ('Y', 'C') THEN
2119 cep_standard.debug('new l_wavg = '|| l_wavg );
2120 cep_standard.debug('new l_count = '|| l_count );
2121 END IF;
2122
2123 END LOOP;
2124 close CR_RANGE;
2125 --if nvl(l_balance,0) <>0 then
2126 -- l_int_rate := round(l_wavg /l_balance,5);
2127 if nvl(x_int_calc_balance,0) <> 0 then
2128 l_int_rate := round(l_wavg /x_int_calc_balance,5);
2129 end if;
2130
2131 IF l_DEBUG in ('Y', 'C') THEN
2132 cep_standard.debug('-------------');
2133 cep_standard.debug('l_int_rate = '|| l_int_rate);
2134 END IF;
2135
2136 end if;
2137
2138 x_new_interest_rate := l_int_rate;
2139 x_interest_amount := (x_int_calc_balance * l_int_rate/100 * x_days_over_year);
2140
2141 END IF; -- end step
2142 ------------------------------------------------------------------------------
2143 -- round interest amount
2144 IF (nvl(x_interest_rounding, 'R') = 'R') THEN
2145 x_interest_amount_round := round(x_interest_amount, precision) ;
2146 ELSIF (x_interest_rounding = 'T') THEN
2147 x_interest_amount_round := trunc(x_interest_amount, precision) ;
2148 ELSIF (x_interest_rounding = 'U') THEN
2149 x_interest_amount_round := roundup(x_interest_amount, precision) ;
2150 END IF;
2151
2152 IF l_DEBUG in ('Y', 'C') THEN
2153 cep_standard.debug('l_wavg = '|| l_wavg );
2154 cep_standard.debug('x_interest_amount = '|| x_interest_amount );
2155 cep_standard.debug('x_interest_amount_round = '|| x_interest_amount_round );
2156 cep_standard.debug('x_new_interest_rate = '|| x_new_interest_rate );
2157 END IF;
2158
2159 IF (x_basis = 'STEP') THEN
2160 --x_new_interest_rate := (x_interest_amount/(nvl((x_int_calc_balance * x_days_over_year),1) * 100));
2161 update CE_INT_CALC_DETAILS_GT
2162 set interest_amount = x_interest_amount_round,
2163 NUMBER_OF_DAYS = x_days,
2164 interest_rate = x_new_interest_rate,
2165 LAST_UPDATE_DATE = sysdate
2166 where
2167 from_date = x_balance_date_from
2168 --and bank_account_id = p_bank_account_id
2169 and INTEREST_SCHEDULE_ID = p_interest_schedule_id
2170 and interest_calc_detail_id = x_interest_calc_detail_id ;
2171 ELSE
2172 update CE_INT_CALC_DETAILS_GT
2173 set interest_amount = x_interest_amount_round,
2174 NUMBER_OF_DAYS = x_days,
2175 LAST_UPDATE_DATE = sysdate
2176 where
2177 from_date = x_balance_date_from
2178 --and bank_account_id = p_bank_account_id
2179 and INTEREST_SCHEDULE_ID = p_interest_schedule_id
2180 and interest_calc_detail_id = x_interest_calc_detail_id ;
2181 END IF;
2182
2183 END LOOP; --calc_detail_cur
2184 CLOSE calc_detail_cur;
2185
2186 IF l_DEBUG in ('Y', 'C') THEN
2187 cep_standard.debug('<< CE_INTEREST_CALC.calculate_interest');
2188 END IF;
2189
2190 EXCEPTION
2191 when others then
2192 IF l_DEBUG in ('Y', 'C') THEN
2193 cep_standard.debug('EXCEPTION: CE_INTEREST_CALC.calculate_interest');
2194 END IF;
2195 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
2196 FND_MESSAGE.Set_Token('PROCEDURE', 'CE_INTEREST_CALC.calculate_interest');
2197 fnd_msg_pub.add;
2198
2199 END calculate_interest;
2200
2201 /* --------------------------------------------------------------------
2202 | PRIVATE PROCEDURE |
2203 | int_cal_xtr |
2204 | |
2205 | CALLED BY |
2206 | Treasury |
2207 | |
2208 | CALLS |
2209 | |
2210 | DESCRIPTION |
2211 | Procedure used for Interest Calculation by Treasury |
2212 | |
2213 --------------------------------------------------------------------- */
2214 PROCEDURE int_cal_xtr( p_from_date IN date,
2215 p_to_date IN date,
2216 p_bank_account_id IN number,
2217 p_interest_rate IN NUMBER,
2218 p_interest_acct_type IN varchar2,
2219 p_interest_amount OUT NOCOPY number) IS
2220
2221 x_balance_date_from DATE;
2222 x_balance_date_to DATE;
2223 x_bank_account_id NUMBER;
2224 x_int_calc_balance NUMBER;
2225 p_interest_schedule_id NUMBER;
2226 p_cashpool_id NUMBER;
2227 x_days NUMBER;
2228 x_rownum NUMBER;
2229
2230 y_balance_date_from DATE;
2231 y_balance_date_to DATE;
2232 y_bank_account_id NUMBER;
2233 y_int_calc_balance NUMBER;
2234 y_days NUMBER;
2235 y_rownum NUMBER;
2236 p_row_count NUMBER;
2237
2238 precision NUMBER default NULL;
2239 ext_precision NUMBER default NULL;
2240 min_acct_unit NUMBER default NULL;
2241
2242 BEGIN
2243 IF l_DEBUG in ('Y', 'C') THEN
2244 cep_standard.debug('>> CE_INTEREST_CALC.int_cal_xtr');
2245 cep_standard.debug('p_from_date ='||p_from_date || ', p_to_date ='||p_to_date );
2246 cep_standard.debug(' p_bank_account_id ='||p_bank_account_id ||
2247 ', p_interest_acct_type ='||p_interest_acct_type ||
2248 ', p_interest_rate ='||p_interest_rate );
2249
2250 END IF;
2251
2252 p_cashpool_id := null;
2253
2254 IF (p_from_date > p_to_date) THEN
2255 FND_MESSAGE.set_name( 'CE','CE_FROM_GREATER_TO_DATE');
2256 fnd_msg_pub.add;
2257 ELSE
2258 IF (p_interest_acct_type = 'TREASURY') THEN
2259 cep_standard.debug(' TREASURY');
2260 IF (p_bank_account_id is not null) THEN
2261 cep_standard.debug(' p_bank_account_id is not null');
2262 SELECT INTEREST_SCHEDULE_ID
2263 INTO P_INTEREST_SCHEDULE_ID
2264 FROM CE_BANK_ACCOUNTS
2265 WHERE BANK_ACCOUNT_ID = p_bank_account_id;
2266
2267 cep_standard.debug(' p_interest_schedule_id ='||p_interest_schedule_id );
2268
2269 IF (P_INTEREST_SCHEDULE_ID is not null) THEN
2270 delete_schedule_account( p_interest_schedule_id ,
2271 p_bank_account_id,
2272 p_interest_acct_type,
2273 p_cashpool_id
2274 );
2275
2276 -- found available balances
2277 get_balance_info(p_from_date , p_to_date,
2278 p_interest_schedule_id, p_bank_account_id,
2279 p_interest_acct_type, p_cashpool_id, p_row_count );
2280
2281 IF l_DEBUG in ('Y', 'C') THEN
2282 cep_standard.debug('p_row_count = '|| p_row_count );
2283 END IF;
2284
2285 IF (p_row_count > 0) THEN
2286 /*
2287 get_interest_info(p_from_date , p_to_date,
2288 p_interest_schedule_id, p_bank_account_id,
2289 p_interest_acct_type, p_cashpool_id, p_row_count );
2290 */
2291 set_int_rate(p_from_date , p_to_date,
2292 p_interest_schedule_id, p_bank_account_id,
2293 p_interest_acct_type, p_interest_rate );
2294
2295 set_end_date(p_from_date , p_to_date,
2296 p_interest_schedule_id, p_bank_account_id,
2297 p_interest_acct_type, p_cashpool_id );
2298
2299 calculate_interest(p_from_date , p_to_date,
2300 p_interest_schedule_id, p_bank_account_id,
2301 p_interest_acct_type, p_cashpool_id );
2302
2303 select sum(INTEREST_AMOUNT)
2304 into p_interest_amount
2305 from CE_INT_CALC_DETAILS_GT
2306 where INTEREST_SCHEDULE_ID = p_interest_schedule_id
2307 and BANK_ACCOUNT_ID = p_bank_account_id
2308 and INTEREST_ACCT_TYPE = p_interest_acct_type
2309 and FROM_DATE >= p_from_date
2310 and TO_DATE <= p_to_date
2311 and CASHPOOL_ID is null;
2312
2313 IF l_DEBUG in ('Y', 'C') THEN
2314 cep_standard.debug(' p_interest_amount ='||p_interest_amount );
2315 END IF;
2316
2317 END IF;
2318 ELSE
2319 FND_MESSAGE.set_name( 'CE','CE_NO_SCHED_BANK_ACCT');
2320 fnd_msg_pub.add;
2321 END IF;
2322 ELSE
2323 FND_MESSAGE.set_name( 'CE','CE_MISSING_BANK_ACCT_ID');
2324 fnd_msg_pub.add;
2325 END IF;
2326 ELSE
2327 FND_MESSAGE.set_name( 'CE','CE_INVALID_INT_ACCT_TYPE');
2328 FND_MESSAGE.Set_Token('INTEREST_ACCT_TYPE', p_interest_acct_type);
2329 fnd_msg_pub.add;
2330 END IF;
2331
2332 END IF;
2333 IF l_DEBUG in ('Y', 'C') THEN
2334 cep_standard.debug('<< CE_INTEREST_CALC.int_cal_xtr');
2335 END IF;
2336
2337 EXCEPTION
2338 when others then
2339 IF l_DEBUG in ('Y', 'C') THEN
2340 cep_standard.debug('EXCEPTION: CE_INTEREST_CALC.int_cal_xtr');
2341 END IF;
2342 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
2343 FND_MESSAGE.Set_Token('PROCEDURE', 'CE_INTEREST_CALC.int_cal_xtr');
2344 fnd_msg_pub.add;
2345
2346 END int_cal_xtr;
2347
2348
2349 /* --------------------------------------------------------------------
2350 | PRIVATE PROCEDURE |
2351 | int_cal_detail_main |
2352 | |
2353 | CALLED BY |
2354 | InterestAMImpl.java (InterestCalculateCO) |
2355 | |
2356 | CALLS |
2357 | get_interest_info |
2358 | set_end_date |
2359 | calculate_interest |
2360 | |
2361 | DESCRIPTION |
2362 | Main procedure use for Interest Calculation |
2363 | |
2364 --------------------------------------------------------------------- */
2365 PROCEDURE int_cal_detail_main( p_from_date date,
2366 p_to_date date,
2367 p_interest_schedule_id number,
2368 p_bank_account_id number,
2369 p_interest_acct_type varchar2,
2370 p_cashpool_id number) IS
2371 x_balance_date_from DATE;
2372 x_balance_date_to DATE;
2373 x_bank_account_id NUMBER;
2374 x_int_calc_balance NUMBER;
2375
2376 x_days NUMBER;
2377 x_rownum NUMBER;
2378
2379 y_balance_date_from DATE;
2380 y_balance_date_to DATE;
2381 y_bank_account_id NUMBER;
2382 y_int_calc_balance NUMBER;
2383 y_days NUMBER;
2384 y_rownum NUMBER;
2385 p_row_count NUMBER;
2386 p_num_of_range NUMBER;
2387 precision NUMBER default NULL;
2388 ext_precision NUMBER default NULL;
2389 min_acct_unit NUMBER default NULL;
2390
2391 BEGIN
2392 IF l_DEBUG in ('Y', 'C') THEN
2393 cep_standard.debug('>> CE_INTEREST_CALC.int_cal_detail_main');
2394 cep_standard.debug('p_from_date = '||p_from_date||', p_to_date = '||p_to_date);
2395 cep_standard.debug('p_interest_schedule_id ='|| p_interest_schedule_id ||
2396 ', p_bank_account_id ='||p_bank_account_id ||
2397 ', p_interest_acct_type ='||p_interest_acct_type ||
2398 ', p_cashpool_id ='||p_cashpool_id );
2399
2400 END IF;
2401
2402 IF (p_from_date > p_to_date) THEN
2403 FND_MESSAGE.set_name( 'CE','CE_FROM_GREATER_TO_DATE');
2404 fnd_msg_pub.add;
2405 ELSE
2406 IF (p_interest_acct_type = 'BANK_ACCOUNT') THEN
2407
2408 delete_schedule_account( p_interest_schedule_id ,
2409 p_bank_account_id,
2410 p_interest_acct_type,
2411 p_cashpool_id
2412 );
2413
2414 -- found available balances
2415 get_balance_info(p_from_date , p_to_date,
2416 p_interest_schedule_id, p_bank_account_id,
2417 p_interest_acct_type, p_cashpool_id, p_row_count );
2418
2419 IF (p_row_count > 0) THEN
2420 get_interest_info(p_from_date , p_to_date,
2421 p_interest_schedule_id, p_bank_account_id,
2422 p_interest_acct_type, p_cashpool_id, p_row_count );
2423
2424 set_end_date(p_from_date , p_to_date,
2425 p_interest_schedule_id, p_bank_account_id,
2426 p_interest_acct_type, p_cashpool_id );
2427
2428 calculate_interest(p_from_date , p_to_date,
2429 p_interest_schedule_id, p_bank_account_id,
2430 p_interest_acct_type, p_cashpool_id );
2431
2432 END IF;
2433 ELSIF (p_interest_acct_type = 'NOTIONAL') THEN
2434 delete_schedule_account( p_interest_schedule_id ,
2435 x_bank_account_id,
2436 p_interest_acct_type,
2437 p_cashpool_id
2438 );
2439
2440 -- do not call cashpool_accts_cur
2441 -- ce_bal_util.get_pool_balance(p_cashpool_id, p_from_date) will get total
2442 -- balances for cashpool accounts (includes: ('CONC', 'ACCT', 'NEST'))
2443 --OPEN cashpool_accts_cur (p_cashpool_id );
2444 --LOOP
2445 --FETCH cashpool_accts_cur INTO x_bank_account_id;
2446 --EXIT WHEN cashpool_accts_cur%NOTFOUND OR cashpool_accts_cur%NOTFOUND IS NULL;
2447
2448 --IF l_DEBUG in ('Y', 'C') THEN
2449 -- cep_standard.debug('x_bank_account_id = '|| x_bank_account_id );
2450 --END IF;
2451
2452 -- found available balances for each cashpool
2453 get_balance_pool_info(p_from_date , p_to_date,
2454 p_interest_schedule_id, p_bank_account_id,
2455 p_interest_acct_type, p_cashpool_id, p_row_count );
2456
2457 IF l_DEBUG in ('Y', 'C') THEN
2458 cep_standard.debug('p_row_count = '|| p_row_count );
2459 END IF;
2460 IF (p_row_count > 0) THEN
2461
2462 set_range_and_rate(p_from_date , p_to_date,
2463 p_interest_schedule_id, p_bank_account_id,
2464 p_interest_acct_type, p_cashpool_id);
2465
2466 get_interest_pool_info(p_from_date , p_to_date,
2467 p_interest_schedule_id, p_bank_account_id,
2468 p_interest_acct_type, p_cashpool_id, p_row_count );
2469
2470
2471 set_end_date(p_from_date , p_to_date,
2472 p_interest_schedule_id, p_bank_account_id,
2473 p_interest_acct_type, p_cashpool_id );
2474
2475 calculate_interest(p_from_date , p_to_date,
2476 p_interest_schedule_id, p_bank_account_id,
2477 p_interest_acct_type, p_cashpool_id );
2478 END IF;
2479 --END LOOP; --cashpool_accts_cur
2480 /* set_end_date(p_from_date , p_to_date,
2481 p_interest_schedule_id, p_bank_account_id,
2482 p_interest_acct_type, p_cashpool_id );
2483
2484 calculate_interest(p_from_date , p_to_date,
2485 p_interest_schedule_id, p_bank_account_id,
2486 p_interest_acct_type, p_cashpool_id ); */
2487
2488 ELSE
2489 FND_MESSAGE.set_name( 'CE','CE_INVALID_INT_ACCT_TYPE');
2490 FND_MESSAGE.Set_Token('INTEREST_ACCT_TYPE', p_interest_acct_type);
2491 fnd_msg_pub.add;
2492 END IF;
2493 --commit;
2494
2495 --bug 5479708, removed last date range
2496 if (p_cashpool_id is null) THEN
2497 select count(*) into p_num_of_range
2498 from CE_INT_CALC_DETAILS_GT
2499 where
2500 INTEREST_SCHEDULE_ID = p_interest_schedule_id
2501 and BANK_ACCOUNT_ID = p_bank_account_id
2502 and INTEREST_ACCT_TYPE = p_interest_acct_type
2503 and CASHPOOL_ID is null;
2504
2505 IF (p_num_of_range > 1) THEN
2506 delete CE_INT_CALC_DETAILS_GT
2507 where
2508 INTEREST_SCHEDULE_ID = p_interest_schedule_id
2509 and BANK_ACCOUNT_ID = p_bank_account_id
2510 and INTEREST_ACCT_TYPE = p_interest_acct_type
2511 and CASHPOOL_ID is null
2512 and FROM_DATE = p_to_date ;
2513
2514 END IF;
2515
2516 else -- p_cashpool_id is not null
2517 select count(*) into p_num_of_range
2518 from CE_INT_CALC_DETAILS_GT
2519 where INTEREST_SCHEDULE_ID = p_interest_schedule_id
2520 --and BANK_ACCOUNT_ID = p_bank_account_id
2521 and INTEREST_ACCT_TYPE = p_interest_acct_type
2522 and CASHPOOL_ID = p_cashpool_id;
2523
2524 IF (p_num_of_range > 1) THEN
2525 delete CE_INT_CALC_DETAILS_GT
2526 where INTEREST_SCHEDULE_ID = p_interest_schedule_id
2527 --and BANK_ACCOUNT_ID = p_bank_account_id
2528 and INTEREST_ACCT_TYPE = p_interest_acct_type
2529 and CASHPOOL_ID = p_cashpool_id
2530 and FROM_DATE = p_to_date ;
2531 END IF;
2532 end if; --p_cashpool_id is null
2533
2534 IF l_DEBUG in ('Y', 'C') THEN
2535 cep_standard.debug('p_num_of_range '|| p_num_of_range );
2536 END IF;
2537
2538 END IF;
2539 IF l_DEBUG in ('Y', 'C') THEN
2540 cep_standard.debug('<< CE_INTEREST_CALC.int_cal_detail_main');
2541 END IF;
2542
2543 EXCEPTION
2544 when others then
2545 IF l_DEBUG in ('Y', 'C') THEN
2546 cep_standard.debug('EXCEPTION: CE_INTEREST_CALC.int_cal_detail_main');
2547 END IF;
2548 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
2549 FND_MESSAGE.Set_Token('PROCEDURE', 'CE_INTEREST_CALC.int_cal_detail_main');
2550 fnd_msg_pub.add;
2551
2552 END int_cal_detail_main;
2553
2554 END CE_INTEREST_CALC;