USP_APPEALMAILING_DELETE

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

  declare @CURRENTDATE datetime = getDate();
  declare @MKTSEGMENTID uniqueidentifier;
  declare @MKTSEGMENTATIONACTIVATEPROCESSID uniqueidentifier;
  declare @COMMUNICATIONEXCLUSIONIDSETREGISTERID uniqueidentifier;

  --Grab the one and only segment in the appeal mailing...

  select
    @MKTSEGMENTID = coalesce([MKTSEGMENTATIONSEGMENT].[SEGMENTID], [APPEALMAILINGSETUP].[SEGMENTID]),
    @MKTSEGMENTATIONACTIVATEPROCESSID = [MKTSEGMENTATIONACTIVATEPROCESS].[ID],
    @COMMUNICATIONEXCLUSIONIDSETREGISTERID = [COMMUNICATIONEXCLUSIONS].[IDSETREGISTERID]
  from dbo.[APPEALMAILING]
  left join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [APPEALMAILING].[MKTSEGMENTATIONSEGMENTID]
  inner join dbo.[MKTSEGMENTATIONACTIVATEPROCESS] on [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = [APPEALMAILING].[ID]
  left join dbo.[APPEALMAILINGSETUP] on [APPEALMAILINGSETUP].[ID] = [APPEALMAILING].[ID]
  left join dbo.[COMMUNICATIONEXCLUSIONS] on [COMMUNICATIONEXCLUSIONS].[SEGMENTATIONID] = [APPEALMAILINGSETUP].[ID]
  where [APPEALMAILING].[ID] = @ID;

  --Delete the contact rules/communication preferences for the mailing activation business process...

  exec dbo.[USP_BUSINESSPROCESSCOMMPREF_DELETE] '22C3D75C-A956-4BFC-A5FD-4B866BAEF509', @MKTSEGMENTATIONACTIVATEPROCESSID, @CHANGEAGENTID;

  -- Delete any appeal mailing setup letters

  declare @LETTERID uniqueidentifier;
  declare LETTERCURSOR cursor local fast_forward for
    select
      ID
    from dbo.APPEALMAILINGSETUPLETTER
    where APPEALMAILINGSETUPID = @ID;

  open LETTERCURSOR;
  fetch next from LETTERCURSOR into @LETTERID;

  while (@@fetch_status = 0)
    begin
      exec dbo.USP_APPEALMAILINGSETUPLETTER_DELETE @LETTERID, @CHANGEAGENTID;
      fetch next from LETTERCURSOR into @LETTERID;
    end

  close LETTERCURSOR;
  deallocate LETTERCURSOR;

  --Delete the appeal mailing information...

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

  --Delete the base mailing...

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

  --Delete the auto generated base segment (have to make the segment not system defined first)...

  update dbo.[MKTSEGMENT] set
    [ISSYSTEM] = 0,
    [CHANGEDBYID] = @CHANGEAGENTID,
    [DATECHANGED] = @CURRENTDATE
  where [ID] = @MKTSEGMENTID;

  exec dbo.[USP_MKTSEGMENT_DELETE] @MKTSEGMENTID, @CHANGEAGENTID, @CURRENTAPPUSERID;

  --Delete the IDSet associated with the communication exclusions now that all segmentation records have cascaded

  if @COMMUNICATIONEXCLUSIONIDSETREGISTERID is not null
    begin
      update dbo.IDSETREGISTER set
        ISSYSTEM = 0,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where ID = @COMMUNICATIONEXCLUSIONIDSETREGISTERID;

      exec dbo.[USP_IDSETREGISTER_DELETEBYID_WITHCHANGEAGENTID] @COMMUNICATIONEXCLUSIONIDSETREGISTERID, @CHANGEAGENTID;
    end
  return 0;