UFN_HOUSEHOLDRECOGNITIONS_GETHOUSEHOLDSPECIFIEDMEMBERS

            CREATE function dbo.UFN_HOUSEHOLDRECOGNITIONS_GETHOUSEHOLDSPECIFIEDMEMBERS
            (
                @SOURCECONSTITUENTID uniqueidentifier
            )
            returns @specifiedmembers table
            (
                ID uniqueidentifier,
                RECIPIENTCONSTITUENTID uniqueidentifier,
                MATCHFACTOR decimal(5, 2),
                REVENUERECOGNITIONTYPECODEID uniqueidentifier
            )
            as
            begin
                declare @CURRENTDATEEARLIESTTIME date;
                set @CURRENTDATEEARLIESTTIME = getdate();

                -- Return constituents that have recognition matching setup with the household and are members.

                insert into @specifiedmembers
                (
                    ID,
                    RECIPIENTCONSTITUENTID,
                    MATCHFACTOR,
                    REVENUERECOGNITIONTYPECODEID
                )
                select
                    RD.ID,
                    RD.RECIPIENTCONSTITUENTID,
                    RD.MATCHFACTOR,
                    RD.REVENUERECOGNITIONTYPECODEID
                from dbo.REVENUERECOGNITIONDEFAULT RD
                inner join dbo.GROUPMEMBER GM on RD.SOURCECONSTITUENTID = GM.GROUPID and RD.RECIPIENTCONSTITUENTID = GM.MEMBERID
                inner join dbo.CONSTITUENT C on GM.MEMBERID = C.ID
                left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                where
                    RD.SOURCECONSTITUENTID = @SOURCECONSTITUENTID
                    -- 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)) and
                    -- Exclude records with PREVENTRECOGNITIONSDEFAULTING set to true

                    -- since that acts as flag to indicate the recognition default

                    -- no longer exists

                    PREVENTRECOGNITIONSDEFAULTING = 0

                order by C.KEYNAME, C.FIRSTNAME

                return
            end