USP_BATCHMEMBERSHIPDUES_VALIDATIONREPORT

CREATE procedure dbo.USP_BATCHMEMBERSHIPDUES_VALIDATIONREPORT
(
    @BATCHID uniqueidentifier
)
as
begin
    set nocount on;

    select
        NAME
        , LOOKUPID
        , DATE
        , TOTALAMOUNT
        , PAYMENTMETHOD
        , REVENUETYPE
        , PROGRAMNAME
        , LEVELNAME
        , ISO4217
        , DECIMALDIGITS
        , CURRENCYSYMBOL
        , SYMBOLDISPLAYSETTINGCODE
    from 
    (
        select 
            coalesce(CONSTITUENT_NF.NAME, BATCHREVENUECONSTITUENT.NAME) NAME
            , coalesce(c.LOOKUPID, '') as LOOKUPID
            , BMD.DATE
            , BMD.TOTALAMOUNT
            , BMD.PAYMENTMETHOD
            , 'Payment' as REVENUETYPE
            , MP.NAME PROGRAMNAME
            , ML.NAME LEVELNAME
            , CURRENCY.ISO4217
            , CURRENCY.DECIMALDIGITS
            , CURRENCY.CURRENCYSYMBOL
            , CURRENCY.SYMBOLDISPLAYSETTINGCODE
            , BMD.SEQUENCE
        from dbo.BATCHMEMBERSHIPDUES BMD
            inner join dbo.MEMBERSHIPPROGRAM MP on MP.ID = BMD.MEMBERSHIPPROGRAMID
            inner join dbo.MEMBERSHIPLEVEL ML on ML.ID = BMD.MEMBERSHIPLEVELID
            left join dbo.CONSTITUENT C on C.ID = BMD.BILLTOCONSTITUENTID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(c.ID) CONSTITUENT_NF
            left join dbo.BATCHREVENUECONSTITUENT on BATCHREVENUECONSTITUENT.ID = BMD.BILLTOCONSTITUENTID
            inner join dbo.CURRENCY on CURRENCY.ID = BMD.TRANSACTIONCURRENCYID
        where 
            BMD.REVENUETYPECODE <> 2 -- not pledge only

            and BMD.BATCHID = @BATCHID
        union all
        select 
            coalesce(CONSTITUENT_NF.NAME, BATCHREVENUECONSTITUENT.NAME) NAME
            , coalesce(c.LOOKUPID, '') as LOOKUPID
            , BMD.DATE
            , case MP.PROGRAMTYPECODE
                when 1 then BMD.TOTALAMOUNT
                else BMD.MEMBERSHIPPLEDGEAMOUNT
            end as TOTALAMOUNT
            , BMD.PAYMENTMETHOD 
            , case MP.PROGRAMTYPECODE
                when 1 then 'Recurring Gift'
                else 'Installment Plan' 
            end as REVENUETYPE
            , MP.NAME PROGRAMNAME
            , ML.NAME LEVELNAME
            , CURRENCY.ISO4217
            , CURRENCY.DECIMALDIGITS
            , CURRENCY.CURRENCYSYMBOL
            , CURRENCY.SYMBOLDISPLAYSETTINGCODE
            , BMD.SEQUENCE
        from dbo.BATCHMEMBERSHIPDUES BMD
            inner join dbo.MEMBERSHIPPROGRAM MP on MP.ID = BMD.MEMBERSHIPPROGRAMID
            inner join dbo.MEMBERSHIPLEVEL ML on ML.ID = BMD.MEMBERSHIPLEVELID
            left join dbo.CONSTITUENT C on C.ID = BMD.BILLTOCONSTITUENTID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(c.ID) CONSTITUENT_NF
            left join dbo.BATCHREVENUECONSTITUENT on BATCHREVENUECONSTITUENT.ID = BMD.BILLTOCONSTITUENTID
            inner join dbo.CURRENCY on CURRENCY.ID = BMD.TRANSACTIONCURRENCYID
        where 
            (BMD.REVENUETYPECODE <> 0 or MP.PROGRAMTYPECODE = 1) -- not pay in full or is a recurring program

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

            and BMD.BATCHID = @BATCHID
    ) S
    order by S.SEQUENCE;
end;