[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(period_code,0) period_code,
nvl(DECODE(v_side,'B',bid_rate,'BID',
bid_rate, 'A', offer_rate, 'ASK', offer_rate,
(bid_rate+offer_rate)/2),0) rate,
nvl(day_count_basis,'ACTUAL/ACTUAL') day_count_basis,
term_type, unique_period_id, currency
FROM xtr_rm_md_show_curves_v outter
WHERE curve_code = p_in_rec.p_curve_code
AND outter.rate_date=
(SELECT max(inner.rate_date)
FROM xtr_rm_md_show_curves_v inner
WHERE trunc(inner.rate_date) <= trunc(p_in_rec.p_spot_date)
AND curve_code = p_in_rec.p_curve_code
AND outter.unique_period_id=inner.unique_period_id)
ORDER BY 1;
SELECT COUNT(*) FROM xtr_rm_md_show_curves_v t1,
xtr_rm_md_show_curves_v t2
WHERE t1.curve_code = p_in_rec.p_curve_code
AND t2.curve_code = p_in_rec.p_curve_code
AND (t1.rate_date, t1.unique_period_id) IN
(SELECT MAX(rate_date), unique_period_id
FROM xtr_rm_md_show_curves_v
WHERE trunc(rate_date) <= trunc(p_in_rec.p_spot_date)
AND curve_code = p_in_rec.p_curve_code
GROUP BY unique_period_id)
AND (t2.rate_date, t2.unique_period_id) IN
(SELECT MAX(rate_date), unique_period_id
FROM xtr_rm_md_show_curves_v
WHERE trunc(rate_date) <= trunc(p_in_rec.p_spot_date)
AND curve_code = p_in_rec.p_curve_code
GROUP BY unique_period_id)
AND nvl(t1.day_count_basis,'ACTUAL/ACTUAL')<>nvl(t2.day_count_basis,'ACTUAL/ACTUAL');
SELECT default_interpolation FROM xtr_rm_md_curves
WHERE curve_code = p_in_rec.p_curve_code;
SELECT nvl(r.number_of_days,0) period_code,
nvl(DECODE(v_side, 'B', nvl(r.bid_overwrite, r.bid),
'BID', nvl(r.bid_overwrite, r.bid),
'A', nvl(r.ask_overwrite, r.ask),
'ASK',nvl(r.ask_overwrite, r.ask),
(nvl(r.bid,r.bid_overwrite)+nvl(r.ask,r.ask_overwrite))/2),0) rate,
nvl(r.day_count_basis,'ACTUAL/ACTUAL') day_count_basis,
r.day_mth term_type, r.reval_type unique_period_id,
r.currencyA currency, r.volatility_or_rate rate_type
FROM xtr_revaluation_rates r, xtr_rm_md_curve_rates c
WHERE r.reval_type = c.rate_code
AND c.curve_code = p_in_rec.p_curve_code
AND r.batch_id = p_in_rec.p_batch_id
ORDER BY 1;
SELECT COUNT(*) FROM xtr_revaluation_rates r1,xtr_revaluation_rates r2,
xtr_rm_md_curve_rates c1, xtr_rm_md_curve_rates c2
WHERE r1.reval_type = c1.rate_code
AND r2.reval_type = c2.rate_code
AND c1.curve_code = p_in_rec.p_curve_code
AND c2.curve_code = p_in_rec.p_curve_code
AND r1.batch_id = p_in_rec.p_batch_id
AND r2.batch_id = p_in_rec.p_batch_id
AND nvl(r1.day_count_basis,'ACTUAL/ACTUAL')<>nvl(r2.day_count_basis,'ACTUAL/ACTUAL');
SELECT DECODE(data_side, 'BID/ASK', p_in_rec.p_side, data_side) side
FROM xtr_rm_md_curves
WHERE curve_code = p_in_rec.p_curve_code;
SELECT usd_base_curr_bid_rate bid_rate,
usd_base_curr_offer_rate ask_rate,
1/usd_base_curr_offer_rate bid_rate_base,
1/usd_base_curr_bid_rate ask_rate_base,
currency
FROM xtr_spot_rates
WHERE (rate_date, currency) IN (SELECT MAX(rate_date), currency
FROM xtr_spot_rates
WHERE currency IN (p_in_rec.p_ccy, p_in_rec.p_contra_ccy)
AND currency <> 'USD'
AND trunc(rate_date) <= trunc(p_in_rec.p_spot_date)
GROUP BY currency);
SELECT DECODE(currencyA, 'USD', nvl(bid_overwrite,bid),
1/nvl(ask_overwrite,ask)) bid_rate,
DECODE(currencyA, 'USD', nvl(ask_overwrite,ask),
1/nvl(bid_overwrite,bid)) ask_rate,
DECODE(currencyA, 'USD', 1/nvl(ask_overwrite,ask),
nvl(bid_overwrite,bid)) bid_rate_base,
DECODE(currencyA, 'USD', 1/nvl(bid_overwrite,bid),
nvl(ask_overwrite,ask)) ask_rate_base,
DECODE(currencyA, 'USD', currencyB, currencyA) currency
--gives non-USD currency
FROM xtr_revaluation_rates
WHERE ((currencyA = 'USD' and currencyB
IN (p_in_rec.p_ccy,p_in_rec.p_contra_ccy))
or (currencyB = 'USD' and currencyA
IN (p_in_rec.p_ccy,p_in_rec.p_contra_ccy)))
AND volatility_or_rate = 'RATE'
AND day_mth is NULL
AND batch_id = p_in_rec.p_batch_id;
SELECT c.curve_code, DECODE(sc.data_side, 'DEFAULT', p_in_rec.p_side, 'BID/ASK', p_in_rec.p_side, sc.data_side) side,
DECODE(sc.interpolation, 'DEFAULT', c.default_interpolation,
sc.interpolation) interpolation
FROM xtr_rm_md_set_curves sc, xtr_rm_md_curves c
WHERE sc.set_code = p_in_rec.p_md_set_code
AND c.type = DECODE(p_in_rec.p_indicator,'V','IRVOL', 'YIELD')
AND c.ccy = p_in_rec.p_ccy
AND sc.curve_code = c.curve_code;
SELECT c.curve_code, DECODE(sc.data_side, 'DEFAULT', p_in_rec.p_side, 'BID/ASK', p_in_rec.p_side, sc.data_side) side,
DECODE(sc.interpolation, 'DEFAULT', c.default_interpolation,
sc.interpolation) interpolation
FROM xtr_rm_md_set_curves sc, xtr_rm_md_curves c
WHERE sc.set_code = p_in_rec.p_md_set_code
AND c.type = 'FXVOL'
AND ((c.ccy = p_in_rec.p_ccy AND c.contra_ccy = p_in_rec.p_contra_ccy)
OR (c.ccy = p_in_rec.p_contra_ccy AND c.contra_ccy = p_in_rec.p_ccy))
AND sc.curve_code = c.curve_code;
SELECT DECODE(fx_spot_side, 'BID/ASK', p_in_rec.p_side, fx_spot_side)
INTO v_in_rec.p_side
FROM xtr_rm_md_sets
WHERE set_code = p_in_rec.p_md_set_code;
SELECT DECODE(bond_price_side, 'BID/ASK', p_in_rec.p_side, bond_price_side)
INTO v_in_rec.p_side
FROM xtr_rm_md_sets
WHERE set_code = p_in_rec.p_md_set_code;
SELECT DECODE(v_in_rec.p_side, 'BID', nvl(bid_overwrite,bid),
'A', nvl(ask_overwrite,ask),
'ASK', nvl(ask_overwrite,ask),
'B', nvl(bid_overwrite,bid),
(nvl(ask_overwrite,ask)+nvl(bid_overwrite,bid))/2)
INTO p_out_rec.p_md_out
FROM xtr_revaluation_rates
WHERE reval_type = p_in_rec.p_bond_code
AND volatility_or_rate = 'PRIC'
AND day_mth IS NULL
AND batch_id = p_in_rec.p_batch_id;
SELECT DECODE(v_in_rec.p_side,
'ASK', offer_rate,
'A', offer_rate,
'BID', bid_rate,
'B', bid_rate,
(bid_rate+offer_rate)/2)
INTO p_out_rec.p_md_out
FROM xtr_interest_period_rates
WHERE (rate_date, unique_period_id) IN
(SELECT MAX(rate_date), unique_period_id
FROM xtr_interest_period_rates
WHERE trunc(rate_date) <= trunc(p_in_rec.p_spot_date)
AND unique_period_id = p_in_rec.p_bond_code
GROUP BY unique_period_id)
AND term_type = 'B';
SELECT DECODE(stock_price_side, 'BID/ASK', p_in_rec.p_side, stock_price_side)
INTO v_in_rec.p_side
FROM xtr_rm_md_sets
WHERE set_code = p_in_rec.p_md_set_code;
SELECT DECODE(v_in_rec.p_side, 'BID', nvl(bid_overwrite,bid),
'A', nvl(ask_overwrite,ask),
'ASK', nvl(ask_overwrite,ask),
'B', nvl(bid_overwrite,bid),
(nvl(ask_overwrite,ask)+nvl(bid_overwrite,bid))/2)
INTO p_out_rec.p_md_out
FROM xtr_revaluation_rates
WHERE reval_type = p_in_rec.p_bond_code
AND volatility_or_rate = 'PRIC'
AND day_mth IS NULL
AND batch_id = p_in_rec.p_batch_id;
SELECT DECODE(v_in_rec.p_side,
'ASK', offer_rate,
'A', offer_rate,
'BID', bid_rate,
'B', bid_rate,
(bid_rate+offer_rate)/2)
INTO p_out_rec.p_md_out
FROM xtr_interest_period_rates
WHERE (rate_date, unique_period_id) IN
(SELECT MAX(rate_date), unique_period_id
FROM xtr_interest_period_rates
WHERE trunc(rate_date) <= trunc(p_in_rec.p_spot_date)
AND unique_period_id = p_in_rec.p_bond_code
GROUP BY unique_period_id)
AND term_type = 'T';