USP_REPORT_COAUPDATE

            CREATE procedure dbo.USP_REPORT_COAUPDATE
            (
                @BATCHID uniqueidentifier,
                @CHANGED int = null,
                @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?CONSTITUENTID=' + convert(nvarchar(36),BATCH.CONSTITUENTID) as CONSTITUENTID,
                    CONSTITUENT.NAME,
                    CONSTITUENT.LOOKUPID,
                    BATCH.ADDRESSBLOCK,
                    BATCH.CITY,
                    dbo.UFN_STATE_GETDESCRIPTION(BATCH.STATEID) as STATE,
                    BATCH.POSTCODE,
                    BATCH.STDADDRESSBLOCK,
                    BATCH.STDCITY,
                    dbo.UFN_STATE_GETDESCRIPTION(BATCH.STDSTATEID) as STDSTATE,
                    BATCH.STDPOSTCODE,
                    BATCH.COAADDRESSBLOCK,
                    BATCH.COACITY,
                    dbo.UFN_STATE_GETDESCRIPTION(BATCH.STDSTATEID) as COASTATE,
                    BATCH.COAPOSTCODE,
                    substring(dbo.UFN_ADDRESS_NCOAFOOTNOTECODE_GETDESCRIPTION(BATCH.NCOAFOOTNOTECODE),1,1) as NCOAFOOTNOTE,
                    BATCH.ADDRESSCHANGED,
                    dbo.UFN_ADDRESSTYPECODE_GETDESCRIPTION(ADDRESS.ADDRESSTYPECODEID) as ADDRESSTYPECODE,
                    COAUPDATE.DATELASTRUN,
                    DATA.UPDATEADDRESSCODE,
                    (select count(ID) from dbo.BATCHCOAUPDATE where BATCHID = @BATCHID) as ADDRESSESPROCESSED,
                    (select count(ID) from dbo.BATCHCOAUPDATE where BATCHID = @BATCHID and ADDRESSCHANGED = 1) as ADDRESSESUPDATED
                from dbo.BATCHCOAUPDATE BATCH
                inner join dbo.COAUPDATE on COAUPDATE.BATCHID = BATCH.BATCHID
                inner join dbo.DATATUNEUP DATA on DATA.ID = COAUPDATE.DATATUNEUPID
                inner join dbo.CONSTITUENT on CONSTITUENT.ID = BATCH.CONSTITUENTID
                inner join dbo.ADDRESS on ADDRESS.ID = BATCH.ADDRESSID
                where BATCH.BATCHID = @BATCHID and
                    (convert(int, ADDRESSCHANGED) = @CHANGED or @CHANGED is null)
                    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;