USP_MKTMEMBERSHIPMAILINGTEMPLATE_DELETE

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

  declare @PARAMETERSETID uniqueidentifier;

  begin try

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

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = GetDate();

    update dbo.MKTMEMBERSHIPRENEWALEFFORTMAILINGPROCESS
        set MEMBERSHIPMAILINGPROCESSSTATUSID = null,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
    from dbo.MKTMEMBERSHIPRENEWALEFFORTMAILINGPROCESS RENEWALPROCESS
        inner join dbo.MKTMEMBERSHIPMAILINGPROCESSSTATUS 
            on MKTMEMBERSHIPMAILINGPROCESSSTATUS.ID = RENEWALPROCESS.MEMBERSHIPMAILINGPROCESSSTATUSID
        inner join dbo.MKTMEMBERSHIPMAILINGPROCESS 
            on MKTMEMBERSHIPMAILINGPROCESS.ID = MKTMEMBERSHIPMAILINGPROCESSSTATUS.PARAMETERSETID 
    where MKTMEMBERSHIPMAILINGPROCESS.MEMBERSHIPMAILINGTEMPLATEID = @ID

  -- Prevent template deletion from cascading to the sole membership renewal effort process for a membership program.

  declare @REPLACEMENTMAILINGTEMPLATEID uniqueidentifier = (
    select top 1 REPLACEMENTTEMPLATEAPPEAL.MEMBERSHIPMAILINGTEMPLATEID
    from dbo.MKTMEMBERSHIPMAILINGTEMPLATEAPPEAL ORIGINALTEMPLATEAPPEAL
      inner join dbo.APPEAL ORIGINALAPPEAL on ORIGINALAPPEAL.ID = ORIGINALTEMPLATEAPPEAL.APPEALSYSTEMID
      inner join dbo.APPEAL REPLACEMENTAPPEAL on REPLACEMENTAPPEAL.MEMBERSHIPPROGRAMID = ORIGINALAPPEAL.MEMBERSHIPPROGRAMID
      inner join dbo.MKTMEMBERSHIPMAILINGTEMPLATEAPPEAL REPLACEMENTTEMPLATEAPPEAL on (
        REPLACEMENTTEMPLATEAPPEAL.APPEALSYSTEMID = REPLACEMENTAPPEAL.ID
          and REPLACEMENTTEMPLATEAPPEAL.MEMBERSHIPMAILINGTEMPLATEID <> @ID
      )
    where ORIGINALTEMPLATEAPPEAL.MEMBERSHIPMAILINGTEMPLATEID = @ID
  );

  if @REPLACEMENTMAILINGTEMPLATEID is not null
  begin
    update dbo.MKTMEMBERSHIPRENEWALEFFORTPROCESS
    set MEMBERSHIPMAILINGTEMPLATEID = @REPLACEMENTMAILINGTEMPLATEID,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = getdate()
    where MEMBERSHIPMAILINGTEMPLATEID = @ID;
  end

    select
      @PARAMETERSETID = [ID]
    from 
      dbo.[MKTMEMBERSHIPMAILINGPROCESS]
    where 
      [MEMBERSHIPMAILINGTEMPLATEID] = @ID;

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

    exec dbo.[USP_BUSINESSPROCESS_PARAMETERSETCANBEDELETED] 'MKTMEMBERSHIPMAILINGPROCESSSTATUS', @PARAMETERSETID;

    exec dbo.[USP_BUSINESSPROCESSINSTANCE_DELETE] '854A7703-89D1-4EF7-A7C6-B8836A887092', @PARAMETERSETID, @CHANGEAGENTID;

    exec dbo.[USP_MKTMEMBERSHIPMAILINGTEMPLATE_DELETEBYID_WITHCHANGEAGENTID] @ID, @CHANGEAGENTID;
  end try

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

  return 0;