USP_DATALIST_WEALTHINFORMATIONDASHBOARDPHILANTHROPICGIFT

This datalist returns WealthPiont philanthropic gift information that is used by the wealth information dashboard.
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Constituent
@ISVISIBLE	bit	IN	Visible
@CONFIDENCE	int	IN	Confidence
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_WEALTHINFORMATIONDASHBOARDPHILANTHROPICGIFT
                (
                    @CONSTITUENTID uniqueidentifier,
                    @ISVISIBLE bit = 1,
                    @CONFIDENCE int = 0,
                    @CURRENTAPPUSERID 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
                        with RECORDCONFIDENCE as(
                            select     
                                WP.ID,
                                case
                                    when WP.CONFIRMED = 1 then '5'
                                    when WP.REJECTED =  1 then '0'
                                    else
                                         isnull(CR.CONFIDENCE,(select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION))
                                end as CONFIDENCE

                            from
                            dbo.WPPHILANTHROPICGIFT WP
                            left outer join
                                dbo.MATCHCODE MC on WP.MC = MC.MATCHCODE
                            left outer join 
                                dbo.CONFIDENCERATING CR on CR.MATCHCODEID = MC.ID    
                            left outer join
                                dbo.WEALTHSOURCE WS on CR.WEALTHSOURCEID = WS.ID 
                            where
                             WS.SOURCE like WP.SOURCE or WS.SOURCE is null
                        )

                        select top 1000
                            SOURCE,
                            ORGANIZATION,
                            LOCATION,
                            nullif(GIFTYEAR, 0) as GIFTYEAR,
                            LO,
                            HI,
                            CATEGORY,
                            NAME,
                            TYPE
                        from
                            dbo.WPPHILANTHROPICGIFT WP
                        left outer join
                            RECORDCONFIDENCE RC on RC.ID = WP.ID
                        where
                            WEALTHID = @CONSTITUENTID and REJECTED = 0 and
                            (((0 <= @CONFIDENCE and @CONFIDENCE <=5) and RC.CONFIDENCE >= @CONFIDENCE) or
                            ((@CONFIDENCE = 99)                     and WP.CONFIRMED = 1))
                        and (@ISADMIN = 1 or 
                            @APPUSER_IN_NONRACROLE = 1 or
                            dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, WEALTHID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                        order by SOURCE, ORGANIZATION, GIFTYEAR desc
                    end
                    else
                    begin
                        with RECORDCONFIDENCE as(
                            select     
                                WP.ID,
                                case
                                    when WP.CONFIRMED = 1 then '5'
                                    when WP.REJECTED =  1 then '0'
                                    else
                                         isnull(CR.CONFIDENCE,(select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION))
                                end as CONFIDENCE

                            from
                            dbo.WPPHILANTHROPICGIFT WP
                            left outer join
                                dbo.MATCHCODE MC on WP.MC = MC.MATCHCODE
                            left outer join 
                                dbo.CONFIDENCERATING CR on CR.MATCHCODEID = MC.ID    
                            left outer join
                                dbo.WEALTHSOURCE WS on CR.WEALTHSOURCEID = WS.ID 
                            where
                             WS.SOURCE like WP.SOURCE or WS.SOURCE is null
                        )

                        select top 1
                            SOURCE,
                            ORGANIZATION,
                            LOCATION,
                            nullif(GIFTYEAR, 0) as GIFTYEAR,
                            LO,
                            HI,
                            CATEGORY,
                            NAME,
                            TYPE
                        from
                            dbo.WPPHILANTHROPICGIFT WP
                        left outer join
                            RECORDCONFIDENCE RC on RC.ID = WP.ID
                        where
                            WEALTHID = @CONSTITUENTID and REJECTED = 0 and
                            (((0 <= @CONFIDENCE and @CONFIDENCE <=5) and RC.CONFIDENCE >= @CONFIDENCE) or
                            ((@CONFIDENCE = 99)                     and WP.CONFIRMED = 1))
                        order by SOURCE, ORGANIZATION, GIFTYEAR desc
                    end