USP_PROSPECTRESEARCHREQUEST_EDITEDALERT_SEND

            create procedure dbo.USP_PROSPECTRESEARCHREQUEST_EDITEDALERT_SEND (
                @CURRENTAPPUSERID uniqueidentifier,
                @OLDREQUESTEDBYID uniqueidentifier,
                @OLDSUBMITTEDBYID uniqueidentifier,
                @PROSPECTRESEARCHREQUESTID uniqueidentifier
            ) as begin
                set nocount on;

                declare
                    @REQUESTEDBYAPPUSERID uniqueidentifier,
                    @SUBMITTEDBYAPPUSERID uniqueidentifier,
                    @ASSIGNEDTOAPPUSERID uniqueidentifier,
                    @OLDREQUESTEDBYAPPUSERID uniqueidentifier,
                    @OLDSUBMITTEDBYAPPUSERID uniqueidentifier;

                select
                    @REQUESTEDBYAPPUSERID = REQUESTEDBYAU.[ID],
                    @SUBMITTEDBYAPPUSERID = SUBMITTEDBYAU.[ID],
                    @ASSIGNEDTOAPPUSERID = ASSIGNEDTOAU.[ID]
                from
                    dbo.[PROSPECTRESEARCHREQUEST] PRR
                left join
                    dbo.[APPUSER] REQUESTEDBYAU on REQUESTEDBYAU.[CONSTITUENTID] = PRR.[REQUESTEDBYID]
                left join
                    dbo.[APPUSER] SUBMITTEDBYAU on SUBMITTEDBYAU.[CONSTITUENTID] = PRR.[SUBMITTEDBYID]
                left join
                    dbo.[APPUSER] ASSIGNEDTOAU on ASSIGNEDTOAU.[CONSTITUENTID] = PRR.[ASSIGNEDTOID]
                where
                    PRR.[ID] = @PROSPECTRESEARCHREQUESTID;

                select
                    @OLDREQUESTEDBYAPPUSERID = AU.[ID]
                from
                    dbo.[APPUSER] AU
                where
                    AU.[CONSTITUENTID] = @OLDREQUESTEDBYID;

                select
                    @OLDSUBMITTEDBYAPPUSERID = AU.[ID]
                from
                    dbo.[APPUSER] AU
                where
                    AU.[CONSTITUENTID] = @OLDSUBMITTEDBYID;

                if @REQUESTEDBYAPPUSERID is not null and
                    (@REQUESTEDBYAPPUSERID <> @CURRENTAPPUSERID or @CURRENTAPPUSERID is null) begin
                    exec dbo.USP_ALERTTYPE_SENDTOAPPUSER 'C30B5AA0-C236-4B1D-B8CC-6B0AD2ADA791', @REQUESTEDBYAPPUSERID, @PROSPECTRESEARCHREQUESTID;
                end

                if @SUBMITTEDBYAPPUSERID is not null and
                    (@SUBMITTEDBYAPPUSERID <> @CURRENTAPPUSERID or @CURRENTAPPUSERID is null) and
                    (@SUBMITTEDBYAPPUSERID <> @REQUESTEDBYAPPUSERID or @REQUESTEDBYAPPUSERID is null) begin
                    exec dbo.USP_ALERTTYPE_SENDTOAPPUSER 'C30B5AA0-C236-4B1D-B8CC-6B0AD2ADA791', @SUBMITTEDBYAPPUSERID, @PROSPECTRESEARCHREQUESTID;
                end

                if @ASSIGNEDTOAPPUSERID is not null and
                    (@ASSIGNEDTOAPPUSERID <> @CURRENTAPPUSERID or @CURRENTAPPUSERID is null) and
                    (@ASSIGNEDTOAPPUSERID <> @REQUESTEDBYAPPUSERID or @REQUESTEDBYAPPUSERID is null) and
                    (@ASSIGNEDTOAPPUSERID <> @SUBMITTEDBYAPPUSERID or @SUBMITTEDBYAPPUSERID is null) begin
                    exec dbo.USP_ALERTTYPE_SENDTOAPPUSER 'C30B5AA0-C236-4B1D-B8CC-6B0AD2ADA791', @ASSIGNEDTOAPPUSERID, @PROSPECTRESEARCHREQUESTID;
                end

                if @OLDREQUESTEDBYAPPUSERID is not null and
                    (@OLDREQUESTEDBYAPPUSERID <> @CURRENTAPPUSERID or @CURRENTAPPUSERID is null) and
                    (@OLDREQUESTEDBYAPPUSERID <> @REQUESTEDBYAPPUSERID or @REQUESTEDBYAPPUSERID is null) and
                    (@OLDREQUESTEDBYAPPUSERID <> @SUBMITTEDBYAPPUSERID or @SUBMITTEDBYAPPUSERID is null) and
                    (@OLDREQUESTEDBYAPPUSERID <> @ASSIGNEDTOAPPUSERID or @ASSIGNEDTOAPPUSERID is null) begin
                    exec dbo.USP_ALERTTYPE_SENDTOAPPUSER 'C30B5AA0-C236-4B1D-B8CC-6B0AD2ADA791', @OLDREQUESTEDBYAPPUSERID, @PROSPECTRESEARCHREQUESTID;
                end

                if @OLDSUBMITTEDBYAPPUSERID is not null and
                    (@OLDSUBMITTEDBYAPPUSERID <> @CURRENTAPPUSERID or @CURRENTAPPUSERID is null) and
                    (@OLDSUBMITTEDBYAPPUSERID <> @REQUESTEDBYAPPUSERID or @REQUESTEDBYAPPUSERID is null) and
                    (@OLDSUBMITTEDBYAPPUSERID <> @SUBMITTEDBYAPPUSERID or @SUBMITTEDBYAPPUSERID is null) and
                    (@OLDSUBMITTEDBYAPPUSERID <> @ASSIGNEDTOAPPUSERID or @ASSIGNEDTOAPPUSERID is null) and
                    (@OLDSUBMITTEDBYAPPUSERID <> @OLDREQUESTEDBYID or @OLDREQUESTEDBYID is null) begin
                    exec dbo.USP_ALERTTYPE_SENDTOAPPUSER 'C30B5AA0-C236-4B1D-B8CC-6B0AD2ADA791', @OLDSUBMITTEDBYAPPUSERID, @PROSPECTRESEARCHREQUESTID;
                end

                return 0;
            end