[Home] [Help]
PACKAGE BODY: APPS.XTR_ACCOUNT_BAL_MAINT_P
Source
1 PACKAGE BODY XTR_ACCOUNT_BAL_MAINT_P as
2 /* $Header: xtracctb.pls 120.15 2006/07/18 08:18:57 csutaria ship $ */
3 -----------------------------------------------------------------------------------------------------------------------------------------
4 PROCEDURE FIND_INT_RATE(l_acct IN VARCHAR2,
5 l_balance IN NUMBER,
6 l_party_code IN VARCHAR2,
7 l_bank_code IN VARCHAR2,
8 l_currency IN VARCHAR2,
9 l_balance_date IN DATE,
10 l_int_rate IN OUT NOCOPY NUMBER) is
11 --
12 l_basis VARCHAR2(4);
13 l_setoff_bank VARCHAR2(30);
14 l_setoff_party VARCHAR2(30);
15 l_count NUMBER;
16 l_diff NUMBER;
17 l_amount NUMBER;
18 l_min NUMBER;
19 l_max NUMBER;
20 l_rate NUMBER;
21 l_wavg NUMBER;
22 --
23 /*
24 -- AW 5/28/99 Extract the Bank Code of the Nominal Account Number (l_acct)
25 -- For example, if l_acct = 'CITI-AAA', then SETOFF_BANK_CODE will return 'AAA'.
26 -- if l_acct = 'CITI', then SETOFF_BANK_CODE will return 'CITI'.
27 cursor SETOFF_BANK_CODE is
28 select substr(L_ACCT,instr(L_ACCT,'-')+1)
29 from dual;
30 */
31
32 cursor RATE_BASIS is
33 select nvl(INTEREST_CALCULATION_BASIS,'STEP')
34 from XTR_BANK_ACCOUNTS
35 where ACCOUNT_NUMBER = l_acct
36 and PARTY_CODE = L_PARTY_CODE
37 and CURRENCY = L_CURRENCY;
38 --
39 cursor FLAT_RATE is
40 select INTEREST_RATE
41 from XTR_INTEREST_RATE_RANGES
42 where REF_CODE = l_acct
43 and PARTY_CODE = l_bank_code
44 -- and PARTY_CODE = l_setoff_party
45 and CURRENCY = L_CURRENCY
46 and MIN_AMT < l_balance
47 and MAX_AMT >= l_balance
48 and EFFECTIVE_FROM_DATE =(select max(EFFECTIVE_FROM_DATE)
49 from XTR_INTEREST_RATE_RANGES
50 where REF_CODE = l_acct
51 and PARTY_CODE = l_bank_code
52 --and PARTY_CODE = l_setoff_party
53 and CURRENCY = L_CURRENCY
54 and MIN_AMT < l_balance
55 and MAX_AMT >= l_balance
56 and EFFECTIVE_FROM_DATE<= L_BALANCE_DATE);
57 --
58 cursor DR_RANGE is
59 select MIN_AMT,MAX_AMT,nvl(INTEREST_RATE,0)
60 from XTR_INTEREST_RATE_RANGES
61 where REF_CODE = l_acct
62 and PARTY_CODE = l_bank_code
63 -- and PARTY_CODE = l_setoff_party
64 and CURRENCY = L_CURRENCY
65 and MAX_AMT >= l_amount
66 and MIN_AMT <0
67 and EFFECTIVE_FROM_DATE =(select max(EFFECTIVE_FROM_DATE)
68 from XTR_INTEREST_RATE_RANGES
69 where REF_CODE = l_acct
70 and PARTY_CODE = l_bank_code
71 --and PARTY_CODE = l_setoff_party
72 and CURRENCY = L_CURRENCY
73 and MAX_AMT >= l_amount
74 and MIN_AMT <0
75 and EFFECTIVE_FROM_DATE<= L_BALANCE_DATE)
76 order by MAX_AMT desc;
77 --
78
79 cursor CR_RANGE is
80 select MIN_AMT,MAX_AMT,nvl(INTEREST_RATE,0)
81 from XTR_INTEREST_RATE_RANGES
82 where REF_CODE = l_acct
83 and PARTY_CODE = l_bank_code
84 -- and PARTY_CODE = l_setoff_party
85 and CURRENCY = L_CURRENCY
86 and MIN_AMT <= l_amount
87 and MAX_AMT >= 0
88 and EFFECTIVE_FROM_DATE =(select max(EFFECTIVE_FROM_DATE)
89 from XTR_INTEREST_RATE_RANGES
90 where REF_CODE = l_acct
91 and PARTY_CODE = l_bank_code
92 -- and PARTY_CODE = l_setoff_party
93 and CURRENCY = L_CURRENCY
94 and MIN_AMT <= l_amount
95 and MAX_AMT >= 0
96 and EFFECTIVE_FROM_DATE<= L_BALANCE_DATE)
97 order by MIN_AMT desc;
98
99 --
100 begin
101 l_int_rate := 0;
102 --
103 open RATE_BASIS;
104 fetch RATE_BASIS INTO l_basis;
105 close RATE_BASIS;
106
107 if nvl(l_basis,'FLAT') = 'STEP' then
108 l_amount := l_balance;
109 if l_amount <= 0 then
110 open DR_RANGE;
111 l_wavg := 0;
112 l_count := 0;
113 LOOP
114 fetch DR_RANGE INTO l_min,l_max,l_rate;
115 EXIT WHEN DR_RANGE%NOTFOUND;
116 if l_max > 0 then
117 l_max := 0;
118 end if;
119 if l_min < l_amount then
120 l_min := l_amount;
121 end if;
122 l_diff := (l_amount - l_max) - (l_amount - l_min);
123
124 l_wavg := l_wavg + (l_diff * l_rate);
125 l_count := l_count + 1;
126 END LOOP;
127 close DR_RANGE;
128 if nvl(l_balance,0) <>0 then
129 l_int_rate := round(l_wavg /l_balance,5);
130 end if;
131 else
132 open CR_RANGE;
133 l_wavg := 0;
134 l_count := 0;
135 LOOP
136 fetch CR_RANGE INTO l_min,l_max,l_rate;
137 EXIT WHEN CR_RANGE%NOTFOUND;
138 if l_min < 0 then
139 l_min := 0;
140 end if;
141 if l_max > l_amount then
142 l_max := l_amount;
143 end if;
144 l_diff := (l_amount - l_min) - (l_amount - l_max);
145
146 l_wavg := l_wavg + (l_diff * l_rate);
147 l_count := l_count + 1;
148 END LOOP;
149 close CR_RANGE;
150 if nvl(l_balance,0) <>0 then
151 l_int_rate := round(l_wavg /l_balance,5);
152 end if;
153 end if;
154 else
155 open FLAT_RATE;
156 fetch FLAT_RATE INTO l_int_rate;
157 if FLAT_RATE%NOTFOUND then
158 l_int_rate := NULL;
159 if l_amount <= 0 then
160 open DR_RANGE;
161 fetch DR_RANGE INTO l_min,l_max,l_int_rate;
162 close DR_RANGE;
163 else
164 open CR_RANGE;
165 fetch CR_RANGE INTO l_min,l_max,l_int_rate;
166 close CR_RANGE;
167 end if;
168 end if;
169 close FLAT_RATE;
170 end if;
171 -- Check that interest rate was found if not set to 0 and disp a warning
172 if l_int_rate is NULL then
173 l_int_rate := 0;
174 end if;
175 end;
176 ---
177 ------------------------------------------------------------------------------------------------------------------
178 PROCEDURE UPLOAD_ACCTS is
179 /*
180 Procedure to upload bank balances from the BANK_BAL_INTERFACE table (called from form PRO1080)
181
182 Note this table needs to be populated by a script in PRO1080 that reads the balances from a flat file
183 (produced by the MTS system) and then populates the BANK_BAL_INTERFACE table).
184
185 It then calls a procedure MAINTAIN_SETOFFS to maintain setoff accounts
186 */
187 --
188 l_dummy VARCHAR2(1);
189 l_comp VARCHAR2(7);
190 l_ccy VARCHAR2(15);
191 acct_no VARCHAR2(20);
192 new_company VARCHAR2(7);
193 new_date DATE;
194 new_bal NUMBER;
195 new_bal_hce NUMBER;
196 v_cross_ref XTR_PARTY_INFO.cross_ref_to_other_party%TYPE;
197 v_dummy_num NUMBER;
198 int_rate NUMBER;
199 roundfac NUMBER;
200 yr_basis NUMBER;
201 l_amount_adj NUMBER;
202 l_amount_cflow NUMBER;
203 l_no_days NUMBER;
204 l_setoff_recalc_date DATE;
205 l_prv_date DATE;
206 l_prv_rate NUMBER;
207 l_prv_bal NUMBER;
208 l_int_bf NUMBER;
209 l_int_cf NUMBER;
210 l_interest NUMBER;
211 l_new_rate NUMBER;
212 l_hc_rate NUMBER;
213 l_yr_type VARCHAR2(20);
214 --add
215 l_limit_code varchar2(7);
216 l_portfolio_code varchar2(7);
217 l_bank_code varchar2(7);
218 --
219 l_prv_accrual_int NUMBER;
220 l_accrual_int NUMBER;
221 -- Added for Interest Override
222 l_rounding_type VARCHAR2(1);
223 l_day_count_type VARCHAR2(1);
224 l_prv_rounding_type VARCHAR2(1);
225 l_prv_day_count_type VARCHAR2(1);
226 l_oldest_date DATE;
227 l_first_trans_flag varchar2(1);
228 l_original_amount NUMBER;
229 l_prv_prv_day_count_type VARCHAR2(1);
230 l_one_day_float NUMBER;
231 l_two_day_float NUMBER;
232 --
233 cursor RNDING is
234 select ROUNDING_FACTOR,YEAR_BASIS,HCE_RATE
235 from XTR_MASTER_CURRENCIES_V
236 where CURRENCY = l_ccy;
237 --
238 cursor ACCT_DETAILS is
239 select PARTY_CODE,CURRENCY,PORTFOLIO_CODE,BANK_CODE,nvl(YEAR_CALC_TYPE,'ACTUAL/ACTUAL') year_calc_type,
240 rounding_type, day_count_type
241 from XTR_BANK_ACCOUNTS
242 where ACCOUNT_NUMBER = acct_no
243 and PARTY_CODE = new_company;
244 --
245 cursor NEW_BALANCE is
246 select rtrim(ACCOUNT_NO,' '),trunc(BALANCE_DATE), AMOUNT, AMOUNT_ADJ, AMOUNT_CFLOW, COMPANY_CODE, ONE_DAY_FLOAT, TWO_DAY_FLOAT
247 from XTR_BANK_BAL_INTERFACE
248 where TRANSFER_SUCCEEDED is null;
249 --
250 cursor PREV_DETAILS is
251 select a.BALANCE_DATE,a.BALANCE_CFLOW,a.ACCUM_INT_CFWD,a.INTEREST_RATE,a.accrual_interest,
252 a.rounding_type, day_count_type
253 from XTR_BANK_BALANCES a
254 where a.ACCOUNT_NUMBER = acct_no
255 and a.COMPANY_CODE = new_company
256 and a.BALANCE_DATE = (select max(b.BALANCE_DATE)
257 from XTR_BANK_BALANCES b
258 where b.ACCOUNT_NUMBER = acct_no
259 and b.COMPANY_CODE = new_company);
260 --
261 cursor CHK_EXISTING_DATE is
262 select 'x'
263 from XTR_BANK_BALANCES
264 where ACCOUNT_NUMBER = acct_no
265 and COMPANY_CODE = new_company
266 and BALANCE_DATE = new_date;
267 --
268 cursor GET_LIM_CODE_BAL is
269 select LIMIT_CODE
270 from XTR_BANK_BALANCES
271 where ACCOUNT_NUMBER = acct_no
272 and COMPANY_CODE = new_company
273 and BALANCE_DATE < new_date
274 and ((new_bal >= 0 and BALANCE_CFLOW >= 0)
275 or (new_bal <= 0 and BALANCE_CFLOW <= 0))
276 order by BALANCE_DATE;
277 --
278 cursor GET_LIM_CODE_CPARTY is
279 select cl.LIMIT_CODE
280 from XTR_COUNTERPARTY_LIMITS cl, XTR_LIMIT_TYPES lt
281 where cl.COMPANY_CODE = new_company
282 and cl.CPARTY_CODE = l_bank_code
283 and cl.LIMIT_TYPE = lt.LIMIT_TYPE
284 and ((new_bal >= 0 and lt.FX_INVEST_FUND_TYPE = 'I')
285 or (new_bal <= 0 and lt.FX_INVEST_FUND_TYPE = 'OD'));
286 --
287 cursor CROSS_REF is
288 select CROSS_REF_TO_OTHER_PARTY
289 from XTR_PARTIES_V
290 where PARTY_CODE = l_comp;
291 -- Added for Interest Override
292 CURSOR oldest_date IS
293 SELECT MIN(a.balance_date)
294 FROM xtr_bank_balances a
295 WHERE a.account_number = acct_no
296 AND a.COMPANY_CODE = new_company;
297
298 CURSOR PRV_PRV_DETAILS IS
299 SELECT a.day_count_type
300 FROM xtr_bank_balances a
301 WHERE a.account_number = acct_no
302 AND a.COMPANY_CODE = new_company
303 AND a.balance_date = (select max(b.BALANCE_DATE)
304 from XTR_BANK_BALANCES b
305 where b.ACCOUNT_NUMBER = acct_no
306 and b.COMPANY_CODE = new_company
307 AND b.balance_date < l_prv_date);
308 --
309 begin
310 open NEW_BALANCE;
311 fetch NEW_BALANCE INTO acct_no, new_date, new_bal, l_amount_adj, l_amount_cflow, new_company, l_one_day_float, l_two_day_float;
312 WHILE NEW_BALANCE%FOUND LOOP
313 open PREV_DETAILS;
314 fetch PREV_DETAILS INTO l_prv_date,l_prv_bal,l_int_bf,l_prv_rate,l_prv_accrual_int,
315 l_prv_rounding_type, l_prv_day_count_type; -- Added for Interest Override
316 if PREV_DETAILS%NOTFOUND then
317 l_prv_date := trunc(new_date);
318 l_prv_bal := 0;
319 l_prv_rate := 0;
320 l_int_bf := 0;
321 l_no_days := 0;
322 l_prv_accrual_int := 0;
323 l_prv_rounding_type := NULL;
324 l_prv_day_count_type := NULL;
325 end if;
326 close PREV_DETAILS;
327 open ACCT_DETAILS;
328 fetch ACCT_DETAILS INTO l_comp,l_ccy,l_portfolio_code,l_bank_code,l_yr_type,
329 l_rounding_type, l_day_count_type; -- Added for Interest Override
330 if ACCT_DETAILS%FOUND then -- Account is loaded in the system
331 close ACCT_DETAILS;
332 open RNDING;
333 fetch RNDING INTO roundfac,yr_basis,l_hc_rate;
334 close RNDING;
335 open GET_LIM_CODE_BAL;
336 fetch GET_LIM_CODE_BAL INTO l_limit_code;
337 if GET_LIM_CODE_BAL%NOTFOUND or l_limit_code IS NULL then
338 open GET_LIM_CODE_CPARTY;
339 fetch GET_LIM_CODE_CPARTY INTO l_limit_code;
340 if GET_LIM_CODE_CPARTY%NOTFOUND then
341 l_limit_code := Null;
342 end if;
343 close GET_LIM_CODE_CPARTY;
344 end if;
345 close GET_LIM_CODE_BAL;
346 ---- l_no_days := (trunc(new_date) - trunc(l_prv_date));
347 -- Added for Interest Override
348 OPEN oldest_date;
349 FETCH oldest_date INTO l_oldest_date;
350 close oldest_date;
351 --
352 if trunc(l_prv_date) < trunc(new_date) then
353 -- Added for Interest Override
354 OPEN prv_prv_details;
355 FETCH prv_prv_details INTO l_prv_prv_day_count_type;
356 CLOSE prv_prv_details;
357 IF (l_prv_day_count_type ='B' AND l_prv_date = l_oldest_date)
358 OR (l_prv_prv_day_count_type ='F' AND l_prv_day_count_type ='B' ) THEN
362 END IF;
359 l_first_trans_flag :='Y';
360 ELSE
361 l_first_trans_flag :=NULL;
363 --
364 XTR_CALC_P.CALC_DAYS_RUN(trunc(l_prv_date),
365 trunc(new_date),
366 l_yr_type,
367 l_no_days,
368 yr_basis,
369 NULL,
370 l_prv_day_count_type, -- Added for Interest Override
371 l_first_trans_flag); -- Added for Interest Overrdie
372
373 -- Added for Interest Override
374 IF l_prv_date <> l_oldest_date AND
375 ((Nvl(l_prv_prv_day_count_type,l_prv_day_count_type) ='L' AND l_prv_day_count_type ='F')
376 OR (Nvl(l_prv_prv_day_count_type,l_prv_day_count_type) ='B' AND l_prv_day_count_type ='F'))
377 THEN
378 l_no_days := l_no_days -1;
379 END IF;
380 --
381 else
382 l_no_days :=0;
383 yr_basis :=365;
384 end if;
385
386 -- Changed for Interest Override
387 -- l_interest := round(l_prv_bal * l_prv_rate / 100 * l_no_days
388 -- / yr_basis,roundfac);
389 l_interest := xtr_fps2_p.interest_round(l_prv_bal * l_prv_rate / 100 * l_no_days
390 / yr_basis,roundfac,l_prv_rounding_type);
391 -- l_int_cf := l_int_bf + l_interest;
392 l_original_amount := l_int_bf + l_interest;
393 l_int_cf := l_original_amount;
394 --
395 l_accrual_int :=nvl(l_prv_accrual_int,0) + nvl(l_interest,0);
396
397 XTR_ACCOUNT_BAL_MAINT_P.FIND_INT_RATE(acct_no, new_bal,
398 new_company,l_bank_code,l_ccy,new_date,l_new_rate);
399 if l_new_rate is null then
400 l_new_rate := 0;
401 end if;
402 open CHK_EXISTING_DATE;
403 fetch CHK_EXISTING_DATE INTO l_dummy;
404 if CHK_EXISTING_DATE%NOTFOUND then
405 close CHK_EXISTING_DATE;
406 -- the uploaded date is the latest date then ok to insert
407 insert into XTR_BANK_BALANCES
408 (company_code,account_number,balance_date,no_of_days,
409 statement_balance,balance_adjustment,balance_cflow,
410 accum_int_bfwd,interest,interest_rate,interest_settled,
411 interest_settled_hce,accum_int_cfwd,limit_code,
412 created_on,created_by,accrual_interest,
413 rounding_type, day_count_type, original_amount, one_day_float, two_day_float) -- Added for Interest Override
414 values
415 (l_comp,acct_no,new_date,l_no_days,new_bal, nvl(l_amount_adj, 0), nvl(l_amount_cflow, new_bal),
416 l_int_bf,l_interest,l_new_rate,0,0,l_int_cf,l_limit_code,
417 sysdate, fnd_global.user_id,l_accrual_int,
418 l_rounding_type, l_day_count_type, l_original_amount, l_one_day_float, l_two_day_float); -- Added for Interest Override
419
420 new_bal_hce := round(new_bal / l_hc_rate,roundfac);
421 --
422 update XTR_BANK_BAL_INTERFACE
423 set TRANSFER_SUCCEEDED = 'Y'
424 where ACCOUNT_NO = acct_no
425 and COMPANY_CODE = new_company
426 and BALANCE_DATE = new_date;
427 --
428 open CROSS_REF;
429 fetch CROSS_REF INTO v_cross_ref;
430 close CROSS_REF;
431 XTR_ACCOUNT_BAL_MAINT_P.UPDATE_BANK_ACCTS(acct_no,
432 l_ccy,
433 l_bank_code,
434 l_portfolio_code,
435 v_cross_ref,
436 l_comp,
437 new_date,
438 v_dummy_num
439 );
440 else
441 close CHK_EXISTING_DATE;
442 --
443 update XTR_BANK_BAL_INTERFACE
444 set TRANSFER_SUCCEEDED = 'N'
445 where ACCOUNT_NO = acct_no
446 and COMPANY_CODE = new_company
447 and BALANCE_DATE = new_date;
448 end if;
449 else
450 update XTR_BANK_BAL_INTERFACE
451 set TRANSFER_SUCCEEDED = 'N'
452 where ACCOUNT_NO = acct_no
453 and COMPANY_CODE = new_company
454 and BALANCE_DATE = new_date;
455
456 close ACCT_DETAILS;
457 end if;
458 fetch NEW_BALANCE INTO acct_no,new_date,new_bal, l_amount_adj, l_amount_cflow, new_company, l_one_day_float, l_two_day_float;
459 END LOOP;
460 close NEW_BALANCE;
461 commit;
462 --
463 end UPLOAD_ACCTS;
464 ------------------------------------------------------------------------------------------------------------------
465 PROCEDURE upload_accts_program(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY NUMBER) IS
466 BEGIN
467 XTR_ACCOUNT_BAL_MAINT_P.upload_accts;
468 END upload_accts_program;
469 ------------------------------------------------------------------------------------------------------------------
470 PROCEDURE MAINTAIN_SETOFFS(
471 p_party_code IN VARCHAR2,
472 p_cashpool_id IN CE_CASHPOOLS.CASHPOOL_ID%TYPE,
473 p_conc_acct_id IN CE_BANK_ACCOUNTS.BANK_ACCOUNT_ID%TYPE,
474 p_calc_date IN DATE) is
475 --
476 -- This procedure calculates the setoff account balances
477 -- after the latest balances are uploaded from the bank_bal table
478 -- ie called from the bank_bal.sql script or a Bank Balance is updated in Maintain
479 -- bank balance form
480 --
481 l_calc_date DATE;
482 roundfac NUMBER;
483 yr_basis NUMBER;
484 l_ccy VARCHAR(15);
485 l_setoff VARCHAR(5);
486 l_setoff_company VARCHAR(7);
487 l_bank_code VARCHAR(7);
488 l_no_days NUMBER;
489 l_prv_date DATE;
490 l_this_rate NUMBER;
491 l_prv_rate NUMBER;
495 l_prv_cflw NUMBER;
492 l_rate NUMBER;
493 l_prv_bal NUMBER;
494 l_prv_adj NUMBER;
496 l_int_bf NUMBER;
497 l_int_cf NUMBER;
498 l_int_set NUMBER;
499 l_interest NUMBER;
500 l_this_bal NUMBER;
501 l_this_adj NUMBER;
502 l_this_cflw NUMBER;
503 l_this_accrual_int NUMBER;
504 l_prv_accrual_int NUMBER;
505 l_accrual_int NUMBER;
506 l_yr_type VARCHAR2(20);
507
508 l_account_id CE_BANK_ACCOUNTS.BANK_ACCOUNT_ID%TYPE;
509
510 l_account_number XTR_BANK_ACCOUNTS.ACCOUNT_NUMBER%TYPE;
511 l_party_code XTR_BANK_ACCOUNTS.PARTY_CODE%TYPE;
512 l_notl_bank_code XTR_BANK_ACCOUNTS.BANK_CODE%TYPE;
513 l_notl_currency XTR_BANK_ACCOUNTS.CURRENCY%TYPE;
514 l_notl_year_calc_type XTR_BANK_ACCOUNTS.YEAR_CALC_TYPE%TYPE;
515 l_notl_rounding_type XTR_BANK_ACCOUNTS.ROUNDING_TYPE%TYPE;
516 l_notl_day_count_type XTR_BANK_ACCOUNTS.DAY_COUNT_TYPE%TYPE;
517
518 --
519
520 cursor SEL_SETOFF_ACCT is
521 SELECT account_id acct_id
522 FROM ce_cashpool_sub_accts
523 WHERE cashpool_id = p_cashpool_id
524 AND type in ('CONC','ACCT')
525 UNION
526 SELECT nvl(conc_account_id, inv_conc_account_id) acct_id
527 FROM ce_cashpool_sub_accts
528 WHERE cashpool_id = p_cashpool_id
529 AND type = 'POOL'
530 AND nvl(conc_account_id, inv_conc_account_id) is not null
531 UNION
532 SELECT fund_conc_account_id acct_id
533 FROM ce_cashpool_sub_accts
534 WHERE cashpool_id = p_cashpool_id
535 AND type = 'POOL'
536 AND fund_conc_account_id is not null;
537
538 cursor NOTIONAL_ACCT_NO IS
539 SELECT account_number, party_code, bank_code,
540 currency, nvl(year_calc_type, 'ACTUAL/ACTUAL'),
541 rounding_type, day_count_type
542 FROM XTR_BANK_ACCOUNTS
543 WHERE cashpool_id = p_cashpool_id;
544
545 cursor RNDING is
546 select ROUNDING_FACTOR,YEAR_BASIS
547 from XTR_MASTER_CURRENCIES_V
548 where CURRENCY = l_notl_currency;
549 --
550 -- R12 modified the Cursor. Removed the reference to AP_BANK_ACCOUNT_ID and Dummy_bank_account_id
551 cursor SETOFF_CAL_DATE IS
552 SELECT distinct balance_date
553 FROM ce_bank_acct_balances --4696629
554 WHERE balance_date >= p_calc_date
555 AND bank_account_id IN
556 (SELECT account_id acct_id
557 FROM ce_cashpool_sub_accts
558 WHERE cashpool_id = p_cashpool_id
559 AND type in ('CONC','ACCT')
560 UNION
561 SELECT nvl(conc_account_id, inv_conc_account_id) acct_id
562 FROM ce_cashpool_sub_accts
563 WHERE cashpool_id = p_cashpool_id
564 AND type = 'POOL'
565 AND nvl(conc_account_id, inv_conc_account_id) is not null
566 UNION
567 SELECT fund_conc_account_id acct_id
568 FROM ce_cashpool_sub_accts
569 WHERE cashpool_id = p_cashpool_id
570 AND type = 'POOL'
571 AND fund_conc_account_id is not null)
572 order by balance_date asc;
573
574 --
575 cursor SETOFF_PREV_DETAILS is
576 select a.BALANCE_DATE,
577 nvl(sum(a.STATEMENT_BALANCE),0),
578 nvl(sum(a.BALANCE_ADJUSTMENT),0),
579 nvl(sum(a.BALANCE_CFLOW),0),
580 nvl(sum(a.ACCUM_INT_CFWD),0),
581 nvl(sum(a.INTEREST_RATE),0),
582 nvl(sum(a.ACCRUAL_INTEREST),0)
583 from XTR_BANK_BALANCES a
584 --where ACCOUNT_NUMBER = l_setoff||'-'||l_bank_code
585 where a.ACCOUNT_NUMBER = l_account_number
586 and a.company_code = l_party_code
587 and a.BALANCE_DATE = (select max(b.BALANCE_DATE)
588 from XTR_BANK_BALANCES b
589 where b.ACCOUNT_NUMBER = l_account_number
590 and b.company_code = l_party_code
591 and b.BALANCE_DATE < l_calc_date)
592 group by a.BALANCE_DATE,a.ACCOUNT_NUMBER;
593 --
594 cursor SETOFF_THIS_DETAILS is
595 SELECT nvl(sum(a.LEDGER_BALANCE),0),
596 nvl(sum(a.VALUE_DATED_BALANCE-a.LEDGER_BALANCE),0),
597 nvl(sum(a.AVAILABLE_BALANCE),0)
598 FROM CE_BANK_ACCT_BALANCES a --bug 4696629
599 WHERE a.bank_account_id IN
600 (SELECT account_id acct_id
601 FROM ce_cashpool_sub_accts
602 WHERE cashpool_id = p_cashpool_id
603 AND type in ('CONC','ACCT')
604 UNION
605 SELECT nvl(conc_account_id, inv_conc_account_id) acct_id
606 FROM ce_cashpool_sub_accts
607 WHERE cashpool_id = p_cashpool_id
608 AND type = 'POOL'
609 AND nvl(conc_account_id, inv_conc_account_id) is not null
610 UNION
611 SELECT fund_conc_account_id acct_id
612 FROM ce_cashpool_sub_accts
613 WHERE cashpool_id = p_cashpool_id
614 AND type = 'POOL'
615 AND fund_conc_account_id is not null)
616 AND a.BALANCE_DATE = (
617 SELECT max(b.BALANCE_DATE)
618 FROM CE_BANK_ACCT_BALANCES b
619 WHERE b.BANK_ACCOUNT_ID = a.BANK_ACCOUNT_ID
620 AND b.BALANCE_DATE <= l_calc_date);
621
622 -- Added for Interest Override
623 CURSOR oldest_date IS
624 SELECT MIN(a.balance_date)
625 FROM xtr_bank_balances a
626 WHERE a.account_number = l_account_number
627 AND a.company_code = l_party_code;
628 /*WHERE a.account_number = l_setoff||'-'||l_bank_code
632 SELECT a.rounding_type, a.day_count_type
629 AND a.company_code = l_setoff_company;*/
630
631 CURSOR PRV_DETAILS IS
633 FROM xtr_bank_balances a
634 WHERE a.account_number = l_account_number
635 AND a.COMPANY_CODE = l_party_code
636 /*WHERE a.account_number = l_setoff||'-'||l_bank_code
637 AND a.COMPANY_CODE = l_setoff_company*/
638 AND a.balance_date = (select max(b.BALANCE_DATE)
639 from XTR_BANK_BALANCES b
640 where b.ACCOUNT_NUMBER = l_account_number
641 and b.COMPANY_CODE = l_party_code
642 /*where b.ACCOUNT_NUMBER = l_setoff||'-'||l_bank_code
643 and b.COMPANY_CODE = l_setoff_company*/
644 AND b.balance_date < l_calc_date);
645
646 CURSOR PRV_PRV_DETAILS IS
647 SELECT a.day_count_type
648 FROM xtr_bank_balances a
649 WHERE a.account_number = l_account_number
650 AND a.COMPANY_CODE = l_party_code
651 /*WHERE a.account_number = l_setoff||'-'||l_bank_code
652 AND a.COMPANY_CODE = l_setoff_company*/
653 AND a.balance_date = (select max(b.BALANCE_DATE)
654 from XTR_BANK_BALANCES b
655 where b.ACCOUNT_NUMBER = l_account_number
656 and b.COMPANY_CODE = l_party_code
657 /*where b.ACCOUNT_NUMBER = l_setoff||'-'||l_bank_code
658 and b.COMPANY_CODE = l_setoff_company*/
659 AND b.balance_date < l_prv_date);
660
661 CURSOR DEALER_DETAILS IS
662 SELECT dealer_code
663 FROM xtr_dealer_codes
664 WHERE user_id = FND_GLOBAL.USER_ID;
665
666
667 l_rounding_type VARCHAR2(1);
668 l_day_count_type VARCHAR2(1);
669 l_prv_rounding_type VARCHAR2(1);
670 l_prv_day_count_type VARCHAR2(1);
671 l_first_trans_flag VARCHAR2(1);
672 l_oldest_date DATE;
673 l_original_amount NUMBER;
674 l_prv_prv_day_count_type VARCHAR2(1);
675
676 l_created_by XTR_DEALER_CODES.DEALER_CODE%TYPE;
677 l_updated_by XTR_DEALER_CODES.DEALER_CODE%TYPE;
678 --
679 begin
680 -- Calculate Setoff details
681 OPEN NOTIONAL_ACCT_NO;
682 FETCH NOTIONAL_ACCT_NO
683 INTO l_account_number, l_party_code, l_notl_bank_code,
684 l_notl_currency, l_notl_year_calc_type,
685 l_notl_rounding_type, l_notl_day_count_type;
686 CLOSE NOTIONAL_ACCT_NO;
687
688 OPEN DEALER_DETAILS;
689 FETCH DEALER_DETAILS
690 INTO l_created_by;
691 CLOSE DEALER_DETAILS;
692
693 IF (l_created_by IS NOT NULL) THEN
694 l_updated_by := l_created_by;
695 ELSE
696 l_updated_by := FND_GLOBAL.USER_ID;
697 l_created_by := FND_GLOBAL.USER_ID;
698 END IF;
699
700
701 OPEN SEL_SETOFF_ACCT;
702 LOOP
703 FETCH SEL_SETOFF_ACCT
704 INTO l_account_id;
705 EXIT WHEN SEL_SETOFF_ACCT%NOTFOUND;
706
707 delete XTR_BANK_BALANCES
708 --where ACCOUNT_NUMBER = l_setoff||'-'||l_bank_code
709 where ACCOUNT_NUMBER = l_account_number
710 and company_code = l_party_code
711 and BALANCE_DATE >= p_calc_date
712 and INTEREST_SETTLED = 0; -- AW 6/15 Bug 906228
713 -- Do not delete row with Interest Settled.
714 open SETOFF_CAL_DATE;
715 LOOP
716 fetch SETOFF_CAL_DATE INTO l_calc_date;
717 EXIT WHEN SETOFF_CAL_DATE%NOTFOUND;
718 open SETOFF_PREV_DETAILS;
719 fetch SETOFF_PREV_DETAILS INTO l_prv_date,l_prv_bal,l_prv_adj,l_prv_cflw,l_int_bf,l_prv_rate,l_prv_accrual_int;
720 if SETOFF_PREV_DETAILS%NOTFOUND then
721 l_prv_date := l_calc_date;
722 l_prv_bal := 0;
723 l_prv_adj := 0;
724 l_prv_cflw := 0;
725 l_prv_rate := 0;
726 l_int_bf := 0;
727 l_no_days := 0;
728 l_prv_accrual_int := 0;
729 end if;
730 open SETOFF_THIS_DETAILS;
731 fetch SETOFF_THIS_DETAILS INTO l_this_bal,l_this_adj,l_this_cflw;
732 close SETOFF_THIS_DETAILS;
733
734 /* XTR_ACCOUNT_BAL_MAINT_P.FIND_INT_RATE(l_account_number,
735 l_this_bal + l_this_adj,
736 l_party_code,l_notl_bank_code,
737 l_notl_currency,l_calc_date,l_rate); */
738
739 l_rate := CE_INTEREST_CALC.GET_INTEREST_RATE(l_account_id,l_calc_date
740 ,l_this_bal+l_this_adj,l_rate);
741
742 if l_rate is null then
743 l_rate := 0;
744 end if;
745 close SETOFF_PREV_DETAILS;
746 open RNDING;
747 fetch RNDING INTO roundfac,yr_basis;
748 close RNDING;
749 if l_prv_rate is null then
750 l_prv_rate := 0;
751 end if;
752 ---- l_no_days := (trunc(l_calc_date) - trunc(l_prv_date));
753
754 -- Added for Interest Override
755 OPEN oldest_date;
756 FETCH oldest_date INTO l_oldest_date;
757 CLOSE oldest_date;
758
759 OPEN prv_details;
760 FETCH prv_details INTO l_prv_rounding_type, l_prv_day_count_type;
761 CLOSE prv_details;
762 --
763 if trunc(l_calc_date) > trunc(l_prv_date) then
764 -- Added for Interest Override
768 IF (l_prv_day_count_type ='B' AND l_prv_date = l_oldest_date)
765 OPEN prv_prv_details;
766 FETCH prv_prv_details INTO l_prv_prv_day_count_type;
767 CLOSE prv_prv_details;
769 OR (l_prv_prv_day_count_type ='F' AND l_prv_day_count_type ='B' ) THEN
770 l_first_trans_flag :='Y';
771 ELSE
772 l_first_trans_flag :=NULL;
773 END IF;
774 --
775 XTR_CALC_P.CALC_DAYS_RUN(trunc(l_prv_date),
776 trunc(l_calc_date),
777 --l_yr_type,
778 l_notl_year_calc_type,
779 l_no_days,
780 yr_basis,
781 NULL,
782 nvl(l_prv_day_count_type, -- Added for Interest Override
783 l_notl_day_count_type),
784 l_first_trans_flag); -- Added for Interest Overrdie
785
786 -- Added for Interest Override
787 IF l_prv_date <> l_oldest_date AND
788 ((Nvl(l_prv_prv_day_count_type,l_prv_day_count_type) ='L' AND l_prv_day_count_type ='F')
789 OR (Nvl(l_prv_prv_day_count_type,l_prv_day_count_type) ='B' AND l_prv_day_count_type ='F'))
790 THEN
791 l_no_days := l_no_days -1;
792 END IF;
793 --
794 else
795 l_no_days :=0;
796 yr_basis :=365;
797 end if;
798
799 -- Changed for Interest Override
800 -- l_interest := round((l_prv_bal+l_prv_adj) * l_prv_rate / 100 * l_no_days
801 -- / yr_basis,roundfac);
802 l_interest := xtr_fps2_p.interest_round((l_prv_bal+l_prv_adj) * l_prv_rate / 100 * l_no_days
803 / yr_basis,roundfac,l_notl_rounding_type);
804 --/ yr_basis,roundfac,l_prv_rounding_type);
805 -- l_int_cf := l_int_bf + l_interest;
806 l_original_amount := l_int_bf + l_interest;
807 l_int_cf := l_original_amount;
808
809 l_accrual_int :=nvl(l_prv_accrual_int,0)+nvl(l_interest,0);
810
811 l_rate := nvl(l_rate,0);
812
813 -- AW 6/15 Bug 906228
814 -- Update row with interest settled, if not found then insert a new row.
815 update XTR_BANK_BALANCES
816 set NO_OF_DAYS = l_no_days,
817 STATEMENT_BALANCE = l_this_bal,
818 BALANCE_ADJUSTMENT = l_this_adj,
819 BALANCE_CFLOW = l_this_cflw,
820 ACCUM_INT_BFWD = l_int_bf,
821 INTEREST = l_interest,
822 INTEREST_RATE = l_rate,
823 ACCUM_INT_CFWD = l_int_cf - INTEREST_SETTLED,
824 ACCRUAL_INTEREST = l_accrual_int,
825 original_amount = l_original_amount, -- Added for Interest Override
826 rounding_type = l_notl_rounding_type,
827 day_count_type = l_notl_day_count_type -- Bug 5393539
828 where COMPANY_CODE = l_party_code
829 and ACCOUNT_NUMBER = l_account_number
830 /*where COMPANY_CODE = l_setoff_company
831 and ACCOUNT_NUMBER = l_setoff||'-'||l_bank_code*/
832 and BALANCE_DATE = l_calc_date;
833 if SQL%NOTFOUND then
834 -- Existing code
835 insert into XTR_BANK_BALANCES
836 (COMPANY_CODE,ACCOUNT_NUMBER,BALANCE_DATE,NO_OF_DAYS,
837 STATEMENT_BALANCE,BALANCE_ADJUSTMENT,BALANCE_CFLOW,
838 ACCUM_INT_BFWD,INTEREST,INTEREST_RATE,INTEREST_SETTLED,
839 INTEREST_SETTLED_HCE,ACCUM_INT_CFWD,created_on, created_by,accrual_interest,
840 original_amount, rounding_type, day_count_type) -- Added for Interest Override
841 values
842 (l_party_code,l_account_number,
843 l_calc_date,l_no_days,l_this_bal,l_this_adj,
844 l_this_cflw,l_int_bf,
845 l_interest,l_rate,0,0,l_int_cf, sysdate,
846 l_created_by,l_accrual_int,
847 l_original_amount, l_notl_rounding_type,l_notl_day_count_type); -- Bug 5393539
848
849 end if;
850 END LOOP;
851 -- AW 6/1/99
852 update XTR_BANK_ACCOUNTS
853 set INTEREST_RATE = l_rate,
854 UPDATED_ON = sysdate,
855 UPDATED_BY = l_updated_by
856 --where ACCOUNT_NUMBER = l_setoff||'-'||l_bank_code
857 where ACCOUNT_NUMBER = l_account_number
858 and party_code = l_party_code
859 and CURRENCY = l_notl_currency;
860 --
861 close SETOFF_CAL_DATE;
862 END LOOP;
863 close SEL_SETOFF_ACCT;
864 commit;
865 end MAINTAIN_SETOFFS;
866 ------------------------------------------------------------------------------------------------------------------
867 PROCEDURE UPDATE_BANK_ACCTS(p_account_number IN VARCHAR2,
868 p_currency IN VARCHAR2,
869 p_bank_code IN VARCHAR2,
870 p_portfolio IN VARCHAR2,
871 p_pty_cross_ref IN VARCHAR2,
872 p_party_code IN VARCHAR2,
873 p_recalc_date IN DATE,
874 p_accum_int_cfwd IN OUT NOCOPY NUMBER,
875 p_overwrite IN VARCHAR2 DEFAULT NULL
876 ) is
877 --
878 -- Procedure to Update the Bank Balance in the Bank Account
882 --
879 -- and DDA tables with the latest balances. This is called from 1080
880 -- after
881
883 l_calc_date DATE;
884 roundfac NUMBER;
885 yr_basis NUMBER;
886 l_no_days NUMBER;
887 l_prv_date DATE;
888 l_this_rate NUMBER;
889 l_prv_rate NUMBER;
890 l_rate NUMBER;
891 l_prv_bal NUMBER;
892 l_prv_adj NUMBER;
893 l_prv_cflw NUMBER;
894 l_int_bf NUMBER;
895 l_int_cf NUMBER;
896 l_int_set NUMBER;
897 l_interest NUMBER;
898 l_this_bal NUMBER;
899 l_stmt_bal NUMBER;
900 l_bal_adj NUMBER;
901 l_bal_cflw NUMBER;
902 l_hce_rate NUMBER;
903 l_open_bal NUMBER;
904 l_limit_code VARCHAR2(7);
905 l_yr_type VARCHAR2(15);
906 l_accrual_int NUMBER;
907 l_prv_accrual_int NUMBER;
908 --
909 cursor DEAL_NUM is
910 select DEAL_NUMBER
911 from XTR_DEAL_DATE_AMOUNTS_V
912 where DEAL_TYPE = 'CA'
913 and AMOUNT_TYPE = 'BAL'
914 and ACCOUNT_NO = p_account_number
915 and CURRENCY = p_currency
916 and COMPANY_CODE = nvl(p_pty_cross_ref,p_party_code);
917 --
918 cursor EXP_NUM is
919 select XTR_DEALS_S.NEXTVAL
920 from DUAL;
921 --
922 l_deal_nos NUMBER;
923
924 cursor RNDING is
925 select HCE_RATE,ROUNDING_FACTOR
926 from XTR_MASTER_CURRENCIES_V
927 where CURRENCY = P_CURRENCY;
928 --
929 cursor CAL_DATE is
930 select BALANCE_DATE,
931 INTEREST_RATE,
932 NVL(STATEMENT_BALANCE,0)+NVL(BALANCE_ADJUSTMENT,0) BAL,
933 INTEREST_SETTLED,
934 nvl( BALANCE_CFLOW,0), -- Added nvl for R12 project Bug 4546183
935 LIMIT_CODE
936 from XTR_BANK_BALANCES
937 where BALANCE_DATE >= P_RECALC_DATE
938 and ACCOUNT_NUMBER = P_ACCOUNT_NUMBER
939 and COMPANY_CODE = P_PARTY_CODE
940 order by BALANCE_DATE asc;
941
942 cursor get_yr_type is
943 select nvl(YEAR_CALC_TYPE,'ACTUAL/ACTUAL'),
944 Nvl(rounding_type,'R'),Nvl(day_count_type,'L'), ce_bank_account_id -- Added for Interest Override
945 from XTR_bank_accounts
946 where ACCOUNT_NUMBER = P_ACCOUNT_NUMBER
947 and PARTY_CODE = P_PARTY_CODE;
948 -- Modified the cursor R12 Removed the reference to ap_bank_account_id, dummy_bank_account_id
949 cursor chk_setoff is
950 select SETOFF_ACCOUNT_YN, ce_bank_account_id
951 from XTR_bank_accounts
952 where ACCOUNT_NUMBER = P_ACCOUNT_NUMBER
953 and PARTY_CODE = P_PARTY_CODE;
954 l_setoff_acct_yn varchar2(1);
955 l_bank_account_id CE_BANK_ACCOUNTS.BANK_ACCOUNT_ID%TYPE;
956 --
957 cursor PREV_DETAILS is
958 select a.BALANCE_DATE,NVL(a.STATEMENT_BALANCE,0)+NVL(a.BALANCE_ADJUSTMENT,0) INT_BAL,a.ACCUM_INT_CFWD,a.INTEREST_RATE,A.ACCRUAL_INTEREST,
959 a.rounding_type, a.day_count_type -- Added for Interest Override
960 from XTR_BANK_BALANCES a
961 where a.ACCOUNT_NUMBER = P_ACCOUNT_NUMBER
962 and COMPANY_CODE = P_PARTY_CODE
963 and a.BALANCE_DATE = (select max(b.BALANCE_DATE)
964 from XTR_BANK_BALANCES b
965 where b.ACCOUNT_NUMBER = P_ACCOUNT_NUMBER
966 and b.COMPANY_CODE = P_PARTY_CODE
967 and b.BALANCE_DATE < l_calc_date);
968 -- Added for Interest Override
969 CURSOR oldest_date IS
970 SELECT MIN(a.balance_date)
971 FROM xtr_bank_balances a
972 WHERE a.account_number = p_account_number
973 AND a.company_code = p_party_code;
974
975 CURSOR PRV_PRV_DETAILS IS
976 SELECT a.day_count_type
977 FROM xtr_bank_balances a
978 WHERE a.account_number = p_account_number
979 AND a.COMPANY_CODE = p_party_code
980 AND a.balance_date = (select max(b.BALANCE_DATE)
981 from XTR_BANK_BALANCES b
982 where b.ACCOUNT_NUMBER = p_account_number
983 and b.COMPANY_CODE = p_party_code
984 AND b.balance_date < l_prv_date);
985 CURSOR CASHPOOL_DETAILS IS
986 SELECT pool.cashpool_id, pool.conc_account_id
987 FROM ce_cashpools pool, ce_cashpool_sub_accts subs
988 WHERE pool.type = 'NOTIONAL'
989 AND pool.cashpool_id = subs.cashpool_id
990 AND subs.type in ('CONC','ACCT')
991 AND subs.account_id = l_bank_account_id
992 UNION
993 SELECT pool.cashpool_id, pool.conc_account_id
994 FROM ce_cashpools pool, ce_cashpool_sub_accts subs
995 WHERE pool.type = 'NOTIONAL'
996 AND subs.type = 'POOL'
997 AND pool.cashpool_id = subs.cashpool_id
998 AND (subs.conc_account_id = l_bank_account_id OR
999 subs.inv_conc_account_id = l_bank_account_id OR
1000 subs.fund_conc_account_id = l_bank_account_id);
1001
1002 CURSOR DEALER_DETAILS IS
1003 SELECT dealer_code
1004 FROM xtr_dealer_codes
1005 WHERE user_id = FND_GLOBAL.USER_ID;
1006
1007 l_rounding_type VARCHAR2(1);
1008 l_day_count_type VARCHAR2(1);
1009 l_ce_bank_account_id XTR_BANK_ACCOUNTS.CE_BANK_ACCOUNT_ID%TYPE;
1010 l_prv_rounding_type VARCHAR2(1);
1011 l_prv_day_count_type VARCHAR2(1);
1012 l_first_trans_flag VARCHAR2(1);
1013 l_oldest_date DATE;
1014 l_original_amount NUMBER;
1015 l_prv_prv_day_count_type VARCHAR2(1);
1016 l_cashpool_id CE_CASHPOOLS.CASHPOOL_ID%TYPE;
1017 l_conc_acct_id CE_BANK_ACCOUNTS.BANK_ACCOUNT_ID%TYPE;
1018 l_created_by XTR_DEALER_CODES.DEALER_CODE%TYPE;
1019 l_updated_by XTR_DEALER_CODES.DEALER_CODE%TYPE;
1020 --
1021 begin
1025 INTO l_created_by;
1022
1023 OPEN DEALER_DETAILS;
1024 FETCH DEALER_DETAILS
1026 CLOSE DEALER_DETAILS;
1027
1028 IF (l_created_by IS NOT NULL) THEN
1029 l_updated_by := l_created_by;
1030 ELSE
1031 l_updated_by := FND_GLOBAL.USER_ID;
1032 l_created_by := FND_GLOBAL.USER_ID;
1033 END IF;
1034
1035 l_calc_date :=null;
1036 --
1037 open get_yr_type;
1038 fetch get_yr_type into l_yr_type,
1039 l_rounding_type, l_day_count_type,l_ce_bank_account_id; -- Added for Interest Override Modified for R12 4425540
1040 close get_yr_type;
1041 --
1042 open CAL_DATE;
1043 fetch CAL_DATE INTO l_calc_date,l_this_rate,l_this_bal,l_int_set,l_open_bal,l_limit_code;
1044 WHILE CAL_DATE%FOUND LOOP
1045 open PREV_DETAILS;
1046 fetch PREV_DETAILS INTO l_prv_date,l_prv_bal,l_int_bf,l_prv_rate,l_prv_accrual_int,
1047 l_prv_rounding_type, l_prv_day_count_type;
1048 if PREV_DETAILS%NOTFOUND then
1049 l_prv_date := l_calc_date;
1050 l_prv_bal := 0;
1051 l_prv_rate := 0;
1052 l_int_bf := 0;
1053 l_no_days := 0;
1054 l_prv_accrual_int :=0;
1055 l_prv_rounding_type := NULL;
1056 l_prv_day_count_type := NULL;
1057 end if;
1058 close PREV_DETAILS;
1059 if nvl(l_this_rate,0) = 0 then
1060 l_this_rate := 0;
1061 /* Modified for R12 No uptake of Bank Balances Bug 4425540
1062 XTR_ACCOUNT_BAL_MAINT_P.FIND_INT_RATE(P_ACCOUNT_NUMBER,
1063 l_this_bal,
1064 p_party_code,
1065 p_bank_code,
1066 p_currency,
1067 l_calc_date,
1068 l_this_rate);
1069 */
1070
1071 l_this_rate := CE_INTEREST_CALC.GET_INTEREST_RATE(l_ce_bank_account_id,l_calc_date
1072 ,l_this_bal,l_this_rate);
1073 l_this_rate := nvl(l_this_rate,0);
1074 end if;
1075 open RNDING;
1076 fetch RNDING INTO l_hce_rate,roundfac;
1077 close RNDING;
1078 roundfac :=nvl(roundfac,2);
1079 -- l_no_days := (trunc(l_calc_date) - trunc(l_prv_date));
1080 -- Added for Interest Override
1081 OPEN oldest_date;
1082 FETCH oldest_date INTO l_oldest_date;
1083 CLOSE oldest_date;
1084 --
1085 if trunc(l_calc_date) > trunc(l_prv_date) THEN
1086 -- Added for Interest Override
1087 OPEN prv_prv_details;
1088 FETCH prv_prv_details INTO l_prv_prv_day_count_type;
1089 CLOSE prv_prv_details;
1090 IF (l_prv_day_count_type ='B' AND l_prv_date = l_oldest_date)
1091 OR (l_prv_prv_day_count_type ='F' AND l_prv_day_count_type ='B' ) THEN
1092 l_first_trans_flag :='Y';
1093 ELSE
1094 l_first_trans_flag :=NULL;
1095 END IF;
1096 --
1097 XTR_CALC_P.CALC_DAYS_RUN(trunc(l_prv_date),
1098 trunc(l_calc_date),
1099 l_yr_type,
1100 l_no_days,
1101 yr_basis,
1102 NULL,
1103 l_prv_day_count_type, -- Added for Interest Override
1104 l_first_trans_flag -- Added for Interest Override
1105 );
1106 -- Added for Interest Override
1107 IF l_prv_date <> l_oldest_date AND
1108 ((Nvl(l_prv_prv_day_count_type,l_prv_day_count_type) ='L' AND l_prv_day_count_type ='F')
1109 OR (Nvl(l_prv_prv_day_count_type,l_prv_day_count_type) ='B' AND l_prv_day_count_type ='F'))
1110 THEN
1111 l_no_days := l_no_days -1;
1112 END IF;
1113 --
1114 else
1115 l_no_days :=0;
1116 yr_basis :=365;
1117 end if;
1118 -- Changed for Interest Override
1119 -- l_interest := round(l_prv_bal * l_prv_rate / 100 * l_no_days
1120 -- / yr_basis,roundfac);
1121 /* Commented the below line in R12 Bug 4593594
1122 l_interest := xtr_fps2_p.interest_round(l_prv_bal * l_prv_rate / 100 * l_no_days
1123 / yr_basis,roundfac,l_prv_rounding_type);
1124 */
1125 -- Added the Below line Bug 4593594 Calling Ce's API to calculate the Interest
1126 CE_INTEREST_CALC.int_cal_xtr( trunc(l_prv_date),
1127 trunc(l_calc_date),
1128 l_ce_bank_account_id,
1129 l_prv_rate,
1130 'TREASURY',
1131 l_interest );
1135 l_int_cf := l_original_amount;
1132
1133 -- l_int_cf := l_int_bf + l_interest - l_int_set;
1134 l_original_amount := nvl(l_int_bf,0) + nvl(l_interest,0) - nvl(l_int_set,0);
1136 --
1137 l_accrual_int :=nvl(l_prv_accrual_int,0) + nvl(l_interest,0);
1138
1139 update XTR_BANK_BALANCES
1140 set NO_OF_DAYS = l_no_days
1141 ,ACCUM_INT_BFWD = l_int_bf
1142 ,INTEREST = nvl(l_interest,0)
1143 ,ACCUM_INT_CFWD = l_int_cf
1144 ,INTEREST_RATE = l_this_rate
1145 ,ACCRUAL_INTEREST = l_accrual_int
1146 ,original_amount = decode(nvl(p_overwrite, 'N'), 'Y',
1147 original_amount, l_original_amount)
1148 ,rounding_type = l_rounding_type
1149 ,day_count_type = l_day_count_type
1150 where ACCOUNT_NUMBER = P_ACCOUNT_NUMBER
1151 and COMPANY_CODE = P_PARTY_CODE
1152 and BALANCE_DATE = l_calc_date;
1153
1154 fetch CAL_DATE INTO l_calc_date,l_this_rate,l_this_bal,l_int_set,l_open_bal,l_limit_code;
1155 END LOOP;
1156 close CAL_DATE;
1157 --
1158 l_setoff_acct_yn :=null;
1159 open chk_setoff ;
1160 fetch chk_setoff into l_setoff_acct_yn, l_bank_account_id;
1161 close chk_setoff;
1162 if l_calc_date is not null and nvl(l_setoff_acct_yn,'N')<>'Y' then
1163 update XTR_BANK_ACCOUNTS
1164 set OPENING_BALANCE = l_open_bal,
1165 OPENING_BAL_HCE = round(l_open_bal/l_hce_rate,roundfac),
1166 STATEMENT_DATE = l_calc_date,
1167 INTEREST_RATE = l_this_rate,
1168 UPDATED_ON = sysdate,
1169 UPDATED_BY = l_updated_by
1170 where ACCOUNT_NUMBER = p_account_Number
1171 and CURRENCY = p_currency
1172 and PARTY_CODE = p_party_code;
1173 --
1174 --p_statement_date := l_calc_date;
1175 p_accum_int_cfwd := l_int_cf;
1176 --
1177 open DEAL_NUM;
1178 fetch DEAL_NUM into l_deal_nos;
1179 if DEAL_NUM%FOUND then
1180 update XTR_DEAL_DATE_AMOUNTS
1181 set AMOUNT =abs(l_open_bal),
1182 CASHFLOW_AMOUNT = 0,
1183 HCE_AMOUNT = abs(nvl(round(l_open_bal/l_hce_rate,roundfac),0)),
1184 AMOUNT_DATE = l_calc_date,
1185 CPARTY_CODE = p_bank_code,
1186 LIMIT_CODE = nvl(l_limit_code,'NILL'), -- AW Bug 968983 8/25/99
1187 PORTFOLIO_CODE = p_portfolio,
1188 DEAL_SUBTYPE = decode(sign(nvl(l_open_bal,0)),-1,'FUND','INVEST'),
1189 TRANSACTION_RATE=l_this_rate
1190 where DEAL_TYPE = 'CA'
1191 and DEAL_NUMBER = l_deal_nos
1192 and AMOUNT_TYPE = 'BAL'
1193 and ACCOUNT_NO = p_account_number
1194 and CURRENCY = p_currency
1195 and COMPANY_CODE = nvl(p_pty_cross_ref,p_party_code) ;
1196 --
1197 else
1198 open EXP_NUM;
1199 fetch EXP_NUM into l_deal_nos;
1200 close EXP_NUM;
1201 --
1202 insert into XTR_DEAL_DATE_AMOUNTS
1203 (DEAL_TYPE,AMOUNT_TYPE,DATE_TYPE,DEAL_NUMBER,TRANSACTION_NUMBER,
1204 TRANSACTION_DATE,CURRENCY,AMOUNT,HCE_AMOUNT,AMOUNT_DATE,
1205 COMPANY_CODE,ACCOUNT_NO,ACTION_CODE,CASHFLOW_AMOUNT,
1206 DEAL_SUBTYPE,PRODUCT_TYPE,LIMIT_CODE,PORTFOLIO_CODE,STATUS_CODE,
1207 CPARTY_CODE,TRANSACTION_RATE)
1208 values
1209 ('CA','BAL','BALANCE',l_deal_nos,1,l_calc_date,p_currency,
1210 abs(l_open_bal),abs(nvl(round(l_open_bal/l_hce_rate,roundfac),0)),l_calc_date,nvl(p_pty_cross_ref,p_party_code),
1211 p_account_number,NULL,0,
1212 decode(sign(nvl(l_open_bal,0)),-1,'FUND','INVEST'),'NOT APPLIC',
1213 nvl(l_limit_code,'NILL'), -- AW Bug 968983 8/25/99
1214 p_portfolio,'CURRENT',p_bank_code,l_this_rate);
1215 end if;
1216 close DEAL_NUM;
1217 elsif l_calc_date is null and nvl(l_setoff_acct_yn,'N')<>'Y' then
1218 -- AW 6/16 Update bank accounts if all balances rows are deleted.
1219 update XTR_BANK_ACCOUNTS
1220 set OPENING_BALANCE = 0,
1221 OPENING_BAL_HCE = 0,
1222 STATEMENT_DATE = null,
1223 INTEREST_RATE = null,
1224 UPDATED_ON = sysdate,
1225 UPDATED_BY = l_updated_by
1226 where ACCOUNT_NUMBER = p_account_Number
1227 and CURRENCY = p_currency
1228 and PARTY_CODE = p_party_code;
1229 --
1230 --p_statement_date := l_calc_date;
1231 p_accum_int_cfwd := 0;
1232 --
1233 open DEAL_NUM;
1234 fetch DEAL_NUM into l_deal_nos;
1235 if DEAL_NUM%FOUND then
1236 update XTR_DEAL_DATE_AMOUNTS
1237 set AMOUNT = 0,
1238 CASHFLOW_AMOUNT = 0,
1239 HCE_AMOUNT = 0
1240 where DEAL_TYPE = 'CA'
1241 and DEAL_NUMBER = l_deal_nos
1242 and AMOUNT_TYPE = 'BAL'
1243 and ACCOUNT_NO = p_account_number
1244 and CURRENCY = p_currency
1245 and COMPANY_CODE = nvl(p_pty_cross_ref,p_party_code) ;
1246 end if;
1247 close DEAL_NUM;
1248 --
1249 end if;
1250 --
1251 OPEN CASHPOOL_DETAILS;
1252 FETCH CASHPOOL_DETAILS
1253 INTO l_cashpool_id, l_conc_acct_id;
1254 CLOSE CASHPOOL_DETAILS;
1255
1256 IF (l_cashpool_id IS NOT NULL) THEN
1257 XTR_ACCOUNT_BAL_MAINT_P.MAINTAIN_SETOFFS(
1258 p_party_code,l_cashpool_id,
1259 l_conc_acct_id,p_recalc_date);
1260 END IF;
1261 --
1262 end UPDATE_BANK_ACCTS;
1263
1264
1265 end XTR_ACCOUNT_BAL_MAINT_P;