XTR Positions - Interest Rate Hedges

select b.user_deal_type,a.deal_no,a.currency,a.start_date,a.maturity_date,
a.face_value_amount/nvl(:AMT_UNIT2,1000000) face_value_amount,
a.interest_rate,
decode(sign(:dt0+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt0),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt0,
decode(sign(:dt1+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt1),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt1,
decode(sign(:dt2+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt2),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt2,
decode(sign(:dt3+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt3),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt3,
decode(sign(:dt4+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt4),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt4,
decode(sign(:dt5+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt5),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt5,
decode(sign(:dt6+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt6),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt6,
decode(sign(:dt7+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt7),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt7,
decode(sign(:dt8+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt8),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt8,
decode(sign(:dt9+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt9),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt9,
decode(sign(:dt10+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt10),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt10,
decode(sign(:dt11+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt11),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt11
from XTR_deals_V a 
, XTR_deal_types b
where a.deal_type='FRA' 
and a.deal_type = b.deal_type
and a.currency like nvl(:CCY2,a.currency) 
and nvl(a.deal_subtype,'*')=nvl(:p_fund_invest,'*')
and a.start_date <=to_date(to_char(last_day(:DATE_TO2),'DD-MON-YYYY'),'DD-MON-YYYY')
and a.maturity_date >=to_date(to_char(:DATE_FROM2,'MONYYYY'),'MONYYYY') 
and a.company_code = nvl(:p_company, a.company_code)
and a.portfolio_code like nvl(:p_portfolio, a.portfolio_code)
union
select b.user_deal_type,a.deal_no,a.currency,a.start_date,a.maturity_date,
a.face_value_amount/nvl(:AMT_UNIT2,1000000) face_value_amount,a.interest_rate,
decode(sign(:dt0+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt0),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt0,
decode(sign(:dt1+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt1),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt1,
decode(sign(:dt2+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt2),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt2,
decode(sign(:dt3+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt3),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt3,
decode(sign(:dt4+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt4),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt4,
decode(sign(:dt5+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt5),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt5,
decode(sign(:dt6+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt6),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt6,
decode(sign(:dt7+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt7),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt7,
decode(sign(:dt8+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt8),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt8,
decode(sign(:dt9+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt9),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt9,
decode(sign(:dt10+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt10),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt10,
decode(sign(:dt11+1-a.start_date),1,decode(sign(a.maturity_date+1-:dt11),1,a.face_value_amount/nvl(:AMT_UNIT2,1000000),null),null) amt11
from XTR_deals_V a 
, XTR_deal_types b
where a.deal_type='IRO' 
and a.deal_type = b.deal_type
and a.currency like nvl(:CCY2,a.currency) 
and a.deal_subtype='BCAP'
and a.start_date <=to_date(to_char(last_day(:DATE_TO2),'DD-MON-YYYY'),'DD-MON-YYYY')
and a.maturity_date >=to_date(to_char(:DATE_FROM2,'MONYYYY'),'MONYYYY')  
and a.company_code = nvl(:p_company, a.company_code)
and a.portfolio_code like nvl(:p_portfolio, a.portfolio_code)
union
select c.user_deal_type,a.deal_no,a.currency,a.start_date,a.maturity_date,
a.face_value_amount/nvl(:AMT_UNIT2,1000000) face_value_amount,a.interest_rate,
sum(decode(sign(:dt0+1-b.start_date),1,decode(sign(b.maturity_date+1-:dt0),1,b.balance_out/nvl(:AMT_UNIT2,1000000),null),null)) amt0,
sum(decode(sign(:dt1+1-b.start_date),1,decode(sign(b.maturity_date+1-:dt1),1,b.balance_out/nvl(:AMT_UNIT2,1000000),null),null)) amt1,
sum(decode(sign(:dt2+1-b.start_date),1,decode(sign(b.maturity_date+1-:dt2),1,b.balance_out/nvl(:AMT_UNIT2,1000000),null),null)) amt2,
sum(decode(sign(:dt3+1-b.start_date),1,decode(sign(b.maturity_date+1-:dt3),1,b.balance_out/nvl(:AMT_UNIT2,1000000),null),null)) amt3,
sum(decode(sign(:dt4+1-b.start_date),1,decode(sign(b.maturity_date+1-:dt4),1,b.balance_out/nvl(:AMT_UNIT2,1000000),null),null)) amt4,
sum(decode(sign(:dt5+1-b.start_date),1,decode(sign(b.maturity_date+1-:dt5),1,b.balance_out/nvl(:AMT_UNIT2,1000000),null),null)) amt5,
sum(decode(sign(:dt6+1-b.start_date),1,decode(sign(b.maturity_date+1-:dt6),1,b.balance_out/nvl(:AMT_UNIT2,1000000),null),null)) amt6,
sum(decode(sign(:dt7+1-b.start_date),1,decode(sign(b.maturity_date+1-:dt7),1,b.balance_out/nvl(:AMT_UNIT2,1000000),null),null)) amt7,
sum(decode(sign(:dt8+1-b.start_date),1,decode(sign(b.maturity_date+1-:dt8),1,b.balance_out/nvl(:AMT_UNIT2,1000000),null),null)) amt8,
sum(decode(sign(:dt9+1-b.start_date),1,decode(sign(b.maturity_date+1-:dt9),1,b.balance_out/nvl(:AMT_UNIT2,1000000),null),null)) amt9,
sum(decode(sign(:dt10+1-b.start_date),1,decode(sign(b.maturity_date+1-:dt10),1,b.balance_out/nvl(:AMT_UNIT2,1000000),null),null)) amt10,
sum(decode(sign(:dt11+1-b.start_date),1,decode(sign(b.maturity_date+1-:dt11),1,b.balance_out/nvl(:AMT_UNIT2,1000000),null),null)) amt11
from xtr_deals_v a ,
xtr_rollover_transactions_V b ,
xtr_deal_types c
where a.deal_type ='IRS' 
and a.deal_type = c.deal_type
and a.currency like nvl(:CCY2,a.currency) 
and a.start_date <=to_date(to_char(last_day(:DATE_TO2),'DD-MON-YYYY'),'DD-MON-YYYY')
and a.maturity_date >=to_date(to_char(:DATE_FROM2,'MONYYYY'),'MONYYYY')  
and nvl(a.deal_subtype,'*')=nvl(:p_fund_invest,'*')
and a.fixed_or_floating_rate='FIXED'
and a.deal_no=b.deal_number
and a.company_code = nvl(:p_company, a.company_code)
and a.portfolio_code like nvl(:p_portfolio, a.portfolio_code)
group by c.user_deal_type,a.deal_no,a.currency,
a.start_date, a.maturity_date, a.face_value_amount/nvl(:AMT_UNIT2,1000000), a.interest_rate 
ORDER BY 3 ASC,1 ASC