USP_DATALIST_FUNDRAISER_STEPSCOMPLETED

A datalist of completed steps for a given fundraiser constituent.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@FUNDRAISERID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@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.

Definition

Copy



                CREATE procedure dbo.USP_DATALIST_FUNDRAISER_STEPSCOMPLETED
                (
                    @CURRENTAPPUSERID uniqueidentifier,
                    @FUNDRAISERID uniqueidentifier,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null
                ) as begin

                    set nocount on;

                    select 
                        I.ID,
                        PP.ID,
                        NF_PC.NAME, 
                        I.EXPECTEDDATE, 
                        I.ACTUALDATE, 
                        I.OBJECTIVE, 
                        I.STATUS, 
                        dbo.UFN_PROSPECTPLANTYPECODE_GETDESCRIPTION(PP.PROSPECTPLANTYPECODEID), 
                        dbo.UFN_PROSPECTPLANSTATUSCODE_GETDESCRIPTION(I.PROSPECTPLANSTATUSCODEID),
                        I.ISINTERACTION,
                        case when exists(select 1 from dbo.INTERACTIONNOTE where INTERACTIONNOTE.INTERACTIONID=I.ID)
                               or exists(select 1 from dbo.INTERACTIONATTACHMENT where INTERACTIONATTACHMENT.INTERACTIONID=I.ID)
                               or exists(select 1 from dbo.INTERACTIONMEDIALINK where INTERACTIONMEDIALINK.INTERACTIONID=I.ID)
                        then convert(bit,1) else convert(bit,0) end
                    from
                        dbo.INTERACTION I
                        inner join dbo.PROSPECTPLAN PP on PP.ID=I.PROSPECTPLANID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTID) NF_PC
                    where 
                        I.FUNDRAISERID=@FUNDRAISERID and
                        I.STATUSCODE in (2,3) 
                        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
                    order by
                        NF_PC.NAME, 
                        I.DATE

                end