XTR Admin - Average FX Rates

Description
Categories: BI Publisher
Columns: Company Code, Currency Combination, Base Ccy, Base Sales Amt, Contra Buy Amt, Base Buy Amt, Contra Sales Amt, Avg Rate1, Avg Rate2, Calc ...
Application: Treasury
Source: Admin - Average FX Rates Report (XML)
Short Name: XTRFXAVG_XML
DB package: XTR_XTRFXAVG_XMLP_PKG
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
Parameter Name SQL text Validation
Company
 
LOV Oracle
Base Currency
 
LOV Oracle
Contra Currency
 
LOV Oracle
Reference Date From
 
Date
Reference Date To
 
Date
Show Contracts
 
LOV Oracle
Date Type
 
LOV Oracle
Factor
 
LOV Oracle