USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONTESTSEGMENT_PRELOAD

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONTESTSEGMENT_PRELOAD]
(
  @SEGMENTIDS nvarchar(max),
  @SEGMENTATIONID uniqueidentifier = null output,
  @SOURCECODEID uniqueidentifier = null output,
  @RECORDSOURCEID uniqueidentifier = null output,
  @SEGMENTTYPECODE tinyint = null output,
  @SEGMENTATIONSITEID uniqueidentifier = null output,
  @MAILINGTYPECODE tinyint = null output,
  @PACKAGEID uniqueidentifier = null output,
  @PACKAGECODE nvarchar(10) = null output,
  @ISVENDORMANAGED bit = null output,
  @SAMPLESEGMENTNAME nvarchar(100) = null output,
  @SAMPLESEGMENTCODE nvarchar(10) = null output,
  @CODE nvarchar(10) = null output,
  @MULTIPLETESTSEGMENTS bit = null output,
  @CODEVALUEID uniqueidentifier = null output,
  @PACKAGECODEVALUEID uniqueidentifier = null output,
  @CHANNELSOURCECODE nvarchar(10) = null output,
  @CHANNELSOURCECODEVALUEID uniqueidentifier = null output,
  @BUSINESSUNITS xml = null output,
  @SEGMENTID uniqueidentifier = null output,
  @ISBBEC bit = null output,
  @CURRENTAPPUSERID uniqueidentifier,
  @BASECURRENCYID uniqueidentifier = null output,
  @PACKAGEADDDATAFORMCONTEXT nvarchar(128) = null output,
  @SEGMENTEDHOUSEFILEEXISTS bit = null output,
  @LISTCODEVALUEID uniqueidentifier = null output,
  @LISTCODE nvarchar(10) = null output,
  @PACKAGECHANNELCODE tinyint = null output,
  @ITEMLIST xml = null output,
  @EFFORTOVERRIDESBUSINESSUNITS bit = null output,
  @EFFORTSEGMENTOVERRIDESBUSINESSUNITS bit = null output,
  @CHANNELCODE tinyint = null output,
  @ALLOWEFFORTBUSINESSUNITSOVERRIDE bit = null output
)
as
  set nocount on;

  declare @ACTIVE bit;
  declare @SEGMENTSTABLE table([ID] uniqueidentifier);
  declare @START int;
  declare @POS int;

  set @MULTIPLETESTSEGMENTS = 0;

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

  -- parse out the multiple effort segment IDs

  set @START = 0;
  select @POS = charindex(',', @SEGMENTIDS, @START)

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

      if not exists (select [ID] from @SEGMENTSTABLE where [ID] = @SEGMENTID)
        insert into @SEGMENTSTABLE values (@SEGMENTID);

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

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

      if not exists (select [ID] from @SEGMENTSTABLE where [ID] = @SEGMENTID) 
        insert into @SEGMENTSTABLE values (@SEGMENTID);
    end

  if (select count(*) from @SEGMENTSTABLE) > 1
    set @MULTIPLETESTSEGMENTS = 1;

  select top 1
    @SEGMENTATIONID = [MKTSEGMENTATION].[ID],
    @SOURCECODEID = [MKTSEGMENTATION].[SOURCECODEID],
    @ITEMLIST = dbo.[UFN_MKTSOURCECODEPART_SEGMENTATIONSEGMENT_GETITEMLIST3_TOITEMLISTXML]([MKTSEGMENTATIONSEGMENT].[ID]),
    @RECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
    @LISTCODEVALUEID = isnull([MKTLIST].[PARTDEFINITIONVALUESID], [MKTRECORDSOURCE].[PARTDEFINITIONVALUESID]),
    @LISTCODE = isnull(isnull([MKTLIST].[CODE], [MKTRECORDSOURCE].[CODE]), ''),
    @SEGMENTTYPECODE = [MKTSEGMENT].[SEGMENTTYPECODE],
    @SEGMENTATIONSITEID = [MKTSEGMENTATION].[SITEID],
    @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
    @ACTIVE = [MKTSEGMENTATION].[ACTIVE],
    @ISVENDORMANAGED = dbo.[UFN_MKTSEGMENT_ISVENDORMANAGEDLIST]([MKTSEGMENT].[ID]),
    @SAMPLESEGMENTNAME = [MKTSEGMENT].[NAME],
    @SAMPLESEGMENTCODE = [MKTSEGMENTATIONSEGMENT].[CODE],
    @CODE = [MKTSEGMENT].[CODE],
    @CODEVALUEID = [MKTSEGMENT].[PARTDEFINITIONVALUESID],
    @EFFORTOVERRIDESBUSINESSUNITS = [MKTSEGMENTATION].[OVERRIDEBUSINESSUNITS],
    @EFFORTSEGMENTOVERRIDESBUSINESSUNITS = [MKTSEGMENTATIONSEGMENT].[OVERRIDEBUSINESSUNITS],
    @ALLOWEFFORTBUSINESSUNITSOVERRIDE = isnull([MKTCOMMUNICATIONTEMPLATE].[ALLOWEFFORTBUSINESSUNITSOVERRIDE], 1),
    @BASECURRENCYID = [MKTSEGMENTATION].[BASECURRENCYID],
    @PACKAGEADDDATAFORMCONTEXT = convert(nvarchar(3), [MKTSEGMENTATION].[MAILINGTYPECODE]) + '|' + convert(nvarchar(36), [MKTSEGMENTATION].[BASECURRENCYID]),
    @SEGMENTEDHOUSEFILEEXISTS = dbo.[UFN_MKTCONSTITUENTFILEIMPORT_IMPORTTABLEEXISTS]([MKTSEGMENTATION].[ID]),
    @CHANNELCODE = isnull([MKTSEGMENTATION].[CHANNELCODE], 255)
  from dbo.[MKTSEGMENTATIONSEGMENT]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
  inner join dbo.[MKTRECORDSOURCE] on [MKTRECORDSOURCE].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
  left outer join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
  left outer join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
  left outer join dbo.[MKTCOMMUNICATIONTEMPLATE] on [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID;

  if @MAILINGTYPECODE = 1
    -- for acknowledgement mailings, grab the package info from the base segment, since the user cannot change the package

    select
      @PACKAGEID = [MKTPACKAGE].[ID],
      @PACKAGECODE = (case when @ACTIVE = 1 then [MKTSEGMENTATIONPACKAGE].[CODE] else [MKTPACKAGE].[CODE] end),
      @PACKAGECODEVALUEID = (case when @ACTIVE = 1 then [MKTSEGMENTATIONPACKAGE].[PARTDEFINITIONVALUESID] else [MKTPACKAGE].[PARTDEFINITIONVALUESID] end),
      @CHANNELSOURCECODE = (case when @ACTIVE = 1 then [MKTSEGMENTATIONPACKAGE].[CHANNELSOURCECODE] else [MKTPACKAGE].[CHANNELSOURCECODE] end),
      @CHANNELSOURCECODEVALUEID = (case when @ACTIVE = 1 then [MKTSEGMENTATIONPACKAGE].[CHANNELPARTDEFINITIONVALUESID] else [MKTPACKAGE].[CHANNELPARTDEFINITIONVALUESID] end)
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
    left join dbo.[MKTSEGMENTATIONPACKAGE] on [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = @SEGMENTATIONID and [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = [MKTPACKAGE].[ID]
    where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID;

  if @ISBBEC = 1
    begin
      if @EFFORTSEGMENTOVERRIDESBUSINESSUNITS = 1
        if @MULTIPLETESTSEGMENTS = 0
          set @BUSINESSUNITS = dbo.[UFN_MKTSEGMENTATIONSEGMENTBUSINESSUNIT_GETBUSINESSUNITS_TOITEMLISTXML](@SEGMENTID);
      else if @EFFORTOVERRIDESBUSINESSUNITS = 1
        set @BUSINESSUNITS = dbo.[UFN_MKTSEGMENTATIONBUSINESSUNIT_GETBUSINESSUNITS_TOITEMLISTXML](@SEGMENTATIONID);
      else
        begin
          declare @APPEALSYSTEMID uniqueidentifier;

          select 
            @APPEALSYSTEMID = convert(uniqueidentifier, [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID])
          from dbo.[MKTAPPEALRECORDSOURCE] 
          inner join [QUERYVIEWCATALOG] on [MKTAPPEALRECORDSOURCE].[ID] = [QUERYVIEWCATALOG].[ID]
          left join [MKTSEGMENTATIONACTIVATE] on ([MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = [MKTAPPEALRECORDSOURCE].[ID] and [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = @SEGMENTATIONID)
          where (@ACTIVE = 0 or (@ACTIVE = 1 and [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] <> '')) 
          and (dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([QUERYVIEWCATALOG].[ID]) = 1);

          if @APPEALSYSTEMID is not null
            set @BUSINESSUNITS = dbo.[UFN_APPEALBUSINESSUNIT_GETBUSINESSUNITS_TOITEMLISTXML](@APPEALSYSTEMID);
        end
    end

  -- check to see if the mailing is currently being activated

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

  return 0;