XTR Term Money - Retail Term Money Overdues

➜ Back to Library
Description
Categories: BI Publisher
Columns: Deal Num2, Nill Field, Int Overdue2, Bal Overdue2, Start Date, Mat Date, Pi Amount Due, Tot Overdue1, Tot Overdue ...
Application: Treasury
Source: Term Money - Retail Term Money Overdues Report (XML)
Short Name: XTRTMAOD_XML
DB package: XTR_XTRTMAOD_XMLP_PKG 
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
Parameter Name SQL text Validation
Product Type 
 
 LOV Oracle
Counterparty 
 
 LOV Oracle
Deal Number 
 
 LOV Oracle
Include Previously Actioned 
 
 LOV Oracle
As of Date 
 
 Date
Sort By 
 
 LOV Oracle
Download
Related Blitz Reports
XTR Term Money - Retail Term Money Aging XTR Term Money - By Type and Product XTR Admin - Cancelled Transactions Summary XTR Positions - Interest Rate Hedges XTR Positions - Treasury Cashflows by Account XTR Positions - Treasury Consolidated Cashflows XTR Term Money - Retail Term Money Statements XTR Positions - Outstanding Hedge Instruments XTR Positions - Discounted Securities Register
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: