USP_DATALIST_CONSTITUENTPROFILEDASHBOARDPROSPECTSUMMARY

This datalist returns prospect summary information that is used by the constituent profile dashboard.
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Constituent
@ISVISIBLE	bit	IN	Visible
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.
@SELECTEDCURRENCYID	uniqueidentifier	IN	Selected currency ID
Definition

Copy


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

                        if @ISVISIBLE = 1
                        begin

                            select top(1)
                                case 
                                    when o.ID is not null then dbo.UFN_PROSPECTPLANTYPECODE_GETDESCRIPTION(pp.PROSPECTPLANTYPECODEID)
                                    else ''
                                end LATESTOPPORTUNITY,
                                dbo.UFN_OPPORTUNITY_GETASKAMOUNTINCURRENCY(o.ID, @SELECTEDCURRENCYID) ASKAMOUNT,
                                o.ASKDATE,
                                dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(o.ID, @SELECTEDCURRENCYID) AMOUNT,
                                (select top(1) 
                                    ACTUALDATE 
                                 from 
                                    dbo.INTERACTION 
                                 where CONSTITUENTID = @CONSTITUENTID 
                                 and PROSPECTPLANID is not null
                                 and ACTUALDATE is not null 
                                 order by ACTUALDATE desc) LASTINTERACTION,
                                (select top(1) 
                                    DATE 
                                 from dbo.REVENUE 
                                 where 
                                    CONSTITUENTID = @CONSTITUENTID 
                                 order by DATE desc) LASTGAVE,
                                MODELINGANDPROPENSITY.ANNUALGIFTLIKELIHOOD,
                                MODELINGANDPROPENSITY.ANNUITYLIKELIHOOD,
                                MODELINGANDPROPENSITY.BEQUESTLIKELIHOOD,
                                MODELINGANDPROPENSITY.CRTLIKELIHOOD,
                                MODELINGANDPROPENSITY.MAJORGIVINGLIKELIHOOD,
                                MODELINGANDPROPENSITY.PLANNEDGIFTLIKELIHOOD,
                                '',
                                '',
                                case when exists 
                                (select top(1) ID 
                                 from PROSPECTDATERANGE 
                                 where CONSTITUENTID = @CONSTITUENTID 
                                 and DATETO is null) then 'Yes' else 'No' end ISACTIVE,
                                PROSPECTMANAGER_NF.NAME PROSPECTMANAGER
                            from 
                                dbo.PROSPECT p
                            left join 
                                dbo.MODELINGANDPROPENSITY on p.ID = MODELINGANDPROPENSITY.ID
                            left join 
                                dbo.PROSPECTPLAN pp on p.ID = pp.PROSPECTID
                            left join 
                                dbo.OPPORTUNITY o on pp.ID = o.PROSPECTPLANID 
                                    and o.ASKDATE is not null
                            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(p.PROSPECTMANAGERFUNDRAISERID) PROSPECTMANAGER_NF
                            where 
                                p.ID = @CONSTITUENTID 
                                and (pp.ID is null
                                    or(
                                        select count(*) 
                                        from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PP.ID) as PROSPECTPLANSITE 
                                        where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PROSPECTPLANSITE].[SITEID] or (SITEID is null and [PROSPECTPLANSITE].[SITEID] is null)))
                                    ) > 0
                                )
                            order by 
                                o.ASKDATE desc
                        end