USP_MKTSPONSORSHIPMAILINGTEMPLATE_COPY

CREATE procedure dbo.[USP_MKTSPONSORSHIPMAILINGTEMPLATE_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 sponsorship mailing template */

    declare @DATALOADED bit;
    declare @CODE nvarchar(10);
    declare @CODEVALUEID uniqueidentifier;
    declare @NAME nvarchar(100);
    declare @DESCRIPTION nvarchar(255);
    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 @TSLONG bigint;
    declare @ACTIVATIONKPIS xml;
    declare @USEKPISASDEFAULT bit;
    declare @RUNACTIVATEANDEXPORT bit;
    declare @EXPORTDESCRIPTION nvarchar(255);
    declare @MAILEXPORTDEFINITIONID uniqueidentifier;
    declare @EMAILEXPORTDEFINITIONID uniqueidentifier;
    declare @RUNSEGMENTATIONSEGMENTREFRESHPROCESS bit;
    declare @CACHESOURCEANALYSISRULEDATA bit;
    declare @BASECURRENCYID uniqueidentifier;
    declare @RUNMARKETINGEXCLUSIONSREPORT bit;

    exec dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTSPONSORSHIPMAILINGTEMPLATE_2]
      @ID = @SOURCEID,
      @CURRENTAPPUSERID = @CURRENTAPPUSERID,
      @DATALOADED = @DATALOADED output,
      @CODE = @CODE output,
      @NAME = @NAME output,
      @DESCRIPTION = @DESCRIPTION output,
      @SITEID = @SITEID output,
      @SOURCECODEID = @SOURCECODEID output,
      @ITEMLIST = @ITEMLIST output,
      @SITEREQUIRED = @SITEREQUIRED output,
      @SITECANBECHANGED = @SITECANBECHANGED 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,
      @TSLONG = @TSLONG output,
      @ACTIVATIONKPIS = @ACTIVATIONKPIS output,
      @USEKPISASDEFAULT = @USEKPISASDEFAULT output,
      @APPEALINFORMATION = null,
      @RUNACTIVATEANDEXPORT = @RUNACTIVATEANDEXPORT output,
      @EXPORTDESCRIPTION = @EXPORTDESCRIPTION output,
      @MAILEXPORTDEFINITIONID = @MAILEXPORTDEFINITIONID output,
      @EMAILEXPORTDEFINITIONID = @EMAILEXPORTDEFINITIONID output,
      @CODEVALUEID = @CODEVALUEID output,
      @RUNSEGMENTATIONSEGMENTREFRESHPROCESS = @RUNSEGMENTATIONSEGMENTREFRESHPROCESS output,
      @CACHESOURCEANALYSISRULEDATA = @CACHESOURCEANALYSISRULEDATA output,
      @BASECURRENCYID = @BASECURRENCYID output,
      @RUNMARKETINGEXCLUSIONSREPORT = @RUNMARKETINGEXCLUSIONSREPORT output;

    declare @UNIQUENAME nvarchar(50);
set @UNIQUENAME = dbo.[UFN_MKTSPONSORSHIPMAILINGTEMPLATE_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]([MKTSPONSORSHIPMAILINGPROCESS].[ID])
    from dbo.[MKTSPONSORSHIPMAILINGTEMPLATE]
    inner join dbo.[MKTSPONSORSHIPMAILINGPROCESS] on [MKTSPONSORSHIPMAILINGPROCESS].[SPONSORSHIPMAILINGTEMPLATEID] = [MKTSPONSORSHIPMAILINGTEMPLATE].[ID]
    where [MKTSPONSORSHIPMAILINGTEMPLATE].[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_MKTSPONSORSHIPMAILINGTEMPLATE] 
      @ID = @TARGETID output,  
      @CURRENTAPPUSERID = @CURRENTAPPUSERID,
      @CHANGEAGENTID = @CHANGEAGENTID,    
      @CODE = @CODE,
      @NAME = @UNIQUENAME,
      @DESCRIPTION = @DESCRIPTION,
      @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,
      @RUNACTIVATEANDEXPORT = @RUNACTIVATEANDEXPORT,
      @EXPORTDESCRIPTION = @EXPORTDESCRIPTION,
      @MAILEXPORTDEFINITIONID = @MAILEXPORTDEFINITIONID,
      @EMAILEXPORTDEFINITIONID = @EMAILEXPORTDEFINITIONID,
      @CODEVALUEID = @CODEVALUEID,
      @RUNSEGMENTATIONSEGMENTREFRESHPROCESS = @RUNSEGMENTATIONSEGMENTREFRESHPROCESS,
      @CACHESOURCEANALYSISRULEDATA = @CACHESOURCEANALYSISRULEDATA,
      @BASECURRENCYID = @BASECURRENCYID,
      @RUNMARKETINGEXCLUSIONSREPORT = @RUNMARKETINGEXCLUSIONSREPORT;

    /* copy the universe/exclusion selection filters */

    insert into dbo.[MKTSPONSORSHIPMAILINGTEMPLATEFILTERSELECTION] (
      [SPONSORSHIPMAILINGTEMPLATEID],
      [FILTERTYPECODE],
      [SELECTIONID],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    )
    select
      @TARGETID,
      [FILTERTYPECODE],
      [SELECTIONID],
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    from dbo.[MKTSPONSORSHIPMAILINGTEMPLATEFILTERSELECTION]
    where [SPONSORSHIPMAILINGTEMPLATEID] = @SOURCEID;


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


    /* copy the appeals    */

    insert into dbo.[MKTSPONSORSHIPMAILINGTEMPLATEAPPEAL] (
      [ID],
      [SPONSORSHIPMAILINGTEMPLATEID],
      [RECORDSOURCEID],
      [APPEALSYSTEMID],
      [APPEALID],
      [APPEALDESCRIPTION],
      [ADDEDBYID],
      [CHANGEDBYID],
     [DATEADDED],
      [DATECHANGED]
    )
    select
      newid(),
      @TARGETID,
      [RECORDSOURCEID],
      [APPEALSYSTEMID],
      [APPEALID],
      [APPEALDESCRIPTION],
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    from dbo.[MKTSPONSORSHIPMAILINGTEMPLATEAPPEAL]
    where [SPONSORSHIPMAILINGTEMPLATEID] = @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.[MKTSPONSORSHIPMAILINGTEMPLATERULE] 
    (
      [ID],
      [SPONSORSHIPMAILINGTEMPLATEID],
      [SEGMENTID],
      [CODE],
      [PARTDEFINITIONVALUESID],
      [TESTSEGMENTCODE],
      [TESTPARTDEFINITIONVALUESID],
      [PACKAGEID],
      [RESPONSERATE],
      [GIFTAMOUNT],
      [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],
      [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.[MKTSPONSORSHIPMAILINGTEMPLATERULE]
    where [SPONSORSHIPMAILINGTEMPLATEID] = @SOURCEID;


    /* copy the seeds */

    insert into dbo.[MKTSPONSORSHIPMAILINGTEMPLATESEED] 
    (
      [SPONSORSHIPMAILINGTEMPLATEID],
      [SEEDID],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    )
    select
      @TARGETID,
      [SEEDID],
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    from dbo.[MKTSPONSORSHIPMAILINGTEMPLATESEED]
    where [SPONSORSHIPMAILINGTEMPLATEID] = @SOURCEID;


    /* copy any ask ladder overrides */

    insert into dbo.[MKTSPONSORSHIPMAILINGTEMPLATEASKLADDEROVERRIDE] 
    (
      [ID],
      [SPONSORSHIPMAILINGTEMPLATEID],
      [ASKLADDERID],
      [IDSETREGISTERID],
      [SEQUENCE],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    )
    select
      newid(),
      @TARGETID,
      [ASKLADDERID],
      [IDSETREGISTERID],
      [SEQUENCE],
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    from dbo.[MKTSPONSORSHIPMAILINGTEMPLATEASKLADDEROVERRIDE]
    where [SPONSORSHIPMAILINGTEMPLATEID] = @SOURCEID;

    -- delete KPIs that are in the Target, but not in the Source

    declare DELETECURSOR cursor local fast_forward for
      select
        ID
      from
        dbo.MKTSPONSORSHIPMAILINGTEMPLATEKPI
      where
        SPONSORSHIPMAILINGTEMPLATEID = @TARGETID and
        KPICATALOGID not in (select KPICATALOGID from dbo.MKTSPONSORSHIPMAILINGTEMPLATEKPI where SPONSORSHIPMAILINGTEMPLATEID = @SOURCEID)

    open DELETECURSOR;
    fetch next from DELETECURSOR into @ID;

    while (@@FETCH_STATUS = 0)
      begin
        exec dbo.[USP_MKTSPONSORSHIPMAILINGTEMPLATEKPI_DELETEBYID_WITHCHANGEAGENTID] @ID, @CHANGEAGENTID;
        fetch next from DELETECURSOR into @ID;
      end

    close DELETECURSOR;
    deallocate DELETECURSOR;

  end try

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

  return 0;