USP_MKTSEGMENTWHITEMAIL_ADDSOURCECODEMAP

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

  declare @QUERYVIEWCATALOGID uniqueidentifier;
  declare @SOURCECODEID uniqueidentifier;
  declare @SEGMENTATIONID uniqueidentifier;
  declare @SQL nvarchar(max);
  declare @CURRENTDATE datetime;

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

    set @CURRENTDATE = getdate();

    select
      @QUERYVIEWCATALOGID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
      @SOURCECODEID = [MKTSEGMENTWHITEMAIL].[SOURCECODEID],
      @SEGMENTATIONID = [MKTSEGMENTWHITEMAIL].[SEGMENTATIONID]
    from dbo.[MKTSEGMENTWHITEMAIL]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTWHITEMAIL].[ID]
    left outer join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTWHITEMAIL].[SEGMENTATIONID]
    where [MKTSEGMENTWHITEMAIL].[ID] = @SEGMENTID;

    insert into dbo.[MKTSOURCECODEMAP]
    (
      [ID],
      [WHITEMAILSEGMENTID],
      [DONORQUERYVIEWCATALOGID],
      [SOURCECODEID],
      [SOURCECODE],
      [SEGMENTATIONID],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    )
    values
    (
      newid(),
      @SEGMENTID,
      @QUERYVIEWCATALOGID,
      @SOURCECODEID,
      dbo.[UFN_MKTSOURCECODE_BUILDCODE](@SEGMENTID, null, null),
      @SEGMENTATIONID,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );

    insert into dbo.[MKTSOURCECODEMAPPART] (
      [ID],
      [MKTSOURCECODEMAPID],
      [MKTSOURCECODEPARTDEFINITIONID],
      [CODE],
      [MKTSOURCECODEPARTDEFINITIONVALUESID],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    )
    select
      newid() as [ID],
      [MKTSOURCECODEMAP].[ID] as [MKTSOURCECODEMAPID],
      [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],
      @CHANGEAGENTID as [ADDEDBYID],
      @CHANGEAGENTID as [CHANGEDBYID],
      @CURRENTDATE as [DATEADDED],
      @CURRENTDATE as [DATECHANGED]
    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;
  end try

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

  return 0;