USP_DATAFORMTEMPLATE_ADD_BATCHMEMBERSHIPBATCHCOMMIT

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_BATCHMEMBERSHIPBATCHCOMMIT
                (
                    @ID uniqueidentifier = null output,
                    @VALIDATEONLY bit = 0,
                    @CHANGEAGENTID uniqueidentifier,
                    @MEMBERSHIPPROGRAMID uniqueidentifier,
                    @COMMENTS nvarchar(1000) = '',
                    @TRANSACTIONS xml = null,
                    @MEMBERS xml = null,
                    @STATUSCODE int = 0,
                    @SENDRENEWALCODE int = 1
                ) 
                as
                    set nocount on;

                    declare @CURRENTDATE datetime = getdate();

                    if @ID is null
                        set @ID = newid();

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

                    begin try
                        -- Assert that transactions exist for an ACTIVE membership and that only one exists for a pending membership
                        declare @NUMTRANSACTIONS as integer;
                        select
                            @NUMTRANSACTIONS = count([TRANSACTION].[ITEM].query('.'))
                        from
                            @TRANSACTIONS.nodes('TRANSACTIONS/ITEM') as [TRANSACTION]([ITEM])

                        if @STATUSCODE = 2 and @NUMTRANSACTIONS <> 1
                            raiserror('ERR_ONETRANSACTIONREQUIRED : One transaction is required for a pending membership', 13, 1);

                        -- Check to make sure a valid MEMBERSHIPPROGRAMID was specified (this does NOT happen as expected in the membership add SP)
                        if not exists(
                            select 1 
                            from dbo.[MEMBERSHIPPROGRAM] 
                            where [MEMBERSHIPPROGRAM].[ID] = @MEMBERSHIPPROGRAMID
                        )
                        raiserror('ERR_INVALIDMEMBERSHIPPROGRAM : Please enter a valid ''Program''.', 13, 1);

                        -- Check that the @TRANSACTIONS MEMBERSHIPLEVELID field is set
                        if exists(
                                select 1 
                                from @TRANSACTIONS.nodes('TRANSACTIONS/ITEM') as [TRANSACTION]([ITEM])
                                where
                                    [TRANSACTION].[ITEM].value('MEMBERSHIPLEVELID[1]', 'uniqueidentifier') is null
                and [TRANSACTION].[ITEM].value('CANCELMEMBERSHIP[1]', 'bit') = 0
                            )
                            raiserror('ERR_MEMBERSHIPLEVELIDREQUIRED', 13, 1);

                        -- Check that the @TRANSACTIONS MEMBERSHIPLEVELTERMID field is set
                        if exists(
                                select 1 
                                from @TRANSACTIONS.nodes('TRANSACTIONS/ITEM') as [TRANSACTION]([ITEM])
                                where
                                    [TRANSACTION].[ITEM].value('MEMBERSHIPLEVELTERMID[1]', 'uniqueidentifier') is null
                  and [TRANSACTION].[ITEM].value('CANCELMEMBERSHIP[1]', 'bit') = 0
                            )
                            raiserror('ERR_MEMBERSHIPLEVELTERMIDREQUIRED', 13, 1);

                        if @VALIDATEONLY = 0
                            exec dbo.[USP_BATCHMEMBERSHIPROW_COMMIT] 
                                @ID output, 
                                @MEMBERSHIPPROGRAMID, 
                                @COMMENTS, 
                                @TRANSACTIONS, 
                                @MEMBERS, 
                                @CHANGEAGENTID, 
                                @STATUSCODE, 
                                @SENDRENEWALCODE;
                        else
                        begin
                            /*    AAW 9/23/09: 
                                Checks that are in USP_MEMBERSHIP_ADD but not in the following validation steps:
                                -    Number of membership cards (in batch, only one membership card is created per member).
                                -    Check to make sure the current constituent is associated with the membership
                                    (constituents are not required to add a membership in batch). 
                            */

                            -- Assert that there are some members in the membership:
                            if @MEMBERS is null
                                raiserror('ERR_NOMEMBERS : Please enter at least one member.', 13, 1);

                            -- Assert that there's at least one primary member:
                            if not exists(
                                select 1 from @MEMBERS.nodes('MEMBERS/ITEM') as [MEMBERS]([ITEM])
                                where [MEMBERS].[ITEM].value('ISPRIMARY[1]', 'bit') = 1
                            )
                            raiserror('ERR_NOPRIMARYMEMBER : Please select one member as a primary member.', 13, 1);

                            -- Assert that there's at most one primary member:
                            if 1 < (
                                select count(*) from @MEMBERS.nodes('MEMBERS/ITEM') as [MEMBERS]([ITEM])
                                where [MEMBERS].[ITEM].value('ISPRIMARY[1]', 'bit') = 1
                            )
                            raiserror('ERR_TOOMANYPRIMARYMEMBERS : Please select only one member as a primary member.', 13, 1);

                            -- Assert that a constituent doesn't show up as a member more than once in the membership
                            if exists(
                                select 1
                                from
                                (
                                    select [MEMBERS].[ITEM].value('CONSTITUENTID[1]', 'uniqueidentifier') as [CONSTITUENTID]
                                    from @MEMBERS.nodes('/MEMBERS/ITEM') as [MEMBERS]([ITEM])
                                ) as [MEMBERSCOUNT]
                                group by [MEMBERSCOUNT].[CONSTITUENTID]
                                having count([MEMBERSCOUNT].[CONSTITUENTID]) > 1
                            )
                            raiserror('BBERR_DUPLICATEMEMBER : Please do not add a constituent more than once to the membership.', 13, 1);

                            -- Assert that if the membership program does not allow multiple memberships from the same constituent that
                            -- validation will fail if a constituent is added to the new membership that already has an existing membership with the membership program.
                            declare @ALLOWMULTIPLEMEMBERSHIPS bit = 0;
                            select 
                                @ALLOWMULTIPLEMEMBERSHIPS = [MEMBERSHIPPROGRAM].[ALLOWMULTIPLEMEMBERSHIPS]
                            from
                                dbo.[MEMBERSHIPPROGRAM]
                            where [MEMBERSHIPPROGRAM].[ID] = @MEMBERSHIPPROGRAMID;

                            if @ALLOWMULTIPLEMEMBERSHIPS = 0
                                if exists(
                                    select 1
                                    from
                                        dbo.[MEMBER] inner join dbo.[MEMBERSHIP] on [MEMBERSHIP].[ID] = [MEMBER].[MEMBERSHIPID]
                                    where
                                        [MEMBERSHIP].[MEMBERSHIPPROGRAMID] = @MEMBERSHIPPROGRAMID and
                                        [MEMBERSHIP].[STATUSCODE] <> 1 and
                                        [MEMBER].[ISDROPPED] <> 1 and
                                        [MEMBER].[CONSTITUENTID] in (
                                            select [MEMBERS].[ITEM].value('CONSTITUENTID[1]', 'uniqueidentifier')
                                            from @MEMBERS.nodes('/MEMBERS/ITEM') as [MEMBERS]([ITEM])
                                        )
                                )
                                raiserror('ERR_MULTIPLEMEMBERSHIPS : This membership program does not allow multiple memberships from the same constituent.', 13, 1);

                            -- Check the expiration date:
                            if exists(
                                select 1 from @TRANSACTIONS.nodes('TRANSACTIONS/ITEM') as [TRANSACTION]([ITEM])
                                where [TRANSACTION].[ITEM].value('EXPIRATIONDATE[1]', 'datetime') < [TRANSACTION].[ITEM].value('TRANSACTIONDATE[1]', 'datetime')
                            )
                            raiserror('ERR_TRANSACTIONEXPIRATIONDATE_INVALID : The expiration date of a transaction must be after the transaction date.', 13, 1);

                            -- Check to make sure the last transaction's membership level supports the number of members entered for this row:
                            if exists(
                                select 1 
                                from @TRANSACTIONS.nodes('TRANSACTIONS/ITEM') as [TRANSACTION]([ITEM])
                                    inner join dbo.[MEMBERSHIPLEVEL] on [MEMBERSHIPLEVEL].[ID] = [TRANSACTION].[ITEM].value('MEMBERSHIPLEVELID[1]', 'uniqueidentifier')
                                where
                                    -- This is the last transaction for this membership
                                    [TRANSACTION].[ITEM].value('SEQUENCE[1]', 'integer') =
                                    (
                                        select max([SUBTRANSACTION].[ITEM].value('SEQUENCE[1]', 'integer'))
                                        from @TRANSACTIONS.nodes('TRANSACTIONS/ITEM') as [SUBTRANSACTION]([ITEM])
                                    )
                                    and
                                    (
                                        select count([MEMBERS].[ITEM].value('CONSTITUENTID[1]', 'uniqueidentifier'))
                                        from @MEMBERS.nodes('MEMBERS/ITEM') as [MEMBERS]([ITEM])
                                    ) > [MEMBERSHIPLEVEL].[MEMBERSALLOWED]
                            )
                            raiserror('ERR_TOOMANYMEMBERS : The number of members on the membership must be less than or equal to the number of members allowed by the last transaction''s membership level.', 13, 1);                                

                            -- Assert that the number of children specified for each transaction is less than or equal to the number of children allowed for the membership level
                            -- specified in the transaction:
                            if exists(
                                select 1 
                                from @TRANSACTIONS.nodes('TRANSACTIONS/ITEM') as [TRANSACTION]([ITEM])
                                    inner join dbo.[MEMBERSHIPLEVEL] on [MEMBERSHIPLEVEL].[ID] = [TRANSACTION].[ITEM].value('MEMBERSHIPLEVELID[1]', 'uniqueidentifier')
                                where
                                    [MEMBERSHIPLEVEL].[CHILDRENALLOWED] > 0 and
                                    [TRANSACTION].[ITEM].value('NUMBEROFCHILDREN[1]', 'integer') > [MEMBERSHIPLEVEL].[CHILDRENALLOWED]
                            )
                            raiserror('ERR_TOOMANYCHILDREN : The number of children specified for a transaction must be less than or equal to the number of children allowed for a transaction''s membership level.', 13, 1);
                        end
                    end try
                    begin catch
                        exec dbo.[USP_RAISE_ERROR];
                        return 1;
                    end catch
                return 0;