[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
This procedure updates the history table
parameters
p_called_from_trigger-is it called from the db trigger?
p_ask_price,p_bid_price- file quote of ask and bid, computed from what's in mdi
the rest - comes directly from mp
*/
PROCEDURE archive_rates(p_called_from_trigger IN BOOLEAN,
p_ask_price IN NUMBER,
p_bid_price IN NUMBER,
p_currency_a IN VARCHAR2,
p_currency_b IN VARCHAR2,
p_nos_of_days IN NUMBER,
p_ric_code IN VARCHAR2,
p_term_length IN NUMBER,
p_term_type IN VARCHAR2,
p_term_year IN NUMBER,
-- p_last_download_time IN DATE) IS
p_last_download_time IN DATE,
p_day_count_basis IN VARCHAR2) IS
--
v_hce NUMBER;
SELECT p.param_value, b.usd_quoted_spot, b.divide_or_multiply
FROM xtr_pro_param p,
xtr_master_currencies b
WHERE p.param_name = 'SYSTEM_FUNCTIONAL_CCY'
AND b.currency = p.param_value;
SELECT rounding_factor
FROM xtr_master_currencies_v
WHERE currency = p_currency;
SELECT 1
FROM xtr_interest_period_rates
WHERE currency = p_currency
AND unique_period_id = p_ric_code
AND rate_date = p_rate_date;
SELECT 1
FROM xtr_spot_rates
WHERE currency = p_currency
AND rate_date = p_rate_date;
INSERT INTO xtr_interest_period_rates
(currency,contra_option_ccy,unique_period_id,period_code,rate_date,
bid_rate,spread,offer_rate,term_type,day_count_basis)
VALUES
(p_currency_a,p_currency_b,p_ric_code,p_nos_of_days,
p_last_download_time,p_bid_price,p_ask_price - p_bid_price,
p_ask_price,p_term_type,p_day_count_basis);
UPDATE xtr_interest_period_rates
SET contra_option_ccy = p_currency_b,
period_code = p_nos_of_days,
bid_rate = p_bid_price,
spread = p_ask_price - p_bid_price,
offer_rate = p_ask_price,
term_type = p_term_type,
day_count_basis = p_day_count_basis
WHERE currency = p_currency_a
AND unique_period_id = p_ric_code
AND rate_date = p_last_download_time;
UPDATE xtr_yield_curve_details
SET bid_price = p_bid_price,
ask_price = p_ask_price,
movement_indicator = p_movement_indicator,
rate_date = p_last_download_time
WHERE currency = p_currency_a
AND term_type = p_term_type
AND term_length = p_term_length
AND Nvl(term_year,1111) = Nvl(p_term_year,1111);
/* 3. Update Spot Rates */
/* ==================== */
IF Nvl(p_currency_a,'$#$')='USD' OR Nvl(p_currency_b,'$#$')='USD' then
IF Nvl(p_currency_a,'$#$') <>'USD' then
-- Put the Rate in USD terms by first inversing
v_mid_usd_rate := (1 / ((p_bid_price + p_ask_price) / 2)); /* bug#2366624, rravunny */
UPDATE xtr_master_currencies
SET current_spot_rate = (p_bid_price + p_ask_price) / 2,
hce_rate = (v_mid_usd_rate / v_old_base_hce_rate),
usd_quoted_spot = Decode(currency,'USD',1,(v_mid_usd_rate)), /* bug#2366624, rravunny */
spot_date = p_last_download_time,
rate_date = p_last_download_time
WHERE currency = p_currency_a;
INSERT INTO xtr_spot_rates
(currency,rate_date,bid_rate_against_usd,
spread_against_usd,offer_rate_against_usd,
usd_base_curr_bid_rate,usd_base_curr_offer_rate,
hce_rate,unique_period_id)
VALUES
(p_currency_a,p_last_download_time,p_bid_price,
p_ask_price-p_bid_price,p_ask_price,
1/p_ask_price,1/p_bid_price,
v_hce,p_ric_code);
UPDATE xtr_spot_rates
SET bid_rate_against_usd = p_bid_price,
spread_against_usd = p_ask_price-p_bid_price,
offer_rate_against_usd = p_ask_price,
usd_base_curr_bid_rate = 1/p_ask_price,
usd_base_curr_offer_rate = 1/p_bid_price,
hce_rate = v_hce,
unique_period_id = p_ric_code
WHERE currency = p_currency_a
AND rate_date = p_last_download_time;
UPDATE xtr_master_currencies
SET current_spot_rate = (p_bid_price + p_ask_price) / 2,
hce_rate = (v_mid_usd_rate / v_old_base_hce_rate),
usd_quoted_spot = Decode(currency,'USD',1,(v_mid_usd_rate)), /* bug#2366624, rravunny */
spot_date = p_last_download_time,
rate_date = p_last_download_time
WHERE currency = p_currency_b;
INSERT INTO xtr_spot_rates
(currency,rate_date,bid_rate_against_usd,
spread_against_usd,offer_rate_against_usd,
usd_base_curr_bid_rate,usd_base_curr_offer_rate,
hce_rate,unique_period_id)
VALUES
(p_currency_b,p_last_download_time,p_bid_price,
p_ask_price - p_bid_price,
p_ask_price,p_bid_price,p_ask_price,
v_hce,p_ric_code);
UPDATE xtr_spot_rates
SET bid_rate_against_usd = p_bid_price,
spread_against_usd = p_ask_price - p_bid_price,
offer_rate_against_usd = p_ask_price,
usd_base_curr_bid_rate = p_bid_price,
usd_base_curr_offer_rate = p_ask_price,
hce_rate = v_hce,
unique_period_id = p_ric_code
WHERE currency = p_currency_b
AND rate_date = p_last_download_time;
UPDATE xtr_master_currencies
SET current_spot_rate = 1,
hce_rate = Decode(v_home_ccy,'USD',1,v_usd_hce_rate),
usd_quoted_spot = 1,
spot_date = p_last_download_time,
rate_date = p_last_download_time
WHERE currency = 'USD';
UPDATE xtr_master_currencies
SET current_spot_rate = ((p_bid_price + p_ask_price) / 2), /* bug#2366624, rravunny */
usd_quoted_spot = (v_mid_usd_rate), /* bug#2366624, rravunny */
hce_rate = 1,
spot_date = p_last_download_time,
rate_date = p_last_download_time
WHERE currency = v_home_ccy;
UPDATE xtr_master_currencies_v a
SET hce_rate = (usd_quoted_spot / v_mid_usd_rate),
spot_date = p_last_download_time,
rate_date = p_last_download_time
WHERE currency <> v_home_ccy
AND currency <> 'USD'
AND usd_quoted_spot IS NOT NULL;
INSERT INTO xtr_spot_rates
(currency,rate_date,bid_rate_against_usd,spread_against_usd,
offer_rate_against_usd,usd_base_curr_bid_rate,
usd_base_curr_offer_rate,hce_rate,unique_period_id)
VALUES
('USD',p_last_download_time,1,0,1,1,1,
v_usd_hce_rate,'USD REF ROW');
UPDATE xtr_spot_rates
SET bid_rate_against_usd = 1,
spread_against_usd = 0,
offer_rate_against_usd = 1,
usd_base_curr_bid_rate = 1,
usd_base_curr_offer_rate = 1,
hce_rate = v_usd_hce_rate,
unique_period_id = 'USD REF ROW'
WHERE currency = 'USD'
AND rate_date = p_last_download_time;
SELECT NVL(datetime,SYSDATE)INTO v_now FROM xtr_market_data_interface where
rowid=p_rowid;
UPDATE XTR_MARKET_PRICES
SET ASK_PRICE= p_ask, BID_PRICE= p_bid,LAST_DOWNLOAD_TIME=v_now
WHERE XTR_MARKET_PRICES.REF_NUMBER = p_ref;
UPDATE XTR_MARKET_DATA_INTERFACE
SET LAST_UPDATED_BY = FND_GLOBAL.user_id, LAST_UPDATED_DATE= sysdate
WHERE XTR_MARKET_DATA_INTERFACE.ROWID = p_rowid;
SELECT NVL(ASK_PRICE-BID_PRICE,0) INTO v_pspread
FROM XTR_MARKET_PRICES WHERE REF_NUMBER= p_ref;
SELECT NVL(ASK_PRICE-BID_PRICE,0) INTO v_pspread
FROM XTR_MARKET_PRICES WHERE REF_NUMBER= p_ref;
SELECT NVL(ASK_PRICE-BID_PRICE,0) INTO v_pspread
FROM XTR_MARKET_PRICES WHERE REF_NUMBER= p_ref;
SELECT REF_NUMBER INTO v_fref FROM XTR_DATA_FEED_CODES
WHERE SOURCE = p_source AND EXTERNAL_REF_CODE = p_external_ref_code;
SELECT ASK_PRICE INTO v_fask FROM XTR_MARKET_PRICES
WHERE REF_NUMBER = v_fref;
SELECT BID_PRICE INTO v_fbid FROM XTR_MARKET_PRICES
WHERE REF_NUMBER = v_fref;
SELECT REF_NUMBER FROM XTR_DATA_FEED_CODES
WHERE SOURCE = p_source AND EXTERNAL_REF_CODE = p_external_ref_code;
SELECT RIC_CODE FROM XTR_MARKET_PRICES
WHERE REF_NUMBER = ( SELECT REF_NUMBER FROM XTR_DATA_FEED_CODES
WHERE SOURCE = p_source AND EXTERNAL_REF_CODE = p_external_ref_code);
SELECT REF_NUMBER INTO v_fref FROM XTR_DATA_FEED_CODES
WHERE SOURCE = p_source AND EXTERNAL_REF_CODE = p_external_ref_code;
SELECT LAST_DOWNLOAD_TIME INTO v_date FROM XTR_MARKET_PRICES
WHERE REF_NUMBER= v_fref;
SELECT text INTO v_source_h FROM xtr_sys_languages_vl WHERE
module_name='XTRMDTRP' AND canvas_type='TEXT' AND ITEM_NAME = 'P_SOURCE';
SELECT text INTO v_ext_ref_h FROM xtr_sys_languages_vl WHERE
module_name='XTRMDTRP' AND canvas_type='TEXT' AND ITEM_NAME =
'P_EXTERNAL_REF_CODE';
SELECT text INTO v_bid_h FROM xtr_sys_languages_vl WHERE
module_name='XTRMDTRP' AND canvas_type='TEXT' AND ITEM_NAME = 'P_BID';
SELECT text INTO v_ask_h FROM xtr_sys_languages_vl WHERE
module_name='XTRMDTRP' AND canvas_type='TEXT' AND ITEM_NAME = 'P_ASK';
SELECT text INTO v_mid_h FROM xtr_sys_languages_vl WHERE
module_name='XTRMDTRP' AND canvas_type='TEXT' AND ITEM_NAME = 'P_MID';
SELECT text INTO v_spread_h FROM xtr_sys_languages_vl WHERE
module_name='XTRMDTRP' AND canvas_type='TEXT' AND ITEM_NAME = 'P_SPREAD';
SELECT text INTO v_date_h FROM xtr_sys_languages_vl WHERE
module_name='XTRMDTRP' AND canvas_type='TEXT' AND ITEM_NAME = 'P_DATE';
SELECT text INTO v_trans_stat_h FROM xtr_sys_languages_vl WHERE
module_name='XTRMDTRP' AND canvas_type='TEXT' AND ITEM_NAME =
'P_TRANSFER_STATUS';
SELECT rowid, source,external_ref_code,ask,bid,datetime,mid,spread,
return_code,ref_code,created_by,creation_date,last_updated_by,
last_updated_date,last_updated_login
FROM XTR_MARKET_DATA_INTERFACE
WHERE RETURN_CODE IS NULL;
select distinct mp.currency_a
bulk collect into v_currency_a_list
from xtr_market_prices mp,
xtr_data_feed_codes dfc,
xtr_market_data_interface mdi
where mp.ric_code=dfc.ric_code
and dfc.source = mdi.source
and dfc.external_ref_code = mdi.external_ref_code
and mp.term_type = 'S'
and mp.currency_a is not null
and mp.currency_a <> 'USD'
and nvl(mp.currency_b,'USD')='USD'
and mdi.return_code is null;
select distinct mp.currency_b
bulk collect into v_currency_b_list
from xtr_market_prices mp,
xtr_data_feed_codes dfc,
xtr_market_data_interface mdi
where mp.ric_code=dfc.ric_code
and dfc.source = mdi.source
and dfc.external_ref_code = mdi.external_ref_code
and mp.currency_b is not null
and mp.currency_b <> 'USD'
and nvl(mp.currency_a,'USD')='USD'
and not exists
(select i_mp.currency_b
from xtr_market_prices i_mp,
xtr_data_feed_codes i_dfc,
xtr_market_data_interface i_mdi
where i_mp.ric_code = i_dfc.ric_code
and i_dfc.source = i_mdi.source
and i_dfc.external_ref_code = i_mdi.external_ref_code
and i_mp.term_type = 'S'
and i_mp.currency_a = mp.currency_b
and i_mdi.return_code is null)
and mdi.return_code is null;
UPDATE XTR_MARKET_DATA_INTERFACE
SET RETURN_CODE = 90,LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATED_DATE= SYSDATE WHERE rowid =v_mdi_rec.rowid;
SELECT description INTO v_trans_stat FROM fnd_lookups WHERE
lookup_type= 'XTR_MDTSF_RTN_CODES'AND lookup_code= '90';
UPDATE XTR_MARKET_DATA_INTERFACE
SET RETURN_CODE = 50 ,LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATED_DATE= SYSDATE WHERE ROWID = v_mdi_rec.rowid;
SELECT description INTO v_trans_stat FROM fnd_lookups WHERE
lookup_type= 'XTR_MDTSF_RTN_CODES'AND lookup_code= '50';
UPDATE XTR_MARKET_DATA_INTERFACE
SET RETURN_CODE = 60,LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATED_DATE= SYSDATE WHERE ROWID = v_mdi_rec.rowid;
SELECT description INTO v_trans_stat FROM fnd_lookups WHERE
lookup_type= 'XTR_MDTSF_RTN_CODES'AND lookup_code= '60';
SELECT REF_NUMBER INTO v_ref FROM XTR_DATA_FEED_CODES
WHERE SOURCE = v_mdi_rec.source
AND EXTERNAL_REF_CODE = v_mdi_rec.external_ref_code;
SELECT RIC_CODE INTO v_ric FROM XTR_MARKET_PRICES
WHERE REF_NUMBER = v_ref;
UPDATE XTR_MARKET_DATA_INTERFACE
SET REF_CODE = v_ric, RETURN_CODE = 20,
LAST_UPDATED_BY = FND_GLOBAL.user_id, LAST_UPDATED_DATE= SYSDATE
WHERE ROWID = v_mdi_rec.rowid;
UPDATE XTR_MARKET_DATA_INTERFACE
SET RETURN_CODE = 70,LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATED_DATE= SYSDATE WHERE ROWID = v_mdi_rec.rowid;
SELECT description INTO v_trans_stat FROM fnd_lookups WHERE
lookup_type= 'XTR_MDTSF_RTN_CODES'AND lookup_code= '70';
UPDATE XTR_MARKET_DATA_INTERFACE
SET RETURN_CODE = 80 ,LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATED_DATE= SYSDATE WHERE ROWID = v_mdi_rec.rowid;
SELECT description INTO v_trans_stat FROM fnd_lookups WHERE
lookup_type= 'XTR_MDTSF_RTN_CODES'AND lookup_code= '80';
SELECT REF_NUMBER INTO v_ref FROM XTR_DATA_FEED_CODES
WHERE SOURCE = v_mdi_rec.source
AND EXTERNAL_REF_CODE = v_mdi_rec.external_ref_code;
SELECT RIC_CODE INTO v_ric FROM XTR_MARKET_PRICES
WHERE REF_NUMBER = v_ref;
SELECT CURRENCY_A INTO v_currency_a FROM XTR_MARKET_PRICES
WHERE REF_NUMBER = v_ref;
SELECT CURRENCY_B INTO v_currency_b FROM XTR_MARKET_PRICES
WHERE REF_NUMBER = v_ref;
SELECT NOS_OF_DAYS INTO v_nos_of_days FROM XTR_MARKET_PRICES
WHERE REF_NUMBER = v_ref;
SELECT TERM_LENGTH INTO v_term_length FROM XTR_MARKET_PRICES
WHERE REF_NUMBER = v_ref;
SELECT TERM_TYPE INTO v_term_type FROM XTR_MARKET_PRICES
WHERE REF_NUMBER = v_ref;
SELECT TERM_YEAR INTO v_term_year FROM XTR_MARKET_PRICES
WHERE REF_NUMBER = v_ref;
SELECT currency_a, currency_b, nos_of_days, term_length, term_type,
term_type, term_year, day_count_basis
INTO v_currency_a, v_currency_b, v_nos_of_days, v_term_length,
v_term_type, v_term_type, v_term_year, v_day_count_basis
FROM xtr_market_prices
WHERE ref_number = v_ref;
UPDATE XTR_MARKET_DATA_INTERFACE
SET REF_CODE = v_ric, RETURN_CODE= 40 ,
LAST_UPDATED_BY = FND_GLOBAL.user_id, LAST_UPDATED_DATE= SYSDATE
WHERE ROWID = v_mdi_rec.rowid;
SELECT REF_NUMBER INTO v_ref FROM XTR_DATA_FEED_CODES
WHERE SOURCE = v_mdi_rec.source
AND EXTERNAL_REF_CODE = v_mdi_rec.external_ref_code;
SELECT RIC_CODE INTO v_ric FROM XTR_MARKET_PRICES
WHERE REF_NUMBER = v_ref;
UPDATE XTR_MARKET_DATA_INTERFACE
SET REF_CODE = v_ric, RETURN_CODE = 10,
LAST_UPDATED_BY = FND_GLOBAL.user_id, LAST_UPDATED_DATE= SYSDATE
WHERE ROWID = v_mdi_rec.rowid;
SELECT outer.currency quote_currency,
AVG(outer.bid_rate_against_usd) bid_rate_against_usd,
AVG(outer.offer_rate_against_usd) offer_rate_against_usd,
mc.divide_or_multiply
FROM xtr_spot_rates outer,
xtr_master_currencies mc
WHERE outer.currency <> 'USD'
AND outer.rate_date >= p_ref_date
AND outer.rate_date <= (p_ref_date+1)
AND outer.currency = mc.currency
GROUP BY outer.currency,mc.divide_or_multiply
UNION ALL
SELECT outer.currency quote_currency,
outer.bid_rate_against_usd,
outer.offer_rate_against_usd,
mc.divide_or_multiply
FROM xtr_spot_rates outer,
xtr_master_currencies mc
WHERE outer.currency <> 'USD'
AND outer.currency = mc.currency
AND NOT EXISTS (SELECT 1
FROM xtr_spot_rates inner
WHERE outer.currency = inner.currency
AND rate_date >= p_ref_date
AND rate_date <= (p_ref_date+1))
AND outer.rate_date = (SELECT max(rate_date)
FROM xtr_spot_rates inner
WHERE outer.currency = inner.currency
AND rate_date < p_ref_date)
ORDER BY quote_currency;
SELECT outer.currency,
outer.bid_rate_against_usd,
outer.offer_rate_against_usd,
mc.divide_or_multiply
FROM xtr_spot_rates outer,
xtr_master_currencies mc
WHERE outer.currency <> 'USD'
AND outer.currency = mc.currency
AND outer.rate_date = (SELECT max(rate_date)
FROM xtr_spot_rates inner
WHERE outer.currency = inner.currency
AND rate_date <= (p_ref_date+1))
ORDER BY outer.currency;
SELECT DECODE(FROM_CURRENCY,'USD',TO_CURRENCY,FROM_CURRENCY) CURRENCY,NVL(ERROR_CODE,'VALIDATION_FAILURE') ERROR_CODE
FROM gl_daily_rates_interface
WHERE MODE_FLAG in ('X','F')
AND FROM_CONVERSION_DATE=p_start_date
AND TO_CONVERSION_DATE=p_end_date
AND USER_ID=fnd_global.user_id
ORDER BY MODE_FLAG,TO_CURRENCY;
p_insert_into_gl BOOLEAN:=TRUE;
SELECT 1
FROM xtr_master_currencies
WHERE currency = p_currency
AND gl_no_upload='Y'
AND ROWNUM=1;
p_insert_into_gl:=FALSE;
IF (p_insert_into_gl) THEN
if (p_bid_mid_ask='ASK') then
p_rate:=p_offer_rate;
insert into gl_daily_rates_interface(
FROM_CURRENCY,
TO_CURRENCY,
FROM_CONVERSION_DATE,
TO_CONVERSION_DATE,
USER_CONVERSION_TYPE,
CONVERSION_RATE,
MODE_FLAG,
INVERSE_CONVERSION_RATE,
USER_ID)
values(
p_from_currency,
p_to_currency,
p_date,
p_date,
p_conv_type,
p_rate,
p_mode_flag,
null,
fnd_global.user_id);
delete from gl_daily_rates_interface
where MODE_FLAG in ('X','F')
and USER_ID=fnd_global.user_id
and FROM_CONVERSION_DATE=TO_CONVERSION_DATE
and FROM_CONVERSION_DATE>=v_start_date
and FROM_CONVERSION_DATE<=v_end_date;