USP_BATCHMEMBERSHIPDUES_EXCEPTIONREPORT_CURRENCYSUMMARY

create procedure dbo.USP_BATCHMEMBERSHIPDUES_EXCEPTIONREPORT_CURRENCYSUMMARY
(
    @BATCHID uniqueidentifier
)
as
begin

    set nocount on;

    declare @BATCHTABLE nvarchar(128)
    set @BATCHTABLE = dbo.UFN_BATCH_GETREPORTTABLENAME(@BATCHID, 1);

    declare @SQLTOEXEC nvarchar(max) = N'set nocount on;
        select
            BMD.TRANSACTIONCURRENCYID
            , BMD.CURRENCYNAME
            , BMD.ISO4217
            , BMD.DECIMALDIGITS
            , BMD.CURRENCYSYMBOL
            , BMD.SYMBOLDISPLAYSETTINGCODE
            , coalesce((select sum(TOTALAMOUNT) from @BATCHMEMBERSHIPDUES BMD where BMD.TRANSACTIONCURRENCYID = CURRENCY.ID), 0) as CURRENTTOTALAMOUNT
            , coalesce((select count(ID) from @BATCHMEMBERSHIPDUES BMD where BMD.TRANSACTIONCURRENCYID = CURRENCY.ID), 0) as CURRENTNUMBEROFRECORDS

            -- multicurrency conversion should be done at the query that loaded the table.

            , coalesce((select SUM(ADDONAMOUNT) from @BATCHMEMBERSHIPDUES BMD where BMD.TRANSACTIONCURRENCYID = CURRENCY.ID), 0) as TOTALADDONAMOUNT 
            , coalesce((select SUM(DONATIONAMOUNT) from dbo.BATCHMEMBERSHIPDUES where BATCHID = @BATCHID), 0) as TOTALDONATIONAMOUNT
        from ' + @BATCHTABLE + ' BMD 
        group by 
            BMD.TRANSACTIONCURRENCYID
            , BMD.CURRENCYNAME
            , BMD.ISO4217
            , BMD.DECIMALDIGITS
            , BMD.CURRENCYSYMBOL
            , BMD.SYMBOLDISPLAYSETTINGCODE';

end