USP_BATCHMEMBERSHIPDUES_DELETEBATCH

                CREATE procedure dbo.USP_BATCHMEMBERSHIPDUES_DELETEBATCH
                (
                    @ID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier
                )
                as begin
                    declare @CURRENTDATE datetime = getdate();
                    -- Cannot delete if there is an uncommitted batch that originated from this one,

                    -- as these records may be associated with that batch. These records will be deleted

                    -- when that batch is committed or deleted.

                    -- Example: an exception batch

                    if not exists (select ID from dbo.BATCH where ORIGINATINGBATCHID = @ID and STATUSCODE = 0)
                    begin
                        delete from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER where ID in
                        (
                            select BATCHREVENUECONSTITUENTGROUPMEMBER.ID
                            from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
                            inner join dbo.BATCHREVENUECONSTITUENT on BATCHREVENUECONSTITUENT.ID = BATCHREVENUECONSTITUENTGROUPMEMBER.MEMBERID
                            inner join dbo.BATCHMEMBERSHIPDUES on BATCHREVENUECONSTITUENT.ID = BATCHMEMBERSHIPDUES.BILLTOCONSTITUENTID or BATCHREVENUECONSTITUENT.ID = BATCHMEMBERSHIPDUES.MEMBERSHIPRECIPIENTID
                            where BATCHMEMBERSHIPDUES.BATCHID = @ID

                            union all

                            select BATCHREVENUECONSTITUENTGROUPMEMBER.ID
                            from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
                            inner join dbo.BATCHMEMBERSHIPDUESMEMBER on BATCHMEMBERSHIPDUESMEMBER.CONSTITUENTID = BATCHREVENUECONSTITUENTGROUPMEMBER.MEMBERID
                            inner join dbo.BATCHMEMBERSHIPDUES on BATCHMEMBERSHIPDUES.ID = BATCHMEMBERSHIPDUESMEMBER.BATCHMEMBERSHIPDUESID
                            where BATCHMEMBERSHIPDUES.BATCHID = @ID
                        );

                        --remove revenue constituent if the constituent was created in this batch

                        delete from dbo.BATCHREVENUECONSTITUENT
                        where ID in
                        (
                            select BATCHREVENUECONSTITUENT.ID
                            from dbo.BATCHREVENUECONSTITUENT
                                inner join dbo.BATCHMEMBERSHIPDUES on BATCHREVENUECONSTITUENT.ID = BATCHMEMBERSHIPDUES.BILLTOCONSTITUENTID
                            where BATCHMEMBERSHIPDUES.BATCHID = @ID

                            union all

                            select BATCHREVENUECONSTITUENT.ID
                            from dbo.BATCHREVENUECONSTITUENT
                                inner join dbo.BATCHMEMBERSHIPDUES on BATCHREVENUECONSTITUENT.ID = BATCHMEMBERSHIPDUES.MEMBERSHIPRECIPIENTID
                            where BATCHMEMBERSHIPDUES.BATCHID = @ID

                            union all

                            select BATCHREVENUECONSTITUENT.ID
                            from dbo.BATCHREVENUECONSTITUENT
                            inner join dbo.BATCHMEMBERSHIPDUESMEMBER on BATCHMEMBERSHIPDUESMEMBER.CONSTITUENTID = BATCHREVENUECONSTITUENT.ID
                            inner join dbo.BATCHMEMBERSHIPDUES on BATCHMEMBERSHIPDUES.ID = BATCHMEMBERSHIPDUESMEMBER.BATCHMEMBERSHIPDUESID
                            where BATCHMEMBERSHIPDUES.BATCHID = @ID
                        );

                        delete from dbo.BATCHREVENUECONSTITUENTACCOUNT
                        where BATCHREVENUECONSTITUENTACCOUNT.ID in
                            (
                                select CONSTITUENTACCOUNTID
                                from dbo.BATCHMEMBERSHIPDUES
                                where BATCHMEMBERSHIPDUES.BATCHID = @ID
                            );
                    end

                    update dbo.REVENUESCHEDULE
                    set
                        REVENUESCHEDULE.ISPENDING = 0,
                        REVENUESCHEDULE.CHANGEDBYID = @CHANGEAGENTID,
                        REVENUESCHEDULE.DATECHANGED = @CURRENTDATE
                    from dbo.REVENUESCHEDULE
                    inner join dbo.BATCHMEMBERSHIPDUES on BATCHMEMBERSHIPDUES.BATCHID = @ID and dbo.UFN_MEMBERSHIP_GETPLEDGE(BATCHMEMBERSHIPDUES.EXISTINGMEMBERSHIPID) = REVENUESCHEDULE.ID;

                    --SEPAMANDATEACTIVITY stores a BATCHROWID column that can store either ERB rows or membership dues batch rows. This is a guid column because we cannot have two FK's due to 

                    -- multiple cascade paths

                    update dbo.SEPAMANDATEACTIVITY
                    set BATCHROWID = null
                    from dbo.SEPAMANDATEACTIVITY
                    inner join dbo.BATCHMEMBERSHIPDUES on BATCHMEMBERSHIPDUES.ID = SEPAMANDATEACTIVITY.BATCHROWID
                    where BATCHMEMBERSHIPDUES.BATCHID = @ID;

                    delete from dbo.BATCHCONSTITUENTUPDATE where BATCHCONSTITUENTUPDATE.BATCHID = @ID;

                    -- use the system generated delete routine to allow proper recording of the deleting agent

                    exec USP_BATCHMEMBERSHIPDUES_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID

                    return 0;
                end