USP_DATALIST_CONSTITUENTPROFILEDASHBOARDLATESTRECOGNITION

                CREATE procedure dbo.USP_DATALIST_CONSTITUENTPROFILEDASHBOARDLATESTRECOGNITION
                (
                    @CONSTITUENTID uniqueidentifier,
                    @ISVISIBLE bit = 1,
                    @CURRENTAPPUSERID uniqueidentifier,
                    @SELECTEDCURRENCYID uniqueidentifier = null
                )
                as
                    set nocount on;

                    declare @ISADMIN bit;
                    declare @APPUSER_IN_NONRACROLE bit;
                    declare @APPUSER_IN_NOSECGROUPROLE bit;

                    set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
                    set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
                    set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);

                    if @ISVISIBLE = 1
                    begin
                        declare @ISGROUP bit, @INCLUDEMEMBERGIVING bit
                        select
                            @ISGROUP = C.ISGROUP,
                            @INCLUDEMEMBERGIVING = case when GD.GROUPTYPECODE = 0 then 1 else coalesce(GT.INCLUDEMEMBERGIVING, 0) end
                        from dbo.CONSTITUENT C 
                        left join dbo.GROUPDATA GD on C.ID = GD.ID
                        left join dbo.GROUPTYPE GT on GD.GROUPTYPEID = GT.ID
                        where C.ID = @CONSTITUENTID;

                        declare @CURRENTDATEEARLIESTTIME date
                        set @CURRENTDATEEARLIESTTIME = getdate();

                        if @ISGROUP = 1
                        begin
                            declare @CONSTITUENTSTOINCLUDE table
                            (
                                CONSTITUENTID uniqueidentifier
                            )

                            insert into @CONSTITUENTSTOINCLUDE(CONSTITUENTID)
                            select @CONSTITUENTID

                            if @INCLUDEMEMBERGIVING = 1
                                insert into @CONSTITUENTSTOINCLUDE (CONSTITUENTID)
                                select
                                    GM.MEMBERID 
                                from dbo.GROUPMEMBER GM
                                left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                                where GM.GROUPID = @CONSTITUENTID 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))


                            select top 1
                                RR.EFFECTIVEDATE,
                                R.TRANSACTIONTYPE,
                                dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY(RR.ID, @SELECTEDCURRENCYID) as TOTALAMOUNT,
                                C.NAME as CONSTITUENTNAME
                            from @CONSTITUENTSTOINCLUDE CTI
                            inner join dbo.REVENUERECOGNITION RR on RR.CONSTITUENTID = CTI.CONSTITUENTID
                            inner join dbo.REVENUESPLIT RS on RS.ID = RR.REVENUESPLITID
                            inner join dbo.REVENUE R on RS.REVENUEID = R.ID
                            inner join dbo.CONSTITUENT C on RR.CONSTITUENTID = C.ID
                            left join dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on PGR.REVENUEID = R.ID
                            left join dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
                            where
                                (R.TRANSACTIONTYPECODE = 1 or --Pledge

                                (R.TRANSACTIONTYPECODE = 3) or --Matching gift claim

                                (R.TRANSACTIONTYPECODE = 4 and PG.VEHICLECODE in (0,1,2,5,6,7,8,9)) or --Planned gift

                                ((R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 3)))) and --Payment (Gift or Recurring gift payment)

                                dbo.UFN_DESIGNATION_USERHASSITEACCESS(DESIGNATIONID,@CURRENTAPPUSERID) = 1
                            order by
                                RR.EFFECTIVEDATE desc, RR.DATEADDED desc
                        end
                        else
                        begin
                            select top 1
                                RR.EFFECTIVEDATE,
                                R.TRANSACTIONTYPE,
                                dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY(RR.ID, @SELECTEDCURRENCYID) as TOTALAMOUNT,
                                C.NAME as CONSTITUENTNAME
                            from dbo.REVENUERECOGNITION RR
                            inner join dbo.REVENUESPLIT RS on RS.ID = RR.REVENUESPLITID
                            inner join dbo.REVENUE R on RS.REVENUEID = R.ID
                            inner join dbo.CONSTITUENT C on RR.CONSTITUENTID = C.ID
                            left join dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on PGR.REVENUEID = R.ID
                            left join dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
                            where
                                (R.TRANSACTIONTYPECODE = 1 or --Pledge

                                (R.TRANSACTIONTYPECODE = 3) or --Matching gift claim

                                (R.TRANSACTIONTYPECODE = 4 and PG.VEHICLECODE in (0,1,2,5,6,7,8,9)) or --Planned gift

                                ((R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 3)))) and --Payment (Gift or Recurring gift payment)

                                RR.CONSTITUENTID = @CONSTITUENTID and
                                dbo.UFN_DESIGNATION_USERHASSITEACCESS(DESIGNATIONID,@CURRENTAPPUSERID) = 1
                            order by
                                RR.EFFECTIVEDATE desc, RR.DATEADDED desc
                        end
                    end