UFN_MEMBERSHIPTRANSACTION_AMOUNT

CREATE function dbo.UFN_MEMBERSHIPTRANSACTION_AMOUNT (
    @MEMBERSHIPTRANSACTIONID uniqueidentifier,
    @CURRENCYCODE tinyint
)
returns table
as return (
    with LINEITEM_CTE as (
        select
            ID,
            case @CURRENCYCODE
                when 0 then TRANSACTIONAMOUNT
                when 1 then BASEAMOUNT
                else ORGAMOUNT
            end as AMOUNT
        from
            dbo.FINANCIALTRANSACTIONLINEITEM
    ),
    CONTRIBUTIONAMOUNT_CTE as (
        select
            MEMBERSHIPCONTRIBUTIONPORTION.MEMBERSHIPTRANSACTIONID,
            sum(LINEITEM_CTE.AMOUNT) as AMOUNT
        from
            dbo.MEMBERSHIPCONTRIBUTIONPORTION
        inner join
            LINEITEM_CTE on LINEITEM_CTE.ID = MEMBERSHIPCONTRIBUTIONPORTION.FINANCIALTRANSACTIONLINEITEMID
        group by
            MEMBERSHIPCONTRIBUTIONPORTION.MEMBERSHIPTRANSACTIONID
    ),
    MEMBERSHIP_ADDONS_CTE as (
        select
            MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID,
            sum(MEMBERSHIPADDON.TRANSACTIONPURCHASEPRICE * (MEMBERSHIPADDON.QUANTITY)) as AMOUNT
        from
            dbo.MEMBERSHIPADDON
        inner join
            LINEITEM_CTE on LINEITEM_CTE.ID = MEMBERSHIPADDON.REVENUESPLITID
        group by
            MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID
    ),
    PROGRAM_CTE as (
        select MEMBERSHIPTRANSACTIONID = MEMBERSHIPTRANSACTION.ID, WHEREISREVENUETRACKEDCODE
            from dbo.MEMBERSHIPTRANSACTION
            inner join
                dbo.MEMBERSHIPLEVEL ON dbo.MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
            inner join
                dbo.MEMBERSHIPPROGRAM ON MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
        where MEMBERSHIPTRANSACTION.ID = @MEMBERSHIPTRANSACTIONID
    )
    select
        case PROGRAM_CTE.WHEREISREVENUETRACKEDCODE
        when 0 then
            isnull(LINEITEM_CTE.AMOUNT - dbo.UFN_MEMBERSHIP_GETBASEAMOUNTDISCOUNT(MEMBERSHIPTRANSACTION.ID) + isnull(CONTRIBUTIONAMOUNT_CTE.AMOUNT, 0), 0)
        when 1 then
            MEMBERSHIPTRANSACTION.BASEAMOUNT
        end as AMOUNT,
        isnull(MEMBERSHIP_ADDONS_CTE.AMOUNT, 0) as ADDONAMOUNT
    from
        dbo.MEMBERSHIPTRANSACTION
    inner join PROGRAM_CTE on MEMBERSHIPTRANSACTION.ID = PROGRAM_CTE.MEMBERSHIPTRANSACTIONID
    left outer join
        LINEITEM_CTE on LINEITEM_CTE.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
    left outer join
        CONTRIBUTIONAMOUNT_CTE on CONTRIBUTIONAMOUNT_CTE.MEMBERSHIPTRANSACTIONID = MEMBERSHIPTRANSACTION.ID
    left outer join
        MEMBERSHIP_ADDONS_CTE on MEMBERSHIP_ADDONS_CTE.MEMBERSHIPTRANSACTIONID = MEMBERSHIPTRANSACTION.ID
    where
        MEMBERSHIPTRANSACTION.ID = @MEMBERSHIPTRANSACTIONID);