UFN_BATCHCONSTITUENT_GETRECOGNITIONDEFAULTSBYSOURCE

            CREATE function dbo.UFN_BATCHCONSTITUENT_GETRECOGNITIONDEFAULTSBYSOURCE
            (
                @SOURCECONSTITUENTID uniqueidentifier,
                @INCLUDESELFMATCHING bit = 1,
                @INCLUDEHOUSEHOLDSETTINGSGENERATEDDEFAULTS bit = 1
            )
            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
            )
            as
            begin
                declare @CURRENTDATEEARLIEST datetime = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
                declare @ISHOUSEHOLDMEMBER bit = 0;
                declare @ISHOUSEHOLD bit = 0;
                declare @ISNEWCONSTITUENT bit = 0;

                if exists (select ID from dbo.BATCHREVENUECONSTITUENT where ID = @SOURCECONSTITUENTID)
                begin
                    set @ISNEWCONSTITUENT = 1;
                end

                if @INCLUDEHOUSEHOLDSETTINGSGENERATEDDEFAULTS = 1
                begin
                    declare @MEMBERSHOUSEHOLDID uniqueidentifier
                    select
                        @MEMBERSHOUSEHOLDID = GROUPID
                    from 
                        dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
                    where 
                        MEMBERID = @SOURCECONSTITUENTID;

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

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

                        -- since @MEMBERHOUSEHOLDID isn't an actually household yet, this will use the defaults

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

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

                        if @MEMBERRECOGNIZEOTHERMEMBERSCODE = 1 -- all other members

                        begin
                            insert into @RECOGNITIONDEFAULTS
                            (
                                RECIPIENTCONSTITUENTID,
                                HOUSEHOLDSETTINGSGENERATED,
                                REVENUERECOGNITIONTYPECODEID,
                                MATCHFACTOR
                            )
                            select
                                coalesce(BATCHREVENUECONSTITUENT.EXISTINGCONSTITUENTID, BATCHREVENUECONSTITUENTGROUPMEMBER.MEMBERID),
                                1,
                                @MEMBERREVENUERECOGNITIONTYPECODEID,
                                100
                            from 
                                dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
                                inner join dbo.BATCHREVENUECONSTITUENT on BATCHREVENUECONSTITUENTGROUPMEMBER.MEMBERID = BATCHREVENUECONSTITUENT.ID
                            where
                                BATCHREVENUECONSTITUENTGROUPMEMBER.GROUPID = @MEMBERSHOUSEHOLDID 
                                and BATCHREVENUECONSTITUENTGROUPMEMBER.MEMBERID <> @SOURCECONSTITUENTID
                        end
                    end
                    else
                    begin

                        select 
                            @ISHOUSEHOLD = case when GROUPTYPECODE = 0 and ISGROUP = 1 then 1 else 0 end
                        from 
                            dbo.BATCHREVENUECONSTITUENT 
                        where 
                            ID = @SOURCECONSTITUENTID

                        set @ISHOUSEHOLD = coalesce(@ISHOUSEHOLD, 0)

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

                            -- since @SOURCECONSTITUENTID isn't an actual household/constituent yet, this will use the defaults

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

                            if @HOUSEHOLDRECOGNIZEMEMBERSCODE = 1 -- all members

                            begin
                                insert into @RECOGNITIONDEFAULTS
                                (
                                    RECIPIENTCONSTITUENTID,
                                    HOUSEHOLDSETTINGSGENERATED,
                                    REVENUERECOGNITIONTYPECODEID,
                                    MATCHFACTOR
                                )
                                select
                                    coalesce(BATCHREVENUECONSTITUENT.EXISTINGCONSTITUENTID, BATCHREVENUECONSTITUENTGROUPMEMBER.MEMBERID),
                                    1,
                                    @HOUSEHOLDREVENUERECOGNITIONTYPECODEID,
                                    100
                                from 
                                    dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
                                    inner join dbo.BATCHREVENUECONSTITUENT on BATCHREVENUECONSTITUENTGROUPMEMBER.MEMBERID = BATCHREVENUECONSTITUENT.ID
                                where
                                    BATCHREVENUECONSTITUENTGROUPMEMBER.GROUPID = @SOURCECONSTITUENTID 
                            end

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

                        end
                    end
                end

                -- always recognize themselves as long as @INCLUDESELFMATCHING is true

                if (
                    (@INCLUDESELFMATCHING = 1)
                    -- sanity check that this is a newly created constituent in batch

                    and @ISNEWCONSTITUENT = 1
                )
                begin
                    declare @DONORDEFAULTIDID uniqueidentifier;
                    select @DONORDEFAULTIDID = DONORREVENUERECOGNITIONTYPECODEID from dbo.RECOGNITIONDEFAULT;

                    insert into @RECOGNITIONDEFAULTS
                    (
                        RECIPIENTCONSTITUENTID, 
                        MATCHFACTOR,
                        REVENUERECOGNITIONTYPECODEID
                    )
                    select 
                        @SOURCECONSTITUENTID,
                        100,
                        @DONORDEFAULTIDID;
                end

        -- WI 676912

        -- Only use BATCHREVENUECONSTITUENTRELATION for defaults if not a new constituent

        -- Constituents created in batch should use recognition settings for defaulting

        -- New constituents will need to use BATCHREVENUECONSTITUENTRELATION defaulting data if default relationship recognition options are set

                if @ISNEWCONSTITUENT = 0 or exists (select top 1 ID from RECOGNITIONRELATIONSHIPDEFAULT)
                begin
                  -- insert what would be generated via REVENUERECOGNITIONDEFAULT

                  insert into @RECOGNITIONDEFAULTS
                  (
                      RECIPIENTCONSTITUENTID,
                      REVENUERECOGNITIONTYPECODEID,
                      MATCHFACTOR,
                      STARTDATE
                  )
                  select
                      COALESCE(BATCHREVENUECONSTITUENT.EXISTINGCONSTITUENTID, BATCHREVENUECONSTITUENTRELATION.RELATIONID),
                      BATCHREVENUECONSTITUENTRELATION.RECIPROCALRECOGNITIONTYPECODEID,
                      BATCHREVENUECONSTITUENTRELATION.RECIPROCALMATCHFACTOR,
                      BATCHREVENUECONSTITUENTRELATION.STARTDATE
                  from
                      dbo.BATCHREVENUECONSTITUENTRELATION
                      left join dbo.BATCHREVENUECONSTITUENT on BATCHREVENUECONSTITUENT.ID = BATCHREVENUECONSTITUENTRELATION.RELATIONID
                  where
                      BATCHREVENUECONSTITUENTRELATION.CONSTITUENTID = @SOURCECONSTITUENTID;
                end

                -- Remove duplicates - there should only be dupes if a default was added

                -- both by a household setting and a relationship recognition entry.

                -- Priority is given to the relationship record 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 as [SUBTABLE]
                            where
                                [SUBTABLE].HOUSEHOLDSETTINGSGENERATED = 0 
                                and [BASETABLE].RECIPIENTCONSTITUENTID = [SUBTABLE].RECIPIENTCONSTITUENTID
                        )
                    );

                return;
            end