USP_MEMBERSHIP_ADDFROMSALE

            CREATE procedure dbo.USP_MEMBERSHIP_ADDFROMSALE
            (
                @CONSTITUENTID uniqueidentifier,
                @TRANSACTIONDATE datetime,
                @MEMBERSHIPID uniqueidentifier output,
                @MEMBERSHIPLEVELID uniqueidentifier,
                @MEMBERSHIPLEVELTERMID uniqueidentifier,
                @MEMBERSHIPTYPECODEID uniqueidentifier,
                @NUMBEROFCHILDREN tinyint,
                @COMMENTS nvarchar(1000),
                @ISGIFT bit,
                @SENDRENEWALCODE tinyint,
                @EXPIRATIONDATE datetime,
                @MEMBERS xml,
                @GIVENBYID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @REVENUESPLITID uniqueidentifier = null,
                @TRANSACTIONID uniqueidentifier = null output
            )
            as
                set nocount on;

                if @CHANGEAGENTID is null  
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                if @MEMBERSHIPID = '00000000-0000-0000-0000-000000000001' 
                    set @MEMBERSHIPID = newid();

                if @MEMBERS is not null
                begin
                    declare @NEWMEMBERS xml;
                    declare @NEWCONSTITID uniqueidentifier;
                    declare @EXCEPTIONID uniqueidentifier;

                    set @EXCEPTIONID = '00000000-0000-0000-0000-000000000000';
                    declare @HASEXCEPT bit;
                    set @HASEXCEPT = 0;

                    select @HASEXCEPT = 1 from 
                    @MEMBERS.nodes('/MEMBERS/ITEM') T(c)
                    where T.c.value('(ID)[1]','uniqueidentifier') = @EXCEPTIONID;

                    select @NEWCONSTITID = T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') 
                    from @MEMBERS.nodes('/MEMBERS/ITEM') T(c)
                    where not exists (select ID from dbo.CONSTITUENT where ID = T.c.value('(CONSTITUENTID)[1]','uniqueidentifier'));

                    if @HASEXCEPT = 1 or @NEWCONSTITID is not null
                    begin
                        set @NEWMEMBERS = 
                        (
                            select 
                            case when T.c.value('(ID)[1]','uniqueidentifier') = @EXCEPTIONID then 
                                coalesce((select top 1 ID from dbo.MEMBER where CONSTITUENTID = T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') and MEMBERSHIPID = @MEMBERSHIPID), newid())
                                else T.c.value('(ID)[1]','uniqueidentifier') end AS ID,
                                case when T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') = @NEWCONSTITID then @CONSTITUENTID else T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') end AS CONSTITUENTID,
                                T.c.value('(ISPRIMARY)[1]','bit') AS ISPRIMARY,
                                T.c.query('./MEMBERSHIPCARDS/ITEM') AS MEMBERSHIPCARDS
                            from @MEMBERS.nodes('/MEMBERS/ITEM') T(c)
                            for xml raw('ITEM'), type, elements, root('MEMBERS'), binary base64
                        );

                        set @MEMBERS = @NEWMEMBERS;
                    end

                    if not exists
                    (
                        select 1
                        from @MEMBERS.nodes('/MEMBERS/ITEM') T(members)
                        where T.members.value('(CONSTITUENTID)[1]','uniqueidentifier') = @CONSTITUENTID
                    )
                    begin
                        if @GIVENBYID is null
                        begin
                            set @GIVENBYID = @CONSTITUENTID;
                            set @SENDRENEWALCODE = 2;
                            set @ISGIFT = 1;
                        end
                    end                    
                end

                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

                exec dbo.USP_MEMBERSHIP_ADD
                    @MEMBERSHIPID output,
                    @CHANGEAGENTID,
                    @CONSTITUENTID,
                    null,
                    @MEMBERSHIPLEVELID,
                    @MEMBERSHIPLEVELTERMID,
                    @MEMBERSHIPTYPECODEID,
                    0,
                    @TRANSACTIONDATE,
                    @NUMBEROFCHILDREN,
                    @COMMENTS,
                    @ISGIFT,
                    @SENDRENEWALCODE,
                    @EXPIRATIONDATE,
                    @MEMBERS,
                    @GIVENBYID,
                    @REVENUESPLITID,
                    @TRANSACTIONID output;

                return 0;