[Home] [Help]
PACKAGE BODY: APPS.XTR_CALCULATION_P
Source
1 PACKAGE BODY XTR_CALCULATION_P AS
2 /* $Header: xtrprc2b.pls 120.6 2005/06/29 10:37:27 rjose ship $ */
3
4 -- Procedure to calculate and return all Option Price variables
5
6 PROCEDURE CALC_OPTION_PRICES(time_in_days IN NUMBER,
7 int_rate IN NUMBER,
8 market_price IN NUMBER,
9 strike_price IN NUMBER,
10 vol IN NUMBER,
11 l_delta_call IN OUT NOCOPY NUMBER,
12 l_delta_put IN OUT NOCOPY NUMBER,
13 l_theta_call IN OUT NOCOPY NUMBER,
14 l_theta_put IN OUT NOCOPY NUMBER,
15 l_rho_call IN OUT NOCOPY NUMBER,
16 l_rho_put IN OUT NOCOPY NUMBER,
17 l_gamma IN OUT NOCOPY NUMBER,
18 l_vega IN OUT NOCOPY NUMBER,
19 l_call_price IN OUT NOCOPY NUMBER,
20 l_put_price IN OUT NOCOPY NUMBER) is
21 --
22 -- Below are approximations of normal probability and PI (always fixed constant)
23 a1 NUMBER := 0.4361836;
24 a2 NUMBER := -0.1201678;
25 a3 NUMBER := 0.9372980;
26 pi NUMBER := 3.14159265358979;
27 --
28 r NUMBER := int_rate / 100;
29 t NUMBER := time_in_days / 360;
30 v NUMBER := vol / 100;
31 d1 NUMBER;
32 d2 NUMBER;
33 n_d1_a NUMBER;
34 k1 NUMBER;
35 n_d1_temp NUMBER;
36 n_d1 NUMBER;
37 n_d2_a NUMBER;
38 k2 NUMBER;
39 n_d2_temp NUMBER;
40 n_d2 NUMBER;
41 --
42 begin
43 d1 := (LN(market_price/strike_price) + (r + POWER(v,2)/2)*t) / (v * SQRT(t));
44 d2 := d1 - v*SQRT(t);
45 n_d1_a := EXP(-(POWER(abs(d1),2)) / 2) / SQRT(2 * pi);
46 k1 := 1 / (1 + 0.33267 * ABS(d1));
47 n_d1_temp := 1 - n_d1_a*(a1*k1+a2*POWER(k1,2)+a3*POWER(k1,3));
48 if d1 >= 0 then
49 n_d1 := n_d1_temp;
50 else
51 n_d1 := 1 - n_d1_temp;
52 end if;
53 n_d2_a := EXP(-(POWER(abs(d2),2)) / 2) / SQRT(2*pi);
54 k2 := 1/(1 + 0.33267 * ABS(d2));
55 n_d2_temp := 1-n_d2_a*(a1*k2+a2*POWER(k2,2)+a3*POWER(k2,3));
56 if d2 >= 0 then
57 n_d2 := n_d2_temp;
58 else
59 n_d2 := 1 - n_d2_temp;
60 end if;
61 ---- See Currency Options on the Philadelphia Exchange p272
62 l_call_price := EXP(-r*t)*(market_price * n_d1-strike_price*n_d2);
63 l_put_price := EXP(-r*t)*(strike_price*(1-n_d2)-market_price*(1-n_d1));
64 /* Black-Scholes Formulas
65 l_call_price := (market_price * n_d1)-(strike_price*EXP(-r*t)*n_d2);
66 l_put_price := strike_price*EXP(-r*t)*(1-n_d2)-market_price*(1-n_d1);
67 */
68
69 l_delta_call := n_d1;
70 l_delta_put := n_d1 - 1;
71 l_gamma := n_d1_a/(market_price*v*SQRT(t));
72 l_vega := market_price*SQRT(t)*n_d1_a;
73 l_theta_call := -((market_price*n_d1_a*v)/2/SQRT(t))-(r*strike_price*EXP(-r*t)*n_d2);
74 l_theta_put := -(market_price*n_d1_a*v/2/SQRT(t))+(r*strike_price*EXP(-r*t)*(1-n_d2));
75 l_rho_call := strike_price*t*EXP(-r*t)*n_d2;
76 l_rho_put := -strike_price*t*EXP(-r*t)*(1-n_d2);
77 end CALC_OPTION_PRICES;
78
79
80 -- Procedure to calculate and return all FX Option Price variables
81 PROCEDURE CALC_FX_OPTION_PRICES(
82 l_days IN NUMBER,
83 l_base_int_rate IN NUMBER,
84 l_contra_int_rate IN NUMBER,
85 l_spot_rate IN NUMBER,
86 l_strike_rate IN NUMBER,
87 vol IN NUMBER,
88 l_delta_call IN OUT NOCOPY NUMBER,
89 l_delta_put IN OUT NOCOPY NUMBER,
90 l_theta_call IN OUT NOCOPY NUMBER,
91 l_theta_put IN OUT NOCOPY NUMBER,
92 l_rho_call IN OUT NOCOPY NUMBER,
93 l_rho_put IN OUT NOCOPY NUMBER,
94 l_gamma IN OUT NOCOPY NUMBER,
95 l_vega IN OUT NOCOPY NUMBER,
96 l_call_price IN OUT NOCOPY NUMBER,
97 l_put_price IN OUT NOCOPY NUMBER,
98 l_fwd_rate IN OUT NOCOPY NUMBER ) is
99 --
100 -- Below are approximations of normal probability and PI (always fixed constant)
101 a1 NUMBER := 0.4361836;
102 a2 NUMBER := -0.1201678;
103 a3 NUMBER := 0.9372980;
104 pi NUMBER := 3.14159265358979;
105 --
106 r_f NUMBER := l_base_int_rate / 100;
107 r NUMBER := l_contra_int_rate / 100;
108 t NUMBER := l_days / 360;
109 v NUMBER := vol / 100;
110 d1 NUMBER;
111 d2 NUMBER;
112 n_d1_a NUMBER;
113 k1 NUMBER;
114 n_d1_temp NUMBER;
115 n_d1 NUMBER;
116 n_d2_a NUMBER;
117 k2 NUMBER;
118 n_d2_temp NUMBER;
119 n_d2 NUMBER;
120
121 begin
122
123 d1 := (LN(l_spot_rate/l_strike_rate) + (r-r_f + POWER(v,2)/2)*t) / (v * SQRT(t));
124 d2 := d1 - v*SQRT(t);
125 n_d1_a := EXP(-(POWER(abs(d1),2)) / 2) / SQRT(2 * pi);
126 k1 := 1 / (1 + 0.33267 * ABS(d1));
127 n_d1_temp := 1 - n_d1_a*(a1*k1+a2*POWER(k1,2)+a3*POWER(k1,3));
128 if d1 >= 0 then
129 n_d1 := n_d1_temp;
130 else
131 n_d1 := 1 - n_d1_temp;
132 end if;
133 n_d2_a := EXP(-(POWER(abs(d2),2)) / 2) / SQRT(2*pi);
134 k2 := 1/(1 + 0.33267 * ABS(d2));
135 n_d2_temp := 1-n_d2_a*(a1*k2+a2*POWER(k2,2)+a3*POWER(k2,3));
136 if d2 >= 0 then
137 n_d2 := n_d2_temp;
138 else
139 n_d2 := 1 - n_d2_temp;
140 end if;
141 ---- See Currency Options on the Philadelphia Exchange p272
142 l_fwd_rate :=round(l_spot_rate*EXP((r-r_f)*t),4);
143 l_call_price := EXP(-r*t)*(l_fwd_rate * n_d1-l_strike_rate*n_d2);
144 l_put_price := EXP(-r*t)*(l_strike_rate*(1-n_d2)-l_fwd_rate*(1-n_d1));
145
146 l_delta_call := n_d1;
147 l_delta_put := n_d1 - 1;
148 l_gamma := n_d1_a/(l_spot_rate*v*SQRT(t));
149 l_vega := l_spot_rate*SQRT(t)*n_d1_a;
150 l_theta_call := -((l_spot_rate*n_d1_a*v)/2/SQRT(t))-(r*l_strike_rate*EXP(-r*t)*n_d2);
151 l_theta_put := -(l_spot_rate*n_d1_a*v/2/SQRT(t))+(r*l_strike_rate*EXP(-r*t)*(1-n_d2));
152 l_rho_call := l_strike_rate*t*EXP(-r*t)*n_d2;
153 l_rho_put := -l_strike_rate*t*EXP(-r*t)*(1-n_d2);
154 end CALC_FX_OPTION_PRICES;
155
156
157 --
158 --Bug 3141263
159 --PROCEDURE to Calculate RTMM Rollover Transactions Details
160 --The calculations are based from CALC_DT_SETTLEMENTS program unit
161 --in XTRINRTL.fmb.
162 --The procedure is being called by CALC_RTM_ROLLOVER.
163 --
164 PROCEDURE CALC_RTMM_RT_DETAILS(p_pi_amount_due IN OUT NOCOPY NUMBER,
165 p_interest_rate NUMBER,
166 p_currency VARCHAR2,
167 p_rounding_fac NUMBER,
168 p_rounding_type VARCHAR2,
169 p_hce_rate NUMBER,
170 p_trans_start_date DATE,
171 p_trans_maturity_date DATE,
172 p_trans_settle_date DATE,
173 p_day_count_type VARCHAR2,
174 p_year_calc_type VARCHAR2,
175 p_deal_maturity_date DATE,
176 p_no_of_days IN OUT NOCOPY NUMBER,
177 p_accum_interest IN OUT NOCOPY NUMBER,
178 p_accum_interest_bf IN OUT NOCOPY NUMBER,
179 p_accum_interest_hce IN OUT NOCOPY NUMBER,
180 p_accum_interest_bf_hce IN OUT NOCOPY NUMBER,
181 p_interest IN OUT NOCOPY NUMBER,
182 p_interest_hce IN OUT NOCOPY NUMBER,
183 p_adjusted_balance IN OUT NOCOPY NUMBER,
184 p_principal_amount_type IN OUT NOCOPY VARCHAR2,
185 p_principal_adjust IN OUT NOCOPY VARCHAR2,
186 p_principal_adjust_hce IN OUT NOCOPY VARCHAR2,
187 p_expected_balance_bf IN OUT NOCOPY NUMBER,
188 p_expected_balance_out IN OUT NOCOPY NUMBER,
189 p_balance_out IN OUT NOCOPY NUMBER,
190 p_balance_out_hce IN OUT NOCOPY NUMBER,
191 p_balance_out_bf IN OUT NOCOPY NUMBER,
192 p_balance_out_bf_hce IN OUT NOCOPY NUMBER,
193 p_accum_int_amount_type IN OUT NOCOPY VARCHAR2,
194 p_principal_action IN OUT NOCOPY VARCHAR2) IS
195 --
196 l_exp_int NUMBER;
197 v_year_basis NUMBER;
198 l_first_trans_flag VARCHAR2(1);
199 --
200 BEGIN
201 p_PRINCIPAL_ADJUST:=0;
202 p_PRINCIPAL_AMOUNT_TYPE:='PRINFLW';
203 if nvl(p_PRINCIPAL_ADJUST,0) <> 0 then
204 P_PRINCIPAL_ACTION := 'INCRSE';
205 end if;
206 P_ADJUSTED_BALANCE := nvl(P_BALANCE_OUT_BF,0) +
207 nvl(P_PRINCIPAL_ADJUST,0);
208 p_accum_int_amount_type := '0';
209
210 xtr_calc_p.CALC_DAYS_RUN(P_TRANS_START_DATE,
211 nvl(P_TRANS_SETTLE_DATE,P_TRANS_MATURITY_DATE),
212 p_YEAR_CALC_TYPE,
213 P_NO_OF_DAYS,
214 v_year_basis,
215 null,
216 p_DAY_COUNT_TYPE, --Add Interest Override
217 'N'); --Add Interest Override
218
219 --Add Interest Override
220 P_INTEREST := xtr_fps2_p.interest_round(nvl(P_ADJUSTED_BALANCE,0) * nvl(P_INTEREST_RATE,0) / 100 * nvl(P_NO_OF_DAYS,0) / v_year_basis,p_rounding_fac,p_ROUNDING_TYPE);
221 P_ACCUM_INTEREST := xtr_fps2_p.interest_round(nvl(P_ACCUM_INTEREST_BF,0) +
222 nvl(P_INTEREST,0),p_rounding_fac,p_ROUNDING_TYPE);
223 P_EXPECTED_BALANCE_OUT := nvl(P_EXPECTED_BALANCE_BF,0) + nvl(P_PRINCIPAL_ADJUST,0);
224
225 --Add Interest Override
226 l_exp_int := xtr_fps2_p.interest_round(nvl(P_EXPECTED_BALANCE_OUT,0) * nvl(P_INTEREST_RATE,0) / 100 * nvl(P_NO_OF_DAYS,0) / v_year_basis,p_rounding_fac,p_ROUNDING_TYPE);
227 --
228 if nvl(P_PI_AMOUNT_DUE,0) > nvl(l_exp_int,0) then
229 P_EXPECTED_BALANCE_OUT :=
230 nvl(P_EXPECTED_BALANCE_OUT,0) - nvl(P_PI_AMOUNT_DUE,0) + nvl(l_exp_int,0);
231 end if;
232 --
233 if P_TRANS_MATURITY_DATE = p_deal_MATURITY_DATE then
234 P_PI_AMOUNT_DUE := nvl(P_PI_AMOUNT_DUE,0) + nvl(P_EXPECTED_BALANCE_OUT,0);
235 P_EXPECTED_BALANCE_OUT := 0;
236 else
237 if P_EXPECTED_BALANCE_OUT < 0 then
238 P_PI_AMOUNT_DUE := nvl(P_PI_AMOUNT_DUE,0) + nvl(P_EXPECTED_BALANCE_OUT,0);
239 P_EXPECTED_BALANCE_OUT := 0;
240 end if;
241 end if;
242 P_BALANCE_OUT := nvl(P_ADJUSTED_BALANCE,0); -- nvl(P_PRINCIPAL_DECR,0);
243
244 -- Calculate HCE amounts
245 P_BALANCE_OUT_BF_HCE := round(P_BALANCE_OUT_BF / p_hce_rate,p_rounding_fac);
246 P_BALANCE_OUT_HCE := round(P_BALANCE_OUT / p_hce_rate,p_rounding_fac);
247 P_INTEREST_HCE := round(P_INTEREST / p_hce_rate,p_rounding_fac);
248 P_PRINCIPAL_ADJUST_HCE := round(P_PRINCIPAL_ADJUST / p_hce_rate,p_rounding_fac);
249 P_ACCUM_INTEREST_HCE := round(P_ACCUM_INTEREST / p_hce_rate,p_rounding_fac);
250 P_ACCUM_INTEREST_BF_HCE := round(P_ACCUM_INTEREST_BF / p_hce_rate,p_rounding_fac);
251
252 END calc_rtmm_rt_details;
253
254
255 -- PROCEDURE to Calculate Retail Term Maturity Date Extensions for a
256 -- specific Schedule Code
257 PROCEDURE CALC_RTM_ROLLOVER(
258 errbuf OUT NOCOPY VARCHAR2,
259 retcode OUT NOCOPY NUMBER,
260 P_DEAL_SUBTYPE IN VARCHAR2,
261 P_PRODUCT_TYPE IN VARCHAR2,
262 P_PAYMENT_SCHEDULE_CODE IN VARCHAR2)
263 IS
264
265 l_jan VARCHAR2(1);l_feb VARCHAR2(1);l_mar VARCHAR2(1);l_apr VARCHAR2(1);
266 l_may VARCHAR2(1);l_jun VARCHAR2(1);l_jul VARCHAR2(1);l_aug VARCHAR2(1);
267 l_sep VARCHAR2(1);l_oct VARCHAR2(1);l_nov VARCHAR2(1);l_dec VARCHAR2(1);
268 g_expected_balance_bf NUMBER := 0;
269 g_balance_out_bf NUMBER := 0;
270 g_accum_interest_bf NUMBER := 0;
271 g_principal_adjust NUMBER := 0;
272
273 l_count NUMBER;
274 l_curr_mth VARCHAR2(3);
275 l_continue VARCHAR2(3);
276 l_new_float_rate NUMBER;
277 l_fixed_until DATE;
278 l_maturity_date DATE;
279 year_basis NUMBER;
280 rounding_fac NUMBER;
281 l_hce_rate NUMBER;
282 l_mths_fwd NUMBER;
283 l_calc_type VARCHAR2(12);
284 l_int_act VARCHAR2(7);
285 F_START_DATE DATE;
286 F_EXPECTED_BALANCE_BF NUMBER;
287 F_BALANCE_OUT_BF NUMBER;
288 F_PRINCIPAL_ADJUST NUMBER;
289 F_NO_OF_DAYS NUMBER;
290 F_MATURITY_DATE DATE;
291 F_EXPECTED_BALANCE_OUT NUMBER;
292 F_ADJUSTED_BALANCE NUMBER;
293 F_INTEREST_RATE NUMBER;
294 F_BALANCE_OUT number;
295 F_INTEREST_SETTLED number;
296 F_PRINCIPAL_DECR number;
297 F_INTEREST NUMBER;
298 F_ACCUM_INTEREST NUMBER;
299 F_PI_AMOUNT_DUE NUMBER;
300 F_BALANCE_OUT_BF_HCE NUMBER;
301 F_BALANCE_OUT_HCE NUMBER;
302 F_INTEREST_HCE NUMBER;
303 F_PRINCIPAL_ADJUST_HCE NUMBER;
304 F_ACCUM_INTEREST_HCE NUMBER;
305 F_ACCUM_INTEREST_BF_HCE NUMBER;
306 F_PRINCIPAL_DECR_HCE NUMBER;
307 F_ACCUM_INTEREST_BF number;
308 --
309 l_start_date DATE;
310 l_cparty VARCHAR2(7);
311 l_client VARCHAR2(7);
312 l_company VARCHAR2(7);
313 l_cparty_acct VARCHAR2(7);
314 l_dealer VARCHAR2(10);
315 l_product VARCHAR2(10);
316 l_portfolio VARCHAR2(7);
317 l_settle_acct VARCHAR2(20);
318 l_maturity DATE;
319 l_start DATE;
320 l_deal_date DATE;
321 l_mth VARCHAR2(10);
322 --
323 cursor CHK_CODE is
324 select to_number(POST_MONTHS_FORWARD),INTEREST_ACTION,
325 JAN_YN,FEB_YN,MAR_YN,APR_YN,MAY_YN,JUN_YN,JUL_YN,AUG_YN,
326 SEP_YN,OCT_YN,NOV_YN,DEC_YN,PAYMENT_SCHEDULE_CODE,
327 PAYMENT_FREQUENCY,MIN_POSTINGS,POSTING_FREQ,
328 NEXT_POSTING_DUE
329 from XTR_PAYMENT_SCHEDULE
330 where PAYMENT_SCHEDULE_CODE = P_PAYMENT_SCHEDULE_CODE
331 and DEAL_TYPE = 'RTMM'
332 and DEAL_SUBTYPE = P_DEAL_SUBTYPE
333 and PRODUCT_TYPE = P_PRODUCT_TYPE
334 and nvl(NEXT_POSTING_DUE, l_start_date) <= l_start_date;
335
339 L_POSTING_FREQ XTR_PAYMENT_SCHEDULE_V.POSTING_FREQ%TYPE;
336 L_PAYMENT_SCHEDULE_CODE XTR_PAYMENT_SCHEDULE_V.PAYMENT_SCHEDULE_CODE%TYPE;
337 L_PAYMENT_FREQUENCY XTR_PAYMENT_SCHEDULE_V.PAYMENT_FREQUENCY%TYPE;
338 L_MIN_POSTINGS XTR_PAYMENT_SCHEDULE_V.MIN_POSTINGS%TYPE;
340 L_NEXT_POSTING_DUE XTR_PAYMENT_SCHEDULE_V.NEXT_POSTING_DUE%TYPE;
341
342 cursor DEAL is
343 select a.DEAL_NO,a.DEAL_DATE,a.START_DATE,a.MATURITY_DATE,
344 a.CPARTY_CODE,a.CLIENT_CODE,a.PRODUCT_TYPE,
345 a.PORTFOLIO_CODE,a.MATURITY_ACCOUNT_NO,a.CPARTY_REF,
346 a.COMPANY_CODE,a.DEALER_CODE,a.TERM_MY,a.CURRENCY,
347 a.FACE_VALUE_AMOUNT,a.PAYMENT_FREQ,a.SETTLE_DATE,SETTLE_ACCOUNT_NO,
348 a.LIMIT_CODE,a.PAYMENT_SCHEDULE_CODE ,a.DEAL_TYPE,a.DEAL_SUBTYPE,
349 --31441263
350 a.rounding_type,a.year_calc_type,a.day_count_type
351 from XTR_DEALS_V a
352 where a.deal_type = 'RTMM'
353 and a.maturity_date > l_start_date
354 and a.PAYMENT_SCHEDULE_CODE = L_PAYMENT_SCHEDULE_CODE
355 and a.status_code = 'CURRENT'
356 and a.deal_subtype = P_DEAL_SUBTYPE
357 and a.product_type = P_PRODUCT_TYPE
358 and a.maturity_date > (select max(b.maturity_date)
359 from XTR_ROLLOVER_TRANSACTIONS_V b
360 where b.deal_number = a.deal_no);
361 l_deal_no NUMBER;
362 c_deal DEAL%ROWTYPE;
363
364 cursor LAST_TRANS is
365 select start_date,maturity_date,expected_balance_out,balance_out,
366 accum_interest,interest_rate,pi_amount_due
367 from XTR_ROLLOVER_TRANSACTIONS_V
368 where deal_number = l_deal_no
369 -- and maturity_date >= l_start_date
370 order by start_date desc,maturity_date desc,transaction_number desc;
371
372 RT LAST_TRANS%ROWTYPE;
373
374 cursor RND_YR(p_ccy VARCHAR2) is
375 select ROUNDING_FACTOR,YEAR_BASIS,nvl(HCE_RATE,1) HCE_RATE
376 from XTR_MASTER_CURRENCIES_V
377 where CURRENCY = p_ccy;
378
379 l_cparty_account_no varchar2(20);
380
381 cursor T_NOS is
382 select nvl(max(TRANSACTION_NUMBER), 0) + 1
383 from XTR_ROLLOVER_TRANSACTIONS_V
384 where DEAL_NUMBER = l_deal_no;
385
386 TRANS_NO number;
387
388 --bug 3141263
389 v_principal_action xtr_rollover_transactions.principal_action%type;
390 v_ACCUM_INTEREST xtr_rollover_transactions.accum_interest%type;
391 v_ACCUM_INTEREST_HCE xtr_rollover_transactions.accum_interest_hce%type;
392 v_ADJUSTED_BALANCE xtr_rollover_transactions.adjusted_balance%type;
393 v_PRINCIPAL_ADJUST xtr_rollover_transactions.principal_adjust%type;
394 v_PRINCIPAL_ADJUST_HCE xtr_rollover_transactions.principal_adjust_hce%type;
395 v_PRINCIPAL_AMOUNT_TYPE xtr_rollover_transactions.principal_amount_type%type;
396 v_accum_int_amount_type xtr_rollover_transactions.accum_int_amount_type%type;
397 v_no_of_days NUMBER;
398
399 BEGIN
400
401 l_start_date := trunc(sysdate);
402
403 open CHK_CODE;
404 fetch CHK_CODE INTO l_mths_fwd,l_int_act,
405 l_jan,l_feb,l_mar,l_apr,l_may,l_jun,l_jul,l_aug,
406 l_sep,l_oct,l_nov,l_dec,L_PAYMENT_SCHEDULE_CODE,
407 L_PAYMENT_FREQUENCY,L_MIN_POSTINGS,
408 L_POSTING_FREQ,L_NEXT_POSTING_DUE;
409
410 WHILE CHK_CODE%FOUND LOOP
411
412 FOR c_deal in DEAL LOOP
413 l_deal_no := c_deal.DEAL_NO;
414
415 open LAST_TRANS;
416 fetch LAST_TRANS into RT;
417
418 IF LAST_TRANS%FOUND and
419 round(months_between(RT.maturity_date, l_start_date),0)
420 < nvl(L_MIN_POSTINGS,1) THEN
421 close LAST_TRANS;
422
423 if nvl(l_mths_fwd,0) = 0 then
424 l_mths_fwd := months_between(c_deal.MATURITY_DATE, RT.START_DATE);
425 end if;
426
427 open RND_YR(c_deal.CURRENCY);
428 fetch RND_YR INTO rounding_fac,year_basis,l_hce_rate;
429 close RND_YR;
430
431 --
432 open T_NOS;
433 fetch T_NOS INTO TRANS_NO;
434 close T_NOS;
435
436 -- Initialise for first row
437 F_START_DATE := rt.maturity_date;
438 l_start := rt.maturity_date;
439 F_EXPECTED_BALANCE_BF := rt.expected_balance_out;
440 F_BALANCE_OUT_BF := rt.balance_out;
441 F_ACCUM_INTEREST_BF :=rt.accum_interest;
442 F_PI_AMOUNT_DUE :=rt.pi_amount_due;
443 l_continue := 'YES';
444 -- Calculate each row
445 WHILE (rt.MATURITY_DATE <= c_deal.MATURITY_DATE and
446 months_between(F_START_DATE, l_start_date) <= l_mths_fwd and
447 l_continue='YES')
448 LOOP
449
450 if c_deal.PAYMENT_FREQ = 'WEEKLY' then
451 F_MATURITY_DATE := F_START_DATE + 7;
452 elsif c_deal.PAYMENT_FREQ = 'FORTNIGHTLY' then
453 F_MATURITY_DATE := F_START_DATE + 14;
454 elsif c_deal.PAYMENT_FREQ = 'FOUR WEEKLY' then
455 F_MATURITY_DATE := F_START_DATE + 28;
456 elsif c_deal.PAYMENT_FREQ = 'MONTHLY' then
457 F_MATURITY_DATE := ADD_MONTHS(F_START_DATE,1);
458 elsif c_deal.PAYMENT_FREQ = 'BI MONTHLY' then
459 F_MATURITY_DATE := ADD_MONTHS(F_START_DATE,2);
460 elsif c_deal.PAYMENT_FREQ = 'QUARTERLY' then
461 F_MATURITY_DATE := ADD_MONTHS(F_START_DATE,3);
462 elsif c_deal.PAYMENT_FREQ = 'SEMI ANNUAL' then
463 F_MATURITY_DATE := ADD_MONTHS(F_START_DATE,6);
464 elsif c_deal.PAYMENT_FREQ = 'ANNUAL' then
468 l_count := 0;
465 F_MATURITY_DATE := ADD_MONTHS(F_START_DATE,12);
466 elsif c_deal.PAYMENT_FREQ = 'AD HOC' then
467 l_curr_mth := to_char(F_START_DATE,'MM');
469 LOOP
470 EXIT WHEN l_count = 13;
471 l_count := l_count + 1;
472 l_mth := to_char(ADD_MONTHS(F_START_DATE,l_count),'MM');
473 F_MATURITY_DATE := ADD_MONTHS(F_START_DATE,l_count);
474 --
475 if l_jan = 'Y' and l_mth = '01' then
476 EXIT;
477 elsif l_feb = 'Y' and l_mth = '02' then
478 EXIT;
479 elsif l_mar = 'Y' and l_mth = '03' then
480 EXIT;
481 elsif l_apr = 'Y' and l_mth = '04' then
482 EXIT;
483 elsif l_may = 'Y' and l_mth = '05' then
484 EXIT;
485 elsif l_jun = 'Y' and l_mth = '06' then
486 EXIT;
487 elsif l_jul = 'Y' and l_mth = '07' then
488 EXIT;
489 elsif l_aug = 'Y' and l_mth = '08' then
490 EXIT;
491 elsif l_sep = 'Y' and l_mth = '09' then
492 EXIT;
493 elsif l_oct = 'Y' and l_mth = '10' then
494 EXIT;
495 elsif l_nov = 'Y' and l_mth = '11' then
496 EXIT;
497 elsif l_dec = 'Y' and l_mth = '12' then
498 EXIT;
499 end if;
500 END LOOP;
501 if l_count >= 13 then
502 null;--- No Months were found for AD HOC repayments
503 end if;
504 else
505 -- No frequency found therfore defualt monthly date
506 F_MATURITY_DATE := ADD_MONTHS(F_START_DATE,1);
507 end if;
508
509
510 F_INTEREST_RATE := rt.INTEREST_RATE;
511
512 l_maturity := F_MATURITY_DATE;
513 -- Adjust for weekends
514 if to_char(l_maturity, 'DY') = to_char(to_date('12/02/2000',
515 'DD/MM/YYYY'), 'DY') then
516 F_MATURITY_DATE := l_maturity + 2;
517 elsif to_char(F_MATURITY_DATE, 'DY') = to_char(to_date('13/02/2000',
518 'DD/MM/YYYY'), 'DY') then
519 F_MATURITY_DATE := l_maturity + 1;
520 end if;
521
522 if F_MATURITY_DATE >= c_deal.MATURITY_DATE then
523 l_continue := 'NO';
524 F_MATURITY_DATE := c_deal.MATURITY_DATE;
525 end if;
526 --
527
528 CALC_RTMM_RT_DETAILS(f_pi_amount_due,
529 f_interest_rate,
530 c_deal.currency,
531 rounding_fac,
532 c_deal.rounding_type,
533 l_hce_rate,
534 f_start_date,
535 f_maturity_date,
536 null,
537 c_deal.day_count_type,
538 c_deal.year_calc_type,
539 c_deal.maturity_date,
540 v_no_of_days,
541 v_accum_interest,
542 f_accum_interest_bf,
543 v_accum_interest_hce,
544 f_accum_interest_bf_hce,
545 f_interest,
546 f_interest_hce,
547 v_adjusted_balance,
548 v_principal_amount_type,
549 v_principal_adjust,
550 v_principal_adjust_hce,
551 f_expected_balance_bf,
552 f_expected_balance_out,
553 f_balance_out,
554 f_balance_out_hce,
555 f_balance_out_bf,
556 f_balance_out_bf_hce,
557 v_accum_int_amount_type,
558 v_principal_action);
559
560 --
561 insert into XTR_ROLLOVER_TRANSACTIONS
562 (DEAL_NUMBER,
563 TRANSACTION_NUMBER,
564 DEAL_TYPE,
565 START_DATE,
566 MATURITY_DATE,
567 INTEREST_RATE,
568 NO_OF_DAYS,
569 PI_AMOUNT_DUE,
570 BALANCE_OUT_BF,
571 BALANCE_OUT_BF_HCE,
572 BALANCE_OUT,
573 BALANCE_OUT_HCE,
574 ACCUM_INTEREST_BF,
575 ACCUM_INTEREST_BF_HCE,
576 INTEREST,
577 INTEREST_HCE,
578 EXPECTED_BALANCE_BF,
579 EXPECTED_BALANCE_OUT,
580 CREATED_BY,
581 CREATED_ON,
582 PRINCIPAL_ADJUST,
583 STATUS_CODE,
584 COMPANY_CODE,
585 BAL_OS_ACCOUNT_NO,
586 CURRENCY,
587 CLIENT_CODE,
588 DEAL_SUBTYPE,
589 PRODUCT_TYPE,
590 CPARTY_CODE,
591 DEAL_DATE,
592 PORTFOLIO_CODE,
593 LIMIT_CODE,
594 INTEREST_SETTLED,
595 PRINCIPAL_ACTION,
596 --bug 3141263
597 ACCUM_INT_AMOUNT_TYPE,
598 ACCUM_INTEREST,
599 ACCUM_INTEREST_HCE,
600 ADJUSTED_BALANCE,
601 PRINCIPAL_ADJUST_HCE,
602 PRINCIPAL_AMOUNT_TYPE,
603 RATE_FIXING_DATE)
604 values(c_deal.DEAL_NO,
605 TRANS_NO,
606 'RTMM',
607 l_start,
608 F_MATURITY_DATE,
609 F_INTEREST_RATE,
610 v_no_of_days, --bug 3141263 F_MATURITY_DATE - F_START_DATE,
611 F_PI_AMOUNT_DUE,
612 F_BALANCE_OUT_BF,
613 F_BALANCE_OUT_BF_HCE,
614 F_BALANCE_OUT,
615 F_BALANCE_OUT_HCE,
616 F_ACCUM_INTEREST_BF,
617 F_ACCUM_INTEREST_BF_HCE,
618 F_INTEREST,
619 F_INTEREST_HCE,
620 F_EXPECTED_BALANCE_BF,
621 F_EXPECTED_BALANCE_OUT,
622 fnd_global.user_id,
623 l_start_date,
624 v_principal_adjust,
625 'CURRENT',
626 c_deal.COMPANY_CODE,
627 c_deal.SETTLE_ACCOUNT_NO,
628 c_deal.CURRENCY,
629 c_deal.CLIENT_CODE,
630 c_deal.DEAL_SUBTYPE,
631 c_deal.PRODUCT_TYPE,
632 c_deal.CPARTY_CODE,
633 c_deal.DEAL_DATE,
637 v_principal_action, --bug 3141263 'INCRSE');
634 c_deal.PORTFOLIO_CODE,
635 c_deal.LIMIT_CODE,
636 0,
638 v_accum_int_amount_type,
639 v_ACCUM_INTEREST,
640 v_ACCUM_INTEREST_HCE,
641 v_ADJUSTED_BALANCE,
642 v_PRINCIPAL_ADJUST_HCE,
643 v_PRINCIPAL_AMOUNT_TYPE,
644 l_Start);
645
646 -- set F_START_DATE to when the next start date should be w/out
647 -- weekend adjustments
648 F_START_DATE := l_maturity;
649 -- make sure l_start is the actual start date for next rollover trans
650 l_start := F_MATURITY_DATE;
651
652 TRANS_NO := TRANS_NO + 1;
653 --3141263
654 f_accum_interest_bf := v_accum_interest;
655 f_expected_balance_bf := f_expected_balance_out;
656 END LOOP;
657
658 ELSE
659 close LAST_TRANS;
660 END IF;
661
662 --Most of the calculations in RECALC_DT_DETAILS are done in
663 --CALC_RTMM_RT_DETAILS. However, RECALC_DT_DETAILS still need to be
664 --called since it has the DML's to DDA and RT tables.
665 XTR_CALC_P.RECALC_DT_DETAILS(
666 c_deal.DEAL_NO,
667 'N',
668 l_start_date,
669 null,
670 'N',
671 'N',
672 g_expected_balance_bf,
673 g_balance_out_bf,
674 g_accum_interest_bf,
675 g_principal_adjust,
676 null,
677 null,
678 null,
679 null,
680 null );
681 commit;
682
683 END LOOP; -- Deal loop
684
685 UPDATE xtr_payment_schedule
686 SET next_posting_due = add_months(l_start_date,nvl(L_POSTING_FREQ,1))
687 WHERE deal_type = 'RTMM'
688 AND deal_subtype = P_DEAL_SUBTYPE
689 AND product_type = P_PRODUCT_TYPE
690 AND payment_schedule_code = P_PAYMENT_SCHEDULE_CODE;
691
692
693 fetch CHK_CODE INTO l_mths_fwd,l_int_act,
694 l_jan,l_feb,l_mar,l_apr,l_may,l_jun,l_jul,l_aug,
695 l_sep,l_oct,l_nov,l_dec,L_PAYMENT_SCHEDULE_CODE,
696 L_PAYMENT_FREQUENCY,L_MIN_POSTINGS,
697 L_POSTING_FREQ,L_NEXT_POSTING_DUE;
698 END LOOP; -- Schedule code loop
699 close CHK_CODE;
700 END CALC_RTM_ROLLOVER;
701
702
703 -- PROCEDURE to Calculate Retail Term Maturity Date Extensions
704 -- All parameters can be null
705 PROCEDURE EXTEND_RTM_ROLLOVER(
706 errbuf OUT NOCOPY VARCHAR2,
707 retcode OUT NOCOPY NUMBER,
708 P_DEAL_SUBTYPE IN VARCHAR2,
709 P_PRODUCT_TYPE IN VARCHAR2,
710 P_PAYMENT_SCHEDULE_CODE IN VARCHAR2)
711 IS
712
713 cursor SCH_CODE is
714 select DEAL_SUBTYPE, PRODUCT_TYPE,PAYMENT_SCHEDULE_CODE
715 from XTR_PAYMENT_SCHEDULE
716 where PAYMENT_SCHEDULE_CODE = nvl(P_PAYMENT_SCHEDULE_CODE,PAYMENT_SCHEDULE_CODE)
717 and DEAL_TYPE = 'RTMM'
718 and DEAL_SUBTYPE = nvl(P_DEAL_SUBTYPE,DEAL_SUBTYPE)
719 and PRODUCT_TYPE = nvl(P_PRODUCT_TYPE,PRODUCT_TYPE)
720 order by DEAL_SUBTYPE, PRODUCT_TYPE,PAYMENT_SCHEDULE_CODE;
721
722 BEGIN
723
724 FOR scode IN SCH_CODE LOOP
725
726 XTR_CALCULATION_P.CALC_RTM_ROLLOVER(
727 errbuf,
728 retcode,
729 scode.DEAL_SUBTYPE,
730 scode.PRODUCT_TYPE,
731 scode.PAYMENT_SCHEDULE_CODE);
732
733 END LOOP;
734 END EXTEND_RTM_ROLLOVER;
735
736
737 END XTR_CALCULATION_P;