USP_MKTSOURCECODEPART_SEGMENTATIONTESTSEGMENTS_GETITEMLIST

create procedure dbo.[USP_MKTSOURCECODEPART_SEGMENTATIONTESTSEGMENTS_GETITEMLIST]
(
  @SEGMENTATIONTESTSEGMENTIDS xml,
  @ITEMLIST xml output
)
as
begin
  declare @SEGMENTATIONTESTSEGMENTS table([ID] uniqueidentifier);
  declare @SEGMENTATIONID uniqueidentifier;
  declare @SOURCECODEID uniqueidentifier;
  declare @ITEMLISTTABLE table (
    [PARTDEFINITIONID] uniqueidentifier,
    [PARTDEFINITIONVALUESID] uniqueidentifier,
    [CODE] nvarchar(10),
    [LOADED] bit
  );
  declare @PARTDEFINITIONID uniqueidentifier;

  insert into @SEGMENTATIONTESTSEGMENTS
    select
      T.c.value('(ID)[1]', 'uniqueidentifier') as [ID]
    from @SEGMENTATIONTESTSEGMENTIDS.nodes('/ITEMLIST/ITEM') T(c);

  select top 1
    @SEGMENTATIONID = [MKTSEGMENTATION].[ID],
    @SOURCECODEID = [MKTSEGMENTATION].[SOURCECODEID]
  from dbo.[MKTSEGMENTATIONTESTSEGMENT]
  inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
  inner join @SEGMENTATIONTESTSEGMENTS as [TESTSEGMENTS] on [TESTSEGMENTS].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID];

  declare USERDEFINEDPARTS cursor local fast_forward for
    select
      [MKTSOURCECODEPARTDEFINITION].[ID]
    from dbo.[MKTSOURCECODEITEM]
    inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
    where [MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEID
    and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 5;

  open USERDEFINEDPARTS
  fetch next from USERDEFINEDPARTS into @PARTDEFINITIONID;

  while (@@FETCH_STATUS = 0)
    begin
      if not exists (select top 1 1 
                     from dbo.[MKTSOURCECODEPART]
                     inner join [MKTSOURCECODEPARTDEFINITIONVALUES] on [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID]
                     where [MKTSOURCECODEPART].[SEGMENTATIONID] = @SEGMENTATIONID
                     and [MKTSOURCECODEPARTDEFINITIONVALUES].[MKTSOURCECODEPARTDEFINITIONID] = @PARTDEFINITIONID
                     and [MKTSOURCECODEPART].[SEGMENTATIONTESTSEGMENTID] is not null)

        -- all of the test segments are using the default mailing user defined code for this source code part

        insert into @ITEMLISTTABLE
        select
          @PARTDEFINITIONID as [PARTDEFINITIONID],
          null,
          '',
          1 as [LOADED]
        from dbo.[MKTSOURCECODEPART]
        inner join [MKTSOURCECODEPARTDEFINITIONVALUES] on [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID]
        where [MKTSOURCECODEPART].[SEGMENTATIONID] = @SEGMENTATIONID
        and [MKTSOURCECODEPARTDEFINITIONVALUES].[MKTSOURCECODEPARTDEFINITIONID] = @PARTDEFINITIONID
        and [MKTSOURCECODEPART].[SEGMENTATIONSEGMENTID] is null
        and [MKTSOURCECODEPART].[SEGMENTATIONTESTSEGMENTID] is null;

      else if (select count(*) from (
                 select distinct [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID], [MKTSOURCECODEPART].[CODE], 1 as [OVERRIDDEN]
                 from @SEGMENTATIONTESTSEGMENTS as [TESTSEGMENTS]
                 inner join dbo.[MKTSOURCECODEPART] on [MKTSOURCECODEPART].[SEGMENTATIONTESTSEGMENTID] = [TESTSEGMENTS].[ID]
                 inner join [MKTSOURCECODEPARTDEFINITIONVALUES] on [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID]
                 where [MKTSOURCECODEPARTDEFINITIONVALUES].[MKTSOURCECODEPARTDEFINITIONID] = @PARTDEFINITIONID
                 union
                 select top 1 null as [PARTDEFINITIONVALUESID], '' as [CODE], 0 as [OVERRIDDEN]
                 from @SEGMENTATIONTESTSEGMENTS as [TESTSEGMENTS]
                 left outer join dbo.[MKTSOURCECODEPART] on [MKTSOURCECODEPART].[SEGMENTATIONTESTSEGMENTID] = [TESTSEGMENTS].[ID]
                 where [MKTSOURCECODEPART].[ID] is null) as [T]) = 1

        -- all of the segments are using the same user defined code for this source code part (and not the mailing default)

        insert into @ITEMLISTTABLE
        select distinct 
          @PARTDEFINITIONID as [PARTDEFINITIONID],
          [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID],
          [MKTSOURCECODEPART].[CODE],
          1 as [LOADED]
        from @SEGMENTATIONTESTSEGMENTS as [TESTSEGMENTS]
        inner join dbo.[MKTSOURCECODEPART] on [MKTSOURCECODEPART].[SEGMENTATIONTESTSEGMENTID] = [TESTSEGMENTS].[ID]
        inner join [MKTSOURCECODEPARTDEFINITIONVALUES] on [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID]
        where [MKTSOURCECODEPARTDEFINITIONVALUES].[MKTSOURCECODEPARTDEFINITIONID] = @PARTDEFINITIONID

      else
        insert into @ITEMLISTTABLE values (
          @PARTDEFINITIONID,
          null,
          '',
          0);

    fetch next from USERDEFINEDPARTS into @PARTDEFINITIONID;
  end

  close USERDEFINEDPARTS;
  deallocate USERDEFINEDPARTS;

  set @ITEMLIST = 
    (select
       null as [ID],
       [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] as [SOURCECODEPARTTYPE],
       null as [SOURCECODEITEMID],
       [PARTDEFINITIONID],
       [PARTDEFINITIONVALUESID],
       [CODE],
       '' as [CODEREGEX],
       0 as [SEQUENCE],
       @SEGMENTATIONID as [SEGMENTATIONID],
       [LOADED]
     from @ITEMLISTTABLE as [ITEMLIST]
     inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [ITEMLIST].[PARTDEFINITIONID]
     for xml raw('ITEM'), type, elements, root('ITEMLIST'), binary base64);

  return 0;
end