UFN_SALESORDERITEMMEMBER_VALIDALLOWMULTIPLEMEMBERSHIPS

            CREATE function dbo.UFN_SALESORDERITEMMEMBER_VALIDALLOWMULTIPLEMEMBERSHIPS
            (
                @CONSTITUENTID uniqueidentifier,
                @SALESORDERITEMMEMBERSHIPID uniqueidentifier
            )
            returns bit
            as begin
                declare @ALLOWMULTIPLEMEMBERSHIPS bit;
                declare @MEMBERSHIPPROGRAMID uniqueidentifier;
                declare @MEMBERSHIPID uniqueidentifier;
                declare @SALESORDERSTATUSCODE tinyint;

                select
                    @ALLOWMULTIPLEMEMBERSHIPS = MEMBERSHIPPROGRAM.ALLOWMULTIPLEMEMBERSHIPS,
                    @MEMBERSHIPPROGRAMID = SALESORDERITEMMEMBERSHIP.MEMBERSHIPPROGRAMID,
                    @MEMBERSHIPID = SALESORDERITEMMEMBERSHIP.MEMBERSHIPID,
                    @SALESORDERSTATUSCODE = SALESORDER.STATUSCODE
                from
                    dbo.MEMBERSHIPPROGRAM
                inner join
                    dbo.SALESORDERITEMMEMBERSHIP on SALESORDERITEMMEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                inner join
                    dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIP.ID
                inner join
                    dbo.SALESORDER on SALESORDER.ID = SALESORDERITEM.SALESORDERID
                where
                    SALESORDERITEMMEMBERSHIP.ID = @SALESORDERITEMMEMBERSHIPID;

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

                --   constituent is already associated with the program.

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

                return 1;
            end