USP_MKTACKNOWLEDGEMENTMAILINGTEMPLATE_DELETE

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

  declare @PARAMETERSETID uniqueidentifier;

  begin try
    select
      @PARAMETERSETID = [ID]
    from dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESS]
    where [ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @ID;

    /* Check if the process is currently running and if we can delete it */
    exec dbo.[USP_BUSINESSPROCESS_PARAMETERSETCANBEDELETED] 'MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS', @PARAMETERSETID;

    /* If we are deleting a template and records have not been marked Acknowledged for runs in the template, */
    /* then clear the revenue letter records so they will be picked up in another subsequent template run.   */
    if dbo.[UFN_MKTACKNOWLEDGEMENTMAILINGTEMPLATE_UNACKNOWLEDGEDREVENUEEXISTS](@ID) = 1
      begin
        declare @STATUSID uniqueidentifier;
        declare TEMPLATESTATUSCURSOR cursor local fast_forward for
          select [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS].[ID]
          from dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS]
          inner join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESS] on [MKTACKNOWLEDGEMENTMAILINGPROCESS].[ID] = [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS].[PARAMETERSETID]
          inner join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION] on [MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION].[ACKNOWLEDGEMENTMAILINGPROCESSSTATUSID] = [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS].[ID]
          where [MKTACKNOWLEDGEMENTMAILINGPROCESS].[ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @ID
          and [MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION].[ACKNOWLEDGEDATE] is null
          union
          select distinct [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS].[ID]
          from dbo.[V_MKTREVENUELETTER]
          inner join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS] on [V_MKTREVENUELETTER].[MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID] = [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS].[ID]
          inner join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESS] on [MKTACKNOWLEDGEMENTMAILINGPROCESS].[ID] = [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS].[PARAMETERSETID]
          where [MKTACKNOWLEDGEMENTMAILINGPROCESS].[ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @ID
          and [V_MKTREVENUELETTER].[ACKNOWLEDGEDATE] is null;

        open TEMPLATESTATUSCURSOR;
        fetch next from TEMPLATESTATUSCURSOR into @STATUSID;

        while (@@FETCH_STATUS = 0)
        begin
          --Clear results for each status record that has unacknowledged revenue...

          exec dbo.[USP_RECORDOPERATION_MKTACKNOWLEDGEMENTMAILINGPROCESSCLEARRESULTS] @STATUSID, @CHANGEAGENTID;

          fetch next from TEMPLATESTATUSCURSOR into @STATUSID;
        end

        close TEMPLATESTATUSCURSOR;
        deallocate TEMPLATESTATUSCURSOR;
      end

    /* Clean up the comm prefs related to activation */
    exec dbo.[USP_BUSINESSPROCESSCOMMPREF_DELETE] '22C3D75C-A956-4BFC-A5FD-4B866BAEF509', @PARAMETERSETID, @CHANGEAGENTID;

    /* Delete the template's process */
    exec dbo.[USP_BUSINESSPROCESSINSTANCE_DELETE] '1F72BAD2-F10A-4F43-9210-AB3B5CBBB576', @PARAMETERSETID, @CHANGEAGENTID;

    /* Delete the acknowledgement mailing template */
    exec dbo.[USP_MKTACKNOWLEDGEMENTMAILINGTEMPLATE_DELETEBYID_WITHCHANGEAGENTID] @ID, @CHANGEAGENTID;
  end try

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

  return 0;