USP_LETTERCODE_DELETE

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

    if (select count([ID]) from dbo.[ASSIGNLETTERCODESPROCESSLETTERCODES] where [LETTERCODEID] = @ID) > 0
      begin
          raiserror('This letter is being used by an assign letters process and cannot be deleted.',13,1);
          return 1;
      end

    if (select count([ID]) from dbo.[ACKNOWLEDGEMENTPROCESS] where [LETTERCODEID] = @ID) > 0
      begin
          raiserror('This letter is being used by an acknowledgement process and cannot be deleted.',13,1);
          return 1;
      end

    if (select count([ID]) from dbo.[REVENUELETTER] where [LETTERCODEID] = @ID) > 0
    begin
        raiserror('This letter is associated with a revenue record and cannot be deleted.',13,1);
        return 1;
    end

    if (select [ISSYSTEM] from dbo.[LETTERCODE] where [ID] = @ID) = 1
    begin
        raiserror('This letter is meant for system use only and cannot be deleted.',13,1);
        return 1;
    end

  -- check if the letter is in use by a marketing acknowledgement template

  if exists(select top 1 1 
            from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE]
            inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[PACKAGEID]
            where [MKTPACKAGE].[LETTERCODEID] = @ID)
    begin
      raiserror('This letter is in use by a marketing acknowledgement template and cannot be deleted.', 13, 1);
      return 1;
    end

    if (select count([ID]) from dbo.[BATCHREVENUE] where [LETTERCODEID] = @ID) > 0
      begin
          update dbo.[BATCHREVENUE] set 
              [LETTERCODEID] = null,
              [ACKNOWLEDGEDATE] = null,
              [ACKNOWLEDGEEID] = null
          where [LETTERCODEID] = @ID;                                
      end

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

    return 0;