XTR Term Money - Retail Term Money Overdues
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
Source: Term Money - Retail Term Money Overdues Report (XML)
Short Name: XTRTMAOD_XML
DB package: XTR_XTRTMAOD_XMLP_PKG
Run
XTR Term Money - Retail Term Money Overdues and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |