USP_BANKACCOUNT_COMMONDATALIST

                CREATE procedure dbo.USP_BANKACCOUNT_COMMONDATALIST
                (
                    @BANKID uniqueidentifier = null,
                    @EXCLUDECLOSEDITEMS smallint = 0,
                    @CURRENTAPPUSERID uniqueidentifier = null
                )
                as
                    set nocount on;

                    exec dbo.USP_GET_KEY_ACCESS;

                    declare @ACCOUNTID uniqueidentifier, @BANKNAME nvarchar(128), @ACCOUNTNAME nvarchar(100), @ACCOUNTTYPE nvarchar(20), @STATUSCODE smallint, @ROUTINGNUMBER nvarchar(9), @ACCOUNTNUMBER nvarchar(19), @CURRENCY nvarchar(110), @CURRENTBALANCE money, @ENDINGBALANCE money, @NAME nvarchar(50), @TRANSACTIONCURRENCYID uniqueidentifier
                    declare @ResultSet TABLE(ACCOUNTID uniqueidentifier, BANKNAME nvarchar(128), ACCOUNTNAME nvarchar(100), ACCOUNTTYPE nvarchar(20), STATUSCODE smallint, ROUTINGNUMBER nvarchar(9), ACCOUNTNUMBER nvarchar(19), CURRENCY nvarchar(110), CURRENTBALANCE money, ENDINGBALANCE money, NAME nvarchar(50), TRANSACTIONCURRENCYID uniqueidentifier)
                    declare OrderedTrans cursor LOCAL FAST_FORWARD  FOR
                        select
                            BANKACCOUNT.ID,
                            (SELECT NAME FROM DBO.CONSTITUENT WHERE ID = BANKACCOUNT.BANKID) BANKNAME,
                            BANKACCOUNT.ACCOUNTNAME,
                            BANKACCOUNT.ACCOUNTTYPE,
                            BANKACCOUNT.STATUSCODE,
                            --BANKACCOUNT.ROUTINGNUMBER,

                            case
                                when dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 0 then BANKACCOUNT.ROUTINGNUMBER
                                else SORTCODE
                            end as ROUTINGNUMBER,
                            convert(nvarchar(19), DecryptByKey(BANKACCOUNT.ACCOUNTNUMBER)),
              dbo.UFN_CURRENCY_GETDESCRIPTION(BANKACCOUNT.TRANSACTIONCURRENCYID),
                            PDACCOUNTSYSTEM.NAME,
                            BANKACCOUNT.TRANSACTIONCURRENCYID

                        from
                            dbo.BANKACCOUNT
                            inner join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = BANKACCOUNT.PDACCOUNTSYSTEMID
                        where
                            (@BANKID is null or BANKACCOUNT.BANKID = @BANKID) and
                            BANKACCOUNT.STATUSCODE in (1, @EXCLUDECLOSEDITEMS) and
                            PDACCOUNTSYSTEM.ID in (select T1.ID from dbo.UFN_PDACCOUNTSYSTEM_GETSYSTEMIDSFORUSER(@CURRENTAPPUSERID) T1)
                        order by
                            BANKNAME, BANKACCOUNT.ACCOUNTNAME

                    OPEN OrderedTrans
                    FETCH NEXT FROM OrderedTrans INTO @ACCOUNTID, @BANKNAME, @ACCOUNTNAME, @ACCOUNTTYPE, @STATUSCODE, @ROUTINGNUMBER, @ACCOUNTNUMBER, @CURRENCY, @NAME, @TRANSACTIONCURRENCYID

                    WHILE @@FETCH_STATUS = 0
                    BEGIN
                        begin try
                            set @CURRENTBALANCE = dbo.UFN_BANKACCOUNT_GETCURRENTBALANCE(@ACCOUNTID)
                        end try
                        begin catch
                            set @CURRENTBALANCE = null
                        end catch

                        begin try
                            set @ENDINGBALANCE = dbo.UFN_BANKACCOUNT_GETENDINGBALANCE(@ACCOUNTID)
                        end try
                        begin catch
                            set @ENDINGBALANCE = null
                        end catch
                            INSERT INTO @ResultSet VALUES(@ACCOUNTID, @BANKNAME, @ACCOUNTNAME, @ACCOUNTTYPE, @STATUSCODE, @ROUTINGNUMBER, @ACCOUNTNUMBER, @CURRENCY, @CURRENTBALANCE, @ENDINGBALANCE, @NAME, @TRANSACTIONCURRENCYID );
                    FETCH NEXT FROM OrderedTrans INTO @ACCOUNTID, @BANKNAME, @ACCOUNTNAME, @ACCOUNTTYPE, @STATUSCODE, @ROUTINGNUMBER, @ACCOUNTNUMBER, @CURRENCY, @NAME, @TRANSACTIONCURRENCYID
                    END

                    exec USP_CLOSE_KEY_ACCESS;

                    CLOSE OrderedTrans
                    DEALLOCATE OrderedTrans

                    SELECT * FROM @ResultSet order by BANKNAME, ACCOUNTNAME