USP_BATCHMEMBERSHIPDUES_EXCEPTIONREPORT_SUMMARY

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

    declare @MULTICURRENCYENABLED bit = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency');
    declare 
        @ORGANIZATIONISO4217 nvarchar(3),
        @ORGANIZATIONDECIMALDIGITS tinyint,
        @ORGANIZATIONCURRENCYSYMBOL nvarchar(5),
        @ORGANIZATIONSYMBOLDISPLAYSETTINGCODE tinyint;

    select
        @ORGANIZATIONISO4217 = ISO4217,
        @ORGANIZATIONDECIMALDIGITS = DECIMALDIGITS,
        @ORGANIZATIONCURRENCYSYMBOL  = CURRENCYSYMBOL,
        @ORGANIZATIONSYMBOLDISPLAYSETTINGCODE = SYMBOLDISPLAYSETTINGCODE
    from dbo.CURRENCY
    where ISORGANIZATIONCURRENCY = 1;

    declare @BATCHTABLE nvarchar(128)
    set @BATCHTABLE = dbo.UFN_BATCH_GETREPORTTABLENAME(@BATCHID, 1);
    declare @SQLTOEXEC nvarchar(max) = N'set nocount on;
        select top 1
            BATCHNUMBER
            , ORIGINALBATCHNUMBER
            , OWNER
            , coalesce((select count(ID) from ' + @BATCHTABLE + '),0) as [CURRENTNUMBEROFRECORDS]
            , coalesce((select sum(TOTALAMOUNT) from ' + @BATCHTABLE + '),0) as [CURRENTTOTALAMOUNT]
            , coalesce((select count(ID) from ' + @BATCHTABLE + ' where REVENUETYPECODE <> 2), 0) AS TOTALPAYMENTCOUNT
            , coalesce((select 
                            count(BMD.ID) 
                        from ' + @BATCHTABLE + ' BMD
                        where 
                            BMD.PROGRAMTYPECODE <> 1 
                            and BMD.MEMBERSHIPTRANSACTIONTYPECODE <> 2
                            and BMD.REVENUETYPECODE <> 0), 0) AS TOTALPLEDGECOUNT
            , coalesce((select 
                            count(BMD.ID) 
                        from ' + @BATCHTABLE + ' BMD
                        where 
                            BMD.PROGRAMTYPECODE = 1 
                            and BMD.MEMBERSHIPTRANSACTIONTYPECODE <> 2
                        ), 0) AS TOTALRECURRINGGIFTCOUNT
            , coalesce((select sum(ADDONAMOUNT) from ' + @BATCHTABLE + '), 0) as TOTALADDONAMOUNT
            , coalesce((select sum(DONATIONAMOUNT) from ' + @BATCHTABLE + '), 0) as TOTALDONATIONAMOUNT
            , @MULTICURRENCYENABLED MULTICURRENCYENABLED
            , @ORGANIZATIONISO4217 ORGANIZATIONISO4217
            , @ORGANIZATIONDECIMALDIGITS ORGANIZATIONDECIMALDIGITS
            , @ORGANIZATIONCURRENCYSYMBOL ORGANIZATIONCURRENCYSYMBOL
            , @ORGANIZATIONSYMBOLDISPLAYSETTINGCODE ORGANIZATIONSYMBOLDISPLAYSETTINGCODE
        from ' + @BATCHTABLE;

    exec sp_executesql @SQLTOEXEC, 
        N'@MULTICURRENCYENABLED bit, 
        @ORGANIZATIONISO4217 nvarchar(3), 
        @ORGANIZATIONDECIMALDIGITS tinyint,
        @ORGANIZATIONCURRENCYSYMBOL nvarchar(5),
        @ORGANIZATIONSYMBOLDISPLAYSETTINGCODE tinyint',
        @MULTICURRENCYENABLED = @MULTICURRENCYENABLED, 
        @ORGANIZATIONISO4217 = @ORGANIZATIONISO4217, 
        @ORGANIZATIONDECIMALDIGITS = @ORGANIZATIONDECIMALDIGITS,
        @ORGANIZATIONCURRENCYSYMBOL = @ORGANIZATIONCURRENCYSYMBOL,
        @ORGANIZATIONSYMBOLDISPLAYSETTINGCODE = @ORGANIZATIONSYMBOLDISPLAYSETTINGCODE 
        ;
end