XTR Admin - Cancelled Transactions Summary

select a.COMPANY_CODE            company,
       p.SHORT_NAME              company_name,
       a.DEAL_NO                 deal_no,
       a.TRANSACTION_NO          trans_no,
       a.CURRENCY                ccy,
       a.CPARTY_CODE             cparty,
       t.USER_DEAL_TYPE             type,
       s.USER_DEAL_SUBTYPE            subtype,
       a.PRODUCT_TYPE            product,
       a.CREATED_ON              created,
       a.UPDATED_ON              updated,
       substr(a.CREATED_BY,1,18) created_by,
       substr(a.UPDATED_BY,1,18) updated_by
from XTR_DEALS_V a,
     XTR_PARTIES_V p,
     XTR_DEAL_TYPES_V t,
    XTR_DEAL_SUBTYPES_V s
where a.STATUS_CODE = 'CANCELLED'
and a.DEAL_TYPE != 'ONC'
and a.COMPANY_CODE like nvl(upper(:COMP_CODE2),a.COMPANY_CODE)
and (a.UPDATED_ON between nvl(:CANCEL_FROM2,a.UPDATED_ON) and nvl(:CANCEL_TO2,a.UPDATED_ON) or a.UPDATED_ON is null)
and nvl(a.UPDATED_BY,'%') like nvl(upper(:CANCELLED_BY2),'%')
and a.DEAL_TYPE like nvl(upper(:DEAL_TYPE2),a.DEAL_TYPE)
and p.PARTY_CODE = a.COMPANY_CODE
and a.DEAL_TYPE = t.DEAL_TYPE
and a.DEAL_SUBTYPE = s.DEAL_SUBTYPE
UNION
select a.COMPANY_CODE            company,
       p.SHORT_NAME              company_name,
       a.DEAL_NUMBER             deal_no,
       a.TRANSACTION_NUMBER      trans_no,
       a.CURRENCY                ccy,
       a.CPARTY_CODE             cparty,
       t.USER_DEAL_TYPE          type,
       s.USER_DEAL_SUBTYPE       subtype,
       a.PRODUCT_TYPE            product,
       a.CREATED_ON              created,
       a.UPDATED_ON              updated,
       substr(a.CREATED_BY,1,18) created_by,
       substr(a.UPDATED_BY,1,18) updated_by
from XTR_ROLLOVER_TRANSACTIONS_V a,
     XTR_PARTIES_V p,
   XTR_DEAL_TYPES_V t,
     XTR_DEAL_SUBTYPES_V s
where a.STATUS_CODE = 'CANCELLED'
and a.DEAL_TYPE = 'ONC'
and a.COMPANY_CODE like nvl(upper(:COMP_CODE2),a.COMPANY_CODE)
and (a.UPDATED_ON between nvl(:CANCEL_FROM2,a.UPDATED_ON) and nvl(:CANCEL_TO2,a.UPDATED_ON) or a.UPDATED_ON is null)
and nvl(a.UPDATED_BY,'%') like nvl(upper(:CANCELLED_BY2),'%')
and a.DEAL_TYPE like nvl(upper(:DEAL_TYPE2),a.DEAL_TYPE)
and p.PARTY_CODE = a.COMPANY_CODE
and a.DEAL_TYPE = t.DEAL_TYPE
and a.DEAL_SUBTYPE = s.DEAL_SUBTYPE
order by 1,11