USP_BATCHMEMBERSHIPDUES_CONTROLREPORT

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

    -- The batch should be outputting only Recurring Gift/Pledge Ids whenever there is no payment made.


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

    declare @BATCHNUMBER nvarchar(100);

    select @BATCHNUMBER = BATCH.BATCHNUMBER
    from dbo.BATCH
    where BATCH.ID = @BATCHID;

    declare @SQLTOEXEC nvarchar(max) = N'set nocount on;
        with FT_CTE as (
        select 
            FT.ID
            , S.MEMBERSHIPID
            , S.PAYMENTMETHOD
        from dbo.FINANCIALTRANSACTION FT
            inner join (
                -- get all the standalone payments/pledges/recurring gifts in the list

                select 
                    O.ID as ID
                    , O.MEMBERSHIPID
                    , PM.PAYMENTMETHOD
                from ' + @BATCHTABLE + ' O
                    inner join dbo.REVENUEPAYMENTMETHOD PM on o.ID = PM.REVENUEID
                union
                -- get Payments for Pledges/Recurring gifts if they exist

                select 
                    O.PARENTID as ID
                    , O.MEMBERSHIPID
                    , PM.PAYMENTMETHOD
                from ' + @BATCHTABLE + ' O
                    inner join dbo.REVENUEPAYMENTMETHOD PM on O.ID = PM.REVENUEID
                    inner join dbo.REVENUE_EXT on O.PARENTID = REVENUE_EXT.ID
                    where o.PARENTID is not null and REVENUE_EXT.BATCHNUMBER = @BATCHNUMBER
            ) S on FT.ID = S.ID
        )
        select 
            T.ID
            , CONSTITUENT_NF.NAME
            , C.LOOKUPID
            , case 
                when FT.TYPECODE = 15 then ''Installment Plan''
                else FT.TYPE end
            as TYPE
            , FT.DATE
            , T.PAYMENTMETHOD
            , MP.NAME as PROGRAMNAME
            , ML.NAME as LEVELNAME
            , FT.TRANSACTIONAMOUNT as TOTALAMOUNT
            , case 
                when FT.TYPECODE = 0 
                then FT.TRANSACTIONAMOUNT 
                else 0 
            end as PAYMENTAMOUNT
            , SUM(case when MPCR.ID is null and RSX.TYPECODE = 0 and RSX.APPLICATIONCODE = 0 then FTLI.TRANSACTIONAMOUNT else 0 end) as ADDITIONALDONATIONAMOUNT
            , SUM(case when RSX.TYPECODE = 18 and FT.TYPECODE = 0 then FTLI.TRANSACTIONAMOUNT else 0 end) as ADDONAMOUNT
            , CURRENCY.ISO4217
            , CURRENCY.DECIMALDIGITS
            , CURRENCY.CURRENCYSYMBOL
            , CURRENCY.SYMBOLDISPLAYSETTINGCODE
        from FT_CTE T
            inner join dbo.FINANCIALTRANSACTION FT on T.ID = FT.ID
            inner join dbo.CURRENCY on FT.TRANSACTIONCURRENCYID = CURRENCY.ID
            inner join dbo.CONSTITUENT C on FT.CONSTITUENTID = C.ID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) CONSTITUENT_NF
            inner join dbo.MEMBERSHIP M on T.MEMBERSHIPID = M.ID
            inner join dbo.MEMBERSHIPPROGRAM MP on M.MEMBERSHIPPROGRAMID = MP.ID
            inner join dbo.MEMBERSHIPLEVEL ML on M.MEMBERSHIPLEVELID = ML.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
        group by
            T.ID
            , CONSTITUENT_NF.NAME
            , C.LOOKUPID
            , FT.TYPE
            , FT.TYPECODE
            , FT.DATE
            , T.PAYMENTMETHOD
            , MP.NAME
            , ML.NAME
            , FT.TRANSACTIONAMOUNT
            , CURRENCY.ISO4217
            , CURRENCY.DECIMALDIGITS
            , CURRENCY.CURRENCYSYMBOL
            , CURRENCY.SYMBOLDISPLAYSETTINGCODE
            , T.MEMBERSHIPID
        order by T.MEMBERSHIPID';

    exec sp_executesql @SQLTOEXEC, N'@BATCHNUMBER nvarchar(100)', @BATCHNUMBER=@BATCHNUMBER;
end