UFN_SALESORDER_GETMEMBERSHIPTOTAL

        CREATE function dbo.UFN_SALESORDER_GETMEMBERSHIPTOTAL
        (
            @SALESORDERID uniqueidentifier
        )
        returns money
        as begin
            declare @MEMBERSHIPTOTAL money
            declare @MEMPROMOTOTAL money

            select 
                @MEMBERSHIPTOTAL = coalesce(
                    (
                        select
                            sum(coalesce([SALESORDERITEM].[TOTAL], 0))
                        from dbo.[SALESORDERITEM]
                        where
                            [SALESORDERITEM].[SALESORDERID] = @SALESORDERID and
                            [SALESORDERITEM].[TYPECODE] = 1
                    ), 0)

            -- Add membership add-ons

            select @MEMBERSHIPTOTAL = @MEMBERSHIPTOTAL + 
                            coalesce((
                                select sum(coalesce(ADDONITEMS.TOTAL, 0))
                                                from dbo.SALESORDERITEM ADDONITEMS
                                                inner join dbo.SALESORDERITEMMEMBERSHIPADDON SOIMA
                                                    on ADDONITEMS.ID = SOIMA.ID
                                                inner join dbo.SALESORDERITEM MEMBERSHIPITEMS
                                                    on SOIMA.SALESORDERITEMMEMBERSHIPID = MEMBERSHIPITEMS.ID
                                                where ADDONITEMS.TYPECODE = 16 and 
                                                    [MEMBERSHIPITEMS].[SALESORDERID] = @SALESORDERID and
                                                    [MEMBERSHIPITEMS].[TYPECODE] = 1
                            ), 0)
            -- Subtract membership promotions

            select
                @MEMPROMOTOTAL = coalesce(
                    (
                        select
                            sum(coalesce([SALESORDERITEMMEMBERSHIPITEMPROMOTION].[AMOUNT], 0)) 
                        from dbo.[SALESORDERITEM]
                        left join dbo.[SALESORDERITEMMEMBERSHIPITEMPROMOTION]
                            on [SALESORDERITEMMEMBERSHIPITEMPROMOTION].[SALESORDERITEMID] = [SALESORDERITEM].[ID]
                        where
                            [SALESORDERITEM].[SALESORDERID] = @SALESORDERID and
                            [SALESORDERITEM].[TYPECODE] = 1
                    ), 0)

            return @MEMBERSHIPTOTAL - @MEMPROMOTOTAL;
        end