USP_SEARCHLIST_BANKACCOUNTADJUSTMENTCATEGORYSEARCH

Search for a bank account adjustment category.
Parameters

Parameter	Parameter Type	Mode	Description
@ACCOUNTNAME	nvarchar(100)	IN	Bank account
@CATEGORY	nvarchar(60)	IN	Category
@ADJUSTMENTTYPECODE	smallint	IN	Type
@AMOUNT	money	IN	Amount
@DEFAULTREFERENCE	nvarchar(100)	IN	Reference
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.
@AMOUNTMULTICURRENCY	money	IN	Amount
@PDACCOUNTSYSTEMID	uniqueidentifier	IN	Account System
Definition

Copy


                CREATE procedure dbo.USP_SEARCHLIST_BANKACCOUNTADJUSTMENTCATEGORYSEARCH
                (
                    @ACCOUNTNAME nvarchar(100) = null,
                    @CATEGORY nvarchar(60) = null,
                    @ADJUSTMENTTYPECODE smallint = null,
                    @AMOUNT money = null,
                    @DEFAULTREFERENCE nvarchar(100) = null,
                    @MAXROWS smallint = 500,
          @AMOUNTMULTICURRENCY money = null
                    ,@PDACCOUNTSYSTEMID uniqueidentifier = null
                )
                as
                    set nocount on;

          --You can not do a 'transfer in' if multicurrency is enabled since exchange rates

          --are not bi-directional.  You start with the transaction you are on and exchange from it.

          --A transfer in would start with another transaction and exchange to the current transaction

          --which is not allowed.  Due to this we are no longer allowing you to pick a category of 'transfer in'.

          if dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency')=1          
            begin
                        select top(@MAXROWS)
                            BANKACCOUNTADJUSTMENTCATEGORY.ID [ID],
                            ACCOUNTNAME,
                            CATEGORY,
                            ADJUSTMENTTYPE,
                            AMOUNT,
                            DEFAULTREFERENCE,
                            TRANSACTIONCURRENCYID
                        from 
                            dbo.BANKACCOUNTADJUSTMENTCATEGORY
                        inner join 
                            dbo.BANKACCOUNT on BANKACCOUNT.ID = BANKACCOUNTADJUSTMENTCATEGORY.BANKACCOUNTID
                        where
                             (@ACCOUNTNAME is null or (BANKACCOUNT.ACCOUNTNAME like REPLACE(REPLACE(@ACCOUNTNAME, '*', '%'), '?', '_') + '%')) and
                             (@CATEGORY is null or (BANKACCOUNTADJUSTMENTCATEGORY.CATEGORY like REPLACE(REPLACE(@CATEGORY, '*', '%'), '?', '_') + '%')) and
                             (@ADJUSTMENTTYPECODE is null or (BANKACCOUNTADJUSTMENTCATEGORY.ADJUSTMENTTYPECODE = @ADJUSTMENTTYPECODE)) and
                             (@AMOUNT is null or (BANKACCOUNTADJUSTMENTCATEGORY.AMOUNT = @AMOUNT)) and
                             (@DEFAULTREFERENCE is null or (BANKACCOUNTADJUSTMENTCATEGORY.DEFAULTREFERENCE like REPLACE(REPLACE(@DEFAULTREFERENCE, '*', '%'), '?', '_') + '%')) and
                 (@AMOUNTMULTICURRENCY is null or (BANKACCOUNTADJUSTMENTCATEGORY.AMOUNT = @AMOUNTMULTICURRENCY)) and
                                 (@PDACCOUNTSYSTEMID is null or (BANKACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)) and
                 ADJUSTMENTTYPECODE <> 33
                        order by ACCOUNTNAME asc
            end
          else
            begin
                        select top(@MAXROWS)
                            BANKACCOUNTADJUSTMENTCATEGORY.ID [ID],
                            ACCOUNTNAME,
                            CATEGORY,
                            ADJUSTMENTTYPE,
                            AMOUNT,
                            DEFAULTREFERENCE,
                            TRANSACTIONCURRENCYID
                        from 
                            dbo.BANKACCOUNTADJUSTMENTCATEGORY
                        inner join 
                            dbo.BANKACCOUNT on BANKACCOUNT.ID = BANKACCOUNTADJUSTMENTCATEGORY.BANKACCOUNTID
                        where
                             (@ACCOUNTNAME is null or (BANKACCOUNT.ACCOUNTNAME like REPLACE(REPLACE(@ACCOUNTNAME, '*', '%'), '?', '_') + '%')) and
                             (@CATEGORY is null or (BANKACCOUNTADJUSTMENTCATEGORY.CATEGORY like REPLACE(REPLACE(@CATEGORY, '*', '%'), '?', '_') + '%')) and
                             (@ADJUSTMENTTYPECODE is null or (BANKACCOUNTADJUSTMENTCATEGORY.ADJUSTMENTTYPECODE = @ADJUSTMENTTYPECODE)) and
                             (@AMOUNT is null or (BANKACCOUNTADJUSTMENTCATEGORY.AMOUNT = @AMOUNT)) and
                             (@DEFAULTREFERENCE is null or (BANKACCOUNTADJUSTMENTCATEGORY.DEFAULTREFERENCE like REPLACE(REPLACE(@DEFAULTREFERENCE, '*', '%'), '?', '_') + '%')) and
                 (@AMOUNTMULTICURRENCY is null or (BANKACCOUNTADJUSTMENTCATEGORY.AMOUNT = @AMOUNTMULTICURRENCY)) and
                                 (@PDACCOUNTSYSTEMID is null or (BANKACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID))                     
                        order by ACCOUNTNAME asc
            end