UFN_SALESORDERITEMMEMBERSHIP_VALIDALLOWMULTIPLEMEMBERSHIPS

Validates if a membership program is allowed to be added according to the allow multiple memberships bit associated with the membership.
Return

Return Type
bit
Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@SALESORDERITEMMEMBERSHIPID	uniqueidentifier	IN
@MEMBERSHIPPROGRAMID	uniqueidentifier	IN
Definition

Copy


            CREATE function dbo.UFN_SALESORDERITEMMEMBERSHIP_VALIDALLOWMULTIPLEMEMBERSHIPS
            (
                @CONSTITUENTID uniqueidentifier,
                @SALESORDERITEMMEMBERSHIPID uniqueidentifier,
                @MEMBERSHIPPROGRAMID uniqueidentifier
            )
            returns bit
            as begin
                declare @MEMBERSHIPID uniqueidentifier;

                select @MEMBERSHIPID = MEMBERSHIPID from dbo.SALESORDERITEMMEMBERSHIP where ID = @SALESORDERITEMMEMBERSHIPID;

                if @MEMBERSHIPID is null 
                begin
                    declare @ALLOWMULTIPLEMEMBERSHIPS bit;

                    select
                        @ALLOWMULTIPLEMEMBERSHIPS = ALLOWMULTIPLEMEMBERSHIPS
                    from
                        dbo.MEMBERSHIPPROGRAM
                    where
                        ID = @MEMBERSHIPPROGRAMID;

                    -- If the program does not allow multiple memberships... check and see if this

                    --   constituent is already associated with the program.

                    if @ALLOWMULTIPLEMEMBERSHIPS = 0
                        if exists
                        (
                            select
                                1
                            from
                                dbo.MEMBER
                            inner join
                                dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                            where
                                @CONSTITUENTID = MEMBER.CONSTITUENTID
                                and @MEMBERSHIPPROGRAMID = MEMBERSHIP.MEMBERSHIPPROGRAMID
                                and MEMBERSHIP.STATUSCODE = 0
                                and MEMBER.ISDROPPED <> 1
                                and MEMBER.ISCAREGIVER = 0
                        )
                        or exists
                        (
                            select
                                1
                            from
                                dbo.SALESORDERITEMMEMBER
                            inner join
                                dbo.SALESORDERITEMMEMBERSHIP on SALESORDERITEMMEMBERSHIP.ID = SALESORDERITEMMEMBER.SALESORDERITEMMEMBERSHIPID
                            left join 
                                dbo.MEMBER on MEMBER.CONSTITUENTID = SALESORDERITEMMEMBER.CONSTITUENTID and
                                            MEMBER.MEMBERSHIPID = SALESORDERITEMMEMBERSHIP.MEMBERSHIPID
                           left join dbo.MEMBERSHIP on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
                            where
                                @CONSTITUENTID = SALESORDERITEMMEMBER.CONSTITUENTID
                                and @MEMBERSHIPPROGRAMID = SALESORDERITEMMEMBERSHIP.MEMBERSHIPPROGRAMID
                                and @SALESORDERITEMMEMBERSHIPID <> SALESORDERITEMMEMBERSHIP.ID
                                and SALESORDERITEMMEMBER.ISCAREGIVER = 0
                                and (MEMBER.ISDROPPED is null or (MEMBER.ISDROPPED is not null and MEMBER.ISDROPPED = 0))
                                and MEMBERSHIP.STATUSCODE = 0
                        )
                            return 0;
                end

                return 1;
            end