USP_PAYABLESCHECK_SINGLESTUB

/*
  User options allow the user to resort the transaction on the stub by 
  (Transaction Number OR Transaction Date) AND (ASC OR DESC).  Instead of having
  two or four SQL statements that are the same except for the order by clause
  this SQL statement will combine all of the SQLs into one statement combining
  the ordering options.

  DF.SORTTRANSACTIONSCODE
    0 = Transaction Number
    1 = Transaction Date
  DF.SOFTORDERCODE
    0 = ASC
    1 = DESC
*/

CREATE procedure dbo.USP_PAYABLESCHECK_SINGLESTUB
(
    @DISBURSEMENTID uniqueidentifier
)
with execute as owner
as
set nocount on;

select 
    top 10
  VD.TRANSACTIONID
  ,VD.TRANSACTIONNUMBER
  ,VD.TRANSACTIONDATE
  ,VD.TRANSACTIONDESCRIPTION
  ,VD.TRANSACTIONAMOUNT
  ,VD.TRANSACTIONDISCOUNTAMOUNT
  ,VD.TRANSACTIONNETAMOUNT
  ,VD.DISBURSEMENTID
  ,case DF.SORTTRANSACTIONSCODE 
    when 0 then case DF.SORTORDERCODE when 0 then row_number() over (order by VD.TRANSACTIONNUMBER ASC) else row_number() over (order by VD.TRANSACTIONNUMBER DESC) end 
    when 1 then case DF.SORTORDERCODE when 0 then row_number() over (order by VD.TRANSACTIONDATE ASC) else row_number() over (order by VD.TRANSACTIONDATE DESC) end 
  end as SORTCOLUMN
from 
  dbo.V_DISBURSEMENT_SHOWTRANSACTIONS as VD
  inner join BANKACCOUNTTRANSACTION_EXT as BATE on VD.DISBURSEMENTID = BATE.ID
  inner join DISBURSEMENTPROCESS as DP on BATE.DISBURSEMENTPROCESSID= DP.ID
  inner join DISBURSEMENTPROCESSFORMAT as DPF on DP.ID = DPF.DISBURSEMENTPROCESSID
  inner join DISBURSEMENTFORMAT as DF on DPF.DISBURSEMENTFORMATID = DF.ID
where
  VD.DISBURSEMENTID = @DISBURSEMENTID
order by
  SORTCOLUMN