1 PACKAGE BODY XTR_MARKET_DATA_P AS
2 /* $Header: xtrmdcsb.pls 120.6 2007/11/13 10:21:27 kbabu ship $ */
3
4 --PL/SQL wrapper for Cubic Spline Interpolation
5 FUNCTION cubic_spline_interpolation (v_X XTR_MD_NUM_TABLE, v_Y XTR_MD_NUM_TABLE,
6 v_N NUMBER, p_x NUMBER, p_endCondInd1 NUMBER, p_endCondInd2 NUMBER,
7 p_endValue1 NUMBER, p_endValue2 NUMBER)
8 RETURN VARCHAR2
9 AS LANGUAGE JAVA NAME 'oracle.apps.xtr.utilities.server.CubicSplineInterpolation.start (oracle.sql.ARRAY, oracle.sql.ARRAY,int,double,int,int,double,double) return String';
10
11
12 /*--------------------------------------------------------------------------
13 LINEAR_INTERPOLATION
14 Returns a linearly interpolated (Y element) value
15 given p_t (X element).
16
17 p_t = the X element whose Y element is p_rate (to
18 be interpolated).
19 p_t1 = one of the data point that is used to calculate
20 the interpolated point. p_t2 = one of the data point
21 that is used to calculate the interpolated point
22 (has to be different from p_t1).
23 p_rate1 = the Y element associated with p_t1.
24 p_rate2 = the Y element associated with p_t2.
25 p_slope = the slope of the line that passed through
26 (p_t1, p_rate1) and (p_t, p_rate). If p_slope is not
27 null, p_t2 and p_rate2 is not necessary.
28 p_rate = the Y element.that is associated with p_t.
29 --------------------------------------------------------------------------*/
30 PROCEDURE LINEAR_INTERPOLATION (p_t IN NUMBER,
31 p_t1 IN NUMBER,
32 p_t2 IN NUMBER,
33 p_rate1 IN NUMBER,
34 p_rate2 IN NUMBER,
35 p_slope IN NUMBER,
36 p_rate OUT NOCOPY NUMBER) is
37 BEGIN
38
39 p_rate := p_rate1*((p_t2-p_t)/(p_t2-p_t1))+p_rate2*(1-((p_t2-p_t)/(p_t2-p_t1)));
40
41 END LINEAR_INTERPOLATION;
42
43
44 /*-------------------------------------------------------------------------
45 DF_EXPONENTIAL_INTERPOLATION
46 Returns an exponentially interpolated (Y element)
47 value given p_t (X element), assuming all the input
48 rates are discount factors.
49
50 p_t = the X element whose Y element is p_rate (to
51 be interpolated).
52 p_t1 = one of the data point that is used to calculate
53 the interpolated point. p_t2 = one of the data point
54 that is used to calculate the interpolated point
55 (has to be different from p_t1).
56 p_rate1 = the Y element associated with p_t1.
57 p_rate2 = the Y element associated with p_t2.
58 p_rate = the Y element.that is associated with p_t.
59 --------------------------------------------------------------------------*/
60 PROCEDURE DF_EXPONENTIAL_INTERPOLATION (p_indicator CHAR, --'I' or 'O'
61 p_t IN NUMBER,
62 p_t1 IN NUMBER,
63 p_t2 IN NUMBER,
64 p_rate1 IN NUMBER,
65 p_rate2 IN NUMBER,
66 p_rate OUT NOCOPY NUMBER) is
67
68 BEGIN
69
70 IF (p_indicator IS NULL or p_indicator = 'I') THEN
71 p_rate := power(p_rate1,(p_t*((p_t2-p_t)/(p_t2-p_t1))/p_t1))*power(p_rate2,(p_t*((p_t-p_t1)/(p_t2-p_t1))/p_t2));
72 ELSE
73 p_rate := power(p_rate1, p_t/p_t1);
74 END IF;
75
76 END DF_EXPONENTIAL_INTERPOLATION;
77
78
79
80 --This private procedure converts the given rate to day_count_basis1 rate
81 PROCEDURE MD_RATE_CONVERSION (p_spot_date IN DATE,
82 p_future_date IN DATE,
83 p_no_days2 IN NUMBER,
84 p_ann_basis2 IN NUMBER,
85 p_day_count_basis1 IN VARCHAR2,
86 p_rate1 IN NUMBER,
87 p_rate2 IN OUT NOCOPY NUMBER) is
88
89 v_ann_basis1 NUMBER;
90 v_no_days1 NUMBER;
91
92 BEGIN
93
94 IF (p_no_days2 = 0) THEN
95 p_rate2 := p_rate1;
96 ELSE
97 xtr_calc_p.calc_days_run_c(p_spot_date, p_future_date, p_day_count_basis1,
98 null, v_no_days1, v_ann_basis1);
99
100 p_rate2 := (p_rate1*v_no_days1*p_ann_basis2)/(v_ann_basis1*p_no_days2);
101 END IF;
102
103 END MD_RATE_CONVERSION;
104
105
106
107 PROCEDURE Modified_Following_Holiday(p_currency IN VARCHAR2,
108 p_date_in IN DATE,
109 p_date_out OUT NOCOPY DATE) is
110 v_err_code number(8);
111 v_level varchar2(2) := ' ';
112 v_date DATE;
113
114 BEGIN
115
116 v_date:= p_date_in;
117 LOOP
118 -- keep on subtracting a day until it's not a holiday or weekend
119 v_date:=v_date - 1;
120 XTR_fps3_P.CHK_HOLIDAY (v_date,
121 p_currency,
122 v_err_code,
123 v_level);
124 EXIT WHEN v_err_code is null;
125 END LOOP;
126 p_date_out := v_date;
127
128 END;
129
130
131
132 PROCEDURE Following_Holiday(p_in_rec IN following_holiday_in_rec_type,
133 p_out_rec OUT NOCOPY following_holiday_out_rec_type) IS
134
135 v_err_code number(8);
136 v_level varchar2(2) := ' ';
137 v_date DATE;
138 v_no_days NUMBER;
139 v_dummy NUMBER;
140 v_term_type VARCHAR2(20);
141
142 BEGIN
143
144 IF (p_in_rec.p_term_type IS NULL) THEN
145 --need to find out the term type based on 30/360 day count basis
146 --which is the standard for period_code in current system rates.
147 xtr_calc_p.calc_days_run_c(p_in_rec.p_spot_date, p_in_rec.p_future_date,
148 '30/', v_dummy, v_no_days, v_dummy);
149 IF (v_no_days >= 30) THEN
150 v_term_type := 'M';
151 ELSE
152 v_term_type := 'D';
153 END IF;
154 ELSE
155 v_term_type := p_in_rec.p_term_type;
156 END IF;
157 v_date := p_in_rec.p_future_date;
158 LOOP
159 -- keep on adding a day until it's not a holiday or weekend
160 v_date:=v_date + 1;
161 XTR_fps3_P.CHK_HOLIDAY (v_date,
162 p_in_rec.p_currency,
163 v_err_code,
164 v_level);
165 EXIT WHEN v_err_code is null;
166 END LOOP;
167 -- if term type is less than a month just do following rule,
168 -- if term type is over a year, do modified following rule
169 IF (v_term_type IN ('D','O','DAY','DAYS') or p_in_rec.p_period_code = 0) THEN
170 p_out_rec.p_date_out := v_date;
171 ELSE
172 -- if the month changed during the loop, do modified following
173 IF TO_CHAR(v_date,'MM') <> TO_CHAR(p_in_rec.p_future_date,'MM') THEN
174 Modified_Following_Holiday(p_in_rec.p_currency, p_in_rec.p_future_date,
175 p_out_rec.p_date_out);
176 ELSE
177 p_out_rec.p_date_out := v_date;
178 END IF;
179 END IF;
180
181 END;
182
183
184 /*------------------------------------------------------------------------
185 GET_MD_FROM_CURVE
186 Returns a yield rate, discount factor, or volatility
187 from a given market data curve.
188
189 Record Type:
190 MD_FROM_CURVE_IN_REC_TYPE
191 MD_FROM_CURVE_OUT_REC_TYPE
192
193 Assumptions:
194 Only consider the passed parameter, p_side,
195 to determine the data side (bid, ask, mid),
196 and ignore the value of DATA_SIDE from XTR_RM_MD_CURVES
197 table.
198
199 The ordering priority of the interpolation method:
200 1. Look at p_interpolation_method (passed parameter)
201 2. If p_interpolation_method = 'D'/'DEFAULT' then look
202 at DEFAULT_INTERPOLATION from XTR_RM_MD_CURVES
203
204 p_curve_code = name of curve from which to extract data.
205 p_source = table source for calculation. 'C' for Current
206 System Rates table and 'R' for revaluation table.
207 p_indicator = data type of output. 'R' for yield rate,
208 'D' for discount factor, 'V' for volatility.
209 p_spot_date = reference date.
210 p_future_date = future date.
211 p_day_count_basis_out = day count basis to use for output.
212 Can set to null and disregard if p_curve_code is
213 volatility curve.
214 p_interpolation_method = interpolation method to be used
215 for curve. 'L'/'LINEAR' for linear, 'E'/'EXPON' for
216 exponential, 'C'/'CUBIC' for cubic spline, or
217 'D'/'DEFAULT' for the default value specified in the curve,
218 otherwise it will be assumed to be 'L'.
219 p_side = data side of market to return. 'B'/'BID' for bid,
220 'A'/'ASK' for ask, or 'M'/'MID' for mid.
221 No 'BID/ASK' allowed.
222 p_batch_id = batch of revaluation table to be used. Can
223 set to null and disregard if p_source <> 'R'.
224 p_md_out = output that is yield rate, discount factor, or
225 volatility.
226 ------------------------------------------------------------------------*/
227 PROCEDURE GET_MD_FROM_CURVE (p_in_rec IN md_from_curve_in_rec_type,
228 p_out_rec OUT NOCOPY md_from_curve_out_rec_type) is
229
230 v_side VARCHAR2(20);
231
232 CURSOR get_rate_value IS
233 SELECT nvl(period_code,0) period_code,
234 nvl(DECODE(v_side,'B',bid_rate,'BID',
235 bid_rate, 'A', offer_rate, 'ASK', offer_rate,
236 (bid_rate+offer_rate)/2),0) rate,
237 nvl(day_count_basis,'ACTUAL/ACTUAL') day_count_basis,
238 term_type, unique_period_id, currency
239 FROM xtr_rm_md_show_curves_v outter
240 WHERE curve_code = p_in_rec.p_curve_code
241 AND outter.rate_date=
242 (SELECT max(inner.rate_date)
243 FROM xtr_rm_md_show_curves_v inner
244 WHERE trunc(inner.rate_date) <= trunc(p_in_rec.p_spot_date)
245 AND curve_code = p_in_rec.p_curve_code
246 AND outter.unique_period_id=inner.unique_period_id)
247 ORDER BY 1;
248
249 CURSOR compare_dcb IS
250 SELECT COUNT(*) FROM xtr_rm_md_show_curves_v t1,
251 xtr_rm_md_show_curves_v t2
252 WHERE t1.curve_code = p_in_rec.p_curve_code
253 AND t2.curve_code = p_in_rec.p_curve_code
254 AND (t1.rate_date, t1.unique_period_id) IN
255 (SELECT MAX(rate_date), unique_period_id
256 FROM xtr_rm_md_show_curves_v
257 WHERE trunc(rate_date) <= trunc(p_in_rec.p_spot_date)
258 AND curve_code = p_in_rec.p_curve_code
259 GROUP BY unique_period_id)
260 AND (t2.rate_date, t2.unique_period_id) IN
261 (SELECT MAX(rate_date), unique_period_id
262 FROM xtr_rm_md_show_curves_v
263 WHERE trunc(rate_date) <= trunc(p_in_rec.p_spot_date)
264 AND curve_code = p_in_rec.p_curve_code
265 GROUP BY unique_period_id)
266 AND nvl(t1.day_count_basis,'ACTUAL/ACTUAL')<>nvl(t2.day_count_basis,'ACTUAL/ACTUAL');
267
268 CURSOR get_curve_interp_method IS
269 SELECT default_interpolation FROM xtr_rm_md_curves
270 WHERE curve_code = p_in_rec.p_curve_code;
271
272 CURSOR get_rate_value_reval IS
273 SELECT nvl(r.number_of_days,0) period_code,
274 nvl(DECODE(v_side, 'B', nvl(r.bid_overwrite, r.bid),
275 'BID', nvl(r.bid_overwrite, r.bid),
276 'A', nvl(r.ask_overwrite, r.ask),
277 'ASK',nvl(r.ask_overwrite, r.ask),
278 (nvl(r.bid,r.bid_overwrite)+nvl(r.ask,r.ask_overwrite))/2),0) rate,
279 nvl(r.day_count_basis,'ACTUAL/ACTUAL') day_count_basis,
280 r.day_mth term_type, r.reval_type unique_period_id,
281 r.currencyA currency, r.volatility_or_rate rate_type
282 FROM xtr_revaluation_rates r, xtr_rm_md_curve_rates c
283 WHERE r.reval_type = c.rate_code
284 AND c.curve_code = p_in_rec.p_curve_code
285 AND r.batch_id = p_in_rec.p_batch_id
286 ORDER BY 1;
287
288 CURSOR compare_dcb_reval IS
289 SELECT COUNT(*) FROM xtr_revaluation_rates r1,xtr_revaluation_rates r2,
290 xtr_rm_md_curve_rates c1, xtr_rm_md_curve_rates c2
291 WHERE r1.reval_type = c1.rate_code
292 AND r2.reval_type = c2.rate_code
293 AND c1.curve_code = p_in_rec.p_curve_code
294 AND c2.curve_code = p_in_rec.p_curve_code
295 AND r1.batch_id = p_in_rec.p_batch_id
296 AND r2.batch_id = p_in_rec.p_batch_id
297 AND nvl(r1.day_count_basis,'ACTUAL/ACTUAL')<>nvl(r2.day_count_basis,'ACTUAL/ACTUAL');
298
299 --this SQL statement implements the logic to find data side explained in the
300 --MD API doc.
301 CURSOR get_curve_side IS
302 SELECT DECODE(data_side, 'BID/ASK', p_in_rec.p_side, data_side) side
303 FROM xtr_rm_md_curves
304 WHERE curve_code = p_in_rec.p_curve_code;
305
306 TYPE dcb_table IS TABLE OF VARCHAR2(15); -- day count basis table
307 v_in_rec xtr_rate_conversion.df_in_rec_type;
308 v_out_rec xtr_rate_conversion.df_out_rec_type;
309 v_hol_in_rec following_holiday_in_rec_type;
310 v_hol_out_rec following_holiday_out_rec_type;
311 v_X XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();--for day count
312 v_Y XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();--for converted rate to ACT/ACT
313 v_N XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();--for annual basis
314 --for CS Intp original rate when v_uniform_day_count_basis IS FALSE
315 v_YO XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
316 v_dcb dcb_table := dcb_table();
317 v_count BINARY_INTEGER;
318 v_dummy NUMBER;
319 v_found BOOLEAN := FALSE;
320 v_final BOOLEAN := FALSE;
321 v_xval NUMBER;--day count of the maturity date on ACT/ACT
322 v_stop BOOLEAN;
323 v_hi BINARY_INTEGER;
324 v_lo BINARY_INTEGER;
325 v_mid BINARY_INTEGER;
326 v_err_code NUMBER(8);
327 v_level VARCHAR2(2) := ' ';
328 v_future_date DATE; --maturity date
329 v_annual_basis NUMBER;
330 v_day_count NUMBER;
331 v_annual_basis_i NUMBER;--dummy variable for annual basis in
332 v_day_count_i NUMBER;--dummy variable for day count in
333 v_annual_basis_out NUMBER; --annual basis of the maturity date on ACT/ACT
334 v_temp VARCHAR2(100);
335 v_uniform_day_count_basis BOOLEAN := TRUE;
336 v_day_count_basis_in VARCHAR2(15); --the MD dcb
337 v_day_count_basis_out VARCHAR2(15); --the final answer dcb
338 v_Yhi NUMBER;
339 v_Ylo NUMBER;
340 v_diff_dcb NUMBER;
341 v_interpolation_method VARCHAR2(20);
342 v_temp_rate NUMBER; --needed for conv. to disc.rate at the end
343
344
345 BEGIN
346 --call the debug package
347 IF xtr_risk_debug_pkg.g_Debug THEN
348 xtr_risk_debug_pkg.dpush('XTR_MARKET_DATA_P.GET_MD_FROM_CURVE');
349 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'p_curve_code',p_in_rec.p_curve_code);
350 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'p_source',p_in_rec.p_source);
351 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'p_indicator',p_in_rec.p_indicator);
352 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'p_spot_date',p_in_rec.p_spot_date);
353 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'p_future_date',p_in_rec.p_future_date);
354 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'p_interpolation_method',p_in_rec.p_interpolation_method);
355 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'p_day_count_basis_out',p_in_rec.p_day_count_basis_out);
356 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'p_side',p_in_rec.p_side);
357 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'p_batch_id',p_in_rec.p_batch_id);
358 END IF;
359
360 --check whether spot date and future date are valid
361 IF (p_in_rec.p_future_date < p_in_rec.p_spot_date) THEN
362 IF xtr_risk_debug_pkg.g_Debug THEN
363 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'p_future_date cannot be earlier than p_spot_date.');
364 xtr_risk_debug_pkg.dpop('XTR_MARKET_DATA_P.GET_MD_FROM_CURVE');
365 END IF;
366 RAISE_APPLICATION_ERROR
367 (-20001,'p_future_date cannot be earlier than p_spot_date.');
368 END IF;
369
370 --transfer day count basis out to v_day_count_basis_out
371 IF (p_in_rec.p_day_count_basis_out IS NULL) THEN
372 v_day_count_basis_out := 'ACTUAL/ACTUAL';
373 ELSE
374 v_day_count_basis_out := p_in_rec.p_day_count_basis_out;
375 END IF;
376
377 --find annual basis and day count based on the future and spot date
378 xtr_calc_p.calc_days_run_c(p_in_rec.p_spot_date, p_in_rec.p_future_date,
379 'ACTUAL/ACTUAL', null, v_xval,
380 v_annual_basis_out);
381
382 --find interpolation method, using the rule specified in MD API doc.
383 IF (UPPER(p_in_rec.p_interpolation_method) IN ('D','DEFAULT')) THEN
384 OPEN get_curve_interp_method;
385 FETCH get_curve_interp_method INTO v_interpolation_method;
386 CLOSE get_curve_interp_method;
387 ELSE
388 v_interpolation_method := p_in_rec.p_interpolation_method;
389 END IF;
390
391 --get the data side from the cursor using the logic describes in the MD API
392 --doc
393 OPEN get_curve_side;
394 FETCH get_curve_side into v_side;
395 CLOSE get_curve_side;
396
397 v_count := 0;
398 --if source from historical tables
399 IF (UPPER(p_in_rec.p_source) = 'C') THEN
400 --check whether the day count basis is uniform for CS Intp
401 --this check does not apply if rate is volatility
402 IF (v_interpolation_method IN ('C','c','CUBIC') and
403 p_in_rec.p_indicator NOT IN ('V','v')) THEN
404 OPEN compare_dcb;
405 FETCH compare_dcb INTO v_diff_dcb;
406 CLOSE compare_dcb;
407 IF (v_diff_dcb > 0) THEN
408 v_uniform_day_count_basis := FALSE;
409 --if MD dcb is not uniform CS Intp will done on ACT/ACT dcb
410 END IF;
411 END IF;
412 --loop to get all the rates from the curve
413 FOR temprec IN get_rate_value LOOP
414 v_count := v_count+1;
415 --first get the X element
416 v_X.EXTEND;
417 --convert all period_code to the same day count basis: ACTUAL/ACTUAL
418 --first, find future date
419 IF (temprec.term_type IN ('M','V')) THEN
420 --adding months without forcing End of Months
421 v_future_date := least ((add_months(p_in_rec.p_spot_date-3,
422 temprec.period_code/30)+3),
423 last_day(add_months(p_in_rec.p_spot_date,
424 temprec.period_code/30)));
425 ELSIF (temprec.term_type IN ('Y')) THEN
426 v_future_date := least ((add_months(p_in_rec.p_spot_date-3,
427 temprec.period_code/30)+3),
428 last_day(add_months(p_in_rec.p_spot_date,
429 temprec.period_code/30)));
430 ELSE
431 v_future_date := p_in_rec.p_spot_date + temprec.period_code;
432 END IF;
433 --Need to adjust the grid with ISDA Mod. Following Bus.Day Convention
434 --for Yield Curve and Volatility
435
436 xtr_fps3_p.chk_holiday(v_future_date,
437 temprec.currency, v_err_code, v_level);
438 --check if v_future_date falls on holiday, if so call
439 --following_holiday
440 IF (v_err_code IS NOT NULL) THEN
441 v_hol_in_rec.p_future_date := v_future_date;
442 v_hol_in_rec.p_currency := temprec.currency;
443 v_hol_in_rec.p_term_type := temprec.term_type;
444 following_holiday(v_hol_in_rec, v_hol_out_rec);
445 v_future_date := v_hol_out_rec.p_date_out;
446 END IF;
447
448 --from future date get the day_count and annual basis in ACTUAL/ACTUAL
449 v_N.EXTEND;
450
451 xtr_calc_p.calc_days_run_c(p_in_rec.p_spot_date, v_future_date,
452 'ACTUAL/ACTUAL', null, v_X(v_count),
453 v_N(v_count));
454
455 --second, get the Y element of the curve
456 v_Y.EXTEND;
457 --Also for yield curve, need to convert the rate to ACT/ACT
458 --for volatility
459 IF temprec.term_type NOT IN ('M','Y','D') THEN
460 v_Y(v_count) := temprec.rate;
461 --for MD rates with uniform dcb (also Lin and Exp)
462 ELSIF (v_uniform_day_count_basis) THEN
463 v_Y(v_count) := temprec.rate;
464 --need to remember the day count basis lo and hi for later
465 v_dcb.EXTEND;
466 v_dcb(v_count) := temprec.day_count_basis;
467 --for CS and Exp Intp rate
468 ELSE
469 --save day count basis to be needed later for conversion or when
470 -- CS fails and need Lin Intp;
471 --also necessary for Exp when they only have 1 data
472 v_dcb.EXTEND;
473 v_dcb(v_count) := temprec.day_count_basis;
474 v_YO.EXTEND;
475 v_YO(v_count) := temprec.rate;
476 --Since not uniform all rates must be converted to dcb_out for CS intp
477 ---
478 --first step is to find day count and ann basis based on dcb in
479 IF (temprec.day_count_basis <> 'ACTUAL/ACTUAL') THEN
480 xtr_calc_p.calc_days_run_c(p_in_rec.p_spot_date,
481 v_future_date,
482 temprec.day_count_basis, null, v_day_count_i,
483 v_annual_basis_i);
484 ELSE
485 v_day_count_i := v_X(v_count);
486 v_annual_basis_i := v_N(v_count);
487 END IF;
488 --find day count and ann basis of based on the dcb_out
489 IF (v_day_count_basis_out <> 'ACTUAL/ACTUAL') THEN
490 xtr_calc_p.calc_days_run_c(p_in_rec.p_spot_date,
491 v_future_date,
492 v_day_count_basis_out, null, v_day_count,
493 v_annual_basis);
494 ELSE
495 v_day_count := v_X(v_count);
496 v_annual_basis := v_N(v_count);
497 END IF;
498 --convert the v_YO (original) rate
499 xtr_rate_conversion.day_count_basis_conv(v_day_count_i,v_day_count,
500 v_annual_basis_i,
501 v_annual_basis,v_YO(v_count),
502 v_Y(v_count));
503 ---
504
505 END IF;
506 /*
507 --print fetched results for debugging
508 IF xtr_risk_debug_pkg.g_Debug THEN
509 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'C->v_future_date', v_future_date);
510 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'C->v_count',v_count);
511 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'C->v_X',v_X(v_count));
512 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'C->v_Y',v_Y(v_count));
513 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'C->term_type',temprec.term_type);
514 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'C->rate_code',temprec.unique_period_id);
515 END IF;
516 IF (p_in_rec.p_indicator NOT IN ('V','v')) THEN
517 IF xtr_risk_debug_pkg.g_Debug THEN
518 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'C->v_YO',v_YO(v_count));
519 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'C->day_count_basis',v_dcb(v_count));
520 END IF;
521 END IF;
522 */
523 --check if the rate that we're looking for is available without int.
524 IF (v_xval = v_X(v_count)) THEN
525 --if CS get the original rate from the v_YO array
526 IF (v_uniform_day_count_basis) THEN
527 v_dummy := v_Y(v_count);
528 ELSE
529 --this is for the case of CS Intp with ununiform dcb
530 v_dummy := v_YO(v_count);
531 END IF;
532 v_found := TRUE;
533 --for non volatility rate we need day count basis info
534 IF (p_in_rec.p_indicator NOT IN ('V','v')) THEN
535 v_day_count_basis_in := temprec.day_count_basis;
536 END IF;
537 EXIT;
538 END IF;
539 --for Exp. and Lin.: check if the the interpolated point is already
540 --in the interval
541 IF (v_X(v_count) > v_xval) and
542 (v_interpolation_method NOT IN ('C','c','CUBIC')) THEN
543 v_hi := v_count;
544 EXIT;
545 END IF;
546 END LOOP;
547
548 --if the source table is revaluation rates table
549 ELSIF (UPPER(p_in_rec.p_source) = 'R') THEN
550 --for CS intp check whether MD are uniform
551 IF (v_interpolation_method IN ('C','c','CUBIC') and
552 p_in_rec.p_indicator NOT IN ('V','v')) THEN
553 OPEN compare_dcb_reval;
554 FETCH compare_dcb_reval INTO v_diff_dcb;
555 CLOSE compare_dcb_reval;
556 IF (v_diff_dcb > 0) THEN
557 v_uniform_day_count_basis := FALSE;
558 --if MD dcb is not uniform CS Intp will done on ACT/ACT dcb
559 END IF;
560 END IF;
561 --loop to get all the rates from the curve
562 FOR temprec IN get_rate_value_reval LOOP
563 v_count := v_count+1;
564 --first get the X element of the curve
565 v_X.EXTEND;
566 IF (temprec.term_type IN ('MONTH','MONTHS')) THEN
567 --adding months without forcing End of Months
568 v_future_date := least ((add_months(p_in_rec.p_spot_date-3,
569 temprec.period_code/30)+3),
570 last_day(add_months(p_in_rec.p_spot_date,
571 temprec.period_code/30)));
572 ELSIF (temprec.term_type IN ('YEAR','YEARS')) THEN
573 v_future_date := least ((add_months(p_in_rec.p_spot_date-3,
574 temprec.period_code/30)+3),
575 last_day(add_months(p_in_rec.p_spot_date,
576 temprec.period_code/30)));
577 ELSE
578 v_future_date := p_in_rec.p_spot_date + temprec.period_code;
579 END IF;
580 --Need to adjust the grid with ISDA Mod. Following Bus.Day Convention
581 --for Yield Curve and Volatility
582
583 xtr_fps3_p.chk_holiday(v_future_date,
584 temprec.currency, v_err_code, v_level);
585 --check if v_future_date falls on holiday, if so call
586 --following_holiday
587 IF (v_err_code IS NOT NULL) THEN
588 v_hol_in_rec.p_future_date := v_future_date;
589 v_hol_in_rec.p_currency := temprec.currency;
590 v_hol_in_rec.p_term_type := temprec.term_type;
591 v_hol_in_rec.p_period_code := temprec.period_code;
592 following_holiday(v_hol_in_rec, v_hol_out_rec);
593 v_future_date := v_hol_out_rec.p_date_out;
594 END IF;
595
596 --from future date get the day_count and annual basis in ACTUAL/ACTUAL
597 v_N.EXTEND;
598
599 xtr_calc_p.calc_days_run_c(p_in_rec.p_spot_date, v_future_date,
600 'ACTUAL/ACTUAL', null, v_X(v_count),
601 v_N(v_count));
602
603 --second, get the Y element of the curve
604 v_Y.EXTEND;
605 --Also for yield curve, need to convert the rate to ACT/ACT
606 --for non-yield rate i.e. volatility
607 IF (temprec.rate_type <> 'RATE') THEN
608 v_Y(v_count) := temprec.rate;
609 --for MD rates with uniform dcb (also Lin and Exp)
610 ELSIF (v_uniform_day_count_basis) THEN
611 v_Y(v_count) := temprec.rate;
612 --need to remember the day count basis lo and hi for later
613 v_dcb.EXTEND;
614 v_dcb(v_count) := temprec.day_count_basis;
615 --for CS and Exp Intp rate
616 ELSE
617 --save day count basis to be needed later for conversion or when
618 -- CS fails and need Lin Intp;
619 --also necessary for Exp when they only have 1 data
620 v_dcb.EXTEND;
621 v_dcb(v_count) := temprec.day_count_basis;
622 v_YO.EXTEND;
623 v_YO(v_count) := temprec.rate;
624 --Since not uniform all rates must be converted to ACT/ACT for CS intp
625 ---
626 --first step is to find day count and ann basis based on dcb in
627 IF (temprec.day_count_basis <> 'ACTUAL/ACTUAL') THEN
628 xtr_calc_p.calc_days_run_c(p_in_rec.p_spot_date,
629 v_future_date,
630 temprec.day_count_basis, null, v_day_count_i,
631 v_annual_basis_i);
632 ELSE
633 v_day_count_i := v_X(v_count);
634 v_annual_basis_i := v_N(v_count);
635 END IF;
636 --find day count and ann basis of based on the dcb_out
637 IF (v_day_count_basis_out <> 'ACTUAL/ACTUAL') THEN
638 xtr_calc_p.calc_days_run_c(p_in_rec.p_spot_date,
639 v_future_date,
640 v_day_count_basis_out, null, v_day_count,
641 v_annual_basis);
642 ELSE
643 v_day_count := v_X(v_count);
644 v_annual_basis := v_N(v_count);
645 END IF;
646 --convert the v_YO (original) rate
647 xtr_rate_conversion.day_count_basis_conv(v_day_count_i,v_day_count,
648 v_annual_basis_i,
649 v_annual_basis,v_YO(v_count),
650 v_Y(v_count));
651 ---
652
653 END IF;
654 /*
655 --print result for debugging
656 --print fetched results for debugging
657 IF xtr_risk_debug_pkg.g_Debug THEN
658 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'R->v_future_date', v_future_date);
659 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'R->v_count',v_count);
660 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'R->v_X',v_X(v_count));
661 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'R->v_Y',v_Y(v_count));
662 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'R->term_type',temprec.term_type);
663 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'R->rate_code',temprec.unique_period_id);
664 END IF;
665 IF (p_in_rec.p_indicator NOT IN ('V','v')) THEN
666 -- xtr_risk_debug_pkg.dlog('R->v_YO',v_YO(v_count));
667 IF xtr_risk_debug_pkg.g_Debug THEN
668 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'R->day_count_basis',v_dcb(v_count));
669 END IF;
670 END IF;
671 */
672 --check if the rate that we're looking for is available without int.
673 IF (v_xval = v_X(v_count)) THEN
674 --if CS get the original rate from the v_YO array
675 IF (v_uniform_day_count_basis) THEN
676 v_dummy := v_Y(v_count);
677 ELSE
678 --this is for the case of CS Intp with ununiform dcb
679 v_dummy := v_YO(v_count);
680 END IF;
681 v_found := TRUE;
682 --for non volatility rate we need day count basis info
683 IF (p_in_rec.p_indicator NOT IN ('V','v')) THEN
684 v_day_count_basis_in := temprec.day_count_basis;
685 END IF;
686 EXIT;
687 END IF;
688 --for Exp. and Lin.: check if the the interpolated point is already
689 --in the interval
690 IF (v_X(v_count) > v_xval) and
691 (v_interpolation_method NOT IN ('C','c','CUBIC')) THEN
692 v_hi := v_count;
693 EXIT;
694 END IF;
695 END LOOP;
696
697 ELSE
698 IF xtr_risk_debug_pkg.g_Debug THEN
699 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'The p_source values can only be ''C'' or ''R''.');
700 xtr_risk_debug_pkg.dpop('XTR_MARKET_DATA_P.GET_MD_FROM_CURVE');
701 END IF;
702 RAISE_APPLICATION_ERROR
703 (-20001,'The p_source values can only be ''C'' or ''R''.');
704 END IF;
705
706 --if no data retrieved from the table raise exception
707 IF (v_count = 0 and NOT v_found) THEN
708 IF xtr_risk_debug_pkg.g_Debug THEN
709 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'v_count = 0 and v_found = false');
710 xtr_risk_debug_pkg.dpop('XTR_MARKET_DATA_P.GET_MD_FROM_CURVE');
711 END IF;
712 RAISE e_mdcs_no_data_found;
713 END IF;
714 --if v_xval is outside the range, only linear int. is needed
715 --and it's agreed that the extension slopes after both ends are 0
716 --this is also applied if there is only 1 point in a curve
717 IF (v_xval < v_X(1) or (v_count = 1 and NOT v_found)) THEN
718 IF (v_uniform_day_count_basis) THEN
719 v_dummy := v_Y(1);
720 ELSE
721 v_dummy := v_YO(1);
722 END IF;
723 v_found := TRUE;
724 --v_day_count_basis for v_Y is ACT/ACT in the case of CS and Expon Intp
725 --for volatility no day count basis notion
726 IF (p_in_rec.p_indicator NOT IN ('V','v')) THEN
727 v_day_count_basis_in := v_dcb(1);
728 END IF;
729 ELSIF (v_xval > v_X(v_count)) THEN
730 IF (v_uniform_day_count_basis) THEN
731 v_dummy := v_Y(v_count);
732 ELSE
733 v_dummy := v_YO(v_count);
734 END IF;
735 v_found := TRUE;
736 --v_day_count_basis for v_Y is ACT/ACT in the case of CS and Expon Intp
737 IF (p_in_rec.p_indicator NOT IN ('V','v')) THEN
738 v_day_count_basis_in := v_dcb(v_count);
739 END IF;
740 END IF;
741
742 --do cubic spline if interpolation method is 'C' and
743 --at least there are 3 points in a curve
744 IF (NOT v_found and
745 v_interpolation_method IN ('C','CUBIC','c') and
746 v_count >= 3) THEN
747 --Either do CS Intp on v_day_count_basis_in or dcb out depending on
748 --v_uniform_day_count_basis
749 --however, no day count basis notion in volatility
750 IF (p_in_rec.p_indicator NOT IN ('V','v')) THEN
751 IF (v_uniform_day_count_basis) THEN
752 v_day_count_basis_in := v_dcb(1);
753 ELSE
754 v_day_count_basis_in := v_day_count_basis_out;
755 END IF;
756 END IF;
757 v_temp := cubic_spline_interpolation(v_X,v_Y,v_X.COUNT,v_xval,1,1,0,0);
758 IF (SUBSTR(v_temp,1,1) = 'E') THEN
759 --if an error occurred gives negative value so that linear int.
760 --will be used
761 v_dummy := -1;
762 ELSE
763 v_dummy := fnd_number.canonical_to_number(v_temp); -- for bug 6408487
764 END IF;
765 IF (v_dummy >= 0) THEN
766 v_found := TRUE;
767 END IF;
768 END IF;
769
770 --result is still not found until this point use either Exponential or Linear
771 IF (NOT v_found) THEN
772 -- for CS Intp where rate is till not found we have to do binary search
773 -- to get the interval and default to Linear Intp
774 IF (v_interpolation_method IN ('C','c','CUBIC') and
775 NOT v_found) THEN
776 --do Binary Search
777 v_lo := 1;
778 v_hi := v_count;
779 v_stop := FALSE;
780 WHILE ((v_hi-v_lo > 1) and NOT v_stop) LOOP
781 v_mid := (v_hi+v_lo)/2;
782 IF (v_X(v_mid) = v_xval) THEN
783 IF (v_uniform_day_count_basis) THEN
784 v_dummy := v_Y(v_mid);
785 ELSE
786 v_dummy := v_YO(v_mid);
787 END IF;
788 --in volatility day count basis is not used
789 IF (p_in_rec.p_indicator NOT IN ('V','v')) THEN
790 v_day_count_basis_in := v_dcb(v_mid);
791 END IF;
792 v_stop := TRUE;
793 ELSE
794 IF (v_X(v_mid) > v_xval) THEN
795 v_hi := v_mid;
796 ELSE
797 v_lo := v_mid;
798 END IF;
799 END IF;
800 END LOOP;
801 ELSE
802 v_lo := v_hi-1;
803 END IF;
804
805 --if any of the day count (v_X) is 0 Exponential Int. will fail
806 --thus, default to Linear Int.
807 --volatility cannot be interpolated with the current Exp. Int. formula.
808 IF(v_interpolation_method IN ('E','EXPON','e')
809 and p_in_rec.p_indicator NOT IN ('V','v') and
810 NOT (v_X(v_lo)=0 or v_X(v_hi)=0)) THEN
811 --save the day count basis Exp Intp always uses ACT/ACT
812 v_day_count_basis_in := 'ACTUAL/ACTUAL';
813 --do day count basis conversion to ACTUAL/ACTUAL
814 --first, dcb conv for v_lo
815 IF (v_dcb(v_lo)<>'ACTUAL/ACTUAL') THEN
816 --convert the v_lo
817 --find day count and ann basis of v_lo based on the dcb_out
818 --convert the v_lo rate
819 v_Ylo := v_Y(v_lo);
820 md_rate_conversion(p_in_rec.p_spot_date,p_in_rec.p_spot_date+v_X(v_lo),
821 v_X(v_lo), v_N(v_lo),
822 v_dcb(v_lo), v_Ylo,
823 v_Y(v_lo));
824 END IF;
825 --dcb conv for v_hi
826 IF (v_dcb(v_hi)<>'ACTUAL/ACTUAL') THEN
827 --convert the v_hi
828 --find day count and ann basis of v_hi based on the dcb_out
829 --convert the v_hi rate
830 v_Yhi := v_Y(v_hi);
831 md_rate_conversion(p_in_rec.p_spot_date,p_in_rec.p_spot_date+v_X(v_hi),
832 v_X(v_hi), v_N(v_hi),
833 v_dcb(v_hi), v_Yhi,
834 v_Y(v_hi));
835 END IF;
836 --do exponential int
837 --first convert the two interval points to discount factors
838 v_in_rec.p_indicator := 'T';
839 v_in_rec.p_day_count := v_X(v_lo);
840 v_in_rec.p_annual_basis := v_N(v_lo);
841 v_in_rec.p_rate := v_Y(v_lo);
842 xtr_rate_conversion.discount_factor_conv(v_in_rec,v_out_rec);
843 v_Y(v_lo) := v_out_rec.p_result;
844 v_in_rec.p_day_count := v_X(v_hi);
845 v_in_rec.p_annual_basis := v_N(v_hi);
846 v_in_rec.p_rate := v_Y(v_hi);
847 xtr_rate_conversion.discount_factor_conv(v_in_rec,v_out_rec);
848 v_Y(v_hi) := v_out_rec.p_result;
849 df_exponential_interpolation('I',v_xval,v_X(v_lo),v_X(v_hi),v_Y(v_lo),
850 v_Y(v_hi), v_dummy);
851
852 --in case the final answer is not Discount Factor or in a case that
853 --we need to convert day count basis
854 --(day_count_basis_out <> v_day_count_basis_in),
855 --we need to convert from discount factor back to yield rate
856 IF (NOT (p_in_rec.p_indicator = 'D' and
857 v_day_count_basis_out = 'ACTUAL/ACTUAL')) THEN
858 --convert back to yield rate
859 v_in_rec.p_indicator := 'F';
860 v_in_rec.p_day_count := v_xval;
861 v_in_rec.p_rate := v_dummy;
862 xtr_rate_conversion.discount_factor_conv(v_in_rec,v_out_rec);
863 v_dummy := v_out_rec.p_result;
864 ELSE
865 v_final := TRUE; --got the final answer, to be used later
866 END IF;
867 ELSE
868 --do linear int
869 --skip day count basis realted conversion for volatility
870 IF (p_in_rec.p_indicator NOT IN ('V','v')) THEN
871 --if data1 dcb = data2 dcb use that dcb
872 --else if data1 dcv <> data dcv use day count basis out
873 IF (v_dcb(v_lo) = v_dcb(v_hi)) THEN
874 v_day_count_basis_in := v_dcb(v_lo);
875 ELSE --do conversion to day count basis out
876 v_day_count_basis_in := v_day_count_basis_out;
877
878 --convert the v_lo to v_day_count_basis_out if not already
879 IF (v_dcb(v_lo) <> v_day_count_basis_out) THEN
880 --find day count and ann basis of v_hi based on the dcb_in
881 IF (v_dcb(v_lo) <> 'ACTUAL/ACTUAL') THEN
882 xtr_calc_p.calc_days_run_c(p_in_rec.p_spot_date,
883 p_in_rec.p_spot_date + v_X(v_lo),
884 v_dcb(v_lo), null, v_day_count_i,
885 v_annual_basis_i);
886 ELSE
887 v_day_count_i := v_X(v_lo);
888 v_annual_basis_i := v_N(v_lo);
889 END IF;
890 --find day count and ann basis of v_hi based on the dcb_out
891 IF (v_day_count_basis_out <> 'ACTUAL/ACTUAL') THEN
892 xtr_calc_p.calc_days_run_c(p_in_rec.p_spot_date,
893 p_in_rec.p_spot_date + v_X(v_lo),
894 v_day_count_basis_out, null, v_day_count,
895 v_annual_basis);
896 ELSE
897 v_day_count := v_X(v_lo);
898 v_annual_basis := v_N(v_lo);
899 END IF;
900 --convert the v_lo rate
901 --v_uniform_day_count_basis is FALSE only in the case of CS intp
902 --with nonuniform MD rates
903 IF v_uniform_day_count_basis THEN
904 v_Ylo := v_Y(v_lo);
905 ELSE
906 v_Ylo := v_YO(v_lo);
907 END IF;
908 xtr_rate_conversion.day_count_basis_conv(v_day_count_i,v_day_count,
909 v_annual_basis_i,
910 v_annual_basis,v_Ylo,
911 v_Y(v_lo));
912 END IF;
913 --convert the v_hi to v_day_count_basis_out if not already
914 IF (v_dcb(v_hi) <> v_day_count_basis_out) THEN
915 --find day count and ann basis of v_hi based on the dcb_in
916 IF (v_dcb(v_hi) <> 'ACTUAL/ACTUAL') THEN
917 xtr_calc_p.calc_days_run_c(p_in_rec.p_spot_date,
918 p_in_rec.p_spot_date + v_X(v_hi),
919 v_dcb(v_hi), null, v_day_count_i,
920 v_annual_basis_i);
921 ELSE
922 v_day_count_i := v_X(v_hi);
923 v_annual_basis_i := v_N(v_hi);
924 END IF;
925 --find day count and ann basis of v_hi based on the dcb_out
926 IF (v_day_count_basis_out <> 'ACTUAL/ACTUAL') THEN
927 xtr_calc_p.calc_days_run_c(p_in_rec.p_spot_date,
928 p_in_rec.p_spot_date + v_X(v_hi),
929 v_day_count_basis_out, null, v_day_count,
930 v_annual_basis);
931 ELSE
932 v_day_count := v_X(v_hi);
933 v_annual_basis := v_N(v_hi);
934 END IF;
935 --convert the v_hi rate
936 --v_uniform_day_count_basis is FALSE onlyin the case of CS intp
937 --with nonuniform MD rates
938 IF v_uniform_day_count_basis THEN
939 v_Yhi := v_Y(v_hi);
940 ELSE
941 v_Yhi := v_YO(v_hi);
942 END IF;
943 xtr_rate_conversion.day_count_basis_conv(v_day_count_i,v_day_count,
944 v_annual_basis_i,
945 v_annual_basis, v_Yhi,
946 v_Y(v_hi));
947 END IF;
948 END IF;
949 END IF;
950 linear_interpolation(v_xval,v_X(v_lo),v_X(v_hi),v_Y(v_lo),v_Y(v_hi),
951 null, v_dummy);
952 END IF;
953 END IF;
954 IF xtr_risk_debug_pkg.g_Debug THEN
955 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'v_uniform_day_count_basis', v_uniform_day_count_basis);
956 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'v_dummy before DCB Conv', v_dummy);
957 END IF;
958 --for volatility we don't need to worry about DCB and DF conversion
959 IF (p_in_rec.p_indicator NOT IN ('V','v')) THEN
960 --Need to convert the rate to p_day_count_basis_out if not the same as
961 --v_day_count_basis_out
962 IF (v_day_count_basis_out <> v_day_count_basis_in) THEN
963 IF (v_day_count_basis_in <> 'ACTUAL/ACTUAL') THEN
964 --need to find the v_day_count_i and v_annual_basis_i based on the
965 --dcb_out
966 xtr_calc_p.calc_days_run_c(p_in_rec.p_spot_date,p_in_rec.p_future_date,
967 v_day_count_basis_in, null, v_day_count_i,
968 v_annual_basis_i);
969 ELSE
970 v_day_count_i := v_xval;
971 v_annual_basis_i := v_annual_basis_out;
972 END IF;
973 IF (v_day_count_basis_out <> 'ACTUAL/ACTUAL') THEN
974 xtr_calc_p.calc_days_run_c(p_in_rec.p_spot_date, p_in_rec.p_future_date,
975 v_day_count_basis_out, null, v_day_count,
976 v_annual_basis);
977 ELSE
978 v_day_count := v_xval;
979 v_annual_basis := v_annual_basis_out;
980 END IF;
981 xtr_rate_conversion.day_count_basis_conv(v_day_count_i, v_day_count,
982 v_annual_basis_i,
983 v_annual_basis,v_dummy,
984 p_out_rec.p_md_out);
985 v_dummy := p_out_rec.p_md_out;
986 ELSE
987 IF (v_day_count_basis_out = 'ACTUAL/ACTUAL') THEN
988 v_annual_basis := v_annual_basis_out;
989 v_day_count := v_xval;
990 ELSE
991 xtr_calc_p.calc_days_run_c(p_in_rec.p_spot_date,p_in_rec.p_future_date,
992 v_day_count_basis_out, null, v_day_count,
993 v_annual_basis);
994 END IF;
995 END IF;
996
997 --if looking for DF then convert to DF except if it's already in DF form
998 --which is the special case for if interpolation_method = 'E' and does not
999 --require day count basis translation (above)
1000 IF ((p_in_rec.p_indicator = 'D') and
1001 NOT (v_interpolation_method IN ('E','EXPON','e') and v_final))
1002 THEN
1003 v_in_rec.p_indicator := 'T';
1004 v_in_rec.p_day_count := v_day_count;
1005 v_in_rec.p_annual_basis := v_annual_basis;
1006 v_in_rec.p_rate := v_dummy;
1007 xtr_rate_conversion.discount_factor_conv(v_in_rec, v_out_rec);
1008 v_dummy := v_out_rec.p_result;
1009 --convert to discount rate if p_indicator = 'DR'
1010 ELSIF (UPPER(p_in_rec.p_indicator) = 'DR') THEN
1011 v_temp_rate := v_dummy;
1012 xtr_rate_conversion.yield_to_discount_rate(v_temp_rate,
1013 v_day_count, v_annual_basis, v_dummy);
1014 END IF;
1015 END IF;
1016
1017 p_out_rec.p_md_out := v_dummy;
1018
1019 --close debug and print the result
1020 IF xtr_risk_debug_pkg.g_Debug THEN
1021 xtr_risk_debug_pkg.dlog('GET_MD_FROM_CURVE: ' || 'p_out_rec.p_md_out', p_out_rec.p_md_out);
1022 xtr_risk_debug_pkg.dpop('XTR_MARKET_DATA_P.GET_MD_FROM_CURVE');
1023 END IF;
1024
1025 END GET_MD_FROM_CURVE;
1026
1027
1028
1029 /*------------------------------------------------------------------------
1030 GET_MD_FROM_SET
1031 Returns a yield rate, discount factor, volatility,
1032 FX spot rate, and bond price of a given market data set.
1033
1034 Record Type:
1035 MD_FROM_SET_IN_REC_TYPE
1036 MD_FROM_SET_OUT_REC_TYPE
1037
1038 For required parameters for each rate type look at MD API
1039 Design Doc.
1040
1041 Assumptions:
1042 To distinguish between IR (interest rate) and FX
1043 (exchange rate) volatility:
1044 If p_indicator = 'V'
1045 If p_contra_ccy is null then
1046 Assumed to look for IR volatility
1047 Else
1048 Assumed to for FX volatility
1049
1050 The ordering priority of the data side (bid, ask, mid):
1051 1. Look at DATA_SIDE from XTR_RM_MD_SET_CURVES
1052 2. Case 1: If DATA_SIDE from XTR_RM_MD_SET_CURVES =
1053 'DEFAULT' then look at DATA_SIDE from XTR_RM_MD_CURVES
1054 Case 2 : If the DATA_SIDE from XTR_RM_MD_SET_CURVES =
1055 'BID/ASK' then look at p_side (passed parameter)
1056 3. If in Case 1 the DATA_SIDE from XTR_RM_MD_CURVES =
1057 'BID/ASK' then look at p_side (passed parameter).
1058
1059 p_ccy = currency.
1060 p_contra_ccy = contra currency. It is only required for
1061 volatility and FX spot rate.
1062 p_md_set_code = name of market data set from which to
1063 extract data.
1064 p_source = table source for calculation. 'C' for Current
1065 System Rates table and 'R' for revaluation table.
1066 p_indicator = data type of output. 'R' for yield rate,
1067 'D' for discount factor, 'V' for volatility.
1068 p_spot_date = reference date.
1069 p_future_date = future date.
1070 p_day_count_basis_out = day count basis to use for output.
1071 Can set to null and disregard if p_curve_code is
1072 volatility curve.
1073 p_interpolation_method = interpolation method to be used
1074 for curve. 'L'/'LINEAR' for linear, 'E'/'EXPON' for
1075 exponential, 'C'/'CUBIC' for cubic spline, or
1076 'D'/'DEFAULT' for the default value specified in the curve,
1077 otherwise it will be assumed to be 'L'.
1078 p_side = data side of market to return. 'B'/'BID' for bid,
1079 'A'/'ASK' for ask, or 'M'/'MID' for mid.
1080 No BID/ASK allowed.
1081 p_batch_id = batch of revaluation table to be used. Can
1082 set to null and disregard if p_source <> 'R'.
1083 p_bond_code = bond reference code. It is only required for
1084 bond price. Set to null and disregard if p_indicator <> 'B'.
1085 p_md_out = output that is yield rate, discount factor, or
1086 volatility.
1087 --------------------------------------------------------------------------*/
1088 PROCEDURE GET_MD_FROM_SET (p_in_rec IN md_from_set_in_rec_type,
1089 p_out_rec OUT NOCOPY md_from_set_out_rec_type,
1090 p_first_call IN NUMBER) is
1091
1092 CURSOR get_fx_spot_rates IS
1093 SELECT usd_base_curr_bid_rate bid_rate,
1094 usd_base_curr_offer_rate ask_rate,
1095 1/usd_base_curr_offer_rate bid_rate_base,
1096 1/usd_base_curr_bid_rate ask_rate_base,
1097 currency
1098 FROM xtr_spot_rates
1099 WHERE (rate_date, currency) IN (SELECT MAX(rate_date), currency
1100 FROM xtr_spot_rates
1101 WHERE currency IN (p_in_rec.p_ccy, p_in_rec.p_contra_ccy)
1102 AND currency <> 'USD'
1103 AND trunc(rate_date) <= trunc(p_in_rec.p_spot_date)
1104 GROUP BY currency);
1105
1106 --The cursor get spot rate in Commodity unit quote (USD based): bid/ask_rate
1107 --and Base unit quote: bid/ask_rate_base
1108 CURSOR get_fx_spot_rates_reval IS
1109 SELECT DECODE(currencyA, 'USD', nvl(bid_overwrite,bid),
1110 1/nvl(ask_overwrite,ask)) bid_rate,
1111 DECODE(currencyA, 'USD', nvl(ask_overwrite,ask),
1112 1/nvl(bid_overwrite,bid)) ask_rate,
1113 DECODE(currencyA, 'USD', 1/nvl(ask_overwrite,ask),
1114 nvl(bid_overwrite,bid)) bid_rate_base,
1115 DECODE(currencyA, 'USD', 1/nvl(bid_overwrite,bid),
1116 nvl(ask_overwrite,ask)) ask_rate_base,
1117 DECODE(currencyA, 'USD', currencyB, currencyA) currency
1118 --gives non-USD currency
1119 FROM xtr_revaluation_rates
1120 WHERE ((currencyA = 'USD' and currencyB
1121 IN (p_in_rec.p_ccy,p_in_rec.p_contra_ccy))
1122 or (currencyB = 'USD' and currencyA
1123 IN (p_in_rec.p_ccy,p_in_rec.p_contra_ccy)))
1124 AND volatility_or_rate = 'RATE'
1125 AND day_mth is NULL
1126 AND batch_id = p_in_rec.p_batch_id;
1127
1128 --get curve info for non FXV
1129 --do the functional logic to determine the side, BID-ASK, as explained
1130 --in MD API doc.
1131 CURSOR get_curve_code IS
1132 SELECT c.curve_code, DECODE(sc.data_side, 'DEFAULT', p_in_rec.p_side, 'BID/ASK', p_in_rec.p_side, sc.data_side) side,
1133 DECODE(sc.interpolation, 'DEFAULT', c.default_interpolation,
1134 sc.interpolation) interpolation
1135 FROM xtr_rm_md_set_curves sc, xtr_rm_md_curves c
1136 WHERE sc.set_code = p_in_rec.p_md_set_code
1137 AND c.type = DECODE(p_in_rec.p_indicator,'V','IRVOL', 'YIELD')
1138 AND c.ccy = p_in_rec.p_ccy
1139 AND sc.curve_code = c.curve_code;
1140
1141 --get curve info for FXV
1142 --do the functional logic to determine the side, BID-ASK, as explained
1143 --in MD API doc.
1144 CURSOR get_curve_code_v IS
1145 SELECT c.curve_code, DECODE(sc.data_side, 'DEFAULT', p_in_rec.p_side, 'BID/ASK', p_in_rec.p_side, sc.data_side) side,
1146 DECODE(sc.interpolation, 'DEFAULT', c.default_interpolation,
1147 sc.interpolation) interpolation
1148 FROM xtr_rm_md_set_curves sc, xtr_rm_md_curves c
1149 WHERE sc.set_code = p_in_rec.p_md_set_code
1150 AND c.type = 'FXVOL'
1151 AND ((c.ccy = p_in_rec.p_ccy AND c.contra_ccy = p_in_rec.p_contra_ccy)
1152 OR (c.ccy = p_in_rec.p_contra_ccy AND c.contra_ccy = p_in_rec.p_ccy))
1153 AND sc.curve_code = c.curve_code;
1154
1155 TYPE rec_type IS RECORD (bid_rate NUMBER, ask_rate NUMBER,
1156 bid_rate_base NUMBER, ask_rate_base NUMBER,
1157 currency VARCHAR2(15));
1158
1159 v_in_rec md_from_curve_in_rec_type;
1160 v_out_rec md_from_curve_out_rec_type;
1161 v_count NUMBER;
1162 v_ccy NUMBER;
1163 v_contra_ccy NUMBER;
1164 v_int VARCHAR2(20);
1165 temprec rec_type;
1166
1167 BEGIN
1168 --start debug and print some initial variable values
1169 IF xtr_risk_debug_pkg.g_Debug THEN
1170 xtr_risk_debug_pkg.dpush('XTR_MARKET_DATA_P.GET_MD_FROM_SET');
1171 xtr_risk_debug_pkg.dlog('GET_MD_FROM_SET: ' || 'p_md_set_code',p_in_rec.p_md_set_code);
1172 xtr_risk_debug_pkg.dlog('GET_MD_FROM_SET: ' || 'p_source',p_in_rec.p_source);
1173 xtr_risk_debug_pkg.dlog('GET_MD_FROM_SET: ' || 'p_indicator',p_in_rec.p_indicator);
1174 xtr_risk_debug_pkg.dlog('GET_MD_FROM_SET: ' || 'p_spot_date',p_in_rec.p_spot_date);
1175 xtr_risk_debug_pkg.dlog('GET_MD_FROM_SET: ' || 'p_future_date',p_in_rec.p_future_date);
1176 xtr_risk_debug_pkg.dlog('GET_MD_FROM_SET: ' || 'p_ccy',p_in_rec.p_ccy);
1177 xtr_risk_debug_pkg.dlog('GET_MD_FROM_SET: ' || 'p_contra_ccy',p_in_rec.p_contra_ccy);
1178 xtr_risk_debug_pkg.dlog('GET_MD_FROM_SET: ' || 'p_day_count_basis_out',p_in_rec.p_day_count_basis_out);
1179 xtr_risk_debug_pkg.dlog('GET_MD_FROM_SET: ' || 'p_interpolation_method',p_in_rec.p_interpolation_method);
1180 xtr_risk_debug_pkg.dlog('GET_MD_FROM_SET: ' || 'p_side',p_in_rec.p_side);
1181 xtr_risk_debug_pkg.dlog('GET_MD_FROM_SET: ' || 'p_batch_id',p_in_rec.p_batch_id);
1182 xtr_risk_debug_pkg.dlog('GET_MD_FROM_SET: ' || 'p_bond_code',p_in_rec.p_bond_code);
1183 END IF;
1184
1185 --Fork the process based on the type of rate
1186 IF (UPPER(p_in_rec.p_indicator) IN ('D','R','DR','Y') or
1187 (UPPER(p_in_rec.p_indicator) IN ('V') and
1188 p_in_rec.p_contra_ccy IS NULL)) THEN
1189 --for discount factor, ir volatility, and yield rate
1190
1191 --fetch curve code and call get_md_from_curve
1192 --fetching necessary info from curve and set
1193 OPEN get_curve_code;
1194 FETCH get_curve_code into v_in_rec.p_curve_code, v_in_rec.p_side,
1195 v_in_rec.p_interpolation_method;
1196 CLOSE get_curve_code;
1197
1198 --check if at least one curve code is retrieved
1199 IF (v_in_rec.p_curve_code IS NULL) THEN
1200 IF xtr_risk_debug_pkg.g_Debug THEN
1201 xtr_risk_debug_pkg.dlog('GET_MD_FROM_SET: ' || 'For Yield/Disc/IRVol: no curve found');
1202 xtr_risk_debug_pkg.dpop('XTR_MARKET_DATA_P.GET_MD_FROM_SET');
1203 END IF;
1204 RAISE e_mdcs_no_curve_found;
1205 END IF;
1206
1207 --check p_side if it's a valid one: cannot be DEFAULT or BID/ASK
1208 --if the result from set and curve is BID/ASK use the passed in parameter
1209 IF (v_in_rec.p_side NOT IN ('MID','BID','ASK')) THEN
1210 v_in_rec.p_side := p_in_rec.p_side;
1211 END IF;
1212
1213 --for interpolation method, the API parameter overwrite the one from the
1214 --table.
1215 IF (UPPER(p_in_rec.p_interpolation_method) NOT IN('D','DEFAULT')) THEN
1216 v_in_rec.p_interpolation_method := p_in_rec.p_interpolation_method;
1217 END IF;
1218 --if int. method is DEFAULT we took the one from above cursor
1219
1220 --pass other parameters and call get_market_data_from_curve
1221 v_in_rec.p_source := p_in_rec.p_source;
1222 v_in_rec.p_indicator := p_in_rec.p_indicator;
1223 v_in_rec.p_spot_date := p_in_rec.p_spot_date;
1224 v_in_rec.p_future_date := p_in_rec.p_future_date;
1225 v_in_rec.p_day_count_basis_out := p_in_rec.p_day_count_basis_out;
1226 v_in_rec.p_batch_id := p_in_rec.p_batch_id;
1227
1228 get_md_from_curve(v_in_rec, v_out_rec);
1229 p_out_rec.p_md_out := v_out_rec.p_md_out;
1230
1231 ELSIF (UPPER(p_in_rec.p_indicator) = 'V') THEN
1232 --for fx volatility
1233
1234 --fetch curve code and data side for fx volatility
1235 --fetching necessary info from curve and set
1236 OPEN get_curve_code_v;
1237 FETCH get_curve_code_v into v_in_rec.p_curve_code, v_in_rec.p_side,
1238 v_in_rec.p_interpolation_method;
1239 CLOSE get_curve_code_v;
1240
1241 --check if at least one curve code is retrieved
1242 IF (v_in_rec.p_curve_code IS NULL) THEN
1243 IF xtr_risk_debug_pkg.g_Debug THEN
1244 xtr_risk_debug_pkg.dlog('GET_MD_FROM_SET: ' || 'For FXVol: no curve found');
1245 xtr_risk_debug_pkg.dpop('GET_MD_FROM_SET: ' || 'XTR_MARKET_DATA_P.GET_MD_FROM_CURVE');
1246 END IF;
1247 RAISE e_mdcs_no_curve_found;
1248 END IF;
1249
1250 --check p_side if it's a valid one: cannot be DEFAULT or BID/ASK
1251 IF (v_in_rec.p_side NOT IN ('MID','BID','ASK')) THEN
1252 v_in_rec.p_side := p_in_rec.p_side;
1253 END IF;
1254
1255 --for interpolation method, the API parameter overwrite the one from the
1256 --table.
1257 IF (UPPER(p_in_rec.p_interpolation_method) NOT IN('D','DEFAULT')) THEN
1258 v_in_rec.p_interpolation_method := p_in_rec.p_interpolation_method;
1259 END IF;
1260 --if int. method is DEFAULT we took the one from above cursor
1261 /*
1262 IF (v_int <> 'D') THEN
1263 v_in_rec.p_interpolation_method := v_int;
1264 END IF;
1265 */
1266 --pass parameters and call get_market_data_from_curve
1267 --pass other parameters and call get_market_data_from_curve
1268 v_in_rec.p_source := p_in_rec.p_source;
1269 v_in_rec.p_indicator := p_in_rec.p_indicator;
1270 v_in_rec.p_spot_date := p_in_rec.p_spot_date;
1271 v_in_rec.p_future_date := p_in_rec.p_future_date;
1272 v_in_rec.p_day_count_basis_out := p_in_rec.p_day_count_basis_out;
1273 -- v_in_rec.p_interpolation_method := p_in_rec.p_interpolation_method;
1274 v_in_rec.p_batch_id := p_in_rec.p_batch_id;
1275 get_md_from_curve(v_in_rec, v_out_rec);
1276 p_out_rec.p_md_out := v_out_rec.p_md_out;
1277
1278
1279 ELSIF (UPPER(p_in_rec.p_indicator) IN ('S')) THEN
1280 --for FX spot rate
1281 --check p_side
1282 SELECT DECODE(fx_spot_side, 'BID/ASK', p_in_rec.p_side, fx_spot_side)
1283 INTO v_in_rec.p_side
1284 FROM xtr_rm_md_sets
1285 WHERE set_code = p_in_rec.p_md_set_code;
1286 --fetch the spot rate from xtr_spot_rates
1287 --check if cross rates
1288 v_count := 0;
1289 IF (p_in_rec.p_ccy <> 'USD' and p_in_rec.p_contra_ccy <> 'USD') THEN
1290 IF (p_in_rec.p_source = 'C') THEN
1291 OPEN get_fx_spot_rates;
1292 ELSIF (p_in_rec.p_source = 'R') THEN
1293 -- bug 4145664 issue 12
1294 IF nvl(p_first_call,0) = 1 then
1295 open get_fx_spot_rates;
1296 ELSE
1297 OPEN get_fx_spot_rates_reval;
1298 END IF;
1299 ELSE
1300 IF xtr_risk_debug_pkg.g_Debug THEN
1301 xtr_risk_debug_pkg.dlog('GET_MD_FROM_SET: ' || 'For FX Spot Rates non-USD: The p_source values can only be ''C'' or ''R''.');
1302 xtr_risk_debug_pkg.dpop('GET_MD_FROM_SET: ' || 'XTR_MARKET_DATA_P.GET_MD_FROM_CURVE');
1303 END IF;
1304 RAISE_APPLICATION_ERROR
1305 (-20001,'The p_source values can only be ''C'' or ''R''.');
1306 END IF;
1307 LOOP
1308 IF (p_in_rec.p_source = 'C') THEN
1309 FETCH get_fx_spot_rates INTO temprec.bid_rate, temprec.ask_rate,
1310 temprec.bid_rate_base, temprec.ask_rate_base,
1311 temprec.currency;
1312 ELSE
1313 -- bug 4145664 issue 12 if the hedgde is revalued for the first time rates to be picked from
1314 -- xtr_spot_rates table
1315 IF nvl(p_first_call,0) = 1 then
1316 FETCH get_fx_spot_rates INTO temprec.bid_rate, temprec.ask_rate,
1317 temprec.bid_rate_base, temprec.ask_rate_base,
1318 temprec.currency;
1319 ELSE
1320 FETCH get_fx_spot_rates_reval INTO temprec.bid_rate,
1321 temprec.ask_rate, temprec.bid_rate_base,
1322 temprec.ask_rate_base, temprec.currency;
1323 END IF;
1324 END IF;
1325
1326 IF (temprec.currency = p_in_rec.p_ccy) THEN
1327 IF (v_in_rec.p_side IN ('BID','B')) THEN
1328 v_ccy := temprec.ask_rate;
1329 ELSIF (v_in_rec.p_side IN ('ASK','A')) THEN
1330 v_ccy := temprec.bid_rate;
1331 ELSE
1332 v_ccy := (temprec.bid_rate+temprec.ask_rate)/2;
1333 END IF;
1334 ELSE
1335 IF (v_in_rec.p_side IN ('BID','B')) THEN
1336 v_contra_ccy := temprec.bid_rate;
1337 ELSIF (v_in_rec.p_side IN ('ASK','A')) THEN
1338 v_contra_ccy := temprec.ask_rate;
1339 ELSE
1340 v_contra_ccy := (temprec.bid_rate+temprec.ask_rate)/2;
1341 END IF;
1342 END IF;
1343 v_count := v_count+1;
1344 IF (v_count >= 2) THEN EXIT;
1345 END IF;
1346 END LOOP;
1347 IF (p_in_rec.p_source = 'C') THEN
1348 CLOSE get_fx_spot_rates;
1349 ELSE
1350 if get_fx_spot_rates%ISOPEN then
1351 CLOSE get_fx_spot_rates;
1352 end if;
1353 if get_fx_spot_rates_reval%ISOPEN then
1354 CLOSE get_fx_spot_rates_reval;
1355 end if;
1356 END IF;
1357 /*
1358 --check whether there is any spot rate retrieved
1359 IF (v_count = 0) THEN
1360 IF xtr_risk_debug_pkg.g_Debug THEN
1361 xtr_risk_debug_pkg.dlog('GET_MD_FROM_SET: ' || 'For FX Spot Rates non-USD: no data found');
1362 xtr_risk_debug_pkg.dpop('XTR_MARKET_DATA_P.GET_MD_FROM_SET');
1363 END IF;
1364 RAISE e_mdcs_no_data_found;
1365 END IF;
1366 */
1367 --since cross rates, use fx_spot_rates formula
1368 xtr_fx_formulas.fx_spot_rate(p_in_rec.p_contra_ccy, p_in_rec.p_ccy,
1369 v_contra_ccy, v_ccy, 'C', 'C',
1370 p_out_rec.p_md_out);
1371 ELSE
1372 IF (p_in_rec.p_source = 'C') THEN
1373 OPEN get_fx_spot_rates;
1374 ELSIF (p_in_rec.p_source = 'R') THEN
1375
1376 -- bug 4145664 issue 12 when either base/contra currency is USD
1377 IF nvl(p_first_call,0) = 1 THEN
1378 OPEN get_fx_spot_rates;
1379 ELSE
1380 OPEN get_fx_spot_rates_reval;
1381 END IF;
1382 ELSE
1383 IF xtr_risk_debug_pkg.g_Debug THEN
1384 xtr_risk_debug_pkg.dlog('GET_MD_FROM_SET: ' || 'For FX Spot Rates USD: the p_source values can only be ''C'' or ''R''.');
1385 xtr_risk_debug_pkg.dpop('XTR_MARKET_DATA_P.GET_MD_FROM_SET');
1386 END IF;
1387 RAISE_APPLICATION_ERROR
1388 (-20001,'The p_source values can only be ''C'' or ''R''.');
1389 END IF;
1390 LOOP
1391 IF (p_in_rec.p_source = 'C') THEN
1392 FETCH get_fx_spot_rates INTO temprec.bid_rate, temprec.ask_rate,
1393 temprec.bid_rate_base, temprec.ask_rate_base,
1394 temprec.currency;
1395 ELSE
1396 -- bug 4145664 issue 12
1397 IF nvl(p_first_call,0) = 1 THEN
1398 FETCH get_fx_spot_rates INTO temprec.bid_rate, temprec.ask_rate,
1399 temprec.bid_rate_base, temprec.ask_rate_base,
1400 temprec.currency;
1401 ELSE
1402 FETCH get_fx_spot_rates_reval INTO temprec.bid_rate,
1403 temprec.ask_rate, temprec.bid_rate_base,
1404 temprec.ask_rate_base, temprec.currency;
1405 END IF;
1406 END IF;
1407 IF (p_in_rec.p_ccy <> 'USD') THEN
1408 IF (v_in_rec.p_side IN ('BID','B')) THEN
1409 v_ccy := temprec.bid_rate_base;
1410 ELSIF (v_in_rec.p_side IN ('ASK','A')) THEN
1411 v_ccy := temprec.ask_rate_base;
1412 ELSE
1413 v_ccy := (temprec.bid_rate_base+temprec.ask_rate_base)/2;
1414 END IF;
1415 p_out_rec.p_md_out := v_ccy;
1416 ELSE
1417 IF (v_in_rec.p_side IN ('BID','B')) THEN
1418 v_ccy := temprec.bid_rate;
1419 ELSIF (v_in_rec.p_side IN ('ASK','A')) THEN
1420 v_ccy := temprec.ask_rate;
1421 ELSE
1422 v_ccy := (temprec.bid_rate+temprec.ask_rate)/2;
1423 END IF;
1424 p_out_rec.p_md_out := v_ccy;
1425 END IF;
1426 v_count := v_count+1;
1427 IF (v_count >= 1) THEN EXIT;
1428 END IF;
1429 END LOOP;
1430 IF (p_in_rec.p_source = 'C') THEN
1431 CLOSE get_fx_spot_rates;
1432 ELSE
1433 if get_fx_spot_rates%ISOPEN then
1434 CLOSE get_fx_spot_rates;
1435 end if;
1436 if get_fx_spot_rates_reval%ISOPEN then
1437 CLOSE get_fx_spot_rates_reval;
1438 end if;
1439 END IF;
1440 /*
1441 --check whether there is any spot rate retrieved
1442 IF (v_count = 0) THEN
1443 IF xtr_risk_debug_pkg.g_Debug THEN
1444 xtr_risk_debug_pkg.dlog('GET_MD_FROM_SET: ' || 'For FX Spot Rates USD: no data found');
1445 xtr_risk_debug_pkg.dpop('XTR_MARKET_DATA_P.GET_MD_FROM_SET');
1446 END IF;
1447 RAISE e_mdcs_no_data_found;
1448 END IF;
1449 */
1450 END IF;
1451
1452 ELSIF (UPPER(p_in_rec.p_indicator) = 'B') THEN
1453 --for bond price
1454 --check p_side
1455 SELECT DECODE(bond_price_side, 'BID/ASK', p_in_rec.p_side, bond_price_side)
1456 INTO v_in_rec.p_side
1457 FROM xtr_rm_md_sets
1458 WHERE set_code = p_in_rec.p_md_set_code;
1459
1460 IF (p_in_rec.p_source = 'R') THEN
1461 SELECT DECODE(v_in_rec.p_side, 'BID', nvl(bid_overwrite,bid),
1462 'A', nvl(ask_overwrite,ask),
1463 'ASK', nvl(ask_overwrite,ask),
1464 'B', nvl(bid_overwrite,bid),
1465 (nvl(ask_overwrite,ask)+nvl(bid_overwrite,bid))/2)
1466 INTO p_out_rec.p_md_out
1467 FROM xtr_revaluation_rates
1468 WHERE reval_type = p_in_rec.p_bond_code
1469 AND volatility_or_rate = 'PRIC'
1470 AND day_mth IS NULL
1471 AND batch_id = p_in_rec.p_batch_id;
1472 ELSE
1473 --fetch bond price from xtr_interest_period_rates
1474 SELECT DECODE(v_in_rec.p_side,
1475 'ASK', offer_rate,
1476 'A', offer_rate,
1477 'BID', bid_rate,
1478 'B', bid_rate,
1479 (bid_rate+offer_rate)/2)
1480 INTO p_out_rec.p_md_out
1481 FROM xtr_interest_period_rates
1482 WHERE (rate_date, unique_period_id) IN
1483 (SELECT MAX(rate_date), unique_period_id
1484 FROM xtr_interest_period_rates
1485 WHERE trunc(rate_date) <= trunc(p_in_rec.p_spot_date)
1486 AND unique_period_id = p_in_rec.p_bond_code
1487 GROUP BY unique_period_id)
1488 AND term_type = 'B';
1489
1490 END IF;
1491 --check whether there is any spot rate retrieved
1492 IF (p_out_rec.p_md_out IS NULL) THEN
1493 IF xtr_risk_debug_pkg.g_Debug THEN
1494 xtr_risk_debug_pkg.dlog('GET_MD_FROM_SET: ' || 'For Bond: no data found');
1495 xtr_risk_debug_pkg.dpop('XTR_MARKET_DATA_P.GET_MD_FROM_SET');
1496 END IF;
1497 RAISE e_mdcs_no_data_found;
1498 END IF;
1499
1500
1501
1502 ELSIF (UPPER(p_in_rec.p_indicator) = 'T') THEN -- jhung STOCK is added
1503 --for stock price
1504 --check p_side
1505 SELECT DECODE(stock_price_side, 'BID/ASK', p_in_rec.p_side, stock_price_side)
1506 INTO v_in_rec.p_side
1507 FROM xtr_rm_md_sets
1508 WHERE set_code = p_in_rec.p_md_set_code;
1509
1510 IF (p_in_rec.p_source = 'R') THEN
1511 SELECT DECODE(v_in_rec.p_side, 'BID', nvl(bid_overwrite,bid),
1512 'A', nvl(ask_overwrite,ask),
1513 'ASK', nvl(ask_overwrite,ask),
1514 'B', nvl(bid_overwrite,bid),
1515 (nvl(ask_overwrite,ask)+nvl(bid_overwrite,bid))/2)
1516 INTO p_out_rec.p_md_out
1517 FROM xtr_revaluation_rates
1518 WHERE reval_type = p_in_rec.p_bond_code
1519 AND volatility_or_rate = 'PRIC'
1520 AND day_mth IS NULL
1521 AND batch_id = p_in_rec.p_batch_id;
1522 ELSE
1523 --fetch stock price from xtr_interest_period_rates
1524 SELECT DECODE(v_in_rec.p_side,
1525 'ASK', offer_rate,
1526 'A', offer_rate,
1527 'BID', bid_rate,
1528 'B', bid_rate,
1529 (bid_rate+offer_rate)/2)
1530 INTO p_out_rec.p_md_out
1531 FROM xtr_interest_period_rates
1532 WHERE (rate_date, unique_period_id) IN
1533 (SELECT MAX(rate_date), unique_period_id
1534 FROM xtr_interest_period_rates
1535 WHERE trunc(rate_date) <= trunc(p_in_rec.p_spot_date)
1536 AND unique_period_id = p_in_rec.p_bond_code
1537 GROUP BY unique_period_id)
1538 AND term_type = 'T';
1539
1540 END IF;
1541 --check whether there is any spot rate retrieved
1542 IF (p_out_rec.p_md_out IS NULL) THEN
1543 IF xtr_risk_debug_pkg.g_Debug THEN
1544 xtr_risk_debug_pkg.dlog('GET_MD_FROM_SET: ' || 'For Stock: no data found');
1545 xtr_risk_debug_pkg.dpop('XTR_MARKET_DATA_P.GET_MD_FROM_SET');
1546 END IF;
1547 RAISE e_mdcs_no_data_found;
1548 END IF;
1549
1550 ELSE
1551 IF xtr_risk_debug_pkg.g_Debug THEN
1552 xtr_risk_debug_pkg.dlog('GET_MD_FROM_SET: ' || 'p_indicator is invalid');
1553 xtr_risk_debug_pkg.dpop('XTR_MARKET_DATA_P.GET_MD_FROM_SET');
1554 END IF;
1555 RAISE_APPLICATION_ERROR
1556 (-20001,'Unknown p_indicator values.');
1557 END IF;
1558
1559 --stop debug and print the result
1560 IF xtr_risk_debug_pkg.g_Debug THEN
1561 xtr_risk_debug_pkg.dlog('GET_MD_FROM_SET: ' || 'p_out_rec.p_md_out', p_out_rec.p_md_out);
1562 xtr_risk_debug_pkg.dpop('XTR_MARKET_DATA_P.GET_MD_FROM_SET');
1563 END IF;
1564 END GET_MD_FROM_SET;
1565
1566
1567
1568 /*-------------------------------------------------------------------------
1569 GET_FX_FORWARD_FROM_SET
1570 Returns an FX Forward rate of a given market
1571 data set.
1572
1573 All parameters, record types, ordering priorities,
1574 and their definitions are the same as those of
1575 -------------------------------------------------------------------------*/
1576 PROCEDURE GET_FX_FORWARD_FROM_SET (p_in_rec IN md_from_set_in_rec_type,
1577 p_out_rec OUT NOCOPY md_from_set_out_rec_type) is
1578
1579 v_in_rec md_from_set_in_rec_type;
1580 v_out_rec md_from_set_out_rec_type;
1581 v_hol_in_rec following_holiday_in_rec_type;
1582 v_hol_out_rec following_holiday_out_rec_type;
1583 v_base_rate NUMBER;
1584 v_contra_rate NUMBER;
1585 v_spot_rate NUMBER;
1586 v_fx_forw_base NUMBER;
1587 v_fx_forw_contra NUMBER;
1588 v_day_count NUMBER;
1589 v_annual_basis NUMBER;
1590 v_future_date_usd DATE;
1591 v_future_date_base DATE;
1592 v_future_date_contra DATE;
1593 v_usd_ir NUMBER := NULL;
1594 v_level VARCHAR2(2) := ' ';
1595 v_err_code NUMBER(8);
1596
1597 BEGIN
1598 IF xtr_risk_debug_pkg.g_Debug THEN
1599 xtr_risk_debug_pkg.dpush('GET_FX_FORWARD_FROM_SET');
1600 xtr_risk_debug_pkg.dlog('GET_FX_FORWARD_FROM_SET: ' || 'p_md_set_code',p_in_rec.p_md_set_code);
1601 xtr_risk_debug_pkg.dlog('GET_FX_FORWARD_FROM_SET: ' || 'p_source',p_in_rec.p_source);
1602 xtr_risk_debug_pkg.dlog('GET_FX_FORWARD_FROM_SET: ' || 'p_spot_date',p_in_rec.p_spot_date);
1603 xtr_risk_debug_pkg.dlog('GET_FX_FORWARD_FROM_SET: ' || 'p_future_date',p_in_rec.p_future_date);
1604 xtr_risk_debug_pkg.dlog('GET_FX_FORWARD_FROM_SET: ' || 'p_ccy',p_in_rec.p_ccy);
1605 xtr_risk_debug_pkg.dlog('GET_FX_FORWARD_FROM_SET: ' || 'p_contra_ccy',p_in_rec.p_contra_ccy);
1606 xtr_risk_debug_pkg.dlog('GET_FX_FORWARD_FROM_SET: ' || 'p_day_count_basis_out',p_in_rec.p_day_count_basis_out);
1607 xtr_risk_debug_pkg.dlog('GET_FX_FORWARD_FROM_SET: ' || 'p_interpolation_method',p_in_rec.p_interpolation_method);
1608 xtr_risk_debug_pkg.dlog('GET_FX_FORWARD_FROM_SET: ' || 'p_side',p_in_rec.p_side);
1609 END IF;
1610
1611 --calc v_day_count and v_annual_basis
1612 xtr_calc_p.calc_days_run_c(p_in_rec.p_spot_date, p_in_rec.p_future_date,
1613 p_in_rec.p_day_count_basis_out, null, v_day_count,
1614 v_annual_basis);
1615
1616 --transfer all the common parameters to the new record
1617 v_in_rec.p_source := p_in_rec.p_source;
1618 v_in_rec.p_spot_date := p_in_rec.p_spot_date;
1619 v_in_rec.p_batch_id := p_in_rec.p_batch_id;
1620 v_in_rec.p_interpolation_method := p_in_rec.p_interpolation_method;
1621 v_in_rec.p_day_count_basis_out := p_in_rec.p_day_count_basis_out;
1622 v_in_rec.p_md_set_code := p_in_rec.p_md_set_code;
1623
1624 --find USD1Y ISDA Modified Follwing Bus Day Conv future date
1625 xtr_fps3_p.chk_holiday(p_in_rec.p_future_date,
1626 'USD', v_err_code, v_level);
1627 --check if v_future_date falls on holiday, if so call
1628 --following_holiday
1629 IF (v_err_code IS NOT NULL) THEN
1630 v_hol_in_rec.p_future_date := p_in_rec.p_future_date;
1631 v_hol_in_rec.p_currency := 'USD';
1632 v_hol_in_rec.p_term_type := 'Y';
1633 following_holiday(v_hol_in_rec, v_hol_out_rec);
1634 v_future_date_usd := v_hol_out_rec.p_date_out;
1635 ELSE
1636 v_future_date_usd := p_in_rec.p_future_date;
1637 END IF;
1638
1639 --find ISDA Modified Follwing Bus Day Conv future date for non USD BASE CCY
1640 IF (p_in_rec.p_ccy <> 'USD') THEN
1641 --find base_ccy1Y ISDA Modified Following Bus Day Conv future date
1642 xtr_fps3_p.chk_holiday(p_in_rec.p_future_date,
1643 p_in_rec.p_ccy, v_err_code, v_level);
1644 --check if v_future_date falls on holiday, if so call
1645 --following_holiday
1646 IF (v_err_code IS NOT NULL) THEN
1647 v_hol_in_rec.p_future_date := p_in_rec.p_future_date;
1648 v_hol_in_rec.p_currency := p_in_rec.p_ccy;
1649 v_hol_in_rec.p_term_type := 'Y';
1650 following_holiday(v_hol_in_rec, v_hol_out_rec);
1651 v_future_date_base := v_hol_out_rec.p_date_out;
1652 END IF;
1653 ELSE
1654 v_future_date_base := v_future_date_usd;
1655 END IF;
1656 --find ISDA Modified Follwing Bus Day Conv future date for non USD CONTRA CCY
1657 IF (p_in_rec.p_contra_ccy <> 'USD') THEN
1658 --find contra_ccy1Y ISDA Modified Follwing Bus Day Conv future date
1659 xtr_fps3_p.chk_holiday(p_in_rec.p_future_date,
1660 p_in_rec.p_contra_ccy, v_err_code, v_level);
1661 --check if v_future_date falls on holiday, if so call
1662 --following_holiday
1663 IF (v_err_code IS NOT NULL) THEN
1664 v_hol_in_rec.p_future_date := p_in_rec.p_future_date;
1665 v_hol_in_rec.p_currency := p_in_rec.p_contra_ccy;
1666 v_hol_in_rec.p_term_type := 'Y';
1667 following_holiday(v_hol_in_rec, v_hol_out_rec);
1668 v_future_date_contra := v_hol_out_rec.p_date_out;
1669 END IF;
1670 ELSE
1671 v_future_date_contra := v_future_date_usd;
1672 END IF;
1673
1674 --For FX Forward BID
1675 IF (UPPER(p_in_rec.p_side) IN ('B','BID')) THEN
1676 --find the base FX Forward BID ask
1677 IF (p_in_rec.p_ccy <> 'USD') THEN
1678 --find the interest rate for the base currency
1679 v_in_rec.p_side := 'B';
1680 v_in_rec.p_indicator := 'R';
1681 v_in_rec.p_ccy := 'USD';
1682 v_in_rec.p_future_date := v_future_date_usd;
1683 get_md_from_set(v_in_rec, v_out_rec);
1684 v_base_rate := v_out_rec.p_md_out;
1685 --find the interest rate for the contra currency
1686 v_in_rec.p_future_date := v_future_date_base;
1687 v_in_rec.p_side := 'A';
1688 v_in_rec.p_indicator := 'R';
1689 v_in_rec.p_ccy := p_in_rec.p_ccy;
1690 get_md_from_set(v_in_rec, v_out_rec);
1691 v_contra_rate := v_out_rec.p_md_out;
1692 --find the spot rate
1693 v_in_rec.p_side := 'A';
1694 v_in_rec.p_indicator := 'S';
1695 v_in_rec.p_ccy := 'USD';
1696 v_in_rec.p_contra_ccy := p_in_rec.p_ccy;
1697 get_md_from_set(v_in_rec, v_out_rec);
1698 v_spot_rate := v_out_rec.p_md_out;
1699 xtr_fx_formulas.fx_forward_rate(v_spot_rate, v_base_rate, v_contra_rate,
1700 v_day_count, v_day_count, v_annual_basis,
1701 v_annual_basis, v_fx_forw_base);
1702
1703 ELSE
1704 v_fx_forw_base := 1;
1705 END IF;
1706 --find the FX Forward Contra BID bid
1707 IF (p_in_rec.p_contra_ccy <> 'USD') THEN
1708 --First find the interest rate for the base currency
1709 v_in_rec.p_side := 'A';
1710 v_in_rec.p_indicator := 'R';
1711 v_in_rec.p_ccy := 'USD';
1712 v_in_rec.p_future_date := v_future_date_usd;
1713 get_md_from_set(v_in_rec, v_out_rec);
1714 v_base_rate := v_out_rec.p_md_out;
1715 --find the interest rate for the contra currency
1716 v_in_rec.p_future_date := v_future_date_contra;
1717 v_in_rec.p_side := 'B';
1718 v_in_rec.p_indicator := 'R';
1719 v_in_rec.p_ccy := p_in_rec.p_contra_ccy;
1720 get_md_from_set(v_in_rec, v_out_rec);
1721 v_contra_rate := v_out_rec.p_md_out;
1722 --find the spot rate
1723 v_in_rec.p_side := 'B';
1724 v_in_rec.p_indicator := 'S';
1725 v_in_rec.p_ccy := 'USD';
1726 v_in_rec.p_contra_ccy := p_in_rec.p_contra_ccy;
1727 get_md_from_set(v_in_rec, v_out_rec);
1728 v_spot_rate := v_out_rec.p_md_out;
1729 xtr_fx_formulas.fx_forward_rate(v_spot_rate, v_base_rate, v_contra_rate,
1730 v_day_count, v_day_count, v_annual_basis,
1731 v_annual_basis, v_fx_forw_contra);
1732 ELSE
1733 v_fx_forw_contra := 1;
1734 END IF;
1735 p_out_rec.p_md_out := v_fx_forw_contra/v_fx_forw_base;
1736 ELSIF (UPPER(p_in_rec.p_side) IN ('A','ASK')) THEN
1737 --for FX Forward ASK
1738 --find the base FX Forward ASK bid
1739 IF (p_in_rec.p_ccy <> 'USD') THEN
1740 --First find the interest rate for the base currency
1741 v_in_rec.p_side := 'A';
1742 v_in_rec.p_indicator := 'R';
1743 v_in_rec.p_ccy := 'USD';
1744 v_in_rec.p_future_date := v_future_date_usd;
1745 get_md_from_set(v_in_rec, v_out_rec);
1746 v_base_rate := v_out_rec.p_md_out;
1747 --find the interest rate for the contra currency
1748 v_in_rec.p_future_date := v_future_date_base;
1749 v_in_rec.p_side := 'B';
1750 v_in_rec.p_indicator := 'R';
1751 v_in_rec.p_ccy := p_in_rec.p_ccy;
1752 get_md_from_set(v_in_rec, v_out_rec);
1753 v_contra_rate := v_out_rec.p_md_out;
1754 --find the interest rate for the spot rate
1755 v_in_rec.p_side := 'B';
1756 v_in_rec.p_indicator := 'S';
1757 v_in_rec.p_ccy := 'USD';
1758 v_in_rec.p_contra_ccy := p_in_rec.p_ccy;
1759 get_md_from_set(v_in_rec, v_out_rec);
1760 v_spot_rate := v_out_rec.p_md_out;
1761 xtr_fx_formulas.fx_forward_rate(v_spot_rate, v_base_rate, v_contra_rate,
1762 v_day_count, v_day_count, v_annual_basis,
1763 v_annual_basis, v_fx_forw_base);
1764 ELSE
1765 v_fx_forw_base := 1;
1766 END IF;
1767 --find the FX Forward Contra ASK ask
1768 IF (p_in_rec.p_contra_ccy <> 'USD') THEN
1769 --find the interest rate for the base currency
1770 v_in_rec.p_side := 'B';
1771 v_in_rec.p_indicator := 'R';
1772 v_in_rec.p_ccy := 'USD';
1773 v_in_rec.p_future_date := v_future_date_usd;
1774 get_md_from_set(v_in_rec, v_out_rec);
1775 v_base_rate := v_out_rec.p_md_out;
1776 --find the interest rate for the contra currency
1777 v_in_rec.p_future_date := v_future_date_contra;
1778 v_in_rec.p_side := 'A';
1779 v_in_rec.p_indicator := 'R';
1780 v_in_rec.p_ccy := p_in_rec.p_contra_ccy;
1781 get_md_from_set(v_in_rec, v_out_rec);
1782 v_contra_rate := v_out_rec.p_md_out;
1783 --find the spot rate
1784 v_in_rec.p_side := 'A';
1785 v_in_rec.p_indicator := 'S';
1786 v_in_rec.p_ccy := 'USD';
1787 v_in_rec.p_contra_ccy := p_in_rec.p_contra_ccy;
1788 get_md_from_set(v_in_rec, v_out_rec);
1789 v_spot_rate := v_out_rec.p_md_out;
1790 xtr_fx_formulas.fx_forward_rate(v_spot_rate, v_base_rate, v_contra_rate,
1791 v_day_count, v_day_count, v_annual_basis,
1792 v_annual_basis, v_fx_forw_contra);
1793 ELSE
1794 v_fx_forw_contra := 1;
1795 END IF;
1796 p_out_rec.p_md_out := v_fx_forw_contra/v_fx_forw_base;
1797 ELSE
1798 --for FX Forward MID
1799 --find the base FX Forward MID mid
1800 IF (p_in_rec.p_ccy <> 'USD') THEN
1801 --find the interest rate for the base currency
1802 v_in_rec.p_side := 'M';
1803 v_in_rec.p_indicator := 'R';
1804 v_in_rec.p_ccy := 'USD';
1805 v_in_rec.p_future_date := v_future_date_usd;
1806 get_md_from_set(v_in_rec, v_out_rec);
1807 v_base_rate := v_out_rec.p_md_out;
1808 v_usd_ir := v_out_rec.p_md_out;
1809 --find the interest rate for the contra currency
1810 v_in_rec.p_future_date := v_future_date_base;
1811 v_in_rec.p_side := 'M';
1812 v_in_rec.p_indicator := 'R';
1813 v_in_rec.p_ccy := p_in_rec.p_ccy;
1814 get_md_from_set(v_in_rec, v_out_rec);
1815 v_contra_rate := v_out_rec.p_md_out;
1816 --find the spot rate
1817 v_in_rec.p_side := 'M';
1818 v_in_rec.p_indicator := 'S';
1819 v_in_rec.p_ccy := 'USD';
1820 v_in_rec.p_contra_ccy := p_in_rec.p_ccy;
1821 get_md_from_set(v_in_rec, v_out_rec);
1822 v_spot_rate := v_out_rec.p_md_out;
1823 xtr_fx_formulas.fx_forward_rate(v_spot_rate, v_base_rate, v_contra_rate,
1824 v_day_count, v_day_count, v_annual_basis,
1825 v_annual_basis, v_fx_forw_base);
1826 ELSE
1827 v_fx_forw_base := 1;
1828 END IF;
1829 --find the FX Forward Contra MID mid
1830 IF (p_in_rec.p_contra_ccy <> 'USD') THEN
1831 --find the interest rate for the base currency
1832 IF (v_usd_ir IS NULL) THEN
1833 v_in_rec.p_side := 'M';
1834 v_in_rec.p_indicator := 'R';
1835 v_in_rec.p_ccy := 'USD';
1836 v_in_rec.p_future_date := v_future_date_usd;
1837 get_md_from_set(v_in_rec, v_out_rec);
1838 v_base_rate := v_out_rec.p_md_out;
1839 ELSE
1840 v_base_rate := v_usd_ir;
1841 END IF;
1842 --find the interest rate for the contra currency
1843 v_in_rec.p_side := 'M';
1844 v_in_rec.p_indicator := 'R';
1845 v_in_rec.p_ccy := p_in_rec.p_contra_ccy;
1846 v_in_rec.p_future_date := v_future_date_contra;
1847 get_md_from_set(v_in_rec, v_out_rec);
1848 v_contra_rate := v_out_rec.p_md_out;
1849 --find the interest rate for the spot rate
1850 v_in_rec.p_side := 'M';
1851 v_in_rec.p_indicator := 'S';
1852 v_in_rec.p_ccy := 'USD';
1853 v_in_rec.p_contra_ccy := p_in_rec.p_contra_ccy;
1854 get_md_from_set(v_in_rec, v_out_rec);
1855 v_spot_rate := v_out_rec.p_md_out;
1856 xtr_fx_formulas.fx_forward_rate(v_spot_rate, v_base_rate, v_contra_rate,
1857 v_day_count, v_day_count, v_annual_basis,
1858 v_annual_basis, v_fx_forw_contra);
1859 ELSE
1860 v_fx_forw_contra := 1;
1861 END IF;
1862 p_out_rec.p_md_out := v_fx_forw_contra/v_fx_forw_base;
1863 END IF;
1864
1865 --stop debug and print result
1866 IF xtr_risk_debug_pkg.g_Debug THEN
1867 xtr_risk_debug_pkg.dlog('GET_FX_FORWARD_FROM_SET: ' || 'p_out_rec.p_md_out', p_out_rec.p_md_out);
1868 xtr_risk_debug_pkg.dpop('GET_FX_FORWARD_FROM_SET');
1869 END IF;
1870
1871 END GET_FX_FORWARD_FROM_SET;
1872
1873
1874
1875 END;
[Home] [Help]