1 PACKAGE BODY XTR_REPLICATE_BANK_BALANCES AS
2 /* | $Header: xtrbbalb.pls 120.17 2006/08/25 15:57:23 csutaria noship $ | */
3 --
4 -- To replicate the data from CE tables to xtr_bank_balances table
5 --
6 --
7 -- Purpose: This package will insert/delete/update the bank balances
8 -- from CE tables to the xtr_bank_balances table.
9
10 -- replicate_bank_account is the main procedure through which the
11 -- insert/delete/update procedures will be called.
12 --
13 -- -- MODIFICATION HISTORY
14 -- Person Date Comments
15 -- Eakta Aggarwal 19-May-2005 Created
16 -- --------- ------ ----------------------------------
17
18
19
20 PROCEDURE REPLICATE_BANK_BALANCE
21 ( p_balance_rec IN xtr_bank_balances%ROWTYPE,
22 p_action_flag IN VARCHAR2,
23 x_return_status OUT NOCOPY VARCHAR2,
24 x_msg_count OUT NOCOPY NUMBER,
25 x_msg_data OUT NOCOPY VARCHAR2)
26 IS
27
28 CURSOR C_BALANCE_DETAILS IS
29 SELECT company_code,account_number,balance_date,statement_balance
30 ,balance_adjustment,balance_cflow,ce_bank_account_balance_id
31 FROM XTR_BANK_BALANCES
32 WHERE CE_BANK_ACCOUNT_BALANCE_ID = p_balance_rec.ce_bank_account_balance_id;
33
34
35
36 l_balance_date_updated BOOLEAN;
37 l_balance_rec XTR_BANK_BALANCES%ROWTYPE;
38
39
40 BEGIN
41 l_balance_date_updated := FALSE;
42 FND_MSG_PUB.Initialize;
43 IF(p_action_flag in ('U','D')) THEN
44
45 OPEN C_BALANCE_DETAILS;
46 FETCH C_BALANCE_DETAILS INTO l_balance_rec.company_code,l_balance_rec.account_number
47 ,l_balance_rec.balance_date,l_balance_rec.statement_balance,l_balance_rec.balance_adjustment
48 ,l_balance_rec.balance_cflow,l_balance_rec.ce_bank_account_balance_id;
49 CLOSE C_BALANCE_DETAILS;
50
51
52 IF(nvl(p_balance_rec.balance_date,sysdate) <> nvl(l_balance_rec.balance_date,sysdate)
53 AND p_balance_rec.balance_date is not null AND l_balance_rec.balance_date is not null
54 AND p_action_flag = 'U') THEN
55
56 l_balance_date_updated := TRUE;
57
58 END IF;
59
60 END IF;
61
62
63
64
65 IF(NOT l_balance_date_updated) THEN -- Balance date is not updated
66 IF (p_action_flag in ('I','U') )THEN
67 VALIDATE_BANK_BALANCE(
68 p_balance_rec.company_code,
69 p_balance_rec.account_number,
70 p_balance_rec.balance_date,
71 p_balance_rec.ce_bank_account_balance_id,
72 p_balance_rec.statement_balance+p_balance_rec.balance_adjustment,
73 p_balance_rec.balance_cflow,
74 p_action_flag,
75 x_return_status );
76 END IF;
77 IF (p_action_flag in ('D')) THEN
78 VALIDATE_BANK_BALANCE(
79 l_balance_rec.company_code,
80 l_balance_rec.account_number,
81 l_balance_rec.balance_date,
82 p_balance_rec.ce_bank_account_balance_id,
83 l_balance_rec.statement_balance+l_balance_rec.balance_adjustment,
84 l_balance_rec.balance_cflow,
85 'D',
86 x_return_status );
87 END IF;
88
89
90 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
91
92 IF p_action_flag = 'I' THEN /* Insertion of a record */
93
94 INSERT_BANK_BALANCE ( p_balance_rec, x_return_status);
95
96 ELSIF p_action_flag = 'U' THEN /* Updation of a balance */
97
98 UPDATE_BANK_BALANCE ( p_balance_rec, x_return_status);
99
100 ELSIF p_action_flag = 'D' THEN /* Deletion of a balance */
101
102 DELETE_BANK_BALANCE( l_balance_rec, x_return_status);
103
104 ELSE
105 x_return_status := FND_API.G_RET_STS_ERROR;
106 LOG_ERR_MSG('XTR_INV_PARAM','ACTION_FLAG');
107
108 END IF;
109
110 END IF;
111
112
113 IF (p_action_flag in ('I','U') )THEN
114
115 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
116 UPDATE_BANK_ACCOUNT ( p_balance_rec.company_code,
117 p_balance_rec.account_number,
118 p_balance_rec.balance_date ,
119 p_action_flag ,
120 x_return_status );
121
122 END IF;
123
124 END IF;
125
126 IF (p_action_flag in ('D') )THEN
127
128 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
129 UPDATE_BANK_ACCOUNT ( l_balance_rec.company_code,
130 l_balance_rec.account_number,
131 l_balance_rec.balance_date ,
132 'D' ,
133 x_return_status );
134
135 END IF;
136
137 END IF;
138
139 ELSIF(l_balance_date_updated) THEN
140
141 VALIDATE_BANK_BALANCE(
142 p_balance_rec.company_code,
143 p_balance_rec.account_number,
144 p_balance_rec.balance_date,
145 p_balance_rec.ce_bank_account_balance_id,
146 p_balance_rec.statement_balance+p_balance_rec.balance_adjustment,
147 p_balance_rec.balance_cflow,
148 'D',
149 x_return_status );
150 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
151 DELETE_BANK_BALANCE( p_balance_rec, x_return_status);
152 END IF;
153 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
154 UPDATE_BANK_ACCOUNT ( p_balance_rec.company_code,
155 p_balance_rec.account_number,
156 p_balance_rec.balance_date ,
157 'D' ,
158 x_return_status );
159
160 END IF;
161 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
162 VALIDATE_BANK_BALANCE(
163 p_balance_rec.company_code,
164 p_balance_rec.account_number,
165 p_balance_rec.balance_date,
166 p_balance_rec.ce_bank_account_balance_id,
167 p_balance_rec.statement_balance+p_balance_rec.balance_adjustment,
168 p_balance_rec.balance_cflow,
169 'I',
170 x_return_status );
171 END IF;
172 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
173 INSERT_BANK_BALANCE( p_balance_rec, x_return_status);
174 END IF;
175 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
176 UPDATE_BANK_ACCOUNT ( p_balance_rec.company_code,
177 p_balance_rec.account_number,
178 p_balance_rec.balance_date ,
179 'I',
180 x_return_status );
181
182 END IF;
183
184
185 END IF;
186
187 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
188 ( p_count => x_msg_count ,
189 p_data => x_msg_data
190 );
191
192
193 EXCEPTION
194
195 WHEN others THEN
196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
197 LOG_ERR_MSG ('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
198
199 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
200 ( p_count => x_msg_count ,
201 p_data => x_msg_data
202 );
203
204 END REPLICATE_BANK_BALANCE;
205
206 PROCEDURE REPLICATE_BANK_BALANCE
207 ( p_ce_bank_account_balance_id IN XTR_BANK_BALANCES.ce_bank_account_balance_id%TYPE,
208 p_company_code IN XTR_BANK_BALANCES.company_code%TYPE,
209 p_account_number IN XTR_BANK_BALANCES.account_number%TYPE,
210 p_balance_date IN XTR_BANK_BALANCES.balance_date%TYPE,
211 p_ledger_balance IN CE_BANK_ACCT_BALANCES.ledger_balance%TYPE,
212 p_available_balance IN CE_BANK_ACCT_BALANCES.available_balance%TYPE,
213 p_interest_calculated_balance IN CE_BANK_ACCT_BALANCES.value_dated_balance%TYPE,
214 p_one_day_float IN XTR_BANK_BALANCES.one_day_float%TYPE,
215 p_two_day_float IN XTR_BANK_BALANCES.two_day_float%TYPE,
216 p_action_flag IN varchar2,
217 x_return_status OUT NOCOPY VARCHAR2,
218 x_msg_count OUT NOCOPY NUMBER,
219 x_msg_data OUT NOCOPY VARCHAR2) IS
220
221 l_xtr_bank_balances_rec XTR_BANK_BALANCES%ROWTYPE;
222 l_bank_account_id CE_BANK_ACCT_USES_ALL.BANK_ACCOUNT_ID%TYPE;
223 l_cashpool_id CE_CASHPOOLS.CASHPOOL_ID%TYPE;
224 l_conc_account_id CE_CASHPOOL_SUB_ACCTS.ACCOUNT_ID%TYPE;
225
226
227 cursor c_bank_account_id is
228 select bank_account_id from ce_bank_acct_uses_all
229 where bank_account_id = (select bank_account_id from
230 ce_bank_acct_balances
231 where bank_acct_balance_id =
232 p_ce_bank_account_balance_id)
233 and xtr_use_enable_flag = 'Y';
234
235
236 cursor c_conc_cashpool_id is
237 select sub.cashpool_id, sub.account_id
238 from ce_bank_acct_uses_all acct
239 , ce_Cashpool_sub_accts sub
240 where acct.bank_account_id = sub.account_id
241 and acct.xtr_use_enable_flag = 'Y'
242 and sub.type = 'CONC'
243 and cashpool_id = (select subacct.cashpool_id
244 from ce_cashpool_sub_accts subacct
245 , ce_cashpools pool
246 where account_id = l_bank_account_id
247 and subacct.cashpool_id = pool.cashpool_id
248 and pool.type = 'NOTIONAL' );
249
250
251 BEGIN
252
253 l_xtr_bank_balances_rec.ce_bank_account_balance_id := p_ce_bank_account_balance_id;
254 l_xtr_bank_balances_rec.company_code := p_company_code;
255 l_xtr_bank_balances_rec.account_number := p_account_number;
256 l_xtr_bank_balances_rec.balance_date := p_balance_date;
257 l_xtr_bank_balances_rec.statement_balance := p_ledger_balance;
258 l_xtr_bank_balances_rec.balance_adjustment := (p_interest_calculated_balance - p_ledger_balance);
259 l_xtr_bank_balances_rec.balance_cflow := p_available_balance;
260 l_xtr_bank_balances_rec.one_day_float := p_one_day_float;
261 l_xtr_bank_balances_rec.two_day_float := p_two_day_float;
262
263
264 -- added for notional bank accounts
265
266 open c_bank_account_id;
267 fetch c_bank_account_id into l_bank_account_id;
268
269 if c_bank_account_id%found then
270
271 REPLICATE_BANK_BALANCE( l_xtr_bank_balances_rec,p_action_flag
272 ,x_return_status,x_msg_count,x_msg_data);
273
274
275 else
276
277 open c_conc_cashpool_id;
278 fetch c_conc_cashpool_id into l_cashpool_id, l_conc_account_id;
279 if c_conc_cashpool_id%found then
280 close c_conc_cashpool_id;
281 xtr_account_bal_maint_p.maintain_setoffs(p_company_code,
282 l_cashpool_id,
283 l_conc_account_id,
284 p_balance_date);
285
286 else
287 close c_conc_cashpool_id;
288 x_return_status := FND_API.G_RET_STS_SUCCESS;
289
290 end if;
291
292
293 end if;
294
295 close c_bank_account_id;
296
297
298
299 EXCEPTION
300
301 WHEN others THEN
302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
303 LOG_ERR_MSG ('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
304
305 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
306 ( p_count => x_msg_count ,
307 p_data => x_msg_data
308 );
309
310 END REPLICATE_BANK_BALANCE;
311
312
313
314
315 PROCEDURE INSERT_BANK_BALANCE
316 ( p_balance_rec IN xtr_bank_balances%ROWTYPE,
317 x_return_status IN OUT NOCOPY VARCHAR2
318 )
319 IS
320
321 l_prv_date XTR_BANK_BALANCES.balance_date%TYPE;
322 l_prv_rate XTR_BANK_BALANCES.interest_rate%TYPE;
323 l_prv_bal XTR_BANK_BALANCES.balance_cflow%TYPE;
324 l_int_bf XTR_BANK_BALANCES.ACCUM_INT_CFWD%TYPE;
325 l_prv_accrual_int XTR_BANK_BALANCES.accrual_interest%TYPE;
326 l_prv_day_count_type XTR_BANK_BALANCES.day_count_type%TYPE;
327 l_prv_rounding_type XTR_BANK_BALANCES.rounding_type%TYPE;
328
329
330 l_ccy xtr_bank_accounts.currency%TYPE;
331 l_portfolio_code xtr_bank_accounts.portfolio_code%TYPE;
332 l_bank_code xtr_bank_accounts.bank_code%TYPE;
333 l_yr_type xtr_bank_accounts.year_calc_type%TYPE;
334 l_rounding_type xtr_bank_accounts.rounding_type%TYPE;
335 l_day_count_type xtr_bank_accounts.day_count_type%TYPE;
336 l_ce_bank_account_id xtr_bank_accounts.ce_bank_account_id%TYPE;
337
338 l_round_factor XTR_MASTER_CURRENCIES_V.ROUNDING_FACTOR%TYPE;
339 l_yr_basis XTR_MASTER_CURRENCIES_V.YEAR_BASIS%TYPE;
340 l_hce_rate XTR_MASTER_CURRENCIES_V.HCE_RATE%TYPE;
341
342 l_prv_prv_day_count_type XTR_BANK_BALANCES.day_count_type%TYPE;
343 l_oldest_date XTR_BANK_BALANCES.balance_date%TYPE;
344
345 l_first_trans_flag VARCHAR2(1);
346 l_invest_limit_code ce_bank_acct_uses_all.investment_limit_code%TYPE;
347 l_fund_limit_code ce_bank_acct_uses_all.funding_limit_code%TYPE;
348
349
350 l_no_days NUMBER;
351
352
353 l_int_cf NUMBER;
354 l_interest NUMBER;
355 l_original_amount NUMBER;
356 l_accrual_int NUMBER;
357 l_new_rate NUMBER;
358
359
360 -- Get the details of the latest balance for a bank account
361 CURSOR PREV_DETAILS IS
362 SELECT a.BALANCE_DATE,NVL(a.STATEMENT_BALANCE,0)+NVL(a.BALANCE_ADJUSTMENT,0),a.ACCUM_INT_CFWD,
363 a.INTEREST_RATE,a.accrual_interest,
364 a.rounding_type, day_count_type
365 FROM XTR_BANK_BALANCES a
366 WHERE a.ACCOUNT_NUMBER = p_balance_rec.account_number
367 AND a.COMPANY_CODE = p_balance_rec.company_code
368 AND a.BALANCE_DATE = (SELECT max(b.BALANCE_DATE)
369 FROM XTR_BANK_BALANCES b
370 WHERE b.ACCOUNT_NUMBER = p_balance_rec.account_number
371 AND b.COMPANY_CODE = p_balance_rec.company_code);
372
373
374 -- Check whether the bank account exist in the xtr_bank_accounts table
375 CURSOR BANK_ACCT_DETAILS IS
376 SELECT CURRENCY,PORTFOLIO_CODE,BANK_CODE,nvl(YEAR_CALC_TYPE,'ACTUAL/ACTUAL') year_calc_type,rounding_type, day_count_type,ce_bank_account_id
377 FROM XTR_BANK_ACCOUNTS
378 WHERE ACCOUNT_NUMBER = p_balance_rec.account_number
379 AND PARTY_CODE = p_balance_rec.company_code;
380 --
381
382 -- Get the details of the rounding factor for the currency
383 CURSOR CURRENCY_RNDING IS
384 SELECT ROUNDING_FACTOR,YEAR_BASIS,HCE_RATE
385 FROM XTR_MASTER_CURRENCIES_V
386 WHERE CURRENCY = l_ccy;
387
388
389
390 -- Getting the limit code
391 CURSOR GET_LIM_CODE is
392 select investment_limit_code, funding_limit_code
393 from ce_bank_acct_uses_all
394 where bank_account_id in ( select bank_account_id from
395 ce_bank_acct_balances
396 where bank_acct_balance_id = p_balance_rec.ce_bank_account_balance_id
397 );
398
399
400 -- Getting the oldest balacne date for that bank account
401 CURSOR oldest_date IS
402 SELECT MIN(a.balance_date)
403 FROM xtr_bank_balances a
404 WHERE a.account_number = p_balance_rec.account_number
405 AND a.COMPANY_CODE = p_balance_rec.company_code;
406
407
408 CURSOR PRV_PRV_DETAILS IS
409 SELECT a.day_count_type
410 FROM xtr_bank_balances a
411 WHERE a.account_number = p_balance_rec.account_number
412 AND a.COMPANY_CODE = p_balance_rec.company_code
413 AND a.balance_date = (SELECT max(b.BALANCE_DATE)
414 FROM XTR_BANK_BALANCES b
415 WHERE b.ACCOUNT_NUMBER = p_balance_rec.account_number
416 AND b.COMPANY_CODE = p_balance_rec.company_code
417 AND b.balance_date < l_prv_date);
418
419
420
421
422
423 BEGIN
424 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
425 x_return_status := FND_API.G_RET_STS_SUCCESS;
426 OPEN PREV_DETAILS;
427 FETCH PREV_DETAILS INTO l_prv_date,l_prv_bal,l_int_bf,l_prv_rate,l_prv_accrual_int,
428 l_prv_rounding_type, l_prv_day_count_type;
429 IF PREV_DETAILS%NOTFOUND THEN
430 l_prv_date := trunc(p_balance_rec.balance_date);
431 l_prv_bal := 0;
432 l_prv_rate := 0;
433 l_int_bf := 0;
434 l_no_days := 0;
435 l_prv_accrual_int := 0;
436 l_prv_rounding_type := NULL;
437 l_prv_day_count_type := NULL;
438 END IF;
439 CLOSE PREV_DETAILS;
440
441
442 OPEN BANK_ACCT_DETAILS;
443 FETCH BANK_ACCT_DETAILS INTO l_ccy,l_portfolio_code,l_bank_code,
444 l_yr_type, l_rounding_type, l_day_count_type,l_ce_bank_account_id;
445 IF BANK_ACCT_DETAILS%FOUND THEN
446 CLOSE BANK_ACCT_DETAILS;
447
448 OPEN CURRENCY_RNDING;
449 FETCH CURRENCY_RNDING INTO l_round_factor,l_yr_basis,l_hce_rate;
450 CLOSE CURRENCY_RNDING;
451
452 -- bug 4870347
453 open GET_LIM_CODE;
454 FETCH GET_LIM_CODE INTO l_invest_limit_code, l_fund_limit_code;
455 IF GET_LIM_CODE%NOTFOUND THEN
456 l_invest_limit_code := NULL;
457 l_fund_limit_code := NULL;
458 END IF;
459 CLOSE GET_LIM_CODE;
460
461 OPEN oldest_date;
462 FETCH oldest_date INTO l_oldest_date;
463 CLOSE oldest_date;
464 --
465 IF trunc(l_prv_date) < trunc(p_balance_rec.balance_date) THEN
466
467 OPEN prv_prv_details;
468 FETCH prv_prv_details INTO l_prv_prv_day_count_type;
469 CLOSE prv_prv_details;
470 IF (l_prv_day_count_type ='B' AND l_prv_date = l_oldest_date)
471 OR (l_prv_prv_day_count_type ='F' AND l_prv_day_count_type ='B' ) THEN
472 l_first_trans_flag :='Y';
473 ELSE
474 l_first_trans_flag :=NULL;
475 END IF;
476
477 XTR_CALC_P.CALC_DAYS_RUN(trunc(l_prv_date),
478 trunc(p_balance_rec.balance_date),
479 l_yr_type,
480 l_no_days,
481 l_yr_basis,
482 NULL,
483 l_prv_day_count_type,
484 l_first_trans_flag);
485
486
487 IF l_prv_date <> l_oldest_date AND
488 ((Nvl(l_prv_prv_day_count_type,l_prv_day_count_type) ='L' AND l_prv_day_count_type ='F')
489 OR (Nvl(l_prv_prv_day_count_type,l_prv_day_count_type) ='B' AND l_prv_day_count_type ='F'))
490 THEN
491 l_no_days := l_no_days -1;
492 END IF;
493
494 ELSE
495 l_no_days :=0;
496 l_yr_basis :=365;
497 END IF;
498
499 /* Commented the below line in R12. Interest is calculated using CE API
500 l_interest := xtr_fps2_p.interest_round(l_prv_bal * l_prv_rate / 100 * l_no_days
501 / l_yr_basis,l_round_factor,l_prv_rounding_type); */
502 -- Added the below line R12 Bug 4593594
503 CE_INTEREST_CALC.int_cal_xtr( trunc(l_prv_date),
504 trunc(p_balance_rec.balance_date),
505 l_ce_bank_account_id,
506 l_prv_rate,
507 'TREASURY',
508 l_interest );
509
510
511 l_original_amount := l_int_bf + nvl(l_interest,0);
512 l_int_cf := l_original_amount;
513
514 l_accrual_int :=nvl(l_prv_accrual_int,0) + nvl(l_interest,0);
515
516
517 /*
518 XTR_ACCOUNT_BAL_MAINT_P.FIND_INT_RATE(p_balance_rec.account_number
519 , p_balance_rec.statement_balance
520 , p_balance_rec.company_code
521 , l_bank_code
522 , l_ccy
523 , p_balance_rec.balance_date
524 , l_new_rate);
525 */
526 l_new_rate := CE_INTEREST_CALC.GET_INTEREST_RATE(l_ce_bank_account_id,p_balance_rec.balance_date
527 ,NVL(p_balance_rec.STATEMENT_BALANCE,0)+NVL(p_balance_rec.BALANCE_ADJUSTMENT,0)
528 ,l_new_rate);
529 IF l_new_rate IS NULL THEN
530 l_new_rate := 0;
531 END IF;
532
533 INSERT INTO XTR_BANK_BALANCES
534 ( company_code
535 ,account_number
536 ,balance_date
537 ,no_of_days
538 ,statement_balance
539 ,balance_adjustment
540 ,balance_cflow
541 ,accum_int_bfwd
542 ,interest
543 ,interest_rate
544 ,interest_settled
545 ,interest_settled_hce
546 ,accum_int_cfwd
547 ,limit_code
548 ,created_on
549 ,created_by
550 ,accrual_interest
551 ,rounding_type
552 ,day_count_type
553 ,original_amount
554 ,one_day_float
555 ,two_day_float
556 ,ce_bank_account_balance_id)
557 VALUES
558 ( p_balance_rec.company_code
559 ,p_balance_rec.account_number
560 ,p_balance_rec.balance_date
561 ,l_no_days
562 ,p_balance_rec.statement_balance
563 ,p_balance_rec.balance_adjustment
564 ,p_balance_rec.balance_cflow
565 ,l_int_bf
566 ,nvl(l_interest,0)
567 ,l_new_rate
568 ,0
569 ,0
570 ,l_int_cf
571 ,decode(sign(nvl(p_balance_rec.statement_balance,0)), -1,l_fund_limit_code,l_invest_limit_code)
572 ,sysdate
573 ,fnd_global.user_id
574 ,l_accrual_int
575 ,l_rounding_type
576 ,l_day_count_type
577 ,l_original_amount
578 ,p_balance_rec.one_day_float
579 ,p_balance_rec.two_day_float
580 ,p_balance_rec.ce_bank_account_balance_id);
581
582 ELSE
583 CLOSE BANK_ACCT_DETAILS;
584 LOG_ERR_MSG('XTR_INV_PARAM', p_balance_rec.ce_bank_account_balance_id);
585 x_return_status := FND_API.G_RET_STS_ERROR;
586
587 END IF;
588
589 EXCEPTION
590
591 WHEN others THEN
592 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
593 LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
594
595 END INSERT_BANK_BALANCE ;
596
597
598
599
600 PROCEDURE UPDATE_BANK_BALANCE
601 ( p_balance_rec IN xtr_bank_balances%ROWTYPE,
602 x_return_status IN OUT NOCOPY VARCHAR2
603 )
604 IS
605
606 CURSOR c_chk_lock IS
607 SELECT ce_bank_account_balance_id
608 FROM xtr_bank_balances
609 WHERE company_code = p_balance_rec.company_code
610 AND account_number = p_balance_rec.account_number
611 AND ce_bank_account_balance_id = p_balance_rec.ce_bank_account_balance_id
612 FOR UPDATE NOWAIT;
613
614
615 l_ce_bank_acct_bal_id xtr_bank_balances.ce_bank_account_balance_id%TYPE;
616
617
618 BEGIN
619 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
620 x_return_status := FND_API.G_RET_STS_SUCCESS;
621 OPEN c_chk_lock;
622 FETCH c_chk_lock INTO l_ce_bank_acct_bal_id;
623 IF c_chk_lock%FOUND THEN
624
625 CLOSE c_chk_lock;
626
627 UPDATE xtr_bank_balances SET
628 statement_balance = nvl(p_balance_rec.statement_balance,0)
629 ,balance_cflow = nvl(p_balance_rec.balance_cflow,0)
630 ,one_day_float = nvl(p_balance_rec.one_day_float,0)
631 ,two_day_float = nvl(p_balance_rec.two_day_float,0)
632 ,balance_adjustment = p_balance_rec.balance_adjustment
633 ,balance_date = p_balance_rec.balance_date
634 ,updated_by = fnd_global.user_id
635 ,updated_on = sysdate
636 WHERE company_code = p_balance_rec.company_code
637 AND account_number = p_balance_rec.account_number
638 AND ce_bank_account_balance_id = p_balance_rec.ce_bank_account_balance_id;
639
640 ELSE
641 CLOSE c_chk_lock;
642 END IF;
643
644
645 EXCEPTION
646 WHEN app_exceptions.RECORD_LOCK_EXCEPTION THEN
647 IF C_CHK_LOCK%ISOPEN THEN
648 CLOSE C_CHK_LOCK;
649 END IF;
650 LOG_ERR_MSG('CHK_LOCK');
651 x_return_status := FND_API.G_RET_STS_ERROR;
652 WHEN others THEN
653 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
654 LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
655
656 END UPDATE_BANK_BALANCE;
657
658
659
660 PROCEDURE DELETE_BANK_BALANCE
661 ( p_balance_rec IN xtr_bank_balances%ROWTYPE,
662 x_return_status IN OUT NOCOPY VARCHAR2
663 )
664 IS
665
666 l_cross_ref xtr_party_info.cross_ref_to_other_party%TYPE;
667 l_exists VARCHAR2(1);
668 l_ce_bank_acct_bal_id xtr_bank_balances.ce_bank_account_balance_id%TYPE;
669 l_ccy xtr_bank_accounts.currency%TYPE;
670
671 -- Finding the subsidiary for the company
672 CURSOR C_CROSS_REF IS
673 SELECT CROSS_REF_TO_OTHER_PARTY
674 FROM XTR_PARTIES_V
675 WHERE PARTY_CODE = p_balance_rec.company_code;
676
677
678 -- Finding the currency for the account number and company combination
679 CURSOR C_CURRENCY IS
680 SELECT CURRENCY
681 FROM XTR_BANK_ACCOUNTS
682 WHERE PARTY_CODE = P_BALANCE_REC.COMPANY_CODE
683 AND ACCOUNT_NUMBER = P_BALANCE_REC.ACCOUNT_NUMBER;
684
685
686 -- Checking whether the row exists in the DDA table
687 CURSOR C_BAL_SETTLED IS
688 SELECT 'Y'
689 FROM xtr_deal_date_amounts
690 WHERE DEAL_TYPE = 'CA'
691 AND AMOUNT_TYPE = 'INTSET'
692 AND ACCOUNT_NO = p_balance_rec.account_number
693 AND CURRENCY = l_ccy
694 AND COMPANY_CODE = nvl(l_cross_ref,p_balance_rec.company_code)
695 AND AMOUNT_DATE = p_balance_rec.balance_date;
696
697 -- Checking the lock on the xtr_bank_balances table
698 CURSOR c_chk_acct_lock IS
699 SELECT ce_bank_account_balance_id
700 FROM xtr_bank_balances
701 WHERE company_code = p_balance_rec.company_code
702 AND account_number = p_balance_rec.account_number
703 AND ce_bank_account_balance_id = p_balance_rec.ce_bank_account_balance_id
704 FOR UPDATE NOWAIT;
705
706 -- Checking the lock on the xtr_deal_date_amounts table
707 CURSOR c_chk_dda_lock IS
708 SELECT ce_bank_account_balance_id
709 FROM xtr_bank_balances
710 WHERE company_code = p_balance_rec.company_code
711 AND account_number = p_balance_rec.account_number
712 AND ce_bank_account_balance_id = p_balance_rec.ce_bank_account_balance_id
713 FOR UPDATE NOWAIT;
714
715
716 BEGIN
717 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
718 x_return_status := FND_API.G_RET_STS_SUCCESS;
719 OPEN c_chk_acct_lock;
720 FETCH c_chk_acct_lock INTO l_ce_bank_acct_bal_id;
721 IF c_chk_acct_lock%FOUND THEN
722
723 CLOSE c_chk_acct_lock;
724
725 OPEN C_CROSS_REF;
726 FETCH C_CROSS_REF INTO l_cross_ref;
727 CLOSE C_CROSS_REF;
728
729 DELETE FROM XTR_BANK_BALANCES
730 WHERE company_code = p_balance_rec.company_code
731 AND account_number = p_balance_rec.account_number
732 AND balance_date = p_balance_rec.balance_date
733 AND ce_bank_account_balance_id = p_balance_rec.ce_bank_account_balance_id;
734
735
736 ELSE
737 CLOSE c_chk_acct_lock;
738
739 END IF;
740
741 OPEN C_CURRENCY;
742 FETCH C_CURRENCY INTO l_ccy;
743 CLOSE C_CURRENCY;
744
745 OPEN C_bal_settled;
746 FETCH C_bal_settled INTO l_exists;
747
748 OPEN c_chk_dda_lock;
749 FETCH c_chk_dda_lock INTO l_ce_bank_acct_bal_id;
750 IF c_chk_dda_lock%FOUND THEN
751
752 CLOSE c_chk_dda_lock;
753
754 IF c_bal_settled%FOUND THEN
755
756 DELETE FROM XTR_DEAL_DATE_AMOUNTS_V
757 WHERE DEAL_TYPE = 'CA'
758 AND AMOUNT_TYPE = 'INTSET'
759 AND ACCOUNT_NO = p_balance_rec.account_number
760 AND CURRENCY = l_ccy
761 AND COMPANY_CODE = nvl(l_cross_ref,p_balance_rec.company_code)
762 AND AMOUNT_DATE = p_balance_rec.balance_date;
763
764 END IF;
765 CLOSE c_bal_settled;
766 ELSE
767 CLOSE c_chk_dda_lock;
768
769 END IF;
770
771
772 EXCEPTION
773 WHEN app_exceptions.RECORD_LOCK_EXCEPTION THEN
774
775 IF C_CHK_acct_LOCK%ISOPEN THEN
776 CLOSE C_CHK_acct_LOCK;
777 END IF;
778
779 IF C_CHK_DDA_LOCK%ISOPEN THEN
780 CLOSE C_CHK_DDA_LOCK;
781 END IF;
782
783 LOG_ERR_MSG('CHK_LOCK');
784 x_return_status := FND_API.G_RET_STS_ERROR;
785
786
787 WHEN others THEN
788
789 LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
790 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
791
792 END DELETE_BANK_BALANCE;
793
794
795
796 PROCEDURE VALIDATE_BANK_BALANCE
797 ( p_company_code IN xtr_bank_balances.company_code%TYPE,
798 p_account_number IN xtr_bank_balances.account_number%TYPE,
799 p_balance_date IN xtr_bank_balances.balance_date%TYPE,
800 p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE DEFAULT NULL,
801 p_interest_calc_balance IN NUMBER,
802 p_available_balance IN NUMBER,
803 p_action_flag IN VARCHAR2,
804 x_return_status OUT NOCOPY VARCHAR2
805 )
806 IS
807
808 l_portfolio_code xtr_bank_accounts.portfolio_code%TYPE;
809 l_currency xtr_bank_accounts.currency%TYPE;
810 l_bank_code xtr_bank_accounts.bank_code%TYPE;
811 l_result BOOLEAN;
812 l_duplicate NUMBER;
813 l_return_error VARCHAR2(30);
814 l_authorised VARCHAR2(1);
815 l_ce_bank_account_id xtr_bank_accounts.ce_bank_account_id%TYPE ;
816 l_int_schedule_id ce_bank_accounts.interest_schedule_id%TYPE ;
817 l_pricing_model xtr_bank_accounts.pricing_model%TYPE;
818
819
820 -- Check whether the portfolio code exists for the bank account for which balacnes
821 -- are to be entered
822 CURSOR C_PORTFOLIO IS
823 SELECT PORTFOLIO_CODE, CURRENCY , BANK_CODE, PRICING_MODEL,
824 ce_bank_account_id /* Bug 5346243 */
825 FROM xtr_bank_accounts
826 WHERE ACCOUNT_NUMBER = p_account_number
827 AND PARTY_CODE = p_company_code;
828
829
830 -- Check whether the balance does not already exist for
831 CURSOR C_DUPLICATE_DATE IS
832 SELECT 1
833 FROM XTR_BANK_BALANCES_V A,
834 XTR_BANK_ACCOUNTS_V B
835 WHERE A.BALANCE_DATE = p_balance_date
836 AND A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER
837 AND A.COMPANY_CODE = B.PARTY_CODE
838 AND B.CURRENCY = l_currency
839 AND B.ACCOUNT_NUMBER = p_account_number
840 AND B.BANK_CODE = l_bank_code
841 AND B.PARTY_CODE = p_company_code;
842
843
844 cursor c_pm_authorized is
845 select 1
846 from xtr_price_models_v
847 where deal_type = 'CA'
848 and code = l_pricing_model
849 and authorized = 'Y';
850
851 /* Bug 5346243 */
852
853 Cursor c_int_schedule ( l_ce_bank_account_id Number ) Is
854 Select interest_schedule_id
855 From ce_bank_accounts
856 Where bank_account_id = l_ce_bank_account_id ;
857
858
859
860 BEGIN
861
862 x_return_status := FND_API.G_RET_STS_SUCCESS;
863
864 OPEN c_portfolio;
865 FETCH c_portfolio INTO l_portfolio_code, l_currency,l_bank_code, l_pricing_model, l_ce_bank_account_id ;
866
867 -- Validation at the time of insertion/updation of the record
868
869
870 IF p_action_flag IN ( 'I' , 'U') THEN
871
872 IF p_action_flag = 'I' AND (l_portfolio_code Is null) THEN
873 -- Checking whether the portfolio code exists for the bank account
874 x_return_status := FND_API.G_RET_STS_ERROR;
875 LOG_ERR_MSG ( 'XTR_PORTFOLIO');
876 END IF;
877
878 CLOSE c_portfolio;
879
880 -- Checking whether the balance date is greater than sysdate
881 IF p_balance_date > sysdate THEN
882 x_return_status := FND_API.G_RET_STS_ERROR;
883 LOG_ERR_MSG ( 'XTR_104');
884 END IF;
885
886 -- Checking whether an interest schedule is assigned to the bank account
887 /* Bug 5346243 */
888 Open c_int_schedule(l_ce_bank_account_id) ;
889 fetch c_int_schedule into l_int_schedule_id ;
890 close c_int_schedule ;
891
892 If l_int_schedule_id is Null Then
893 x_return_status := FND_API.G_RET_STS_ERROR;
894 LOG_ERR_MSG ( 'CE_NO_SCHED_BANK_ACCT');
895 End If ;
896
897 -- Checking whether the pricing model is authorised
898 If l_pricing_model Is Not Null Then
899 OPEN c_pm_authorized;
900 FETCH c_pm_authorized into l_authorised;
901 if c_pm_authorized%NOTFOUND then
902 x_return_status := FND_API.G_RET_STS_ERROR;
903 LOG_ERR_MSG ( 'XTR_INV_PRICING_MODEL');
904 end if;
905 close c_pm_authorized;
906 End If ;
907
908 -- Checking the balance does not exist for the date being entered by the user
909 OPEN c_duplicate_date;
910 FETCH c_duplicate_date INTO l_duplicate;
911 IF c_duplicate_date%FOUND AND p_action_flag = 'I' THEN
912 x_return_status := FND_API.G_RET_STS_ERROR;
913 LOG_ERR_MSG ( 'XTR_1237');
914 END IF;
915 CLOSE c_duplicate_date;
916 -- Checking for interest includes/ interest rounding
917 IF ((NOT CHK_ROUNDING_CHANGE ( p_company_code ,p_account_number ,p_balance_date )) AND p_interest_calc_balance IS NOT NULL) THEN
918 x_return_status := FND_API.G_RET_STS_ERROR;
919 LOG_ERR_MSG ( 'XTR_TYPES_CHANGED');
920 END IF;
921
922 -- Checking for revaluations
923
924 l_return_error := chk_reval( p_company_code,
925 p_account_number,
926 l_currency,
927 p_balance_date,
928 p_ce_bank_account_balance_id,
929 p_available_balance,
930 p_action_flag,
931 'E');
932
933
934 IF nvl(l_return_error, 'XXX') = 'XTR_CA_REVAL_DONE' THEN
935 x_return_status := FND_API.G_RET_STS_ERROR;
936 LOG_ERR_MSG ( 'XTR_CA_REVAL_DONE');
937 ELSIF nvl(l_return_error, 'XXX') = 'XTR_REVAL_ACCRL_DATE' THEN
938 x_return_status := FND_API.G_RET_STS_ERROR;
939 LOG_ERR_MSG ( 'XTR_REVAL_ACCRL_DATE',p_balance_date);
940 ELSIF nvl(l_return_error, 'XXX') = 'XTR_BANK_REVAL_DONE' THEN
941 x_return_status := FND_API.G_RET_STS_ERROR;
942 LOG_ERR_MSG ( 'XTR_BANK_REVAL_DONE',p_balance_date);
943 END IF;
944
945
946 -- Checking for accruals
947
948 l_return_error := chk_accrual( p_company_code,
949 p_account_number,
950 l_currency,
951 p_balance_date,
952 p_ce_bank_account_balance_id,
953 p_interest_calc_balance,
954 p_action_flag,
955 'E');
956
957 IF nvl(l_return_error, 'XXX') = 'XTR_BANK_ACCRLS_EXIST' THEN
958 x_return_status := FND_API.G_RET_STS_ERROR;
959 LOG_ERR_MSG ( 'XTR_BANK_ACCRLS_EXIST',p_balance_date);
960
961 ELSIF nvl(l_return_error, 'XXX') = 'XTR_REVAL_ACCRL_DATE' THEN
962 x_return_status := FND_API.G_RET_STS_ERROR;
963 LOG_ERR_MSG ( 'XTR_REVAL_ACCRL_DATE',p_balance_date);
964 END IF;
965
966 ELSIF p_action_flag = 'D' THEN
967
968 CLOSE c_portfolio;
969
970
971 -- Checking for interest includes/ interest rounding
972
973 IF ((NOT CHK_ROUNDING_CHANGE( p_company_code ,p_account_number ,p_balance_date )) AND p_interest_calc_balance IS NOT NULL) THEN
974 x_return_status := FND_API.G_RET_STS_ERROR;
975 LOG_ERR_MSG ( 'XTR_CHANGED_DAYCOUNT_ROUND');
976 END IF;
977
978 -- Checking for revaluations
979 l_return_error := chk_reval( p_company_code,
980 p_account_number,
981 l_currency,
982 p_balance_date,
983 p_ce_bank_account_balance_id,
984 p_available_balance,
985 'D',
986 'E');
987
988 IF nvl(l_return_error, 'XXX') = 'XTR_ACCT_DELETE' THEN
989 x_return_status := FND_API.G_RET_STS_ERROR;
990 LOG_ERR_MSG ( 'XTR_ACCT_DELETE');
991 END IF;
992
993
994 -- Checking for accruals
995 l_return_error := chk_accrual( p_company_code,
996 p_account_number,
997 l_currency,
998 p_balance_date,
999 p_ce_bank_account_balance_id,
1000 p_interest_calc_balance,
1001 'D',
1002 'E');
1003
1004 IF nvl(l_return_error, 'XXX') = 'XTR_DEAL_ACCRLS_EXIST' THEN
1005 x_return_status := FND_API.G_RET_STS_ERROR;
1006 LOG_ERR_MSG ( 'XTR_DEAL_ACCRLS_EXIST',p_balance_date);
1007 END IF;
1008
1009 END IF ; -- p_action_flag
1010
1011 EXCEPTION
1012 WHEN others THEN
1013
1014 LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
1015 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1016
1017 END VALIDATE_BANK_BALANCE;
1018
1019
1020 PROCEDURE VALIDATE_BANK_BALANCE
1021 ( p_company_code IN xtr_bank_balances.company_code%TYPE,
1022 p_account_number IN xtr_bank_balances.account_number%TYPE,
1023 p_balance_date IN xtr_bank_balances.balance_date%TYPE,
1024 p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE default null,
1025 p_interest_calc_balance IN NUMBER,
1026 p_available_balance in NUMBER,
1027 p_action_flag IN VARCHAR2,
1028 x_return_status OUT NOCOPY VARCHAR2,
1029 x_msg_count OUT NOCOPY NUMBER,
1030 x_msg_data OUT NOCOPY VARCHAR2) IS
1031
1032 CURSOR C_BALANCE_DETAILS IS
1033 SELECT company_code,account_number,balance_date,statement_balance
1034 ,balance_adjustment,balance_cflow,ce_bank_account_balance_id
1035 FROM XTR_BANK_BALANCES
1036 WHERE CE_BANK_ACCOUNT_BALANCE_ID = p_ce_bank_account_balance_id;
1037
1038
1039 l_balance_date_updated BOOLEAN;
1040 l_balance_rec XTR_BANK_BALANCES%ROWTYPE;
1041
1042 BEGIN
1043
1044 x_return_status := FND_API.G_RET_STS_SUCCESS;
1045 l_balance_date_updated := FALSE;
1046 FND_MSG_PUB.Initialize;
1047 IF(p_action_flag in ('U','D')) THEN
1048
1049 OPEN C_BALANCE_DETAILS;
1050 FETCH C_BALANCE_DETAILS INTO l_balance_rec.company_code,l_balance_rec.account_number
1051 ,l_balance_rec.balance_date,l_balance_rec.statement_balance,l_balance_rec.balance_adjustment
1052 ,l_balance_rec.balance_cflow,l_balance_rec.ce_bank_account_balance_id;
1053 CLOSE C_BALANCE_DETAILS;
1054
1055
1056 IF(nvl(p_balance_date,sysdate) <> nvl(l_balance_rec.balance_date,sysdate)
1057 AND p_balance_date is not null AND l_balance_rec.balance_date is not null
1058 AND p_action_flag = 'U') THEN
1059
1060 l_balance_date_updated := TRUE;
1061
1062 END IF;
1063
1064 END IF;
1065
1066 IF(NOT l_balance_date_updated) THEN -- Balance date is not updated
1067 IF (p_action_flag in ('I','U') )THEN
1068 VALIDATE_BANK_BALANCE(
1069 p_company_code,
1070 p_account_number,
1071 p_balance_date,
1072 p_ce_bank_account_balance_id,
1073 p_interest_calc_balance,
1074 p_available_balance,
1075 p_action_flag,
1076 x_return_status );
1077 END IF;
1078 IF (p_action_flag in ('D')) THEN
1079 VALIDATE_BANK_BALANCE(
1080 l_balance_rec.company_code,
1081 l_balance_rec.account_number,
1082 l_balance_rec.balance_date,
1083 p_ce_bank_account_balance_id,
1084 l_balance_rec.statement_balance+l_balance_rec.balance_adjustment,
1085 l_balance_rec.balance_cflow,
1086 'D',
1087 x_return_status );
1088 END IF;
1089
1090 ELSIF(l_balance_date_updated) THEN
1091
1092 VALIDATE_BANK_BALANCE(
1093 p_company_code,
1094 p_account_number,
1095 p_balance_date,
1096 p_ce_bank_account_balance_id,
1097 p_interest_calc_balance,
1098 p_available_balance,
1099 'D',
1100 x_return_status );
1101
1102 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1103 VALIDATE_BANK_BALANCE(
1104 p_company_code,
1105 p_account_number,
1106 p_balance_date,
1107 p_ce_bank_account_balance_id,
1108 p_interest_calc_balance,
1109 p_available_balance,
1110 'I',
1111 x_return_status );
1112 END IF;
1113
1114
1115 END IF;
1116
1117 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
1118 ( p_count => x_msg_count ,
1119 p_data => x_msg_data
1120 );
1121
1122
1123 EXCEPTION
1124
1125 WHEN others THEN
1126 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1127 LOG_ERR_MSG ('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
1128
1129 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
1130 ( p_count => x_msg_count ,
1131 p_data => x_msg_data
1132 );
1133
1134 END VALIDATE_BANK_BALANCE;
1135
1136
1137
1138
1139 FUNCTION CHK_ACCRUAL ( p_company_code IN xtr_bank_balances.company_code%TYPE
1140 , p_account_number IN xtr_bank_balances.account_number%TYPE
1141 , p_currency IN xtr_bank_accounts.currency%TYPE
1142 , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1143 , p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE DEFAULT NULL
1144 , p_interest_calc_balance IN NUMBER
1145 , p_action_flag IN VARCHAR2
1146 , p_val_type IN VARCHAR2)
1147 RETURN VARCHAR2
1148 IS
1149
1150 l__val_type varchar2(1);
1151 l_error VARCHAR2(50);
1152 l_accrual_deal_date DATE;
1153 l_accrual_batch_date DATE;
1154 l_interest_calc_balance NUMBER;
1155
1156 -- Check whether the accruals have been run for that particular deal
1157 CURSOR c_accrl_deal IS
1158 SELECT max(period_to)
1159 FROM
1160 xtr_bank_balances bb, xtr_bank_accounts ba,
1161 xtr_deal_date_amounts dd, xtr_accrls_amort aa
1162 WHERE bb.company_code = p_company_code
1163 AND bb.account_number = p_account_number
1164 AND bb.company_code = ba.party_code
1165 AND bb.account_number = ba.account_number
1166 AND ba.currency = p_currency
1167 AND bb.company_code = dd.company_code
1168 AND bb.account_number = dd.account_no
1169 AND ba.currency = dd.currency
1170 AND dd.deal_number = aa.deal_no;
1171
1172 -- Check whether the accruals have been run for period greater than the
1173 -- balance date entered by the user
1174 CURSOR c_accrl_comp IS
1175 SELECT max(period_end)
1176 FROM
1177 xtr_batches b, xtr_batch_events e
1178 WHERE b.company_code = p_company_code
1179 AND b.batch_id = e.batch_id
1180 AND e.event_code = 'ACCRUAL'
1181 AND b.period_end >=p_balance_date;
1182
1183
1184 -- Getting the interest calc amount from the database for the updated record
1185 CURSOR c_accrl_amount IS
1186 SELECT BALANCE_ADJUSTMENT + STATEMENT_BALANCE
1187 FROM
1188 xtr_bank_balances bb
1189 WHERE
1190 bb.company_code = p_company_code
1191 AND bb.account_number = p_account_number
1192 AND ce_bank_account_balance_id = p_ce_bank_account_balance_id;
1193
1194
1195
1196 BEGIN
1197 IF p_action_flag IN ('D','U') THEN
1198
1199 OPEN c_accrl_deal;
1200 FETCH c_accrl_deal INTO l_accrual_deal_date;
1201 CLOSE c_accrl_deal;
1202
1203 IF l_accrual_deal_date IS NOT NULL AND l_accrual_deal_date >= p_balance_date THEN
1204
1205 IF p_action_flag = 'U' THEN
1206
1207 OPEN c_accrl_amount;
1208 FETCH c_accrl_amount INTO l_interest_calc_balance;
1209 CLOSE c_accrl_amount;
1210
1211 IF l_interest_calc_balance <> p_interest_calc_balance
1212 AND p_val_type = 'E' THEN
1213 l_error := 'XTR_DEAL_ACCRLS_EXIST';
1214
1215 END IF;
1216
1217 ELSIF p_action_flag = 'D' AND p_val_type = 'E'THEN
1218 l_error := 'XTR_DEAL_ACCRLS_EXIST';
1219 END IF;
1220 END IF;
1221 ELSIF (p_action_flag = 'I' AND nvl(p_interest_calc_balance,0) <> 0) THEN
1222 OPEN c_accrl_deal;
1223 FETCH c_accrl_deal INTO l_accrual_deal_date;
1224 CLOSE c_accrl_deal;
1225 IF l_accrual_deal_date IS NULL THEN
1226 OPEN c_accrl_comp;
1227 FETCH c_accrl_comp INTO l_accrual_batch_date;
1228 CLOSE c_accrl_comp;
1229
1230 IF l_accrual_batch_date IS NOT NULL
1231 AND l_accrual_batch_date >= p_balance_date AND p_val_type = 'W' THEN
1232 l_error := 'XTR_DEALS_BEFORE_ACCRUAL'; -- Warning message
1233
1234 END IF;
1235 ELSIF l_accrual_deal_date IS NOT NULL
1236 AND l_accrual_deal_date >= p_balance_date AND p_val_type = 'E' THEN
1237
1238 l_error := 'XTR_DEALS_ACCRLS_EXIST';
1239
1240 END IF;
1241
1242
1243 END IF; -- p_action_flag
1244 RETURN l_error;
1245 END CHK_ACCRUAL;
1246
1247
1248
1249 -- This function checks whether the revaluations have been run at the time of
1250 -- insertion/updation/deletion
1251
1252 FUNCTION CHK_REVAL ( p_company_code IN xtr_bank_balances.company_code%TYPE
1253 , p_account_number IN xtr_bank_balances.account_number%TYPE
1254 , p_currency IN xtr_bank_accounts.currency%TYPE
1255 , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1256 , p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE DEFAULT NULL
1257 , p_balance_cflow IN xtr_bank_balances.balance_cflow%TYPE
1258 , p_action_flag IN VARCHAR2
1259 , p_val_type IN VARCHAR2 )
1260 RETURN VARCHAR2
1261 IS
1262
1263 l_error VARCHAR2(50);
1264 l_reval_deal_date DATE;
1265 l_reval_batch_date DATE;
1266 l_reval_delete_date DATE;
1267 l_balance_cflow xtr_bank_balances.balance_cflow%TYPE;
1268
1269
1270 -- Check whether the revaluations have been run for period greater than the
1271 -- balance date entered by the user
1272 CURSOR c_reval_comp IS
1273 SELECT max(period_end)
1274 FROM
1275 xtr_batches b,xtr_batch_events e
1276 WHERE
1277 b.company_code = p_company_code
1278 AND b.batch_id = e.batch_id
1279 AND e.event_code = 'REVAL'
1280 AND b.period_end >= p_balance_date;
1281
1282
1283 CURSOR c_deal_delete IS
1284 SELECT max(bb.balance_date)
1285 FROM
1286 xtr_bank_balances bb,xtr_bank_accounts ba
1287 WHERE
1288 bb.company_code = p_company_code
1289 AND bb.account_number = p_account_number
1290 AND bb.company_code = ba.party_code
1291 AND bb.account_number = ba.account_number
1292 AND ba.currency = p_currency
1293 AND bb.first_batch_id IS NOT NULL;
1294
1295
1296 -- Check whether the revaluations have been run for that particular deal
1297 CURSOR c_reval_deal IS
1298 SELECT max(period_to)
1299 FROM
1300 xtr_bank_balances bb,xtr_bank_accounts ba,
1301 xtr_revaluation_details rd
1302 WHERE
1303 bb.company_code = p_company_code
1304 AND bb.account_number = p_account_number
1305 AND bb.company_code = ba.party_code
1306 AND bb.account_number = ba.account_number
1307 AND ba.currency = p_currency
1308 AND bb.account_number = rd.account_no
1309 AND rd.deal_type = 'CA';
1310
1311 -- Getting the cashflow amount from the database for the updated record
1312 CURSOR c_reval_amount IS
1313 SELECT BALANCE_CFLOW
1314 FROM
1315 xtr_bank_balances bb
1316 WHERE
1317 bb.company_code = p_company_code
1318 AND bb.account_number = p_account_number
1319 AND ce_bank_account_balance_id = p_ce_bank_account_balance_id;
1320
1321
1322
1323 BEGIN
1324 -- checking at time of updation of balance
1325 IF p_action_flag = 'U' THEN
1326
1327 OPEN c_reval_deal;
1328 FETCH c_reval_deal INTO l_reval_deal_date;
1329 CLOSE c_reval_deal;
1330
1331 IF l_reval_deal_date IS NOT NULL AND l_reval_deal_date >= p_balance_date THEN
1332 -- checks whether the changed amount is same as the database amount
1333 OPEN c_reval_amount;
1334 FETCH c_reval_amount INTO l_balance_cflow;
1335 CLOSE c_reval_amount;
1336 IF (l_balance_cflow <> p_balance_cflow AND p_val_type = 'E')THEN
1337 l_error := 'XTR_DEAL_REVAL_DONE';
1338 END IF;
1339
1340 END IF;
1341
1342 -- checking at time deletion of balance
1343 ELSIF p_action_flag = 'D' THEN
1344
1345 OPEN c_deal_delete ;
1346 FETCH c_deal_delete INTO l_reval_delete_date;
1347 CLOSE c_deal_delete ;
1348
1349 IF l_reval_delete_date IS NOT NULL AND l_reval_delete_date >= p_BALANCE_DATE
1350 AND p_val_type = 'E'THEN
1351 l_error :='XTR_ACCT_DELETE';
1352 END IF;
1353
1354 -- checking at time insertion of balance
1355 -- Added the condition for checking balance not 0
1356 ELSIF (p_action_flag = 'I' AND nvl(p_balance_cflow,0) <> 0) THEN
1357
1358 OPEN c_reval_deal;
1359 FETCH c_reval_deal INTO l_reval_deal_date;
1360 CLOSE c_reval_deal;
1361 IF l_reval_deal_date IS NULL THEN
1362 OPEN c_reval_comp;
1363 FETCH c_reval_comp INTO l_reval_batch_date;
1364 CLOSE c_reval_comp;
1365
1366 IF l_reval_batch_date IS NOT NULL AND l_reval_batch_date >= p_balance_date
1367 AND p_val_type = 'W' THEN
1368 l_error := 'XTR_DEALS_BEFORE_REVAL'; -- warning message
1369 END IF;
1370
1371 ELSIF l_reval_deal_date IS NOT NULL AND l_reval_deal_date >= p_balance_date
1372 AND p_val_type = 'E' THEN
1373 l_error := 'XTR_CA_REVAL_DONE';
1374
1375 END IF;
1376
1377 END IF; -- p_action_flag
1378
1379 RETURN l_error;
1380
1381 END CHK_REVAL;
1382
1383
1384
1385 PROCEDURE UPDATE_BANK_ACCOUNT
1386 ( p_company_code IN xtr_bank_balances.company_code%TYPE,
1387 p_account_number IN xtr_bank_balances.account_number%TYPE,
1388 p_balance_date IN xtr_bank_balances.balance_date%TYPE,
1389 p_action_flag IN VARCHAR2,
1390 x_return_status OUT NOCOPY VARCHAR2
1391 )
1392 IS
1393
1394 l_ccy xtr_bank_accounts.currency%TYPE;
1395 l_portfolio_code xtr_bank_accounts.portfolio_code%TYPE;
1396 l_bank_code xtr_bank_accounts.bank_code%TYPE;
1397 l_cross_ref xtr_party_info.cross_ref_to_other_party%TYPE;
1398 l_bal_exists VARCHAR2(10);
1399 l_dummy_num VARCHAR2(1);
1400 l_bal_date xtr_bank_balances.balance_date%TYPE;
1401 l_accum_int_cfwd xtr_bank_balances.accum_int_cfwd%TYPE;
1402
1403 CURSOR C_SUBSIDIARY IS
1404 SELECT CROSS_REF_TO_OTHER_PARTY
1405 FROM XTR_PARTIES_V
1406 WHERE PARTY_CODE = p_company_code;
1407
1408
1409 CURSOR C_ACCT_DETAILS IS
1410 SELECT CURRENCY,PORTFOLIO_CODE,BANK_CODE
1411 FROM XTR_BANK_ACCOUNTS
1412 WHERE ACCOUNT_NUMBER = p_account_number
1413 AND PARTY_CODE = p_company_code;
1414
1415 -- To check whether the 'BAL' row exists in DDA table
1416 CURSOR C_BAL_EXISTS IS
1417 SELECT 'Y'
1418 FROM XTR_DEAL_DATE_AMOUNTS_V
1419 WHERE ACCOUNT_NO = p_account_number
1420 AND COMPANY_CODE = nvl(l_cross_ref,p_company_code)
1421 AND CURRENCY = l_ccy
1422 AND AMOUNT_DATE = p_balance_date
1423 AND AMOUNT_TYPE = 'BAL';
1424
1425 -- To find the latest date which is less the than date for the user
1426 -- is deleting the balance
1427
1428 CURSOR C_BAL_DATE IS
1429 SELECT max(BALANCE_DATE)
1430 FROM XTR_BANK_BALANCES_V
1431 WHERE ACCOUNT_NUMBER = p_account_number
1432 AND COMPANY_CODE = p_company_code
1433 AND BALANCE_DATE < p_balance_date;
1434
1435
1436 --
1437 CURSOR C_ACCUM_INT IS
1438 SELECT ACCUM_INT_CFWD
1439 FROM XTR_BANK_BALANCES
1440 WHERE ACCOUNT_NUMBER = P_ACCOUNT_NUMBER
1441 AND COMPANY_CODE = P_COMPANY_CODE
1442 AND BALANCE_DATE = (SELECT MAX(BALANCE_DATE) FROM
1443 XTR_BANK_BALANCES
1444 WHERE ACCOUNT_NUMBER = P_ACCOUNT_NUMBER
1445 AND COMPANY_CODE = P_COMPANY_CODE);
1446
1447 BEGIN
1448
1449 x_return_status := FND_API.G_RET_STS_SUCCESS; -- Added Bug 4546183
1450 OPEN C_ACCT_DETAILS;
1451 FETCH C_ACCT_DETAILS INTO l_ccy,l_portfolio_code, l_bank_code;
1452 CLOSE C_ACCT_DETAILS;
1453
1454
1455 OPEN C_SUBSIDIARY;
1456 FETCH C_SUBSIDIARY INTO l_cross_ref;
1457 CLOSE C_SUBSIDIARY;
1458
1459
1460 OPEN C_ACCUM_INT;
1461 FETCH C_ACCUM_INT INTO L_ACCUM_INT_CFWD;
1462 IF l_accum_int_cfwd is null then
1463 l_accum_int_cfwd := 0 ;
1464 end if;
1465 CLOSE C_ACCUM_INT;
1466
1467 IF p_action_flag = 'I' THEN
1468
1469 XTR_ACCOUNT_BAL_MAINT_P.UPDATE_BANK_ACCTS(
1470 p_account_number,
1471 l_ccy,
1472 l_bank_code,
1473 l_portfolio_code,
1474 l_cross_ref,
1475 p_company_code,
1476 p_balance_date,
1477 l_accum_int_cfwd,
1478 l_dummy_num
1479 );
1480
1481 ELSIF p_action_flag = 'D' THEN
1482
1483 OPEN C_BAL_EXISTS;
1484 FETCH C_BAL_EXISTS INTO l_bal_exists;
1485 IF C_BAL_EXISTS%FOUND THEN
1486 OPEN C_BAL_DATE;
1487 FETCH C_BAL_DATE INTO l_bal_date;
1488 CLOSE C_BAL_DATE;
1489 END IF;
1490 CLOSE C_BAL_EXISTS;
1491
1492 XTR_ACCOUNT_BAL_MAINT_P.UPDATE_BANK_ACCTS(
1493 p_account_number,
1494 l_ccy,
1495 l_bank_code,
1496 l_portfolio_code,
1497 l_cross_ref,
1498 p_company_code,
1499 nvl(l_bal_date, p_balance_date),
1500 l_accum_int_cfwd,
1501 l_dummy_num
1502 );
1503
1504 ELSIF p_action_flag = 'U' THEN
1505
1506 XTR_ACCOUNT_BAL_MAINT_P.UPDATE_BANK_ACCTS(
1507 p_account_number,
1508 l_ccy,
1509 l_bank_code,
1510 l_portfolio_code,
1511 l_cross_ref,
1512 p_company_code,
1513 p_balance_date,
1514 l_accum_int_cfwd,
1515 l_dummy_num
1516 );
1517
1518 END IF;
1519
1520 END UPDATE_BANK_ACCOUNT;
1521
1522
1523 /*
1524 This FUNCTION will be called BY CE AT THE TIME WHEN THE balance page IS rendered
1525 TO ENABLE/DISABLE THE balance DATE
1526
1527 THE same fuction will be also called FOR VALIDATION OF THE RECORD during
1528 insertion/updation/deletion
1529
1530 */
1531
1532 FUNCTION CHK_ROUNDING_CHANGE ( p_company_code IN xtr_bank_balances.company_code%TYPE,
1533 p_account_number IN xtr_bank_balances.account_number%TYPE,
1534 p_balance_date IN xtr_bank_balances.balance_date%TYPE)
1535 RETURN BOOLEAN IS
1536
1537 CURSOR c_chk_bal IS
1538 SELECT count(balance_date)
1539 FROM XTR_BANK_BALANCES a
1540 WHERE a.ACCOUNT_NUMBER = p_account_number
1541 AND a.COMPANY_CODE = p_company_code;
1542
1543
1544 CURSOR c_chk_type IS
1545 SELECT COUNT(DISTINCT ROUNDING_TYPE||'-'||DAY_COUNT_TYPE)
1546 FROM XTR_BANK_BALANCES a
1547 WHERE a.ACCOUNT_NUMBER = p_account_number
1548 AND a.COMPANY_CODE = p_company_code
1549 AND a.BALANCE_DATE >= (SELECT max(balance_date)
1550 FROM xtr_bank_balances b
1551 WHERE b.account_number = p_account_number
1552 AND b.company_code = p_company_code
1553 AND b.balance_date < p_balance_date);
1554
1555 l_chk_bal NUMBER;
1556 l_count NUMBER;
1557 BEGIN
1558
1559 OPEN c_chk_bal;
1560 FETCH c_chk_bal INTO l_chk_bal;
1561 CLOSE c_chk_bal;
1562
1563 IF l_chk_bal >= 1 THEN -- Bug 5393641
1564 OPEN c_chk_type;
1565 FETCH c_chk_type INTO l_count;
1566 CLOSE c_chk_type;
1567
1568 IF l_count <= 1 THEN
1569 RETURN(TRUE);
1570 ELSE
1571 RETURN(FALSE);
1572 END IF;
1573 ELSE
1574 RETURN(TRUE);
1575 END IF;
1576
1577 END CHK_ROUNDING_CHANGE;
1578
1579
1580
1581 /* This function will be called by CE when the one account- multiple balance
1582 * date page is rendered to enable/disable the balance date and delete button
1583
1584 This will be also called by the validate_bank_balance procedure at the time of
1585 deletion of the record
1586 */
1587
1588 FUNCTION CHK_INTEREST_SETTLED
1589 ( p_ce_bank_account_id IN xtr_bank_accounts.ce_bank_account_id%TYPE
1590 , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1591 )return VARCHAR2 is
1592
1593 l_return VARCHAR2(2);
1594 l_dummy VARCHAR2(1);
1595 l_company_code XTR_BANK_ACCOUNTS.party_code%TYPE;
1596 l_account_number XTR_BANK_ACCOUNTS.account_number%TYPE;
1597 l_currency XTR_BANK_ACCOUNTS.currency%TYPE;
1598
1599
1600 CURSOR c_account_details IS
1601 SELECT party_code, account_number,currency
1602 FROM XTR_BANK_ACCOUNTS
1603 WHERE ce_bank_account_id = p_ce_bank_account_id;
1604
1605 cursor INT_SETTLED is
1606 select 'Y'
1607 from XTR_DEAL_DATE_AMOUNTS_V
1608 where DEAL_TYPE = 'CA'
1609 and AMOUNT_TYPE = 'INTSET'
1610 and ACCOUNT_NO = l_account_number
1611 and CURRENCY = l_currency
1612 and COMPANY_CODE = l_company_code
1613 and AMOUNT_DATE = p_balance_date
1614 and (Batch_Id IS NOT NULL or nvl(SETTLE, 'N') = 'Y');
1615
1616
1617 BEGIN
1618
1619 l_return := 'Y';
1620
1621 IF(p_ce_bank_account_id is not null and p_balance_date is not null) THEN
1622
1623 OPEN c_account_details;
1624 FETCH c_account_details INTO l_company_code,l_account_number,l_currency;
1625 CLOSE c_account_details;
1626
1627 open INT_SETTLED;
1628 fetch INT_SETTLED into l_dummy;
1629 if INT_SETTLED%FOUND then
1630 close INT_SETTLED;
1631 l_return := 'N';
1632 else
1633 close INT_SETTLED;
1634 l_return := 'Y';
1635 end if;
1636
1637 END If;
1638
1639 END CHK_INTEREST_SETTLED;
1640
1641
1642
1643
1644 -- This function will be called by CE at the time when the balabce page is rendered
1645 -- to enable/disable the available balance
1646
1647 FUNCTION CHK_REVAL_ON_RENDER
1648 ( p_company_code IN xtr_bank_balances.company_code%TYPE
1649 , p_account_number IN xtr_bank_balances.account_number%TYPE
1650 , p_currency IN xtr_bank_accounts.currency%TYPE
1651 , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1652 )RETURN BOOLEAN
1653 IS
1654
1655 l_reval_deal_date DATE;
1656 l_reval_delete_date DATE;
1657
1658 -- Check whether the revaluations have been run for that particular deal
1659 CURSOR c_reval_deal IS
1660 SELECT max(period_to)
1661 FROM
1662 xtr_bank_balances bb,xtr_bank_accounts ba,
1663 xtr_revaluation_details rd
1664 WHERE
1665 bb.company_code = p_company_code
1666 AND bb.account_number = p_account_number
1667 AND bb.company_code = ba.party_code
1668 AND bb.account_number = ba.account_number
1669 AND ba.currency = p_currency
1670 AND bb.account_number = rd.account_no
1671 AND rd.deal_type = 'CA';
1672
1673
1674 CURSOR c_deal_delete IS
1675 SELECT max(bb.balance_date)
1676 FROM
1677 xtr_bank_balances bb,xtr_bank_accounts ba
1678 WHERE
1679 bb.company_code = p_company_code
1680 AND bb.account_number = p_account_number
1681 AND bb.company_code = ba.party_code
1682 AND bb.account_number = ba.account_number
1683 AND ba.currency = p_currency
1684 AND bb.first_batch_id IS NOT NULL;
1685
1686 BEGIN
1687
1688 OPEN c_reval_deal;
1689 FETCH c_reval_deal INTO l_reval_deal_date;
1690 CLOSE c_reval_deal;
1691
1692 OPEN c_deal_delete ;
1693 FETCH c_deal_delete INTO l_reval_delete_date;
1694 CLOSE c_deal_delete ;
1695
1696
1697 IF (l_reval_deal_date IS NOT NULL AND l_reval_deal_date >= p_balance_date)
1698 OR ( l_reval_delete_date IS NOT NULL AND l_reval_delete_date >= p_BALANCE_DATE) THEN
1699 RETURN(FALSE);
1700 ELSE
1701 RETURN(TRUE);
1702 END IF;
1703
1704 END CHK_REVAL_ON_RENDER;
1705
1706
1707 -- This function will be called by CE at the time when the balabce page is rendered
1708 -- to enable/disable the interest calculated balance
1709 FUNCTION CHK_ACCRUAL_ON_RENDER
1710 ( p_company_code IN xtr_bank_balances.company_code%TYPE
1711 , p_account_number IN xtr_bank_balances.account_number%TYPE
1712 , p_currency IN xtr_bank_accounts.currency%TYPE
1713 , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1714 )RETURN BOOLEAN
1715 IS
1716
1717 l_accrual_deal_date DATE;
1718
1719 -- Check whether the accruals have been run for that particular deal
1720 CURSOR c_accrl_deal IS
1721 SELECT max(period_to)
1722 FROM
1723 xtr_bank_balances bb, xtr_bank_accounts ba,
1724 xtr_deal_date_amounts dd, xtr_accrls_amort aa
1725 WHERE bb.company_code = p_company_code
1726 AND bb.account_number = p_account_number
1727 AND bb.company_code = ba.party_code
1728 AND bb.account_number = ba.account_number
1729 AND ba.currency = p_currency
1730 AND bb.company_code = dd.company_code
1731 AND bb.account_number = dd.account_no
1732 AND ba.currency = dd.currency
1733 AND dd.deal_number = aa.deal_no;
1734
1735
1736
1737 BEGIN
1738
1739 OPEN c_accrl_deal;
1740 FETCH c_accrl_deal INTO l_accrual_deal_date;
1741 CLOSE c_accrl_deal;
1742
1743 IF l_accrual_deal_date IS NOT NULL AND l_accrual_deal_date >= p_balance_date THEN
1744 RETURN(FALSE);
1745 ELSE
1746 RETURN(TRUE);
1747 END IF;
1748
1749 END CHK_ACCRUAL_ON_RENDER;
1750
1751 FUNCTION CHK_ACCRUAL_ON_RENDER
1752 ( p_ce_bank_account_id IN xtr_bank_accounts.ce_bank_account_id%TYPE
1753 , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1754 )return VARCHAR2 is
1755 l_return VARCHAR2(2);
1756 l_company_code XTR_BANK_ACCOUNTS.party_code%TYPE;
1757 l_account_number XTR_BANK_ACCOUNTS.account_number%TYPE;
1758 l_currency XTR_BANK_ACCOUNTS.currency%TYPE;
1759 CURSOR c_account_details IS
1760 SELECT party_code, account_number,currency
1761 FROM XTR_BANK_ACCOUNTS
1762 WHERE ce_bank_account_id = p_ce_bank_account_id;
1763 BEGIN
1764 l_return := 'Y';
1765 IF(p_ce_bank_account_id is not null and p_balance_date is not null) THEN
1766 OPEN c_account_details;
1767 FETCH c_account_details INTO l_company_code,l_account_number,l_currency;
1768 CLOSE c_account_details;
1769 IF( NOT CHK_ACCRUAL_ON_RENDER(l_company_code,l_account_number,l_currency,p_balance_date)) THEN
1770 l_return := 'N';
1771 END IF;
1772
1773 END IF;
1774
1775 RETURN l_return;
1776 END CHK_ACCRUAL_ON_RENDER;
1777
1778
1779
1780 FUNCTION CHK_REVAL_ON_RENDER
1781 ( p_ce_bank_account_id IN xtr_bank_accounts.ce_bank_account_id%TYPE
1782 , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1783 )return VARCHAR2 is
1784 l_return VARCHAR2(2);
1785 l_company_code XTR_BANK_ACCOUNTS.party_code%TYPE;
1786 l_account_number XTR_BANK_ACCOUNTS.account_number%TYPE;
1787 l_currency XTR_BANK_ACCOUNTS.currency%TYPE;
1788
1789 CURSOR c_account_details IS
1790 SELECT party_code, account_number,currency
1791 FROM XTR_BANK_ACCOUNTS
1792 WHERE ce_bank_account_id = p_ce_bank_account_id;
1793
1794 BEGIN
1795 l_return := 'Y';
1796
1797 IF(p_ce_bank_account_id is not null and p_balance_date is not null) THEN
1798 OPEN c_account_details;
1799 FETCH c_account_details INTO l_company_code,l_account_number,l_currency;
1800 CLOSE c_account_details;
1801 IF( NOT CHK_REVAL_ON_RENDER(l_company_code,l_account_number,l_currency,p_balance_date)) THEN
1802 l_return := 'N';
1803 END IF;
1804 END IF;
1805 RETURN l_return;
1806 END CHK_REVAL_ON_RENDER;
1807
1808
1809
1810
1811 PROCEDURE LOG_ERR_MSG
1812 ( p_error_code IN VARCHAR2,
1813 p_field_name IN VARCHAR2 DEFAULT NULL,
1814 p_balance_date IN xtr_bank_balances.balance_date%TYPE DEFAULT NULL
1815 )
1816 IS
1817
1818 BEGIN
1819
1820 IF p_error_code = 'XTR_PORTFOLIO' THEN
1821 FND_MESSAGE.Set_Name('XTR','XTR_2208');
1822 FND_MSG_PUB.ADD;
1823
1824 ELSIF p_error_code = 'XTR_INV_PRICING_MODEL' THEN
1825 FND_MESSAGE.Set_Name('XTR','XTR_INV_PRICING_MODEL');
1826 FND_MSG_PUB.ADD;
1827
1828 ELSIF p_error_code = 'CE_NO_SCHED_BANK_ACCT' THEN
1829 FND_MESSAGE.Set_Name('CE','CE_NO_SCHED_BANK_ACCT');
1830 FND_MSG_PUB.ADD;
1831
1832
1833 ELSIF p_error_code = 'XTR_REVAL_ACCRL_BANK' THEN
1834 /* warning has to be raised will look into this later */
1835 FND_MESSAGE.Set_Name ('XTR', 'XTR_REVAL_ACCRL_BANK');
1836 FND_MESSAGE.Set_Token ('DATE',p_balance_date);
1837 FND_MSG_PUB.ADD;
1838
1839 ELSIF p_error_code = 'XTR_BANK_ACCRLS_EXIST' THEN
1840 FND_MESSAGE.Set_Name ('XTR', 'XTR_BANK_ACCRLS_EXIST');
1841 FND_MESSAGE.Set_Token ('DATE',p_balance_date);
1842 FND_MSG_PUB.ADD;
1843
1844 ELSIF p_error_code = 'XTR_BANK_REVAL_DONE' THEN
1845 FND_MESSAGE.Set_Name ('XTR', 'XTR_BANK_REVAL_DONE');
1846 FND_MESSAGE.Set_Token ('DATE',p_balance_date);
1847 FND_MSG_PUB.ADD;
1848
1849 ELSIF p_error_code = 'XTR_ACCT_DELETE' THEN
1850 FND_MESSAGE.Set_Name ('XTR', 'XTR_ACCT_DELETE');
1851 FND_MSG_PUB.ADD;
1852
1853 ELSIF p_error_code = 'XTR_REVAL_ACCRL_DATE' THEN
1854 /* warning has to be raised will look into this later */
1855 FND_MESSAGE.Set_Name ('XTR', 'XTR_REVAL_ACCRL_DATE');
1856 FND_MESSAGE.Set_Token ('DATE',p_balance_date);
1857 FND_MSG_PUB.ADD;
1858
1859 ELSIF p_error_code = 'XTR_CA_REVAL_DONE' THEN
1860 FND_MESSAGE.Set_Name ('XTR', 'XTR_CA_REVAL_DONE');
1861 FND_MSG_PUB.ADD;
1862
1863 ELSIF p_error_code = 'XTR_104' THEN
1864 FND_MESSAGE.Set_Name ( 'XTR','XTR_104');
1865 FND_MSG_PUB.ADD;
1866
1867 ELSIF p_error_code = 'XTR_1237' THEN
1868 FND_MESSAGE.Set_Name ( 'XTR','XTR_1237');
1869 FND_MSG_PUB.ADD;
1870
1871 ELSIF p_error_code = 'XTR_UNEXP_ERROR' THEN
1872 FND_MESSAGE.Set_Name('XTR','XTR_UNEXP_ERROR');
1873 FND_MESSAGE.Set_Token('SQLCODE', p_field_name);
1874 FND_MSG_PUB.ADD; -- Adds the error messages to the list.
1875
1876 ELSIF p_error_code = 'CHK_LOCK' THEN
1877 FND_MESSAGE.Set_Name('XTR','XTR_1999');
1878 FND_MSG_PUB.ADD;
1879
1880
1881 ELSIF p_error_code = 'XTR_INV_PARAM' THEN
1882 FND_MESSAGE.Set_Name('XTR','XTR_INV_PARAM');
1883 FND_MESSAGE.Set_Token('FIELD', p_field_name);
1884 FND_MSG_PUB.ADD;
1885
1886 ELSIF p_error_code = 'XTR_TYPES_CHANGED' THEN
1887 FND_MESSAGE.Set_Name('XTR','XTR_TYPES_CHANGED');
1888 -- FND_MESSAGE.Set_Token('FIELD', p_field_name);
1889 FND_MSG_PUB.ADD;
1890
1891 ELSIF p_error_code = 'XTR_CHANGED_DAYCOUNT_ROUND' THEN
1892 FND_MESSAGE.Set_Name('XTR', 'XTR_CHANGED_DAYCOUNT_ROUND');
1893 -- FND_MESSAGE.Set_Token('FIELD', p_field_name);
1894 FND_MSG_PUB.ADD;
1895
1896
1897
1898
1899 END IF;
1900
1901 END LOG_ERR_MSG;
1902
1903
1904 /* This procedure updates the rounding_type and the day_count_type in the
1905 xtr_bank_balances IF THE same has been updated IN THE xtr_bank_accounts TABLE
1906
1907 This will be called FORM xtr_replicate_bank_accounts API WHEN THE rounding TYPE/
1908 day_count_type has been updated IN THE interest schedules page OR THE
1909 schedule has been changed BY THE USER */
1910
1911
1912 PROCEDURE UPDATE_ROUNDING_DAYCOUNT
1913 (p_ce_bank_account_id xtr_bank_accounts.ce_bank_account_id%TYPE
1914 ,p_rounding_type xtr_bank_accounts.rounding_type%TYPE
1915 ,p_day_count_type xtr_bank_accounts.day_count_type%TYPE
1916 ,x_return_status OUT NOCOPY VARCHAR2
1917 )
1918
1919 IS
1920
1921 l_old_rounding_type xtr_bank_accounts.rounding_type%TYPE;
1922 l_old_day_count_type xtr_bank_accounts.day_count_type%TYPE;
1923 l_day_count_type xtr_bank_accounts.day_count_type%TYPE;
1924 l_party_code xtr_bank_accounts.party_code%TYPE;
1925 l_account_number xtr_bank_accounts.account_number%TYPE;
1926 l_latest_date DATE;
1927 l_oldest_date DATE;
1928 l_acc_status varchar2(20);
1929 l_batch_error varchar2(20);
1930
1931 -- This cursor gets the existing rounding type/day count type from the
1932 -- xtr_bank_accounts table
1933
1934 CURSOR c_old_types IS
1935 SELECT rounding_type, day_count_type, party_code, account_number
1936 FROM XTR_BANK_ACCOUNTS
1937 WHERE ce_bank_account_id = p_ce_bank_account_id;
1938
1939 -- Gets the maximum balance date and the minimum balance date for that
1940 -- company and account
1941 CURSOR c_bal_date IS
1942 SELECT max(balance_date), min(balance_date)
1943 FROM XTR_BANK_BALANCES
1944 WHERE company_code = l_party_code
1945 AND account_number = l_account_number;
1946
1947 -- Checking for record lock
1948 CURSOR c_chk_lock IS
1949 SELECT day_count_type
1950 FROM xtr_bank_balances
1951 WHERE company_code = l_party_code
1952 AND account_number = l_account_number
1953 FOR UPDATE NOWAIT;
1954
1955
1956
1957 BEGIN
1958
1959 x_return_status := FND_API.G_RET_STS_SUCCESS;
1960
1961 OPEN c_old_types;
1962 FETCH c_old_types INTO l_old_rounding_type, l_old_day_count_type,l_party_code,l_account_number;
1963 CLOSE c_old_types;
1964
1965 IF l_old_rounding_type <> p_rounding_type
1966 OR l_old_day_count_type <> p_day_count_type THEN
1967
1968 OPEN c_bal_date;
1969 FETCH c_bal_date INTO l_latest_date, l_oldest_date;
1970
1971 IF c_bal_date%FOUND then
1972
1973 CLOSE c_bal_date;
1974
1975 l_batch_error := chk_int_override(l_party_code,l_account_number, l_oldest_date);
1976 l_acc_status := chk_accrual_int(l_party_code,l_account_number);
1977
1978 OPEN c_chk_lock;
1979 FETCH c_chk_lock INTO l_day_count_type;
1980 IF c_chk_lock%FOUND THEN
1981
1982 CLOSE c_chk_lock;
1983
1984 IF l_batch_error IS NULL THEN
1985
1986 UPDATE xtr_bank_balances
1987 SET rounding_type = p_rounding_type,
1988 day_count_type = p_day_count_type
1989 WHERE company_code =l_party_code
1990 AND account_number = l_account_number;
1991
1992 UPDATE_BANK_ACCOUNT(l_party_code,
1993 l_account_number,
1994 l_oldest_date,
1995 'U' ,
1996 x_return_status );
1997
1998
1999
2000 ELSIF l_batch_error IS NOT NULL AND l_acc_status IS NOT NULL THEN
2001
2002 UPDATE xtr_bank_balances
2003 SET rounding_type = p_rounding_type,
2004 day_count_type = p_day_count_type
2005 WHERE company_code =l_party_code
2006 AND account_number = l_account_number
2007 AND balance_date = l_latest_date;
2008
2009 END IF; -- l_batch_error
2010
2011 ELSE
2012 CLOSE c_chk_lock;
2013
2014 END IF; -- c_chk_lock
2015
2016 ELSE
2017
2018 close c_bal_date;
2019
2020 END IF; -- c_bal_date
2021
2022 END IF;
2023
2024 EXCEPTION
2025 WHEN app_exceptions.RECORD_LOCK_EXCEPTION THEN
2026 IF C_CHK_LOCK%ISOPEN THEN
2027 CLOSE C_CHK_LOCK;
2028 END IF;
2029 LOG_ERR_MSG('CHK_LOCK');
2030 x_return_status := FND_API.G_RET_STS_ERROR;
2031 WHEN others THEN
2032 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2033 LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
2034
2035 END UPDATE_ROUNDING_DAYCOUNT;
2036
2037
2038
2039 FUNCTION CHK_INT_OVERRIDE (
2040 p_party_code xtr_bank_accounts.party_code%type
2041 ,p_account_number xtr_bank_accounts.account_number%type
2042 ,p_oldest_date DATE
2043 ) RETURN VARCHAR2 IS
2044
2045 l_check_status varchar2(1);
2046 l_batch_error varchar2(20);
2047
2048
2049 -- Settlement Check cursor
2050 cursor CHK_SETTLE is
2051 select 'Y'
2052 from xtr_bank_balances
2053 where account_number = p_ACCOUNT_NUMBER
2054 and nvl(interest_settled,0) <> 0;
2055
2056 -- Accrual Check cursor
2057 cursor CHK_ACCRLS is
2058 select 'Y'
2059 from xtr_batches b, xtr_batch_events e
2060 where b.company_code = p_PARTY_CODE
2061 and b.batch_id = e.batch_id
2062 and e.event_code = 'ACCRUAL'
2063 and b.period_end > p_oldest_date;
2064
2065 -- Journal Check cursor
2066 cursor CHK_JNLS is
2067 select 'Y'
2068 from xtr_batches b, xtr_batch_events e
2069 where b.company_code =p_PARTY_CODE
2070 and b.batch_id = e.batch_id
2071 and e.event_code = 'JRNLGN'
2072 and b.period_end > p_oldest_date;
2073
2074 BEGIN
2075
2076 l_check_status := 'N';
2077 l_batch_error := NULL;
2078
2079 /* Check Settlement */
2080 Open CHK_SETTLE;
2081 fetch CHK_SETTLE into l_check_status;
2082 if CHK_SETTLE%FOUND then
2083 l_batch_error := 'SETTLE';
2084 end if;
2085 close CHK_SETTLE;
2086
2087 IF l_batch_error is NULL then
2088 /* Check Accruals */
2089 open CHK_ACCRLS;
2090 fetch CHK_ACCRLS into l_check_status;
2091 if CHK_ACCRLS%FOUND then
2092 l_batch_error := 'ACCRUE';
2093 END IF;
2094 close CHK_ACCRLS;
2095 END IF;
2096
2097 IF l_batch_error is NULL then
2098 /* Check Journals */
2099 open CHK_JNLS;
2100 fetch CHK_JNLS into l_check_status;
2101 if CHK_JNLS%FOUND then
2102 l_batch_error := 'JOURL';
2103 END IF;
2104 close CHK_JNLS;
2105
2106 END IF;
2107 RETURN l_batch_error;
2108 END CHK_INT_OVERRIDE;
2109
2110
2111
2112 FUNCTION CHK_ACCRUAL_INT (
2113 p_party_code xtr_bank_accounts.party_code%type
2114 ,p_account_number xtr_bank_accounts.account_number%type
2115 )RETURN varchar2 IS
2116
2117 l_acc_int number;
2118 l_acc_status varchar2(20);
2119
2120 cursor CHK_ACC_INT is
2121 select accum_int_cfwd
2122 from xtr_bank_balances
2123 where account_number = p_ACCOUNT_NUMBER
2124 and company_code = p_PARTY_CODE
2125 and balance_date = (select max(balance_date)
2126 from xtr_bank_balances
2127 where account_number = p_Account_Number
2128 and company_code = p_PARTY_CODE);
2129
2130 BEGIN
2131 l_acc_status := null;
2132
2133 open CHK_ACC_INT;
2134 fetch CHK_ACC_INT into l_acc_int;
2135
2136 if CHK_ACC_INT%FOUND then
2137
2138 if nvl(l_acc_int ,0) = 0 then
2139 l_acc_status :='ZERO';
2140 end if;
2141
2142 end if;
2143 close CHK_ACC_INT;
2144 RETURN l_acc_status;
2145 END CHK_ACCRUAL_INT;
2146
2147
2148 PROCEDURE CHK_ACCRUAL_REVAL_WARNINGS
2149 (p_ce_bank_account_id IN xtr_bank_accounts.ce_bank_account_id%TYPE
2150 ,p_balance_date IN xtr_bank_balances.balance_date%TYPE
2151 ,p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE default null
2152 ,p_interest_calc_balance IN NUMBER
2153 ,p_balance_cflow IN xtr_bank_balances.balance_cflow%TYPE
2154 ,p_action_flag IN VARCHAR2
2155 ,x_return_status OUT NOCOPY VARCHAR2
2156 ,x_msg_count OUT NOCOPY NUMBER
2157 ,x_msg_data OUT NOCOPY VARCHAR2 )IS
2158
2159 CURSOR c_bank_acct_details IS
2160 SELECT party_code,account_number,currency
2161 FROM XTR_BANK_ACCOUNTS
2162 WHERE ce_bank_account_id = p_ce_bank_account_id;
2163
2164 l_company_code xtr_bank_accounts.party_code%TYPE;
2165 l_account_number xtr_bank_accounts.account_number%TYPE;
2166 l_currency xtr_bank_accounts.currency%TYPE;
2167 l_return_error VARCHAR2(30);
2168
2169 BEGIN
2170 FND_MSG_PUB.Initialize;
2171
2172 OPEN c_bank_acct_details;
2173 FETCH c_bank_acct_details INTO l_company_code,l_account_number,l_currency;
2174 IF c_bank_acct_details%FOUND THEN
2175 CLOSE c_bank_acct_details;
2176
2177 l_return_error := chk_reval( l_company_code,
2178 l_account_number,
2179 l_currency,
2180 p_balance_date,
2181 p_ce_bank_account_balance_id,
2182 p_balance_cflow,
2183 p_action_flag,
2184 'W');
2185 IF nvl(l_return_error, '$$$') = 'XTR_DEALS_BEFORE_REVAL' THEN
2186 x_return_status := FND_API.G_RET_STS_ERROR;
2187 LOG_ERR_MSG ( 'XTR_DEALS_BEFORE_REVAL',p_balance_date);
2188 END IF;
2189
2190 l_return_error := chk_accrual(l_company_code,
2191 l_account_number,
2192 l_currency,
2193 p_balance_date,
2194 p_ce_bank_account_balance_id,
2195 p_interest_calc_balance,
2196 p_action_flag,
2197 'W');
2198
2199 IF nvl(l_return_error, '$$$') = 'XTR_DEALS_BEFORE_ACCRUAL' THEN
2200 x_return_status := FND_API.G_RET_STS_ERROR;
2201 LOG_ERR_MSG ( 'XTR_DEALS_BEFORE_ACCRUAL',p_balance_date);
2202 END IF;
2203
2204 ELSE
2205 CLOSE c_bank_acct_details;
2206 x_return_status := FND_API.G_RET_STS_ERROR;
2207 LOG_ERR_MSG('XTR_INV_PARAM','ACTION_FLAG');
2208 END IF;
2209
2210 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
2211 ( p_count => x_msg_count ,
2212 p_data => x_msg_data
2213 );
2214
2215 EXCEPTION
2216
2217 WHEN others THEN
2218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2219 LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
2220 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
2221 ( p_count => x_msg_count ,
2222 p_data => x_msg_data
2223 );
2224
2225 END CHK_ACCRUAL_REVAL_WARNINGS;
2226
2227 END XTR_REPLICATE_BANK_BALANCES;
2228
[Home] [Help]