USP_REPORT_REVENUEBATCHCONTROLREPORT

            CREATE procedure dbo.USP_REPORT_REVENUEBATCHCONTROLREPORT(@BATCHID uniqueidentifier) with execute as owner
            as

                declare @TABLENAME nvarchar(128);

                set nocount on;

                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 = 'REVENUECURRENCYID')
                    select @CURRENCYFIELD = 'REVENUECURRENCYID';
                else
                    set @CURRENCYFIELD = '''' + cast(coalesce(dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(), '00000000-0000-0000-0000-000000000000') as nvarchar(36)) + '''';


                declare @SQLTOEXEC nvarchar(max);

                set @SQLTOEXEC = N'set nocount on;
                    with TBL_CTE as
                    (
                    select
                        ' + @CURRENCYFIELD + ' as REVENUECURRENCYID,
                        ID,
                        CONSTITUENTNAME,
                        KEYNAME,
                        LOOKUPID,
                        AMOUNT,
                        TYPE,
                        DATE,
                        PAYMENTMETHOD,
                        DESIGNATION,
                        APPEAL,
                        ISPAYMENT,
                        ISPLEDGE,
                        ISRECURRINGGIFT,
                        PAYMENTAMOUNT,
                        PLEDGEAMOUNT,
                        RECURRINGGIFTAMOUNT,
                        MGAMOUNT,
                        MGCOUNT,
                        MEMBERSHIPAMOUNT,
                        MEMBERSHIPCOUNT,
                        REVENUELOOKUPID,
                        BATCHRECORDID
                    from ' + @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.CONSTITUENTNAME,
                        VIEWDATA.KEYNAME,
                        VIEWDATA.LOOKUPID,
                        VIEWDATA.AMOUNT,
                        VIEWDATA.TYPE,
                        VIEWDATA.DATE,
                        VIEWDATA.PAYMENTMETHOD,
                        VIEWDATA.DESIGNATION,
                        VIEWDATA.APPEAL,
                        VIEWDATA.ISPAYMENT,
                        VIEWDATA.ISPLEDGE,
                        VIEWDATA.ISRECURRINGGIFT,
                        VIEWDATA.PAYMENTAMOUNT,
                        VIEWDATA.PLEDGEAMOUNT,
                        VIEWDATA.RECURRINGGIFTAMOUNT,
                        VIEWDATA.MGAMOUNT,
                        VIEWDATA.MGCOUNT,
                        VIEWDATA.MEMBERSHIPAMOUNT,
                        VIEWDATA.MEMBERSHIPCOUNT,
                        CURRENCY.ISO4217,
                        CURRENCY.DECIMALDIGITS,
                        CURRENCY.CURRENCYSYMBOL,
                        CURRENCY.SYMBOLDISPLAYSETTINGCODE,
                        VIEWDATA.REVENUELOOKUPID
                    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
                        left join dbo.BATCHREVENUE on BATCHREVENUE.ID = VIEWDATA.BATCHRECORDID
                    order by BATCHREVENUE.SEQUENCE';

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