USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONTESTSEGMENT

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

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@SEGMENTIDS	nvarchar(max)	IN	Input parameter indicating the context ID for the record being added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@NAME	nvarchar(100)	IN	Name
@DESCRIPTION	nvarchar(255)	IN	Description
@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
@ASKLADDERID	uniqueidentifier	IN	Ask ladder
@PREFIXCODE	tinyint	IN	Prepend list
@SAMPLESEGMENTNAME	nvarchar(100)	IN	Sample name
@SAMPLESEGMENTCODE	nvarchar(10)	IN	Sample code
@MULTIPLETESTSEGMENTS	bit	IN	Multiple test segments
@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
@FRACTION	nvarchar(10)	IN	Fraction
@BUSINESSUNITS	xml	IN	Business units
@OVERRIDEBUSINESSUNITS	bit	IN	Override business units
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONTESTSEGMENT]
(
  @ID uniqueidentifier = null output,
  @SEGMENTIDS nvarchar(max),
  @CHANGEAGENTID uniqueidentifier = null,
  @NAME nvarchar(100),
  @DESCRIPTION nvarchar(255) = '',
  @CODE nvarchar(10) = '',
  @TESTSEGMENTCODE nvarchar(10) = '',
  @PACKAGEID uniqueidentifier,
  @PACKAGECODE nvarchar(10) = '',
  @RESPONSERATE decimal(5,2) = 5,
  @GIFTAMOUNT money,
  @SAMPLESIZE int = 5,
  @SAMPLESIZETYPECODE tinyint = 0,
  @SAMPLESIZEMETHODCODE tinyint = 0,
  @ASKLADDERID uniqueidentifier = null,
  @PREFIXCODE tinyint = 0,
  @SAMPLESEGMENTNAME nvarchar(100) = null,
  @SAMPLESEGMENTCODE nvarchar(10) = null,
  @MULTIPLETESTSEGMENTS bit = null,
  @CODEVALUEID uniqueidentifier = null,
  @TESTSEGMENTCODEVALUEID uniqueidentifier = null,
  @PACKAGECODEVALUEID uniqueidentifier = null,
  @ITEMLIST xml = null,
  @CHANNELSOURCECODE nvarchar(10) = '',
  @CHANNELSOURCECODEVALUEID uniqueidentifier = null,
  @FRACTION nvarchar(10) = '',
  @BUSINESSUNITS xml = null,
  @OVERRIDEBUSINESSUNITS bit = 0,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  declare @TESTSEGMENTID as uniqueidentifier;
  declare @SEGMENTSTABLE table([ID] uniqueidentifier);
  declare @START int;
  declare @POS int;
  declare @SEGMENTID uniqueidentifier;
  declare @SEGMENTATIONID uniqueidentifier;
  declare @MAILINGTYPECODE tinyint;
  declare @SEGMENTPACKAGEID uniqueidentifier;
  declare @SEQUENCE int;
  declare @CURRENTDATE datetime;
  declare @SEGMENTNAME nvarchar(100);
  declare @TESTSEGMENTNAME nvarchar(100);
  declare @BASECURRENCYID uniqueidentifier;
  declare @ORGANIZATIONCURRENCYEXCHANGERATEID uniqueidentifier;
  declare @ORGANIZATIONGIFTAMOUNT money;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  declare @SEGMENTEDHOUSEFILEEXISTS bit;

  begin try

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

    set @CURRENTDATE = getdate();

    /* Parse out the multiple test segment IDs */
    set @START = 0;
    select @POS = charindex(',', @SEGMENTIDS, @START)

    while (@POS <> 0)
      begin
        set @SEGMENTID = substring(@SEGMENTIDS, @START, @POS - @START);

        /* Checking to see if segmentID is already in the table */
        if not exists (select ID from @SEGMENTSTABLE where ID = @SEGMENTID)
          insert into @SEGMENTSTABLE
            select @SEGMENTID;

        set @START = @POS + 1;
        select @POS = charindex(',', @SEGMENTIDS, @START);
      end;

    if len(@SEGMENTIDS) > 0
      begin
        set @SEGMENTID = substring(@SEGMENTIDS, @START, 37);

        /* Checking to see if segmentID is already in the table */
        if not exists (select ID from @SEGMENTSTABLE where ID = @SEGMENTID) 
          insert into @SEGMENTSTABLE
            select @SEGMENTID;
      end

    declare SEGMENTCURSOR cursor local fast_forward for
      select [ID] from @SEGMENTSTABLE;

    open SEGMENTCURSOR;
    fetch next from SEGMENTCURSOR into @SEGMENTID;

    while (@@FETCH_STATUS = 0)
      begin
        select
          @SEGMENTATIONID = [MKTSEGMENTATION].[ID],
          @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
          @SEGMENTPACKAGEID = [MKTSEGMENTATIONSEGMENT].[PACKAGEID],
          @CODE = [MKTSEGMENTATIONSEGMENT].[CODE],
          @CODEVALUEID = [MKTSEGMENTATIONSEGMENT].[PARTDEFINITIONVALUESID],
          @BASECURRENCYID = [MKTSEGMENTATION].[BASECURRENCYID],
          @SEGMENTEDHOUSEFILEEXISTS = dbo.[UFN_MKTCONSTITUENTFILEIMPORT_IMPORTTABLEEXISTS]([MKTSEGMENTATION].[ID])
        from dbo.[MKTSEGMENTATIONSEGMENT]
        inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
        where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID;

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

        exec dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;

        set @TESTSEGMENTID = newid();
        set @ID = @TESTSEGMENTID;
        if @CHANGEAGENTID is null  
          exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

        set @CURRENTDATE = getdate();

        --For acknowledgement mailings, don't allow the user to change the package...

        if @MAILINGTYPECODE = 1 and @PACKAGEID <> @SEGMENTPACKAGEID
          set @PACKAGEID = @SEGMENTPACKAGEID;

        --If a segmented house file exists, then force the sample size to be 0 records...

        if @SEGMENTEDHOUSEFILEEXISTS = 1
          begin
            set @SAMPLESIZE = 0;
            set @SAMPLESIZETYPECODE = 1;    --Records

            set @SAMPLESIZEMETHODCODE = 0;  --nth

          end

        select @SEQUENCE = isnull(max([SEQUENCE]), 0) + 1
        from dbo.[MKTSEGMENTATIONTESTSEGMENT]
        where [SEGMENTID] = @SEGMENTID;

        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 test segment */
        insert into dbo.[MKTSEGMENTATIONTESTSEGMENT] (
          [ID],
          [SEGMENTID],
          [PREFIXCODE],
          [NAME],
          [DESCRIPTION],
          [CODE],
          [PARTDEFINITIONVALUESID],
          [TESTSEGMENTCODE],
          [TESTPARTDEFINITIONVALUESID],
          [PACKAGEID],
          [RESPONSERATE],
          [GIFTAMOUNT],
          [SAMPLESIZE],
          [SAMPLESIZETYPECODE],
          [SAMPLESIZEMETHODCODE],
          [ASKLADDERID],
          [SEQUENCE],
          [FRACTION],
          [OVERRIDEBUSINESSUNITS],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED],
          [BASECURRENCYID],
          [ORGANIZATIONGIFTAMOUNT],
          [ORGANIZATIONCURRENCYEXCHANGERATEID]
        ) values (
          @TESTSEGMENTID,
          @SEGMENTID,
          @PREFIXCODE,
          @NAME,
          @DESCRIPTION,
          @CODE,
          @CODEVALUEID,
          @TESTSEGMENTCODE,
          @TESTSEGMENTCODEVALUEID,
          @PACKAGEID,
          @RESPONSERATE,
          @GIFTAMOUNT,
          @SAMPLESIZE,
          @SAMPLESIZETYPECODE,
          @SAMPLESIZEMETHODCODE,
          @ASKLADDERID,
          @SEQUENCE,
          case when @SAMPLESIZETYPECODE = 2 then @FRACTION else '' end,
          @OVERRIDEBUSINESSUNITS,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE,
          @BASECURRENCYID,
          @ORGANIZATIONGIFTAMOUNT,
          @ORGANIZATIONCURRENCYEXCHANGERATEID
        );

        -- business units

        exec dbo.[USP_MKTSEGMENTATIONTESTSEGMENTBUSINESSUNIT_GETBUSINESSUNITS_ADDFROMXML] @TESTSEGMENTID, @BUSINESSUNITS, @CHANGEAGENTID;   

        -- clear the cached information for the parent segment to force mailing data recalculation

        exec dbo.[USP_MKTSEGMENTATIONSEGMENT_CLEARCACHE] @SEGMENTID, 0, 1;

        /* 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;

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

        /* insert new ids into item list for each test segment */
        if @ITEMLIST is not null
          begin
            declare @ITEMLISTTEMP table (
              [CODE] nvarchar(10),
              [CODEREGEX] nvarchar(max),
              [ID] uniqueidentifier,
              [PARTDEFINITIONID] uniqueidentifier,
              [PARTDEFINITIONNAME] nvarchar(100),
              [PARTDEFINITIONVALUESID] uniqueidentifier,
              [SEGMENTATIONID] uniqueidentifier,
              [SEQUENCE] integer,
              [SOURCECODEITEMID] uniqueidentifier,
              [SOURCECODEPARTTYPE] tinyint
            );

            /* insert unique MKTSOURCECODEPART ID for each value in the itemlist */ 
            insert into @ITEMLISTTEMP
              select 
                [CODE],
                [CODEREGEX],
                newid(),
                [PARTDEFINITIONID],
                [PARTDEFINITIONNAME],
                [PARTDEFINITIONVALUESID],
                [SEGMENTATIONID],
                [SEQUENCE],
                [SOURCECODEITEMID],
                [SOURCECODEPARTTYPE]
              from dbo.[UFN_MKTSOURCECODEPART_SEGMENTATIONTESTSEGMENT_GETITEMLIST3_FROMITEMLISTXML](@ITEMLIST);

            set @ITEMLIST = (select
                               [ID],
                               [SOURCECODEPARTTYPE],
                               [SOURCECODEITEMID],
                               [PARTDEFINITIONID],
                               [PARTDEFINITIONNAME],
                               [PARTDEFINITIONVALUESID],
                               [CODE],
                               [CODEREGEX],
                               [SEQUENCE],
                               [SEGMENTATIONID]
                             from @ITEMLISTTEMP
                             for xml raw('ITEM'), type, elements, root('ITEMLIST'), binary base64);

            delete from @ITEMLISTTEMP;
          end

        /* Save the source code information */
        exec dbo.[USP_MKTSOURCECODEPART_SEGMENTATIONTESTSEGMENT_GETITEMLIST3_UPDATEFROMXML] @TESTSEGMENTID, @ITEMLIST, @CHANGEAGENTID, @CURRENTDATE;

        fetch next from SEGMENTCURSOR into @SEGMENTID;
      end;

    close SEGMENTCURSOR;
    deallocate SEGMENTCURSOR;

  end try

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

  return 0;