UFN_SALESORDERITEMMEMBERSHIP_CALCULATEFINALEXPIRATIONDATE

CREATE function dbo.UFN_SALESORDERITEMMEMBERSHIP_CALCULATEFINALEXPIRATIONDATE
(
    @SALESORDERITEMMEMBERSHIPID uniqueidentifier,
    @TRANSACTIONDATE datetime
)
returns datetime
with execute as caller
as begin
    declare @EXPIRATIONDATE datetime
    declare @MEMBERSHIPID uniqueidentifier
    declare @MEMBERSHIPLEVELID uniqueidentifier
    declare @MEMBERSHIPLEVELTERMID uniqueidentifier
    declare @SALESORDERID uniqueidentifier

    select 
        @EXPIRATIONDATE = EXPIRATIONDATE,
        @MEMBERSHIPID = MEMBERSHIPID,
        @MEMBERSHIPLEVELID = MEMBERSHIPLEVELID,
        @MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERMID
    from dbo.SALESORDERITEMMEMBERSHIP 
    where ID = @SALESORDERITEMMEMBERSHIPID

    if @EXPIRATIONDATE is null
    begin
        declare @ACTIONCODE tinyint;
        select @ACTIONCODE = dbo.UFN_MEMBERSHIPTRANSACTION_DETERMINEACTIONCODE(@MEMBERSHIPID, @MEMBERSHIPLEVELID, @TRANSACTIONDATE)

        if @ACTIONCODE = 0 or @ACTIONCODE = 5 --join/rejoin

        begin
            set @EXPIRATIONDATE = dbo.UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL(@MEMBERSHIPLEVELID, @MEMBERSHIPLEVELTERMID, @TRANSACTIONDATE);
        end
        else
        begin
            declare @CURRENTSTATUS tinyint;
            select @EXPIRATIONDATE = EXPIRATIONDATE, 
                @CURRENTSTATUS = STATUSCODE
            from dbo.MEMBERSHIP where ID = @MEMBERSHIPID;

            if @CURRENTSTATUS = 2 --pending membership

            begin                            
                set @EXPIRATIONDATE = dbo.UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL(@MEMBERSHIPLEVELID, @MEMBERSHIPLEVELTERMID, @TRANSACTIONDATE);
            end
            else
            begin
                set @EXPIRATIONDATE = dbo.UFN_MEMBERSHIP_CALCULATERENEWALEXPIRATION(@EXPIRATIONDATE, @MEMBERSHIPLEVELTERMID);
            end
        end
    end

    if exists (select top(1) 1 from dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION where SALESORDERITEMID = @SALESORDERITEMMEMBERSHIPID)
    begin
        declare @GOTOMONTHEND bit = 0;

        if not (month(@EXPIRATIONDATE) = month(dateadd(d, 1, @EXPIRATIONDATE)))
            set @GOTOMONTHEND = 1;

        select top(1)
            @EXPIRATIONDATE = case EXTENSIONCALCULATIONTYPECODE
                                  when 0 then dateadd(d, EXTENSIONVALUE, @EXPIRATIONDATE)
                                  when 1 then dateadd(m, EXTENSIONVALUE, @EXPIRATIONDATE)
                                  else @EXPIRATIONDATE
                              end
        from dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION
        where 
            SALESORDERITEMID = @SALESORDERITEMMEMBERSHIPID and
            EXTENSIONVALUE > 0

        if @GOTOMONTHEND = 1
            set @EXPIRATIONDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@EXPIRATIONDATE, 1);
    end

    return @EXPIRATIONDATE
end