XTR Term Money - Retail Term Money Overdues

select r.deal_number          DEAL_NUM2,
       ' '                    NILL_FIELD,
      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_OVERDUE2,
      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_OVERDUE2,
       r.start_date           START_DATE,
       r.maturity_date        MAT_DATE,
       r.pi_amount_due   PI_AMOUNT_DUE, 
/*	XTR_XTRTMAOD_XMLP_PKG.int_component1formula(:INT_OD_RUNNING, 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) INT_COMPONENT1, 
	XTR_XTRTMAOD_XMLP_PKG.bal_component1formula(:BAL_RUNNING, 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) BAL_COMPONENT1, */
	XTR_XTRTMAOD_XMLP_PKG.tot_overdue1formula() TOT_OVERDUE1, 
/*	XTR_XTRTMAOD_XMLP_PKG.prin_amt_dueformula(r.pi_amount_due) INT_AMT_DUE,*/
/*	XTR_XTRTMAOD_XMLP_PKG.INT_COMPONENT_p INT_COMPONENT,
	XTR_XTRTMAOD_XMLP_PKG.BAL_COMPONENT_p BAL_COMPONENT,*/
	XTR_XTRTMAOD_XMLP_PKG.TOT_OVERDUE_p TOT_OVERDUE
from XTR_ROLLOVER_TRANSACTIONS_V r
where nvl(upper(:SHOW_DETAILS2),'N') = 'Y'
and (r.accum_interest > 0 or
     ((r.expected_balance_out - r.balance_out) < 0))
and r.maturity_date <= :AS_AT_DATE2
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 <= :AS_AT_DATE2
                        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 r.deal_number=:DEAL_NUM
order by r.start_date desc