[Home] [Help]
PACKAGE BODY: APPS.XTR_RESET_BOND_RATE
Source
1 PACKAGE BODY XTR_RESET_BOND_RATE AS
2 /* $Header: xtrrfbrb.pls 120.1 2005/11/17 10:52:01 badiredd noship $*/
3
4
5
6 PROCEDURE RESET_BOND_BENCHMARK_RATE (errbuf OUT NOCOPY VARCHAR2,
7 retcode OUT NOCOPY NUMBER,
8 p_rateset_from IN VARCHAR2,
9 p_rateset_to IN VARCHAR2,
10 p_rateset_adj IN NUMBER,
11 p_bond_issue_code IN VARCHAR2,
12 p_currency IN VARCHAR2,
13 p_bench_mark IN VARCHAR2,
14 p_overwrite_type IN VARCHAR2 DEFAULT 'N')
15 AS
16 l_buf VARCHAR2(300);
17 l_rowid ROWID;
18 l_company XTR_DEALS.COMPANY_CODE%TYPE;
19 l_deal_no NUMBER;
20 l_tran_no NUMBER;
21 l_deal_type XTR_DEALS.DEAL_TYPE%TYPE;
22 l_start_date DATE;
23 l_coupon_date DATE;
24 l_ratefix_date DATE;
25 l_bond_issue_code XTR_BOND_COUPON_DATES.bond_issue_code%TYPE;
26 l_bench_mark XTR_BOND_ISSUES.BENCHMARK_RATE%TYPE;
27 l_margin NUMBER;
28 l_new_rate NUMBER;
29 l_rate NUMBER;
30 l_valid_ok BOOLEAN;
31 l_error NUMBER;
32 l_count NUMBER:= 0;
33 l_count1 NUMBER:= 0;
34 l_hold VARCHAR2(1);
35 l_retcode NUMBER := 0;
36
37
38 /*-------------------------------------------------*/
39 /* Selection criteria for transactions : */
40 /* - only BOND deals with Benchmark Rate */
41 /* - Benchmark Rate is not null */
42 /* - RATE_FIXING_DATE is not null on transaction */
43 /* - RATE_FIXING_DATE within parameter date range */
44 /*-------------------------------------------------*/
45
46 CURSOR C_GET_ALL_BOND_ISSUES IS
47 select b.bond_issue_code,
48 b.rate_fixing_date,
49 b.coupon_date,
50 c.benchmark_rate,
51 nvl(c.float_margin,0)
52 FROM
53 XTR_BOND_COUPON_DATES b,
54 XTR_BOND_ISSUES c
55 WHERE b.bond_issue_code = c.bond_issue_code
56 AND b.bond_issue_code = NVL(p_bond_issue_code,b.bond_issue_code)
57 and b.rate_fixing_date between fnd_date.canonical_to_date(p_rateset_from)
58 and fnd_date.canonical_to_date(p_rateset_to)
59 and c.benchmark_rate is not null
60 and c.benchmark_rate = nvl(p_bench_mark, c.benchmark_rate)
61 and c.calc_type in ('FL IRREGULAR','FL REGULAR')
62 ORDER BY b.bond_issue_code,b.rate_fixing_date,b.coupon_date;
63
64
65
66
67 cursor C_ONE_ROW_BOND is
68 select 'Y'
69 from XTR_BOND_COUPON_DATES a
70 where a.bond_issue_code = l_bond_issue_code
71 and a.rate_fixing_date = l_ratefix_date
72 and a.rate_fixing_date < (
73 select max (b.rate_fixing_date)
74 FROM
75 XTR_BOND_COUPON_DATES b
76 WHERE b.bond_issue_code = l_bond_issue_code
77 and b.rate_fixing_date between fnd_date.canonical_to_date(p_rateset_from)
78 and fnd_date.canonical_to_date(p_rateset_to) );
79
80 cursor C_SUBSEQ_BOND_DETAILS IS
81 select a.rate_fixing_date,
82 a.coupon_date
83 from XTR_BOND_COUPON_DATES a
84 where a.bond_issue_code = l_bond_issue_code
85 and a.rate_fixing_date >= l_ratefix_date
86 Order by a.rate_fixing_date;
87
88 bond_info C_SUBSEQ_BOND_DETAILS%ROWTYPE;
89
90 BEGIN
91
92 retcode := 0;
93
94 if fnd_date.canonical_to_date(p_rateset_from) > sysdate then
95 retcode := 2;
96 FND_MESSAGE.SET_NAME('XTR', 'XTR_RESET_DATE_FROM');
97 l_buf := FND_MESSAGE.GET;
98 fnd_file.put_line(fnd_file.log, l_buf);
99 end if;
100
101 if fnd_date.canonical_to_date(p_rateset_to) > sysdate then
102 retcode := 2;
103 FND_MESSAGE.SET_NAME('XTR', 'XTR_RESET_DATE_TO');
104 l_buf := FND_MESSAGE.GET;
105 fnd_file.put_line(fnd_file.log, l_buf);
106 end if;
107
108 If(retcode = 0)then
109 OPEN C_GET_ALL_BOND_ISSUES;
110 LOOP
111 FETCH C_GET_ALL_BOND_ISSUES into l_bond_issue_code, l_ratefix_date,
112 l_coupon_date, l_bench_mark, l_margin;
113 EXIT when C_GET_ALL_BOND_ISSUES%notfound;
114
115 l_count := l_count +1;
116 l_valid_ok := TRUE;
117 l_error := 0;
118 VALIDATE_TRANSACTION (l_bond_issue_code,
119 l_coupon_date,
120 l_ratefix_date,
121 p_overwrite_type,
122 l_valid_ok,
123 l_retcode);
124 retcode := greatest(retcode,nvl(l_retcode,0));
125
126 if l_valid_ok then
127
128 XTR_FPS2_P.GET_BENCHMARK_RATE(l_bench_mark,
129 l_ratefix_date,
130 nvl(p_rateset_adj,0),
131 l_new_rate);
132
133 if l_new_rate is not null then
134
135 l_new_rate := l_new_rate + (l_margin/100);
136 open C_ONE_ROW_BOND;
137 fetch C_ONE_ROW_BOND into l_hold;
138 if C_ONE_ROW_BOND%FOUND then -- update only one row. This is not the latest transaction of this deal number
139 close C_ONE_ROW_BOND;
140 l_count1 := 0;
141 UPDATE_BOND_DETAILS(l_bond_issue_code,l_coupon_date,
142 l_ratefix_date,l_new_rate,l_count1);
143 else --update current record as well as subsequent transactions
144 close C_ONE_ROW_BOND;
145
146 OPEN C_SUBSEQ_BOND_DETAILS;
147 LOOP
148 FETCH C_SUBSEQ_BOND_DETAILS into bond_info;
149 EXIT when C_SUBSEQ_BOND_DETAILS%notfound;
150 l_valid_ok := TRUE;
151 l_error := 0;
152 l_count1 := 0;
153 VALIDATE_TRANSACTION (l_bond_issue_code,
154 bond_info.coupon_date,
155 bond_info.rate_fixing_date,
156 p_overwrite_type,
157 l_valid_ok,
158 l_retcode);
159 retcode := greatest(retcode,nvl(l_retcode,0));
160
161 if l_valid_ok then
162 UPDATE_BOND_DETAILS(l_bond_issue_code,bond_info.coupon_date,
163 bond_info.rate_fixing_date,l_new_rate,l_count1);
164 end if;
165
166 END LOOP;
167 CLOSE C_SUBSEQ_BOND_DETAILS;
168 end if;
169 if l_count1 = 0 then
170 retcode := 1;
171 -- No deals/transactions for the bond issue code were found using the specified search criteria.
172 FND_MESSAGE.SET_NAME('XTR', 'XTR_NO_ELIGI_BENCH_BOND');
173 FND_MESSAGE.SET_TOKEN('ISSUE_CODE', l_bond_issue_code);
174 FND_MESSAGE.SET_TOKEN('RATE_DATE', l_ratefix_date);
175 l_buf := FND_MESSAGE.GET;
176 fnd_file.put_line(fnd_file.log, l_buf);
177 end if;
178 else
179 retcode := 1;
180 FND_MESSAGE.SET_NAME('XTR', 'XTR_NO_BOND_BENCH_RATE');
181 FND_MESSAGE.SET_TOKEN('ISSUE_CODE', l_bond_issue_code);
182 FND_MESSAGE.SET_TOKEN('RATE_DATE', l_ratefix_date);
183 FND_MESSAGE.SET_TOKEN('COUPON_DATE', l_coupon_date);
184 l_buf := FND_MESSAGE.GET;
185 fnd_file.put_line(fnd_file.log, l_buf);
186 end if;
187 End if; -- valid OK
188
189 END LOOP;
190 CLOSE C_GET_ALL_BOND_ISSUES;
191
192 if l_count = 0 then
193 retcode := 1;
194 -- No deals/transactions were found using the specified search criteria.
195 FND_MESSAGE.SET_NAME('XTR', 'XTR_NO_ELIGI_BENCH_ISSUE');
196 l_buf := FND_MESSAGE.GET;
197 fnd_file.put_line(fnd_file.log, l_buf);
198 end if;
199 end if;
200 EXCEPTION
201 WHEN OTHERS THEN
202 APP_EXCEPTION.raise_exception;
203 END RESET_BOND_BENCHMARK_RATE;
204
205 PROCEDURE VALIDATE_TRANSACTION(p_bond_issue_code IN VARCHAR2,
206 p_coupon_date IN DATE,
207 p_ratefix_date IN DATE,
208 p_overwrite_type IN VARCHAR2,
209 p_valid_ok OUT NOCOPY BOOLEAN,
210 p_retcode OUT NOCOPY NUMBER) AS
211
212
213 v_out_rec xtr_fps3_p.validation_out_rec;
214 v_in_rec xtr_fps3_p.validation_in_rec;
215 v_settled BOOLEAN := FALSE;
216 v_journaled BOOLEAN := FALSE;
217 v_reconciled BOOLEAN := FALSE;
218 v_accrued BOOLEAN := FALSE;
219 v_count NUMBER := 0;
220 l_buf2 VARCHAR2(300);
221 l_error NUMBER;
222
223 cursor check_overridden_cpn_amt(p_bond_issue_code VARCHAR2,
224 p_coupon_date DATE) is
225 select count(*)
226 from xtr_deals d, xtr_rollover_transactions rt
227 where d.deal_no=rt.deal_number
228 and d.bond_issue=p_bond_issue_code
229 and rt.maturity_date=p_coupon_date
230 and rt.interest<>rt.original_amount
231 and rt.interest>0;
232
233
234
235
236 BEGIN
237
238 p_valid_ok := TRUE;
239 v_in_rec.deal_type:='BOND';
240 v_in_rec.bond_issue_code:=p_bond_issue_code;
241 v_in_rec.bond_coupon_date:=p_COUPON_DATE;
242
243 If p_overwrite_type = 'N' then
244 open check_overridden_cpn_amt(p_bond_issue_code,p_coupon_date);
245 fetch check_overridden_cpn_amt into v_count;
246 close check_overridden_cpn_amt;
247 if v_count>0 then
248 l_error := 4;
249 p_valid_ok := FALSE;
250 End if;
251 End if;
252
253 If p_valid_ok then
254 xtr_fps3_p.settled_validation(v_in_rec,v_out_rec);
255 v_settled:=v_out_rec.yes;
256 v_out_rec.yes:=NULL;
257 if v_settled then
258 l_error := 1;
259 p_valid_ok := FALSE;
260 end if;
261 end if;
262
263 If p_valid_ok then
264 xtr_fps3_p.journaled_validation(v_in_rec,v_out_rec);
265 v_journaled:=v_out_rec.yes;
266 v_out_rec.yes:=NULL;
267 if v_journaled then
268 l_error := 3;
269 p_valid_ok := FALSE;
270 end if;
271 end if;
272
273 If p_valid_ok then
274 xtr_fps3_p.accrued_validation(v_in_rec,v_out_rec);
275 v_accrued:=v_out_rec.yes;
276 v_out_rec.yes:=NULL;
277 if v_accrued then
278 l_error := 2;
279 p_valid_ok := FALSE;
280 end if;
281 end if;
282
283 If Not p_valid_ok then
284
285 p_retcode := 1;
286 if l_error = 1 then -- deal been settled
287 FND_MESSAGE.SET_NAME('XTR', 'XTR_BENCH_BOND_SETTLE');
288 FND_MESSAGE.SET_TOKEN('ISSUE_CODE', p_bond_issue_code);
289 FND_MESSAGE.SET_TOKEN('COUPON_DATE', p_coupon_date);
290 FND_MESSAGE.SET_TOKEN('RATEFIX_DATE', p_ratefix_date);
291 l_buf2 := FND_MESSAGE.GET;
292 fnd_file.put_line(fnd_file.log, l_buf2);
293 elsif l_error = 2 then -- Accrual has been generated
294 FND_MESSAGE.SET_NAME('XTR', 'XTR_BENCH_BOND_ACCRUAL');
295 FND_MESSAGE.SET_TOKEN('ISSUE_CODE', p_bond_issue_code);
296 FND_MESSAGE.SET_TOKEN('COUPON_DATE', p_coupon_date);
297 FND_MESSAGE.SET_TOKEN('RATEFIX_DATE', p_ratefix_date);
298 l_buf2 := FND_MESSAGE.GET;
299 fnd_file.put_line(fnd_file.log, l_buf2);
300 elsif l_error = 3 then -- journal has been generated
301 FND_MESSAGE.SET_NAME('XTR', 'XTR_BENCH_BOND_JOURNAL');
302 FND_MESSAGE.SET_TOKEN('ISSUE_CODE', p_bond_issue_code);
303 FND_MESSAGE.SET_TOKEN('COUPON_DATE', p_coupon_date);
304 FND_MESSAGE.SET_TOKEN('RATEFIX_DATE', p_ratefix_date);
305 l_buf2 := FND_MESSAGE.GET;
306 fnd_file.put_line(fnd_file.log, l_buf2);
307 elsif l_error = 4 then -- Coupon amount has been overwritten manually
308 FND_MESSAGE.SET_NAME('XTR', 'XTR_COUPON_AMOUNT_OVERWRITTEN');
309 FND_MESSAGE.SET_TOKEN('ISSUE_CODE', p_bond_issue_code);
310 FND_MESSAGE.SET_TOKEN('COUPON_DATE', p_coupon_date);
311 FND_MESSAGE.SET_TOKEN('RATEFIX_DATE', p_ratefix_date);
312 l_buf2 := FND_MESSAGE.GET;
313 fnd_file.put_line(fnd_file.log, l_buf2);
314
315 End if;
316
317 End if;
318
319
320
321
322 END VALIDATE_TRANSACTION;
323
324 PROCEDURE UPDATE_COUPON_DETAILS(p_bond_issue_code IN VARCHAR2,
325 p_coupon_date IN DATE,
326 p_new_rate IN NUMBER,
327 p_deal_number IN NUMBER,
328 p_transaction_number IN NUMBER,
329 p_update_type IN VARCHAR2) AS
330
331 CURSOR C_GET_SEQ_TRANSACTIONS IS
332 SELECT maturity_date,transaction_number
333 FROM XTR_ROLLOVER_TRANSACTIONS
334 WHERE deal_number = p_deal_number
335 AND transaction_number >= p_transaction_number
336 ORDER BY transaction_number;
337
338 l_coupon_date DATE;
339 l_trans_no NUMBER;
340
341
342
343
344 BEGIN
345 IF p_update_type = 'SINGLE' THEN
346 UPDATE XTR_ROLLOVER_TRANSACTIONS
347 SET INTEREST_RATE=P_NEW_RATE,
348 UPDATED_ON = SYSDATE,
349 UPDATED_BY = fnd_global.user_id
350 WHERE STATUS_CODE = 'CURRENT'
351 AND MATURITY_DATE = P_COUPON_DATE
352 AND DEAL_NUMBER = P_DEAL_NUMBER;
353
354 UPDATE_COUPON_AMOUNT(p_bond_issue_code,p_deal_number,p_transaction_number);
355
356
357 ELSE
358 OPEN C_GET_SEQ_TRANSACTIONS;
359 LOOP
360 FETCH C_GET_SEQ_TRANSACTIONS into l_coupon_date,l_trans_no;
361 EXIT when C_GET_SEQ_TRANSACTIONS%notfound;
362
363 UPDATE XTR_ROLLOVER_TRANSACTIONS
364 SET INTEREST_RATE=P_NEW_RATE,
365 UPDATED_ON = SYSDATE,
366 UPDATED_BY = fnd_global.user_id
367 WHERE STATUS_CODE = 'CURRENT'
368 AND MATURITY_DATE = l_COUPON_DATE
369 AND DEAL_NUMBER = P_DEAL_NUMBER;
370
371 UPDATE_COUPON_AMOUNT(p_bond_issue_code,p_deal_number,l_trans_no);
372
373 END LOOP;
374 CLOSE C_GET_SEQ_TRANSACTIONS;
375 END IF;
376 END UPDATE_COUPON_DETAILS;
377
378 PROCEDURE UPDATE_COUPON_AMOUNT(p_bond_issue_code IN VARCHAR2,
379 p_deal_number IN NUMBER,
380 p_transaction_number IN NUMBER
381 ) AS
382
383 v_new_cpn_amt NUMBER;
384 v_tax_amt NUMBER;
385 v_hce_amt NUMBER;
386 v_tax_diff NUMBER;
387 v_new_cpn_amt_dda NUMBER;
388 v_out_rec xtr_mm_covers.CalcBondCpnAmt_out_rec;
389 v_in_rec xtr_mm_covers.CalcBondCpnAmt_in_rec;
390 v_prncpl_ctype xtr_tax_brokerage_setup.calc_type%TYPE;
391 v_prncpl_method xtr_tax_brokerage_setup.tax_settle_method%TYPE;
392 v_income_ctype xtr_tax_brokerage_setup.calc_type%TYPE;
393 v_income_method xtr_tax_brokerage_setup.tax_settle_method%TYPE;
394
395 CURSOR C_GET_CURR_RELATED_TRANS(p_deal_number NUMBER,
396 p_transaction_number NUMBER) is
397 select rt.deal_number,rt.transaction_number,rt.tax_settled_reference,
398 rt.currency,rt.deal_subtype,rt.tax_code,rt.tax_amount
399 from xtr_rollover_transactions rt
400 where rt.status_code='CURRENT'
401 and rt.deal_number = p_deal_number
402 and rt.transaction_number = p_transaction_number
403 and rt.deal_subtype in ('ISSUE','SHORT','BUY')
404 order by rt.deal_number,rt.transaction_number;
405
406 curr_trans_info c_get_curr_related_trans%ROWTYPE;
407
408 cursor GET_HCE(p_amt NUMBER, p_ccy VARCHAR2) is
409 select nvl(round(p_amt/round(hce_rate,5),
410 rounding_factor),0)
411 from XTR_MASTER_CURRENCIES_V
412 where CURRENCY = p_ccy;
413
414
415
416
417 BEGIN
418
419 open c_get_curr_related_trans(p_deal_number,p_transaction_number);
420 LOOP
421 FETCH c_get_curr_related_trans into curr_trans_info;
422 EXIT when c_get_curr_related_trans%NOTFOUND or
423 c_get_curr_related_trans%NOTFOUND is null;
424
425 v_in_rec.deal_no := curr_trans_info.deal_number;
426 v_in_rec.transaction_no := curr_trans_info.transaction_number;
427 xtr_mm_covers.calc_bond_coupon_amt(v_in_rec,v_out_rec);
428 v_new_cpn_amt:=v_out_rec.coupon_amt;
429 v_tax_amt:=v_out_rec.coupon_tax_amt;
430 --calc interest_hce
431 OPEN GET_HCE(v_new_cpn_amt,curr_trans_info.currency);
432 FETCH GET_HCE INTO V_HCE_AMT;
433 CLOSE GET_HCE;
434 --find out tax settlement method
435 if curr_trans_info.tax_code is not null then
436 xtr_fps2_p.GET_SETTLE_METHOD (null,
437 v_prncpl_ctype,
438 v_prncpl_method,
439 curr_trans_info.tax_code,
440 v_income_ctype,
441 v_income_method);
442 end if;
443 --update XTR_ROLLOVER_TRANSACTIONS
444 UPDATE xtr_rollover_transactions
445 set interest=v_new_cpn_amt,interest_hce=v_hce_amt,
446 tax_amount=v_tax_amt,original_amount=v_new_cpn_amt,
447 UPDATED_ON = sysdate,
448 UPDATED_BY = fnd_global.user_id
449 where deal_number=curr_trans_info.deal_number
450 and transaction_number=curr_trans_info.transaction_number;
451 --update XTR_DEAL_DATE_AMOUNTS
452 if nvl(v_income_method,'NUL')='NIA' then
453 v_new_cpn_amt_dda:=v_new_cpn_amt-v_tax_amt;
454 else
455 v_new_cpn_amt_dda:=v_new_cpn_amt;
456 end if;
457 UPDATE xtr_deal_date_amounts
458 set amount=nvl(v_new_cpn_amt_dda,0),
459 cashflow_amount=decode(curr_trans_info.deal_subtype,'BUY',1,-1)*nvl(v_new_cpn_amt_dda,0),
460 UPDATED_ON = sysdate,
461 UPDATED_BY = fnd_global.user_id
462 where deal_type='BOND'
463 and deal_number=curr_trans_info.deal_number
464 and transaction_number=curr_trans_info.transaction_number
465 and amount_type='COUPON' and date_type='COUPON';
466 --update the TAX DDA
467 if curr_trans_info.tax_code is not null then
468 if nvl(v_income_method,'NUL')='NIA' then
469 UPDATE xtr_deal_date_amounts
470 set amount=v_tax_amt,
471 UPDATED_ON = sysdate,
472 UPDATED_BY = fnd_global.user_id
473 where deal_type='BOND'
474 and deal_number=curr_trans_info.deal_number
475 and transaction_number=curr_trans_info.transaction_number
476 and amount_type='TAX' and date_type='INCUR';
477 else
478 if curr_trans_info.tax_settled_reference is not null then
479 v_tax_diff:=curr_trans_info.tax_amount-v_tax_amt;
480 --Only pass the differences to UPDATE_TAX_DDA/EXP function
481 --since tax can be consolidated.
482 xtr_fps2_p.UPDATE_TAX_EXP (curr_trans_info.tax_settled_reference,
483 v_tax_diff);
484 xtr_fps2_p.UPDATE_TAX_DDA (curr_trans_info.tax_settled_reference,
485 v_tax_diff);
486 end if;
487 end if;
488 end if;
489 end loop;
490 close c_get_curr_related_trans;
491
492 END UPDATE_COUPON_AMOUNT;
493
494
495 PROCEDURE UPDATE_BOND_DETAILS(p_bond_issue_code IN VARCHAR2,
496 p_coupon_date IN DATE,
497 p_ratefix_date DATE,
498 p_new_rate IN NUMBER,
499 p_count OUT NOCOPY NUMBER) AS
500
501 CURSOR C_GET_RELATED_TRANS(p_bond_issue_code VARCHAR2,
502 p_coupon_date DATE) is
503 select rt.deal_number,rt.transaction_number
504 from xtr_rollover_transactions rt, xtr_deals d
505 where rt.status_code='CURRENT'
506 and d.deal_no=rt.deal_number
507 and rt.deal_subtype in ('ISSUE','SHORT','BUY')
508 and d.bond_issue=p_bond_issue_code
509 and rt.maturity_date=p_coupon_date
510 order by rt.deal_number,rt.transaction_number;
511
512 trans_info c_get_related_trans%ROWTYPE;
513 l_buf1 VARCHAR2(300);
514 -- Start Fix not to show transactions with zero amount
515 CURSOR C_GET_BALANCE_AMOUNT (p_deal NUMBER) IS
516 SELECT maturity_balance_amount
517 FROM XTR_DEALS
518 WHERE DEAL_NO = p_deal;
519
520 l_balance_amount NUMBER;
521
522 CURSOR C_GET_MAX_RESALE_DATE(p_deal_no NUMBER) IS
523 SELECT max(cross_ref_start_date)
524 FROM XTR_BOND_ALLOC_DETAILS
525 WHERE DEAL_NO = p_deal_no;
526
527 l_max_resale_date DATE;
528 l_show_message NUMBER := 1;
529 -- End Added not to show the Transactions with 0 amount
530
531 BEGIN
532 p_count := 0;
533 UPDATE XTR_BOND_COUPON_DATES
534 SET RATE = p_new_rate
535 WHERE BOND_ISSUE_CODE = p_bond_issue_code
536 AND COUPON_DATE = p_coupon_date;
537
538 OPEN C_GET_RELATED_TRANS(p_bond_issue_code,p_coupon_date);
539 LOOP
540 FETCH C_GET_RELATED_TRANS into trans_info;
541 EXIT when C_GET_RELATED_TRANS%NOTFOUND or
542 C_GET_RELATED_TRANS%NOTFOUND is null;
543 p_count := p_count +1;
544
545 UPDATE_COUPON_DETAILS(p_bond_issue_code,
546 p_coupon_date,
547 p_new_rate,
548 trans_info.deal_number,
549 trans_info.transaction_number,
550 'SINGLE');
551 -- Below fix added not to show the update of transactions with zero coupon amount
552 l_show_message := 1;
553 OPEN C_GET_BALANCE_AMOUNT(trans_info.deal_number);
554 FETCH C_GET_BALANCE_AMOUNT INTO l_balance_amount;
555 CLOSE C_GET_BALANCE_AMOUNT;
556
557
558
559 IF nvl(l_balance_amount,0) = 0 THEN
560 OPEN C_GET_MAX_RESALE_DATE(trans_info.deal_number);
561 FETCH C_GET_MAX_RESALE_DATE into l_max_resale_date;
562 CLOSE C_GET_MAX_RESALE_DATE;
563 If p_coupon_date > l_max_resale_date then
564
565 l_show_message := 0;
566
567 End If;
568 END IF;
569
570 If l_show_message = 1 then
571
572 FND_MESSAGE.SET_NAME('XTR', 'XTR_UPDATE_BENCH_RATE_BOND');
573 FND_MESSAGE.SET_TOKEN('BENCH_RATE', p_new_rate);
574 FND_MESSAGE.SET_TOKEN('ISSUE_CODE', p_bond_issue_code);
575 FND_MESSAGE.SET_TOKEN('DEAL_NO', trans_info.deal_number);
576 FND_MESSAGE.SET_TOKEN('TRANS_NO', trans_info.transaction_number);
577 FND_MESSAGE.SET_TOKEN('RATE_DATE', p_ratefix_date);
578 l_buf1 := FND_MESSAGE.GET;
579 fnd_file.put_line(fnd_file.log, l_buf1);
580 End If;
581
582 end loop;
583 close C_GET_RELATED_TRANS;
584
585
586 END UPDATE_BOND_DETAILS;
587
588
589
590 END;