USP_REPORT_PROSPECTPLANFOLLOWUP

            CREATE procedure dbo.USP_REPORT_PROSPECTPLANFOLLOWUP
            (
                @PROSPECTQUERYID uniqueidentifier = null,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @INCLUDECOMPLETEDSTEPS bit = null,
                @OWNERID uniqueidentifier = null,
                @STEPTYPE uniqueidentifier = null,
                @PROSPECTSTATUS uniqueidentifier = null,
                @INCLUDEHISTORICALPLANS bit = null,
                @PROSPECTMANAGER uniqueidentifier = null,
                @PLANSTAGE uniqueidentifier = null,
                @PLANTYPE uniqueidentifier = null,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @ONLYOWNEDINTERACTIONS bit = 0,
                @SECURITYFEATUREID uniqueidentifier = null,
                @SECURITYFEATURETYPE tinyint = null
            )
            as            
                set nocount on;

                declare @ISADMIN bit;
                declare @APPUSER_IN_NONRACROLE bit;
                declare @APPUSER_IN_NOSECGROUPROLE bit;
                declare @APPUSER_IN_NONSITEROLE bit;
                declare @APPUSER_IN_NOSITEROLE 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);
                set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);
                set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);

                begin try
                    if @PROSPECTQUERYID is null
                        select 
                            CONSTITUENT.ID as PROSPECTID,
                            CONSTITUENT.KEYNAME as PROSPECTKEYNAME,
                            CONSTITUENT.FIRSTNAME as PROSPECTFIRSTNAME,
                            CONSTITUENT_NF.NAME as PROSPECTNAME, 
                            dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID) as ADDRESS,
                            PROSPECTPLANTYPECODE.DESCRIPTION as PLANTYPE,
                            PROSPECTPLANSTATUSCODE.DESCRIPTION as PLANSTAGE,
                            INTERACTION.DATE as STEPDATE,
                            INTERACTIONTYPECODE.DESCRIPTION as STEPTYPE,
                            FUNDRAISER.KEYNAME as FUNDRAISERKEYNAME,
                            FUNDRAISER.FIRSTNAME as FUNDRAISERFIRSTNAME,
                            FUNDRAISER_NF.NAME as FUNDRAISERNAME,
                            PROSPECTMANAGER.KEYNAME as PROSPECTMANAGERKEYNAME,
                            PROSPECTMANAGER.FIRSTNAME as PROSPECTMANAGERFIRSTNAME,
                            PROSPECTMANAGER_NF.NAME as PROSPECTMANAGERNAME,
                            INTERACTION.STATUS as STEPSTATUS,
                            (select dbo.UDA_BUILDLIST(NF.NAME) from dbo.INTERACTIONADDITIONALFUNDRAISER outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(INTERACTIONADDITIONALFUNDRAISER.FUNDRAISERID) NF where INTERACTIONADDITIONALFUNDRAISER.INTERACTIONID = INTERACTION.ID) as ADDITIONALFUNDRAISERNAME
                        from 
                            dbo.INTERACTION 
                            inner join dbo.CONSTITUENT on INTERACTION.CONSTITUENTID = CONSTITUENT.ID
                            inner join dbo.PROSPECT on CONSTITUENT.ID = PROSPECT.ID
                            inner join dbo.PROSPECTPLAN on INTERACTION.PROSPECTPLANID = PROSPECTPLAN.ID
                            inner join dbo.PROSPECTPLANTYPECODE on PROSPECTPLAN.PROSPECTPLANTYPECODEID = PROSPECTPLANTYPECODE.ID
                            left join dbo.PROSPECTPLANSTATUSCODE on PROSPECTPLAN.PROSPECTPLANSTATUSCODEID = PROSPECTPLANSTATUSCODE.ID
                            left join dbo.INTERACTIONTYPECODE on INTERACTION.INTERACTIONTYPECODEID = INTERACTIONTYPECODE.ID
                            left join dbo.CONSTITUENT as FUNDRAISER on INTERACTION.FUNDRAISERID = FUNDRAISER.ID        
                            left join dbo.ADDRESS on CONSTITUENT.ID = ADDRESS.CONSTITUENTID and ADDRESS.ISPRIMARY = 1
                            left join dbo.CONSTITUENT as PROSPECTMANAGER on PROSPECT.PROSPECTMANAGERFUNDRAISERID = PROSPECTMANAGER.ID
                            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) CONSTITUENT_NF
                            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(FUNDRAISER.ID) FUNDRAISER_NF
                            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTMANAGER.ID) PROSPECTMANAGER_NF
                        where
                            INTERACTION.DATE between @STARTDATE and @ENDDATE and
                            (@INCLUDECOMPLETEDSTEPS = 1 or INTERACTION.COMPLETED = 0) and
                            (@OWNERID is null or
                                (
                                    @OWNERID = INTERACTION.FUNDRAISERID or
                                    (@ONLYOWNEDINTERACTIONS = 0 and exists(select ID from dbo.INTERACTIONADDITIONALFUNDRAISER where INTERACTIONID = INTERACTION.ID and FUNDRAISERID = @OWNERID))
                                )
                            ) and
                            (@STEPTYPE is null or INTERACTION.INTERACTIONTYPECODEID = @STEPTYPE) and
                            (@PROSPECTSTATUS is null or PROSPECT.PROSPECTSTATUSCODEID = @PROSPECTSTATUS) and
                            (@INCLUDEHISTORICALPLANS = 1 or PROSPECTPLAN.ISACTIVE = 1) and
                            (@PROSPECTMANAGER is null or PROSPECT.PROSPECTMANAGERFUNDRAISERID = @PROSPECTMANAGER) and
                            (@PLANSTAGE is null or PROSPECTPLAN.PROSPECTPLANSTATUSCODEID = @PLANSTAGE) and
                            (@PLANTYPE is null or PROSPECTPLAN.PROSPECTPLANTYPECODEID = @PLANTYPE) and
                            (@ISADMIN = 1 or
                                (
                                    (@APPUSER_IN_NONRACROLE = 1 or
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                                    and
                                    (@APPUSER_IN_NONSITEROLE = 1 or
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSITEROLE) = 1)
                                )
                            ) and
                            (
                              (@SECURITYFEATUREID is null and @SECURITYFEATURETYPE is null) or
                              (
                                  select count(*) 
                                  from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PROSPECTPLAN.ID) as PROSPECTPLANSITE 
                                  /*SITEEXTENSION*/
                                  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
                            CONSTITUENT.KEYNAME,
                            CONSTITUENT.FIRSTNAME,
                            PROSPECTPLANTYPECODE.DESCRIPTION,
                            INTERACTION.DATE
                    else
                        select 
                            CONSTITUENT.ID as PROSPECTID,
                            CONSTITUENT.KEYNAME as PROSPECTKEYNAME,
                            CONSTITUENT.FIRSTNAME as PROSPECTFIRSTNAME,
                            CONSTITUENT_NF.NAME as PROSPECTNAME, 
                            dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID) as ADDRESS,
                            PROSPECTPLANTYPECODE.DESCRIPTION as PLANTYPE,
                            PROSPECTPLANSTATUSCODE.DESCRIPTION as PLANSTAGE,
                            INTERACTION.DATE as STEPDATE,
                            INTERACTIONTYPECODE.DESCRIPTION as STEPTYPE,
                            FUNDRAISER.KEYNAME as FUNDRAISERKEYNAME,
                            FUNDRAISER.FIRSTNAME as FUNDRAISERFIRSTNAME,
                            FUNDRAISER_NF.NAME as FUNDRAISERNAME,
                            PROSPECTMANAGER.KEYNAME as PROSPECTMANAGERKEYNAME,
                            PROSPECTMANAGER.FIRSTNAME as PROSPECTMANAGERFIRSTNAME,
                            PROSPECTMANAGER_NF.NAME as PROSPECTMANAGERNAME,
                            INTERACTION.STATUS as STEPSTATUS,
                            (select dbo.UDA_BUILDLIST(NF.NAME) from dbo.INTERACTIONADDITIONALFUNDRAISER outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(INTERACTIONADDITIONALFUNDRAISER.FUNDRAISERID) NF where INTERACTIONADDITIONALFUNDRAISER.INTERACTIONID = INTERACTION.ID) as ADDITIONALFUNDRAISERNAME
                        from 
                            dbo.INTERACTION
                            inner join dbo.CONSTITUENT on INTERACTION.CONSTITUENTID = CONSTITUENT.ID
                            inner join dbo.PROSPECT on CONSTITUENT.ID = PROSPECT.ID
                            inner join dbo.PROSPECTPLAN on INTERACTION.PROSPECTPLANID = PROSPECTPLAN.ID
                            inner join dbo.PROSPECTPLANTYPECODE on PROSPECTPLAN.PROSPECTPLANTYPECODEID = PROSPECTPLANTYPECODE.ID
                            inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@PROSPECTQUERYID) SELECTION on CONSTITUENT.ID = SELECTION.ID
                            left join dbo.PROSPECTPLANSTATUSCODE on PROSPECTPLAN.PROSPECTPLANSTATUSCODEID = PROSPECTPLANSTATUSCODE.ID
                            left join dbo.INTERACTIONTYPECODE on INTERACTION.INTERACTIONTYPECODEID = INTERACTIONTYPECODE.ID
                            left join dbo.CONSTITUENT as FUNDRAISER on INTERACTION.FUNDRAISERID = FUNDRAISER.ID
                            left join dbo.ADDRESS on CONSTITUENT.ID = ADDRESS.CONSTITUENTID and ADDRESS.ISPRIMARY = 1
                            left join dbo.CONSTITUENT as PROSPECTMANAGER on PROSPECT.PROSPECTMANAGERFUNDRAISERID = PROSPECTMANAGER.ID
                            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) CONSTITUENT_NF
                            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(FUNDRAISER.ID) FUNDRAISER_NF
                            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTMANAGER.ID) PROSPECTMANAGER_NF
                        where
                            INTERACTION.DATE between @STARTDATE and @ENDDATE and
                            (@INCLUDECOMPLETEDSTEPS = 1 or INTERACTION.COMPLETED = 0) and
                            (@OWNERID is null or
                                (
                                    @OWNERID = INTERACTION.FUNDRAISERID or
                                    (@ONLYOWNEDINTERACTIONS = 0 and exists(select ID from dbo.INTERACTIONADDITIONALFUNDRAISER where INTERACTIONID = INTERACTION.ID and FUNDRAISERID = @OWNERID))
                                )
                            ) and
                            (@STEPTYPE is null or INTERACTION.INTERACTIONTYPECODEID = @STEPTYPE) and
                            (@PROSPECTSTATUS is null or PROSPECT.PROSPECTSTATUSCODEID = @PROSPECTSTATUS) and
                            (@INCLUDEHISTORICALPLANS = 1 or PROSPECTPLAN.ISACTIVE = 1) and
                            (@PROSPECTMANAGER is null or PROSPECT.PROSPECTMANAGERFUNDRAISERID = @PROSPECTMANAGER) and
                            (@PLANSTAGE is null or PROSPECTPLAN.PROSPECTPLANSTATUSCODEID = @PLANSTAGE) and
                            (@PLANTYPE is null or PROSPECTPLAN.PROSPECTPLANTYPECODEID = @PLANTYPE) and
                            (@ISADMIN = 1 or
                                (
                                    (@APPUSER_IN_NONRACROLE = 1 or
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                                    and
                                    (@APPUSER_IN_NONSITEROLE = 1 or
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSITEROLE) = 1)
                                )
                            ) and
                            (
                              (@SECURITYFEATUREID is null and @SECURITYFEATURETYPE is null) or
                              (
                                  select count(*) 
                                  from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PROSPECTPLAN.ID) as PROSPECTPLANSITE 
                                  /*SITEEXTENSION*/
                                  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
                            CONSTITUENT.KEYNAME,
                            CONSTITUENT.FIRSTNAME,
                            PROSPECTPLANTYPECODE.DESCRIPTION,
                            INTERACTION.DATE
                end try

                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;