XTR Positions - Treasury Consolidated Cashflows

select
   d.currency_code                                CCY,
   'Opening Balance'                  OPEN_BAL2,
   decode(nvl(upper(:DAY_WEEK_MONTH2),'WEEK')
     ,'DAY',d.trx_date
     ,'WEEK',trunc(d.trx_date,'WW') + 7
     ,last_day(d.trx_date))               ADATE,
   'B'                                       ORD_COLUMN,
   round(sum(decode(t.fx_mm_exp_flag
               ,'FX',d.amount
               ,0)),0)/:L_amt_unit2                       FX_IN,
   round(sum(decode(t.fx_mm_exp_flag
               ,'MM',d.amount, 'EM', d.amount
               ,0)),0)/:L_AMT_UNIT2                       MM_IN,
   round(sum(decode(t.fx_mm_exp_flag
               ,'EX',d.amount
               ,0)),0)/:L_AMT_UNIT2                       EXP,
   round(sum(d.amount),0)/:L_AMT_UNIT2           NET_FLOW,
   ' '                                       INDIC,
   count(d.transaction_number)               NUM_TRANS, 
	XTR_XTRCFCON_XMLP_PKG.open_bal1formula(d.currency_code) OPEN_BAL1,
	XTR_XTRCFCON_XMLP_PKG.OPEN_BAL_p OPEN_BAL, 
	XTR_XTRCFCON_XMLP_PKG.rolling_bal1formula() ROLLING_BAL1,
	XTR_XTRCFCON_XMLP_PKG.ROLLING_BAL_p ROLLING_BAL
from XTR_CASHFLOWS_V d,
    XTR_DEAL_TYPES_V t
where d.dda_deal_type = t.deal_type
and d.amount != 0
and d.dda_deal_type != 'CA'
and nvl(d.multiple_settlements,'N') = 'N'
and d.currency_code like nvl(upper(:CURRENCY2),'%')
and ((d.dda_deal_type != 'FXO' and nvl(upper(:INCL_CCY_OPT2),'Y') = 'N') or
      nvl(upper(:INCL_CCY_OPT2),'Y') = 'Y')
 and (d.dda_DEAL_TYPE not in('EXP','EXT') or (d.dda_DEAL_TYPE in ('EXP','EXT')  &P_1 ) )
                                 and ((d.dda_deal_type != 'FXO' and nvl(upper(:INCL_CCY_OPT2),'Y') = 'N') or
     (d.dda_deal_type = 'FXO' and d.type_of_amount = 'PREMIUM' and
      nvl(upper(:INCL_CCY_OPT2),'Y') = 'N') or
      nvl(upper(:INCL_CCY_OPT2),'Y') = 'Y')
and d.trx_date between :SETTLE_FROM_DATE2 and :SETTLE_TO_DATE2
group by d.currency_code,decode(nvl(upper(:DAY_WEEK_MONTH2),'WEEK')
                      ,'DAY',d.trx_date
                      ,'WEEK',trunc(d.trx_date,'WW') + 7
                      ,last_day(d.trx_date))
UNION
select
   r.currency                                   CCY,
   'Opening Balance'                     OPEN_BAL2,
   decode(nvl(upper(:DAY_WEEK_MONTH2),'WEEK')
     ,'DAY',trunc(:SETTLE_FROM_DATE2)
     ,'WEEK',trunc(trunc(:SETTLE_FROM_DATE2),'WW') + 7
     ,last_day(trunc(:SETTLE_FROM_DATE2)))    ADATE,
   'A'                                          ORD_COLUMN,
   0                                            FX_IN,
   round(sum(decode(r.deal_subtype
               ,'FUND',(-1)
               ,1) * r.principal_adjust),0)/:L_AMT_UNIT2     MM_IN,
   0                                            EXP,
  0		           NET_FLOW,
   '*'                                          INDIC,
   count(r.deal_number)                         NUM_TRANS, 
	XTR_XTRCFCON_XMLP_PKG.open_bal1formula(r.currency) OPEN_BAL1,
	XTR_XTRCFCON_XMLP_PKG.OPEN_BAL_p OPEN_BAL, 
	XTR_XTRCFCON_XMLP_PKG.rolling_bal1formula() ROLLING_BAL1,
	XTR_XTRCFCON_XMLP_PKG.ROLLING_BAL_p ROLLING_BAL
from XTR_ROLLOVER_TRANSACTIONS_V r
where r.deal_type = 'ONC'
and r.status_code = 'CURRENT'
and r.currency like nvl(upper(:CURRENCY2),'%')
and r.start_date < :SETTLE_FROM_DATE2
and nvl(r.principal_adjust,0) != 0
and r.maturity_date is NULL
group by r.currency,decode(nvl(upper(:DAY_WEEK_MONTH2),'WEEK')
                      ,'DAY',trunc(:SETTLE_FROM_DATE2)
                      ,'WEEK',trunc(trunc(:SETTLE_FROM_DATE2),'WW') + 7
                      ,last_day(trunc(:SETTLE_FROM_DATE2)))
order by 1,3,4