USP_MKTMEMBERSHIPMAILINGPROCESS_CREATEMAILING

CREATE procedure dbo.[USP_MKTMEMBERSHIPMAILINGPROCESS_CREATEMAILING]
(
  @MEMBERSHIPMAILINGTEMPLATEID 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 = [MKTMEMBERSHIPMAILINGTEMPLATE].[CODE],
      @CODEVALUEID = [MKTMEMBERSHIPMAILINGTEMPLATE].[PARTDEFINITIONVALUESID],
      @NAME = dbo.[UFN_MKTMEMBERSHIPMAILINGPROCESS_GETUNIQUENAME]([MKTMEMBERSHIPMAILINGTEMPLATE].[NAME], @DATETEXT),
      @DESCRIPTION = [MKTMEMBERSHIPMAILINGTEMPLATE].[DESCRIPTION],
      @SITEID = [MKTMEMBERSHIPMAILINGTEMPLATE].[SITEID],
      @SOURCECODEID = [MKTMEMBERSHIPMAILINGTEMPLATE].[SOURCECODEID],
      @EXCLUSIONDATETYPECODE = [BUSINESSPROCESSCOMMPREF].[DATETYPECODE],
      @EXCLUSIONASOFDATE = [BUSINESSPROCESSCOMMPREF].[ASOFDATE],
      @EXCLUDEDECEASED = [BUSINESSPROCESSCOMMPREF].[EXCLUDEDECEASED],
      @EXCLUDEINACTIVE = [BUSINESSPROCESSCOMMPREF].[EXCLUDEINACTIVE],
      @EXCLUSIONS = dbo.[UFN_BUSINESSPROCESSCOMMPREF_GETEXCLUSIONS_TOITEMLISTXML]([MKTMEMBERSHIPMAILINGPROCESS].[ID]),
      @USEADDRESSPROCESSING = [MKTMEMBERSHIPMAILINGTEMPLATE].[USEADDRESSPROCESSING],
      @ADDRESSPROCESSINGOPTIONID = [MKTMEMBERSHIPMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONID],
      @NAMEFORMATPARAMETERID = [MKTMEMBERSHIPMAILINGTEMPLATE].[NAMEFORMATPARAMETERID],
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = [MKTMEMBERSHIPMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = [MKTMEMBERSHIPMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
      @RUNACTIVATEANDEXPORT = [MKTMEMBERSHIPMAILINGTEMPLATE].[RUNACTIVATEANDEXPORT],
      @RUNSEGMENTATIONSEGMENTREFRESHPROCESS = [MKTMEMBERSHIPMAILINGTEMPLATE].[RUNSEGMENTATIONSEGMENTREFRESHPROCESS],
      @CACHESOURCEANALYSISRULEDATA = [MKTMEMBERSHIPMAILINGTEMPLATE].[CACHESOURCEANALYSISRULEDATA],
      @EXPORTDESCRIPTION = isnull([MKTMEMBERSHIPMAILINGTEMPLATEEXPORT].[DESCRIPTION], ''),
      @MAILEXPORTDEFINITIONID = [MKTMEMBERSHIPMAILINGTEMPLATEEXPORT].[MAILEXPORTDEFINITIONID],
      @EMAILEXPORTDEFINITIONID = [MKTMEMBERSHIPMAILINGTEMPLATEEXPORT].[EMAILEXPORTDEFINITIONID],
      @PHONEEXPORTDEFINITIONID = [MKTMEMBERSHIPMAILINGTEMPLATEEXPORT].[PHONEEXPORTDEFINITIONID],
      @BASECURRENCYID = [MKTMEMBERSHIPMAILINGTEMPLATE].[BASECURRENCYID],
      @RUNMARKETINGEXCLUSIONSREPORT = [MKTMEMBERSHIPMAILINGTEMPLATE].[RUNMARKETINGEXCLUSIONSREPORT]
    from dbo.[MKTMEMBERSHIPMAILINGTEMPLATE]
    inner join dbo.[MKTMEMBERSHIPMAILINGPROCESS] on [MKTMEMBERSHIPMAILINGPROCESS].[MEMBERSHIPMAILINGTEMPLATEID] = [MKTMEMBERSHIPMAILINGTEMPLATE].[ID]
    left outer join dbo.[BUSINESSPROCESSCOMMPREF] on [BUSINESSPROCESSCOMMPREF].[BUSINESSPROCESSPARAMETERSETID] = [MKTMEMBERSHIPMAILINGPROCESS].[ID]
    left outer join dbo.[MKTMEMBERSHIPMAILINGTEMPLATEEXPORT] on [MKTMEMBERSHIPMAILINGTEMPLATEEXPORT].[MEMBERSHIPMAILINGTEMPLATEID] = [MKTMEMBERSHIPMAILINGTEMPLATE].[ID]
    where [MKTMEMBERSHIPMAILINGTEMPLATE].[ID] = @MEMBERSHIPMAILINGTEMPLATEID;

    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 = 2,
      @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.[MKTMEMBERSHIPMAILINGTEMPLATEFILTERSELECTION]
    where [MEMBERSHIPMAILINGTEMPLATEID] = @MEMBERSHIPMAILINGTEMPLATEID;


    -- 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 [MEMBERSHIPMAILINGTEMPLATEID] = @MEMBERSHIPMAILINGTEMPLATEID;


    -- copy the seeds


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


    -- 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.[MKTMEMBERSHIPMAILINGTEMPLATEASKLADDEROVERRIDE]
    where [MEMBERSHIPMAILINGTEMPLATEID] = @MEMBERSHIPMAILINGTEMPLATEID;


    -- 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
        [MKTMEMBERSHIPMAILINGTEMPLATERULE].[SEGMENTID],
        isnull([MKTMEMBERSHIPMAILINGTEMPLATERULE].[CODE], '') as [CODE],
        [MKTMEMBERSHIPMAILINGTEMPLATERULE].[PARTDEFINITIONVALUESID] as [CODEVALUEID],
        [MKTMEMBERSHIPMAILINGTEMPLATERULE].[PACKAGEID],
        isnull([MKTPACKAGE].[CODE], '') as [PACKAGECODE],
        [MKTPACKAGE].[PARTDEFINITIONVALUESID] as [PACKAGECODEVALUEID],
        isnull([MKTPACKAGE].[CHANNELSOURCECODE], '') as [CHANNELSOURCECODE],
        [MKTPACKAGE].[CHANNELPARTDEFINITIONVALUESID] as [CHANNELSOURCECODEVALUEID],
        [MKTMEMBERSHIPMAILINGTEMPLATERULE].[RESPONSERATE],
        [MKTMEMBERSHIPMAILINGTEMPLATERULE].[GIFTAMOUNT],
        [MKTMEMBERSHIPMAILINGTEMPLATERULE].[ASKLADDERID],
        [MKTMEMBERSHIPMAILINGTEMPLATERULE].[SEQUENCE],
        isnull([MKTMEMBERSHIPMAILINGTEMPLATERULE].[TESTSEGMENTCODE], '') as [TESTSEGMENTCODE],
        [MKTMEMBERSHIPMAILINGTEMPLATERULE].[TESTPARTDEFINITIONVALUESID] as [TESTSEGMENTCODEVALUEID],
        [MKTMEMBERSHIPMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING],
        [MKTMEMBERSHIPMAILINGTEMPLATERULE].[USEADDRESSPROCESSING],
        [MKTMEMBERSHIPMAILINGTEMPLATERULE].[ADDRESSPROCESSINGOPTIONID],
        [MKTMEMBERSHIPMAILINGTEMPLATERULE].[NAMEFORMATPARAMETERID],
        [MKTMEMBERSHIPMAILINGTEMPLATERULE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
        [MKTMEMBERSHIPMAILINGTEMPLATERULE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
        [MKTMEMBERSHIPMAILINGTEMPLATERULE].[EXCLUDE]
      from dbo.[MKTMEMBERSHIPMAILINGTEMPLATERULE]
      left outer join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTMEMBERSHIPMAILINGTEMPLATERULE].[PACKAGEID]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTMEMBERSHIPMAILINGTEMPLATERULE].[SEGMENTID]
      where [MKTMEMBERSHIPMAILINGTEMPLATERULE].[MEMBERSHIPMAILINGTEMPLATEID] = @MEMBERSHIPMAILINGTEMPLATEID
      order by [MKTMEMBERSHIPMAILINGTEMPLATERULE].[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;