USP_BATCHMEMBERSHIPDUES_EXCEPTIONREPORT

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

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

    declare @SQLTOEXEC nvarchar(max) = N'set nocount on;            
        select
            NAME
            , LOOKUPID
            , DATE
            , TOTALAMOUNT
            , PAYMENTMETHOD
            , REVENUETYPE
            , REASON
            , ISO4217
            , DECIMALDIGITS
            , CURRENCYSYMBOL
            , SYMBOLDISPLAYSETTINGCODE
        from 
        (
            select 
                BMD.NAME
                , BMD.LOOKUPID
                , BMD.DATE
                , BMD.TOTALAMOUNT
                , BMD.PAYMENTMETHOD
                , ''Payment'' as REVENUETYPE
                , BMD.REASON
                , BMD.ISO4217
                , BMD.DECIMALDIGITS
                , BMD.CURRENCYSYMBOL
                , BMD.SYMBOLDISPLAYSETTINGCODE
                , BMD.SEQUENCE
            from ' + @BATCHTABLE + ' BMD
            where 
                BMD.REVENUETYPECODE <> 2 -- not pledge

            union all
            select 
                BMD.NAME
                , BMD.LOOKUPID
                , BMD.DATE
                , case BMD.PROGRAMTYPECODE
                    when 1 then BMD.TOTALAMOUNT
                    else BMD.MEMBERSHIPPLEDGEAMOUNT
                end as TOTALAMOUNT
                , BMD.PAYMENTMETHOD 
                , case BMD.PROGRAMTYPECODE
                    when 1 then ''Recurring Gift''
                    else ''Installment Plan'' 
                end as REVENUETYPE
                , BMD.REASON
                , BMD.ISO4217
                , BMD.DECIMALDIGITS
                , BMD.CURRENCYSYMBOL
                , BMD.SYMBOLDISPLAYSETTINGCODE
                , BMD.SEQUENCE
            from ' + @BATCHTABLE + ' BMD
            where 
                (BMD.REVENUETYPECODE <> 0 or BMD.PROGRAMTYPECODE = 1) -- not pay in full or is a recurring program

                and BMD.MEMBERSHIPTRANSACTIONTYPECODE <> 2 -- not pay membership

        ) S
        order by S.SEQUENCE';

    exec sp_executesql @SQLTOEXEC;    

end;