USP_MKTSEGMENTWHITEMAIL_UPDATESOURCECODEMAP

create procedure dbo.[USP_MKTSEGMENTWHITEMAIL_UPDATESOURCECODEMAP]
(
  @SEGMENTID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @SOURCECODEMAPID uniqueidentifier;
  declare @QUERYVIEWCATALOGID uniqueidentifier;
  declare @SOURCECODEID uniqueidentifier;
  declare @SEGMENTATIONID uniqueidentifier;
  declare @ACTIVE bit;
  declare @SQL nvarchar(max);
  declare @CURRENTDATE datetime;
  declare @WORKTABLE table (
    [MKTSOURCECODEMAPID] uniqueidentifier,
    [MKTSOURCECODEPARTDEFINITIONID] uniqueidentifier,
    [CODE] nvarchar(10),
    [MKTSOURCECODEPARTDEFINITIONVALUESID] uniqueidentifier
  );

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

    set @CURRENTDATE = getdate();

    select
      @SOURCECODEMAPID = [MKTSOURCECODEMAP].[ID],
      @QUERYVIEWCATALOGID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
      @SOURCECODEID = [MKTSEGMENTWHITEMAIL].[SOURCECODEID],
      @SEGMENTATIONID = [MKTSEGMENTWHITEMAIL].[SEGMENTATIONID],
      @ACTIVE = isnull([MKTSEGMENTATION].[ACTIVE], 0)
    from dbo.[MKTSEGMENTWHITEMAIL]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTWHITEMAIL].[ID]
    inner join dbo.[MKTSOURCECODEMAP] on [MKTSOURCECODEMAP].[WHITEMAILSEGMENTID] = [MKTSEGMENTWHITEMAIL].[ID]
    left outer join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTWHITEMAIL].[SEGMENTATIONID]
    where [MKTSEGMENTWHITEMAIL].[ID] = @SEGMENTID;

    update dbo.[MKTSOURCECODEMAP] set
      [DONORQUERYVIEWCATALOGID] = @QUERYVIEWCATALOGID,
      [SOURCECODEID] = @SOURCECODEID,
      [SOURCECODE] = dbo.[UFN_MKTSOURCECODE_BUILDCODE](@SEGMENTID, null, null),
      [SEGMENTATIONID] = @SEGMENTATIONID,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @SOURCECODEMAPID;

    insert into @WORKTABLE (
      [MKTSOURCECODEMAPID],
      [MKTSOURCECODEPARTDEFINITIONID],
      [CODE],
      [MKTSOURCECODEPARTDEFINITIONVALUESID]
    )
    select
      @SOURCECODEMAPID,
      [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID],
      dbo.[UFN_MKTSOURCECODE_BUILDCODE](@SEGMENTID, null, [MKTSOURCECODEITEM].[ID]) as [CODE],
      case when [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0
           then case when exists (select top 1 1 from dbo.[MKTSOURCECODEPART] where [SOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID] and [WHITEMAILSEGMENTID] = @SEGMENTID)
                     then (select [PARTDEFINITIONVALUESID] from dbo.[MKTSOURCECODEPART] where [SOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID] and [WHITEMAILSEGMENTID] = @SEGMENTID)
                     else (select [PARTDEFINITIONVALUESID] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID)
                end
           when [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 1
           then [MKTSEGMENT].[PARTDEFINITIONVALUESID]
           when [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 5
           then case when exists (select top 1 1 from dbo.[MKTSOURCECODEPART] where [SOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID] and [WHITEMAILSEGMENTID] = @SEGMENTID)
                     then (select [PARTDEFINITIONVALUESID] from dbo.[MKTSOURCECODEPART] where [SOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID] and [WHITEMAILSEGMENTID] = @SEGMENTID)
                     else (select [PARTDEFINITIONVALUESID] from dbo.[MKTSOURCECODEPART] where [SEGMENTATIONID] = @SEGMENTATIONID and [SOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID] and [SEGMENTATIONSEGMENTID] is null and [SEGMENTATIONTESTSEGMENTID] is null and [WHITEMAILSEGMENTID] is null)
                end
           else 
                (select [PARTDEFINITIONVALUESID] from dbo.[MKTSOURCECODEPART] where [SOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID] and [WHITEMAILSEGMENTID] = @SEGMENTID)
      end as [MKTSOURCECODEPARTDEFINITIONVALUESID]
    from dbo.[MKTSOURCECODEMAP]
    inner join dbo.[MKTSOURCECODEITEM] on [MKTSOURCECODEITEM].[SOURCECODEID] = [MKTSOURCECODEMAP].[SOURCECODEID]
    inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
    inner join dbo.[MKTSEGMENTWHITEMAIL] on [MKTSEGMENTWHITEMAIL].[ID] = [MKTSOURCECODEMAP].[WHITEMAILSEGMENTID]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTWHITEMAIL].[ID]
    where [MKTSOURCECODEMAP].[WHITEMAILSEGMENTID] = @SEGMENTID;

    -- remove any parts that were dropped from the source code

    delete from dbo.[MKTSOURCECODEMAPPART]
    where [MKTSOURCECODEMAPID] = @SOURCECODEMAPID
    and [MKTSOURCECODEPARTDEFINITIONID] not in (select [MKTSOURCECODEPARTDEFINITIONID] from @WORKTABLE);

    -- update any parts that are still in the source code

    update dbo.[MKTSOURCECODEMAPPART] set
      [CODE] = [W].[CODE],
      [MKTSOURCECODEPARTDEFINITIONVALUESID] = [W].[MKTSOURCECODEPARTDEFINITIONVALUESID]
    from dbo.[MKTSOURCECODEMAPPART]
    inner join @WORKTABLE as [W] on ([W].[MKTSOURCECODEMAPID] = [MKTSOURCECODEMAPPART].[MKTSOURCECODEMAPID] and [W].[MKTSOURCECODEPARTDEFINITIONID] = [MKTSOURCECODEMAPPART].[MKTSOURCECODEPARTDEFINITIONID]);

    -- add any parts that are new to the source code

    insert into dbo.[MKTSOURCECODEMAPPART] (
      [ID],
      [MKTSOURCECODEMAPID],
      [MKTSOURCECODEPARTDEFINITIONID],
      [CODE],
      [MKTSOURCECODEPARTDEFINITIONVALUESID],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    )
    select
      newid(),
      [W].[MKTSOURCECODEMAPID],
      [W].[MKTSOURCECODEPARTDEFINITIONID],
      [W].[CODE],
      [W].[MKTSOURCECODEPARTDEFINITIONVALUESID],
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    from @WORKTABLE as [W]
    left outer join dbo.[MKTSOURCECODEMAPPART] on ([MKTSOURCECODEMAPPART].[MKTSOURCECODEMAPID] = [W].[MKTSOURCECODEMAPID] and [MKTSOURCECODEMAPPART].[MKTSOURCECODEPARTDEFINITIONID] = [W].[MKTSOURCECODEPARTDEFINITIONID])
    where [MKTSOURCECODEMAPPART].[ID] is null;

  end try

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

  return 0;