XTR Settlements - Status

select a.company_code       COMPANY,
       a.deal_subtype       D_SUB,
       a.product_type       PROD,
       a.amount_date        AMT_DATE,
       a.currency           CCY,
       a.account_no         ACCT,
       a.cparty_code        CPARTY,
       nvl(a.settlement_party,
       decode(sign(a.cashflow_amount)
         ,-1,a.cparty_code
         ,a.company_code))  BENEFICIARY,
       a.deal_type          D_TYPE,
       a.deal_number DEAL_NO,
       a.transaction_number TRANS_NO,
       a.amount_type        AMT_TYPE,
       a.cashflow_amount    CFLOW,
       a.settlement_authorised_by
                          AUTH_BY,
       decode(nvl(a.chq_reqd,'N')
         ,'Y','CQ'
         ,decode(nvl(a.direct_debit,'N')
            ,'Y','DD'
            ,decode(nvl(a.trans_mts,'N')
               ,'Y','FT'))) 
                          METHOD, 
	XTR_XTRSESTA_XMLP_PKG.cflow1formula(a.cashflow_amount, a.currency) CFLOW1
from XTR_DEAL_DATE_AMOUNTS_V a
where a.COMPANY_CODE like nvl(upper(:P_COMPANY),a.COMPANY_CODE)
and a.CASHFLOW_AMOUNT != 0
and a.STATUS_CODE != 'CANCELLED'
and a.CURRENCY like nvl(upper(:P_CURRENCY),a.CURRENCY)
and nvl(a.MULTIPLE_SETTLEMENTS,'N') != 'Y'
and a.ACCOUNT_NO like nvl(upper(:P_BANK_ACCOUNT_NUMBER),a.ACCOUNT_NO)
and a.AMOUNT_DATE >= nvl(:FROM_DATE2,a.AMOUNT_DATE)
and a.AMOUNT_DATE <= nvl(:TO_DATE2,a.AMOUNT_DATE)
and a.SETTLE = decode(:P_AUTH_UNAUTH,'AUTHORIZED','Y','UNAUTHORIZED','N',a.SETTLE) 
and ((nvl(a.SETTLEMENT_PARTY,a.COMPANY_CODE) like nvl(upper(:P_BENEFICIARY),'%')
         and a.CASHFLOW_AMOUNT > 0) 
      or (nvl(a.SETTLEMENT_PARTY,CPARTY_CODE) like nvl(upper(:P_BENEFICIARY),'%')
         and a.CASHFLOW_AMOUNT < 0))
order by COMPANY_CODE,AMOUNT_DATE,CURRENCY