USP_REPORT_PROSPECTASSIGNMENTPROCESS_DATA

            CREATE procedure dbo.USP_REPORT_PROSPECTASSIGNMENTPROCESS_DATA
            (
                @BUSINESSPROCESSSTATUSID uniqueidentifier,
                @REPORTUSERID nvarchar(128),
                @ALTREPORTUSERID nvarchar(128),
                @REPORTTYPE tinyint = 0 -- 0 for success report, 1 for exception report

            )
            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,
                                        PROSPECTPLAN.NAME as PLANNAME,
                                        INTERACTION.OBJECTIVE,
                                        REPORTDATA.EXCEPTIONTYPECODE
                                    from dbo.' + @TABLENAME + ' as REPORTDATA
                                    left join dbo.PROSPECTPLAN on PROSPECTPLAN.ID = REPORTDATA.PROSPECTPLANID
                                    left join dbo.INTERACTION on INTERACTION.ID = REPORTDATA.INTERACTIONID
                                    cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REPORTDATA.PROSPECTID) NF
                                    where
                                        (@ISADMIN = 1 or 
                                        (
                                            -- Prospect constituent security attributes

                                            (
                                                @APPUSER_IN_NONRACROLE = 1 or
                                                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, REPORTDATA.PROSPECTID, @APPUSER_IN_NOSECGROUPROLE) = 1
                                            ) and
                                            -- Prospect site security

                                            (
                                                @APPUSER_IN_NONSITEROLE = 1 or
                                                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, REPORTDATA.PROSPECTID, @APPUSER_IN_NOSITEROLE) = 1
                                            ) and
                                            -- Prospect plan site security

                                            (
                                                REPORTDATA.PROSPECTPLANID is null or
                                                @APPUSER_IN_NONSITEROLE = 1 or
                                                exists 
                                                (
                                                    select 1
                                                    from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(REPORTDATA.PROSPECTPLANID) as PLANSITE
                                                    where exists (select 1 from dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, ''0c6113cf-45e7-4543-b5a3-ce6072bcf9a6'', PLANSITE.SITEID))
                                                )
                                            ) and
                                            -- Interaction site security

                                            (
                                                REPORTDATA.INTERACTIONID is null or
                                                @APPUSER_IN_NONSITEROLE = 1 or
                                                exists 
                                                (
                                                    select 1
                                                    from dbo.UFN_SITEID_MAPFROM_INTERACTIONID(REPORTDATA.INTERACTIONID) as INTERACTIONSITE
                                                    where exists (select 1 from dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, ''0c6113cf-45e7-4543-b5a3-ce6072bcf9a6'', INTERACTIONSITE.SITEID))
                                                )
                                            )
                                        ))'

                if @REPORTTYPE = 0
                    set @SQLTOEXEC = @SQLTOEXEC + ' and REPORTDATA.EXCEPTIONTYPECODE is null '
                else
                    set @SQLTOEXEC = @SQLTOEXEC + ' and REPORTDATA.EXCEPTIONTYPECODE is not null '                

                set @SQLTOEXEC = @SQLTOEXEC + ' order by NF.NAME, PROSPECTPLAN.NAME, INTERACTION.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