USP_SEARCHLIST_BANKACCOUNTDEPOSIT_BY_ACCOUNTSYSTEM

This searches for a deposit by account system.

Parameters

Parameter	Parameter Type	Mode	Description
@DEPOSITNUMBER	nvarchar(30)	IN	Deposit number
@DEPOSITDATE	datetime	IN	Deposit date
@DEPOSITAMOUNT	decimal(20, 4)	IN	Deposit amount
@STATUSCODE	tinyint	IN	Status
@REFERENCE	nvarchar(60)	IN	Reference
@BANKNAME	nvarchar(100)	IN	Bank
@ACCOUNTNAME	nvarchar(100)	IN	Account name
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.
@NUMBERACCOUNT	nvarchar(113)	IN	Deposit description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@PDACCOUNTSYSTEMID	uniqueidentifier	IN	Account system
@TRANSACTIONCURRENCYID	uniqueidentifier	IN	Payment currency

Definition

Copy



            CREATE procedure dbo.USP_SEARCHLIST_BANKACCOUNTDEPOSIT_BY_ACCOUNTSYSTEM
                (
                    @DEPOSITNUMBER nvarchar(30) = null,
                    @DEPOSITDATE datetime = null,
                    @DEPOSITAMOUNT decimal(20,4) = null,
                    @STATUSCODE tinyint = 1,
                    @REFERENCE nvarchar(60) = null,
                    @BANKNAME nvarchar(100) = null,
                    @ACCOUNTNAME nvarchar(100) = null,
                    @MAXROWS smallint = 500,
                    @NUMBERACCOUNT nvarchar(113) = null,
                    @CURRENTAPPUSERID uniqueidentifier,
                    @PDACCOUNTSYSTEMID uniqueidentifier,
          @TRANSACTIONCURRENCYID uniqueidentifier = null
                )
                as
                    set @ACCOUNTNAME = REPLACE(REPLACE(COALESCE(@ACCOUNTNAME,''), '*', '%'), '?', '_') + '%'
                    set @REFERENCE = REPLACE(REPLACE(COALESCE(@REFERENCE,''), '*', '%'), '?', '_') + '%'
                    set @BANKNAME = REPLACE(REPLACE(COALESCE(@BANKNAME,''), '*', '%'), '?', '_') + '%'

                    select top(@MAXROWS)
                        BANKACCOUNTTRANSACTION.ID [ID],
                        BANKACCOUNTTRANSACTION.TRANSACTIONDATE,
                        CONSTITUENT.NAME,
                        BANKACCOUNT.ACCOUNTNAME,
                        'AMOUNT' = case  when BANKACCOUNTTRANSACTION.AMOUNT >0 then BANKACCOUNTTRANSACTION.AMOUNT else '$0.00' end ,
                        BANKACCOUNTTRANSACTION.TRANSACTIONNUMBER,
                        BANKACCOUNTTRANSACTION.REFERENCE,
                        BANKACCOUNTDEPOSIT.STATUS,
                        PDACCOUNTSYSTEM.NAME SYSTEMNAME,
            BANKACCOUNTDEPOSIT.TRANSACTIONCURRENCYID
                    from 
                        dbo.BANKACCOUNTDEPOSIT
                        inner join BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION.ID = BANKACCOUNTDEPOSIT.ID
                        inner join BANKACCOUNT on BANKACCOUNT.ID = BANKACCOUNTTRANSACTION.BANKACCOUNTID
                        inner join CONSTITUENT on CONSTITUENT.ID = BANKACCOUNT.BANKID
                        inner join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = BANKACCOUNT.PDACCOUNTSYSTEMID
                    where
                    (
                         (@DEPOSITNUMBER is null or (BANKACCOUNTTRANSACTION.TRANSACTIONNUMBER = @DEPOSITNUMBER)) and
                         (@DEPOSITDATE is null or (convert(nvarchar,BANKACCOUNTTRANSACTION.TRANSACTIONDATE,101) = convert(nvarchar,@DEPOSITDATE,101))) and
                         (@DEPOSITAMOUNT is null or (BANKACCOUNTTRANSACTION.AMOUNT = @DEPOSITAMOUNT)) and
                         (@STATUSCODE is null or (BANKACCOUNTDEPOSIT.STATUSCODE = @STATUSCODE)) and
                         (@REFERENCE is null or (BANKACCOUNTTRANSACTION.REFERENCE like @REFERENCE + '%')) and
                         (@BANKNAME is null or (CONSTITUENT.KEYNAME like @BANKNAME + '%')) and
                         (@ACCOUNTNAME is null or (BANKACCOUNT.ACCOUNTNAME like @ACCOUNTNAME + '%')) and
                         (@PDACCOUNTSYSTEMID is null or (PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID))and
             (@TRANSACTIONCURRENCYID is null or (BANKACCOUNTDEPOSIT.TRANSACTIONCURRENCYID like @TRANSACTIONCURRENCYID)) 
                    ) and
                            PDACCOUNTSYSTEM.ID in (select T1.ID from dbo.UFN_PDACCOUNTSYSTEM_GETSYSTEMIDSFORUSER(@CURRENTAPPUSERID) T1)
                    order by 
                        BANKACCOUNTTRANSACTION.TRANSACTIONDATE asc, CONSTITUENT.NAME asc