USP_REPORT_DECEASEDFINDER

CREATE procedure dbo.USP_REPORT_DECEASEDFINDER
(
  @BATCHID uniqueidentifier,
  @CHANGED int = null,
  @REPORTUSERID nvarchar(128) = null,
  @ALTREPORTUSERID nvarchar(128) = null
)
with execute as owner
as
set nocount on;

declare @CURRENTAPPUSERID uniqueidentifier;
set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);

declare @ISADMIN bit;
set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

declare @APPUSER_IN_NONRACROLE bit;
set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);

declare @APPUSER_IN_NOSECGROUPROLE bit;
set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);        

declare @APPUSER_IN_NONSITEROLE bit;
set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);

declare @APPUSER_IN_NOSITEROLE bit;
set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);

select 
  'http://www.blackbaud.com?CONSTITUENTID=' + convert(nvarchar(36),DATA.CONSTITUENTID) as CONSTITUENTID,
  NF.NAME,
  CONSTITUENT.LOOKUPID,
  DATA.ADDRESSBLOCK,
  DATA.CITY,
  dbo.UFN_STATE_GETDESCRIPTION(DATA.STATEID) as STATE,
  DATA.POSTCODE,
  DATA.DECEASEDDATE,
  DATA.DECEASEDCONFIRMATION,
  dbo.UFN_DECEASEDSOURCECODE_GETDESCRIPTION(DATA.DECEASEDSOURCECODEID) as DECEASEDSOURCE,
  DECEASEDFINDER.DATELASTRUN,
  (select count(ID) from dbo.DECEASEDFINDERGETPROCESSDATA where BATCHID = @BATCHID) as CONSTITUENTSUPDATED
from 
  dbo.DECEASEDFINDERGETPROCESSDATA DATA
inner join 
  dbo.DECEASEDFINDER 
on 
  DECEASEDFINDER.BATCHID = DATA.BATCHID
inner join 
  dbo.CONSTITUENT 
on 
  CONSTITUENT.ID = DATA.CONSTITUENTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
where 
  DATA.BATCHID = @BATCHID 
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)
    ))
order by
  NF.NAME;