USP_DATALIST_SALESDEPOSITPROCESS_SUMMARYREPORTDEPOSIT

                CREATE procedure dbo.USP_DATALIST_SALESDEPOSITPROCESS_SUMMARYREPORTDEPOSIT 
                (
                    @PROCESSSTATUSID uniqueidentifier
                )
                with execute as owner
                as
                    set nocount on;

                    declare @ORGCURRENCYID uniqueidentifier;
                    declare @TABLENAME nvarchar(255);
                    declare @SQL nvarchar(4000);

                    select @ORGCURRENCYID = ID from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1;

                    select @TABLENAME = [BUSINESSPROCESSOUTPUT].[TABLENAME]
                    from dbo.[BUSINESSPROCESSSTATUS]
                    inner join dbo.[BUSINESSPROCESSOUTPUT]
                        on [BUSINESSPROCESSSTATUS].[ID] = [BUSINESSPROCESSOUTPUT].[BUSINESSPROCESSSTATUSID]
                    where [BUSINESSPROCESSSTATUS].[ID] = @PROCESSSTATUSID

                    set @SQL = 'select 
                                    SD.[BANKACCOUNTNAME],
                                    SD.[DEPOSITNUMBER],
                                    SD.[REFERENCENAME],
                                    case SD.[PAYMENTMETHODCODE]
                                        when 2 then isnull(nullif(SD.[PAYMENTTYPE], ''''), ''Credit Card - <Card type not specified>'')
                                        when 10 then SD.[PAYMENTTYPE]
                                        else SD.[PAYMENTMETHOD]
                                    end as [PAYMENTTYPE],
                                    sum(SD.[NUMBEROFPAYMENTS]) as [NUMBEROFPAYMENTS],
                                    case SD.[ISREFUND]
                                        when 0 then sum(SD.[AMOUNT])
                                        else -1 * sum(SD.[AMOUNT])
                                    end as [AMOUNT],
                                    SD.[ISREFUND],
                                    SD.[ISOVERSHORT],
                                    BAT.POSTDATE,
                                    cast(BAT.[DATE] as datetime) TRANSACTIONDATE
                                    ,TRANCURRENCYPROPERTIES.ID CURRENCYID
                                    ,TRANCURRENCYPROPERTIES.ISO4217
                                    ,TRANCURRENCYPROPERTIES.DECIMALDIGITS
                                    ,TRANCURRENCYPROPERTIES.CURRENCYSYMBOL
                                    ,TRANCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE
                                    from dbo.' + @TABLENAME + ' SD
                                        left join dbo.FINANCIALTRANSACTION BAT on SD.DEPOSITID = BAT.ID '
                                        IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLENAME AND  COLUMN_NAME = 'CURRENCYID')
                                            set @SQL = @SQL + 'outer apply dbo.UFN_CURRENCY_GETPROPERTIES(isnull(SD.CURRENCYID, @ORGCURRENCYID)) TRANCURRENCYPROPERTIES '
                                        else
                                            set @SQL = @SQL + 'outer apply dbo.UFN_CURRENCY_GETPROPERTIES(@ORGCURRENCYID) TRANCURRENCYPROPERTIES '
                                    set @SQL = @SQL + ' group by
                                        SD.[BANKACCOUNTNAME],
                                        SD.[REFERENCENAME],
                                        SD.[DEPOSITNUMBER],
                                        SD.[PAYMENTMETHODCODE],
                                        SD.[PAYMENTTYPE],
                                        SD.[PAYMENTMETHOD],
                                        SD.[ISREFUND],
                                        SD.[ISOVERSHORT],
                                        BAT.POSTDATE,
                                        cast(BAT.[DATE] as datetime)
                                        ,TRANCURRENCYPROPERTIES.ID
                                        ,TRANCURRENCYPROPERTIES.ISO4217
                                        ,TRANCURRENCYPROPERTIES.DECIMALDIGITS
                                        ,TRANCURRENCYPROPERTIES.CURRENCYSYMBOL
                                        ,TRANCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE
                                    order by
                                        SD.[ISREFUND],
                                        SD.[ISOVERSHORT],
                                        SD.[BANKACCOUNTNAME],
                                        SD.[DEPOSITNUMBER],
                                        SD.[PAYMENTMETHODCODE],
                                        SD.[PAYMENTTYPE]';

                    exec sp_executesql @SQL, N'@ORGCURRENCYID uniqueidentifier',@ORGCURRENCYID=@ORGCURRENCYID;