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 |
