USP_BATCHMEMBERSHIPDUES_CONTROLREPORT_CURRENCYSUMMARY

create procedure dbo.USP_BATCHMEMBERSHIPDUES_CONTROLREPORT_CURRENCYSUMMARY
(
    @BATCHID uniqueidentifier
)
with execute as owner
as
begin

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

    declare @SQLTOEXEC nvarchar(max) = N'set nocount on;
        select
            FT.TRANSACTIONCURRENCYID as CURRENCYID
            , C.CURRENCYNAME
            , C.ISO4217
            , C.DECIMALDIGITS
            , C.CURRENCYSYMBOL
            , C.SYMBOLDISPLAYSETTINGCODE
            , S.NUMBEROFROWS as CURRENTNUMBEROFRECORDS
            , SUM(case when MPCR.ID is null and RSX.TYPECODE = 0 then FTLI.TRANSACTIONAMOUNT else 0 end) as ADDITIONALDONATIONAMOUNT
            , SUM(case when RSX.TYPECODE = 18 then FTLI.TRANSACTIONAMOUNT else 0 end) as ADDONAMOUNT
            , SUM(FTLI.TRANSACTIONAMOUNT) as TOTALAMOUNT
        from ' + @BATCHTABLE + ' T
            inner join dbo.FINANCIALTRANSACTION FT on T.ID = FT.ID
            inner join dbo.CURRENCY C on FT.TRANSACTIONCURRENCYID = C.ID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on T.ID = FTLI.FINANCIALTRANSACTIONID
            inner join dbo.REVENUESPLIT_EXT RSX on FTLI.ID = RSX.ID
            left join dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE MPCR on MPCR.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
            inner join (
                select
                    FT.TRANSACTIONCURRENCYID as CURRENCYID
                    , COUNT(T.ID) as NUMBEROFROWS
                from ' + @BATCHTABLE + ' T
                    inner join dbo.FINANCIALTRANSACTION FT on T.ID = FT.ID
                group by FT.TRANSACTIONCURRENCYID
            ) S on S.CURRENCYID = C.ID
        where T.TYPECODE = 0 -- just look at payments for totals

        group by
            FT.TRANSACTIONCURRENCYID
            , C.ISO4217
            , C.DECIMALDIGITS
            , C.CURRENCYSYMBOL
            , C.SYMBOLDISPLAYSETTINGCODE
            , S.NUMBEROFROWS';

end;