USP_DATALIST_CONSTITUENTPROFILEDASHBOARDPROSPECTPLANS

This datalist returns prospect plan 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 currencyID

Definition

Copy



                    CREATE procedure dbo.USP_DATALIST_CONSTITUENTPROFILEDASHBOARDPROSPECTPLANS
                    (
                        @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
                                dbo.UFN_PROSPECTPLAN_GETSITELIST(PP.ID) as SITE,
                                PSC.DESCRIPTION CURRENTSTAGE,
                                LI.ACTUALDATE LASTDATE,
                                LIO_NF.NAME LASTSTEPOWNER,
                                LI.OBJECTIVE LASTSTEPOBJECTIVE,
                                NI.EXPECTEDDATE NEXTDATE,
                                NIO_NF.NAME NEXTSTEPOWNER,
                                NI.OBJECTIVE NEXTSTEPOBJECTIVE,
                                PM_NF.NAME PRIMARYMANAGER,
                                PF_NF.NAME SECONDARYMANAGER,
                                dbo.UFN_OPPORTUNITY_DESIGNATIONLIST(o.ID) DESIGNATIONS,
                                dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(o.ID, @SELECTEDCURRENCYID) OPPORTUNITYAMOUNT,
                                o.ASKDATE,
                                STC.DESCRIPTION,
                                PP.NAME as PROSPECTPLANNAME
                            from
                                dbo.PROSPECTPLAN PP
                                left outer join dbo.INTERACTION LI on LI.ID=dbo.UFN_PROSPECTPLAN_GETLASTSTEP(PP.ID)
                                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(LI.FUNDRAISERID) LIO_NF
                                left outer join dbo.INTERACTION NI on NI.ID=dbo.UFN_PROSPECTPLAN_GETNEXTSTEP(PP.ID)
                                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(NI.FUNDRAISERID) NIO_NF
                                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.PRIMARYMANAGERFUNDRAISERID) PM_NF
                                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.SECONDARYMANAGERFUNDRAISERID) PF_NF
                                left outer join dbo.PROSPECTPLANTYPECODE STC on STC.ID=PP.PROSPECTPLANTYPECODEID
                                left outer join dbo.PROSPECTPLANSTATUSCODE PSC on PSC.ID=PP.PROSPECTPLANSTATUSCODEID
                                left outer join dbo.OPPORTUNITY o on o.PROSPECTPLANID = pp.ID
                            where
                                PP.PROSPECTID = @CONSTITUENTID
                                and
                                (
                                    o.ID is null
                                    or o.ID in (select top(1) ID from dbo.OPPORTUNITY where PROSPECTPLANID = pp.ID and STATUSCODE in (1,2))
                                    or o.ID in (select top(1) ID from dbo.OPPORTUNITY where PROSPECTPLANID = pp.ID order by coalesce(RESPONSEDATE, ASKDATE, EXPECTEDASKDATE, DATEADDED) desc)
                                )
                                and (
                                    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
                        end