USP_MKTSEGMENTATIONSEGMENT_GETCOMMONUSERDEFINEDCODES

CREATE procedure dbo.[USP_MKTSEGMENTATIONSEGMENT_GETCOMMONUSERDEFINEDCODES]
(
  @SEGMENTIDSXML xml,
  @SOURCECODEID uniqueidentifier,
  @USERDEFINEDLOADEDSOURCECODESXML xml = null output
)
as
begin
  /* Convert IDs from xml to table */
  declare @SEGMENTSTABLE table([ID] uniqueidentifier);
  insert into @SEGMENTSTABLE
    select
      T.c.value('(ID)[1]','uniqueidentifier') AS 'ID'
    from @SEGMENTIDSXML.nodes('/ITEMLIST/ITEM') T(c);

  declare @USERDEFINEDLOADEDSOURCECODES table([PARTDEFINITIONVALUESID] uniqueidentifier, [CODE] nvarchar(10));
  declare @USERDEFINEDPARTDEFINITIONID uniqueidentifier;

  declare USERDEFINEDPARTS_CURSOR 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_CURSOR
  fetch next from USERDEFINEDPARTS_CURSOR into @USERDEFINEDPARTDEFINITIONID;

  while (@@fetch_status = 0)
  begin

   if (select COUNT(distinct ([MKTSOURCECODEPART].[PARTDEFINITIONVALUESID])) 
       from dbo.[MKTSOURCECODEPART]
         inner join [MKTSOURCECODEPARTDEFINITIONVALUES] on [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID]
       where 
         [MKTSOURCECODEPART].[SEGMENTATIONTESTSEGMENTID] is null and
         [MKTSOURCECODEPARTDEFINITIONVALUES].[MKTSOURCECODEPARTDEFINITIONID] = @USERDEFINEDPARTDEFINITIONID) = 1
   begin
     /* All of the segments are using the default mailing user defined code for this source code part. */
     insert into @USERDEFINEDLOADEDSOURCECODES
     select distinct
       [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID],
       [MKTSOURCECODEPART].[CODE]
     from dbo.[MKTSOURCECODEPART]
       inner join [MKTSOURCECODEPARTDEFINITIONVALUES] on [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID]
     where 
       [MKTSOURCECODEPART].[SEGMENTATIONTESTSEGMENTID] is null and
       [MKTSOURCECODEPARTDEFINITIONVALUES].[MKTSOURCECODEPARTDEFINITIONID] = @USERDEFINEDPARTDEFINITIONID
   end

   else if (select count(*)
              from (
                select  distinct
                  [MKTSOURCECODEPARTDEFINITIONVALUES].[MKTSOURCECODEPARTDEFINITIONID],
                  [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID],
                  [MKTSOURCECODEPART].[CODE]
                from @SEGMENTSTABLE [S] 
                  left join dbo.[MKTSOURCECODEPART] on [MKTSOURCECODEPART].[SEGMENTATIONSEGMENTID] = [S].[ID]
                  left join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] on ([MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID] and [MKTSOURCECODEPARTDEFINITIONVALUES].[MKTSOURCECODEPARTDEFINITIONID] = @USERDEFINEDPARTDEFINITIONID)
                where 
                  [MKTSOURCECODEPART].[SEGMENTATIONTESTSEGMENTID] is null
                ) as [UNIQUEUSERDEFINEDCODES]) = 1
   begin

     /*All of the segments are using the same user defined code for this source code part (and not the mailing default). */
     insert into @USERDEFINEDLOADEDSOURCECODES
       select distinct
         [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID],
         [MKTSOURCECODEPART].[CODE]
       from @SEGMENTSTABLE [S] 
         left join dbo.[MKTSOURCECODEPART] on [MKTSOURCECODEPART].[SEGMENTATIONSEGMENTID] = [S].[ID]
         left join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] on ([MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID] and [MKTSOURCECODEPARTDEFINITIONVALUES].[MKTSOURCECODEPARTDEFINITIONID] = @USERDEFINEDPARTDEFINITIONID)
       where 
         [MKTSOURCECODEPART].[SEGMENTATIONTESTSEGMENTID] is null
   end  

    fetch next from USERDEFINEDPARTS_CURSOR into @USERDEFINEDPARTDEFINITIONID;
  end

  close USERDEFINEDPARTS_CURSOR;
  deallocate USERDEFINEDPARTS_CURSOR;

  set @USERDEFINEDLOADEDSOURCECODESXML = (select [PARTDEFINITIONVALUESID], [CODE] from @USERDEFINEDLOADEDSOURCECODES for xml raw('ITEM'),type,elements,root('ITEMLIST'),BINARY BASE64);

  return 0;
end