select a.company COMPANY,
a.cparty CPARTY,
a.amount_date VAL_DATE,
a.client CLIENT,
decode(a.contract_type,'ONC',to_char(a.deal_number)||'/'||to_char(a.transaction_number),to_char(a.deal_number)) DEAL_NO,
nvl(f.user_deal_type,a.contract_type)||' '||a.subtype
DEAL_TYPE,
a.type_of_amount AMT_TYPE,
a.portfolio PORT,
a.product PROD,
a.rate RATE,
a.currency CCY,
a.amount/:amt_unit2 AMOUNT,
(round(a.amount/nvl(b.HCE_RATE,1),nvl(b.ROUNDING_FACTOR,2)))/:amt_unit2 HCE_AMOUNT,
XTR_XTRMTCPY_XMLP_PKG.company_name1formula(a.company) COMPANY_NAME1,
XTR_XTRMTCPY_XMLP_PKG.COMPANY_NAME_p COMPANY_NAME,
XTR_XTRMTCPY_XMLP_PKG.cparty_name1formula(a.cparty) CPARTY_NAME1,
XTR_XTRMTCPY_XMLP_PKG.CPARTY_NAME_p CPARTY_NAME
from XTR_MATURITIES_V a,
XTR_DEAL_TYPES_V f,
XTR_MASTER_CURRENCIES_V b
where a.cparty like nvl(upper(:CPARTY_CODE2),a.cparty)
and a.company like nvl(upper(:COMPANY_CODE2),a.company)
and a.amount_date between NVL(:FROM_DATE2, a.amount_date)
and NVL(:TO_DATE2, a.amount_date)
and a.contract_type like nvl(upper(:DEAL_TYPE2),a.contract_type)
and a.subtype like nvl(upper(:DEAL_SUBTYPE2),a.subtype)
and a.portfolio like nvl(upper(:PORTFOLIO2), a.portfolio)
and a.product like nvl(upper(:PRODUCT_TYPE2),a.product)
and a.currency like nvl(upper(:CURRENCY2),a.currency)
and b.currency = a.currency
and ((a.contract_type = 'FXO' and a.date_type != 'VALUE') or
(a.contract_type != 'FXO'))
and ((nvl(a.client,'%') like nvl(:CLIENT_CODE,a.client)
and nvl(upper(:AS_PRINCIPAL2),'N') = 'N') or
(nvl(a.client,'NILL') = 'NILL' and nvl(upper(:AS_PRINCIPAL2),'N') = 'Y'))
and f.deal_type = a.contract_type
--order by 1,2,3,5,8
order by COMPANY_NAME1,CPARTY_NAME1,VAL_DATE,DEAL_NO,PORT |
