USP_REPORT_PEOPLEFINDERUPDATE

CREATE procedure dbo.USP_REPORT_PEOPLEFINDERUPDATE
  (
    @BATCHID uniqueidentifier,
        @REPORTUSERID nvarchar(128) = null,
        @ALTREPORTUSERID nvarchar(128) = null
  )

  with execute as owner
  as
    set nocount on;

    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);
    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);

    select 'http://www.blackbaud.com?CONSTITUENTID=' + convert(nvarchar(36),BATCHPEOPLEFINDER.CONSTITUENTID) as CONSTITUENTID,
      CONSTITUENT.LOOKUPID,
      CONSTITUENT.NAME,
      ADDRESS.ADDRESSBLOCK,
      ADDRESS.CITY,
      STATE.ABBREVIATION as STATE,
      ADDRESS.POSTCODE,
      BATCHPEOPLEFINDER.NEWADDRESSBLOCK,
      BATCHPEOPLEFINDER.NEWCITY,
      dbo.UFN_STATE_GETABBREVIATION(BATCHPEOPLEFINDER.NEWSTATEID) as NEWSTATE,
      BATCHPEOPLEFINDER.NEWPOSTCODE,
      BATCHPEOPLEFINDER.NEWPHONENUMBER,
      BATCHPEOPLEFINDER.ISDECEASED,
      BATCHPEOPLEFINDER.DECEASEDYEARDISPLAY as DECEASEDYEAR,
      BATCHPEOPLEFINDER.OTHERLASTNAME,
      (select COUNT(ID) from BATCHPEOPLEFINDER where OTHERLASTNAME is not null and BATCHID = @BATCHID) as LASTNAMESTOUPDATE,
      (select COUNT(ID) from BATCHPEOPLEFINDER where NEWPHONENUMBER != '' and BATCHID = @BATCHID) as PHONENUMBERSTOUPDATE,
      (select COUNT(ID) from BATCHPEOPLEFINDER where ISDECEASED = 1 and BATCHID = @BATCHID) as CONSTITUENTSDECEASED,
      (select COUNT(ID) from BATCHPEOPLEFINDER where BATCHID = @BATCHID) as ADDRESSESTOPROCESS
    from BATCHPEOPLEFINDER
    inner join CONSTITUENT on CONSTITUENT.ID = BATCHPEOPLEFINDER.CONSTITUENTID
    left join ADDRESS on Address.ID = BATCHPEOPLEFINDER.OLDADDRESSID
    left join STATE on STATE.ID = BATCHPEOPLEFINDER.NEWSTATEID
    where BATCHPEOPLEFINDER.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 CONSTITUENT.NAME;