USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONSEGMENT_MULTIPLE_7

The save procedure used by the edit dataform template "Marketing Effort Multiple Segment Edit Form 4".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	nvarchar(max)	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@ASKLADDER	xml	IN	Ask ladder
@ASKLADDERLOADED	bit	IN	Ask ladder loaded
@TESTSEGMENTCODE	nvarchar(10)	IN	Test segment
@TESTSEGMENTCODELOADED	bit	IN	Test segment code loaded
@PACKAGEID	uniqueidentifier	IN	Package
@PACKAGECODE	nvarchar(10)	IN	Package code
@PACKAGELOADED	bit	IN	Package loaded
@RESPONSERATE	decimal(5, 2)	IN	Response rate
@RESPONSERATELOADED	bit	IN	Response rate loaded
@GIFTAMOUNT	money	IN	Gift amount
@GIFTAMOUNTLOADED	bit	IN	Gift amount loaded
@SAMPLESIZE	int	IN	Sample size
@SAMPLESIZETYPECODE	tinyint	IN	Sample size type
@SAMPLESIZEMETHODCODE	tinyint	IN	Sample size method
@SAMPLESIZEEXCLUDEREMAINDER	bit	IN	Exclude remaining records from the marketing effort
@SAMPLESIZELOADED	bit	IN	Sample size loaded
@ADDRESSPROCESSINGOPTIONSLOADED	bit	IN	Address processing options loaded
@OVERRIDEADDRESSPROCESSING	bit	IN	Override address processing / name format rules
@USEADDRESSPROCESSING	bit	IN	Use address processing?
@ADDRESSPROCESSINGOPTIONID	uniqueidentifier	IN	Address processing option ID
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE	tinyint	IN	Consider seasonal addresses as of
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE	datetime	IN	Consider seasonal addresses as of
@NAMEFORMATPARAMETERID	uniqueidentifier	IN	Name format options
@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
@OVERRIDEBUSINESSUNITSLOADED	bit	IN	Override business units loaded
@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
@EXCLUDELOADED	bit	IN	Segment
Definition

Copy


CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONSEGMENT_MULTIPLE_7]
(
  @ID nvarchar(max),
  @CHANGEAGENTID uniqueidentifier = null,
  @ASKLADDER xml,
  @ASKLADDERLOADED bit,
  @TESTSEGMENTCODE nvarchar(10),
  @TESTSEGMENTCODELOADED bit,
  @PACKAGEID uniqueidentifier,
  @PACKAGECODE nvarchar(10),
  @PACKAGELOADED bit,
  @RESPONSERATE decimal(5,2),
  @RESPONSERATELOADED bit,
  @GIFTAMOUNT money,
  @GIFTAMOUNTLOADED bit,
  @SAMPLESIZE int,
  @SAMPLESIZETYPECODE tinyint,
  @SAMPLESIZEMETHODCODE tinyint,
  @SAMPLESIZEEXCLUDEREMAINDER bit,
  @SAMPLESIZELOADED bit,
  @ADDRESSPROCESSINGOPTIONSLOADED bit,
  @OVERRIDEADDRESSPROCESSING bit,
  @USEADDRESSPROCESSING bit,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime,
  @NAMEFORMATPARAMETERID uniqueidentifier,
  @TESTSEGMENTCODEVALUEID uniqueidentifier,
  @PACKAGECODEVALUEID uniqueidentifier,
  @ITEMLIST xml,
  @CHANNELSOURCECODE nvarchar(10),
  @CHANNELSOURCECODEVALUEID uniqueidentifier,
  @OVERRIDEBUSINESSUNITSLOADED bit,
  @OVERRIDEBUSINESSUNITS bit,
  @BUSINESSUNITS xml,
  @CURRENTAPPUSERID uniqueidentifier,
  @EXCLUDE bit,
  @EXCLUDELOADED bit
)
as
  set nocount on;

  declare @SEGMENTATIONSEGMENTS table([ID] uniqueidentifier);
  declare @START int;
  declare @POS int;
  declare @SEGMENTID uniqueidentifier;
  declare @SEGMENTTYPECODE tinyint;
  declare @SQL nvarchar(max);
  declare @SEGMENTATIONID uniqueidentifier;
  declare @DATATABLENAME nvarchar(128);
  declare @PACKAGECHANNELCODE tinyint;
  declare @OLDEXCLUDE bit;
  declare @OLDSEGMENTATIONPACKAGEID uniqueidentifier;
  declare @OLDPACKAGEID uniqueidentifier;
  declare @OLDPACKAGECHANNELCODE tinyint;
  declare @OLDSAMPLESIZE int;
  declare @OLDSAMPLESIZETYPECODE tinyint;
  declare @OLDSAMPLESIZEMETHODCODE tinyint;
  declare @OLDSAMPLESIZEEXCLUDEREMAINDER bit;
  declare @OLDASKLADDERID uniqueidentifier;
  declare @CURRENTDATE datetime;
  declare @OLDUSEADDRESSPROCESSING bit;
  declare @OLDADDRESSPROCESSINGOPTIONID uniqueidentifier;
  declare @SEQUENCE integer;
  declare @PACKAGECHANNELCHANGED bit;
  declare @ADDRESSPROCESSINGOPTIONSCHANGED bit;
  declare @ASKLADDERID uniqueidentifier;
  declare @BASECURRENCYID uniqueidentifier;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  declare @ORGANIZATIONGIFTAMOUNT money;
  declare @ORGANIZATIONCURRENCYEXCHANGERATEID uniqueidentifier;
  declare @TESTSEGMENTID uniqueidentifier;
  declare @ORIGINALITEMLIST xml;

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

    set @CURRENTDATE = getdate();

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

    if @EXCLUDELOADED = 1 and @EXCLUDE = 1
      begin
        set @ASKLADDERLOADED = 1;
        set @TESTSEGMENTCODELOADED = 1;
        set @PACKAGELOADED = 1;
        set @RESPONSERATELOADED = 1;
        set @GIFTAMOUNTLOADED = 1;
        set @SAMPLESIZELOADED = 1;
        set @ADDRESSPROCESSINGOPTIONSLOADED = 1;
        set @OVERRIDEBUSINESSUNITSLOADED = 1;

        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 @PACKAGECODE = '';
        set @PACKAGECODEVALUEID = null;
        set @CHANNELSOURCECODE = '';
        set @CHANNELSOURCECODEVALUEID = null;
        set @TESTSEGMENTCODE = '';
        set @TESTSEGMENTCODEVALUEID = null;
        set @OVERRIDEBUSINESSUNITS = 0;
        set @BUSINESSUNITS = null;
      end

      set @ORIGINALITEMLIST = @ITEMLIST;

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

    while (@POS <> 0)
      begin
        insert into @SEGMENTATIONSEGMENTS
          select substring(@ID, @START, @POS - @START);

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

    if len(@ID) > 0
      insert into @SEGMENTATIONSEGMENTS
        select substring(@ID, @START, 37);

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

    declare SEGMENTCURSOR cursor local fast_forward for
      select [SEG].[ID], [MKTSEGMENTATIONSEGMENT].[SEQUENCE]
      from @SEGMENTATIONSEGMENTS as [SEG]
      inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [SEG].[ID]
      order by [MKTSEGMENTATIONSEGMENT].[SEQUENCE] desc;

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

    while (@@FETCH_STATUS = 0)
      begin
        select 
          @SEGMENTATIONID = [MKTSEGMENTATION].[ID],
          @DATATABLENAME = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([MKTSEGMENTATION].[ID]),
          @SEGMENTTYPECODE = [MKTSEGMENT].[SEGMENTTYPECODE],
          @OLDEXCLUDE = [MKTSEGMENTATIONSEGMENT].[EXCLUDE],
          @PACKAGELOADED = (case when [MKTSEGMENTATION].[MAILINGTYPECODE] = 1 then 0 else @PACKAGELOADED end),
          @SAMPLESIZELOADED = (case when [MKTSEGMENTATION].[MAILINGTYPECODE] = 1 then 0 else @SAMPLESIZELOADED end),
          @OLDUSEADDRESSPROCESSING = case when [MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTSEGMENTATIONSEGMENT].[USEADDRESSPROCESSING] else [MKTSEGMENTATION].[USEADDRESSPROCESSING] end,
          @OLDADDRESSPROCESSINGOPTIONID = case when [MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTSEGMENTATIONSEGMENT].[ADDRESSPROCESSINGOPTIONID] else [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONID] end,
          @BASECURRENCYID = [MKTSEGMENTATION].[BASECURRENCYID],
          @ORGANIZATIONCURRENCYEXCHANGERATEID = [MKTSEGMENTATIONSEGMENT].[CURRENCYEXCHANGERATEID],
          @ORGANIZATIONGIFTAMOUNT = [MKTSEGMENTATIONSEGMENT].[ORGANIZATIONGIFTAMOUNT]
        from dbo.[MKTSEGMENTATIONSEGMENT]
        inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
        inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
        left outer join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
        where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID;

        /* Check if the mailing is currently being activated */
        exec dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;

        /* Grab the old packageID info before we save the segment */
        if @PACKAGELOADED = 1
          select 
            @OLDSEGMENTATIONPACKAGEID = (select [PAK].[ID] from dbo.[MKTSEGMENTATIONPACKAGE] as [PAK] where [PAK].[SEGMENTATIONID] = [SEG].[SEGMENTATIONID] and [PAK].[PACKAGEID] = [SEG].[PACKAGEID]),
            @OLDPACKAGEID = [SEG].[PACKAGEID],
            @OLDPACKAGECHANNELCODE = [MKTPACKAGE].[CHANNELCODE]
          from dbo.[MKTSEGMENTATIONSEGMENT] as [SEG]
          left outer join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [SEG].[PACKAGEID]
          where [SEG].[ID] = @SEGMENTID;

        if @SAMPLESIZELOADED = 1
          select
            @OLDSAMPLESIZE = [SAMPLESIZE],
            @OLDSAMPLESIZETYPECODE = [SAMPLESIZETYPECODE],
            @OLDSAMPLESIZEMETHODCODE = [SAMPLESIZEMETHODCODE],
            @OLDSAMPLESIZEEXCLUDEREMAINDER = [SAMPLESIZEEXCLUDEREMAINDER]
          from dbo.[MKTSEGMENTATIONSEGMENT]
          where [ID] = @SEGMENTID;

        if @ASKLADDERLOADED = 1
          begin
            select
              @OLDASKLADDERID = [ASKLADDERID]
            from dbo.[MKTSEGMENTATIONSEGMENT]
            where [ID] = @SEGMENTID;

            select
              @ASKLADDERID = T.c.value('(ASKLADDERID)[1]', 'uniqueidentifier')
            from @ASKLADDER.nodes('/ASKLADDER/ITEM') T(c);
          end

        if @ORGANIZATIONCURRENCYID = @BASECURRENCYID
          set @ORGANIZATIONGIFTAMOUNT = case when @GIFTAMOUNTLOADED = 1 then @GIFTAMOUNT else @ORGANIZATIONGIFTAMOUNT end;
        else
          begin
            if @ORGANIZATIONCURRENCYEXCHANGERATEID is null
              set @ORGANIZATIONCURRENCYEXCHANGERATEID = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null);

            if @GIFTAMOUNTLOADED = 1
              set @ORGANIZATIONGIFTAMOUNT = dbo.[UFN_CURRENCY_CONVERT](@GIFTAMOUNT, @ORGANIZATIONCURRENCYEXCHANGERATEID);
          end

        /* Save the segment */
        update dbo.[MKTSEGMENTATIONSEGMENT] set
          [EXCLUDE] = (case when @EXCLUDELOADED = 1 then @EXCLUDE else [EXCLUDE] end),
          [ASKLADDERID] = (case when @ASKLADDERLOADED = 1 then case when (@EXCLUDELOADED = 1 and @EXCLUDE = 0) or (@EXCLUDELOADED = 0 and [EXCLUDE] = 0) then @ASKLADDERID else null end else [ASKLADDERID] end),
          [TESTSEGMENTCODE] = (case when @TESTSEGMENTCODELOADED = 1 then case when (@EXCLUDELOADED = 1 and @EXCLUDE = 0) or (@EXCLUDELOADED = 0 and [EXCLUDE] = 0) then @TESTSEGMENTCODE else '' end else [TESTSEGMENTCODE] end),
          [TESTPARTDEFINITIONVALUESID] = (case when @TESTSEGMENTCODELOADED = 1 then case when (@EXCLUDELOADED = 1 and @EXCLUDE = 0) or (@EXCLUDELOADED = 0 and [EXCLUDE] = 0) then @TESTSEGMENTCODEVALUEID else null end else [TESTPARTDEFINITIONVALUESID] end),
          [PACKAGEID] = (case when @PACKAGELOADED = 1 then case when (@EXCLUDELOADED = 1 and @EXCLUDE = 0) or (@EXCLUDELOADED = 0 and [EXCLUDE] = 0) then @PACKAGEID else null end else [PACKAGEID] end),
          [RESPONSERATE] = (case when @RESPONSERATELOADED = 1 then case when (@EXCLUDELOADED = 1 and @EXCLUDE = 0) or (@EXCLUDELOADED = 0 and [EXCLUDE] = 0) then @RESPONSERATE else 0 end else [RESPONSERATE] end),
          [GIFTAMOUNT] = (case when @GIFTAMOUNTLOADED = 1 then case when (@EXCLUDELOADED = 1 and @EXCLUDE = 0) or (@EXCLUDELOADED = 0 and [EXCLUDE] = 0) then @GIFTAMOUNT else 0 end else [GIFTAMOUNT] end),
          [SAMPLESIZE] = (case when @SAMPLESIZELOADED = 1 then case when (@EXCLUDELOADED = 1 and @EXCLUDE = 0) or (@EXCLUDELOADED = 0 and [EXCLUDE] = 0) then @SAMPLESIZE else 100 end else [SAMPLESIZE] end),
          [SAMPLESIZETYPECODE] = (case when @SAMPLESIZELOADED = 1 then case when (@EXCLUDELOADED = 1 and @EXCLUDE = 0) or (@EXCLUDELOADED = 0 and [EXCLUDE] = 0) then @SAMPLESIZETYPECODE else 0 end else [SAMPLESIZETYPECODE] end),
          [SAMPLESIZEMETHODCODE] = (case when @SAMPLESIZELOADED = 1 then case when (@EXCLUDELOADED = 1 and @EXCLUDE = 0) or (@EXCLUDELOADED = 0 and [EXCLUDE] = 0) then @SAMPLESIZEMETHODCODE else 0 end else [SAMPLESIZEMETHODCODE] end),
          [SAMPLESIZEEXCLUDEREMAINDER] = (case when @SAMPLESIZELOADED = 1 then case when (@EXCLUDELOADED = 1 and @EXCLUDE = 0) or (@EXCLUDELOADED = 0 and [EXCLUDE] = 0) then @SAMPLESIZEEXCLUDEREMAINDER else 1 end else [SAMPLESIZEEXCLUDEREMAINDER] end),
          [OVERRIDEADDRESSPROCESSING] = (case when @SEGMENTTYPECODE <> 2 and @ADDRESSPROCESSINGOPTIONSLOADED = 1 then case when (@EXCLUDELOADED = 1 and @EXCLUDE = 0) or (@EXCLUDELOADED = 0 and [EXCLUDE] = 0) then @OVERRIDEADDRESSPROCESSING else 0 end else [OVERRIDEADDRESSPROCESSING] end),
          [USEADDRESSPROCESSING] = (case when @SEGMENTTYPECODE <> 2 and @ADDRESSPROCESSINGOPTIONSLOADED = 1 then case when @OVERRIDEADDRESSPROCESSING = 1 and ((@EXCLUDELOADED = 1 and @EXCLUDE = 0) or (@EXCLUDELOADED = 0 and [EXCLUDE] = 0)) then @USEADDRESSPROCESSING else 0 end else [USEADDRESSPROCESSING] end),
          [ADDRESSPROCESSINGOPTIONID] = (case when @SEGMENTTYPECODE <> 2 and @ADDRESSPROCESSINGOPTIONSLOADED = 1 then case when @OVERRIDEADDRESSPROCESSING = 1 and @USEADDRESSPROCESSING = 1 and ((@EXCLUDELOADED = 1 and @EXCLUDE = 0) or (@EXCLUDELOADED = 0 and [EXCLUDE] = 0)) then @ADDRESSPROCESSINGOPTIONID else null end else [ADDRESSPROCESSINGOPTIONID] end),
          [ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE] = (case when @SEGMENTTYPECODE <> 2 and @ADDRESSPROCESSINGOPTIONSLOADED = 1 then case when @OVERRIDEADDRESSPROCESSING = 1 and @USEADDRESSPROCESSING = 1 and ((@EXCLUDELOADED = 1 and @EXCLUDE = 0) or (@EXCLUDELOADED = 0 and [EXCLUDE] = 0)) then @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE else 0 end else [ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE] end),
          [ADDRESSPROCESSINGOPTIONSEASONALASOFDATE] = (case when @SEGMENTTYPECODE <> 2 and @ADDRESSPROCESSINGOPTIONSLOADED = 1 then case when @OVERRIDEADDRESSPROCESSING = 1 and @USEADDRESSPROCESSING = 1 and ((@EXCLUDELOADED = 1 and @EXCLUDE = 0) or (@EXCLUDELOADED = 0 and [EXCLUDE] = 0)) then @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE else null end else [ADDRESSPROCESSINGOPTIONSEASONALASOFDATE] end),
          [NAMEFORMATPARAMETERID] = (case when @SEGMENTTYPECODE <> 2 and @ADDRESSPROCESSINGOPTIONSLOADED = 1 then case when @OVERRIDEADDRESSPROCESSING = 1 and @USEADDRESSPROCESSING = 1 and ((@EXCLUDELOADED = 1 and @EXCLUDE = 0) or (@EXCLUDELOADED = 0 and [EXCLUDE] = 0)) then @NAMEFORMATPARAMETERID else null end else [NAMEFORMATPARAMETERID] end),
          [CURRENCYEXCHANGERATEID] = @ORGANIZATIONCURRENCYEXCHANGERATEID,
          [ORGANIZATIONGIFTAMOUNT] = @ORGANIZATIONGIFTAMOUNT,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where [ID] = @SEGMENTID;

        if @PACKAGELOADED = 1
          begin
            /* Save and update the package for the segment */
            if isnull(@OLDPACKAGEID, '00000000-0000-0000-0000-000000000000') <> isnull(@PACKAGEID, '00000000-0000-0000-0000-000000000000') or @OLDEXCLUDE <> @EXCLUDE
              begin
                /* Remove the old package, only if is not being used by any other segments and test segments */
                if @OLDEXCLUDE = 0 and
                   not exists(select [ID] from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID and [PACKAGEID] = @OLDPACKAGEID) and
                   not exists(select [MKTSEGMENTATIONTESTSEGMENT].[ID] from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID and [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID] = @OLDPACKAGEID)
                  exec dbo.[USP_MKTSEGMENTATIONPACKAGE_DELETE] @OLDSEGMENTATIONPACKAGEID, @CHANGEAGENTID;

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

                select @PACKAGECHANNELCODE = [CHANNELCODE] from dbo.[MKTPACKAGE] where [ID] = @PACKAGEID;

                if (@OLDPACKAGECHANNELCODE <> @PACKAGECHANNELCODE and @USEADDRESSPROCESSING = 1 and @ADDRESSPROCESSINGOPTIONID is not null)
                  /* clear the cache for this segment and all segments after it, 
                     since the package's channel can affect record counts when address processing is used */
                  exec dbo.[USP_MKTSEGMENTATIONSEGMENT_CLEARCACHE] @SEGMENTID, 0, 1;
              end

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

            if @OLDEXCLUDE <> @EXCLUDE
              begin
                if exists (select top 1 1 from sys.objects where name = @DATATABLENAME)
              begin
                    set @SQL = '-- make finder number nullable' + char(13) +

                               'if exists (select top 1 1 from dbo.sysindexes where name = ''UIX_' + @DATATABLENAME + '_FINDERNUMBER'')' + char(13) +
                               '  drop index [UIX_' + @DATATABLENAME + '_FINDERNUMBER] on dbo.[' + @DATATABLENAME + '];' + char(13) +
                               'alter table dbo.[' + @DATATABLENAME + '] alter column [FINDERNUMBER] bigint null;' + char(13) +
                               char(13) +
                               '-- wipe out finder numbers for this segment' + char(13) +

                               'update dbo.[' + @DATATABLENAME + '] set [FINDERNUMBER] = null' +
                               case when @EXCLUDE = 1 then ', [SOURCECODE] = '''' ' else ' ' end +
                               'where [SEGMENTID] = @SEGMENTID;';

                    exec sp_executesql @SQL, N'@SEGMENTID uniqueidentifier', @SEGMENTID = @SEGMENTID;
                  end

                if @EXCLUDE = 1
                  begin
                    -- inline exclusions may not have test segments

                    declare TESTSEGMENTS cursor local fast_forward for
                    select [ID]
                    from dbo.[MKTSEGMENTATIONTESTSEGMENT]
                    where [SEGMENTID] = @SEGMENTID
                    order by [SEQUENCE] desc;

                    open TESTSEGMENTS
                    fetch next from TESTSEGMENTS into @TESTSEGMENTID;

                    while (@@FETCH_STATUS = 0)
                      begin
                        exec dbo.[USP_MKTSEGMENTATIONTESTSEGMENT_DELETE] @TESTSEGMENTID, @CHANGEAGENTID;
                        fetch next from TESTSEGMENTS into @TESTSEGMENTID;
                      end

                    close TESTSEGMENTS;
                    deallocate TESTSEGMENTS;
                  end
              end
          end

        if @PACKAGELOADED = 1
          if @PACKAGECHANNELCHANGED = 0
            set @PACKAGECHANNELCHANGED = case when (@OLDPACKAGECHANNELCODE <> @PACKAGECHANNELCODE) then 1 else 0 end;

        if @ADDRESSPROCESSINGOPTIONSLOADED = 1
          if @ADDRESSPROCESSINGOPTIONSCHANGED = 0
            set @ADDRESSPROCESSINGOPTIONSCHANGED = case when (@OLDUSEADDRESSPROCESSING <> @USEADDRESSPROCESSING or (@USEADDRESSPROCESSING = 1 and ((@OLDADDRESSPROCESSINGOPTIONID is null and @OLDADDRESSPROCESSINGOPTIONID is not null) or (@OLDADDRESSPROCESSINGOPTIONID is not null and @OLDADDRESSPROCESSINGOPTIONID is null) or @OLDADDRESSPROCESSINGOPTIONID <> @OLDADDRESSPROCESSINGOPTIONID))) then 1 else 0 end;

        /* Clear the segment cache only if the sample size settings changed */
        if (@EXCLUDELOADED = 1 and (@OLDEXCLUDE <> @EXCLUDE)) or
           (@SAMPLESIZELOADED = 1 and (@OLDSAMPLESIZEEXCLUDEREMAINDER <> @SAMPLESIZEEXCLUDEREMAINDER or @OLDSAMPLESIZE <> @SAMPLESIZE or @OLDSAMPLESIZETYPECODE <> @SAMPLESIZETYPECODE or @OLDSAMPLESIZEMETHODCODE <> @SAMPLESIZEMETHODCODE)) or
           (@ADDRESSPROCESSINGOPTIONSLOADED = 1 and (@OLDUSEADDRESSPROCESSING <> @USEADDRESSPROCESSING or (@USEADDRESSPROCESSING = 1 and ((@OLDADDRESSPROCESSINGOPTIONID is null and @OLDADDRESSPROCESSINGOPTIONID is not null) or (@OLDADDRESSPROCESSINGOPTIONID is not null and @OLDADDRESSPROCESSINGOPTIONID is null) or @OLDADDRESSPROCESSINGOPTIONID <> @OLDADDRESSPROCESSINGOPTIONID)))) or
           (@PACKAGELOADED = 1 and (@OLDPACKAGECHANNELCODE <> @PACKAGECHANNELCODE and (@USEADDRESSPROCESSING = 1 and @ADDRESSPROCESSINGOPTIONID is not null))) or
           (@ASKLADDERLOADED = 1 and ((@OLDASKLADDERID is null and @ASKLADDERID is not null) or (@OLDASKLADDERID is not null and @ASKLADDERID is null) or (@OLDASKLADDERID <> @ASKLADDERID)))
          /* Clear the cache for this segment and all segments after it */
          exec dbo.[USP_MKTSEGMENTATIONSEGMENT_CLEARCACHE] @SEGMENTID, 0, 1;

        /* insert new ids into item list for each test segment */
        if @ORIGINALITEMLIST is not null
          begin
            declare @ITEMLISTTABLE table
            (
              [CODE] nvarchar(50),
              [ID] uniqueidentifier,
              [PARTDEFINITIONVALUESID] uniqueidentifier,
              [SEGMENTATIONID] uniqueidentifier,
              [SOURCECODEITEMID] uniqueidentifier
            );

            /* get existing parts for this segment */
            insert into @ITEMLISTTABLE
            select 
              [CODE], 
              [ID], 
              [PARTDEFINITIONVALUESID], 
              [SEGMENTATIONID], 
              [SOURCECODEITEMID]  
            from dbo.[UFN_MKTSOURCECODEPART_SEGMENTATIONSEGMENT_GETITEMLIST3](@SEGMENTID);

            --Remove the ones we are updating

            delete @ITEMLISTTABLE where [SOURCECODEITEMID] in (select [SOURCECODEITEMID] from dbo.[UFN_MKTSOURCECODEPART_SEGMENTATIONSEGMENT_GETITEMLIST3_FROMITEMLISTXML](@ORIGINALITEMLIST));

            /* insert unique MKTSOURCECODEPART ID for each value in the itemlist */ 
            insert into @ITEMLISTTABLE
            select 
              [CODE], 
              newid(), 
              [PARTDEFINITIONVALUESID], 
              [SEGMENTATIONID], 
              [SOURCECODEITEMID]  
            from dbo.[UFN_MKTSOURCECODEPART_SEGMENTATIONSEGMENT_GETITEMLIST3_FROMITEMLISTXML](@ORIGINALITEMLIST);

            set @ITEMLIST = 
            (
              select
                [ID],
                [SOURCECODEITEMID],
                [PARTDEFINITIONVALUESID],
                [CODE],
                [SEGMENTATIONID]
              from @ITEMLISTTABLE
              for xml raw('ITEM'), type, elements, root('ITEMLIST'), binary base64);

            delete from @ITEMLISTTABLE;

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

          end

        /* Save the business units information */
        if @SEGMENTTYPECODE in (1, 2) and @OVERRIDEBUSINESSUNITSLOADED = 1
          begin
            update dbo.[MKTSEGMENTATIONSEGMENT] set 
              [OVERRIDEBUSINESSUNITS] = @OVERRIDEBUSINESSUNITS,
              [CHANGEDBYID] = @CHANGEAGENTID,
              [DATECHANGED] = @CURRENTDATE
            where [ID] = @SEGMENTID;

            exec dbo.[USP_MKTSEGMENTATIONSEGMENTBUSINESSUNIT_GETBUSINESSUNITS_UPDATEFROMXML] @SEGMENTID, @BUSINESSUNITS, @CHANGEAGENTID;  
          end

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

    close SEGMENTCURSOR;
    deallocate SEGMENTCURSOR;
  end try

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

  return 0;