USP_DATAFORMTEMPLATE_EDIT_HOUSEHOLDRECOGNITIONSETTINGS_2

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_HOUSEHOLDRECOGNITIONSETTINGS_2
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @HOUSEHOLDRECOGNIZEHOUSEHOLD bit,
                        @HOUSEHOLDRECOGNIZEMEMBERS tinyint,
                        @HOUSEHOLDSPECIFIEDMEMBERS xml,
                        @MEMBERRECOGNIZEHOUSEHOLD bit,
                        @MEMBERRECOGNIZEMEMBER bit,
                        @MEMBERRECOGNIZEOTHERMEMBERS tinyint,
                        @MEMBERSPECIFIEDMEMBERS xml,
                        @HOUSEHOLDREVENUERECOGNITIONTYPECODEID uniqueidentifier,
                        @MEMBERREVENUERECOGNITIONTYPECODEID uniqueidentifier,
                        @MEMBERHOUSEHOLDREVENUERECOGNITIONTYPECODEID uniqueidentifier
                    )
                    as
                        set nocount on;

                        declare @CURRENTDATE datetime
                        set @CURRENTDATE = getdate()

                        declare @CURRENTDATEEARLIESTTIME date;
                        set @CURRENTDATEEARLIESTTIME = getdate();

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

                        declare @OLDHOUSEHOLDRECOGNIZEMEMBERS tinyint, @OLDMEMBERRECOGNIZEHOUSEHOLD bit, @OLDMEMBERRECOGNIZEOTHERMEMBERS tinyint
                        select
                            @OLDHOUSEHOLDRECOGNIZEMEMBERS = HOUSEHOLDRECOGNIZEMEMBERSCODE,
                            @OLDMEMBERRECOGNIZEHOUSEHOLD = MEMBERRECOGNIZEHOUSEHOLD,
                            @OLDMEMBERRECOGNIZEOTHERMEMBERS = MEMBERRECOGNIZEOTHERMEMBERSCODE
                        from dbo.HOUSEHOLDRECOGNITIONSETTINGS
                        where ID = @ID

                        -- make sure that settings are not self-contradictory

                        declare @numHHSpecifiedMembers int = 0
                        select @numHHSpecifiedMembers = COUNT(*) from dbo.UFN_HOUSEHOLDRECOGNITIONS_GETHOUSEHOLDSPECIFIEDMEMBERS_FROMITEMLISTXML(@HOUSEHOLDSPECIFIEDMEMBERS)
                        if (@numHHSpecifiedMembers > 0) And (@HOUSEHOLDRECOGNIZEMEMBERS <> 2)
                            raiserror('ERR_HOUSEHOLDSPECIFIEDMEMBERS_INVALID', 13, 1);

                        declare @numMemberSpecifiedMembers int = 0
                        select @numMemberSpecifiedMembers = COUNT(*) from dbo.UFN_HOUSEHOLDRECOGNITIONS_GETMEMBERSPECIFIEDMEMBERS_FROMITEMLISTXML(@MEMBERSPECIFIEDMEMBERS)
                        if (@numMemberSpecifiedMembers > 0) And (@MEMBERRECOGNIZEOTHERMEMBERS <> 2)
                            raiserror('ERR_MEMBERSPECIFIEDMEMBERS_INVALID', 13, 1);

                        begin try
                            update dbo.HOUSEHOLDRECOGNITIONSETTINGS set
                                HOUSEHOLDRECOGNIZEHOUSEHOLD = @HOUSEHOLDRECOGNIZEHOUSEHOLD,
                                HOUSEHOLDRECOGNIZEMEMBERSCODE = @HOUSEHOLDRECOGNIZEMEMBERS,
                                MEMBERRECOGNIZEHOUSEHOLD = @MEMBERRECOGNIZEHOUSEHOLD,
                                MEMBERRECOGNIZEMEMBER = @MEMBERRECOGNIZEMEMBER,
                                MEMBERRECOGNIZEOTHERMEMBERSCODE = @MEMBERRECOGNIZEOTHERMEMBERS,
                                HOUSEHOLDREVENUERECOGNITIONTYPECODEID = @HOUSEHOLDREVENUERECOGNITIONTYPECODEID,
                                MEMBERREVENUERECOGNITIONTYPECODEID = @MEMBERREVENUERECOGNITIONTYPECODEID,
                                MEMBERHOUSEHOLDREVENUERECOGNITIONTYPECODEID = @MEMBERHOUSEHOLDREVENUERECOGNITIONTYPECODEID,
                                DATECHANGED = @CURRENTDATE,
                                CHANGEDBYID = @CHANGEAGENTID
                            where ID = @ID

                            if @@ROWCOUNT = 0
                                insert into dbo.HOUSEHOLDRECOGNITIONSETTINGS
                                (
                                    ID,
                                    HOUSEHOLDRECOGNIZEHOUSEHOLD,
                                    HOUSEHOLDRECOGNIZEMEMBERSCODE,
                                    MEMBERRECOGNIZEHOUSEHOLD,
                                    MEMBERRECOGNIZEMEMBER,
                                    MEMBERRECOGNIZEOTHERMEMBERSCODE,
                                    HOUSEHOLDREVENUERECOGNITIONTYPECODEID,
                                    MEMBERREVENUERECOGNITIONTYPECODEID,
                                    MEMBERHOUSEHOLDREVENUERECOGNITIONTYPECODEID,
                                    DATEADDED,DATECHANGED,ADDEDBYID,CHANGEDBYID
                                )
                                values
                                (
                                    @ID,
                                    @HOUSEHOLDRECOGNIZEHOUSEHOLD,
                                    @HOUSEHOLDRECOGNIZEMEMBERS,
                                    @MEMBERRECOGNIZEHOUSEHOLD,
                                    @MEMBERRECOGNIZEMEMBER,
                                    @MEMBERRECOGNIZEOTHERMEMBERS,
                                    @HOUSEHOLDREVENUERECOGNITIONTYPECODEID,
                                    @MEMBERREVENUERECOGNITIONTYPECODEID,
                                    @MEMBERHOUSEHOLDREVENUERECOGNITIONTYPECODEID,
                                    @CURRENTDATE,@CURRENTDATE,@CHANGEAGENTID,@CHANGEAGENTID
                                )

                            --cache current context information

                            declare @contextCache varbinary(128)
                            set @contextCache = CONTEXT_INFO()

                            --set CONTEXT_INFO to @CHANGEAGENTID

                            set CONTEXT_INFO @CHANGEAGENTID

                            declare @GROUPMEMBER table
                            (
                                GROUPMEMBERID uniqueidentifier
                            )

                            insert into @GROUPMEMBER (GROUPMEMBERID)
                            select
                                GM.MEMBERID 
                            from dbo.GROUPMEMBER GM
                            left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                            where GM.GROUPID = @ID
                            -- the GMDR.DATETO is set to the earliest time on that day, so use a strictly greater than in checks using current day

                            and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
                            or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME)) 
                            or (GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME and GMDR.DATETO > @CURRENTDATEEARLIESTTIME))                                    

                            -- If the household gives recognition credit to specific members, setup those records.

                            -- Otherwise, the recognition defaults are cleared for any members if the value was changed

                            -- and the new value is the household doesn't recognize any members or recognizes all members.  The records

                            -- aren't always cleared so that any values entered to override the defaults aren't cleared.

                            if @HOUSEHOLDRECOGNIZEMEMBERS = 2
                            begin
                                -- Clear any records that have PREVENTRECOGNITIONSDEFAULTING set since it isn't used unless all members are recognized for household

                                delete from dbo.REVENUERECOGNITIONDEFAULT where SOURCECONSTITUENTID = @ID and PREVENTRECOGNITIONSDEFAULTING = 1

                                exec dbo.USP_HOUSEHOLDRECOGNITIONS_GETHOUSEHOLDSPECIFIEDMEMBERS_UPDATEFROMXML @ID, @HOUSEHOLDSPECIFIEDMEMBERS, @CHANGEAGENTID, @CURRENTDATE
                            end
                            else if (@OLDHOUSEHOLDRECOGNIZEMEMBERS <> 0 and @HOUSEHOLDRECOGNIZEMEMBERS = 0) or (@OLDHOUSEHOLDRECOGNIZEMEMBERS <> 1 and @HOUSEHOLDRECOGNIZEMEMBERS = 1) -- Household doesn't recognize any members

                            begin
                                -- Clear specific recognition default entries for all members


                                -- make sure that audit records are aware of correct change agent ID

                                set @contextCache = CONTEXT_INFO();
                                set CONTEXT_INFO @CHANGEAGENTID;

                                delete from dbo.REVENUERECOGNITIONDEFAULT 
                                where
                                    SOURCECONSTITUENTID = @ID and
                                    RECIPIENTCONSTITUENTID in (select GROUPMEMBERID from @GROUPMEMBER) and
                                    (@HOUSEHOLDRECOGNIZEMEMBERS = 0 or PREVENTRECOGNITIONSDEFAULTING = 1) -- Clear entries if either no members should be recognized or the record prevents recognition defaulting

                            end

                            -- If the option Member recognizes household changed, clean up any overrides

                            if @OLDMEMBERRECOGNIZEHOUSEHOLD <> @MEMBERRECOGNIZEHOUSEHOLD
                            begin
                                delete from dbo.REVENUERECOGNITIONDEFAULT
                                where
                                    SOURCECONSTITUENTID in (select GROUPMEMBERID from @GROUPMEMBER) and
                                    RECIPIENTCONSTITUENTID = @ID and
                                    (@MEMBERRECOGNIZEHOUSEHOLD = 0 or PREVENTRECOGNITIONSDEFAULTING = 1) -- Clear entries if either the household isn't recognized or the record prevents recognition defaulting

                            end

                            --reset CONTEXT_INFO to previous value

                            if not @contextCache is null
                                set CONTEXT_INFO @contextCache;

                            -- If Member to Member is set to Specified, update the recognition defaults.  If the value

                            -- was changed to recognize no other members or all other members, clear the records.  The

                            -- records are only cleared when changed so that records entered to override those values

                            -- aren't cleared.

                            if @MEMBERRECOGNIZEOTHERMEMBERS = 2
                            begin
                                -- Handle the Other members specified members collection.  The UPDATEFROMXML

                                -- SP can't be used since the ParentIDColumn is GROUPID but that column

                                -- isn't on the GROUPRECOGNITION table.

                                exec dbo.USP_HOUSEHOLDRECOGNITIONS_MEMBERSPECIFIEDMEMBERSUPDATE @ID, @MEMBERSPECIFIEDMEMBERS, @CURRENTDATE, @CHANGEAGENTID
                            end
                            else if (@OLDMEMBERRECOGNIZEOTHERMEMBERS <> 0 and @MEMBERRECOGNIZEOTHERMEMBERS = 0) or (@OLDMEMBERRECOGNIZEOTHERMEMBERS <> 1 and @MEMBERRECOGNIZEOTHERMEMBERS = 1)
                            begin
                                -- make sure that audit records are aware of correct change agent ID

                                set @contextCache = CONTEXT_INFO();
                                set CONTEXT_INFO @CHANGEAGENTID;

                                delete from dbo.REVENUERECOGNITIONDEFAULT
                                where
                                    SOURCECONSTITUENTID in (select GROUPMEMBERID from @GROUPMEMBER) and
                                    RECIPIENTCONSTITUENTID in (select GROUPMEMBERID from @GROUPMEMBER) and
                                    (@MEMBERRECOGNIZEOTHERMEMBERS = 0 or PREVENTRECOGNITIONSDEFAULTING = 1) -- Clear entries if either no members should be recognized or the record prevents recognition defaulting


                                --reset CONTEXT_INFO to previous value

                                if not @contextCache is null
                                    set CONTEXT_INFO @contextCache;
                            end

                            -- reset CONTEXT_INFO to previous value

                            if not @contextCache is null
                                set CONTEXT_INFO @contextCache;
                    end try
                    begin catch
                        exec dbo.USP_RAISE_ERROR;
                        return 1;
                    end catch

                    return 0;