XTR Positions - Maturities by Counterparty

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