USP_GLOBALCHANGE_UPDATECONSTITUENTGENDERBATCH

        CREATE procedure dbo.USP_GLOBALCHANGE_UPDATECONSTITUENTGENDERBATCH
        (
            @IDSETREGISTERID uniqueidentifier, 
            @CHANGEAGENTID uniqueidentifier = null,
            @ASOF as datetime = null,
            @NUMBERADDED int = 0 output,
            @NUMBEREDITED int = 0 output,
            @NUMBERDELETED int = 0 output,
            @CURRENTAPPUSERID uniqueidentifier = null
        )
        as
            set nocount on;

            declare @CURRENTDATE datetime

            set @CURRENTDATE = getdate();
            set @NUMBERADDED = 0;
            set @NUMBEREDITED = 0;
            set @NUMBERDELETED = 0; 
            declare @BPID uniqueidentifier = '3269A1D1-31CB-4D28-945C-B7623A3EFCCA';

            declare @BYPASSSECURITY bit;
            declare @BYPASSSITESECURITY bit;
            exec dbo.USP_SECURITY_APPUSER_BYPASSSECURITYFORBUSINESSPROCESS @CURRENTAPPUSERID, @BPID, @BYPASSSECURITY output, @BYPASSSITESECURITY output;

            if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
            begin
                raiserror('INSUFFICIENTRIGHTS', 13, 1)
                return 1
            end

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

            declare @SELECTION table (ID uniqueidentifier not null primary key);

            insert into @SELECTION (ID)
            select
                ID
            from dbo.BATCH 
            where STATUSCODE = 0 and
            ID in (select [ID] from dbo.[UFN_IDSETREADER_GETRESULTS_GUID](@IDSETREGISTERID))

            begin try
                --updating constituent gendercodeid based on gendercode

                update dbo.BATCHREVENUECONSTITUENT 
                set dbo.BATCHREVENUECONSTITUENT.GENDERCODEID=GENDERCODEDEFAULTMAPPING.GENDERCODEID
                from dbo.BATCHREVENUECONSTITUENT
                inner join dbo.GENDERCODEDEFAULTMAPPING on BATCHREVENUECONSTITUENT.GENDERCODE = GENDERCODEDEFAULTMAPPING.DEFAULTREASONCODE
                inner join BATCHREVENUE on BATCHREVENUE.CONSTITUENTID = BATCHREVENUECONSTITUENT.ID
                inner join @SELECTION as SELECTION on SELECTION.ID = BATCHREVENUE.BATCHID

                set @NUMBEREDITED = @@ROWCOUNT;

                --updating constituent gendercodeid based on gendercode

                update dbo.BATCHCONSTITUENTUPDATE 
                set dbo.BATCHCONSTITUENTUPDATE.GENDERCODEID=GENDERCODEDEFAULTMAPPING.GENDERCODEID
                from dbo.BATCHCONSTITUENTUPDATE
                inner join dbo.GENDERCODEDEFAULTMAPPING on BATCHCONSTITUENTUPDATE.GENDERCODE = GENDERCODEDEFAULTMAPPING.DEFAULTREASONCODE
                inner join @SELECTION as SELECTION on SELECTION.ID = BATCHCONSTITUENTUPDATE.BATCHID

                set @NUMBEREDITED = @NUMBEREDITED + @@ROWCOUNT;

                --updating constituent spouse gendercodeid based on spouse gendercode

                update dbo.BATCHCONSTITUENTUPDATE 
                set dbo.BATCHCONSTITUENTUPDATE.SPOUSE_GENDERCODEID=GENDERCODEDEFAULTMAPPING.GENDERCODEID
                from dbo.BATCHCONSTITUENTUPDATE
                inner join dbo.GENDERCODEDEFAULTMAPPING on BATCHCONSTITUENTUPDATE.SPOUSE_GENDERCODE = GENDERCODEDEFAULTMAPPING.DEFAULTREASONCODE
                inner join @SELECTION as SELECTION on SELECTION.ID = BATCHCONSTITUENTUPDATE.BATCHID

            end try
            begin catch
                exec dbo.USP_RAISE_ERROR;
                return 1;
            end catch