USP_MKTACKNOWLEDGEMENTMAILINGTEMPLATE_COPY

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

  declare @SOURCEID uniqueidentifier;
  declare @TARGETID uniqueidentifier;
  declare @CURRENTDATE datetime;

  begin try
    set @SOURCEID = @ID;
    set @TARGETID = newid();

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

    set @CURRENTDATE = getdate();


    /***************************/
    /* Create the new template */
    /***************************/
    declare @DATALOADED bit;
    declare @CODE nvarchar(10);
    declare @CODEVALUEID uniqueidentifier;
    declare @NAME nvarchar(100);
    declare @DESCRIPTION nvarchar(255);
    declare @MARKLETTERSACKNOWLEDGED bit;
    declare @ACKNOWLEDGEDATETYPECODE tinyint;
    declare @ACKNOWLEDGEDATE datetime;
    declare @SITEID uniqueidentifier;
    declare @SOURCECODEID uniqueidentifier;
    declare @ITEMLIST xml;
    declare @SITEREQUIRED bit;
    declare @SITECANBECHANGED bit;
    declare @ISBBEC bit;
    declare @OWNERID uniqueidentifier;
    declare @EXCLUSIONDATETYPECODE tinyint;
    declare @EXCLUSIONASOFDATE datetime;
    declare @EXCLUDEDECEASED bit;
    declare @EXCLUDEINACTIVE bit;
    declare @EXCLUSIONS xml;
    declare @USEADDRESSPROCESSING bit;
    declare @ADDRESSPROCESSINGOPTIONID uniqueidentifier;
    declare @NAMEFORMATPARAMETERID uniqueidentifier;
    declare @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint;
    declare @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime;
    declare @CANUPDATEEXCLUDEDECEASED bit;
    declare @CANUPDATEEXCLUDEINACTIVE bit;
    declare @TSLONG bigint;
    declare @ACTIVATIONKPIS xml;
    declare @USEKPISASDEFAULT bit;
    declare @MULTIPLELETTERSPERREVENUE bit;
    declare @RUNACTIVATEANDEXPORT bit;
    declare @EXPORTDESCRIPTION nvarchar(255);
    declare @CACHESOURCEANALYSISRULEDATA bit;
    declare @BASECURRENCYID uniqueidentifier;
    declare @RUNMARKETINGEXCLUSIONSREPORT bit;

    exec dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTACKNOWLEDGEMENTMAILINGTEMPLATE_2]
      @ID = @SOURCEID,
      @CURRENTAPPUSERID = @CURRENTAPPUSERID,
      @DATALOADED = @DATALOADED output,
      @CODE = @CODE output,
      @NAME = @NAME output,
      @DESCRIPTION = @DESCRIPTION output,
      @MARKLETTERSACKNOWLEDGED = @MARKLETTERSACKNOWLEDGED output,
      @ACKNOWLEDGEDATETYPECODE = @ACKNOWLEDGEDATETYPECODE output,
      @ACKNOWLEDGEDATE = @ACKNOWLEDGEDATE output,
      @SITEID = @SITEID output,
      @SOURCECODEID = @SOURCECODEID output,
      @ITEMLIST = @ITEMLIST output,
      @SITEREQUIRED = @SITEREQUIRED output,
      @SITECANBECHANGED = @SITECANBECHANGED output,
      @ISBBEC = @ISBBEC output,
      @OWNERID = @OWNERID output,
      @EXCLUSIONDATETYPECODE = @EXCLUSIONDATETYPECODE output,
      @EXCLUSIONASOFDATE = @EXCLUSIONASOFDATE output,
      @EXCLUDEDECEASED = @EXCLUDEDECEASED output,
      @EXCLUDEINACTIVE = @EXCLUDEINACTIVE output,
      @EXCLUSIONS = null,
      @USEADDRESSPROCESSING = @USEADDRESSPROCESSING output,
      @ADDRESSPROCESSINGOPTIONID = @ADDRESSPROCESSINGOPTIONID output,
      @NAMEFORMATPARAMETERID = @NAMEFORMATPARAMETERID output,
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE output,
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE output,
      @CANUPDATEEXCLUDEDECEASED = @CANUPDATEEXCLUDEDECEASED output,
      @CANUPDATEEXCLUDEINACTIVE = @CANUPDATEEXCLUDEINACTIVE output,
      @TSLONG = @TSLONG output,
      @ACTIVATIONKPIS = @ACTIVATIONKPIS output,
      @USEKPISASDEFAULT = @USEKPISASDEFAULT output,
      @APPEALINFORMATION = null,  --this will be copied below separately

      @MULTIPLELETTERSPERREVENUE = @MULTIPLELETTERSPERREVENUE output,
      @RUNACTIVATEANDEXPORT = @RUNACTIVATEANDEXPORT output,
      @EXPORTDESCRIPTION = @EXPORTDESCRIPTION output,
      @CODEVALUEID = @CODEVALUEID output,
      @CACHESOURCEANALYSISRULEDATA = @CACHESOURCEANALYSISRULEDATA output,
      @BASECURRENCYID = @BASECURRENCYID output,
      @RUNMARKETINGEXCLUSIONSREPORT = @RUNMARKETINGEXCLUSIONSREPORT output;

    declare @UNIQUENAME nvarchar(50);
    set @UNIQUENAME = dbo.[UFN_MKTACKNOWLEDGEMENTMAILINGTEMPLATE_GETUNIQUENAME](@NAME);

    -- return only the selected solicit code exclusions 

    -- (required are always used by default and thus aren't even saved to the database)

    select
      @EXCLUSIONS = dbo.[UFN_MKTSEGMENTATION_GETSELECTEDSOLICITCODEEXCLUSIONS_TOITEMLISTXML]([MKTACKNOWLEDGEMENTMAILINGPROCESS].[ID])
    from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATE]
    inner join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESS] on [MKTACKNOWLEDGEMENTMAILINGPROCESS].[ACKNOWLEDGEMENTMAILINGTEMPLATEID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ID]
    where [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ID] = @SOURCEID;
    -- null out existing IDs from solicit code exclusions

    if @EXCLUSIONS is not null
      set @EXCLUSIONS.modify('delete /EXCLUSIONS/ITEM/ID');

    exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTACKNOWLEDGEMENTMAILINGTEMPLATE] 
      @ID = @TARGETID output,
      @CURRENTAPPUSERID = @CURRENTAPPUSERID,
      @CHANGEAGENTID = @CHANGEAGENTID,
      @CODE = @CODE,
      @NAME = @UNIQUENAME,
      @DESCRIPTION = @DESCRIPTION,
      @MARKLETTERSACKNOWLEDGED = @MARKLETTERSACKNOWLEDGED,
      @ACKNOWLEDGEDATETYPECODE = @ACKNOWLEDGEDATETYPECODE,
      @ACKNOWLEDGEDATE = @ACKNOWLEDGEDATE,
      @INCLUDESELECTIONS = null,
      @EXCLUDESELECTIONS = null,
      @SOURCECODEID = @SOURCECODEID,
      @ITEMLIST = null,
      @SITEID = @SITEID,
      @EXCLUSIONDATETYPECODE = @EXCLUSIONDATETYPECODE,
      @EXCLUSIONASOFDATE = @EXCLUSIONASOFDATE,
      @EXCLUDEDECEASED = @EXCLUDEDECEASED,
      @EXCLUDEINACTIVE = @EXCLUDEINACTIVE,
      @EXCLUSIONS = @EXCLUSIONS,
      @USEADDRESSPROCESSING = @USEADDRESSPROCESSING,
      @ADDRESSPROCESSINGOPTIONID = @ADDRESSPROCESSINGOPTIONID,
      @NAMEFORMATPARAMETERID = @NAMEFORMATPARAMETERID,
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE,
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE,
      @ACTIVATIONKPIS = @ACTIVATIONKPIS,
      @USEKPISASDEFAULT = @USEKPISASDEFAULT,
      @APPEALINFORMATION = null,  --this will be copied below separately

      @MULTIPLELETTERSPERREVENUE = @MULTIPLELETTERSPERREVENUE,
      @RUNACTIVATEANDEXPORT = @RUNACTIVATEANDEXPORT,
      @EXPORTDESCRIPTION = @EXPORTDESCRIPTION,
      @CODEVALUEID = @CODEVALUEID,
      @CACHESOURCEANALYSISRULEDATA = @CACHESOURCEANALYSISRULEDATA,
      @BASECURRENCYID = @BASECURRENCYID,
      @RUNMARKETINGEXCLUSIONSREPORT = @RUNMARKETINGEXCLUSIONSREPORT;


    /**************************************************/
    /* Copy the universe/exclusion selection filters */
    /**************************************************/
    insert into dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATEFILTERSELECTION] (
      [ACKNOWLEDGEMENTMAILINGTEMPLATEID],
      [FILTERTYPECODE],
      [SELECTIONID],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    )
    select
      @TARGETID,
      [FILTERTYPECODE],
      [SELECTIONID],
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATEFILTERSELECTION]
    where [ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @SOURCEID;


    /******************************/
    /* Copy the source code parts */
    /******************************/
    insert into dbo.[MKTSOURCECODEPART] (
      [ACKNOWLEDGEMENTMAILINGTEMPLATEID],
      [SOURCECODEITEMID],
      [LISTID],
      [CHANNELCODE],
      [CODE],
      [PARTDEFINITIONVALUESID],
[ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    )
    select
      @TARGETID,
      [SOURCECODEITEMID],
      [LISTID],
      [CHANNELCODE],
      [CODE],
      [PARTDEFINITIONVALUESID],
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    from dbo.[MKTSOURCECODEPART]
    where [ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @SOURCEID;


    /******************/
    /* Copy the seeds */
    /******************/
    insert into dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATESEED] (
      [ACKNOWLEDGEMENTMAILINGTEMPLATEID],
      [SEEDID],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    )
    select
      @TARGETID,
      [SEEDID],
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATESEED]
    where [ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @SOURCEID;


    /*********************************/
    /* Copy any ask ladder overrides */
    /*********************************/
    insert into dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATEASKLADDEROVERRIDE] (
      [ID],
      [ACKNOWLEDGEMENTMAILINGTEMPLATEID],
      [ASKLADDERID],
      [IDSETREGISTERID],
      [SEQUENCE],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    )
    select
      newid(),
      @TARGETID,
      [ASKLADDERID],
      [IDSETREGISTERID],
      [SEQUENCE],
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATEASKLADDEROVERRIDE]
    where [ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @SOURCEID;


    /********************************/
    /* Copy the appeals             */
    /********************************/
    insert into dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATEAPPEAL] (
      [ID],
      [ACKNOWLEDGEMENTTEMPLATEID],
      [RECORDSOURCEID],
      [APPEALSYSTEMID],
      [APPEALID],
      [APPEALDESCRIPTION],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    )
    select
      newid(),
      @TARGETID,
      [RECORDSOURCEID],
      [APPEALSYSTEMID],
      [APPEALID],
      [APPEALDESCRIPTION],
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATEAPPEAL]
    where [ACKNOWLEDGEMENTTEMPLATEID] = @SOURCEID;


    /********************************/
    /* Copy the rules               */
    /********************************/
    declare @CURRENCYEXCHANGERATEID uniqueidentifier;
    declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

    if @ORGANIZATIONCURRENCYID <> @BASECURRENCYID
      set @CURRENCYEXCHANGERATEID = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null);

    insert into dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] (
      [ID],
      [ACKNOWLEDGEMENTMAILINGTEMPLATEID],
      [SEGMENTID],
      [CODE],
      [PARTDEFINITIONVALUESID],
      [TESTSEGMENTCODE],
      [TESTPARTDEFINITIONVALUESID],
      [PACKAGEID],
      [RESPONSERATE],
      [GIFTAMOUNT],
      [MAXDAYSCHECKED],
      [MAXDAYS],
      [MINQUANTITYCHECKED],
      [MINQUANTITY],
      [SEQUENCE],
      [ASKLADDERID],
      [OVERRIDEADDRESSPROCESSING],
      [USEADDRESSPROCESSING],
      [ADDRESSPROCESSINGOPTIONID],
      [ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
      [ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
      [NAMEFORMATPARAMETERID],
      [BASECURRENCYID],
      [ORGANIZATIONGIFTAMOUNT],
      [CURRENCYEXCHANGERATEID],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED],
      [EXCLUDE]
    ) 
    select
      newid(),
      @TARGETID,
      [SEGMENTID],
      [CODE],
      [PARTDEFINITIONVALUESID],
      [TESTSEGMENTCODE],
      [TESTPARTDEFINITIONVALUESID],
      [PACKAGEID],
      [RESPONSERATE],
      [GIFTAMOUNT],
      [MAXDAYSCHECKED],
      [MAXDAYS],
      [MINQUANTITYCHECKED],
      [MINQUANTITY],
      [SEQUENCE],
      [ASKLADDERID],
      [OVERRIDEADDRESSPROCESSING],
      [USEADDRESSPROCESSING],
      [ADDRESSPROCESSINGOPTIONID],
      [ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
      [ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
      [NAMEFORMATPARAMETERID],
      [BASECURRENCYID],
      (case when @ORGANIZATIONCURRENCYID = @BASECURRENCYID then [GIFTAMOUNT] else dbo.[UFN_CURRENCY_CONVERT]([GIFTAMOUNT], @CURRENCYEXCHANGERATEID) end),
      @CURRENCYEXCHANGERATEID,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE,
      [EXCLUDE]
    from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE]
    where [ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @SOURCEID;
  end try

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

  return 0;