USP_SALESORDER_HANDLEZEROCOSTMEMBERSHIPS

            CREATE procedure dbo.USP_SALESORDER_HANDLEZEROCOSTMEMBERSHIPS
            (
                @SALESORDERID uniqueidentifier,
                @TRANSACTIONDATE datetime,
                @CONSTITUENTID uniqueidentifier = null,
                @CHANGEAGENTID uniqueidentifier = null,
                @CURRENTDATE datetime = null
            )
            as
                set nocount on;

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

                if @CURRENTDATE is null
                    set @CURRENTDATE = getdate();

                if exists (select 1 from dbo.SALESORDERITEM 
                        where SALESORDERID = @SALESORDERID and 
                            TYPECODE = 1 and 
                            TOTAL + (select isnull(sum(ADDON.TOTAL), 0) from dbo.SALESORDERITEM ADDON
                                        inner join dbo.SALESORDERITEMMEMBERSHIPADDON on ADDON.ID = SALESORDERITEMMEMBERSHIPADDON.ID
                                        where SALESORDERITEMMEMBERSHIPADDON.SALESORDERITEMMEMBERSHIPID = SALESORDERITEM.ID) = 0)
                begin

                    declare @SALESORDERITEMID uniqueidentifier;
                    declare @MEMBERSHIPID uniqueidentifier;
                    declare @MEMBERSHIPPROGRAMID uniqueidentifier;
                    declare @MEMBERSHIPLEVELID uniqueidentifier;
                    declare @MEMBERSHIPLEVELTERMID uniqueidentifier;
                    declare @MEMBERSHIPLEVELTYPECODEID uniqueidentifier;
                    declare @NUMBEROFCHILDREN tinyint;
                    declare @COMMENTS nvarchar(1000);
                    declare @ISGIFT bit;
                    declare @SENDRENEWALCODE tinyint;
                    declare @EXPIRATIONDATE datetime;
                    declare @MEMBERS xml;
                    declare @GIVENBYID uniqueidentifier;
                    declare @MEMBERSHIPCARDS xml;
                    declare @ADDONS xml;

                    if @CONSTITUENTID is null
                        select @CONSTITUENTID = CONSTITUENTID from dbo.SALESORDER where ID = @SALESORDERID;

                    declare MEMBERSHIPSCURSOR cursor local fast_forward for
                        select
                            SALESORDERITEM.ID,
                            MEMBERSHIPID, 
                            MEMBERSHIPPROGRAMID, 
                            MEMBERSHIPLEVELID, 
                            MEMBERSHIPLEVELTERMID, 
                            MEMBERSHIPLEVELTYPECODEID,
                            NUMBEROFCHILDREN, 
                            COMMENTS, 
                            ISGIFT, 
                            SENDRENEWALCODE, 
                            EXPIRATIONDATE, 
                            GIVENBYID, 

                            (select
                                SALESORDERITEMMEMBER.ID as SALESORDERITEMMEMBERID,
                                SALESORDERITEMMEMBER.MEMBERID,
                                SALESORDERITEMMEMBER.CONSTITUENTID,
                                SALESORDERITEMMEMBER.ISPRIMARY

                                from dbo.SALESORDERITEMMEMBER
                                where SALESORDERITEMMEMBER.SALESORDERITEMMEMBERSHIPID = SALESORDERITEMMEMBERSHIP.ID 
                                for xml raw('ITEM'),type,elements,root('MEMBERS'),BINARY BASE64
                            ) as MEMBERS,

                            (select 
                                    SALESORDERITEMMEMBERSHIPCARD.SALESORDERITEMMEMBERID,
                                    SALESORDERITEMMEMBERSHIPCARD.MEMBERSHIPCARDID,
                                    SALESORDERITEMMEMBERSHIPCARD.NAMEONCARD,
                                    SALESORDERITEMMEMBERSHIPCARD.EXPIRATIONDATE

                                    from dbo.SALESORDERITEMMEMBERSHIPCARD
                                        inner join dbo.SALESORDERITEMMEMBER on SALESORDERITEMMEMBERSHIPCARD.SALESORDERITEMMEMBERID = SALESORDERITEMMEMBER.ID
                                    where SALESORDERITEMMEMBER.SALESORDERITEMMEMBERSHIPID = SALESORDERITEMMEMBERSHIP.ID
                                    for xml raw('ITEM'),type,elements,root('MEMBERSHIPCARDS'),BINARY BASE64
                            ) as MEMBERSHIPCARDS,

                            (select
                                SALESORDERITEMMEMBERSHIPADDON.ADDONID,
                                SALESORDERITEMMEMBERSHIPADDON.ADDONTYPECODE,
                                ADDONITEM.PRICE,
                                convert(int, ADDONITEM.QUANTITY) as QUANTITY,
                                null as SPLITID

                                from dbo.SALESORDERITEMMEMBERSHIPADDON
                                    inner join dbo.SALESORDERITEM ADDONITEM on SALESORDERITEMMEMBERSHIPADDON.ID = ADDONITEM.ID
                                where SALESORDERITEMMEMBERSHIPADDON.SALESORDERITEMMEMBERSHIPID = SALESORDERITEMMEMBERSHIP.ID 
                                for xml raw('ITEM'),type,elements,root('ADDONS'),BINARY BASE64
                            ) as ADDONS

                            from dbo.SALESORDERITEMMEMBERSHIP
                                inner join dbo.SALESORDERITEM on SALESORDERITEMMEMBERSHIP.ID = SALESORDERITEM.ID
                            where SALESORDERITEM.SALESORDERID = @SALESORDERID and
                                    SALESORDERITEM.TOTAL + (select isnull(sum(ADDON.TOTAL), 0) from dbo.SALESORDERITEM ADDON
                                                inner join dbo.SALESORDERITEMMEMBERSHIPADDON on ADDON.ID = SALESORDERITEMMEMBERSHIPADDON.ID
                                                where SALESORDERITEMMEMBERSHIPADDON.SALESORDERITEMMEMBERSHIPID = SALESORDERITEM.ID) = 0

                    open MEMBERSHIPSCURSOR;

                    fetch next from MEMBERSHIPSCURSOR into
                        @SALESORDERITEMID,
                        @MEMBERSHIPID,
                        @MEMBERSHIPPROGRAMID,
                        @MEMBERSHIPLEVELID,
                        @MEMBERSHIPLEVELTERMID,
                        @MEMBERSHIPLEVELTYPECODEID,
                        @NUMBEROFCHILDREN,
                        @COMMENTS,
                        @ISGIFT,
                        @SENDRENEWALCODE,
                        @EXPIRATIONDATE,
                        @GIVENBYID,
                        @MEMBERS,
                        @MEMBERSHIPCARDS,
                        @ADDONS;

                    declare @MEMBERSHIPTRANSACTIONID uniqueidentifier = null
                    while @@fetch_status = 0
                    begin

                        exec dbo.USP_MEMBERSHIP_ADDMEMBERSHIPTRANSACTION
                            @MEMBERSHIPID output,
                            @CHANGEAGENTID,
                            @CONSTITUENTID,
                            @MEMBERSHIPPROGRAMID,
                            @MEMBERSHIPLEVELID,
                            @MEMBERSHIPLEVELTERMID,
                            @MEMBERSHIPLEVELTYPECODEID,
                            @TRANSACTIONDATE,
                            @EXPIRATIONDATE,
                            @NUMBEROFCHILDREN,
                            @COMMENTS,
                            @ISGIFT,
                            @GIVENBYID,
                            @SENDRENEWALCODE,
                            @MEMBERS,
                            @MEMBERSHIPCARDS,
                            @ADDONS,
                            null,
                            @MEMBERSHIPTRANSACTIONID output;

                        update dbo.SALESORDERITEMMEMBERSHIP set
                            MEMBERSHIPID = @MEMBERSHIPID,
                            MEMBERSHIPTRANSACTIONID = @MEMBERSHIPTRANSACTIONID,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where
                            ID = @SALESORDERITEMID
                            --and MEMBERSHIPID is null;


                        update dbo.SALESORDERITEMMEMBERSHIPADDON set
                            MEMBERSHIPID = @MEMBERSHIPID,
                            MEMBERSHIPTRANSACTIONID = @MEMBERSHIPTRANSACTIONID,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where SALESORDERITEMMEMBERSHIPADDON.SALESORDERITEMMEMBERSHIPID = @SALESORDERITEMID

                        update dbo.SALESORDERITEMMEMBERSHIPADDON set 
                            MEMBERSHIPADDONID = MEMBERSHIPADDON.ID,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        from dbo.SALESORDERITEMMEMBERSHIPADDON
                            inner join dbo.MEMBERSHIPADDON
                            on SALESORDERITEMMEMBERSHIPADDON.MEMBERSHIPID = MEMBERSHIPADDON.MEMBERSHIPID and
                                SALESORDERITEMMEMBERSHIPADDON.ADDONID = MEMBERSHIPADDON.ADDONID and
                                SALESORDERITEMMEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID = MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID
                        where SALESORDERITEMMEMBERSHIPADDON.SALESORDERITEMMEMBERSHIPID = @SALESORDERITEMID

                        fetch next from MEMBERSHIPSCURSOR into
                            @SALESORDERITEMID,
                            @MEMBERSHIPID,
                            @MEMBERSHIPPROGRAMID,
                            @MEMBERSHIPLEVELID,
                            @MEMBERSHIPLEVELTERMID,
                            @MEMBERSHIPLEVELTYPECODEID,
                            @NUMBEROFCHILDREN,
                            @COMMENTS,
                            @ISGIFT,
                            @SENDRENEWALCODE,
                            @EXPIRATIONDATE,
                            @GIVENBYID,
                            @MEMBERS,
                            @MEMBERSHIPCARDS,
                            @ADDONS;

                        set @MEMBERSHIPTRANSACTIONID = null

                    end

                    close MEMBERSHIPSCURSOR;
                    deallocate MEMBERSHIPSCURSOR;
                end

                return 0;