USP_COMMUNICATIONS_CREATEORUPDATEMAILING

CREATE procedure dbo.USP_COMMUNICATIONS_CREATEORUPDATEMAILING
(
  @SEGMENTATIONID uniqueidentifier,
  @COMMUNICATIONTYPECODE tinyint = 0,
  @MAILTYPECODE tinyint = 0,
  @MAILINGNAME nvarchar(100) = '',
  @MAILINGDESCRIPTION nvarchar(255) = '',
  @MAILINGBUDGET money = 0,
  @MAILDATE datetime = null,
  @SELECTIONS xml = null,
  @EXCLUSIONS xml = null,
  @CHANNELCODE tinyint = 0,
  @CHANNELPREFERENCECODE tinyint = 0,
  @EMAILPACKAGEID uniqueidentifier = null,
  @MAILPACKAGEID uniqueidentifier = null,
  @ASKLADDERID uniqueidentifier = null,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null,
  @NAMEFORMATPARAMETERID uniqueidentifier = null,
  @HOUSEHOLDINGTYPECODE tinyint = 0,
  @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD bit = 0,
  @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS bit = 0,
  @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit = 0,
  @APPEALID uniqueidentifier = null,
  @CREATEOUTPUTIDSET bit = 0,
  @OUTPUTIDSETNAME nvarchar(100) = '',
  @OVERWRITEOUTPUTIDSET bit = 0,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @CHANGEAGENTID uniqueidentifier = null,
  @PACKAGESITEID uniqueidentifier = null, -- if set, overrides the appeal's site

  @EXCLUDESELECTIONS xml = null,
  @EXCLUSIONDATETYPECODE tinyint = 0,
  @EXCLUSIONASOFDATE datetime = null,
  @ESTIMATEDRESPONSERATE decimal(5, 2) = 0,
  @ESTIMATEDAVERAGEGIFTAMOUNT money = 0
)
as
begin

  -- @COMMINICATIONTYPECODE - enum from MKTSegmentation table

  --    0 - Direct marketing effort

  --    1 - Appeal mailing

  --    2 - Event invitation


  -- @MAILTYPECODE pertains to the MailTypeCode field in dbo.MAILPREFERENCE for constituent communication preferences

  --    0 - Revenue Acknowledgements

  --    1 - Appeals

  --    2 - Events

  --    3 - General Correspondence

  --    4 - Reminders

  --    5 - Receipts

  --    6 - Planned Gift Acknowledgements

  --    7 - Tribute Acknowledgements

  --    8 - Stewardship

  --    etc...  This code should not need to change as new types are introduced...


  -- @CHANNELCODE       0 - Both mail and email, 1 - Email only, 2 - Mail only

  -- @CHANNELPREFERENCECODE 0 - Email, 1 - Mail


  set nocount on;

  declare @CURRENTDATE datetime = getDate();

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

  begin try
    declare @ACTIVE bit;
    declare @EMAILSEGMENTID uniqueidentifier;
    declare @MAILSEGMENTID uniqueidentifier;
    declare @SEGMENTATIONACTIVATEPROCESSID uniqueidentifier;
    declare @SEGMENTATIONEXPORTPROCESSID uniqueidentifier;
    declare @EMAILEXPORTDEFINITIONID uniqueidentifier;
    declare @MAILEXPORTDEFINITIONID uniqueidentifier;
    declare @SEGMENTNAME nvarchar(100);
    declare @OLDMAILDATE datetime;
    declare @OLDNAME nvarchar(100);
    declare @OLDEXCLUSIONS as xml;
    declare @OLDEXCLUSIONDATETYPECODE as tinyint;
    declare @OLDEXCLUSIONASOFDATE as datetime;
    declare @OLDUSEADDRESSPROCESSING bit;
    declare @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint;
    declare @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime;
    declare @OLDADDRESSPROCESSINGOPTIONID uniqueidentifier;
    declare @OLDNAMEFORMATPARAMETERID uniqueidentifier;
    declare @OLDHOUSEHOLDINGTYPECODE tinyint;
    declare @OLDEXCLUDEDECEASED as bit;
    declare @OLDEXCLUDEINACTIVE as bit;
    declare @OLDMAILPACKAGEID uniqueidentifier;
    declare @OLDEMAILPACKAGEID uniqueidentifier;
    declare @ADDRESSPROCESSINGOPTIONSCHANGED bit;
    declare @EXCLUSIONSCHANGED bit;
    declare @APPEALNAME nvarchar(100) = null;
    declare @APPEALDESCRIPTION nvarchar(255) = null;
    declare @SITEID uniqueidentifier;

    select 
      @ACTIVE = [MKTSEGMENTATION].ACTIVE,
      @SEGMENTATIONACTIVATEPROCESSID = [MKTSEGMENTATIONACTIVATEPROCESS].[ID],
      @SEGMENTATIONEXPORTPROCESSID = [MKTSEGMENTATIONEXPORTPROCESS].[ID],
      @OLDMAILDATE = [MKTSEGMENTATION].[MAILDATE],
      @OLDNAME = [MKTSEGMENTATION].[NAME],
      @OLDHOUSEHOLDINGTYPECODE = [MKTSEGMENTATION].[HOUSEHOLDINGTYPECODE],
      @OLDUSEADDRESSPROCESSING = [MKTSEGMENTATION].[USEADDRESSPROCESSING],
      @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
      @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATE = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
      @OLDADDRESSPROCESSINGOPTIONID = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONID],
      @OLDNAMEFORMATPARAMETERID = [MKTSEGMENTATION].[NAMEFORMATPARAMETERID],
      @OLDEXCLUSIONDATETYPECODE = [BUSINESSPROCESSCOMMPREF].[DATETYPECODE],
      @OLDEXCLUSIONASOFDATE = [BUSINESSPROCESSCOMMPREF].[ASOFDATE],
      @OLDEXCLUSIONS = dbo.[UFN_BUSINESSPROCESSCOMMPREF_GETEXCLUSIONS_TOITEMLISTXML]([MKTSEGMENTATIONACTIVATEPROCESS].[ID]),
      @OLDEXCLUDEDECEASED = [BUSINESSPROCESSCOMMPREF].[EXCLUDEDECEASED],
      @OLDEXCLUDEINACTIVE = [BUSINESSPROCESSCOMMPREF].[EXCLUDEINACTIVE]
    from dbo.[MKTSEGMENTATION]
    inner join dbo.[MKTSEGMENTATIONACTIVATEPROCESS] 
      on [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    inner join dbo.[MKTSEGMENTATIONEXPORTPROCESS] 
      on [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    left join dbo.[BUSINESSPROCESSCOMMPREF] 
      on [BUSINESSPROCESSCOMMPREF].[BUSINESSPROCESSPARAMETERSETID] = [MKTSEGMENTATIONACTIVATEPROCESS].[ID]
    where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;

    -- Retrieve the Email and Mail Segment IDs

    select
      @EMAILSEGMENTID = MKTSEGMENTATIONSEGMENT.SEGMENTID
    from dbo.MKTSEGMENTATIONSEGMENT
    inner join dbo.MKTPACKAGE
      on MKTSEGMENTATIONSEGMENT.PACKAGEID = MKTPACKAGE.ID
        and MKTPACKAGE.CHANNELCODE = 1              -- CHANNELCODE 1 - Email

    where MKTSEGMENTATIONSEGMENT.SEGMENTATIONID = @SEGMENTATIONID;

    select
      @MAILSEGMENTID = MKTSEGMENTATIONSEGMENT.SEGMENTID
    from dbo.MKTSEGMENTATIONSEGMENT
    inner join dbo.MKTPACKAGE
      on MKTSEGMENTATIONSEGMENT.PACKAGEID = MKTPACKAGE.ID
        and MKTPACKAGE.CHANNELCODE = 0              -- CHANNELCODE 0 - Mail

    where MKTSEGMENTATIONSEGMENT.SEGMENTATIONID = @SEGMENTATIONID;

    if @PACKAGESITEID is null -- Retrieve the appeal information...

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

    -- Rollback active mailings to be re-activated

    if @ACTIVE = 1 or dbo.UFN_MKTSEGMENTATION_HASACTIVESEGMENTS(@SEGMENTATIONID) = 1
      begin
        declare @DELETEAPPEALINFO bit = case when @APPEALID is null then 0 else 1 end;

        exec dbo.USP_MKTSEGMENTATIONACTIVATE_ROLLBACK @SEGMENTATIONID, @CHANGEAGENTID, @DELETEAPPEALINFO, 1;
        --exec dbo.[USP_MKTSEGMENTATIONSEGMENTCACHEADDRESSES_CREATETABLE] @SEGMENTATIONID;

      end

    declare @SEGMENTATIONSEGMENTID uniqueidentifier;

    -- Delete segment and segmentation segments for Channels no longer included in the mailing

    if @CHANNELCODE = 1 and @MAILSEGMENTID is not null
      begin

        select
          @SEGMENTATIONSEGMENTID = ID
        from dbo.MKTSEGMENTATIONSEGMENT
        where SEGMENTID = @MAILSEGMENTID;

        if @SEGMENTATIONSEGMENTID is not null
          exec dbo.USP_MKTSEGMENTATIONSEGMENT_DELETE @SEGMENTATIONSEGMENTID, @CHANGEAGENTID;

        update dbo.[MKTSEGMENT] set
          [ISSYSTEM] = 0,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where [ID] = @MAILSEGMENTID;

        exec dbo.USP_MKTSEGMENT_DELETE @MAILSEGMENTID, @CHANGEAGENTID, @CURRENTAPPUSERID;

        set @MAILSEGMENTID = null;
      end

    if @CHANNELCODE = 2 and @EMAILSEGMENTID is not null
      begin

        set @SEGMENTATIONSEGMENTID = null;

        select
          @SEGMENTATIONSEGMENTID = ID
        from dbo.MKTSEGMENTATIONSEGMENT
        where SEGMENTID = @EMAILSEGMENTID;

        if @SEGMENTATIONSEGMENTID is not null
          exec dbo.USP_MKTSEGMENTATIONSEGMENT_DELETE @SEGMENTATIONSEGMENTID, @CHANGEAGENTID;

        update dbo.[MKTSEGMENT] set
          [ISSYSTEM] = 0,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where [ID] = @EMAILSEGMENTID;

        exec dbo.USP_MKTSEGMENT_DELETE @EMAILSEGMENTID, @CHANGEAGENTID, @CURRENTAPPUSERID;

        set @EMAILSEGMENTID = null;
      end

    -- Create or update the base mailing

        if @COMMUNICATIONTYPECODE = 2
          set @MAILINGNAME = dbo.UFN_MKTSEGMENTATION_GETUNIQUENAME(@SEGMENTATIONID, @MAILINGNAME, default);

    if not exists (select top 1 1 from dbo.MKTSEGMENTATION where ID = @SEGMENTATIONID)
      -- Insert the base mailing...

      insert into dbo.[MKTSEGMENTATION]
      (
        [ID],
        [MAILINGTYPECODE],
        [COMMUNICATIONTYPECODE],
        [NAME],
        [DESCRIPTION],
        [MAILDATE],
        [ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
        [ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
        [SITEID],
        [HOUSEHOLDINGTYPECODE],
        [HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD],
        [HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS],
        [HOUSEHOLDINGONERECORDPERHOUSEHOLD],
        [USEADDRESSPROCESSING],
        [ADDRESSPROCESSINGOPTIONID],
        [NAMEFORMATPARAMETERID],
        [RUNACTIVATEANDEXPORT],
        [CREATEOUTPUTIDSET],
        [OUTPUTIDSETNAME],
        [OVERWRITEOUTPUTIDSET],
        [ADDEDBYID],
        [CHANGEDBYID],
        [DATEADDED],
        [DATECHANGED]
      )
      values
      (
        @SEGMENTATIONID,
        0,
        @COMMUNICATIONTYPECODE,
        @MAILINGNAME,
        @MAILINGDESCRIPTION,
        @MAILDATE,
        @MAILDATE,
        1, -- as of date

        @SITEID,
        @HOUSEHOLDINGTYPECODE,
        @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD,
        @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS,
        @HOUSEHOLDINGONERECORDPERHOUSEHOLD,
        1,
        @ADDRESSPROCESSINGOPTIONID,
        @NAMEFORMATPARAMETERID,
        1,                      -- RunActivateAndExport

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

    else
      update dbo.[MKTSEGMENTATION] set
        [NAME] = @MAILINGNAME,
        [DESCRIPTION] = @MAILINGDESCRIPTION,
        [MAILDATE] = @MAILDATE,
        [ADDRESSPROCESSINGOPTIONSEASONALASOFDATE] = @MAILDATE,
        [ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE] = 1, -- as of date

        [SITEID] = @SITEID,
        [HOUSEHOLDINGTYPECODE] = @HOUSEHOLDINGTYPECODE,
        [HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD] = @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD,
        [HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS] = @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS,
        [HOUSEHOLDINGONERECORDPERHOUSEHOLD] = @HOUSEHOLDINGONERECORDPERHOUSEHOLD,
        [USEADDRESSPROCESSING] = 1,
        [ADDRESSPROCESSINGOPTIONID] = @ADDRESSPROCESSINGOPTIONID,
        [NAMEFORMATPARAMETERID] = @NAMEFORMATPARAMETERID,
        [CREATEOUTPUTIDSET] = @CREATEOUTPUTIDSET,
        [OUTPUTIDSETNAME] = @OUTPUTIDSETNAME,
        [OVERWRITEOUTPUTIDSET] = @OVERWRITEOUTPUTIDSET,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where 
        [ID] = @SEGMENTATIONID;

    -- Save the excluded selections

    exec dbo.USP_MKTSEGMENTATIONFILTERSELECTION_GETEXCLUDESELECTIONS_UPDATEFROMXML @SEGMENTATIONID, @EXCLUDESELECTIONS, @CHANGEAGENTID, @CURRENTDATE;

    -- Create or update budget information...

    if not exists(select 1 from dbo.[MKTSEGMENTATIONBUDGET] where [ID] = @SEGMENTATIONID)
      insert into dbo.[MKTSEGMENTATIONBUDGET] 
        ([ID],[BUDGETAMOUNT], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED])
      values 
        (@SEGMENTATIONID, @MAILINGBUDGET, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
    else
      update dbo.[MKTSEGMENTATIONBUDGET] set
        [BUDGETAMOUNT] = @MAILINGBUDGET,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where ID = @SEGMENTATIONID;

    -- Add this mailing to the PreActivationProcess table

    if not exists(select 1 from dbo.[MKTMAILINGPREACTIVATIONPROCESS] where [SEGMENTATIONID] = @SEGMENTATIONID)
      insert into dbo.[MKTMAILINGPREACTIVATIONPROCESS]
      (
        [ID],
        [SEGMENTATIONID],
        [ADDEDBYID],
        [CHANGEDBYID],
        [DATEADDED],
        [DATECHANGED]
      ) 
      values
      (
        newid(),
        @SEGMENTATIONID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
      );

    -- Add the mailing to the SegmentCalculationProcess table

    if not exists(select 1 from dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS] where [SEGMENTATIONID] = @SEGMENTATIONID)
      exec dbo.[USP_MKTSEGMENTATIONSEGMENTCALCULATEPROCESS_SAVE] @SEGMENTATIONID, @CHANGEAGENTID;

    -- Add the mailing to the SegmentRefreshProcess table 

    --    Appeal Mailings only

    if @COMMUNICATIONTYPECODE = 1
      if not exists(select 1 from dbo.[MKTSEGMENTATIONSEGMENTREFRESHPROCESS] where [SEGMENTATIONID] = @SEGMENTATIONID)
        exec dbo.[USP_MKTSEGMENTATIONSEGMENTREFRESHPROCESS_SAVE] @SEGMENTATIONID, @CHANGEAGENTID;

    -- Add the mailing to the ActivationProcess table...

    if not exists(select 1 from dbo.[MKTSEGMENTATIONACTIVATEPROCESS] where [SEGMENTATIONID] = @SEGMENTATIONID)
      exec dbo.[USP_MKTSEGMENTATIONACTIVATEPROCESS_SAVE] @SEGMENTATIONID, @CHANGEAGENTID;

    -- Add the effort to the EffortExclusionsProcess table 

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

    -- Retrieve the activation process ID

    if @SEGMENTATIONACTIVATEPROCESSID is null
      select
        @SEGMENTATIONACTIVATEPROCESSID = ID
      from dbo.[MKTSEGMENTATIONACTIVATEPROCESS]
      where SEGMENTATIONID = @SEGMENTATIONID;


    if not exists(select 1 from dbo.[BUSINESSPROCESSCOMMPREF] where [BUSINESSPROCESSPARAMETERSETID] = @SEGMENTATIONACTIVATEPROCESSID)
      exec dbo.[USP_BUSINESSPROCESSCOMMPREF_ADD]
        null,
        @CHANGEAGENTID,
        '22C3D75C-A956-4BFC-A5FD-4B866BAEF509',
        @SEGMENTATIONACTIVATEPROCESSID,
        @EXCLUSIONDATETYPECODE,
        @EXCLUSIONASOFDATE,
        0, -- Setting Exclude Deceased to False so it will take the invitees that are in the current list

        0, -- Setting Exclude Inactive to False so it will take the invitees that are in the current list

        @EXCLUSIONS,
        @CURRENTAPPUSERID;

    else
      begin
        -- Making sure Exclude Deceased (first zero) is false, Making sure Exclude Inactive is false for update, so it will take in the invitees in the list.

        set @EXCLUSIONSCHANGED = dbo.[UFN_MKTSEGMENTATION_COMPARECOMMPREFS](@EXCLUSIONDATETYPECODE, @MAILDATE, 0, 0, @EXCLUSIONS, @OLDEXCLUSIONDATETYPECODE, @OLDMAILDATE, @OLDEXCLUDEDECEASED, @OLDEXCLUDEINACTIVE, @OLDEXCLUSIONS);

        if @EXCLUSIONSCHANGED = 1
          exec dbo.[USP_BUSINESSPROCESSCOMMPREF_UPDATE]
            @CHANGEAGENTID = @CHANGEAGENTID,
            @BUSINESSPROCESSCATALOGID = '116332AF-BB79-4608-9709-4203BD2BA318',
            @BUSINESSPROCESSPARAMETERSETID = @SEGMENTATIONACTIVATEPROCESSID,
            @EXCLUSIONDATETYPECODE = @EXCLUSIONDATETYPECODE,
            @EXCLUSIONASOFDATE = @EXCLUSIONASOFDATE,
            @EXCLUDEDECEASED = 0,
            @EXCLUDEINACTIVE = 0,
            @EXCLUSIONS = @EXCLUSIONS,
            @CURRENTAPPUSERID = @CURRENTAPPUSERID;

      end

    -- Create or update the mailing export process...

    select
      @MAILEXPORTDEFINITIONID = coalesce([LETTERCODE].[EXPORTDEFINITIONID], [MKTPACKAGE].[EXPORTDEFINITIONID])
    from dbo.[MKTPACKAGE]
    left join dbo.[LETTERCODE] on [LETTERCODE].[ID] = [MKTPACKAGE].[LETTERCODEID]
    where [MKTPACKAGE].[ID] = @MAILPACKAGEID;

    select
      @EMAILEXPORTDEFINITIONID = [MKTPACKAGE].[EXPORTDEFINITIONID]
    from dbo.[MKTPACKAGE]
    where [MKTPACKAGE].[ID] = @EMAILPACKAGEID;

    if @SEGMENTATIONEXPORTPROCESSID is null
      begin
        set @SEGMENTATIONEXPORTPROCESSID = newid();

        insert into dbo.[MKTSEGMENTATIONEXPORTPROCESS] (
          [ID],
          [SEGMENTATIONID],
          [DESCRIPTION],
          [MAILEXPORTDEFINITIONID],
          [EMAILEXPORTDEFINITIONID],
          [PHONEEXPORTDEFINITIONID],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        ) values (
          @SEGMENTATIONEXPORTPROCESSID,
          @SEGMENTATIONID,
          '',
          @MAILEXPORTDEFINITIONID,
          @EMAILEXPORTDEFINITIONID,
          null,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        )
      end
    else
      exec dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONEXPORTPROCESS_4]
        @SEGMENTATIONEXPORTPROCESSID,
        @CHANGEAGENTID,
        @SEGMENTATIONID, 
        '',
        @MAILEXPORTDEFINITIONID,
        @EMAILEXPORTDEFINITIONID,
        null;

    exec dbo.[USP_MKTSEGMENTATIONSEGMENTCACHEADDRESSES_CREATETABLE] @SEGMENTATIONID;

    -- Save the activation source's information

    --  If the appeal fields do not apply to a particular mailing, 

    --    simply send in null for the Appeal field arguments

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

    -- Create or update the segment(s)

    declare @EXCLUDECONSTITSBASEDONPREFERENCE bit;
    declare @INCLUDECONSTITSWITHOUTPREFERENCE bit;

    set @EXCLUDECONSTITSBASEDONPREFERENCE =
        case @CHANNELCODE 
          when 0 then 1 
          else 0 
        end;

    if @CHANNELCODE <> 1 and @MAILPACKAGEID is not null
      begin
        set @INCLUDECONSTITSWITHOUTPREFERENCE = 
            case @CHANNELCODE 
              when 0 then 
                case @CHANNELPREFERENCECODE 
                  when 1 then 1 
                  else 0 
                end 
              when 2 then 1 
              else 0 
            end;

        -- Create or update the segment

        exec dbo.[USP_COMMUNICATIONS_CREATEORUPDATESEGMENT]
          @MAILSEGMENTID output,
          @CURRENTAPPUSERID,
          @CHANGEAGENTID,
          @MAILINGNAME,
          @SELECTIONS,
          @MAILTYPECODE,
          0,                          -- DeliveryMethodCode 0 - Mail

          @EXCLUDECONSTITSBASEDONPREFERENCE,
          @INCLUDECONSTITSWITHOUTPREFERENCE,
          @SEGMENTATIONID,
          @MAILPACKAGEID,
          @ASKLADDERID,
          @ADDRESSPROCESSINGOPTIONID,
          @NAMEFORMATPARAMETERID,
          @MAILDATE,
          @ESTIMATEDRESPONSERATE,
          @ESTIMATEDAVERAGEGIFTAMOUNT;

    end

    if @CHANNELCODE <> 2 and @EMAILPACKAGEID is not null
      begin
        set @INCLUDECONSTITSWITHOUTPREFERENCE =
            case @CHANNELCODE
              when 0 then
                case @CHANNELPREFERENCECODE
                  when 0 then 1
                  else 0
                end
              when 1 then 1
              else 0
            end;

        -- Create or update the segment

        exec dbo.[USP_COMMUNICATIONS_CREATEORUPDATESEGMENT]
          @EMAILSEGMENTID output,
          @CURRENTAPPUSERID,
          @CHANGEAGENTID,
          @MAILINGNAME,
          @SELECTIONS,
          @MAILTYPECODE,
          1,                          -- DeliveryMethodCode 1 - Email

          @EXCLUDECONSTITSBASEDONPREFERENCE,
          @INCLUDECONSTITSWITHOUTPREFERENCE,
          @SEGMENTATIONID,
          @EMAILPACKAGEID,
          @ASKLADDERID,
          @ADDRESSPROCESSINGOPTIONID,
          @NAMEFORMATPARAMETERID,
          @MAILDATE,
          @ESTIMATEDRESPONSERATE,
          @ESTIMATEDAVERAGEGIFTAMOUNT;

      end

  end try

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

  return 0;
end