XTR Term Money - By Type and Product

Description
Categories: BI Publisher
Application: Treasury
Source: Term Money - By Type and Product Report (XML)
Short Name: XTRTMBLT_XML
DB package: XTR_XTRTMBLT_XMLP_PKG
Run XTR Term Money - By Type and Product and other Oracle EBS reports with Blitz Report™ on our demo environment
select
 d.deal_no           DEAL_NO,
 decode(ds.user_deal_subtype
   ,'FUND','Deposit'
   ,'Advance')       DEAL_TYPE,
 d.product_type      PRODUCT,
 c.product_desc      PRODUCT_NAME,
 d.currency          CCY,
 d.cparty_code       CP_CODE,
 p.short_name        CP_NAME,
 r.start_date        START_DATE,
 r.accum_interest - r.interest + (r.interest_rate / 100 * r.balance_out / m.year_basis * (:AS_AT_DATE2 - TRUNC(r.start_date))) ACCUM_INTEREST,
 r.balance_out       BALANCE_OUT,
 r.maturity_date     MATURITY_DATE,
 r.interest_rate     CURR_RATE
from XTR_DEALS_V d,
     XTR_PARTIES_V p,
     XTR_ROLLOVER_TRANSACTIONS_V r,
     XTR_MASTER_CURRENCIES_V m,
     XTR_PRODUCT_TYPES_V c,
    XTR_DEAL_SUBTYPES ds
where d.status_code='CURRENT'
and d.cparty_code like nvl(upper(:CPARTY_CODE2),d.cparty_code)
and d.company_code like nvl(upper(:COMPANY2),d.company_code)
and d.product_type like nvl(upper(:PRODUCT_TYPE2),d.product_type)
and ((d.deal_subtype = 'FUND' and upper(:DEAL_SUBTYPE2) = 'DEPOSIT') or
     (d.deal_subtype = 'INVEST' and upper(:DEAL_SUBTYPE2) = 'ADVANCE') or
     (d.deal_subtype IN('INVEST','FUND') and nvl(:DEAL_SUBTYPE2,'%') = '%'))
and d.cparty_code  = p.party_code
and c.deal_type = 'RTMM'
and d.product_type = c.product_type
and r.currency = m.currency
and p.short_name like nvl(upper(:CPARTY_NAME2),p.short_name)
and nvl(p.account_manager,'%') like nvl(upper(:ACCOUNT_MANAGER2),'%')
and d.deal_type = 'RTMM'
and d.deal_no = r.deal_number
and r.balance_out >= nvl(:BALANCES_FROM2,0)
and r.maturity_date = (select max(t.maturity_date)
                     from XTR_ROLLOVER_TRANSACTIONS_V t
                     where t.deal_number = r.deal_number
                     and t.start_date <= :AS_AT_DATE2
                     and t.settle_date is NULL)
and r.start_date !=r.maturity_date
and (((nvl(r.accum_interest_bf,0) > 0 or r.expected_balance_out <
       r.balance_out) and nvl(upper(:OVERDUE_ONLY2),'N') = 'Y') or
     (nvl(upper(:OVERDUE_ONLY2),'N') = 'N'))
and d.deal_type = ds.deal_type
and d.deal_subtype = ds.deal_subtype
order by 2,3,5
Parameter Name SQL text Validation
Company
 
LOV Oracle
Product Type
 
LOV Oracle
Counterparty
 
LOV Oracle
As of Date
 
Date
Deposit/Advance
 
LOV Oracle
Balances From
 
Number
Account Manager
 
LOV Oracle
Overdue Amounts Only
 
LOV Oracle