USP_MKTCOMMUNICATIONNAMESCHEME_COPY

CREATE procedure dbo.[USP_MKTCOMMUNICATIONNAMESCHEME_COPY]
(
  @ID uniqueidentifier output,
  @CURRENTAPPUSERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @UNIQUENAME nvarchar(100),
  @MKTSEGMENTATIONID uniqueidentifier = null
)
as
  set nocount on;

  declare @SOURCENAMESCHEMEID uniqueidentifier;
  declare @TARGETNAMESCHEMEID uniqueidentifier;
  declare @PARENTCOMMUNICATIONNAMESCHEMEID uniqueidentifier;
  declare @ORIGINALPARENTCOMMUNICATIONNAMESCHEMEID uniqueidentifier;

  begin try
    set @SOURCENAMESCHEMEID = @ID;

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

    declare @SITEID uniqueidentifier;
    declare @NAMEPARTS xml;
    declare @ISACTIVE bit;


    /* load existing name pattern */
    exec dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTCOMMUNICATIONNAMESCHEME]
      @ID = @SOURCENAMESCHEMEID,
      @CURRENTAPPUSERID = @CURRENTAPPUSERID,
      @SITEID = @SITEID output,
      @NAMEPARTS = @NAMEPARTS output,
      @ISACTIVE = @ISACTIVE output;

    /* remove nameparts ids, unique ids will be added back in the name pattern add */
    set @NAMEPARTS.modify('delete (/NAMEPARTS/ITEM/@ID)')    

    if @MKTSEGMENTATIONID is not null
    begin
      set @PARENTCOMMUNICATIONNAMESCHEMEID = @SOURCENAMESCHEMEID;

      -- when copying for an effort we need to get the top level template

      select
        @ORIGINALPARENTCOMMUNICATIONNAMESCHEMEID = [MKTCOMMUNICATIONNAMESCHEME].[PARENTCOMMUNICATIONNAMESCHEMEID]
      from
        dbo.[MKTCOMMUNICATIONNAMESCHEME]
      where
        [MKTCOMMUNICATIONNAMESCHEME].[ID] = @SOURCENAMESCHEMEID;

      if @ORIGINALPARENTCOMMUNICATIONNAMESCHEMEID is not null
        set @PARENTCOMMUNICATIONNAMESCHEMEID = @ORIGINALPARENTCOMMUNICATIONNAMESCHEMEID;

    end

    /* create the copy of the name pattern */
    exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTCOMMUNICATIONNAMESCHEME]
      @ID = @TARGETNAMESCHEMEID output,
      @CHANGEAGENTID = @CHANGEAGENTID,
      @NAME = @UNIQUENAME,
      @SITEID = @SITEID,
      @NAMEPARTS = @NAMEPARTS,
      @MKTSEGMENTATIONID = @MKTSEGMENTATIONID,
      @PARENTCOMMUNICATIONNAMESCHEMEID = @PARENTCOMMUNICATIONNAMESCHEMEID;

    /* update active status */
    update dbo.[MKTCOMMUNICATIONNAMESCHEME]
      set 
        [ISACTIVE] = @ISACTIVE,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = getdate()
    where [ID] = @TARGETNAMESCHEMEID;

    /* return the new name pattern ID */
    set @ID = @TARGETNAMESCHEMEID;
  end try

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

  return 0;