USP_REPORT_DEPOSITREPORT_DEPOSIT

            CREATE procedure dbo.USP_REPORT_DEPOSITREPORT_DEPOSIT
            (
                @DATETOUSE int = null,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @BANKACCOUNTDEPOSITQUERY uniqueidentifier = null,
                @DEPOSITID uniqueidentifier = null,
                @REPORTUSERID nvarchar(128) = null,
                @DEPOSITCOLLECTION xml = null,
                @DEPOSITLIST nvarchar(max) = null,
                @ALTREPORTUSERID nvarchar(128) = null
            )
            with execute as owner
            as
            set nocount on;

                declare @CURRENTAPPUSERID uniqueidentifier;
                declare @ISADMIN bit;
                declare @APPUSER_IN_NONRACROLE bit;
                declare @APPUSER_IN_NOSECGROUPROLE bit;

                set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@ALTREPORTUSERID, @REPORTUSERID);
                set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
                set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
                set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);

                declare @SQLTOEXEC nvarchar(max);

                declare @DBOBJECTNAME nvarchar(128);
                declare @DBOBJECTTYPE smallint;

                if @BANKACCOUNTDEPOSITQUERY is not null begin
                    if not exists(select ID from dbo.IDSETREGISTER where ID = @BANKACCOUNTDEPOSITQUERY) raiserror('ID set does not exist in the database.', 15, 1);
                    select @DBOBJECTNAME = DBOBJECTNAME, @DBOBJECTTYPE = OBJECTTYPE from dbo.IDSETREGISTER where ID = @BANKACCOUNTDEPOSITQUERY;
                    if @DBOBJECTTYPE = 1 set @DBOBJECTNAME = @DBOBJECTNAME + '()';
                    else if @DBOBJECTTYPE = 2 set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @BANKACCOUNTDEPOSITQUERY) + ''')';
                end

                --if @DEPOSITCOLLECTION is not null

                --begin

                --    declare @WTF nvarchar(max) = convert(nvarchar(max),@DEPOSITCOLLECTION);

                --    raiserror(@WTF,13,1);

                --    return 1;

                --end


                exec USP_GET_KEY_ACCESS;

                set @SQLTOEXEC = 
                    'select    
                        dbo.UFN_CONSTITUENT_BUILDNAME(BANKACCOUNT.BANKID) as [BANKNAME],
                        ISNULL((select 
                            dbo.UFN_BUILDFULLADDRESS(a.ID, a.ADDRESSBLOCK, a.CITY, a.STATEID, a.POSTCODE, a.COUNTRYID)+'' '' ADDRESS
                            from dbo.BANK c
                            inner join dbo.ADDRESS a on c.ID = a.CONSTITUENTID
                            and a.ISPRIMARY = 1  and c.ID = BANKACCOUNT.BANKID),'' '') as [BANKADDRESS],
                            BANKACCOUNTTRANSACTION.TRANSACTIONNUMBER,
                            BANKACCOUNT.ACCOUNTNAME,
                            convert(nvarchar(50), DecryptByKey(BANKACCOUNT.ACCOUNTNUMBER)) [ACCOUNTNUMBER],
                            BANKACCOUNT.ROUTINGNUMBER,
                            cast(FT.[DATE] as datetime) as TRANSACTIONDATE,
                            BANKACCOUNTDEPOSIT.NUMBEROFPAYMENTS,
                            FT.TRANSACTIONAMOUNT as AMOUNT,
                            BANKACCOUNTDEPOSIT.ID as [DEPOSITID],
                            BANKACCOUNT.SORTCODE,
                            dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS (''9568A6C2-F7AA-45fd-8F54-21FE9654EE2D'') as [ISUK],
                            CURRENCYPROPERTIES.ID as CURRENCYID,
                            CURRENCYPROPERTIES.ISO4217 as CURRENCYISO,
                            CURRENCYPROPERTIES.DECIMALDIGITS as CURRENCYDECIMALDIGITS,
                            CURRENCYPROPERTIES.CURRENCYSYMBOL as CURRENCYSYMBOL,
                            CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
                            BANKACCOUNTDEPOSIT.TOTALPAYMENTAMOUNT as TOTALPAYMENTAMOUNT,
                            TRANCURRENCYPROPERTIES.ID as TOTALCURRENCYID,
                            TRANCURRENCYPROPERTIES.ISO4217 as TOTALCURRENCYISO,
                            TRANCURRENCYPROPERTIES.DECIMALDIGITS as TOTALCURRENCYDECIMALDIGITS,
                            TRANCURRENCYPROPERTIES.CURRENCYSYMBOL as TOTALCURRENCYSYMBOL,
                            TRANCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as TOTALCURRENCYSYMBOLDISPLAYSETTINGCODE,
                            FT.POSTDATE,
                            ''http://www.blackbaud.com/LINKDEPOSITID?LINKDEPOSITID='' + convert(nvarchar(36), BANKACCOUNTDEPOSIT.ID) as [LINKDEPOSITID],
                            (select count(1) from dbo.FINANCIALTRANSACTION FT where FT.PARENTID = BANKACCOUNTDEPOSIT.ID and FT.TYPECODE in (24,25)) + (select count(1) from dbo.BANKACCOUNTDEPOSITCREDITPAYMENT where DEPOSITID = BANKACCOUNTDEPOSIT.ID) as [CORRECTIONCOUNT],
                            BANKINGSYSTEM.CONDITIONSETTINGNAME as BANKINGSYSTEMCODITIONSETTINGNAME,
                            BANKACCOUNT.BIC,
                            BANKACCOUNT.BANKCODE
                        from dbo.BANKACCOUNTDEPOSIT with (nolock)
                        inner join dbo.BANKACCOUNTTRANSACTION_EXT BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION.ID=BANKACCOUNTDEPOSIT.ID
                        inner join dbo.FINANCIALTRANSACTION FT on FT.ID = BANKACCOUNTTRANSACTION.ID
                        inner join dbo.BANKACCOUNT on BANKACCOUNT.ID = BANKACCOUNTTRANSACTION.BANKACCOUNTID 
                        inner join dbo.BANKINGSYSTEM on BANKINGSYSTEM.ID = BANKACCOUNT.BANKINGSYSTEMID
                        outer apply dbo.UFN_CURRENCY_GETPROPERTIES(BANKACCOUNT.TRANSACTIONCURRENCYID) CURRENCYPROPERTIES
                        outer apply dbo.UFN_CURRENCY_GETPROPERTIES(BANKACCOUNTDEPOSIT.TRANSACTIONCURRENCYID) TRANCURRENCYPROPERTIES ' + nchar(13);                        

                if @BANKACCOUNTDEPOSITQUERY is not null
                    set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on BANKACCOUNTDEPOSIT.[ID] = SELECTION.[ID]' + nchar(13);

                if @DEPOSITLIST is not null and @DEPOSITLIST <> ''
                begin
                    set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.UFN_DEPOSITREPORT_PARSEDEPOSITLIST(@DEPOSITLIST) as DLIST on BANKACCOUNTDEPOSIT.[ID] = DLIST.[DEPOSITID]' + nchar(13)
                    set @DEPOSITID = null;
                end

                set @SQLTOEXEC = @SQLTOEXEC + '
                    where FT.TYPECODE = 10 and ((@ISADMIN = 1 or
                        @APPUSER_IN_NONRACROLE = 1)    or
                        dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, BANKACCOUNT.BANKID, @APPUSER_IN_NOSECGROUPROLE) = 1) ';
                if (@DATETOUSE = 0 or @DATETOUSE is null)
                begin
                    if not @STARTDATE is null
                        set @SQLTOEXEC = @SQLTOEXEC + '
                            and cast(FT.DATE as datetime) >= @STARTDATE ';
                    if not @ENDDATE is null
                        set @SQLTOEXEC = @SQLTOEXEC + '
                            and cast(FT.DATE as datetime) <= @ENDDATE ';
                end
                else
                begin
                    if not @STARTDATE is null
                        set @SQLTOEXEC = @SQLTOEXEC + '
                            and cast(FT.POSTDATE as datetime) >= @STARTDATE ';
                    if not @ENDDATE is null
                        set @SQLTOEXEC = @SQLTOEXEC + '
                            and cast(FT.POSTDATE as datetime) <= @ENDDATE ';
                end
                if not @DEPOSITID is null
                    set @SQLTOEXEC = @SQLTOEXEC + '
                        and BANKACCOUNTDEPOSIT.ID = @DEPOSITID ';

                -- PBS Filter deposits based on Accout system security.

                if dbo.UFN_VALID_BASICGL_INSTALLED() = 1 and @ISADMIN = 0
                    set @SQLTOEXEC = @SQLTOEXEC + ' and (FT.PDACCOUNTSYSTEMID in (select ID from dbo.UFN_PDACCOUNTSYSTEM_GETSYSTEMIDSFORUSER(@CURRENTAPPUSERID)))'

                exec sp_executesql @SQLTOEXEC, 
                    N'@DATETOUSE int, @STARTDATE datetime, @ENDDATE datetime, @DEPOSITID uniqueidentifier, @ISADMIN bit, @APPUSER_IN_NONRACROLE bit, @APPUSER_IN_NOSECGROUPROLE bit, @CURRENTAPPUSERID uniqueidentifier, @DEPOSITLIST nvarchar(max)',
                    @DATETOUSE=@DATETOUSE, @STARTDATE=@STARTDATE, @ENDDATE=@ENDDATE, @DEPOSITID=@DEPOSITID, @ISADMIN=@ISADMIN, @APPUSER_IN_NONRACROLE=@APPUSER_IN_NONRACROLE, @APPUSER_IN_NOSECGROUPROLE=@APPUSER_IN_NOSECGROUPROLE, @CURRENTAPPUSERID=@CURRENTAPPUSERID, @DEPOSITLIST=@DEPOSITLIST;
                exec USP_CLOSE_KEY_ACCESS;