USP_DATAFORMTEMPLATE_ADD_APPEALMAILING

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_APPEALMAILING]
(
  @ID uniqueidentifier = null output,
  @APPEALID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @NAME nvarchar(100),
  @DESCRIPTION nvarchar(255) = '',
  @MAILDATE datetime = null,
  @FIXEDCOST money = 0,
  @MKTPACKAGEID uniqueidentifier,
  @IDSETREGISTERID uniqueidentifier,
  @HOUSEHOLDINGTYPECODE tinyint = 0,
  @EXCLUSIONDATETYPECODE tinyint = 0,
  @EXCLUSIONASOFDATE datetime = null,
  @EXCLUDEDECEASED bit = 1,
  @EXCLUDEINACTIVE bit = 1,
  @EXCLUSIONS xml = null,
  @LABELFILENAME nvarchar(255) = '',
  @LABELFILE varbinary(max) = null,
  @CREATEOUTPUTIDSET bit = 0,
  @OUTPUTIDSETNAME nvarchar(100) = '',
  @OVERWRITEOUTPUTIDSET bit = 0,
  @USEADDRESSPROCESSING bit = 0,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null,
  @NAMEFORMATPARAMETERID uniqueidentifier = null,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint = 0,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime = null,
  @USEKPISASDEFAULT bit = null,
  @ACTIVATIONKPIS xml = null,
  @MAILINGBUDGET money = 0,
  @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD bit = 0,
  @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS bit = 0,
  @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit = 0,
  @BASECURRENCYID uniqueidentifier = null
)
as
  set nocount on;

  declare @APPEALNAME nvarchar(100);
  declare @APPEALDESCRIPTION nvarchar(255);
  declare @SITEID uniqueidentifier;
  declare @EXPORTDEFINITIONID uniqueidentifier;
  declare @CURRENTDATE datetime;
  declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
  declare @ORGANIZATIONFIXEDCOST money;
  declare @ORGANIZATIONMAILINGBUDGET money;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;

  begin try
    if @ID is null
      set @ID = newid();

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

    set @CURRENTDATE = getdate();

    select
      @APPEALNAME = [NAME],
      @APPEALDESCRIPTION = [DESCRIPTION],
      @SITEID = [SITEID]
    from dbo.[APPEAL]
    where [ID] = @APPEALID;

    select
      @EXPORTDEFINITIONID = [LETTERCODE].[EXPORTDEFINITIONID]
    from dbo.[MKTPACKAGE]
    inner join dbo.[LETTERCODE] on [LETTERCODE].[ID] = [MKTPACKAGE].[LETTERCODEID]
    where [MKTPACKAGE].[ID] = @MKTPACKAGEID;

    if @BASECURRENCYID is null
      select @BASECURRENCYID = [BASECURRENCYID] from dbo.[APPEAL] where [ID] = @APPEALID;

    --Insert the base mailing...

    insert into dbo.[MKTSEGMENTATION] (
      [ID],
      [NAME],
      [DESCRIPTION],
      [MAILDATE],
      [SITEID],
      [HOUSEHOLDINGTYPECODE],
      [HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD],
      [HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS],
      [HOUSEHOLDINGONERECORDPERHOUSEHOLD],
      [USEADDRESSPROCESSING],
      [ADDRESSPROCESSINGOPTIONID],
      [NAMEFORMATPARAMETERID],
      [ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
      [ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
      [COMMUNICATIONTYPECODE],
      [RUNACTIVATEANDEXPORT],
      [CREATEOUTPUTIDSET],
      [OUTPUTIDSETNAME],
      [OVERWRITEOUTPUTIDSET],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED],
      [BASECURRENCYID]
    ) values (
      @ID,
      @NAME,
      @DESCRIPTION,
      @MAILDATE,
      @SITEID,
      @HOUSEHOLDINGTYPECODE,
      @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD,
      @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS,
      @HOUSEHOLDINGONERECORDPERHOUSEHOLD,
      @USEADDRESSPROCESSING,
      @ADDRESSPROCESSINGOPTIONID,
      @NAMEFORMATPARAMETERID,
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE,
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE,
      1,  --Appeal mailing

      1,  --Run export

      @CREATEOUTPUTIDSET,
      @OUTPUTIDSETNAME,
      @OVERWRITEOUTPUTIDSET,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE,
      @BASECURRENCYID
    );

    set @ORGANIZATIONCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

    if (@ORGANIZATIONCURRENCYID = @BASECURRENCYID)
      begin
        set @ORGANIZATIONFIXEDCOST = @FIXEDCOST;
        set @ORGANIZATIONMAILINGBUDGET = @MAILINGBUDGET;
      end
    else
      begin
        set @ORGANIZATIONEXCHANGERATEID = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null);
        set @ORGANIZATIONFIXEDCOST = dbo.[UFN_CURRENCY_CONVERT](@FIXEDCOST, @ORGANIZATIONEXCHANGERATEID);
        set @ORGANIZATIONMAILINGBUDGET = dbo.[UFN_CURRENCY_CONVERT](@MAILINGBUDGET, @ORGANIZATIONEXCHANGERATEID);
      end

    --Insert the budget information...

    insert into dbo.[MKTSEGMENTATIONBUDGET] (
      [ID],
      [BUDGETAMOUNT],
      [FIXEDCOST],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED],
      [BASECURRENCYID],
      [ORGANIZATIONBUDGETAMOUNT],
      [ORGANIZATIONFIXEDCOST],
      [ORGANIZATIONCURRENCYEXCHANGERATEID]
    ) values (
      @ID,
      @MAILINGBUDGET,
      @FIXEDCOST,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE,
      @BASECURRENCYID,
      @ORGANIZATIONMAILINGBUDGET,
      @ORGANIZATIONFIXEDCOST,
      @ORGANIZATIONEXCHANGERATEID
    );

    -- add the mailing to the SegmentCalculationProcess table

    exec dbo.[USP_MKTSEGMENTATIONSEGMENTCALCULATEPROCESS_SAVE] @ID, @CHANGEAGENTID;

    -- add the mailing to the ActivationProcess table

    exec dbo.[USP_MKTSEGMENTATIONACTIVATEPROCESS_SAVE] @ID, @CHANGEAGENTID;

    -- add the mailing activation criteria

    exec dbo.[USP_MKTSEGMENTATIONACTIVATE_SAVEFIELD] @ID, @CHANGEAGENTID, 'DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0', null, null, @APPEALID, @APPEALNAME, @APPEALDESCRIPTION;

    -- add the effort to the exclusions process table (if an appeal mailing uses advanced name format processing options, this is necessary for those to 

    -- work correctly during export)

    exec dbo.[USP_MKTSEGMENTATION_EFFORTEXCLUSIONSPROCESS_SAVE] @ID, @CHANGEAGENTID;

    -- add the mailing export process

    insert into dbo.[MKTSEGMENTATIONEXPORTPROCESS] (
      [ID],
      [SEGMENTATIONID],
      [MAILEXPORTDEFINITIONID],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    ) values (
      newid(),
      @ID,
      @EXPORTDEFINITIONID,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );

    if @USEADDRESSPROCESSING = 1 and @ADDRESSPROCESSINGOPTIONID is not null
      exec dbo.[USP_MKTSEGMENTATIONSEGMENTCACHEADDRESSES_CREATETABLE] @ID;

    --Create the underlying segment information and grab the new mailing segment ID...

    exec dbo.[USP_APPEALMAILING_CREATEORUPDATESEGMENT]
      @APPEALMAILINGID = @ID,
      @CURRENTAPPUSERID = @CURRENTAPPUSERID,
      @NAME = @NAME,
      @IDSETREGISTERID = @IDSETREGISTERID,
      @MKTPACKAGEID = @MKTPACKAGEID,
      @HOUSEHOLDINGTYPECODE = @HOUSEHOLDINGTYPECODE,
      @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD,
      @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS,
      @HOUSEHOLDINGONERECORDPERHOUSEHOLD = @HOUSEHOLDINGONERECORDPERHOUSEHOLD,
      @EXCLUSIONDATETYPECODE = @EXCLUSIONDATETYPECODE,
      @EXCLUSIONASOFDATE = @EXCLUSIONASOFDATE,
      @EXCLUDEDECEASED = @EXCLUDEDECEASED,
      @EXCLUDEINACTIVE = @EXCLUDEINACTIVE,
      @EXCLUSIONS = @EXCLUSIONS,
      @USEADDRESSPROCESSING = @USEADDRESSPROCESSING,
      @ADDRESSPROCESSINGOPTIONID = @ADDRESSPROCESSINGOPTIONID,
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE,
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE,
      @CHANGEAGENTID = @CHANGEAGENTID,
      @APPEALID = @APPEALID,
      @BASECURRENCYID = @BASECURRENCYID;

    --Because the appeal mailing name uniqueness depends on the appeal, we need to check for name uniqueness after the appeal mailing is created.

    if dbo.[UFN_MKTSEGMENTATION_NAMEEXISTS](@NAME, 0, 1) = 1
      begin
        --Name is not unique, throw error.

        raiserror('BBERR_MKTSEGMENTATION_VALIDNAME', 13, 1);
      end

    --The mailing was created in the SP above, because of ordering issues, so update the rest of the fields here...

    update dbo.[APPEALMAILING] set
      [LABELFILENAME] = @LABELFILENAME,
      [LABELFILE] = @LABELFILE,
      [CREATEOUTPUTIDSET] = @CREATEOUTPUTIDSET,
      [OUTPUTIDSETNAME] = @OUTPUTIDSETNAME,
      [OVERWRITEOUTPUTIDSET] = @OVERWRITEOUTPUTIDSET,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @ID;

    --Save KPIs...

    exec dbo.[USP_MKTSEGMENTATIONACTIVATEKPI_SAVEFIELD_FROMXML] 
      @ID, 
      @ACTIVATIONKPIS, 
      @USEKPISASDEFAULT, 
      @CHANGEAGENTID,
      @CURRENTAPPUSERID;
  end try

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

  return 0;