USP_RECORDOPERATION_MKTACKNOWLEDGEMENTMAILINGPROCESSCLEARRESULTS

CREATE procedure dbo.[USP_RECORDOPERATION_MKTACKNOWLEDGEMENTMAILINGPROCESSCLEARRESULTS]
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;
  declare @CURRENTDATE datetime;

  begin try
    select
      @SEGMENTATIONID = [SEGMENTATIONID]
    from dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION]
    where [ACKNOWLEDGEMENTMAILINGPROCESSSTATUSID] = @ID;

    --Validate that the mailing is not currently being activated before clearing results...

    if @SEGMENTATIONID is not null and dbo.[UFN_MKTSEGMENTATION_ISACTIVATING](@SEGMENTATIONID) = 1
      raiserror('The marketing acknowledgement is currently being activated and results cannot be cleared.', 13, 1);

    if @CHANGEAGENTID is null  
      exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    set @CURRENTDATE = getdate();

    --Rollback and remove all REVENUELETTER and REVENUERECEIPT records along with the mailing if one exists...

    exec dbo.[USP_MKTACKNOWLEDGEMENTMAILINGPROCESS_ROLLBACK] @ID, @CHANGEAGENTID;

    --Set the status message to 'Results cleared' for this process status...

    update dbo.[BUSINESSPROCESSSTATUS] set 
      [STATUSCODE] = 3,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @ID;
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;