USP_MKTACKNOWLEDGEMENTMAILINGPROCESS_ROLLBACK

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

  declare @RECORDSOURCEID uniqueidentifier;
  declare @REVENUELETTERTABLENAME nvarchar(128);
  declare @REVENUERECEIPTTABLENAME nvarchar(128);
  declare @SQL as nvarchar(max);
  declare @CONTEXTCACHE varbinary(128);
  declare @MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATIONID uniqueidentifier;
  declare @SEGMENTATIONID uniqueidentifier;

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


    /*******************************************************************/
    /* Remove records from the REVENUELETTER and REVENUERECEIPT tables */
    /*******************************************************************/
    --Cache current context information and set CONTEXT_INFO to @CHANGEAGENTID

    set @CONTEXTCACHE = CONTEXT_INFO();
    set CONTEXT_INFO @CHANGEAGENTID;

    --Loop through every record source and remove the records.  We have to check every record source because the

    --user could change the segments in the acknowledgement template and then the segments may no longer match

    --the records that are stored in the REVENUELETTER and REVENUERECEIPT tables.  Since we set the CONTEXT_INFO

    --above, we can bulk delete from each REVENUELETTER and REVENUERECEIPT tables.

    declare RECORDSOURCECURSOR cursor local fast_forward for
      select
        [ID],
        dbo.[UFN_MKTREVENUELETTER_MAKETABLENAME]([ID]),
        dbo.[UFN_MKTREVENUERECEIPT_MAKETABLENAME]([ID])
      from dbo.[MKTGIFTRECORDSOURCE]
      where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([ID]) = 1;

    open RECORDSOURCECURSOR;
    fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID, @REVENUELETTERTABLENAME, @REVENUERECEIPTTABLENAME;

    while (@@FETCH_STATUS = 0)
    begin
      if dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@RECORDSOURCEID) = 1
        begin
          --For BBEC, delete records from the REVENUELETTER table...

          --Since BBEC users can manually add revenue letter records, we don't want to be deleting those records, so delete everything

          --except those records and just reset the process dates and remove the matching records in the REVENUELETTERMARKETING table.

          delete from dbo.[REVENUELETTER]
          from dbo.[REVENUELETTER] as [RL]
          inner join dbo.[REVENUELETTERMARKETING] as [RLM] on [RLM].[ID] = [RL].[ID]
          where [RLM].[MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID] = @MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID
          and [RLM].[EXISTINGREVENUELETTER] = 0;

          update dbo.[REVENUELETTER] set
            [PROCESSDATE] = null,
            [ACKNOWLEDGEDATE] = null,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = getdate()
          from dbo.[REVENUELETTER] as [RL]
          inner join dbo.[REVENUELETTERMARKETING] as [RLM] on [RLM].[ID] = [RL].[ID]
          where [RLM].[MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID] = @MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID
          and [RLM].[EXISTINGREVENUELETTER] = 1;

          delete from dbo.[REVENUELETTERMARKETING]
          where [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID] = @MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID
          and [EXISTINGREVENUELETTER] = 1;

              --Get the next available receipt number as it was before the process ran based on the receipt stack used in it

          declare @NEXTNUMBER int;
          declare @RECEIPTSTACKID uniqueidentifier;
          declare @RECEIPTPROCESSDATE datetime;
          select @NEXTNUMBER = MIN(RECEIPTNUMBER), @RECEIPTSTACKID = RR.RECEIPTSTACKINFOID, @RECEIPTPROCESSDATE = RR.RECEIPTPROCESSDATE
          from dbo.REVENUERECEIPT as RR
          inner join dbo.REVENUERECEIPTMARKETING as RRM on RRM.ID = RR.ID
          where RRM.MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID = @MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID
          group by RR.RECEIPTSTACKINFOID,RR.RECEIPTPROCESSDATE;    

          --Delete records from the REVENUERECEIPT table...

          delete from dbo.[REVENUERECEIPT]
          from dbo.[REVENUERECEIPT] as [RR]
          inner join dbo.[REVENUERECEIPTMARKETING] as [RRM] on [RRM].[ID] = [RR].[ID]
          where [RRM].[MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID] = @MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID;

          -- Update the next available receipt number to what it was prior to this run

          if not exists (select ID from dbo.REVENUERECEIPT where @RECEIPTSTACKID = RECEIPTSTACKINFOID and RECEIPTPROCESSDATE > @RECEIPTPROCESSDATE)
            exec dbo.USP_REVENUE_SETNEXTRECEIPTNUMBER @RECEIPTSTACKID,@NEXTNUMBER; 

        end
      else
        begin
          --Delete records from the "MKTREVENUELETTER_<guid>" table...

          set @SQL = 'delete from dbo.[' + @REVENUELETTERTABLENAME + ']' + char(13) +
                     'where [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID] = @MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID';
          exec sp_executesql @SQL, N'@MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID uniqueidentifier', @MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID = @MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID;

          --Delete records from the "MKTREVENUERECEIPT_<guid>" table...

          set @SQL = 'delete from dbo.[' + @REVENUERECEIPTTABLENAME + ']' + char(13) +
                     'where [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID] = @MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID';
          exec sp_executesql @SQL, N'@MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID uniqueidentifier', @MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID = @MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID;
        end

      fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID, @REVENUELETTERTABLENAME, @REVENUERECEIPTTABLENAME;
    end

    close RECORDSOURCECURSOR;
    deallocate RECORDSOURCECURSOR;

    --Reset CONTEXT_INFO to previous value

    if @CONTEXTCACHE is not null
      set CONTEXT_INFO @CONTEXTCACHE;


    /******************************************/
    /* Remove the mailing, if one was created */
    /******************************************/
    select
      @MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATIONID = [ID],
      @SEGMENTATIONID = [SEGMENTATIONID]
    from dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION]
    where [ACKNOWLEDGEMENTMAILINGPROCESSSTATUSID] = @MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID;

    if @MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATIONID is not null
      begin
        exec dbo.[USP_MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION_DELETEBYID_WITHCHANGEAGENTID] @MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATIONID, @CHANGEAGENTID;

        if @SEGMENTATIONID is not null
          exec dbo.[USP_MKTSEGMENTATION_DELETE] @SEGMENTATIONID, @CHANGEAGENTID;
      end
  end try

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

  return 0;