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 |