USP_DATAFORMTEMPLATE_ADD_APPEALMAILINGSETUP

Adds an appeal mailing.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CURRENTAPPUSERID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@NAME	nvarchar(100)	IN
@DESCRIPTION	nvarchar(255)	IN
@MAILDATE	datetime	IN
@MAILINGBUDGET	money	IN
@APPEALID	uniqueidentifier	IN
@SELECTEDSELECTIONS	xml	IN
@EXCLUSIONS	xml	IN
@EXCLUDESELECTIONS	xml	IN
@EXCLUSIONDATETYPECODE	tinyint	IN
@EXCLUSIONASOFDATE	datetime	IN
@CHANNELCODE	tinyint	IN
@CHANNELPREFERENCECODE	tinyint	IN
@MAILPACKAGEID	uniqueidentifier	IN
@EMAILPACKAGEID	uniqueidentifier	IN
@ADDRESSPROCESSINGOPTIONID	uniqueidentifier	IN
@NAMEFORMATPARAMETERID	uniqueidentifier	IN
@HOUSEHOLDINGTYPECODE	tinyint	IN
@HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD	bit	IN
@HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS	bit	IN
@HOUSEHOLDINGONERECORDPERHOUSEHOLD	bit	IN
@CREATEOUTPUTIDSET	bit	IN
@OUTPUTIDSETNAME	nvarchar(100)	IN
@OVERWRITEOUTPUTIDSET	bit	IN
@FINALSAVECODE	tinyint	IN
@OUTPUTPATH	nvarchar(256)	IN
@MKTASKLADDERID	uniqueidentifier	IN
@ESTIMATEDRESPONSERATE	decimal(5, 2)	IN
@ESTIMATEDAVERAGEGIFTAMOUNT	money	IN

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_APPEALMAILINGSETUP
(
  @ID uniqueidentifier = null output,
  @CURRENTAPPUSERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @NAME nvarchar(100),
  @DESCRIPTION nvarchar(255) = '',
  @MAILDATE datetime = null,
  @MAILINGBUDGET money = 0,
  @APPEALID uniqueidentifier,
  @SELECTEDSELECTIONS xml = null,
  @EXCLUSIONS xml = null,
  @EXCLUDESELECTIONS xml = null,
  @EXCLUSIONDATETYPECODE tinyint = 0,
  @EXCLUSIONASOFDATE datetime = null,
  @CHANNELCODE tinyint = 0,
  @CHANNELPREFERENCECODE tinyint = 0,
  @MAILPACKAGEID uniqueidentifier = null,
  @EMAILPACKAGEID uniqueidentifier = null,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null,
  @NAMEFORMATPARAMETERID uniqueidentifier = null,
  @HOUSEHOLDINGTYPECODE tinyint = 0,
  @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD bit = 0,
  @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS bit = 0,
  @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit = 0 ,
  @CREATEOUTPUTIDSET bit = 0,
  @OUTPUTIDSETNAME nvarchar(100) = '',
  @OVERWRITEOUTPUTIDSET bit = 0,
  @FINALSAVECODE tinyint = 0,
  @OUTPUTPATH nvarchar(256) = '',
  @MKTASKLADDERID uniqueidentifier = null,
  @ESTIMATEDRESPONSERATE decimal(5, 2) = 0,
  @ESTIMATEDAVERAGEGIFTAMOUNT money = 0
)
as
begin
  declare @APPEALNAME nvarchar(100);
  declare @APPEALDESCRIPTION nvarchar(255);
  declare @SITEID uniqueidentifier;
  declare @USEADDRESSPROCESSING bit = 1;
  declare @EMAILEXPORTDEFINITIONID uniqueidentifier;
  declare @MAILEXPORTDEFINITIONID uniqueidentifier;
  declare @CURRENTDATE datetime;
  declare @MKTSEGMENTATIONACTIVATEPROCESSID uniqueidentifier;

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

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

    set @CURRENTDATE = getdate();

    -- Add mailing information first b/c foreign key points from AppealMailing to MKTSegmentation        

    exec dbo.[USP_COMMUNICATIONS_CREATEORUPDATEMAILING]
      @ID,
      1,
      1,
      @NAME,
      @DESCRIPTION,
      @MAILINGBUDGET,
      @MAILDATE,
      @SELECTEDSELECTIONS,
      @EXCLUSIONS,            
      @CHANNELCODE,
      @CHANNELPREFERENCECODE,
      @EMAILPACKAGEID,
      @MAILPACKAGEID,
      @MKTASKLADDERID,
      @ADDRESSPROCESSINGOPTIONID,
      @NAMEFORMATPARAMETERID,
      @HOUSEHOLDINGTYPECODE,
      @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD,
      @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS,
      @HOUSEHOLDINGONERECORDPERHOUSEHOLD,
      @APPEALID,
      @CREATEOUTPUTIDSET,
      @OUTPUTIDSETNAME,
      @OVERWRITEOUTPUTIDSET,
      @CURRENTAPPUSERID,
      @CHANGEAGENTID,
      null,-- PACKAGESITEID

      @EXCLUDESELECTIONS,
      @EXCLUSIONDATETYPECODE,
      @EXCLUSIONASOFDATE,
      @ESTIMATEDRESPONSERATE,
      @ESTIMATEDAVERAGEGIFTAMOUNT;

    insert into dbo.[APPEALMAILING] 
    (
      [ID], 
      [APPEALID], 
      [CREATEOUTPUTIDSET], 
      [OUTPUTIDSETNAME], 
      [OVERWRITEOUTPUTIDSET], 
      [ADDEDBYID], 
      [CHANGEDBYID], 
      [DATEADDED], 
      [DATECHANGED]
    )
    values
    (
      @ID, 
      @APPEALID, 
      @CREATEOUTPUTIDSET, 
      @OUTPUTIDSETNAME, 
      @OVERWRITEOUTPUTIDSET, 
      @CHANGEAGENTID, 
      @CHANGEAGENTID, 
      @CURRENTDATE, 
      @CURRENTDATE
    );

    --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

    -- If Enterprise don't install into setup table

    if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('3117d2c8-7f46-42f2-abeb-b654f2f63046') = 0 
    begin
      insert into dbo.[APPEALMAILINGSETUP]
      (
        [ID], 
        [CHANNELCODE], 
        [CHANNELPREFERENCECODE], 
        [MAILPACKAGEID], 
        [EMAILPACKAGEID], 
        [ESTIMATEDRESPONSERATE], 
        [ESTIMATEDAVERAGEGIFTAMOUNT], 
        [ADDEDBYID], 
        [CHANGEDBYID], 
        [DATEADDED], 
        [DATECHANGED], 
        [MKTASKLADDERID]
      )
      values
      (
        @ID, 
        @CHANNELCODE, 
        @CHANNELPREFERENCECODE, 
        @MAILPACKAGEID, 
        @EMAILPACKAGEID, 
        @ESTIMATEDRESPONSERATE,
        @ESTIMATEDAVERAGEGIFTAMOUNT, 
        @CHANGEAGENTID, 
        @CHANGEAGENTID,
        @CURRENTDATE, 
        @CURRENTDATE, 
        @MKTASKLADDERID
      );

      exec dbo.[USP_APPEALMAILINGSETUP_GETSELECTIONS_ADDFROMXML] @ID, @SELECTEDSELECTIONS, @CHANGEAGENTID, @CURRENTDATE;
    end;

    -- Always activate KPIs

    -- Load KPI values

    declare @KpiTable as table
    (
      [KPICATALOGID] uniqueidentifier,
      [SELECTED] bit,
      [NAME] nvarchar(255),
      [GOALTYPECODE] tinyint,
      [DEFAULT] bit,
      [LOCKED] bit,
      [TEMPLATETYPECODE] tinyint
    );

    insert into @KpiTable
      exec dbo.[USP_MKTSEGMENTATIONACTIVATEKPI_GETFIELDS] null, 0;

    declare @ACTIVATIONKPIS xml;
    set @ACTIVATIONKPIS = (
      select 
        [KPICATALOGID],
        [SELECTED],
        [NAME],
        [GOALTYPECODE],
        [DEFAULT]
      from
        @KpiTable 
      for xml raw('ITEM'),type,elements,root('ACTIVATIONKPIS'),BINARY BASE64);

    /* Clear KPI fields */
    exec dbo.[USP_MKTSEGMENTATIONACTIVATEKPI_CLEARFIELDS]
      @ID,
      1,
      @CHANGEAGENTID;

    /* Save KPIs */
    exec dbo.[USP_MKTSEGMENTATIONACTIVATEKPI_SAVEFIELD_FROMXML] 
      @ID, 
      @ACTIVATIONKPIS, 
      1, 
      @CHANGEAGENTID,
      @CURRENTAPPUSERID;

  end try

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

  return 0;
end