USP_SEARCHLIST_DISBURSEMENT

Search for a disbursement financial transaction.

Parameters

Parameter	Parameter Type	Mode	Description
@PAYEEID	uniqueidentifier	IN	Payee
@PAYMENTMETHODTYPECODE	tinyint	IN	Disbursement type
@BANKACCOUNTID	uniqueidentifier	IN	Bank account
@NUMBER	int	IN	Disbursement number
@DATE	date	IN	Disbursement date
@POSTDATE	date	IN	Post date
@AMOUNT	money	IN	Amount
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.

Definition

Copy



CREATE procedure dbo.USP_SEARCHLIST_DISBURSEMENT
(
    @PAYEEID uniqueidentifier = null,
    @PAYMENTMETHODTYPECODE tinyint = null,
    @BANKACCOUNTID uniqueidentifier = null,
    @NUMBER int = null,
    @DATE date = null,
    @POSTDATE date = null,
    @AMOUNT money = null,
    @MAXROWS smallint = 500
)
as
    select top(@MAXROWS)
        FT.ID
        ,C.NAME
        ,'Check' PAYMENTMETHOD
        ,BA.ACCOUNTNAME
        ,CAST(BAT.TRANSACTIONNUMBER as nvarchar) NUMBER
        ,BAT.TRANSACTIONDATE
        ,BAT.POSTDATE
        ,BAT.AMOUNT
    from dbo.BANKACCOUNTTRANSACTION BAT
    inner join dbo.FINANCIALTRANSACTION FT on FT.ID = BAT.ID
    inner join dbo.CONSTITUENT C on C.ID = FT.CONSTITUENTID
    inner join dbo.BANKACCOUNT BA on BA.ID = BAT.BANKACCOUNTID
    where (C.ID = @PAYEEID or @PAYEEID is null)
        and (@PAYMENTMETHODTYPECODE is null or @PAYMENTMETHODTYPECODE = BAT.TRANSACTIONTYPECODE)
        and (@NUMBER is null or BAT.TRANSACTIONNUMBER = @NUMBER or @NUMBER = 0)
        and (@DATE is null or BAT.TRANSACTIONDATE = @DATE)
        and (@POSTDATE is null or BAT.POSTDATE = @POSTDATE)
        and (@AMOUNT is null or BAT.AMOUNT = @AMOUNT or @AMOUNT = 0)
    order by BAT.TRANSACTIONDATE asc