1 PACKAGE BODY XTR_REPLICATE_BANK_BALANCES AS
2 /* | $Header: xtrbbalb.pls 120.20.12020000.2 2013/01/17 06:25:21 rtumati ship $ | */
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 := nvl(p_ledger_balance,0);
258 l_xtr_bank_balances_rec.balance_adjustment := (nvl(p_interest_calculated_balance,0) - nvl(p_ledger_balance,0));
259 l_xtr_bank_balances_rec.balance_cflow := nvl(p_available_balance,0);
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, (NVL(p_balance_rec.STATEMENT_BALANCE,0)+NVL(p_balance_rec.BALANCE_ADJUSTMENT,0)) ,l_new_rate);
527 IF l_new_rate IS NULL THEN
528 l_new_rate := 0;
529 END IF;
530
531 INSERT INTO XTR_BANK_BALANCES
532 ( company_code
533 ,account_number
534 ,balance_date
535 ,no_of_days
536 ,statement_balance
537 ,balance_adjustment
538 ,balance_cflow
539 ,accum_int_bfwd
540 ,interest
541 ,interest_rate
542 ,interest_settled
543 ,interest_settled_hce
544 ,accum_int_cfwd
545 ,limit_code
546 ,created_on
547 ,created_by
548 ,accrual_interest
549 ,rounding_type
550 ,day_count_type
551 ,original_amount
552 ,one_day_float
553 ,two_day_float
554 ,ce_bank_account_balance_id)
555 VALUES
556 ( p_balance_rec.company_code
557 ,p_balance_rec.account_number
558 ,p_balance_rec.balance_date
559 ,l_no_days
560 ,p_balance_rec.statement_balance
561 ,p_balance_rec.balance_adjustment
562 ,p_balance_rec.balance_cflow
563 ,l_int_bf
564 ,nvl(l_interest,0)
565 ,l_new_rate
566 ,0
567 ,0
568 ,l_int_cf
569 ,decode(sign(nvl(p_balance_rec.statement_balance,0)), -1,l_fund_limit_code,l_invest_limit_code)
570 ,sysdate
571 ,fnd_global.user_id
572 ,l_accrual_int
573 ,l_rounding_type
574 ,l_day_count_type
575 ,l_original_amount
576 ,p_balance_rec.one_day_float
577 ,p_balance_rec.two_day_float
578 ,p_balance_rec.ce_bank_account_balance_id);
579
580 ELSE
581 CLOSE BANK_ACCT_DETAILS;
582 LOG_ERR_MSG('XTR_INV_PARAM', p_balance_rec.ce_bank_account_balance_id);
583 x_return_status := FND_API.G_RET_STS_ERROR;
584
585 END IF;
586
587 EXCEPTION
588
589 WHEN others THEN
590 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
591 LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
592
593 END INSERT_BANK_BALANCE ;
594
595
596
597
598 PROCEDURE UPDATE_BANK_BALANCE
599 ( p_balance_rec IN xtr_bank_balances%ROWTYPE,
600 x_return_status IN OUT NOCOPY VARCHAR2
601 )
602 IS
603
604 CURSOR c_chk_lock IS
605 SELECT ce_bank_account_balance_id
606 FROM xtr_bank_balances
607 WHERE company_code = p_balance_rec.company_code
608 AND account_number = p_balance_rec.account_number
609 AND ce_bank_account_balance_id = p_balance_rec.ce_bank_account_balance_id
610 FOR UPDATE NOWAIT;
611
612
613 l_ce_bank_acct_bal_id xtr_bank_balances.ce_bank_account_balance_id%TYPE;
614
615
616 BEGIN
617 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
618 x_return_status := FND_API.G_RET_STS_SUCCESS;
619 OPEN c_chk_lock;
620 FETCH c_chk_lock INTO l_ce_bank_acct_bal_id;
621 IF c_chk_lock%FOUND THEN
622
623 CLOSE c_chk_lock;
624
625 UPDATE xtr_bank_balances SET
626 statement_balance = nvl(p_balance_rec.statement_balance,0)
627 ,balance_cflow = nvl(p_balance_rec.balance_cflow,0)
628 ,one_day_float = nvl(p_balance_rec.one_day_float,0)
629 ,two_day_float = nvl(p_balance_rec.two_day_float,0)
630 ,balance_adjustment = p_balance_rec.balance_adjustment
631 ,balance_date = p_balance_rec.balance_date
632 ,updated_by = fnd_global.user_id
633 ,updated_on = sysdate
634 WHERE company_code = p_balance_rec.company_code
635 AND account_number = p_balance_rec.account_number
636 AND ce_bank_account_balance_id = p_balance_rec.ce_bank_account_balance_id;
637
638 ELSE
639 CLOSE c_chk_lock;
640 END IF;
641
642
643 EXCEPTION
644 WHEN app_exceptions.RECORD_LOCK_EXCEPTION THEN
645 IF C_CHK_LOCK%ISOPEN THEN
646 CLOSE C_CHK_LOCK;
647 END IF;
648 LOG_ERR_MSG('CHK_LOCK');
649 x_return_status := FND_API.G_RET_STS_ERROR;
650 WHEN others THEN
651 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
652 LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
653
654 END UPDATE_BANK_BALANCE;
655
656
657
658 PROCEDURE DELETE_BANK_BALANCE
659 ( p_balance_rec IN xtr_bank_balances%ROWTYPE,
660 x_return_status IN OUT NOCOPY VARCHAR2
661 )
662 IS
663
664 l_cross_ref xtr_party_info.cross_ref_to_other_party%TYPE;
665 l_exists VARCHAR2(1);
666 l_ce_bank_acct_bal_id xtr_bank_balances.ce_bank_account_balance_id%TYPE;
667 l_ccy xtr_bank_accounts.currency%TYPE;
668
669 -- Finding the subsidiary for the company
670 CURSOR C_CROSS_REF IS
671 SELECT CROSS_REF_TO_OTHER_PARTY
672 FROM XTR_PARTIES_V
673 WHERE PARTY_CODE = p_balance_rec.company_code;
674
675
676 -- Finding the currency for the account number and company combination
677 CURSOR C_CURRENCY IS
678 SELECT CURRENCY
679 FROM XTR_BANK_ACCOUNTS
680 WHERE PARTY_CODE = P_BALANCE_REC.COMPANY_CODE
681 AND ACCOUNT_NUMBER = P_BALANCE_REC.ACCOUNT_NUMBER;
682
683
684 -- Checking whether the row exists in the DDA table
685 CURSOR C_BAL_SETTLED IS
686 SELECT 'Y'
687 FROM xtr_deal_date_amounts
688 WHERE DEAL_TYPE = 'CA'
689 AND AMOUNT_TYPE = 'INTSET'
690 AND ACCOUNT_NO = p_balance_rec.account_number
691 AND CURRENCY = l_ccy
692 AND COMPANY_CODE = nvl(l_cross_ref,p_balance_rec.company_code)
693 AND AMOUNT_DATE = p_balance_rec.balance_date;
694
695 -- Checking the lock on the xtr_bank_balances table
696 CURSOR c_chk_acct_lock IS
697 SELECT ce_bank_account_balance_id
698 FROM xtr_bank_balances
699 WHERE company_code = p_balance_rec.company_code
700 AND account_number = p_balance_rec.account_number
701 AND ce_bank_account_balance_id = p_balance_rec.ce_bank_account_balance_id
702 FOR UPDATE NOWAIT;
703
704 -- Checking the lock on the xtr_deal_date_amounts table
705 CURSOR c_chk_dda_lock IS
706 SELECT ce_bank_account_balance_id
707 FROM xtr_bank_balances
708 WHERE company_code = p_balance_rec.company_code
709 AND account_number = p_balance_rec.account_number
710 AND ce_bank_account_balance_id = p_balance_rec.ce_bank_account_balance_id
711 FOR UPDATE NOWAIT;
712
713
714 BEGIN
715 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
716 x_return_status := FND_API.G_RET_STS_SUCCESS;
717 OPEN c_chk_acct_lock;
718 FETCH c_chk_acct_lock INTO l_ce_bank_acct_bal_id;
719 IF c_chk_acct_lock%FOUND THEN
720
721 CLOSE c_chk_acct_lock;
722
723 OPEN C_CROSS_REF;
724 FETCH C_CROSS_REF INTO l_cross_ref;
725 CLOSE C_CROSS_REF;
726
727 DELETE FROM XTR_BANK_BALANCES
728 WHERE company_code = p_balance_rec.company_code
729 AND account_number = p_balance_rec.account_number
730 AND balance_date = p_balance_rec.balance_date
731 AND ce_bank_account_balance_id = p_balance_rec.ce_bank_account_balance_id;
732
733
734 ELSE
735 CLOSE c_chk_acct_lock;
736
737 END IF;
738
739 OPEN C_CURRENCY;
740 FETCH C_CURRENCY INTO l_ccy;
741 CLOSE C_CURRENCY;
742
743 OPEN C_bal_settled;
744 FETCH C_bal_settled INTO l_exists;
745
746 OPEN c_chk_dda_lock;
747 FETCH c_chk_dda_lock INTO l_ce_bank_acct_bal_id;
748 IF c_chk_dda_lock%FOUND THEN
749
750 CLOSE c_chk_dda_lock;
751
752 IF c_bal_settled%FOUND THEN
753
754 DELETE FROM XTR_DEAL_DATE_AMOUNTS_V
755 WHERE DEAL_TYPE = 'CA'
756 AND AMOUNT_TYPE = 'INTSET'
757 AND ACCOUNT_NO = p_balance_rec.account_number
758 AND CURRENCY = l_ccy
759 AND COMPANY_CODE = nvl(l_cross_ref,p_balance_rec.company_code)
760 AND AMOUNT_DATE = p_balance_rec.balance_date;
761
762 END IF;
763 CLOSE c_bal_settled;
764 ELSE
765 CLOSE c_chk_dda_lock;
766
767 END IF;
768
769
770 EXCEPTION
771 WHEN app_exceptions.RECORD_LOCK_EXCEPTION THEN
772
773 IF C_CHK_acct_LOCK%ISOPEN THEN
774 CLOSE C_CHK_acct_LOCK;
775 END IF;
776
777 IF C_CHK_DDA_LOCK%ISOPEN THEN
778 CLOSE C_CHK_DDA_LOCK;
779 END IF;
780
781 LOG_ERR_MSG('CHK_LOCK');
782 x_return_status := FND_API.G_RET_STS_ERROR;
783
784
785 WHEN others THEN
786
787 LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
788 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
789
790 END DELETE_BANK_BALANCE;
791
792
793
794 PROCEDURE VALIDATE_BANK_BALANCE
795 ( p_company_code IN xtr_bank_balances.company_code%TYPE,
796 p_account_number IN xtr_bank_balances.account_number%TYPE,
797 p_balance_date IN xtr_bank_balances.balance_date%TYPE,
798 p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE DEFAULT NULL,
799 p_interest_calc_balance IN NUMBER,
800 p_available_balance IN NUMBER,
801 p_action_flag IN VARCHAR2,
802 x_return_status OUT NOCOPY VARCHAR2
803 )
804 IS
805
806 l_portfolio_code xtr_bank_accounts.portfolio_code%TYPE;
807 l_currency xtr_bank_accounts.currency%TYPE;
808 l_bank_code xtr_bank_accounts.bank_code%TYPE;
809 l_result BOOLEAN;
810 l_duplicate NUMBER;
811 l_return_error VARCHAR2(30);
812 l_authorised VARCHAR2(1);
813 l_ce_bank_account_id xtr_bank_accounts.ce_bank_account_id%TYPE ;
814 l_int_schedule_id ce_bank_accounts.interest_schedule_id%TYPE ;
815 l_pricing_model xtr_bank_accounts.pricing_model%TYPE;
816
817
818 -- Check whether the portfolio code exists for the bank account for which balacnes
819 -- are to be entered
820 CURSOR C_PORTFOLIO IS
821 SELECT PORTFOLIO_CODE, CURRENCY , BANK_CODE, PRICING_MODEL,
822 ce_bank_account_id /* Bug 5346243 */
823 FROM xtr_bank_accounts
824 WHERE ACCOUNT_NUMBER = p_account_number
825 AND PARTY_CODE = p_company_code;
826
827
828 -- Check whether the balance does not already exist for
829 CURSOR C_DUPLICATE_DATE IS
830 SELECT 1
831 FROM XTR_BANK_BALANCES_V A,
832 XTR_BANK_ACCOUNTS_V B
833 WHERE A.BALANCE_DATE = p_balance_date
834 AND A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER
835 AND A.COMPANY_CODE = B.PARTY_CODE
836 AND B.CURRENCY = l_currency
837 AND B.ACCOUNT_NUMBER = p_account_number
838 AND B.BANK_CODE = l_bank_code
839 AND B.PARTY_CODE = p_company_code;
840
841
842 cursor c_pm_authorized is
843 select 1
844 from xtr_price_models_v
845 where deal_type = 'CA'
846 and code = l_pricing_model
847 and authorized = 'Y';
848
849 /* Bug 5346243 */
850
851 Cursor c_int_schedule ( l_ce_bank_account_id Number ) Is
852 Select interest_schedule_id
853 From ce_bank_accounts
854 Where bank_account_id = l_ce_bank_account_id ;
855
856
857
858 BEGIN
859
860 x_return_status := FND_API.G_RET_STS_SUCCESS;
861
862 OPEN c_portfolio;
863 FETCH c_portfolio INTO l_portfolio_code, l_currency,l_bank_code, l_pricing_model, l_ce_bank_account_id ;
864
865 -- Validation at the time of insertion/updation of the record
866
867
868 IF p_action_flag IN ( 'I' , 'U') THEN
869
870 IF p_action_flag = 'I' AND (l_portfolio_code Is null) THEN
871 -- Checking whether the portfolio code exists for the bank account
872 x_return_status := FND_API.G_RET_STS_ERROR;
873 LOG_ERR_MSG ( 'XTR_PORTFOLIO');
874 END IF;
875
876 CLOSE c_portfolio;
877
878 -- Checking whether the balance date is greater than sysdate
879 IF p_balance_date > sysdate THEN
880 x_return_status := FND_API.G_RET_STS_ERROR;
881 LOG_ERR_MSG ( 'XTR_104');
882 END IF;
883
884 -- Checking whether an interest schedule is assigned to the bank account
885 /* Bug 5346243 */
886 Open c_int_schedule(l_ce_bank_account_id) ;
887 fetch c_int_schedule into l_int_schedule_id ;
888 close c_int_schedule ;
889
890 If l_int_schedule_id is Null Then
891 x_return_status := FND_API.G_RET_STS_ERROR;
892 LOG_ERR_MSG ( 'CE_NO_SCHED_BANK_ACCT');
893 End If ;
894
895 -- Checking whether the pricing model is authorised
896 If l_pricing_model Is Not Null Then
897 OPEN c_pm_authorized;
898 FETCH c_pm_authorized into l_authorised;
899 if c_pm_authorized%NOTFOUND then
900 x_return_status := FND_API.G_RET_STS_ERROR;
901 LOG_ERR_MSG ( 'XTR_INV_PRICING_MODEL');
902 end if;
903 close c_pm_authorized;
904 End If ;
905
906 -- Checking the balance does not exist for the date being entered by the user
907 OPEN c_duplicate_date;
908 FETCH c_duplicate_date INTO l_duplicate;
909 IF c_duplicate_date%FOUND AND p_action_flag = 'I' THEN
910 x_return_status := FND_API.G_RET_STS_ERROR;
911 LOG_ERR_MSG ( 'XTR_1237');
912 END IF;
913 CLOSE c_duplicate_date;
914 -- Checking for interest includes/ interest rounding
915 IF ((NOT CHK_ROUNDING_CHANGE ( p_company_code ,p_account_number ,p_balance_date )) AND p_interest_calc_balance IS NOT NULL) THEN
916 x_return_status := FND_API.G_RET_STS_ERROR;
917 LOG_ERR_MSG ( 'XTR_TYPES_CHANGED');
918 END IF;
919
920 -- Checking for revaluations
921
922 l_return_error := chk_reval( p_company_code,
923 p_account_number,
924 l_currency,
925 p_balance_date,
926 p_ce_bank_account_balance_id,
927 p_available_balance,
928 p_action_flag,
929 'E');
930
931
932 IF nvl(l_return_error, 'XXX') = 'XTR_CA_REVAL_DONE' THEN
933 x_return_status := FND_API.G_RET_STS_ERROR;
934 LOG_ERR_MSG ( 'XTR_CA_REVAL_DONE');
935 ELSIF nvl(l_return_error, 'XXX') = 'XTR_REVAL_ACCRL_DATE' THEN
936 x_return_status := FND_API.G_RET_STS_ERROR;
937 LOG_ERR_MSG ( 'XTR_REVAL_ACCRL_DATE',p_balance_date);
938 ELSIF nvl(l_return_error, 'XXX') = 'XTR_BANK_REVAL_DONE' THEN
939 x_return_status := FND_API.G_RET_STS_ERROR;
940 LOG_ERR_MSG ( 'XTR_BANK_REVAL_DONE',p_balance_date);
941 END IF;
942
943
944 -- Checking for accruals
945
946 l_return_error := chk_accrual( p_company_code,
947 p_account_number,
948 l_currency,
949 p_balance_date,
950 p_ce_bank_account_balance_id,
951 p_interest_calc_balance,
952 p_action_flag,
953 'E');
954
955 IF nvl(l_return_error, 'XXX') = 'XTR_BANK_ACCRLS_EXIST' THEN
956 x_return_status := FND_API.G_RET_STS_ERROR;
957 LOG_ERR_MSG ( 'XTR_BANK_ACCRLS_EXIST',p_balance_date);
958
959 ELSIF nvl(l_return_error, 'XXX') = 'XTR_REVAL_ACCRL_DATE' THEN
960 x_return_status := FND_API.G_RET_STS_ERROR;
961 LOG_ERR_MSG ( 'XTR_REVAL_ACCRL_DATE',p_balance_date);
962 END IF;
963
964 ELSIF p_action_flag = 'D' THEN
965
966 CLOSE c_portfolio;
967
968
969 -- Checking for interest includes/ interest rounding
970
971 IF ((NOT CHK_ROUNDING_CHANGE( p_company_code ,p_account_number ,p_balance_date )) AND p_interest_calc_balance IS NOT NULL) THEN
972 x_return_status := FND_API.G_RET_STS_ERROR;
973 LOG_ERR_MSG ( 'XTR_CHANGED_DAYCOUNT_ROUND');
974 END IF;
975
976 -- Checking for revaluations
977 l_return_error := chk_reval( p_company_code,
978 p_account_number,
979 l_currency,
980 p_balance_date,
981 p_ce_bank_account_balance_id,
982 p_available_balance,
983 'D',
984 'E');
985
986 IF nvl(l_return_error, 'XXX') = 'XTR_ACCT_DELETE' THEN
987 x_return_status := FND_API.G_RET_STS_ERROR;
988 LOG_ERR_MSG ( 'XTR_ACCT_DELETE');
989 END IF;
990
991
992 -- Checking for accruals
993 l_return_error := chk_accrual( p_company_code,
994 p_account_number,
995 l_currency,
996 p_balance_date,
997 p_ce_bank_account_balance_id,
998 p_interest_calc_balance,
999 'D',
1000 'E');
1001
1002 IF nvl(l_return_error, 'XXX') = 'XTR_DEAL_ACCRLS_EXIST' THEN
1003 x_return_status := FND_API.G_RET_STS_ERROR;
1004 LOG_ERR_MSG ( 'XTR_DEAL_ACCRLS_EXIST',p_balance_date);
1005 END IF;
1006
1007 END IF ; -- p_action_flag
1008
1009 EXCEPTION
1010 WHEN others THEN
1011
1012 LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
1013 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1014
1015 END VALIDATE_BANK_BALANCE;
1016
1017
1018 PROCEDURE VALIDATE_BANK_BALANCE
1019 ( p_company_code IN xtr_bank_balances.company_code%TYPE,
1020 p_account_number IN xtr_bank_balances.account_number%TYPE,
1021 p_balance_date IN xtr_bank_balances.balance_date%TYPE,
1022 p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE default null,
1023 p_interest_calc_balance IN NUMBER,
1024 p_available_balance in NUMBER,
1025 p_action_flag IN VARCHAR2,
1026 x_return_status OUT NOCOPY VARCHAR2,
1027 x_msg_count OUT NOCOPY NUMBER,
1028 x_msg_data OUT NOCOPY VARCHAR2) IS
1029
1030 CURSOR C_BALANCE_DETAILS IS
1031 SELECT company_code,account_number,balance_date,statement_balance
1032 ,balance_adjustment,balance_cflow,ce_bank_account_balance_id
1033 FROM XTR_BANK_BALANCES
1034 WHERE CE_BANK_ACCOUNT_BALANCE_ID = p_ce_bank_account_balance_id;
1035
1036
1037 l_balance_date_updated BOOLEAN;
1038 l_balance_rec XTR_BANK_BALANCES%ROWTYPE;
1039
1040 BEGIN
1041
1042 x_return_status := FND_API.G_RET_STS_SUCCESS;
1043 l_balance_date_updated := FALSE;
1044 FND_MSG_PUB.Initialize;
1045 IF(p_action_flag in ('U','D')) THEN
1046
1047 OPEN C_BALANCE_DETAILS;
1048 FETCH C_BALANCE_DETAILS INTO l_balance_rec.company_code,l_balance_rec.account_number
1049 ,l_balance_rec.balance_date,l_balance_rec.statement_balance,l_balance_rec.balance_adjustment
1050 ,l_balance_rec.balance_cflow,l_balance_rec.ce_bank_account_balance_id;
1051 CLOSE C_BALANCE_DETAILS;
1052
1053
1054 IF(nvl(p_balance_date,sysdate) <> nvl(l_balance_rec.balance_date,sysdate)
1055 AND p_balance_date is not null AND l_balance_rec.balance_date is not null
1056 AND p_action_flag = 'U') THEN
1057
1058 l_balance_date_updated := TRUE;
1059
1060 END IF;
1061
1062 END IF;
1063
1064 IF(NOT l_balance_date_updated) THEN -- Balance date is not updated
1065 IF (p_action_flag in ('I','U') )THEN
1066 VALIDATE_BANK_BALANCE(
1067 p_company_code,
1068 p_account_number,
1069 p_balance_date,
1070 p_ce_bank_account_balance_id,
1071 p_interest_calc_balance,
1072 p_available_balance,
1073 p_action_flag,
1074 x_return_status );
1075 END IF;
1076 IF (p_action_flag in ('D')) THEN
1077 VALIDATE_BANK_BALANCE(
1078 l_balance_rec.company_code,
1079 l_balance_rec.account_number,
1080 l_balance_rec.balance_date,
1081 p_ce_bank_account_balance_id,
1082 l_balance_rec.statement_balance+l_balance_rec.balance_adjustment,
1083 l_balance_rec.balance_cflow,
1084 'D',
1085 x_return_status );
1086 END IF;
1087
1088 ELSIF(l_balance_date_updated) THEN
1089
1090 VALIDATE_BANK_BALANCE(
1091 p_company_code,
1092 p_account_number,
1093 p_balance_date,
1094 p_ce_bank_account_balance_id,
1095 p_interest_calc_balance,
1096 p_available_balance,
1097 'D',
1098 x_return_status );
1099
1100 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1101 VALIDATE_BANK_BALANCE(
1102 p_company_code,
1103 p_account_number,
1104 p_balance_date,
1105 p_ce_bank_account_balance_id,
1106 p_interest_calc_balance,
1107 p_available_balance,
1108 'I',
1109 x_return_status );
1110 END IF;
1111
1112
1113 END IF;
1114
1115 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
1116 ( p_count => x_msg_count ,
1117 p_data => x_msg_data
1118 );
1119
1120
1121 EXCEPTION
1122
1123 WHEN others THEN
1124 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1125 LOG_ERR_MSG ('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
1126
1127 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
1128 ( p_count => x_msg_count ,
1129 p_data => x_msg_data
1130 );
1131
1132 END VALIDATE_BANK_BALANCE;
1133
1134
1135
1136
1137 FUNCTION CHK_ACCRUAL ( p_company_code IN xtr_bank_balances.company_code%TYPE
1138 , p_account_number IN xtr_bank_balances.account_number%TYPE
1139 , p_currency IN xtr_bank_accounts.currency%TYPE
1140 , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1141 , p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE DEFAULT NULL
1142 , p_interest_calc_balance IN NUMBER
1143 , p_action_flag IN VARCHAR2
1144 , p_val_type IN VARCHAR2)
1145 RETURN VARCHAR2
1146 IS
1147
1148 l__val_type varchar2(1);
1149 l_error VARCHAR2(50);
1150 l_accrual_deal_date DATE;
1151 l_accrual_batch_date DATE;
1152 l_interest_calc_balance NUMBER;
1153
1154 -- Check whether the accruals have been run for that particular deal
1155 CURSOR c_accrl_deal IS
1156 SELECT max(period_to)
1157 FROM
1158 xtr_bank_balances bb, xtr_bank_accounts ba,
1159 xtr_deal_date_amounts dd, xtr_accrls_amort aa
1160 WHERE bb.company_code = p_company_code
1161 AND bb.account_number = p_account_number
1162 AND bb.company_code = ba.party_code
1163 AND bb.account_number = ba.account_number
1164 AND ba.currency = p_currency
1165 AND bb.company_code = dd.company_code
1166 AND bb.account_number = dd.account_no
1167 AND ba.currency = dd.currency
1168 AND dd.deal_number = aa.deal_no;
1169
1170 -- Check whether the accruals have been run for period greater than the
1171 -- balance date entered by the user
1172 CURSOR c_accrl_comp IS
1173 SELECT max(period_end)
1174 FROM
1175 xtr_batches b, xtr_batch_events e
1176 WHERE b.company_code = p_company_code
1177 AND b.batch_id = e.batch_id
1178 AND e.event_code = 'ACCRUAL'
1179 AND b.period_end >=p_balance_date;
1180
1181
1182 -- Getting the interest calc amount from the database for the updated record
1183 CURSOR c_accrl_amount IS
1184 SELECT BALANCE_ADJUSTMENT + STATEMENT_BALANCE
1185 FROM
1186 xtr_bank_balances bb
1187 WHERE
1188 bb.company_code = p_company_code
1189 AND bb.account_number = p_account_number
1190 AND ce_bank_account_balance_id = p_ce_bank_account_balance_id;
1191
1192
1193
1194 BEGIN
1195 IF p_action_flag IN ('D','U') THEN
1196
1197 OPEN c_accrl_deal;
1198 FETCH c_accrl_deal INTO l_accrual_deal_date;
1199 CLOSE c_accrl_deal;
1200
1201 IF l_accrual_deal_date IS NOT NULL AND l_accrual_deal_date >= p_balance_date THEN
1202
1203 IF p_action_flag = 'U' THEN
1204
1205 OPEN c_accrl_amount;
1206 FETCH c_accrl_amount INTO l_interest_calc_balance;
1207 CLOSE c_accrl_amount;
1208
1209 IF l_interest_calc_balance <> p_interest_calc_balance
1210 AND p_val_type = 'E' THEN
1211 l_error := 'XTR_DEAL_ACCRLS_EXIST';
1212
1213 END IF;
1214
1215 ELSIF p_action_flag = 'D' AND p_val_type = 'E'THEN
1216 l_error := 'XTR_DEAL_ACCRLS_EXIST';
1217 END IF;
1218 END IF;
1219 ELSIF (p_action_flag = 'I' AND nvl(p_interest_calc_balance,0) <> 0) THEN
1220 OPEN c_accrl_deal;
1221 FETCH c_accrl_deal INTO l_accrual_deal_date;
1222 CLOSE c_accrl_deal;
1223 IF l_accrual_deal_date IS NULL THEN
1224 OPEN c_accrl_comp;
1225 FETCH c_accrl_comp INTO l_accrual_batch_date;
1226 CLOSE c_accrl_comp;
1227
1228 IF l_accrual_batch_date IS NOT NULL
1229 AND l_accrual_batch_date >= p_balance_date AND p_val_type = 'W' THEN
1230 l_error := 'XTR_DEALS_BEFORE_ACCRUAL'; -- Warning message
1231
1232 END IF;
1233 ELSIF l_accrual_deal_date IS NOT NULL
1234 AND l_accrual_deal_date >= p_balance_date AND p_val_type = 'E' THEN
1235
1236 l_error := 'XTR_DEALS_ACCRLS_EXIST';
1237
1238 END IF;
1239
1240
1241 END IF; -- p_action_flag
1242 RETURN l_error;
1243 END CHK_ACCRUAL;
1244
1245
1246
1247 -- This function checks whether the revaluations have been run at the time of
1248 -- insertion/updation/deletion
1249
1250 FUNCTION CHK_REVAL ( p_company_code IN xtr_bank_balances.company_code%TYPE
1251 , p_account_number IN xtr_bank_balances.account_number%TYPE
1252 , p_currency IN xtr_bank_accounts.currency%TYPE
1253 , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1254 , p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE DEFAULT NULL
1255 , p_balance_cflow IN xtr_bank_balances.balance_cflow%TYPE
1256 , p_action_flag IN VARCHAR2
1257 , p_val_type IN VARCHAR2 )
1258 RETURN VARCHAR2
1259 IS
1260
1261 l_error VARCHAR2(50);
1262 l_reval_deal_date DATE;
1263 l_reval_batch_date DATE;
1264 l_reval_delete_date DATE;
1265 l_balance_cflow xtr_bank_balances.balance_cflow%TYPE;
1266
1267
1268 -- Check whether the revaluations have been run for period greater than the
1269 -- balance date entered by the user
1270 CURSOR c_reval_comp IS
1271 SELECT max(period_end)
1272 FROM
1273 xtr_batches b,xtr_batch_events e
1274 WHERE
1275 b.company_code = p_company_code
1276 AND b.batch_id = e.batch_id
1277 AND e.event_code = 'REVAL'
1278 AND b.period_end >= p_balance_date;
1279
1280
1281 CURSOR c_deal_delete IS
1282 SELECT max(bb.balance_date)
1283 FROM
1284 xtr_bank_balances bb,xtr_bank_accounts ba
1285 WHERE
1286 bb.company_code = p_company_code
1287 AND bb.account_number = p_account_number
1288 AND bb.company_code = ba.party_code
1289 AND bb.account_number = ba.account_number
1290 AND ba.currency = p_currency
1291 AND bb.first_batch_id IS NOT NULL;
1292
1293
1294 -- Check whether the revaluations have been run for that particular deal
1295 CURSOR c_reval_deal IS
1296 SELECT max(period_to)
1297 FROM
1298 xtr_bank_balances bb,xtr_bank_accounts ba,
1299 xtr_revaluation_details rd
1300 WHERE
1301 bb.company_code = p_company_code
1302 AND bb.account_number = p_account_number
1303 AND bb.company_code = ba.party_code
1304 AND bb.account_number = ba.account_number
1305 AND ba.currency = p_currency
1306 AND bb.account_number = rd.account_no
1307 AND rd.deal_type = 'CA';
1308
1309 -- Getting the cashflow amount from the database for the updated record
1310 CURSOR c_reval_amount IS
1311 SELECT BALANCE_CFLOW
1312 FROM
1313 xtr_bank_balances bb
1314 WHERE
1315 bb.company_code = p_company_code
1316 AND bb.account_number = p_account_number
1317 AND ce_bank_account_balance_id = p_ce_bank_account_balance_id;
1318
1319
1320
1321 BEGIN
1322 -- checking at time of updation of balance
1323 IF p_action_flag = 'U' THEN
1324
1325 OPEN c_reval_deal;
1326 FETCH c_reval_deal INTO l_reval_deal_date;
1327 CLOSE c_reval_deal;
1328
1329 IF l_reval_deal_date IS NOT NULL AND l_reval_deal_date >= p_balance_date THEN
1330 -- checks whether the changed amount is same as the database amount
1331 OPEN c_reval_amount;
1332 FETCH c_reval_amount INTO l_balance_cflow;
1333 CLOSE c_reval_amount;
1334 IF (l_balance_cflow <> p_balance_cflow AND p_val_type = 'E')THEN
1335 l_error := 'XTR_DEAL_REVAL_DONE';
1336 END IF;
1337
1338 END IF;
1339
1340 -- checking at time deletion of balance
1341 ELSIF p_action_flag = 'D' THEN
1342
1343 OPEN c_deal_delete ;
1344 FETCH c_deal_delete INTO l_reval_delete_date;
1345 CLOSE c_deal_delete ;
1346
1347 IF l_reval_delete_date IS NOT NULL AND l_reval_delete_date >= p_BALANCE_DATE
1348 AND p_val_type = 'E'THEN
1349 l_error :='XTR_ACCT_DELETE';
1350 END IF;
1351
1352 -- checking at time insertion of balance
1353 -- Added the condition for checking balance not 0
1354 ELSIF (p_action_flag = 'I' AND nvl(p_balance_cflow,0) <> 0) THEN
1355
1356 OPEN c_reval_deal;
1357 FETCH c_reval_deal INTO l_reval_deal_date;
1358 CLOSE c_reval_deal;
1359 IF l_reval_deal_date IS NULL THEN
1360 OPEN c_reval_comp;
1361 FETCH c_reval_comp INTO l_reval_batch_date;
1362 CLOSE c_reval_comp;
1363
1364 IF l_reval_batch_date IS NOT NULL AND l_reval_batch_date >= p_balance_date
1365 AND p_val_type = 'W' THEN
1366 l_error := 'XTR_DEALS_BEFORE_REVAL'; -- warning message
1367 END IF;
1368
1369 ELSIF l_reval_deal_date IS NOT NULL AND l_reval_deal_date >= p_balance_date
1370 AND p_val_type = 'E' THEN
1371 l_error := 'XTR_CA_REVAL_DONE';
1372
1373 END IF;
1374
1375 END IF; -- p_action_flag
1376
1377 RETURN l_error;
1378
1379 END CHK_REVAL;
1380
1381
1382
1383 PROCEDURE UPDATE_BANK_ACCOUNT
1384 ( p_company_code IN xtr_bank_balances.company_code%TYPE,
1385 p_account_number IN xtr_bank_balances.account_number%TYPE,
1386 p_balance_date IN xtr_bank_balances.balance_date%TYPE,
1387 p_action_flag IN VARCHAR2,
1388 x_return_status OUT NOCOPY VARCHAR2
1389 )
1390 IS
1391
1392 l_ccy xtr_bank_accounts.currency%TYPE;
1393 l_portfolio_code xtr_bank_accounts.portfolio_code%TYPE;
1394 l_bank_code xtr_bank_accounts.bank_code%TYPE;
1395 l_cross_ref xtr_party_info.cross_ref_to_other_party%TYPE;
1396 l_bal_exists VARCHAR2(10);
1397 l_dummy_num VARCHAR2(1);
1398 l_bal_date xtr_bank_balances.balance_date%TYPE;
1399 l_accum_int_cfwd xtr_bank_balances.accum_int_cfwd%TYPE;
1400
1401 CURSOR C_SUBSIDIARY IS
1402 SELECT CROSS_REF_TO_OTHER_PARTY
1403 FROM XTR_PARTIES_V
1404 WHERE PARTY_CODE = p_company_code;
1405
1406
1407 CURSOR C_ACCT_DETAILS IS
1408 SELECT CURRENCY,PORTFOLIO_CODE,BANK_CODE
1409 FROM XTR_BANK_ACCOUNTS
1410 WHERE ACCOUNT_NUMBER = p_account_number
1411 AND PARTY_CODE = p_company_code;
1412
1413 -- To check whether the 'BAL' row exists in DDA table
1414 CURSOR C_BAL_EXISTS IS
1415 SELECT 'Y'
1416 FROM XTR_DEAL_DATE_AMOUNTS_V
1417 WHERE ACCOUNT_NO = p_account_number
1418 AND COMPANY_CODE = nvl(l_cross_ref,p_company_code)
1419 AND CURRENCY = l_ccy
1420 AND AMOUNT_DATE = p_balance_date
1421 AND AMOUNT_TYPE = 'BAL';
1422
1423 -- To find the latest date which is less the than date for the user
1424 -- is deleting the balance
1425
1426 CURSOR C_BAL_DATE IS
1427 SELECT max(BALANCE_DATE)
1428 FROM XTR_BANK_BALANCES_V
1429 WHERE ACCOUNT_NUMBER = p_account_number
1430 AND COMPANY_CODE = p_company_code
1431 AND BALANCE_DATE < p_balance_date;
1432
1433
1434 --
1435 CURSOR C_ACCUM_INT IS
1436 SELECT ACCUM_INT_CFWD
1437 FROM XTR_BANK_BALANCES
1438 WHERE ACCOUNT_NUMBER = P_ACCOUNT_NUMBER
1439 AND COMPANY_CODE = P_COMPANY_CODE
1440 AND BALANCE_DATE = (SELECT MAX(BALANCE_DATE) FROM
1441 XTR_BANK_BALANCES
1442 WHERE ACCOUNT_NUMBER = P_ACCOUNT_NUMBER
1443 AND COMPANY_CODE = P_COMPANY_CODE);
1444
1445 BEGIN
1446
1447 x_return_status := FND_API.G_RET_STS_SUCCESS; -- Added Bug 4546183
1448 OPEN C_ACCT_DETAILS;
1449 FETCH C_ACCT_DETAILS INTO l_ccy,l_portfolio_code, l_bank_code;
1450 CLOSE C_ACCT_DETAILS;
1451
1452
1453 OPEN C_SUBSIDIARY;
1454 FETCH C_SUBSIDIARY INTO l_cross_ref;
1455 CLOSE C_SUBSIDIARY;
1456
1457
1458 OPEN C_ACCUM_INT;
1459 FETCH C_ACCUM_INT INTO L_ACCUM_INT_CFWD;
1460 IF l_accum_int_cfwd is null then
1461 l_accum_int_cfwd := 0 ;
1462 end if;
1463 CLOSE C_ACCUM_INT;
1464
1465 IF p_action_flag = 'I' THEN
1466
1467 XTR_ACCOUNT_BAL_MAINT_P.UPDATE_BANK_ACCTS(
1468 p_account_number,
1469 l_ccy,
1470 l_bank_code,
1471 l_portfolio_code,
1472 l_cross_ref,
1473 p_company_code,
1474 p_balance_date,
1475 l_accum_int_cfwd,
1476 l_dummy_num
1477 );
1478
1479 ELSIF p_action_flag = 'D' THEN
1480
1481 OPEN C_BAL_EXISTS;
1482 FETCH C_BAL_EXISTS INTO l_bal_exists;
1483 IF C_BAL_EXISTS%FOUND THEN
1484 OPEN C_BAL_DATE;
1485 FETCH C_BAL_DATE INTO l_bal_date;
1486 CLOSE C_BAL_DATE;
1487 END IF;
1488 CLOSE C_BAL_EXISTS;
1489
1490 XTR_ACCOUNT_BAL_MAINT_P.UPDATE_BANK_ACCTS(
1491 p_account_number,
1492 l_ccy,
1493 l_bank_code,
1494 l_portfolio_code,
1495 l_cross_ref,
1496 p_company_code,
1497 nvl(l_bal_date, p_balance_date),
1498 l_accum_int_cfwd,
1499 l_dummy_num
1500 );
1501
1502 ELSIF p_action_flag = 'U' THEN
1503
1504 XTR_ACCOUNT_BAL_MAINT_P.UPDATE_BANK_ACCTS(
1505 p_account_number,
1506 l_ccy,
1507 l_bank_code,
1508 l_portfolio_code,
1509 l_cross_ref,
1510 p_company_code,
1511 p_balance_date,
1512 l_accum_int_cfwd,
1513 l_dummy_num
1514 );
1515
1516 END IF;
1517
1518 END UPDATE_BANK_ACCOUNT;
1519
1520
1521 /*
1522 This FUNCTION will be called BY CE AT THE TIME WHEN THE balance page IS rendered
1523 TO ENABLE/DISABLE THE balance DATE
1524
1525 THE same fuction will be also called FOR VALIDATION OF THE RECORD during
1526 insertion/updation/deletion
1527
1528 */
1529
1530 FUNCTION CHK_ROUNDING_CHANGE ( p_company_code IN xtr_bank_balances.company_code%TYPE,
1531 p_account_number IN xtr_bank_balances.account_number%TYPE,
1532 p_balance_date IN xtr_bank_balances.balance_date%TYPE)
1533 RETURN BOOLEAN IS
1534
1535 CURSOR c_chk_bal IS
1536 SELECT count(balance_date)
1537 FROM XTR_BANK_BALANCES a
1538 WHERE a.ACCOUNT_NUMBER = p_account_number
1539 AND a.COMPANY_CODE = p_company_code;
1540
1541
1542 CURSOR c_chk_type IS
1543 SELECT COUNT(DISTINCT ROUNDING_TYPE||'-'||DAY_COUNT_TYPE)
1544 FROM XTR_BANK_BALANCES a
1545 WHERE a.ACCOUNT_NUMBER = p_account_number
1546 AND a.COMPANY_CODE = p_company_code
1547 AND a.BALANCE_DATE >= (SELECT max(balance_date)
1548 FROM xtr_bank_balances b
1549 WHERE b.account_number = p_account_number
1550 AND b.company_code = p_company_code
1551 AND b.balance_date < p_balance_date);
1552
1553 l_chk_bal NUMBER;
1554 l_count NUMBER;
1555 BEGIN
1556
1557 OPEN c_chk_bal;
1558 FETCH c_chk_bal INTO l_chk_bal;
1559 CLOSE c_chk_bal;
1560
1561 IF l_chk_bal >= 1 THEN -- Bug 5393641
1562 OPEN c_chk_type;
1563 FETCH c_chk_type INTO l_count;
1564 CLOSE c_chk_type;
1565
1566 IF l_count <= 1 THEN
1567 RETURN(TRUE);
1568 ELSE
1569 RETURN(FALSE);
1570 END IF;
1571 ELSE
1572 RETURN(TRUE);
1573 END IF;
1574
1575 END CHK_ROUNDING_CHANGE;
1576
1577
1578
1579 /* This function will be called by CE when the one account- multiple balance
1580 * date page is rendered to enable/disable the balance date and delete button
1581
1582 This will be also called by the validate_bank_balance procedure at the time of
1583 deletion of the record
1584 */
1585
1586 FUNCTION CHK_INTEREST_SETTLED
1587 ( p_ce_bank_account_id IN xtr_bank_accounts.ce_bank_account_id%TYPE
1588 , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1589 )return VARCHAR2 is
1590
1591 l_return VARCHAR2(2);
1592 l_dummy VARCHAR2(1);
1593 l_company_code XTR_BANK_ACCOUNTS.party_code%TYPE;
1594 l_account_number XTR_BANK_ACCOUNTS.account_number%TYPE;
1595 l_currency XTR_BANK_ACCOUNTS.currency%TYPE;
1596
1597
1598 CURSOR c_account_details IS
1599 SELECT party_code, account_number,currency
1600 FROM XTR_BANK_ACCOUNTS
1601 WHERE ce_bank_account_id = p_ce_bank_account_id;
1602
1603 cursor INT_SETTLED is
1604 select 'Y'
1605 from XTR_DEAL_DATE_AMOUNTS_V
1606 where DEAL_TYPE = 'CA'
1607 and AMOUNT_TYPE = 'INTSET'
1608 and ACCOUNT_NO = l_account_number
1609 and CURRENCY = l_currency
1610 and COMPANY_CODE = l_company_code
1611 and AMOUNT_DATE = p_balance_date
1612 and (Batch_Id IS NOT NULL or nvl(SETTLE, 'N') = 'Y');
1613
1614
1615 BEGIN
1616
1617 l_return := 'Y';
1618
1619 IF(p_ce_bank_account_id is not null and p_balance_date is not null) THEN
1620
1621 OPEN c_account_details;
1622 FETCH c_account_details INTO l_company_code,l_account_number,l_currency;
1623 CLOSE c_account_details;
1624
1625 open INT_SETTLED;
1626 fetch INT_SETTLED into l_dummy;
1627 if INT_SETTLED%FOUND then
1628 close INT_SETTLED;
1629 l_return := 'N';
1630 else
1631 close INT_SETTLED;
1632 l_return := 'Y';
1633 end if;
1634
1635 END If;
1636
1637 END CHK_INTEREST_SETTLED;
1638
1639
1640
1641
1642 -- This function will be called by CE at the time when the balabce page is rendered
1643 -- to enable/disable the available balance
1644
1645 FUNCTION CHK_REVAL_ON_RENDER
1646 ( p_company_code IN xtr_bank_balances.company_code%TYPE
1647 , p_account_number IN xtr_bank_balances.account_number%TYPE
1648 , p_currency IN xtr_bank_accounts.currency%TYPE
1649 , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1650 )RETURN BOOLEAN
1651 IS
1652
1653 l_reval_deal_date DATE;
1654 l_reval_delete_date DATE;
1655
1656 -- Check whether the revaluations have been run for that particular deal
1657 CURSOR c_reval_deal IS
1658 SELECT max(period_to)
1659 FROM
1660 xtr_bank_balances bb,xtr_bank_accounts ba,
1661 xtr_revaluation_details rd
1662 WHERE
1663 bb.company_code = p_company_code
1664 AND bb.account_number = p_account_number
1665 AND bb.company_code = ba.party_code
1666 AND bb.account_number = ba.account_number
1667 AND ba.currency = p_currency
1668 AND bb.account_number = rd.account_no
1669 AND rd.deal_type = 'CA';
1670
1671
1672 CURSOR c_deal_delete IS
1673 SELECT max(bb.balance_date)
1674 FROM
1675 xtr_bank_balances bb,xtr_bank_accounts ba
1676 WHERE
1677 bb.company_code = p_company_code
1678 AND bb.account_number = p_account_number
1679 AND bb.company_code = ba.party_code
1680 AND bb.account_number = ba.account_number
1681 AND ba.currency = p_currency
1682 AND bb.first_batch_id IS NOT NULL;
1683
1684 BEGIN
1685
1686 OPEN c_reval_deal;
1687 FETCH c_reval_deal INTO l_reval_deal_date;
1688 CLOSE c_reval_deal;
1689
1690 OPEN c_deal_delete ;
1691 FETCH c_deal_delete INTO l_reval_delete_date;
1692 CLOSE c_deal_delete ;
1693
1694
1695 IF (l_reval_deal_date IS NOT NULL AND l_reval_deal_date >= p_balance_date)
1696 OR ( l_reval_delete_date IS NOT NULL AND l_reval_delete_date >= p_BALANCE_DATE) THEN
1697 RETURN(FALSE);
1698 ELSE
1699 RETURN(TRUE);
1700 END IF;
1701
1702 END CHK_REVAL_ON_RENDER;
1703
1704
1705 -- This function will be called by CE at the time when the balabce page is rendered
1706 -- to enable/disable the interest calculated balance
1707 FUNCTION CHK_ACCRUAL_ON_RENDER
1708 ( p_company_code IN xtr_bank_balances.company_code%TYPE
1709 , p_account_number IN xtr_bank_balances.account_number%TYPE
1710 , p_currency IN xtr_bank_accounts.currency%TYPE
1711 , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1712 )RETURN BOOLEAN
1713 IS
1714
1715 l_accrual_deal_date DATE;
1716
1717 -- Check whether the accruals have been run for that particular deal
1718 CURSOR c_accrl_deal IS
1719 SELECT max(period_to)
1720 FROM
1721 xtr_bank_balances bb, xtr_bank_accounts ba,
1722 xtr_deal_date_amounts dd, xtr_accrls_amort aa
1723 WHERE bb.company_code = p_company_code
1724 AND bb.account_number = p_account_number
1725 AND bb.company_code = ba.party_code
1726 AND bb.account_number = ba.account_number
1727 AND ba.currency = p_currency
1728 AND bb.company_code = dd.company_code
1729 AND bb.account_number = dd.account_no
1730 AND ba.currency = dd.currency
1731 AND dd.deal_number = aa.deal_no;
1732
1733
1734
1735 BEGIN
1736
1737 OPEN c_accrl_deal;
1738 FETCH c_accrl_deal INTO l_accrual_deal_date;
1739 CLOSE c_accrl_deal;
1740
1741 IF l_accrual_deal_date IS NOT NULL AND l_accrual_deal_date >= p_balance_date THEN
1742 RETURN(FALSE);
1743 ELSE
1744 RETURN(TRUE);
1745 END IF;
1746
1747 END CHK_ACCRUAL_ON_RENDER;
1748
1749 FUNCTION CHK_ACCRUAL_ON_RENDER
1750 ( p_ce_bank_account_id IN xtr_bank_accounts.ce_bank_account_id%TYPE
1751 , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1752 )return VARCHAR2 is
1753 l_return VARCHAR2(2);
1754 l_company_code XTR_BANK_ACCOUNTS.party_code%TYPE;
1755 l_account_number XTR_BANK_ACCOUNTS.account_number%TYPE;
1756 l_currency XTR_BANK_ACCOUNTS.currency%TYPE;
1757 CURSOR c_account_details IS
1758 SELECT party_code, account_number,currency
1759 FROM XTR_BANK_ACCOUNTS
1760 WHERE ce_bank_account_id = p_ce_bank_account_id;
1761 BEGIN
1762 l_return := 'Y';
1763 IF(p_ce_bank_account_id is not null and p_balance_date is not null) THEN
1764 OPEN c_account_details;
1765 FETCH c_account_details INTO l_company_code,l_account_number,l_currency;
1766 CLOSE c_account_details;
1767 IF( NOT CHK_ACCRUAL_ON_RENDER(l_company_code,l_account_number,l_currency,p_balance_date)) THEN
1768 l_return := 'N';
1769 END IF;
1770
1771 END IF;
1772
1773 RETURN l_return;
1774 END CHK_ACCRUAL_ON_RENDER;
1775
1776
1777
1778 FUNCTION CHK_REVAL_ON_RENDER
1779 ( p_ce_bank_account_id IN xtr_bank_accounts.ce_bank_account_id%TYPE
1780 , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1781 )return VARCHAR2 is
1782 l_return VARCHAR2(2);
1783 l_company_code XTR_BANK_ACCOUNTS.party_code%TYPE;
1784 l_account_number XTR_BANK_ACCOUNTS.account_number%TYPE;
1785 l_currency XTR_BANK_ACCOUNTS.currency%TYPE;
1786
1787 CURSOR c_account_details IS
1788 SELECT party_code, account_number,currency
1789 FROM XTR_BANK_ACCOUNTS
1790 WHERE ce_bank_account_id = p_ce_bank_account_id;
1791
1792 BEGIN
1793 l_return := 'Y';
1794
1795 IF(p_ce_bank_account_id is not null and p_balance_date is not null) THEN
1796 OPEN c_account_details;
1797 FETCH c_account_details INTO l_company_code,l_account_number,l_currency;
1798 CLOSE c_account_details;
1799 IF( NOT CHK_REVAL_ON_RENDER(l_company_code,l_account_number,l_currency,p_balance_date)) THEN
1800 l_return := 'N';
1801 END IF;
1802 END IF;
1803 RETURN l_return;
1804 END CHK_REVAL_ON_RENDER;
1805
1806
1807
1808
1809 PROCEDURE LOG_ERR_MSG
1810 ( p_error_code IN VARCHAR2,
1811 p_field_name IN VARCHAR2 DEFAULT NULL,
1812 p_balance_date IN xtr_bank_balances.balance_date%TYPE DEFAULT NULL
1813 )
1814 IS
1815
1816 BEGIN
1817
1818 IF p_error_code = 'XTR_PORTFOLIO' THEN
1819 FND_MESSAGE.Set_Name('XTR','XTR_2208');
1820 FND_MSG_PUB.ADD;
1821
1822 ELSIF p_error_code = 'XTR_INV_PRICING_MODEL' THEN
1823 FND_MESSAGE.Set_Name('XTR','XTR_INV_PRICING_MODEL');
1824 FND_MSG_PUB.ADD;
1825
1826 ELSIF p_error_code = 'CE_NO_SCHED_BANK_ACCT' THEN
1827 FND_MESSAGE.Set_Name('CE','CE_NO_SCHED_BANK_ACCT');
1828 FND_MSG_PUB.ADD;
1829
1830
1831 ELSIF p_error_code = 'XTR_REVAL_ACCRL_BANK' THEN
1832 /* warning has to be raised will look into this later */
1833 FND_MESSAGE.Set_Name ('XTR', 'XTR_REVAL_ACCRL_BANK');
1834 FND_MESSAGE.Set_Token ('DATE',p_balance_date);
1835 FND_MSG_PUB.ADD;
1836
1837 ELSIF p_error_code = 'XTR_BANK_ACCRLS_EXIST' THEN
1838 FND_MESSAGE.Set_Name ('XTR', 'XTR_BANK_ACCRLS_EXIST');
1839 FND_MESSAGE.Set_Token ('DATE',p_balance_date);
1840 FND_MSG_PUB.ADD;
1841
1842 ELSIF p_error_code = 'XTR_BANK_REVAL_DONE' THEN
1843 FND_MESSAGE.Set_Name ('XTR', 'XTR_BANK_REVAL_DONE');
1844 FND_MESSAGE.Set_Token ('DATE',p_balance_date);
1845 FND_MSG_PUB.ADD;
1846
1847 ELSIF p_error_code = 'XTR_ACCT_DELETE' THEN
1848 FND_MESSAGE.Set_Name ('XTR', 'XTR_ACCT_DELETE');
1849 FND_MSG_PUB.ADD;
1850
1851 ELSIF p_error_code = 'XTR_REVAL_ACCRL_DATE' THEN
1852 /* warning has to be raised will look into this later */
1853 FND_MESSAGE.Set_Name ('XTR', 'XTR_REVAL_ACCRL_DATE');
1854 FND_MESSAGE.Set_Token ('DATE',p_balance_date);
1855 FND_MSG_PUB.ADD;
1856
1857 ELSIF p_error_code = 'XTR_CA_REVAL_DONE' THEN
1858 FND_MESSAGE.Set_Name ('XTR', 'XTR_CA_REVAL_DONE');
1859 FND_MSG_PUB.ADD;
1860
1861 ELSIF p_error_code = 'XTR_104' THEN
1862 FND_MESSAGE.Set_Name ( 'XTR','XTR_104');
1863 FND_MSG_PUB.ADD;
1864
1865 ELSIF p_error_code = 'XTR_1237' THEN
1866 FND_MESSAGE.Set_Name ( 'XTR','XTR_1237');
1867 FND_MSG_PUB.ADD;
1868
1869 ELSIF p_error_code = 'XTR_UNEXP_ERROR' THEN
1870 FND_MESSAGE.Set_Name('XTR','XTR_UNEXP_ERROR');
1871 FND_MESSAGE.Set_Token('SQLCODE', p_field_name);
1872 FND_MSG_PUB.ADD; -- Adds the error messages to the list.
1873
1874 ELSIF p_error_code = 'CHK_LOCK' THEN
1875 FND_MESSAGE.Set_Name('XTR','XTR_1999');
1876 FND_MSG_PUB.ADD;
1877
1878
1879 ELSIF p_error_code = 'XTR_INV_PARAM' THEN
1880 FND_MESSAGE.Set_Name('XTR','XTR_INV_PARAM');
1881 FND_MESSAGE.Set_Token('FIELD', p_field_name);
1882 FND_MSG_PUB.ADD;
1883
1884 ELSIF p_error_code = 'XTR_TYPES_CHANGED' THEN
1885 FND_MESSAGE.Set_Name('XTR','XTR_TYPES_CHANGED');
1886 -- FND_MESSAGE.Set_Token('FIELD', p_field_name);
1887 FND_MSG_PUB.ADD;
1888
1889 ELSIF p_error_code = 'XTR_CHANGED_DAYCOUNT_ROUND' THEN
1890 FND_MESSAGE.Set_Name('XTR', 'XTR_CHANGED_DAYCOUNT_ROUND');
1891 -- FND_MESSAGE.Set_Token('FIELD', p_field_name);
1892 FND_MSG_PUB.ADD;
1893
1894
1895
1896
1897 END IF;
1898
1899 END LOG_ERR_MSG;
1900
1901
1902 /* This procedure updates the rounding_type and the day_count_type in the
1903 xtr_bank_balances IF THE same has been updated IN THE xtr_bank_accounts TABLE
1904
1905 This will be called FORM xtr_replicate_bank_accounts API WHEN THE rounding TYPE/
1906 day_count_type has been updated IN THE interest schedules page OR THE
1907 schedule has been changed BY THE USER */
1908
1909
1910 PROCEDURE UPDATE_ROUNDING_DAYCOUNT
1911 (p_ce_bank_account_id xtr_bank_accounts.ce_bank_account_id%TYPE
1912 ,p_rounding_type xtr_bank_accounts.rounding_type%TYPE
1913 ,p_day_count_type xtr_bank_accounts.day_count_type%TYPE
1914 ,x_return_status OUT NOCOPY VARCHAR2
1915 )
1916
1917 IS
1918
1919 l_old_rounding_type xtr_bank_accounts.rounding_type%TYPE;
1920 l_old_day_count_type xtr_bank_accounts.day_count_type%TYPE;
1921 l_day_count_type xtr_bank_accounts.day_count_type%TYPE;
1922 l_party_code xtr_bank_accounts.party_code%TYPE;
1923 l_account_number xtr_bank_accounts.account_number%TYPE;
1924 l_latest_date DATE;
1925 l_oldest_date DATE;
1926 l_acc_status varchar2(20);
1927 l_batch_error varchar2(20);
1928
1929 -- This cursor gets the existing rounding type/day count type from the
1930 -- xtr_bank_accounts table
1931
1932 CURSOR c_old_types IS
1933 SELECT rounding_type, day_count_type, party_code, account_number
1934 FROM XTR_BANK_ACCOUNTS
1935 WHERE ce_bank_account_id = p_ce_bank_account_id;
1936
1937 -- Gets the maximum balance date and the minimum balance date for that
1938 -- company and account
1939 CURSOR c_bal_date IS
1940 SELECT max(balance_date), min(balance_date)
1941 FROM XTR_BANK_BALANCES
1942 WHERE company_code = l_party_code
1943 AND account_number = l_account_number;
1944
1945 -- Checking for record lock
1946 CURSOR c_chk_lock IS
1947 SELECT day_count_type
1948 FROM xtr_bank_balances
1949 WHERE company_code = l_party_code
1950 AND account_number = l_account_number
1951 FOR UPDATE NOWAIT;
1952
1953
1954
1955 BEGIN
1956
1957 x_return_status := FND_API.G_RET_STS_SUCCESS;
1958
1959 OPEN c_old_types;
1960 FETCH c_old_types INTO l_old_rounding_type, l_old_day_count_type,l_party_code,l_account_number;
1961 CLOSE c_old_types;
1962
1963 IF l_old_rounding_type <> p_rounding_type
1964 OR l_old_day_count_type <> p_day_count_type THEN
1965
1966 OPEN c_bal_date;
1967 FETCH c_bal_date INTO l_latest_date, l_oldest_date;
1968
1969 IF c_bal_date%FOUND then
1970
1971 CLOSE c_bal_date;
1972
1973 l_batch_error := chk_int_override(l_party_code,l_account_number, l_oldest_date);
1974 l_acc_status := chk_accrual_int(l_party_code,l_account_number);
1975
1976 OPEN c_chk_lock;
1977 FETCH c_chk_lock INTO l_day_count_type;
1978 IF c_chk_lock%FOUND THEN
1979
1980 CLOSE c_chk_lock;
1981
1982 IF l_batch_error IS NULL THEN
1983
1984 UPDATE xtr_bank_balances
1985 SET rounding_type = p_rounding_type,
1986 day_count_type = p_day_count_type
1987 WHERE company_code =l_party_code
1988 AND account_number = l_account_number;
1989
1990 UPDATE_BANK_ACCOUNT(l_party_code,
1991 l_account_number,
1992 l_oldest_date,
1993 'U' ,
1994 x_return_status );
1995
1996
1997
1998 ELSIF l_batch_error IS NOT NULL AND l_acc_status IS NOT NULL THEN
1999
2000 UPDATE xtr_bank_balances
2001 SET rounding_type = p_rounding_type,
2002 day_count_type = p_day_count_type
2003 WHERE company_code =l_party_code
2004 AND account_number = l_account_number
2005 AND balance_date = l_latest_date;
2006
2007 END IF; -- l_batch_error
2008
2009 ELSE
2010 CLOSE c_chk_lock;
2011
2012 END IF; -- c_chk_lock
2013
2014 ELSE
2015
2016 close c_bal_date;
2017
2018 END IF; -- c_bal_date
2019
2020 END IF;
2021
2022 EXCEPTION
2023 WHEN app_exceptions.RECORD_LOCK_EXCEPTION THEN
2024 IF C_CHK_LOCK%ISOPEN THEN
2025 CLOSE C_CHK_LOCK;
2026 END IF;
2027 LOG_ERR_MSG('CHK_LOCK');
2028 x_return_status := FND_API.G_RET_STS_ERROR;
2029 WHEN others THEN
2030 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2031 LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
2032
2033 END UPDATE_ROUNDING_DAYCOUNT;
2034
2035
2036
2037 FUNCTION CHK_INT_OVERRIDE (
2038 p_party_code xtr_bank_accounts.party_code%type
2039 ,p_account_number xtr_bank_accounts.account_number%type
2040 ,p_oldest_date DATE
2041 ) RETURN VARCHAR2 IS
2042
2043 l_check_status varchar2(1);
2044 l_batch_error varchar2(20);
2045
2046
2047 -- Settlement Check cursor
2048 cursor CHK_SETTLE is
2049 select 'Y'
2050 from xtr_bank_balances
2051 where account_number = p_ACCOUNT_NUMBER
2052 and nvl(interest_settled,0) <> 0;
2053
2054 -- Accrual Check cursor
2055 cursor CHK_ACCRLS is
2056 select 'Y'
2057 from xtr_batches b, xtr_batch_events e
2058 where b.company_code = p_PARTY_CODE
2059 and b.batch_id = e.batch_id
2060 and e.event_code = 'ACCRUAL'
2061 and b.period_end > p_oldest_date;
2062
2063 -- Journal Check cursor
2064 cursor CHK_JNLS is
2065 select 'Y'
2066 from xtr_batches b, xtr_batch_events e
2067 where b.company_code =p_PARTY_CODE
2068 and b.batch_id = e.batch_id
2069 and e.event_code = 'JRNLGN'
2070 and b.period_end > p_oldest_date;
2071
2072 BEGIN
2073
2074 l_check_status := 'N';
2075 l_batch_error := NULL;
2076
2077 /* Check Settlement */
2078 Open CHK_SETTLE;
2079 fetch CHK_SETTLE into l_check_status;
2080 if CHK_SETTLE%FOUND then
2081 l_batch_error := 'SETTLE';
2082 end if;
2083 close CHK_SETTLE;
2084
2085 IF l_batch_error is NULL then
2086 /* Check Accruals */
2087 open CHK_ACCRLS;
2088 fetch CHK_ACCRLS into l_check_status;
2089 if CHK_ACCRLS%FOUND then
2090 l_batch_error := 'ACCRUE';
2091 END IF;
2092 close CHK_ACCRLS;
2093 END IF;
2094
2095 IF l_batch_error is NULL then
2096 /* Check Journals */
2097 open CHK_JNLS;
2098 fetch CHK_JNLS into l_check_status;
2099 if CHK_JNLS%FOUND then
2100 l_batch_error := 'JOURL';
2101 END IF;
2102 close CHK_JNLS;
2103
2104 END IF;
2105 RETURN l_batch_error;
2106 END CHK_INT_OVERRIDE;
2107
2108
2109
2110 FUNCTION CHK_ACCRUAL_INT (
2111 p_party_code xtr_bank_accounts.party_code%type
2112 ,p_account_number xtr_bank_accounts.account_number%type
2113 )RETURN varchar2 IS
2114
2115 l_acc_int number;
2116 l_acc_status varchar2(20);
2117
2118 cursor CHK_ACC_INT is
2119 select accum_int_cfwd
2120 from xtr_bank_balances
2121 where account_number = p_ACCOUNT_NUMBER
2122 and company_code = p_PARTY_CODE
2123 and balance_date = (select max(balance_date)
2124 from xtr_bank_balances
2125 where account_number = p_Account_Number
2126 and company_code = p_PARTY_CODE);
2127
2128 BEGIN
2129 l_acc_status := null;
2130
2131 open CHK_ACC_INT;
2132 fetch CHK_ACC_INT into l_acc_int;
2133
2134 if CHK_ACC_INT%FOUND then
2135
2136 if nvl(l_acc_int ,0) = 0 then
2137 l_acc_status :='ZERO';
2138 end if;
2139
2140 end if;
2141 close CHK_ACC_INT;
2142 RETURN l_acc_status;
2143 END CHK_ACCRUAL_INT;
2144
2145
2146 PROCEDURE CHK_ACCRUAL_REVAL_WARNINGS
2147 (p_ce_bank_account_id IN xtr_bank_accounts.ce_bank_account_id%TYPE
2148 ,p_balance_date IN xtr_bank_balances.balance_date%TYPE
2149 ,p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE default null
2150 ,p_interest_calc_balance IN NUMBER
2151 ,p_balance_cflow IN xtr_bank_balances.balance_cflow%TYPE
2152 ,p_action_flag IN VARCHAR2
2153 ,x_return_status OUT NOCOPY VARCHAR2
2154 ,x_msg_count OUT NOCOPY NUMBER
2155 ,x_msg_data OUT NOCOPY VARCHAR2 )IS
2156
2157 CURSOR c_bank_acct_details IS
2158 SELECT party_code,account_number,currency
2159 FROM XTR_BANK_ACCOUNTS
2160 WHERE ce_bank_account_id = p_ce_bank_account_id;
2161
2162 l_company_code xtr_bank_accounts.party_code%TYPE;
2163 l_account_number xtr_bank_accounts.account_number%TYPE;
2164 l_currency xtr_bank_accounts.currency%TYPE;
2165 l_return_error VARCHAR2(30);
2166
2167 BEGIN
2168 FND_MSG_PUB.Initialize;
2169
2170 OPEN c_bank_acct_details;
2171 FETCH c_bank_acct_details INTO l_company_code,l_account_number,l_currency;
2172 IF c_bank_acct_details%FOUND THEN
2173 CLOSE c_bank_acct_details;
2174
2175 l_return_error := chk_reval( l_company_code,
2176 l_account_number,
2177 l_currency,
2178 p_balance_date,
2179 p_ce_bank_account_balance_id,
2180 p_balance_cflow,
2181 p_action_flag,
2182 'W');
2183 IF nvl(l_return_error, '$$$') = 'XTR_DEALS_BEFORE_REVAL' THEN
2184 x_return_status := FND_API.G_RET_STS_ERROR;
2185 LOG_ERR_MSG ( 'XTR_DEALS_BEFORE_REVAL',p_balance_date);
2186 END IF;
2187
2188 l_return_error := chk_accrual(l_company_code,
2189 l_account_number,
2190 l_currency,
2191 p_balance_date,
2192 p_ce_bank_account_balance_id,
2193 p_interest_calc_balance,
2194 p_action_flag,
2195 'W');
2196
2197 IF nvl(l_return_error, '$$$') = 'XTR_DEALS_BEFORE_ACCRUAL' THEN
2198 x_return_status := FND_API.G_RET_STS_ERROR;
2199 LOG_ERR_MSG ( 'XTR_DEALS_BEFORE_ACCRUAL',p_balance_date);
2200 END IF;
2201
2202 ELSE
2203 CLOSE c_bank_acct_details;
2204 x_return_status := FND_API.G_RET_STS_ERROR;
2205 LOG_ERR_MSG('XTR_INV_PARAM','ACTION_FLAG');
2206 END IF;
2207
2208 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
2209 ( p_count => x_msg_count ,
2210 p_data => x_msg_data
2211 );
2212
2213 EXCEPTION
2214
2215 WHEN others THEN
2216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2217 LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
2218 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
2219 ( p_count => x_msg_count ,
2220 p_data => x_msg_data
2221 );
2222
2223 END CHK_ACCRUAL_REVAL_WARNINGS;
2224
2225
2226 -- bug 9742850 starts
2227
2228 PROCEDURE VALIDATE_OD_LIMIT_BALANCE
2229 ( p_company_code IN xtr_bank_balances.company_code%TYPE,
2230 p_branch_id IN NUMBER,
2231 p_bank_accont_id IN NUMBER,
2232 p_ledger_balance IN NUMBER,
2233 x_return_status OUT NOCOPY VARCHAR2,
2234 x_msg_count OUT NOCOPY NUMBER,
2235 x_msg_data OUT NOCOPY VARCHAR2) IS
2236 p_bal number:=0;
2237 p_funding_limit_code ce_bank_acct_uses_all.funding_limit_code%type;
2238
2239 BEGIN
2240
2241 x_return_status := FND_API.G_RET_STS_SUCCESS;
2242
2243 SELECT b.funding_limit_code into p_funding_limit_code
2244 FROM ce_bank_accounts a,
2245 ce_bank_acct_uses_all b
2246 WHERE a.bank_account_id=b.bank_account_id
2247 and a.bank_account_id= p_bank_accont_id
2248 AND b.xtr_use_enable_flag='Y';
2249
2250 SELECT limit_amount into p_bal
2251 FROM xtr_counterparty_limits_v a ,
2252 xtr_limit_types_v b,
2253 xtr_pro_param c
2254 WHERE a.limit_code = p_funding_limit_code
2255 AND b.fx_invest_fund_type='OD'
2256 AND a.company_code = p_company_code
2257 AND a.limit_type= b.limit_type
2258 AND c.param_name='DISPLAY_LIMIT_WARNING' and c.param_value='Y'
2259 AND a.cparty_code IN
2260 (SELECT party_code FROM xtr_party_info WHERE ce_bank_branch_id =
2261 p_branch_id
2262 );
2263
2264 if (-(p_bal + p_ledger_balance))>0 then
2265 x_return_status := FND_API.G_RET_STS_ERROR;
2266 LOG_ERR_MSG ('CE_OD_LIMIT_FAIL_INFO');
2267 end if;
2268
2269 EXCEPTION
2270
2271 WHEN NO_DATA_FOUND THEN
2272 x_return_status := FND_API.G_RET_STS_SUCCESS;
2273 WHEN others THEN
2274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2275 LOG_ERR_MSG ('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
2276
2277 FND_MSG_PUB.Count_And_Get
2278 ( p_count => x_msg_count ,
2279 p_data => x_msg_data
2280 );
2281
2282 END VALIDATE_OD_LIMIT_BALANCE;
2283
2284 -- bug 9742850 ends
2285
2286 END XTR_REPLICATE_BANK_BALANCES;
2287
[Home] [Help]