XTR Admin - Average FX Rates

SELECT company_code,currency_combination,currency base_ccy,
round(sum(decode(sign(cashflow_amount),-1,cashflow_amount,0)),0)/:amt_unit2  base_sales_amt,
round(sum(decode(sign(cashflow_amount),-1,-1*transaction_rate*cashflow_amount,0)),0)/:amt_unit2 contra_buy_amt,
round(sum(decode(sign(cashflow_amount),-1,0,cashflow_amount)),0)/:amt_unit2 base_buy_amt,
round(sum(decode(sign(cashflow_amount),-1,0,-1*transaction_rate*cashflow_amount)),0)/:amt_unit2 contra_sales_amt, 
	XTR_XTRFXAVG_XMLP_PKG.avg_rate1formula(round ( sum ( decode ( sign ( cashflow_amount ) , - 1 , 0 , cashflow_amount ) ) , 0 ) / :amt_unit2, round ( sum ( decode ( sign ( cashflow_amount ) , - 1 , 0 , - 1 * transaction_rate * cashflow_amount ) ) , 0 ) / :amt_unit2) avg_rate1, 
	XTR_XTRFXAVG_XMLP_PKG.avg_rate2formula(round ( sum ( decode ( sign ( cashflow_amount ) , - 1 , cashflow_amount , 0 ) ) , 0 ) / :amt_unit2, round ( sum ( decode ( sign ( cashflow_amount ) , - 1 , - 1 * transaction_rate * cashflow_amount , 0 ) ) , 0 ) / :amt_unit2) avg_rate2, 
	XTR_XTRFXAVG_XMLP_PKG.calcformula(round ( sum ( decode ( sign ( cashflow_amount ) , - 1 , 0 , cashflow_amount ) ) , 0 ) / :amt_unit2, round ( sum ( decode ( sign ( cashflow_amount ) , - 1 , cashflow_amount , 0 ) ) , 0 ) / :amt_unit2, round ( sum ( decode ( sign ( cashflow_amount ) , - 1 , - 1 * transaction_rate * cashflow_amount , 0 ) ) , 0 ) / :amt_unit2, round ( sum ( decode ( sign ( cashflow_amount ) , - 1 , 0 , - 1 * transaction_rate * cashflow_amount ) ) , 0 ) / :amt_unit2) calc,
	XTR_XTRFXAVG_XMLP_PKG.Base_net_amt_p Base_net_amt,
	XTR_XTRFXAVG_XMLP_PKG.contra_net_amt_p contra_net_amt,
	XTR_XTRFXAVG_XMLP_PKG.avg_rate3_p avg_rate3
FROM xtr_deal_date_amounts_v
 where deal_type='FX' and status_code='CURRENT'
 and decode(nvl(:date_base2,'MATURITY DATE'),'MATURITY DATE',amount_date,transaction_date)  >= :MATURING_FROM2
  and ((decode(nvl(:date_base2,'MATURITY DATE'),'MATURITY DATE',amount_date,transaction_date) <=:MATURING_TO2  and :MATURING_TO2 is not null) or :MATURING_TO2 is null) 
 and limit_code is not null
 and cashflow_amount !=0
 and substr(currency_combination,1,3) like   nvl(:currency_a2, substr(currency_combination,1,3))
 and substr(currency_combination,5,3) like nvl(:currency_b2,substr(currency_combination,5,3))
 and company_code like nvl(:p_company,'%')
group by company_code,currency_combination,currency
 order by company_code,currency_combination,currency