USP_APPEALMAILING_SAVECHANNELOFFERCOUNTS

Aggregates current channel offer counts for the given segmentation and associates them with a run of the segmentation activate process.

Parameters

Parameter	Parameter Type	Mode
@SEGMENTATIONID	uniqueidentifier	IN
@MKTSEGMENTATIONACTIVATEPROCESSSTATUSID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.[USP_APPEALMAILING_SAVECHANNELOFFERCOUNTS]
(
  @SEGMENTATIONID uniqueidentifier,
  @MKTSEGMENTATIONACTIVATEPROCESSSTATUSID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  begin
    if @CHANGEAGENTID is null
      exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output

    declare @CURRENTDATE datetime = getdate();

    declare @SEGMENTATIONDATATABLE as nvarchar(128) = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID)
    declare @SQL nvarchar(max);

    -- Aggregate the channel offer counts and insert into table with associated status id.

    set @SQL =
      'insert into dbo.[APPEALMAILINGACTIVATEPROCESSOFFERCOUNT]' + char(13) +
      '(' + char(13) +
      '  [ID],' + char(13) +
      '  [MKTSEGMENTATIONACTIVATEPROCESSSTATUSID],' + char(13) +
      '  [CHANNELCODE],' + char(13) +
      '  [OFFERCOUNT], ' + char(13) +
      '  [ADDEDBYID],' + char(13) +
      '  [CHANGEDBYID],' + char(13) +
      '  [DATEADDED],' + char(13) +
      '  [DATECHANGED]' + char(13) +
      ')' + char(13) +
      'select' + char(13) +
      '  newid() as ID,' + char(13) +
      '  @MKTSEGMENTATIONACTIVATEPROCESSSTATUSID as [MKTSEGMENTATIONACTIVATEPROCESSSTATUSID],' + char(13) +
      '  [MKTPACKAGE].[CHANNELCODE] as [CHANNELCODE],' + char(13) +
      '  count([SEGMENTATIONDATA].[DONORID]) as [OFFERCOUNT],' + char(13) +
      '  @CHANGEAGENTID as [ADDEDBYID],' + char(13) +
      '  @CHANGEAGENTID as [CHANGEDBYID],' + char(13) +
      '  @CURRENTDATE as [DATEADDED],' + char(13) +
      '  @CURRENTDATE as [DATECHANGED]' + char(13) +
      'from dbo.[' + @SEGMENTATIONDATATABLE + '] as [SEGMENTATIONDATA]' + char(13) +
      'inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [SEGMENTATIONDATA].[SEGMENTID]' + char(13) +
      'inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]' + char(13) +
      'where [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0' + char(13) +
      'group by [MKTPACKAGE].[CHANNELCODE];';

    exec sp_executesql @SQL, N'@MKTSEGMENTATIONACTIVATEPROCESSSTATUSID uniqueidentifier, @CHANGEAGENTID uniqueidentifier, @CURRENTDATE datetime', @MKTSEGMENTATIONACTIVATEPROCESSSTATUSID = @MKTSEGMENTATIONACTIVATEPROCESSSTATUSID, @CHANGEAGENTID = @CHANGEAGENTID, @CURRENTDATE = @CURRENTDATE;
  end