XTR Term Money - Retail Term Money Aging

Description
Categories: BI Publisher
Application: Treasury
Source: Term Money - Retail Term Money Aging Report (XML)
Short Name: XTRTMODR_XML
DB package: XTR_XTRTMODR_XMLP_PKG
Run XTR Term Money - Retail Term Money Aging and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Product Type
 
LOV Oracle
Counterparty
 
LOV Oracle
Deal Number
 
LOV Oracle
As of Date
 
Date
Sort By
 
LOV Oracle
Factor
 
LOV Oracle
Blitz Report™