USP_REVENUEBATCH_CONSTITUENT_DELETE

            CREATE procedure dbo.USP_REVENUEBATCH_CONSTITUENT_DELETE
            (
                @BATCHREVENUECONSTITUENTID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null output
            )
            as
                set nocount on;

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

                --cache current context information

                declare @contextCache varbinary(128);
                set @contextCache = CONTEXT_INFO();
                --set CONTEXT_INFO to @CHANGEAGENTID

                set CONTEXT_INFO @CHANGEAGENTID;

                delete from
                    dbo.BATCHREVENUEFINANCIALINSTITUTION
                where
                    BATCHREVENUEFINANCIALINSTITUTION.ID in (
                                                            select
                                                                BATCHREVENUECONSTITUENTACCOUNT.FINANCIALINSTITUTIONID
                                                            from
                                                                dbo.BATCHREVENUECONSTITUENTACCOUNT
                                                            where
                                                                BATCHREVENUECONSTITUENTACCOUNT.CONSTITUENTID = @BATCHREVENUECONSTITUENTID
                                                           )

                delete from
                    dbo.BATCHREVENUECONSTITUENTACCOUNT
                where
                    BATCHREVENUECONSTITUENTACCOUNT.CONSTITUENTID = @BATCHREVENUECONSTITUENTID

                -- If the constituent is a group/household or individual that resulted in a household, remove members of the group


                -- First, store which constituents are members

                declare @GROUPMEMBERSANDRELATIONS table
                (
                    BATCHREVENUECONSTITUENTID uniqueidentifier
                )

                -- Determine the household ID that may have been generated if the constituent is an individual

                declare @HOUSEHOLDID uniqueidentifier
                select @HOUSEHOLDID = BRCGM.GROUPID
                from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER BRCGM
                inner join BATCHREVENUECONSTITUENT BRC on BRCGM.GROUPID = BRC.ID
                where
                    BRCGM.MEMBERID = @BATCHREVENUECONSTITUENTID and
                    BRC.GROUPTYPECODE = 0 -- Verify group is household


                insert into @GROUPMEMBERSANDRELATIONS (BATCHREVENUECONSTITUENTID)
                select MEMBERID from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER 
                where 
                    -- Handle when the constituent is a group/household

                    GROUPID = @BATCHREVENUECONSTITUENTID or
                    -- Handle household generated by individual

                    GROUPID = @HOUSEHOLDID
                union
                select RELATIONID from dbo.BATCHREVENUECONSTITUENTRELATION where CONSTITUENTID = @BATCHREVENUECONSTITUENTID

                -- Handle the case when the constituent was a group and their primary contact was an individual or household

                -- (we prevent the user from selecting a group as a primary contact through the form metadata)

                insert into @GROUPMEMBERSANDRELATIONS (BATCHREVENUECONSTITUENTID)
                select MEMBERID from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER 
                where
                    GROUPID in (select BATCHREVENUECONSTITUENTID from @GROUPMEMBERSANDRELATIONS)
                union
                select GROUPID from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER 
                where
                    MEMBERID in (select BATCHREVENUECONSTITUENTID from @GROUPMEMBERSANDRELATIONS)
                union
                select RELATIONID from dbo.BATCHREVENUECONSTITUENTRELATION
                where CONSTITUENTID in (select BATCHREVENUECONSTITUENTID from @GROUPMEMBERSANDRELATIONS)

                delete from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER 
                where 
                    GROUPID = @BATCHREVENUECONSTITUENTID or 
                    GROUPID = @HOUSEHOLDID or
                    GROUPID in (select BATCHREVENUECONSTITUENTID from @GROUPMEMBERSANDRELATIONS)

                delete from dbo.BATCHREVENUECONSTITUENTRELATION
                where
                    CONSTITUENTID = @BATCHREVENUECONSTITUENTID or
                    CONSTITUENTID in (select BATCHREVENUECONSTITUENTID from @GROUPMEMBERSANDRELATIONS)

                delete from dbo.BATCHREVENUECONSTITUENT 
                where ID in (select BATCHREVENUECONSTITUENTID from @GROUPMEMBERSANDRELATIONS)

                if @HOUSEHOLDID is not null
                    delete from dbo.BATCHREVENUECONSTITUENT where ID = @HOUSEHOLDID

                --delete the temporary batch version of the constituent

                delete from dbo.BATCHREVENUECONSTITUENT where ID = @BATCHREVENUECONSTITUENTID
                delete from dbo.[BATCHREVENUECONSTITUENTAPPEAL] where [CONSTITUENTID] = @BATCHREVENUECONSTITUENTID;

                --reset CONTEXT_INFO to previous value

                if not @contextCache is null
                    set CONTEXT_INFO @contextCache;