USP_SEARCHLIST_BANKACCOUNT

This search list searches for a bank account.

Parameters

Parameter	Parameter Type	Mode	Description
@ACCOUNTNAME	nvarchar(100)	IN	Account name
@BANKID	uniqueidentifier	IN
@NAME	nvarchar(100)	IN	Bank
@ACCOUNTNUMBER	nvarchar(50)	IN	Account number
@STATUSCODE	tinyint	IN	Status
@ACCOUNTTYPECODE	tinyint	IN	Account type
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@PDACCOUNTSYSTEMID	uniqueidentifier	IN	Account system
@BANKINGSYSTEMID	uniqueidentifier	IN

Definition

Copy



                CREATE procedure dbo.USP_SEARCHLIST_BANKACCOUNT
                (
                    @ACCOUNTNAME nvarchar(100) = null,
                    @BANKID uniqueidentifier = null,
                    @NAME nvarchar(100) = null,
                    @ACCOUNTNUMBER nvarchar(50) = null,
                    @STATUSCODE tinyint = null,
                    @ACCOUNTTYPECODE tinyint = null,
                    @MAXROWS smallint = 500,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @PDACCOUNTSYSTEMID uniqueidentifier = null,
                    @BANKINGSYSTEMID uniqueidentifier = null

                )
                as
                    set nocount on;
                    if @ACCOUNTNAME is not null
                        set @ACCOUNTNAME = REPLACE(REPLACE(COALESCE(@ACCOUNTNAME,''), '*', '%'), '?', '_') + '%' ;
                    if @ACCOUNTNUMBER is not null
                        set @ACCOUNTNUMBER = REPLACE(REPLACE(COALESCE(@ACCOUNTNUMBER,''), '*', '%'), '?', '_') + '%' ;
                    --set @PDACCOUNTSYSTEMID = (select PDACCOUNTSYSTEMID from dbo.BANKACCOUNT where ID = @ID);                    


                    exec USP_GET_KEY_ACCESS;

                    select top(@MAXROWS)
                        BANKACCOUNT.ID [ID],
                        CONSTITUENT.NAME,
                        BANKACCOUNT.ACCOUNTNAME,
                        convert(nvarchar(50), DecryptByKey(BANKACCOUNT.ACCOUNTNUMBER)),
                        BANKACCOUNT.ACCOUNTTYPE,
                        BANKACCOUNT.STATUS,
                        PDACCOUNTSYSTEM.NAME SYSTEMNAME,
                        BANKINGSYSTEM.NAME BANKINGSYSTEM
                    from 
                        dbo.BANKACCOUNT
                        inner join dbo.CONSTITUENT on CONSTITUENT.ID = BANKACCOUNT.BANKID
                        inner join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = BANKACCOUNT.PDACCOUNTSYSTEMID
                        inner join dbo.UFN_PDACCOUNTSYSTEM_GETSYSTEMIDSFORUSER(@CURRENTAPPUSERID) ts on PDACCOUNTSYSTEM.ID = ts.ID
                        inner join dbo.BANKINGSYSTEM on BANKINGSYSTEM.ID = BANKACCOUNT.BANKINGSYSTEMID
                    where
                        (
                         (@BANKID is null or (BANKACCOUNT.BANKID = @BANKID)) and
                         (@ACCOUNTNAME is null or (BANKACCOUNT.ACCOUNTNAME like @ACCOUNTNAME + '%')) and
                         (@ACCOUNTNUMBER is null or (convert(nvarchar(50), DecryptByKey(BANKACCOUNT.ACCOUNTNUMBER)) like @ACCOUNTNUMBER + '%')) and
                         (@STATUSCODE is null or (BANKACCOUNT.STATUSCODE = @STATUSCODE)) and
                         (@ACCOUNTTYPECODE is null or (BANKACCOUNT.ACCOUNTTYPECODE = @ACCOUNTTYPECODE)) and
                         (@NAME is null or (CONSTITUENT.KEYNAME like @NAME + '%')) and
                         (@PDACCOUNTSYSTEMID is null or PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID)) and
                         (@BANKINGSYSTEMID is null or BANKINGSYSTEM.ID = @BANKINGSYSTEMID)

                    order by 
                        NAME, BANKACCOUNT.ACCOUNTNAME asc

                    exec USP_CLOSE_KEY_ACCESS;