UFN_MEMBERSHIPLEVELTERM_TERMALREADYEXISTS

CREATE function dbo.UFN_MEMBERSHIPLEVELTERM_TERMALREADYEXISTS
(
    @LEVELTERMID uniqueidentifier,
    @LEVELID uniqueidentifier,
    @TERMTIMELENGTH smallint,
    @TERMLENGTHCODE tinyint,
    @RECURRINGPAYMENTOPTIONCODE tinyint,
    @LIFETIMEPAYMENTOPTIONCODE tinyint,
    @LIFETIMENUMBEROFPAYMENTS smallint
)
returns bit
as begin
    declare @RESULTBIT bit = 0

    declare @PROGRAMTYPECODE tinyint

    select @PROGRAMTYPECODE = MP.PROGRAMTYPECODE from dbo.MEMBERSHIPLEVEL ML join dbo.MEMBERSHIPPROGRAM MP on ML.ID = @LEVELID and ML.MEMBERSHIPPROGRAMID = MP.ID

    -- We do not allow duplicate terms but in order to support client data that had duplicate terms we will grandfather those terms in.

    -- Only Annual Dues-based programs are eligible since they are the only types that existed before this release.

    -- Setting this date to the probable release date

    declare @GRANDFATHERDATE datetime
    set @GRANDFATHERDATE = dbo.UFN_DATE_GETLATESTTIME('05/10/2012')

    --Annual program

    if @PROGRAMTYPECODE = 0
    begin
        if  (
                select count(*)
                from dbo.[MEMBERSHIPLEVELTERM] LT
                where LT.ID != @LEVELTERMID
                  and LT.LEVELID = @LEVELID
                  and LT.TERMTIMELENGTH = @TERMTIMELENGTH
                  and LT.TERMLENGTHCODE = @TERMLENGTHCODE
                  and LT.DATEADDED > @GRANDFATHERDATE
            ) > 0
            set @RESULTBIT = 1
    end

    --Recurring/Sustaining program

    if @PROGRAMTYPECODE = 1
    begin
        if  (
                select count(*)
                from dbo.[MEMBERSHIPLEVELTERM] LT
                where LT.ID != @LEVELTERMID
                  and LT.LEVELID = @LEVELID
                  and LT.RECURRINGPAYMENTOPTIONCODE = @RECURRINGPAYMENTOPTIONCODE
            ) > 0
            set @RESULTBIT = 1
    end

    --Lifetime program

    if @PROGRAMTYPECODE = 2
    begin
        if  (
                select count(*)
                from dbo.[MEMBERSHIPLEVELTERM] LT
                where LT.ID != @LEVELTERMID
                  and LT.LEVELID = @LEVELID
                  and LT.LIFETIMEPAYMENTOPTIONCODE = @LIFETIMEPAYMENTOPTIONCODE
                  and LT.LIFETIMENUMBEROFPAYMENTS = @LIFETIMENUMBEROFPAYMENTS
            ) > 0
            set @RESULTBIT = 1
    end

    return @RESULTBIT

end