UFN_SALESORDER_GETRECEIPTAMOUNT

CREATE function dbo.UFN_SALESORDER_GETRECEIPTAMOUNT
(
    @SALESORDERID uniqueidentifier
)
returns money
with execute as caller
as begin
    declare @RECEIPTAMOUNT money

    declare @RA table
    (
        RECEIPTAMOUNT money
    )

    insert into @RA (RECEIPTAMOUNT)
    select SALESORDERITEM.TOTAL
    from dbo.SALESORDERITEM
    where SALESORDERITEM.SALESORDERID = @SALESORDERID and TYPECODE = 2

    if exists (select * from dbo.SALESORDERITEM where SALESORDERID = @SALESORDERID and TYPECODE = 1)
        insert into @RA (RECEIPTAMOUNT)
        select
            case            
                when MEMBERSHIPPROGRAM.DEDUCTIBILITYCODE = 0 then
                    SALESORDERITEM.TOTAL - PROMOS.AMOUNT
               when MEMBERSHIPPROGRAM.DEDUCTIBILITYCODE = 1 then 
                    case
                        when MEMBERSHIPLEVEL.RECEIPTAMOUNT <= SALESORDERITEM.TOTAL then
                            MEMBERSHIPLEVEL.RECEIPTAMOUNT - PROMOS.AMOUNT
                        else
                            (SALESORDERITEM.TOTAL * (MEMBERSHIPLEVEL.RECEIPTAMOUNT / MEMBERSHIPLEVELTERM.AMOUNT) - PROMOS.AMOUNT) 
                    end
                else 0
            end
        from dbo.SALESORDERITEM
        inner join dbo.SALESORDERITEMMEMBERSHIP on SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIP.ID
        inner join dbo.MEMBERSHIPPROGRAM on SALESORDERITEMMEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
        inner join dbo.MEMBERSHIPLEVEL on SALESORDERITEMMEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
        inner join dbo.MEMBERSHIPLEVELTERM on SALESORDERITEMMEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
        outer apply (
            select isnull(sum(SALESORDERITEMMEMBERSHIPITEMPROMOTION.AMOUNT), 0) as AMOUNT
            from dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION
            where SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID = SALESORDERITEM.ID
        ) as PROMOS
        where SALESORDERITEM.SALESORDERID = @SALESORDERID and MEMBERSHIPPROGRAM.DEDUCTIBILITYCODE <> 2

    if exists (select * from dbo.SALESORDERITEM where SALESORDERID = @SALESORDERID and TYPECODE = 6)
        insert into @RA (RECEIPTAMOUNT)
        select isnull(sum(EVENTREGISTRANTPAYMENT.RECEIPTAMOUNT), 0)
        from dbo.SALESORDER
        inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = SALESORDER.REVENUEID
        inner join dbo.EVENTREGISTRANTPAYMENT on EVENTREGISTRANTPAYMENT.PAYMENTID = LI.ID
        where SALESORDER.ID = @SALESORDERID and LI.DELETEDON is null and LI.TYPECODE <> 1;  -- Reversal


    select @RECEIPTAMOUNT = 
        sum( 
            (case 
                when RECEIPTAMOUNT < 0 then 0 
                else RECEIPTAMOUNT 
            end)
        ) 
        from @RA

    return isnull(@RECEIPTAMOUNT, 0)

end