USP_MKTSEGMENTATIONEXCLUSION_DELETEFROMCONSTITUENTAPPEAL

CREATE procedure dbo.[USP_MKTSEGMENTATIONEXCLUSION_DELETEFROMCONSTITUENTAPPEAL]
(
  @SEGMENTATIONEXCLUSIONID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @EXCLUSIONTABLE nvarchar(128);
  declare @SQL nvarchar(max);

  --Only process this when we are not BBDM/RE7...

  if dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0
    begin
      select
        @EXCLUSIONTABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATEEXCLUSION_MAKETABLENAME]([SEGMENTATIONID])
      from dbo.[MKTSEGMENTATIONEXCLUSION]
      where [ID] = @SEGMENTATIONEXCLUSIONID;

      --Cache current context information

      declare @CONTEXTCACHE varbinary(128);
      set @CONTEXTCACHE = CONTEXT_INFO();

      --Set CONTEXT_INFO to @CHANGEAGENTID

      if @CHANGEAGENTID is not null
        set CONTEXT_INFO @CHANGEAGENTID;

      --Bulk delete from constit appeal for this segmentation exclusion...

      set @SQL = 'delete from dbo.[CONSTITUENTAPPEAL]' + char(13) +
                 'from dbo.[CONSTITUENTAPPEAL] as [CA]' + char(13) +
                 'inner join dbo.[' + @EXCLUSIONTABLE + '] as [EXCL] on [EXCL].[FINDERNUMBER] = [CA].[FINDERNUMBER]' + char(13) +  
                 'where [EXCL].[SEGMENTATIONEXCLUSIONID] = @SEGMENTATIONEXCLUSIONID';
      exec sp_executesql @SQL, N'@SEGMENTATIONEXCLUSIONID uniqueidentifier', @SEGMENTATIONEXCLUSIONID = @SEGMENTATIONEXCLUSIONID;

      --Reset CONTEXT_INFO to previous value

      if not @CONTEXTCACHE is null
        set CONTEXT_INFO @CONTEXTCACHE;
    end

  return 0;