1 PACKAGE BODY XTR_FX_FORMULAS AS
2 /* $Header: xtrfxflb.pls 120.5 2005/06/29 08:07:57 badiredd ship $ */
3
4 /*************************FX FORWARD************************************/
5
6 /*-----------------------------------------------------------------------
7 Calculates the FX Spot Rate for different currencies exchange.
8
9 Formula:
10 * If the BASIS_CONTRA/BASE is not 'C' (Commodity Unit Quote) then convert with FX FORWARD (HLD) Formula 1
11 * Check CURRENCY_CONTRA and CURRENCY_BASE if cross-currency pair is involved then use FX FORWARD (HLD) Formula 2
12
13 Formula 1 is converting from Base Unit Quote to Commodity Unit Quote, and vice versa.
14 Formula 2 is to calculate the SPOT RATE(=Cross Rate)
15
16 Assumption: p_RATE_CONTRA and p_RATE_BASE have the same day count basis.
17
18 For IRS: BASE = Receive Leg
19 CONTRA = Pay Leg
20
21 Example for FX:CHFGBP -> CHF = Base Currency
22 GBP = Contra Currency
23
24 IF there is a notion of BID and ASK then:
25 To find p_SPOT_RATE (BID/ASK):
26 FOR CONTRA:
27 IF p_BASIS_CONTRA = 'C' THEN
28 p_RATE_CONTRA (BID/ASK) = BID/ASK Rate of
29 Contra Currency
30 ELSE
31 p_RATE_CONTRA (BID/ASK) = ASK/BID Rate of
32 Contra Currency
33 FOR BASE:
34 IF p_BASIS_BASE = 'C' THEN
35 p_RATE_BASE (BID/ASK) = ASK/BID Rate of Base
36 Currency
37 ELSE
38 p_RATE_BASE (BID/ASK) = BID/ASK Rate of Base
39 Currency
40
41 * p_RATE_CONTRA/BASE = FX rate of the contra/base side against USD
42 (p_RATE_CONTRA = Rate vs. USD Contra, p_RATE_ BASE = Rate vs. USD Base).
43 If the currency is USD then p_RATE = 1;
44 * p_CURRENCY_CONTRA/BASE = the currency for contra/base.
45 * p_BASIS_CONTRA/BASE indicates the quotation basis against USD for the CONTRA
46 BASE side, 'C' for Commodity Unit Quote (=USDGBP) and 'B' for Base Unit Quote=
47 GBPUSD) (Definitions are in FX Calculator HLD)
48 * p_SPOT_RATE = fair exchange rate of two different currencies.
49 -----------------------------------------------------------------------*/
50
51 PROCEDURE FX_SPOT_RATE (p_currency_contra IN VARCHAR2,
52 p_currency_base IN VARCHAR2,
53 p_rate_contra IN NUMBER,
54 p_rate_base IN NUMBER,
55 p_basis_contra IN CHAR,
56 p_basis_base IN CHAR,
57 p_spot_rate IN OUT NOCOPY NUMBER) is
58
59 e_basis_code EXCEPTION;
60 e_USD_rate EXCEPTION;
61 v_rate_c NUMBER;
62 v_rate_b NUMBER;
63
64 BEGIN
65
66 --CHeck whether the indicator is correct
67 IF (p_basis_contra not IN ('C','B','c','b') or
68 p_basis_base not IN ( 'C','B','c','b')) THEN
69
70 -- FND_MESSAGE.Set_Name('XTR', 'XTR_1059');
71 -- APP_EXCEPTION.raise_exception;
72
73 RAISE e_basis_code;
74 END IF;
75
76 --Check if currency USD then rate has to be 1
77 IF (((UPPER(p_currency_contra)= 'USD') and (p_rate_contra <> 1)) or
78 ((UPPER(p_currency_base)= 'USD') and (p_rate_base <> 1))) THEN
79 RAISE e_USD_rate;
80 END IF;
81
82 --Use Formula 1 to convert the rate to Commodity Unit Quote
83 --
84 IF (p_basis_contra = 'B') THEN
85 v_rate_c := 1/p_rate_contra;
86 ELSE
87 v_rate_c := p_rate_contra;
88 END IF;
89
90 IF (p_basis_base = 'B') THEN
91 v_rate_b := 1/p_rate_base;
92 ELSE
93 v_rate_b := p_rate_base;
94 END IF;
95
96 --Use Formula 2 in FX Forward HLD,
97 --if the exchange involves 2 non USD currencies.
98 --If it involves USD we can still use Formula 2 with the USD RATE = 1.
99 --
100 p_spot_rate := v_rate_c/v_rate_b;
101
102 EXCEPTION
103 WHEN e_basis_code THEN
104 -- dbms_output.put_line('The basis_contra/base values can only be ''C'' or ''B''.');
105 RAISE_APPLICATION_ERROR
106 (-20001,'The basis_contra/base values can only be ''C'' or ''B''.');
107 WHEN e_USD_rate THEN
108 RAISE_APPLICATION_ERROR
109 (-20002,'If currency is USD then the rate = 1.');
110
111 END FX_SPOT_RATE;
112
113
114 /*-------------------------------------------------------------------------
115 Calculates the FX Forward Rate
116
117 Formula:
118 FX FORWARD (HLD) Formula 3
119
120 Example for FX: CHFGBP -> CHF = Base Currency
121 GBP = Contra Currency
122
123 IF there is a notion of BID and ASK then:
124 To find p_FORWARD_RATE (BID):
125 p_SPOT_RATE = BID FX Spot Rate
126 p_BASE_CURR_INT_RATE = ASK Base Currency risk free interest rate.
127 p_CONTRA_CURR_INT_RATE = BID Contra Currency risk free interest rate.
128
129 To find p_FORWARD_RATE (ASK):
130 p_SPOT_RATE = ASK FX Spot Rate
131 p_BASE_CURR_INT_RATE = BID Base Currency risk free interest rate.
132 p_CONTRA_CURR_INT_RATE = ASK Contra Currency risk free interest rate.
133
134
135 * p_SPOT_RATE = fair exchange rate of two different currencies.
136 * p_BASE/CONTRA_CURR_INT_RATE = risk free interest rate for the base/contra
137 currency.
138 * p_DAY_COUNT_BASE/CONTRA = number of days between the spot date and the
139 forward
140 date.
141 * p_ANNUAL_BASIS_BASE/CONTRA = number of days in a year of which the
142 * p_DAY_COUNT_BASE/CONTRA and the p_BASE/CONTRA_CURR_INT_RATE are based on.
143 -------------------------------------------------------------------------*/
144
145 PROCEDURE FX_FORWARD_RATE (p_spot_rate IN NUMBER,
146 p_base_curr_int_rate IN NUMBER,
147 p_contra_curr_int_rate IN NUMBER,
148 p_day_count_base IN NUMBER,
149 p_day_count_contra IN NUMBER,
150 p_annual_basis_base IN NUMBER,
151 p_annual_basis_contra IN NUMBER,
152 p_forward_rate IN OUT NOCOPY NUMBER) is
153
154 v_rate_base NUMBER;
155 v_rate_contra NUMBER;
156
157 BEGIN
158
159 XTR_MM_FORMULAS.GROWTH_FACTOR(p_base_curr_int_rate,
160 p_day_count_base,
161 p_annual_basis_base,
162 v_rate_base);
163 XTR_MM_FORMULAS.GROWTH_FACTOR(p_contra_curr_int_rate,
164 p_day_count_contra,
165 p_annual_basis_contra,
166 v_rate_contra);
167 p_forward_rate := p_spot_rate*(v_rate_contra/v_rate_base);
168
169 END FX_FORWARD_RATE;
170
171
172 /***********************CALC_FX_OPTION_PRICES*******************************/
173
174 /*--------------------------------------------------------------------------
175 Calculates the price and sensitivity of a currency option and its associated greek ratio's using Garman-Kohlhagen formula, which is the extension of Black-Scholes formula.
176
177 Formula:
178 Taken from Currency Option Pricing Formula in Hull's Option, Future, and Other Derivatives, Third Edition p.272, p.317.
179 (Defined in xtrprc2b.pls)
180
181 IMPORTANT: due to the cumulative normal distribution function used, the
182 procedure is accurate up to 6 decimal places.
183
184 Currently used in xtrrevlb.pld
185
186 Call XTR_RATE_CONVERSION.rate_conversion to convert day counts and/or between compounded and simple interest rates.
187
188 * l_days = time left to maturity in days (assuming Actual/365 day count basis).
189 * l_base_int_rate = annual risk free interest rate for base currency.
190 * l_contra_int_rate = annual risk free interest rate for contra currency.
191 * l_spot_rate = the current market rate for the exchange.
192 * l_strike_price = the strike price agreed in the option.
193 * vol = volatility
194 * l_call_price = theoretical fair value of the call.
195 * l_put_price = theoretical fair value of the put
196 * l_fwd_price = the forward rate of the exchange calculated from the l_spot_rate
197 * l_delta_call/put = delta of the call/put
198 * l_theta_call/put = theta of the call/put
199 * l_rho_call/put = rho of the call/put (with respect to the change in base interest rate)
200 * l_rho_f_call/put = rho of call/put with respect to change in foreign interest rate
201 * l_gamma = gamma
202 * l_vega = vega
203 * l_nd1/2 = cumulative normal probability distribution value = N(x) in Black Formu
204 la
205 * l_nd1/2_a = N'(x) in Black Formula
206
207 gamma, theta, delta, vega are sensitivity measurements of the model relatives to its different variables and explained extensively in Hull's Option, Future, and Other Derivatives.
208 -----------------------------------------------------------------------*/
209 -- modified fhu 6/12/01: added sensitivity calculations
210 PROCEDURE FX_GK_OPTION_PRICE(
211 l_days IN NUMBER,
212 l_base_int_rate IN NUMBER,
213 l_contra_int_rate IN NUMBER,
214 l_spot_rate IN NUMBER,
215 l_strike_rate IN NUMBER,
216 vol IN NUMBER,
217 l_call_price IN OUT NOCOPY NUMBER,
218 l_put_price IN OUT NOCOPY NUMBER,
219 l_fwd_rate IN OUT NOCOPY NUMBER,
220 l_nd1 IN OUT NOCOPY NUMBER,
221 l_nd2 IN OUT NOCOPY NUMBER,
222 l_nd1_a IN OUT NOCOPY NUMBER,
223 l_nd2_a IN OUT NOCOPY NUMBER ) IS
224
225 --
226 -- Below are approximations of normal probability and PI (always fixed constant)
227 a1 NUMBER := 0.4361836;
228 a2 NUMBER := -0.1201678;
229 a3 NUMBER := 0.9372980;
230 pi NUMBER := 3.14159265358979;
231 --
232 r_f NUMBER := l_base_int_rate / 100;
233 r NUMBER := l_contra_int_rate / 100;
234 t NUMBER := l_days / 365; -- bug 3509267
235 v NUMBER := vol / 100;
236 d1 NUMBER;
237 d2 NUMBER;
238 n_d1 NUMBER;
239 n_d2 NUMBER;
240 n_d1_a NUMBER;
241 n_d2_a NUMBER;
242
243 v_cum_normdist_in_rec xtr_mm_formulas.cum_normdist_in_rec_type;
244 v_cum_normdist_out_rec xtr_mm_formulas.cum_normdist_out_rec_type;
245
246 BEGIN
247
248 d1 := (LN(l_spot_rate/l_strike_rate) + (r-r_f + POWER(v,2)/2)*t) / (v * SQRT(t));
249 d2 := d1 - v*SQRT(t);
250
251 v_cum_normdist_in_rec.p_d1 := d1;
252 v_cum_normdist_in_rec.p_d2 := d2;
253 xtr_mm_formulas.cumulative_norm_distribution
254 (v_cum_normdist_in_rec, v_cum_normdist_out_rec);
255
256 n_d1 := v_cum_normdist_out_rec.p_n_d1;
257 n_d2 := v_cum_normdist_out_rec.p_n_d2;
258 n_d1_a := v_cum_normdist_out_rec.p_n_d1_a;
259 n_d2_a := v_cum_normdist_out_rec.p_n_d2_a;
260
261 ---- See Currency Options on the Philadelphia Exchange p272
262 l_fwd_rate :=l_spot_rate*EXP((r-r_f)*t);
263 l_call_price := EXP(-r*t)*(l_fwd_rate * n_d1-l_strike_rate*n_d2);
264 l_put_price := EXP(-r*t)*(l_strike_rate*(1-n_d2)-l_fwd_rate*(1-n_d1));
265
266 l_nd1 := n_d1;
267 l_nd2 := n_d2;
268 l_nd1_a := n_d1_a;
269 l_nd2_a := n_d2_a;
270
271 END FX_GK_OPTION_PRICE;
272
273 /*-----------------------------------------------------------------------
274 FX_GK_OPTION_PRICE_CV
275 Cover procedure to calculate the price of a currency option
276 using Garman-Kohlhagen formula, which is the extension of
277 Black-Scholes formula.
278
279 IMPORTANT: it is better to supply a Simple Actual/365 (from GET_MD_FROM_SET)
280 interest rates for this procedure in order to avoid redundant conversions.
281
282 IMPORTANT: this procedure is only accurate up to six decimal places due
283 to CUMULATIVE_NORM_DISTRIBUTION procedure it calls.
284
285 GK_OPTION_CV_IN_REC_TYPE:
286 p_SPOT_DATE date
287 p_MATURITY_DATE date
288 p_RATE_DOM num
289 p_RATE_TYPE_DOM varchar2(1) DEFAULT 'S'
290 p_COMPOUND_FREQ_DOM num
291 p_DAY_COUNT_BASIS_DOM varchar2(15)
292 p_RF_RATE_FOR num
293 p_RATE_TYPE_FOR varchar2(1) DEFAULT 'S'
294 p_COMPOUND_FREQ_FOR num
295 p_DAY_COUNT_BASIS_FOR varchar2(15)
296 p_SPOT_RATE num
297 p_STRIKE_RATE num
298 p_VOLATILITY num
299
300 GK_OPTION_CV_OUT_REC_TYPE:
301 p_CALL_PRICE num
302 p_PUT_PRICE num
303 p_FX_FWD_RATE num
304 p_Nd1 num
305 p_Nd2 num
306 p_Nd1_a num
307 p_Nd2_a num
308
309 Formula:
310 1. Converts interest rates to fit the FX_GK_OPTION_PRICE assumptions.
311 2. Calls FX_GK_OPTION_PRICE.
312
313 Example to calculate p_SPOT_RATE:
314 Given: CAD = foreign, USD = domestic
315 1 USD = 1.5 CADThen: p_SPOT_RATE = 0.666667
316
317 p_SPOT_DATE = the spot date where the option value is evaluated
318 p_MATURITY_DATE = the maturity date where the option expires
319 p_RF_RATE_DOM = domestic risk free interest rate.
320 p_RATE_TYPE_DOM/FOR = the p_RF_RATE_DOM/FOR rate's type. 'S' for Simple
321 Rate. 'C' for Continuous Rate, and 'P' for Compounding Rate.
322 Default value = 'S' (Simple IR)
323 p_DAY_COUNT_BASIS_DOM/FOR = day count basis for p_RF_RATE_DOM/FOR.
324 p_RATE_FOR = foreign risk free interest rate.
325 p_SPOT_RATE = the current market exchange rate = the value of one unit
326 of the foreign currency measured in the domestic currency.
327 p_STRIKE_RATE = the strike price agreed in the option.
328 p_VOLATILITY = volatility
329 p_CALL_PRICE = theoretical fair value of the call.
330 p_PUT_PRICE = theoretical fair value of the put
331 p_FX_FWD_RATE = the forward rate of the exchange calculated from the
332 p_SPOT_RATE
333 p_Nd1/2 = cumulative distribution value given limit probability values
334 in Black's formula = N(x) (refer to Hull's Fourth Edition p.252)
335 p_Nd1/2_a = N'(x) in Black's formula (refer to Hull's Fourth Edition p.252)
336 p_COMPOUND_FREQ_DOM/FOR = frequencies of discretely compounded input/output
337 rate. This is only necessary if p_RATE_TYPE_DOM/FOR is 'P'.
338 -----------------------------------------------------------------------*/
339
340 PROCEDURE FX_GK_OPTION_PRICE_CV(p_in_rec IN GK_OPTION_CV_IN_REC_TYPE,
341 p_out_rec OUT NOCOPY GK_OPTION_CV_OUT_REC_TYPE) IS
342
343 v_days NUMBER;
344 v_base_int_rate NUMBER;
345 v_contra_int_rate NUMBER;
346 v_rate_type_dom VARCHAR2(1):='S';
347 v_rate_type_for VARCHAR2(1):='S';
348
349 v_dummy NUMBER;
350 v_rc_in xtr_rate_conversion.rate_conv_in_rec_type;
351 v_rc_out xtr_rate_conversion.rate_conv_out_rec_type;
352
353 BEGIN
354 IF xtr_risk_debug_pkg.g_Debug THEN
355 xtr_risk_debug_pkg.dpush('FX_SPOT_RATE: ' || 'XTR_MM_FORMULAS.FX_GK_OPTION_PRICE_CV');
356 END IF;
357
358 --get number of days in Actual/365 -- bug 3509267
359 xtr_calc_p.calc_days_run_c(p_in_rec.p_spot_date, p_in_rec.p_maturity_date,
360 'ACTUAL365', null, v_days, v_dummy);
361
362 --need to converts all rates to Continuously compounded Actual/365 -- bug 3509267
363 v_rc_in.p_rate_type_out := 'C';
364 v_rc_in.p_day_count_basis_out := 'ACTUAL365';
365 /*
366 IF xtr_risk_debug_pkg.g_Debug THEN
367 xtr_risk_debug_pkg.dlog('FX_SPOT_RATE: ' || 'Rate Type Dom',p_in_rec.p_rate_type_dom);
368 xtr_risk_debug_pkg.dlog('FX_SPOT_RATE: ' || 'Rate Type For',p_in_rec.p_rate_type_for);
369 END IF;
370 */
371 --convert domestic rate to continuous ACTUAL/365 -- bug 3509267
372 IF NOT (p_in_rec.p_rate_type_dom IN ('C','c') AND
373 p_in_rec.p_day_count_basis_dom = 'ACTUAL365') THEN
374 v_rc_in.p_rate_type_in := p_in_rec.p_rate_type_dom;
375 v_rc_in.p_day_count_basis_in := p_in_rec.p_day_count_basis_dom;
376 v_rc_in.p_rate_in := p_in_rec.p_rate_dom;
377 v_rc_in.p_start_date := p_in_rec.p_spot_date;
378 v_rc_in.p_end_date := p_in_rec.p_maturity_date;
379 v_rc_in.p_compound_freq_in := p_in_rec.p_compound_freq_dom;
380 IF xtr_risk_debug_pkg.g_Debug THEN
381 xtr_risk_debug_pkg.dlog('FX_SPOT_RATE: ' || 'Rate Type Dom',p_in_rec.p_rate_type_dom);
382 END IF;
383 xtr_rate_conversion.rate_conversion(v_rc_in, v_rc_out);
384 v_contra_int_rate := v_rc_out.p_rate_out;
385 ELSE
386 v_contra_int_rate := p_in_rec.p_rate_dom;
387 END IF;
388
389 --convert foreign rate to continuous Actual/365 -- bug 3509267
390 IF NOT (p_in_rec.p_rate_type_for IN ('C','c') AND
391 p_in_rec.p_day_count_basis_for = 'ACTUAL365') THEN
392 v_rc_in.p_rate_type_in := p_in_rec.p_rate_type_for;
393 v_rc_in.p_day_count_basis_in := p_in_rec.p_day_count_basis_for;
394 v_rc_in.p_rate_in := p_in_rec.p_rate_for;
395 v_rc_in.p_start_date := p_in_rec.p_spot_date;
396 v_rc_in.p_end_date := p_in_rec.p_maturity_date;
397 v_rc_in.p_compound_freq_in := p_in_rec.p_compound_freq_for;
398 xtr_rate_conversion.rate_conversion(v_rc_in, v_rc_out);
399 v_base_int_rate := v_rc_out.p_rate_out;
400 ELSE
401 v_base_int_rate := p_in_rec.p_rate_for;
402 END IF;
403 /*
404 IF xtr_risk_debug_pkg.g_Debug THEN
405 xtr_risk_debug_pkg.dlog('FX_SPOT_RATE: ' || 'No. of Days',v_days);
406 xtr_risk_debug_pkg.dlog('FX_SPOT_RATE: ' || 'Foreign IR C Actual/365',v_base_int_rate);
407 xtr_risk_debug_pkg.dlog('FX_SPOT_RATE: ' || 'Domestic IR C Actual365', v_contra_int_rate);
408 xtr_risk_debug_pkg.dlog('FX_SPOT_RATE: ' || 'Strike Rate',p_in_rec.p_strike_rate);
409 xtr_risk_debug_pkg.dlog('FX_SPOT_RATE: ' || 'Spot Rate',p_in_rec.p_spot_rate);
410 xtr_risk_debug_pkg.dlog('FX_SPOT_RATE: ' || 'Vol',p_in_rec.p_volatility);
411 END IF;
412 */
413 --call fx_gk_option_price
414 fx_gk_option_price(v_days, v_base_int_rate, v_contra_int_rate,
415 p_in_rec.p_spot_rate, p_in_rec.p_strike_rate,
416 p_in_rec.p_volatility,
417 p_out_rec.p_call_price, p_out_rec.p_put_price,
418 p_out_rec.p_fx_fwd_rate, p_out_rec.p_nd1, p_out_rec.p_nd2,
419 p_out_rec.p_nd1_a, p_out_rec.p_nd2_a);
420
421 IF xtr_risk_debug_pkg.g_Debug THEN
422 xtr_risk_debug_pkg.dpop('FX_SPOT_RATE: ' || 'XTR_MM_FORMULAS.FX_GK_OPTION_PRICE_CV');
423 END IF;
424 END fx_gk_option_price_cv;
425
426 --added by sankim 9/12/01
427 /*
428 FX_SPOT_RATE_CV (FUNCTION)Cover routine that calculates the FX Spot Rate for
429 bid and ask side of different currencies exchange.In order to make the cover
430 routine easier to be called from Java (middle tier) directly, the record type
431 is not used to encapsulate the arguments. Moreover, function is used instead of
432 procedure since function can be called from SQL.
433 Parameters
434 * p_RATE_CONTRA/BASE_BID/ASK = FX rate of the contra/base currency against USD
435 for bid/ask side (p_RATE_ CONTRA = Rate vs. USD Contra, p_RATE_ BASE = Rate vs.
436 USD Base). If the currency is USD then use the default rate (=1).
437 * p_CURRENCY_CONTRA/BASE = the currency for contra/base.
438 p_QUOTATION_BASIS_CONTRA/BASE indicates the quotation basis against USD for
439 the CONTRA /BASE side, 'C' for Commodity Unit Quote (=USDGBP) and 'B' for Base
440 Unit Quote (= GBPUSD) (Definitions are in FX Calculator HLD)
441 * Returned: p_SPOT_RATE (BID/ASK) = fair exchange rate of two different
442 currencies of side bid/ask.
443 */
444 FUNCTION FX_SPOT_RATE_CV( p_currency_contra IN VARCHAR2,
445 p_currency_base IN VARCHAR2,
446 p_rate_contra_bid IN NUMBER,
447 p_rate_contra_ask IN NUMBER,
448 p_rate_base_bid IN NUMBER,
449 p_rate_base_ask IN NUMBER,
450 p_quotation_basis_contra IN VARCHAR2,
451 p_quotation_basis_base IN VARCHAR2)
452 RETURN XTR_MD_NUM_TABLE IS
453 v_results_array XTR_MD_NUM_TABLE:=XTR_MD_NUM_TABLE();
454 BEGIN
455 v_results_array.extend;
456 v_results_array.extend;
457 if p_quotation_basis_contra = 'C' then
458 if p_quotation_basis_base = 'C' then
459 --base in commodity quotation basis, contra in commodity quotation basis
460 --bid
461 fx_spot_rate(p_currency_contra,p_currency_base,p_rate_contra_bid,p_rate_base_ask,p_quotation_basis_contra,p_quotation_basis_base,v_results_array(1));
462 --ask
463 fx_spot_rate(p_currency_contra,p_currency_base,p_rate_contra_ask,p_rate_base_bid,p_quotation_basis_contra,p_quotation_basis_base,v_results_array(2));
464 elsif p_quotation_basis_base = 'B' then
465 --base in commodity quotation basis, contra in base quotation basis
466 --bid
467 fx_spot_rate(p_currency_contra,p_currency_base,p_rate_contra_bid,p_rate_base_bid,p_quotation_basis_contra,p_quotation_basis_base,v_results_array(1));
468 --ask
469 fx_spot_rate(p_currency_contra,p_currency_base,p_rate_contra_ask,p_rate_base_ask,p_quotation_basis_contra,p_quotation_basis_base,v_results_array(2));
470 else
471 RAISE_APPLICATION_ERROR
472 (-20001,'p_quotation_basis_base must be ''C'' or ''B''.');
473 end if;
474 elsif p_quotation_basis_contra = 'B' then
475 if p_quotation_basis_base = 'C' then
476 --base in base quotation basis, contra in commodity quotation basis
477 --bid
478 fx_spot_rate(p_currency_contra,p_currency_base,p_rate_contra_ask,p_rate_base_ask,p_quotation_basis_contra,p_quotation_basis_base,v_results_array(1));
479 --ask
480 fx_spot_rate(p_currency_contra,p_currency_base,p_rate_contra_bid,p_rate_base_bid,p_quotation_basis_contra,p_quotation_basis_base,v_results_array(2));
481 elsif p_quotation_basis_base = 'B' then
482 --base in base quotation basis, contra in base quotation basis
483 --bid
484 fx_spot_rate(p_currency_contra,p_currency_base,p_rate_contra_ask,p_rate_base_bid,p_quotation_basis_contra,p_quotation_basis_base,v_results_array(1));
485 --ask
486 fx_spot_rate(p_currency_contra,p_currency_base,p_rate_contra_bid,p_rate_base_ask,p_quotation_basis_contra,p_quotation_basis_base,v_results_array(2));
487 else
488 RAISE_APPLICATION_ERROR
489 (-20001,'p_quotation_basis_base must be ''C'' or ''B''.');
490 end if;
491 else
492 RAISE_APPLICATION_ERROR
493 (-20001,'p_quotation_basis_contra must be ''C'' or ''B''.');
494 end if;
495 RETURN v_results_array;
496 END FX_SPOT_RATE_CV;
497
498 --added by sankim 9/12/01
499 /*
500 FX_FORWARD_RATE_CV (FUNCTION)A cover routine that calculates the FX Forward
501 Rate for exchange that has USD as the base.In order to make the cover routine
502 easier to be called from Java (middle tier) directly, the record type is not
503 used to encapsulate the arguments. Moreover, function is used instead of
504 procedure since function can be called from SQL.
505 Parameters
506 * p_SPOT_RATE_BASE_BID/ASK = fair exchange rate of between the base currency
507 against USD. If the base currency is USD, then the default value of 1 should be
508 used.
509 * p_SPOT_RATE_CONTRA_BID/ASK = fair exchange rate of between the contra
510 currency against USD. If the contra currency is USD, then the default value of
511 1 should be used.
512 * p_BASE_CURR_INT_RATE_BID/ASK = bid/ask risk free interest rate for the base
513 currency. This parameter should be null if the base currency is USD.
514 * p_CONTRA_CURR_INT_RATE_BID/ASK = bid/ask risk free interest rate for the
515 contra currency. This parameter should be null if the contra currency is USD.
516 * p_USD _CURR_INT_RATE = risk free interest rate for the USD.
517 * p_DAY_COUNT_BASE/CONTRA = number of days between the spot date and the
518 forward date. If the contra currency is USD, then p_DAY_COUNT_CONTRA should be
519 null, and vice versa in the case of base is USD.
520 * p_ANNUAL_BASIS_BASE/CONTRA = number of days in a year of which the
521 p_DAY_COUNT_BASE/CONTRA and the p_BASE/CONTRA_CURR_INT_RATE are based on. If
522 the contra currency is USD, then p_ANNUAL_BASIS_CONTRA should be null, and vice
523 versa in the case of base is USD.
524 * p_DAY_COUNT_USD = number of days between the spot date and the forward date.
525 = number of days in a year of which the p_DAY_COUNT_USD and the
526 p_USD _CURR_INT_RATE are based on.
527 * p_ANNUAL_BASIS_USD = number of days in a year of which the p_DAY_COUNT_USD
528 and the p_USD _CURR_INT_RATE are based on.
529 * p_CURRENCY_CONTRA/BASE = the currency for contra/base.
530 * p_QUOTATION_BASIS_CONTRA/BASE indicates the quotation basis against USD for
531 the CONTRA /BASE side, 'C' for Commodity Unit Quote (=USDGBP) and 'B' for Base
532 Unit Quote (= GBPUSD) (Definitions are in FX Calculator HLD). This parameter is
533 required if base/contra is non-USD accordingly.
534 * Returned: p_FORWARD_RATE (BID/ASK) indicates the bid/ask side of forward rate
535 results.
536 */
537 FUNCTION FX_FORWARD_RATE_CV( p_spot_rate_base_bid IN NUMBER,
538 p_spot_rate_base_ask IN NUMBER,
539 p_spot_rate_contra_bid IN NUMBER,
540 p_spot_rate_contra_ask IN NUMBER,
541 p_base_curr_int_rate_bid IN NUMBER,
542 p_base_curr_int_rate_ask IN NUMBER,
543 p_contra_curr_int_rate_bid IN NUMBER,
544 p_contra_curr_int_rate_ask IN NUMBER,
545 p_usd_curr_int_rate_bid IN NUMBER,
546 p_usd_curr_int_rate_ask IN NUMBER,
547 p_day_count_base IN NUMBER,
548 p_day_count_contra IN NUMBER,
549 p_day_count_usd IN NUMBER,
550 p_annual_basis_base IN NUMBER,
551 p_annual_basis_contra IN NUMBER,
552 p_annual_basis_usd IN NUMBER,
553 p_currency_base IN VARCHAR2,
554 p_currency_contra IN VARCHAR2,
555 p_quotation_basis_base IN VARCHAR2,
556 p_quotation_basis_contra IN VARCHAR2)
557 RETURN XTR_MD_NUM_TABLE IS
558 v_results_array XTR_MD_NUM_TABLE:=XTR_MD_NUM_TABLE();
559 v_forward_rate_base_bid NUMBER;
560 v_forward_rate_base_ask NUMBER;
561 v_forward_rate_contra_bid NUMBER;
562 v_forward_rate_contra_ask NUMBER;
563 BEGIN
564 IF xtr_risk_debug_pkg.g_Debug THEN
565 xtr_risk_debug_pkg.dpush('FX_FORWARD_RATE_CV: ' || 'XTR_FORWARD_RATE_CV');
566 END IF;
567 if (p_currency_base <> 'USD') AND (p_currency_contra <> 'USD') THEN
568 --if cross currency is involved
569 -- calculate forward rates for base currency first
570 if p_quotation_basis_base = 'C' then
571 IF xtr_risk_debug_pkg.g_Debug THEN
572 xtr_risk_debug_pkg.dlog('FX_FORWARD_RATE_CV: ' || 'right base bid');
573 END IF;
574 --bid
575 XTR_FX_FORMULAS.fx_forward_rate(p_spot_rate_base_bid, p_usd_curr_int_rate_ask, p_base_curr_int_rate_bid, p_day_count_usd, p_day_count_base, p_annual_basis_usd, p_annual_basis_base, v_forward_rate_base_bid);
576 --ask
577 XTR_FX_FORMULAS.fx_forward_rate(p_spot_rate_base_ask, p_usd_curr_int_rate_bid, p_base_curr_int_rate_ask, p_day_count_usd, p_day_count_base, p_annual_basis_usd, p_annual_basis_base, v_forward_rate_base_ask);
578 elsif p_quotation_basis_base = 'B' then
579 --bid
580 XTR_FX_FORMULAS.fx_forward_rate(p_spot_rate_base_bid, p_base_curr_int_rate_ask, p_usd_curr_int_rate_bid, p_day_count_base, p_day_count_usd, p_annual_basis_base, p_annual_basis_usd, v_forward_rate_base_bid);
581 --ask
582 XTR_FX_FORMULAS.fx_forward_rate(p_spot_rate_base_ask, p_base_curr_int_rate_bid, p_usd_curr_int_rate_ask, p_day_count_base, p_day_count_usd, p_annual_basis_base, p_annual_basis_usd, v_forward_rate_base_ask);
583 else
584 RAISE_APPLICATION_ERROR
585 (-20001,'p_quotation_basis_base must be ''C'' or ''B''.');
586 end if;
587 IF xtr_risk_debug_pkg.g_Debug THEN
588 xtr_risk_debug_pkg.dlog('FX_FORWARD_RATE_CV: ' || 'contra quotation basis',p_quotation_basis_contra);
589 END IF;
590 --now calculate forward rates for contra currency
591 if p_quotation_basis_contra = 'C' then
592 IF xtr_risk_debug_pkg.g_Debug THEN
593 xtr_risk_debug_pkg.dlog('FX_FORWARD_RATE_CV: ' || 'wrong contra bid');
594 END IF;
595 --bid
596 XTR_FX_FORMULAS.fx_forward_rate(p_spot_rate_contra_bid, p_usd_curr_int_rate_ask, p_contra_curr_int_rate_bid, p_day_count_usd, p_day_count_contra, p_annual_basis_usd, p_annual_basis_contra, v_forward_rate_contra_bid);
597 --ask
598 XTR_FX_FORMULAS.fx_forward_rate(p_spot_rate_contra_ask, p_usd_curr_int_rate_bid, p_contra_curr_int_rate_ask, p_day_count_usd, p_day_count_contra, p_annual_basis_usd, p_annual_basis_contra, v_forward_rate_contra_ask);
599 elsif p_quotation_basis_contra = 'B' then
600 IF xtr_risk_debug_pkg.g_Debug THEN
601 xtr_risk_debug_pkg.dlog('FX_FORWARD_RATE_CV: ' || 'right contra bid');
602 END IF;
603 --bid
604 XTR_FX_FORMULAS.fx_forward_rate(p_spot_rate_contra_bid, p_contra_curr_int_rate_ask, p_usd_curr_int_rate_bid, p_day_count_contra, p_day_count_usd, p_annual_basis_contra, p_annual_basis_usd, v_forward_rate_contra_bid);
605 --ask
606 XTR_FX_FORMULAS.fx_forward_rate(p_spot_rate_contra_ask, p_contra_curr_int_rate_bid, p_usd_curr_int_rate_ask, p_day_count_contra, p_day_count_usd, p_annual_basis_contra, p_annual_basis_usd, v_forward_rate_contra_ask);
607 else
608 RAISE_APPLICATION_ERROR
609 (-20001,'p_quotation_basis_base must be ''C'' or ''B''.');
610 end if;
611 -- calculate cross rate
612 IF xtr_risk_debug_pkg.g_Debug THEN
613 xtr_risk_debug_pkg.dlog('FX_FORWARD_RATE_CV: ' || 'base fwd bid',v_forward_rate_base_bid);
614 xtr_risk_debug_pkg.dlog('FX_FORWARD_RATE_CV: ' || 'base fwd ask',v_forward_rate_base_ask);
615 xtr_risk_debug_pkg.dlog('FX_FORWARD_RATE_CV: ' || 'contra fwd bid',v_forward_rate_contra_bid);
616 xtr_risk_debug_pkg.dlog('FX_FORWARD_RATE_CV: ' || 'contra fwd ask',v_forward_rate_contra_ask);
617 END IF;
618 return XTR_FX_FORMULAS.fx_spot_rate_cv(p_currency_contra,p_currency_base,v_forward_rate_contra_bid,v_forward_rate_contra_ask,v_forward_rate_base_bid,v_forward_rate_base_ask,p_quotation_basis_contra,p_quotation_basis_base);
619 else
620 --simpler case where there is no cross currency involved
621 if (p_currency_base = 'USD') THEN --USD is base
622 v_results_array.extend;
623 v_results_array.extend;
624 if p_quotation_basis_contra = 'C' then
625 -- calculate bid forward rate
626 XTR_FX_FORMULAS.fx_forward_rate(p_spot_rate_contra_bid, p_usd_curr_int_rate_ask, p_contra_curr_int_rate_bid, p_day_count_usd, p_day_count_contra, p_annual_basis_usd, p_annual_basis_contra, v_results_array(1));
627 --now ask
628 XTR_FX_FORMULAS.fx_forward_rate(p_spot_rate_contra_ask, p_usd_curr_int_rate_bid, p_contra_curr_int_rate_ask, p_day_count_usd, p_day_count_contra, p_annual_basis_usd, p_annual_basis_contra, v_results_array(2));
629 RETURN v_results_array;
630 elsif p_quotation_basis_contra = 'B' then
631 -- calculate bid forward rate
632 XTR_FX_FORMULAS.fx_forward_rate(1/p_spot_rate_contra_ask, p_usd_curr_int_rate_ask, p_contra_curr_int_rate_bid, p_day_count_usd, p_day_count_contra, p_annual_basis_usd, p_annual_basis_contra, v_results_array(1));
633 --now ask
634 XTR_FX_FORMULAS.fx_forward_rate(1/p_spot_rate_contra_bid, p_usd_curr_int_rate_bid, p_contra_curr_int_rate_ask, p_day_count_usd, p_day_count_contra, p_annual_basis_usd, p_annual_basis_contra, v_results_array(2));
635 RETURN v_results_array;
636 else
637 RAISE_APPLICATION_ERROR
638 (-20001,'p_quotation_basis_contra must be ''C'' or ''B''.');
639 end if;
640 else --USD is contra
641 v_results_array.extend;
642 v_results_array.extend;
643 if p_quotation_basis_base = 'B' then
644 -- calculate bid forward rate
645 XTR_FX_FORMULAS.fx_forward_rate(p_spot_rate_base_bid, p_base_curr_int_rate_ask, p_usd_curr_int_rate_bid, p_day_count_base, p_day_count_usd, p_annual_basis_base, p_annual_basis_usd, v_results_array(1));
646 -- calculate ask forward rate
647 XTR_FX_FORMULAS.fx_forward_rate(p_spot_rate_base_ask, p_base_curr_int_rate_bid, p_usd_curr_int_rate_ask, p_day_count_base, p_day_count_usd, p_annual_basis_base, p_annual_basis_usd, v_results_array(2));
648 RETURN v_results_array;
649 elsif p_quotation_basis_base = 'C' then
650 -- calculate bid forward rate
651 XTR_FX_FORMULAS.fx_forward_rate(1/p_spot_rate_base_ask, p_base_curr_int_rate_ask, p_usd_curr_int_rate_bid, p_day_count_base, p_day_count_usd, p_annual_basis_base, p_annual_basis_usd, v_results_array(1));
652 -- calculate ask forward rate
653 XTR_FX_FORMULAS.fx_forward_rate(1/p_spot_rate_base_bid, p_base_curr_int_rate_bid, p_usd_curr_int_rate_ask, p_day_count_base, p_day_count_usd, p_annual_basis_base, p_annual_basis_usd, v_results_array(2));
654 RETURN v_results_array;
655 else
656 RAISE_APPLICATION_ERROR
657 (-20001,'p_quotation_basis_base must be ''C'' or ''B''.');
658 end if;
659
660 end if;
661 end if;
662 IF xtr_risk_debug_pkg.g_Debug THEN
663 xtr_risk_debug_pkg.dpop('FX_FORWARD_RATE_CV: ' || 'XTR_FORWARD_RATE_CV');
664 END IF;
665 END FX_FORWARD_RATE_CV;
666
667 END XTR_FX_FORMULAS;
[Home] [Help]