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 |