UFN_CONSTITUENT_GETRECOGNITIONDEFAULTSBYSOURCE_2

            CREATE function dbo.UFN_CONSTITUENT_GETRECOGNITIONDEFAULTSBYSOURCE_2
            (
                @SOURCECONSTITUENTID uniqueidentifier,
                @INCLUDESELFMATCHING bit = 1,
                @INCLUDEHOUSEHOLDSETTINGSGENERATEDDEFAULTS bit = 1,
                @DATE datetime
            )
            returns @recognitiondefaults table
            (
                ID uniqueidentifier default '00000000-0000-0000-0000-000000000000',
                RECIPIENTCONSTITUENTID uniqueidentifier,
                MATCHFACTOR decimal(5, 2),
                REVENUERECOGNITIONTYPECODEID uniqueidentifier,
                HOUSEHOLDSETTINGSGENERATED bit default 0,
                STARTDATE datetime,
                ENDDATE datetime,
                PREVENTRECOGNITIONSDEFAULTING bit default 0
            )
            as
            begin
                declare @CURRENTDATEEARLIEST datetime
                set @CURRENTDATEEARLIEST = dbo.UFN_DATE_GETEARLIESTTIME(@DATE)

                declare @ISHOUSEHOLDMEMBER bit, @ISHOUSEHOLD bit
                set @ISHOUSEHOLDMEMBER = 0
                set @ISHOUSEHOLD = 0

                --Grab Donor Default

                declare @DONORDEFAULTIDID uniqueidentifier;
                select @DONORDEFAULTIDID = DONORREVENUERECOGNITIONTYPECODEID from dbo.RECOGNITIONDEFAULT;


                if @INCLUDEHOUSEHOLDSETTINGSGENERATEDDEFAULTS = 1
                begin
                    -- Determine the constituents household, if they have one

                    declare @MEMBERSHOUSEHOLDID uniqueidentifier
                    select 
                        @MEMBERSHOUSEHOLDID = GM.GROUPID
                    from dbo.GROUPMEMBER GM
                    inner join dbo.GROUPDATA GD on GM.GROUPID = GD.ID
                    left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                    where
                        GM.MEMBERID = @SOURCECONSTITUENTID and
                        GD.GROUPTYPECODE = 0  and -- Indicates household

                        -- Verify they are still active

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

                    -- Handle household members 

                    if @MEMBERSHOUSEHOLDID is not null
                    begin
                        set @ISHOUSEHOLDMEMBER = 1

                        declare @MEMBERRECOGNIZEHOUSEHOLD bit, @MEMBERRECOGNIZEOTHERMEMBERSCODE tinyint
                        declare @MEMBERREVENUERECOGNITIONTYPECODEID uniqueidentifier;
                        declare @MEMBERHOUSEHOLDREVENUERECOGNITIONTYPECODEID uniqueidentifier;

                        select
                            @MEMBERRECOGNIZEHOUSEHOLD = MEMBERRECOGNIZEHOUSEHOLD,
                            @MEMBERRECOGNIZEOTHERMEMBERSCODE = MEMBERRECOGNIZEOTHERMEMBERSCODE,
                            @MEMBERREVENUERECOGNITIONTYPECODEID = MEMBERREVENUERECOGNITIONTYPECODEID,
                            @MEMBERHOUSEHOLDREVENUERECOGNITIONTYPECODEID = MEMBERHOUSEHOLDREVENUERECOGNITIONTYPECODEID
                        from dbo.UFN_HOUSEHOLD_GETRECOGNITIONSETTINGS(@MEMBERSHOUSEHOLDID)

                        if @MEMBERRECOGNIZEHOUSEHOLD = 1
                            insert into @recognitiondefaults
                            (
                                RECIPIENTCONSTITUENTID,
                                HOUSEHOLDSETTINGSGENERATED,
                                REVENUERECOGNITIONTYPECODEID,
                                MATCHFACTOR
                            )
                            values
                            (
                                @MEMBERSHOUSEHOLDID,
                                1,
                                @MEMBERHOUSEHOLDREVENUERECOGNITIONTYPECODEID,
                                100
                            )

                        if @MEMBERRECOGNIZEOTHERMEMBERSCODE = 1 -- All other members

                            insert into @recognitiondefaults
                            (
                                RECIPIENTCONSTITUENTID,
                                HOUSEHOLDSETTINGSGENERATED,
                                REVENUERECOGNITIONTYPECODEID,
                                MATCHFACTOR
                            )
                            select
                                MEMBERID,
                                1,
                                @MEMBERREVENUERECOGNITIONTYPECODEID,
                                100
                            from dbo.GROUPMEMBER GM
                            left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                            where
                                GROUPID = @MEMBERSHOUSEHOLDID and 
                                MEMBERID <> @SOURCECONSTITUENTID and
                                not exists (select top 1 1 from dbo.DECEASEDCONSTITUENT where DECEASEDCONSTITUENT.ID = MEMBERID) and
                                -- Verify included members are still active

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

                        -- @MEMBERRECOGNIZEOTHERMEMBERSCODE = 2 will be handled later, when the insert from REVENUERECOGNITIONDEFAULT occurs

                    end
                    else
                    begin
                        -- Determine if the constituent is a household

                        select 
                            @ISHOUSEHOLD = case when GD.GROUPTYPECODE = 0 then 1 else 0 end
                        from dbo.GROUPDATA GD
                        where
                            GD.ID = @SOURCECONSTITUENTID

                        set @ISHOUSEHOLD = coalesce(@ISHOUSEHOLD, 0)

                        -- Handle households

                        if @ISHOUSEHOLD = 1
                        begin                            
                            declare @HOUSEHOLDRECOGNIZEMEMBERSCODE tinyint
                            declare @HOUSEHOLDREVENUERECOGNITIONTYPECODEID uniqueidentifier;

                            select
                                @HOUSEHOLDRECOGNIZEMEMBERSCODE = HOUSEHOLDRECOGNIZEMEMBERSCODE,
                                @HOUSEHOLDREVENUERECOGNITIONTYPECODEID = HOUSEHOLDREVENUERECOGNITIONTYPECODEID
                            from dbo.UFN_HOUSEHOLD_GETRECOGNITIONSETTINGS(@SOURCECONSTITUENTID)

                            if @HOUSEHOLDRECOGNIZEMEMBERSCODE = 1 -- All members

                                insert into @recognitiondefaults
                                (
                                    RECIPIENTCONSTITUENTID,
                                    HOUSEHOLDSETTINGSGENERATED,
                                    REVENUERECOGNITIONTYPECODEID,
                                    MATCHFACTOR
                                )
                                select
                                    MEMBERID,
                                    1,
                                    @HOUSEHOLDREVENUERECOGNITIONTYPECODEID,
                                    100
                                from dbo.GROUPMEMBER GM
                                left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                                where
                                    GROUPID = @SOURCECONSTITUENTID and
                                    not exists (select top 1 1 from dbo.DECEASEDCONSTITUENT where DECEASEDCONSTITUENT.ID = MEMBERID) and
                                    -- Verify included members are still active

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

                            -- @HOUSEHOLDRECOGNIZEMEMBERSCODE = 2 will be handled later, when the insert from REVENUERECOGNITIONDEFAULT occurs

                        end
                    end
                end

                -- If constituent isn't a household always recognize themselves as long

                -- as @INCLUDESELFMATCHING is true.

                if @INCLUDESELFMATCHING = 1 
                    and (exists (select ID from dbo.CONSTITUENT where ID = @SOURCECONSTITUENTID) -- The constituent may not exist yet if being called from batch

                         or exists (select ID from dbo.MKTFINDERNUMBERCONSTITUENT where ID = @SOURCECONSTITUENTID)) -- acquisition list constituent

                begin

                    insert into @recognitiondefaults
                    (
                      RECIPIENTCONSTITUENTID, 
                      MATCHFACTOR,
                      REVENUERECOGNITIONTYPECODEID
                    )
                    -- Create recognition for constituent tied to revenue record

                    values(
                      @SOURCECONSTITUENTID,
                      100,
                      @DONORDEFAULTIDID
                    )
                end

                insert into @recognitiondefaults
                (
                    ID,
                    RECIPIENTCONSTITUENTID,
                    MATCHFACTOR,
                    REVENUERECOGNITIONTYPECODEID,
                    STARTDATE,
                    ENDDATE,
                    PREVENTRECOGNITIONSDEFAULTING
                )
                select
                    ID,
                    RECIPIENTCONSTITUENTID,
                    MATCHFACTOR,
                    REVENUERECOGNITIONTYPECODEID,
                    STARTDATE,
                    ENDDATE,
                    PREVENTRECOGNITIONSDEFAULTING
                from dbo.REVENUERECOGNITIONDEFAULT RM
                where
                    SOURCECONSTITUENTID = @SOURCECONSTITUENTID

                -- Remove duplicate records.  There should only be duplicates

                -- if a default was added both by a household setting and an entry

                -- in REVENUERECOGNITIONDEFAULT.  Priority is given to the record

                -- from REVENUERECOGNITIONDEFAULT since that means a user overrode

                -- the household setting.

                delete @recognitiondefaults
                from @recognitiondefaults as BASETABLE
                where
                    (BASETABLE.HOUSEHOLDSETTINGSGENERATED = 1 and
                    exists (
                        select 1 
                        from @recognitiondefaults SUBTABLE
                        where
                            SUBTABLE.HOUSEHOLDSETTINGSGENERATED = 0 and
                            BASETABLE.RECIPIENTCONSTITUENTID = SUBTABLE.RECIPIENTCONSTITUENTID)) or
                    PREVENTRECOGNITIONSDEFAULTING = 1

                return
            end