XTR Positions - Discounted Securities Register

select a.deal_no       D_NO,
       a.company_code  COMPANY,
       a.currency      CCY,
       a.start_date    S_DATE,
       a.maturity_date M_DATE,
       a.product_type  PROD,
       a.deal_subtype  D_SUB,
       a.cparty_code   C_PARTY,
       a.portfolio_code    PORT,
       p.name          PORT_NAME,
       a.acceptor_code ACCEPTOR,
       a.interest_rate RATE,
       a.status_code   STATUS,
       b.available_for_resale AVAILABLE,
       b.face_value_amount/:amt_unit2
                       FV_AMT,
       b.parcel_remaining/:amt_unit2
                       PARCEL_REMAIN,
       b.reserve_parcel/:amt_unit2
                       RESERVE_PARCEL,
       c.short_name    COMPANY_NAME
from XTR_DEALS_V a,
     XTR_PARCEL_SPLITS_V b,
     XTR_PARTIES_V c,
     XTR_PORTFOLIOS_V p
   where a.DEAL_TYPE = 'NI'
and
(
       (a.DEAL_SUBTYPE = 'ISSUE' and nvl(upper(:ISS_PURCH2),'ALL') = 'ISSUED')
   or (a.DEAL_SUBTYPE = 'BUY' and nvl(upper(:ISS_PURCH2),'ALL') = 'PURCHASED')
   or (nvl(upper(:ISS_PURCH2),'ALL') = 'ALL')
)
and a.STATUS_CODE NOT IN('CLOSED','CANCELLED')
and nvl(a.COMPANY_CODE,'%') like nvl(upper(:COMPANY_CODE2),'%')
and nvl(a.CPARTY_CODE,'%') like nvl(upper(:CPARTY_CODE2),'%')
and (((:FROM_DATE2 is null) and (:TO_DATE2 is null)) or ((:FROM_DATE2 is null) and (a.MATURITY_DATE <= :TO_DATE2)) or ((:TO_DATE2 is null) and (a.MATURITY_DATE >= :FROM_DATE2)) or (a.MATURITY_DATE between :FROM_DATE2 and :TO_DATE2))
and nvl(a.PORTFOLIO_CODE,'%') like  nvl(upper(:PORTFOLIO2),'%')
and nvl(a.PRODUCT_TYPE,'%') like  nvl(upper(:PRODUCT_TYPE2),'%')
and nvl(a.ACCEPTOR_CODE,'%') like nvl(upper(:ACCEPTOR2),'%')
and b.DEAL_NO = a.DEAL_NO
and c.PARTY_CODE = a.COMPANY_CODE
and p.PORTFOLIO (+) = a.PORTFOLIO_CODE
and p.COMPANY_CODE (+) = a.COMPANY_CODE
order by a.COMPANY_CODE,a.DEAL_SUBTYPE,a.PORTFOLIO_CODE,a.CURRENCY