USP_APPEAL_DELETE

CREATE procedure dbo.USP_APPEAL_DELETE
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier
)
as begin
  set nocount on;

  if (select count(*) from dbo.REVENUE where APPEALID = @ID) > 0
  begin
    raiserror('ERR_ASSOCIATEDREVENUE', 13, 1);
    return 0;
  end

  -- Casting ID to string because there could exist non uniqueidentifier appeal system ids in MKTSEGMENTATIONACTIVATE table.

  if (select count(*) from dbo.[MKTSEGMENTATIONACTIVATE] where [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] <> '' and [APPEALSYSTEMID] = cast(@ID as nvarchar(36))) > 0
  begin
    raiserror('ERR_ASSOCIATEDMARKETINGEFFORT', 13, 1);
    return 0;
  end

  -- Checking to see if appeal is associated with a communication template.

  if (select count(*) from dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL] where [APPEALSYSTEMID] <> '' and [APPEALSYSTEMID] = cast(@ID as nvarchar(36))) > 0
  begin
    raiserror('ERR_ASSOCIATEDCOMMUNICATIONTEMPLATE', 13, 1);
    return 0;
  end

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

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

  begin try
    declare @contextCache varbinary(128);

    --cache current context information

    set @contextCache = CONTEXT_INFO();

    --set CONTEXT_INFO to @CHANGEAGENTID

    set CONTEXT_INFO @CHANGEAGENTID;

    -- Clear related BBNC email records

    if exists(select object_id from sys.objects where type = 'U' and name = 'NETCOMMUNITYTEAMFUNDRAISEREMAIL')
      delete from dbo.NETCOMMUNITYTEAMFUNDRAISEREMAIL
      where NETCOMMUNITYTEAMFUNDRAISEREMAIL.APPEALID = @ID;

    --reset CONTEXT_INFO to previous value

    if not @contextCache is null
      set CONTEXT_INFO @contextCache;

    --Delete all the appeal mailings (if any exist)...

    if exists(select 1 from dbo.[APPEALMAILING] where [APPEALID] = @ID)
      begin
        declare @APPEALMAILINGID uniqueidentifier;
        declare APPEALMAILINGCURSOR cursor local fast_forward for
          select [ID]
          from dbo.[APPEALMAILING]
          where [APPEALID] = @ID;

        open APPEALMAILINGCURSOR;
        fetch next from APPEALMAILINGCURSOR into @APPEALMAILINGID;

        while (@@FETCH_STATUS = 0)
        begin
          exec dbo.[USP_APPEALMAILING_DELETE] @APPEALMAILINGID, @CHANGEAGENTID;
          fetch next from APPEALMAILINGCURSOR into @APPEALMAILINGID;
        end

        close APPEALMAILINGCURSOR;
        deallocate APPEALMAILINGCURSOR;
      end

  end try

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

  exec dbo.USP_APPEAL_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;

  return 0;
end