USP_BATCHMEMBERSHIPDUE_DELETE

                CREATE procedure dbo.USP_BATCHMEMBERSHIPDUE_DELETE
                (
                    @ID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier
                )
                as
                    declare @CURRENTDATE datetime = getdate();
                    --check deletion rules, if any

                    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.ID = @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.ID = @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.ID = @ID

                        union all

                        select BATCHREVENUECONSTITUENT.ID
                        from dbo.BATCHREVENUECONSTITUENT
                        inner join dbo.BATCHMEMBERSHIPDUES on BATCHREVENUECONSTITUENT.ID = BATCHMEMBERSHIPDUES.MEMBERSHIPRECIPIENTID
                        where BATCHMEMBERSHIPDUES.ID = @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.ID = @ID
                    );

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

                    delete from dbo.BATCHCONSTITUENTUPDATE
                    where BATCHCONSTITUENTUPDATE.ID in
                    (
                    select BATCHCONSTITUENTUPDATE.ID
                    from dbo.BATCHCONSTITUENTUPDATE
                        inner join dbo.BATCHMEMBERSHIPDUES on BATCHMEMBERSHIPDUES.ID = @ID
                    where BATCHMEMBERSHIPDUES.BILLTOCONSTITUENTID = BATCHCONSTITUENTUPDATE.PRIMARYRECORDID
                    );

                    declare @EXISTINGMEMBERSHIPID uniqueidentifier = (select EXISTINGMEMBERSHIPID from dbo.BATCHMEMBERSHIPDUES where BATCHMEMBERSHIPDUES.ID = @ID);
                    declare @PLEDGEID uniqueidentifier = dbo.UFN_MEMBERSHIP_GETPLEDGE(@EXISTINGMEMBERSHIPID);

                    update dbo.REVENUESCHEDULE
                    set
                        REVENUESCHEDULE.ISPENDING = 0,
                        REVENUESCHEDULE.CHANGEDBYID = @CHANGEAGENTID,
                        REVENUESCHEDULE.DATECHANGED = @CURRENTDATE
                    from dbo.REVENUESCHEDULE
                    where REVENUESCHEDULE.ID = @PLEDGEID;

                    --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
                    where SEPAMANDATEACTIVITY.BATCHROWID = @ID;

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

                    exec dbo.USP_BATCHMEMBERSHIPDUES_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;

                    return 0;