USP_DATAFORMTEMPLATE_ADD_MKTTESTSEGMENTATION

The save procedure used by the add dataform template "Test Marketing Effort 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.
@SEGMENTATIONID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@NAME	nvarchar(100)	IN	Name
@DESCRIPTION	nvarchar(255)	IN	Description
@CODE	nvarchar(10)	IN	Code
@SAMPLESIZE	int	IN	Sample size
@SAMPLESIZETYPECODE	tinyint	IN	Sample size type
@SOURCECODEID	uniqueidentifier	IN	Source code
@ITEMLIST	xml	IN	Items
@MAILDATE	datetime	IN	Date
@HOUSEHOLDINGTYPECODE	tinyint	IN	Include
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@EXCLUSIONDATETYPECODE	tinyint	IN	Consider exclusions as of
@EXCLUSIONASOFDATE	datetime	IN	Consider exclusions as of
@EXCLUDEDECEASED	bit	IN	Exclude deceased constituents
@EXCLUDEINACTIVE	bit	IN	Exclude inactive constituents
@EXCLUSIONS	xml	IN	Exclusions
@USEADDRESSPROCESSING	bit	IN	Use address processing?
@ADDRESSPROCESSINGOPTIONID	uniqueidentifier	IN	Address processing options
@NAMEFORMATPARAMETERID	uniqueidentifier	IN	Name format options
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE	tinyint	IN	Consider seasonal addresses as of
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE	datetime	IN	Consider seasonal addresses as of
@ACTIVATIONKPIS	xml	IN	Activation KPIs
@USEKPISASDEFAULT	bit	IN	Use the chosen KPIs as the default for future marketing efforts
@APPEALINFORMATION	xml	IN	Appeal information including the appeal searchlist and record source.
@RUNACTIVATEANDEXPORT	bit	IN	Export marketing effort when activation completes
@EXPORTDESCRIPTION	nvarchar(255)	IN	Export description
@MAILEXPORTDEFINITIONID	uniqueidentifier	IN	Mail export definition
@EMAILEXPORTDEFINITIONID	uniqueidentifier	IN	Email export definition
@PHONEEXPORTDEFINITIONID	uniqueidentifier	IN	Phone export definition
@HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD	bit	IN	Also include qualifying individuals who are not members of any household
@HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS	bit	IN	Also include qualifying households which do not have any members
@HOUSEHOLDINGONERECORDPERHOUSEHOLD	bit	IN	Send to one person per household
@CODEVALUEID	uniqueidentifier	IN	Code value ID
@RUNSEGMENTATIONSEGMENTREFRESHPROCESS	bit	IN	Refresh segment selections and filters
@CACHESOURCEANALYSISRULEDATA	bit	IN	Capture source analysis rule data
@OVERRIDEBUSINESSUNITS	bit	IN	Override appeal business units
@BUSINESSUNITS	xml	IN	Business units
@RUNMARKETINGEXCLUSIONSREPORT	bit	IN

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_MKTTESTSEGMENTATION]
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,    
  @SEGMENTATIONID uniqueidentifier,
  @NAME nvarchar(100),
  @DESCRIPTION nvarchar(255) = '',
  @CODE nvarchar(10) = '',
  @SAMPLESIZE int = 5,
  @SAMPLESIZETYPECODE tinyint = 0,
  @SOURCECODEID uniqueidentifier = null,
  @ITEMLIST xml = null,
  @MAILDATE datetime = null,
  @HOUSEHOLDINGTYPECODE tinyint = 0,
  @CURRENTAPPUSERID uniqueidentifier,
  @EXCLUSIONDATETYPECODE tinyint = 0,
  @EXCLUSIONASOFDATE datetime = null,
  @EXCLUDEDECEASED bit = 1,
  @EXCLUDEINACTIVE bit = 1,
  @EXCLUSIONS xml = null,
  @USEADDRESSPROCESSING bit = 0,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null,
  @NAMEFORMATPARAMETERID uniqueidentifier = null,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint = 0,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime = null,
  @ACTIVATIONKPIS xml = null,
  @USEKPISASDEFAULT bit = 0,
  @APPEALINFORMATION xml = null,
  @RUNACTIVATEANDEXPORT bit = 0,
  @EXPORTDESCRIPTION nvarchar(255) = null,
  @MAILEXPORTDEFINITIONID uniqueidentifier = null,
  @EMAILEXPORTDEFINITIONID uniqueidentifier = null,
  @PHONEEXPORTDEFINITIONID uniqueidentifier = null,
  @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD bit = 0,
  @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS bit = 0,
  @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit = 0,
  @CODEVALUEID uniqueidentifier = null,
  @RUNSEGMENTATIONSEGMENTREFRESHPROCESS bit = 0,
  @CACHESOURCEANALYSISRULEDATA bit = 1,
  @OVERRIDEBUSINESSUNITS bit = 0,
  @BUSINESSUNITS xml = null,
  @RUNMARKETINGEXCLUSIONSREPORT bit = 1
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @PARENTID uniqueidentifier;
  declare @OLDHOUSEHOLDINGTYPECODE bit;
  declare @OLDHOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD bit;
  declare @OLDHOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS bit;
  declare @OLDHOUSEHOLDINGONERECORDPERHOUSEHOLD bit;
  declare @OLDUSEADDRESSPROCESSING bit;
  declare @OLDADDRESSPROCESSINGOPTIONID uniqueidentifier;
  declare @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint;
  declare @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime;
  declare @OLDEXCLUSIONDATETYPECODE as tinyint;
  declare @OLDEXCLUSIONASOFDATE as datetime;
  declare @OLDEXCLUDEDECEASED as bit;
  declare @OLDEXCLUDEINACTIVE as bit;
  declare @OLDEXCLUSIONS as xml;
  declare @OLDRUNSEGMENTATIONSEGMENTREFRESHPROCESS as bit;
  declare @OLDCACHESOURCEANALYSISRULEDATA as bit;
  declare @MKTSEGMENTATIONACTIVATEPROCESSID uniqueidentifier;
  declare @ADDRESSPROCESSINGOPTIONSCHANGED bit;
  declare @EXCLUSIONSCHANGED bit;
  declare @BASECURRENCYID uniqueidentifier;
  declare @OLDRUNMARKETINGEXCLUSIONSREPORT as bit;

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

    exec dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;

    set @CURRENTDATE = GetDate();

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

    select
      @PARENTID = [MKTSEGMENTATION].[ID],
      @OLDHOUSEHOLDINGTYPECODE = [MKTSEGMENTATION].[HOUSEHOLDINGTYPECODE],
      @OLDHOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = [MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD],
      @OLDHOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = [MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS],
      @OLDHOUSEHOLDINGONERECORDPERHOUSEHOLD = [MKTSEGMENTATION].[HOUSEHOLDINGONERECORDPERHOUSEHOLD],
      @OLDUSEADDRESSPROCESSING = [MKTSEGMENTATION].[USEADDRESSPROCESSING],
      @OLDADDRESSPROCESSINGOPTIONID = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONID],
      @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
      @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATE = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
      @OLDEXCLUSIONDATETYPECODE = [BUSINESSPROCESSCOMMPREF].[DATETYPECODE],
      @OLDEXCLUSIONASOFDATE = [BUSINESSPROCESSCOMMPREF].[ASOFDATE],
      @OLDEXCLUDEDECEASED = [BUSINESSPROCESSCOMMPREF].[EXCLUDEDECEASED],
      @OLDEXCLUDEINACTIVE = [BUSINESSPROCESSCOMMPREF].[EXCLUDEINACTIVE],
      @OLDEXCLUSIONS = dbo.[UFN_BUSINESSPROCESSCOMMPREF_GETEXCLUSIONS_TOITEMLISTXML]([MKTSEGMENTATIONACTIVATEPROCESS].[ID]),
      @OLDRUNSEGMENTATIONSEGMENTREFRESHPROCESS = [MKTSEGMENTATIONACTIVATEPROCESS].[RUNSEGMENTATIONSEGMENTREFRESHPROCESS],
      @OLDCACHESOURCEANALYSISRULEDATA = [MKTSEGMENTATIONACTIVATEPROCESS].[CACHESOURCEANALYSISRULEDATA],
      @BASECURRENCYID = [MKTSEGMENTATION].[BASECURRENCYID]
    from dbo.[MKTSEGMENTATION]
    inner join dbo.[MKTSEGMENTATIONACTIVATEPROCESS] on [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    left join dbo.[BUSINESSPROCESSCOMMPREF] on [BUSINESSPROCESSCOMMPREF].[BUSINESSPROCESSPARAMETERSETID] = [MKTSEGMENTATIONACTIVATEPROCESS].[ID]
    where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;

    set @ADDRESSPROCESSINGOPTIONSCHANGED = dbo.[UFN_MKTSEGMENTATION_COMPAREADDRESSPROCESSINGOPTIONS](@USEADDRESSPROCESSING, @ADDRESSPROCESSINGOPTIONID, @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE, @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE, @OLDUSEADDRESSPROCESSING, @OLDADDRESSPROCESSINGOPTIONID, @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE, @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATE);
    set @EXCLUSIONSCHANGED = dbo.[UFN_MKTSEGMENTATION_COMPARECOMMPREFS](@EXCLUSIONDATETYPECODE, @EXCLUSIONASOFDATE, @EXCLUDEDECEASED, @EXCLUDEINACTIVE, @EXCLUSIONS, @OLDEXCLUSIONDATETYPECODE, @OLDEXCLUSIONASOFDATE, @OLDEXCLUDEDECEASED, @OLDEXCLUDEINACTIVE, @OLDEXCLUSIONS);

    --@SEGMENTATIONID goes in as the SOURCE SEGMENTATION ID, and comes out as the COPY's ID

    exec dbo.[USP_MKTSEGMENTATION_COPY_HELPER] 
      @SEGMENTATIONID output, 
      @CURRENTAPPUSERID, 
      @CHANGEAGENTID, 
      0,
      null,
      '{0} - Copy{1}';

    set @ID = @SEGMENTATIONID;

    --Copy in any test mailings as exclusions on the parent mailing...

    declare @PREVIOUSSEGMENTATIONID uniqueidentifier;
    declare EXCEPTIONCURSOR cursor local fast_forward for
      select [ID]
      from [MKTSEGMENTATION]
      where [PARENTSEGMENTATIONID] = @PARENTID
      and [ACTIVE] = 1;

    open EXCEPTIONCURSOR;
    fetch next from EXCEPTIONCURSOR into @PREVIOUSSEGMENTATIONID;

    while (@@FETCH_STATUS = 0)
    begin
      if not exists(select [ID] from dbo.[MKTSEGMENTATIONFILTERSEGMENTATION] where [SEGMENTATIONID] = @SEGMENTATIONID and [PREVIOUSSEGMENTATIONID] = @PREVIOUSSEGMENTATIONID)
        exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONFILTERSEGMENTATION] null, @CHANGEAGENTID, @SEGMENTATIONID, @PREVIOUSSEGMENTATIONID;

      fetch next from EXCEPTIONCURSOR into @PREVIOUSSEGMENTATIONID;
    end;

    close EXCEPTIONCURSOR;
    deallocate EXCEPTIONCURSOR;

    --Set the fields for the test mailing that the user may have changed...

    update dbo.[MKTSEGMENTATION] set
      [NAME] = @NAME,
      [DESCRIPTION] = @DESCRIPTION,
      [CODE] = @CODE,
      [PARTDEFINITIONVALUESID] = @CODEVALUEID,
      [SOURCECODEID] = @SOURCECODEID,
      [MAILDATE] = @MAILDATE,
      [SAMPLESIZE] = @SAMPLESIZE,
      [SAMPLESIZETYPECODE] = @SAMPLESIZETYPECODE,
      [HOUSEHOLDINGTYPECODE] = @HOUSEHOLDINGTYPECODE,
      [HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD] = @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD,
      [HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS] = @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS,
      [HOUSEHOLDINGONERECORDPERHOUSEHOLD] = @HOUSEHOLDINGONERECORDPERHOUSEHOLD,
      [USEADDRESSPROCESSING] = @USEADDRESSPROCESSING,
      [ADDRESSPROCESSINGOPTIONID] = @ADDRESSPROCESSINGOPTIONID,
      [NAMEFORMATPARAMETERID] = @NAMEFORMATPARAMETERID,
      [ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE] = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE,
    [ADDRESSPROCESSINGOPTIONSEASONALASOFDATE] = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE,
      [RUNACTIVATEANDEXPORT] = @RUNACTIVATEANDEXPORT,
      [PARENTSEGMENTATIONID] = @PARENTID,
      [OVERRIDEBUSINESSUNITS] = @OVERRIDEBUSINESSUNITS,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE,
      [BASECURRENCYID] = @BASECURRENCYID
    where [ID] = @SEGMENTATIONID;

    exec dbo.USP_MKTSEGMENTATIONBUSINESSUNIT_GETBUSINESSUNITS_UPDATEFROMXML @ID, @BUSINESSUNITS, @CHANGEAGENTID;  

    select @MKTSEGMENTATIONACTIVATEPROCESSID = [ID]
    from dbo.[MKTSEGMENTATIONACTIVATEPROCESS]
    where [SEGMENTATIONID] = @SEGMENTATIONID;

    if (@RUNSEGMENTATIONSEGMENTREFRESHPROCESS <> @OLDRUNSEGMENTATIONSEGMENTREFRESHPROCESS) or 
       (@CACHESOURCEANALYSISRULEDATA <> @OLDCACHESOURCEANALYSISRULEDATA) or
       (@RUNMARKETINGEXCLUSIONSREPORT <> @OLDRUNMARKETINGEXCLUSIONSREPORT)
      exec dbo.[USP_MKTSEGMENTATIONACTIVATEPROCESS_SAVE] @SEGMENTATIONID, @CHANGEAGENTID, 1, @RUNSEGMENTATIONSEGMENTREFRESHPROCESS, @CACHESOURCEANALYSISRULEDATA, @RUNMARKETINGEXCLUSIONSREPORT;

    if @EXCLUSIONSCHANGED = 1
      begin
        --Update the contact rules/communication preferences for the mailing activation business process...

        exec dbo.[USP_BUSINESSPROCESSCOMMPREF_UPDATE]
          @CHANGEAGENTID = @CHANGEAGENTID,
          @BUSINESSPROCESSCATALOGID = '22C3D75C-A956-4BFC-A5FD-4B866BAEF509',
          @BUSINESSPROCESSPARAMETERSETID = @MKTSEGMENTATIONACTIVATEPROCESSID,
          @EXCLUSIONDATETYPECODE = @EXCLUSIONDATETYPECODE,
          @EXCLUSIONASOFDATE = @EXCLUSIONASOFDATE,
          @EXCLUDEDECEASED = @EXCLUDEDECEASED,
          @EXCLUDEINACTIVE = @EXCLUDEINACTIVE,
          @EXCLUSIONS = @EXCLUSIONS,
          @CURRENTAPPUSERID = @CURRENTAPPUSERID;
      end;

    --Set all the segments to the correct sample size (only for non-vendormanaged segments)...

    update dbo.[MKTSEGMENTATIONSEGMENT] set
      [SAMPLESIZE] = @SAMPLESIZE,
      [SAMPLESIZETYPECODE] = @SAMPLESIZETYPECODE,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    from dbo.[MKTSEGMENTATIONSEGMENT] as [SS]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [SS].[SEGMENTID]
    where [SS].[SEGMENTATIONID] = @SEGMENTATIONID
    and ([MKTSEGMENT].[SEGMENTTYPECODE] <> 2 or ([MKTSEGMENT].[SEGMENTTYPECODE] = 2 and dbo.[UFN_MKTSEGMENT_ISVENDORMANAGEDLIST]([MKTSEGMENT].[ID]) = 0));

    -- BTR CR287449-110707 11/20/2007

    -- respect sample size when creating test mailings for mailings with vendor managed list segments

    declare @SEGMENTATIONSEGMENTID uniqueidentifier;
    declare @SEGMENTID uniqueidentifier;
    declare @RENTALQUANTITY integer;
    declare @EXCHANGEQUANTITY integer;
    declare @BASERENTALCOST money;
    declare @BASERENTALCOSTBASISCODE tinyint;
    declare @BASEEXCHANGECOST money;
    declare @BASEEXCHANGECOSTBASISCODE tinyint;
    declare @RENTALCOSTADJUSTMENT money;
    declare @RENTALCOSTADJUSTMENTBASISCODE tinyint;
    declare @EXCHANGECOSTADJUSTMENT money;
    declare @EXCHANGECOSTADJUSTMENTBASISCODE tinyint;

    declare SEGMENTCURSOR cursor local fast_forward for
      select [MKTSEGMENTATIONSEGMENT].[ID], [MKTSEGMENT].[ID]
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
      and [MKTSEGMENT].[SEGMENTTYPECODE] = 2
      and dbo.[UFN_MKTSEGMENT_ISVENDORMANAGEDLIST]([MKTSEGMENT].[ID]) = 1;

    open SEGMENTCURSOR;
    fetch next from SEGMENTCURSOR into @SEGMENTATIONSEGMENTID, @SEGMENTID;

    while (@@FETCH_STATUS = 0)
    begin
      if exists(select top 1 1 from dbo.[MKTSEGMENTATIONSEGMENTLIST] where [ID] = @SEGMENTATIONSEGMENTID)
        begin
          select
            @RENTALQUANTITY = [RENTALQUANTITY],
            @EXCHANGEQUANTITY = [EXCHANGEQUANTITY]
          from dbo.[MKTSEGMENTATIONSEGMENTLIST]
          where [ID] = @SEGMENTATIONSEGMENTID;

          update dbo.[MKTSEGMENTATIONSEGMENTLIST] set
            [OVERRIDEQUANTITIESANDORSEGMENTCOSTS] = 1,
            [RENTALQUANTITY] = (case when @SAMPLESIZETYPECODE = 0 then -- percent

                                  floor(cast(@RENTALQUANTITY as decimal(20,5)) * (cast(@SAMPLESIZE as decimal(20,5)) / 100))
                                else -- records

                                  (case when @SAMPLESIZE < @RENTALQUANTITY then @SAMPLESIZE else @RENTALQUANTITY end)
                                end),
            [EXCHANGEQUANTITY] = (case when @SAMPLESIZETYPECODE = 0 then -- percent

                                    floor(cast(@EXCHANGEQUANTITY as decimal(20,5)) * (cast(@SAMPLESIZE as decimal(20,5)) / 100))
                                  else -- records

                                    (case when @SAMPLESIZE < @EXCHANGEQUANTITY then @SAMPLESIZE else @EXCHANGEQUANTITY end)
                                  end),
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
          where [ID] = @SEGMENTATIONSEGMENTID;
        end;
      else
        begin
          select
            @RENTALQUANTITY = [MKTSEGMENTLIST].[RENTALQUANTITY], 
            @EXCHANGEQUANTITY = [MKTSEGMENTLIST].[EXCHANGEQUANTITY],
            @BASERENTALCOST = [MKTLIST].[BASERENTALCOST],
            @BASERENTALCOSTBASISCODE = [MKTLIST].[BASERENTALCOSTBASISCODE],
            @BASEEXCHANGECOST = [MKTLIST].[BASEEXCHANGECOST],
            @BASEEXCHANGECOSTBASISCODE = [MKTLIST].[BASEEXCHANGECOSTBASISCODE],
            @RENTALCOSTADJUSTMENT = [MKTSEGMENTLIST].[RENTALCOSTADJUSTMENT],
            @RENTALCOSTADJUSTMENTBASISCODE = [MKTSEGMENTLIST].[RENTALCOSTBASISCODE],
            @EXCHANGECOSTADJUSTMENT = [MKTSEGMENTLIST].[EXCHANGECOSTADJUSTMENT],
            @EXCHANGECOSTADJUSTMENTBASISCODE = [MKTSEGMENTLIST].[EXCHANGECOSTBASISCODE]
          from dbo.[MKTSEGMENT]
          inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
          inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
          where [MKTSEGMENT].[ID] = @SEGMENTID;

          insert into dbo.[MKTSEGMENTATIONSEGMENTLIST] (
            [ID],
            [OVERRIDEQUANTITIESANDORSEGMENTCOSTS],
            [OVERRIDELISTCOSTS],
            [BASERENTALCOST],
            [BASERENTALCOSTBASISCODE],
            [RENTALQUANTITY],
            [RENTALCOSTADJUSTMENT],
            [RENTALCOSTADJUSTMENTBASISCODE],
            [BASEEXCHANGECOST],
            [BASEEXCHANGECOSTBASISCODE],
            [EXCHANGEQUANTITY],
            [EXCHANGECOSTADJUSTMENT],
            [EXCHANGECOSTADJUSTMENTBASISCODE],
            [ADDEDBYID],
            [CHANGEDBYID],
            [DATEADDED],
            [DATECHANGED]
          ) values (
            @SEGMENTATIONSEGMENTID,
            1,
            0,
            @BASERENTALCOST,
            @BASERENTALCOSTBASISCODE,
            (case when @SAMPLESIZETYPECODE = 0 then 
              floor(cast(@RENTALQUANTITY as decimal(20,5)) * (cast(@SAMPLESIZE as decimal(20,5)) / 100))
            else
              (case when @SAMPLESIZE < @RENTALQUANTITY then @SAMPLESIZE else @RENTALQUANTITY end)
            end),
            @RENTALCOSTADJUSTMENT,
            @RENTALCOSTADJUSTMENTBASISCODE,
            @BASEEXCHANGECOST,
            @BASEEXCHANGECOSTBASISCODE,
            (case when @SAMPLESIZETYPECODE = 0 then 
              floor(cast(@EXCHANGEQUANTITY as decimal(20,5)) * (cast(@SAMPLESIZE as decimal(20,5)) / 100))
            else
              (case when @SAMPLESIZE < @EXCHANGEQUANTITY then @SAMPLESIZE else @EXCHANGEQUANTITY end)
            end),
            @EXCHANGECOSTADJUSTMENT,
            @EXCHANGECOSTADJUSTMENTBASISCODE,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
          );
        end;

      fetch next from SEGMENTCURSOR into @SEGMENTATIONSEGMENTID, @SEGMENTID;
    end;

    --Save the source code information that the user may have changed...

    exec dbo.[USP_MKTSOURCECODEPART_GETITEMLIST2_UPDATEFROMXML] @SEGMENTATIONID, @ITEMLIST, @CHANGEAGENTID, @CURRENTDATE;

    /* Save KPIs */   
    exec dbo.[USP_MKTSEGMENTATIONACTIVATEKPI_SAVEFIELD_FROMXML] 
      @ID, 
      @ACTIVATIONKPIS, 
      @USEKPISASDEFAULT, 
      @CHANGEAGENTID,
      @CURRENTAPPUSERID;

    /* Save the appeal information */
    exec dbo.[USP_MKTSEGMENTATIONACTIVATE_SAVEFIELD_FROMXML]
      @ID,
      @APPEALINFORMATION,
      @CHANGEAGENTID;

    /* Update the export information */
    update dbo.[MKTSEGMENTATIONEXPORTPROCESS] set 
      [DESCRIPTION] = @EXPORTDESCRIPTION,
      [MAILEXPORTDEFINITIONID] = @MAILEXPORTDEFINITIONID,
      [EMAILEXPORTDEFINITIONID] = @EMAILEXPORTDEFINITIONID,
      [PHONEEXPORTDEFINITIONID] = @PHONEEXPORTDEFINITIONID,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [SEGMENTATIONID] = @SEGMENTATIONID;

    if (select [MAILINGTYPECODE] from dbo.[MKTSEGMENTATION] where [ID] = @ID)  = 3
      begin
        /* For sponsorship efforts, save a link to the business process. */
        declare @SPONSORSHIPMAILINGPROCESSSTATUSID uniqueidentifier;

        select 
          @SPONSORSHIPMAILINGPROCESSSTATUSID = [MKTSPONSORSHIPMAILINGPROCESSSEGMENTATION].[SPONSORSHIPMAILINGPROCESSSTATUSID]
        from dbo.[MKTSEGMENTATION]
        inner join dbo.[MKTSPONSORSHIPMAILINGPROCESSSEGMENTATION] on [MKTSPONSORSHIPMAILINGPROCESSSEGMENTATION].[SEGMENTATIONID] = [MKTSEGMENTATION].[PARENTSEGMENTATIONID]
        where [MKTSEGMENTATION].[ID] = @ID;

        insert into dbo.[MKTSPONSORSHIPMAILINGPROCESSSEGMENTATION] (
          [ID],
          [SPONSORSHIPMAILINGPROCESSSTATUSID],
          [SEGMENTATIONID],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        ) values (
          newid(),
          @SPONSORSHIPMAILINGPROCESSSTATUSID,
          @ID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        );
      end

  end try

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

  return 0;