USP_MKTSEGMENTATIONACTIVATE_POPULATESOURCECODEPARTSFORLISTS

CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_POPULATESOURCECODEPARTSFORLISTS]
(
  @SEGMENTATIONID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @ACTIVE bit;
  declare @SOURCECODEISHISTORICAL bit;
  declare @CURRENTDATE datetime;
  declare @SQL nvarchar(max);
  declare @DATATABLENAME nvarchar(128);

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

    set @CURRENTDATE = getdate();

    select
      @ACTIVE = [ACTIVE],
      @SOURCECODEISHISTORICAL = isnull([MKTSOURCECODE].[ISHISTORICAL], 0),
      @DATATABLENAME = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID)
    from dbo.[MKTSEGMENTATION]
    left outer join dbo.[MKTSOURCECODE] on [MKTSOURCECODE].[ID] = [MKTSEGMENTATION].[SOURCECODEID]
    where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;

    -- only save this info if this is the first activation

    if @ACTIVE = 0 and @SOURCECODEISHISTORICAL = 0
      begin
        -- put the list codes into the source code parts table

        declare @LISTSOURCECODEID uniqueidentifier;

        select
          @LISTSOURCECODEID = [MKTSOURCECODEITEM].[ID]
        from dbo.[MKTSOURCECODEITEM]
        inner join dbo.[MKTSOURCECODEVALIDPARTVALUES] on [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID]
        inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
        where [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 6
        and [MKTSOURCECODEITEM].[SOURCECODEID] = (select [SOURCECODEID] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID);

        if @LISTSOURCECODEID is not null
          begin
            declare @SEGMENTATIONLISTS table([LISTID] uniqueidentifier, [LISTCODE] nvarchar(50), [PARTDEFINITIONVALUESID] uniqueidentifier);

            -- constituent segments (including segments based on the consolidated list)...

            set @SQL = 'with [VALIDRECORDSOURCES] ([ID], [CODE], [PARTDEFINITIONVALUESID]) as' + char(13) +
                       '(' + char(13) +
                       '  select [ID], [CODE], [PARTDEFINITIONVALUESID]' + char(13) +
                       '  from dbo.[MKTRECORDSOURCE]' + char(13) +
                       '  where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([ID]) = 1' + char(13) +
                       ')' + char(13) +
                       'select distinct' + char(13) +
                       '  coalesce([MKTLIST].[ID], [MKTLIST].[RECORDSOURCEID], [MKTSEGMENT].[QUERYVIEWCATALOGID]),' + char(13) +
                       '  isnull((case when [MKTLIST].[ID] is null then [VALIDRECORDSOURCES].[CODE] else [MKTLIST].[CODE] end), ''''),' + char(13) +
                       '  coalesce([MKTLIST].[PARTDEFINITIONVALUESID], [VALIDRECORDSOURCES].[PARTDEFINITIONVALUESID])' + char(13) +
                       'from dbo.[MKTSEGMENTATIONSEGMENT]' + char(13) +
                       'inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]' + char(13) +
                       'inner join [VALIDRECORDSOURCES] on [VALIDRECORDSOURCES].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]' + char(13) +
                       'left join dbo.[' + @DATATABLENAME + '] as [DATATABLE] on [DATATABLE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]' + char(13) + 
                       'left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]' + char(13) +
                       'left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID] or [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] = [DATATABLE].[DONORQUERYVIEWCATALOGID]' + char(13) +
                       'left join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]' + char(13) +
                       'where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID and [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0';

            insert into @SEGMENTATIONLISTS
              exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier', @SEGMENTATIONID = @SEGMENTATIONID;

            -- vendor managed segments...

            insert into @SEGMENTATIONLISTS
              select distinct
                [MKTLIST].[ID] as [LISTID],
                isnull([MKTLIST].[CODE], '') as [LISTCODE],
                [MKTLIST].[PARTDEFINITIONVALUESID]
              from dbo.[MKTSEGMENTATIONSEGMENT]
              inner join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
              inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID]
              inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
              left join @SEGMENTATIONLISTS [S] on [S].[LISTID] = [MKTLIST].[ID]
              where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
              and [MKTSEGMENTLIST].[TYPECODE] = 1
              and [S].[LISTID] is null;

            insert into dbo.[MKTSOURCECODEPART] (
              [ID],
              [SEGMENTATIONID],
              [LISTID],
              [SOURCECODEITEMID],
              [CODE],
              [PARTDEFINITIONVALUESID],
              [ADDEDBYID],
              [CHANGEDBYID],
              [DATEADDED],
              [DATECHANGED]
            )
            select
              NewID(),
              @SEGMENTATIONID,
              [LISTID],
              @LISTSOURCECODEID,
              [LISTCODE],
              [PARTDEFINITIONVALUESID],
              @CHANGEAGENTID,
              @CHANGEAGENTID,
              @CURRENTDATE,
              @CURRENTDATE
            from @SEGMENTATIONLISTS;
          end;
      end;
  end try

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

  return 0;