USP_REPORT_STEWARDSHIPPLANASSIGNMENTPROCESS_DATA

            create procedure dbo.USP_REPORT_STEWARDSHIPPLANASSIGNMENTPROCESS_DATA
            (
                @BUSINESSPROCESSSTATUSID uniqueidentifier,
                @REPORTUSERID nvarchar(128),
                @ALTREPORTUSERID nvarchar(128)
            )
            with execute as owner
            as
            begin
                declare @CURRENTAPPUSERID uniqueidentifier;
                declare @ISADMIN bit;
                declare @APPUSER_IN_NONRACROLE bit;
                declare @APPUSER_IN_NOSECGROUPROLE bit;
                declare @APPUSER_IN_NONSITEROLE bit;
                declare @APPUSER_IN_NOSITEROLE bit;

                set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
                set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

                if @ISADMIN <> 1
                    set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);

                if @ISADMIN <> 1 and @APPUSER_IN_NONRACROLE <> 1
                    set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);

                if @ISADMIN <> 1
                    set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);

                if @ISADMIN <> 1 and @APPUSER_IN_NONSITEROLE <> 1
                    set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);


                declare @TABLENAME nvarchar(256);
                select @TABLENAME = TABLENAME from dbo.BUSINESSPROCESSOUTPUT
                where BUSINESSPROCESSSTATUSID = @BUSINESSPROCESSSTATUSID and upper(TABLEKEY) = 'UPDATEDRECORD';

                declare @SQLTOEXEC nvarchar(max);
                set @SQLTOEXEC = '    select 
                                        POSITIONTYPE,
                                        NF.NAME as PROSPECTNAME,
                                        STEWARDSHIPPLAN.NAME as PLANNAME,
                                        STEWARDSHIPPLANSTEP.OBJECTIVE,
                                        STEWARDSHIPSTEWARDROLECODE.DESCRIPTION as ROLE
                                    from dbo.' + @TABLENAME + ' as REPORTDATA
                                    inner join dbo.STEWARDSHIPPLAN on REPORTDATA.STEWARDSHIPPLANID = STEWARDSHIPPLAN.ID
                                    left join dbo.STEWARDSHIPPLANSTEP on POSITIONTYPE = 2 and REPORTDATA.ID = STEWARDSHIPPLANSTEP.ID
                                    cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPPLAN.CONSTITUENTID) NF
                                    left join dbo.STEWARDSHIPSTEWARDROLECODE on REPORTDATA.ROLECODEID = STEWARDSHIPSTEWARDROLECODE.ID
                                    '

                set @SQLTOEXEC = @SQLTOEXEC + ' order by PROSPECTNAME, PLANNAME, OBJECTIVE, REPORTDATA.POSITIONTYPE';

                exec sp_executesql @SQLTOEXEC,N'@CURRENTAPPUSERID uniqueidentifier,@ISADMIN bit,@APPUSER_IN_NONRACROLE bit,@APPUSER_IN_NOSECGROUPROLE bit,@APPUSER_IN_NONSITEROLE bit,@APPUSER_IN_NOSITEROLE bit',@CURRENTAPPUSERID=@CURRENTAPPUSERID,@ISADMIN=@ISADMIN,@APPUSER_IN_NONRACROLE=@APPUSER_IN_NONRACROLE,@APPUSER_IN_NOSECGROUPROLE=@APPUSER_IN_NOSECGROUPROLE,@APPUSER_IN_NONSITEROLE=@APPUSER_IN_NONSITEROLE,@APPUSER_IN_NOSITEROLE=@APPUSER_IN_NOSITEROLE;
            end