4
1 PACKAGE BODY xtr_market_data_interface_p AS
2 /* $Header: xtrmdtrb.pls 120.8.12010000.2 2010/01/28 11:26:47 nipant ship $ */
3 --------------------------------------------------------------------------------------------------------------------
5 /* archive_rates
6 This procedure updates the history table
7 parameters
8 p_called_from_trigger-is it called from the db trigger?
9 p_ask_price,p_bid_price- file quote of ask and bid, computed from what's in mdi
10 the rest - comes directly from mp
11 */
12 PROCEDURE archive_rates(p_called_from_trigger IN BOOLEAN,
13 p_ask_price IN NUMBER,
14 p_bid_price IN NUMBER,
15 p_currency_a IN VARCHAR2,
16 p_currency_b IN VARCHAR2,
17 p_nos_of_days IN NUMBER,
18 p_ric_code IN VARCHAR2,
19 p_term_length IN NUMBER,
20 p_term_type IN VARCHAR2,
21 p_term_year IN NUMBER,
22 -- p_last_download_time IN DATE) IS
23 p_last_download_time IN DATE,
24 p_day_count_basis IN VARCHAR2) IS
25 --
26 v_hce NUMBER;
27 v_old_base_hce_rate NUMBER;
28 v_mid_usd_rate NUMBER;
29 v_home_ccy VARCHAR2(15);
30 v_basis VARCHAR2(1);
31 v_rounding_factor NUMBER;
32 v_usd_hce_rate NUMBER;
33 v_row_exists NUMBER;
34 --
35 CURSOR home_ccy_cursor IS
36 SELECT p.param_value, b.usd_quoted_spot, b.divide_or_multiply
37 FROM xtr_pro_param p,
38 xtr_master_currencies b
39 WHERE p.param_name = 'SYSTEM_FUNCTIONAL_CCY'
40 AND b.currency = p.param_value;
41 --
42 CURSOR rnd_factor_cursor (p_currency VARCHAR2) IS
43 SELECT rounding_factor
44 FROM xtr_master_currencies_v
45 WHERE currency = p_currency;
46 --
47 CURSOR c_interest_date_stamp (p_currency VARCHAR2,
48 p_ric_code VARCHAR2,
49 p_rate_date DATE) IS
50 SELECT 1
51 FROM xtr_interest_period_rates
52 WHERE currency = p_currency
53 AND unique_period_id = p_ric_code
54 AND rate_date = p_rate_date;
55 --
56 CURSOR c_spot_date_stamp (p_currency VARCHAR2,
57 p_rate_date DATE) IS
58 SELECT 1
59 FROM xtr_spot_rates
60 WHERE currency = p_currency
61 AND rate_date = p_rate_date;
62 --
63 BEGIN
64 -- Check if No Spot Rates exist for this currency
65 -- if not found ensure at least one row is inserted before the Archive Freq
66 -- takes effect
67 OPEN home_ccy_cursor;
68 FETCH home_ccy_cursor INTO v_home_ccy,v_old_base_hce_rate,v_basis;
69 CLOSE home_ccy_cursor;
70 IF v_home_ccy ='USD' then
71 v_old_base_hce_rate :=1;
72 END IF;
73
74 /* ======================= */
75 /* 2. not in ('S','W','F') */
76 /* ======================= */
77 IF Nvl(p_term_type,'S') <>'S' then
78 --
79 OPEN c_interest_date_stamp(p_currency_a, p_ric_code,
80 p_last_download_time);
81 FETCH c_interest_date_stamp INTO v_row_exists;
82 CLOSE c_interest_date_stamp;
83 -- If date stamp already exists in table (v_row_exists = 1),
84 -- do not insert, just update
85 IF Nvl(v_row_exists,0)<>1 THEN
86 INSERT INTO xtr_interest_period_rates
87 (currency,contra_option_ccy,unique_period_id,period_code,rate_date,
88 bid_rate,spread,offer_rate,term_type,day_count_basis)
89 VALUES
90 (p_currency_a,p_currency_b,p_ric_code,p_nos_of_days,
91 p_last_download_time,p_bid_price,p_ask_price - p_bid_price,
92 p_ask_price,p_term_type,p_day_count_basis);
93 ELSE
94 UPDATE xtr_interest_period_rates
95 SET contra_option_ccy = p_currency_b,
96 period_code = p_nos_of_days,
97 bid_rate = p_bid_price,
98 spread = p_ask_price - p_bid_price,
99 offer_rate = p_ask_price,
100 term_type = p_term_type,
101 day_count_basis = p_day_count_basis
102 WHERE currency = p_currency_a
103 AND unique_period_id = p_ric_code
104 AND rate_date = p_last_download_time;
105 END IF;
106 --
107 /* -- table not currently used
108 UPDATE xtr_yield_curve_details
109 SET bid_price = p_bid_price,
110 ask_price = p_ask_price,
111 movement_indicator = p_movement_indicator,
112 rate_date = p_last_download_time
113 WHERE currency = p_currency_a
114 AND term_type = p_term_type
115 AND term_length = p_term_length
116 AND Nvl(term_year,1111) = Nvl(p_term_year,1111);
117 */
118 --
119 ELSIF Nvl(p_term_type,'^') = 'S' then
120 /* ==================== */
121 /* 3. Update Spot Rates */
122 /* ==================== */
123 IF Nvl(p_currency_a,'$#$')='USD' OR Nvl(p_currency_b,'$#$')='USD' then
124 IF Nvl(p_currency_a,'$#$') <>'USD' then
125 -- Put the Rate in USD terms by first inversing
126 v_mid_usd_rate := (1 / ((p_bid_price + p_ask_price) / 2)); /* bug#2366624, rravunny */
127 ELSE
128 -- The Rate is in USD terms
129 v_mid_usd_rate := ((p_bid_price + p_ask_price) / 2); /* bug#2366624, rravunny */
130 END IF;
131 --
132 IF Nvl(p_currency_a,'$#$') <> 'USD' then
133 -- Currency A <> 'USD' ie AUD/USD, GBP/USD
134 IF (v_home_ccy <> p_currency_a AND v_home_ccy <> p_currency_b) OR v_home_ccy='USD' then --- add OR
135 --
136 IF p_called_from_trigger THEN
137 OPEN rnd_factor_cursor(p_currency_a);
141 SET current_spot_rate = (p_bid_price + p_ask_price) / 2,
138 FETCH rnd_factor_cursor INTO v_rounding_factor;
139 CLOSE rnd_factor_cursor;
140 UPDATE xtr_master_currencies
142 hce_rate = (v_mid_usd_rate / v_old_base_hce_rate),
143 usd_quoted_spot = Decode(currency,'USD',1,(v_mid_usd_rate)), /* bug#2366624, rravunny */
144 spot_date = p_last_download_time,
145 rate_date = p_last_download_time
146 WHERE currency = p_currency_a;
147 END IF;
148 --
149 v_hce := (v_mid_usd_rate / v_old_base_hce_rate); /* bug#2366624, rravunny */
150 ELSE
151 v_hce := 1;
152 END IF;
153 --
154 OPEN c_spot_date_stamp(p_currency_a, p_last_download_time);
155 FETCH c_spot_date_stamp INTO v_row_exists;
156 CLOSE c_spot_date_stamp;
157 -- If date stamp already exists in table (v_row_exists = 1),
158 -- do not insert, just update
159 IF Nvl(v_row_exists,0)<>1 THEN
160 INSERT INTO xtr_spot_rates
161 (currency,rate_date,bid_rate_against_usd,
162 spread_against_usd,offer_rate_against_usd,
163 usd_base_curr_bid_rate,usd_base_curr_offer_rate,
164 hce_rate,unique_period_id)
165 VALUES
166 (p_currency_a,p_last_download_time,p_bid_price,
167 p_ask_price-p_bid_price,p_ask_price,
168 1/p_ask_price,1/p_bid_price,
169 v_hce,p_ric_code);
170 ELSE
171 UPDATE xtr_spot_rates
172 SET bid_rate_against_usd = p_bid_price,
173 spread_against_usd = p_ask_price-p_bid_price,
174 offer_rate_against_usd = p_ask_price,
175 usd_base_curr_bid_rate = 1/p_ask_price,
176 usd_base_curr_offer_rate = 1/p_bid_price,
177 hce_rate = v_hce,
178 unique_period_id = p_ric_code
179 WHERE currency = p_currency_a
180 AND rate_date = p_last_download_time;
181 END IF;
182 --
183 ELSE
184 -- Currency A = 'USD' eg USD/DEM, USD/JPY etc
185 IF (v_home_ccy <> p_currency_a AND v_home_ccy <> p_currency_b) OR v_home_ccy='USD' then
186 --
187 IF p_called_from_trigger THEN
188 OPEN rnd_factor_cursor(p_currency_b);
189 FETCH rnd_factor_cursor INTO v_rounding_factor;
190 CLOSE rnd_factor_cursor;
191 UPDATE xtr_master_currencies
192 SET current_spot_rate = (p_bid_price + p_ask_price) / 2,
193 hce_rate = (v_mid_usd_rate / v_old_base_hce_rate),
194 usd_quoted_spot = Decode(currency,'USD',1,(v_mid_usd_rate)), /* bug#2366624, rravunny */
195 spot_date = p_last_download_time,
196 rate_date = p_last_download_time
197 WHERE currency = p_currency_b;
198 END IF;
199 --
200 v_hce := (v_mid_usd_rate / v_old_base_hce_rate); /* bug#2366624, rravunny */
201 ELSE
202 v_hce := 1;
203 END IF;
204 --
205 OPEN c_spot_date_stamp(p_currency_b, p_last_download_time);
206 FETCH c_spot_date_stamp INTO v_row_exists;
207 CLOSE c_spot_date_stamp;
208 -- If date stamp already exists in table (v_row_exists = 1),
209 -- do not insert, just update
210 IF Nvl(v_row_exists,0)<>1 THEN
211 INSERT INTO xtr_spot_rates
212 (currency,rate_date,bid_rate_against_usd,
213 spread_against_usd,offer_rate_against_usd,
214 usd_base_curr_bid_rate,usd_base_curr_offer_rate,
215 hce_rate,unique_period_id)
216 VALUES
217 (p_currency_b,p_last_download_time,p_bid_price,
218 p_ask_price - p_bid_price,
219 p_ask_price,p_bid_price,p_ask_price,
220 v_hce,p_ric_code);
221 ELSE
222 UPDATE xtr_spot_rates
223 SET bid_rate_against_usd = p_bid_price,
224 spread_against_usd = p_ask_price - p_bid_price,
225 offer_rate_against_usd = p_ask_price,
226 usd_base_curr_bid_rate = p_bid_price,
227 usd_base_curr_offer_rate = p_ask_price,
228 hce_rate = v_hce,
229 unique_period_id = p_ric_code
230 WHERE currency = p_currency_b
231 AND rate_date = p_last_download_time;
232 END IF;
233 --
234 END IF;
235 /* =================================================== */
236 /* 4. Compare home_ccy with currency_A and currrency_B */
237 /* =================================================== */
238 IF v_home_ccy = p_currency_a OR v_home_ccy = p_currency_b then
239 -- ie updating home currency where currency = 'USD'
240 -- note the following if statement (using basis for home ccy) is to ensure
241 -- that the hce for usd is quoted in the home ccy basis.
242 IF v_basis = '*' then
243 v_usd_hce_rate := ((p_bid_price + p_ask_price) / 2); /* bug#2366624, rravunny */
244 ELSE
245 -- need to put hce rate back in home ccy terms for the usd
246 v_usd_hce_rate := (1 / ((p_bid_price + p_ask_price)/2)); /* bug#2366624, rravunny */
247 END IF;
248 --
249 IF p_called_from_trigger THEN
253 usd_quoted_spot = 1,
250 UPDATE xtr_master_currencies
251 SET current_spot_rate = 1,
252 hce_rate = Decode(v_home_ccy,'USD',1,v_usd_hce_rate),
254 spot_date = p_last_download_time,
255 rate_date = p_last_download_time
256 WHERE currency = 'USD';
257 END IF;
258 --
259 /* ====== */
260 /* 5. Add */
261 /* ====== */
262 IF v_home_ccy <>'USD' then
263 --
264 IF p_called_from_trigger THEN
265 UPDATE xtr_master_currencies
266 SET current_spot_rate = ((p_bid_price + p_ask_price) / 2), /* bug#2366624, rravunny */
267 usd_quoted_spot = (v_mid_usd_rate), /* bug#2366624, rravunny */
268 hce_rate = 1,
269 spot_date = p_last_download_time,
270 rate_date = p_last_download_time
271 WHERE currency = v_home_ccy;
272 -- ie updating home currency therefore ALL hce rates need updating for each currency
273 UPDATE xtr_master_currencies_v a
274 SET hce_rate = (usd_quoted_spot / v_mid_usd_rate),
275 spot_date = p_last_download_time,
276 rate_date = p_last_download_time
277 WHERE currency <> v_home_ccy
278 AND currency <> 'USD'
279 AND usd_quoted_spot IS NOT NULL;
280 END IF;
281 --
282 -- Insert USD 'DUMMY' Row ie this is used by USD deals to enable them to pick
283 -- the HCE RATE.
284 OPEN c_spot_date_stamp('USD', p_last_download_time);
285 FETCH c_spot_date_stamp INTO v_row_exists;
286 CLOSE c_spot_date_stamp;
287 -- If date stamp already exists in table (v_row_exists = 1),
288 -- do not insert, just update
289 IF Nvl(v_row_exists,0)<>1 THEN
290 INSERT INTO xtr_spot_rates
291 (currency,rate_date,bid_rate_against_usd,spread_against_usd,
292 offer_rate_against_usd,usd_base_curr_bid_rate,
293 usd_base_curr_offer_rate,hce_rate,unique_period_id)
294 VALUES
295 ('USD',p_last_download_time,1,0,1,1,1,
296 v_usd_hce_rate,'USD REF ROW');
297 ELSE
298 UPDATE xtr_spot_rates
299 SET bid_rate_against_usd = 1,
300 spread_against_usd = 0,
301 offer_rate_against_usd = 1,
302 usd_base_curr_bid_rate = 1,
303 usd_base_curr_offer_rate = 1,
304 hce_rate = v_usd_hce_rate,
305 unique_period_id = 'USD REF ROW'
306 WHERE currency = 'USD'
307 AND rate_date = p_last_download_time;
308 END IF;
309 --
310 END IF; /* 5 */
311 END IF; /* 4 */
312 END IF; /* 3 */
313 END IF; /* 2 */
314 END archive_rates;
315
316
317
318 /* transfer_mp
319 This procedure does the actual transfer from mdi to mp
320 parameters
321 p_ref- the reference number from xtr_data_feed_codes based on the source and external ref code of mdi
322 p_ask,p_bid- file quote of ask and bid, computed from what's in mdi
323 p_rowid- the rowid of the mdi record we are dealing with currently
324 */
325
326 PROCEDURE transfer_mp(p_ref IN NUMBER,p_ask IN NUMBER, p_bid IN NUMBER,
327 p_rowid IN ROWID ) IS
328 --
329 v_now DATE;
330 --
331 BEGIN
332 SELECT NVL(datetime,SYSDATE)INTO v_now FROM xtr_market_data_interface where
333 rowid=p_rowid;
334 IF v_now > sysdate THEN
335 v_now := sysdate;
336 END IF;
337 UPDATE XTR_MARKET_PRICES
338 SET ASK_PRICE= p_ask, BID_PRICE= p_bid,LAST_DOWNLOAD_TIME=v_now
339 WHERE XTR_MARKET_PRICES.REF_NUMBER = p_ref;
340 UPDATE XTR_MARKET_DATA_INTERFACE
341 SET LAST_UPDATED_BY = FND_GLOBAL.user_id, LAST_UPDATED_DATE= sysdate
342 WHERE XTR_MARKET_DATA_INTERFACE.ROWID = p_rowid;
343 END transfer_mp;
344
345
346
347 /* calc_ask_bid
348 This procedure calculates the correct ask and bid values based on ask, bid, mid, and spread that are alredy stored in mdi
349 parameters
350 p_ref- the reference number from xtr_data_feed_codes based on the source and external ref code of mdi
351 p_ask,p_bid,p_mid,p_spread- file quote of ask, bid,mid, and spread that's in mdi
352 p_code- code scheme that lets you know which values are missing based on its code
353 */
354 PROCEDURE calc_ask_bid(p_ref IN NUMBER, p_ask IN OUT NOCOPY NUMBER,
355 p_bid IN OUT NOCOPY NUMBER,p_mid IN NUMBER, p_spread IN NUMBER, p_code IN NUMBER) IS
356 --
357 v_pspread NUMBER;
358 --
359 BEGIN
360 /* v_code scheme
361 1 is added to v_code if ask is missing
362 2 is added to v_code if bid is missing
363 4 is added to v_code if mid is missing
364 8 is added to v_code if spread is missing
365 the combinations of those codes will enable to programmer to uniquely identify which items are missing */
366 IF (p_code <> 0) AND (p_code<> 4) AND (p_code<> 8) AND (p_code<> 12) THEN
367 -- if ask or bid are missing
368 IF (p_code = 1) THEN -- just ask missing
369 p_ask:=2*p_mid-p_bid;
370 ELSIF (p_code = 2) THEN -- just bid missing
371 p_bid:= 2*p_mid-p_ask;
372 ELSIF (p_code = 3) THEN -- ask and bid missing
373 p_ask:= (2*p_mid+p_spread)/2;
374 p_bid:= (2*p_mid-p_spread)/2;
375 ELSIF (p_code = 5) THEN -- mid and ask missing
379 ELSIF (p_code = 9) THEN -- ask and spread missing
376 p_ask:=p_bid+p_spread;
377 ELSIF (p_code = 6) THEN--bid and mid missing
378 p_bid:= p_ask-p_spread;
380 p_ask:=2*p_mid-p_bid;
381 ELSIF (p_code = 10) THEN -- bid and spread missing
382 p_bid:= 2*p_mid-p_ask;
383 ELSIF (p_code = 11) THEN -- ask, bid, and spread missing
384 SELECT NVL(ASK_PRICE-BID_PRICE,0) INTO v_pspread
385 FROM XTR_MARKET_PRICES WHERE REF_NUMBER= p_ref;
386 p_ask:= (2*p_mid+v_pspread)/2;
387 p_bid:= (2*p_mid-v_pspread)/2;
388 ELSIF (p_code = 13) THEN-- ask, mid, and spread missing
389 SELECT NVL(ASK_PRICE-BID_PRICE,0) INTO v_pspread
390 FROM XTR_MARKET_PRICES WHERE REF_NUMBER= p_ref;
391 p_ask:= p_bid+v_pspread;
392 ELSIF (p_code = 14) THEN-- bid, mid, and spread missing
393 SELECT NVL(ASK_PRICE-BID_PRICE,0) INTO v_pspread
394 FROM XTR_MARKET_PRICES WHERE REF_NUMBER= p_ref;
395 p_bid:=p_ask-v_pspread;
396 END IF;
397 END IF;
398 END calc_ask_bid;
399
400
401
402 /* q_quote_compare
403 This function compares file quote computed from mdi and system quote that's
404 already in mp. It returns true if they are not equal and returns true if they
405 are equal. It was created so that sql statements could be used inside of if
406 structure. It first computes file quote based on data in mdi
407 parameters
408 p_source,p_external_ref_code- souce and ext_ref_code of what's in mdi. These combined enables you to find unique row in data feed codes
409 p_ask,p_bid,p_mid,p_spread- file quote of ask,bid,mid, and spread that's in mdi
410 p_code-used to call calc_ask_bid proc
411 */
412 FUNCTION q_quote_compare(p_source IN VARCHAR2,
413 p_external_ref_code IN VARCHAR2,p_ask IN OUT NOCOPY NUMBER,
414 p_bid IN OUT NOCOPY NUMBER,p_mid IN NUMBER, p_spread IN NUMBER, p_code IN NUMBER)
415 RETURN BOOLEAN IS
416 --
417 v_fask NUMBER;
418 v_fbid NUMBER;-- temporary storage for ask and bid
419 v_fref NUMBER;
420 --
421 BEGIN
422 SELECT REF_NUMBER INTO v_fref FROM XTR_DATA_FEED_CODES
423 WHERE SOURCE = p_source AND EXTERNAL_REF_CODE = p_external_ref_code;
424 SELECT ASK_PRICE INTO v_fask FROM XTR_MARKET_PRICES
425 WHERE REF_NUMBER = v_fref;
426 SELECT BID_PRICE INTO v_fbid FROM XTR_MARKET_PRICES
427 WHERE REF_NUMBER = v_fref;
428 calc_ask_bid(v_fref,p_ask,p_bid,p_mid,p_spread,p_code);
429 IF (v_fask<> p_ask) OR (v_fbid<> p_bid) OR (v_fask IS NULL) OR
430 (v_fbid IS NULL)
431 THEN
432 RETURN TRUE;
433 ELSE
434 RETURN FALSE;
435 END IF;
436 END q_quote_compare;
437
438
439
440 /* q_code_check
441 This function checks if mdi.source and mdi.external_ref_code not found in data feed codes or corresponding ric code not found in mp
442 returns true if not found. returns false if found
443 parameters
444 p_source, p_external_ref_code-source and ext ref code in mdi
445 */
446 FUNCTION q_code_check(p_source IN VARCHAR2,
447 p_external_ref_code IN VARCHAR2) RETURN BOOLEAN IS
448 --
449 CURSOR c1_cursor IS
450 SELECT REF_NUMBER FROM XTR_DATA_FEED_CODES
451 WHERE SOURCE = p_source AND EXTERNAL_REF_CODE = p_external_ref_code;
452 CURSOR c2_cursor IS
453 SELECT RIC_CODE FROM XTR_MARKET_PRICES
454 WHERE REF_NUMBER = ( SELECT REF_NUMBER FROM XTR_DATA_FEED_CODES
455 WHERE SOURCE = p_source AND EXTERNAL_REF_CODE = p_external_ref_code);
456 v_fref NUMBER;
457 v_fric VARCHAR2(20);
458 --
459 BEGIN
460 OPEN c1_cursor;
461 FETCH c1_cursor INTO v_fref;
462 IF c1_cursor%NOTFOUND THEN
463 CLOSE c1_cursor;
464 RETURN TRUE;
465 ELSE
466 OPEN c2_cursor;
467 FETCH c2_cursor INTO v_fric;
468 IF c2_cursor%NOTFOUND THEN
469 CLOSE c1_cursor;
470 CLOSE c2_cursor;
471 RETURN TRUE;
472 ELSE
473 CLOSE c1_cursor;
474 CLOSE c2_cursor;
475 RETURN FALSE;
476 END IF;
477 END IF;
478 END q_code_check;
479
480
481
482 /* q_date_check
483 This function checks if mdi.datetime<mp.last_download_time
484 returns true if that is the case. returns false otherwise
485 parameters
486 p_date-datetime in mdi
487 p_source, p_external_ref_code-source and ext ref code in mdi
488 */
489 FUNCTION q_date_check(p_date IN DATE, p_source IN VARCHAR2,
490 p_external_ref_code IN VARCHAR2) RETURN BOOLEAN IS
491 --
492 v_fref NUMBER;
493 v_date DATE;
494 --
495 BEGIN
496 SELECT REF_NUMBER INTO v_fref FROM XTR_DATA_FEED_CODES
497 WHERE SOURCE = p_source AND EXTERNAL_REF_CODE = p_external_ref_code;
498 SELECT LAST_DOWNLOAD_TIME INTO v_date FROM XTR_MARKET_PRICES
499 WHERE REF_NUMBER= v_fref;
500 IF (p_date< v_date) THEN
501 RETURN TRUE;
502 ELSE
503 RETURN FALSE;
504 END IF;
505 END q_date_check;
506
507
508
509 /*put_header
510 This procedure puts the header(titles) for the log messages. It is invoked only if there are errors
511 */
512 PROCEDURE put_header IS
513 --
514 v_source_h VARCHAR2(100);
515 v_ext_ref_h VARCHAR2(100);
516 v_bid_h VARCHAR2(100);
517 v_ask_h VARCHAR2(100);
518 v_mid_h VARCHAR2(100);
519 v_spread_h VARCHAR2(100);
520 v_date_h VARCHAR2(100);
521 v_trans_stat_h VARCHAR2(100);
522 --
523 BEGIN
524 SELECT text INTO v_source_h FROM xtr_sys_languages_vl WHERE
525 module_name='XTRMDTRP' AND canvas_type='TEXT' AND ITEM_NAME = 'P_SOURCE';
526 SELECT text INTO v_ext_ref_h FROM xtr_sys_languages_vl WHERE
530 module_name='XTRMDTRP' AND canvas_type='TEXT' AND ITEM_NAME = 'P_BID';
527 module_name='XTRMDTRP' AND canvas_type='TEXT' AND ITEM_NAME =
528 'P_EXTERNAL_REF_CODE';
529 SELECT text INTO v_bid_h FROM xtr_sys_languages_vl WHERE
531 SELECT text INTO v_ask_h FROM xtr_sys_languages_vl WHERE
532 module_name='XTRMDTRP' AND canvas_type='TEXT' AND ITEM_NAME = 'P_ASK';
533 SELECT text INTO v_mid_h FROM xtr_sys_languages_vl WHERE
534 module_name='XTRMDTRP' AND canvas_type='TEXT' AND ITEM_NAME = 'P_MID';
535 SELECT text INTO v_spread_h FROM xtr_sys_languages_vl WHERE
536 module_name='XTRMDTRP' AND canvas_type='TEXT' AND ITEM_NAME = 'P_SPREAD';
537 SELECT text INTO v_date_h FROM xtr_sys_languages_vl WHERE
538 module_name='XTRMDTRP' AND canvas_type='TEXT' AND ITEM_NAME = 'P_DATE';
539 SELECT text INTO v_trans_stat_h FROM xtr_sys_languages_vl WHERE
540 module_name='XTRMDTRP' AND canvas_type='TEXT' AND ITEM_NAME =
541 'P_TRANSFER_STATUS';
542 FND_FILE.PUT_LINE(FND_FILE.LOG,v_source_h ||', '||v_ext_ref_h||', '
543 ||v_bid_h||', '||v_ask_h||', '||v_mid_h||', '||v_spread_h||', '
544 ||v_date_h||', '||v_trans_stat_h);
545 END put_header;
546
547
548
549 /*market_data_transfer_cp
550 This is a dummy procedure that simply calls market_data_transfer. It is called by the concurrent program
551 */
552 PROCEDURE market_data_transfer_cp(
553 errbuf OUT NOCOPY VARCHAR2,
554 retcode OUT NOCOPY VARCHAR2,
555 p_upd_date_missing IN VARCHAR2,
556 p_upd_history IN VARCHAR2)
557 IS
558 --
559 --
560 BEGIN
561 market_data_transfer(p_upd_date_missing,p_upd_history);
562 END market_data_transfer_cp;
563
564
565
566 /* market_data_transfer
567 *
568 * transfers market data from the XTR_MARKET_DATA_INTERFACE table
569 * to the current (or historic) rates table within Treasury.
570 * For each record in the XTR_MARKET_DATA_INTERFACE table, this procedure
571 * attempts to make the transfer and writes a return code back
572 * on the interface table corresponding to the status of the transfer.
573 * For successful transfers, the corresponding Reference Code is also
574 * recorded on the interface table.
575 *
576 * The Return Code and Description are stored in FND_LOOKUPS where the
577 * lookup_type = 'XTR_MDTSF_RTN_CODES'
578 */
579 PROCEDURE market_data_transfer (p_upd_date_missing IN VARCHAR2,
580 p_upd_history IN VARCHAR2) IS
581 --
582 CURSOR each_row_cursor IS
583 SELECT rowid, source,external_ref_code,ask,bid,datetime,mid,spread,
584 return_code,ref_code,created_by,creation_date,last_updated_by,
585 last_updated_date,last_updated_login
586 FROM XTR_MARKET_DATA_INTERFACE
587 WHERE RETURN_CODE IS NULL;
588 v_counter NUMBER DEFAULT 0;
589 e_inv EXCEPTION;
590 -- invalid parameter exception
591 v_ask NUMBER;
592 v_bid NUMBER;
593 v_ref NUMBER;
594 v_ric VARCHAR2(20);
595 v_currency_a VARCHAR2(15);
596 v_currency_b VARCHAR2(15);
597 v_nos_of_days NUMBER;
598 v_term_length NUMBER;
599 v_term_type VARCHAR2(1);
600 v_term_year NUMBER;
601 v_code NUMBER ;
602 v_day_count_basis XTR_INTEREST_PERIOD_RATES.DAY_COUNT_BASIS%TYPE;
603 /* v_code scheme
604 1 is added to v_code if ask is missing
605 2 is added to v_code if bid is missing
606 4 is added to v_code if mid is missing
607 8 is added to v_code if spread is missing
608 the combinations of those codes will enable to programmer to uniquely identify which items are missing */
609 v_trans_stat VARCHAR2(240);
610 v_e_count NUMBER; --number of rows with errors
611
612 --bug 2588763
613 type currency_list_type is table of xtr_market_prices.currency_a%type index by binary_integer;
614 v_currency_a_list currency_list_type;
615 v_currency_b_list currency_list_type;
616
617 --
618 BEGIN
619 IF NOT(((p_upd_date_missing = 'A') OR (p_upd_date_missing = 'N') OR
620 (p_upd_date_missing = 'C')) AND ((p_upd_history = 'Y') OR
621 (p_upd_history = 'N'))) THEN
622 RAISE e_inv;
623 END IF;
624 -- check parameter integrity
625 LOCK TABLE XTR_MARKET_DATA_INTERFACE,XTR_MARKET_PRICES IN EXCLUSIVE MODE
626 NOWAIT;
627
628 -- bug 2588763 - get list of all currencies that will be updated
629 select distinct mp.currency_a
630 bulk collect into v_currency_a_list
631 from xtr_market_prices mp,
632 xtr_data_feed_codes dfc,
633 xtr_market_data_interface mdi
634 where mp.ric_code=dfc.ric_code
635 and dfc.source = mdi.source
636 and dfc.external_ref_code = mdi.external_ref_code
637 and mp.term_type = 'S'
638 and mp.currency_a is not null
639 and mp.currency_a <> 'USD'
640 and nvl(mp.currency_b,'USD')='USD'
641 and mdi.return_code is null;
642
643 select distinct mp.currency_b
644 bulk collect into v_currency_b_list
645 from xtr_market_prices mp,
646 xtr_data_feed_codes dfc,
647 xtr_market_data_interface mdi
648 where mp.ric_code=dfc.ric_code
649 and dfc.source = mdi.source
650 and dfc.external_ref_code = mdi.external_ref_code
651 and mp.currency_b is not null
652 and mp.currency_b <> 'USD'
653 and nvl(mp.currency_a,'USD')='USD'
654 and not exists
655 (select i_mp.currency_b
656 from xtr_market_prices i_mp,
657 xtr_data_feed_codes i_dfc,
658 xtr_market_data_interface i_mdi
659 where i_mp.ric_code = i_dfc.ric_code
660 and i_dfc.source = i_mdi.source
664 and i_mdi.return_code is null)
661 and i_dfc.external_ref_code = i_mdi.external_ref_code
662 and i_mp.term_type = 'S'
663 and i_mp.currency_a = mp.currency_b
665 and mdi.return_code is null;
666
667 -- lock the tables
668 v_counter := 0;
669 v_e_count:= 0;
670 FOR v_mdi_rec IN each_row_cursor LOOP
671 v_code:=0;
672 v_counter:=v_counter+1;
673 IF (v_mdi_rec.ASK IS NOT NULL) THEN
674 v_ask:=v_mdi_rec.ASK ;
675 ELSE
676 v_code:=v_code+1;
677 END IF;
678 IF (v_mdi_rec.BID IS NOT NULL) THEN
679 v_bid:=v_mdi_rec.BID ;
680 ELSE
681 v_code:=v_code+2;
682 END IF;
683 IF (v_mdi_rec.MID IS NULL) THEN
684 v_code:=v_code+4;
685 END IF;
686 IF (v_mdi_rec.SPREAD IS NULL) THEN
687 v_code:=v_code+8;
688 END IF;
689
690 IF (v_mdi_rec.SOURCE IS NULL) OR (v_mdi_rec.EXTERNAL_REF_CODE IS NULL) OR
691 ((v_mdi_rec.ASK IS NULL) AND (v_mdi_rec.BID IS NULL) AND
692 (v_mdi_rec.MID IS NULL)) THEN
693 --XTRMDT special logic fails
694 UPDATE XTR_MARKET_DATA_INTERFACE
695 SET RETURN_CODE = 90,LAST_UPDATED_BY = FND_GLOBAL.user_id,
696 LAST_UPDATED_DATE= SYSDATE WHERE rowid =v_mdi_rec.rowid;
697
698 --Print error to concurrent log
699 v_e_count := v_e_count + 1;
700 IF v_e_count = 1 THEN
701 put_header;
702 END IF;
703
704 SELECT description INTO v_trans_stat FROM fnd_lookups WHERE
705 lookup_type= 'XTR_MDTSF_RTN_CODES'AND lookup_code= '90';
706 FND_FILE.PUT_LINE(FND_FILE.LOG,v_mdi_rec.source||', '||
707 v_mdi_rec.external_ref_code||', '||v_mdi_rec.bid||', '||v_mdi_rec.ask
708 ||', '||v_mdi_rec.mid||', '||v_mdi_rec.spread||', '
709 ||v_mdi_rec.datetime||', '||v_trans_stat||' [90]');
710
711 ELSIF q_code_check(v_mdi_rec.source,v_mdi_rec.external_ref_code) THEN
712 UPDATE XTR_MARKET_DATA_INTERFACE
713 SET RETURN_CODE = 50 ,LAST_UPDATED_BY = FND_GLOBAL.user_id,
714 LAST_UPDATED_DATE= SYSDATE WHERE ROWID = v_mdi_rec.rowid;
715 --Print error to concurrent log
716 v_e_count := v_e_count + 1;
717 IF v_e_count = 1 THEN
718 put_header;
719 END IF;
720 SELECT description INTO v_trans_stat FROM fnd_lookups WHERE
721 lookup_type= 'XTR_MDTSF_RTN_CODES'AND lookup_code= '50';
722 FND_FILE.PUT_LINE(FND_FILE.LOG,v_mdi_rec.source||', '||
723 v_mdi_rec.external_ref_code||', '||v_mdi_rec.bid||', '||v_mdi_rec.ask
724 ||', '||v_mdi_rec.mid||', '||v_mdi_rec.spread||', '
725 ||v_mdi_rec.datetime||', '||v_trans_stat||' [50]');
726
727 ELSIF v_mdi_rec.DATETIME IS NULL THEN
728 IF (p_upd_date_missing = 'N') THEN
729 -- UPD_DATE_MISSING = Never
730 UPDATE XTR_MARKET_DATA_INTERFACE
731 SET RETURN_CODE = 60,LAST_UPDATED_BY = FND_GLOBAL.user_id,
732 LAST_UPDATED_DATE= SYSDATE WHERE ROWID = v_mdi_rec.rowid;
733 --Print error to concurrent log
734 v_e_count := v_e_count + 1;
735 IF v_e_count = 1 THEN
736 put_header;
737 END IF;
738 SELECT description INTO v_trans_stat FROM fnd_lookups WHERE
739 lookup_type= 'XTR_MDTSF_RTN_CODES'AND lookup_code= '60';
740 FND_FILE.PUT_LINE(FND_FILE.LOG,v_mdi_rec.source||', '||
741 v_mdi_rec.external_ref_code||', '||v_mdi_rec.bid||', '||v_mdi_rec.ask
742 ||', '||v_mdi_rec.mid||', '||v_mdi_rec.spread||', '
743 ||v_mdi_rec.datetime||', '||v_trans_stat||' [60]');
744
745 ELSIF (p_upd_date_missing = 'A') OR (q_quote_compare(v_mdi_rec.source,
746 v_mdi_rec.external_ref_code,v_ask,v_bid,v_mdi_rec.mid,v_mdi_rec.spread,
747 v_code)) THEN
748 --UPD_DATE_MISSING = Always OR File Quote <> System Quote
749 SELECT REF_NUMBER INTO v_ref FROM XTR_DATA_FEED_CODES
750 WHERE SOURCE = v_mdi_rec.source
751 AND EXTERNAL_REF_CODE = v_mdi_rec.external_ref_code;
752 SELECT RIC_CODE INTO v_ric FROM XTR_MARKET_PRICES
753 WHERE REF_NUMBER = v_ref;
754 calc_ask_bid(v_ref,v_ask,v_bid,v_mdi_rec.mid,v_mdi_rec.spread,v_code);
755
756 --transfer to mp
757 transfer_mp(v_ref, v_ask, v_bid, v_mdi_rec.rowid);
758 UPDATE XTR_MARKET_DATA_INTERFACE
759 SET REF_CODE = v_ric, RETURN_CODE = 20,
760 LAST_UPDATED_BY = FND_GLOBAL.user_id, LAST_UPDATED_DATE= SYSDATE
761 WHERE ROWID = v_mdi_rec.rowid;
762 ELSE
763 UPDATE XTR_MARKET_DATA_INTERFACE
764 SET RETURN_CODE = 70,LAST_UPDATED_BY = FND_GLOBAL.user_id,
765 LAST_UPDATED_DATE= SYSDATE WHERE ROWID = v_mdi_rec.rowid;
766 --Print error to concurrent log
767 v_e_count := v_e_count + 1;
768 IF v_e_count = 1 THEN
769 put_header;
770 END IF;
771 SELECT description INTO v_trans_stat FROM fnd_lookups WHERE
772 lookup_type= 'XTR_MDTSF_RTN_CODES'AND lookup_code= '70';
773 FND_FILE.PUT_LINE(FND_FILE.LOG,v_mdi_rec.source||', '||
774 v_mdi_rec.external_ref_code||', '||v_mdi_rec.bid||', '||v_mdi_rec.ask
775 ||', '||v_mdi_rec.mid||', '||v_mdi_rec.spread||', '
776 ||v_mdi_rec.datetime||', '||v_trans_stat||' [70]');
777 END IF;
778 ELSIF(q_date_check(v_mdi_rec.DATETIME,v_mdi_rec.source,
779 v_mdi_rec.external_ref_code)) THEN
780 --IF (UPD_HISTORY = N)
781 IF (p_upd_history = 'N') THEN
782 UPDATE XTR_MARKET_DATA_INTERFACE
783 SET RETURN_CODE = 80 ,LAST_UPDATED_BY = FND_GLOBAL.user_id,
784 LAST_UPDATED_DATE= SYSDATE WHERE ROWID = v_mdi_rec.rowid;
785 --Print error to concurrent log
786 v_e_count := v_e_count + 1;
787 IF v_e_count = 1 THEN
788 put_header;
789 END IF;
790 SELECT description INTO v_trans_stat FROM fnd_lookups WHERE
791 lookup_type= 'XTR_MDTSF_RTN_CODES'AND lookup_code= '80';
792 FND_FILE.PUT_LINE(FND_FILE.LOG,v_mdi_rec.source||', '||
793 v_mdi_rec.external_ref_code||', '||v_mdi_rec.bid||', '||v_mdi_rec.ask
794 ||', '||v_mdi_rec.mid||', '||v_mdi_rec.spread||', '
795 ||v_mdi_rec.datetime||', '||v_trans_stat||' [80]');
796 ELSE
797 SELECT REF_NUMBER INTO v_ref FROM XTR_DATA_FEED_CODES
798 WHERE SOURCE = v_mdi_rec.source
799 AND EXTERNAL_REF_CODE = v_mdi_rec.external_ref_code;
800 SELECT RIC_CODE INTO v_ric FROM XTR_MARKET_PRICES
801 WHERE REF_NUMBER = v_ref;
802 calc_ask_bid(v_ref,v_ask,v_bid,v_mdi_rec.mid,v_mdi_rec.spread,v_code);
803 /*
804 SELECT CURRENCY_A INTO v_currency_a FROM XTR_MARKET_PRICES
805 WHERE REF_NUMBER = v_ref;
806 SELECT CURRENCY_B INTO v_currency_b FROM XTR_MARKET_PRICES
807 WHERE REF_NUMBER = v_ref;
808 SELECT NOS_OF_DAYS INTO v_nos_of_days FROM XTR_MARKET_PRICES
809 WHERE REF_NUMBER = v_ref;
810 SELECT TERM_LENGTH INTO v_term_length FROM XTR_MARKET_PRICES
811 WHERE REF_NUMBER = v_ref;
812 SELECT TERM_TYPE INTO v_term_type FROM XTR_MARKET_PRICES
813 WHERE REF_NUMBER = v_ref;
814 SELECT TERM_YEAR INTO v_term_year FROM XTR_MARKET_PRICES
815 WHERE REF_NUMBER = v_ref;
816 */
817 SELECT currency_a, currency_b, nos_of_days, term_length, term_type,
818 term_type, term_year, day_count_basis
819 INTO v_currency_a, v_currency_b, v_nos_of_days, v_term_length,
820 v_term_type, v_term_type, v_term_year, v_day_count_basis
821 FROM xtr_market_prices
822 WHERE ref_number = v_ref;
823
824 --transfer to history table
825 archive_rates(FALSE,v_ask,v_bid,v_currency_a,v_currency_b,
826 v_nos_of_days,v_ric,v_term_length,v_term_type,v_term_year,
827 v_mdi_rec.datetime,v_day_count_basis);
828 UPDATE XTR_MARKET_DATA_INTERFACE
829 SET REF_CODE = v_ric, RETURN_CODE= 40 ,
830 LAST_UPDATED_BY = FND_GLOBAL.user_id, LAST_UPDATED_DATE= SYSDATE
831 WHERE ROWID = v_mdi_rec.rowid;
832 END IF;
833 ELSE
834 SELECT REF_NUMBER INTO v_ref FROM XTR_DATA_FEED_CODES
835 WHERE SOURCE = v_mdi_rec.source
836 AND EXTERNAL_REF_CODE = v_mdi_rec.external_ref_code;
837 SELECT RIC_CODE INTO v_ric FROM XTR_MARKET_PRICES
838 WHERE REF_NUMBER = v_ref;
839 calc_ask_bid(v_ref,v_ask,v_bid,v_mdi_rec.mid,v_mdi_rec.spread,v_code);
840 --transfer to mp
841 transfer_mp( v_ref,v_ask, v_bid,v_mdi_rec.rowid);
842 UPDATE XTR_MARKET_DATA_INTERFACE
843 SET REF_CODE = v_ric, RETURN_CODE = 10,
844 LAST_UPDATED_BY = FND_GLOBAL.user_id, LAST_UPDATED_DATE= SYSDATE
845 WHERE ROWID = v_mdi_rec.rowid;
846 END IF;
847
848 IF v_counter = 50 THEN
849 COMMIT;
850 v_counter:= 0;
851 -- lock the tables again because it was released by commit statement
852 LOCK TABLE XTR_MARKET_DATA_INTERFACE,XTR_MARKET_PRICES
853 IN EXCLUSIVE MODE NOWAIT;
854 END IF;
855 END LOOP;
856
857 COMMIT;
858
859 -- bug 2588763, update cross rates
860 for i in 1..v_currency_a_list.count loop
861 XTR_fps2_P.calc_cross_rate(v_currency_a_list(i),null);
862 end loop;
863 for i in 1..v_currency_b_list.count loop
864 XTR_fps2_P.calc_cross_rate(v_currency_b_list(i),null);
865 END LOOP;
866
867 EXCEPTION
868 WHEN e_inv THEN
869 FND_FILE.put_line(FND_FILE.LOG,'invalid parameter error. You must enter A,N, or C for the first parameter and Y or N for the second');
870
871 END market_data_transfer;
872
873
874 PROCEDURE upload_rates_to_gl_cp(errbuf OUT NOCOPY VARCHAR2,
875 retcode OUT NOCOPY VARCHAR2,
876 p_rel_abs IN VARCHAR2,
877 p_abs_start_date IN VARCHAR2,
878 p_abs_end_date IN VARCHAR2,
879 p_rel_end_date IN NUMBER,
880 p_rel_start_date IN NUMBER,
881 p_rate_calc IN VARCHAR2,
882 p_bid_mid_ask IN VARCHAR2,
883 p_conv_type IN VARCHAR2,
884 p_overwrite IN VARCHAR2) IS
885
886 t_currencies dbms_sql.varchar2_table;
887 t_bids dbms_sql.number_table;
888 t_offers dbms_sql.number_table;
889 t_mul_or_div dbms_sql.varchar2_table;
890 v_start_date DATE;
891 v_end_date DATE;
892 v_errbuf VARCHAR2(80);
893 v_retcode VARCHAR2(80);
894 p_batch_number_v VARCHAR2(40) DEFAULT NULL; --Bug 9300833
895
896 CURSOR get_from_usd_quote_avg(p_ref_date DATE) is
897 SELECT outer.currency quote_currency,
898 AVG(outer.bid_rate_against_usd) bid_rate_against_usd,
899 AVG(outer.offer_rate_against_usd) offer_rate_against_usd,
900 mc.divide_or_multiply
901 FROM xtr_spot_rates outer,
902 xtr_master_currencies mc
903 WHERE outer.currency <> 'USD'
904 AND outer.rate_date >= p_ref_date
905 AND outer.rate_date <= (p_ref_date+1)
906 AND outer.currency = mc.currency
907 GROUP BY outer.currency,mc.divide_or_multiply
908 UNION ALL
909 SELECT outer.currency quote_currency,
913 FROM xtr_spot_rates outer,
910 outer.bid_rate_against_usd,
911 outer.offer_rate_against_usd,
912 mc.divide_or_multiply
914 xtr_master_currencies mc
915 WHERE outer.currency <> 'USD'
916 AND outer.currency = mc.currency
917 AND NOT EXISTS (SELECT 1
918 FROM xtr_spot_rates inner
919 WHERE outer.currency = inner.currency
920 AND rate_date >= p_ref_date
921 AND rate_date <= (p_ref_date+1))
922 AND outer.rate_date = (SELECT max(rate_date)
923 FROM xtr_spot_rates inner
924 WHERE outer.currency = inner.currency
925 AND rate_date < p_ref_date)
926 ORDER BY quote_currency;
927
928 CURSOR get_from_usd_quote_eod(p_ref_date DATE) is
929 SELECT outer.currency,
930 outer.bid_rate_against_usd,
931 outer.offer_rate_against_usd,
932 mc.divide_or_multiply
933 FROM xtr_spot_rates outer,
934 xtr_master_currencies mc
935 WHERE outer.currency <> 'USD'
936 AND outer.currency = mc.currency
937 AND outer.rate_date = (SELECT max(rate_date)
938 FROM xtr_spot_rates inner
939 WHERE outer.currency = inner.currency
940 AND rate_date <= (p_ref_date+1))
941 ORDER BY outer.currency;
942
943 cursor get_failed_imports(p_start_date DATE,p_end_date DATE) is
944 SELECT DECODE(FROM_CURRENCY,'USD',TO_CURRENCY,FROM_CURRENCY) CURRENCY,NVL(ERROR_CODE,'VALIDATION_FAILURE') ERROR_CODE
945 FROM gl_daily_rates_interface
946 WHERE MODE_FLAG in ('X','F')
947 AND FROM_CONVERSION_DATE=p_start_date
948 AND TO_CONVERSION_DATE=p_end_date
949 AND USER_ID=fnd_global.user_id
950 ORDER BY MODE_FLAG,TO_CURRENCY;
951
952
953 Procedure Put_Log(Avr_Buff In Varchar2) is
954 Begin
955 Fnd_File.Put_Line(Fnd_file.LOG,Avr_Buff);
956 End;
957 PROCEDURE populate_rate(p_currency VARCHAR2,
958 p_date DATE,
959 p_bid_rate NUMBER,
960 p_offer_rate NUMBER,
961 p_mul_or_div VARCHAR2) IS
962
963 p_insert_into_gl BOOLEAN:=TRUE;
964 p_rate NUMBER;
965 p_dummy NUMBER;
966 p_mode_flag VARCHAR2(1);
967 p_from_currency VARCHAR2(15);
968 p_to_currency VARCHAR2(15);
969 CURSOR get_is_no_upload IS
970 SELECT 1
971 FROM xtr_master_currencies
972 WHERE currency = p_currency
973 AND gl_no_upload='Y'
974 AND ROWNUM=1;
975 BEGIN
976 OPEN get_is_no_upload;
977 FETCH get_is_no_upload INTO p_dummy;
978 IF get_is_no_upload%FOUND THEN
979 p_insert_into_gl:=FALSE;
980 END IF;
981 CLOSE get_is_no_upload;
982 if (p_overwrite='N') then
983 p_mode_flag:='N';
984 else
985 p_mode_flag:='T';
986 end if;
987
988 IF (p_insert_into_gl) THEN
989 if (p_bid_mid_ask='ASK') then
990 p_rate:=p_offer_rate;
991 elsif (p_bid_mid_ask='BID') then
992 p_rate:=p_bid_rate;
993 else
994 p_rate:=(p_offer_rate+p_bid_rate)/2;
995 end if;
996 if p_mul_or_div='*' then
997 p_from_currency := p_currency;
998 p_to_currency := 'USD';
999 else
1000 p_from_currency := 'USD';
1001 p_to_currency := p_currency;
1002 end if;
1003
1004 put_log(rpad(p_currency,15)||rpad(p_rate,15)||rpad(1/p_rate,15));
1005
1006 insert into gl_daily_rates_interface(
1007 FROM_CURRENCY,
1008 TO_CURRENCY,
1009 FROM_CONVERSION_DATE,
1010 TO_CONVERSION_DATE,
1011 USER_CONVERSION_TYPE,
1012 CONVERSION_RATE,
1013 MODE_FLAG,
1014 INVERSE_CONVERSION_RATE,
1015 USER_ID)
1016 values(
1017 p_from_currency,
1018 p_to_currency,
1019 p_date,
1020 p_date,
1021 p_conv_type,
1022 p_rate,
1023 p_mode_flag,
1024 null,
1025 fnd_global.user_id);
1026
1027 END IF;
1028 END populate_rate;
1029
1030 BEGIN
1031 retcode:='0';
1032 IF (p_rel_abs='R') THEN
1033 v_end_date := TRUNC(SYSDATE)-p_rel_end_date;
1034 v_start_date := TRUNC(SYSDATE)-p_rel_start_date;
1035 ELSIF (p_rel_abs='A') THEN
1036 v_end_date := fnd_date.canonical_to_date(p_abs_end_date);
1037 v_start_date := fnd_date.canonical_to_date(p_abs_start_date);
1038 END IF;
1039
1040 IF ( v_start_date is null
1041 or v_end_date is null
1042 or p_bid_mid_ask is null
1043 or p_bid_mid_ask not in ('BID','MID','ASK')
1044 or v_start_date > v_end_date
1045 ) THEN
1046 put_log(fnd_message.get_string('XTR','XTR_1328'));
1047 retcode:='2';
1048 END IF;
1049
1050 if (retcode<>'2') then
1051
1052 for i in 0..(v_end_date-v_start_date) loop
1053
1054 fnd_message.set_name('XTR','XTR_GL_UPLOAD_RATES');
1055 fnd_message.set_token('RATE_TYPE',p_conv_type);
1056 fnd_message.set_token('RATE_DATE',v_start_date+i);
1057 put_log(fnd_message.get);
1058 put_log(rpad(' ',15)||rpad(fnd_message.get_string('XTR','XTR_RATE_QUOTE'),30,'-'));
1059 put_log(rpad(fnd_message.get_string('XTR','XTR_CURRENCY_PROMPT'),15)||rpad(fnd_message.get_string('XTR','XTR_BASE_UNIT'),15)||rpad(fnd_message.get_string('XTR','XTR_CONTRA_UNIT'),15));
1060
1061 IF (p_rate_calc='DAY_AVG') THEN
1062 OPEN get_from_usd_quote_avg(v_start_date+i);
1063 FETCH get_from_usd_quote_avg BULK COLLECT INTO t_currencies,t_bids,t_offers,t_mul_or_div;
1064 CLOSE get_from_usd_quote_avg;
1065 ELSE
1066 OPEN get_from_usd_quote_eod(v_start_date+i);
1067 FETCH get_from_usd_quote_eod BULK COLLECT INTO t_currencies,t_bids,t_offers,t_mul_or_div;
1068 CLOSE get_from_usd_quote_eod;
1069 END IF;
1070
1071 FOR j IN 1..t_currencies.COUNT LOOP
1072 populate_rate(t_currencies(j),v_start_date+i,t_bids(j),t_offers(j),t_mul_or_div(j));
1073 END LOOP;
1074 put_log(' ');
1075
1076 end loop;
1077
1078 --GL_CRM_UTILITIES_PKG.daily_rates_import(v_errbuf,v_retcode);
1079 GL_CRM_UTILITIES_PKG.daily_rates_import(v_errbuf,v_retcode, p_batch_number_v); --BUG 9300833
1080
1081 for c_error in get_failed_imports(v_start_date,v_end_date) loop
1082 if (retcode='0') then
1083 retcode:='1';
1084 put_log(' ');
1085 put_log(fnd_message.get_string('XTR','XTR_GL_UPLOAD_RATES_FAIL'));
1086 put_log(rpad(fnd_message.get_string('XTR','XTR_CURRENCY_PROMPT'),15)||rpad(fnd_message.get_string('XTR','XTR_ERROR'),30));
1087 put_log(rpad('-',45,'-'));
1088 end if;
1089 put_log(rpad(c_error.currency,15)||rpad(c_error.error_code,30));
1090 end loop;
1091
1092 delete from gl_daily_rates_interface
1093 where MODE_FLAG in ('X','F')
1094 and USER_ID=fnd_global.user_id
1095 and FROM_CONVERSION_DATE=TO_CONVERSION_DATE
1096 and FROM_CONVERSION_DATE>=v_start_date
1097 and FROM_CONVERSION_DATE<=v_end_date;
1098
1099 END IF;
1100 END upload_rates_to_gl_cp;
1101
1102 --------------------------------------------------------------------------------------------------------------------
1103 END xtr_market_data_interface_p;
[Home] [Help]