[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select decode(l_wk_mth,'W',to_char(next_day(nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),to_char(to_date('09/03/1997','DD/MM/YYYY'),'DY')),'DD-MM-YYYY')
,to_char(AMOUNT_DATE,'MON-YYYY')),AMOUNT_DATE,
sum(CASHFLOW_AMOUNT),CURRENCY_COMBINATION,sum(TRANSACTION_RATE),
decode(DEAL_TYPE,'FX',TRANSACTION_NUMBER,'FXO',TRANSACTION_NUMBER,1),
decode(DEAL_TYPE,'FX',nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),'FXO',nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),NULL),count(AMOUNT)
from XTR_DEAL_DATE_AMOUNTS_V
where STATUS_CODE = 'CURRENT'
and (AMOUNT_DATE >= trunc(SYSDATE) or EXPOSURE_REF_DATE >=trunc(SYSDATE))
and COMPANY_CODE = l_company
and CASHFLOW_AMOUNT <> 0
and CURRENCY = sel_ccy
and nvl(PORTFOLIO_CODE,'%') like nvl(upper(l_portfolio),'%')
and (incl_options = 'Y' or (incl_options = 'N' and AMOUNT_TYPE NOT IN('FXOBUY','FXOSELL')))
and ((DEAL_SUBTYPE = 'INDIC' and incl_indic_exp = 'Y') or
DEAL_SUBTYPE <> 'INDIC')
and nvl(multiple_settlements,'N') <> 'Y'
group by decode(l_wk_mth,'W',to_char(next_day(nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),to_char(to_date('09/03/1997','DD/MM/YYYY'),'DY')),'DD-MM-YYYY')
,to_char(AMOUNT_DATE,'MON-YYYY')),AMOUNT_DATE,CURRENCY_COMBINATION,
decode(DEAL_TYPE,'FX',TRANSACTION_NUMBER,'FXO',TRANSACTION_NUMBER,1),
decode(DEAL_TYPE,'FX',nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),'FXO',nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),NULL);
select sum(nvl(OPENING_BALANCE,0)),count(ACCOUNT_NUMBER)
from XTR_BANK_ACCOUNTS
where PARTY_CODE = l_company
and CURRENCY like nvl(sel_ccy,'%')
and nvl(PORTFOLIO_CODE,'%') like nvl(upper(l_portfolio),'%')
and nvl(SETOFF_ACCOUNT_YN,'N') <> 'Y';
select sum((-1) * d.cashflow_amount) CASH_ON_HAND
from XTR_DEAL_DATE_AMOUNTS_V d,
XTR_ROLLOVER_TRANSACTIONS_V r
where d.STATUS_CODE = 'CURRENT'
and d.DEAL_TYPE = 'ONC'
and d.COMPANY_CODE = l_company
and d.CURRENCY like upper(nvl(sel_ccy,'%'))
and nvl(d.PORTFOLIO_CODE,'%') like nvl(upper(l_portfolio),'%')
and nvl(d.multiple_settlements,'N') = 'N'
and d.CASHFLOW_AMOUNT <> 0
and r.deal_number = d.deal_number
and r.transaction_number = d.transaction_number
and r.MATURITY_DATE is NULL;
delete from XTR_exposure_summary
where created_on < (trunc(sysdate) - 7);
insert into XTR_EXPOSURE_SUMMARY
(unique_ref_number,currency,period,amount,average_days,weighted_average,currency_combination,
transaction_rate,created_on,created_by,incl_fx_options,incl_indic_exposures,company,base_currency,
number_of_items,acct_balance,portfolio_code,hedge_or_trade_view,discount_rate,discounted_back_to,
discounted_value,yield_curve,forward_fx_rate,selected_ccy,selected_indic,selected_options,
hedge_trade_whatif,selected_portfolio,month_or_week,period_date)
values
(ref_number,sel_ccy,l_date,round(l_amount,2),l_avg_days,l_weight_avg,l_combination,round(l_rate,5),
trunc(sysdate),fnd_global.user_id,
incl_options,incl_indic_exp,l_company,l_base_ccy,l_num_items,'N',l_portfolio,perspective,disc_rate,
trunc(sysdate),disc_value,l_yield_curve,l_fwd_fx_rate,sel_ccy,incl_indic_exp,incl_options,'H',l_portfolio,
nvl(l_wk_mth,'M'),decode(l_wk_mth,'W',to_date(l_date,'DD-MM-YYYY'),
-- to_date(l_date,'MON-YYYY')));
insert into XTR_EXPOSURE_SUMMARY
(unique_ref_number,currency,period,amount,average_days,weighted_average,currency_combination,
transaction_rate,created_on,created_by,incl_fx_options,incl_indic_exposures,company,base_currency,
number_of_items,acct_balance,portfolio_code,hedge_or_trade_view,discount_rate,discounted_back_to,
discounted_value,yield_curve,selected_ccy,selected_indic,selected_options,
hedge_trade_whatif,selected_portfolio,month_or_week,period_date)
values
(ref_number,sel_ccy,decode(l_wk_mth,'W',to_char(next_day(trunc(sysdate),to_char(to_date('09/03/1997','DD/MM/YYYY'),'DY')),'DD-MM-YYYY'),
to_char(trunc(sysdate),'MON-YYYY')),round(l_amount,2),1,0,null,null,trunc(sysdate),fnd_global.user_id,
incl_options,incl_indic_exp,l_company,l_base_ccy,l_num_items,'Y',l_portfolio,perspective,
0,trunc(sysdate),l_amount,l_yield_curve,sel_ccy,incl_indic_exp,incl_options,'H',l_portfolio,
nvl(l_wk_mth,'M'),trunc(sysdate));
select CURRENCY,to_char(nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),'MON-YYYY'),
nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),
sum(CASHFLOW_AMOUNT),
CURRENCY_COMBINATION,sum(TRANSACTION_RATE),
TRANSACTION_NUMBER,nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),count(AMOUNT)
from XTR_DEAL_DATE_AMOUNTS_V
where STATUS_CODE = 'CURRENT'
and CURRENCY_COMBINATION like upper(nvl(l_ccy_a,'%'))||'/'||upper(nvl(l_ccy_b,'%'))
and (AMOUNT_DATE >= trunc(SYSDATE) or EXPOSURE_REF_DATE >=trunc(sysdate))
and CURRENCY = substr(CURRENCY_COMBINATION,1,3)
and DEAL_TYPE like 'FX%'
and AMOUNT_TYPE <> 'EXPIRY'
and COMPANY_CODE = l_company
and CASHFLOW_AMOUNT <> 0
and nvl(PORTFOLIO_CODE,'%') like nvl(upper(l_portfolio),'%')
and (incl_options = 'Y' or (incl_options = 'N' and AMOUNT_TYPE NOT IN('FXOBUY','FXOSELL')))
group by CURRENCY,to_char(nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),'MON-YYYY'),
nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),
CURRENCY_COMBINATION,
TRANSACTION_NUMBER,nvl(EXPOSURE_REF_DATE,AMOUNT_DATE);
select HCE_RATE
from XTR_MASTER_CURRENCIES
where CURRENCY = l_ccy;
delete from XTR_exposure_summary
where created_on < (trunc(sysdate) - 7);
insert into XTR_EXPOSURE_SUMMARY
(unique_ref_number,currency,period,amount,average_days,weighted_average,currency_combination,
transaction_rate,created_on,created_by,incl_fx_options,company,base_currency,discounted_value,
number_of_items,acct_balance,portfolio_code,hedge_or_trade_view,discounted_back_to,forward_fx_rate,
selected_ccy,selected_ccy2,selected_indic,selected_options,hedge_trade_whatif,selected_portfolio,period_date)
values
(ref_number,l_ccy,l_date,round(l_amount,2),l_avg_days,l_weight_avg,l_combination,round(l_rate,5),
trunc(sysdate),fnd_global.user_id,
incl_options,l_company,l_base,l_pl,l_num_items,'N',l_portfolio,perspective,trunc(sysdate),l_fwd_fx_rate,
l_ccy_a,l_ccy_b,'N',incl_options,'T',l_portfolio,
--to_date(l_date,'MON-YYYY'));
select (r.BID_PRICE + r.ASK_PRICE) / 2
from XTR_MARKET_PRICES r
where ((r.CURRENCY_A = l_ccy
and r.CURRENCY_B = 'USD') or
(r.CURRENCY_A = 'USD'
and r.CURRENCY_B = l_ccy))
and r.TERM_TYPE = 'S';
select m1.year_basis,m1.rounding_factor,m2.year_basis,m2.rounding_factor,
m3.year_basis,m3.rounding_factor,
round(decode(m1.divide_or_multiply,'*',1 / l_ccya_spot,l_ccya_spot),9),
decode(m1.divide_or_multiply,'*','BASE','CONTRA'),
round(decode(m2.divide_or_multiply,'*',1 / l_ccyb_spot,l_ccyb_spot),9),
decode(m2.divide_or_multiply,'*','BASE','CONTRA')
from XTR_MASTER_CURRENCIES_V m1,
XTR_MASTER_CURRENCIES_V m2,
XTR_MASTER_CURRENCIES_V m3
where m1.CURRENCY = l_ccya
and m2.CURRENCY = l_ccyb
and m3.CURRENCY = 'USD';
select CURRENCY,to_char(nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),'DD-MM-YYYY'),sum(CASHFLOW_AMOUNT),
CURRENCY_COMBINATION,sum(TRANSACTION_RATE),
decode(DEAL_TYPE,'FX',TRANSACTION_NUMBER,'FXO',TRANSACTION_NUMBER,1),
decode(DEAL_TYPE,'FX',nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),'FXO',nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),NULL),count(AMOUNT)
from XTR_DEAL_DATE_AMOUNTS_V
where STATUS_CODE = 'CURRENT'
and (AMOUNT_DATE >= trunc(SYSDATE)
or EXPOSURE_REF_DATE >=trunc(SYSDATE))
and COMPANY_CODE = l_company
and CASHFLOW_AMOUNT <> 0
and CURRENCY = substr(l_combin,1,3)
and nvl(PORTFOLIO_CODE,'%') like nvl(upper(l_portfolio),'%')
and CURRENCY_COMBINATION = upper(l_combin)
and (incl_options = 'Y' or (incl_options = 'N' and AMOUNT_TYPE NOT IN('FXOBUY','FXOSELL')))
and nvl(multiple_settlements,'N') <> 'Y'
group by CURRENCY,to_char(nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),'DD-MM-YYYY'),CURRENCY_COMBINATION,
decode(DEAL_TYPE,'FX',TRANSACTION_NUMBER,'FXO',TRANSACTION_NUMBER,1),
decode(DEAL_TYPE,'FX',nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),'FXO',nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),NULL);
select CURRENCY,to_char(AMOUNT_DATE,'DD-MM-YYYY'),sum(CASHFLOW_AMOUNT),
CURRENCY_COMBINATION,sum(TRANSACTION_RATE),
decode(DEAL_TYPE,'FX',TRANSACTION_NUMBER,'FXO',TRANSACTION_NUMBER,1),
decode(DEAL_TYPE,'FX',AMOUNT_DATE,'FXO',AMOUNT_DATE,NULL),count(AMOUNT)
from XTR_DEAL_DATE_AMOUNTS_V
where STATUS_CODE = 'CURRENT'
and AMOUNT_DATE >= trunc(SYSDATE)
and COMPANY_CODE = l_company
and ((deal_type='EXP' and perspective='^') or perspective <>'^')
and CASHFLOW_AMOUNT <> 0
and CURRENCY like upper(nvl(sel_ccy,'%'))
and nvl(PORTFOLIO_CODE,'%') like nvl(upper(l_portfolio),'%')
and CURRENCY_COMBINATION is NULL
and ((DEAL_SUBTYPE = 'INDIC' and incl_indic_exp = 'Y') or
DEAL_SUBTYPE <> 'INDIC')
and nvl(multiple_settlements,'N') <> 'Y'
group by CURRENCY,to_char(AMOUNT_DATE,'DD-MM-YYYY'),CURRENCY_COMBINATION,
decode(DEAL_TYPE,'FX',TRANSACTION_NUMBER,'FXO',TRANSACTION_NUMBER,1),
decode(DEAL_TYPE,'FX',AMOUNT_DATE,'FXO',AMOUNT_DATE,NULL);
select currency,sum(nvl(OPENING_BALANCE,0)),count(ACCOUNT_NUMBER)
from XTR_BANK_ACCOUNTS
where PARTY_CODE = l_company
and CURRENCY like upper(nvl(sel_ccy,'%'))
and nvl(PORTFOLIO_CODE,'%') like nvl(upper(l_portfolio),'%')
and nvl(SETOFF_ACCOUNT_YN,'N') <> 'Y'
group by currency;
select d.currency,sum((-1) * d.cashflow_amount) CASH_ON_HAND
from XTR_DEAL_DATE_AMOUNTS_V d,
XTR_ROLLOVER_TRANSACTIONS_V r
where d.STATUS_CODE = 'CURRENT'
and d.DEAL_TYPE = 'ONC'
and d.COMPANY_CODE = l_company
and d.CURRENCY like upper(nvl(sel_ccy,'%'))
and nvl(d.PORTFOLIO_CODE,'%') like nvl(upper(l_portfolio),'%')
and nvl(d.multiple_settlements,'N') = 'N'
and d.CASHFLOW_AMOUNT <> 0
and r.deal_number = d.deal_number
and r.transaction_number = d.transaction_number
and r.maturity_date is NULL
group by d.currency;
cursor SELECTED_CCY is
select CURRENCY
from XTR_FX_WHAT_IFS
where UNIQUE_REF_NUMBER = ref_number
and REVIEW = 'Y';
select distinct CURRENCY_FIRST||'/'||CURRENCY_SECOND
from XTR_BUY_SELL_COMBINATIONS
where CURRENCY_FIRST IN(select CURRENCY
from XTR_FX_WHAT_IFS
where UNIQUE_REF_NUMBER = ref_number)
or CURRENCY_SECOND IN(select CURRENCY
from XTR_FX_WHAT_IFS
where UNIQUE_REF_NUMBER = ref_number);
select currency,nvl(PERIOD_DESC,nvl(PERIOD_FROM,'0')||decode(nvl(TYPE_PERIOD_FROM_MONTH_YEAR,'M'),nvl(TYPE_PERIOD_TO_MONTH_YEAR,'M'),NULL,decode(nvl(TYPE_PERIOD_FROM_MONTH_YEAR,'M'),'M','Months','Years'))||'-'||
nvl(PERIOD_TO,'0')||decode(nvl(TYPE_PERIOD_TO_MONTH_YEAR,'M'),'M','Months','Years')) PERIOD_NAME,
add_months(trunc(count_months_from),decode(nvl(TYPE_PERIOD_FROM_MONTH_YEAR,'M'),'M',1,12)*nvl(PERIOD_FROM,0)) P_FROM,
decode(PERIOD_TO,NULL,to_date('01/01/2200','DD/MM/YYYY'),add_months(trunc(count_months_from),decode(nvl(TYPE_PERIOD_TO_MONTH_YEAR,'M'),'M',1,12)*PERIOD_TO)) P_TO,
FX_PERCENT_MAX,FX_PERCENT_MIN,period_desc
from XTR_INTEREST_RATE_BANDS
where currency like upper(nvl(p_sel_ccy,'%'))
and currency not in(select home_currency
from XTR_parties_v
where party_type='C');
SELECT nvl(sum(decode(CURRENCY_COMBINATION,NULL,AMOUNT,0)),0) exp_amt,
nvl(sum(decode(CURRENCY_COMBINATION,NULL,0,
decode(currency,l_fx_ccy,AMOUNT,-AMOUNT*round(TRANSACTION_RATE,5)))),0) fx_amt
from XTR_EXPOSURE_SUMMARY
where UNIQUE_REF_NUMBER = ref_number
and((currency_combination is null and currency = l_fx_ccy) or
(currency_combination is not null and
(substr(currency_combination,1,3) = l_fx_ccy or substr(currency_combination,5,3) = l_fx_ccy)))
and period_date >= l_p_from
and period_date < l_p_to
and hedge_trade_whatif = 'W';
select YEAR_BASIS, ROUNDING_FACTOR
from XTR_MASTER_CURRENCIES_V
where CURRENCY = l_ccy;
select YEAR_BASIS, ROUNDING_FACTOR
from XTR_MASTER_CURRENCIES_V
where CURRENCY = l_row_ccy;
delete from XTR_exposure_summary
where created_on < (trunc(sysdate) - 7);
delete from XTR_tmp_fx_exposure
where created_on < (trunc(sysdate) - 7);
open SELECTED_CCY;
fetch SELECTED_CCY INTO sel_ccy;
EXIT WHEN SELECTED_CCY%NOTFOUND;
insert into XTR_EXPOSURE_SUMMARY
(unique_ref_number,currency,period,amount,average_days,weighted_average,currency_combination,
transaction_rate,created_on,created_by,incl_fx_options,incl_indic_exposures,company,base_currency,
number_of_items,acct_balance,portfolio_code,hedge_or_trade_view,discount_rate,discounted_back_to,
discounted_value,yield_curve,forward_fx_rate,selected_ccy,selected_indic,selected_options,
hedge_trade_whatif,selected_portfolio,period_date)
values
(ref_number,l_ccy,l_date,round(l_amount,2),l_avg_days,l_weight_avg,l_combination,round(l_rate,5),
trunc(sysdate),fnd_global.user_id,
incl_options,incl_indic_exp,l_company,l_base_ccy,l_num_items,'N',l_portfolio,decode(perspective,'^','W',perspective),disc_rate,
trunc(sysdate),disc_value,l_yield_curve,l_fwd_fx_rate,nvl(p_sel_ccy,'%'),incl_indic_exp,incl_options,'W',l_portfolio,
to_date(l_date,'DD-MM-YYYY'));
insert into XTR_EXPOSURE_SUMMARY
(unique_ref_number,currency,period,amount,average_days,weighted_average,currency_combination,
transaction_rate,created_on,created_by,incl_fx_options,incl_indic_exposures,company,base_currency,
number_of_items,acct_balance,portfolio_code,hedge_or_trade_view,discount_rate,discounted_back_to,
discounted_value,yield_curve,selected_ccy,selected_indic,selected_options,
hedge_trade_whatif,selected_portfolio,period_date)
values
(ref_number,l_row_ccy,to_char(trunc(sysdate),'DD-MM-YYYY'),round(l_amount,2),1,0,null,null,trunc(sysdate),
fnd_global.user_id,incl_options,incl_indic_exp,l_company,l_base_ccy,l_num_items,'Y',l_portfolio,perspective,
0,trunc(sysdate),l_amount,l_yield_curve,nvl(p_sel_ccy,'%'),incl_indic_exp,incl_options,'W',l_portfolio,trunc(sysdate));
close SELECTED_CCY;
insert into XTR_EXPOSURE_SUMMARY
(unique_ref_number,currency,period,amount,average_days,weighted_average,currency_combination,
transaction_rate,created_on,created_by,incl_fx_options,incl_indic_exposures,company,base_currency,
number_of_items,acct_balance,portfolio_code,hedge_or_trade_view,discount_rate,discounted_back_to,
discounted_value,yield_curve,forward_fx_rate,selected_ccy,selected_indic,selected_options,
hedge_trade_whatif,selected_portfolio,period_date)
values
(ref_number,l_row_ccy,l_date,round(l_amount,2),l_avg_days,l_weight_avg,l_combination,round(l_rate,5),
trunc(sysdate),fnd_global.user_id,
incl_options,incl_indic_exp,l_company,l_base_ccy,l_num_items,'N',l_portfolio,decode(perspective,'^','W',perspective),disc_rate,
trunc(sysdate),disc_value,l_yield_curve,l_fwd_fx_rate,nvl(p_sel_ccy,'%'),incl_indic_exp,incl_options,'W',l_portfolio,
to_date(l_date,'DD-MM-YYYY'));
insert into XTR_TMP_FX_EXPOSURE
(unique_ref_number,currency,period_name,period_desc,FX_PERCENT_MAX,FX_PERCENT_MIN,
exp_amount,fx_amount,created_on,created_by,incl_fx_options,incl_indic_exposures,
company,base_currency,selected_base_currency,selected_ccy,selected_portfolio,yield_curve,
period_from,period_to,net_exposure,cover_to_forecast,max_amount,min_amount)
values
(ref_number,l_fx_ccy,l_period_name,l_period_desc,l_max,l_min,round(l_exp_amt,2),round(l_fx_amt,2),
trunc(sysdate),fnd_global.user_id,incl_options,incl_indic_exp,l_company,l_base_ccy,l_base_ccy,nvl(p_sel_ccy,'%'),
l_portfolio,l_yield_curve,l_p_from,l_p_to,nvl(round(l_exp_amt,2),0)+nvl(round(l_fx_amt,2),0),
decode(nvl(round(l_exp_amt,2),0),0,null,round(nvl(-100*round(l_fx_amt,2),0)/round(l_exp_amt,2),2)),
(nvl(l_max,0)*nvl(round(-l_exp_amt,2),0))/100-nvl(round(l_fx_amt,2),0),
(nvl(l_min,0)*nvl(round(-l_exp_amt,2),0))/100-nvl(round(l_fx_amt,2),0));
SELECT BID_RATE
FROM XTR_CURRENCY_CROSS_RATES
WHERE CURRENCY_FIRST=p_base_ccy
and CURRENCY_SECOND=p_contra_ccy
and rate_date in(select max(rate_date)
from XTR_spot_rates
where CURRENCY_FIRST=p_base_ccy
and CURRENCY_SECOND=p_contra_ccy
and to_char(rate_date,'DD-MM-YYYY')=to_char(p_date,'DD-MM-YYYY'));
SELECT USD_BASE_CURR_BID_RATE
FROM XTR_spot_rates
WHERE currency = l_ccy
and rate_date in(select max(rate_date)
from XTR_spot_rates
where currency=l_ccy
and to_char(rate_date,'DD-MM-YYYY')=to_char(p_date,'DD-MM-YYYY'));
select HCE_RATE
from XTR_spot_rates
where currency=p_base_ccy
and rate_date in(select max(rate_date)
from XTR_spot_rates
where currency = p_base_ccy
and to_char(rate_date,'DD-MM-YYYY')=to_char(p_date,'DD-MM-YYYY'));
select company_code,deal_type,currency,contra_ccy,currency_combination,
decode(deal_type,'FX','%',deal_subtype) deal_subtype,
limit_party,
product_type,
portfolio_code,
sum(nvl(amount_indic,1)*nvl(amount,0)) gross_principal,
sum(nvl(amount_indic,1)*nvl(amount,0)*nvl(transaction_rate,0)
/(decode(deal_type,'FX',1,'FXO',1,100))) weighted_amt,
count(distinct decode(deal_type,'ONC',transaction_number,deal_number)) no_of_deals
from XTR_MIRROR_DDA_LIMIT_ROW_V
where (amount_date >= l_date or deal_type = 'ONC' or deal_type = 'CMF')
and deal_type <>'CA'
group by company_code,deal_type,currency,contra_ccy,currency_combination,
decode(deal_type,'FX','%',deal_subtype),limit_party,product_type,portfolio_code;
select a.company_code,a.deal_type,a.currency,a.contra_ccy,a.currency_combination,
decode(a.deal_type,'FX','%',a.deal_subtype) subtype,
a.limit_party,
a.product_type,
a.portfolio_code,
round(sum(nvl(a.amount_indic,1)*nvl(a.amount,0)),0) gross_principal,
round(sum(nvl(a.amount_indic,1)*nvl(a.amount,0)*nvl(a.transaction_rate,0)
/(decode(a.deal_type,'FX',1,'FXO',1,100))),0) weighted_amt,
count(distinct a.deal_number) no_of_deals
from XTR_MIRROR_DDA_LIMIT_ROW_V a,
XTR_DEALS_V b
where a.amount_date > l_date
and a.deal_type not in('CA','ONC','CMF','IG')
and a.deal_number=b.deal_no
and (nvl(b.start_date,l_date) <= l_date or a.deal_type='FXO')
group by a.company_code,a.deal_type,a.currency,a.contra_ccy,a.currency_combination,
decode(a.deal_type,'FX','%',a.deal_subtype),a.limit_party,a.product_type,a.portfolio_code
union all
select a.company_code,a.deal_type,a.currency,a.contra_ccy,a.currency_combination,
a.deal_subtype subtype,
a.limit_party,
a.product_type,
a.portfolio_code,
round(sum(nvl(a.amount_indic,1)*nvl(a.amount,0)),0) gross_principal,
round(sum(nvl(a.amount_indic,1)*nvl(a.amount,0)*nvl(a.transaction_rate,0)/100),0) weighted_amt,
count(distinct decode(a.deal_type,'ONC',a.transaction_number,a.deal_number)) no_of_deals
from XTR_MIRROR_DDA_LIMIT_ROW_V a,
XTR_ROLLOVER_TRANSACTIONS_V b
where a.deal_type in('ONC','CMF','IG')
and a.deal_number=b.deal_number
and a.transaction_number=b.transaction_number
and nvl(b.start_date,l_date) <=l_date
group by a.company_code,a.deal_type,a.currency,a.contra_ccy,a.currency_combination,
a.deal_subtype,a.limit_party,a.product_type,a.portfolio_code;
select as_at_date
from XTR_cost_of_funds
where as_at_date < l_date
order by as_at_date desc;
select 1
from XTR_cost_of_funds
where as_at_date = l_date;
select rowid,deal_number
from XTR_mirror_dda_limit_row_V
where amount_date > l_date
and deal_type = 'TMM';
select INTEREST_RATE
from XTR_rollover_transactions_V
where deal_number=l_deal_no
and deal_type='TMM'
and start_date<=l_date
and maturity_date>l_date
order by start_date desc;
select COMPANY_CODE,CURRENCY,DEAL_TYPE,AMOUNT_DATE,TRANSACTION_RATE,
CONTRA_CCY,CURRENCY_COMBINATION,DEAL_SUBTYPE,
AMOUNT,HCE_AMOUNT,LIMIT_PARTY,PORTFOLIO_CODE,PRODUCT_TYPE,ACCOUNT_NO
from XTR_mirror_dda_limit_row_V
where deal_type='CA';
select nvl(hce_rate,1),year_basis
from XTR_master_currencies
where currency = l_ccy;
delete from XTR_cost_of_funds
where as_at_date = l_date;
insert into XTR_COST_OF_FUNDS
(AS_AT_DATE,COMPANY_CODE,CURRENCY,DEAL_TYPE,AVG_INTEREST_RATE,
CONTRA_CCY,CURRENCY_COMBINATION,DEAL_SUBTYPE,
GROSS_PRINCIPAL,HCE_GROSS_PRINCIPAL,INTEREST,HCE_INTEREST,
NO_OF_DAYS,NO_OF_DEALS,PARTY_CODE, PORTFOLIO_CODE,PRODUCT_TYPE,
WEIGHTED_AVG_PRINCIPAL,ACCOUNT_NO,CREATED_ON)
select l_ins_date,COMPANY_CODE,CURRENCY,DEAL_TYPE,AVG_INTEREST_RATE,
CONTRA_CCY,CURRENCY_COMBINATION,DEAL_SUBTYPE,
GROSS_PRINCIPAL,HCE_GROSS_PRINCIPAL,INTEREST,HCE_INTEREST,
NO_OF_DAYS,NO_OF_DEALS,PARTY_CODE, PORTFOLIO_CODE,PRODUCT_TYPE,
WEIGHTED_AVG_PRINCIPAL,ACCOUNT_NO,trunc(l_run_date)
from XTR_COST_OF_FUNDS where as_at_date=l_prv_date;
insert into XTR_COST_OF_FUNDS
(AS_AT_DATE,COMPANY_CODE,CURRENCY,DEAL_TYPE,AVG_INTEREST_RATE,
CONTRA_CCY,CURRENCY_COMBINATION,DEAL_SUBTYPE,
GROSS_PRINCIPAL,HCE_GROSS_PRINCIPAL,INTEREST,HCE_INTEREST,
NO_OF_DAYS,NO_OF_DEALS,PARTY_CODE, PORTFOLIO_CODE,PRODUCT_TYPE,
WEIGHTED_AVG_PRINCIPAL,ACCOUNT_NO,CREATED_ON)
select l_ins_date,COMPANY_CODE,CURRENCY,DEAL_TYPE,NULL,
CONTRA_CCY,CURRENCY_COMBINATION,DEAL_SUBTYPE,
0,0,0,0,
1,0,PARTY_CODE, PORTFOLIO_CODE,PRODUCT_TYPE,
0,ACCOUNT_NO,l_run_date
from XTR_COST_OF_FUNDS where as_at_date=l_prv_date;
update XTR_cost_of_funds
set AVG_INTEREST_RATE = round(bal.transaction_rate,5),
GROSS_PRINCIPAL = round(bal.amount,0),
HCE_GROSS_PRINCIPAL = round(bal.amount/l_hce_rate,0),
WEIGHTED_AVG_PRINCIPAL = round(bal.amount*bal.transaction_rate/100,0),
INTEREST = round(no_of_days*bal.amount*bal.transaction_rate/(100*l_year_basis),2),
HCE_INTEREST = round((no_of_days*bal.amount*bal.transaction_rate/(100*l_year_basis))/l_hce_rate,2)
where as_at_date >= bal.amount_date and as_at_date <>l_date
and deal_type='CA'
and deal_subtype=bal.deal_subtype
and currency=bal.currency
and company_code=bal.company_code
and account_no=bal.account_no
and nvl(portfolio_code,'@#@')=nvl(bal.portfolio_code,'@#@')
and nvl(product_type,'@#@')=nvl(bal.product_type,'@#@')
and nvl(party_code,'@#@')=nvl(bal.limit_party,'@#@');
update XTR_cost_of_funds
set AVG_INTEREST_RATE = round(bal.transaction_rate,5),
GROSS_PRINCIPAL = round(bal.amount,0),
HCE_GROSS_PRINCIPAL = round(bal.amount/l_hce_rate,0),
WEIGHTED_AVG_PRINCIPAL = round(bal.amount*bal.transaction_rate/100,0),
INTEREST = round(no_of_days*bal.amount*bal.transaction_rate/(100*l_year_basis),2),
HCE_INTEREST = round((no_of_days*bal.amount*bal.transaction_rate/(100*l_year_basis))/l_hce_rate,2)
where as_at_date =l_date
and deal_type='CA'
and deal_subtype=bal.deal_subtype
and currency=bal.currency
and company_code=bal.company_code
and account_no=bal.account_no
and nvl(portfolio_code,'@#@')=nvl(bal.portfolio_code,'@#@')
and nvl(product_type,'@#@')=nvl(bal.product_type,'@#@')
and nvl(party_code,'@#@')=nvl(bal.limit_party,'@#@');
insert into XTR_COST_OF_FUNDS
(AS_AT_DATE,COMPANY_CODE,CURRENCY,DEAL_TYPE,AVG_INTEREST_RATE,
CONTRA_CCY,CURRENCY_COMBINATION,DEAL_SUBTYPE,
GROSS_PRINCIPAL,HCE_GROSS_PRINCIPAL,INTEREST,NO_OF_DAYS,NO_OF_DEALS,PARTY_CODE,
PORTFOLIO_CODE,PRODUCT_TYPE,WEIGHTED_AVG_PRINCIPAL,ACCOUNT_NO,CREATED_ON)
values(l_date,bal.COMPANY_CODE,bal.CURRENCY,bal.DEAL_TYPE,round(bal.TRANSACTION_RATE,5),
bal.CONTRA_CCY,bal.CURRENCY_COMBINATION,bal.DEAL_SUBTYPE,
round(bal.AMOUNT,0),round(bal.HCE_AMOUNT,0),
round(bal.AMOUNT*bal.TRANSACTION_RATE/(100*l_year_basis),4),
1,1,bal.LIMIT_PARTY,bal.PORTFOLIO_CODE,bal.PRODUCT_TYPE,
round(bal.AMOUNT*bal.TRANSACTION_RATE/(100*l_year_basis),2),bal.ACCOUNT_NO,l_run_date);
update XTR_mirror_dda_limit_row
set transaction_rate=l_transaction_rate
where rowid=l_rowid;
update XTR_cost_of_funds
set AVG_INTEREST_RATE = l_avg_rate,
GROSS_PRINCIPAL = cof.gross_principal,
HCE_GROSS_PRINCIPAL = round(cof.GROSS_PRINCIPAL/l_hce_rate,0),
WEIGHTED_AVG_PRINCIPAL = cof.weighted_amt,
INTEREST = l_interest,
HCE_INTEREST =round(l_interest/l_hce_rate,2),
NO_OF_DAYS =1,
NO_OF_DEALS=cof.NO_OF_DEALS
where as_at_date =l_date
and company_code=cof.company_code
and deal_type=cof.deal_type
and deal_subtype=cof.subtype
and currency=cof.currency
and nvl(contra_ccy,'@#@')=nvl(cof.contra_ccy,'@#@')
and nvl(currency_combination,'@#@')=nvl(cof.currency_combination,'@#@')
and nvl(portfolio_code,'@#@')=nvl(cof.portfolio_code,'@#@')
and nvl(product_type,'@#@')=nvl(cof.product_type,'@#@')
and nvl(party_code,'@#@')=nvl(cof.limit_party,'@#@');
insert into XTR_COST_OF_FUNDS
(AS_AT_DATE,COMPANY_CODE,CURRENCY,DEAL_TYPE,AVG_INTEREST_RATE,
CONTRA_CCY,CURRENCY_COMBINATION,DEAL_SUBTYPE,
GROSS_PRINCIPAL,HCE_GROSS_PRINCIPAL,INTEREST,HCE_INTEREST,
NO_OF_DAYS,NO_OF_DEALS,PARTY_CODE, PORTFOLIO_CODE,PRODUCT_TYPE,
WEIGHTED_AVG_PRINCIPAL,ACCOUNT_NO,CREATED_ON)
values(l_date,cof.COMPANY_CODE,cof.CURRENCY,cof.DEAL_TYPE,
round(l_avg_rate,5),cof.CONTRA_CCY,cof.CURRENCY_COMBINATION,
cof.SUBTYPE,round(cof.GROSS_PRINCIPAL,0),round(cof.GROSS_PRINCIPAL/l_hce_rate,0),
l_interest,round(l_interest/l_hce_rate,2),1,cof.NO_OF_DEALS,cof.LIMIT_PARTY,
cof.PORTFOLIO_CODE,cof.PRODUCT_TYPE,cof.weighted_amt,'%',l_run_date);
select DISTINCT AS_AT_DATE
from XTR_COST_OF_FUNDS
where AS_AT_DATE >= L_REF_DATE
and (DEAL_TYPE in('ONC','CA') or AS_AT_DATE < L_AMOUNT_DATE)
ORDER BY AS_AT_DATE ASC;
select GROSS_PRINCIPAL,
GROSS_PRINCIPAL * AVG_INTEREST_RATE / decode(DEAL_TYPE,'FX',1,'FXO',1,100),
NO_OF_DAYS,ROWID
from XTR_COST_OF_FUNDS
where AS_AT_DATE = l_dist_date
and DEAL_TYPE = L_DEAL_TYPE
and COMPANY_CODE = L_COMPANY_CODE
and CURRENCY = L_CURRENCY
and nvl(CONTRA_CCY,'%')=nvl(L_CONTRA_CCY,'%')
and nvl(CURRENCY_COMBINATION,'%')=nvl(L_CURRENCY_COMBINATION,'%')
and DEAL_SUBTYPE = L_DEAL_SUBTYPE
and nvl(PRODUCT_TYPE,'%') = nvl(L_PRODUCT_TYPE,'%')
and nvl(PORTFOLIO_CODE,'%') = nvl(L_PORTFOLIO_CODE,'%')
and nvl(PARTY_CODE,'%') = nvl(L_PARTY_CODE,'%')
and nvl(ACCOUNT_NO,'%') = nvl(L_ACCOUNT,'%');
select AS_AT_DATE
from XTR_COST_OF_FUNDS
where AS_AT_DATE <= L_REF_DATE
order by as_at_date desc;
select AS_AT_DATE
from XTR_COST_OF_FUNDS
where AS_AT_DATE > L_REF_DATE
order by as_at_date asc;
select nvl(hce_rate,1),year_basis
from XTR_master_currencies
where currency = l_ccy;
select rowid row_id,deal_type,currency,gross_principal,avg_interest_rate
from XTR_cost_of_funds
where as_at_date = l_prv_date;
select decode(AS_AT_DATE,L_REF_DATE,no_of_days,as_at_date - L_REF_DATE)
from XTR_COST_OF_FUNDS
where AS_AT_DATE >= L_REF_DATE
order by AS_AT_DATE;
select AS_AT_DATE,CURRENCY,DEAL_TYPE,GROSS_PRINCIPAL,AVG_INTEREST_RATE,ROWID
from XTR_COST_OF_FUNDS
where AS_AT_DATE = L_REF_DATE
and (DEAL_TYPE in('ONC','CA') or AS_AT_DATE < L_AMOUNT_DATE)
and DEAL_TYPE = L_DEAL_TYPE
and COMPANY_CODE = L_COMPANY_CODE
and CURRENCY = L_CURRENCY
and nvl(CONTRA_CCY,'%')=nvl(L_CONTRA_CCY,'%')
and nvl(CURRENCY_COMBINATION,'%')=nvl(L_CURRENCY_COMBINATION,'%')
and DEAL_SUBTYPE = L_DEAL_SUBTYPE
and nvl(PRODUCT_TYPE,'%') = nvl(L_PRODUCT_TYPE,'%')
and nvl(PORTFOLIO_CODE,'%') = nvl(L_PORTFOLIO_CODE,'%')
and nvl(PARTY_CODE,'%') = nvl(L_PARTY_CODE,'%')
and nvl(ACCOUNT_NO,'%') = nvl(L_ACCOUNT,'%')
order by AS_AT_DATE desc;
select round(l_interest / s.HCE_RATE,2),
round(l_gross / s.HCE_RATE,0)
from XTR_MASTER_CURRENCIES s
where s.CURRENCY = L_CURRENCY;
update XTR_cost_of_funds
set no_of_days =l_no_of_days
where rowid=c.row_id;
update XTR_cost_of_funds
set interest = l_interest,
hce_interest = l_interest_hce,
no_of_days =l_no_of_days
where rowid = c.row_id;
insert into XTR_COST_OF_FUNDS
(as_at_date,company_code,currency,deal_type,
deal_subtype,party_code,portfolio_code,product_type,
gross_principal,hce_gross_principal,
weighted_avg_principal,avg_interest_rate,interest,
hce_interest,no_of_days,no_of_deals,contra_ccy,
currency_combination,account_no,created_on)
select L_REF_DATE,company_code,currency,deal_type,
deal_subtype,party_code,portfolio_code,product_type,
gross_principal,hce_gross_principal,
weighted_avg_principal,avg_interest_rate,c_interest,
c_interest_hce,c_no_of_days,no_of_deals,contra_ccy,
currency_combination,account_no,L_REF_DATE
from XTR_cost_of_funds
where rowid = c.row_id;
insert into XTR_COST_OF_FUNDS
(as_at_date,company_code,currency,deal_type,
deal_subtype,party_code,portfolio_code,product_type,
gross_principal,hce_gross_principal,
weighted_avg_principal,avg_interest_rate,interest,
hce_interest,no_of_days,no_of_deals,contra_ccy,
currency_combination,account_no,created_on)
values
(L_REF_DATE,L_COMPANY_CODE,L_CURRENCY,L_DEAL_TYPE,
L_DEAL_SUBTYPE,L_PARTY_CODE,L_PORTFOLIO_CODE,
L_PRODUCT_TYPE,0,0,0,0,0,
0,l_no_of_days,0,L_CONTRA_CCY,L_CURRENCY_COMBINATION,L_ACCOUNT,
trunc(sysdate));
delete from XTR_COST_OF_FUNDS
where rowid=l_rowid;
update XTR_COST_OF_FUNDS
set GROSS_PRINCIPAL = round(l_gross,0),
HCE_GROSS_PRINCIPAL = round(l_gross_hce,0),
AVG_INTEREST_RATE = round(l_rate,5),
HCE_INTEREST = round(l_interest_hce,2),
WEIGHTED_AVG_PRINCIPAL = round(l_wavg,0),
INTEREST = round(l_interest,2),
NO_OF_DEALS=nvl(NO_OF_DEALS,0)+L_ACTION_INDIC
where rowid=l_rowid;
insert into XTR_COST_OF_FUNDS
(as_at_date,company_code,currency,deal_type,
deal_subtype,party_code,portfolio_code,product_type,
gross_principal,hce_gross_principal,
weighted_avg_principal,avg_interest_rate,interest,
hce_interest,no_of_days,no_of_deals,contra_ccy,
currency_combination,account_no,created_on)
values
(nvl(l_dist_date,L_REF_DATE),L_COMPANY_CODE,L_CURRENCY,L_DEAL_TYPE,
L_DEAL_SUBTYPE,L_PARTY_CODE,L_PORTFOLIO_CODE,
L_PRODUCT_TYPE,round(l_gross,0),round(l_gross_hce,0),round(l_wavg,0),round(l_rate,5),round(l_interest,2),
round(l_interest_hce,2),nvl(l_days,0),1,L_CONTRA_CCY,L_CURRENCY_COMBINATION,L_ACCOUNT,
trunc(sysdate));