USP_APPEALMAILING_CREATEORUPDATESEGMENT

CREATE procedure dbo.[USP_APPEALMAILING_CREATEORUPDATESEGMENT]
(
  /* All parameters are required when creating a new segment. */
  /* When updating an existing segment, parameters can be optionally supplied depending on what is being updated. */
  @APPEALMAILINGID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @NAME nvarchar(100) = null,
  @IDSETREGISTERID uniqueidentifier = null,
  @MKTPACKAGEID uniqueidentifier = null,
  @HOUSEHOLDINGTYPECODE tinyint = null,
  @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD bit = null,
  @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS bit = null,
  @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit = null,
  @EXCLUSIONDATETYPECODE tinyint = null,
  @EXCLUSIONASOFDATE datetime = null,
  @EXCLUDEDECEASED bit = null,
  @EXCLUDEINACTIVE bit = null,
  @EXCLUSIONS xml = null,
  @USEADDRESSPROCESSING bit = null output,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint = 0,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime = null,
  @CHANGEAGENTID uniqueidentifier = null,
  @APPEALID uniqueidentifier = null,  /* Only needed when calling from the add form. */
  @RECORDCOUNTNEEDSUPDATED bit = null output,
  @BASECURRENCYID uniqueidentifier = null
)
as
  set nocount on;

  declare @UPDATE bit;
  declare @MKTSEGMENTID uniqueidentifier;
  declare @MKTSEGMENTATIONSEGMENTID uniqueidentifier;
  declare @MKTSEGMENTATIONACTIVATEPROCESSID uniqueidentifier;
  declare @OLDIDSETREGISTERID uniqueidentifier;
  declare @OLDMKTPACKAGEID uniqueidentifier;
  declare @OLDHOUSEHOLDINGTYPECODE tinyint;
  declare @OLDHOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD bit;
  declare @OLDHOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS bit;
  declare @OLDHOUSEHOLDINGONERECORDPERHOUSEHOLD bit;
  declare @OLDUSEADDRESSPROCESSING bit;
  declare @OLDADDRESSPROCESSINGOPTIONID uniqueidentifier;
  declare @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint;
  declare @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime;
  declare @ADDRESSPROCESSINGOPTIONSCHANGED bit;
  declare @OLDMKTSEGMENTATIONPACKAGEID uniqueidentifier;
  declare @OLDNAME nvarchar(100);
  declare @OLDEXCLUSIONDATETYPECODE tinyint;
  declare @OLDEXCLUSIONASOFDATE datetime;
  declare @OLDEXCLUDEDECEASED bit;
  declare @OLDEXCLUDEINACTIVE bit;
  declare @OLDEXCLUSIONS xml;
  declare @EXCLUSIONSCHANGED bit;
  declare @CURRENTDATE datetime;
  declare @APPEALNAME nvarchar(100);
  declare @SELECTIONNAME nvarchar(300);
  declare @RECORDTYPES table ([ID] uniqueidentifier not null);
  declare @RECORDTYPEID uniqueidentifier;
  declare @SQL nvarchar(max);
  declare @TABLENAME nvarchar(128);
  declare @RECORDCOUNT int;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  declare @CURRENCYEXCHANGERATEID uniqueidentifier;

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

    set @CURRENTDATE = getdate();

    --Grab some info so we know whether to add or update (and what to update)...

    select
      @UPDATE = (case when [APPEALMAILING].[ID] is null then 0 else 1 end),
      @ADDRESSPROCESSINGOPTIONSCHANGED = 0,
      @EXCLUSIONSCHANGED = 0,
      @RECORDCOUNTNEEDSUPDATED = 0,
      @MKTSEGMENTATIONACTIVATEPROCESSID = [MKTSEGMENTATIONACTIVATEPROCESS].[ID],
      @MKTSEGMENTID = [MKTSEGMENTATIONSEGMENT].[SEGMENTID],
      @MKTSEGMENTATIONSEGMENTID = [APPEALMAILING].[MKTSEGMENTATIONSEGMENTID],
      @OLDIDSETREGISTERID = [MKTSEGMENTSELECTION].[SELECTIONID],
      @OLDMKTPACKAGEID = [MKTSEGMENTATIONSEGMENT].[PACKAGEID],
      @OLDHOUSEHOLDINGTYPECODE = [MKTSEGMENTATION].[HOUSEHOLDINGTYPECODE],
      @OLDHOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = [MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD],
      @OLDHOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = [MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS],
      @OLDHOUSEHOLDINGONERECORDPERHOUSEHOLD = [MKTSEGMENTATION].[HOUSEHOLDINGONERECORDPERHOUSEHOLD],
      @OLDUSEADDRESSPROCESSING = [MKTSEGMENTATION].[USEADDRESSPROCESSING],
      @OLDADDRESSPROCESSINGOPTIONID = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONID],
      @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
      @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATE = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
      @OLDMKTSEGMENTATIONPACKAGEID = [MKTSEGMENTATIONPACKAGE].[ID],
      @OLDNAME = [MKTSEGMENTATION].[NAME],
      @OLDEXCLUSIONDATETYPECODE = [BUSINESSPROCESSCOMMPREF].[DATETYPECODE],
      @OLDEXCLUSIONASOFDATE = [BUSINESSPROCESSCOMMPREF].[ASOFDATE],
      @OLDEXCLUDEDECEASED = [BUSINESSPROCESSCOMMPREF].[EXCLUDEDECEASED],
      @OLDEXCLUDEINACTIVE = [BUSINESSPROCESSCOMMPREF].[EXCLUDEINACTIVE],
      @OLDEXCLUSIONS = dbo.[UFN_BUSINESSPROCESSCOMMPREF_GETEXCLUSIONS_TOITEMLISTXML]([MKTSEGMENTATIONACTIVATEPROCESS].[ID]),
      @NAME = isnull(@NAME, [MKTSEGMENTATION].[NAME]),
      @APPEALNAME = (case when [APPEAL].[ID] is null and @APPEALID is not null then dbo.[UFN_APPEAL_GETNAME](@APPEALID) else [APPEAL].[NAME] end),
      @SELECTIONNAME = (case when @IDSETREGISTERID is null then [IDSETREGISTER].[NAME] else (select [NAME] from dbo.[IDSETREGISTER] where [ID] = @IDSETREGISTERID) end),
      @BASECURRENCYID = coalesce(@BASECURRENCYID, [MKTSEGMENTATION].[BASECURRENCYID], [APPEAL].[BASECURRENCYID])
    from dbo.[MKTSEGMENTATION]
    inner join dbo.[MKTSEGMENTATIONACTIVATEPROCESS] on [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    left join dbo.[APPEALMAILING] on [APPEALMAILING].[ID] = [MKTSEGMENTATION].[ID]
    left join dbo.[BUSINESSPROCESSCOMMPREF] on [BUSINESSPROCESSCOMMPREF].[BUSINESSPROCESSPARAMETERSETID] = [MKTSEGMENTATIONACTIVATEPROCESS].[ID]
    left join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [APPEALMAILING].[MKTSEGMENTATIONSEGMENTID]
    left join dbo.[MKTSEGMENTSELECTION] on [MKTSEGMENTSELECTION].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    left join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENTSELECTION].[SELECTIONID]
    left join dbo.[MKTSEGMENTATIONPACKAGE] on [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = [APPEALMAILING].[ID] and [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
    left join dbo.[APPEAL] on [APPEAL].[ID] = [APPEALMAILING].[APPEALID]
    where [MKTSEGMENTATION].[ID] = @APPEALMAILINGID;

    --Check to make sure all parameters are specified when creating a new segment...

    if @UPDATE = 0
      begin
        --It is possible for @EXCLUSIONS to be null if the user doesn't specify any solicit codes, so don't worry about that parameter.

        if @APPEALID is null or
           @APPEALMAILINGID is null or
           @NAME is null or
           @IDSETREGISTERID is null or
           @MKTPACKAGEID is null or
           @HOUSEHOLDINGTYPECODE is null or
           @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD is null or
           @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS is null or
           @HOUSEHOLDINGONERECORDPERHOUSEHOLD is null or
           @EXCLUSIONDATETYPECODE is null or
           (@EXCLUSIONDATETYPECODE = 1 and @EXCLUSIONASOFDATE is null) or
           @EXCLUDEDECEASED is null or
           @EXCLUDEINACTIVE is null or
           @USEADDRESSPROCESSING is null or
           (@USEADDRESSPROCESSING = 1 and @ADDRESSPROCESSINGOPTIONID is null) or
           (@USEADDRESSPROCESSING = 1 and @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE is null) or
           (@USEADDRESSPROCESSING = 1 and @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = 1 and @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE is null)
          raiserror('All parameters are required when creating a new segment.', 13, 1);
      end

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

if (@ORGANIZATIONCURRENCYID <> @BASECURRENCYID)
      set @CURRENCYEXCHANGERATEID = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null);


    /****************************************************************/
    /* Create or update the contact rules/communication preferences */
    /****************************************************************/
    if @UPDATE = 0
      begin
        set @ADDRESSPROCESSINGOPTIONSCHANGED = 1;
        set @EXCLUSIONSCHANGED = 1;

        --Save the contact rules/communication preferences for the mailing activation business process...

        exec dbo.[USP_BUSINESSPROCESSCOMMPREF_ADD]
          @CHANGEAGENTID = @CHANGEAGENTID,
          @BUSINESSPROCESSCATALOGID = '22C3D75C-A956-4BFC-A5FD-4B866BAEF509',
          @BUSINESSPROCESSPARAMETERSETID = @MKTSEGMENTATIONACTIVATEPROCESSID,
          @EXCLUSIONDATETYPECODE = @EXCLUSIONDATETYPECODE,
          @EXCLUSIONASOFDATE = @EXCLUSIONASOFDATE,
          @EXCLUDEDECEASED = @EXCLUDEDECEASED,
          @EXCLUDEINACTIVE = @EXCLUDEINACTIVE,
          @EXCLUSIONS = @EXCLUSIONS,
          @CURRENTAPPUSERID = @CURRENTAPPUSERID;
      end
    else
      begin
        set @ADDRESSPROCESSINGOPTIONSCHANGED = dbo.[UFN_MKTSEGMENTATION_COMPAREADDRESSPROCESSINGOPTIONS](@USEADDRESSPROCESSING, @ADDRESSPROCESSINGOPTIONID, @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE, @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE, @OLDUSEADDRESSPROCESSING, @OLDADDRESSPROCESSINGOPTIONID, @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE, @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATE);
        set @EXCLUSIONSCHANGED = dbo.[UFN_MKTSEGMENTATION_COMPARECOMMPREFS](@EXCLUSIONDATETYPECODE, @EXCLUSIONASOFDATE, @EXCLUDEDECEASED, @EXCLUDEINACTIVE, @EXCLUSIONS, @OLDEXCLUSIONDATETYPECODE, @OLDEXCLUSIONASOFDATE, @OLDEXCLUDEDECEASED, @OLDEXCLUDEINACTIVE, @OLDEXCLUSIONS);

        if @EXCLUSIONSCHANGED = 1
          --Update the contact rules/communication preferences for the mailing activation business process...

          exec dbo.[USP_BUSINESSPROCESSCOMMPREF_UPDATE]
            @CHANGEAGENTID = @CHANGEAGENTID,
            @BUSINESSPROCESSCATALOGID = '22C3D75C-A956-4BFC-A5FD-4B866BAEF509',
            @BUSINESSPROCESSPARAMETERSETID = @MKTSEGMENTATIONACTIVATEPROCESSID,
            @EXCLUSIONDATETYPECODE = @EXCLUSIONDATETYPECODE,
            @EXCLUSIONASOFDATE = @EXCLUSIONASOFDATE,
            @EXCLUDEDECEASED = @EXCLUDEDECEASED,
            @EXCLUDEINACTIVE = @EXCLUDEINACTIVE,
            @EXCLUSIONS = @EXCLUSIONS,
            @CURRENTAPPUSERID = @CURRENTAPPUSERID;
      end



    /*************************************/
    /* Create or update the base segment */
    /*************************************/
    declare @MKTSEGMENTNAME nvarchar(100);
    declare @MKTSEGMENTDESCRIPTION nvarchar(255);
    declare @TEMPSEGMENTNAME nvarchar(100);
    declare @COUNTER int;

    if upper(right(@SELECTIONNAME, 15)) = upper(' (Ad-hoc Query)')
      set @SELECTIONNAME = left(@SELECTIONNAME, len(@SELECTIONNAME) - 15);

    set @TEMPSEGMENTNAME = @APPEALNAME + ': ' + @SELECTIONNAME;
    set @COUNTER = 0;

    --Get a unique name if the user wants to use the same selection for multiple mailings in this appeal...

    while exists(select 1 from dbo.[MKTSEGMENT] where [NAME] = @TEMPSEGMENTNAME)
    begin
      set @COUNTER = @COUNTER + 1;
      set @TEMPSEGMENTNAME = left(@TEMPSEGMENTNAME, 100 - len(cast(@COUNTER as nvarchar(10))) - 1) + ' ' + cast(@COUNTER as nvarchar(10));
    end

    set @MKTSEGMENTNAME = @TEMPSEGMENTNAME;
    set @MKTSEGMENTDESCRIPTION = 'Auto generated segment for mailing "' + @NAME + '".';

    declare @BBECRECORDSOURCEID uniqueidentifier;
    set @BBECRECORDSOURCEID = dbo.[UFN_MKTRECORDSOURCE_GETFIRSTBBECRECORDSOURCEID]();

    if @UPDATE = 0 or @OLDIDSETREGISTERID is null
      begin
        set @MKTSEGMENTID = newid();

        insert into dbo.[MKTSEGMENT] (
          [ID],
          [NAME],
          [DESCRIPTION],
          [SEGMENTTYPECODE],
          [QUERYVIEWCATALOGID],
          [ISSYSTEM],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        ) values (
          @MKTSEGMENTID,
          @MKTSEGMENTNAME,
          @MKTSEGMENTDESCRIPTION,
          1,
          @BBECRECORDSOURCEID,
          1,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        );

        --Insert a parameter set for the refresh process for this segment...

        insert into dbo.[MKTSEGMENTREFRESHPROCESS] (
          [ID],
          [SEGMENTID],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        ) values (
          NewID(),
          @MKTSEGMENTID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        );
      end
    else if (@NAME is not null and @NAME <> @OLDNAME) or (@IDSETREGISTERID is not null and @IDSETREGISTERID <> @OLDIDSETREGISTERID)
      --Update the base segment name and description...

      update dbo.[MKTSEGMENT] set
        [NAME] = @MKTSEGMENTNAME,
        [DESCRIPTION] = @MKTSEGMENTDESCRIPTION,
        [ISSYSTEM] = 1,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ID] = @MKTSEGMENTID;



    /****************************************/
    /* Create or update the mailing segment */
    /****************************************/
    declare @MKTSEGMENTSELECTIONS xml = (select @IDSETREGISTERID as [SELECTIONID] for xml raw('ITEM'),type,elements,root('SELECTIONS'),BINARY BASE64);

    if @UPDATE = 0 or @OLDIDSETREGISTERID is null
      begin
        --Save the segment's selection...

        exec dbo.[USP_MKTSEGMENT_GETSELECTIONS_ADDFROMXML] @MKTSEGMENTID, null, @MKTSEGMENTSELECTIONS, @CHANGEAGENTID, @CURRENTDATE;

        --Create the mailing segment...

        set @MKTSEGMENTATIONSEGMENTID = newid();
        insert into dbo.[MKTSEGMENTATIONSEGMENT] (
          [ID],
          [SEGMENTATIONID],
          [SEGMENTID],
          [PACKAGEID],
          [SEQUENCE],
          [BASECURRENCYID],
          [CURRENCYEXCHANGERATEID],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        ) values (
          @MKTSEGMENTATIONSEGMENTID,
          @APPEALMAILINGID,
          @MKTSEGMENTID,
          @MKTPACKAGEID,
          1,
          @BASECURRENCYID,
          @CURRENCYEXCHANGERATEID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        );

        if @UPDATE = 0 or @OLDIDSETREGISTERID is not null
          --Insert the appeal mailing (needs to be done here in order for the segment view to be created properly)...

          insert into dbo.[APPEALMAILING] (
            [ID],
            [APPEALID],
            [MKTSEGMENTATIONSEGMENTID],
            [ADDEDBYID],
            [CHANGEDBYID],
            [DATEADDED],
            [DATECHANGED]
          ) values (
            @APPEALMAILINGID,
            @APPEALID,
            @MKTSEGMENTATIONSEGMENTID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
          );
        else
          --Update the appeal mailing segmentation segment id (this mailing was created using the appeal mailing setup minimal add form)

          update dbo.APPEALMAILING set
            MKTSEGMENTATIONSEGMENTID = @MKTSEGMENTATIONSEGMENTID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
          where ID = @APPEALMAILINGID;
      end
    else if @IDSETREGISTERID is not null and @IDSETREGISTERID <> @OLDIDSETREGISTERID
      begin
        --Update the segment's selection...

        exec dbo.[USP_MKTSEGMENT_GETSELECTIONS_UPDATEFROMXML] @MKTSEGMENTID, null, @MKTSEGMENTSELECTIONS, @CHANGEAGENTID, @CURRENTDATE;
      end

    if @UPDATE = 0 or (@IDSETREGISTERID is not null and @HOUSEHOLDINGTYPECODE is not null and (@IDSETREGISTERID <> @OLDIDSETREGISTERID or @HOUSEHOLDINGTYPECODE <> @OLDHOUSEHOLDINGTYPECODE or @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD <> @OLDHOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD or @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS <> @OLDHOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS or @HOUSEHOLDINGONERECORDPERHOUSEHOLD <> @OLDHOUSEHOLDINGONERECORDPERHOUSEHOLD or @ADDRESSPROCESSINGOPTIONSCHANGED = 1 or @EXCLUSIONSCHANGED = 1 or dbo.[UFN_MKTSEGMENTATIONSEGMENT_ISRECORDCOUNTCACHECURRENT](@MKTSEGMENTATIONSEGMENTID, 1) = 0))
      begin
        --Update the segment view...

        exec dbo.[USP_MKTSEGMENT_CREATEORUPDATEVIEW] @MKTSEGMENTID, @CHANGEAGENTID, 0;

        --Mark the segment selection as system-defined...

        update dbo.[IDSETREGISTER] set
          [ISSYSTEM] = 1,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where [ID] = (select [IDSETREGISTERID] from dbo.[MKTSEGMENT] where [ID] = @MKTSEGMENTID);

        --If householding changed, then we must update the base mailing table before we recreate the segment views, since the views contain the householding logic...

        if @UPDATE = 1 and (@HOUSEHOLDINGTYPECODE <> @OLDHOUSEHOLDINGTYPECODE or @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD <> @OLDHOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD or @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS <> @OLDHOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS or @HOUSEHOLDINGONERECORDPERHOUSEHOLD <> @OLDHOUSEHOLDINGONERECORDPERHOUSEHOLD or @ADDRESSPROCESSINGOPTIONSCHANGED = 1)
          update dbo.[MKTSEGMENTATION] set
            [HOUSEHOLDINGTYPECODE] = @HOUSEHOLDINGTYPECODE,
            [HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD] = @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD,
            [HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS] = @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS,
            [HOUSEHOLDINGONERECORDPERHOUSEHOLD] = @HOUSEHOLDINGONERECORDPERHOUSEHOLD,
            [USEADDRESSPROCESSING] = @USEADDRESSPROCESSING,
            [ADDRESSPROCESSINGOPTIONID] = @ADDRESSPROCESSINGOPTIONID,
            [ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE] = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE,
            [ADDRESSPROCESSINGOPTIONSEASONALASOFDATE] = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
          where [ID] = @APPEALMAILINGID;

        set @RECORDCOUNTNEEDSUPDATED = 1;
      end



    /*************************************************/
    /* Update the package on the segment and mailing */
    /*************************************************/
    if @UPDATE = 1 and @MKTPACKAGEID is not null and @MKTPACKAGEID <> @OLDMKTPACKAGEID
      begin
        --Update the package on the mailing segment...

        update dbo.[MKTSEGMENTATIONSEGMENT] set 
          [PACKAGEID] = @MKTPACKAGEID,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where [ID] = @MKTSEGMENTATIONSEGMENTID;

        --Remove the old package for the mailing...

        exec dbo.[USP_MKTSEGMENTATIONPACKAGE_DELETE] @OLDMKTSEGMENTATIONPACKAGEID, @CHANGEAGENTID;
      end

    if @UPDATE = 0 or (@MKTPACKAGEID is not null and @MKTPACKAGEID <> @OLDMKTPACKAGEID)
      --Add/update the package to the mailing...

      exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONPACKAGE] null, @CHANGEAGENTID, @APPEALMAILINGID, @MKTPACKAGEID, @BASECURRENCYID;
  end try

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

  return 0;