USP_STEWARDSHIPPACKAGEREPORT_RECIPIENTS

            create proc dbo.USP_STEWARDSHIPPACKAGEREPORT_RECIPIENTS
            (
                @DESIGNATIONLEVELID uniqueidentifier,
                @REPORTUSERID nvarchar(128) = null,
                @ALTREPORTUSERID nvarchar(128) = null
            )
                with execute as owner
                as
                set nocount on;

                declare @SQL nvarchar(4000);
                declare @TABLE nvarchar(255);
                declare @CURRENTAPPUSERID uniqueidentifier;
                declare @ISADMIN bit;
                declare @APPUSER_IN_NONRACROLE bit;
                declare @APPUSER_IN_NOSECGROUPROLE bit;

                set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
                set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
                if @ISADMIN = 0
                    begin
                        set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
                        if @APPUSER_IN_NONRACROLE = 0
                            set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);                        
                    end 

                begin try
                    select @TABLE = BUSINESSPROCESSOUTPUT.TABLENAME 
                    from dbo.BUSINESSPROCESSOUTPUT inner join dbo.STEWARDSHIPPACKAGEPROCESSHISTORY on BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID = STEWARDSHIPPACKAGEPROCESSHISTORY.BUSINESSPROCESSSTATUSGUID
                    where STEWARDSHIPPACKAGEPROCESSHISTORY.ID = @DESIGNATIONLEVELID and BUSINESSPROCESSOUTPUT.TABLEKEY = 'OUTPUT_RECIPIENTS';                                    

                    set @SQL = 'select 
                                    dbo.UFN_CONSTITUENT_BUILDNAME(CONSTITUENTID) as NAME,
                                    DATE,
                                    AMOUNT,
                                    PURPOSERECIPIENTSTATUSCODE.DESCRIPTION as STATUS,
                                    PURPOSERECIPIENTTYPECODE.DESCRIPTION as TYPE,
                                    SCHOLARSHIPTERM.NAME as SCHOLARSHIPTERM,
                                    FERPA
                                from 
                                    dbo.' + @TABLE + ' RECIPIENT inner join dbo.CONSTITUENT on RECIPIENT.CONSTITUENTID = CONSTITUENT.ID
                                        left outer join dbo.PURPOSERECIPIENTSTATUSCODE on RECIPIENT.STATUSCODEID = PURPOSERECIPIENTSTATUSCODE.ID
                                        left outer join dbo.PURPOSERECIPIENTTYPECODE on RECIPIENT.TYPECODEID = PURPOSERECIPIENTTYPECODE.ID
                                        left outer join dbo.SCHOLARSHIPTERM as SCHOLARSHIPTERM on RECIPIENT.SCHOLARSHIPTERMID = SCHOLARSHIPTERM.ID' + nchar(13)                       

                    set @SQL = @SQL + ' where
                                        (@ISADMIN = 1 or 
                                            @APPUSER_IN_NONRACROLE = 1 or
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)' + nchar(13)

                    set @SQL = @SQL + ' order by NAME';

                    exec sp_executesql @SQL, 
                        N'@TABLE nvarchar(255), @CURRENTAPPUSERID uniqueidentifier, @ISADMIN bit, @APPUSER_IN_NONRACROLE bit, @APPUSER_IN_NOSECGROUPROLE bit',
                        @TABLE=@TABLE, @CURRENTAPPUSERID=@CURRENTAPPUSERID, @ISADMIN=@ISADMIN, @APPUSER_IN_NONRACROLE=@APPUSER_IN_NONRACROLE, @APPUSER_IN_NOSECGROUPROLE=@APPUSER_IN_NOSECGROUPROLE;
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch