USP_DATALIST_MEMBERSHIPTRANSACTION

                CREATE procedure dbo.USP_DATALIST_MEMBERSHIPTRANSACTION
                (
                    @MEMBERSHIPID uniqueidentifier,
                    @CURRENCYCODE tinyint = 1
                )
                as
                    set nocount on;

                    declare @SELECTEDCURRENCYID uniqueidentifier;
                    declare @REVENUEISTRACKEDINANOTHERSYSTEM smallint = 1;

                    if coalesce(@CURRENCYCODE, 2) = 2
                    begin
                        set @SELECTEDCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                    end

                    select
                        MT.ID,
                        cast(MT.TRANSACTIONDATE as date) as TRANSACTIONDATE,
                        MT.ACTION,
                        case
                            when MT.ACTIONCODE not in (4,6) then
                                case
                                    when ML.OBTAINLEVELCODE = 1 then (select SUM(AMOUNT)
                                                                      from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
                                                                      where (ORIGINALMEMBERSHIPTRANSACTIONID = MT.ID))
                                    else MEMBERSHIPTRANSACTIONAMOUNT.AMOUNT + MEMBERSHIPTRANSACTIONAMOUNT.ADDONAMOUNT
                                end
                            else null
                        end as AMOUNT,
                        ML.NAME,
                        case MP.PROGRAMTYPECODE
                            when 0 then cast(TERM.TERMTIMELENGTH as nvarchar(5)) + ' ' + TERM.TERMLENGTH
                            when 1 then TERM.RECURRINGPAYMENTOPTION
                            when 2 then case when TERM.LIFETIMEPAYMENTOPTIONCODE = 0 then TERM.LIFETIMEPAYMENTOPTION
                                else cast((select count(ID) from dbo.INSTALLMENT where REVENUEID = FT.ID) as nvarchar(5)) + ' ' + RSC.FREQUENCY
                                end
                            end as TERM,
                        cast(MT.EXPIRATIONDATE as date) as EXPIRATIONDATE,
                        case FT.TYPECODE
                            when 5 then dbo.UFN_SALESORDER_GETPAYMENTMETHODLIST(SALESORDER.ID)
                            else REVENUEPAYMENTMETHOD.PAYMENTMETHOD
                        end as DESCRIPTION,
                        MT.ISGIFT,
                        GIVENBYID_NF.NAME GIVENBYID,
                        FTL.ID as REVENUESPLITID,
                        case @CURRENCYCODE
                            when 0 then FT.TRANSACTIONCURRENCYID
                            else
                            case MP.WHEREISREVENUETRACKEDCODE
                                        when @REVENUEISTRACKEDINANOTHERSYSTEM then TERM.BASECURRENCYID
                                        else coalesce(@SELECTEDCURRENCYID, isnull(R.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID))
                                end
                        end as SELECTEDCURRENCYID,
                        TIER.DESCRIPTION as TIER,
                        CONSTITUENTID_NF.NAME CONSTITUENT,
                        dbo.UFN_DESIGNATION_BUILDNAME(RS.DESIGNATIONID) as DESIGNATION,
                        dbo.UFN_APPEAL_GETNAME(R.APPEALID) as APPEAL,
                        FT.CONSTITUENTID,
                        FT.ID as REVENUEID,
                        case when FTL.ID is null
                            then (select SALESORDERID from dbo.SALESORDERITEMMEMBERSHIP inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIP.ID where SALESORDERITEMMEMBERSHIP.MEMBERSHIPTRANSACTIONID = MT.ID)
                        else
                            null
                        end as ZERODOLLARSALESORDERID,
                        MT.ACTIONCODE
                    from dbo.MEMBERSHIPTRANSACTION MT
                        inner join dbo.MEMBERSHIPLEVELTERM TERM on TERM.ID = MT.MEMBERSHIPLEVELTERMID
                        inner join dbo.MEMBERSHIPLEVEL ML on ML.ID = MT.MEMBERSHIPLEVELID 
                        inner join dbo.MEMBERSHIPPROGRAM MP on MP.ID = ML.MEMBERSHIPPROGRAMID
                        inner join dbo.MEMBERSHIP M on M.ID = MT.MEMBERSHIPID
                        left join dbo.FINANCIALTRANSACTIONLINEITEM FTL on MT.REVENUESPLITID = FTL.ID
                        left join dbo.REVENUESPLIT_EXT RS on RS.ID = FTL.ID
                        left join dbo.FINANCIALTRANSACTION FT on FTL.FINANCIALTRANSACTIONID = FT.ID
                        left join dbo.REVENUESCHEDULE RSC on FT.ID = RSC.ID
                        left join dbo.REVENUE_EXT R on R.ID = FT.ID
                        left join dbo.PDACCOUNTSYSTEM on FT.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                        left join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                        left join dbo.TIERCODE TIER on ML.TIERCODEID = TIER.ID
                        left join dbo.REVENUEPAYMENTMETHOD on FT.ID = REVENUEPAYMENTMETHOD.REVENUEID
                        left join dbo.SALESORDER on SALESORDER.REVENUEID = FT.ID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(MT.DONORID) GIVENBYID_NF
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(FT.CONSTITUENTID) CONSTITUENTID_NF
                        outer apply dbo.UFN_MEMBERSHIPTRANSACTION_AMOUNT(MT.ID, @CURRENCYCODE) as MEMBERSHIPTRANSACTIONAMOUNT
                    where
                        MT.MEMBERSHIPID = @MEMBERSHIPID
                        and FT.DELETEDON is null
                        and FTL.DELETEDON is null
                        and coalesce(FTL.TYPECODE,0) <> 1
                    order by MT.TRANSACTIONDATE, MT.DATEADDED asc