USP_REPORT_BBNCEVENTREGISTRATIONBATCHCONTROLREPORT

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

                declare @TABLENAME nvarchar(128);

                select @TABLENAME = dbo.UFN_BATCH_GETREPORTTABLENAME(@BATCHID, 0)

                declare @CURRENCYFIELD nvarchar(100);

                if exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TABLENAME and COLUMN_NAME = 'CURRENCYID')
                    select @CURRENCYFIELD = 'CURRENCYID';
                else
                    set @CURRENCYFIELD = '''' + cast(coalesce(dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(), '00000000-0000-0000-0000-000000000000') as nvarchar(36)) + '''';

                declare @SQLTOEXEC nvarchar(max);

                IF COL_LENGTH( @TABLENAME , 'BATCHID') IS NULL
                BEGIN

                set @SQLTOEXEC = N'set nocount on;
                    with TBL_CTE as
                    (
                    select  
                        ' + @CURRENCYFIELD + ' as REVENUECURRENCYID
                        ,ID
                        ,DONORNAME
                        ,null as AMOUNT
                        ,null as NUMBEROFREGISTRANTS
                        ,null as APPEAL
                        ,null as EVENT
                        ,null as CONSTITUENTLOOKUPID
                        ,null as DESIGNATION
                        ,null as DATE
                        ,null as PAYMENTMETHOD
            ,null as ISPLEDGE
            ,null as ISPAYMENT
                ,'''' AS REVENUETYPE
                    from dbo.' + @TABLENAME + '
                    )
                    select
                        VIEWDATA.ID,
                        BATCH.BATCHNUMBER,
                        BATCH.STATUS,
                        EXCEPTIONBATCH.BATCHNUMBER EXCEPTIONBATCHNAME,
                        coalesce((select USERNAME from dbo.APPUSER where ID = BATCH.APPUSERID), '''') as [OWNER],
                        BATCH.PROJECTEDNUMBEROFRECORDS,
                        BATCH.PROJECTEDTOTALAMOUNT,
                        VIEWDATA.CONSTITUENTLOOKUPID,
                        VIEWDATA.DONORNAME as CONSTITUENTNAME,
                (CONVERT(VARCHAR(10), VIEWDATA.DATE, 101)) AS DATE,
                        VIEWDATA.AMOUNT,
                        VIEWDATA.DESIGNATION,
                        VIEWDATA.APPEAL,
                        VIEWDATA.PAYMENTMETHOD,
                        VIEWDATA.REVENUETYPE,
                        VIEWDATA.EVENT,
                        VIEWDATA.NUMBEROFREGISTRANTS,
            VIEWDATA.ISPLEDGE,
            VIEWDATA.ISPAYMENT,
                        CURRENCY.ISO4217,
                        CURRENCY.DECIMALDIGITS,
                        CURRENCY.CURRENCYSYMBOL,
                        CURRENCY.SYMBOLDISPLAYSETTINGCODE
                    from TBL_CTE as VIEWDATA
                        inner join dbo.BATCH on BATCH.ID = @BATCHID
                        inner join dbo.CURRENCY on CURRENCY.ID = VIEWDATA.REVENUECURRENCYID
                        /*    JamesWill WI200374 2012-07-20 The BATCH.EXCEPTIONBATCHNAME field is only used when overriding the default exception batch number. 
                            So we need to actually look up the exception bathc using the exception batch change data. */
                        left join 
                        (
                            select top 1 
                                EXCEPTIONBATCH.ID,
                                EXCEPTIONBATCH.ORIGINATINGBATCHID,
                                EXCEPTIONBATCH.BATCHNUMBER
                            from dbo.BATCH EXCEPTIONBATCH 
                            where EXCEPTIONBATCH.ORIGINATINGBATCHID = @BATCHID
                        ) EXCEPTIONBATCH on EXCEPTIONBATCH.ORIGINATINGBATCHID = BATCH.ID';
                END

                IF COL_LENGTH( @TABLENAME , 'BATCHID') IS NOT NULL
                BEGIN

                set @SQLTOEXEC = N'set nocount on;
                    with TBL_CTE as
                    (
                    select  distinct 
                        ' + @CURRENCYFIELD + ' as REVENUECURRENCYID
                        ,ID
                        ,AMOUNT
                        ,NUMBEROFREGISTRANTS
                        ,APPEAL
                        ,EVENT
            ,EVENTPRICEID
                        ,DONORNAME
                        ,CONSTITUENTLOOKUPID
                        ,DESIGNATION
                        ,DATE
                        ,PAYMENTMETHOD
            ,ISPLEDGE
            ,ISPAYMENT
            ,BATCHID
            ,BATCHBBNCEVENTREGISTRATIONID
                ,case when ISPAYMENT = 1 then ''Payment (Event Registrations)'' else ''None'' end AS REVENUETYPE
                    from dbo.' + @TABLENAME + '
                    where BATCHID = @BATCHID
                    )
                    select
                        VIEWDATA.ID,
                        BATCH.BATCHNUMBER,
                        BATCH.STATUS,
                        EXCEPTIONBATCH.BATCHNUMBER EXCEPTIONBATCHNAME,
                        coalesce((select USERNAME from dbo.APPUSER where ID = BATCH.APPUSERID), '''') as [OWNER],
                        BATCH.PROJECTEDNUMBEROFRECORDS,
                        BATCH.PROJECTEDTOTALAMOUNT,
                        VIEWDATA.CONSTITUENTLOOKUPID,
                        VIEWDATA.DONORNAME as CONSTITUENTNAME,
                (CONVERT(VARCHAR(10), VIEWDATA.DATE, 101)) AS DATE,
                        VIEWDATA.AMOUNT,
                        VIEWDATA.DESIGNATION,
                        VIEWDATA.APPEAL,
                        VIEWDATA.PAYMENTMETHOD,
                        VIEWDATA.REVENUETYPE,
                        VIEWDATA.EVENT,
                        VIEWDATA.NUMBEROFREGISTRANTS,
            VIEWDATA.ISPLEDGE,
            VIEWDATA.ISPAYMENT,
                        CURRENCY.ISO4217,
                        CURRENCY.DECIMALDIGITS,
                        CURRENCY.CURRENCYSYMBOL,
                        CURRENCY.SYMBOLDISPLAYSETTINGCODE
                    from TBL_CTE as VIEWDATA
                        inner join dbo.BATCH on BATCH.ID = @BATCHID and BATCH.ID = VIEWDATA.BATCHID
                        inner join dbo.CURRENCY on CURRENCY.ID = VIEWDATA.REVENUECURRENCYID
                        /*    JamesWill WI200374 2012-07-20 The BATCH.EXCEPTIONBATCHNAME field is only used when overriding the default exception batch number. 
                            So we need to actually look up the exception bathc using the exception batch change data. */
                        left join 
                        (
                            select top 1 
                                EXCEPTIONBATCH.ID,
                                EXCEPTIONBATCH.ORIGINATINGBATCHID,
                                EXCEPTIONBATCH.BATCHNUMBER
                            from dbo.BATCH EXCEPTIONBATCH 
                            where EXCEPTIONBATCH.ORIGINATINGBATCHID = @BATCHID
                        ) EXCEPTIONBATCH on EXCEPTIONBATCH.ORIGINATINGBATCHID = BATCH.ID';
                END

                exec sp_executesql @SQLTOEXEC,
                    N'@BATCHID uniqueidentifier',
                    @BATCHID=@BATCHID;