XTR Term Money - Retail Term Money Aging

select r.deal_number          DEAL_NUM2,
       months_between(:AS_AT_DATE2,r.maturity_date) 
                              NUMDAY,
       decode(r.accum_interest
        ,0,0
        ,decode(sign(nvl(r.accum_interest,0) - nvl(r.accum_interest_bf,0))
           ,1,abs(nvl(round(r.accum_interest,2),0) - nvl(round(r.accum_interest_bf,2),0))
           ,round(r.accum_interest,2))) INT_OVERDUE,
      decode(sign((nvl(r.expected_balance_bf,0) - nvl(r.expected_balance_out,0))
                - (nvl(r.balance_out_bf,0) - nvl(r.balance_out,0)))
        ,0,0
        ,-1,nvl(r.expected_balance_bf,0) - nvl(r.expected_balance_out,0)
        ,(nvl(round(r.expected_balance_bf,2),0) - nvl(round(r.expected_balance_out,2),0))
               - (nvl(round(r.balance_out_bf,2),0) - nvl(round(r.balance_out,2),0)))
                              BAL_OVERDUE,
       r.start_date           START_DATE,
       r.maturity_date        MAT_DATE,
/*
XTR_XTRTMODR_XMLP_PKG.int_component1formula() INT_COMPONENT1, 
	XTR_XTRTMODR_XMLP_PKG.bal_component1formula() BAL_COMPONENT1, 
	XTR_XTRTMODR_XMLP_PKG.tot_overdue1formula() TOT_OVERDUE1, 
	XTR_XTRTMODR_XMLP_PKG.curr1formula() CURR1, 
	XTR_XTRTMODR_XMLP_PKG.day30p1formula() DAY30P1, 
	XTR_XTRTMODR_XMLP_PKG.day60p1formula() DAY60P1, 
	XTR_XTRTMODR_XMLP_PKG.day90p1formula() DAY90P1,
	*/
	XTR_XTRTMODR_XMLP_PKG.INT_COMPONENT_p INT_COMPONENT,
	XTR_XTRTMODR_XMLP_PKG.BAL_COMPONENT_p BAL_COMPONENT,
	XTR_XTRTMODR_XMLP_PKG.TOT_OVERDUE_p TOT_OVERDUE,
	XTR_XTRTMODR_XMLP_PKG.CURR_p CURR,
	XTR_XTRTMODR_XMLP_PKG.DAY30P_p DAY30P,
	XTR_XTRTMODR_XMLP_PKG.DAY60P_p DAY60P,
	XTR_XTRTMODR_XMLP_PKG.DAY90P_p DAY90P
from XTR_ROLLOVER_TRANSACTIONS_V r
where (r.accum_interest > 0 or
     ((r.expected_balance_out - r.balance_out) < 0))
and r.maturity_date <= nvl(:AS_AT_DATE2, r.maturity_date)
and r.maturity_date >= (select max(s.maturity_date)
                        from XTR_ROLLOVER_TRANSACTIONS_V s,
                             XTR_DEALS_V d
                        where s.deal_number = r.deal_number
                        and s.maturity_date <= nvl(:AS_AT_DATE2, s.maturity_date)
                        and ((s.accum_interest = 0 and
                               s.expected_balance_out >= s.balance_out) or
                             (s.deal_number = d.deal_no and 
                               s.start_date = d.start_date and
                                s.settle_date is NULL))) 
 and r.deal_number=:DEAL_NUM
order by r.start_date desc