USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTATIONSEGMENT_MULTIPLE_4

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTATIONSEGMENT_MULTIPLE_4]
( 
  @ID nvarchar(max),
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @SEGMENTATIONID uniqueidentifier = null output,
  @ASKLADDER xml = null output,
  @ASKLADDERLOADED bit = null output,
  @ASKLADDERLOCKED bit = null output,
  @TESTSEGMENTCODE nvarchar(10) = null output,
  @TESTSEGMENTCODELOADED 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,
  @SAMPLESIZEEXCLUDEREMAINDER bit = null output,
  @SAMPLESIZELOADED bit = null output,
  @SAMPLESIZELOCKED bit = null output,
  @SOURCECODEID uniqueidentifier = null output,
  @SEGMENTATIONSITEID uniqueidentifier = null output,
  @TSLONG bigint = 0 output,
  @MAILINGTYPECODE tinyint = null output,
  @ISTESTMAILING bit = null output,
  @PACKAGECHANNELCODE tinyint = null output,
  @USEADDRESSPROCESSING bit = null output,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null output,
  @ISBBEC bit = null output,
  @ADDRESSPROCESSINGOPTIONSLOADED bit = null output,
  @OVERRIDEADDRESSPROCESSING bit = null output,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint = null output,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime = null output,
  @NAMEFORMATPARAMETERID uniqueidentifier = null output,
  @SEGMENTTYPECODE tinyint = 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,
  @OVERRIDEBUSINESSUNITSLOADED  bit = null output,
  @OVERRIDEBUSINESSUNITS bit = null output,
  @BUSINESSUNITS xml = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @PACKAGEADDDATAFORMCONTEXT nvarchar(128) = null output,
  @EXCLUDE bit = null output,
  @EXCLUDELOADED bit = null output,
  @EXCLUDELOCKED bit = null output,
  @ISBBAC bit = null output,
  @ISHISTORICAL bit = null output,
  @CHANNELCODE tinyint = null output,
  @ALLOWEFFORTBUSINESSUNITSOVERRIDE bit = null output,
  @ALLOWEFFORTADDRESSPROCESSINGOVERRIDE bit = null output
)
as
  set nocount on;

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

  declare @CURRENTDATE datetime;
  declare @SEGMENTATIONSEGMENTS table([ID] uniqueidentifier primary key, [SEGMENTTYPECODE] tinyint);
  declare @START integer;
  declare @POS integer;
  declare @SEGMENTATIONSEGMENTIDS xml;

  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]();

  set @CURRENTDATE = getdate();

  /* 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), 0;

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

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

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

  -- when used with a standard mailing, this form can be used to edit both constituent and list segments at the same time

  -- if this occurs, we want the segment type to register as constituent (1), not list (2), so that address processing options show up

  -- the address processing options will not apply to list segments

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

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

  if not exists (select top 1 1 from dbo.[MKTSEGMENTATIONSEGMENT] right outer join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID] where [MKTSEGMENTATIONSEGMENT].[ID] is null)
    set @DATALOADED = 1;

  select top 1
    @SEGMENTATIONID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
    @SOURCECODEID = [MKTSEGMENTATION].[SOURCECODEID],
    @SEGMENTATIONSITEID = [MKTSEGMENTATION].[SITEID],
    @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
    @ISTESTMAILING = (case when [MKTSEGMENTATION].[PARENTSEGMENTATIONID] is not null then 1 else 0 end),
    @BASECURRENCYID = [MKTSEGMENTATION].[BASECURRENCYID],
    @PACKAGEADDDATAFORMCONTEXT = '0|' + convert(nvarchar(36), [MKTSEGMENTATIONSEGMENT].[BASECURRENCYID]),
    @ISHISTORICAL = [MKTSEGMENT].[ISHISTORICAL],
    @ALLOWEFFORTBUSINESSUNITSOVERRIDE = isnull([MKTCOMMUNICATIONTEMPLATE].[ALLOWEFFORTBUSINESSUNITSOVERRIDE], 1),
    @CHANNELCODE = isnull([MKTSEGMENTATION].[CHANNELCODE], 255),
    @ALLOWEFFORTADDRESSPROCESSINGOVERRIDE = isnull([MKTCOMMUNICATIONTEMPLATE].[ALLOWEFFORTADDRESSPROCESSINGOVERRIDE], 1)
  from dbo.[MKTSEGMENTATIONSEGMENT]
  inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
  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];

  /* Check if the mailing is currently being activated */
  declare @R int;
  exec @R = dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;
  if @R <> 0
    return 1;

  /* common exclude */
  if (select count(distinct([MKTSEGMENTATIONSEGMENT].[EXCLUDE])) from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]) = 1
    select top 1
      @EXCLUDE = [EXCLUDE],
      @EXCLUDELOADED = 1,
      @EXCLUDELOCKED = 0
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID];
  else
    begin
      set @EXCLUDE = 0;
      set @EXCLUDELOADED = 0;
    end

  if exists (select top 1 1 from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID] inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID] where [MKTSEGMENT].[SEGMENTTYPECODE] not in (1, 3, 5))
    -- if any of the segments are list segments (or not house file / consolidated segments), EXCLUDE cannot be changed

    set @EXCLUDELOCKED = 1;
  else
    set @EXCLUDELOCKED = 0;

  /* common record source and common ask ladder */
  if (select count(distinct([MKTSEGMENT].[QUERYVIEWCATALOGID])) from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID] inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]) = 1
    begin
      if (select count(distinct(isnull([MKTSEGMENTATIONSEGMENT].[ASKLADDERID], '00000000-0000-0000-0000-000000000000'))) from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]) = 1
        select top 1
          @ASKLADDER = (select 
                          [QUERYVIEWCATALOG].[ID] as [RECORDSOURCEID],
                          [QUERYVIEWCATALOG].[DISPLAYNAME] as [RECORDSOURCENAME],
                          (select top 1 [T1].[ASKLADDERID] from dbo.[MKTSEGMENTATIONSEGMENT] as [T1] inner join @SEGMENTATIONSEGMENTS 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]
                        for xml raw('ITEM'), type, elements, root('ASKLADDER'), binary base64),
          @ASKLADDERLOADED = 1,
          @ASKLADDERLOCKED = 0
        from dbo.[MKTSEGMENTATIONSEGMENT]
        inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
        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]
                        for xml raw('ITEM'), type, elements, root('ASKLADDER'), binary base64),
          @ASKLADDERLOADED = 0,
          @ASKLADDERLOCKED = 0
        from dbo.[MKTSEGMENTATIONSEGMENT]
        inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
        inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID];
    end
  else
    begin
      set @ASKLADDERLOADED = 0;
      set @ASKLADDERLOCKED = 1;
    end

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

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

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

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

  /* common sample size */
  if (@MAILINGTYPECODE = 1 and @ISTESTMAILING = 0) or exists(select 1 from @SEGMENTATIONSEGMENTS as [S] where dbo.[UFN_MKTSEGMENTATIONSEGMENT_ISVENDORMANAGEDLIST]([S].[ID]) = 1)
    begin
      set @SAMPLESIZE = 100;
      set @SAMPLESIZETYPECODE = 0;
      set @SAMPLESIZEMETHODCODE = 0;
      set @SAMPLESIZEEXCLUDEREMAINDER = 1;
      set @SAMPLESIZELOADED = 0;
      set @SAMPLESIZELOCKED = 1;
      set @EXCLUDELOCKED = 1;
    end
  else
    begin
      if (select count(distinct([SAMPLESIZETYPECODE])) from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]) = 1 and
         (select count(distinct([SAMPLESIZE])) from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]) = 1 and
         (select count(distinct([SAMPLESIZEMETHODCODE])) from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]) = 1 and
         (select count(distinct([SAMPLESIZEEXCLUDEREMAINDER])) from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]) = 1
        select top 1
          @SAMPLESIZE = [SAMPLESIZE],
          @SAMPLESIZETYPECODE = [SAMPLESIZETYPECODE],
          @SAMPLESIZEMETHODCODE = [SAMPLESIZEMETHODCODE],
          @SAMPLESIZEEXCLUDEREMAINDER = [SAMPLESIZEEXCLUDEREMAINDER],
          @SAMPLESIZELOADED = 1
        from dbo.[MKTSEGMENTATIONSEGMENT]
        inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID];
      else
        begin
          set @SAMPLESIZE = 100;
          set @SAMPLESIZETYPECODE = 0;
          set @SAMPLESIZEMETHODCODE = 0;
          set @SAMPLESIZEEXCLUDEREMAINDER = 1;
          set @SAMPLESIZELOADED = 0;
        end
      set @SAMPLESIZELOCKED = 0;
    end

  /* common address processing options */
  if (select count(distinct([OVERRIDEADDRESSPROCESSING])) from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID] where [S].[SEGMENTTYPECODE] <> 2) = 1 and
     (select count(distinct([USEADDRESSPROCESSING])) from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID] where [S].[SEGMENTTYPECODE] <> 2) = 1 and
     (select count(distinct(isnull([ADDRESSPROCESSINGOPTIONID], '00000000-0000-0000-0000-000000000000'))) from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID] where [S].[SEGMENTTYPECODE] <> 2) = 1 and
     (select count(distinct([ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE])) from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID] where [S].[SEGMENTTYPECODE] <> 2) = 1 and
     (select count(distinct(isnull([ADDRESSPROCESSINGOPTIONSEASONALASOFDATE], @CURRENTDATE))) from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID] where [S].[SEGMENTTYPECODE] <> 2) = 1 and
     (select count(distinct(isnull([NAMEFORMATPARAMETERID], '00000000-0000-0000-0000-000000000000'))) from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID] where [S].[SEGMENTTYPECODE] <> 2) = 1
    select top 1
    @OVERRIDEADDRESSPROCESSING = [OVERRIDEADDRESSPROCESSING],
      @USEADDRESSPROCESSING = [USEADDRESSPROCESSING],
      @ADDRESSPROCESSINGOPTIONID = [ADDRESSPROCESSINGOPTIONID],
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = [ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = [ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
      @NAMEFORMATPARAMETERID = [NAMEFORMATPARAMETERID],
      @ADDRESSPROCESSINGOPTIONSLOADED = 1
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
    where [S].[SEGMENTTYPECODE] <> 2;
  else
    begin
      set @OVERRIDEADDRESSPROCESSING = 0;
      set @USEADDRESSPROCESSING = 0;
      set @ADDRESSPROCESSINGOPTIONID = null;
      set @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = 0;
      set @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = null;
      set @NAMEFORMATPARAMETERID = null;
      set @ADDRESSPROCESSINGOPTIONSLOADED = 0;
    end

  -- this is for cosmetic purposes, so that the drop-downs displaying these fields show the defaults instead of blanks in

  -- situations where they're not enabled

  if @ADDRESSPROCESSINGOPTIONID is null
    select top 1 @ADDRESSPROCESSINGOPTIONID = [ID] from dbo.[ADDRESSPROCESSINGOPTION] where [ISDEFAULT] = 1 and dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [SITEID]) = 1;
  if @NAMEFORMATPARAMETERID is null
    select top 1 @NAMEFORMATPARAMETERID = [ID] from dbo.[NAMEFORMATPARAMETER] where [ISDEFAULT] = 1 and dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [SITEID]) = 1;

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

  select @TSLONG = max([TSLONG])
  from dbo.[MKTSEGMENTATIONSEGMENT]
  inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID];

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

      select top 1
        @TEMPSEGMENTID = [SS].[ID]
      from dbo.[MKTSEGMENTATIONSEGMENT] as [SS]
      inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [SS].[ID];

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

      insert into @BUSINESSUNITSTABLE ([ID], [PERCENTVALUE])
      select
        [BUSINESSUNITCODEID],
        [PERCENTVALUE]
      from dbo.[MKTSEGMENTATIONSEGMENTBUSINESSUNIT] as [SSBU]
      inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [SSBU].[MKTSEGMENTATIONSEGMENTID]
      where [SSBU].[MKTSEGMENTATIONSEGMENTID] = @TEMPSEGMENTID;

      if (select count(distinct([OVERRIDEBUSINESSUNITS])) 
          from dbo.[MKTSEGMENTATIONSEGMENT] 
          inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]) = 1 
         and (select count(*)
              from dbo.[MKTSEGMENTATIONSEGMENTBUSINESSUNIT] as [SSBU]
              inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [SSBU].[MKTSEGMENTATIONSEGMENTID]
              inner join @BUSINESSUNITSTABLE as [BU] on [BU].[ID] = [SSBU].[BUSINESSUNITCODEID]
              where [SSBU].[PERCENTVALUE] = [BU].[PERCENTVALUE])
         = ((select count(*) from @BUSINESSUNITSTABLE) * (select count(*) from @SEGMENTATIONSEGMENTS))
          select top 1
            @OVERRIDEBUSINESSUNITSLOADED = 1,
            @OVERRIDEBUSINESSUNITS = [MKTSEGMENTATIONSEGMENT].[OVERRIDEBUSINESSUNITS],
            @BUSINESSUNITS = dbo.[UFN_MKTSEGMENTATIONSEGMENTBUSINESSUNIT_GETBUSINESSUNITS_TOITEMLISTXML]([MKTSEGMENTATIONSEGMENT].[ID])
          from dbo.[MKTSEGMENTATIONSEGMENT]
          inner join @SEGMENTATIONSEGMENTS as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
        else
          begin
            set @OVERRIDEBUSINESSUNITSLOADED = 0;
            set @OVERRIDEBUSINESSUNITS = 0;
          end
    end
 else
    begin
      set @OVERRIDEBUSINESSUNITSLOADED = 0;
      set @OVERRIDEBUSINESSUNITS = 0;
    end

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

  exec dbo.[USP_MKTSEGMENTATIONSEGMENT_GETCOMMONUSERDEFINEDCODES] @SEGMENTATIONSEGMENTIDS, @SOURCECODEID, @USERDEFINEDLOADEDSOURCECODES output;

  return 0;