USP_HOUSEHOLDRECOGNITIONSETTINGS_OVERRIDEIFNEEDED

            CREATE procedure dbo.USP_HOUSEHOLDRECOGNITIONSETTINGS_OVERRIDEIFNEEDED
            (
                @CONSTITUENTONEID uniqueidentifier,
                @CONSTITUENTTWOID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier                        
            )
            as
                set nocount on

                declare @HOUSEHOLDID uniqueidentifier

                -- Handle the two constituents being a part of the same group

                select @HOUSEHOLDID = GROUPMEMBERONE.GROUPID
                from dbo.GROUPMEMBER GROUPMEMBERONE
                inner join dbo.GROUPMEMBER GROUPMEMBERTWO on GROUPMEMBERONE.GROUPID = GROUPMEMBERTWO.GROUPID
                inner join dbo.GROUPDATA on GROUPMEMBERONE.GROUPID = GROUPDATA.ID
                where
                    GROUPMEMBERONE.MEMBERID = @CONSTITUENTONEID and
                    GROUPMEMBERTWO.MEMBERID = @CONSTITUENTTWOID and
                    GROUPDATA.GROUPTYPECODE = 0 -- Group is a household


                -- Handle one of the constituents being a household and the other a member

                if @HOUSEHOLDID is null
                begin
                    declare @CURRENTDATEEARLIEST date
                    set @CURRENTDATEEARLIEST = getdate()

                    select @HOUSEHOLDID = GROUPMEMBER.GROUPID
                    from dbo.GROUPMEMBER
                    inner join dbo.GROUPDATA on GROUPMEMBER.GROUPID = GROUPDATA.ID
                    left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
                    where
                        GROUPDATA.GROUPTYPECODE = 0 and -- Household

                        (
                            (GROUPMEMBER.MEMBERID = @CONSTITUENTONEID and GROUPMEMBER.GROUPID = @CONSTITUENTTWOID) or
                            (GROUPMEMBER.MEMBERID = @CONSTITUENTTWOID and GROUPMEMBER.GROUPID = @CONSTITUENTONEID)
                        ) and
                        -- Verify they are still active

                        ((GROUPMEMBERDATERANGE.DATEFROM is null and (GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO > @CURRENTDATEEARLIEST))
                            or (GROUPMEMBERDATERANGE.DATETO is null and (GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM <= @CURRENTDATEEARLIEST)) 
                            or (GROUPMEMBERDATERANGE.DATEFROM <= @CURRENTDATEEARLIEST and GROUPMEMBERDATERANGE.DATETO > @CURRENTDATEEARLIEST))
                end

                if @HOUSEHOLDID is not null
                begin
                    declare @CURRENTDATE datetime
                    set @CURRENTDATE = getdate()

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

                    -- Only copy the default settings if the household doesn't already have 

                    -- an override row.

                    if not exists (select 1 from dbo.HOUSEHOLDRECOGNITIONSETTINGS where ID = @HOUSEHOLDID)
                        insert into dbo.HOUSEHOLDRECOGNITIONSETTINGS 
                        (
                            ID,
                            HOUSEHOLDRECOGNIZEHOUSEHOLD,
                            HOUSEHOLDRECOGNIZEMEMBERSCODE,
                            MEMBERRECOGNIZEHOUSEHOLD,
                            MEMBERRECOGNIZEMEMBER,
                            MEMBERRECOGNIZEOTHERMEMBERSCODE,
                            HOUSEHOLDREVENUERECOGNITIONTYPECODEID,
                            MEMBERREVENUERECOGNITIONTYPECODEID,
                            MEMBERHOUSEHOLDREVENUERECOGNITIONTYPECODEID,
                            DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID
                        )
                        select top 1
                            @HOUSEHOLDID,
                            HOUSEHOLDRECOGNIZEHOUSEHOLD,
                            HOUSEHOLDRECOGNIZEMEMBERS,
                            MEMBERRECOGNIZEHOUSEHOLD,
                            MEMBERRECOGNIZEMEMBER,
                            MEMBERRECOGNIZEOTHERMEMBERS,
                            HOUSEHOLDREVENUERECOGNITIONTYPECODEID,
                            MEMBERREVENUERECOGNITIONTYPECODEID,
                            MEMBERHOUSEHOLDREVENUERECOGNITIONTYPECODEID,
                            @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID
                        from dbo.HOUSEHOLDINFO
                        order by DATEADDED
                end