USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTATIONTESTSEGMENT_MULTIPLE_3

The load procedure used by the edit dataform template "Marketing Effort Multiple Test Segment Edit Form 3"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	nvarchar(max)	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@SEGMENTATIONID	uniqueidentifier	INOUT	Marketing effort
@ASKLADDER	xml	INOUT	Ask ladder
@ASKLADDERLOADED	bit	INOUT	Ask ladder loaded
@ASKLADDERLOCKED	bit	INOUT	Ask ladder locked
@PACKAGEID	uniqueidentifier	INOUT	Package
@PACKAGECODE	nvarchar(10)	INOUT	Package code
@PACKAGELOADED	bit	INOUT	Package loaded
@RESPONSERATE	decimal(5, 2)	INOUT	Response rate
@RESPONSERATELOADED	bit	INOUT	Response rate loaded
@GIFTAMOUNT	money	INOUT	Gift amount
@GIFTAMOUNTLOADED	bit	INOUT	Gift amount loaded
@SAMPLESIZE	int	INOUT	Sample size
@SAMPLESIZETYPECODE	tinyint	INOUT	Sample size type
@SAMPLESIZEMETHODCODE	tinyint	INOUT	Sample size method
@SAMPLESIZELOADED	bit	INOUT	Sample size loaded
@SOURCECODEID	uniqueidentifier	INOUT	Source code ID
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@MAILINGTYPECODE	tinyint	INOUT	Marketing effort type code
@TESTSEGMENTCODE	nvarchar(10)	INOUT	Test segment
@TESTSEGMENTCODELOADED	bit	INOUT	Test segment code loaded
@PREFIXCODE	tinyint	INOUT	Prepend list
@PREFIXCODELOADED	bit	INOUT
@TESTSEGMENTCODEVALUEID	uniqueidentifier	INOUT	Test segment code value ID
@PACKAGECODEVALUEID	uniqueidentifier	INOUT	Package code value ID
@ITEMLIST	xml	INOUT	Items
@CHANNELSOURCECODE	nvarchar(10)	INOUT	Channel source code
@CHANNELSOURCECODEVALUEID	uniqueidentifier	INOUT	Channel code value ID
@USERDEFINEDLOADEDSOURCECODES	xml	INOUT	Items
@FRACTION	nvarchar(10)	INOUT	Fraction
@OVERRIDEBUSINESSUNITSLOADED	bit	INOUT	Override business units loaded
@OVERRIDEBUSINESSUNITS	bit	INOUT	Override business units
@BUSINESSUNITS	xml	INOUT	Business units
@ISBBEC	bit	INOUT	Is BBEC?
@SEGMENTTYPECODE	tinyint	INOUT	Segment type code
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@SEGMENTEDHOUSEFILEEXISTS	bit	INOUT	Segmented house file exists
@PACKAGEADDDATAFORMCONTEXT	nvarchar(128)	INOUT	Package add data form context
@PACKAGECHANNELCODE	tinyint	INOUT
@ISBBAC	bit	INOUT
@ISHISTORICAL	bit	INOUT
@CHANNELCODE	tinyint	INOUT
@ALLOWEFFORTBUSINESSUNITSOVERRIDE	bit	INOUT

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTATIONTESTSEGMENT_MULTIPLE_3]
(
  @ID nvarchar(max),
  @DATALOADED bit = 0 output,
  @SEGMENTATIONID uniqueidentifier = null output,
  @ASKLADDER xml = null output,
  @ASKLADDERLOADED bit = null output,
  @ASKLADDERLOCKED bit = null output,
  @PACKAGEID uniqueidentifier = null output,
  @PACKAGECODE nvarchar(10) = null output,
  @PACKAGELOADED bit = null output,
  @RESPONSERATE decimal(5,2) = null output,
  @RESPONSERATELOADED bit = null output,
  @GIFTAMOUNT money = null output,
  @GIFTAMOUNTLOADED bit = null output,
  @SAMPLESIZE int = null output,
  @SAMPLESIZETYPECODE tinyint = null output,
  @SAMPLESIZEMETHODCODE tinyint = null output,
  @SAMPLESIZELOADED bit = null output,
  @SOURCECODEID uniqueidentifier = null output,
  @TSLONG bigint = 0 output,
  @MAILINGTYPECODE tinyint = null output,
  @TESTSEGMENTCODE nvarchar(10) = null output,
  @TESTSEGMENTCODELOADED bit = null output,
  @PREFIXCODE tinyint = null output,
  @PREFIXCODELOADED bit = null output,
  @TESTSEGMENTCODEVALUEID uniqueidentifier = null output,
  @PACKAGECODEVALUEID uniqueidentifier = null output,
  @ITEMLIST xml = null output,
  @CHANNELSOURCECODE nvarchar(10) = null output,
  @CHANNELSOURCECODEVALUEID uniqueidentifier = null output,
  @USERDEFINEDLOADEDSOURCECODES xml = null output,
  @FRACTION nvarchar(10) = null output,
  @OVERRIDEBUSINESSUNITSLOADED  bit = null output,
  @OVERRIDEBUSINESSUNITS bit = null output,
  @BUSINESSUNITS xml = null output,
  @ISBBEC bit = null output,
  @SEGMENTTYPECODE tinyint = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @SEGMENTEDHOUSEFILEEXISTS bit = null output,
  @PACKAGEADDDATAFORMCONTEXT nvarchar(128) = null output,
  @PACKAGECHANNELCODE tinyint = null output,
  @ISBBAC bit = null output,
  @ISHISTORICAL bit = null output,
  @CHANNELCODE tinyint = null output,
  @ALLOWEFFORTBUSINESSUNITSOVERRIDE bit = null output
)
as
  set nocount on;

  set @DATALOADED = 0;
  set @TSLONG = 0;

  set @ISBBEC = (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end);
  set @ISBBAC = dbo.[UFN_MKTCOMMON_PRODUCTISALTRU]();

  declare @SEGMENTATIONTESTSEGMENTS table([ID] uniqueidentifier primary key, [SEGMENTTYPECODE] tinyint);
  declare @START integer;
  declare @POS integer;
  declare @SEGMENTATIONTESTSEGMENTIDS xml;

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

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

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

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

  update @SEGMENTATIONTESTSEGMENTS
    set [SEGMENTTYPECODE] = [MKTSEGMENT].[SEGMENTTYPECODE]
  from @SEGMENTATIONTESTSEGMENTS as [S]
  inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[ID] = [S].[ID]
  inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID];

  select 
    @SEGMENTTYPECODE = min([SEGMENTTYPECODE])
  from @SEGMENTATIONTESTSEGMENTS;

  set @SEGMENTATIONTESTSEGMENTIDS = (select [ID] from @SEGMENTATIONTESTSEGMENTS for xml raw('ITEM'), type, elements, root('ITEMLIST'), binary base64);

  select top 1
    @DATALOADED = 1,
    @SEGMENTATIONID = [MKTSEGMENTATION].[ID],
    @SOURCECODEID = [MKTSEGMENTATION].[SOURCECODEID],
    @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
    @SEGMENTEDHOUSEFILEEXISTS = dbo.[UFN_MKTCONSTITUENTFILEIMPORT_IMPORTTABLEEXISTS]([MKTSEGMENTATION].[ID]),
    @PACKAGEADDDATAFORMCONTEXT = convert(nvarchar(3), [MKTSEGMENTATION].[MAILINGTYPECODE]) + '|' + convert(nvarchar(36), [MKTSEGMENTATION].[BASECURRENCYID]),
    @ISHISTORICAL = [MKTSEGMENT].[ISHISTORICAL],
    @ALLOWEFFORTBUSINESSUNITSOVERRIDE = isnull([MKTCOMMUNICATIONTEMPLATE].[ALLOWEFFORTBUSINESSUNITSOVERRIDE], 1),
    @CHANNELCODE = isnull([MKTSEGMENTATION].[CHANNELCODE], 255)
  from dbo.[MKTSEGMENTATIONTESTSEGMENT]
  inner join @SEGMENTATIONTESTSEGMENTS as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]
  inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
  left outer join dbo.[MKTCOMMUNICATIONTEMPLATE] on [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] = [MKTSEGMENTATION].[ID];

  if @DATALOADED = 1
    begin
      declare @R int;
      exec @R = dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;
      if @R <> 0
        return 1;

      /* common record source and common ask ladder */
      if (select count(distinct([MKTSEGMENT].[QUERYVIEWCATALOGID])) from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join @SEGMENTATIONTESTSEGMENTS as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID] inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]) = 1
        begin
          if (select count(distinct(isnull([MKTSEGMENTATIONTESTSEGMENT].[ASKLADDERID], '00000000-0000-0000-0000-000000000000'))) from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join @SEGMENTATIONTESTSEGMENTS as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]) = 1
            select top 1
              @ASKLADDER = (select 
                              [QUERYVIEWCATALOG].[ID] as [RECORDSOURCEID],
                              [QUERYVIEWCATALOG].[DISPLAYNAME] as [RECORDSOURCENAME],
                              (select top 1 [T1].[ASKLADDERID] from dbo.[MKTSEGMENTATIONTESTSEGMENT] as [T1] inner join @SEGMENTATIONTESTSEGMENTS as [T2] on [T2].[ID] = [T1].[ID]) as [ASKLADDERID]
                            from dbo.[MKTRECORDSOURCE]
                            inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTRECORDSOURCE].[ID]
                            where [QUERYVIEWCATALOG].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
                            and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1
                            for xml raw('ITEM'), type, elements, root('ASKLADDER'), BINARY BASE64),
              @ASKLADDERLOADED = 1,
              @ASKLADDERLOCKED = 0
            from dbo.[MKTSEGMENTATIONTESTSEGMENT]
            inner join @SEGMENTATIONTESTSEGMENTS as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]
            inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
            inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID];
          else
            select top 1
              @ASKLADDER = (select 
                              [QUERYVIEWCATALOG].[ID] as [RECORDSOURCEID],
                              [QUERYVIEWCATALOG].[DISPLAYNAME] as [RECORDSOURCENAME]
                            from dbo.[MKTRECORDSOURCE]
                            inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTRECORDSOURCE].[ID]
                            where [QUERYVIEWCATALOG].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
                            and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1
                            for xml raw('ITEM'), type, elements, root('ASKLADDER'), BINARY BASE64),
              @ASKLADDERLOADED = 0,
              @ASKLADDERLOCKED = 0
            from dbo.[MKTSEGMENTATIONTESTSEGMENT]
          inner join @SEGMENTATIONTESTSEGMENTS as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]
            inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
            inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID];
        end
      else
        begin
          set @ASKLADDERLOADED = 0;
          set @ASKLADDERLOCKED = 1;
        end

      /* common prefix */
      if (select COUNT(distinct([PREFIXCODE])) FROM dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join @SEGMENTATIONTESTSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]) = 1
        select top 1
          @PREFIXCODE = [MKTSEGMENTATIONTESTSEGMENT].[PREFIXCODE],
          @PREFIXCODELOADED = 1
        from dbo.[MKTSEGMENTATIONTESTSEGMENT]
        inner join @SEGMENTATIONTESTSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID];
      else
        set @PREFIXCODELOADED = 0;

      /* common test segment code */
      if (select COUNT(distinct([TESTSEGMENTCODE])) from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join @SEGMENTATIONTESTSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]) = 1
        select top 1
          @TESTSEGMENTCODE = [MKTSEGMENTATIONTESTSEGMENT].[TESTSEGMENTCODE],
          @TESTSEGMENTCODEVALUEID = [MKTSEGMENTATIONTESTSEGMENT].[TESTPARTDEFINITIONVALUESID],
          @TESTSEGMENTCODELOADED = 1
        from dbo.[MKTSEGMENTATIONTESTSEGMENT]
        inner join @SEGMENTATIONTESTSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID];
      else
        set @TESTSEGMENTCODELOADED = 0;

      /* common package */
      if @MAILINGTYPECODE <> 1 and (select count(distinct([PACKAGEID])) from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join @SEGMENTATIONTESTSEGMENTS as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]) = 1
        select top 1
          @PACKAGEID = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID],
          @PACKAGECHANNELCODE = [MKTPACKAGE].[CHANNELCODE],
          @PACKAGECODE = [MKTPACKAGE].[CODE],
          @PACKAGECODEVALUEID = [MKTPACKAGE].[PARTDEFINITIONVALUESID],
          @CHANNELSOURCECODE = [MKTPACKAGE].[CHANNELSOURCECODE],
          @CHANNELSOURCECODEVALUEID = [MKTPACKAGE].[CHANNELPARTDEFINITIONVALUESID],
          @PACKAGELOADED = 1
        from dbo.[MKTSEGMENTATIONTESTSEGMENT]
        inner join @SEGMENTATIONTESTSEGMENTS as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]
        inner join [MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID];
      else
        begin
          set @PACKAGELOADED = 0;
          set @PACKAGECHANNELCODE = 255;
        end

      /* common response rate */
      if (select count(distinct([RESPONSERATE])) from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join @SEGMENTATIONTESTSEGMENTS as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]) = 1
        select top 1
          @RESPONSERATE = [RESPONSERATE],
          @RESPONSERATELOADED = 1
        from dbo.[MKTSEGMENTATIONTESTSEGMENT]
        inner join @SEGMENTATIONTESTSEGMENTS as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID];
      else
        begin
          set @RESPONSERATE = 5;
          set @RESPONSERATELOADED = 0;
        end

      /* common gift amount */
      if (select count(distinct([GIFTAMOUNT])) from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join @SEGMENTATIONTESTSEGMENTS as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]) = 1
        select top 1
          @GIFTAMOUNT = [GIFTAMOUNT],
          @GIFTAMOUNTLOADED = 1
        from dbo.[MKTSEGMENTATIONTESTSEGMENT]
        inner join @SEGMENTATIONTESTSEGMENTS as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID];
      else
        set @GIFTAMOUNTLOADED = 0;

      /* common sample size - where sample size is the same and type is not fraction.  Or type is fraction and fractions are the same. */
      if @SEGMENTEDHOUSEFILEEXISTS = 0 and
         (select count(distinct([SAMPLESIZEMETHODCODE])) from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join @SEGMENTATIONTESTSEGMENTS as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]) = 1 and
         (select count(distinct([SAMPLESIZETYPECODE])) from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join @SEGMENTATIONTESTSEGMENTS as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]) = 1 and
         (
          (select count(distinct([SAMPLESIZE])) from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join @SEGMENTATIONTESTSEGMENTS as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]) = 1 and
          (select count([SAMPLESIZETYPECODE]) from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join @SEGMENTATIONTESTSEGMENTS as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID] where [SAMPLESIZETYPECODE] <> 2) > 0 
          or
          (select count(distinct([FRACTION])) from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join @SEGMENTATIONTESTSEGMENTS as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]) = 1 and
          (select count([SAMPLESIZETYPECODE]) from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join @SEGMENTATIONTESTSEGMENTS as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID] where [SAMPLESIZETYPECODE] = 2) > 0
         )
        select top 1
          @SAMPLESIZE = [SAMPLESIZE],
          @SAMPLESIZETYPECODE = [SAMPLESIZETYPECODE],
          @SAMPLESIZEMETHODCODE = [SAMPLESIZEMETHODCODE],
          @SAMPLESIZELOADED = 1,
          @FRACTION = [FRACTION]
        from dbo.[MKTSEGMENTATIONTESTSEGMENT]
        inner join @SEGMENTATIONTESTSEGMENTS as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID];
      else
        begin
          set @SAMPLESIZE = 100;
          set @SAMPLESIZETYPECODE = 0;
          set @SAMPLESIZEMETHODCODE = 0;
          set @SAMPLESIZELOADED = 0;
          set @FRACTION = '1/3';
        end

      /* common user defined source codes for the selected segments */
      exec dbo.[USP_MKTSOURCECODEPART_SEGMENTATIONTESTSEGMENTS_GETITEMLIST] @SEGMENTATIONTESTSEGMENTIDS, @ITEMLIST output;

      select @TSLONG = max([TSLONG])
      from dbo.[MKTSEGMENTATIONTESTSEGMENT]
      inner join @SEGMENTATIONTESTSEGMENTS as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID];

      /* common business units */
      if @SEGMENTTYPECODE in (1, 2)
        begin
          declare @TEMPSEGMENTID uniqueidentifier;

          select top 1 @TEMPSEGMENTID = [STS].[ID]
          from dbo.[MKTSEGMENTATIONTESTSEGMENT] as [STS]
          inner join @SEGMENTATIONTESTSEGMENTS as [S] on [S].[ID] = [STS].[ID];

          declare @BUSINESSUNITSTABLE table([ID] uniqueidentifier, [PERCENTVALUE] float);

          insert into @BUSINESSUNITSTABLE([ID], [PERCENTVALUE])
          select [BUSINESSUNITCODEID], [PERCENTVALUE]
          from dbo.[MKTSEGMENTATIONTESTSEGMENTBUSINESSUNIT] as [STSBU]
          inner join @SEGMENTATIONTESTSEGMENTS as [S] on [S].[ID] = [STSBU].[MKTSEGMENTATIONTESTSEGMENTID]
          where [STSBU].[MKTSEGMENTATIONTESTSEGMENTID] = @TEMPSEGMENTID;

          if (select count(distinct([OVERRIDEBUSINESSUNITS])) 
              from dbo.[MKTSEGMENTATIONTESTSEGMENT] 
              inner join @SEGMENTATIONTESTSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]) = 1 
             and ( 
              select count(*)
              from dbo.[MKTSEGMENTATIONTESTSEGMENTBUSINESSUNIT] as [STSBU]
              inner join @SEGMENTATIONTESTSEGMENTS as [S] on [S].[ID] = [STSBU].[MKTSEGMENTATIONTESTSEGMENTID]
              inner join @BUSINESSUNITSTABLE as [BU] on [BU].[ID] = [STSBU].[BUSINESSUNITCODEID]
              where [STSBU].[PERCENTVALUE] = [BU].[PERCENTVALUE]
             ) = (select count(*) from @BUSINESSUNITSTABLE) * (select COUNT(*) from @SEGMENTATIONTESTSEGMENTS)
            select top 1
              @OVERRIDEBUSINESSUNITSLOADED = 1,
              @OVERRIDEBUSINESSUNITS = [MKTSEGMENTATIONTESTSEGMENT].[OVERRIDEBUSINESSUNITS],
              @BUSINESSUNITS = dbo.[UFN_MKTSEGMENTATIONTESTSEGMENTBUSINESSUNIT_GETBUSINESSUNITS_TOITEMLISTXML]([MKTSEGMENTATIONTESTSEGMENT].[ID])
            from dbo.[MKTSEGMENTATIONTESTSEGMENT]
            inner join @SEGMENTATIONTESTSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]
          else
            begin
              set @OVERRIDEBUSINESSUNITSLOADED = 0;
              set @OVERRIDEBUSINESSUNITS = 0;
            end
        end
      else
        begin
          set @OVERRIDEBUSINESSUNITSLOADED = 0;
          set @OVERRIDEBUSINESSUNITS = 0;
        end

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

      -- this is obsolete in WebUI; leaving in so WebUI can be compared to WinForms

      exec dbo.[USP_MKTSEGMENTATIONTESTSEGMENT_GETCOMMONUSERDEFINEDCODES] @SEGMENTATIONTESTSEGMENTIDS, @SOURCECODEID, @USERDEFINEDLOADEDSOURCECODES output
    end

  return 0;