select a.amount_date VAL_DATE,
a.company COMPANY,
a.cparty CPARTY,
decode(a.contract_type,'ONC',a.transaction_number,a.deal_number)
DEAL_NO,
nvl(b.user_deal_type,a.user_deal_type)||' '||a.user_deal_subtype
DEAL_TYPE,
a.user_amount_type AMT_TYPE,
a.product PROD,
a.rate RATE,
a.currency CCY,
a.portfolio PORT,
a.amount/:amt_unit2 AMOUNT,
a.hce_amount/:amt_unit2 HCE_AMOUNT,
a.user_date_type date_type
from XTR_MATURITIES_V a,
XTR_DEAL_TYPES_V b
where
&p_period_where_clause
and a.amount > 0
and a.company like nvl(upper(:P_COMPANY),a.company)
and a.contract_type like nvl(upper(:P_DEAL_TYPE),a.contract_type)
and a.contract_type != 'DN'
and a.subtype like nvl(upper(:P_DEAL_SUBTYPE),a.subtype)
and a.product like nvl(upper(:P_PRODUCT_TYPE),a.product)
and a.currency like nvl(upper(:P_CURRENCY),a.currency)
and a.cparty like nvl(upper(:P_CPARTY),a.cparty)
and a.portfolio like nvl(upper(:P_PORTFOLIO),a.portfolio)
and ((nvl(a.client,'%') like nvl(:P_CLIENT,a.client) and nvl(upper(:P_AS_PRINCIPAL),'N') = 'N') or
(nvl(a.client,'NILL') = 'NILL' and nvl(upper(:P_AS_PRINCIPAL),'N') = 'Y'))
and b.deal_type = a.contract_type and a.contract_status ='CURRENT' and NOT EXISTS ( select 'x'
from xtr_maturities_v c
where c.company = a.company
and c.user_deal_type = a.user_deal_type
and c.user_date_type = a.user_date_type
and c.user_amount_type = a.user_amount_type
and c.type_of_amount = a.type_of_amount
and c.date_type = a.date_type
and c.type_of_amount = 'FACEVAL'
and c.contract_type = 'TMM'
and c.date_type = 'RATESET'
and c.contract_type = a.contract_type
and c.product = a.product
and c.currency = a.currency
and c.cparty = a.cparty
and c.portfolio = a.portfolio
and c.amount_date = a.amount_date
)
order by 1,2,5,4,6 |
