XTR Admin - Average FX Rates
Description
Categories: BI Publisher
Application: Treasury
Source: Admin - Average FX Rates Report (XML)
Short Name: XTRFXAVG_XML
DB package: XTR_XTRFXAVG_XMLP_PKG
Source: Admin - Average FX Rates Report (XML)
Short Name: XTRFXAVG_XML
DB package: XTR_XTRFXAVG_XMLP_PKG
Run
XTR Admin - Average FX Rates and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |