XTR Positions - Treasury Cashflows by Account

select
   d.company_code                            COMPANY,
   upper(c.full_name)                        COMPANY_NAME,
   nvl(d.company_account,'NOT ALLOCATED TO A/C')  ACCT_NOS,
   d.currency_code                              CCY,
   decode(nvl(d.company_account,'NOT ALLOCATED TO A/C')
     ,'NOT ALLOCATED TO A/C',' '
     ,'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)/:amt_unit2                       FX_IN,
   round(sum(decode(t.fx_mm_exp_flag
               ,'MM',d.amount
               ,0)),0)/:amt_unit2                       MM_IN,
   round(sum(decode(t.fx_mm_exp_flag
               ,'EX',d.amount
               ,0)),0)/:amt_unit2                       EXP,
   round(sum(d.amount),0)/:amt_unit2           NET_FLOW,
   ' '                                       INDIC,
   count(d.transaction_number)               NUM_TRANS,
	XTR_XTRCFACC_XMLP_PKG.open_bal1formula(nvl(d.company_account,'NOT ALLOCATED TO A/C'),d.currency_code,d.company_code) OPEN_BAL1,
	XTR_XTRCFACC_XMLP_PKG.OPEN_BAL_p OPEN_BAL,
	XTR_XTRCFACC_XMLP_PKG.rolling_bal1formula(:ACCOUNT_BAL) ROLLING_BAL1,
	XTR_XTRCFACC_XMLP_PKG.ROLLING_BAL_p ROLLING_BAL
from XTR_CASHFLOWS_V d,
     XTR_DEAL_TYPES_V t,
     XTR_PARTIES_V c
where d.dda_deal_type = t.deal_type
and d.company_code = c.party_code
and d.amount != 0
and d.dda_deal_type != 'CA'
and nvl(d.multiple_settlements,'N') = 'N'
and d.company_code like nvl(upper(:COMPANY_CODE2),d.company_code)
and d.currency_code like nvl(upper(:CURRENCY2),d.currency_code)
and d.trx_date between :SETTLE_FROM_DATE2  and  :SETTLE_TO_DATE2
and nvl(d.company_account,'%') like nvl(upper(:ACCOUNT_NUM2),'%')
and ((d.dda_deal_type != 'FXO' and nvl(upper(:INCL_CCY_OPT2),'Y') = 'N') or
      nvl(upper(:INCL_CCY_OPT2),'Y') = 'Y')
and (  (nvl(upper(:EXP_TYPES2), 'ALL') = 'ALL')
         or
           (upper(:EXP_TYPES2) = 'FIRM' and dda_deal_subtype <> 'INDIC')
         or
           (upper(:EXP_TYPES2) = 'INDICATIVE' and dda_deal_subtype = 'INDIC'))
                               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')
group by d.company_code,c.full_name,d.currency_code,nvl(d.company_account,'NOT ALLOCATED TO A/C'),
         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.company_code                            COMPANY,
   upper(c.full_name)                        COMPANY_NAME,
   nvl(r.principal_account_no,'NOT ALLOCATED TO A/C')
                                             ACCT_NOS,
   r.currency                                CCY,
   decode(nvl(r.principal_account_no,'NOT ALLOCATED TO A/C')
     ,'NOT ALLOCATED TO A/C',' '
     ,'Opening Balance   --->')              OPEN_BAL2,
   decode(nvl(upper(:DAY_WEEK_MONTH2),'WEEK')
     ,'DAY',:SETTLE_FROM_DATE2
     ,'WEEK',trunc(:SETTLE_FROM_DATE2,'WW') + 7
     ,last_day(:SETTLE_FROM_DATE2)) ADATE,
   'A'                                       ORD_COLUMN,
   0                                         FX_IN,
   round(sum(decode(r.deal_subtype
               ,'FUND',(-1)
               ,1) * r.principal_adjust),0)/:amt_unit2  MM_IN,
   0                                         EXP,
   0                                         NET_FLOW,
   '*'                                        INDIC,
   count(r.deal_number)                      NUM_TRANS,
	XTR_XTRCFACC_XMLP_PKG.open_bal1formula(nvl(r.principal_account_no,'NOT ALLOCATED TO A/C'),r.currency,r.company_code ) OPEN_BAL1,
	XTR_XTRCFACC_XMLP_PKG.OPEN_BAL_p OPEN_BAL,
	XTR_XTRCFACC_XMLP_PKG.rolling_bal1formula(:ACCOUNT_BAL) ROLLING_BAL1,
	XTR_XTRCFACC_XMLP_PKG.ROLLING_BAL_p ROLLING_BAL
/*FROM_DATE2
     ,'WEEK',trunc(:SETTLE_FROM_DATE2,'WW') + 7
     ,last_day(:SETTLE_FROM_DATE2)) ADATE,
   'A'                                       ORD_COLUMN,
   0                                         FX_IN,
   round(sum(decode(r.deal_subtype
               ,'FUND',(-1)
               ,1) * r.principal_adjust),0)/:amt_unit2  MM_IN,
   0                                         EXP,
   0                                         NET_FLOW,
   '*'                                        INDIC,
   count(r.deal_number)                      NUM_TRANS*/
from XTR_ROLLOVER_TRANSACTIONS_V r,
     XTR_PARTIES_V c
where r.deal_type = 'ONC'
and r.status_code = 'CURRENT'
and r.company_code = c.party_code
and r.company_code like nvl(upper(:COMPANY_CODE2),r.company_code)
and r.currency like nvl(upper(:CURRENCY2),'%')
and r.start_date < :SETTLE_FROM_DATE2
and nvl(r.principal_account_no,'%') like nvl(upper(:ACCOUNT_NUM2),'%')
and nvl(r.principal_adjust,0) != 0
and r.maturity_date is NULL
and r.company_code = c.party_code
group by r.company_code,c.full_name,r.currency,nvl(r.principal_account_no,'NOT ALLOCATED TO A/C'),
         decode(nvl(upper(:DAY_WEEK_MONTH2),'WEEK')
           ,'DAY',:SETTLE_FROM_DATE2
           ,'WEEK',trunc(:SETTLE_FROM_DATE2,'WW') + 7
           ,last_day(:SETTLE_FROM_DATE2))
order by 1,3,6,7