USP_PROSPECTRESEARCHREQUESTCONSTITUENT_CANCELEDREJECTEDALERT_SEND

This procedure sends an email alert to the submitter/requester/researcher of a prospect research request if the requester/submitter/researcher is not the current application user.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN
@PROSPECTRESEARCHREQUESTID	uniqueidentifier	IN

Definition

Copy



            CREATE procedure dbo.USP_PROSPECTRESEARCHREQUESTCONSTITUENT_CANCELEDREJECTEDALERT_SEND (
                @CURRENTAPPUSERID uniqueidentifier,
                @PROSPECTRESEARCHREQUESTID uniqueidentifier
            )as begin
            set nocount on;

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

                select
                    @REQUESTEDBYAPPUSERID = REQUESTEDBYAU.[ID],
                    @SUBMITTEDBYAPPUSERID = SUBMITTEDBYAU.[ID],
                    @ASSIGNEDTOAPPUSERID = ASSIGNEDTOAU.[ID]
                from
                    dbo.[PROSPECTRESEARCHREQUESTCONSTITUENT] PRRC
                left join
                    dbo.[PROSPECTRESEARCHREQUEST] PRR on PRR.ID = PRRC.[PROSPECTRESEARCHREQUESTID]
                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] = PRRC.[ASSIGNEDTOID]
                where
                    PRRC.[ID] = @PROSPECTRESEARCHREQUESTID;

                if @REQUESTEDBYAPPUSERID is not null and @REQUESTEDBYAPPUSERID <> @CURRENTAPPUSERID and (@REQUESTEDBYAPPUSERID <> @SUBMITTEDBYAPPUSERID or @SUBMITTEDBYAPPUSERID is null) begin
                    exec dbo.USP_ALERTTYPE_SENDTOAPPUSER '4E000585-3ADE-490e-A2A6-84485FCFE020', @REQUESTEDBYAPPUSERID, @PROSPECTRESEARCHREQUESTID;
                end
                if @SUBMITTEDBYAPPUSERID is not null and @SUBMITTEDBYAPPUSERID <> @CURRENTAPPUSERID begin
                    exec dbo.USP_ALERTTYPE_SENDTOAPPUSER '4E000585-3ADE-490e-A2A6-84485FCFE020', @SUBMITTEDBYAPPUSERID, @PROSPECTRESEARCHREQUESTID;
                end
                if @ASSIGNEDTOAPPUSERID is not null and @ASSIGNEDTOAPPUSERID <> @CURRENTAPPUSERID and (@ASSIGNEDTOAPPUSERID <> @SUBMITTEDBYAPPUSERID or @SUBMITTEDBYAPPUSERID is null) and (@ASSIGNEDTOAPPUSERID <> @REQUESTEDBYAPPUSERID or @REQUESTEDBYAPPUSERID is null) begin
                    exec dbo.USP_ALERTTYPE_SENDTOAPPUSER '4E000585-3ADE-490e-A2A6-84485FCFE020', @ASSIGNEDTOAPPUSERID, @PROSPECTRESEARCHREQUESTID;
                end

                return 0;
            end