1 PACKAGE BODY XTR_ACCRUAL_PROCESS_P as
2 /* $Header: xtracclb.pls 120.33 2006/12/19 12:23:35 kbabu ship $ */
3 --------------------------------------------------------------------------------------------
4 /********************************************************************************************/
5 /*This procedure calculate NI effective interest. user inputs: */
6 /* p_face_value : the face value of transaction */
7 /* p_all_in_rate : XTR_ROLLOVER_TRANSACTIONS.all_in_rate column */
8 /* p_deal_date : call from the accrual process - deal.start_date */
9 /* call from the resale form - buy start date */
10 /* p_start_date : call from the accrual process - nvl(ni_reneg_date, batch_end_date) */
11 /* call from the resale form - sell start date */
12 /* p_maturity_date : deal.maturity_date */
13 /* p_adjust : indicator to know if we need to make day adjustment. */
14 /* If p_start_date is a ni_reneg date, then pass 'N', else pass 'Y' */
15 /* p_year_calc_type: xtr_deals.year_calc_type */
16 /* p_calc_basis : XTR_DEALS.calc_basis, if 'DISCOUNT', pass 'D', if 'YIELD', pass 'Y' */
17 /* p_pre_disc_end : the previous Discount amount. */
18 /* For Reval, it's the value from xtr_revaluation_details.ni_disc_amount */
19 /* from previous batch. */
20 /* For Accrual, it's the value from xtr_accrls_amort.accrls_amount_bal */
21 /* from previous batch */
22 /* p_day_count_type: Added for JPY Interest Override Project. 'B'-Both, 'L'-Last, 'F'-1st. */
23 /* p_resale_both_flag : Added for bug 2448432. Denotes if procedure is being called as a */
24 /* result of a resale in which the day count type of the resale = Both.*/
25 /* In such a case, the BUY deal will lose a day's worth of interest */
26 /* to the SELL deal and the no_of_days from resale start to maturity */
27 /* will need to be increased by 1 to obtain the correct unamortized */
28 /* interest amount. */
29 /* Additional stipulation: 0 <= no_of_days <= l_deal_no_of_days */
30 /* */
31 /* The output will be: */
32 /* p_no_of_days : the no of days in this accrual period */
33 /* p_year_basis : the year basis returns from CALC_DAYS_RUN */
34 /* p_disc_amount : the current discount amount */
35 /* p_eff_interest : the effective interest */
36 /********************************************************************************************/
37
38
39 PROCEDURE CALCULATE_EFFECTIVE_INTEREST(
40 p_face_value IN NUMBER,
41 p_all_in_rate IN NUMBER,
42 p_deal_date IN DATE,
43 p_start_date IN DATE,
44 p_maturity_date IN DATE,
45 p_adjust IN VARCHAR2,
46 p_year_calc_type IN VARCHAR2,
47 p_calc_basis IN VARCHAR2,
48 p_pre_disc_end IN NUMBER,
49 p_no_of_days OUT NOCOPY NUMBER,
50 p_year_basis OUT NOCOPY NUMBER,
51 p_disc_amount OUT NOCOPY NUMBER,
52 p_eff_interest OUT NOCOPY NUMBER,
53 p_day_count_type IN VARCHAR2,
54 p_resale_both_flag IN VARCHAR2,
55 p_status_code IN VARCHAR2) IS
56
57 /*-----------------------------------------------------*/
58 /* Get param for Arrears(FOLLOWING) or Forward (PRIOR).*/
59 /*-----------------------------------------------------*/
60 cursor ADJUST(p_param_name varchar2) is
61 select PARAM_VALUE
62 from XTR_PRO_PARAM
63 where PARAM_NAME = p_param_name;
64
65 l_start_disc NUMBER;
66 l_start_date DATE;
67 l_end_disc NUMBER;
68 l_days_adjust VARCHAR2(50);
69 l_deal_no_of_days NUMBER;
70
71 Begin
72
73 l_start_date := p_start_date;
74
75
76 -- AW Japan Project
77 l_days_adjust := p_day_count_type;
78
79 /*-----------------*/
80 /* Forward (PRIOR) */
81 /*-----------------*/
82 -- AW Japan Project
83 If nvl(l_days_adjust,'FOLLOWING') in ('PRIOR','B') and l_start_date <> p_maturity_date and
84 p_adjust = 'Y' then
85
86 If l_days_adjust = 'PRIOR' then
87 XTR_CALC_P.calc_days_run(p_deal_date,
88 p_maturity_date,
89 p_year_calc_type,
90 l_deal_no_of_days,
91 p_year_basis,
92 null, -- AW Japan Project
93 p_day_count_type, -- AW Japan Project
94 null); -- AW Japan Project
95
96 -- AW Japan Project
97 elsif l_days_adjust = 'B' then
98 XTR_CALC_P.calc_days_run(p_deal_date,
99 p_maturity_date,
100 p_year_calc_type,
101 l_deal_no_of_days,
102 p_year_basis,
103 null,
104 p_day_count_type,
105 'Y');
106 end if;
107
108 l_start_date := l_start_date + 1;
109 if l_start_date <> p_maturity_date then
110 if l_days_adjust = 'PRIOR' then -- AW Japan Project
111 XTR_CALC_P.calc_days_run(l_start_date,
112 p_maturity_date,
113 p_year_calc_type,
114 p_no_of_days,
115 p_year_basis,
116 null, -- AW Japan Project
117 p_day_count_type, -- AW Japan Project
118 null); -- AW Japan Project
119 else
120 -- AW Japan Project
121 XTR_CALC_P.calc_days_run(l_start_date,
122 p_maturity_date,
123 p_year_calc_type,
124 p_no_of_days,
125 p_year_basis,
126 null,
127 p_day_count_type,
128 'Y');
129 end if;
130 if p_no_of_days > l_deal_no_of_days then
131 p_no_of_days := l_deal_no_of_days;
132 end if;
133 else
134 if l_days_adjust = 'PRIOR' then
135 p_no_of_days := 0;
136 else -- AW Japan Project
137 p_no_of_days := 1; -- AW Japan Project
138 end if;
139 end if;
140
141 /*---------------------*/
142 /* Arrears (FOLLOWING) */
143 /*---------------------*/
144 else
145
146 XTR_CALC_P.calc_days_run(l_start_date,
147 p_maturity_date,
148 p_year_calc_type,
149 p_no_of_days,
150 p_year_basis,
151 null, -- AW Japan Project
152 p_day_count_type, -- AW Japan Project
153 null); -- AW Japan Project
154
155 -- Bug 2448432.
156 -- If procedure being called as a result of a resale to obtain the unamortized discount,
157 -- p_adjust would be passed in as 'N', bypassing the above "If" condition and behaving as
158 -- if the accrual system parameter is being set as 'Arrears'.
159 --
160 -- If the day count type of the resale deal = 'Both', need to add an additional day to
161 -- p_no_of_days because the seller must sacrafice a day of interest from the original
162 -- deal to the buyer. Of course, p_no_of_days cannot exceed the original deal's
163 -- total number of days.
164
165 If (nvl(p_resale_both_flag,'N') = 'Y') then
166
167 XTR_CALC_P.calc_days_run(p_deal_date,
168 p_maturity_date,
169 p_year_calc_type,
170 l_deal_no_of_days,
171 p_year_basis,
172 null,
173 p_day_count_type,
174 'Y');
175
176 p_no_of_days := p_no_of_days + 1;
177 p_no_of_days := least(p_no_of_days, l_deal_no_of_days);
178 End If;
179
180 -- End Bug 2448432.
181
182 end if;
183
184
185 /*----------------------------------------------*/
186 /* Calculate the accrual amount for this period */
187 /*----------------------------------------------*/
188
189 -- bug 4969194
190 /* If the status of the deal is closed and clacl basis is 'DISCOUNT' than formaula to be used for
191 calculation of effective interest should be done using the 'simple interest' formula
192 with rate as in the "BUY" deal */
193
194 if p_status_code = 'CLOSED' and (p_calc_basis = 'D' or p_calc_basis = 'DISCOUNT') then
195
196 p_disc_amount := (nvl(p_face_value,0) * p_all_in_rate * p_no_of_days)/(p_year_basis * 100);
197
198 else
199
200
201 p_disc_amount := nvl(p_face_value,0) - nvl(p_face_value,0)/(1 + ((p_all_in_rate * p_no_of_days)/(p_year_basis * 100)));
202
203 end if;
204
205 /* AW 2184427 Always calculate using Yield formula.
206 if p_calc_basis = 'D' then -- DISCOUNT
207 p_disc_amount := (nvl(p_face_value,0) * p_all_in_rate * p_no_of_days)/(p_year_basis * 100);
208 else -- YIELD
209 p_disc_amount := nvl(p_face_value,0) - nvl(p_face_value,0)/(1 + ((p_all_in_rate * p_no_of_days)/(p_year_basis * 100)));
210 end if;
211 */
212
213 /*--------------------------------------------------------------*/
214 /* Effective interest is the difference between two calculation */
215 /*--------------------------------------------------------------*/
216
217 p_eff_interest := nvl(p_pre_disc_end,0) - nvl(p_disc_amount,0);
218
219 End;
220 -----------------------------------------------------------------------------------------------------------------
221 PROCEDURE CALCULATE_BOND_AMORTISATION (p_company IN VARCHAR2,
222 p_batch_id IN NUMBER,
223 p_start_date IN DATE,
224 p_end_date IN DATE,
225 p_deal_type IN VARCHAR2) is
226 --
227 l_first_accrual_indic VARCHAR2(1);
228 days_adjust NUMBER :=0;
229 foreign_dom_ccy VARCHAR2(10);
230 starting_date DATE;
231 maturiting_date DATE;
232 deal_next_coupon DATE;
233 coupon_mat_date DATE;
234 l_rev_exp VARCHAR2(3);
235 l_deduct_coupon NUMBER;
236 l_amount_to_accrue_amort NUMBER;
237 trans_days NUMBER;
238 calc_days NUMBER;
239 l_round NUMBER;
240 l_yr_basis NUMBER;
241 l_rounding NUMBER;
242 l_calc_basis VARCHAR2(10);
243 l_ccy VARCHAR2(15);
244 effective DATE;
245 l_cross_ref NUMBER;
246 l_hce_rate NUMBER := 1;
247 l_interest_adj NUMBER := 0;
248 l_dummy_num NUMBER := 0;
249 l_bond_date DATE;
250 annual_yield NUMBER;
251 l_cum_tot_price NUMBER;
252 l_vol_tot_price NUMBER;
253 l_price NUMBER;
254 l_price_rounding NUMBER;
255 l_yield_rounding NUMBER;
256 l_bond_start_amt NUMBER;
257 l_bond_maturity_amt NUMBER;
258 l_bond_accrual_amort NUMBER;
259 l_true_adjust NUMBER;
260 l_exit VARCHAR2(1);
261 l_start_interest NUMBER;
262 l_maturity_interest NUMBER;
263 l_start_prem NUMBER;
264 l_maturity_prem NUMBER;
265 l_start_accrued_price NUMBER;
266 l_maturity_accrued_price NUMBER;
267
268 l_bond_issue VARCHAR2(10);
269 l_days_adjust VARCHAR2(50);
270 l_deal_nos NUMBER;
271 l_trans_nos NUMBER;
272 l_amount_type VARCHAR2(7);
273 l_accrls_amount_bal NUMBER;
274 l_year_calc_type VARCHAR2(20);
275 l_days_EOP NUMBER;
276 l_days_BOP NUMBER;
277 p_start_date_adj DATE;
278 p_end_date_adj DATE;
279
280 -- Get days adjustment for Accruals (ie ADD a day to end of period).
281 cursor ADJUST(p_param_name varchar2) is
282 select PARAM_VALUE
283 from XTR_PRO_PARAM
284 where PARAM_NAME = p_param_name;
285 --
286 cursor RND_FAC is
287 select m.ROUNDING_FACTOR
288 from XTR_MASTER_CURRENCIES_V m
289 where m.CURRENCY = l_ccy ;
290 --
291 cursor HCE is
292 select s.HCE_RATE
293 from XTR_MASTER_CURRENCIES s
294 where s.CURRENCY = l_ccy;
295
296 ---
297
298 --
299 cursor BONDS is
300 select a.deal_no,1 trans_no,a.start_date,a.maturity_date,a.next_coupon_date,a.interest_rate,
301 a.maturity_amount,a.coupon_action,a.bond_issue,a.currency,
305 a.year_calc_type,capital_price,
302 decode(nvl(a.frequency,0),0,1,a.frequency) frequency,
303 a.coupon_rate,a.start_amount,a.status_code,a.bond_reneg_date,
304 a.bond_sale_date,a.deal_subtype,a.product_type,a.portfolio_code,a.cparty_code,
306 a.day_count_type, a.rounding_type, -- AW Japan Project
307 decode(a.day_count_type,'B','Y','N') first_trans_flag -- AW Japan Project
308 from XTR_DEALS a
309 where a.deal_type = 'BOND'
310 and a.company_code = p_company
311 and a.deal_subtype IN ('BUY','ISSUE')
312 and a.status_code <> 'CANCELLED'
313 and a.start_date <= p_end_date
314 and (a.maturity_date >= p_start_date
315 or a.deal_no not in
316 ( select b.deal_no
317 from XTR_ACCRLS_AMORT b
318 where b.company_code=p_company
319 and b.deal_type = 'BOND'
320 and b.amount_type <> 'CPMADJ'))
321 and a.maturity_date >= a.start_date;
322
323 bond_det BONDS%ROWTYPE;
324 --
325
326 cursor BOND_COUPONS is
327 select r.deal_number,r.cparty_code,r.interest,r.interest_hce,r.start_date,
328 r.maturity_date,r.interest_rate,r.deal_subtype,r.product_type,r.transaction_number,
329 r.portfolio_code,r.currency,
330 nvl(d.day_count_type,'L') day_count_type, -- AW Japan Project
331 nvl(d.rounding_type,'R') rounding_type, -- AW Japan Project
332 decode(nvl(d.day_count_type,'L'),'B',decode(r.transaction_number,2,'Y','N'),
333 'N') first_trans_flag -- AW Japan Project
334 from XTR_ROLLOVER_TRANSACTIONS r,
335 XTR_DEALS d
336 where r.deal_type = 'BOND'
337 and r.deal_number = l_deal_nos
338 and r.status_code = 'CURRENT'
339 and r.maturity_date > maturiting_date
340 and d.deal_no = r.deal_number -- AW Japan Project
341 and d.deal_type = 'BOND'
342 order by r.maturity_date;
343
344 bond_cpn BOND_COUPONS%ROWTYPE;
345
346 cursor chk_first_accrual is
347 select 'N'
348 from XTR_ACCRLS_AMORT
349 where deal_no=l_deal_nos
350 and trans_no=l_trans_nos
351 and deal_type ='BOND';
352
353 cursor get_year_calc_type is
354 select year_calc_type
355 from xtr_bond_issues
356 where bond_issue_code=l_bond_issue;
357
358 --
359
360 cursor get_prv_value is
361 select nvl(EFFECTIVE_CALCULATED_VALUE,0) accrls_value
362 from XTR_ACCRLS_AMORT
363 where deal_no=l_deal_nos
364 and trans_no =l_trans_nos
365 and deal_type = 'BOND'
366 and amount_type=l_amount_type
367 and period_to<p_end_date
368 order by period_to desc;
369
370 cursor get_prv_bal is
371 select nvl(ACCRLS_AMOUNT_BAL,0) accrls_bal
372 from XTR_ACCRLS_AMORT
373 where deal_no=l_deal_nos
374 and trans_no =l_trans_nos
375 and deal_type = 'BOND'
376 and amount_type=l_amount_type
377 and period_to<p_end_date
378 order by period_to desc;
379
380 cursor get_coupon_prv_bal is
381 select trans_no,nvl(ACCRLS_AMOUNT_BAL,0) accrls_bal
382 from XTR_ACCRLS_AMORT
383 where deal_no=l_deal_nos
384 and amount_type=l_amount_type
385 and deal_type='BOND'
386 and action_code='POS'
387 and period_to < p_end_date
388 and (trans_no,period_to)
389 in(select trans_no,max(period_to)
390 from XTR_ACCRLS_AMORT
391 where deal_no=l_deal_nos
392 and amount_type=l_amount_type
393 and action_code='POS'
394 and period_to < p_end_date
395 group by trans_no);
396
397 l_face_discount NUMBER;
398 l_coupon_discount NUMBER;
399 l_this_coupon_disc NUMBER;
400 l_accr_interest NUMBER;
401 l_no_of_days NUMBER;
402
403 l_prv_effective_value NUMBER;
404 l_action VARCHAR2(10);
405
406 --
407 begin
408 l_first_accrual_indic := NULL;
409
410 --
411 l_days_adjust :=null;
412
413 open ADJUST('ACCRUAL_DAYS_ADJUST');
414 fetch ADJUST INTO l_days_adjust;
415 close ADJUST;
416
417 l_days_adjust :=nvl(l_days_adjust,'FOLLOWING');
418
419 if l_days_adjust ='PRIOR' then
420 bond_det.start_date :=bond_det.start_date -1;
421 end if;
422
423 ----------------------------------------------------------------------------------------------------------------------
424 -- Do Bonds / Fixed Rate Securities using discounted Cflows
425 -- Refer bug 929029
426 open BONDS;
427 LOOP
428 fetch BONDS INTO bond_det;
429 EXIT when BONDS%NOTFOUND;
430 l_deal_nos :=bond_det.deal_no;
431 l_trans_nos :=bond_det.trans_no;
432 l_ccy :=bond_det.currency;
433 l_amount_to_accrue_amort := 0;
434 l_bond_accrual_amort := 0;
435
436 if nvl(bond_det.coupon_rate,0)< bond_det.interest_rate then
437 l_amount_type :='EFDISC';
438 else
439 l_amount_type :='EFPREM';
440 end if;
441
442
443 l_first_accrual_indic :='Y';
444 open CHK_FIRST_ACCRUAL;
445 fetch CHK_FIRST_ACCRUAL into l_first_accrual_indic;
446 close CHK_FIRST_ACCRUAL;
447
448
452
449 open RND_FAC;
450 fetch RND_FAC into l_yield_rounding;
451 close RND_FAC;
453 l_yield_rounding := nvl(l_yield_rounding,2);
454
455 if bond_det.maturity_date is NOT NULL and bond_det.maturity_amount is NOT NULL and
456 bond_det.interest_rate is NOT NULL then
457 l_bond_accrual_amort := 0;
458 l_bond_issue :=bond_det.bond_issue;
459
460 l_face_discount :=0;
461 l_coupon_discount :=0;
462
463
464 if bond_det.year_calc_type is null then
465 open get_year_calc_type;
466 fetch get_year_calc_type into l_year_calc_type;
467 close get_year_calc_type;
468 else
469 l_year_calc_type := bond_det.year_calc_type;
470 end if;
471
472 if l_first_accrual_indic ='Y' then
473 l_prv_effective_value := xtr_fps2_p.interest_round(bond_det.maturity_amount*bond_det.capital_price/100,
474 l_yield_rounding, bond_det.rounding_type);
475 else
476
477 l_prv_effective_value :=0;
478 open get_prv_value;
479 fetch get_prv_value into l_prv_effective_value;
480 close get_prv_value;
481 end if;
482
483 if (bond_det.start_date >= p_start_date
484 and bond_det.bond_reneg_date is NULL) or
485 l_first_accrual_indic ='Y' then
486 starting_date := bond_det.start_date;
487 else
488 if nvl(bond_det.bond_reneg_date,p_start_date) > p_start_date then
489 starting_date := bond_det.bond_reneg_date;
490 else
491 starting_date := p_start_date;
492 end if;
493 end if;
494
495 if bond_det.status_code <> 'CURRENT' then
496 -- If the Bond has Been Sold need to Check if the BOND_SALE_DATE
497 -- is in this Period
498 if nvl(bond_det.bond_sale_date,bond_det.maturity_date) < p_end_date then
499 maturiting_date := nvl(bond_det.bond_sale_date,bond_det.maturity_date);
500 else
501 maturiting_date := p_end_date;
502 end if;
503 elsif bond_det.maturity_date > p_end_date then
504 maturiting_date := p_end_date;
505 else
506 maturiting_date := bond_det.maturity_date;
507 end if;
508
509 if bond_det.maturity_date > maturiting_date then
510 XTR_CALC_P.CALC_DAYS_RUN(maturiting_date,
511 bond_det.maturity_date,
512 l_year_calc_type,
513 calc_days,
514 l_yr_basis,
515 null, -- AW Japan Project
516 bond_det.day_count_type, -- AW Japan Project
517 bond_det.first_trans_flag); -- AW Japan Project
518 else
519 calc_days :=0;
520 l_yr_basis :=365;
521 end if;
522
523 xtr_fps2_p.PRESENT_VALUE_COMPOUND(days_in_year => l_yr_basis/bond_det.frequency,
524 amount => bond_det.maturity_amount,
525 rate => bond_det.interest_rate/bond_det.frequency,
526 no_of_days => calc_days,
527 round_factor => l_yield_rounding,
528 present_value => l_face_discount);
529 l_coupon_discount :=0;
530 l_accr_interest :=0;
531 open BOND_COUPONS;
532 LOOP
533 fetch BOND_COUPONS INTO bond_cpn;
534 EXIT when BOND_COUPONS%NOTFOUND;
535
536 if maturiting_date > bond_cpn.start_date and maturiting_date <= bond_cpn.maturity_date then
537 XTR_CALC_P.CALC_DAYS_RUN(bond_cpn.start_date,
538 bond_cpn.maturity_date,
539 l_year_calc_type,
540 l_no_of_days,
541 l_yr_basis,
542 null, -- AW Japan Project
543 bond_cpn.day_count_type, -- AW Japan Project
544 bond_cpn.first_trans_flag); -- AW Japan Project
545
546 XTR_CALC_P.CALC_DAYS_RUN(bond_cpn.start_date,
547 maturiting_date,
548 l_year_calc_type,
549 calc_days,
550 l_yr_basis,
551 null, -- AW Japan Project
552 bond_cpn.day_count_type, -- AW Japan Project
553 bond_cpn.first_trans_flag); -- AW Japan Project
554
555 l_accr_interest := nvl(l_accr_interest,0) + xtr_fps2_p.interest_round(abs((bond_cpn.interest /
556 l_no_of_days) * calc_days),l_yield_rounding,
557 bond_cpn.rounding_type);
558
559 end if;
560
561
562
563 if bond_cpn.maturity_date > maturiting_date then
564 XTR_CALC_P.CALC_DAYS_RUN(maturiting_date,
565 bond_cpn.maturity_date,
569 null, -- AW Japan Project
566 l_year_calc_type,
567 calc_days,
568 l_yr_basis,
570 bond_cpn.day_count_type, -- AW Japan Project
571 bond_cpn.first_trans_flag); -- AW Japan Project
572 else
573 calc_days :=0;
574 l_yr_basis :=365;
575 end if;
576
577 xtr_fps2_p.PRESENT_VALUE_COMPOUND(days_in_year => l_yr_basis/bond_det.frequency,
578 amount => bond_cpn.interest,
579 rate => bond_det.interest_rate/bond_det.frequency,
580 no_of_days => calc_days,
581 round_factor => l_yield_rounding,
582 present_value => l_this_coupon_disc);
583
584 l_coupon_discount :=nvl(l_coupon_discount,0)+nvl(l_this_coupon_disc,0);
585
586 END LOOP;
587 close BOND_COUPONS;
588 l_amount_to_accrue_amort := nvl(l_prv_effective_value,0)-(nvl(l_coupon_discount,0)+nvl(l_face_discount,0)-nvl(l_accr_interest,0));
589
590 if l_amount_to_accrue_amort>=0 then
591 l_action :='POS';
592 else
593 l_action :='REV';
594 end if;
595
596 l_accrls_amount_bal :=0;
597 open get_prv_bal;
598 fetch get_prv_bal into l_accrls_amount_bal;
599 close get_prv_bal;
600
601 if l_accrls_amount_bal <> 0 then
602 insert into XTR_ACCRLS_AMORT
603 (BATCH_ID,DEAL_NO,TRANS_NO,COMPANY_CODE,DEAL_SUBTYPE,
604 DEAL_TYPE,CURRENCY,PERIOD_FROM,PERIOD_TO,
605 CPARTY_CODE,PRODUCT_TYPE,PORTFOLIO_CODE,
606 INTEREST_RATE,TRANSACTION_AMOUNT,AMOUNT_TYPE,
607 ACCRLS_AMOUNT,YEAR_BASIS,
608 FIRST_ACCRUAL_INDIC,ACTUAL_START_DATE,ACTUAL_MATURITY_DATE,
609 NO_OF_DAYS,ACCRLS_AMOUNT_BAL,EFFECTIVE_CALCULATED_VALUE,ACTION_CODE)
610 values(p_batch_id,bond_det.deal_no,1,p_company,bond_det.deal_subtype,
611 'BOND',bond_det.currency,p_start_date,p_end_date,
612 bond_det.cparty_code,bond_det.product_type,
613 bond_det.portfolio_code,bond_det.interest_rate,
614 bond_det.maturity_amount,l_amount_type,
615 abs(l_amount_to_accrue_amort),NULL,
616 l_first_accrual_indic,starting_date,maturiting_date,
617 NULL,l_amount_to_accrue_amort+nvl(l_accrls_amount_bal,0),
618 nvl(l_face_discount,0)+nvl(l_coupon_discount,0)-nvl(l_accr_interest,0),l_action);
619
620 if bond_det.status_code <> 'CURRENT'
621 and bond_det.bond_sale_date <=p_end_date then
622 insert into XTR_ACCRLS_AMORT
623 (BATCH_ID,DEAL_NO,TRANS_NO,COMPANY_CODE,DEAL_SUBTYPE,
624 DEAL_TYPE,CURRENCY,PERIOD_FROM,PERIOD_TO,
625 CPARTY_CODE,PRODUCT_TYPE,PORTFOLIO_CODE,
626 INTEREST_RATE,TRANSACTION_AMOUNT,AMOUNT_TYPE,
627 ACCRLS_AMOUNT,YEAR_BASIS,
628 FIRST_ACCRUAL_INDIC,ACTUAL_START_DATE,ACTUAL_MATURITY_DATE,
629 NO_OF_DAYS,ACCRLS_AMOUNT_BAL,ACTION_CODE)
630 values(p_batch_id,bond_det.deal_no,1,p_company,bond_det.deal_subtype,
631 'BOND',bond_det.currency,p_start_date,p_end_date,
632 bond_det.cparty_code,bond_det.product_type,
633 bond_det.portfolio_code,bond_det.interest_rate,
634 bond_det.maturity_amount,l_amount_type,
635 l_amount_to_accrue_amort,NULL,
636 l_first_accrual_indic,starting_date,maturiting_date,
637 NULL,l_amount_to_accrue_amort+nvl(l_accrls_amount_bal,0),
638 decode(sign(l_amount_to_accrue_amort+nvl(l_accrls_amount_bal,0)),-1,'POS','REV'));
639 end if;
640 end if;
641
642
643 end if;
644 END LOOP;
645 close BONDS;
646 end CALCULATE_BOND_AMORTISATION;
647 ------------------------------------------------------------------------------------------------------------
648 PROCEDURE CALCULATE_ACCRUAL_AMORTISATION(errbuf OUT NOCOPY VARCHAR2,
649 retcode OUT NOCOPY NUMBER,
650 p_company IN VARCHAR2,
651 p_batch_id IN NUMBER,
652 start_date IN VARCHAR2,
653 end_date IN VARCHAR2,
654 p_upgrade_batch IN VARCHAR2) AS
655
656 --
657 /* Generalized Steps:
658 a) Get Reference Amount
659 b) Calculate days in full reference amount period
660 c) Calculate days from reference amount start date to Accrual To Date
661 d) Calculate Accrual To Date Balance
662 e) Calculate Accrual Period Amount
663 */
664
665 p_start_date DATE :=trunc(to_date(start_date,'YYYY/MM/DD HH24:MI:SS'));
666 p_end_date DATE :=trunc(to_date(end_date,'YYYY/MM/DD HH24:MI:SS'));
667
668 deal_yr_basis NUMBER;
669 period_end DATE;
670 period_start DATE;
671 maturing_date DATE;
672 starting_date DATE;
673
677 l_actual_start_date DATE;
674 l_accrls_amount_bal NUMBER; -- for 'l_cumm_int_bal'
675 l_action_code VARCHAR2(7);
676 l_actual_maturity DATE;
678 l_adj_coupon_amt NUMBER;
679 l_adj_amount NUMBER := 0; -- 2422480 added
680 l_adj_days NUMBER := 0; -- 2422480 added
681 --2422480 l_adj_coupon_start DATE;
682 l_amount_to_accrue_amort NUMBER;
683 l_amount_type VARCHAR2(7);
684 l_back_end_interest NUMBER;
685 l_balance_out NUMBER;
686 l_batch_id XTR_BATCHES.BATCH_ID%TYPE;
687 l_batch_start VARCHAR2(30);
688 l_bond_issue VARCHAR2(7);
689 l_calc_period_end DATE;
690 l_calc_period_accrl_int NUMBER;
691 l_dda_INT NUMBER;
692 l_calc_type XTR_BOND_ISSUES.CALC_TYPE%TYPE; -- renamed l_flat_zero for COMPOUND COUPON
693 l_clean_price NUMBER;
694 l_ccy VARCHAR2(15);
695 l_cum_ex XTR_DEALS.COUPON_ACTION%TYPE;
696 l_coupon_rate NUMBER;
697 l_coupon_start DATE;
698 l_coupon_end DATE;
699 l_cross_ref_start_date DATE;
700 l_cumm_resale_face NUMBER;
701 l_day_adjust_flag VARCHAR2(1) := 'Y';
702 l_day_count_type VARCHAR2(1);
703 l_days_adjust VARCHAR2(100);
704 l_days_BOP NUMBER;
705 l_days_EOP NUMBER;
706 l_deal_start DATE;
707 l_deal_closed VARCHAR2(1);
708 l_deal_nos NUMBER;
709 l_deal_type VARCHAR2(7);
710 l_dummy NUMBER;
711 l_elig_resale_start DATE;
712 l_elig_resale_end DATE;
713 l_event_id NUMBER;
714 l_face_value NUMBER;
715 l_face_value_bal NUMBER;
716 l_first_accrual_indic VARCHAR2(1);
717 l_forward_adjust NUMBER;
718 l_frequency NUMBER;
719 l_group_period_accrual_amt NUMBER := 0; -- 2422480 added
720 l_group_period_start DATE; -- 2422480 added
721 l_group_end_date DATE; -- 2422480 added
722 l_hce_rate NUMBER := 1;
723 l_maturity_amount NUMBER;
724 l_maturity_face_value NUMBER;
725 l_no_of_days NUMBER;
726 l_length_of_deal NUMBER;
727 l_period_accrual_amount NUMBER;
728 l_period_resale_amort NUMBER;
729 l_period_start_face_value NUMBER;
730 l_price_rounding NUMBER;
731 l_resale_total_amort NUMBER;
732 l_resale_cumm_amort NUMBER;
733 l_rounding NUMBER;
734 l_rounding_type VARCHAR2(1);
735 l_start_amount NUMBER;
736 l_status_code VARCHAR2(20);
737 l_sysdate DATE := trunc(sysdate);
738 l_temp NUMBER;
739 l_to_date_amort_amt NUMBER;
740 l_to_date_amort_amt_bal NUMBER;
741 l_to_date_resale_accrl_int NUMBER := 0; -- 2422480 added
742 l_trade_settle XTR_COMPANY_PARAMETERS.parameter_value_code%TYPE;
743 l_trans_nos NUMBER;
744 l_year_calc_type VARCHAR2(20);
745 l_yr_basis NUMBER;
746
747
748 /*---------------------------------------*/
749 /* Determine batch process starting point*/
750 /*---------------------------------------*/
751 cursor BATCH_START is
752 select parameter_value_code
753 from XTR_COMPANY_PARAMETERS
754 where company_code = p_company
755 and parameter_code = 'ACCNT_BPSTP';
756
757
758 /*-----------------------------------------------------*/
759 /* If batch start from Accruals, generate new Batch_id */
760 /*-----------------------------------------------------*/
761 cursor GEN_BATCH is
762 select XTR_BATCHES_S.NEXTVAL
763 from DUAL;
764
765 /*-----------------------*/
766 /* Generate new Event_id */
767 /*-----------------------*/
768 Cursor EVENT_ID is
769 Select XTR_BATCH_EVENTS_S.NEXTVAL
770 From DUAL;
771
772 /*-----------------------------------------------------------------------*/
773 /* If batch start from Reval, check reval authorization before Accruals */
774 /*-----------------------------------------------------------------------*/
775 cursor CHK_REVAL_AUTH is
776 select 1
777 from XTR_BATCH_EVENTS
778 where batch_id = p_batch_id
779 and event_code = 'REVAL'
780 and authorized = 'Y';
781
782 /*---------------------*/
783 /* Get rounding factor */
784 /*---------------------*/
785 cursor RND_FAC is
786 select m.ROUNDING_FACTOR
787 from XTR_MASTER_CURRENCIES_V m
788 where m.CURRENCY =l_ccy;
789
790 /*-----------------------------------------------------------------------------*/
791 /* Get accrual methods Interest in arrears(Following), Forward interest(Prior).*/
792 /*-----------------------------------------------------------------------------*/
793 cursor ADJUST(p_param_name varchar2) is
794 select PARAM_VALUE
795 from XTR_PRO_PARAM
796 where PARAM_NAME = p_param_name;
797
801 cursor cur_TRADE_SETTLE is
798 /*------------------------------*/
799 /* Get TRADE/SETTLE accounting */
800 /*------------------------------*/
802 select PARAMETER_VALUE_CODE
803 from XTR_COMPANY_PARAMETERS
804 where company_code = p_company
805 and parameter_code = 'ACCNT_TSDTM';
806
807 /*------------------------------*/
808 /* Find IRS First Transaction */
809 /*------------------------------*/
810 -- AW Japan Project
811 cursor CHK_FIRST_TRAN(l_deal_type VARCHAR2, l_deal_no NUMBER, l_tran_no NUMBER,
812 l_date_from DATE, l_date_to DATE) is
813 select 1
814 from XTR_ROLLOVER_TRANSACTIONS
815 where deal_type = l_deal_type
816 and deal_number = l_deal_no
817 and transaction_number <> l_tran_no
818 and start_date <= l_date_from
819 and maturity_date <= l_date_to;
820
821 /*---------------------------------------*/
822 /* Select deals for accrual calculations */
823 /*---------------------------------------*/
824 cursor ACCRUAL_DEALS is select
825 -----------------------------------------------
826 -- 'TMM','RTMM','IRS','ONC','BOND'(Coupon)
827 -----------------------------------------------
828 a.status_code status_code,
829 a.deal_type deal_type,
830 a.deal_number deal_nos,
831 a.transaction_number trans_nos,
832 a.deal_subtype subtype,
833 a.product_type product,
834 a.portfolio_code portfolio,
835 a.currency ccy,
836 a.cparty_code cparty,
837 a.client_code client,
838 NULL action,
839 decode(a.deal_type,'BOND','CPMADJ', 'INTADJ') main_amt_type,
840 decode(a.deal_type,'BOND',a.interest, a.balance_out) main_amount,
841 decode(a.deal_type,'BOND',a.interest_hce, a.balance_out_hce) hce_amount,
842 a.interest_rate rate,
843 a.start_date date_from,
844 decode(a.maturity_date,NULL, 'PEREND', 'MATURE') date_type_to,
845 a.maturity_date date_to, -- AW 2113171 For ONC without maturity date
846 --nvl(a.maturity_date,p_end_date) date_to, -- old
847 a.year_calc_type year_calc_type,
848 a.no_of_days no_of_days,
849 NULL bond_issue,
850 a.maturity_date deal_action_date,
851 decode(a.deal_type,'ONC',a.interest + nvl(a.interest_refund,0),a.interest) override_amount, -- AW Japan Project
852 nvl(d.rounding_type,'R') rounding_type, -- AW Japan Project
853 decode(nvl(d.day_count_type,'L'),'F','PRIOR','L','FOLLOWING','B') day_count_type, -- AW Japan Project
854 decode(nvl(d.day_count_type,'L'),'F',1,0) forward_adjust, -- AW Japan Project
855 decode(nvl(d.day_count_type,'L'),'B',decode(a.deal_type,'TMM', decode(a.transaction_number,1,'Y','N'),
856 'ONC', nvl(a.first_transaction_flag,'N'),
857 'BOND',decode(a.transaction_number,2,'Y','N'),
858 'N'),
859 'N') first_trans_flag -- AW Japan Project
860 from XTR_ROLLOVER_TRANSACTIONS a,
861 XTR_DEALS d
862 where a.company_code = p_company
863 and a.deal_number = d.deal_no
864 and a.deal_type = d.deal_type
865 and a.deal_type in ('TMM','RTMM','IRS','ONC','BOND')
866 and nvl(a.maturity_date,a.start_date+1) > a.start_date
867 and nvl(a.interest_rate,0) <> 0
868 and ((a.start_date <= p_end_date and a.deal_type <> 'BOND')
869 or (a.start_date <= p_end_date and a.deal_type = 'BOND' and
870 p_end_date >= (select b.start_date
871 from xtr_deals b
872 where b.deal_no = a.deal_number
873 and b.deal_type = 'BOND')))
874 and ((a.maturity_date >= p_start_date or a.maturity_date is NULL)
875 or (a.deal_number,a.transaction_number) not in (select b.deal_no,b.trans_no
876 from XTR_ACCRLS_AMORT b
877 where b.company_code = p_company
878 and b.deal_type in ('TMM','RTMM','IRS','ONC','BOND')))
879 and ((a.deal_type <> 'BOND' and a.status_code <> 'CANCELLED')
880 or (a.deal_type = 'BOND' and a.status_code not in ('CANCELLED','CLOSED')
881 and a.deal_subtype in ('BUY','ISSUE'))
882 and a.deal_number not in (select deal_no from
883 xtr_bond_alloc_details b
884 where b.deal_no = d.deal_no
885 and b.face_value = d.maturity_amount
889 -----------------------------
886 and b.cross_ref_start_date = d.start_date)) -- bug 5490311
887 -----------------------------
888 -- BOND (Discount/Premium) --
890 union all
891 select
892 a.status_code status_code,
893 a.deal_type deal_type,
894 a.deal_no deal_nos,
895 1 trans_nos,
896 a.deal_subtype subtype,
897 a.product_type product,
898 a.portfolio_code portfolio,
899 a.currency ccy,
900 a.cparty_code cparty,
901 a.client_code client,
902 NULL action,
903 decode(sign(a.capital_price-100),-1,'SLDISC','SLPREM') main_amt_type,
904 abs(a.maturity_amount) main_amount,
905 a.maturity_hce_amount hce_amount,
906 a.interest_rate rate,
907 decode(l_trade_settle,'TRADE',a.deal_date,a.start_date) date_from,
908 decode(a.maturity_date,NULL,'PEREND','MATURE') date_type_to, --Always MATURE cos maturity_date is not null
909 a.maturity_date date_to,
910 a.year_calc_type year_calc_type,
911 a.no_of_days no_of_days,
912 NULL bond_issue,
913 a.bond_sale_date deal_action_date, -- not use bond_reneg_date !
914 abs(a.maturity_amount) override_amount, -- AW Japan Project, not used
915 nvl(a.rounding_type,'R') rounding_type, -- AW Japan Project
916 decode(nvl(a.day_count_type,'L'),'F','PRIOR','L','FOLLOWING','B') day_count_type, -- AW Japan Project
917 decode(nvl(a.day_count_type,'L'),'F',1,0) forward_adjust, -- AW Japan Project
918 decode(nvl(a.day_count_type,'L'),'B','Y','N') first_trans_flag -- AW Japan Project
919 from XTR_DEALS a
920 where a.company_code = p_company
921 and a.deal_type = 'BOND'
922 and a.deal_subtype in ('BUY','ISSUE')
923 and decode(l_trade_settle,'TRADE',a.deal_date,a.start_date) <= p_end_date
924 and ((a.maturity_amount <> 0
925 and a.deal_no not in ( select b.deal_no
926 from XTR_ACCRLS_AMORT b
927 where b.company_code = p_company
928 and b.trans_no = 1
929 and b.deal_type = 'BOND'
930 and b.action_code = 'POS'
931 and b.amount_type in ('SLDISC','SLPREM')))
932 or (a.maturity_date >= p_start_date
933 and a.deal_no in (select b.deal_no
934 from XTR_ACCRLS_AMORT b
935 where b.company_code = p_company
936 and b.trans_no = 1
937 and b.deal_type = 'BOND'
938 and b.action_code = 'POS'
939 and b.amount_type in ('SLDISC','SLPREM')
940 and nvl(b.calc_face_value,0) <> 0
941 and b.batch_id = ( select max(c.batch_id)
942 from XTR_ACCRLS_AMORT c
943 where c.company_code = p_company
944 and c.deal_no = b.deal_no
945 and c.trans_no = 1
946 and c.deal_type = 'BOND'
947 and c.action_code = 'POS'
948 and c.amount_type in ('SLDISC','SLPREM')))))
949 and a.status_code not in ('CANCELLED', 'CLOSED')
950 and a.deal_no not in (select deal_no from
951 xtr_bond_alloc_details b
952 where b.deal_no = a.deal_no
953 and b.face_value = a.maturity_amount
954 and b.cross_ref_start_date = a.start_date) -- bug 5490311
955 -------------------------------
956 -- NI (Straight Line Method) --
957 -------------------------------
958
959 -- Bug 2448432.
960 -- Removed references to company trade/settle date accounting method parameter.
961 -- Accrual of interest is not to begin until the deal start date always.
962
963 union all
964 select
965 a.status_code status_code,
966 a.deal_type deal_type,
967 a.deal_number deal_nos,
968 a.transaction_number trans_nos,
969 a.deal_subtype subtype,
970 a.product_type product,
971 a.portfolio_code portfolio,
975 NULL action,
972 a.currency ccy,
973 a.cparty_code cparty,
974 a.client_code client,
976 'INTADJ' main_amt_type,
977 a.interest main_amount,
978 a.interest_hce hce_amount,
979 a.interest_rate rate,
980 a.start_date date_from,
981 decode(a.maturity_date,NULL, 'PEREND', 'MATURE') date_type_to,
982 a.maturity_date date_to,
983 a.year_calc_type year_calc_type,
984 a.no_of_days no_of_days,
985 NULL bond_issue,
986 a.ni_reneg_date deal_action_date,
987 a.interest override_amount, -- AW Japan Project, not used
988 nvl(d.rounding_type,'R') rounding_type, -- AW Japan Project
989 decode(nvl(d.day_count_type,'L'),'F','PRIOR','L','FOLLOWING','B') day_count_type, -- AW Japan Project
990 decode(nvl(d.day_count_type,'L'),'F',1,0) forward_adjust, -- AW Japan Project
991 decode(nvl(d.day_count_type,'L'),'B','Y','N') first_trans_flag -- AW Japan Project
992 from XTR_ROLLOVER_TRANSACTIONS a,
993 XTR_DEALS d
994 where a.company_code = p_company
995 and a.deal_number = d.deal_no
996 and a.deal_type = d.deal_type
997 and a.deal_type = 'NI'
998 and a.deal_subtype in ('BUY','SHORT','ISSUE')
999 and a.status_code <> 'CANCELLED'
1000 and nvl(a.interest_rate,0) <> 0
1001 and a.start_date <= p_end_date
1002 and (a.maturity_date >= p_start_date or
1003 (a.deal_number,a.transaction_number,'INTADJ') not in (select b.deal_no,b.trans_no,b.amount_type
1004 from XTR_ACCRLS_AMORT b
1005 where b.company_code = p_company
1006 and b.deal_type = 'NI'))
1007 -----------------------------------------------
1008 -- 'FXO','IRO','BDO','SWPTN' -- premium
1009 -----------------------------------------------
1010 union all
1011 select
1012 a.status_code status_code,
1013 a.deal_type deal_type,
1014 a.deal_no deal_nos,
1015 1 trans_nos,
1016 a.deal_subtype subtype,
1017 a.product_type product,
1018 a.portfolio_code portfolio,
1019 nvl(a.premium_currency,a.currency) ccy,
1020 a.cparty_code cparty,
1021 a.client_code client,
1022 a.settle_action action,
1023 'PREMADJ' main_amt_type,
1024 a.premium_amount main_amount,
1025 a.premium_hce_amount hce_amount,
1026 decode(a.deal_type,'FXO',a.transaction_rate,
1027 'BDO',a.capital_price,a.interest_rate) rate, -- AW 2113171 No rate displayed for BDO.
1028 a.premium_date date_from,
1029 'MATURE' date_type_to,
1030 a.expiry_date date_to,
1031 a.year_calc_type year_calc_type,
1032 a.no_of_days no_of_days,
1033 a.bond_issue bond_issue,
1034 a.settle_date deal_action_date,
1035 a.premium_amount override_amount, -- AW Japan Project, not used
1036 'R' rounding_type, -- AW Japan Project
1037 l_days_adjust day_count_type, -- AW Japan Project
1038 l_forward_adjust forward_adjust, -- AW Japan Project
1039 'N' first_trans_flag -- AW Japan Project
1040 from XTR_DEALS a
1041 where a.company_code = p_company
1042 and a.deal_type in ('IRO','BDO','SWPTN','FXO')
1043 and a.premium_date <= p_end_date
1044 and (a.expiry_date >= p_start_date
1045 or a.deal_no not in ( select b.deal_no
1046 from XTR_ACCRLS_AMORT b
1047 where b.company_code = p_company
1048 and b.deal_type in ('FXO','IRO','BDO','SWPTN')
1049 and b.amount_type = 'PREMADJ')) -- AW 1395208
1050 and a.status_code <> 'CANCELLED'
1051 and nvl(a.premium_amount,0) <> 0
1052
1056 union all
1053 --------------------------------------------------------
1054 -- 'FRA','BDO','IRO','SWPTN' -- interest AW 1395208
1055 --------------------------------------------------------
1057 select
1058 a.status_code status_code,
1059 a.deal_type deal_type,
1060 a.deal_no deal_nos,
1061 1 trans_nos,
1062 a.deal_subtype subtype,
1063 a.product_type product,
1064 a.portfolio_code portfolio,
1065 a.currency ccy,
1066 a.cparty_code cparty,
1067 a.client_code client,
1068 a.settle_action action,
1069 decode(a.deal_type,'BDO',decode(a.deal_subtype,'BCAP','SLDISC','SCAP','SLDISC','SLPREM'),
1070 'INTADJ') main_amt_type,
1071 a.settle_amount main_amount,
1072 a.settle_hce_amount hce_amount,
1073 decode(a.deal_type,'BDO',a.exercise_price,a.settle_rate) rate,
1074 a.start_date date_from,
1075 'MATURE' date_type_to,
1076 a.maturity_date date_to,
1077 a.year_calc_type year_calc_type, -- if null, ACTUAL/ACTUAL later
1078 a.no_of_days no_of_days,
1079 a.bond_issue bond_issue,
1080 a.settle_date deal_action_date,
1081 a.settle_amount override_amount, -- only apply to FRA
1082 decode(a.deal_type,'FRA',nvl(a.settle_rounding_type,'R'),
1083 'R') rounding_type,
1084 decode(a.deal_type,'FRA',decode(nvl(a.settle_day_count_type,l_days_adjust),'F','PRIOR',
1085 'L','FOLLOWING',
1086 'B','B',
1087 l_days_adjust),
1088 l_days_adjust) day_count_type,
1089 decode(decode(a.deal_type,'FRA',nvl(a.settle_day_count_type,l_days_adjust),
1090 l_days_adjust),
1091 'F',1,'PRIOR',1,0) forward_adjust,
1092 decode(a.deal_type,'FRA','Y','N') first_trans_flag
1093 from XTR_DEALS a
1094 where a.company_code = p_company
1095 and a.deal_type in ('FRA','BDO','IRO','SWPTN')
1096 and a.maturity_date is not null
1097 and a.start_date < a.maturity_date -- avoid Bug 3006377 in BDO allowing Start >= Maturity Date
1098 and a.start_date <= p_end_date
1099 and (a.maturity_date >= p_start_date
1100 or a.deal_no not in ( select b.deal_no
1101 from XTR_ACCRLS_AMORT b
1102 where b.company_code = p_company
1103 and b.deal_type in ('FRA','BDO','IRO','SWPTN')
1104 and b.amount_type in ('INTADJ','SLDISC','SLPREM')))
1105 and a.status_code in ('EXERCISED','SETTLED')
1106 and nvl(a.settle_amount,0) <> 0
1107 order by 2,3,4;
1108
1109
1110 ---------------------------------------
1111 -- Per Bruce, Tom and Ellen's requests
1112 ---------------------------------------
1113 -- select
1114 -- a.status_code status_code,
1115 -- a.deal_type deal_type,
1116 -- a.deal_no deal_nos,
1117 -- 1 trans_nos,
1118 -- a.deal_subtype subtype,
1119 -- a.product_type product,
1120 -- a.portfolio_code portfolio,
1121 -- a.currency ccy,
1122 -- a.cparty_code cparty,
1123 -- a.client_code client,
1124 -- a.settle_action action,
1125 -- 'SETLADJ' main_amt_type,
1126 -- a.settle_amount main_amount,
1127 -- a.settle_hce_amount hce_amount,
1128 -- a.interest_rate rate,
1129 -- a.start_date date_from,
1130 -- 'MATURE' date_type_to,
1131 -- a.maturity_date date_to,
1132 -- a.year_calc_type year_calc_type,
1133 -- a.no_of_days no_of_days,
1134 -- a.bond_issue bond_issue,
1135 -- a.maturity_date deal_action_date
1136 -- from XTR_DEALS a
1137 -- where a.company_code =p_company
1138 -- and a.deal_type in ('FRA','IRO','BDO','SWPTN')
1139 -- and a.start_date <= p_end_date
1140 -- and ((a.maturity_date >= p_start_date or
1141 -- a.maturity_date is NULL)
1142 -- or a.deal_no not in
1143 -- ( select b.deal_no
1144 -- from XTR_ACCRLS_AMORT b
1145 -- where b.company_code=p_company
1146 -- and b.deal_type in('FRA','IRO','BDO','SWPTN')))
1147 -- and a.maturity_date > a.start_date
1148 -- and a.status_code <> 'CANCELLED'
1152 onc_det ACCRUAL_DEALS%ROWTYPE;
1149 -- and nvl(a.settle_amount,0) <> 0
1150 -- union all
1151
1153
1154 -----------------------------
1155 -- Get Bond Discount/Premium
1156 -----------------------------
1157 cursor BOND_DISC_PREM is
1158 select decode(l_trade_settle,'TRADE',cross_ref_deal_date,cross_ref_start_date) resale_recognition_date,
1159 front_end_prem_disc,
1160 face_value,
1161 cross_ref_no
1162 from XTR_BOND_ALLOC_DETAILS
1163 where deal_no = l_deal_nos
1164 and decode(l_trade_settle,'TRADE',cross_ref_deal_date,cross_ref_start_date) <= p_end_date
1165 and (deal_no,cross_ref_no) not in (select deal_no,trans_no
1166 from xtr_accrls_amort
1167 where company_code = p_company);
1168
1169 discprem_det BOND_DISC_PREM%ROWTYPE;
1170
1171 -----------------------------
1172 -- Get Bond Discount/Premium
1173 -----------------------------
1174 cursor BOND_COUPON_RESALE is
1175 select b.back_end_interest,
1176 b.cross_ref_start_date,
1177 b.face_value,
1178 d.coupon_action -- 2422480 added
1179 from XTR_BOND_ALLOC_DETAILS b,
1180 XTR_DEALS d
1181 where b.deal_no = l_deal_nos
1182 and d.deal_no = l_deal_nos
1183 and b.cross_ref_start_date between l_elig_resale_start and l_elig_resale_end;
1184
1185 resale_det BOND_COUPON_RESALE%ROWTYPE;
1186
1187
1188 --------------------------------------
1189 -- Check if this is the first accrual
1190 --------------------------------------
1191 cursor CHK_FIRST_ACCRUAL is
1192 select 'N'
1193 from XTR_ACCRLS_AMORT
1194 where deal_no = l_deal_nos
1195 and deal_type = l_deal_type
1196 and ((deal_type = 'NI' and amount_type = 'INTADJ') -- To differentiate from new amt type EFFINT for NI.
1197 or (deal_type = 'BOND' and amount_type = l_amount_type and
1198 l_amount_type in ('SLDISC','SLPREM') and action_code = 'POS')
1199 or (deal_type = 'BOND' and amount_type = l_amount_type and
1200 l_amount_type = 'CPMADJ' and trans_no = l_trans_nos) -- AW 2113171 1st accrual indic for coupon.
1201 or (deal_type in ('TMM','RTMM','IRS','ONC') and trans_no = l_trans_nos) -- AW 2113171 1st accrual indic
1202 or (deal_type in ('BDO','IRO','SWPTN') and amount_type = l_amount_type and -- AW 1395208
1203 action_code = 'POS') -- AW 1395208
1204 or (deal_type not in ('NI','BOND','TMM','RTMM','IRS','ONC','BDO','IRO','SWPTN'))); -- AW 1395208
1205 --
1206
1207 -----------------------------
1208 -- Get Deal's Year Calc Type
1209 -----------------------------
1210 cursor GET_DEAL_DATA is
1211 select year_calc_type
1212 from xtr_deals
1213 where deal_no = l_deal_nos
1214 and deal_type = l_deal_type;
1215
1216 ------------------------------------
1217 -- Get issue details for Bond deal
1218 ------------------------------------
1219 cursor GET_BOND_DEAL_DATA is
1220 select bond_issue,
1221 capital_price,
1222 coupon_action,
1223 maturity_amount,
1224 start_amount,
1225 start_date
1226 from xtr_deals
1227 where deal_no = l_deal_nos
1228 and deal_type = l_deal_type;
1229
1230 ----------------------------------------------
1231 -- Get Accrued Interest for Discount/Premium
1232 ----------------------------------------------
1233 cursor GET_BOND_DDA_INT is
1234 select amount
1235 from XTR_DEAL_DATE_AMOUNTS
1236 where deal_number = l_deal_nos
1237 and transaction_number = 1
1238 and amount_type = 'INT'
1239 and date_type = 'COMENCE';
1240
1241 --------------------------
1242 -- Get Bond Issue details
1243 --------------------------
1244 cursor GET_BOND_ISSUE_DATA is
1245 select year_calc_type,
1246 price_rounding,
1247 nvl(no_of_coupons_per_year,0),
1248 calc_type,
1249 commence_date, -- COMPOUND COUPON
1250 maturity_date -- COMPOUND COUPON
1251 from xtr_bond_issues
1252 where bond_issue_code = l_bond_issue;
1253
1254
1255 --------------------------------
1256 -- Get previous accrual balance
1257 --------------------------------
1258 cursor GET_PRV_BAL is
1259 -- 2422480. Chg from accrls_amount_bal to fix Testing Issue 2.
1260 -- 2751078 Issue 2. Cummulative amount is calculated for SLPREM/SLDISC
1261 select nvl(decode(l_amount_type,'CPMADJ',EFFINT_ACCRLS_AMOUNT_BAL,ACCRLS_AMOUNT_BAL),0),
1262 nvl(CALC_FACE_VALUE,0)
1263 from XTR_ACCRLS_AMORT
1264 where deal_no = l_deal_nos
1265 and trans_no = l_trans_nos
1266 and deal_type = l_deal_type
1267 and amount_type = l_amount_type
1268 and action_code in ('POS','REV') -- 2422480. Added 'REV'.
1269 and batch_id < nvl(p_batch_id, l_batch_id) -- To handle inaugural batch. period_to < p_end_date
1270 order by period_to desc, calc_face_value asc; -- 2422480. Added calc_face_value to handle multi-sales on same day.
1271
1272
1273 ---------------------------------------------------------------------------------------------------------
1274 -- Check if the deal is closed - if closed, it means 'REV' has been created. Do not create 'REV' again.
1278 from XTR_ACCRLS_AMORT
1275 ---------------------------------------------------------------------------------------------------------
1276 cursor CHK_CLOSED_DEAL is
1277 select 'Y'
1279 where deal_no = l_deal_nos
1280 and trans_no = l_trans_nos
1281 and deal_type = l_deal_type
1282 and amount_type = l_amount_type
1283 and action_code = 'REV'
1284 and batch_id < nvl(p_batch_id, l_batch_id) -- To handle inaugural batch. period_to < p_end_date
1285 order by period_to desc;
1286
1287 ---------------------------------------------------------------------
1288 -- Get No of Null Coupon and Odd Coupon Maturity for COMPOUND COUPON
1289 ---------------------------------------------------------------------
1290 cursor TOTAL_FULL_COUPONS (p_issue_code VARCHAR2) is
1291 select count(*)-1, -- Total FULL Coupon
1292 min(coupon_date) -- Odd Coupon Maturity
1293 from xtr_bond_coupon_dates
1294 where bond_issue_code = p_issue_code;
1295
1296 -------------------------------------------------------------------------------
1297 -- Get Previous Coupon Date and No of Previous Full Coupon for COMPOUND COUPON
1298 -------------------------------------------------------------------------------
1299 cursor PRV_COUPON_DATES is
1300 select max(COUPON_DATE), -- Previous Coupon Date
1301 greatest(count(*)-1,0) -- Previous Full Coupon
1302 from XTR_BOND_COUPON_DATES
1303 where BOND_ISSUE_CODE = l_bond_issue
1304 and COUPON_DATE <= p_end_date;
1305
1306 ------------------------------------------------
1307 -- Get Next Coupon Date for COMPOUND COUPON
1308 ------------------------------------------------
1309 cursor NXT_COUPON_DATES is
1310 select min(COUPON_DATE) -- Next Coupon Date
1311 from XTR_BOND_COUPON_DATES
1312 where BOND_ISSUE_CODE = l_bond_issue
1313 and COUPON_DATE > p_end_date;
1314
1315 l_dummy_date DATE;
1316 l_bond_rec XTR_MM_COVERS.BOND_INFO_REC_TYPE;
1317 l_comp_coupon XTR_MM_COVERS.COMPOUND_CPN_REC_TYPE;
1318 l_no_quasi_coupon NUMBER;
1319 l_bond_commence DATE;
1320 l_bond_maturity DATE;
1321 l_odd_coupon_start DATE;
1322 l_odd_coupon_maturity DATE;
1323 l_prev_coupon_date DATE;
1324 l_next_coupon_date DATE;
1325 l_num_full_cpn_previous NUMBER;
1326 l_precision NUMBER;
1327 l_ext_precision NUMBER;
1328 l_min_acct_unit NUMBER;
1329 l_num_current_coupon NUMBER;
1330 l_prv_quasi_coupon NUMBER;
1331
1332 ex_reval_auth exception;
1333
1334 ----------------------------------------------------
1335 -- Get sum of accrued to-date balance of the coupon.
1336 ----------------------------------------------------
1337
1338 -- Cursor added for 2422480 to fix Testing Issue 2 without too hairy an upgrade script.
1339
1340 cursor GET_TOTAL_BOND_CPN_ACCRUAL is
1341 select sum(decode(action_code,'POS',nvl(ACCRLS_AMOUNT,0),nvl(-ACCRLS_AMOUNT,0)))
1342 from XTR_ACCRLS_AMORT
1343 where deal_no = l_deal_nos
1344 and trans_no = l_trans_nos
1345 and deal_type = 'BOND'
1346 and amount_type = 'CPMADJ'
1347 and action_code in ('POS','REV');
1348
1349 ------------------------------------------------------------------
1350 -- 2753088,2751078 - Get total resale face value up to given date
1351 ------------------------------------------------------------------
1352 cursor TOTAL_RESALE_FACE_VALUE (p_deal_no NUMBER, p_date DATE) is
1353 select nvl(sum(face_value),0)
1354 from XTR_BOND_ALLOC_DETAILS
1355 where deal_no = p_deal_no
1356 and cross_ref_start_date <= p_date;
1357
1358 -----------------------------------------------------------------------------------------
1359 -- Bug 2751078 - To balance total CPMADJ accruals amount.
1360 -----------------------------------------------------------------------------------------
1361 l_sum_prev_accrls NUMBER;
1362 l_sum_backend_int NUMBER;
1363
1364 --bug 2804548
1365 v_ChkCpnRateReset_out xtr_mm_covers.ChkCpnRateReset_out_rec;
1366 v_ChkCpnRateReset_in xtr_mm_covers.ChkCpnRateReset_in_rec;
1367
1368 -----------------------------------------------------------------------------------------
1369 -- Bug 2781438 (3450474) - To log reversal amount for TMM, IRS, ONC.
1370 -----------------------------------------------------------------------------------------
1371 l_rev_exists VARCHAR2(1) := 'N';
1372 l_rev_message VARCHAR2(20):= '';
1373
1374 BEGIN
1375
1376 -- Added for Streamline Accounting
1377 retcode := 0;
1378 SAVEPOINT sp_accrual;
1379
1380 Open BATCH_START;
1381 Fetch BATCH_START into l_batch_start;
1382 Close BATCH_START;
1383
1384 --------------------------------------
1385 -- Batch process starts from Reval
1386 --------------------------------------
1387 If l_batch_start = 'REVAL' then
1388 Open CHK_REVAL_AUTH;
1389 Fetch CHK_REVAL_AUTH into l_temp;
1390 If CHK_REVAL_AUTH%NOTFOUND then
1391 Close CHK_REVAL_AUTH;
1392 Raise ex_reval_auth;
1393 Else
1394 Close CHK_REVAL_AUTH;
1395 End if;
1396 l_batch_id := p_batch_id;
1397
1398 --------------------------------------
1399 -- Batch process starts from ACCRUAL
1400 --------------------------------------
1404 Fetch GEN_BATCH into l_batch_id;
1401 Else
1402 If p_batch_id is null then
1403 Open GEN_BATCH;
1405 Close GEN_BATCH;
1406
1407 -- Insert new row to XTR_BATCH when new batch process staring from accrual
1408 Insert into XTR_BATCHES(batch_id, company_code, period_start, period_end,
1409 gl_group_id, upgrade_batch, created_by, creation_date,
1410 last_updated_by, last_update_date, last_update_login)
1411 values (l_batch_id, p_company, p_start_date, p_end_date,
1412 null, nvl(p_upgrade_batch,'N'), fnd_global.user_id, l_sysdate,
1413 fnd_global.user_id, l_sysdate, fnd_global.login_id);
1414 Else
1415 l_batch_id := p_batch_id;
1416 End if;
1417 End if;
1418
1419 /*-----------------------------*/
1420 /* Delete before recalculation */
1421 /*-----------------------------*/
1422 if p_batch_id is not null then
1423 delete from XTR_ACCRLS_AMORT
1424 where company_code = p_company
1425 and batch_id = p_batch_id;
1426 end if;
1427
1428
1429 /*-----------------------------------------------------*/
1430 /* Get param for Arrears(FOLLOWING) or Forward (PRIOR).*/
1431 /*-----------------------------------------------------*/
1432 -- AW Japan Project
1433 -- Replaced in loop with ONC_DET.day_count_type and ONC_DET.forward_adjust
1434 -- Only FXO, IRO, BDO and SWPTN will be using system parameter for Forward/Arrear calculation
1435 l_days_adjust := null;
1436 open ADJUST('ACCRUAL_DAYS_ADJUST');
1437 fetch ADJUST INTO l_days_adjust;
1438 close ADJUST;
1439
1440 l_days_adjust :=nvl(l_days_adjust,'FOLLOWING');
1441 if l_days_adjust = 'PRIOR' then
1442 l_forward_adjust := 1;
1443 else
1444 l_forward_adjust := 0;
1445 end if;
1446
1447
1448 /*--------------------------------*/
1449 /* Get Trade or Settle accounting */
1450 /*--------------------------------*/
1451 open cur_TRADE_SETTLE;
1452 fetch cur_TRADE_SETTLE into l_trade_settle;
1453 close cur_TRADE_SETTLE;
1454
1455 l_trade_settle := nvl(l_trade_settle,'TRADE');
1456
1457
1458 /*--------------*/
1459 /* Main Program */
1460 /*--------------*/
1461
1462 ------------------------------------------------------------------------------------
1463 -- If this is inaugural batch, create a dummy event only. No details are required.
1464 ------------------------------------------------------------------------------------
1465 if nvl(p_upgrade_batch,'N') <> 'I' then
1466
1467 open ACCRUAL_DEALS;
1468 fetch ACCRUAL_DEALS INTO onc_det;
1469 while ACCRUAL_DEALS%FOUND LOOP
1470
1471
1472 /*----------------------------*/
1473 /* Initialise Deal Details */
1474 /*----------------------------*/
1475 l_amount_type := onc_det.main_amt_type;
1476 l_ccy := onc_det.ccy;
1477 l_deal_nos := onc_det.deal_nos;
1478 l_deal_type := onc_det.deal_type;
1479 l_status_code := onc_det.status_code;
1480 l_trans_nos := onc_det.trans_nos;
1481
1482 /*----------------------------*/
1483 /* Initialise Coupon Details */
1484 /*----------------------------*/
1485 if l_deal_type = 'BOND' and l_amount_type = 'CPMADJ' then
1486 l_coupon_rate := onc_det.rate;
1487 l_coupon_start := onc_det.date_from; -- for COMPOUND COUPON - Bond Commencement Date
1488 l_coupon_end := onc_det.date_to;
1489 --2422480 l_adj_coupon_start := onc_det.date_from;
1490
1491 l_adj_coupon_amt := 0;
1492 l_dda_INT := 0;
1493 l_calc_period_accrl_int := 0;
1494 l_cumm_resale_face := 0;
1495 l_face_value_bal := 0;
1496 l_maturity_amount := 0;
1497 l_start_amount := 0;
1498
1499 end if;
1500
1501 /*------------------------------------------------*/
1502 /* Initialise IRS and RTMM First Transaction Flag */
1503 /*------------------------------------------------*/
1504 -- AW Japan Project
1505 if onc_det.deal_type in ('IRS','RTMM') then
1506 open CHK_FIRST_TRAN(onc_det.deal_type,onc_det.deal_nos,onc_det.trans_nos,
1507 onc_det.date_from,onc_det.date_to);
1508 fetch CHK_FIRST_TRAN into l_dummy;
1509 if CHK_FIRST_TRAN%NOTFOUND then
1510 onc_det.first_trans_flag := 'Y';
1511 else
1512 onc_det.first_trans_flag := 'N';
1513 end if;
1514 close CHK_FIRST_TRAN;
1515 end if;
1516
1517 /*----------------------*/
1518 /* Get rounding factor */
1519 /*----------------------*/
1520 open RND_FAC;
1521 fetch RND_FAC into l_rounding;
1522 close RND_FAC;
1523 l_rounding := nvl(l_rounding,2);
1524
1525 /*----------------------------------------*/
1526 /* Get Year_Calc_Type (l_year_calc_type) */
1527 /*----------------------------------------*/
1528 l_year_calc_type := null;
1529 l_day_adjust_flag := 'Y';
1530
1531 if onc_det.year_calc_type is NULL and onc_det.deal_type in ('ONC','TMM','RTMM','IRS','NI') then -- Bug1680184 AW
1532 open GET_DEAL_DATA;
1533 fetch GET_DEAL_DATA into l_year_calc_type;
1534 close GET_DEAL_DATA;
1538 fetch GET_BOND_DEAL_DATA into l_bond_issue, l_clean_price, l_cum_ex,
1535
1536 elsif onc_det.deal_type in ('BDO','BOND') then
1537 open GET_BOND_DEAL_DATA;
1539 l_maturity_amount, l_start_amount, l_deal_start;
1540 close GET_BOND_DEAL_DATA;
1541
1542 open GET_BOND_ISSUE_DATA;
1543 fetch GET_BOND_ISSUE_DATA into l_year_calc_type, l_price_rounding, l_frequency, l_calc_type,
1544 l_bond_commence, l_bond_maturity; -- COMPOUND COUPON
1545 close GET_BOND_ISSUE_DATA;
1546
1547 l_price_rounding := nvl(l_price_rounding,6);
1548
1549 else
1550 l_year_calc_type := onc_det.year_calc_type;
1551
1552 end if;
1553
1554 l_year_calc_type := nvl(l_year_calc_type,'ACTUAL/ACTUAL');
1555
1556 /*-----------------------------------------------------------------------*/
1557 /* Initialise BOND - COMPOUND COUPON: - first transaction flag */
1558 /* - odd coupon start date */
1559 /* - odd coupon maturity date */
1560 /* - no of FULL coupon */
1561 /* - previous Coupon Date */
1562 /* - previous FULL coupon */
1563 /* - next Coupon Date */
1564 /*-----------------------------------------------------------------------*/
1565 if onc_det.deal_type = 'BOND' and l_amount_type = 'CPMADJ' and l_calc_type = 'COMPOUND COUPON' then
1566
1567 -----------------------------------------
1568 -- First transaction flag
1569 -----------------------------------------
1570 l_dummy_date := to_date(null);
1571 select nvl(min(COUPON_DATE),p_end_date)
1572 into l_dummy_date
1573 from XTR_BOND_COUPON_DATES
1574 where BOND_ISSUE_CODE = l_bond_issue
1575 and COUPON_DATE > l_deal_start;
1576 if p_end_date <= l_dummy_date then
1577 onc_det.first_trans_flag := 'Y';
1578 else
1579 onc_det.first_trans_flag := 'N';
1580 end if;
1581
1582 ---------------------------------------------
1583 -- Total full coupon and odd coupon maturity
1584 ---------------------------------------------
1585 l_no_quasi_coupon := 0;
1586 open TOTAL_FULL_COUPONS (l_bond_issue);
1587 fetch TOTAL_FULL_COUPONS into l_no_quasi_coupon, l_odd_coupon_maturity;
1588 close TOTAL_FULL_COUPONS;
1589
1590 ----------------------------------------------------------------------------------
1591 -- Fetch previous coupon date and previous FULL coupons
1592 ----------------------------------------------------------------------------------
1593 open PRV_COUPON_DATES;
1594 fetch PRV_COUPON_DATES INTO l_prev_coupon_date, l_num_full_cpn_previous;
1595 close PRV_COUPON_DATES;
1596 IF l_prev_coupon_date is null THEN
1597 l_prev_coupon_date := l_bond_commence;
1598 END IF;
1599
1600 ----------------------------------------------------------------------------------
1601 -- Fetch Next Coupon date
1602 ----------------------------------------------------------------------------------
1603 open NXT_COUPON_DATES;
1604 fetch NXT_COUPON_DATES INTO l_next_coupon_date;
1605 close NXT_COUPON_DATES;
1606 if l_next_coupon_date is null then
1607 l_next_coupon_date := l_coupon_end; -- ????????????????????????????????
1608 end if;
1609
1610 l_odd_coupon_start := XTR_MM_COVERS.ODD_COUPON_DATE(l_bond_commence,l_bond_maturity, l_frequency,'S');
1611
1612 FND_CURRENCY.Get_Info ( l_ccy,
1613 l_precision,
1614 l_ext_precision,
1615 l_min_acct_unit);
1616
1617 end if;
1618
1619 /*-----------------------------------------------------------------------*/
1620 /* 4. Determine begining of period accrued balance (l_accrls_amount_bal) */
1621 /*-----------------------------------------------------------------------*/
1622
1623 ----------------------------
1624 -- Find first time accrual
1625 ----------------------------
1626 l_accrls_amount_bal := 0;
1627 l_maturity_face_value := 0;
1628 l_first_accrual_indic := 'Y';
1629
1630 open CHK_FIRST_ACCRUAL;
1631 fetch CHK_FIRST_ACCRUAL into l_first_accrual_indic;
1632 close CHK_FIRST_ACCRUAL;
1633
1634 --start bug 2804548
1635 --EXP do not get accrued
1636 if l_deal_type='BOND' and l_amount_type='CPMADJ' and
1637 l_trans_nos is not null and l_deal_nos is not null and
1638 l_first_accrual_indic='Y' then
1639 v_ChkCpnRateReset_in.deal_type:=l_deal_type;
1640 v_ChkCpnRateReset_in.transaction_no:=l_trans_nos;
1641 v_ChkCpnRateReset_in.deal_no:=l_deal_nos;
1642 xtr_mm_covers.check_coupon_rate_reset(v_ChkCpnRateReset_in,
1643 v_ChkCpnRateReset_out);
1644 --if the coupon or its tax comp has not been reset
1645 --print out a warning message.
1646 if not v_ChkCpnRateReset_out.yes then
1647 FND_MESSAGE.Set_Name ('XTR','XTR_COUPON_RESET_DEAL');
1648 FND_MESSAGE.Set_Token ('DEAL_NO',l_deal_nos);
1649 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
1653 --end bug 2804548
1650 retcode:=1;
1651 end if;
1652 end if;
1654
1655 if l_deal_type = 'BOND' and l_amount_type in ('SLDISC','SLPREM') then
1656
1657 if l_first_accrual_indic = 'Y' then
1658 --------------------------------
1659 -- Deal has never been accrued.
1660 --------------------------------
1661 l_maturity_face_value := round(onc_det.main_amount,l_rounding); -- not an interest amount
1662 l_period_start_face_value := round(onc_det.main_amount,l_rounding); -- not an interest amount
1663 l_to_date_amort_amt := 0;
1664 l_to_date_amort_amt_bal := 0;
1665
1666 else
1667
1668 -------------------------------------------------------------------
1669 -- 2753088,2751078 - SLPREM/SLDISC are generated after total resale
1670 -------------------------------------------------------------------
1671 l_dummy := 0;
1672 l_dummy_date := p_start_date - 1; -- to find all Resale Start Date < Batch Start Date
1673 open TOTAL_RESALE_FACE_VALUE (l_deal_nos, l_dummy_date);
1674 fetch TOTAL_RESALE_FACE_VALUE into l_dummy;
1675 close TOTAL_RESALE_FACE_VALUE;
1676 if onc_det.main_amount - l_dummy = 0 then
1677 Goto NEXT_ACCRUAL_DEALS;
1678 end if;
1679
1680 ------------------------------------------------------------
1681 -- Get remaining face value at beginning of the batch period
1682 ------------------------------------------------------------
1683 open GET_PRV_BAL;
1684 fetch GET_PRV_BAL into l_accrls_amount_bal, l_maturity_face_value;
1685 close GET_PRV_BAL;
1686 l_period_start_face_value := l_maturity_face_value;
1687 l_to_date_amort_amt := l_accrls_amount_bal;
1688 l_to_date_amort_amt_bal := l_accrls_amount_bal;
1689
1690 end if;
1691
1692 elsif l_deal_type = 'BOND' and l_amount_type = 'CPMADJ' then
1693
1694 -- Bug 2422480.
1695 -- To resolve Testing Issue 2.
1696 -- Obtain initial purchase interest if processing 1st coupon of deal.
1697
1698 If (l_coupon_start <= l_deal_start) then
1699 -------------------------------------------------------------------------------------------
1700 -- Initial Interest is the Cumulative Interest already accounted for in this first coupon.
1701 -------------------------------------------------------------------------------------------
1702 open GET_BOND_DDA_INT;
1703 fetch GET_BOND_DDA_INT into l_dda_INT;
1704 close GET_BOND_DDA_INT;
1705 End If;
1706
1707 --if l_first_accrual_indic = 'Y' and l_cum_ex = 'CUM' then
1708 if l_first_accrual_indic = 'Y' and l_coupon_start <= l_deal_start then
1709 l_accrls_amount_bal := l_dda_INT;
1710 l_face_value_bal := l_maturity_amount;
1711
1712 else
1713
1714 open GET_PRV_BAL;
1715 fetch GET_PRV_BAL into l_accrls_amount_bal, l_face_value_bal;
1716 -----------------------------------------------------------------------------------------
1717 -- If found:
1718 -- Coupon has been processed before. Obtain Cumulative Interest already accounted for
1719 -- based on the latest 'calculation' face value at the end of the last accrual period.
1720 -----------------------------------------------------------------------------------------
1721 -- OR
1722 -----------------------------------------------------------------------------------------
1723 -- If not found:
1724 -- New coupon being processed. Determine how much of the original face value
1725 -- has been resold prior to start of the current coupon period.
1726 -----------------------------------------------------------------------------------------
1727 if GET_PRV_BAL%NOTFOUND then
1728
1729 ------------------------------------------------------------------------------
1730 -- 2753088,2751078 - common logic, replace with cursor TOTAL_RESALE_FACE_VALUE
1731 ------------------------------------------------------------------------------
1732 if l_calc_type = 'COMPOUND COUPON' then
1733 open TOTAL_RESALE_FACE_VALUE (l_deal_nos, p_end_date); -- ??????????????
1734 fetch TOTAL_RESALE_FACE_VALUE into l_cumm_resale_face;
1735 close TOTAL_RESALE_FACE_VALUE;
1736 else
1737 open TOTAL_RESALE_FACE_VALUE (l_deal_nos, l_coupon_start);
1738 fetch TOTAL_RESALE_FACE_VALUE into l_cumm_resale_face;
1739 close TOTAL_RESALE_FACE_VALUE;
1740 end if;
1741
1742 l_face_value_bal := l_maturity_amount - l_cumm_resale_face;
1743 /* for bug 5622679 starts */
1744 else
1745 open TOTAL_RESALE_FACE_VALUE (l_deal_nos, p_start_date-1);
1746 fetch TOTAL_RESALE_FACE_VALUE into l_cumm_resale_face;
1747 close TOTAL_RESALE_FACE_VALUE;
1748 if round(l_maturity_amount - l_cumm_resale_face,l_rounding) = 0 then
1749 close GET_PRV_BAL;
1750 Goto NEXT_ACCRUAL_DEALS;
1751 end if;
1752 end if;
1753 if GET_PRV_BAL%isopen then
1754 close GET_PRV_BAL;
1755 end if;
1756 /* for bug 5622679 ends */
1757 end if;
1758
1762
1759 if round(l_face_value_bal,l_rounding) = 0 then -- not an interest amount
1760 Goto NEXT_ACCRUAL_DEALS;
1761 end if;
1763 else
1764 open GET_PRV_BAL;
1765 fetch GET_PRV_BAL into l_accrls_amount_bal, l_dummy;
1766 close GET_PRV_BAL;
1767
1768 end if;
1769
1770
1771 /*-------------------------------------------------------------------------------------------*/
1772 /* 1. Calculate accrual / amortisation periods (period_start, period_end, l_length_of_deal) */
1773 /*-------------------------------------------------------------------------------------------*/
1774
1775 -----------------
1776 -- Period Start
1777 -----------------
1778 if l_deal_type = 'BOND' and l_amount_type = 'CPMADJ' then
1779
1780 -- Bug 2422480 changes start.
1781 -- Adjust period_start to reflect the actual start date for which the periodic
1782 -- accrued interest amount is to be calculated for.
1783
1784 If (nvl(l_first_accrual_indic,'N') = 'Y') then
1785
1786 -- Coupon being processed for the 1st time.
1787
1788 If (onc_det.trans_nos = 2) then
1789
1790 -- This is also the first coupon of the deal.
1791 -- Periodic interest is to exclude purchase interest.
1792 -- Therefore, period start should be deal start date.
1793
1794 period_start := l_deal_start;
1795 Else
1796 -- Not the first coupon of the deal.
1797 -- Periodic interest is to be calculated from the coupon start date.
1798
1799 period_start := l_coupon_start;
1800 End If;
1801 Else
1802 -- Coupon has been processed before.
1803 -- period start should be the batch period start date.
1804
1805 period_start := p_start_date;
1806 End If;
1807
1808 -- End 2422480 changes.
1809
1810 else
1811 period_start := onc_det.date_from;
1812
1813 end if;
1814
1815
1816 ----------------
1817 -- Period End
1818 ----------------
1819 if onc_det.date_type_to = 'PEREND' or onc_det.date_to is null then
1820 period_end := p_end_date;
1821
1822 -- AW 2113171 If DATE_TO is null, keep DATE_TO null for ONC
1823 if onc_det.deal_type <> 'ONC' then
1824 onc_det.date_to := p_end_date;
1825 end if;
1826
1827
1828 elsif l_amount_type ='PREMADJ' and onc_det.deal_type in ('BDO','IRO','SWPTN','FXO') then
1829 period_end := nvl(onc_det.deal_action_date,onc_det.date_to);
1830
1831 elsif l_deal_type = 'BOND' and l_amount_type = 'CPMADJ' then
1832 period_end := least(l_coupon_end, p_end_date);
1833 l_calc_period_end := period_end;
1834
1835
1836 else
1837 ---------------------------------------------------
1838 -- FRA, BDO, IRO, SWPTN : INTADJ, SLPREM, SLDISC -- AW 1395208
1839 -- Bond (Discount/Premium) comes in this category
1840 ---------------------------------------------------
1841 period_end := onc_det.date_to;
1842
1843
1844 end if;
1845
1846 --------------------------------------------------------------------------------
1847 -- Calculate Length of Deal and Year Basis
1848 --------------------------------------------------------------------------------
1849
1850 if onc_det.deal_type = 'BOND' and l_amount_type = 'CPMADJ' then
1851
1852 if l_calc_type = 'COMPOUND COUPON' then
1853 -------------------------------------------------------------------------
1854 -- l_length_of_deal = 'No of Days in Current Coupon' for COMPOUND COUPON
1855 -------------------------------------------------------------------------
1856 if p_end_date < l_odd_coupon_maturity then
1857
1858 XTR_CALC_P.CALC_DAYS_RUN_C(l_odd_coupon_start,
1859 l_odd_coupon_maturity,
1860 l_year_calc_type,
1861 l_frequency,
1862 l_length_of_deal,
1863 l_yr_basis,
1864 null,
1865 onc_det.day_count_type, -- AW Japan Project
1866 onc_det.first_trans_flag); -- AW Japan Project
1867 else
1868 XTR_CALC_P.CALC_DAYS_RUN_C(l_prev_coupon_date,
1869 l_next_coupon_date,
1870 l_year_calc_type,
1871 l_frequency,
1872 l_length_of_deal,
1873 l_yr_basis,
1874 null,
1875 onc_det.day_count_type, -- AW Japan Project
1876 onc_det.first_trans_flag); -- AW Japan Project
1877 end if;
1878 else
1879 -- AW 2113171 -- DO NOT ADJUST FOR Length of deal.
1880 XTR_CALC_P.CALC_DAYS_RUN_C(l_coupon_start,
1881 l_coupon_end,
1882 l_year_calc_type,
1883 l_frequency,
1887 onc_det.day_count_type, -- AW Japan Project
1884 l_length_of_deal,
1885 l_yr_basis,
1886 null,
1888 onc_det.first_trans_flag); -- AW Japan Project
1889 end if;
1890
1891 else
1892 if period_end > period_start then
1893 if onc_det.deal_type = 'BOND' then
1894
1895 -- AW 2113171 -- DO NOT ADJUST FOR Length of deal.
1896 XTR_CALC_P.CALC_DAYS_RUN_C(period_start,
1897 period_end,
1898 l_year_calc_type,
1899 l_frequency,
1900 l_length_of_deal,
1901 l_yr_basis,
1902 null,
1903 onc_det.day_count_type, -- AW Japan Project
1904 onc_det.first_trans_flag); -- AW Japan Project
1905 else
1906 -- AW Japan Project
1907 if ((onc_det.day_count_type='PRIOR') or (onc_det.day_count_type='B' and onc_det.first_trans_flag='Y')) and
1908 onc_det.deal_type = 'ONC' and onc_det.date_to is null then
1909 -- if l_days_adjust = 'PRIOR' and onc_det.deal_type = 'ONC' and onc_det.date_to is null then
1910 -- AW 2113171 Adjust length of deal for ONC with no maturity date
1911 XTR_CALC_P.CALC_DAYS_RUN(period_start - onc_det.forward_adjust,
1912 period_end,
1913 l_year_calc_type,
1914 l_length_of_deal,
1915 l_yr_basis,
1916 onc_det.forward_adjust,
1917 onc_det.day_count_type, -- AW Japan Project
1918 null); -- AW Japan Project
1919 else
1920 -- AW 2113171 -- DO NOT ADJUST FOR Length of deal.
1921 XTR_CALC_P.CALC_DAYS_RUN(period_start,
1922 period_end,
1923 l_year_calc_type,
1924 l_length_of_deal,
1925 l_yr_basis,
1926 null, -- AW Japan Project
1927 onc_det.day_count_type, -- AW Japan Project
1928 onc_det.first_trans_flag); -- AW Japan Project
1929 end if;
1930 end if;
1931 else
1932 -- AW Japan Project
1933 if ((onc_det.day_count_type='PRIOR') or (onc_det.day_count_type='B' and onc_det.first_trans_flag='Y')) and
1934 onc_det.deal_type = 'ONC' and onc_det.date_to is null then
1935 -- if onc_det.day_count_type = 'PRIOR' and onc_det.deal_type = 'ONC' and onc_det.date_to is null then
1936 -- if onc_det.day_count_type in ('PRIOR','B') and
1937 -- onc_det.deal_type = 'ONC' and onc_det.date_to is null then
1938 -- if l_days_adjust = 'PRIOR' and onc_det.deal_type = 'ONC' and onc_det.date_to is null then
1939 -- AW 2113171 -- To handle ONC with no maturity date and same day batch
1940 XTR_CALC_P.CALC_DAYS_RUN(period_start - onc_det.forward_adjust,
1941 period_end,
1942 l_year_calc_type,
1943 l_length_of_deal,
1944 l_yr_basis,
1945 onc_det.forward_adjust,
1946 onc_det.day_count_type, -- AW Japan Project
1947 null); -- AW Japan Project
1948
1949 else
1950 -- AW Japan Project
1951 if onc_det.day_count_type = 'B' and onc_det.first_trans_flag = 'Y' and
1952 onc_det.deal_type in ('TMM','RTMM','ONC','IRS','BOND','NI') then -- no accrual for FRA
1953 l_length_of_deal := 1;
1954 else
1955 l_length_of_deal := 0; -- AW Japan Project This is for IRO, BDO, FXO, SWPTN
1956 end if;
1957 end if;
1958
1959 end if;
1960
1961 end if;
1962
1963 l_yr_basis := nvl(l_yr_basis,365);
1964
1965
1966 /*---------------------------------------*/
1967 /* 1a. Bond Discount/Premium Adjustment */
1968 /*---------------------------------------*/
1969
1970 if onc_det.deal_type = 'BOND' and l_amount_type in ('SLPREM','SLDISC') then
1971 ----------------------------------------------------------------
1972 -- Any resale that needs to be recognised in this batch period.
1973 ----------------------------------------------------------------
1974
1975 l_resale_total_amort := 0;
1976 l_resale_cumm_amort := 0;
1977 l_period_resale_amort := 0;
1978
1979 open BOND_DISC_PREM;
1980 fetch BOND_DISC_PREM into discprem_det;
1981 while BOND_DISC_PREM%FOUND loop
1982
1983 -----------------------------------------------------------------------------
1987 -- When Both days type is selected, system should include both start date and
1984 -- AW 2113171 -- Consider matured hence do not adjust even for FORWARD
1985 -----------------------------------------------------------------------------
1986 -- AW Japan Project
1988 -- resale date to calculate number of days.
1989 -----------------------------------------------------------------------------
1990 XTR_CALC_P.CALC_DAYS_RUN_C(period_start,
1991 discprem_det.resale_recognition_date,
1992 l_year_calc_type,
1993 l_frequency,
1994 l_no_of_days,
1995 deal_yr_basis,
1996 null,
1997 onc_det.day_count_type, -- AW Japan Project
1998 onc_det.first_trans_flag); -- AW Japan Project
1999
2000 -----------------------------------------------------------------------------------------------
2001 -- Calc Total Amort Disc/Prem for the Resale Amount from Deal's Eligible Date to Resale Date.
2002 -----------------------------------------------------------------------------------------------
2003 l_resale_total_amort := round(abs(discprem_det.front_end_prem_disc) *
2004 (l_no_of_days/l_length_of_deal),l_rounding);
2005
2006 -----------------------------------------------------------------------------------------------
2007 -- Allocate Amort Disc/Prem for the Resale Amount already accounted for in previous periods.
2008 -----------------------------------------------------------------------------------------------
2009 -- In case this will cause a divide by zero error.
2010 if l_period_start_face_value <> 0 then
2011 l_resale_cumm_amort := round(discprem_det.face_value/l_period_start_face_value *
2012 l_to_date_amort_amt,l_rounding);
2013 else
2014 l_resale_cumm_amort := 0;
2015 end if;
2016
2017 -------------------------------------------------------------------------------------
2018 -- Calculate the Remaining Cummulative Amortization not yet accounted for.
2019 -------------------------------------------------------------------------------------
2020 l_to_date_amort_amt_bal := l_to_date_amort_amt_bal - l_resale_cumm_amort;
2021
2022 -------------------------------------------------------------------------------------
2023 -- Calculate the Period Disc/Prem Amortization for the Resale Amount.
2024 -------------------------------------------------------------------------------------
2025 l_period_resale_amort := abs(discprem_det.front_end_prem_disc) - l_resale_cumm_amort;
2026
2027 -------------------------------------------------------------------------------------
2028 -- Calculate Remaining Face Value to be accounted for.
2029 -------------------------------------------------------------------------------------
2030 l_maturity_face_value := l_maturity_face_value - discprem_det.face_value;
2031
2032 --------------------------------------
2033 -- Period Amort for the Resold Amount.
2034 --------------------------------------
2035 -- AW 2113171 Do not display if both accrual amount and balance are zero.
2036 if l_period_resale_amort <> 0 then
2037 -- if l_period_resale_amort <> 0 and l_resale_total_amort <> 0 then -- 2737823
2038 insert into XTR_ACCRLS_AMORT (BATCH_ID, DEAL_NO, TRANS_NO,
2039 COMPANY_CODE, DEAL_SUBTYPE, DEAL_TYPE, CURRENCY,
2040 PERIOD_FROM, PERIOD_TO,
2041 CPARTY_CODE, PRODUCT_TYPE, PORTFOLIO_CODE,
2042 INTEREST_RATE, TRANSACTION_AMOUNT,
2043 AMOUNT_TYPE, ACTION_CODE,
2044 ACCRLS_AMOUNT,
2045 CALC_FACE_VALUE,
2046 YEAR_BASIS, FIRST_ACCRUAL_INDIC,
2047 ACTUAL_START_DATE, ACTUAL_MATURITY_DATE,
2048 NO_OF_DAYS, ACCRLS_AMOUNT_BAL)
2049 values (l_batch_id, onc_det.deal_nos, discprem_det.cross_ref_no,
2050 p_company, onc_det.subtype, onc_det.deal_type,onc_det.ccy,
2051 decode(l_first_accrual_indic,'Y',period_start,
2052 greatest(period_start,p_start_date)),
2053 discprem_det.resale_recognition_date,
2054 onc_det.cparty, onc_det.product, onc_det.portfolio,
2055 onc_det.rate, onc_det.main_amount,
2056 decode(sign(100-l_clean_price),-1,'SLPREM','SLDISC'), 'POS',
2057 l_period_resale_amort,
2058 discprem_det.face_value,
2059 deal_yr_basis, l_first_accrual_indic,
2060 decode(l_first_accrual_indic,'Y',period_start,
2064 end if;
2061 greatest(period_start,p_start_date)),
2062 discprem_det.resale_recognition_date,
2063 l_no_of_days, l_resale_total_amort);
2065
2066 -----------------------------------------------------
2067 -- Reverse Cumulative Amort for the Resold Amount.
2068 -----------------------------------------------------
2069 -- AW 2753088,2751078 Do not display if accrual amount is zero.
2070 if abs(nvl(discprem_det.front_end_prem_disc,0)) <> 0 then
2071 insert into XTR_ACCRLS_AMORT (BATCH_ID, DEAL_NO, TRANS_NO,
2072 COMPANY_CODE, DEAL_SUBTYPE, DEAL_TYPE, CURRENCY,
2073 PERIOD_FROM, PERIOD_TO,
2074 CPARTY_CODE, PRODUCT_TYPE, PORTFOLIO_CODE,
2075 INTEREST_RATE, TRANSACTION_AMOUNT,
2076 AMOUNT_TYPE, ACTION_CODE,
2077 ACCRLS_AMOUNT,
2078 CALC_FACE_VALUE,
2079 YEAR_BASIS, FIRST_ACCRUAL_INDIC,
2080 ACTUAL_START_DATE, ACTUAL_MATURITY_DATE,
2081 NO_OF_DAYS, ACCRLS_AMOUNT_BAL)
2082 values(l_batch_id, onc_det.deal_nos, discprem_det.cross_ref_no,
2083 p_company, onc_det.subtype, onc_det.deal_type,onc_det.ccy,
2084 period_start, discprem_det.resale_recognition_date,
2085 onc_det.cparty, onc_det.product, onc_det.portfolio,
2086 onc_det.rate, onc_det.main_amount,
2087 decode(sign(100-l_clean_price),-1,'SLPREM','SLDISC'), 'REV',
2088 abs(discprem_det.front_end_prem_disc),
2089 discprem_det.face_value,
2090 deal_yr_basis, l_first_accrual_indic,
2091 period_start, discprem_det.resale_recognition_date,
2092 l_no_of_days, abs(discprem_det.front_end_prem_disc));
2093 end if;
2094
2095 -----------------------------------------------------
2096 -- Reverse Unamort Balance for the Resold Amount.
2097 -----------------------------------------------------
2098 -- AW 2753088,2751078 Do not display if accrual amount is zero.
2099 if abs(nvl(discprem_det.front_end_prem_disc,0)) <> 0 and l_resale_total_amort <> 0 then
2100 insert into XTR_ACCRLS_AMORT (BATCH_ID, DEAL_NO, TRANS_NO,
2101 COMPANY_CODE, DEAL_SUBTYPE, DEAL_TYPE, CURRENCY,
2102 PERIOD_FROM, PERIOD_TO,
2103 CPARTY_CODE, PRODUCT_TYPE, PORTFOLIO_CODE,
2104 INTEREST_RATE, TRANSACTION_AMOUNT,
2105 AMOUNT_TYPE, ACTION_CODE,
2106 ACCRLS_AMOUNT,
2107 CALC_FACE_VALUE,
2108 YEAR_BASIS, FIRST_ACCRUAL_INDIC,
2109 ACTUAL_START_DATE, ACTUAL_MATURITY_DATE,
2110 NO_OF_DAYS, ACCRLS_AMOUNT_BAL)
2111 values(l_batch_id, onc_det.deal_nos, discprem_det.cross_ref_no,
2112 p_company, onc_det.subtype, onc_det.deal_type,onc_det.ccy,
2113 period_start, discprem_det.resale_recognition_date,
2114 onc_det.cparty, onc_det.product, onc_det.portfolio,
2115 onc_det.rate, onc_det.main_amount,
2116 decode(sign(100-l_clean_price),-1,'SLUAMP','SLUAMD'), 'REV',
2117 abs(discprem_det.front_end_prem_disc) - l_resale_total_amort,
2118 discprem_det.face_value,
2119 deal_yr_basis, l_first_accrual_indic,
2120 period_start, discprem_det.resale_recognition_date,
2121 l_no_of_days, l_resale_total_amort);
2122 end if;
2123
2124 fetch BOND_DISC_PREM into discprem_det;
2125 end loop;
2126 close BOND_DISC_PREM;
2127 end if;
2128
2129
2130 /*--------------------------------------------------------------------------*/
2131 /* 1b. Recalc coupon amount at the beginning of the actual coupon period. */
2132 /*--------------------------------------------------------------------------*/
2133 if onc_det.deal_type = 'BOND' and l_amount_type = 'CPMADJ' then
2134
2135 if l_calc_type in ('FLAT COUPON','FL REGULAR') then --b 2804548
2136 l_adj_coupon_amt := (l_face_value_bal * (l_coupon_rate/100))/l_frequency;
2137
2141 l_comp_coupon.p_odd_coupon_start := l_odd_coupon_start;
2138 elsif l_calc_type = 'COMPOUND COUPON' then
2139
2140 l_comp_coupon.p_bond_start_date := l_bond_commence;
2142 l_comp_coupon.p_odd_coupon_maturity := l_odd_coupon_maturity;
2143 l_comp_coupon.p_full_coupon := l_no_quasi_coupon;
2144 l_comp_coupon.p_coupon_rate := l_coupon_rate;
2145 l_comp_coupon.p_maturity_amount := l_face_value_bal; -- Remaining Face Value
2146 l_comp_coupon.p_precision := l_precision;
2147 l_comp_coupon.p_rounding_type := onc_det.rounding_type;
2148 l_comp_coupon.p_year_calc_type := l_year_calc_type;
2149 l_comp_coupon.p_frequency := l_frequency;
2150 l_comp_coupon.p_day_count_type := onc_det.day_count_type;
2151 l_comp_coupon.p_amount_redemption_ind := 'A';
2152
2153 l_adj_coupon_amt := XTR_MM_COVERS.CALC_COMPOUND_COUPON_AMT(l_comp_coupon);
2154
2155 else
2156 l_adj_coupon_amt := l_face_value_bal * (l_coupon_rate/100)*(l_length_of_deal/l_yr_basis);
2157 end if;
2158
2159 -- Bug 2422480 additions.
2160 -- In an attempt to minimize rounding issues, round the coupon amount
2161 -- based on the interest rounding of the bond issue.
2162
2163 l_adj_coupon_amt := xtr_fps2_p.interest_round(l_adj_coupon_amt,l_rounding,onc_det.rounding_type);
2164
2165 -- End 2422480 additions.
2166 end if;
2167
2168
2169 /*-----------------------------------------------------------*/
2170 /* 2. Determine TO DATE (maturing_date , l_actual_maturity) */
2171 /*-----------------------------------------------------------*/
2172
2173 ----------
2174 -- NI
2175 ----------
2176 if onc_det.deal_type ='NI' then
2177 if nvl(onc_det.deal_action_date,onc_det.date_to)<= p_end_date then
2178
2179 maturing_date := nvl(onc_det.deal_action_date,onc_det.date_to);
2180
2181 -- AW 2113171 -- Do not adjust if either maturity or resale within batch end date
2182 -- if onc_det.deal_action_date is not null then
2183 l_day_adjust_flag := 'N';
2184 -- end if;
2185
2186 else
2187 maturing_date := p_end_date;
2188 end if;
2189
2190 -----------------
2191 -- Bond Coupons
2192 -----------------
2193 elsif onc_det.deal_type = 'BOND' and l_amount_type = 'CPMADJ' then
2194
2195 -----------------------------------------------------------------------------------------------
2196 -- Determine Start and End dates of the resale period. This will differ from the batch start
2197 -- and batch end periods because resales occurring ona certain date will only affect
2198 -- interest accrual calculations starting on the day after the resale.
2199 -----------------------------------------------------------------------------------------------
2200
2201 -- Changed for 2422480.
2202
2203 If period_start = l_coupon_start then
2204 l_elig_resale_start := period_start + 1;
2205 Else
2206 l_elig_resale_start := period_start;
2207 End If;
2208
2209 l_elig_resale_end := l_calc_period_end;
2210
2211 -- End 2422480 changes.
2212
2213 /* 2422480
2214 if period_start = l_coupon_start then
2215 l_elig_resale_start := period_start;
2216 else
2217 l_elig_resale_start := period_start - 1;
2218 end if;
2219
2220 if period_end = l_coupon_end then
2221 l_elig_resale_end := period_end;
2222 else
2223 l_elig_resale_end := period_end - 1;
2224 end if;
2225 */
2226 ----------------------------------------
2227 -- Others (exclude NI and BOND-Coupon)
2228 ----------------------------------------
2229 else
2230 -- AW 2113171 To handle ONC with no maturity date
2231 -- if onc_det.date_to < p_end_date then -- old
2232 if onc_det.date_to <= p_end_date and onc_det.date_to is not null then
2233
2234 maturing_date := onc_det.date_to;
2235
2236 else
2237 maturing_date := p_end_date;
2238
2239 end if;
2240 end if;
2241
2242 l_actual_maturity := maturing_date;
2243
2244 /*---------------------------------------------------------------------------------------------*/
2245 /* 3. Day adjustment - Forward or Arrears (l_actual_start_date, deal_yr_basis, l_no_of_days) */
2246 /*---------------------------------------------------------------------------------------------*/
2247
2248 if not (onc_det.deal_type = 'BOND' and l_amount_type = 'CPMADJ') then
2249
2250 -- AW Japan Project
2251 if onc_det.day_count_type='PRIOR' and maturing_date<>nvl(onc_det.date_to,p_end_date) and
2252 l_day_adjust_flag ='Y' then
2253 -- if l_days_adjust='PRIOR' and maturing_date<>nvl(onc_det.date_to,p_end_date) and l_day_adjust_flag ='Y' then
2254
2255 if period_start < maturing_date then
2256 if onc_det.deal_type = 'BOND' then
2257 XTR_CALC_P.CALC_DAYS_RUN_C(period_start - onc_det.forward_adjust,
2258 maturing_date,
2259 l_year_calc_type,
2263 onc_det.forward_adjust,
2260 l_frequency,
2261 l_no_of_days,
2262 deal_yr_basis,
2264 onc_det.day_count_type, -- AW Japan Project
2265 null); -- AW Japan Project
2266 else
2267 -- AW 2113171 To handle ONC with no maturity date
2268 XTR_CALC_P.CALC_DAYS_RUN(period_start - onc_det.forward_adjust,
2269 maturing_date,
2270 l_year_calc_type,
2271 l_no_of_days,
2272 deal_yr_basis,
2273 onc_det.forward_adjust,
2274 onc_det.day_count_type, -- AW Japan Project
2275 null); -- AW Japan Project
2276 end if;
2277 else
2278 -- AW 2113171 Similar to One day batch
2279 if onc_det.day_count_type = 'PRIOR' and period_start = maturing_date then
2280 -- if l_days_adjust = 'PRIOR' and period_start = maturing_date then
2281 XTR_CALC_P.CALC_DAYS_RUN(period_start - onc_det.forward_adjust,
2282 maturing_date,
2283 l_year_calc_type,
2284 l_no_of_days,
2285 deal_yr_basis,
2286 onc_det.forward_adjust,
2287 onc_det.day_count_type, -- AW Japan Project
2288 null); -- AW Japan Project
2289 else
2290 l_no_of_days := 0;
2291 end if;
2292 end if;
2293
2294 if l_no_of_days > l_length_of_deal then
2295 l_no_of_days := l_length_of_deal;
2296 end if;
2297
2298 else
2299 if period_start < maturing_date then
2300 if onc_det.deal_type = 'BOND' then
2301
2302 if maturing_date <= onc_det.date_to then
2303 -- AW 2113171 Do not adjust if matured
2304 XTR_CALC_P.CALC_DAYS_RUN_C(period_start,
2305 maturing_date,
2306 l_year_calc_type,
2307 l_frequency,
2308 l_no_of_days,
2309 deal_yr_basis,
2310 null,
2311 onc_det.day_count_type, -- AW Japan Project
2312 onc_det.first_trans_flag); -- AW Japan Project
2313 else
2314 XTR_CALC_P.CALC_DAYS_RUN_C(period_start - onc_det.forward_adjust,
2315 maturing_date,
2316 l_year_calc_type,
2317 l_frequency,
2318 l_no_of_days,
2319 deal_yr_basis,
2320 onc_det.forward_adjust,
2321 onc_det.day_count_type, -- AW Japan Project
2322 onc_det.first_trans_flag); -- AW Japan Project
2323
2324 end if;
2325 else
2326 if maturing_date <= onc_det.date_to and onc_det.date_to is not null then
2327 -- AW 2113171 Do not adjust for matured deal
2328 XTR_CALC_P.CALC_DAYS_RUN(period_start,
2329 maturing_date,
2330 l_year_calc_type,
2331 l_no_of_days,
2332 deal_yr_basis,
2333 null,
2334 onc_det.day_count_type, -- AW Japan Project
2335 onc_det.first_trans_flag); -- AW Japan Project
2336
2337 else
2338 -- AW 2113171 To handle ONC with no maturity date
2339 XTR_CALC_P.CALC_DAYS_RUN(period_start - onc_det.forward_adjust,
2340 maturing_date,
2341 l_year_calc_type,
2342 l_no_of_days,
2343 deal_yr_basis,
2344 onc_det.forward_adjust,
2345 onc_det.day_count_type, -- AW Japan Project
2346 onc_det.first_trans_flag); -- AW Japan Project
2347 end if;
2348
2349 /* old
2350 -- AW 2113171
2351 -- Problem - this does not work for FXO
2352 XTR_CALC_P.CALC_DAYS_RUN(period_start - l_forward_adjust,
2353 maturing_date,
2357 l_forward_adjust);
2354 l_year_calc_type,
2355 l_no_of_days,
2356 deal_yr_basis,
2358 */
2359
2360 end if;
2361 else
2362 -- AW Japan Project
2363 if onc_det.day_count_type = 'PRIOR' then
2364 -- if l_days_adjust = 'PRIOR' then
2365 -- AW 2113171 Same day batch for all deal types need to adjust
2366 XTR_CALC_P.CALC_DAYS_RUN(period_start - onc_det.forward_adjust,
2367 maturing_date,
2368 l_year_calc_type,
2369 l_no_of_days,
2370 deal_yr_basis,
2371 onc_det.forward_adjust,
2372 onc_det.day_count_type, -- AW Japan Project
2373 null); -- AW Japan Project
2374 elsif onc_det.day_count_type = 'B' then -- AW Japan Project
2375 -- AW Japan Project
2376 XTR_CALC_P.CALC_DAYS_RUN(period_start - onc_det.forward_adjust,
2377 maturing_date,
2378 l_year_calc_type,
2379 l_no_of_days,
2380 deal_yr_basis,
2381 onc_det.forward_adjust,
2382 onc_det.day_count_type,
2383 onc_det.first_trans_flag);
2384 else
2385 l_no_of_days :=0;
2386 end if;
2387 end if;
2388 end if;
2389
2390 deal_yr_basis := nvl(deal_yr_basis,365);
2391
2392 l_actual_start_date := period_start;
2393
2394 end if;
2395
2396
2397 /*------------------------------------------------------------------*/
2398 /* 5. Calculate accrued-to-date balance (l_amount_to_accrue_amort) */
2399 /*------------------------------------------------------------------*/
2400
2401 ---------------------------
2402 -- i) ONC, TMM, RTMM, IRS
2403 ---------------------------
2404 if onc_det.deal_type in ('ONC','TMM','RTMM','IRS') then
2405
2406
2407 if onc_det.deal_type = 'ONC' and onc_det.date_to is null then
2408 ----------------------------------------------------------------
2409 -- 2781438 (3450474) separate for ONC without maturity date
2410 ----------------------------------------------------------------
2411 -- ONC deal without maturity date: always calculate Accrual Amt.
2412 ----------------------------------------------------------------
2413 l_amount_to_accrue_amort := xtr_fps2_p.interest_round(abs(onc_det.main_amount * onc_det.rate /
2414 (deal_yr_basis * 100) * l_no_of_days),l_rounding,onc_det.rounding_type);
2415
2416 elsif onc_det.date_to > p_end_date then
2417 ----------------------------------------------------------------
2418 -- Deal not yet matured -- 2781438 (3450474) Use overriden amount
2419 ----------------------------------------------------------------
2420 if l_length_of_deal > 0 and onc_det.override_amount is not null then
2421 if l_accrls_amount_bal > onc_det.override_amount then -- Override amount exceeded
2422 l_amount_to_accrue_amort := onc_det.override_amount;
2423 elsif l_accrls_amount_bal = onc_det.override_amount then -- Override amount fully accrued
2424 Goto NEXT_ACCRUAL_DEALS;
2425 else -- May have over-accrued, but not the full amount
2426 l_amount_to_accrue_amort := xtr_fps2_p.interest_round(abs(onc_det.override_amount * l_no_of_days/
2427 l_length_of_deal),l_rounding,onc_det.rounding_type);
2428 end if;
2429
2430 else
2431 l_amount_to_accrue_amort := 0;
2432 end if;
2433
2434 else
2435 ----------------------------------------------------------
2436 -- AW Japan Project
2437 -- Deal matured
2438 ----------------------------------------------------------
2439 if onc_det.override_amount is not null then
2440 if l_accrls_amount_bal = onc_det.override_amount then -- Override amount fully accrued
2441 Goto NEXT_ACCRUAL_DEALS;
2442 else
2443 l_amount_to_accrue_amort := onc_det.override_amount; -- Final accrual should be Override Amount
2444 end if;
2445 else
2446 l_amount_to_accrue_amort := xtr_fps2_p.interest_round(abs(onc_det.main_amount* l_length_of_deal *
2447 onc_det.rate / (l_yr_basis * 100)),l_rounding,onc_det.rounding_type);
2448 end if;
2449 end if;
2450
2451 --------------------
2452 -- Reference Amount
2453 --------------------
2454 if onc_det.override_amount is null or (onc_det.deal_type = 'ONC' and onc_det.date_to is null) then
2455 -- AW Japan Project
2456 if onc_det.deal_type = 'ONC' then
2460 onc_det.main_amount := xtr_fps2_p.interest_round(abs(onc_det.main_amount*l_length_of_deal*onc_det.rate/
2457 onc_det.main_amount := xtr_fps2_p.interest_round(abs(onc_det.main_amount*l_no_of_days*onc_det.rate/
2458 (l_yr_basis * 100)),l_rounding,onc_det.rounding_type);
2459 else
2461 (l_yr_basis * 100)),l_rounding,onc_det.rounding_type);
2462 end if;
2463 else
2464 onc_det.main_amount := onc_det.override_amount;
2465 end if;
2466
2467 -----------------------------
2468 -- ii) BDO, IRO, SWPTN, FXO
2469 -----------------------------
2470 elsif l_amount_type = 'PREMADJ' and onc_det.deal_type in('BDO','IRO','SWPTN','FXO') and
2471 onc_det.status_code in ('EXERCISED','EXPIRED') and
2472 nvl(onc_det.deal_action_date,onc_det.date_to) < p_end_date then
2473
2474 l_amount_to_accrue_amort := onc_det.main_amount;
2475
2476 ----------------------------------
2477 -- iii) Bond (Discount/Premium)
2478 ----------------------------------
2479 elsif onc_det.deal_type = 'BOND' and l_amount_type in ('SLDISC','SLPREM') then
2480 -------------------------------------------------------------------
2481 -- Calculate Total Amortize Disc/Prem for the Remaining Face Value.
2482 -------------------------------------------------------------------
2483 l_amount_to_accrue_amort := xtr_fps2_p.interest_round(abs(l_maturity_face_value *
2484 ((100-l_clean_price)/100)*(l_no_of_days/l_length_of_deal)),
2485 l_rounding,onc_det.rounding_type);
2486
2487 -------------------------------------------------------------------
2488 -- Calculate Period Amortize Disc/Prem for the Remaining Face Value.
2489 -------------------------------------------------------------------
2490 l_period_accrual_amount := nvl(l_amount_to_accrue_amort,0) - nvl(l_to_date_amort_amt_bal,0);
2491
2492 -----------------------
2493 -- iv) Bond Coupons
2494 -----------------------
2495 elsif onc_det.deal_type = 'BOND' and l_amount_type = 'CPMADJ' then
2496
2497 if l_elig_resale_start <= l_elig_resale_end then
2498
2499 open BOND_COUPON_RESALE;
2500 fetch BOND_COUPON_RESALE into resale_det;
2501 while BOND_COUPON_RESALE%FOUND loop
2502
2503 -- Bug 2422480.
2504 -- Added logic to group adjustments for resales occuring on the same day into a single
2505 -- accrual row. This will fix the issue with inability by subsequent batches to obtain
2506 -- the correct "accrued to date" balance when multiple resales occurs on the same day
2507
2508 l_group_period_accrual_amt := 0;
2509 l_group_end_date := resale_det.cross_ref_start_date;
2510 l_group_period_start := period_start;
2511
2512 -- Bug 2422480. Testing Issue 2.
2513 -- Initialize coupon accrued to-date total amount.
2514 -- This is the amount to be displayed and stored in column accrls_amount_bal.
2515
2516 l_to_date_amort_amt := 0;
2517
2518 While (BOND_COUPON_RESALE%FOUND and l_group_end_date = resale_det.cross_ref_start_date)
2519 Loop
2520
2521 -- l_cum_ex is the purchase deal's coupon status.
2522
2523 if l_cum_ex <> 'CUM' then
2524 -----------------------------------------------------------------------------------------
2525 -- Current coupon amount unaffected. But cumulative interest accounted for needs to be
2526 -- adjusted by the resale interest allocated to the sale amount from the resale deal.
2527 -----------------------------------------------------------------------------------------
2528 l_accrls_amount_bal := l_accrls_amount_bal - resale_det.back_end_interest;
2529 else
2530 -----------------------------------------------------------------------------------------
2531 -- Current coupon amount affected by resale. Need to calc the interest up to this point
2532 -- for the current coupon amount and adjust cumulative totals for subsequent processing.
2533 -----------------------------------------------------------------------------------------
2534
2535 ------------------------------------------------------------------------
2536 -- Determine ending date of the calc period and no of days between the
2537 -- adjusted coupon start date and the calculation period end date.
2538 ------------------------------------------------------------------------
2539 l_calc_period_end := resale_det.cross_ref_start_date;
2540
2541 -- Bug 2422480 begin additions.
2542 -- Do not adjust for extra day in coupon period when deal = 'First' and if calculation
2543 -- period end = coupon end. Otherwise, an extra day of interest will result for the
2544 -- remaining face value and interest income will be overstated.
2545
2546 If (l_calc_period_end = l_coupon_end) then
2547 l_adj_days := 0;
2548 Else
2549 l_adj_days := onc_det.forward_adjust;
2550 End If;
2551
2552 -- End 2422480 additions.
2553
2554 if l_calc_type = 'COMPOUND COUPON' then
2555
2559 if l_prev_coupon_date <= l_calc_period_end then -- 2737823 prevent ERROR in accrual
2556 -------------------------------------------------------------------------
2557 -- l_no_of_days = 'Accrual Date to Prev Coupon Date' for COMPOUND COUPON
2558 -------------------------------------------------------------------------
2560 XTR_CALC_P.CALC_DAYS_RUN_C(l_prev_coupon_date-l_adj_days, -- forward_adjust
2561 l_calc_period_end, -- 2737823 p_end_date
2562 l_year_calc_type,
2563 l_frequency,
2564 l_no_of_days,
2565 l_yr_basis,
2566 onc_det.forward_adjust,
2567 onc_det.day_count_type, -- AW Japan Project
2568 onc_det.first_trans_flag); -- AW Japan Project
2569 else
2570 l_no_of_days := 0;
2571 end if;
2572
2573 ---------------------------------------------------------------------------
2574 -- Calc Total Accrued to date interest based on the pre-sale coupon amount.
2575 ---------------------------------------------------------------------------
2576 if nvl(l_no_of_days,0) <> 0 and nvl(l_length_of_deal,0) <> 0 then
2577 l_num_current_coupon := l_no_of_days/l_length_of_deal;
2578 else
2579 ---------------------------------------------------------------------------
2580 -- If Accrual End Date is on Coupon Date, then l_no_of_days = 0
2581 ---------------------------------------------------------------------------
2582 l_num_current_coupon := 0;
2583 end if;
2584
2585 l_bond_rec.p_bond_commence := l_bond_commence;
2586 l_bond_rec.p_odd_coupon_start := l_odd_coupon_start;
2587 l_bond_rec.p_odd_coupon_maturity := l_odd_coupon_maturity;
2588 l_bond_rec.p_calc_date := l_calc_period_end; -- p_end_date ?????
2589 l_bond_rec.p_yr_calc_type := l_year_calc_type;
2590 l_bond_rec.p_frequency := l_frequency;
2591 l_bond_rec.p_curr_coupon := l_num_current_coupon;
2592 l_bond_rec.p_prv_full_coupon := l_num_full_cpn_previous;
2593 l_bond_rec.p_day_count_type := onc_det.day_count_type;
2594 l_prv_quasi_coupon := 0;
2595
2596 l_prv_quasi_coupon := XTR_MM_COVERS.CALC_TOTAL_PREVIOUS_COUPON(l_bond_rec);
2597
2598 l_calc_period_accrl_int :=(POWER(1+((l_coupon_rate/100)/l_frequency),l_prv_quasi_coupon)-1)*l_face_value_bal;
2599
2600 else
2601
2602
2603 --2422480 XTR_CALC_P.CALC_DAYS_RUN_C(l_adj_coupon_start - onc_det.forward_adjust,
2604 XTR_CALC_P.CALC_DAYS_RUN_C(onc_det.date_from - l_adj_days,
2605 l_calc_period_end,
2606 l_year_calc_type,
2607 l_frequency,
2608 l_no_of_days,
2609 l_yr_basis,
2610 onc_det.forward_adjust,
2611 onc_det.day_count_type, -- AW Japan Project
2612 onc_det.first_trans_flag); -- AW Japan Project
2613
2614 ---------------------------------------------------------------------------
2615 -- Calc Total Accrued to date interest based on the pre-sale coupon amount.
2616 ---------------------------------------------------------------------------
2617 l_calc_period_accrl_int := (l_no_of_days/l_length_of_deal)*l_adj_coupon_amt;
2618
2619 end if;
2620
2621 ---------------------------------------------------------------------------------------
2622 -- Determine the calculation period's accrued interest for the pre-sale coupon amount.
2623 ---------------------------------------------------------------------------------------
2624 l_period_accrual_amount := l_calc_period_accrl_int - l_accrls_amount_bal;
2625
2626 --2422480 ---------------------------------------------------------------------------------------
2627 --2422480 -- Find the greater of l_calc_period_accrl_int and l_accrls_amount_bal
2628 --2422480 ---------------------------------------------------------------------------------------
2629 --2422480 l_accrls_amount_bal := greatest(l_calc_period_accrl_int , l_accrls_amount_bal);
2630
2631 -- Bug 2422480 additions begin.
2632
2633 -- Need to adjust for any differences between the resale interest and the accrued
2634 -- interest calculated by the system. Differences may occur due to different
2635 -- day count and accrual basis defined for the bond issue. However, do not adjust
2639 -- Adjust the accrual to-date amount to be based on the face value after the resale
2636 -- if the resale occurs on the coupon's end date, because such a CUM resale will not
2637 -- affect the coupon amount of a coupon maturing on its start date.
2638
2640 -- by allocating the to-date accrued amount based on the pre-sale face vale.
2641 -- The coupon's next periodic accrual amount will need to rely on this amount
2642 -- in its calculations. This adjustment is also necessary only if resale date <> coupon maturity.
2643 -- Otherwise, the to-date amount calculated by the system should be used.
2644
2645 If (resale_det.cross_ref_start_date = l_coupon_end) then
2646 l_accrls_amount_bal := l_calc_period_accrl_int;
2647 Else
2648
2649 -- Calculate the accrued to date amount as calculated by the accrual process
2650 -- for the resold portion of the face value.
2651
2652 l_to_date_resale_accrl_int := (resale_det.face_value/l_face_value_bal) * l_calc_period_accrl_int;
2653 l_to_date_resale_accrl_int := xtr_fps2_p.interest_round(l_to_date_resale_accrl_int, l_rounding, onc_det.rounding_type);
2654
2655 -- Determine necessary adjustment amount between the amount accrued to date
2656 -- as calculated by the accrual process vs. the actual resale interest
2657 -- received by the buyer for the face value resold.
2658
2659 l_adj_amount := resale_det.back_end_interest - l_to_date_resale_accrl_int;
2660
2661 -- Apply the adjustment amount to the current period's periodic accrual
2662 -- interest amount. This would result in having the correct balance to be
2663 -- reflected in the Interest Income GL account on the day of the resale.
2664
2665 l_period_accrual_amount := l_period_accrual_amount + l_adj_amount;
2666
2667 -- If deal has not been completely resold, adjust the accrual to-date amount to be based
2668 -- on the face value after the resale by allocating the to-date accrued amount based on
2669 -- the pre-sale face vale. The coupon's next periodic accrual amount will need to rely
2670 -- on this amount in its calculations.
2671
2672 If ((l_face_value_bal - resale_det.face_value) <> 0) then
2673 l_accrls_amount_bal := ((l_face_value_bal - resale_det.face_value) / l_face_value_bal) * l_calc_period_accrl_int;
2674 Else
2675 l_accrls_amount_bal := l_calc_period_accrl_int + l_adj_amount;
2676 End If;
2677 End If; -- resale start = coupon end.
2678
2679 -- Keep a running total of the periodic accrual amount for the l_calc_period_end_date.
2680 -- This is to prevent creation of multiple accrual rows for the same period end date
2681 -- when multiple resales occurs on the same day.
2682
2683 l_group_period_accrual_amt := l_group_period_accrual_amt + l_period_accrual_amount;
2684
2685 -- End Bug 2422480 additions.
2686
2687 -- Bug 2422480.
2688 -- Added condition to perform adjustments only if the resale start date <> coupon maturity
2689 -- and resale = CUM. Because if the resale occurred on the coupon maturity and the resale = CUM,
2690 -- the face value resold will only affect the value of the next coupon. We do not want to
2691 -- understate the accrued to date amount.
2692
2693 If (resale_det.coupon_action = 'CUM' and
2694 resale_det.cross_ref_start_date <> l_coupon_end) then
2695
2696 ---------------------------------------------------------------------------------------
2697 -- Adjust 'Calculation' face value of the deal for purpose of recalc the coupon amount.
2698 ---------------------------------------------------------------------------------------
2699 l_face_value_bal := l_face_value_bal - resale_det.face_value;
2700
2701 ----------------------------------------------------------------------------
2702 -- Recalc the coupon amount based on the adjusted 'calculation' face value.
2703 ----------------------------------------------------------------------------
2704 if l_calc_type in ('FLAT COUPON','FL REGULAR') then --b 2804548
2705 l_adj_coupon_amt := (l_face_value_bal * (l_coupon_rate/100))/l_frequency;
2706
2707 elsif l_calc_type = 'COMPOUND COUPON' then
2708
2709 l_comp_coupon.p_bond_start_date := l_bond_commence;
2710 l_comp_coupon.p_odd_coupon_start := l_odd_coupon_start;
2711 l_comp_coupon.p_odd_coupon_maturity := l_odd_coupon_maturity;
2712 l_comp_coupon.p_full_coupon := l_no_quasi_coupon;
2713 l_comp_coupon.p_coupon_rate := l_coupon_rate;
2714 l_comp_coupon.p_maturity_amount := l_face_value_bal; -- Remaining Face Value
2715 l_comp_coupon.p_precision := l_precision;
2716 l_comp_coupon.p_rounding_type := onc_det.rounding_type;
2720 l_comp_coupon.p_amount_redemption_ind := 'A';
2717 l_comp_coupon.p_year_calc_type := l_year_calc_type;
2718 l_comp_coupon.p_frequency := l_frequency;
2719 l_comp_coupon.p_day_count_type := onc_det.day_count_type;
2721
2722 l_adj_coupon_amt := XTR_MM_COVERS.CALC_COMPOUND_COUPON_AMT(l_comp_coupon);
2723
2724 else
2725 l_adj_coupon_amt := l_face_value_bal * (l_coupon_rate/100)*(l_length_of_deal/l_yr_basis);
2726 end if;
2727
2728 -- Bug 2422480 addition.
2729
2730 l_adj_coupon_amt := xtr_fps2_p.interest_round(l_adj_coupon_amt, l_rounding, onc_det.rounding_type);
2731
2732 -- End bug 2422480 addition.
2733 End If;
2734
2735 ------------------------------------
2736 -- Line up the next period start
2737 ------------------------------------
2738 period_start := l_calc_period_end;
2739 End If; -- l_cum_ex <> 'CUM'.
2740
2741 Fetch BOND_COUPON_RESALE into resale_det;
2742 End Loop; -- "group" processing of resales occuring on same date.
2743
2744 -----------------------------------------------------------------------------
2745 -- Insert one summarized period accrued interest record for each cutoff date.
2746 -----------------------------------------------------------------------------
2747 -- AW 2113171 Do not display if both accrual amount and balance are zero.
2748
2749 -- Bug 2422480.
2750 -- Changed the period_accrual_amt and period_start to use the "group" variables
2751 -- in both the conditions and insert statements.
2752
2753 if xtr_fps2_p.interest_round(l_group_period_accrual_amt,l_rounding,onc_det.rounding_type)<>0 and
2754 xtr_fps2_p.interest_round(l_accrls_amount_bal,l_rounding,onc_det.rounding_type)<>0 then
2755 if l_group_period_start <= l_calc_period_end then
2756
2757 -- Bug 2422480. Fixes Testing Issue 2.
2758 -- Obtain accrued to-date amount for the entire coupon for purposes of displaying
2759 -- the sum of it and the current periodic accrual amount as the "Balance" of the
2760 -- accrued coupon interest.
2761
2762 -- This to-date amount is calculated thusly because it would be impossible to
2763 -- properly update any pre-patchset J accrual records to reflect the amount for
2764 -- the entire coupon. Instead, a simple script will be included for bug 2422480
2765 -- such that the amounts in the accrls_amount_bal column will be copied to the
2766 -- effint_accrls_amount_bal for Bond Coupon records and all future internal
2767 -- calculations will utilize the effint_accrls_amount_bal.
2768
2769 OPEN GET_TOTAL_BOND_CPN_ACCRUAL;
2770 FETCH GET_TOTAL_BOND_CPN_ACCRUAL into l_to_date_amort_amt;
2771 CLOSE GET_TOTAL_BOND_CPN_ACCRUAL;
2772
2773 l_group_period_accrual_amt := xtr_fps2_p.interest_round(l_group_period_accrual_amt,l_rounding,onc_det.rounding_type);
2774
2775 l_to_date_amort_amt := nvl(l_to_date_amort_amt,0) + l_group_period_accrual_amt + nvl(l_dda_INT,0);
2776
2777 insert into XTR_ACCRLS_AMORT (BATCH_ID, DEAL_NO, TRANS_NO,
2778 COMPANY_CODE, DEAL_SUBTYPE, DEAL_TYPE, CURRENCY,
2779 PERIOD_FROM, PERIOD_TO,
2780 CPARTY_CODE, PRODUCT_TYPE, PORTFOLIO_CODE,
2781 INTEREST_RATE, TRANSACTION_AMOUNT,
2782 AMOUNT_TYPE, ACTION_CODE,
2783 ACCRLS_AMOUNT,
2784 CALC_FACE_VALUE,
2785 YEAR_BASIS, FIRST_ACCRUAL_INDIC,
2786 ACTUAL_START_DATE, ACTUAL_MATURITY_DATE,
2787 NO_OF_DAYS, ACCRLS_AMOUNT_BAL,
2788 EFFINT_ACCRLS_AMOUNT_BAL)
2789 values(l_batch_id, onc_det.deal_nos, onc_det.trans_nos,
2790 p_company, onc_det.subtype, onc_det.deal_type, onc_det.ccy,
2791 l_group_period_start,
2792 l_calc_period_end,
2793 onc_det.cparty, onc_det.product, onc_det.portfolio,
2794 onc_det.rate, l_adj_coupon_amt,
2795 'CPMADJ', decode(sign(l_group_period_accrual_amt),-1,'REV','POS'),
2796 abs(l_group_period_accrual_amt),
2797 l_face_value_bal,
2798 l_yr_basis, l_first_accrual_indic,
2802 l_to_date_amort_amt,
2799 l_group_period_start,
2800 l_calc_period_end,
2801 l_no_of_days,
2803 xtr_fps2_p.interest_round(l_accrls_amount_bal,
2804 l_rounding,onc_det.rounding_type));
2805 end if; -- period_start <= l_calc_period_end.
2806 end if; -- both period accrual and accrual to-date <> 0.
2807
2808 End loop; -- All eligible resales.
2809
2810 close BOND_COUPON_RESALE;
2811
2812 end if; -- eligible resale start <= eligible resale end.
2813
2814 --------------------------------
2815 -- v) NI Deal
2816 -- Also FRA, BDO, IRO, SWPTN -- AW 1395208
2817 --------------------------------
2818 else
2819 if l_length_of_deal <> 0 then
2820 ----------------------------------------------------------------
2821 -- AW Japan Project - NI always use Overriden Amount for accrual
2822 ----------------------------------------------------------------
2823 l_amount_to_accrue_amort := xtr_fps2_p.interest_round(abs((onc_det.main_amount/l_length_of_deal)*l_no_of_days),l_rounding,onc_det.rounding_type);
2824 else
2825 l_amount_to_accrue_amort :=NULL;
2826 end if;
2827
2828 end if;
2829
2830
2831 /*-----------------------------------------*/
2832 /* Period Accrual Amount (l_action_code) */
2833 /*-----------------------------------------*/
2834 if onc_det.deal_type = 'BOND' then
2835 l_action_code := 'POS';
2836
2837 else -- Exclude BOND - CPMADJ
2838 l_period_accrual_amount := nvl(l_amount_to_accrue_amort,0)-nvl(l_accrls_amount_bal,0);
2839
2840 if l_period_accrual_amount < 0 then
2841 l_action_code := 'REV';
2842 -------------------------------------------------------
2843 -- 2781438 (3450474) Reset the new accrual balance
2844 -------------------------------------------------------
2845 if onc_det.deal_type in ('ONC','TMM','RTMM','IRS') then
2846 l_accrls_amount_bal := l_amount_to_accrue_amort;
2847 if l_rev_exists = 'N' then
2848 FND_MESSAGE.Set_Name ('XTR','XTR_ACCRUAL_REVERSAL');
2849 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
2850 l_rev_exists := 'Y';
2851 end if;
2852 l_rev_message := ' '||to_char(onc_det.deal_nos)||'/'||to_char(onc_det.trans_nos);
2853 FND_FILE.Put_Line (FND_FILE.LOG, l_rev_message);
2854 end if;
2855 -------------------------------------------------------
2856
2857 else
2858 l_action_code := 'POS';
2859 end if;
2860 end if;
2861
2862
2863 /*--------------------*/
2864 /* 6. Create Accruals */
2865 /*--------------------*/
2866
2867 -------------------------
2868 -- Bond Discount/Premium
2869 -------------------------
2870 if onc_det.deal_type = 'BOND' and l_amount_type in ('SLPREM','SLDISC') then
2871
2872 ---------------------------------
2873 -- Check if REV has been created
2874 ---------------------------------
2875 l_deal_closed := 'N';
2876 open chk_closed_deal;
2877 fetch chk_closed_deal into l_deal_closed;
2878 close chk_closed_deal;
2879
2880 if l_deal_closed = 'N' then
2881 -----------------------------------------------
2882 -- Period Amort for the remaining Face Value.
2883 -----------------------------------------------
2884
2885 -- AW 2113171 Do not display if both accrual amount and balance are zero.
2886 if l_period_accrual_amount <> 0 and l_amount_to_accrue_amort <> 0 then
2887
2888 insert into XTR_ACCRLS_AMORT (BATCH_ID, DEAL_NO,TRANS_NO,COMPANY_CODE,DEAL_SUBTYPE,
2889 DEAL_TYPE,CURRENCY,PERIOD_FROM,PERIOD_TO,
2890 CPARTY_CODE,PRODUCT_TYPE,PORTFOLIO_CODE,
2891 INTEREST_RATE,TRANSACTION_AMOUNT,AMOUNT_TYPE,
2892 ACTION_CODE,ACCRLS_AMOUNT,CALC_FACE_VALUE,YEAR_BASIS,
2893 FIRST_ACCRUAL_INDIC,ACTUAL_START_DATE,ACTUAL_MATURITY_DATE,
2894 NO_OF_DAYS,ACCRLS_AMOUNT_BAL)
2895 values(l_batch_id, onc_det.deal_nos,onc_det.trans_nos,p_company,
2896 onc_det.subtype,
2897 onc_det.deal_type,onc_det.ccy,
2898 decode(l_first_accrual_indic,'Y',period_start,
2899 greatest(period_start,p_start_date)),
2900 least(onc_det.date_to,p_end_date),
2901 onc_det.cparty,onc_det.product,
2902 onc_det.portfolio,onc_det.rate,
2903 onc_det.main_amount,
2907 deal_yr_basis,l_first_accrual_indic,
2904 decode(sign(100-l_clean_price),-1,'SLPREM','SLDISC'),
2905 'POS',decode(l_maturity_face_value,0,0,abs(l_period_accrual_amount)),
2906 l_maturity_face_value,
2908 decode(l_first_accrual_indic,'Y',period_start,
2909 greatest(period_start,p_start_date)),
2910 least(onc_det.date_to,p_end_date),
2911 l_no_of_days,l_amount_to_accrue_amort);
2912 end if;
2913
2914 ---------------------------------------------------------------------------
2915 -- Deal Matured. Reverse Cumulative Amort for the Remaining Face Value.
2916 ---------------------------------------------------------------------------
2917 if onc_det.date_to <= p_end_date and l_maturity_face_value <> 0 and
2918 nvl(l_amount_to_accrue_amort,0) <> 0 then -- 2753088,2751078 zero PREM/DISC REV for matured deal.
2919
2920 insert into XTR_ACCRLS_AMORT (BATCH_ID, DEAL_NO,TRANS_NO,COMPANY_CODE,DEAL_SUBTYPE,
2921 DEAL_TYPE,CURRENCY,PERIOD_FROM,PERIOD_TO,
2922 CPARTY_CODE,PRODUCT_TYPE,PORTFOLIO_CODE,
2923 INTEREST_RATE,TRANSACTION_AMOUNT,AMOUNT_TYPE,
2924 ACTION_CODE,ACCRLS_AMOUNT,CALC_FACE_VALUE,YEAR_BASIS,
2925 FIRST_ACCRUAL_INDIC,ACTUAL_START_DATE,ACTUAL_MATURITY_DATE,
2926 NO_OF_DAYS,ACCRLS_AMOUNT_BAL)
2927 values(l_batch_id, onc_det.deal_nos,onc_det.trans_nos,p_company,
2928 onc_det.subtype,
2929 onc_det.deal_type,onc_det.ccy,
2930 period_start, onc_det.date_to,
2931 onc_det.cparty,onc_det.product,
2932 onc_det.portfolio,onc_det.rate,
2933 onc_det.main_amount,
2934 decode(sign(100-l_clean_price),-1,'SLPREM','SLDISC'),
2935 'REV', l_amount_to_accrue_amort,
2936 l_maturity_face_value,
2937 deal_yr_basis,l_first_accrual_indic,
2938 period_start, onc_det.date_to,
2939 l_no_of_days,l_amount_to_accrue_amort);
2940
2941
2942 end if;
2943 end if; -- l_deal_closed = 'N'
2944
2945 --------------------------
2946 -- Bond Coupons
2947 --------------------------
2948 elsif onc_det.deal_type = 'BOND' and l_amount_type = 'CPMADJ' then
2949
2950 ---------------------------------------------------------------------------------------------------------
2951 -- Calc the accrued interest for the remainder of the batch period, based on the adjusted coupon amount.
2952 ---------------------------------------------------------------------------------------------------------
2953
2954 -- Bug 2422480 addition.
2955 -- Process remainder of the batch period only if entire face value has not been resold.
2956
2957 If (nvl(l_face_value_bal,0) <> 0) then
2958
2959 if period_end = l_coupon_end then
2960 l_adj_coupon_amt := onc_det.main_amount; -- rollover transaction interest
2961 l_calc_period_accrl_int := onc_det.main_amount; -- rollover transaction interest
2962 else
2963 if l_calc_type = 'COMPOUND COUPON' then
2964 -------------------------------------------------------------------------
2965 -- l_no_of_days = 'Accrual Date to Prev Coupon Date' for COMPOUND COUPON
2966 -------------------------------------------------------------------------
2967 XTR_CALC_P.CALC_DAYS_RUN_C(l_prev_coupon_date - onc_det.forward_adjust,
2968 period_end,
2969 l_year_calc_type,
2970 l_frequency,
2971 l_no_of_days,
2972 l_yr_basis,
2973 onc_det.forward_adjust,
2974 onc_det.day_count_type, -- AW Japan Project
2975 onc_det.first_trans_flag); -- AW Japan Project
2976
2977 ---------------------------------------------------------------------------
2978 -- Calc Total Accrued to date interest based on the remaining coupon amount.
2979 ---------------------------------------------------------------------------
2980 if nvl(l_no_of_days,0) <> 0 and nvl(l_length_of_deal,0) <> 0 then
2981 l_num_current_coupon := l_no_of_days/l_length_of_deal;
2982 else
2986 l_num_current_coupon := 0;
2983 ---------------------------------------------------------------------------
2984 -- If Accrual End Date is on Coupon Date, then l_no_of_days = 0
2985 ---------------------------------------------------------------------------
2987 end if;
2988
2989 l_bond_rec.p_bond_commence := l_bond_commence;
2990 l_bond_rec.p_odd_coupon_start := l_odd_coupon_start;
2991 l_bond_rec.p_odd_coupon_maturity := l_odd_coupon_maturity;
2992 l_bond_rec.p_calc_date := period_end;
2993 l_bond_rec.p_yr_calc_type := l_year_calc_type;
2994 l_bond_rec.p_frequency := l_frequency;
2995 l_bond_rec.p_curr_coupon := l_num_current_coupon;
2996 l_bond_rec.p_prv_full_coupon := l_num_full_cpn_previous;
2997 l_bond_rec.p_day_count_type := onc_det.day_count_type;
2998 l_prv_quasi_coupon := 0;
2999
3000 l_prv_quasi_coupon := XTR_MM_COVERS.CALC_TOTAL_PREVIOUS_COUPON(l_bond_rec);
3001
3002 l_calc_period_accrl_int :=(POWER(1+((l_coupon_rate/100)/l_frequency),l_prv_quasi_coupon)-1)*l_face_value_bal;
3003
3004 else
3005
3006 -- Replaced l_adj_coupon_start with onc_det.date_from for 2422480.
3007
3008 XTR_CALC_P.CALC_DAYS_RUN_C(onc_det.date_from - onc_det.forward_adjust,
3009 period_end,
3010 l_year_calc_type,
3011 l_frequency,
3012 l_no_of_days,
3013 deal_yr_basis,
3014 onc_det.forward_adjust,
3015 onc_det.day_count_type, -- AW Japan Project
3016 onc_det.first_trans_flag); -- AW Japan Project
3017
3018 l_calc_period_accrl_int := (l_no_of_days/l_length_of_deal) * l_adj_coupon_amt;
3019
3020 end if;
3021
3022 -- Bug 2422480 additions.
3023
3024 l_calc_period_accrl_int := xtr_fps2_p.interest_round(l_calc_period_accrl_int, l_rounding, onc_det.rounding_type);
3025
3026 -- End 2422480 additions.
3027
3028 end if;
3029
3030 l_period_accrual_amount := l_calc_period_accrl_int - l_accrls_amount_bal;
3031 l_period_accrual_amount := xtr_fps2_p.interest_round(l_period_accrual_amount,l_rounding,
3032 onc_det.rounding_type);
3033 ------------------------------------------------------------------------------------
3034 -- AW Japan Project - in case the overriden amount is less than system amount, need
3035 -- to adjust the balance to use the overriden amount on maturity
3036 ------------------------------------------------------------------------------------
3037 if period_end = l_coupon_end then
3038 l_accrls_amount_bal := onc_det.main_amount;
3039 else
3040 l_accrls_amount_bal := l_calc_period_accrl_int;
3041 end if;
3042
3043 -----------------------------------------------------------------------------------------
3044 -- Bug 2751078 - To balance total CPMADJ accruals amount on maturity.
3045 -----------------------------------------------------------------------------------------
3046 if l_bond_maturity = l_coupon_end and l_coupon_end <= period_end then
3047 l_sum_prev_accrls := 0;
3048 l_sum_backend_int := 0;
3049
3050 -----------------------------------------------------------------------------
3051 -- (1) = Sum all previous CPMADJ accruals
3052 -----------------------------------------------------------------------------
3053 select nvl(sum(decode(ACTION_CODE,'REV',-1*ACCRLS_AMOUNT,ACCRLS_AMOUNT)),0)
3054 into l_sum_prev_accrls
3055 from xtr_accrls_amort
3056 where deal_no = onc_det.deal_nos
3057 and trans_no = onc_det.trans_nos
3058 and amount_type = 'CPMADJ'
3059 and action_code in ('POS','REV');
3060
3061 -----------------------------------------------------------------------------
3062 -- (2) = (1) + BUY's INT if first coupon
3063 -----------------------------------------------------------------------------
3064 if onc_det.trans_nos = 2 and l_cum_ex = 'CUM' then
3065 l_sum_prev_accrls := l_sum_prev_accrls + l_dda_INT;
3066 end if;
3067
3068 -----------------------------------------------------------------------------
3069 -- (3) = Sum all back end interest
3070 -----------------------------------------------------------------------------
3071 select nvl(sum(back_end_interest),0)
3072 into l_sum_backend_int
3073 from XTR_BOND_ALLOC_DETAILS
3077
3074 where deal_no = onc_det.deal_nos
3075 and cross_ref_start_date <= period_end
3076 and cross_ref_start_date >= period_start; -- Bug 4613248 Added the condition
3078 -----------------------------------------------------------------------------
3079 -- (4) = (2) + current period accruals - (3)
3080 -----------------------------------------------------------------------------
3081 l_dummy := (l_sum_prev_accrls + l_period_accrual_amount) - l_sum_backend_int;
3082
3083 -------------------------------------------------------------------------------
3084 -- Adjustment to current period accruals:
3085 -- l_period_accrual_amount = difference between Remaining Coupon Amount and (4)
3086 -------------------------------------------------------------------------------
3087 if onc_det.main_amount <> l_dummy then
3088 l_period_accrual_amount := l_period_accrual_amount + (onc_det.main_amount - l_dummy);
3089 end if;
3090 -----------------------------------------------------------------------------------------
3091
3092 end if;
3093
3094 -----------------------------------
3095 -- Accrued Interest for the coupon.
3096 -----------------------------------
3097 -- AW 2113171 Do not display if both accrual amount and balance are zero.
3098 if xtr_fps2_p.interest_round(l_period_accrual_amount,l_rounding,onc_det.rounding_type) <> 0 and
3099 xtr_fps2_p.interest_round(l_accrls_amount_bal,l_rounding,onc_det.rounding_type) <> 0 then
3100
3101 -- Bug 2422480. Fixes Testing Issue 2.
3102 -- Calculate the total accrual to-date amount for the coupon to be displayed as 'Balance'.
3103
3104 OPEN GET_TOTAL_BOND_CPN_ACCRUAL;
3105 FETCH GET_TOTAL_BOND_CPN_ACCRUAL into l_to_date_amort_amt;
3106 CLOSE GET_TOTAL_BOND_CPN_ACCRUAL;
3107
3108 l_to_date_amort_amt := nvl(l_to_date_amort_amt,0) + l_period_accrual_amount + nvl(l_dda_INT,0);
3109
3110 insert into XTR_ACCRLS_AMORT (BATCH_ID, DEAL_NO, TRANS_NO,
3111 COMPANY_CODE, DEAL_SUBTYPE, DEAL_TYPE, CURRENCY,
3112 PERIOD_FROM, PERIOD_TO,
3113 CPARTY_CODE, PRODUCT_TYPE, PORTFOLIO_CODE,
3114 INTEREST_RATE, TRANSACTION_AMOUNT,
3115 AMOUNT_TYPE, ACTION_CODE,
3116 ACCRLS_AMOUNT,
3117 CALC_FACE_VALUE,
3118 YEAR_BASIS, FIRST_ACCRUAL_INDIC,
3119 ACTUAL_START_DATE, ACTUAL_MATURITY_DATE,
3120 NO_OF_DAYS, ACCRLS_AMOUNT_BAL,
3121 EFFINT_ACCRLS_AMOUNT_BAL)
3122 values(l_batch_id, onc_det.deal_nos, onc_det.trans_nos,
3123 p_company, onc_det.subtype, onc_det.deal_type, onc_det.ccy,
3124 period_start,
3125 period_end,
3126 onc_det.cparty, onc_det.product, onc_det.portfolio,
3127 onc_det.rate, onc_det.main_amount, -- l_adj_coupon_amt, AW Japan
3128 'CPMADJ',
3129 decode(sign(l_period_accrual_amount),-1,'REV','POS'), -- AW Japan Project
3130 abs(xtr_fps2_p.interest_round(abs(l_period_accrual_amount), -- AW Japan Project
3131 l_rounding,onc_det.rounding_type)),
3132 l_face_value_bal,
3133 l_yr_basis, l_first_accrual_indic,
3134 period_start,
3135 period_end,
3136 l_no_of_days,
3137 l_to_date_amort_amt,
3138 xtr_fps2_p.interest_round(l_accrls_amount_bal,
3139 l_rounding,onc_det.rounding_type));
3140 end if;
3141 End If; -- only if remaining face <> 0.
3142 else
3143
3144 if NOT (nvl(l_period_accrual_amount,0) = 0 and nvl(l_amount_to_accrue_amort,0) =0 ) then
3145
3146 ------------
3147 -- NI
3148 ------------
3149 if onc_det.deal_type = 'NI' and
3150 ((onc_det.status_code = 'CLOSED' and onc_det.deal_action_date <= p_end_date) or
3151 (onc_det.date_to <= p_end_date)) then -- Bug 1717213 AW
3152
3153 ---------------------------------
3154 -- Check if REV has been created
3155 ---------------------------------
3156 l_deal_closed :='N';
3157 open chk_closed_deal;
3161 if nvl(l_deal_closed,'N') ='N' then
3158 fetch chk_closed_deal into l_deal_closed;
3159 close chk_closed_deal;
3160
3162 if nvl(l_period_accrual_amount,0) <> 0 then
3163 insert into XTR_ACCRLS_AMORT (BATCH_ID, DEAL_NO,TRANS_NO,COMPANY_CODE,DEAL_SUBTYPE,
3164 DEAL_TYPE,CURRENCY,PERIOD_FROM,PERIOD_TO,
3165 CPARTY_CODE,PRODUCT_TYPE,PORTFOLIO_CODE,
3166 INTEREST_RATE,TRANSACTION_AMOUNT,AMOUNT_TYPE,
3167 ACTION_CODE,ACCRLS_AMOUNT,YEAR_BASIS,
3168 FIRST_ACCRUAL_INDIC,ACTUAL_START_DATE,ACTUAL_MATURITY_DATE,
3169 NO_OF_DAYS,ACCRLS_AMOUNT_BAL)
3170 values(l_batch_id, onc_det.deal_nos,onc_det.trans_nos,p_company,
3171 onc_det.subtype,
3172 onc_det.deal_type,onc_det.ccy,p_start_date,l_actual_maturity,
3173 --onc_det.deal_type,onc_det.ccy,p_start_date,p_end_date,
3174 onc_det.cparty,onc_det.product,
3175 onc_det.portfolio,onc_det.rate,
3176 onc_det.main_amount,onc_det.main_amt_type,
3177 l_action_code, abs(l_period_accrual_amount),
3178 deal_yr_basis,l_first_accrual_indic,
3179 l_actual_start_date,l_actual_maturity,
3180 l_no_of_days,l_amount_to_accrue_amort);
3181 end if;
3182 if nvl(l_amount_to_accrue_amort,0) <> 0 then
3183 insert into XTR_ACCRLS_AMORT (BATCH_ID,DEAL_NO,TRANS_NO,COMPANY_CODE,DEAL_SUBTYPE,
3184 DEAL_TYPE,CURRENCY,PERIOD_FROM,PERIOD_TO,
3185 CPARTY_CODE,PRODUCT_TYPE,PORTFOLIO_CODE,
3186 INTEREST_RATE,TRANSACTION_AMOUNT,AMOUNT_TYPE,
3187 ACTION_CODE,ACCRLS_AMOUNT,YEAR_BASIS,
3188 FIRST_ACCRUAL_INDIC,ACTUAL_START_DATE,ACTUAL_MATURITY_DATE,
3189 NO_OF_DAYS,ACCRLS_AMOUNT_BAL)
3190 values(l_batch_id,onc_det.deal_nos,onc_det.trans_nos,
3191 p_company,onc_det.subtype,
3192 onc_det.deal_type,onc_det.ccy,p_start_date,l_actual_maturity,
3193 --onc_det.deal_type,onc_det.ccy,p_start_date,p_end_date,
3194 onc_det.cparty,onc_det.product,
3195 onc_det.portfolio,onc_det.rate,
3196 onc_det.main_amount,onc_det.main_amt_type,
3197 decode(sign(l_amount_to_accrue_amort),-1,'POS','REV'),
3198 abs(l_amount_to_accrue_amort),
3199 deal_yr_basis,l_first_accrual_indic,l_actual_start_date,
3200 l_actual_maturity,
3201 l_no_of_days,0);
3202 end if;
3203 end if;
3204
3205 -----------
3206 -- Others
3207 -----------
3208 else
3209
3210 if nvl(l_period_accrual_amount,0) <> 0 then
3211 insert into XTR_ACCRLS_AMORT (BATCH_ID,DEAL_NO,TRANS_NO,COMPANY_CODE,
3212 DEAL_SUBTYPE, DEAL_TYPE,CURRENCY,PERIOD_FROM,PERIOD_TO,
3213 CPARTY_CODE,PRODUCT_TYPE,PORTFOLIO_CODE,
3214 INTEREST_RATE,TRANSACTION_AMOUNT,AMOUNT_TYPE,
3215 ACTION_CODE,ACCRLS_AMOUNT,YEAR_BASIS,
3216 FIRST_ACCRUAL_INDIC,ACTUAL_START_DATE,ACTUAL_MATURITY_DATE,
3217 NO_OF_DAYS,ACCRLS_AMOUNT_BAL)
3218 values(l_batch_id,onc_det.deal_nos,onc_det.trans_nos,
3219 p_company,onc_det.subtype,
3220 onc_det.deal_type,onc_det.ccy,p_start_date,
3221 nvl(l_actual_maturity,p_end_date), --Bug 2416970
3222 --decode(onc_det.deal_type,'NI',l_actual_maturity,p_end_date), Bug 2416970
3223 --onc_det.deal_type,onc_det.ccy,p_start_date,p_end_date,
3224 onc_det.cparty,onc_det.product,
3225 onc_det.portfolio,onc_det.rate,
3226 onc_det.main_amount,onc_det.main_amt_type,
3227 l_action_code,abs(l_period_accrual_amount),
3228 deal_yr_basis,l_first_accrual_indic,
3232 end if;
3229 l_actual_start_date,l_actual_maturity,
3230 l_no_of_days,l_amount_to_accrue_amort);
3231 end if;
3233 end if;
3234 end if;
3235 --
3236
3237 <<NEXT_ACCRUAL_DEALS>>
3238
3239 fetch ACCRUAL_DEALS INTO onc_det;
3240
3241 END LOOP;
3242
3243 close ACCRUAL_DEALS;
3244
3245 /* -------------------------------------------------------*/
3246 /* Accruals for Intergroup Transfers and Bank Balances */
3247 /* -------------------------------------------------------*/
3248 XTR_ACCRUAL_PROCESS_P.CALC_INTGROUP_CACCT_ACCLS(p_company,l_batch_id,p_start_date,p_end_date,'%');
3249
3250
3251 /*-----------------------------------*/
3252 /* Wait the correct effective mothod */
3253 /*-----------------------------------*/
3254 -- XTR_ACCRUAL_PROCESS_P.CALCULATE_BOND_AMORTISATION(p_company,p_start_date,p_end_date,'%');
3255
3256
3257 /* --------------------------------------------*/
3258 /* NI accruals using effective interest method */
3259 /* --------------------------------------------*/
3260 XTR_ACCRUAL_PROCESS_P.CALCULATE_NI_EFFINT(p_company,l_batch_id,p_batch_id,p_start_date,p_end_date);
3261
3262 end if;
3263
3264
3265 -------------------------------------------------------------------------------
3266 -- Insert row to XTR_BATCH_EVENTS table after generating Accrual for the Batch
3267 -------------------------------------------------------------------------------
3268 open EVENT_ID;
3269 fetch EVENT_ID into l_event_id;
3270 cLose EVENT_ID;
3271
3272 insert into XTR_BATCH_EVENTS(batch_event_id, batch_id,event_code, authorized,
3273 authorized_by, authorized_on, created_by, creation_date, last_updated_by,
3274 last_update_date, last_update_login)
3275 values (l_event_id, l_batch_id, 'ACCRUAL', decode(p_upgrade_batch,'I','Y','N'),
3276 null, null, fnd_global.user_id,
3277 l_sysdate, fnd_global.user_id, l_sysdate, fnd_global.login_id);
3278
3279
3280 COMMIT;
3281
3282
3283 EXCEPTION
3284 when ex_reval_auth then
3285 ROLLBACK TO SAVEPOINT sp_accrual;
3286 retcode := -1;
3287 FND_MESSAGE.Set_Name('XTR', 'XTR_REVAL_AUTH');
3288 FND_MESSAGE.Set_Token('BATCH', p_batch_id);
3289 APP_EXCEPTION.Raise_exception;
3290 when others then
3291 ROLLBACK TO SAVEPOINT sp_accrual;
3292 retcode := -1;
3293 RAISE;
3294
3295
3296 end CALCULATE_ACCRUAL_AMORTISATION;
3297 -----------------------------------------------------------------------------------------------------------------------
3298 PROCEDURE CALC_INTGROUP_CACCT_ACCLS (p_company IN VARCHAR2,
3299 p_batch_id IN NUMBER,
3300 p_start_date IN DATE,
3301 p_end_date IN DATE,
3302 p_deal_type IN VARCHAR2) is
3303 --
3304 l_days_adjust VARCHAR2(100);
3305 l_year NUMBER := 365;
3306 l_subtype VARCHAR2(7);
3307 l_amount_to_accrue_amort NUMBER;
3308 l_period_accrual_amount NUMBER;
3309 calc_days NUMBER;
3310 l_deal_nos NUMBER;
3311 l_trans_nos NUMBER;
3312 l_int_rate NUMBER;
3313 l_rounding NUMBER;
3314 l_ccy VARCHAR2(15);
3315 l_start_interest NUMBER;
3316 l_maturity_interest NUMBER;
3317 l_hce_rate NUMBER := 1;
3318 l_product_type VARCHAR2(10);
3319 l_year_calc_type VARCHAR2(15);
3320 l_accrls_amount_bal NUMBER;
3321 l_first_accrual_indic VARCHAR2(1);
3322 l_deal_type VARCHAR2(7);
3323 l_actual_end_date DATE;
3324 l_forward_adjust NUMBER;
3325 l_oldest_date DATE;
3326 l_oldest_tran NUMBER;
3327 l_first_tran VARCHAR2(1);
3328 l_prv_subtype XTR_ACCRLS_AMORT.DEAL_SUBTYPE%TYPE;
3329
3330 -----------------------------------------------------------------------------
3331 -- Get accrual methods Interest in arreas(Following), Forward interest(Prior).
3332 -----------------------------------------------------------------------------
3333 cursor ADJUST(p_param_name varchar2) is
3334 select PARAM_VALUE
3335 from XTR_PRO_PARAM
3336 where PARAM_NAME = p_param_name;
3337
3338 ---
3339 cursor CHK_FIRST_ACCRUAL is
3340 select 'N'
3341 from XTR_ACCRLS_AMORT
3342 where deal_no = l_deal_nos
3343 and deal_type = l_deal_type;
3344 --
3345
3346 cursor GET_PRV_BAL is
3347 select nvl(ACCRLS_AMOUNT_BAL,0) accrls_bal, deal_subtype -- 3866372 to get previous deal subtype
3348 from XTR_ACCRLS_AMORT
3349 where deal_no = l_deal_nos
3350 and deal_type = l_deal_type
3351 and amount_type = 'INTADJ'
3352 and action_code = 'POS'
3353 and period_to < p_end_date
3354 order by period_to desc;
3355
3356
3357 cursor RND_FAC is
3358 select s.ROUNDING_FACTOR,s.YEAR_BASIS,s.HCE_RATE,s.IG_YEAR_BASIS
3359 from XTR_MASTER_CURRENCIES_V s
3360 where s.CURRENCY = l_ccy;
3361
3362
3363 ------------------------------------
3364 -- Accruals for Intergroup Transfers
3368 i.PARTY_CODE cparty,
3365 ------------------------------------
3366 cursor IG is
3367 select i.COMPANY_CODE,
3369 i.PORTFOLIO portfolio_code,
3370 i.PRODUCT_TYPE,
3371 i.TRANSFER_DATE balance_date,
3372 i.CURRENCY,
3373 nvl(i.BALANCE_OUT,0) bal_out, -- AW 2113171 Use abs(i.BALANCE_OUT) to avoid negative IG balance ???
3374 nvl(i.ACCRUAL_INTEREST,0) main_amount,
3375 nvl(i.INTEREST_RATE,0) int_rate,
3376 i.TRANSACTION_NUMBER,
3377 i.DEAL_NUMBER,
3378 decode(i.DAY_COUNT_TYPE,'F','PRIOR','L','FOLLOWING','B') day_count_type, -- AW Japan Project
3379 nvl(i.ROUNDING_TYPE,'R') rounding_type, -- AW Japan Project
3380 decode(nvl(i.DAY_COUNT_TYPE,'L'),'F',1,0) forward_adjust -- AW Japan Project
3381 from XTR_INTERGROUP_TRANSFERS i
3382 where i.COMPANY_CODE = p_company
3383 and i.TRANSACTION_NUMBER =
3384 (select max(k.TRANSACTION_NUMBER)
3385 from XTR_INTERGROUP_TRANSFERS k
3386 where k.COMPANY_CODE = i.COMPANY_CODE
3387 and k.PARTY_CODE = i.PARTY_CODE
3388 and k.CURRENCY = i.CURRENCY
3389 and k.TRANSFER_DATE =
3390 (select max(j.TRANSFER_DATE)
3391 from XTR_INTERGROUP_TRANSFERS j
3392 where j.TRANSFER_DATE <= p_end_date
3393 and j.COMPANY_CODE = i.COMPANY_CODE
3394 and j.PARTY_CODE = i.PARTY_CODE
3395 and j.CURRENCY = i.CURRENCY))
3396 order by i.CURRENCY;
3397
3398 ig_det IG%ROWTYPE;
3399
3400
3401 ------------------------------------
3402 -- Accruals for Company Bank Charges
3403 ------------------------------------
3404 cursor BK_CHARGE is
3405 select b.COMPANY_CODE,
3406 b.ACCOUNT_NUMBER,
3407 b.BALANCE_DATE,
3408 a.CURRENCY,
3409 a.BANK_CODE cparty,
3410 nvl(b.accrual_interest,0) main_amount,
3411 b.INTEREST_RATE int_rate,
3412 nvl(b.statement_balance,0)+nvl(b.balance_adjustment,0) bal_out,
3413 a.portfolio_code,
3414 nvl(a.year_calc_type,'ACTUAL/ACTUAL') year_calc_type,
3415 decode(b.DAY_COUNT_TYPE,'F','PRIOR','L','FOLLOWING','B') day_count_type, -- AW Japan Project
3416 nvl(b.ROUNDING_TYPE,'R') rounding_type, -- AW Japan Project
3417 decode(nvl(b.DAY_COUNT_TYPE,'L'),'F',1,0) forward_adjust -- AW Japan Project
3418 from XTR_BANK_ACCOUNTS a,
3419 XTR_BANK_BALANCES b
3420 where b.COMPANY_CODE = p_company
3421 and a.ACCOUNT_NUMBER = b.ACCOUNT_NUMBER
3422 and a.PARTY_CODE = b.COMPANY_CODE
3423 --and nvl(a.SETOFF_ACCOUNT_YN,'N') <> 'Y'
3424 and b.BALANCE_DATE = (select max(c.BALANCE_DATE)
3425 from XTR_BANK_BALANCES c
3426 where c.BALANCE_DATE <= p_end_date
3427 and c.COMPANY_CODE = b.COMPANY_CODE
3428 and c.ACCOUNT_NUMBER = b.ACCOUNT_NUMBER)
3429 order by a.CURRENCY,a.ACCOUNT_NUMBER;
3430
3431 bk_det BK_CHARGE%ROWTYPE;
3432
3433 --
3434 cursor CA_DEAL_NUM(v_account_number varchar2,v_currency varchar2) is
3435 select DEAL_NUMBER,TRANSACTION_NUMBER,PRODUCT_TYPE
3436 from XTR_DEAL_DATE_AMOUNTS_V
3437 where DEAL_TYPE = 'CA'
3438 --and AMOUNT_TYPE = 'BAL'
3439 and ACCOUNT_NO = v_account_number
3440 and CURRENCY = v_currency
3441 and COMPANY_CODE = p_company;
3442
3443 ------------------------------------
3444 -- Oldest IG Transaction
3445 ------------------------------------
3446 cursor IG_OLDEST (v_deal_no NUMBER) is
3447 select TRANSFER_DATE
3448 from XTR_INTERGROUP_TRANSFERS
3449 where DEAL_NUMBER = v_deal_no
3450 order by TRANSFER_DATE;
3451
3452 ------------------------------------
3453 -- Oldest CA Transaction
3454 ------------------------------------
3455 cursor CA_OLDEST (v_acct_no VARCHAR2, v_ccy VARCHAR2, v_comp VARCHAR2) is
3456 select b.BALANCE_DATE
3457 from XTR_BANK_ACCOUNTS a,
3458 XTR_BANK_BALANCES b
3459 where b.COMPANY_CODE = v_comp
3460 and a.PARTY_CODE = v_comp
3461 and a.ACCOUNT_NUMBER = v_acct_no
3462 and a.CURRENCY = v_ccy
3463 and a.ACCOUNT_NUMBER = b.ACCOUNT_NUMBER
3464 order by BALANCE_DATE;
3465
3466 begin
3467
3468 /* AW Japan Project - select from IG and CA table.
3469 open ADJUST('ACCRUAL_DAYS_ADJUST');
3470 fetch ADJUST INTO l_days_adjust;
3471 close ADJUST;
3472
3473 l_days_adjust :=nvl(l_days_adjust,'FOLLOWING');
3474
3475 if l_days_adjust ='PRIOR' then
3476 l_forward_adjust := 1;
3477 --l_actual_end_date := p_end_date + 1;
3478 else
3479 l_forward_adjust := 0;
3480 --l_actual_end_date := p_end_date;
3481 end if;
3482 ----------------------------------------------------*/
3483
3484 l_actual_end_date := p_end_date;
3485
3486 ----------------------------------------------------------------------
3487 -- Calculate Accrual/Amortisation adustjments for Intergroup Transfers
3488 ----------------------------------------------------------------------
3489 open IG;
3490 l_ccy := NULL;
3491 l_deal_type :='IG';
3492 l_year_calc_type := null;
3493 LOOP
3494 fetch IG INTO ig_det;
3495 EXIT WHEN IG%NOTFOUND;
3496
3500
3497 l_deal_nos := ig_det.deal_number;
3498 l_trans_nos := ig_det.transaction_number;
3499
3501 if l_ccy is NOT NULL then
3502 ------------------------------------------------------------------
3503 -- IG compound issue - causes the wrong year_calc_type
3504 ------------------------------------------------------------------
3505 if l_ccy <> ig_det.currency then
3506 l_ccy := ig_det.currency;
3507 end if;
3508 open RND_FAC;
3509 fetch RND_FAC INTO l_rounding,l_year,l_hce_rate,l_year_calc_type;
3510 if RND_FAC%NOTFOUND then
3511 l_year := 365;
3512 l_rounding := 2;
3513 end if;
3514 close RND_FAC;
3515 -- end if;
3516 else
3517 l_ccy := ig_det.currency;
3518 open RND_FAC;
3519 fetch RND_FAC INTO l_rounding,l_year,l_hce_rate,l_year_calc_type;
3520 if RND_FAC%NOTFOUND then
3521 l_year := 365;
3522 l_rounding := 2;
3523 end if;
3524 close RND_FAC;
3525 end if;
3526
3527 l_year_calc_type :=nvl(l_year_calc_type,'ACTUAL/ACTUAL');
3528
3529 l_first_accrual_indic :='Y';
3530
3531 open CHK_FIRST_ACCRUAL;
3532 fetch CHK_FIRST_ACCRUAL into l_first_accrual_indic;
3533 close CHK_FIRST_ACCRUAL;
3534
3535 ----------------------------- AW Japan Project --------------------------------------------
3536 open IG_OLDEST(ig_det.deal_number);
3537 fetch IG_OLDEST into l_oldest_date;
3538 close IG_OLDEST;
3539 if l_oldest_date = ig_det.balance_date and ig_det.day_count_type = 'B' then
3540 l_first_tran := 'Y';
3541 else
3542 l_first_tran := 'N';
3543 end if;
3544 --------------------------------------------------------------------------------------------
3545
3546 if ig_det.balance_date = l_actual_end_date then
3547 -- l_amount_to_accrue_amort:= nvl(ig_det.main_amount,0);
3548
3549 -- AW 2113171 To handle FORWARD calculation
3550 if ig_det.day_count_type = 'PRIOR' or l_first_tran = 'Y' then -- AW Japan Project
3551 XTR_CALC_P.CALC_DAYS_RUN(ig_det.balance_date - ig_det.forward_adjust, -- AW Japan Project
3552 l_actual_end_date,
3553 l_year_calc_type,
3554 calc_days,
3555 l_year,
3556 ig_det.forward_adjust, -- AW Japan Project
3557 ig_det.day_count_type, -- AW Japan Project
3558 l_first_tran); -- AW Japan Project
3559 l_amount_to_accrue_amort := nvl(ig_det.main_amount,0) +
3560 xtr_fps2_p.interest_round(ig_det.bal_out * (ig_det.int_rate / 100) / l_year * calc_days,l_rounding,ig_det.rounding_type);
3561 else
3562 l_amount_to_accrue_amort:= nvl(ig_det.main_amount,0);
3563 end if;
3564 else
3565 -- AW 2113171 To handle FORWARD calculation
3566 XTR_CALC_P.CALC_DAYS_RUN(ig_det.balance_date - ig_det.forward_adjust, -- AW Japan Project
3567 l_actual_end_date,
3568 l_year_calc_type,
3569 calc_days,
3570 l_year,
3571 ig_det.forward_adjust, -- AW Japan Project
3572 ig_det.day_count_type, -- AW Japan Project
3573 l_first_tran); -- AW Japan Project
3574 l_amount_to_accrue_amort := nvl(ig_det.main_amount,0) +
3575 xtr_fps2_p.interest_round(ig_det.bal_out *
3576 (ig_det.int_rate / 100) / l_year * calc_days,l_rounding,ig_det.rounding_type);
3577 end if;
3578
3579 l_accrls_amount_bal :=0;
3580 open get_prv_bal;
3581 fetch get_prv_bal into l_accrls_amount_bal, l_prv_subtype; -- bug 3866372
3582 close get_prv_bal;
3583
3584 l_period_accrual_amount := nvl(l_amount_to_accrue_amort,0)-nvl(l_accrls_amount_bal,0);
3585
3586 if l_period_accrual_amount < 0 then
3587 l_subtype := 'FUND';
3588 elsif l_period_accrual_amount > 0 then
3589 l_subtype := 'INVEST';
3590 else
3591 l_subtype := nvl(l_prv_subtype,'INVEST');
3592 end if;
3593
3594
3595 if l_period_accrual_amount <> 0 then
3596 insert into XTR_ACCRLS_AMORT (BATCH_ID,DEAL_NO,TRANS_NO,COMPANY_CODE,DEAL_SUBTYPE,
3597 DEAL_TYPE,CURRENCY,PERIOD_FROM,PERIOD_TO,
3598 CPARTY_CODE,PRODUCT_TYPE,PORTFOLIO_CODE,
3599 INTEREST_RATE,TRANSACTION_AMOUNT,AMOUNT_TYPE,
3600 ACTION_CODE,ACCRLS_AMOUNT,YEAR_BASIS,
3601 FIRST_ACCRUAL_INDIC,ACTUAL_START_DATE,ACTUAL_MATURITY_DATE,
3602 NO_OF_DAYS,ACCRLS_AMOUNT_BAL)
3603 values(p_batch_id,l_deal_nos,l_trans_nos,p_company,l_subtype,
3604 'IG',ig_det.currency,p_start_date,p_end_date,
3605 ig_det.cparty,ig_det.product_type,
3609 'POS',abs(l_period_accrual_amount),
3606 ig_det.portfolio_code,ig_det.int_rate,
3607 abs(l_period_accrual_amount),'INTADJ', -- AW 2113171 Display same as accrl amt
3608 --ig_det.main_amount,'INTADJ',
3610 l_year,l_first_accrual_indic,
3611 ig_det.balance_date,l_actual_end_date,
3612 calc_days,l_amount_to_accrue_amort);
3613 end if;
3614 END LOOP;
3615 close IG;
3616
3617 ---------------------------------------------------------
3618 -- Calulate accrual adjustments for Company Bank Accounts
3619 ---------------------------------------------------------
3620 open BK_CHARGE;
3621 l_ccy := NULL;
3622 l_deal_type := 'CA';
3623 LOOP
3624 fetch BK_CHARGE INTO bk_det;
3625 EXIT WHEN BK_CHARGE%NOTFOUND;
3626 if l_ccy is NOT NULL then
3627 if l_ccy <> bk_det.currency then
3628 l_ccy := bk_det.currency;
3629 open RND_FAC;
3630 fetch RND_FAC INTO l_rounding,l_year,l_hce_rate,l_year_calc_type;
3631 if RND_FAC%NOTFOUND then
3632 l_year := 365;
3633 l_rounding := 2;
3634 end if;
3635 close RND_FAC;
3636 end if;
3637 else
3638 l_ccy := bk_det.currency;
3639 open RND_FAC;
3640 fetch RND_FAC INTO l_rounding,l_year,l_hce_rate,l_year_calc_type;
3641 if RND_FAC%NOTFOUND then
3642 l_year := 365;
3643 l_rounding := 2;
3644 end if;
3645 close RND_FAC;
3646 end if;
3647
3648 l_deal_nos := null;
3649 l_trans_nos := null;
3650 l_product_type := null;
3651
3652 open CA_DEAL_NUM(bk_det.account_number,bk_det.currency);
3653 fetch CA_DEAL_NUM into l_deal_nos,l_trans_nos,l_product_type;
3654 close CA_DEAL_NUM;
3655
3656 l_first_accrual_indic :='Y';
3657 open CHK_FIRST_ACCRUAL;
3658 fetch CHK_FIRST_ACCRUAL into l_first_accrual_indic;
3659 close CHK_FIRST_ACCRUAL;
3660
3661 ----------------------------- AW Japan Project --------------------------------------------
3662 open CA_OLDEST(bk_det.account_number, bk_det.currency, bk_det.company_code);
3663 fetch CA_OLDEST into l_oldest_date;
3664 close CA_OLDEST;
3665 if l_oldest_date = bk_det.balance_date and bk_det.day_count_type = 'B' then
3666 l_first_tran := 'Y';
3667 else
3668 l_first_tran := 'N';
3669 end if;
3670 --------------------------------------------------------------------------------------------
3671
3672 if bk_det.balance_date = l_actual_end_date then
3673 -- l_amount_to_accrue_amort:= nvl(bk_det.main_amount,0);
3674
3675 -- AW 2113171 To handle FORWARD calculation
3676 if bk_det.day_count_type = 'PRIOR' or l_first_tran = 'Y' then -- AW Japan Project
3677 XTR_CALC_P.CALC_DAYS_RUN(bk_det.balance_date - bk_det.forward_adjust, -- AW Japan Project
3678 l_actual_end_date,
3679 bk_det.year_calc_type,
3680 calc_days,
3681 l_year,
3682 bk_det.forward_adjust, -- AW Japan Project
3683 bk_det.day_count_type, -- AW Japan Project
3684 l_first_tran); -- AW Japan Project
3685 l_amount_to_accrue_amort := nvl(bk_det.main_amount,0) +
3686 xtr_fps2_p.interest_round(bk_det.bal_out *
3687 (bk_det.int_rate / 100) / l_year * calc_days,l_rounding,bk_det.rounding_type);
3688 --AW 2113171 Displays the Ref Amount similar to Arrear method
3689 --AW 2113171 if bk_det.main_amount = 0 or p_start_date = p_end_date then
3690 --AW 2113171 bk_det.main_amount := l_amount_to_accrue_amort;
3691 --AW 2113171 end if;
3692 else
3693 l_amount_to_accrue_amort:= nvl(bk_det.main_amount,0);
3694 end if;
3695 else
3696 -- AW 2113171 To handle FORWARD calculation
3697 XTR_CALC_P.CALC_DAYS_RUN(bk_det.balance_date - bk_det.forward_adjust, -- AW Japan Project
3698 l_actual_end_date,
3699 bk_det.year_calc_type,
3700 calc_days,
3701 l_year,
3702 bk_det.forward_adjust, -- AW Japan Project
3703 bk_det.day_count_type, -- AW Japan Project
3704 l_first_tran); -- AW Japan Project
3705
3706 l_amount_to_accrue_amort := nvl(bk_det.main_amount,0) +
3707 xtr_fps2_p.interest_round(bk_det.bal_out *
3708 (bk_det.int_rate / 100) / l_year * calc_days,l_rounding,bk_det.rounding_type);
3709 --AW 2113171 Displays the Ref Amount similar to Arrear method -- Should this be here too ????
3710 --AW 2113171 if bk_det.main_amount = 0 or p_start_date = p_end_date then
3714
3711 --AW 2113171 bk_det.main_amount := l_amount_to_accrue_amort;
3712 --AW 2113171 end if;
3713 end if;
3715 l_accrls_amount_bal :=0;
3716 open get_prv_bal;
3717 fetch get_prv_bal into l_accrls_amount_bal,l_prv_subtype;
3718 close get_prv_bal;
3719 l_period_accrual_amount := nvl(l_amount_to_accrue_amort,0)-nvl(l_accrls_amount_bal,0);
3720
3721 -- bug 3866372
3722 if l_period_accrual_amount < 0 then
3723 l_subtype := 'FUND';
3724 elsif l_period_accrual_amount > 0 then
3725 l_subtype := 'INVEST';
3726 else
3727 l_subtype := nvl(l_prv_subtype,'INVEST');
3728 end if;
3729
3730
3731 if l_period_accrual_amount <> 0 and l_deal_nos is not null then
3732 insert into XTR_ACCRLS_AMORT (BATCH_ID,DEAL_NO,TRANS_NO,COMPANY_CODE,DEAL_SUBTYPE,
3733 DEAL_TYPE,CURRENCY,PERIOD_FROM,PERIOD_TO,
3734 CPARTY_CODE,PRODUCT_TYPE,PORTFOLIO_CODE,
3735 INTEREST_RATE,TRANSACTION_AMOUNT,AMOUNT_TYPE,
3736 ACTION_CODE,ACCRLS_AMOUNT,YEAR_BASIS,
3737 FIRST_ACCRUAL_INDIC,ACTUAL_START_DATE,ACTUAL_MATURITY_DATE,
3738 NO_OF_DAYS,ACCRLS_AMOUNT_BAL)
3739 values(p_batch_id,l_deal_nos,l_trans_nos,p_company,l_subtype,
3740 'CA',bk_det.currency,p_start_date,p_end_date,
3741 bk_det.cparty,l_product_type,
3742 bk_det.portfolio_code,bk_det.int_rate,
3743 abs(l_period_accrual_amount),'INTADJ', -- AW 2113171 Display same as accrl amt
3744 --bk_det.main_amount,'INTADJ',
3745 'POS',abs(l_period_accrual_amount),
3746 l_year,l_first_accrual_indic,
3747 bk_det.balance_date,l_actual_end_date,
3748 calc_days,l_amount_to_accrue_amort);
3749 end if;
3750 END LOOP;
3751 close BK_CHARGE;
3752
3753 end CALC_INTGROUP_CACCT_ACCLS;
3754
3755 -----------------------------------------------------------------------------------------------------------------------
3756 -- Procedure to calculate NI accruals using effective interest method
3757 PROCEDURE CALCULATE_NI_EFFINT(p_company IN VARCHAR2,
3758 p_new_batch_id IN NUMBER,
3759 p_cur_batch_id IN NUMBER,
3760 p_batch_start IN DATE,
3761 p_batch_end IN DATE) AS
3762
3763
3764 /*-----------------------------------------------------*/
3765 /* Get param for Arrears(FOLLOWING) or Forward (PRIOR).*/
3766 /*-----------------------------------------------------*/
3767 cursor ADJUST(p_param_name varchar2) is
3768 select PARAM_VALUE
3769 from XTR_PRO_PARAM
3770 where PARAM_NAME = p_param_name;
3771
3772 /*------------------------------*/
3773 /* Get TRADE/SETTLE accounting */
3774 /*------------------------------*/
3775 cursor cur_TRADE_SETTLE is
3776 select PARAMETER_VALUE_CODE
3777 from XTR_COMPANY_PARAMETERS
3778 where company_code = p_company
3779 and parameter_code = 'ACCNT_TSDTM';
3780
3781 /*---------------------*/
3782 /* Get Rounding Factor */
3783 /*---------------------*/
3784 cursor RND_FAC (p_ccy VARCHAR2) is
3785 select m.ROUNDING_FACTOR
3786 from XTR_MASTER_CURRENCIES_V m
3787 where m.CURRENCY = p_ccy;
3788
3789
3790 l_trade_settle XTR_COMPANY_PARAMETERS.parameter_value_code%TYPE;
3791
3792 /*---------------------------------------------------*/
3793 /* NI Effective Interest method accruals calculation */
3794 /*---------------------------------------------------*/
3795
3796 -- Bug 2448432.
3797 -- Removed all usage of deal date when company accounting method parameter is set to
3798 -- trade date. Interest accrual should comence on deal start date always.
3799
3800 cursor EFFINT_DEALS is select
3801 a.status_code status_code,
3802 a.deal_type deal_type,
3803 a.deal_number deal_no,
3804 a.transaction_number trans_no,
3805 a.deal_subtype subtype,
3806 a.product_type product,
3807 a.portfolio_code portfolio,
3808 a.currency ccy,
3809 a.cparty_code cparty,
3810 a.client_code client,
3811 'EFFINT' EFFINT_amt_type,
3812 a.balance_out face_value,
3813 a.interest interest,
3814 a.all_in_rate all_in_rate,
3815 a.initial_fair_value initial_fair_value,
3816 a.start_date deal_start,
3817 a.maturity_date deal_maturity,
3821 decode(b.day_count_type,'F','PRIOR','L','FOLLOWING','B') day_count_type, -- AW Japan Project
3818 a.ni_reneg_date deal_action_date,
3819 b.year_calc_type year_calc_type,
3820 decode(b.calc_basis,'DISCOUNT','D','Y') calc_basis,
3822 b.rounding_type rounding_type, -- AW Japan Project
3823 a.trans_closeout_no resale_deal_no, -- Bug 2448432.
3824 a.interest_rate interest_rate --bug 4969194
3825 from XTR_ROLLOVER_TRANSACTIONS a,
3826 XTR_DEALS b
3827 where a.company_code = p_company
3828 and a.deal_type = 'NI'
3829 and a.deal_subtype in ('BUY','SHORT','ISSUE')
3830 and b.company_code = p_company
3831 and b.deal_no = a.deal_number
3832 and b.deal_type = a.deal_type
3833 and a.status_code <> 'CANCELLED'
3834 and nvl(a.all_in_rate,0) <> 0
3835 and a.start_date <= p_batch_end
3836 --and (a.maturity_date >= p_batch_start
3837 and (nvl(a.ni_reneg_date,a.maturity_date) >= p_batch_start
3838 or (a.deal_number,a.transaction_number,'EFFINT') not in (select b.deal_no,b.trans_no,b.amount_type
3839 from XTR_ACCRLS_AMORT b
3840 where b.company_code = p_company
3841 and b.deal_type = 'NI'));
3842
3843 /*----------------------------------------------------------*/
3844 /* Check if this is the first EFFINT accrual for the parcel */
3845 /*----------------------------------------------------------*/
3846 cursor CHK_FIRST_ACCRUAL (p_deal_no NUMBER,p_trans_no NUMBER,p_deal_type VARCHAR2,p_amt_type VARCHAR2)is
3847 select 'N'
3848 from XTR_ACCRLS_AMORT
3849 where deal_no = p_deal_no
3850 and trans_no = p_trans_no
3851 and deal_type = p_deal_type
3852 and amount_type = p_amt_type;
3853
3854 /*-----------------------------------------*/
3855 /* Get the previous EFFINT accrual balance */
3856 /*-----------------------------------------*/
3857 cursor GET_PRV_BAL (p_deal_no NUMBER, p_trans_no NUMBER,
3858 p_deal_type VARCHAR2, p_amt_type VARCHAR2,
3859 p_batch_end DATE) is
3860 select nvl(EFFINT_ACCRLS_AMOUNT_BAL,0)
3861 from XTR_ACCRLS_AMORT
3862 where deal_no = p_deal_no
3863 and trans_no = p_trans_no
3864 and deal_type = p_deal_type
3865 and amount_type = p_amt_type
3866 and action_code = 'POS'
3867 and batch_id < nvl(p_cur_batch_id, p_new_batch_id)
3868 order by period_to desc;
3869
3870 /*-----------------------------*/
3871 /* Get sum of all delta amount */
3872 /*-----------------------------*/
3873 cursor GET_TOTAL_POS (p_deal_no NUMBER, p_trans_no NUMBER,
3874 p_deal_type VARCHAR2, p_amt_type VARCHAR2) is
3875 select sum(decode(action_code,'POS',ACCRLS_AMOUNT,-ACCRLS_AMOUNT))
3876 from XTR_ACCRLS_AMORT
3877 where deal_no = p_deal_no
3878 and trans_no = p_trans_no
3879 and deal_type = p_deal_type
3880 and amount_type = p_amt_type
3881 and action_code in ('POS','ADJ'); -- Bug 2448432. Added new action code ADJ.
3882
3883 /*-----------*/
3884 /* Variables */
3885 /*-----------*/
3886 EFF EFFINT_DEALS%ROWTYPE;
3887 l_action_code VARCHAR2(7);
3888 l_accrls_amt_bal NUMBER;
3889 l_amount_to_accrue_amort NUMBER;
3890 l_actual_start DATE;
3891 l_accrl_adjust VARCHAR2(1);
3892 l_deal_closed VARCHAR2(1);
3893 l_deal_start DATE;
3894 l_deal_end DATE;
3895 l_dirname VARCHAR2(512);
3896 l_first_accrual_indic VARCHAR2(1);
3897 l_init_discnt_amt NUMBER;
3898 l_no_of_days NUMBER;
3899 l_period_accrual_amount NUMBER;
3900 l_prv_accrl_amt_bal NUMBER;
3901 l_rec XTR_REVAL_PROCESS_P.XTR_REVL_REC;
3902 l_REV_amt NUMBER;
3903 l_rounding NUMBER;
3904 l_start_adjust XTR_PRO_PARAM.PARAM_VALUE%TYPE;
3905 l_year_basis NUMBER;
3906 l_interest_rate NUMBER;
3907 l_status_code VARCHAR2(30);
3908
3909 -- Additions for bug 2448432.
3910
3911 l_tot_prev_accrls_amt NUMBER := 0;
3912 l_resale_both VARCHAR2(1) := 'N';
3913 l_accrl_to_date_amt NUMBER := 0;
3914
3915 Function RESALE_BOTH (p_deal_no IN NUMBER) return BOOLEAN is
3916 l_day_cnt_type XTR_DEALS.day_count_type%TYPE := 'L';
3917 Begin
3918 Select day_count_type
3919 into l_day_cnt_type
3920 from xtr_deals
3921 where deal_no = p_deal_no;
3922
3923 If (l_day_cnt_type = 'B') then
3924 Return (true);
3925 Else
3926 Return (false);
3927 End If;
3928
3929 Exception
3930 When Others then
3931 Return (false);
3932 End;
3933
3934 -- End 2448432 additions.
3935
3936 function CUMM_AMT_BAL (p_company IN VARCHAR2, p_new_batch_id IN NUMBER, p_amt_type IN VARCHAR2,
3937 p_deal_no IN NUMBER, p_trans_no IN NUMBER,
3941 begin
3938 p_action_code IN VARCHAR2) return NUMBER is
3939
3940 l_cumm_amt_bal NUMBER;
3942 select sum(ACCRLS_AMOUNT)
3943 into l_cumm_amt_bal
3944 from xtr_accrls_amort
3945 where company_code = p_company
3946 and batch_id < p_new_batch_id
3947 and deal_no = p_deal_no
3948 and trans_no = p_trans_no
3949 and amount_type = p_amt_type
3950 and action_code = p_action_code;
3951
3952 return nvl(l_cumm_amt_bal,0);
3953
3954 end;
3955
3956 BEGIN
3957
3958 xtr_risk_debug_pkg.start_conc_prog;
3959
3960 /*--------------------------------*/
3961 /* Get Trade or Settle accounting */
3962 /*--------------------------------*/
3963 open cur_TRADE_SETTLE;
3964 fetch cur_TRADE_SETTLE into l_trade_settle;
3965 close cur_TRADE_SETTLE;
3966
3967 l_trade_settle := nvl(l_trade_settle,'TRADE');
3968
3969 open EFFINT_DEALS;
3970 fetch EFFINT_DEALS INTO EFF;
3971 while EFFINT_DEALS%FOUND LOOP
3972
3973 open RND_FAC (EFF.ccy);
3974 fetch RND_FAC into l_rounding;
3975 close RND_FAC;
3976 l_rounding := nvl(l_rounding,2);
3977
3978 /*****************************************************************************************/
3979 /* Find Initial Fair Value (required for first accrual, i.e previous accrual not found) */
3980 /*****************************************************************************************/
3981
3982 l_rec.company_code := p_company;
3983 l_rec.deal_no := EFF.deal_no;
3984 l_rec.deal_type := EFF.deal_type;
3985 l_rec.trans_no := EFF.trans_no;
3986
3987 l_init_discnt_amt := EFF.interest;
3988 l_status_code := NULL;
3989
3990 /**********************************************************************/
3991 /* Determine Start Date to calculate Accrual Balance for this period */
3992 /**********************************************************************/
3993
3994 if nvl(EFF.deal_action_date, EFF.deal_maturity)> p_batch_end then
3995 ----------------------------------------------------------------------
3996 -- Deal's maturity or resale date is not reached. Create POS only. --
3997 ----------------------------------------------------------------------
3998 l_actual_start := p_batch_end;
3999
4000 else
4001 --------------------------------------------------------------
4002 -- Deal's maturity or resale date is reached. Create REV. --
4003 --------------------------------------------------------------
4004 l_actual_start := nvl(EFF.deal_action_date,EFF.deal_maturity);
4005 end if;
4006
4007
4008 /**********************************************/
4009 /* Determine previous period accrued balance */
4010 /**********************************************/
4011
4012 l_first_accrual_indic :='Y';
4013 open CHK_FIRST_ACCRUAL (EFF.deal_no, EFF.trans_no, EFF.deal_type, EFF.EFFINT_amt_type);
4014 fetch CHK_FIRST_ACCRUAL into l_first_accrual_indic;
4015 close CHK_FIRST_ACCRUAL;
4016
4017 if l_first_accrual_indic = 'Y' then
4018 l_prv_accrl_amt_bal := l_init_discnt_amt;
4019 else
4020 open get_prv_bal (EFF.deal_no, EFF.trans_no, EFF.deal_type, EFF.EFFINT_amt_type, p_batch_end);
4021 fetch get_prv_bal into l_prv_accrl_amt_bal;
4022 close get_prv_bal;
4023 end if;
4024
4025 /**********************************************/
4026 /* Calculate current period accrued balance */
4027 /**********************************************/
4028
4029 l_accrl_adjust := 'N';
4030 if (nvl(EFF.deal_action_date,EFF.deal_maturity) > p_batch_end) or
4031 (EFF.deal_action_date is not null and EFF.deal_action_date <= p_batch_end) then
4032
4033 -- Bug 2448432.
4034 -- Init resale deal = 'Both' flag.
4035
4036 l_resale_both := 'N';
4037
4038 -- End 2448432.
4039
4040 /*---------------------------------------*/
4041 /* Adjust date for Forward or Arrears */
4042 /*---------------------------------------*/
4043
4044 if EFF.deal_action_date is not null and EFF.deal_action_date <= p_batch_end then
4045 l_accrl_adjust := 'N';
4046 l_interest_rate := EFF.interest_rate; -- bug 4969194
4047 l_status_code := EFF.status_code; -- bug 4969194
4048 -- Bug 2448432.
4049 -- Parcel resold within batch period.
4050 -- Set resale flag to be used in call to CALCULATION_EFFECTIVE_INTEREST proc.
4051
4052 If (RESALE_BOTH(EFF.resale_deal_no)) then
4053 l_resale_both := 'Y';
4054 End If;
4055
4056 -- End 2448432.
4057
4058 else
4059 l_accrl_adjust := 'Y';
4060 l_interest_rate := EFF.all_in_rate; -- bug 4969194
4061 end if;
4062
4063 XTR_ACCRUAL_PROCESS_P.CALCULATE_EFFECTIVE_INTEREST(
4064 EFF.face_value,
4065 l_interest_rate,
4069 l_accrl_adjust,
4066 EFF.deal_start,
4067 l_actual_start,
4068 EFF.deal_maturity,
4070 EFF.year_calc_type,
4071 EFF.calc_basis,
4072 l_prv_accrl_amt_bal,
4073 l_no_of_days,
4074 l_year_basis,
4075 l_amount_to_accrue_amort, -- unamortized interest from l_actual_start to maturity.
4076 l_period_accrual_amount, -- accrual amount from EFF.deal_start to l_actual_start.
4077 EFF.day_count_type,
4078 l_resale_both, -- bug 244832
4079 l_status_code); -- Bug 4969194
4080
4081 else
4082 --------------------------------------------------------------------------------
4083 -- Maturity date is reached and parcel held to maturity - need not calculate. --
4084 --------------------------------------------------------------------------------
4085
4086 l_amount_to_accrue_amort := 0;
4087
4088 end if;
4089
4090 /* -------------------------------------------------------------------------------------------------*/
4091 /* This is to adjust the start date to be displayed in Accruals form. Same logic as Straight line. */
4092 /* -------------------------------------------------------------------------------------------------*/
4093 l_deal_start := EFF.deal_start;
4094 /* AW 2113171 Problem: EFFINT's display start date is one day earlier than INTADJ. Confusing to user.
4095 open ADJUST('ACCRUAL_DAYS_ADJUST');
4096 fetch ADJUST INTO l_start_adjust;
4097 close ADJUST;
4098 l_start_adjust :=nvl(l_start_adjust,'FOLLOWING');
4099
4100 if nvl(EFF.deal_action_date,EFF.deal_maturity)<= p_batch_end then
4101 l_deal_end := nvl(EFF.deal_action_date,EFF.deal_maturity);
4102 else
4103 l_deal_end := p_batch_end;
4104 end if;
4105
4106 if l_start_adjust ='PRIOR' and l_deal_end <> EFF.deal_maturity and l_accrl_adjust ='Y' then
4107 l_deal_start := EFF.deal_start - 1;
4108 else
4109 l_deal_start := EFF.deal_start;
4110 end if;
4111 */
4112 /* -------------------------------------------------------------------------------------------------*/
4113
4114
4115 /****************************/
4116 /* Create accrual records */
4117 /****************************/
4118
4119 -- Bug 2448432.
4120 -- Obtain actual accrued-to-date effective interest amount from previous batches.
4121 -- If parcel has matured or resold during batch period, need to back calculate the
4122 -- last periodic accrual amount to minimize rounding issues which may cause under
4123 -- or over accrual of the actual interest defined at time of deal creation.
4124
4125 l_accrl_to_date_amt := 0;
4126
4127 Open GET_TOTAL_POS (EFF.deal_no, EFF.trans_no, EFF.deal_type, EFF.EFFINT_amt_type);
4128 Fetch GET_TOTAL_POS into l_accrl_to_date_amt;
4129 Close GET_TOTAL_POS;
4130
4131 -- End 2448432.
4132
4133 If nvl(EFF.deal_action_date, EFF.deal_maturity) <= p_batch_end then
4134
4135 -- Bug 2448432.
4136 -- Last accrual batch for the parcel. Parcel has either been resold or matured within the batch period.
4137 -- Adjust the last period's periodic accrual interest to minimize rounding issues.
4138
4139 -- EFFINT.interest calculated at time of deal entry should already be properly rounded based
4140 -- on the deal's interest rounding setting and currency. Likewise for l_accrl_to_date_amt,
4141 -- the sum of accrual interest (EFFINT/POS) for the parcel as calculated for previous accrual batches,
4142 -- since each parcel's periodic accrual amount for each batch is also rounded based on the deal's
4143 -- interest rounding setting and currency. In the event of a resale, the unamortized interest portion
4144 -- l_amount_to_accrue_amort (with the identical rounding) would be recorded as the reversal amount
4145 -- at time of resale.
4146
4147
4148 -- bug 4969194 changed the call to interest rounding
4149
4150 l_period_accrual_amount := EFF.interest - nvl(l_accrl_to_date_amt,0)- xtr_fps2_p.interest_round( nvl(l_amount_to_accrue_amort,0), l_rounding, EFF.rounding_type);
4151
4152 l_accrls_amt_bal := nvl(l_accrl_to_date_amt,0) + nvl(l_period_accrual_amount,0);
4153
4154
4155
4156 -- End 2448432.
4157
4158
4159 -- AW 2113171 Do not display a row if Accrual Amt and Balance are zero.
4160 -- AW Japan Project
4161
4162 -- Bug 2448432.
4163 -- (1) Removed unecessary interest rounding to the periodic and cumulative accrual interest amounts.
4164 -- These amounts have already been properly rounded before being stored.
4165 -- (2) Changed the condition under which a 'POS' EFFINT accrual record is to be created.
4166 -- A 'POS' record will be created only if the periodic accrual amount is > 0.
4167 -- In cases where the periodic accrual amount is < 0, a record with the new action code 'ADJ'
4168 -- will be created using the absolute value of the periodic accrual amount.
4172 -- may result.
4169 -- A negative value may result due to rounding or certain resale scenerios when a day's worth
4170 -- of interest is to be backed out from the original purchase deal. In such cases, if an
4171 -- adjustment amount is not provided, an overstatement of interest income in the GL accounts
4173
4174 if (l_accrls_amt_bal <> 0) and (l_period_accrual_amount <> 0) then
4175
4176 insert into XTR_ACCRLS_AMORT (BATCH_ID, DEAL_NO, TRANS_NO,
4177 COMPANY_CODE, DEAL_SUBTYPE, DEAL_TYPE,
4178 CURRENCY, PERIOD_FROM, PERIOD_TO,
4179 CPARTY_CODE, PRODUCT_TYPE, PORTFOLIO_CODE,
4180 INTEREST_RATE, TRANSACTION_AMOUNT,
4181 AMOUNT_TYPE, ACTION_CODE,
4182 ACCRLS_AMOUNT, YEAR_BASIS, FIRST_ACCRUAL_INDIC,
4183 ACTUAL_START_DATE, ACTUAL_MATURITY_DATE,
4184 NO_OF_DAYS, EFFINT_ACCRLS_AMOUNT_BAL,
4185 ACCRLS_AMOUNT_BAL)
4186 values(p_new_batch_id, EFF.deal_no, EFF.trans_no,
4187 p_company, EFF.subtype, EFF.deal_type,
4188 EFF.ccy, p_batch_start, l_actual_start,
4189 --EFF.ccy, p_batch_start, p_batch_end,
4190 EFF.cparty, EFF.product, EFF.portfolio,
4191 EFF.all_in_rate, EFF.interest,
4192 EFF.EFFINT_amt_type, decode(sign(l_period_accrual_amount),-1,'ADJ','POS'),
4193 abs(nvl(l_period_accrual_amount,0)),
4194 l_year_basis, l_first_accrual_indic,
4195 l_deal_start, EFF.deal_maturity,
4196 -- l_actual_start, EFF.deal_maturity,
4197 nvl(l_no_of_days,0),
4198 abs(l_amount_to_accrue_amort),
4199 l_accrls_amt_bal);
4200 end if;
4201
4202 -- By issuing another fetch to GET_TOTAL_POS, we should obtain the total accrual amount to-date
4203 -- in all the 'POS' and 'ADJ' records created for the parcel to-date, including the one just
4204 -- created above.
4205
4206 open GET_TOTAL_POS(EFF.deal_no, EFF.trans_no, EFF.deal_type, EFF.EFFINT_amt_type);
4207 fetch GET_TOTAL_POS into l_REV_amt;
4208 close GET_TOTAL_POS;
4209
4210 -- The 'REV' record created in the final accrual batch of the parcel should properly contain
4211 -- the total 'POS' and 'ADJ' amount to-date for the parcel.
4212
4213 -- Bug 2449432.
4214 -- Create reversal record only if there is an accrual amount to reverse.
4215 -- If resold on the 1st day of the purchase. There will be no accrual (POS) amounts created.
4216
4217 If (nvl(l_REV_amt,0) <> 0) then
4218 insert into XTR_ACCRLS_AMORT (BATCH_ID, DEAL_NO, TRANS_NO,
4219 COMPANY_CODE, DEAL_SUBTYPE, DEAL_TYPE,
4220 CURRENCY, PERIOD_FROM, PERIOD_TO,
4221 CPARTY_CODE, PRODUCT_TYPE, PORTFOLIO_CODE,
4222 INTEREST_RATE, TRANSACTION_AMOUNT,
4223 AMOUNT_TYPE, ACTION_CODE,
4224 ACCRLS_AMOUNT, YEAR_BASIS, FIRST_ACCRUAL_INDIC,
4225 ACTUAL_START_DATE, ACTUAL_MATURITY_DATE,
4226 NO_OF_DAYS, EFFINT_ACCRLS_AMOUNT_BAL,
4227 ACCRLS_AMOUNT_BAL)
4228 values(p_new_batch_id, EFF.deal_no, EFF.trans_no,
4229 p_company, EFF.subtype, EFF.deal_type,
4230 EFF.ccy, p_batch_start, l_actual_start,
4231 --EFF.ccy, p_batch_start, p_batch_end,
4232 EFF.cparty, EFF.product, EFF.portfolio,
4233 EFF.all_in_rate, EFF.interest,
4234 EFF.EFFINT_amt_type, 'REV',
4235 xtr_fps2_p.interest_round(l_REV_amt,l_rounding,EFF.rounding_type),
4236 l_year_basis, l_first_accrual_indic,
4237 l_deal_start, EFF.deal_maturity,
4238 --l_actual_start, EFF.deal_maturity,
4239 nvl(l_no_of_days,0), 0,
4240 0);
4241 --round(l_REV_amt,l_rounding));
4242 --round(l_accrls_amt_bal,l_rounding))
4243 End If;
4244 else
4245
4246 --Add 2448432.
4247
4251
4248 l_accrls_amt_bal := nvl(l_accrl_to_date_amt,0) + nvl(l_period_accrual_amount,0);
4249
4250 --End add 2448432.
4252 -- AW 2113171 Do not display a row if Accrual Amt and Balance are zero.
4253 -- AW Japan Project
4254
4255 if xtr_fps2_p.interest_round(l_accrls_amt_bal,l_rounding,EFF.rounding_type) <> 0 and
4256 xtr_fps2_p.interest_round(abs(l_period_accrual_amount),l_rounding,EFF.rounding_type) <> 0 then
4257
4258 insert into XTR_ACCRLS_AMORT (BATCH_ID, DEAL_NO, TRANS_NO,
4259 COMPANY_CODE, DEAL_SUBTYPE, DEAL_TYPE,
4260 CURRENCY, PERIOD_FROM, PERIOD_TO,
4261 CPARTY_CODE, PRODUCT_TYPE, PORTFOLIO_CODE,
4262 INTEREST_RATE, TRANSACTION_AMOUNT,
4263 AMOUNT_TYPE, ACTION_CODE,
4264 ACCRLS_AMOUNT, YEAR_BASIS, FIRST_ACCRUAL_INDIC,
4265 ACTUAL_START_DATE, ACTUAL_MATURITY_DATE,
4266 NO_OF_DAYS, EFFINT_ACCRLS_AMOUNT_BAL,
4267 ACCRLS_AMOUNT_BAL)
4268 values(p_new_batch_id, EFF.deal_no, EFF.trans_no,
4269 p_company, EFF.subtype, EFF.deal_type,
4270 EFF.ccy, p_batch_start, l_actual_start,
4271 --EFF.ccy, p_batch_start, p_batch_end,
4272 EFF.cparty, EFF.product, EFF.portfolio,
4273 EFF.all_in_rate, EFF.interest,
4274 EFF.EFFINT_amt_type, 'POS',
4275 xtr_fps2_p.interest_round(abs(l_period_accrual_amount),
4276 l_rounding,EFF.rounding_type),
4277 l_year_basis, l_first_accrual_indic,
4278 l_deal_start, EFF.deal_maturity,
4279 --l_actual_start, EFF.deal_maturity,
4280 nvl(l_no_of_days,0),
4281 xtr_fps2_p.interest_round(abs(l_amount_to_accrue_amort),
4282 l_rounding,EFF.rounding_type),
4283 xtr_fps2_p.interest_round( l_accrls_amt_bal,l_rounding,EFF.rounding_type));
4284 end if;
4285 end if;
4286
4287 fetch EFFINT_DEALS INTO EFF;
4288
4289 END LOOP;
4290 close EFFINT_DEALS;
4291 --
4292
4293 xtr_risk_debug_pkg.stop_conc_debug;
4294
4295 end CALCULATE_NI_EFFINT;
4296
4297 -----------------------------------------------------------------------------------------------------------------------
4298 -- Procedure to Transfer Revaluations to Deal Date Amounts to await Journalling
4299 PROCEDURE TSFR_ACCRUALS_FOR_JNL_PROCESS(
4300 p_company IN VARCHAR2,
4301 p_end_date IN DATE) is
4302
4303 --
4304 cursor GETROWS is
4305 select ROWID,DEAL_NO,TRANS_NO,DEAL_TYPE,DEAL_SUBTYPE,PRODUCT_TYPE,PORTFOLIO_CODE,ACTION_CODE,
4306 CURRENCY,ACCRLS_AMOUNT,AMOUNT_TYPE ACC_AMOUNT_TYPE
4307 from XTR_ACCRLS_AMORT
4308 where COMPANY_CODE = p_company
4309 and PERIOD_TO = p_end_date
4310 order by currency;
4311 --
4312 l_amttype VARCHAR2(7);
4313 l_date_ty VARCHAR2(7) :='ACCRUAL';
4314 l_deal_no NUMBER;
4315 l_trans_nos NUMBER;
4316 l_old_ccy VARCHAR2(15);
4317 l_amt_hce NUMBER;
4318 l_product VARCHAR2(10);
4319 l_portfolio VARCHAR2(7);
4320 l_type VARCHAR2(7);
4321 l_ccy VARCHAR2(15);
4322 l_subty VARCHAR2(7);
4323 l_acclrs_amount NUMBER;
4324 l_action VARCHAR2(7);
4325 l_hce_rate NUMBER;
4326 l_sysdate DATE :=trunc(sysdate);
4327 l_rowid VARCHAR2(30);
4328 --
4329 cursor HCE is
4330 select s.hce_rate
4331 from XTR_MASTER_CURRENCIES s
4332 where s.CURRENCY = l_ccy;
4333 --
4334 begin
4335 l_old_ccy := NULL;
4336 open GETROWS;
4337 LOOP
4338 FETCH GETROWS INTO l_rowid,l_deal_no,l_trans_nos,l_type,l_subty,l_product,l_portfolio,l_action,
4339 l_ccy,l_acclrs_amount,l_amttype;
4340 EXIT WHEN GETROWS%NOTFOUND;
4341 if l_old_ccy is NULL or l_old_ccy <> l_ccy or l_hce_rate is NULL then
4342 -- fetch HCE rate
4343 open HCE;
4344 fetch HCE INTO l_hce_rate;
4345 close HCE;
4346 end if;
4347 l_old_ccy := l_ccy;
4348 --
4349 l_amt_hce := round((l_acclrs_amount / nvl(l_hce_rate,1)),2);
4350 --
4351 insert into XTR_DEAL_DATE_AMOUNTS
4352 (deal_type,amount_type,date_type,deal_number,transaction_date,
4353 currency,amount,hce_amount,amount_date,action_code,
4354 cashflow_amount,company_code,transaction_number,
4355 deal_subtype,authorised,product_type,status_code,
4356 portfolio_code)
4357 values
4358 (l_type,l_amttype,l_date_ty,l_deal_no,l_sysdate,
4359 l_ccy,l_acclrs_amount,l_amt_hce,p_end_date,l_action,0,
4360 p_company,l_trans_nos,l_subty,'N',l_product,'CURRENT',
4361 l_portfolio);
4362 --
4366 END LOOP;
4363 update XTR_ACCRLS_AMORT
4364 set TRANSFERED_ON = l_sysdate
4365 where rowid=l_rowid;
4367 close GETROWS;
4368 /*
4369 exception
4370 when others then
4371 xtr_debug_pkg.debug('Transfer err:l_deal_no='||to_char(l_deal_no));
4372 */
4373 end TSFR_ACCRUALS_FOR_JNL_PROCESS;
4374 --------------------------------------------------------------------------------------------------------------------------
4375 end XTR_ACCRUAL_PROCESS_P;
[Home] [Help]