[Home] [Help]
PACKAGE BODY: APPS.XTR_CALC_P
Source
1 PACKAGE BODY XTR_CALC_P as
2 /* $Header: xtrcalcb.pls 120.15 2009/01/13 09:37:05 amunnuru ship $ */
3 ----------------------------------------------------------------------------------------------------------------
4 -- This is just a cover function that determines whether CALC_DAYS_RUN or
5 -- CALC_DAYS_RUN_B should be called
6 -- use this procedure instead of CALC_DAYS_RUN if ACT/ACT-BOND day count basis
7 -- is used
8 -- When this procedure is called, and if the method is ACT/ACT-BOND,be aware of
9 -- the fact that year_basis will be
10 -- calculated incorrectly if start_date and end_date combined do not form a
11 -- coupon period. So, if year_basis are needed, make sure that, coupon periods
12 -- are sent in as parameters. num_days are calculated correctly all the time
13 PROCEDURE CALC_DAYS_RUN_C(start_date IN DATE,
14 end_date IN DATE,
15 method IN VARCHAR2,
16 frequency IN NUMBER,
17 num_days IN OUT NOCOPY NUMBER,
18 year_basis IN OUT NOCOPY NUMBER,
19 fwd_adjust IN NUMBER,
20 day_count_type IN VARCHAR2,
21 first_trans_flag IN VARCHAR2) is
22 --
23 begin
24 XTR_MM_COVERS.CALC_DAYS_RUN_C(start_date,end_date,method,frequency,num_days,year_basis,fwd_adjust,day_count_type,first_trans_flag);
25 end CALC_DAYS_RUN_C;
26
27
28 -- This calculates the number of days and year basis for bond only day count
29 -- basis(ACT/ACT-BOND)
30 -- For ACT/ACT-BOND day count basis, this procedure must be used or preferably
31 -- through CALC_DAYS_RUN_C. CALC_DAYS_RUN must not be used for the day count
32 -- basis
33 -- When this procedure is called, be aware of the fact that year_basis will be
34 -- calculated incorrectly if start_date and end_date combined do not form a
35 -- coupon period. So, if year_basis are needed, make sure that, coupon periods
36 -- are sent in as parameters. num_days are calculated correctly all the time
37 PROCEDURE CALC_DAYS_RUN_B(start_date IN DATE,
38 end_date IN DATE,
39 method IN VARCHAR2,
40 frequency IN NUMBER,
41 num_days IN OUT NOCOPY NUMBER,
42 year_basis IN OUT NOCOPY NUMBER) is
43 begin
44 XTR_MM_COVERS.CALC_DAYS_RUN_B(start_date,end_date,method,frequency,num_days,year_basis);
45 end CALC_DAYS_RUN_B;
46
47
48 -- Calculate over a Year Basis and Number of Days ased on different calc
49 -- methods. Note that this procedure now supports ACTUAL/365L day count basis,
50 -- but it does not support ACT/ACT-BOND day count basis. In order to use the day
51 -- count basis, CALC_DAYS_RUN_C must be used
52 PROCEDURE CALC_DAYS_RUN(start_date IN DATE,
53 end_date IN DATE,
54 method IN VARCHAR2,
55 num_days IN OUT NOCOPY NUMBER,
56 year_basis IN OUT NOCOPY NUMBER,
57 fwd_adjust IN NUMBER,
58 day_count_type IN VARCHAR2,
59 first_trans_flag IN VARCHAR2) is
60 begin
61 XTR_MM_COVERS.CALC_DAYS_RUN(start_date,end_date,method,num_days,year_basis,fwd_adjust,day_count_type,first_trans_flag);
62 end CALC_DAYS_RUN;
63 -----------
64 /**************************************************/
65 /* New procedure added to handle IG calc_days_run */
66 /* calculation only. Made for bug 5349167 */
67 /**************************************************/
68 PROCEDURE CALC_DAYS_RUN_IG(start_date IN DATE,
69 end_date IN DATE,
70 method IN VARCHAR2,
71 num_days IN OUT NOCOPY NUMBER,
72 year_basis IN OUT NOCOPY NUMBER,
73 fwd_adjust IN NUMBER DEFAULT NULL,
74 day_count_type IN VARCHAR2 DEFAULT NULL,
75 first_trans_flag IN VARCHAR2 DEFAULT NULL) is
76 --
77 l_start_date DATE := start_date;
78 l_end_date DATE := end_date;
79 l_start_year NUMBER := to_number(to_char(start_date,'YYYY'));
80 l_end_year NUMBER := to_number(to_char(end_date,'YYYY'));
81 start_year_basis NUMBER;
82 end_year_basis NUMBER;
83 l_total_days NUMBER;
84 l_total_year NUMBER:= l_end_year - l_start_year;
85 --
86 begin
87 if start_date is not null and end_date is not null and method is not null then
88
89 if l_end_date <l_start_date then
90 FND_MESSAGE.Set_Name('XTR', 'XTR_1059');
91 APP_EXCEPTION.raise_exception;
92
93 else
94
95 -------------------------------
96 -- For all ACTUAL year basis --
97 -------------------------------
98 if substr(method,1,6) = 'ACTUAL' then
99 num_days := l_end_date - l_start_date;
100 year_basis := 365;
101
102 if method = 'ACTUAL360' then
103 year_basis := 360;
104 elsif method = 'ACTUAL365' then
105 year_basis := 365;
106 elsif method = 'ACTUAL365L' then
107 -- if the "to year" is a leap year use 366 day count basis. Otherwise, use 365
108 if to_char(last_day(to_date('01/02'||to_char(l_end_date,'YYYY'),'DD/MM/YYYY')),'DD') = '29' then
109 year_basis:=366;
110 else
111 year_basis:=365;
112 end if;
113 elsif method = 'ACTUAL/ACTUAL' then
114
115 /***************************************************************/
116 /* Bug 3887142. Correct Actual/Actual calculation for IG only. */
117 /***************************************************************/
118 If l_end_year = l_start_year then -- same year. Determine whether it's leap year.
119 if to_char(last_day(to_date('01/02'||to_char(l_end_date,'YYYY'),
120 'DD/MM/YYYY')),'DD') = '29' then
121 year_basis := 366;
122 else
123 year_basis := 365;
124 end if;
125 else
126 if to_char(last_day(to_date('01/02'||to_char(l_start_date,'YYYY'),
127 'DD/MM/YYYY')),'DD') = '29' then
128 IF day_count_type='B' AND first_trans_flag ='Y' THEN
129 start_year_basis := (to_date('1/1/'||to_char(l_start_year+1),'DD/MM/YYYY') - l_start_date + 1) /366;
130 else
131 start_year_basis := (to_date('1/1/'||to_char(l_start_year+1),'DD/MM/YYYY')
132 - l_start_date) /366;
133 end if;
134 else
135 IF day_count_type='B' AND first_trans_flag ='Y' THEN
136 start_year_basis := (to_date('1/1/'||to_char(l_start_year+1),'DD/MM/YYYY') - l_start_date + 1) / 365;
137 else
138 start_year_basis := (to_date('1/1/'||to_char(l_start_year+1),'DD/MM/YYYY') - l_start_date) / 365;
139 end if;
140 end if;
141
142 if to_char(last_day(to_date('01/02'||to_char(l_end_date,'YYYY'),
143 'DD/MM/YYYY')),'DD') = '29' then
144 end_year_basis := (l_end_date - to_date('1/1/'||to_char(l_end_year),
145 'DD/MM/YYYY')) / 366;
146 else
147 end_year_basis := (l_end_date - to_date('1/1/'||to_char(l_end_year),
148 'DD/MM/YYYY')) / 365;
149 end if;
150
151 IF day_count_type='B' AND first_trans_flag ='Y' THEN
152 l_total_days := num_days +1;
153 else
154 l_total_days := num_days;
155 END IF;
156
157 Year_basis := l_total_days / (start_year_basis + (l_total_year -1)
158 + end_year_basis);
159 End if;
160 End if;
161
162 -------------------------------
163 -- Interest Override feature --
164 -- Adde Day count type logic --
165 -------------------------------
166 IF day_count_type='B' AND first_trans_flag ='Y' THEN
167 num_days := num_days +1;
168 END IF;
169 ------------------------------
170 -- For all other year basis --
171 ------------------------------
172 else
173
174 /*-------------------------------------------------------------------------------------------*/
175 /* AW 2113171 This date is adjusted when called in CALCULATE_ACCRUAL_AMORTISATION. */
176 /* Need to add one day back to it for FORWARD, and then adjust later in num_days
177 (see below).*/
178 /* The 'fwd_adjust' parameter is used 30/360, 30E/360, 30E+/360 calculations.
179 */
180 /* If it is 1, then it is Forward, if it is 0, then it is Arrear.
181 */
182 /*-------------------------------------------------------------------------------------------*/
183 l_start_date := start_date + nvl(fwd_adjust,0);
184 /*-------------------------------------------------------------------------------------------*/
185
186 -- Calculate over a 360 basis based on different calc methods
187 year_basis :=360;
188
189 if method = '30/' then
190 if to_number(to_char(start_date + nvl(fwd_adjust,0),'DD')) = 31 then
191 -- AW 2113171
192 -- make start date = 30th ie add 1 day
193 l_start_date := start_date + nvl(fwd_adjust,0) - 1;
194 -- AW 2113171
195 end if;
196 if to_number(to_char(end_date,'DD')) = 31 then
197 if to_number(to_char(start_date + nvl(fwd_adjust,0),'DD')) in(30,31) then
198 -- AW 2113171
199 -- make end date = 30th if end date = 31st
200 -- only if start date is 30th or 31st ie minus 1 day from calc
201 l_end_date := end_date - 1;
202 end if;
203 end if;
204 elsif method = '30E/' then
205 if to_number(to_char(start_date + nvl(fwd_adjust,0),'DD')) = 31 then
206 -- AW 2113171
207 -- make start date = 30th ie add 1 day
208 l_start_date := start_date + nvl(fwd_adjust,0) - 1;
209 -- AW 2113171
210 end if;
211 if to_number(to_char(end_date,'DD')) = 31 then
212 -- make end date = 30th ie minus 1 day
213 l_end_date := end_date - 1;
214 end if;
215 elsif method = '30E+/' then
216 if to_number(to_char(start_date + nvl(fwd_adjust,0),'DD')) = 31 then
217 -- AW 2113171
218 -- make start date = 30th ie add 1 day
219 l_start_date := start_date + nvl(fwd_adjust,0) - 1;
220 -- AW 2113171
221 end if;
222 if to_number(to_char(end_date,'DD')) = 31 then
223 -- make end date = 1st of the next month
224 l_end_date := end_date + 1;
225 end if;
226 end if;
227
228 -- Calculate based on basic 30/360 method
229 --with the above modifications
230 num_days := to_number(to_char(l_end_date,'DD')) -
231 to_number(to_char(l_start_date,'DD')) +
232 (30 * (
233 to_number(to_char(l_end_date,'MM')) -
234 to_number(to_char(l_start_date,'MM')))) +
235 (360 * (
236 to_number(to_char(l_end_date,'YYYY')) -
237 to_number(to_char(l_start_date,'YYYY'))));
238
239 /*-----------------------------------------------*/
240 /* AW 2113171 */
241 /*-----------------------------------------------*/
242 num_days := num_days + nvl(fwd_adjust,0);
243 /*-----------------------------------------------*/
244
245 end if;
246
247 end if;
248
249 end if;
250
251 end CALC_DAYS_RUN_IG;
252 -------------
253
254 /* Bug 2358592 - this procedure is now a stub function for the similar procedure found in xtr_mm_covers */
255 PROCEDURE CALCULATE_BOND_PRICE_YIELD(p_bond_issue_code IN VARCHAR2,
256 p_settlement_date IN DATE,
257 p_ex_cum_next_coupon IN VARCHAR2,-- EX,CUM
258 p_calculate_yield_or_price IN VARCHAR2,-- Y,P
259 p_yield IN OUT NOCOPY NUMBER,
260 p_accrued_interest IN OUT NOCOPY NUMBER,
261 p_clean_price IN OUT NOCOPY NUMBER,
262 p_dirty_price IN OUT NOCOPY NUMBER,
263 p_input_or_calculator IN VARCHAR2, -- C,I
264 p_commence_date IN DATE,
265 p_maturity_date IN DATE,
266 p_prev_coupon_date IN DATE,
267 p_next_coupon_date IN DATE,
268 p_calc_type IN VARCHAR2,
269 p_year_calc_type IN VARCHAR2,
270 p_accrued_int_calc_basis IN VARCHAR2,
271 p_coupon_freq IN NUMBER,
272 p_calc_rounding IN NUMBER,
273 p_price_rounding IN NUMBER,
274 p_price_round_type IN VARCHAR2,
275 p_yield_rounding IN NUMBER,
276 p_yield_round_type IN VARCHAR2,
277 p_coupon_rate IN NUMBER,
278 p_num_coupons_remain IN NUMBER,
279 p_day_count_type IN VARCHAR2,
280 p_first_trans_flag IN VARCHAR2,
281 p_deal_subtype IN VARCHAR2,
282 p_currency IN VARCHAR2,
283 p_face_value IN NUMBER,
284 p_consideration IN NUMBER,
285 p_rounding_type IN VARCHAR2) IS
286
287 p_py_in XTR_MM_COVERS.BOND_PRICE_YIELD_IN_REC_TYPE;
288 p_py_out XTR_MM_COVERS.BOND_PRICE_YIELD_OUT_REC_TYPE;
289
290 BEGIN
291 p_py_in.p_bond_issue_code:= p_bond_issue_code;
292 p_py_in.p_settlement_date:= p_settlement_date;
293 p_py_in.p_ex_cum_next_coupon:= p_ex_cum_next_coupon;
294 p_py_in.p_calculate_yield_or_price:= p_calculate_yield_or_price;
295 p_py_in.p_yield:= p_yield;
296 p_py_in.p_accrued_interest:= p_accrued_interest;
297 p_py_in.p_clean_price:= p_clean_price;
298 p_py_in.p_dirty_price:= p_dirty_price;
299 p_py_in.p_input_or_calculator:= p_input_or_calculator;
300 p_py_in.p_commence_date:= p_commence_date;
301 p_py_in.p_maturity_date:= p_maturity_date;
302 p_py_in.p_prev_coupon_date:= p_prev_coupon_date;
303 p_py_in.p_next_coupon_date:= p_next_coupon_date;
304 p_py_in.p_calc_type:= p_calc_type;
305 p_py_in.p_year_calc_type:= p_year_calc_type;
306 p_py_in.p_accrued_int_calc_basis:= p_accrued_int_calc_basis;
307 p_py_in.p_coupon_freq:= p_coupon_freq;
308 p_py_in.p_calc_rounding:= p_calc_rounding;
309 p_py_in.p_price_rounding:= p_price_rounding;
310 p_py_in.p_price_round_type:= p_price_round_type;
311 p_py_in.p_yield_rounding:= p_yield_rounding;
312 p_py_in.p_yield_round_type:= p_yield_round_type;
313 p_py_in.p_coupon_rate:= p_coupon_rate;
314 p_py_in.p_num_coupons_remain:= p_num_coupons_remain;
315 p_py_in.p_day_count_type:= p_day_count_type;
316 p_py_in.p_first_trans_flag:= p_first_trans_flag;
317 p_py_in.p_deal_subtype:= p_deal_subtype;
318
319
320 p_py_in.p_currency := p_currency; -- COMPOUND COUPON
321 p_py_in.p_face_value := p_face_value; -- COMPOUND COUPON
322 p_py_in.p_consideration := p_consideration; -- COMPOUND COUPON
323 p_py_in.p_rounding_type := p_rounding_type; -- COMPOUND COUPON
324
325 XTR_MM_COVERS.CALCULATE_BOND_PRICE_YIELD(p_py_in,p_py_out);
326
327 p_yield:= p_py_out.p_yield;
328 p_accrued_interest:= p_py_out.p_accrued_interest;
329 p_clean_price:= p_py_out.p_clean_price;
330 p_dirty_price:= p_py_out.p_dirty_price;
331
332 END CALCULATE_BOND_PRICE_YIELD;
333
334
335 /* Procedure to calculate the coupon amounts. */
336
337 PROCEDURE Calculate_Bond_Coupon_Amounts (
338 p_bond_issue_code IN VARCHAR2,
339 p_next_coupon_date IN DATE,
340 p_settlement_date IN DATE,
341 p_deal_number IN NUMBER,
342 p_deal_date IN DATE,
343 p_company_code IN VARCHAR2,
344 p_cparty_code IN VARCHAR2,
345 p_dealer_code IN VARCHAR2,
346 p_status_code IN VARCHAR2,
347 p_client_code IN VARCHAR2,
348 p_acceptor_code IN VARCHAR2,
349 p_maturity_account_number IN VARCHAR2,
350 p_maturity_amount IN NUMBER,
351 p_deal_subtype IN VARCHAR2,
352 p_product_type IN VARCHAR2,
353 p_portfolio_code IN VARCHAR2,
354 p_rounding_type IN VARCHAR2,
355 p_day_count_type IN VARCHAR2,
356 p_income_tax_ref IN VARCHAR2,
357 p_income_tax_rate IN OUT NOCOPY NUMBER,
358 p_income_tax_settled_ref IN OUT NOCOPY NUMBER) is
359
360 l_last_coupon_date date;
361 l_coupon_date date := p_next_coupon_date;
362 l_bond_start_date date;
363 l_bond_maturity_date date;
364 l_precision number;
365 l_ext_precision number;
366 l_min_acct_unit number;
367 l_coupon_amt number;
368 l_currency varchar2(15);
369 l_coupon_rate number;
370 l_frequency number;
371 l_year_calc_type varchar2(15);
372 l_year_basis number;
373 l_nbr_days_in_period number;
374 l_transaction_number number;
375 l_fnd_user number(15);
376 l_xtr_user varchar2(30);
377 errnum number;
378 errmsg varchar2(100);
379 l_errmsg varchar2(120);
380 l_calc_type varchar2(15);
381 -- Added for Interest Override
382 l_original_amount NUMBER;
383 l_first_trans_flag VARCHAR2(1);
384 --
385 l_income_tax_out NUMBER;
386 l_dummy_num NUMBER;
387 l_dummy_char VARCHAR2(20);
388 l_tax_settle_method VARCHAR2(15);
389 -- Bug 7023669 For Short First Coupon Fix Start
390 l_flg_first_flat_coupon VARCHAR2(1) := 'N';
391 l_orig_freq NUMBER;
392 l_first_orig_coupon_date DATE;
393 l_chk_commence DATE;
394 l_default_first_date DATE ;
395 -- Bug 7023669 For Short First Coupon Fix End
396
397
398
399 one_step_rec XTR_FPS2_P.ONE_STEP_REC_TYPE;
400 l_one_step_error VARCHAR2(40);
401 --
402 cursor BOND_DETAILS is
403 select currency,
404 coupon_rate,
405 no_of_coupons_per_year,
406 maturity_date,
407 year_calc_type,
408 commence_date,
409 calc_type
410 from xtr_bond_issues
411 where bond_issue_code = p_bond_issue_code;
412 --
413 -- Bug 7023669 For Short First Coupon Fix Start
414 cursor CHK_OVERRIDE is
415 select commence_date,no_of_coupons_per_year,
416 first_coupon_date
417 from xtr_bond_issues
418 where bond_issue_code = p_bond_issue_code;
419 -- Bug 7023669 For Short First Coupon Fix End
420
421
422 --
423 cursor GET_NEXT_COUPON_DATE is
424 select coupon_date,
425 due_date,rate --bug 2804548
426 from xtr_bond_coupon_dates
427 where bond_issue_code = p_bond_issue_code
428 and coupon_date > l_coupon_date
429 order by coupon_date;
430 --
431 cursor GET_LAST_COUPON_DATE is
432 select max(coupon_date)
433 from xtr_bond_coupon_dates
434 where bond_issue_code = p_bond_issue_code
435 and coupon_date < p_next_coupon_date;
436 --
437 cursor FIND_USER is
438 select dealer_code
439 from xtr_dealer_codes_v
440 where user_id = l_fnd_user;
441 --
442 cursor GET_SETTLE_METHOD(p_tax_code VARCHAR2) is
443 select TAX_SETTLE_METHOD
447 cursor TOTAL_FULL_COUPONS (p_issue_code VARCHAR2) is
444 from XTR_TAX_BROKERAGE_SETUP
445 where REFERENCE_CODE = p_tax_code;
446 --
448 select count(*)-1, min(coupon_date)
449 from xtr_bond_coupon_dates
450 where bond_issue_code = p_issue_code;
451
452 l_no_quasi_coupon NUMBER;
453 l_total_coupon_days NUMBER;
454 l_odd_coupon_start DATE;
455 l_odd_coupon_maturity DATE;
456 l_comp_coupon XTR_MM_COVERS.COMPOUND_CPN_REC_TYPE;
457
458 --bug 2804548
459 cursor get_cpn_info(p_cpn_date DATE,
460 p_bond_issue_code VARCHAR2) is
461 select due_date,rate
462 from xtr_bond_coupon_dates
463 where bond_issue_code=p_bond_issue_code
464 and coupon_date=p_cpn_date;
465
466 v_due_date DATE;
467
468 Begin
469
470 /* Setup user info. */
471 l_fnd_user := fnd_global.user_id;
472 Open FIND_USER;
473 Fetch FIND_USER into l_xtr_user;
474 If (FIND_USER%NOTFOUND) then
475 l_xtr_user := null;
476 End If;
477 Close FIND_USER;
478
479 /* Obtain pertinent info on bond. */
480
481 Open BOND_DETAILS;
482 Fetch BOND_DETAILS into l_currency, l_coupon_rate, l_frequency, l_bond_maturity_date,
483 l_year_calc_type, l_bond_start_date, l_calc_type;
484 If (BOND_DETAILS%NOTFOUND) then
485 Close BOND_DETAILS;
486 FND_MESSAGE.Set_Name('XTR','XTR_2171');
487 APP_EXCEPTION.Raise_Exception;
488 End If;
489 Close BOND_DETAILS;
490
491 -- Bug 7023669 For Short First Coupon Fix Start
492 open CHK_OVERRIDE ;
493 fetch CHK_OVERRIDE into l_chk_commence,l_orig_freq,l_first_orig_coupon_date;
494 If (CHK_OVERRIDE%NOTFOUND) then
495 Close CHK_OVERRIDE;
496 FND_MESSAGE.Set_Name('XTR','XTR_2171');
497 APP_EXCEPTION.Raise_Exception;
498 End If;
499 close CHK_OVERRIDE;
500
501 if to_char(l_chk_commence,'MM') = to_char(l_chk_commence-3,'MM') then
502 l_default_first_date := add_months(l_chk_commence-3, (12 / l_orig_freq)) +3;
503 else
504 l_default_first_date := add_months(l_chk_commence, (12 / l_orig_freq)) ;
505 end if;
506
507 -- Bug 7023669 For Short First Coupon Fix End
508
509 /* Obtain currency precision for bond. */
510
511 FND_CURRENCY.Get_Info (
512 l_currency,
513 l_precision,
514 l_ext_precision,
515 l_min_acct_unit);
516
517 /* Obtain last coupon date before the next coupon date.
518 In the case of an 'EX' status bond, this last coupon date is > the settlement date.
519 In the case of an 'CUM' status bond, this last coupon date is <= the settlement date.
520 We need to determine this date in order to compute the 'nbr of days' between the coupon
521 period, with consideration given for the days calc method. */
522
523 Open GET_LAST_COUPON_DATE;
524 Fetch GET_LAST_COUPON_DATE into l_last_coupon_date;
525 If (l_last_coupon_date is NULL) then
526
527 -- NOTE: Can't check for cursor %NOTFOUND since the 'max' will return a NULL row,
528 -- which is considered a 'found' case.
529
530 Close GET_LAST_COUPON_DATE;
531 l_last_coupon_date := nvl(l_bond_start_date,p_settlement_date);
532 -- Bug 7023669 For Short First Coupon Fix Start
533 l_flg_first_flat_coupon := 'Y' ;
534 -- Bug 7023669 For Short First Coupon Fix End
535 Else
536 Close GET_LAST_COUPON_DATE;
537 End If;
538
539 /* Open cursor to obtain next coupon date for process in loop. */
540
541 Open GET_NEXT_COUPON_DATE;
542
543 /* Compute coupon amounts and populate XTR_ROLLOVER_TRANSACTION and XTR_DEAL_DATE_AMOUNTS tables. */
544 /* NOTE: Start transaction number off at 2 since the 'commence' tasks' transaction number
545 would have been set to 1 when the bond deal was committed. */
546
547 l_transaction_number := 1;
548
549 --start bug 2804548
550 open get_cpn_info(l_coupon_date,p_bond_issue_code);
551 fetch get_cpn_info into v_due_date,l_coupon_rate;
552 close get_cpn_info;
553 --end bug 2804548
554
555 LOOP
556 l_transaction_number := l_transaction_number + 1;
557
558 -- Added for Interest Override
559 IF l_transaction_number = 2 and l_calc_type <> 'COMPOUND COUPON' THEN
560 l_first_trans_flag :='Y';
561 ELSE
562 l_first_trans_flag := NULL;
563 END IF;
564 --
565
566 If (l_calc_type in ('VARIABLE COUPON','FL IRREGULAR')) then
567
568 /* Need to compute # of days between the coupon period and determine # of days in the year
569 (l_year_basis) based on the year_calc_type. */
570
571 -- Bug 2358549.
572 -- Changed call to Calc_Days_Run_C from Calc_Days_Run in order
573 -- to properly handle the year calc type of 'Actual/Actual-Bond'
574 -- which was introduced in patchset C.
575
576 XTR_CALC_P.Calc_Days_Run_C (
577 l_last_coupon_date,
578 l_coupon_date,
579 l_year_calc_type,
580 l_frequency,
581 l_nbr_days_in_period,
582 l_year_basis,
583 NULL,
584 p_day_count_type, -- Added for Override feature
585 l_first_trans_flag -- Added for Override feature
586 );
587
588 -- Changed for Interest Override
589 -- l_coupon_amt := round((p_maturity_amount * (l_coupon_rate / 100) * (l_nbr_days_in_period / l_year_basis)), l_precision);
590 l_original_amount := xtr_fps2_p.interest_round((p_maturity_amount * (l_coupon_rate / 100) * (l_nbr_days_in_period / l_year_basis)), l_precision,p_rounding_type);
591 l_coupon_amt := l_original_amount;
592
593 Elsif (l_calc_type = 'COMPOUND COUPON') then
594
595 l_coupon_date := l_bond_maturity_date;
596 l_no_quasi_coupon := 0;
597 l_last_coupon_date := nvl(l_bond_start_date,p_settlement_date); -- l_bond_start_date should have a value
598
599 open TOTAL_FULL_COUPONS (p_bond_issue_code);
600 fetch TOTAL_FULL_COUPONS into l_no_quasi_coupon, l_odd_coupon_maturity;
601 close TOTAL_FULL_COUPONS;
602
603 l_odd_coupon_start := XTR_MM_COVERS.ODD_COUPON_DATE(l_bond_start_date,l_coupon_date,l_frequency,'S');
604
605 l_comp_coupon.p_bond_start_date := l_bond_start_date;
606 l_comp_coupon.p_odd_coupon_start := l_odd_coupon_start;
607 l_comp_coupon.p_odd_coupon_maturity := l_odd_coupon_maturity;
608 l_comp_coupon.p_full_coupon := l_no_quasi_coupon;
609 l_comp_coupon.p_coupon_rate := l_coupon_rate;
610 l_comp_coupon.p_maturity_amount := p_maturity_amount; -- Face Value
611 l_comp_coupon.p_precision := l_precision;
612 l_comp_coupon.p_rounding_type := p_rounding_type;
613 l_comp_coupon.p_year_calc_type := l_year_calc_type;
614 l_comp_coupon.p_frequency := l_frequency;
615 l_comp_coupon.p_day_count_type := p_day_count_type;
616 l_comp_coupon.p_amount_redemption_ind := 'A';
617
618 l_original_amount := XTR_MM_COVERS.CALC_COMPOUND_COUPON_AMT(l_comp_coupon);
619
620 l_coupon_amt := l_original_amount;
621
622 Else
623 /* Flat coupons do not need to take day count basis into consideration. */
624
625 /* We need to call this to calculate NO_OF_DAYS even though we are not using */
626 /* in coupon calculation.*/
627
628 -- Bug 2358549.
629 -- Changed call from Calc_Days_Run to Calc_Days_Run_C in order
630 -- to properly handle the year calc type of 'Actual/Actual-Bond'
631 -- which was introduced in patchset C.
632
633 XTR_CALC_P.Calc_Days_Run_C (
634 l_last_coupon_date,
635 l_coupon_date,
636 l_year_calc_type,
637 l_frequency,
638 l_nbr_days_in_period,
639 l_year_basis,
640 NULL,
641 p_day_count_type, -- Added for Override feature
642 l_first_trans_flag -- Added for Override feature
643 );
644 -- Changed for Interest Override
645 -- l_coupon_amt := round((p_maturity_amount * (l_coupon_rate / 100) / nvl(l_frequency,2)), l_precision);
646 l_original_amount := xtr_fps2_p.interest_round((p_maturity_amount * (l_coupon_rate / 100) / nvl(l_frequency,2)),
647 l_precision,p_rounding_type);
648 l_coupon_amt := l_original_amount;
649 -- Bug 7023669 For Short First Coupon Fix Start
650 if ( l_flg_first_flat_coupon = 'Y' and l_default_first_date <> l_first_orig_coupon_date )
651 then
652 l_flg_first_flat_coupon := 'N' ;
653 l_original_amount := xtr_fps2_p.interest_round((p_maturity_amount * (l_coupon_rate / 100) * (l_nbr_days_in_period / l_year_basis)), l_precision,p_rounding_type);
654 l_coupon_amt := l_original_amount;
655 end if;
656 -- Bug 7023669 For Short First Coupon Fix End
657
658 End If;
659
660 -- added by fhu 7/16/2002
661 -- calculate taxes
662 IF (p_income_tax_ref IS NOT NULL) THEN
663 XTR_FPS1_P.calc_tax_amount('BOND',
664 p_deal_date,
665 null,
666 p_income_tax_ref,
667 l_currency,
668 null,
669 0,
670 0,
671 null,
672 l_dummy_num,
673 l_coupon_amt,
674 p_income_tax_rate,
675 l_dummy_num,
676 l_income_tax_out,
677 l_dummy_num,
678 l_dummy_char);
679 END IF;
680
681 -- add by fhu 7/18/2002 for tax witholding project
682 -- check settle method for principal tax, generate one-step if needed
683 IF (p_income_tax_ref IS NOT NULL) THEN
684 OPEN get_settle_method(p_income_tax_ref);
685 FETCH get_settle_method INTO l_tax_settle_method;
686 IF (l_tax_settle_method = 'OSG') THEN
687 one_step_rec.p_source := 'TAX';
688 one_step_rec.p_schedule_code := p_income_tax_ref;
689 one_step_rec.p_currency := l_currency;
690 one_step_rec.p_amount := l_income_tax_out;--bug 2488604
691 one_step_rec.p_settlement_date := v_due_date;--bug 2488604
692 one_step_rec.p_settlement_account := p_maturity_account_number;
693 one_step_rec.p_company_code := p_company_code;
694 one_step_rec.p_cparty_code := p_cparty_code;
695 one_step_rec.p_error := l_one_step_error;
696 one_step_rec.p_settle_method := l_tax_settle_method;
697 one_step_rec.p_exp_number := p_income_tax_settled_ref;
698 XTR_FPS2_P.one_step_settlement(one_step_rec);
699 p_income_tax_settled_ref := one_step_rec.p_exp_number;
700 END IF;
701 CLOSE get_settle_method;
702 END IF;
703
704 Begin
705 Insert into XTR_ROLLOVER_TRANSACTIONS (
706 deal_number,
707 transaction_number,
708 deal_type,
709 start_date,
710 no_of_days,
711 maturity_date,
712 interest_rate,
713 interest,
714 orig_coupon_amount,
715 deal_subtype,
716 product_type,
717 portfolio_code,
718 company_code,
719 cparty_code,
720 client_code,
721 currency,
722 deal_date,
723 status_code,
724 created_by,
725 created_on,
726 settle_date,
727 original_amount, --Added for Interest Override
728 tax_code,
729 tax_rate,
730 tax_amount,
731 tax_settled_reference,
732 coupon_due_date --bug 2804548
733 )
734 Values (p_deal_number,
735 l_transaction_number,
736 'BOND',
737 l_last_coupon_date,
738 l_nbr_days_in_period,
739 l_coupon_date,
740 l_coupon_rate,
741 l_coupon_amt,
742 l_coupon_amt,
743 p_deal_subtype,
744 p_product_type,
745 p_portfolio_code,
746 p_company_code,
747 p_cparty_code,
748 p_client_code,
749 l_currency,
750 p_deal_date,
751 'CURRENT',
752 l_xtr_user,
753 sysdate,
754 v_due_date,
755 l_original_amount,-- Added for Interest Override
756 p_income_tax_ref,
757 p_income_tax_rate,
758 l_income_tax_out,
759 p_income_tax_settled_ref,
760 v_due_date
761 );
762
763 EXCEPTION
764 WHEN OTHERS then
765 errnum := SQLCODE;
766 errmsg := SUBSTR(SQLERRM,1,100);
767 l_errmsg := to_char(errnum) || ' - ' || errmsg;
768 FND_MESSAGE.Set_Name ('XTR', 'XTR_2172');
769 FND_MESSAGE.Set_Token ('TABLE', 'XTR_ROLLOVER_TRANSACTIONS');
770 FND_MESSAGE.Set_Token ('ERRCODE_TEXT', l_errmsg);
771 APP_EXCEPTION.Raise_Exception;
772 END;
773
774 BEGIN
775 -- if tax settle method = Netted Income Amount,
776 -- then net coupon amount with tax amount
777 -- also need to insert new tax row
778 IF (l_tax_settle_method IS NOT NULL) THEN
779 IF (l_tax_settle_method = 'NIA') THEN
780 -- for netting the coupon interest
781 l_coupon_amt := l_coupon_amt - l_income_tax_out;
782 -- insert new row with just tax
783 Insert into XTR_DEAL_DATE_AMOUNTS (
784 deal_type,
785 amount_type,
786 date_type,
787 deal_number,
788 transaction_number,
789 transaction_date,
790 currency,
791 amount,
792 amount_date,
793 transaction_rate,
794 cashflow_amount,
795 company_code,
796 deal_subtype,
797 product_type,
798 status_code,
799 client_code,
800 portfolio_code,
801 cparty_code,
802 settle,
803 dealer_code)
804 Values ('BOND',
805 'TAX',
806 'INCUR',
807 p_deal_number,
808 l_transaction_number,
809 l_coupon_date,
810 l_currency,
811 l_income_tax_out,
812 l_coupon_date,
813 0, -- ONC currently saves tax rate as 0
814 0, -- ONC currently saves cashflow amount as 0
815 p_company_code,
816 p_deal_subtype,
817 p_product_type,
818 'CURRENT',
819 p_client_code,
820 nvl(p_portfolio_code, 'NOTAPPL'),
821 p_cparty_code,
822 'N',
823 p_dealer_code);
824
825 END IF;
826 END IF;
827 Insert into XTR_DEAL_DATE_AMOUNTS (
828 deal_type,
829 amount_type,
830 date_type,
831 deal_number,
832 transaction_number,
833 transaction_date,
834 currency,
835 amount,
836 amount_date,
837 transaction_rate,
838 cashflow_amount,
839 company_code,
840 account_no,
841 status_code,
842 portfolio_code,
843 dealer_code,
844 client_code,
845 deal_subtype,
846 cparty_code,
847 settle,
848 product_type)
849 Values ('BOND',
850 'COUPON',
851 'COUPON',
852 p_deal_number,
853 l_transaction_number,
854 p_deal_date,
855 l_currency,
856 l_coupon_amt,
857 v_due_date,--bug 2804548
858 l_coupon_rate,
859 decode(p_deal_subtype,'BUY',1,-1) * l_coupon_amt,
860 p_company_code,
861 p_maturity_account_number,
862 p_status_code,
863 p_portfolio_code,
864 p_dealer_code,
865 p_client_code,
866 p_deal_subtype,
867 p_acceptor_code,
868 'N',
869 p_product_type);
870 EXCEPTION
871 WHEN OTHERS then
872 errnum := SQLCODE;
873 errmsg := SUBSTR(SQLERRM,1,100);
874 l_errmsg := to_char(errnum) || '. ' || errmsg;
875 FND_MESSAGE.Set_Name ('XTR', 'XTR_2172');
876 FND_MESSAGE.Set_Token ('TABLE', 'XTR_DEAL_DATE_AMOUNTS');
877 FND_MESSAGE.Set_Token ('ERRCODE_TEXT', l_errmsg);
878 APP_EXCEPTION.Raise_Exception;
879 END;
880
881 l_last_coupon_date := l_coupon_date;
882 Fetch GET_NEXT_COUPON_DATE into l_coupon_date,v_due_date,l_coupon_rate;
883
884 EXIT WHEN (GET_NEXT_COUPON_DATE%NOTFOUND) or (l_coupon_date > l_bond_maturity_date) or
885 (l_calc_type = 'COMPOUND COUPON');
886 END LOOP;
887
888 Close GET_NEXT_COUPON_DATE;
889 End Calculate_Bond_Coupon_Amounts;
890
891
892
893 PROCEDURE RECALC_DT_DETAILS (
894 l_deal_no IN NUMBER,
895 l_least_inserted IN VARCHAR2,
896 l_ref_date IN DATE,
897 l_trans_num IN NUMBER,
898 l_last_row IN VARCHAR2,
899 g_chk_bal IN VARCHAR2,
900 g_expected_balance_bf IN OUT NOCOPY NUMBER,
901 g_balance_out_bf IN OUT NOCOPY NUMBER,
902 g_accum_interest_bf IN OUT NOCOPY NUMBER,
903 g_principal_adjust IN OUT NOCOPY NUMBER,
904 c_principal_action IN VARCHAR2,
905 c_principal_amount_type IN VARCHAR2,
906 c_principal_adjust IN NUMBER,
907 c_writoff_int IN NUMBER,
908 c_increase_effective_from IN DATE,
909 l_rounding_type IN VARCHAR2, --Add Interest Override
910 l_day_count_type IN VARCHAR2) IS --Add Interest Override
911
912 l_deal_date DATE;
913 l_company VARCHAR2(7);
914 l_subtype VARCHAR2(7);
915 l_product VARCHAR2(10);
916 l_portfolio VARCHAR2(7);
917 l_ccy VARCHAR2(15);
918 l_maturity DATE;
919 l_settle_acct VARCHAR2(20);
920 l_cparty VARCHAR2(7);
921 l_client VARCHAR2(7);
922 l_dealer VARCHAR2(10);
923 l_cparty_acct VARCHAR2(20);
924 l_year_calc_type VARCHAR2(15);
925 l_limit_code VARCHAR2(7);
926 l_internal_ticket_no VARCHAR2(15);
927 l_face_value_amount NUMBER;
928 l_cparty_ref VARCHAR2(7);
929
930 chk_off VARCHAR2(1);
931 l_comments VARCHAR2(30);
932 l_nill_date DATE;
933 l_compound VARCHAR2(10);
934 l_prv_row_exists VARCHAR2(1);
935 l_start_date DATE;
936 l_prin_decr NUMBER;
937 new_exp_bal NUMBER;
938 new_accum_int NUMBER;
939 new_balbf NUMBER;
940 new_start_date DATE;
941 year_basis NUMBER;
942 no_of_days NUMBER;
943 rounding_fac NUMBER;
944 l_hce_rate NUMBER;
945 hce_interest NUMBER;
946 hce_settled NUMBER;
947 hce_accum_int_bf NUMBER;
948 hce_decr NUMBER;
949 hce_accum_int NUMBER;
950 hce_balbf NUMBER;
951 hce_balos NUMBER;
952 hce_princ NUMBER;
953 hce_due NUMBER;
954 l_exp_int NUMBER;
955 l_cum_int NUMBER;
956 l_prin_adj NUMBER;
957 l_mark VARCHAR2(1);
958 cnt NUMBER;
959
960 -- 3958736
961 l_dual_authorisation_by xtr_deals.dual_authorisation_by%type ;
962 l_dual_authorisation_on xtr_deals.dual_authorisation_on%type ;
963
964 l_in_btw_date_adj_flag VARCHAR2(1); -- Bug 7375762
965
966 cursor THIS_DEAL is
967 select DEAL_DATE, COMPANY_CODE, DEAL_SUBTYPE,
968 PRODUCT_TYPE, PORTFOLIO_CODE, CURRENCY,
969 MATURITY_DATE, SETTLE_ACCOUNT_NO, CPARTY_CODE,
970 CLIENT_CODE, DEALER_CODE, YEAR_CALC_TYPE,
971 LIMIT_CODE, INTERNAL_TICKET_NO,
972 FACE_VALUE_AMOUNT, cparty_ref,
973 start_date, --Add Interest Override
974 DUAL_AUTHORISATION_BY, -- bug 3958736
975 DUAL_AUTHORISATION_ON
976 from XTR_DEALS
977 where DEAL_NO = l_deal_no
978 and DEAL_TYPE = 'RTMM';
979
980 cursor CHK_REF is
981 select ACCOUNT_NUMBER
982 from XTR_BANK_ACCOUNTS_V
983 where PARTY_CODE = l_cparty
984 and BANK_SHORT_CODE = l_cparty_ref
985 and CURRENCY = l_ccy;
986
987 cursor RND_YR is
988 select ROUNDING_FACTOR,YEAR_BASIS,nvl(HCE_RATE,1) HCE_RATE
989 from XTR_MASTER_CURRENCIES_V
990 where CURRENCY = l_ccy;
991
992 cursor START_ROW is
993 select max(START_DATE)
994 from XTR_ROLLOVER_TRANSACTIONS
995 where DEAL_NUMBER = l_deal_no
996 and START_DATE <= l_ref_date --- <
997 and STATUS_CODE = 'CURRENT'
998 and TRANSACTION_NUMBER <>l_trans_num;
999
1000 cursor LAST_ROW is
1001 select rowid
1002 from XTR_ROLLOVER_TRANSACTIONS
1003 where DEAL_NUMBER = l_deal_no
1004 and START_DATE >= l_start_date
1005 and (MATURITY_DATE > l_start_date or l_last_row='Y') --- add
1006 and STATUS_CODE = 'CURRENT'
1007 -- and ((nvl(g_chk_bal,'N')='Y' and TRANSACTION_NUMBER=l_trans_num)
1008 -- or nvl(g_chk_bal,'N')='N')
1009 order by START_DATE desc,TRANSACTION_NUMBER desc;
1010
1011 last_pmt LAST_ROW%ROWTYPE;
1012
1013 cursor DT_ROW is
1014 select START_DATE,MATURITY_DATE,NO_OF_DAYS,BALANCE_OUT_BF,
1015 BALANCE_OUT,PRINCIPAL_ADJUST,INTEREST_RATE,INTEREST,
1016 INTEREST_SETTLED,PRINCIPAL_ACTION,TRANSACTION_NUMBER,
1017 SETTLE_DATE,ACCUM_INTEREST_BF,PI_AMOUNT_DUE,PI_AMOUNT_RECEIVED,
1018 ACCUM_INTEREST,ROWID,ADJUSTED_BALANCE,COMMENTS,
1019 EXPECTED_BALANCE_BF,EXPECTED_BALANCE_OUT,PRINCIPAL_AMOUNT_TYPE,
1020 ENDORSER_CODE,RATE_FIXING_DATE
1021 from XTR_ROLLOVER_TRANSACTIONS
1022 where DEAL_NUMBER = l_deal_no
1023 and START_DATE >= l_start_date
1024 and (MATURITY_DATE > l_start_date or l_last_row = 'Y')
1025 and STATUS_CODE = 'CURRENT'
1026 -- and ((nvl(g_chk_bal,'N') = 'Y' and TRANSACTION_NUMBER = l_trans_num)
1027 -- or nvl(g_chk_bal,'N') = 'N')
1028 order by START_DATE asc,TRANSACTION_NUMBER asc
1029 for UPDATE OF START_DATE; --lock all rows until commit
1030
1031 pmt DT_ROW%ROWTYPE;
1032
1033 cursor COMP is
1034 select b.INTEREST_ACTION
1035 from XTR_DEALS_V a,
1036 XTR_PAYMENT_SCHEDULE_V b
1037 where a.DEAL_NO = l_deal_no
1038 and b.PAYMENT_SCHEDULE_CODE = a.PAYMENT_SCHEDULE_CODE;
1039
1040 l_date_exits varchar2(1);
1041
1042 cursor chk_date_exits is
1043 select NULL
1044 from XTR_ROLLOVER_TRANSACTIONS
1045 where DEAL_NUMBER = l_deal_no
1046 and (START_DATE = l_ref_date or MATURITY_DATE = l_ref_date)
1047 and transaction_number <>l_trans_num
1048 order by START_DATE desc,TRANSACTION_NUMBER desc ;
1049
1050 -- Add Interest Override
1051 l_first_trans_flag VARCHAR2(1);
1052 l_max_trans_no NUMBER;
1053 l_deal_start_date DATE;
1054 -- End of Change
1055
1056 BEGIN
1057
1058 open THIS_DEAL;
1059 fetch THIS_DEAL INTO l_deal_date, l_company, l_subtype,
1060 l_product, l_portfolio, l_ccy,
1061 l_maturity, l_settle_acct,l_cparty,
1062 l_client, l_dealer, l_year_calc_type,
1063 l_limit_code, l_internal_ticket_no,
1064 l_face_value_amount, l_cparty_ref,
1065 l_deal_start_date,
1066 l_dual_authorisation_by,
1067 l_dual_authorisation_on;
1068
1069 close THIS_DEAL;
1070
1071 if l_cparty_ref is NOT NULL then
1072 open CHK_REF;
1073 fetch CHK_REF into l_cparty_acct;
1074 if CHK_REF%NOTFOUND then
1075 l_cparty_acct := NULL;
1076 end if;
1077 close CHK_REF;
1078 end if;
1079
1080 open RND_YR;
1081 fetch RND_YR INTO rounding_fac,year_basis,l_hce_rate;
1082 close RND_YR;
1083
1084 open COMP;
1085 fetch COMP INTO l_compound;
1086 close COMP;
1087
1088 l_hce_rate := nvl(l_hce_rate,1);
1089 rounding_fac := nvl(rounding_fac,2);
1090 l_compound := nvl(l_compound,'N');
1091 l_comments := NULL;
1092 l_start_date := NULL;
1093 l_nill_date := NULL;
1094
1095 l_in_btw_date_adj_flag := 'N'; -- Bug 7375762
1096
1097 open START_ROW;
1098 fetch START_ROW INTO l_start_date;
1099 close START_ROW;
1100
1101 -- fnd_message.debug('l_start_date = ' || l_start_date);
1102 -- fnd_message.debug('l_ref_date = ' || l_ref_date);
1103 -- fnd_message.debug(' l_trans_num = ' || l_trans_num);
1104
1105 if l_start_date is NULL then
1106 l_start_date := l_ref_date;
1107 l_prv_row_exists := 'N';
1108 else
1109 l_prv_row_exists := 'Y';
1110 end if;
1111
1112
1113 open LAST_ROW;
1114 fetch LAST_ROW INTO last_pmt;
1115 close LAST_ROW;
1116 --
1117 --
1118 --
1119 open DT_ROW;
1120 l_nill_date := NULL;
1121 fetch DT_ROW INTO pmt;
1122
1123 WHILE DT_ROW%FOUND LOOP
1124 --Add Interest Override
1125 IF pmt.start_date = l_deal_start_date
1126 AND nvl(l_day_count_type,'F') = 'B' THEN
1127
1128 SELECT MAX(transaction_number)
1129 INTO l_max_trans_no
1130 FROM xtr_rollover_transactions_v
1131 WHERE deal_number = l_deal_no
1132 AND start_date = pmt.start_date;
1133
1134 IF pmt.transaction_number = Nvl(l_max_trans_no,1) THEN
1135 l_first_trans_flag := 'Y';
1136 ELSE
1137 l_first_trans_flag := 'N';
1138 END IF;
1139 ELSE
1140 l_first_trans_flag := 'N';
1141 END IF;
1142 --End of Addition
1143
1144 -- Reset balance bf and start date from previous row information except
1145 -- for the first row
1146 l_date_exits :=NULL;
1147 l_mark :='N';
1148
1149 if nvl(pmt.PRINCIPAL_ACTION,'@#@') = 'DECRSE' then
1150 l_prin_adj := (-1) * nvl(pmt.PRINCIPAL_ADJUST,0);
1151 else
1152 l_prin_adj := nvl(pmt.PRINCIPAL_ADJUST,0);
1153 end if;
1154 --
1155 -- Initialize
1156 if DT_ROW%ROWCOUNT <> 1 then
1157 pmt.EXPECTED_BALANCE_BF := new_exp_bal;
1158 pmt.ACCUM_INTEREST_BF := new_accum_int;
1159 pmt.BALANCE_OUT_BF := new_balbf;
1160 pmt.START_DATE := new_start_date;
1161 pmt.COMMENTS := l_comments;
1162 elsif DT_ROW%ROWCOUNT = 1 then
1163 if l_prv_row_exists = 'Y' and (nvl(l_least_inserted,'N') = 'Y' or l_trans_num = -1)
1164 and nvl(g_chk_bal,'N') = 'N'
1165 or nvl(g_chk_bal,'N') = 'Y' then
1166 -- This is the row before the EARLIEST ROW CHANGED
1167 -- ie reset its maturity date to the Start date of the row changed.
1168 -- This is because the earliest row may have been inserted.
1169
1170 if nvl(c_principal_action,'@#@') = 'DECRSE' then
1171 if nvl(c_principal_amount_type,'PRINFLW') = 'PRINFLW' then
1172 pmt.PI_AMOUNT_DUE := nvl(c_principal_adjust,0);
1173 l_date_exits := 'W';
1174 l_mark := 'Y';
1175 else
1176 pmt.PI_AMOUNT_DUE := 0;
1177 end if;
1178 elsif l_trans_num <> -1 then
1179 pmt.PI_AMOUNT_DUE :=0;
1180 end if;
1181 --
1182 if nvl(g_chk_bal,'N') = 'N' then
1183 pmt.PI_AMOUNT_RECEIVED := 0;
1184 l_in_btw_date_adj_flag := 'Y'; -- Bug 7375762
1185 end if;
1186 --
1187 pmt.MATURITY_DATE := l_ref_date;
1188
1189 XTR_CALC_P.CALC_DAYS_RUN(pmt.START_DATE,
1190 pmt.MATURITY_DATE,
1191 l_year_calc_type,
1192 no_of_days,
1193 year_basis,
1194 NULL,
1195 l_day_count_type, --Add Interest Override
1196 l_first_trans_flag); --Add Interest Override
1197
1198 --Add Interest Override
1199 l_cum_int := XTR_FPS2_P.interest_round(pmt.EXPECTED_BALANCE_BF
1200 + l_prin_adj
1201 * pmt.INTEREST_RATE / 100
1202 * (no_of_days)
1203 / year_basis,rounding_fac,l_rounding_type);
1204 --Original
1205 -- l_cum_int := round(pmt.EXPECTED_BALANCE_BF
1206 -- + l_prin_adj
1207 -- * pmt.INTEREST_RATE / 100
1208 -- * (no_of_days)
1209 -- / year_basis,rounding_fac);
1210 --End of Change
1211 else
1212 l_cum_int := 0;
1213 if nvl(c_principal_action,'@#@') = 'DECRSE'
1214 and nvl(c_principal_amount_type,'PRINFLW') = 'PRINFLW'
1215 and nvl(g_chk_bal,'N') = 'Y' then
1216 l_date_exits := 'W';
1217 end if;
1218 end if;
1219 end if;
1220 -- End Initialize
1221
1222 -- Recalc interest amount
1223 l_prin_decr := 0;
1224 pmt.ADJUSTED_BALANCE := nvl(pmt.BALANCE_OUT_BF,0) + nvl(l_prin_adj,0);
1225 -- added
1226 XTR_CALC_P.CALC_DAYS_RUN(pmt.START_DATE,
1227 pmt.MATURITY_DATE,
1228 l_year_calc_type,
1229 pmt.NO_OF_DAYS,
1230 year_basis,
1231 NULL,
1232 l_day_count_type, --Add Interest Override
1233 l_first_trans_flag);--Add Interest Override
1234
1235 if nvl(pmt.ADJUSTED_BALANCE,0) > 0 then
1236
1237 --Add Interest Override
1238 pmt.INTEREST := XTR_FPS2_P.interest_round(pmt.ADJUSTED_BALANCE * pmt.INTEREST_RATE / 100 *
1239 pmt.NO_OF_DAYS / year_basis,rounding_fac,l_rounding_type);
1240 --Original
1241 -- pmt.INTEREST := round(pmt.ADJUSTED_BALANCE * pmt.INTEREST_RATE / 100 *
1242 -- pmt.NO_OF_DAYS / year_basis,rounding_fac);
1243 --End of Change
1244 else
1245 pmt.INTEREST := 0;
1246 end if;
1247 --
1248 pmt.ACCUM_INTEREST := nvl(pmt.ACCUM_INTEREST_BF,0) + nvl(pmt.INTEREST,0);
1249 -- Added
1250 if pmt.NO_OF_DAYS = 0 and l_mark = 'N' then
1251 pmt.PI_AMOUNT_DUE := 0;
1252 pmt.PI_AMOUNT_RECEIVED := 0;
1253 end if;
1254 --
1255 --
1256 if pmt.SETTLE_DATE is NOT NULL then
1257 -- added if 'W' not split for decrese on different day.
1258 if nvl(pmt.ENDORSER_CODE,'N') = 'W' then
1259 l_prin_decr := pmt.PI_AMOUNT_RECEIVED;
1260 pmt.INTEREST_SETTLED := 0;
1261 else
1262 if pmt.PI_AMOUNT_RECEIVED >= pmt.ACCUM_INTEREST then
1263 l_prin_decr := pmt.PI_AMOUNT_RECEIVED - nvl(pmt.ACCUM_INTEREST,0);
1264 pmt.INTEREST_SETTLED := nvl(pmt.ACCUM_INTEREST,0);
1265 pmt.ACCUM_INTEREST := 0;
1266 else
1267 l_prin_decr := 0;
1268 pmt.INTEREST_SETTLED := abs(nvl(pmt.PI_AMOUNT_RECEIVED,0) - nvl(pmt.ACCUM_INTEREST,0));
1269 pmt.ACCUM_INTEREST := pmt.ACCUM_INTEREST - nvl(pmt.PI_AMOUNT_RECEIVED,0);
1270 end if;
1271 end if;
1272 else
1273 NULL;
1274 end if;
1275 --
1276 if l_compound in('C','COMPOUND') then
1277 pmt.BALANCE_OUT := nvl(pmt.ADJUSTED_BALANCE,0) - nvl(l_prin_decr,0) +
1278 nvl(pmt.ACCUM_INTEREST,0);
1279 pmt.ACCUM_INTEREST := 0;
1280 else
1281 pmt.BALANCE_OUT := nvl(pmt.ADJUSTED_BALANCE,0) - nvl(l_prin_decr,0);
1282 end if;
1283
1284 pmt.EXPECTED_BALANCE_OUT := nvl(pmt.EXPECTED_BALANCE_BF,0) +
1285 nvl(l_prin_adj,0);
1286 if nvl(pmt.EXPECTED_BALANCE_OUT,0) > 0 then -- added
1287
1288 --Add Interest Override
1289 l_exp_int := XTR_FPS2_P.interest_round(nvl(pmt.EXPECTED_BALANCE_OUT,0) * nvl(pmt.INTEREST_RATE,0)
1290 / 100 * pmt.NO_OF_DAYS / year_basis,rounding_fac,l_rounding_type);
1291 --Original
1292 -- l_exp_int := round(nvl(pmt.EXPECTED_BALANCE_OUT,0) *
1293 -- nvl(pmt.INTEREST_RATE,0)
1294 -- / 100 * pmt.NO_OF_DAYS / year_basis,rounding_fac);
1295 --End of Change
1296 else
1297 l_exp_int := 0;
1298 end if;
1299
1300 pmt.EXPECTED_BALANCE_OUT := nvl(pmt.EXPECTED_BALANCE_OUT,0)
1301 - nvl(pmt.PI_AMOUNT_DUE,0) + nvl(l_exp_int,0);
1302
1303 if nvl(pmt.EXPECTED_BALANCE_OUT, 0) < 0 then
1304 pmt.PI_AMOUNT_DUE := pmt.PI_AMOUNT_DUE + pmt.EXPECTED_BALANCE_OUT;
1305 pmt.EXPECTED_BALANCE_OUT := 0;
1306 end if;
1307
1308 -- Bug 7375762 start
1309
1310 if nvl(l_in_btw_date_adj_flag,'N') = 'Y' then
1311 pmt.BALANCE_OUT := pmt.BALANCE_OUT + pmt.ACCUM_INTEREST;
1312 pmt.ACCUM_INTEREST := 0;
1313 l_in_btw_date_adj_flag := 'N';
1314 end if;
1315
1316 -- Bug 7375762 end
1317
1318 --
1319 --add
1320 if pmt.MATURITY_DATE = l_maturity and pmt.ROWID=last_pmt.ROWID then
1321 -- Last transaction therefore make the repayment = Balance Out +
1322 -- Interest Due.
1323
1324 pmt.PI_AMOUNT_DUE := nvl(pmt.PI_AMOUNT_DUE,0) + nvl(pmt.EXPECTED_BALANCE_OUT,0);
1325 pmt.EXPECTED_BALANCE_OUT := 0;
1326 end if;
1327
1328 if pmt.BALANCE_OUT_BF < 0 then
1329 pmt.PI_AMOUNT_DUE := 0;
1330 end if;
1331 --
1332 if g_chk_bal = 'Y' and pmt.ROWID = last_pmt.ROWID then
1333 -- Last transaction therefore make the repayment = Balance Out + Interest Due.
1334 pmt.ACCUM_INTEREST := 0;
1335 pmt.EXPECTED_BALANCE_OUT := 0;
1336 end if;
1337 --
1338 -- Store balance carried fwd and start date for the next row
1339 new_exp_bal := nvl(pmt.EXPECTED_BALANCE_OUT,0);
1340 new_accum_int := nvl(pmt.ACCUM_INTEREST,0);
1341 new_balbf := nvl(pmt.BALANCE_OUT,0);
1342 new_start_date := pmt.MATURITY_DATE;
1343 --
1344 if nvl(pmt.PI_AMOUNT_RECEIVED,0) <> 0 then
1345 l_comments := 'RECD SETTLEMENT ON PREV ROLL';
1346 else
1347 l_comments := NULL;
1348 end if;
1349 --
1350 l_prin_decr := nvl(l_prin_decr,0);
1351 pmt.INTEREST_SETTLED := nvl(pmt.INTEREST_SETTLED,0);
1352 -- Calc HCE amounts
1353 hce_decr := round(nvl(l_prin_decr,0) / l_hce_rate,rounding_fac);
1354 hce_balbf := round(nvl(pmt.BALANCE_OUT_BF,0) / l_hce_rate,rounding_fac);
1355 hce_interest := round(nvl(pmt.INTEREST,0) / l_hce_rate,rounding_fac);
1356 hce_settled := round(nvl(pmt.INTEREST_SETTLED,0) / l_hce_rate,rounding_fac);
1357 hce_accum_int_bf := round(nvl(pmt.ACCUM_INTEREST_BF,0) / l_hce_rate,rounding_fac);
1358 hce_princ := nvl(round(nvl(pmt.PRINCIPAL_ADJUST,0) / l_hce_rate,rounding_fac),0);
1359 hce_balos := round(nvl(pmt.BALANCE_OUT,0) / l_hce_rate,rounding_fac);
1360 hce_accum_int := round(nvl(pmt.ACCUM_INTEREST,0) / l_hce_rate,rounding_fac);
1361 hce_due := round(nvl(pmt.PI_AMOUNT_DUE,0) / l_hce_rate,rounding_fac);
1362 --
1363 update XTR_ROLLOVER_TRANSACTIONS
1364 set START_DATE = pmt.START_DATE,
1365 RATE_FIXING_DATE = pmt.START_DATE,
1366 BALANCE_OUT_BF = pmt.BALANCE_OUT_BF,
1367 BALANCE_OUT_BF_HCE = hce_balbf,
1368 ACCUM_INTEREST_BF = pmt.ACCUM_INTEREST_BF,
1369 ACCUM_INTEREST_BF_HCE = hce_accum_int_bf,
1370 PI_AMOUNT_DUE = pmt.PI_AMOUNT_DUE,
1371 PI_AMOUNT_RECEIVED = pmt.PI_AMOUNT_RECEIVED,
1372 ADJUSTED_BALANCE = pmt.ADJUSTED_BALANCE,
1373 BALANCE_OUT = pmt.BALANCE_OUT,
1374 BALANCE_OUT_HCE = hce_balos,
1375 PRINCIPAL_ADJUST_HCE = hce_princ,
1376 PRINCIPAL_ADJUST = pmt.PRINCIPAL_ADJUST,
1377 INTEREST = pmt.INTEREST,
1378 INTEREST_SETTLED = pmt.INTEREST_SETTLED,
1379 INTEREST_HCE = hce_interest,
1380 ACCUM_INTEREST = pmt.ACCUM_INTEREST,
1381 ACCUM_INTEREST_HCE = hce_accum_int,
1382 SETTLE_DATE = pmt.SETTLE_DATE,
1383 NO_OF_DAYS = pmt.NO_OF_DAYS,
1384 MATURITY_DATE = pmt.MATURITY_DATE,
1385 EXPECTED_BALANCE_BF = nvl(pmt.EXPECTED_BALANCE_BF,0),
1386 EXPECTED_BALANCE_OUT = pmt.EXPECTED_BALANCE_OUT,
1387 ENDORSER_CODE = l_date_exits
1388 where ROWID = pmt.ROWID;
1389
1390 --DDA
1391 delete from XTR_DEAL_DATE_AMOUNTS_V
1392 where DEAL_NUMBER = l_deal_no
1393 and TRANSACTION_NUMBER = pmt.TRANSACTION_NUMBER;
1394
1395 --
1396 -- Insert rows for Principal Adjustments
1397 --
1398 if nvl(pmt.PRINCIPAL_ADJUST,0) <> 0 then
1399 -- Principal Increase has ocurred
1400 if pmt.PRINCIPAL_ACTION = 'INCRSE' then
1401 -- Principal Increase has ocurred
1402 insert into XTR_DEAL_DATE_AMOUNTS_V
1403 (deal_type,amount_type,date_type,
1404 deal_number,transaction_number,transaction_date,currency,
1405 amount,hce_amount,amount_date,transaction_rate,
1406 cashflow_amount,company_code,account_no,action_code,
1407 cparty_account_no,deal_subtype,product_type,
1408 portfolio_code,status_code,cparty_code,dealer_code,
1409 settle,client_code,serial_reference,
1410 dual_authorisation_by,
1411 dual_authorisation_on)
1412 values ('RTMM',nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW'),
1413 'COMENCE',l_deal_no,pmt.TRANSACTION_NUMBER,
1414 l_deal_date,l_ccy ,pmt.PRINCIPAL_ADJUST,hce_princ,
1415 decode(nvl(g_chk_bal,'N'),'N',pmt.START_DATE,pmt.MATURITY_DATE),pmt.INTEREST_RATE,
1416 decode(nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW'),'PRINFLW',decode(l_subtype
1417 ,'FUND',pmt.PRINCIPAL_ADJUST
1418 ,'INVEST',(-1) * pmt.PRINCIPAL_ADJUST),0),
1419 l_company,l_settle_acct ,'INCRSE',
1420 l_cparty_acct ,l_subtype,l_product,
1421 l_portfolio ,'CURRENT',l_cparty,
1422 l_dealer,'N',l_client ,substr(l_internal_ticket_no,1,12),
1423 l_dual_authorisation_by,
1424 l_dual_authorisation_on);
1425 end if;
1426 -- Principal Reduction Row
1427 if nvl(pmt.PRINCIPAL_ACTION,'@#@') = 'DECRSE' then
1428 if nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW') <> 'PRINFLW' then
1429 insert into XTR_DEAL_DATE_AMOUNTS_V
1430 (deal_type,amount_type,date_type,
1431 deal_number,transaction_number,transaction_date,currency,
1432 amount,hce_amount,amount_date,transaction_rate,
1433 cashflow_amount,company_code,account_no,action_code,
1434 cparty_account_no,deal_subtype,product_type,
1435 portfolio_code,status_code,cparty_code,dealer_code,
1436 settle,client_code,serial_reference,
1437 dual_authorisation_by,
1438 dual_authorisation_on)
1439 values ('RTMM',nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW'),
1440 'SETTLE',
1441 -- decode(nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW'),'PRINFLW',
1442 -- decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1443 -- ,0,'FORCAST','SETTLE'),'SETTLE'),
1444
1445 l_deal_no,pmt.TRANSACTION_NUMBER,
1446 l_deal_date,l_ccy ,nvl(pmt.PRINCIPAL_ADJUST,0),
1447 nvl(nvl(hce_princ,pmt.PRINCIPAL_ADJUST),0),
1448 decode(nvl(g_chk_bal,'N'),'N',pmt.START_DATE,pmt.MATURITY_DATE),pmt.INTEREST_RATE,
1449 decode(nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW'),'PRINFLW',decode(l_subtype
1450 ,'FUND',(-1),1) * nvl(pmt.PRINCIPAL_ADJUST,0),0),
1451 l_company,l_settle_acct ,'DECRSE',
1452 l_cparty_acct ,l_subtype,l_product,
1453 l_portfolio ,'CURRENT',l_cparty,
1454 l_dealer,'N',l_client ,substr(l_internal_ticket_no,1,12),
1455 l_dual_authorisation_by,
1456 l_dual_authorisation_on);
1457 end if;
1458 end if;
1459 end if; -- End of Insert rows for Principal Adjustments
1460
1461 --
1462 -- Reduction in Principal from a repayment (insert forcast row with 0's if not received)
1463 --
1464 insert into XTR_DEAL_DATE_AMOUNTS_V
1465 (deal_type,amount_type,date_type,
1466 deal_number,transaction_number,transaction_date,currency,
1467 amount,hce_amount,amount_date,transaction_rate,
1468 cashflow_amount,company_code,account_no,action_code,
1469 cparty_account_no,deal_subtype,product_type,
1470 portfolio_code,status_code,cparty_code,dealer_code,
1471 settle,client_code,serial_reference,
1472 dual_authorisation_by,
1473 dual_authorisation_on)
1474 values ('RTMM','PRINFLW',decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1475 ,0,'FORCAST','SETTLE'),
1476 /*
1477 decode(nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW'),'PRINFLW',
1478 decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1479 ,0,'FORCAST','SETTLE'),'SETTLE'),
1480 decode(nvl(pmt.PRINCIPAL_ACTION,'@#@'),'DECRSE',
1481 decode(nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW'),'PRINFLW',
1482 decode(nvl(pmt.PI_AMOUNT_RECEIVED,NULL),NULL,'FORCAST','SETTLE'),'SETTLE'),
1483 decode(pmt.PI_AMOUNT_RECEIVED,NULL,'FORCAST','SETTLE')),
1484 */
1485 l_deal_no,pmt.TRANSACTION_NUMBER,
1486 l_deal_date,l_ccy ,decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1487 ,0,0,l_prin_decr),
1488 decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1489 ,0,0,round(l_prin_decr/l_hce_rate,rounding_fac)),
1490 nvl(pmt.SETTLE_DATE,pmt.MATURITY_DATE),pmt.INTEREST_RATE,
1491 decode(l_subtype,'FUND',(-1),1) *
1492 decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1493 ,0,0,l_prin_decr),
1494 l_company,l_settle_acct ,'DECRSE',
1495 l_cparty_acct ,l_subtype,l_product,
1496 l_portfolio ,'CURRENT',l_cparty,
1497 l_dealer,'N',l_client ,substr(l_internal_ticket_no,1,12),
1498 l_dual_authorisation_by, -- bug 3958736
1499 l_dual_authorisation_on);
1500
1501 if nvl(pmt.BALANCE_OUT,0) <> 0 and pmt.INTEREST_RATE <> 0 then
1502 --- Rateset Date
1503 insert into XTR_DEAL_DATE_AMOUNTS_V
1504 (deal_type,amount_type,date_type,
1505 deal_number,transaction_number,transaction_date,currency,
1506 amount,hce_amount,amount_date,transaction_rate,
1507 cashflow_amount,company_code,account_no,action_code,
1508 cparty_account_no,deal_subtype,product_type,
1509 portfolio_code,status_code,cparty_code,dealer_code,
1510 settle,client_code,
1511 dual_authorisation_by, -- bug 3958736
1512 dual_authorisation_on)
1513 values ('RTMM','FACEVAL','RATESET',l_deal_no,pmt.TRANSACTION_NUMBER,
1514 l_deal_date,l_ccy ,nvl(pmt.BALANCE_OUT,0),hce_balos,
1515 pmt.START_DATE,pmt.INTEREST_RATE,0,l_company,l_settle_acct ,NULL,
1516 l_cparty_acct ,l_subtype,l_product,
1517 l_portfolio ,'CURRENT',l_cparty,l_dealer,'N',l_client,
1518 l_dual_authorisation_by, -- bug 3958736
1519 l_dual_authorisation_on);
1520 end if;
1521
1522 -- Interest Row
1523
1524 insert into XTR_DEAL_DATE_AMOUNTS_V
1525 (deal_type,amount_type,date_type,
1526 deal_number,transaction_number,transaction_date,currency,
1527 amount,hce_amount,amount_date,transaction_rate,
1528 cashflow_amount,company_code,account_no,action_code,
1529 cparty_account_no,deal_subtype,product_type,
1530 portfolio_code,status_code,cparty_code,dealer_code,
1531 settle,client_code,serial_reference,
1532 dual_authorisation_by, -- bug 3958736
1533 dual_authorisation_on)
1534 values ('RTMM','INTSET',decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1535 ,0,'FORCAST','SETTLE'),
1536 l_deal_no,pmt.TRANSACTION_NUMBER,
1537 l_deal_date,l_ccy ,
1538 decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1539 ,0,pmt.PI_AMOUNT_DUE,pmt.INTEREST_SETTLED),
1540 decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1541 ,0,hce_due,hce_settled),
1542 nvl(pmt.SETTLE_DATE,pmt.MATURITY_DATE),pmt.INTEREST_RATE,
1543 decode(l_subtype
1544 ,'FUND',(-1),1) * decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1545 ,0,nvl(pmt.PI_AMOUNT_DUE,0)
1546 ,nvl(pmt.INTEREST_SETTLED,0)),
1547 l_company,l_settle_acct ,NULL,
1548 l_cparty_acct ,l_subtype,l_product,
1549 l_portfolio ,'CURRENT',l_cparty,
1550 l_dealer,'N',l_client ,substr(l_internal_ticket_no,1,12),
1551 l_dual_authorisation_by, -- bug 3958736
1552 l_dual_authorisation_on);
1553 --
1554 if nvl(pmt.PRINCIPAL_ADJUST,0) = 0 then
1555 delete from XTR_DEAL_DATE_AMOUNTS_V
1556 where DEAL_NUMBER = l_deal_no
1557 and TRANSACTION_NUMBER = pmt.TRANSACTION_NUMBER
1558 and AMOUNT_TYPE = 'PRINFLW'
1559 and ACTION_CODE = 'INCRSE';-- Why only INCRSE ????
1560 end if;
1561 --
1562 if pmt.NO_OF_DAYS = 0 and pmt.PRINCIPAL_ACTION is NULL and nvl(pmt.INTEREST,0) = 0 then
1563 -- **** questionable delete
1564 delete from XTR_DEAL_DATE_AMOUNTS_V
1565 where DEAL_NUMBER = l_deal_no
1566 and TRANSACTION_NUMBER = pmt.TRANSACTION_NUMBER
1567 and DATE_TYPE = 'RATESET';
1568 end if;
1569 --
1570 if nvl(pmt.BALANCE_OUT,0) = 0 and nvl(pmt.ACCUM_INTEREST,0) = 0 and l_nill_date is NULL then
1571 -- add l_nill_date is NULL
1572 l_nill_date := pmt.MATURITY_DATE;
1573 end if;
1574 --
1575 fetch DT_ROW INTO pmt;
1576 END LOOP;
1577
1578 --
1579 --
1580 if l_nill_date is NOT NULL then
1581 delete from XTR_ROLLOVER_TRANSACTIONS
1582 where DEAL_NUMBER = l_deal_no
1583 and START_DATE > l_nill_date
1584 and SETTLE_DATE is null; --- >=
1585 --
1586 delete from XTR_DEAL_DATE_AMOUNTS_V
1587 where DEAL_NUMBER = l_deal_no
1588 and amount_date > l_nill_date;
1589 end if;
1590 --
1591 if nvl(l_trans_num,0)=-1 then
1592 if pmt.balance_out is not null then
1593 g_expected_balance_bf := to_char(pmt.EXPECTED_BALANCE_OUT);
1594 g_balance_out_bf := to_char(pmt.BALANCE_OUT);
1595 g_accum_interest_bf := to_char(pmt.ACCUM_INTEREST);
1596 g_principal_adjust := '0';
1597 else
1598 g_expected_balance_bf := '0';
1599 g_balance_out_bf := '0';
1600 g_accum_interest_bf :='0';
1601 g_principal_adjust := to_char(l_face_value_amount);
1602 end if;
1603 else
1604 update XTR_DEAL_DATE_AMOUNTS_V
1605 set amount=nvl(pmt.BALANCE_OUT,0),
1606 hce_amount=nvl(hce_balos,0)
1607 where deal_type='RTMM' and amount_type='BALOUT' and deal_number=l_deal_no;
1608 if SQL%NOTFOUND then
1609 --- Add 1 more row to DDA for Balout
1610 insert into XTR_DEAL_DATE_AMOUNTS_V
1611 (deal_type,amount_type,date_type,
1612 deal_number,transaction_number,transaction_date,currency,
1613 amount,hce_amount,amount_date,transaction_rate,
1614 cashflow_amount,company_code,account_no,action_code,
1615 cparty_account_no,deal_subtype,product_type,
1616 portfolio_code,status_code,cparty_code,dealer_code,
1617 settle,client_code,limit_code,limit_party,
1618 dual_authorisation_by, -- bug 3958736
1619 dual_authorisation_on)
1620 values ('RTMM','BALOUT','COMENCE',
1621 l_deal_no,pmt.TRANSACTION_NUMBER,
1622 l_deal_date,l_ccy ,nvl(pmt.BALANCE_OUT,0),
1623 nvl(hce_balos,0),l_maturity ,pmt.INTEREST_RATE,0,
1624 l_company,l_settle_acct ,NULL,
1625 l_cparty_acct ,l_subtype,l_product,
1626 l_portfolio ,'CURRENT',l_cparty,
1627 l_dealer,'N',l_client ,
1628 nvl(l_limit_code,'NILL'),l_cparty,
1629 l_dual_authorisation_by, -- bug 3958736
1630 l_dual_authorisation_on);
1631 end if;
1632
1633 if nvl(g_chk_bal,'N')='Y' and nvl(c_writoff_int,0)<>0 then
1634 -- Add 1 more row to DDA for WRITINT
1635 insert into XTR_DEAL_DATE_AMOUNTS_V
1636 (deal_type,amount_type,date_type,
1637 deal_number,transaction_number,transaction_date,currency,
1638 amount,hce_amount,amount_date,transaction_rate,
1639 cashflow_amount,company_code,account_no,action_code,
1640 cparty_account_no,deal_subtype,product_type,
1641 portfolio_code,status_code,cparty_code,dealer_code,
1642 settle,client_code,limit_code,limit_party,
1643 dual_authorisation_by, -- bug 3958736
1644 dual_authorisation_on
1645 )
1646 values ('RTMM','WRTEINT','SETTLE',
1647 l_deal_no,l_trans_num,
1648 l_deal_date,l_ccy ,nvl(c_writoff_int,0),
1649 round(nvl(c_writoff_int,0) / l_hce_rate,rounding_fac),
1650 c_increase_effective_from,pmt.INTEREST_RATE,0,
1651 l_company,l_settle_acct ,NULL,
1652 l_cparty_acct ,l_subtype,l_product,
1653 l_portfolio ,'CURRENT',l_cparty,
1654 l_dealer,'N',l_client ,
1655 null,l_cparty,
1656 l_dual_authorisation_by, -- bug 3958736
1657 l_dual_authorisation_on);
1658 end if;
1659 end if;
1660
1661 if DT_ROW%ISOPEN then
1662 close DT_ROW;
1663 end if;
1664
1665 END RECALC_DT_DETAILS ;
1666
1667
1668
1669 end XTR_CALC_P;