USP_MKTSOURCECODE_COPY

CREATE procedure dbo.[USP_MKTSOURCECODE_COPY]
(
  @ID uniqueidentifier output,
  @CHANGEAGENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier
)
as
  set nocount on;

  declare @ORIGINALSOURCECODEID uniqueidentifier;
  declare @TARGETSOURCECODEID uniqueidentifier;

  set @ORIGINALSOURCECODEID = @ID;
  set @TARGETSOURCECODEID = newID();

  declare @RC int;
  declare @DATALOADED bit;
  declare @NAME nvarchar(100);
  declare @SITEID uniqueidentifier;
  declare @ISDEFAULT bit;
  declare @SITEREQUIRED bit;
  declare @DESCRIPTION nvarchar(255);
  declare @INUSE bit;
  declare @TSLONG bigint;

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

  exec @RC = dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTSOURCECODE_2]
    @ID = @ORIGINALSOURCECODEID,
    @CURRENTAPPUSERID = @CURRENTAPPUSERID,
    @DATALOADED = @DATALOADED output,
    @NAME = @NAME output,
    @SITEID = @SITEID output,
    @ISDEFAULT = 0,
    @SITEREQUIRED = @SITEREQUIRED output,
    @DESCRIPTION = @DESCRIPTION output,
    @INUSE = @INUSE output,
    @TSLONG = @TSLONG output;

  declare @UNIQUENAME nvarchar(100);
  set @UNIQUENAME = dbo.[UFN_MKTSOURCECODE_GETUNIQUENAME](@ORIGINALSOURCECODEID, @NAME, null);

  /* Now create the new source code layout */
  exec @RC = dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSOURCECODE]
    @TARGETSOURCECODEID,
    @CHANGEAGENTID,
    @UNIQUENAME,
    0,
    @SITEID,
    @DESCRIPTION;

  /* Go through each item in the mktsourcecodeitem table and add the parts and valid values to the new layout. */
  declare @ORIGINALSOURCECODEITEMID uniqueidentifier;
  declare @TARGETSOURCECODEITEMID uniqueidentifier;
  declare @MKTSOURCECODEDEFINITIONTYPES xml;
  declare @ITEMTYPENAME nvarchar(50);
  declare @MKTSOURCECODEPARTDEFINITIONID uniqueidentifier;
  declare @MKTSOURCECODEPARTDEFINITIONVALUES xml;
  declare @LENGTH tinyint;
  declare @SEQUENCE tinyint;
  declare @SELECTEDVALIDPARTS xml;

  declare ITEMCURSOR cursor local fast_forward for
    select 
      [ID],
      [MKTSOURCECODEPARTDEFINITIONID]
    from dbo.[MKTSOURCECODEITEM]
    where [SOURCECODEID] = @ORIGINALSOURCECODEID
    order by [SEQUENCE];

  open ITEMCURSOR;
  fetch next from ITEMCURSOR into @ORIGINALSOURCECODEITEMID, @MKTSOURCECODEPARTDEFINITIONID;

  while (@@FETCH_STATUS = 0)
  begin
    set @TARGETSOURCECODEITEMID = null;

    /* Get the part definition ID and valid values from the current sourcecodeitem */
    exec @RC = dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTSOURCECODELAYOUTPART]
      @ID = @ORIGINALSOURCECODEITEMID,
      @CURRENTAPPUSERID = @CURRENTAPPUSERID,
      @DATALOADED = @DATALOADED output,
      @TSLONG = @TSLONG output,
      @MKTSOURCECODEDEFINITIONTYPES = @MKTSOURCECODEDEFINITIONTYPES output,
      @ITEMTYPENAME = @ITEMTYPENAME output,
      @MKTSOURCECODEPARTDEFINITION = @MKTSOURCECODEPARTDEFINITIONID output,
      @MKTSOURCECODEPARTDEFINITIONVALUES = @MKTSOURCECODEPARTDEFINITIONVALUES output,    
      @LENGTH = @LENGTH output,
      @SEQUENCE = @SEQUENCE output,
      @SELECTEDVALIDPARTS = @SELECTEDVALIDPARTS output;  

    /* Get the valid source code parts for the current source code item ID */
    declare @SELECTEDITEMS table
    (
      [ID] uniqueidentifier,
      [MKTSOURCECODEPARTDEFINITIONVALUESID] uniqueidentifier,
      [VALUESELECTED] nvarchar(10)
    );

    /* Clear selected parts xml and selected parts table. */
    set @SELECTEDVALIDPARTS = '';
    delete from @SELECTEDITEMS;

    insert into @SELECTEDITEMS
      select
        newID(),
        [MKTSOURCECODEPARTDEFINITIONVALUESID],
        'True'
      from dbo.[MKTSOURCECODEVALIDPARTVALUES]
      inner join dbo.[MKTSOURCECODEITEM] on [MKTSOURCECODEITEM].[ID] = [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEITEMID]
      where [MKTSOURCECODEITEM].[ID] = @ORIGINALSOURCECODEITEMID;

    set @SELECTEDVALIDPARTS = (
      select 
          [ID],
          [MKTSOURCECODEPARTDEFINITIONVALUESID],
          [VALUESELECTED]
    from @SELECTEDITEMS 
    for xml raw('ITEM'),type,elements,root('MKTSOURCECODEPARTDEFINITIONVALUES'),BINARY BASE64);  

    /* Add the part to the new layout with the valid values of the original source code layout */
    exec @RC = dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSOURCECODELAYOUTPART]
      @TARGETSOURCECODEITEMID,
      @TARGETSOURCECODEID,  
      @MKTSOURCECODEPARTDEFINITIONID,
      @SELECTEDVALIDPARTS,
      @CHANGEAGENTID,    
      @LENGTH,
      @SEQUENCE;

    fetch next from ITEMCURSOR into @ORIGINALSOURCECODEITEMID, @MKTSOURCECODEPARTDEFINITIONID;  
  end

  close ITEMCURSOR;
  deallocate ITEMCURSOR;

  return 0;