XTR Term Money - Retail Term Money Overdues

➜ Back to Library
Description
Categories: BI Publisher
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 NameSQL textValidation
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 - By Type and Product XTR Positions - Interest Rate Hedges XTR Admin - Cancelled Transactions Summary XTR Term Money - Retail Term Money Aging XTR Positions - Treasury Cashflows by Account XTR Positions - Treasury Consolidated Cashflows XTR Term Money - Term Average Rates XTR Term Money - Retail Term Money Statements XTR Term Money - New/Settled Retail Term Money
Blitz Report™