USP_SEARCHLIST_BANKACCOUNTDEPOSIT

This searches for a deposit.

Parameters

Parameter	Parameter Type	Mode	Description
@DEPOSITNUMBER	nvarchar(30)	IN	Deposit number
@DEPOSITDATE	datetime	IN	Deposit date
@DEPOSITAMOUNT	money	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
@NUMSYSTEMS	tinyint	IN	Number of account systems
@TRANSACTIONCURRENCYID	uniqueidentifier	IN	Payment currency
@POSTSTATUSCODE	tinyint	IN	Post status
@POSTDATE	datetime	IN	Post date
@AMOUNTISZERO	bit	IN

Definition

Copy



                CREATE procedure dbo.USP_SEARCHLIST_BANKACCOUNTDEPOSIT
                (
                    @DEPOSITNUMBER nvarchar(30) = null,
                    @DEPOSITDATE datetime = null,
                    @DEPOSITAMOUNT money = null,
                    @STATUSCODE tinyint = null,
                    @REFERENCE nvarchar(60) = null,
                    @BANKNAME nvarchar(100) = null,
                    @ACCOUNTNAME nvarchar(100) = null,
                    @MAXROWS smallint = 500,
                    @NUMBERACCOUNT nvarchar(113) = null,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @PDACCOUNTSYSTEMID uniqueidentifier = null,
                    @NUMSYSTEMS tinyint = null,
                    @TRANSACTIONCURRENCYID uniqueidentifier = null
                    ,@POSTSTATUSCODE tinyint = null
                    ,@POSTDATE datetime = null
                    ,@AMOUNTISZERO bit = null
                )
                as
                    set @ACCOUNTNAME = REPLACE(REPLACE(COALESCE(@ACCOUNTNAME,''), '*', '%'), '?', '_') + '%'
                    set @REFERENCE = REPLACE(REPLACE(COALESCE(@REFERENCE,''), '*', '%'), '?', '_') + '%'
                    set @BANKNAME = REPLACE(REPLACE(COALESCE(@BANKNAME,''), '*', '%'), '?', '_') + '%'
                    set @NUMSYSTEMS = dbo.UFN_PDACCOUNTSYSTEM_GETNUMBEROFSYSTEMSFORUSER(@CURRENTAPPUSERID);
                    if isnull(@AMOUNTISZERO, 0) = 1 and @DEPOSITAMOUNT is null
                        set @DEPOSITAMOUNT = 0


                    declare @NUMBER integer
                    set @NUMBER = CAST(@DEPOSITNUMBER AS integer);

                    select top(@MAXROWS)
                        BANKACCOUNTTRANSACTION.ID [ID],
                        BANKACCOUNTTRANSACTION.TRANSACTIONDATE,
                        CONSTITUENT.NAME,
                        BANKACCOUNT.ACCOUNTNAME,
                        BANKACCOUNTTRANSACTION.TRANSACTIONAMOUNT [TRANSACTIONAMOUNT],
                        BANKACCOUNTDEPOSIT.TOTALPAYMENTAMOUNT [AMOUNT],
                        BANKACCOUNTTRANSACTION.TRANSACTIONNUMBER,
                        BANKACCOUNTTRANSACTION.REFERENCE,
                        BANKACCOUNTDEPOSIT.STATUS,
                        PDACCOUNTSYSTEM.NAME SYSTEMNAME,
                        PDACCOUNTSYSTEM.ID [PDACCOUNTSYSTEMID],
                        BANKACCOUNTDEPOSIT.TRANSACTIONCURRENCYID,
                        BANKACCOUNT.TRANSACTIONCURRENCYID [BANKCURRENCYID]
                    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
                    (
                         (@NUMBER is null or (BANKACCOUNTTRANSACTION.TRANSACTIONNUMBER = @NUMBER)) and
                         (@DEPOSITDATE is null or (convert(nvarchar,BANKACCOUNTTRANSACTION.TRANSACTIONDATE,101) = convert(nvarchar,@DEPOSITDATE,101))) and
                         (@POSTDATE is null or (convert(nvarchar,BANKACCOUNTTRANSACTION.POSTDATE,101) = convert(nvarchar,@POSTDATE,101))) and
                         (@DEPOSITAMOUNT is null or (BANKACCOUNTTRANSACTION.TRANSACTIONAMOUNT = @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 like @PDACCOUNTSYSTEMID)) and
                         (@TRANSACTIONCURRENCYID is null or (BANKACCOUNTDEPOSIT.TRANSACTIONCURRENCYID like @TRANSACTIONCURRENCYID)) and
                         (@POSTSTATUSCODE is null or (BANKACCOUNTTRANSACTION.POSTSTATUSCODE = @POSTSTATUSCODE))
                    ) and
                            PDACCOUNTSYSTEM.ID in (select T1.ID from dbo.UFN_PDACCOUNTSYSTEM_GETSYSTEMIDSFORUSER(@CURRENTAPPUSERID) T1)
                    order by 
                        BANKACCOUNTTRANSACTION.TRANSACTIONDATE desc, CONSTITUENT.NAME asc