USP_REPORT_REVENUEBATCHCONTROLREPORT_CURRENCYSUMMARY

            CREATE procedure dbo.USP_REPORT_REVENUEBATCHCONTROLREPORT_CURRENCYSUMMARY
            (
                @BATCHID uniqueidentifier
            )
            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 = '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;

                    declare @MGREVENUE table (AMOUNT money, CURRENCYID uniqueidentifier);
                    declare @MEMBERSHIPREVENUE table (AMOUNT money, CURRENCYID uniqueidentifier);

                ';

                --If MGDETAIL column exists then add data.

                if exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TABLENAME and COLUMN_NAME = 'MGDEATIL')
                    set @SQLTOEXEC = @SQLTOEXEC + '
                    insert into @MGREVENUE(
                        AMOUNT, CURRENCYID
                    )
                    select
                        T.c.value(''(TRANSACTIONAMOUNT)[1]'',''money'') AS ''AMOUNT'',
                        T.c.value(''(TRANSACTIONCURRENCYID)[1]'',''uniqueidentifier'') AS ''CURRENCYID''
                    from ' + @TABLENAME + '
                    outer apply MGDETAIL.nodes(''/ITEM'') T(c)
                    where MGDETAIL is not null;
                ';                

                --If MEMBERSHIPDETAIL column exists then add data.

                if exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TABLENAME and COLUMN_NAME = 'MEMBERSHIPDETAIL')
                    set @SQLTOEXEC = @SQLTOEXEC + '
                    insert into @MEMBERSHIPREVENUE(
                        AMOUNT, CURRENCYID
                    )
                    select
                        T.c.value(''(TRANSACTIONAMOUNT)[1]'',''money'') AS ''AMOUNT'',
                        T.c.value(''(TRANSACTIONCURRENCYID)[1]'',''uniqueidentifier'') AS ''CURRENCYID''
                    from ' + @TABLENAME + '
                    outer apply MEMBERSHIPDETAIL.nodes(''/ITEM'') T(c)
                    where MEMBERSHIPDETAIL is not null;
                ';


                set @SQLTOEXEC = @SQLTOEXEC + '
                    with TBL_CTE as
                    (
                    select
                        ' + @CURRENCYFIELD + ' as REVENUECURRENCYID,
                        ISPAYMENT,
                        PAYMENTAMOUNT,
                        ISPLEDGE,
                        PLEDGEAMOUNT,
                        ISRECURRINGGIFT,
                        RECURRINGGIFTAMOUNT
                    from ' + @TABLENAME + '
                    )
                    select
                        VIEWDATA.REVENUECURRENCYID,
                        CURRENCY.NAME,
                        sum(VIEWDATA.ISPAYMENT) as [TOTALPAYMENTCOUNT],
                        sum(VIEWDATA.PAYMENTAMOUNT) as [TOTALPAYMENTAMOUNT],
                        sum(VIEWDATA.ISPLEDGE) as [TOTALPLEDGECOUNT],
                        sum(VIEWDATA.PLEDGEAMOUNT) as [TOTALPLEDGEAMOUNT],
                        sum(VIEWDATA.ISRECURRINGGIFT) as [TOTALRECURRINGGIFTCOUNT],
                        sum(VIEWDATA.RECURRINGGIFTAMOUNT) as [TOTALRECURRINGGIFTAMOUNT],
                        (select count(1) from @MGREVENUE MGREVENUE where MGREVENUE.CURRENCYID = VIEWDATA.REVENUECURRENCYID) as TOTALMGCOUNT,
                        (select sum(AMOUNT) from @MGREVENUE MGREVENUE where MGREVENUE.CURRENCYID = VIEWDATA.REVENUECURRENCYID) as TOTALMGAMOUNT,
                        (select count(1) from @MEMBERSHIPREVENUE MEMBERSHIPREVENUE where MEMBERSHIPREVENUE.CURRENCYID = VIEWDATA.REVENUECURRENCYID) as TOTALMEMBERSHIPCOUNT,
                        (select sum(AMOUNT) from @MEMBERSHIPREVENUE MEMBERSHIPREVENUE where MEMBERSHIPREVENUE.CURRENCYID = VIEWDATA.REVENUECURRENCYID) as TOTALMEMBERSHIPAMOUNT,
                        CURRENCY.ISO4217,
                        CURRENCY.DECIMALDIGITS,
                        CURRENCY.CURRENCYSYMBOL,
                        CURRENCY.SYMBOLDISPLAYSETTINGCODE
                    from TBL_CTE VIEWDATA
                        inner join dbo.CURRENCY on CURRENCY.ID = VIEWDATA.REVENUECURRENCYID
                    group by VIEWDATA.REVENUECURRENCYID,CURRENCY.NAME,CURRENCY.ISO4217,CURRENCY.DECIMALDIGITS,CURRENCY.CURRENCYSYMBOL,CURRENCY.SYMBOLDISPLAYSETTINGCODE
                    order by CURRENCY.NAME;';

                exec sp_executesql @SQLTOEXEC;