[Home] [Help]
PACKAGE BODY: APPS.XTR_CALC_P
Source
1 PACKAGE BODY XTR_CALC_P as
2 /* $Header: xtrcalcb.pls 120.13.12010000.2 2008/08/06 10:42:31 srsampat 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
325 XTR_MM_COVERS.CALCULATE_BOND_PRICE_YIELD(p_py_in,p_py_out);
322 p_py_in.p_consideration := p_consideration; -- COMPOUND COUPON
323 p_py_in.p_rounding_type := p_rounding_type; -- COMPOUND COUPON
324
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
444 from XTR_TAX_BROKERAGE_SETUP
445 where REFERENCE_CODE = p_tax_code;
446 --
447 cursor TOTAL_FULL_COUPONS (p_issue_code VARCHAR2) is
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;
476 End If;
473 Fetch FIND_USER into l_xtr_user;
474 If (FIND_USER%NOTFOUND) then
475 l_xtr_user := null;
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
606 l_comp_coupon.p_odd_coupon_start := l_odd_coupon_start;
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;
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
736 'BOND',
733 )
734 Values (p_deal_number,
735 l_transaction_number,
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;
893 PROCEDURE RECALC_DT_DETAILS (
890
891
892
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 cursor THIS_DEAL is
965 select DEAL_DATE, COMPANY_CODE, DEAL_SUBTYPE,
966 PRODUCT_TYPE, PORTFOLIO_CODE, CURRENCY,
967 MATURITY_DATE, SETTLE_ACCOUNT_NO, CPARTY_CODE,
968 CLIENT_CODE, DEALER_CODE, YEAR_CALC_TYPE,
969 LIMIT_CODE, INTERNAL_TICKET_NO,
970 FACE_VALUE_AMOUNT, cparty_ref,
971 start_date, --Add Interest Override
972 DUAL_AUTHORISATION_BY, -- bug 3958736
973 DUAL_AUTHORISATION_ON
974 from XTR_DEALS
975 where DEAL_NO = l_deal_no
976 and DEAL_TYPE = 'RTMM';
977
978 cursor CHK_REF is
979 select ACCOUNT_NUMBER
980 from XTR_BANK_ACCOUNTS_V
981 where PARTY_CODE = l_cparty
982 and BANK_SHORT_CODE = l_cparty_ref
983 and CURRENCY = l_ccy;
984
985 cursor RND_YR is
986 select ROUNDING_FACTOR,YEAR_BASIS,nvl(HCE_RATE,1) HCE_RATE
987 from XTR_MASTER_CURRENCIES_V
988 where CURRENCY = l_ccy;
989
990 cursor START_ROW is
991 select max(START_DATE)
992 from XTR_ROLLOVER_TRANSACTIONS
993 where DEAL_NUMBER = l_deal_no
994 and START_DATE <= l_ref_date --- <
995 and STATUS_CODE = 'CURRENT'
996 and TRANSACTION_NUMBER <>l_trans_num;
997
998 cursor LAST_ROW is
999 select rowid
1000 from XTR_ROLLOVER_TRANSACTIONS
1001 where DEAL_NUMBER = l_deal_no
1002 and START_DATE >= l_start_date
1003 and (MATURITY_DATE > l_start_date or l_last_row='Y') --- add
1004 and STATUS_CODE = 'CURRENT'
1005 -- and ((nvl(g_chk_bal,'N')='Y' and TRANSACTION_NUMBER=l_trans_num)
1006 -- or nvl(g_chk_bal,'N')='N')
1007 order by START_DATE desc,TRANSACTION_NUMBER desc;
1008
1009 last_pmt LAST_ROW%ROWTYPE;
1010
1011 cursor DT_ROW is
1012 select START_DATE,MATURITY_DATE,NO_OF_DAYS,BALANCE_OUT_BF,
1013 BALANCE_OUT,PRINCIPAL_ADJUST,INTEREST_RATE,INTEREST,
1014 INTEREST_SETTLED,PRINCIPAL_ACTION,TRANSACTION_NUMBER,
1015 SETTLE_DATE,ACCUM_INTEREST_BF,PI_AMOUNT_DUE,PI_AMOUNT_RECEIVED,
1016 ACCUM_INTEREST,ROWID,ADJUSTED_BALANCE,COMMENTS,
1017 EXPECTED_BALANCE_BF,EXPECTED_BALANCE_OUT,PRINCIPAL_AMOUNT_TYPE,
1018 ENDORSER_CODE,RATE_FIXING_DATE
1019 from XTR_ROLLOVER_TRANSACTIONS
1020 where DEAL_NUMBER = l_deal_no
1021 and START_DATE >= l_start_date
1022 and (MATURITY_DATE > l_start_date or l_last_row = 'Y')
1023 and STATUS_CODE = 'CURRENT'
1027 for UPDATE OF START_DATE; --lock all rows until commit
1024 -- and ((nvl(g_chk_bal,'N') = 'Y' and TRANSACTION_NUMBER = l_trans_num)
1025 -- or nvl(g_chk_bal,'N') = 'N')
1026 order by START_DATE asc,TRANSACTION_NUMBER asc
1028
1029 pmt DT_ROW%ROWTYPE;
1030
1031 cursor COMP is
1032 select b.INTEREST_ACTION
1033 from XTR_DEALS_V a,
1034 XTR_PAYMENT_SCHEDULE_V b
1035 where a.DEAL_NO = l_deal_no
1036 and b.PAYMENT_SCHEDULE_CODE = a.PAYMENT_SCHEDULE_CODE;
1037
1038 l_date_exits varchar2(1);
1039
1040 cursor chk_date_exits is
1041 select NULL
1042 from XTR_ROLLOVER_TRANSACTIONS
1043 where DEAL_NUMBER = l_deal_no
1044 and (START_DATE = l_ref_date or MATURITY_DATE = l_ref_date)
1045 and transaction_number <>l_trans_num
1046 order by START_DATE desc,TRANSACTION_NUMBER desc ;
1047
1048 -- Add Interest Override
1049 l_first_trans_flag VARCHAR2(1);
1050 l_max_trans_no NUMBER;
1051 l_deal_start_date DATE;
1052 -- End of Change
1053
1054 BEGIN
1055
1056 open THIS_DEAL;
1057 fetch THIS_DEAL INTO l_deal_date, l_company, l_subtype,
1058 l_product, l_portfolio, l_ccy,
1059 l_maturity, l_settle_acct,l_cparty,
1060 l_client, l_dealer, l_year_calc_type,
1061 l_limit_code, l_internal_ticket_no,
1062 l_face_value_amount, l_cparty_ref,
1063 l_deal_start_date,
1064 l_dual_authorisation_by,
1065 l_dual_authorisation_on;
1066
1067 close THIS_DEAL;
1068
1069 if l_cparty_ref is NOT NULL then
1070 open CHK_REF;
1071 fetch CHK_REF into l_cparty_acct;
1072 if CHK_REF%NOTFOUND then
1073 l_cparty_acct := NULL;
1074 end if;
1075 close CHK_REF;
1076 end if;
1077
1078 open RND_YR;
1079 fetch RND_YR INTO rounding_fac,year_basis,l_hce_rate;
1080 close RND_YR;
1081
1082 open COMP;
1083 fetch COMP INTO l_compound;
1084 close COMP;
1085
1086 l_hce_rate := nvl(l_hce_rate,1);
1087 rounding_fac := nvl(rounding_fac,2);
1088 l_compound := nvl(l_compound,'N');
1089 l_comments := NULL;
1090 l_start_date := NULL;
1091 l_nill_date := NULL;
1092
1093 open START_ROW;
1094 fetch START_ROW INTO l_start_date;
1095 close START_ROW;
1096
1097 -- fnd_message.debug('l_start_date = ' || l_start_date);
1098 -- fnd_message.debug('l_ref_date = ' || l_ref_date);
1099 -- fnd_message.debug(' l_trans_num = ' || l_trans_num);
1100
1101 if l_start_date is NULL then
1102 l_start_date := l_ref_date;
1103 l_prv_row_exists := 'N';
1104 else
1105 l_prv_row_exists := 'Y';
1106 end if;
1107
1108
1109 open LAST_ROW;
1110 fetch LAST_ROW INTO last_pmt;
1111 close LAST_ROW;
1112 --
1113 --
1114 --
1115 open DT_ROW;
1116 l_nill_date := NULL;
1117 fetch DT_ROW INTO pmt;
1118
1119 WHILE DT_ROW%FOUND LOOP
1120 --Add Interest Override
1121 IF pmt.start_date = l_deal_start_date
1122 AND nvl(l_day_count_type,'F') = 'B' THEN
1123
1124 SELECT MAX(transaction_number)
1125 INTO l_max_trans_no
1126 FROM xtr_rollover_transactions_v
1127 WHERE deal_number = l_deal_no
1128 AND start_date = pmt.start_date;
1129
1130 IF pmt.transaction_number = Nvl(l_max_trans_no,1) THEN
1131 l_first_trans_flag := 'Y';
1132 ELSE
1133 l_first_trans_flag := 'N';
1134 END IF;
1135 ELSE
1136 l_first_trans_flag := 'N';
1137 END IF;
1138 --End of Addition
1139
1140 -- Reset balance bf and start date from previous row information except
1141 -- for the first row
1142 l_date_exits :=NULL;
1143 l_mark :='N';
1144
1145 if nvl(pmt.PRINCIPAL_ACTION,'@#@') = 'DECRSE' then
1146 l_prin_adj := (-1) * nvl(pmt.PRINCIPAL_ADJUST,0);
1147 else
1148 l_prin_adj := nvl(pmt.PRINCIPAL_ADJUST,0);
1149 end if;
1150 --
1151 -- Initialize
1152 if DT_ROW%ROWCOUNT <> 1 then
1153 pmt.EXPECTED_BALANCE_BF := new_exp_bal;
1154 pmt.ACCUM_INTEREST_BF := new_accum_int;
1155 pmt.BALANCE_OUT_BF := new_balbf;
1156 pmt.START_DATE := new_start_date;
1157 pmt.COMMENTS := l_comments;
1158 elsif DT_ROW%ROWCOUNT = 1 then
1159 if l_prv_row_exists = 'Y' and (nvl(l_least_inserted,'N') = 'Y' or l_trans_num = -1)
1160 and nvl(g_chk_bal,'N') = 'N'
1161 or nvl(g_chk_bal,'N') = 'Y' then
1162 -- This is the row before the EARLIEST ROW CHANGED
1163 -- ie reset its maturity date to the Start date of the row changed.
1164 -- This is because the earliest row may have been inserted.
1165
1166 if nvl(c_principal_action,'@#@') = 'DECRSE' then
1167 if nvl(c_principal_amount_type,'PRINFLW') = 'PRINFLW' then
1168 pmt.PI_AMOUNT_DUE := nvl(c_principal_adjust,0);
1169 l_date_exits := 'W';
1170 l_mark := 'Y';
1171 else
1172 pmt.PI_AMOUNT_DUE := 0;
1173 end if;
1174 elsif l_trans_num <> -1 then
1178 if nvl(g_chk_bal,'N') = 'N' then
1175 pmt.PI_AMOUNT_DUE :=0;
1176 end if;
1177 --
1179 pmt.PI_AMOUNT_RECEIVED := 0;
1180 end if;
1181 --
1182 pmt.MATURITY_DATE := l_ref_date;
1183
1184 XTR_CALC_P.CALC_DAYS_RUN(pmt.START_DATE,
1185 pmt.MATURITY_DATE,
1186 l_year_calc_type,
1187 no_of_days,
1188 year_basis,
1189 NULL,
1190 l_day_count_type, --Add Interest Override
1191 l_first_trans_flag); --Add Interest Override
1192
1193 --Add Interest Override
1194 l_cum_int := XTR_FPS2_P.interest_round(pmt.EXPECTED_BALANCE_BF
1195 + l_prin_adj
1196 * pmt.INTEREST_RATE / 100
1197 * (no_of_days)
1198 / year_basis,rounding_fac,l_rounding_type);
1199 --Original
1200 -- l_cum_int := round(pmt.EXPECTED_BALANCE_BF
1201 -- + l_prin_adj
1202 -- * pmt.INTEREST_RATE / 100
1203 -- * (no_of_days)
1204 -- / year_basis,rounding_fac);
1205 --End of Change
1206 else
1207 l_cum_int := 0;
1208 if nvl(c_principal_action,'@#@') = 'DECRSE'
1209 and nvl(c_principal_amount_type,'PRINFLW') = 'PRINFLW'
1210 and nvl(g_chk_bal,'N') = 'Y' then
1211 l_date_exits := 'W';
1212 end if;
1213 end if;
1214 end if;
1215 -- End Initialize
1216
1217 -- Recalc interest amount
1218 l_prin_decr := 0;
1219 pmt.ADJUSTED_BALANCE := nvl(pmt.BALANCE_OUT_BF,0) + nvl(l_prin_adj,0);
1220 -- added
1221 XTR_CALC_P.CALC_DAYS_RUN(pmt.START_DATE,
1222 pmt.MATURITY_DATE,
1223 l_year_calc_type,
1224 pmt.NO_OF_DAYS,
1225 year_basis,
1226 NULL,
1227 l_day_count_type, --Add Interest Override
1228 l_first_trans_flag);--Add Interest Override
1229
1230 if nvl(pmt.ADJUSTED_BALANCE,0) > 0 then
1231
1232 --Add Interest Override
1233 pmt.INTEREST := XTR_FPS2_P.interest_round(pmt.ADJUSTED_BALANCE * pmt.INTEREST_RATE / 100 *
1234 pmt.NO_OF_DAYS / year_basis,rounding_fac,l_rounding_type);
1235 --Original
1236 -- pmt.INTEREST := round(pmt.ADJUSTED_BALANCE * pmt.INTEREST_RATE / 100 *
1237 -- pmt.NO_OF_DAYS / year_basis,rounding_fac);
1238 --End of Change
1239 else
1240 pmt.INTEREST := 0;
1241 end if;
1242 --
1243 pmt.ACCUM_INTEREST := nvl(pmt.ACCUM_INTEREST_BF,0) + nvl(pmt.INTEREST,0);
1244 -- Added
1245 if pmt.NO_OF_DAYS = 0 and l_mark = 'N' then
1246 pmt.PI_AMOUNT_DUE := 0;
1247 pmt.PI_AMOUNT_RECEIVED := 0;
1248 end if;
1249 --
1250 --
1251 if pmt.SETTLE_DATE is NOT NULL then
1252 -- added if 'W' not split for decrese on different day.
1253 if nvl(pmt.ENDORSER_CODE,'N') = 'W' then
1254 l_prin_decr := pmt.PI_AMOUNT_RECEIVED;
1255 pmt.INTEREST_SETTLED := 0;
1256 else
1257 if pmt.PI_AMOUNT_RECEIVED >= pmt.ACCUM_INTEREST then
1258 l_prin_decr := pmt.PI_AMOUNT_RECEIVED - nvl(pmt.ACCUM_INTEREST,0);
1259 pmt.INTEREST_SETTLED := nvl(pmt.ACCUM_INTEREST,0);
1260 pmt.ACCUM_INTEREST := 0;
1261 else
1262 l_prin_decr := 0;
1263 pmt.INTEREST_SETTLED := abs(nvl(pmt.PI_AMOUNT_RECEIVED,0) - nvl(pmt.ACCUM_INTEREST,0));
1264 pmt.ACCUM_INTEREST := pmt.ACCUM_INTEREST - nvl(pmt.PI_AMOUNT_RECEIVED,0);
1265 end if;
1266 end if;
1267 else
1268 NULL;
1269 end if;
1270 --
1271 if l_compound in('C','COMPOUND') then
1272 pmt.BALANCE_OUT := nvl(pmt.ADJUSTED_BALANCE,0) - nvl(l_prin_decr,0) +
1273 nvl(pmt.ACCUM_INTEREST,0);
1274 pmt.ACCUM_INTEREST := 0;
1275 else
1276 pmt.BALANCE_OUT := nvl(pmt.ADJUSTED_BALANCE,0) - nvl(l_prin_decr,0);
1277 end if;
1278
1279 pmt.EXPECTED_BALANCE_OUT := nvl(pmt.EXPECTED_BALANCE_BF,0) +
1280 nvl(l_prin_adj,0);
1281 if nvl(pmt.EXPECTED_BALANCE_OUT,0) > 0 then -- added
1282
1283 --Add Interest Override
1284 l_exp_int := XTR_FPS2_P.interest_round(nvl(pmt.EXPECTED_BALANCE_OUT,0) * nvl(pmt.INTEREST_RATE,0)
1285 / 100 * pmt.NO_OF_DAYS / year_basis,rounding_fac,l_rounding_type);
1286 --Original
1287 -- l_exp_int := round(nvl(pmt.EXPECTED_BALANCE_OUT,0) *
1288 -- nvl(pmt.INTEREST_RATE,0)
1289 -- / 100 * pmt.NO_OF_DAYS / year_basis,rounding_fac);
1290 --End of Change
1291 else
1292 l_exp_int := 0;
1293 end if;
1294
1295 pmt.EXPECTED_BALANCE_OUT := nvl(pmt.EXPECTED_BALANCE_OUT,0)
1296 - nvl(pmt.PI_AMOUNT_DUE,0) + nvl(l_exp_int,0);
1297
1298 if nvl(pmt.EXPECTED_BALANCE_OUT, 0) < 0 then
1299 pmt.PI_AMOUNT_DUE := pmt.PI_AMOUNT_DUE + pmt.EXPECTED_BALANCE_OUT;
1300 pmt.EXPECTED_BALANCE_OUT := 0;
1301 end if;
1302
1303 --
1304 --add
1305 if pmt.MATURITY_DATE = l_maturity and pmt.ROWID=last_pmt.ROWID then
1306 -- Last transaction therefore make the repayment = Balance Out +
1307 -- Interest Due.
1308
1312
1309 pmt.PI_AMOUNT_DUE := nvl(pmt.PI_AMOUNT_DUE,0) + nvl(pmt.EXPECTED_BALANCE_OUT,0);
1310 pmt.EXPECTED_BALANCE_OUT := 0;
1311 end if;
1313 if pmt.BALANCE_OUT_BF < 0 then
1314 pmt.PI_AMOUNT_DUE := 0;
1315 end if;
1316 --
1317 if g_chk_bal = 'Y' and pmt.ROWID = last_pmt.ROWID then
1318 -- Last transaction therefore make the repayment = Balance Out + Interest Due.
1319 pmt.ACCUM_INTEREST := 0;
1320 pmt.EXPECTED_BALANCE_OUT := 0;
1321 end if;
1322 --
1323 -- Store balance carried fwd and start date for the next row
1324 new_exp_bal := nvl(pmt.EXPECTED_BALANCE_OUT,0);
1325 new_accum_int := nvl(pmt.ACCUM_INTEREST,0);
1326 new_balbf := nvl(pmt.BALANCE_OUT,0);
1327 new_start_date := pmt.MATURITY_DATE;
1328 --
1329 if nvl(pmt.PI_AMOUNT_RECEIVED,0) <> 0 then
1330 l_comments := 'RECD SETTLEMENT ON PREV ROLL';
1331 else
1332 l_comments := NULL;
1333 end if;
1334 --
1335 l_prin_decr := nvl(l_prin_decr,0);
1336 pmt.INTEREST_SETTLED := nvl(pmt.INTEREST_SETTLED,0);
1337 -- Calc HCE amounts
1338 hce_decr := round(nvl(l_prin_decr,0) / l_hce_rate,rounding_fac);
1339 hce_balbf := round(nvl(pmt.BALANCE_OUT_BF,0) / l_hce_rate,rounding_fac);
1340 hce_interest := round(nvl(pmt.INTEREST,0) / l_hce_rate,rounding_fac);
1341 hce_settled := round(nvl(pmt.INTEREST_SETTLED,0) / l_hce_rate,rounding_fac);
1342 hce_accum_int_bf := round(nvl(pmt.ACCUM_INTEREST_BF,0) / l_hce_rate,rounding_fac);
1343 hce_princ := nvl(round(nvl(pmt.PRINCIPAL_ADJUST,0) / l_hce_rate,rounding_fac),0);
1344 hce_balos := round(nvl(pmt.BALANCE_OUT,0) / l_hce_rate,rounding_fac);
1345 hce_accum_int := round(nvl(pmt.ACCUM_INTEREST,0) / l_hce_rate,rounding_fac);
1346 hce_due := round(nvl(pmt.PI_AMOUNT_DUE,0) / l_hce_rate,rounding_fac);
1347 --
1348 update XTR_ROLLOVER_TRANSACTIONS
1349 set START_DATE = pmt.START_DATE,
1350 RATE_FIXING_DATE = pmt.START_DATE,
1351 BALANCE_OUT_BF = pmt.BALANCE_OUT_BF,
1352 BALANCE_OUT_BF_HCE = hce_balbf,
1353 ACCUM_INTEREST_BF = pmt.ACCUM_INTEREST_BF,
1354 ACCUM_INTEREST_BF_HCE = hce_accum_int_bf,
1355 PI_AMOUNT_DUE = pmt.PI_AMOUNT_DUE,
1356 PI_AMOUNT_RECEIVED = pmt.PI_AMOUNT_RECEIVED,
1357 ADJUSTED_BALANCE = pmt.ADJUSTED_BALANCE,
1358 BALANCE_OUT = pmt.BALANCE_OUT,
1359 BALANCE_OUT_HCE = hce_balos,
1360 PRINCIPAL_ADJUST_HCE = hce_princ,
1361 PRINCIPAL_ADJUST = pmt.PRINCIPAL_ADJUST,
1362 INTEREST = pmt.INTEREST,
1363 INTEREST_SETTLED = pmt.INTEREST_SETTLED,
1364 INTEREST_HCE = hce_interest,
1365 ACCUM_INTEREST = pmt.ACCUM_INTEREST,
1366 ACCUM_INTEREST_HCE = hce_accum_int,
1367 SETTLE_DATE = pmt.SETTLE_DATE,
1368 NO_OF_DAYS = pmt.NO_OF_DAYS,
1369 MATURITY_DATE = pmt.MATURITY_DATE,
1370 EXPECTED_BALANCE_BF = nvl(pmt.EXPECTED_BALANCE_BF,0),
1371 EXPECTED_BALANCE_OUT = pmt.EXPECTED_BALANCE_OUT,
1372 ENDORSER_CODE = l_date_exits
1373 where ROWID = pmt.ROWID;
1374
1375 --DDA
1376 delete from XTR_DEAL_DATE_AMOUNTS_V
1377 where DEAL_NUMBER = l_deal_no
1378 and TRANSACTION_NUMBER = pmt.TRANSACTION_NUMBER;
1379
1380 --
1381 -- Insert rows for Principal Adjustments
1382 --
1383 if nvl(pmt.PRINCIPAL_ADJUST,0) <> 0 then
1384 -- Principal Increase has ocurred
1385 if pmt.PRINCIPAL_ACTION = 'INCRSE' then
1386 -- Principal Increase has ocurred
1387 insert into XTR_DEAL_DATE_AMOUNTS_V
1388 (deal_type,amount_type,date_type,
1389 deal_number,transaction_number,transaction_date,currency,
1390 amount,hce_amount,amount_date,transaction_rate,
1391 cashflow_amount,company_code,account_no,action_code,
1392 cparty_account_no,deal_subtype,product_type,
1393 portfolio_code,status_code,cparty_code,dealer_code,
1394 settle,client_code,serial_reference,
1395 dual_authorisation_by,
1396 dual_authorisation_on)
1397 values ('RTMM',nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW'),
1398 'COMENCE',l_deal_no,pmt.TRANSACTION_NUMBER,
1399 l_deal_date,l_ccy ,pmt.PRINCIPAL_ADJUST,hce_princ,
1400 decode(nvl(g_chk_bal,'N'),'N',pmt.START_DATE,pmt.MATURITY_DATE),pmt.INTEREST_RATE,
1401 decode(nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW'),'PRINFLW',decode(l_subtype
1402 ,'FUND',pmt.PRINCIPAL_ADJUST
1403 ,'INVEST',(-1) * pmt.PRINCIPAL_ADJUST),0),
1404 l_company,l_settle_acct ,'INCRSE',
1405 l_cparty_acct ,l_subtype,l_product,
1406 l_portfolio ,'CURRENT',l_cparty,
1407 l_dealer,'N',l_client ,substr(l_internal_ticket_no,1,12),
1408 l_dual_authorisation_by,
1409 l_dual_authorisation_on);
1410 end if;
1411 -- Principal Reduction Row
1412 if nvl(pmt.PRINCIPAL_ACTION,'@#@') = 'DECRSE' then
1413 if nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW') <> 'PRINFLW' then
1414 insert into XTR_DEAL_DATE_AMOUNTS_V
1415 (deal_type,amount_type,date_type,
1416 deal_number,transaction_number,transaction_date,currency,
1417 amount,hce_amount,amount_date,transaction_rate,
1421 settle,client_code,serial_reference,
1418 cashflow_amount,company_code,account_no,action_code,
1419 cparty_account_no,deal_subtype,product_type,
1420 portfolio_code,status_code,cparty_code,dealer_code,
1422 dual_authorisation_by,
1423 dual_authorisation_on)
1424 values ('RTMM',nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW'),
1425 'SETTLE',
1426 -- decode(nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW'),'PRINFLW',
1427 -- decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1428 -- ,0,'FORCAST','SETTLE'),'SETTLE'),
1429
1430 l_deal_no,pmt.TRANSACTION_NUMBER,
1431 l_deal_date,l_ccy ,nvl(pmt.PRINCIPAL_ADJUST,0),
1432 nvl(nvl(hce_princ,pmt.PRINCIPAL_ADJUST),0),
1433 decode(nvl(g_chk_bal,'N'),'N',pmt.START_DATE,pmt.MATURITY_DATE),pmt.INTEREST_RATE,
1434 decode(nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW'),'PRINFLW',decode(l_subtype
1435 ,'FUND',(-1),1) * nvl(pmt.PRINCIPAL_ADJUST,0),0),
1436 l_company,l_settle_acct ,'DECRSE',
1437 l_cparty_acct ,l_subtype,l_product,
1438 l_portfolio ,'CURRENT',l_cparty,
1439 l_dealer,'N',l_client ,substr(l_internal_ticket_no,1,12),
1440 l_dual_authorisation_by,
1441 l_dual_authorisation_on);
1442 end if;
1443 end if;
1444 end if; -- End of Insert rows for Principal Adjustments
1445
1446 --
1447 -- Reduction in Principal from a repayment (insert forcast row with 0's if not received)
1448 --
1449 insert into XTR_DEAL_DATE_AMOUNTS_V
1450 (deal_type,amount_type,date_type,
1451 deal_number,transaction_number,transaction_date,currency,
1452 amount,hce_amount,amount_date,transaction_rate,
1453 cashflow_amount,company_code,account_no,action_code,
1454 cparty_account_no,deal_subtype,product_type,
1455 portfolio_code,status_code,cparty_code,dealer_code,
1456 settle,client_code,serial_reference,
1457 dual_authorisation_by,
1458 dual_authorisation_on)
1459 values ('RTMM','PRINFLW',decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1460 ,0,'FORCAST','SETTLE'),
1461 /*
1462 decode(nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW'),'PRINFLW',
1463 decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1464 ,0,'FORCAST','SETTLE'),'SETTLE'),
1465 decode(nvl(pmt.PRINCIPAL_ACTION,'@#@'),'DECRSE',
1466 decode(nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW'),'PRINFLW',
1467 decode(nvl(pmt.PI_AMOUNT_RECEIVED,NULL),NULL,'FORCAST','SETTLE'),'SETTLE'),
1468 decode(pmt.PI_AMOUNT_RECEIVED,NULL,'FORCAST','SETTLE')),
1469 */
1470 l_deal_no,pmt.TRANSACTION_NUMBER,
1471 l_deal_date,l_ccy ,decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1472 ,0,0,l_prin_decr),
1473 decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1474 ,0,0,round(l_prin_decr/l_hce_rate,rounding_fac)),
1475 nvl(pmt.SETTLE_DATE,pmt.MATURITY_DATE),pmt.INTEREST_RATE,
1476 decode(l_subtype,'FUND',(-1),1) *
1477 decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1478 ,0,0,l_prin_decr),
1479 l_company,l_settle_acct ,'DECRSE',
1480 l_cparty_acct ,l_subtype,l_product,
1481 l_portfolio ,'CURRENT',l_cparty,
1482 l_dealer,'N',l_client ,substr(l_internal_ticket_no,1,12),
1483 l_dual_authorisation_by, -- bug 3958736
1484 l_dual_authorisation_on);
1485
1486 if nvl(pmt.BALANCE_OUT,0) <> 0 and pmt.INTEREST_RATE <> 0 then
1487 --- Rateset Date
1488 insert into XTR_DEAL_DATE_AMOUNTS_V
1489 (deal_type,amount_type,date_type,
1490 deal_number,transaction_number,transaction_date,currency,
1491 amount,hce_amount,amount_date,transaction_rate,
1492 cashflow_amount,company_code,account_no,action_code,
1493 cparty_account_no,deal_subtype,product_type,
1494 portfolio_code,status_code,cparty_code,dealer_code,
1495 settle,client_code,
1496 dual_authorisation_by, -- bug 3958736
1497 dual_authorisation_on)
1498 values ('RTMM','FACEVAL','RATESET',l_deal_no,pmt.TRANSACTION_NUMBER,
1499 l_deal_date,l_ccy ,nvl(pmt.BALANCE_OUT,0),hce_balos,
1500 pmt.START_DATE,pmt.INTEREST_RATE,0,l_company,l_settle_acct ,NULL,
1501 l_cparty_acct ,l_subtype,l_product,
1502 l_portfolio ,'CURRENT',l_cparty,l_dealer,'N',l_client,
1503 l_dual_authorisation_by, -- bug 3958736
1504 l_dual_authorisation_on);
1505 end if;
1506
1507 -- Interest Row
1508
1509 insert into XTR_DEAL_DATE_AMOUNTS_V
1510 (deal_type,amount_type,date_type,
1511 deal_number,transaction_number,transaction_date,currency,
1512 amount,hce_amount,amount_date,transaction_rate,
1513 cashflow_amount,company_code,account_no,action_code,
1514 cparty_account_no,deal_subtype,product_type,
1515 portfolio_code,status_code,cparty_code,dealer_code,
1516 settle,client_code,serial_reference,
1517 dual_authorisation_by, -- bug 3958736
1518 dual_authorisation_on)
1519 values ('RTMM','INTSET',decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1520 ,0,'FORCAST','SETTLE'),
1521 l_deal_no,pmt.TRANSACTION_NUMBER,
1522 l_deal_date,l_ccy ,
1523 decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1524 ,0,pmt.PI_AMOUNT_DUE,pmt.INTEREST_SETTLED),
1525 decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1526 ,0,hce_due,hce_settled),
1527 nvl(pmt.SETTLE_DATE,pmt.MATURITY_DATE),pmt.INTEREST_RATE,
1528 decode(l_subtype
1529 ,'FUND',(-1),1) * decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1530 ,0,nvl(pmt.PI_AMOUNT_DUE,0)
1531 ,nvl(pmt.INTEREST_SETTLED,0)),
1532 l_company,l_settle_acct ,NULL,
1533 l_cparty_acct ,l_subtype,l_product,
1534 l_portfolio ,'CURRENT',l_cparty,
1535 l_dealer,'N',l_client ,substr(l_internal_ticket_no,1,12),
1536 l_dual_authorisation_by, -- bug 3958736
1537 l_dual_authorisation_on);
1538 --
1539 if nvl(pmt.PRINCIPAL_ADJUST,0) = 0 then
1540 delete from XTR_DEAL_DATE_AMOUNTS_V
1541 where DEAL_NUMBER = l_deal_no
1542 and TRANSACTION_NUMBER = pmt.TRANSACTION_NUMBER
1543 and AMOUNT_TYPE = 'PRINFLW'
1544 and ACTION_CODE = 'INCRSE';-- Why only INCRSE ????
1545 end if;
1546 --
1547 if pmt.NO_OF_DAYS = 0 and pmt.PRINCIPAL_ACTION is NULL and nvl(pmt.INTEREST,0) = 0 then
1548 -- **** questionable delete
1549 delete from XTR_DEAL_DATE_AMOUNTS_V
1550 where DEAL_NUMBER = l_deal_no
1551 and TRANSACTION_NUMBER = pmt.TRANSACTION_NUMBER
1552 and DATE_TYPE = 'RATESET';
1553 end if;
1554 --
1555 if nvl(pmt.BALANCE_OUT,0) = 0 and nvl(pmt.ACCUM_INTEREST,0) = 0 and l_nill_date is NULL then
1556 -- add l_nill_date is NULL
1557 l_nill_date := pmt.MATURITY_DATE;
1558 end if;
1559 --
1560 fetch DT_ROW INTO pmt;
1561 END LOOP;
1562
1563 --
1564 --
1565 if l_nill_date is NOT NULL then
1566 delete from XTR_ROLLOVER_TRANSACTIONS
1567 where DEAL_NUMBER = l_deal_no
1568 and START_DATE > l_nill_date
1569 and SETTLE_DATE is null; --- >=
1570 --
1571 delete from XTR_DEAL_DATE_AMOUNTS_V
1572 where DEAL_NUMBER = l_deal_no
1573 and amount_date > l_nill_date;
1574 end if;
1575 --
1576 if nvl(l_trans_num,0)=-1 then
1577 if pmt.balance_out is not null then
1578 g_expected_balance_bf := to_char(pmt.EXPECTED_BALANCE_OUT);
1579 g_balance_out_bf := to_char(pmt.BALANCE_OUT);
1580 g_accum_interest_bf := to_char(pmt.ACCUM_INTEREST);
1581 g_principal_adjust := '0';
1582 else
1583 g_expected_balance_bf := '0';
1584 g_balance_out_bf := '0';
1585 g_accum_interest_bf :='0';
1586 g_principal_adjust := to_char(l_face_value_amount);
1587 end if;
1588 else
1589 update XTR_DEAL_DATE_AMOUNTS_V
1590 set amount=nvl(pmt.BALANCE_OUT,0),
1591 hce_amount=nvl(hce_balos,0)
1592 where deal_type='RTMM' and amount_type='BALOUT' and deal_number=l_deal_no;
1593 if SQL%NOTFOUND then
1594 --- Add 1 more row to DDA for Balout
1595 insert into XTR_DEAL_DATE_AMOUNTS_V
1596 (deal_type,amount_type,date_type,
1597 deal_number,transaction_number,transaction_date,currency,
1598 amount,hce_amount,amount_date,transaction_rate,
1599 cashflow_amount,company_code,account_no,action_code,
1600 cparty_account_no,deal_subtype,product_type,
1601 portfolio_code,status_code,cparty_code,dealer_code,
1602 settle,client_code,limit_code,limit_party,
1606 l_deal_no,pmt.TRANSACTION_NUMBER,
1603 dual_authorisation_by, -- bug 3958736
1604 dual_authorisation_on)
1605 values ('RTMM','BALOUT','COMENCE',
1607 l_deal_date,l_ccy ,nvl(pmt.BALANCE_OUT,0),
1608 nvl(hce_balos,0),l_maturity ,pmt.INTEREST_RATE,0,
1609 l_company,l_settle_acct ,NULL,
1610 l_cparty_acct ,l_subtype,l_product,
1611 l_portfolio ,'CURRENT',l_cparty,
1612 l_dealer,'N',l_client ,
1613 nvl(l_limit_code,'NILL'),l_cparty,
1614 l_dual_authorisation_by, -- bug 3958736
1615 l_dual_authorisation_on);
1616 end if;
1617
1618 if nvl(g_chk_bal,'N')='Y' and nvl(c_writoff_int,0)<>0 then
1619 -- Add 1 more row to DDA for WRITINT
1620 insert into XTR_DEAL_DATE_AMOUNTS_V
1621 (deal_type,amount_type,date_type,
1622 deal_number,transaction_number,transaction_date,currency,
1623 amount,hce_amount,amount_date,transaction_rate,
1624 cashflow_amount,company_code,account_no,action_code,
1625 cparty_account_no,deal_subtype,product_type,
1626 portfolio_code,status_code,cparty_code,dealer_code,
1627 settle,client_code,limit_code,limit_party,
1628 dual_authorisation_by, -- bug 3958736
1629 dual_authorisation_on
1630 )
1631 values ('RTMM','WRTEINT','SETTLE',
1632 l_deal_no,l_trans_num,
1633 l_deal_date,l_ccy ,nvl(c_writoff_int,0),
1634 round(nvl(c_writoff_int,0) / l_hce_rate,rounding_fac),
1635 c_increase_effective_from,pmt.INTEREST_RATE,0,
1636 l_company,l_settle_acct ,NULL,
1637 l_cparty_acct ,l_subtype,l_product,
1638 l_portfolio ,'CURRENT',l_cparty,
1639 l_dealer,'N',l_client ,
1640 null,l_cparty,
1641 l_dual_authorisation_by, -- bug 3958736
1642 l_dual_authorisation_on);
1643 end if;
1644 end if;
1645
1646 if DT_ROW%ISOPEN then
1647 close DT_ROW;
1648 end if;
1649
1650 END RECALC_DT_DETAILS ;
1651
1652
1653
1654 end XTR_CALC_P;