1 PACKAGE XTR_MARKET_DATA_P AS
2 /* $Header: xtrmdcss.pls 120.3 2005/06/29 10:32:42 badiredd ship $ */
3
4 e_mdcs_no_data_found EXCEPTION;
5 e_mdcs_no_curve_found EXCEPTION;
6
7 --Create PL/SQL wrapper for Cubic Spline Interpolation
8 FUNCTION cubic_spline_interpolation (v_X XTR_MD_NUM_TABLE, v_Y XTR_MD_NUM_TABLE,
9 v_N NUMBER, p_x NUMBER, p_endCondInd1 NUMBER, p_endCondInd2 NUMBER,
10 p_endValue1 NUMBER, p_endValue2 NUMBER) RETURN VARCHAR2;
11
12
13 /*----------------------------------------------------------------------
14 GET_MD_FROM_CURVE
15 Returns a yield rate, discount factor, or volatility
16 from a given market data curve.
17
18 Record Type:
19 MD_FROM_CURVE_IN_REC_TYPE
20 MD_FROM_CURVE_OUT_REC_TYPE
21
22 Assumptions:
23 Only consider the passed parameter, p_side,
24 to determine the data side (bid, ask, mid),
25 and ignore the value of DATA_SIDE from XTR_RM_MD_CURVES
26 table.
27
28 The ordering priority of the interpolation method:
29 1. Look at p_interpolation_method (passed parameter)
30 2. If p_interpolation_method = 'D'/'DEFAULT' then look
31 at DEFAULT_INTERPOLATION from XTR_RM_MD_CURVES
32
33 p_curve_code = name of curve from which to extract data.
34 p_source = table source for calculation. 'C' for Current
35 System Rates table and 'R' for revaluation table.
36 p_indicator = data type of output. 'R' for yield rate,
37 'D' for discount factor, 'V' for volatility.
38 p_spot_date = reference date.
39 p_future_date = future date.
40 p_day_count_basis_out = day count basis to use for output.
41 Can set to null and disregard if p_curve_code is
42 volatility curve.
43 p_interpolation_method = interpolation method to be used
44 for curve. 'L'/'LINEAR' for linear, 'E'/'EXPON' for
45 exponential, 'C'/'CUBIC' for cubic spline, or
46 'D'/'DEFAULT' for the default value specified in the curve,
47 otherwise it will be assumed to be 'L'.
48 p_side = data side of market to return. 'B'/'BID' for bid,
49 'A'/'ASK' for ask, or 'M'/'MID' for mid.
50 No 'BID/ASK' allowed.
51 p_batch_id = batch of revaluation table to be used. Can
52 set to null and disregard if p_source <> 'R'.
53 p_md_out = output that is yield rate, discount factor, or
54 volatility.
55 ------------------------------------------------------------------------*/
56 TYPE md_from_curve_in_rec_type is RECORD (p_curve_code VARCHAR2(20),
57 p_source VARCHAR2(1),
58 p_indicator VARCHAR2(2),
59 p_spot_date DATE,
60 p_future_date DATE,
61 p_day_count_basis_out VARCHAR2(20),
62 p_interpolation_method VARCHAR2(20) DEFAULT 'DEFAULT',
63 p_side VARCHAR2(20),
64 p_batch_id NUMBER);
65
66
67 TYPE md_from_curve_out_rec_type is RECORD ( p_md_out NUMBER);
68
69
70 /*------------------------------------------------------------------------
71 GET_MD_FROM_SET
72 Returns a yield rate, discount factor, volatility,
73 FX spot rate, and bond price of a given market data set.
74
75 Record Type:
76 MD_FROM_SET_IN_REC_TYPE
77 MD_FROM_SET_OUT_REC_TYPE
78
79 For required parameters for each rate type look at MD API
80 Design Doc.
81
82 Assumptions:
83 To distinguish between IR (interest rate) and FX
84 (exchange rate) volatility:
85 If p_indicator = 'V'
86 If p_contra_ccy is null then
87 Assumed to look for IR volatility
88 Else
89 Assumed to for FX volatility
90
91 The ordering priority of the data side (bid, ask, mid):
92 1. Look at DATA_SIDE from XTR_RM_MD_SET_CURVES
93 2. Case 1: If DATA_SIDE from XTR_RM_MD_SET_CURVES =
94 'DEFAULT' then look at DATA_SIDE from XTR_RM_MD_CURVES
95 Case 2 : If the DATA_SIDE from XTR_RM_MD_SET_CURVES =
96 'BID/ASK' then look at p_side (passed parameter)
97 3. If in Case 1 the DATA_SIDE from XTR_RM_MD_CURVES =
98 'BID/ASK' then look at p_side (passed parameter).
99
100 p_ccy = currency.
101 p_contra_ccy = contra currency. It is only required for
102 volatility and FX spot rate.
103 p_md_set_code = name of market data set from which to
104 extract data.
105 p_source = table source for calculation. 'C' for Current
106 System Rates table and 'R' for revaluation table.
107 p_indicator = data type of output. 'R' for yield rate,
108 'D' for discount factor, 'V' for volatility.
109 p_spot_date = reference date.
110 p_future_date = future date.
111 p_day_count_basis_out = day count basis to use for output.
112 Can set to null and disregard if p_curve_code is
113 volatility curve.
114 p_interpolation_method = interpolation method to be used
115 for curve. 'L'/'LINEAR' for linear, 'E'/'EXPON' for
116 exponential, 'C'/'CUBIC' for cubic spline, or
117 'D'/'DEFAULT' for the default value specified in the curve,
118 otherwise it will be assumed to be 'L'.
119 p_side = data side of market to return. 'B'/'BID' for bid,
120 'A'/'ASK' for ask, or 'M'/'MID' for mid.
121 No BID/ASK allowed.
122 p_batch_id = batch of revaluation table to be used. Can
123 set to null and disregard if p_source <> 'R'.
124 p_bond_code = bond reference code. It is only required for
125 bond price. Set to null and disregard if p_indicator <> 'B'.
126 p_md_out = output that is yield rate, discount factor, or
127 volatility.
128 --------------------------------------------------------------------------*/
129 TYPE md_from_set_in_rec_type is RECORD (p_md_set_code VARCHAR2(20),
130 p_source VARCHAR2(1),
131 p_indicator VARCHAR2(2),
132 p_spot_date DATE,
133 p_future_date DATE,
134 p_ccy VARCHAR2(15),
135 p_contra_ccy VARCHAR2(15),
136 p_day_count_basis_out VARCHAR2(20),
137 p_interpolation_method VARCHAR2(20) DEFAULT 'DEFAULT',
138 p_side VARCHAR2(20),
139 p_batch_id NUMBER,
140 p_bond_code VARCHAR2(20));
141
142
143 TYPE md_from_set_out_rec_type is RECORD (p_md_out NUMBER);
144
145
146 TYPE following_holiday_in_rec_type IS RECORD (p_term_type VARCHAR2(13),
147 p_spot_date DATE DEFAULT NULL,
148 p_currency VARCHAR2(15),
149 p_future_date DATE,
150 p_period_code NUMBER);
151 TYPE following_holiday_out_rec_type IS RECORD (p_date_out DATE);
152
153
154 PROCEDURE GET_MD_FROM_CURVE (p_in_rec IN md_from_curve_in_rec_type,
155 p_out_rec OUT NOCOPY md_from_curve_out_rec_type);
156
157 PROCEDURE GET_MD_FROM_SET (p_in_rec IN md_from_set_in_rec_type,
158 p_out_rec OUT NOCOPY md_from_set_out_rec_type,
159 p_first_call IN NUMBER DEFAULT NULL);
160
161
162 /*-------------------------------------------------------------------------
163 GET_FX_FORWARD_FROM_SET
164 Returns an FX Forward rate of a given market
165 data set.
166
167 All parameters, record types, ordering priorities,
168 and their definitions are the same as those of
169 -------------------------------------------------------------------------*/
170 PROCEDURE GET_FX_FORWARD_FROM_SET (p_in_rec IN md_from_set_in_rec_type,
171 p_out_rec OUT NOCOPY md_from_set_out_rec_type);
172
173
174 /*--------------------------------------------------------------------------
175 LINEAR_INTERPOLATION
176 Returns a linearly interpolated (Y element) value
177 given p_t (X element).
178
179 p_t = the X element whose Y element is p_rate (to
180 be interpolated).
181 p_t1 = one of the data point that is used to calculate
182 the interpolated point. p_t2 = one of the data point
183 that is used to calculate the interpolated point
184 (has to be different from p_t1).
185 p_rate1 = the Y element associated with p_t1.
186 p_rate2 = the Y element associated with p_t2.
187 p_slope = the slope of the line that passed through
188 (p_t1, p_rate1) and (p_t, p_rate). If p_slope is not
189 null, p_t2 and p_rate2 is not necessary.
190 p_rate = the Y element.that is associated with p_t.
191 --------------------------------------------------------------------------*/
192 PROCEDURE LINEAR_INTERPOLATION (p_t IN NUMBER,
193 p_t1 IN NUMBER,
194 p_t2 IN NUMBER,
195 p_rate1 IN NUMBER,
196 p_rate2 IN NUMBER,
197 p_slope IN NUMBER DEFAULT NULL,
198 p_rate OUT NOCOPY NUMBER);
199
200
201 /*-------------------------------------------------------------------------
202 DF_EXPONENTIAL_INTERPOLATION
203 Returns an exponentially interpolated (Y element)
204 value given p_t (X element), assuming all the input
205 rates are discount factors.
206
207 p_t = the X element whose Y element is p_rate (to
208 be interpolated).
209 p_t1 = one of the data point that is used to calculate
210 the interpolated point. p_t2 = one of the data point
211 that is used to calculate the interpolated point
212 (has to be different from p_t1).
213 p_rate1 = the Y element associated with p_t1.
214 p_rate2 = the Y element associated with p_t2.
215 p_rate = the Y element.that is associated with p_t.
216 --------------------------------------------------------------------------*/
217 PROCEDURE DF_EXPONENTIAL_INTERPOLATION (p_indicator CHAR, --'I' or 'O'
218 p_t IN NUMBER,
219 p_t1 IN NUMBER,
220 p_t2 IN NUMBER,
221 p_rate1 IN NUMBER,
222 p_rate2 IN NUMBER,
223 p_rate OUT NOCOPY NUMBER);
224
225 PROCEDURE Following_Holiday(p_in_rec IN following_holiday_in_rec_type,
226 p_out_rec OUT NOCOPY following_holiday_out_rec_type);
227
228 --Bug 2804548
229 --This actually calculates PREVIOUS BUSINESS DAY rule.
233
230 PROCEDURE Modified_Following_Holiday(p_currency IN VARCHAR2,
231 p_date_in IN DATE,
232 p_date_out OUT NOCOPY DATE);
234 END;