XTR Term Money - Retail Term Money Overdues

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