USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENTGROUP

The save procedure used by the add dataform template "Marketing Effort Segment Group Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE	nvarchar(100)	IN	Input parameter indicating the context ID for the record being added.
@MARKETINGPLANBRIEFID	uniqueidentifier	IN
@SEGMENTGROUPID	uniqueidentifier	IN	Segment group
@PACKAGEID	uniqueidentifier	IN	Package
@PACKAGECODE	nvarchar(10)	IN	Package code
@RESPONSERATE	decimal(5, 2)	IN	Response rate
@GIFTAMOUNT	money	IN	Gift amount
@SAMPLESIZE	int	IN	Sample size
@SAMPLESIZETYPECODE	tinyint	IN	Sample size type
@SAMPLESIZEMETHODCODE	tinyint	IN	Sample size method
@SEQUENCE	int	IN
@ASKLADDERS	xml	IN	Ask ladders
@SAMPLESIZEEXCLUDEREMAINDER	bit	IN	Exclude remaining records from the marketing effort
@TESTSEGMENTCODE	nvarchar(10)	IN	Test segment
@OVERRIDEADDRESSPROCESSING	bit	IN	Override address processing / name format rules
@USEADDRESSPROCESSING	bit	IN	Use address processing?
@ADDRESSPROCESSINGOPTIONID	uniqueidentifier	IN	Address processing options
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE	tinyint	IN	Consider seasonal addresses as of
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE	datetime	IN	Consider seasonal addresses as of
@NAMEFORMATPARAMETERID	uniqueidentifier	IN	Name format options
@PACKAGECODEVALUEID	uniqueidentifier	IN	Package code value ID
@TESTSEGMENTCODEVALUEID	uniqueidentifier	IN	Test segment code value ID
@OVERRIDEBUSINESSUNITS	bit	IN	Override business units
@BUSINESSUNITS	xml	IN	Business units
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@EXCLUDE	bit	IN	Exclude from effort but show counts
@CHANNELCODEVALUEID	uniqueidentifier	IN
@CHANNELCODE	nvarchar(10)	IN

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENTGROUP]
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,    
  @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE nvarchar(100),
  @MARKETINGPLANBRIEFID uniqueidentifier = null,
  @SEGMENTGROUPID uniqueidentifier,
  @PACKAGEID uniqueidentifier = null,
  @PACKAGECODE nvarchar(10) = '',
  @RESPONSERATE decimal(5,2) = 5,
  @GIFTAMOUNT money,
  @SAMPLESIZE int = 100,
  @SAMPLESIZETYPECODE tinyint = 0,
  @SAMPLESIZEMETHODCODE tinyint = 0,
  @SEQUENCE int,
  @ASKLADDERS xml = null,
  @SAMPLESIZEEXCLUDEREMAINDER bit = 1,
  @TESTSEGMENTCODE nvarchar(10) = '',
  @OVERRIDEADDRESSPROCESSING bit = 0,
  @USEADDRESSPROCESSING bit = 0,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint = 0,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime = null,
  @NAMEFORMATPARAMETERID uniqueidentifier = null,
  @PACKAGECODEVALUEID uniqueidentifier = null,
  @TESTSEGMENTCODEVALUEID uniqueidentifier = null,
  @OVERRIDEBUSINESSUNITS bit = 0,
  @BUSINESSUNITS xml = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @EXCLUDE bit = 0,
  @CHANNELCODEVALUEID uniqueidentifier = null,
  @CHANNELCODE nvarchar(10) = ''
)
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;
  declare @MAILINGTYPECODE tinyint;

  if charindex('|', @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE, 1) > 0
    set @SEGMENTATIONID = convert(uniqueidentifier, substring(@SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE, 1, charindex('|', @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE, 1) - 1));
  else
    set @SEGMENTATIONID = convert(uniqueidentifier, @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE);

  declare @ASKLADDERID uniqueidentifier;
  declare @SEGMENTID uniqueidentifier;
  declare @SEGMENTCODE nvarchar(10);
  declare @SEGMENTCODEVALUEID uniqueidentifier;
  declare @SEGMENTTYPECODE tinyint;
  declare @ASKLADDERTABLE table ([RECORDSOURCEID] uniqueidentifier, [ASKLADDERID] uniqueidentifier null);

  select @MAILINGTYPECODE = [MAILINGTYPECODE] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID;

  begin try
    --Check if the mailing is currently being activated...

    exec dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;

    select 
      @PACKAGECODEVALUEID = case when @PACKAGECODEVALUEID is null then [PARTDEFINITIONVALUESID] else @PACKAGECODEVALUEID end,
      @PACKAGECODE = case when len(@PACKAGECODE) = 0 then [CODE] else @PACKAGECODE end,
      @CHANNELCODEVALUEID = case when @CHANNELCODEVALUEID is null then [CHANNELPARTDEFINITIONVALUESID] else @CHANNELCODEVALUEID end,
      @CHANNELCODE = case when len(@CHANNELCODE) = 0 then [CHANNELSOURCECODE] else @CHANNELCODE end
    from dbo.[MKTPACKAGE] 
    where [ID] = @PACKAGEID;

    if @SAMPLESIZETYPECODE = 0 and @SAMPLESIZE = 100
      set @SAMPLESIZEEXCLUDEREMAINDER = 1;

    if @ASKLADDERS is not null
      begin
        insert into @ASKLADDERTABLE
          select
            T.c.value('(RECORDSOURCEID)[1]', 'uniqueidentifier') as [RECORDSOURCEID],
            T.c.value('(ASKLADDERID)[1]', 'uniqueidentifier') as [ASKLADDERID]
          from @ASKLADDERS.nodes('/ASKLADDERS/ITEM') T(c);
      end

    declare SEGMENTCURSOR cursor local fast_forward for
      select 
        [MKTGROUPSEGMENTS].[SEGMENTID], 
        [MKTSEGMENT].[CODE],
        [MKTSEGMENT].[PARTDEFINITIONVALUESID],
        [MKTSEGMENT].[SEGMENTTYPECODE], 
        (select [ASKLADDERID] from @ASKLADDERTABLE where [RECORDSOURCEID] = [MKTSEGMENT].[QUERYVIEWCATALOGID])
      from dbo.[MKTGROUPSEGMENTS]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTGROUPSEGMENTS].[SEGMENTID]
      where [MKTGROUPSEGMENTS].[SEGMENTGROUPID] = @SEGMENTGROUPID
      -- load constituent and list segments into appeal mailings, membership segments into membership mailings, sponsorship segments into sponsorship mailings

      -- (the addition of segments to acknowledgement mailings is not supported)

      and ((@MAILINGTYPECODE = 0 and [MKTSEGMENT].[SEGMENTTYPECODE] in (1, 2)) or (@MAILINGTYPECODE = 2 and [MKTSEGMENT].[SEGMENTTYPECODE] = 4) or (@MAILINGTYPECODE = 3 and [MKTSEGMENT].[SEGMENTTYPECODE] = 5))
      order by [MKTGROUPSEGMENTS].[SEQUENCE];

    open SEGMENTCURSOR;
    fetch next from SEGMENTCURSOR into @SEGMENTID, @SEGMENTCODE, @SEGMENTCODEVALUEID, @SEGMENTTYPECODE, @ASKLADDERID;

    while (@@FETCH_STATUS = 0)
      begin
        set @ID = null;

        if @SEGMENTTYPECODE in (1, 4, 5)  -- constituent / membership / sponsorship segment

          -- for the moment, the membership and sponsorship add forms just call this SP

          exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENT] 
            @ID output, 
            @CHANGEAGENTID, 
            @SEGMENTATIONID, 
            @MARKETINGPLANBRIEFID, 
            @SEGMENTID, 
            @SEGMENTCODE, 
            @TESTSEGMENTCODE, 
            @PACKAGEID, 
            @PACKAGECODE, 
            @RESPONSERATE, 
            @GIFTAMOUNT, 
            @SAMPLESIZE, 
            @SAMPLESIZETYPECODE, 
            @SAMPLESIZEMETHODCODE, 
            @SEQUENCE, 
            @ASKLADDERID, 
            @SAMPLESIZEEXCLUDEREMAINDER, 
            @OVERRIDEADDRESSPROCESSING, 
            @USEADDRESSPROCESSING, 
            @ADDRESSPROCESSINGOPTIONID, 
            @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE, 
            @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE, 
            @NAMEFORMATPARAMETERID,
            @SEGMENTCODEVALUEID,
            @TESTSEGMENTCODEVALUEID,
            @PACKAGECODEVALUEID,
            null,
            @CHANNELCODE,
            @CHANNELCODEVALUEID,
            0,
            @OVERRIDEBUSINESSUNITS,
            @BUSINESSUNITS,
            @CURRENTAPPUSERID,
            @EXCLUDE;

        else if @SEGMENTTYPECODE = 2 and @EXCLUDE = 0 -- list segment; list segments cannot be added as exclusions and thus if EXCLUDE is true

                                                      -- then the list segments in the group cannot be added (PACKAGEID will be null)

          exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENTLIST] 
            @ID output, 
            @CHANGEAGENTID, 
            @SEGMENTATIONID, 
            @SEGMENTID, 
            @MARKETINGPLANBRIEFID, 
            default, 
            @SEGMENTCODE, 
            @TESTSEGMENTCODE, 
            0, 
            default, 
            default, 
            default, 
            default, 
            default, 
            default, 
            @PACKAGEID, 
            @PACKAGECODE, 
            @RESPONSERATE, 
            @GIFTAMOUNT, 
            @SEQUENCE, 
            @ASKLADDERID,
            default,
            default,
            default,
            default,
            @SEGMENTCODEVALUEID,
            @TESTSEGMENTCODEVALUEID,
            @PACKAGECODEVALUEID,
            null,
            @CHANNELCODE,
            @CHANNELCODEVALUEID,
            default,
            default,
            @OVERRIDEBUSINESSUNITS,
            @BUSINESSUNITS,
            @CURRENTAPPUSERID;

        set @SEQUENCE = @SEQUENCE + 1;

        fetch next from SEGMENTCURSOR into @SEGMENTID, @SEGMENTCODE, @SEGMENTCODEVALUEID, @SEGMENTTYPECODE, @ASKLADDERID;
      end

    close SEGMENTCURSOR;
    deallocate SEGMENTCURSOR;

    /* Update the package with the selected code */
    exec dbo.[USP_MKTPACKAGE_UPDATECODE] @PACKAGEID, @PACKAGECODE, @PACKAGECODEVALUEID, @CHANNELCODE, @CHANNELCODEVALUEID, @CHANGEAGENTID, @CURRENTAPPUSERID;
  end try

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

  return 0;