USP_REPORT_DEPOSITREPORT_PAYMENTS_SUMMARY

            create procedure dbo.USP_REPORT_DEPOSITREPORT_PAYMENTS_SUMMARY
            (
                @DEPOSITID uniqueidentifier 
            )
            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 @SQLTOEXEC nvarchar(max);

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

                set @SQLTOEXEC =                         
                    'select 
                        REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
                        coalesce(sum(REVENUE.TRANSACTIONAMOUNT),0) as [AMOUNT],
                        count(*) as [NUMBEROFPAYMENTS],
                        0 as [NUMBEROFCORRECTIONS],
                        CP0.ID as CURRENCYID,
                        CP0.ISO4217 as CURRENCYISO,
                        CP0.DECIMALDIGITS as CURRENCYDECIMALDIGITS,
                        CP0.CURRENCYSYMBOL as CURRENCYSYMBOL,
                        CP0.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
                        count(coalesce (CREDITTYPECODE.DESCRIPTION,OTHERPAYMENTMETHODCODE.DESCRIPTION,''<Card type not specified>'')) as [PAYMENTTYPECOUNT],
                        coalesce (CREDITTYPECODE.DESCRIPTION,OTHERPAYMENTMETHODCODE.DESCRIPTION,''<Card type not specified>'') as [PAYMENTTYPEDESCRIPTION],
                        0 as [CORRECTIONTYPECOUNT],
                        REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE as [PAYMENTMETHODCODE]
                    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
                    inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
                    left outer join dbo.CREDITCARDPAYMENTMETHODDETAIL on CREDITCARDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.id
                    left outer join dbo.OTHERPAYMENTMETHODDETAIL on OTHERPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
                    left outer join dbo.OTHERPAYMENTMETHODCODE on OTHERPAYMENTMETHODCODE.ID = OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID
                    left outer join dbo.CREDITTYPECODE on CREDITTYPECODE.ID = CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID
                    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(REVENUE.TRANSACTIONCURRENCYID) as CP0
                    where (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE IN (0,1,2,9,10)) 
                        and REVENUE.TYPECODE = 0 and REVENUE.DELETEDON is null
                        and (BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = @DEPOSITID) 
                    group by [REVENUEPAYMENTMETHOD].[PAYMENTMETHOD], REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE, CP0.ID, CP0.ISO4217, CP0.DECIMALDIGITS, CP0.CURRENCYSYMBOL, CP0.SYMBOLDISPLAYSETTINGCODE,CREDITTYPECODE.DESCRIPTION,OTHERPAYMENTMETHODCODE.DESCRIPTION  '    

                set @SQLTOEXEC = @SQLTOEXEC + 
                    'union all
                    select 
                        [BADCE].[PAYMENTMETHOD],
                        coalesce(sum((case when BANKACCOUNTDEPOSITCORRECTION.TYPECODE=24 then -BANKACCOUNTDEPOSITCORRECTION.TRANSACTIONAMOUNT else BANKACCOUNTDEPOSITCORRECTION.TRANSACTIONAMOUNT end)),0) as [AMOUNT],
                        0 as [NUMBEROFPAYMENTS],
                        count(*) as [NUMBEROFCORRECTIONS],
                        CP1.ID as CURRENCYID,
                        CP1.ISO4217 as CURRENCYISO,
                        CP1.DECIMALDIGITS as CURRENCYDECIMALDIGITS,
                        CP1.CURRENCYSYMBOL as CURRENCYSYMBOL,
                        CP1.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
                        null as [PAYMENTTYPECOUNT],
                        case BADCE.PAYMENTMETHODCODE when 2 then ''<Card type not specified>'' when 3 then ''<Type not specified>'' else null end as [PAYMENTTYPEDESCRIPTION],
                        count(*) as [CORRECTIONTYPECOUNT],
                        case BADCE.PAYMENTMETHODCODE when 3 then 10 else BADCE.PAYMENTMETHODCODE end as [PAYMENTMETHODCODE]
                    from dbo.FINANCIALTRANSACTION [BANKACCOUNTDEPOSITCORRECTION] with (nolock)
                    inner join dbo.BANKACCOUNTDEPOSITCORRECTION_EXT as BADCE on BADCE.ID = BANKACCOUNTDEPOSITCORRECTION.ID
                    inner join BANKACCOUNTDEPOSIT on BANKACCOUNTDEPOSIT.ID = [BANKACCOUNTDEPOSITCORRECTION].[PARENTID]
                    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(BANKACCOUNTDEPOSIT.TRANSACTIONCURRENCYID) as CP1
                    where ([BADCE].[PAYMENTMETHODCODE] IN (0,1,2,3))  
                            and ([BANKACCOUNTDEPOSITCORRECTION].[PARENTID] = @DEPOSITID)
                            and BANKACCOUNTDEPOSITCORRECTION.TYPECODE in (24,25)
                    group by [BADCE].[PAYMENTMETHOD], BADCE.PAYMENTMETHODCODE, CP1.ID, CP1.ISO4217, CP1.DECIMALDIGITS, CP1.CURRENCYSYMBOL, CP1.SYMBOLDISPLAYSETTINGCODE  '

                set @SQLTOEXEC = @SQLTOEXEC + 
                    'union all
                    select 
                        [CREDITPAYMENT].[PAYMENTMETHOD],
                        -1 * coalesce(sum([CREDITPAYMENT].[AMOUNT]),0) as [AMOUNT],
                        0 as [NUMBEROFPAYMENTS],
                        count(*) as [NUMBEROFCORRECTIONS],
                        CP2.ID as CURRENCYID,
                        CP2.ISO4217 as CURRENCYISO,
                        CP2.DECIMALDIGITS as CURRENCYDECIMALDIGITS,
                        CP2.CURRENCYSYMBOL as CURRENCYSYMBOL,
                        CP2.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
                        0 as [PAYMENTTYPECOUNT],
                        coalesce(CREDITTYPECODE.DESCRIPTION,OTHERPAYMENTMETHODCODE.DESCRIPTION,''<Card type not specified>'') as [PAYMENTTYPEDESCRIPTION],
                        count(1) as [CORRECTIONTYPECOUNT],
                        CREDITPAYMENT.PAYMENTMETHODCODE
                    from dbo.[BANKACCOUNTDEPOSITCREDITPAYMENT] with (nolock)
                    inner join dbo.[CREDITPAYMENT] on [BANKACCOUNTDEPOSITCREDITPAYMENT].[ID] = [CREDITPAYMENT].[ID]
                    left outer join BANKACCOUNTDEPOSIT on BANKACCOUNTDEPOSIT.ID = [BANKACCOUNTDEPOSITCREDITPAYMENT].[DEPOSITID]
                    left outer join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = CREDITPAYMENT.REVENUEID and REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE=CREDITPAYMENT.PAYMENTMETHODCODE
                    left outer join dbo.CREDITCARDPAYMENTMETHODDETAIL on CREDITCARDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
                    left outer join dbo.OTHERPAYMENTMETHODDETAIL on OTHERPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
                    left outer join dbo.OTHERPAYMENTMETHODCODE on OTHERPAYMENTMETHODCODE.ID = OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID
                    left outer join dbo.CREDITTYPECODE on CREDITTYPECODE.ID = CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID
                    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(BANKACCOUNTDEPOSIT.TRANSACTIONCURRENCYID) CP2
                    where [BANKACCOUNTDEPOSITCREDITPAYMENT].[DEPOSITID] = @DEPOSITID 
                    group by [CREDITPAYMENT].[PAYMENTMETHOD], CP2.ID, CP2.ISO4217, CP2.DECIMALDIGITS, CP2.CURRENCYSYMBOL, CP2.SYMBOLDISPLAYSETTINGCODE,CREDITPAYMENT.REVENUEID,CREDITPAYMENT.PAYMENTMETHODCODE,[CREDITPAYMENT].[PAYMENTMETHODCODE]
                        ,coalesce(CREDITTYPECODE.DESCRIPTION,OTHERPAYMENTMETHODCODE.DESCRIPTION,''<Card type not specified>'') '

                exec sp_executesql @SQLTOEXEC,
                    N'@DEPOSITID uniqueidentifier',
                    @DEPOSITID=@DEPOSITID