USP_EMAILLIST_REFRESHAPPEALLIST

Executes the "Appeal Email List Refresh" record operation.

Parameters

Parameter	Parameter Type	Mode	Description
@ID	int	IN	Input parameter indicating the ID of the record being updated.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the update.

Definition

Copy



create procedure dbo.USP_EMAILLIST_REFRESHAPPEALLIST
(
    @ID int,
    @CHANGEAGENTID uniqueidentifier
)
as begin

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    create table #APPEALEMAILLISTMEMBER (CONSTITUENTID uniqueidentifier, ANONYMOUSEMAILSUBSCRIBERID uniqueidentifier, EMAILADDRESS nvarchar(100) COLLATE database_default)

    delete from dbo.APPEALEMAILLISTMEMBER where EmailListID = @ID


    exec USP_EMAILLIST_PROCESSAPPEALLIST @ID

    insert into dbo.APPEALEMAILLISTMEMBER (EMAILLISTID, CONSTITUENTID, ANONYMOUSEMAILSUBSCRIBERID, EMAILADDRESS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select @ID, AEM.CONSTITUENTID, AEM.ANONYMOUSEMAILSUBSCRIBERID, AEM.EMAILADDRESS, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from #APPEALEMAILLISTMEMBER AEM

    ------------------------------------------------------------------------------------------------------------------------

    ------------------------Update the email list status--------------------------------------------------------------------

    ------------------------------------------------------------------------------------------------------------------------  

    update dbo.EmailList set
        AcquiredRecordCount = (select count(1) from dbo.APPEALEMAILLISTMEMBER where EMAILLISTID = @ID),
        AcquiredStatus = 1,
        AcquiredMsg = 'Success',
        AcquiredDate = GETUTCDATE()
    where ID = @ID

end