USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENT

The save procedure used by the add dataform template "Marketing Effort Segment 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	Marketing plan brief ID
@SEGMENTID	uniqueidentifier	IN	Segment
@CODE	nvarchar(10)	IN	Code
@TESTSEGMENTCODE	nvarchar(10)	IN	Test segment
@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	Sequence
@ASKLADDERID	uniqueidentifier	IN	Ask ladder
@SAMPLESIZEEXCLUDEREMAINDER	bit	IN	Exclude remaining records from the marketing effort
@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
@CODEVALUEID	uniqueidentifier	IN	Code value ID
@TESTSEGMENTCODEVALUEID	uniqueidentifier	IN	Test segment code value ID
@PACKAGECODEVALUEID	uniqueidentifier	IN	Package code value ID
@ITEMLIST	xml	IN	Items
@CHANNELSOURCECODE	nvarchar(10)	IN	Channel source code
@CHANNELSOURCECODEVALUEID	uniqueidentifier	IN	Channel code value ID
@EXCLUDESPOUSE	bit	IN	Exclude spouse (wording)
@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

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENT]
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,
  @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE nvarchar(100),
  @MARKETINGPLANBRIEFID uniqueidentifier = null,
  @SEGMENTID uniqueidentifier,
  @CODE nvarchar(10) = '',
  @TESTSEGMENTCODE nvarchar(10) = '',
  @PACKAGEID uniqueidentifier = null,
  @PACKAGECODE nvarchar(10) = '',
  @RESPONSERATE decimal(5,2) = 5,
  @GIFTAMOUNT money,
  @SAMPLESIZE integer = 100,
  @SAMPLESIZETYPECODE tinyint = 0,
  @SAMPLESIZEMETHODCODE tinyint = 0,
  @SEQUENCE integer,
  @ASKLADDERID uniqueidentifier = null,
  @SAMPLESIZEEXCLUDEREMAINDER bit = 1,
  @OVERRIDEADDRESSPROCESSING bit = 0,
  @USEADDRESSPROCESSING bit = 0,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint = 0,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime = null,
  @NAMEFORMATPARAMETERID uniqueidentifier = null,
  @CODEVALUEID uniqueidentifier = null,
  @TESTSEGMENTCODEVALUEID uniqueidentifier = null,
  @PACKAGECODEVALUEID uniqueidentifier = null,
  @ITEMLIST xml = null,
  @CHANNELSOURCECODE nvarchar(10) = '',
  @CHANNELSOURCECODEVALUEID uniqueidentifier = null,
  @EXCLUDESPOUSE bit = 0,
  @OVERRIDEBUSINESSUNITS bit = 0,
  @BUSINESSUNITS xml = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @EXCLUDE bit = 0
)
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;

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

  declare @NEXTBRIEFSEQ int;
  declare @CURRENTDATE datetime;
  declare @BASECURRENCYID uniqueidentifier;
  declare @ORGANIZATIONCURRENCYEXCHANGERATEID uniqueidentifier;
  declare @ORGANIZATIONGIFTAMOUNT money;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;

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

    exec dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;

    if @ID is null set @ID = newid();

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

    set @CURRENTDATE = getdate();

    if @EXCLUDE = 1
      begin
        set @MARKETINGPLANBRIEFID = null;
        set @PACKAGEID = null;
        set @ASKLADDERID = null;
        set @RESPONSERATE = 0;
        set @GIFTAMOUNT = 0;
        set @SAMPLESIZE = 100;
        set @SAMPLESIZETYPECODE = 0;
        set @SAMPLESIZEMETHODCODE = 0;
        set @OVERRIDEADDRESSPROCESSING = 0;
        set @ITEMLIST = null;
        set @CODE = '';
        set @CODEVALUEID = null;
        set @PACKAGECODE = '';
        set @PACKAGECODEVALUEID = null;
        set @CHANNELSOURCECODE = '';
        set @CHANNELSOURCECODEVALUEID = null;
        set @TESTSEGMENTCODE = '';
        set @TESTSEGMENTCODEVALUEID = null;
        set @OVERRIDEBUSINESSUNITS = 0;
        set @BUSINESSUNITS = null;
      end

    if @MARKETINGPLANBRIEFID is not null
      begin
        set @NEXTBRIEFSEQ = 0;

        /* Resequence all segments after this one */
        update dbo.[MKTSEGMENTATIONSEGMENT] set
          [SEQUENCE] = [SEQUENCE] + 1,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where [SEGMENTATIONID] = @SEGMENTATIONID 
        and [SEQUENCE] >= @SEQUENCE;
      end
    else
      select @NEXTBRIEFSEQ = isnull(max([SEQUENCE]),0) + 1
      from dbo.[MKTMARKETINGPLANBRIEF]
      where [MARKETINGPLANITEMID] = (select [MARKETINGPLANITEMID] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID);

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

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

    set @ORGANIZATIONCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

    if @ORGANIZATIONCURRENCYID = @BASECURRENCYID
      set @ORGANIZATIONGIFTAMOUNT = @GIFTAMOUNT;
    else
      begin
        set @ORGANIZATIONCURRENCYEXCHANGERATEID = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null);
        set @ORGANIZATIONGIFTAMOUNT = dbo.[UFN_CURRENCY_CONVERT](@GIFTAMOUNT, @ORGANIZATIONCURRENCYEXCHANGERATEID);
      end

    /* Save the segment */
    insert into dbo.[MKTSEGMENTATIONSEGMENT] (
      [ID],
      [SEGMENTATIONID],
      [MARKETINGPLANBRIEFID],
      [SEGMENTID],
      [EXCLUDE],
      [CODE],
      [PARTDEFINITIONVALUESID],
      [TESTSEGMENTCODE],
      [TESTPARTDEFINITIONVALUESID],
      [PACKAGEID],
      [RESPONSERATE],
      [GIFTAMOUNT],
      [SAMPLESIZE],
      [SAMPLESIZETYPECODE],
      [SAMPLESIZEMETHODCODE],
      [SEQUENCE],
      [NEXTBRIEFSEQUENCE],
      [ASKLADDERID],
      [SAMPLESIZEEXCLUDEREMAINDER],
      [OVERRIDEADDRESSPROCESSING],
      [USEADDRESSPROCESSING],
      [ADDRESSPROCESSINGOPTIONID],
      [ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
      [ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
      [NAMEFORMATPARAMETERID],
      [OVERRIDEBUSINESSUNITS],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED],
      [BASECURRENCYID],
      [ORGANIZATIONGIFTAMOUNT],
      [CURRENCYEXCHANGERATEID]
    ) values (
      @ID,
      @SEGMENTATIONID,
      @MARKETINGPLANBRIEFID,
      @SEGMENTID,
      @EXCLUDE,
      @CODE,
      @CODEVALUEID,
      @TESTSEGMENTCODE,
      @TESTSEGMENTCODEVALUEID,
      @PACKAGEID,
      @RESPONSERATE,
      @GIFTAMOUNT,
      @SAMPLESIZE,
      @SAMPLESIZETYPECODE,
      @SAMPLESIZEMETHODCODE,
      @SEQUENCE,
      @NEXTBRIEFSEQ,
      @ASKLADDERID,
      @SAMPLESIZEEXCLUDEREMAINDER,
      @OVERRIDEADDRESSPROCESSING,
      case when @OVERRIDEADDRESSPROCESSING = 1 then @USEADDRESSPROCESSING else 0 end,
      case when @OVERRIDEADDRESSPROCESSING = 1 and @USEADDRESSPROCESSING = 1 then @ADDRESSPROCESSINGOPTIONID else null end,
      case when @OVERRIDEADDRESSPROCESSING = 1 and @USEADDRESSPROCESSING = 1 then @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE else 0 end,
      case when @OVERRIDEADDRESSPROCESSING = 1 and @USEADDRESSPROCESSING = 1 then @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE else null end,
      case when @OVERRIDEADDRESSPROCESSING = 1 and @USEADDRESSPROCESSING = 1 then @NAMEFORMATPARAMETERID else null end,
      @OVERRIDEBUSINESSUNITS,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE,
      @BASECURRENCYID,
      @ORGANIZATIONGIFTAMOUNT,
      @ORGANIZATIONCURRENCYEXCHANGERATEID
    );

    if @EXCLUDE = 0
      begin
        exec dbo.[USP_MKTSEGMENTATIONSEGMENTBUSINESSUNIT_GETBUSINESSUNITS_ADDFROMXML] @ID, @BUSINESSUNITS, @CHANGEAGENTID;   

        /* Save and add the package to the mailing, only if it doesn't already exist */
        if not exists(select [ID] from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = @SEGMENTATIONID and [PACKAGEID] = @PACKAGEID)
          exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONPACKAGE] null, @CHANGEAGENTID, @SEGMENTATIONID, @PACKAGEID, @BASECURRENCYID;

        /* Update the segment with the selected code */
        exec dbo.[USP_MKTSEGMENT_UPDATECODE] @SEGMENTID, @CODE, @CODEVALUEID, @CHANGEAGENTID, 0, @CURRENTAPPUSERID;

        /* Update the package with the selected code */
        exec dbo.[USP_MKTPACKAGE_UPDATECODE] @PACKAGEID, @PACKAGECODE, @PACKAGECODEVALUEID, @CHANNELSOURCECODE, @CHANNELSOURCECODEVALUEID, @CHANGEAGENTID, @CURRENTAPPUSERID;

        if @MARKETINGPLANBRIEFID is not null
          begin
            /* Clear the cache for this segment and all segments after it with the same record type */
            exec dbo.[USP_MKTSEGMENTATIONSEGMENT_CLEARCACHE] @ID, 1, 1;
          end

        /* Save the source code information */
        exec dbo.[USP_MKTSOURCECODEPART_SEGMENTATIONSEGMENT_GETITEMLIST3_UPDATEFROMXML] @ID, @ITEMLIST, @CHANGEAGENTID, @CURRENTDATE;
      end
  end try

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

  return 0;