UFN_RECOGNITIONDEFAULTS_GETBETWEENTWOCONSTITUENTS

            create function dbo.UFN_RECOGNITIONDEFAULTS_GETBETWEENTWOCONSTITUENTS
            (
                @CONSTITUENTONEID uniqueidentifier,
                @CONSTITUENTTWOID uniqueidentifier
            )
            returns @RECOGNITIONDEFAULT table 
            (
                SOURCECONSTITUENTID uniqueidentifier,
                RECIPIENTCONSTITUENTID uniqueidentifier,
                MATCHFACTOR decimal(5, 2),
                REVENUERECOGNITIONTYPECODEID uniqueidentifier,
                ARESETTHROUGHMEMBERDEFAULTINGALLOTHERMEMBERS bit
            )
            with execute as caller
            as
            begin
                declare    @SOURCETORECIPIENTMATCHFACTOR decimal(5, 2), 
                        @SOURCETORECIPIENTREVENUERECOGNITIONTYPECODEID uniqueidentifier, 
                        @SOURCETORECIPIENTARESETTHROUGHMEMBERDEFAULTINGALLOTHERMEMBERS bit,
                        @SOURCETORECIPIENTPREVENTRECOGNITIONDEFAULTS bit,
                        @RECIPIENTTOSOURCEMATCHFACTOR decimal(5, 2), 
                        @RECIPIENTTOSOURCEREVENUERECOGNITIONTYPECODEID uniqueidentifier,
                        @RECIPIENTTOSOURCEARESETTHROUGHMEMBERDEFAULTINGALLOTHERMEMBERS bit,
                        @RECIPIENTTOSOURCEPREVENTRECOGNITIONDEFAULTS bit

                -- Check to see if there is a record setting up a recognition default just the two constituents

                select
                    @SOURCETORECIPIENTMATCHFACTOR = MATCHFACTOR,
                    @SOURCETORECIPIENTREVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODEID,
                    @SOURCETORECIPIENTPREVENTRECOGNITIONDEFAULTS = PREVENTRECOGNITIONSDEFAULTING
                from dbo.REVENUERECOGNITIONDEFAULT
                where
                    SOURCECONSTITUENTID = @CONSTITUENTONEID and
                    RECIPIENTCONSTITUENTID = @CONSTITUENTTWOID

                select
                    @RECIPIENTTOSOURCEMATCHFACTOR = MATCHFACTOR,
                    @RECIPIENTTOSOURCEREVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODEID,
                    @RECIPIENTTOSOURCEPREVENTRECOGNITIONDEFAULTS = PREVENTRECOGNITIONSDEFAULTING
                from dbo.REVENUERECOGNITIONDEFAULT
                where
                    SOURCECONSTITUENTID = @CONSTITUENTTWOID and
                    RECIPIENTCONSTITUENTID = @CONSTITUENTONEID

                if @SOURCETORECIPIENTMATCHFACTOR is null or @RECIPIENTTOSOURCEMATCHFACTOR is null
                begin
                    -- If the two constituents are members of the same household and that same household recognizes all members

                    -- for any member's revenue, then the two both have recognition defaults setup and the percent is 100%

                    declare @HOUSEHOLDID uniqueidentifier
                    select 
                        @HOUSEHOLDID = GM1.GROUPID 
                    from dbo.GROUPMEMBER GM1
                    inner join dbo.GROUPMEMBER GM2 on GM1.GROUPID = GM2.GROUPID
                    inner join dbo.GROUPDATA GD on GM1.GROUPID = GD.ID
                    where
                        GD.GROUPTYPECODE = 0 and
                        GM1.MEMBERID = @CONSTITUENTONEID and
                        GM2.MEMBERID = @CONSTITUENTTWOID

                    if @HOUSEHOLDID is not null
                    begin
                        declare @MEMBERRECOGNIZEOTHERMEMBERSCODE tinyint
                        set @MEMBERRECOGNIZEOTHERMEMBERSCODE = dbo.UFN_HOUSEHOLD_GETMEMBERRECOGNIZEOTHERMEMBERSSETTING(@HOUSEHOLDID)

                        if @MEMBERRECOGNIZEOTHERMEMBERSCODE = 1 -- All other members

                        begin
                            if @SOURCETORECIPIENTMATCHFACTOR is null
                            begin
                                set @SOURCETORECIPIENTMATCHFACTOR = 100
                                set @SOURCETORECIPIENTARESETTHROUGHMEMBERDEFAULTINGALLOTHERMEMBERS = 1
                            end

                            if @RECIPIENTTOSOURCEMATCHFACTOR is null
                            begin
                                set @RECIPIENTTOSOURCEMATCHFACTOR = 100
                                set @RECIPIENTTOSOURCEARESETTHROUGHMEMBERDEFAULTINGALLOTHERMEMBERS = 1
                            end
                        end
                    end
                end

                insert into @RECOGNITIONDEFAULT
                (
                    SOURCECONSTITUENTID,
                    RECIPIENTCONSTITUENTID,
                    MATCHFACTOR,
                    REVENUERECOGNITIONTYPECODEID,
                    ARESETTHROUGHMEMBERDEFAULTINGALLOTHERMEMBERS
                )
                select
                    @CONSTITUENTONEID,
                    @CONSTITUENTTWOID,
                    @SOURCETORECIPIENTMATCHFACTOR,
                    @SOURCETORECIPIENTREVENUERECOGNITIONTYPECODEID,
                    coalesce(@SOURCETORECIPIENTARESETTHROUGHMEMBERDEFAULTINGALLOTHERMEMBERS, 0)
                where
                    @SOURCETORECIPIENTMATCHFACTOR is not null and 
                    coalesce(@SOURCETORECIPIENTPREVENTRECOGNITIONDEFAULTS, 0) = 0

                union all

                select
                    @CONSTITUENTTWOID,
                    @CONSTITUENTONEID,
                    @RECIPIENTTOSOURCEMATCHFACTOR,
                    @RECIPIENTTOSOURCEREVENUERECOGNITIONTYPECODEID,
                    coalesce(@RECIPIENTTOSOURCEARESETTHROUGHMEMBERDEFAULTINGALLOTHERMEMBERS, 0)
                where
                    @RECIPIENTTOSOURCEMATCHFACTOR is not null and 
                    coalesce(@RECIPIENTTOSOURCEPREVENTRECOGNITIONDEFAULTS, 0) = 0

                return
            end