UFN_CONSTITUENT_GETGROUPRECOGNITIONS

This function returns all the recognitions for a group and members.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN
@GROUPINCLUDESMEMBERGIVING	bit	IN

Definition

Copy



        CREATE function dbo.UFN_CONSTITUENT_GETGROUPRECOGNITIONS(@CONSTITUENTID uniqueidentifier, @GROUPINCLUDESMEMBERGIVING bit)
        returns table
        as return
            select
                RR.ID, RR.EFFECTIVEDATE, RR.AMOUNT, RR.REVENUERECOGNITIONTYPECODEID, RR.REVENUESPLITID
            from
                dbo.REVENUERECOGNITION RR with (nolock)
            where 
                RR.CONSTITUENTID = @CONSTITUENTID
            union all
            select
                RR.ID, RR.EFFECTIVEDATE, RR.AMOUNT, RR.REVENUERECOGNITIONTYPECODEID, RR.REVENUESPLITID
            from
                dbo.REVENUERECOGNITION RR with (nolock)
            inner join dbo.GROUPMEMBER GM with (nolock) on RR.CONSTITUENTID = GM.MEMBERID
            inner join dbo.GROUPMEMBERDATERANGE GMDR with (nolock) on GMDR.GROUPMEMBERID = GM.ID
            where 
                (
                    (
                        GM.GROUPID = @CONSTITUENTID
                        and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= RR.EFFECTIVEDATE))
                            or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= RR.EFFECTIVEDATE)) 
                            or (GMDR.DATEFROM <= RR.EFFECTIVEDATE and GMDR.DATETO >= RR.EFFECTIVEDATE))
                    )
                )