USP_MKTSPONSORSHIPMAILINGPROCESS_CREATEMAILING

CREATE procedure dbo.[USP_MKTSPONSORSHIPMAILINGPROCESS_CREATEMAILING]
(
  @SPONSORSHIPMAILINGTEMPLATEID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @SEGMENTATIONID uniqueidentifier output,
  @DATETEXT nvarchar(50),
  @CHANGEAGENTID uniqueidentifier
)
as
  set nocount on;

  declare @CURRENTDATE datetime;

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

    set @CURRENTDATE = getdate();


    -- create the new mailing


    declare @CODE nvarchar(10);
    declare @CODEVALUEID uniqueidentifier;
    declare @NAME nvarchar(100);
    declare @DESCRIPTION nvarchar(255);
    declare @SITEID uniqueidentifier;
    declare @SOURCECODEID 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 @RUNACTIVATEANDEXPORT bit;
    declare @RUNSEGMENTATIONSEGMENTREFRESHPROCESS bit;
    declare @CACHESOURCEANALYSISRULEDATA bit;
    declare @EXPORTDESCRIPTION nvarchar(255);
    declare @MAILEXPORTDEFINITIONID uniqueidentifier;
    declare @EMAILEXPORTDEFINITIONID uniqueidentifier;
    declare @PHONEEXPORTDEFINITIONID uniqueidentifier;
    declare @BASECURRENCYID uniqueidentifier;
    declare @RUNMARKETINGEXCLUSIONSREPORT bit;

    select
      @CODE = [MKTSPONSORSHIPMAILINGTEMPLATE].[CODE],
      @CODEVALUEID = [MKTSPONSORSHIPMAILINGTEMPLATE].[PARTDEFINITIONVALUESID],
      @NAME = dbo.[UFN_MKTSPONSORSHIPMAILINGPROCESS_GETUNIQUENAME]([MKTSPONSORSHIPMAILINGTEMPLATE].[NAME], @DATETEXT),
      @DESCRIPTION = [MKTSPONSORSHIPMAILINGTEMPLATE].[DESCRIPTION],
      @SITEID = [MKTSPONSORSHIPMAILINGTEMPLATE].[SITEID],
      @SOURCECODEID = [MKTSPONSORSHIPMAILINGTEMPLATE].[SOURCECODEID],
      @EXCLUSIONDATETYPECODE = [BUSINESSPROCESSCOMMPREF].[DATETYPECODE],
      @EXCLUSIONASOFDATE = [BUSINESSPROCESSCOMMPREF].[ASOFDATE],
      @EXCLUDEDECEASED = [BUSINESSPROCESSCOMMPREF].[EXCLUDEDECEASED],
      @EXCLUDEINACTIVE = [BUSINESSPROCESSCOMMPREF].[EXCLUDEINACTIVE],
      @EXCLUSIONS = dbo.[UFN_BUSINESSPROCESSCOMMPREF_GETEXCLUSIONS_TOITEMLISTXML]([MKTSPONSORSHIPMAILINGPROCESS].[ID]),
      @USEADDRESSPROCESSING = [MKTSPONSORSHIPMAILINGTEMPLATE].[USEADDRESSPROCESSING],
      @ADDRESSPROCESSINGOPTIONID = [MKTSPONSORSHIPMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONID],
      @NAMEFORMATPARAMETERID = [MKTSPONSORSHIPMAILINGTEMPLATE].[NAMEFORMATPARAMETERID],
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = [MKTSPONSORSHIPMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = [MKTSPONSORSHIPMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
      @RUNACTIVATEANDEXPORT = [MKTSPONSORSHIPMAILINGTEMPLATE].[RUNACTIVATEANDEXPORT],
      @RUNSEGMENTATIONSEGMENTREFRESHPROCESS = [MKTSPONSORSHIPMAILINGTEMPLATE].[RUNSEGMENTATIONSEGMENTREFRESHPROCESS],
      @CACHESOURCEANALYSISRULEDATA = [MKTSPONSORSHIPMAILINGTEMPLATE].[CACHESOURCEANALYSISRULEDATA],
      @EXPORTDESCRIPTION = isnull([MKTSPONSORSHIPMAILINGTEMPLATEEXPORT].[DESCRIPTION], ''),
      @MAILEXPORTDEFINITIONID = [MKTSPONSORSHIPMAILINGTEMPLATEEXPORT].[MAILEXPORTDEFINITIONID],
      @EMAILEXPORTDEFINITIONID = [MKTSPONSORSHIPMAILINGTEMPLATEEXPORT].[EMAILEXPORTDEFINITIONID],
      @PHONEEXPORTDEFINITIONID = [MKTSPONSORSHIPMAILINGTEMPLATEEXPORT].[PHONEEXPORTDEFINITIONID],
      @BASECURRENCYID = [MKTSPONSORSHIPMAILINGTEMPLATE].[BASECURRENCYID],
      @RUNMARKETINGEXCLUSIONSREPORT = [MKTSPONSORSHIPMAILINGTEMPLATE].[RUNMARKETINGEXCLUSIONSREPORT]
    from dbo.[MKTSPONSORSHIPMAILINGTEMPLATE]
    inner join dbo.[MKTSPONSORSHIPMAILINGPROCESS] on [MKTSPONSORSHIPMAILINGPROCESS].[SPONSORSHIPMAILINGTEMPLATEID] = [MKTSPONSORSHIPMAILINGTEMPLATE].[ID]
    left outer join dbo.[BUSINESSPROCESSCOMMPREF] on [BUSINESSPROCESSCOMMPREF].[BUSINESSPROCESSPARAMETERSETID] = [MKTSPONSORSHIPMAILINGPROCESS].[ID]
    left outer join dbo.[MKTSPONSORSHIPMAILINGTEMPLATEEXPORT] on [MKTSPONSORSHIPMAILINGTEMPLATEEXPORT].[SPONSORSHIPMAILINGTEMPLATEID] = [MKTSPONSORSHIPMAILINGTEMPLATE].[ID]
    where [MKTSPONSORSHIPMAILINGTEMPLATE].[ID] = @SPONSORSHIPMAILINGTEMPLATEID;

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

    exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATION] 
      @ID = @SEGMENTATIONID output,
      @CURRENTAPPUSERID = @CURRENTAPPUSERID,
      @CHANGEAGENTID = @CHANGEAGENTID,
      @CODE = @CODE,
      @NAME = @NAME,
      @DESCRIPTION = @DESCRIPTION,
      @INCLUDESELECTIONS = null,
      @EXCLUDESELECTIONS = null,
      @EXCLUDESEGMENTATIONS = null,
      @MARKETINGPLANITEMID = null,
      @SOURCECODEID = @SOURCECODEID,
      @ITEMLIST = null,
      @MAILDATE = @CURRENTDATE,
      @HOUSEHOLDINGTYPECODE = 0,
      @SITEID = @SITEID,
      @MAILINGTYPECODE = 3,
      @EXCLUSIONDATETYPECODE = @EXCLUSIONDATETYPECODE,
      @EXCLUSIONASOFDATE = @EXCLUSIONASOFDATE,
      @EXCLUDEDECEASED = @EXCLUDEDECEASED,
      @EXCLUDEINACTIVE = @EXCLUDEINACTIVE,
      @EXCLUSIONS = @EXCLUSIONS,
      @USEADDRESSPROCESSING = @USEADDRESSPROCESSING,
      @ADDRESSPROCESSINGOPTIONID = @ADDRESSPROCESSINGOPTIONID,
      @NAMEFORMATPARAMETERID = @NAMEFORMATPARAMETERID,
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE,
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE,
      @ACTIVATIONKPIS = null,
      @USEKPISASDEFAULT = 0,
      @APPEALINFORMATION = null,
      @RUNACTIVATEANDEXPORT = @RUNACTIVATEANDEXPORT,
      @EXPORTDESCRIPTION = @EXPORTDESCRIPTION,
      @MAILEXPORTDEFINITIONID = @MAILEXPORTDEFINITIONID,
      @EMAILEXPORTDEFINITIONID = @EMAILEXPORTDEFINITIONID,
      @PHONEEXPORTDEFINITIONID = @PHONEEXPORTDEFINITIONID,
      @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = 0,
      @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = 0,
      @HOUSEHOLDINGONERECORDPERHOUSEHOLD = 0,
      @CODEVALUEID = @CODEVALUEID,
      @RUNSEGMENTATIONSEGMENTREFRESHPROCESS = @RUNSEGMENTATIONSEGMENTREFRESHPROCESS,
      @CACHESOURCEANALYSISRULEDATA = @CACHESOURCEANALYSISRULEDATA,
      @BUSINESSUNITS = null,
      @OVERRIDEBUSINESSUNITS = 0,
      @BASECURRENCYID = @BASECURRENCYID,
      @ORGANIZATIONCURRENCYEXCHANGERATEID = null,
      @RUNMARKETINGEXCLUSIONSREPORT = @RUNMARKETINGEXCLUSIONSREPORT;

    -- copy the universe/exclusion selection filters


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

    -- copy the source code parts

    insert into dbo.[MKTSOURCECODEPART] (
      [ID],
      [SEGMENTATIONID],
      [SOURCECODEITEMID],
      [LISTID],
      [CHANNELCODE],
      [CODE],
      [PARTDEFINITIONVALUESID],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    )
    select
      newid(),
      @SEGMENTATIONID,
      [SOURCECODEITEMID],
      [LISTID],
      [CHANNELCODE],
      [CODE],
      [PARTDEFINITIONVALUESID],
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    from dbo.[MKTSOURCECODEPART]
    where [SPONSORSHIPMAILINGTEMPLATEID] = @SPONSORSHIPMAILINGTEMPLATEID;

    -- copy the seeds

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

    -- copy any ask ladder overrides

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

    -- copy the segments via cursor

    declare @TARGETSEGMENTID uniqueidentifier;
    declare @SEGMENTID uniqueidentifier;
    declare @PACKAGEID uniqueidentifier;
    declare @PACKAGECODE nvarchar(10);
    declare @PACKAGECODEVALUEID uniqueidentifier;
    declare @CHANNELSOURCECODE nvarchar(10);
    declare @CHANNELSOURCECODEVALUEID uniqueidentifier;
    declare @RESPONSERATE decimal(5, 2);
    declare @GIFTAMOUNT money;
    declare @ASKLADDERID uniqueidentifier;
    declare @SEQUENCE int;
    declare @TESTSEGMENTCODE nvarchar(10);
    declare @TESTSEGMENTCODEVALUEID uniqueidentifier;
    declare @OVERRIDEADDRESSPROCESSING bit;
    declare @SEGMENTUSEADDRESSPROCESSING bit;
    declare @SEGMENTADDRESSPROCESSINGOPTIONID uniqueidentifier;
    declare @SEGMENTNAMEFORMATPARAMETERID uniqueidentifier;
    declare @SEGMENTADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint;
    declare @SEGMENTADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime;
    declare @EXCLUDE bit;

    declare SEGMENTCURSOR cursor local fast_forward for
      select
        [MKTSPONSORSHIPMAILINGTEMPLATERULE].[SEGMENTID],
        isnull([MKTSPONSORSHIPMAILINGTEMPLATERULE].[CODE], '') as [CODE],
        [MKTSPONSORSHIPMAILINGTEMPLATERULE].[PARTDEFINITIONVALUESID] as [CODEVALUEID],
        [MKTSPONSORSHIPMAILINGTEMPLATERULE].[PACKAGEID],
        isnull([MKTPACKAGE].[CODE], '') as [PACKAGECODE],
        [MKTPACKAGE].[PARTDEFINITIONVALUESID] as [PACKAGECODEVALUEID],
        isnull([MKTPACKAGE].[CHANNELSOURCECODE], '') as [CHANNELSOURCECODE],
        [MKTPACKAGE].[CHANNELPARTDEFINITIONVALUESID] as [CHANNELSOURCECODEVALUEID],
        [MKTSPONSORSHIPMAILINGTEMPLATERULE].[RESPONSERATE],
        [MKTSPONSORSHIPMAILINGTEMPLATERULE].[GIFTAMOUNT],
        [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ASKLADDERID],
        [MKTSPONSORSHIPMAILINGTEMPLATERULE].[SEQUENCE],
        isnull([MKTSPONSORSHIPMAILINGTEMPLATERULE].[TESTSEGMENTCODE], '') as [TESTSEGMENTCODE],
        [MKTSPONSORSHIPMAILINGTEMPLATERULE].[TESTPARTDEFINITIONVALUESID] as [TESTSEGMENTCODEVALUEID],
        [MKTSPONSORSHIPMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING],
        [MKTSPONSORSHIPMAILINGTEMPLATERULE].[USEADDRESSPROCESSING],
        [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ADDRESSPROCESSINGOPTIONID],
        [MKTSPONSORSHIPMAILINGTEMPLATERULE].[NAMEFORMATPARAMETERID],
        [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
        [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
        [MKTSPONSORSHIPMAILINGTEMPLATERULE].[EXCLUDE]
      from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE]
      left outer join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[PACKAGEID]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[SEGMENTID]
      where [MKTSPONSORSHIPMAILINGTEMPLATERULE].[SPONSORSHIPMAILINGTEMPLATEID] = @SPONSORSHIPMAILINGTEMPLATEID
      order by [MKTSPONSORSHIPMAILINGTEMPLATERULE].[SEQUENCE];

    open SEGMENTCURSOR;
    fetch next from SEGMENTCURSOR into @SEGMENTID, @CODE, @CODEVALUEID, @PACKAGEID, @PACKAGECODE, @PACKAGECODEVALUEID, @CHANNELSOURCECODE, @CHANNELSOURCECODEVALUEID, @RESPONSERATE, @GIFTAMOUNT, @ASKLADDERID, @SEQUENCE, @TESTSEGMENTCODE, @TESTSEGMENTCODEVALUEID, @OVERRIDEADDRESSPROCESSING, @SEGMENTUSEADDRESSPROCESSING, @SEGMENTADDRESSPROCESSINGOPTIONID, @SEGMENTNAMEFORMATPARAMETERID, @SEGMENTADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE, @SEGMENTADDRESSPROCESSINGOPTIONSEASONALASOFDATE, @EXCLUDE;

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

      -- create the standard segment

      exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENT] 
        @TARGETSEGMENTID output, 
        @CHANGEAGENTID, 
        @SEGMENTATIONID, 
        null, 
        @SEGMENTID, 
        @CODE, 
        @TESTSEGMENTCODE, 
        @PACKAGEID, 
        @PACKAGECODE, 
        @RESPONSERATE, 
        @GIFTAMOUNT, 
        100, 
        0, 
        0, 
        @SEQUENCE, 
        @ASKLADDERID, 
        1, 
        @OVERRIDEADDRESSPROCESSING, 
        @SEGMENTUSEADDRESSPROCESSING, 
        @SEGMENTADDRESSPROCESSINGOPTIONID, 
        @SEGMENTADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE, 
        @SEGMENTADDRESSPROCESSINGOPTIONSEASONALASOFDATE, 
        @SEGMENTNAMEFORMATPARAMETERID,
        @CODEVALUEID,
        @TESTSEGMENTCODEVALUEID,
        @PACKAGECODEVALUEID,
        null,
        @CHANNELSOURCECODE,
        @CHANNELSOURCECODEVALUEID,
        0,
        0,
        null,
        null,
        @EXCLUDE;

      fetch next from SEGMENTCURSOR into @SEGMENTID, @CODE, @CODEVALUEID, @PACKAGEID, @PACKAGECODE, @PACKAGECODEVALUEID, @CHANNELSOURCECODE, @CHANNELSOURCECODEVALUEID, @RESPONSERATE, @GIFTAMOUNT, @ASKLADDERID, @SEQUENCE, @TESTSEGMENTCODE, @TESTSEGMENTCODEVALUEID, @OVERRIDEADDRESSPROCESSING, @SEGMENTUSEADDRESSPROCESSING, @SEGMENTADDRESSPROCESSINGOPTIONID, @SEGMENTNAMEFORMATPARAMETERID, @SEGMENTADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE, @SEGMENTADDRESSPROCESSINGOPTIONSEASONALASOFDATE, @EXCLUDE;
    end;

    close SEGMENTCURSOR;
    deallocate SEGMENTCURSOR;
  end try

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

  return 0;