USP_REPORT_DEPOSITREPORT_PAYMENTS

            create procedure dbo.USP_REPORT_DEPOSITREPORT_PAYMENTS
            (
                @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 
                        cast(REVENUE.DATE as datetime) [DATE],
                        REVENUEPAYMENTMETHOD.PAYMENTMETHOD AS [PAYMENTMETHOD],
                        REVENUE_EXT.REFERENCE AS [PAYMENTSOURCE],
                        REVENUE.TRANSACTIONAMOUNT as AMOUNT,
                        BANKACCOUNTDEPOSITPAYMENT.DEPOSITID,
                        BANKACCOUNTDEPOSITPAYMENT.ID,
                        null  as [CORRECTIONDATE],
                        null as [CORRECTIONMETHOD],
                        null as [CORRECTIONTYPE],
                        null as [CORRECTIONAMOUNT],
                        null as [CORRECTIONID],
                        CP0.ID as CURRENCYID,
                        CP0.ISO4217 as CURRENCYISO,
                        CP0.DECIMALDIGITS as CURRENCYDECIMALDIGITS,
                        CP0.CURRENCYSYMBOL as CURRENCYSYMBOL,
                        CP0.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
                        coalesce (CREDITTYPECODE.DESCRIPTION,OTHERPAYMENTMETHODCODE.DESCRIPTION,''<Card type not specified>'') as [PAYMENTTYPEDESCRIPTION],
                        coalesce (CASHPAYMENTMETHODDETAIL.REFERENCENUMBER,CHECKPAYMENTMETHODDETAIL.CHECKNUMBER,CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER,OTHERPAYMENTMETHODDETAIL.REFERENCENUMBER) as [REFERENCECHECKNUMBER],
                        CREDITCARDPAYMENTMETHODDETAIL.AUTHORIZATIONCODE,
                        REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE AS [PAYMENTMETHODCODE]
                        from dbo.BANKACCOUNTDEPOSITPAYMENT with (nolock)
                        inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = BANKACCOUNTDEPOSITPAYMENT.ID
                        inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
                        inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
                        left outer join dbo.CREDITCARDPAYMENTMETHODDETAIL on CREDITCARDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.id
                        left outer join dbo.CASHPAYMENTMETHODDETAIL on CASHPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
                        left outer join dbo.CHECKPAYMENTMETHODDETAIL on CHECKPAYMENTMETHODDETAIL.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  ' + nchar(13);                        

                set @SQLTOEXEC = @SQLTOEXEC + 
                    'where (BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = @DEPOSITID) 
                        and REVENUE.TYPECODE = 0  and REVENUE.DELETEDON is null
                        and (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE IN (0,1,2,9,10)) '

                set @SQLTOEXEC = @SQLTOEXEC + 
                    'union all
                    select 
                        isnull(cast(BANKACCOUNTDEPOSITCORRECTION.[DATE] as datetime), '''') as [DATE],
                        null AS [PAYMENTMETHOD],
                        null AS [PAYMENTSOURCE],
                        null as [AMOUNT],
                        isnull(BANKACCOUNTDEPOSITCORRECTION.PARENTID, ''00000000-0000-0000-0000-000000000000'') as DEPOSITID,
                        null as ID, 
                        isnull(cast(BANKACCOUNTDEPOSITCORRECTION.[DATE] as datetime), '''') as [CORRECTIONDATE],
                        BADCE.PAYMENTMETHOD as [CORRECTIONMETHOD],
                        case cast(BANKACCOUNTDEPOSITCORRECTION.TYPECODE - 24 + 2 * BADCE.CORRECTIONTYPECODE as tinyint) 
                          when (0) then N''Sales - Short'' 
                          when (1) then N''Sales - Over'' 
                          when (2) then N''Treasury - Short'' 
                          when (3) then N''Treasury - Over'' else null end as CORRECTIONTYPE,
                        (case when BANKACCOUNTDEPOSITCORRECTION.TYPECODE=24 then -BANKACCOUNTDEPOSITCORRECTION.TRANSACTIONAMOUNT else BANKACCOUNTDEPOSITCORRECTION.TRANSACTIONAMOUNT end) as [CORRECTIONAMOUNT],
                        BANKACCOUNTDEPOSITCORRECTION.ID as [CORRECTIONID],
                        CP1.ID as CURRENCYID,
                        CP1.ISO4217 as CURRENCYISO,
                        CP1.DECIMALDIGITS as CURRENCYDECIMALDIGITS,
                        CP1.CURRENCYSYMBOL as CURRENCYSYMBOL,
                        CP1.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
                        null as [PAYMENTTYPEDESCRIPTION],
                        null as [REFERENCECHECKNUMBER],
                        null as [AUTHORIZATIONCODE],
                        0 AS [PAYMENTMETHODCODE]
                        from dbo.BANKACCOUNTDEPOSITCORRECTION_EXT as BADCE with (nolock)
                        inner join dbo.FINANCIALTRANSACTION BANKACCOUNTDEPOSITCORRECTION on BADCE.ID = BANKACCOUNTDEPOSITCORRECTION.ID
                        outer apply dbo.UFN_CURRENCY_GETPROPERTIES(BANKACCOUNTDEPOSITCORRECTION.TRANSACTIONCURRENCYID) as CP1 ' + nchar(13);

                set @SQLTOEXEC = @SQLTOEXEC + 
                    'where (BADCE.PAYMENTMETHODCODE IN (0,1,2,3))    
                        and BANKACCOUNTDEPOSITCORRECTION.TYPECODE in (24,25)
                        and (BANKACCOUNTDEPOSITCORRECTION.PARENTID = @DEPOSITID) '                         

                set @SQLTOEXEC = @SQLTOEXEC + 
                    'union all
                    select 
                        CAST(CREDIT.[DATE] as datetime) as [DATE],
                        null as [PAYMENTMETHOD],
                        null as [PAYMENTSOURCE],
                        null as [AMOUNT],
                        [BANKACCOUNTDEPOSITCREDITPAYMENT].[DEPOSITID],
                        null as [ID], 
                        CAST(CREDIT.[DATE] as datetime) as [CORRECTIONDATE],
                        [CREDITPAYMENT].[PAYMENTMETHOD] as [CORRECTIONMETHOD],
                        dbo.[UFN_CREDIT_TYPECODE_GETDESCRIPTION](0) as [CORRECTIONTYPE],
                        -1 * [CREDITPAYMENT].[AMOUNT] as [CORRECTIONAMOUNT],
                        [CREDITPAYMENT].[ID] as [CORRECTIONID],
                        CP2.ID as CURRENCYID,
                        CP2.ISO4217 as CURRENCYISO,
                        CP2.DECIMALDIGITS as CURRENCYDECIMALDIGITS,
                        CP2.CURRENCYSYMBOL as CURRENCYSYMBOL,
                        CP2.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
                        coalesce (CREDITTYPECODE.DESCRIPTION,OTHERPAYMENTMETHODCODE.DESCRIPTION,''<Card type not specified>'') as [PAYMENTTYPEDESCRIPTION],
                        null as [REFERENCECHECKNUMBER],
                        null as [AUTHORIZATIONCODE],
                        REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE as [PAYMENTMETHODCODE]
                        from dbo.[BANKACCOUNTDEPOSITCREDITPAYMENT] with (nolock)
                        inner join dbo.[CREDITPAYMENT]
                            on [BANKACCOUNTDEPOSITCREDITPAYMENT].[ID] = [CREDITPAYMENT].[ID]
                        inner join dbo.FINANCIALTRANSACTION [CREDIT]
                            on [CREDITPAYMENT].[CREDITID] = [CREDIT].[ID]
                        left outer join BANKACCOUNTDEPOSIT 
                            on BANKACCOUNTDEPOSIT.ID = [BANKACCOUNTDEPOSITCREDITPAYMENT].[DEPOSITID]
                        left outer join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = CREDITPAYMENT.REVENUEID
                        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 CREDIT.TYPECODE = 23 and [BANKACCOUNTDEPOSITCREDITPAYMENT].[DEPOSITID] = @DEPOSITID '

                set @SQLTOEXEC = @SQLTOEXEC + 
                    'order by [CORRECTIONTYPE] desc, [CORRECTIONAMOUNT] desc, [DATE] desc '

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