USP_REPORT_DEPOSITREPORT_DEPOSITSLIP

            create procedure [dbo].[USP_REPORT_DEPOSITREPORT_DEPOSITSLIP]
            (
                @DEPOSITID uniqueidentifier,
                @BANKNAME nvarchar(100),
                @BANKADDRESS nvarchar(150),
                @BANKACCOUNTNAME nvarchar(100),
                @DEPOSITDATE date
            )
            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 @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 @DBOBJECTNAME nvarchar(128);
                declare @DBOBJECTTYPE smallint;
                declare @TRANSACTIONCURRENCYID uniqueidentifier
                declare @MICRLINE nvarchar(100)

                select @TRANSACTIONCURRENCYID=TRANSACTIONCURRENCYID from BANKACCOUNTDEPOSIT where ID = @DEPOSITID
                set @MICRLINE = dbo.[UFN_TREASURY_BUILDDEPOSITMICRLINE](@DEPOSITID)

                declare @TEMPDEPOSITSLIP table (
                    [DATE] date,
                    [PAYMENTMETHOD] varchar(100),
                    [REFERENCE] varchar(100),
                    [AMOUNT] money,
                    [CORRECTIONAMOUNT] money,
                    [DEPOSITID] uniqueidentifier,
                    [BANKNAME] varchar(100),
                    [BANKADDRESS] nvarchar(150),
                    [BANKACCOUNTNAME] nvarchar(100),
                    [DEPOSITDATE] date,
                    [MICRLINE] nvarchar(100))

                insert into @TEMPDEPOSITSLIP
                    select  
                        cast(REVENUE.DATE as datetime) [DATE],
                        REVENUEPAYMENTMETHOD.PAYMENTMETHOD AS [PAYMENTMETHOD],
                        REVENUE_EXT.REFERENCE AS [PAYMENTSOURCE],
                            IsNull(REVENUE.TRANSACTIONAMOUNT, REVENUE.BASEAMOUNT) AS AMOUNT,
                        null as [CORRECTIONAMOUNT],
                        BANKACCOUNTDEPOSITPAYMENT.DEPOSITID,
                        @BANKNAME as [BANKNAME],
                        @BANKADDRESS as [BANKADDRESS],
                        @BANKACCOUNTNAME as [BANKACCOUNTNAME],
                        @DEPOSITDATE as [DEPOSITDATE],
                        @MICRLINE as MICRLINE
                    from dbo.FINANCIALTRANSACTION REVENUE with (nolock)
                    inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
                    inner join dbo.BANKACCOUNTDEPOSITPAYMENT on REVENUE.ID = BANKACCOUNTDEPOSITPAYMENT.ID
                    left outer join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID                    
                    where (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE IN (0,1,2,9,10)) 
                        and REVENUE.TYPECODE = 0  
                        and (BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = @DEPOSITID)

                    union all
                    select  
                        isnull(cast(FT.[DATE] as datetime), '') [DATE],
                        BADCE.PAYMENTMETHOD,
                        null AS [PAYMENTSOURCE],
                        null as [AMOUNT],
                        (case when FT.TYPECODE=24 then -FT.TRANSACTIONAMOUNT else FT.TRANSACTIONAMOUNT end) as [CORRECTIONAMOUNT],
                        FT.PARENTID [DEPOSITID],
                        @BANKNAME as [BANKNAME],
                        @BANKADDRESS as [BANKADDRESS],
                        @BANKACCOUNTNAME as [BANKACCOUNTNAME],
                        @DEPOSITDATE as [DEPOSITDATE],
                        @MICRLINE as MICRLINE
                    from dbo.FINANCIALTRANSACTION as FT
                    inner join dbo.BANKACCOUNTDEPOSITCORRECTION_EXT as BADCE on BADCE.ID = FT.ID
                    where (BADCE.PAYMENTMETHODCODE IN (0,1,2,3))  
                        and FT.TYPECODE in (24,25)
                        and (FT.PARENTID = @DEPOSITID)

                    union all                
                    select 
                        CAST([CREDIT].[DATE] as datetime) [TRANSACTIONDATE],
                        [CREDITPAYMENT].[PAYMENTMETHOD],
                        null as [PAYMENTSOURCE],
                        0 as [AMOUNT],
                        -1 * [CREDITPAYMENT].[AMOUNT] as [CORRECTIONAMOUNT],
                        [BANKACCOUNTDEPOSITCREDITPAYMENT].[DEPOSITID],
                        @BANKNAME as [BANKNAME],
                        @BANKADDRESS as [BANKADDRESS],
                        @BANKACCOUNTNAME as [BANKACCOUNTNAME],
                        @DEPOSITDATE as [DEPOSITDATE],
                        @MICRLINE as MICRLINE
                    from dbo.[BANKACCOUNTDEPOSITCREDITPAYMENT] with (nolock)
                    inner join dbo.[CREDITPAYMENT] on [BANKACCOUNTDEPOSITCREDITPAYMENT].[ID] = [CREDITPAYMENT].[ID]
                    inner join dbo.FINANCIALTRANSACTION [CREDIT] on [CREDITPAYMENT].[CREDITID] = [CREDIT].[ID]
                    where [BANKACCOUNTDEPOSITCREDITPAYMENT].[DEPOSITID] = @DEPOSITID

                order by cast(REVENUE.DATE as datetime) desc

                select top 1
                    [DATE] AS [DATE]
                    ,[REFERENCE] AS [REFERENCE]
                    ,[AMOUNT] AS [AMOUNT]
                    ,[CORRECTIONAMOUNT] AS [CORRECTIONAMOUNT]
                    ,[BANKNAME] AS [BANKNAME]
                    ,[BANKADDRESS] AS [BANKADDRESS]
                    ,[BANKACCOUNTNAME] AS [BANKACCOUNTNAME]
                    ,[DEPOSITDATE] AS [DEPOSITDATE]
                    ,[MICRLINE] AS [MICRLINE]
                    ,case when [CASH].[TOTAL] < 0 then 0 else [CASH].[TOTAL] end as CASHTOTAL
                    ,case when [CHECK].[TOTAL] < 0 then 0 else [CHECK].[TOTAL] end as CHECKTOTAL
                    ,case when [CREDITCARD].[TOTAL] < 0 then 0 else [CREDITCARD].[TOTAL] end as CREDITCARDTOTAL
                    ,case when [OTHER].[TOTAL] < 0 then 0 else [OTHER].[TOTAL] end as OTHERTOTAL
                    ,(select COUNT(AMOUNT) from @TEMPDEPOSITSLIP) AS NUMRECORDS,
                    CURRENCYPROPERTIES.ID as CURRENCYID,
                    CURRENCYPROPERTIES.ISO4217 as CURRENCYISO,
                    CURRENCYPROPERTIES.DECIMALDIGITS as CURRENCYDECIMALDIGITS,
                    CURRENCYPROPERTIES.CURRENCYSYMBOL as CURRENCYSYMBOL,
                    CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE
                from @TEMPDEPOSITSLIP
                outer apply(select SUM(ISNULL(AMOUNT, 0) + ISNULL(CORRECTIONAMOUNT, 0)) AS [TOTAL] from @TEMPDEPOSITSLIP where [PAYMENTMETHOD] = 'Cash') AS [CASH]
                outer apply(select SUM(ISNULL(AMOUNT, 0) + ISNULL(CORRECTIONAMOUNT, 0)) AS [TOTAL] from @TEMPDEPOSITSLIP where [PAYMENTMETHOD] = 'Check') AS [CHECK]
                outer apply(select SUM(ISNULL(AMOUNT, 0) + ISNULL(CORRECTIONAMOUNT, 0)) AS [TOTAL] from @TEMPDEPOSITSLIP where [PAYMENTMETHOD] = 'Credit card') AS [CREDITCARD]
                outer apply(select SUM(ISNULL(AMOUNT, 0) + ISNULL(CORRECTIONAMOUNT, 0)) AS [TOTAL] from @TEMPDEPOSITSLIP where [PAYMENTMETHOD] = 'Other') AS [OTHER]
                outer apply dbo.UFN_CURRENCY_GETPROPERTIES(@TRANSACTIONCURRENCYID) CURRENCYPROPERTIES