UFN_MEMBERSHIPTRANSACTIONSPLITS_BYPROGRAM

        CREATE function dbo.UFN_MEMBERSHIPTRANSACTIONSPLITS_BYPROGRAM
        (
            @PROGRAMID uniqueidentifier,
            @FROMDATE datetime,
            @TODATE datetime
        )
        returns table
        as return
        (
            select
                MT.REVENUESPLITID as REVENUESPLITID,
                ML.ID as LEVELID,
                ML.TIERCODEID as TIERCODEID,
                MT.ACTIONCODE as ACTIONCODE
            from dbo.MEMBERSHIPTRANSACTION MT
            inner join dbo.MEMBERSHIPLEVEL ML
                on ML.ID = MT.MEMBERSHIPLEVELID
            inner join dbo.MEMBERSHIPPROGRAM 
                on MEMBERSHIPPROGRAM.ID = ML.MEMBERSHIPPROGRAMID
            where 
                ML.MEMBERSHIPPROGRAMID = @PROGRAMID
                and MT.TRANSACTIONDATE between @FROMDATE and @TODATE
                and MEMBERSHIPPROGRAM.PROGRAMTYPECODE <> 1

            union all

            select
                RECURRINGMEMBERSHIPINSTALLMENTPAYMENT.ID as REVENUESPLITID,
                MEMBERSHIPLEVEL.ID as LEVELID,
                MEMBERSHIPLEVEL.TIERCODEID as TIERCODEID,
                MEMBERSHIPTRANSACTION.ACTIONCODE as ACTIONCODE
            from dbo.MEMBERSHIPTRANSACTION
            inner join dbo.MEMBERSHIPLEVEL
                on MEMBERSHIPLEVEL.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
            inner join  FINANCIALTRANSACTIONLINEITEM
                on FINANCIALTRANSACTIONLINEITEM.ID=MEMBERSHIPTRANSACTION.REVENUESPLITID
            inner join dbo.RECURRINGGIFTINSTALLMENT
                on RECURRINGGIFTINSTALLMENT.REVENUEID= FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
            inner join dbo.RECURRINGGIFTINSTALLMENTPAYMENT 
                on RECURRINGGIFTINSTALLMENT.ID = RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID
            inner join FINANCIALTRANSACTIONLINEITEM RECURRINGMEMBERSHIPINSTALLMENTPAYMENT
                on RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID =RECURRINGMEMBERSHIPINSTALLMENTPAYMENT.FINANCIALTRANSACTIONID
            inner join dbo.FINANCIALTRANSACTION
                on FINANCIALTRANSACTION.ID= FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
            inner join dbo.MEMBERSHIPPROGRAM
                on MEMBERSHIPPROGRAM.ID = MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID
            where 
                MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @PROGRAMID
                and FINANCIALTRANSACTION.DATE between @FROMDATE and @TODATE
                and MEMBERSHIPPROGRAM.PROGRAMTYPECODE = 1

            union all

            select
                MA.REVENUESPLITID as REVENUESPLITID,
                ML.ID as LEVELID,
                ML.TIERCODEID as TIERCODEID,
                MT.ACTIONCODE as ACTIONCODE
            from dbo.MEMBERSHIPTRANSACTION MT
            inner join dbo.MEMBERSHIPADDON MA
                on MA.MEMBERSHIPTRANSACTIONID = MT.ID
            inner join dbo.MEMBERSHIPLEVEL ML
                on ML.ID = MT.MEMBERSHIPLEVELID
            inner join dbo.MEMBERSHIPPROGRAM 
                on MEMBERSHIPPROGRAM.ID = ML.MEMBERSHIPPROGRAMID
            where 
                ML.MEMBERSHIPPROGRAMID = @PROGRAMID
                and MT.TRANSACTIONDATE between @FROMDATE and @TODATE
                and MEMBERSHIPPROGRAM.PROGRAMTYPECODE <> 1                
        )